guiadeexcelavanzado

UNIVERSIDAD DE CARABOBO FACULTAD DE CIENCIAS ECONMICAS Y SOCIALES ESCUELA DE ECONOMA DEPARTAMENTO DE MTODOS CUANTITATIVOS PARA ECONOMA

GUA DE EXCEL AVANZADO 2007 TABLA DE CONTENIDOSECCIN 1.: ESTRUCTURA FUNDAMENTAL DE UNA HOJA DE TRABAJO Pantalla principal Etiquetas y gestin de las hojas Uso del mouse Formato de celdas Frmulas Insertar Funciones Opciones de impresin SECCIN 2.: LISTAS: ESTRUCTURA FUNDAMENTAL DE UNA BASE DE DATOS Ficha de datos Ordenar datos Para ordenar por varios campos o criterios Referencias relativas y absolutas Filtro automtico Creacin automtica de esquemas Uso de Subtotales Matrices SECCIN 3.: TABLA DE DATOS DE UNA Y DOS VARIABLES Recalcular las tablas de datos SECCIN 4.: OBTENIENDO INFORMACIN DE LAS LISTAS Funcin: BUSCARV Funcin : BUSCARH Funcin COINCIDIR Funcin: INDICE Funcin: DESREF Funcin: BUSCAR Utilizacin de los botones de control Administracin de datos con la funcin transponer y las opciones de pegado especial Protegiendo informacin SECCIN 5.: TRABAJANDO CON TABLAS DINMICAS Eliminar una tabla dinmica. Obtener promedios en una tabla dinmica Grfico dinmico Tabla dinmica: Caso de estudio: Encuesta sobre el consumo de aceite de oliva Tabla dinmica: Caso de estudio: Estudio del mercado de postres Anlisis de contingencia con tablas dinmicas Para entender el anlisis de contingencia SECCIN 6.: CREACIN DE ESCENARIOS Y VISTAS Manejo de escenarios para un proyecto empresarial Vistas rea de trabajo SECCIN 7.: BUSCAR OBJETIVOS Y SOLVER SECCIN 8.: CONSOLIDACIN DE DATOS Consolidacin en hojas mltiples dentro del mismo libro de trabajo. Consolidacin de datos con la herramienta CONSOLIDACIN Consolidacin de datos con tablas dinmicas SECCIN 9.: PRONSTICOS CON EXCEL Componentes de una serie de tiempo Media mvil Tendencia Crecimiento Suavizacin exponencial Pronsticos suavizados para manejar datos por estacinElaborado por: Prof. Sonia Albarracn

3 3 4 7 8 9 10 11 15 18 18 18 19 20 21 22 25 26 27 28 28 29 30 30 32 33 33 34 41 41 42 47 48 49 49 50 50 51 52 53 54 55 56 60 60 61 61 63 64 64 64 65 65 65

2

SECCIN 10.: UTILIZACIN AVANZADA DE FRMULAS SECCIN 11.IMPORTANDO DATOS EXTERNOS SECCIN 12.: MANEJO DE GRFICOS

66 68 70

Elaborado por: Prof. Sonia Albarracn

3

SECCIN 1.: ESTRUCTURA FUNDAMENTAL DE UNA HOJA DE TRABAJO En el men de inicio Todos los programas, Microsoft Office, Microsoft Office Excel 2007, aunque el icono puede estar en el escritorio o el men de inicio. VIDEOS: http://www.todoexcel.com/videoclases-excel/15-novedades-excel-2007.php http://www.youtube.com/watch?v=d_gd3kFqI7g&feature=related http://www.youtube.com/watch?v=scO63GyL5yo

Pantalla principal

Ilustracin 1: Pantalla de Excel

1. Barra de Inicio Rpido (Nueva caracterstica en Office 2007). Al hacer clic en el este botn se podrn mostrar u ocultar los comandos que desee utilizar con mayor frecuencia. Tambin se podr minimizar la cinta de opciones.Elaborado por: Prof. Sonia Albarracn

4

2. Barra de Ttulo de la ventana de Excel, incluye el nombre del libro abierto. 3. Botones normales de las ventanas de Windows (minimizar, maximizar y cerrar). 4. Fichas: Esta es la forma caracterstica de presentar las herramientas en Office 2007. Se dividen en grupos. Con doble clic sobre cualquiera de las etiquetas de la ficha se oculta la cinta de opciones. 5. Conjunto de Herramientas conformando Grupos dentro de la Cinta De Opciones que cambiar segn la ficha seleccionada. Algunos grupos tienen un pequeo botn en el extremo inferior derecho el cual mostrar el cuadro de dilogo de la opcin (ver Ilustracin 2)

Ilustracin 2: para mostrar el cuadro de dilogo de la opcin.

6. Encabezados de columnas de la hoja. 7. Encabezados de filas de la hoja. 8. Celda activa. 9. Cuadro de nombres, indica la celda activa 10. Barra de frmulas y asistente para funciones. 11. Etiquetas de las hojas, para desplazarse o insertar nuevas hojas al libro. 12. Barra de desplazamiento horizontal que permite desplazar el contenido de de forma lateral. 13. Zoom sobre la hoja. Estos controles son nuevos y permiten ampliar o reducir el porcentaje de zoom de forma rpida y precisa. 14. Controles para cambiar la vista de la hoja. 15. Barra de estado. Muestra los mensajes que corresponden a lo que se est realizando. En este caso aparece listo, lo cual indica que el programa est preparado para que el usuario elija un comando o escriba datos en una celda. Adems informa sobre el estado de las teclas de bloqueo de maysculas, bloqueo de nmeros y otros. Etiquetas y gestin de las hojas Agregar hojas: Clic con el botn contrario en la etiqueta de la hoja, opcin Insertar (Shift + F11)Elaborado por: Prof. Sonia Albarracn

5

Eliminar hojas: Clic con el botn contrario en la etiqueta de la hoja, opcin eliminar Cambiar nombre: Doble clic en etiqueta de la hoja e introduzca en nombre, tambin podr usar la opcin Cambiar nombre del men contextual Cambiar orden: clic en la etiqueta y arrstrela hasta su nueva posicin, suelte el botn en el lugar deseado Cambiar color: clic con el botn contrario en la etiqueta de la hoja, opcin Color de etiqueta, seleccione el color y haga clic en Aceptar. Si lo que desea es quitar el color, seleccione Sin color. Filas y columnas Alto de filas: Ficha inicio, grupo Celdas la opcin Formato, tamao de la celda, Alto de la fila. Ancho de la columna: Ficha inicio grupo Celdas la opcin Formato, tamao de la celda, Ancho de columna. En este mismo men Ancho predeterminado automticamente asigna 10.71 que es la medida por omisin para el ancho de las columnas. Auto ajustar: Con el Mouse en medio del encabezado, doble clic. Tambin podr usar las opciones Ajustar, para fila o columna segn sea el caso. Ocultar y mostrar: Ubquese en una celda de la columna oculta y ficha inicio grupo Celdas la opcin Formato, Visibilidad, ocultar y mostrar. Cmo me ubico en la celda que no se ve? En el cuadro de nombres escriba la referencia de esta columna, por ejemplo C2, y de un enter. Trabajo con libros En la barra de inicio rpido, clic en el botn Guardar, en Nombre de Archivo se escribe el nombre. Una vez que se le asignaron por primera vez las opciones, ya no es necesario volver a especificarlas. Si se desea cambiar alguno de los parmetros se utiliza Guardar como del botn de office. Poner contrasea 1. Guardar como o F12. 2. En el cuadro de Guardar como, clic en el botn Herramientas. 3. Del men clic en Opciones generales. 4. Frente a Contrasea de APERTURA escriba la contrasea.Elaborado por: Prof. Sonia Albarracn

6

5. Clic en aceptar 6. Aparecer un el cuadro Confirmar contrasea, vuelva a escribirla y de un clic en aceptar 7. Nuevamente queda en el cuadro de Guardar como, de un clic en el botn Guardar 8. Si Excel le indica que el libro ya existe y que si desea reemplazarlo haga clic en Si Quitar o cambiar una contrasea Repita los pasos de Poner contrasea; Para cambiarla cuando llegue al numeral 4 borre los caracteres (puntos) de la contrasea anterior, escriba una nueva y contine hasta el paso 8. Para eliminarla borre los puntos de la contrasea anterior dejando el cuadro Contrasea de apertura en blanco y contine con los dems pasos hasta el 8. Cerrar archivo y salir de Excel Para cerrar el archivo actual seleccione Cerrar del botn de office. Si no se ha guardado el archivo o se hicieron modificaciones desde la ltima vez que se guard, Excel preguntar si se quiere guardar el archivo. Para salir de Excel doble clic en el botn de Office. Abrir un libro: Botn de office opcin Abrir. All tambin encontrar los archivos recientes. Para mantener la compatibilidad con versiones anteriores, en el cuadro de dilogo en la opcin Guardar como tipo seleccione libro de Excel 97-2003 Edicin de celdas Insertar datos numricos y textos: Para capturar o introducir informacin, se debe estar en la celda deseada y comenzar a escribir. Al hacer esto, la barra de frmulas un aspecto diferente y adquiere las siguientes funciones:

Ilustracin 3:Barra de frmulas al editar

Cancelar Este botn deja la celda como estaba antes de escribir, tambin puede pulsar la tecla Escape IntroducirElaborado por: Prof. Sonia Albarracn

7

Lo que se ha escrito queda en la celda, tambin puede pulsar la tecla Enter Introducir funcin Abre el cuadro Insertar funcin, esta opcin se estudiar ms adelante Correccin y borrado de datos: Por barra de frmulas, clic en barra de frmulas. Por teclado: F2. Con Mouse: doble clic en la celda Uso del mouse Dependiendo de la ubicacin del Mouse en la celda este se podr utilizar de con diversos fines Seleccin: Con el Mouse en el centro de la celda, el apuntador toma forma de cruz gruesa de color blanco. Dar un clic sostenido sobre el rea deseada. Para seleccionar columnas o filas enteras, llevar el Mouse sobre los encabezados, cuando luzca como una flecha slida de color negro clic extendiendo el Mouse, hasta seleccionar cuantas columnas o filas se deseen. Para seleccionar toda la hoja Ctrl. +E Mover y copiar con el Mouse Para mover algn texto u objeto primeramente se selecciona, despus se coloca el Mouse sobre el borde del rea seleccionada. Con un clic sostenido se mueve la seleccin al lugar deseado. Para copiar se utiliza el mismo mtodo, pero presionando la tecla . Cortar copiar y pegar Copiar: Seleccionar las celdas deseadas. Men contextual opcin Copiar Cortar: Se selecciona la informacin deseada Men contextual opcin Cortar. Pegar Men contextual opcin Pegar. Deshacer y repetir: Barra de inicio rpido, botn Deshacer. Barra de inicio rpido, opcin Rehacer. Buscar y reemplazar Buscar; Ficha inicio, grupo Modificar opcin Buscar y seleccionar, buscar (Ctrl. +B).


8

En el cuadro escribimos el texto que deseamos encontrar. El botn Buscar Siguiente busca y selecciona el siguiente texto especificado. El botn Buscar todos mostrar todas las celdas que contienen lo buscado, al hacer clic en alguna de ellas Excel mostrar la celda. Reemplazar; en el cuadro de dilogo anterior, clic en la ficha Reemplazar, con las opciones Reemplazar y Reemplazar Todo podemos ir cambiando una ocurrencia a la vez o bien todas las ocurrencias, respectivamente. Formato de celdas Ficha inicio, grupo Celdas la opcin Formato, Formato celdas, (Ctrl +1 del teclado estndar) Ficha nmero Segn el tipo de datos numricos que se tengan se debe elegir su formato, Las categoras Moneda y Contabilidad ponen smbolos , $ u otro que usted seleccione, tambin permite cambiar el nmero de decimales. En Excel una fecha es el la cantidad de das trascurridos desde el 1 de enero de 1900 hasta la fecha digitada, dicho de otra forma es un nmero. El nmero 39923, por ejemplo, corresponde al 20 de abril del 2009, es decir que habr trascurrido 39923 das desde el 1 de enero de 1900 hasta esa fecha. Si se digita 1/sep/08 Excel entender que se trata del primero de septiembre del 2008, al digitar solamente 1/sep Excel asumir que es de ao actual. Tambin puede usar guiones, por ejemplo, 1-sep o escribir el mes en nmeros, 1-9. Ntese que Excel no usa Setiembre, usa Septiembre por ser de mejor aceptacin. Algunos formatos como Porcentaje se aplican automticamente al escribir, por ejemplo si en una celda se introduce 5%, esa celda mantendr el formato de porcentaje an cambiando el 5. Se pueden eliminar los formatos de nmero de las celdas asignado la categora General Ficha alineacin Controla la posicin de los datos en las celdas, horizontal, vertical o un espacio adicional desde el borde de la celda con la opcin Sangra. En Control de texto se cambia la forma en como se ajusta el contenido cuando ste es ms ancho que la celda y no se desea cambiar el ancho de la columna. En Orientacin se cambia el ngulo de los caracteres dentro de la celda, cuando se aplica con algn borde, este tendr la orientacin que se le asigno a la celda.Elaborado por: Prof. Sonia Albarracn

9

Ficha fuentes Fuente, su estilo y Tamao. "Subrayado" y tipo de Subrayado. Efectos de Tachado, Subndice o Superndice Tambin se puede cambiar el Color eligindolo de la lista correspondiente. Ficha bordes Contorno pone borde a los exteriores de la seleccin, Interior lo hace con las todas las celdas excepto a los exteriores. Ficha Relleno Con esta ficha se da color a las celdas seleccionadas. Formatos automticos Esta opcin aplica varios de los formatos ya vistos de forma simultnea con base en diseos incorporados con Excel. Para una tabla: Seleccione la tabla, Ficha inicio, grupo Estilos la opcin Dar formato como tabla. Seleccione el diseo que desee y haga clic en el botn Aceptar. Para eliminar el formato, seleccione el primer estilo. Una vez se ubique dentro de la tabla aparecern de forma automtica la herramientas de tabla en la cinta de opciones. En el grupo opciones de estilo podr modificar los aspectos de la tabla seleccionada. Para celdas: Seleccione las celdas, Ficha inicio, grupo Estilos la opcin Estilos de celda. Frmulas En Excel una frmula es cualquier operacin matemtica, desde una simple suma hasta complejas operaciones de clculo de las celdas de una hoja. Este es el verdadero potencial de una hoja de clculo, el manejo de las frmulas, Frmulas Siga estos pasos para crear una frmula mediante el mtodo de sealar y hacer clic: 1. Ubquese en la celda en la que se desea el resultado. 2. Escriba el signo igual (=) para indicarle a Excel que se va a introducir una frmula. 3. Haga clic en la primera celda que desea incluir 4. Escriba el signo del operador.Elaborado por: Prof. Sonia Albarracn

10

5. Haga clic en la siguiente celda de la frmula 6. Pulse Enter Operadores Suma + Resta - Multiplicacin * Divisin Insertar Insercin de celdas, filas o columnas Seleccionar la celda o el rango de celdas deseado, en la ficha Inicio, grupo Celdas opcin Insertar (Ctrl. + ms del teclado numrico) Eliminar celdas, filas y columnas. Para eliminar una celda, columna o fila se selecciona las que desean eliminar, ficha Inicio, grupo Celdas opcin Eliminar (Ctrl. + menos del teclado numrico) Nota Mayscula (Shift) + espaciadora selecciona toda la fila, Ctrl. + espaciadora toda la columna, al pulsar Ctrl. + ms del teclado numrico, se insertar una fila o columna segn lo seleccionado. Tambinpuede eliminar o insertar columnas completas con el men contextual (botn derecho) en los encabezados de filas o columnas. Ver Ilustracin 4

Ilustracin 4: Opciones para insertar o eliminar celdas, filas y columnas

Comentarios Ubquese en la celda a que le quiere agregar el comentario y pulse la teclas Mayscula + F2 (Shift + F2).Elaborado por: Prof. Sonia Albarracn

11

Para eliminar el comentario haga clic en la celda con el botn contrario (men contextual) y elija Eliminar comentario En el men Edicin podr usar la opcin Eliminar, Comentario Nota En la celda que tiene un comentario, elegir Mostrar comentario. Esto har que est visible an sin pasar el Mouse por la celda. Tema opcional: Operaciones entre fechas Aunque Excel representa las fechas en formato das/mes/ao, su valor corresponde al nmero de das que han transcurrido desde el primero de enero de 1900. Escriba el nmero 39.000 y aplquele formato de fecha. Corresponde al 10 de octubre del 2006, si se suma 5 a esta celda, el resultado ser 15 de octubre del 2006. Para calcular su edad en das; escriba su fecha de nacimiento, escriba la fecha actual. A la fecha actual reste la fecha de su nacimiento a esta celda aplique formato o estilo millares sin decimales. El resultado ser el nmero de das que usted ha vivido, al dividirlo entre 30 obtendr meses y entre 365 aos. Ahora ya sabe realizar clculos entre fechas que le sern de mucha utilidad. Funciones Es una forma abreviada para hacer operaciones, desde algunas simples como sumatoria o promedio hasta las ms complejas para finanzas o ingeniera. Esta funciones est predeterminadas en Excel, de tal forma que se pueden usar en cualquier momento. Hay varios mtodos para insertar funciones, Shift +F3, clic en el botn insertar funciones de la barra de frmulas o en la ficha Frmulas, grupo Biblioteca de funciones opcin insertar funcin. Con cualquiera de ellos aparece este cuadro de dilogo:


12

Ilustracin 5: Cuadro de dilogo de funciones

Si conoce el nombre de la funcin, en Categora seleccione Todas y seleccinela de la lista, est en orden alfabtico. Tambin podr escribir una breve descripcin de la funcin en el cuadro Buscar. Ver Ilustracin 5. Excel mostrar funciones relacionadas con la bsqueda. Al seleccionar la funcin de la lista, en la parte inferior del cuadro de dilogo aparecer una breve descripcin de la misma con sus argumentos. Puede seleccionar ayuda sobre esta funcin para que Excel abra la ventana de ayuda con explicaciones o ejemplos sobre la funcin. Siga estos pasos para insertar una funcin: 1. Ubquese en la celda en la que desea el resultado 2. Abra el cuadro Insertar funcin por el mtodo que desee, por ejemplo Shift +F3 3. Seccione la funcin deseada y haga clic en aceptar 4. Seleccione los argumentos o celdas de la funcin, use el botn seleccionar celdas. 5. Pulse Enter dos veces Si se van a incluir ms celdas como argumento de una funcin deber repetir el paso 4 Sintaxis de una funcin Sintaxis: SIGNO IGUAL FUNCIN PARNTESIS ARGUMENTOS PARNTESIS Ejemplo1: = SUMA ( C3:C10 ) Ejemplo2: = MAX ( B3:B12 ) Ejemplo3: = MIN ( B2:B10 ) Funciones sin argumentosElaborado por: Prof. Sonia Albarracn

13

Algunas funciones como AHORA, (que muestra la hora del sistema), no requieren de argumentos, esto se podr ver por que el espacio para argumento estar vaci () en la descripcin de la funcin. Grficos Un grfico es una representacin de los datos de la hoja de clculo. Para que la elaboracin de un grfico sea una tarea sencilla los datos deben estar bien organizados por filas o columnas y no mezclar en las mismas series rtulos de texto con nmeros o fechas con porcentajes. Aunque lo que realmente lo que se grafican son nmeros, Excel automticamente los incluir los rtulos con el fin de ilustrar mejor el grfico. Siga estos pasos para crear un grfico con el asistente para grficos: 1. Seleccione los datos con sus rtulos 2. Clic en la ficha Insertar, grupo Grficos 3. Seleccione el tipo el tipo de grfico, por ejemplo columna, lneas, circular o rea. Si desea otro, haga clic en la ltima opcin; Todos los tipos de grfico y seleccinelo. Tipos de grficos VIDEOS: http://www.youtube.com/watch?v=8FnlqDxCtuM&feature=related El grfico de columnas permite comparar valores de varios conjuntos directamente. Un grfico circular est diseado para mostrar comparaciones dentro de un solo conjunto de valores y mostrar cmo las diferentes partes contribuyen al total El grfico de lneas es adecuado para mostrar los cambios y tendencias a lo largo del tiempo Cambiar a un tipo de grfico existente. Para hacer modificaciones se usa la ficha Herramientas de grficos que slo se muestra cuando se selecciona un grfico. En la ficha Diseo, grupo Tipo, opcin Cambiar tipo de grfico. A la izquierda del cuadro aparecen los tipos y a la derecha los subtipos. Clic en el tipo deseado y Aceptar. Cambiar datos de origen En la ficha Diseo, grupo Datos, opcin Seleccionar datos. Con el cuadro Rango de datos del grfico podr seleccionar ms o menos datos que segn desee incluir en el grfico. En este mismoElaborado por: Prof. Sonia Albarracn

14

cuadro con el botn Cambiar fila/Columna podr alternar entre series y categoras o quitar un dato en medio las series. Opciones de grfico Dependiendo del tipo de grfico se dispondrn de las opciones correspondientes al mismo, ya que por ejemplo no se dispondr de rtulos de eje para un grfico tipo circular. Rotulacin. Ficha Presentacin, grupo Etiquetas. All se podrn ocultar o mostrar ttulo del grfico, aunque de forma predeterminada Excel lo pone en la parte superior central, se puede arrastrar el cuadro que lo contiene hacia otro lugar dentro del grfico. La leyenda es un recuadro con un texto que relaciona el color de los datos con lo que cada dato representa. Con rtulos de datos se ponen nombres o valores dentro del grfico sobre la columna correspondiente. Tabla de datos, muestra los datos de origen en la parte inferior dentro de la misma rea del grfico. Lneas de cuadrcula sirve para colocar o quitar las cuadrculas verticales u horizontales. Ficha Presentacin grupo Ejes. Vista en 3D. Las opciones 3D tambin dependern del tipo e grfico, los de columnas, barras lneas y reas son similares. Ficha Presentacin, grupo fondo, opcin Giro 3D. Giro. Cambia el ngulo de visualizacin en los ejes X, Y y Z Escala del grfico. Si se desmarca la casilla Ejes en ngulo recto se podrn cambiar las opcin de perspectiva, da mayor profundidad la grfico. Los anteriores pueden ser cambiados usando los botones de las flechas o escribiendo directamente los valores en las casillas. Alto % de la base, cambia la proporcin del alto con respecto al ancho del grfico. Cambiar grficos circulares Para mover series simultneamente arrastre una de ellas hacia fuera del centro, para moverlas de formaindependiente haga un segundo clic (no doble clic) en la serie y seprela de las otras. Para reubicarlas, repita el proceso anterior hacia el centro del grfico. Cambiar formatos Todas las opciones estarn en la ficha Formato y dependern de elemento seleccionado, por ejemplo si se selecciona el rea de trazado, en Estilo visual de forma se dispondr de contornos coloreados, al seleccionar un eje sern solo lneas.


15

Se podrn seleccionar elementos de un grfico haciendo clic en ellos o en la ficha Formato grupos Seleccin actual hay un men desplegable llamado Elementos del grfico. Al hacer clic en Aplicar formato a la seleccin aparecer un cuadro de dilogo con las opciones del objeto. Colores y efectos Tambin podr cambiar colores de relleno, borde y efectos en el grupo Estilos de forma de la fichaFormato. Los bordes son recuadros que podrn tener diferentes estilos grosores o colores. El rea es el color del relleno, con el botn Efectos de relleno podr poner sombreados, texturas e inclusive imgenes en los objetos del grfico. Cambiar ubicacin Para pasar un grfico de la hoja de clculo a una hoja de grfico y viceversa; en la ficha Diseo, grupo Ubicacin clic en la opcin Mover grfico, seleccione la ubicacin deseada y haga clic en Aceptar. Haga el grfico de la muestra con los datos que ve en la hoja de clculo. Ver Ilustracin 6

Ilustracin 6: Muestra para practicar grficos

Opciones de impresin Vista preliminar Permite ver cual ser el aspecto de la hoja en al impresora. Para obtener la vista preliminar Ctrl. + F2. Los botones Pgina siguiente y Pgina anterior permiten desplazarse entre varias pginas. Zoom realiza un acercamiento. Las opciones Imprimir y Configurar pgina se explicarn msElaborado por: Prof. Sonia Albarracn

16

adelante. La casilla Mostrar mrgenes muestra las lneas de los lugares donde se encuentran las mrgenes, las cuales pueden ser movidas con el Mouse. El botn Cerrar vista preliminar nos deja nuevamente en la hoja. Vista de salto de pgina Es una forma de ver cmo se distribuirn las filas y columnas al ser impresas en el papel. Clic en la ficha Vista, grupo Vistas del libro, botn Vista previa salto de pgina. Los bordes resaltados, generalmente de color azul, muestran los que sern los bordes de la hoja. Puede arrastrarlos para cambiar el contenido de cada pgina impresa. Configurar pgina Aqu se dan los parmetros referentes a cmo se imprimir la hoja. Ficha Diseo de pgina, grupo configurar pgina. Se podrn cambiar las mrgenes, orientacin o tamao del papel. En Tamao de papel verifique que est seleccionado el mismo que tiene en la impresora. rea de impresin Con esta opcin se le indicar a Excel que solo debe imprimir las celdas seleccionadas sin importar la cantidad de datos en la hoja. Para establecer rea de impresin seleccione las celdas que desea incluir, haga clic en el botn rea de impresin, opcin Establecer rea de impresin. Al guardar el libro, tambin se guardar el rea de impresin. Para agregar celdas a un rea de impresin existente, seleccione las celdas que desea agregar al rea de impresin. Haga clic en el botn rea de impresin opcin Agregar al rea de impresin. Para eliminar un rea de impresin del mismo botn seleccione Borrar rea de impresin Saltos de pgina Envan un comando a la impresora para que expulse la hoja en el lugar indicado. Con el botn Saltos se podrn insertar, borrar o reestablecer a sus valores originales. Ajustar rea de impresin Esta opcin permite controlar reducir o aumentar el tamao al que se imprimir segn la cantidad de datos y el nmero de pginas. Si lo que desea es imprimir en una pgina de ancho sin importar cuntas de alto, en Ajustar a escriba 1 en pginas de ancho y seleccione automtico en Alto. Encabezado y pie de pgina


17

Controla contenidos que aparecern en todas las pginas. Con Personalizar encabezado aparecer un cuadro de dilogo que permite modificar de forma separada las secciones izquierda, central y derecha. Para el pie de pgina, Las opciones son iguales. Haga clic en el extremo inferior izquierdo del grupo configurar pgina Imprimir Para iniciar la impresin, Ctrl + P. en el cuadro de dilogo de la Ilustracin 7:

Ilustracin 7: cuadro de dilogo de imprimir

Impresora seleccione cualquiera de las impresoras configuradas en la computadora Intervalo de pginas permite especificar las pginas del documento que se desea imprimir. Si hace clic en Pginas, escriba el rango de pginas que desea imprimir. Imprimir Especifica que parte se va a imprimir. Si se desea imprimir solo un rea especfica se debe haber seleccionado previamente, marque la casilla Seleccin. Esta opcin tiene similitud con reas de impresin pero de carcter temporal. Omitir reas de impresin no tendr en cuenta las celdas marcadas como rea de impresin, es decir imprimir todo. Copias, escriba aqu el nmero de copias deseadas.


18

SECCIN 2.: LISTAS: ESTRUCTURA FUNDAMENTAL DE UNA BASE DE DATOS Una base de datos es una coleccin de informacin que de alguna manera posee un orden. Conceptos sobre bases de datos Campos, Son los ttulos o nombre de las columnas, se definen la estructura de base de datos. Cada columna o campo debe contener datos del mismo tipo, por ejemplo fechas, nombres, precios o porcentajes. Registros, Son los datos en s, a cada uno le corresponde una fila, para cada dato habr un registro. Crear una base de datos en Excel Bastar con escribir los nombres de los campos en la fila superior e iniciar la digitacin de los registros en las filas inferiores. Un ejemplo puede observarse en la Hoja 1. Caractersticas Ocupa 1 ms columnas y hasta 16.384 filas. Primera fila: ttulo de fila que contiene una o ms etiquetas de columnas. Todas las filas en la lista debajo del ttulo de fila contienen registros.

Ficha de datos Utilizamos la opcin ficha de datos: 1. Haga clic en el botn de Microsoft Office 2. Busque el men Personalizar. 3. Escoja todos los comandos. 4. Busque en la lista el comando Formulario y agrguelo a la barra de Inicio rpido. Con esta utilidad podremos ingresar la data de manera ms rpida y eficiente, adems de realizar bsquedas por criterios establecidos. Ordenar datos VIDEO: http://www.youtube.com/watch?v=ijwnnk0BtK8&feature=relatedElaborado por: Prof. Sonia Albarracn

y, a continuacin, haga clic en Opciones de Excel.

19

Siga estos pasos para ordenar una base de datos por un solo campo en la Hoja 2.: 1. Ubquese DENTRO los datos haciendo clic en una celda, NO seleccione celdas. 2. Clic en la ficha Inicio, grupo Modificar, botn Ordenar y filtrar. 3. Con la opcin A ? Z o Z ? A se ordenar ascendente o descendente del campo de la celda activa. Para ordenar por varios campos o criterios 1. Ubquese DENTRO los datos haciendo clic en una celda, SIN seleccionar ms de una celda en la Hoja 3. 2. Clic en la ficha Inicio, grupo Modificar, botn Ordenar y filtrar, opcin Orden personalizado. Tendr un cuadro de dilogo como el de la Ilustracin 8

Ilustracin 8: Cuadro de dilogo de ordenacin

3. Verifique que la opcin Mis datos tienen encabezados est marcada (si sigui la recomendacin anterior Crear una base de datos en Excel). 4. En Columna seleccione el campo por el que desea ordenar como primer criterio. 5. En Ordenar segn selecciones Valores. 6. En Criterio de ordenacin podr seleccionar A a Z si es campo es de texto, De mayor a menor o viceversa si el campo contiene nmeros y De ms antiguos a ms recientes o viceversa si el campo contiene fechas. 7. Cuando alguno de los datos del campo utilizado en Columna, Ordenar por se repite, por ejemplo, varias personas con el mismo nombre, clic en el botn Agregar nivel y en Luego por podrElaborado por: Prof. Sonia Albarracn

20

aplicar un segundo criterio, por ejemplo Apellido. Si an as hay campos repetidos, por ejemplo, varias personas con el mismo nombre y apellido, puede repetir este paso para aplicar ms criterios, por ejemplo segundo apellido. Si no se repiten datos y aplica varios criterios Luego por, solo tendr efecto el primero, los dems no afectarn los datos, por ejemplo en Ordenar por cdula har que los luego por no tengan efecto ya que los nmeros de cdula no se repiten. En el punto 5 podr seleccionar formatos como color de la celda o color de la fuente. Autollenar y listas personalizadas Con clic sostenido sen la parte inferior izquierda de la celda y se extiende hacia abajo o la derecha. Si se utilizan nmeros Excel seguir la secuencia Al hacer una autollenado con celdas que comiencen o terminen con nmeros, se har un incremento automtico de estos nmeros. Lista personalizada Trabajar con listas de texto personalizadas http://excelforo.blogspot.com/2009/09/creacion-de-una-lista-personalizada-en_08.html 1. Haga clic en el botn de Microsoft Office y, a continuacin, haga clic en Opciones de Excel.

2. Haga clic en la categora Popular y, a continuacin, en Opciones principales para trabajar con Excel, haga clic en Modificar listas personalizadas. 3. En el cuadro Listas personalizadas, haga clic en NUEVA LISTA y, a continuacin, escriba las entradas en el cuadro Entradas de lista, empezando desde la primera. Presione ENTRAR despus de cada entrada. 4. Cuando la lista est completa, haga clic en Agregar. Los elementos de la lista que ha seleccionado se agregan al cuadro Listas personalizadas. 5. Haga clic dos veces en Aceptar. Para eliminar, repita los pasos 1 al 3. En el cuadro de la izquierda Listas personalizadas clic en la lista que se desea eliminar y Clic en el botn Eliminar Referencias relativas y absolutas VIDEO: http://www.youtube.com/watch?v=DTJIYa9nI4EElaborado por: Prof. Sonia Albarracn

21

http://www.youtube.com/watch?v=tYSWbK64XT4 http://www.youtube.com/watch?v=vbS9AjE2DjA&feature=related Referencias absolutas y F4 En el momento de crear la frmula presionar la tecla F4 al sealar la celda que deseamos no cambie, esto antepone el signo $ antes de la referencia de la celda. Vamos a trabajar con los distintos tipos de ordenacin, Referencia absoluta: Cuando no va a cambiar ni la columna ni la fila (como $G$2). Referencia relativa: Cuando van a cambiar columnas o filas (como E2) Filtro automtico El filtro oculta las filas que no cumplen con una condicin que se especifique. Siga estos pasos activar los filtros. 1. Ubquese dentro de los datos haciendo clic en una celda, NO seleccione celdas 2. Clic en la ficha Inicio, grupo Modificar, botn Ordenar y filtrar opcin Filtro. La tabla se ver como la de la Ilustracin 9

Ilustracin 9: Filtro en Excel 2007

Los mens desplegables a la derecha los nombres de cada campo indican que est activada la opcin de filtro. Cuando hay una flecha en el men desplegable indica que se orden por ese campo. El cono del embudo aparecer cuando se ha aplicado un filtro por ese campo. Siga estos pasos aplicar filtros que coincidan con valores de las celdas. 1. Active filtros. 2. Haga clic en el men desplegable del campo que desee filtrar. 3. Desmarque la casilla Seleccionar todo.Elaborado por: Prof. Sonia Albarracn

22

4. Marque la(s) casilla(s) que coincidan con los datos que desea filtrar. 5. Clic en aceptar. Para quitar filtros de un campo haga clic el men desplegable del campo y clic en Borra filtro de nombre del campo. Siga estos pasos para aplicar filtros que cumplan con determinadas condiciones de las celdas. 1. Active filtros 2. Haga clic en el men desplegable del campo que dese filtrar. 3. Tendr alguna de estas opciones: Filtro de nmero, Filtro de fecha o Filtro de texto segn el tipo de datos. 4. Seleccione la opcin segn el criterio que desee aplicar. Podr repetir los dos pasos anteriores y aplicar varios filtros por varios campos. Para quitar todos los filtros aplicados, clic en la ficha Inicio, grupo Modificar, botn Ordenar y filtrar opcin Borrar. Para desactivar los filtros, clic en la ficha Inicio, grupo Modificar, botn Ordenar y filtrar opcin Filtro. Filtro avanzado http://www.youtube.com/watch?v=yOhUMj8GwoM&feature=related Creacin automtica de esquemas http://www.youtube.com/watch?v=UvWPUUh6UQM La mejor opcin para crear esquemas es que lo haga Excel automticamente, puesto que tarda mucho menos tiempo que hacindolo manualmente. En la Hoja 4. tiene datos para practicar esta utilidad. Existen unos requisitos previos para que Excel 2007 pueda crear automticamente el esquema: Las filas sumario deben estar por encima o por debajo de los datos, nunca entremezclados. Las columnas sumario deben estar a la derecha o a la izquierda de los datos, nunca entremezclados.


23

Si la disposicin de los datos no se corresponde con estas caractersticas nos veremos obligados a definir el esquema manualmente. En la Ilustracin 10 podemos ver el ejemplo de datos bien estructurados:

Ilustracin 10: datos bien estructurados para un esquema

Podemos ver que existen subtotales en las celdas B6, C6, D6, B11, C11, D11, B20, C20, D20 y la columna E est llena de subtotales correspondientes a las filas donde se encuentran. En las celdas B21, C21, D21 y E21 son los totales de los subtotales. En este ejemplo podemos hacer un esquema tanto de filas como de columnas, puesto que se ha organizado los subtotales de cada comunidad autnoma (filas) y se ha calculado los subtotales de cada trimestre (columnas). Por tanto, como ya tenemos los datos vamos a realizar el esquema. Para ello nos situamos sobre una celda cualquiera y seleccionamos la opcin Autoesquema del men Agrupar que se encuentra en la pestaa Datos, como se observa en la Ilustracin 11


24

Ilustracin 11: Opcin Autoesquema

Automticamente, Excel nos genera los niveles del esquema como podemos ver a continuacin en la Ilustracin 12:

Ilustracin 12: Esquema

En la imagen anterior podemos ver que ahora aparecen unas lneas en la zona izquierda de las celdas y otra lnea sobre las columnas de la tabla. Adems, vemos en la esquina superior izquierda unos nmeros que nos indican cuntos niveles tiene el esquema. Por columnas podemos ver que existen dos niveles: La tabla desplegada por completo


25

y la tabla con los datos trimestrales.

Por filas tenemos tres niveles: La tabla desplegada completamente Por autonomas y solo por Espaa. y

Para comprimir y expander el esquema slo tenemos que hacer clic en los smbolos de cada nivel. Por ejemplo, en la tabla del ejemplo, si hacemos clic sobre el

encima del primer trimestre,

comprimiremos ese trimestre, si hacemos lo mismo sobre los niveles de cada autonoma, el esquema nos quedara como podemos ver en la.

Ilustracin 13: Comprimiendo el esquema

Uso de Subtotales http://www.youtube.com/watch?v=AfIfj7zK7sA Microsoft Excel puede calcular automticamente valores de subtotales y de totales generales en una lista (lista: serie de filas que contienen datos relacionados o serie de filas que designa para que funcionen como hojas de datos mediante el comando Crear lista.). Cuando se insertan subtotales automticos, Excel esquematiza la lista para que se puedan mostrar y ocultar las filas de detalle de cada subtotal. Para insertar subtotales, primero se ordena la lista para agrupar las filas cuyos subtotales se desea calcular. Despus pueden calcularse los subtotales de cualquier columna que contenga nmeros.


26

Si los datos no estn organizados en una lista, o si slo necesita un total, puede utilizar Autosuma Matrices http://www.youtube.com/v/7xN0qXR-64w El concepto de Matriz viene de los lenguajes de programacin y de la necesidad de trabajar con varios elementos de forma rpida y cmoda. Podramos decir que una matriz es una serie de elementos formando filas (matriz bi-dimensional) o filas y columnas (matriz tri-dimensional). La siguiente tabla representa una matriz bidimensional: 1 2 3 4 5 ...ahora una matriz tridimensional: 1,1 1,2 1,3 1,4 1,5 2,1 2,2 2,3 2,4 2,5 3,1 3,2 3,3 3,4 3,4 Observe, por ejemplo, el nombre del elemento 3,4 que significa que est en la posicin de fila 3, columna 4. En Excel, podemos tener un grupo de celdas en forma de matriz y aplicar una frmula determinada en ellas de forma que tendremos un ahorro del tiempo de escritura de frmulas. En Excel, las frmulas que hacen referencia a matrices se encierran entre llaves {}. Hay que tener en cuenta al trabajar con matrices lo siguiente: No se puede cambiar el contenido de las celdas que componen la matriz No se puede eliminar o mover celdas que componen la matriz No se puede insertar nuevas celdas en el rango que compone la matriz Una frmula matricial se tiene que aceptar utilizando CTRL+MYSC+Intro y Excel colocar las llaves automticamente. 1. Se tiene ciertas informacin de la siguiente forma: la combinacin de teclas en lugar de subtotales automticos.

Ilustracin 14: Uso de frmulas matriciales

2. Site el cursor en la celda B8 e introduzca la frmula: =SUMA(B3:E3*B4:E4) 3. Acepte la frmula usando la combinacin de teclas adecuada. 4. Observe cmo hemos obtenido el mismo resultado tan slo con introducir una frmula.Elaborado por: Prof. Sonia Albarracn

27

SECCIN 3.: TABLA DE DATOS DE UNA Y DOS VARIABLES Ms informacin: http://excelforo.blogspot.com/2009/09/ejemplo-de-tabla-de-datos-excel-2007.html http://excelforo.blogspot.com/2009/07/ejemplo-de-tabla-o-tabla-de-datos-i.html http://www.mailxmail.com/curso-excel-xp-segunda-parte/tablas-datos-dos-variables Existe un mtodo para buscar valores deseados llamado tablas de variables. Existen dos tipos de tablas: Tabla de una variable: utilizada cuando se quiere comprobar cmo afecta un valor determinado a una o varias frmulas. Tabla de dos variables: para comprobar cmo afectan dos valores a una frmula. A continuacin modificaremos la tabla de amortizacin del prstamo de forma que Excel calcule varios intereses y varios aos al mismo tiempo. Para crear una tabla hay que tener en cuenta: La celda que contiene la frmula deber ocupar el vrtice superior izquierdo del rango que contendr el resultado de los clculos. Los diferentes valores de una de las variables debern ser introducidos en una columna, y los valores de la otra variable en una fila, de forma que los valores queden a la derecha y debajo de la frmula. El resultado obtenido es una matriz, y deber ser tratada como tal. El ejemplo que se utiliza con mayor frecuencia es el clculo de los pagos de una hipoteca. Y si deseamos realizar una anlisis donde dos variables cambien? Tarea: Estudio de caso: Cmo adecuar tablas de datos a las necesidades de su negocio De manera tpica, las tablas de datos se emplean para estimar los efectos de cambios en variables rutinarias de negocios, como tasas de inters, montos de gasto de capital, pronsticos de acciones del mercado y aspectos por el estilo. Sin embargo, las tablas de datos pueden ser tan exticas como sea necesario, como se demostr en el caso de una necesidad agropecuaria ocurrida en el oeste de Pennsilvania a principios de los noventa. El giro de la compaa, que se dedicaba a la crianza de animales exticos, como avestruces, monos, serpientes, requera de algunos mtodos de pronstico poco usuales. Las tablas de datos aparecieron en el horizonte cuando la firma concurs por un contrato para criar chinchillas, cuya entrega se estableca en un tiempo aproximado de dos aos. Las preguntas eran: Qu cantidad de material de construccin especializado requera la firma para alojar a su producto hasta la fecha de entrega? Cuntas chinchillas habra para entonces? La respuesta, claro, dependa en esencia de dos variables: las tasas de reproduccin y mortalidad de las chinchillas. Una tabla de datos de dos variables proporcion un rango de posibles resultados. De inmediato, la firma adquiri suficientes materiales de construccin para alojar el nmero mnimo posible de chinchillas y al mismo tiempo procur opciones para realizar compras posteriores, hasta llegar al mximo nmero posible de chinchillas. Esto tambin fue algo bueno porque las chinchillas resultaron ser una banda resistente (y prolfica), por lo que la poblacin final casi alcanz el pronstico del nmero mximo de la tabla de datos de dos variables. La firma de crianza se protegi de los aumentos en el precio de materiales de construccin, el cual tuvo un incremento sustancial en el perodo. El resultado fue tambin un final feliz para las chinchillas. El contrato de la firma se suscribi con una franquicia de tienda para mascotas, y noElaborado por: Prof. Sonia Albarracn

28

con un peletero; cada una de las acariciables criaturas encontr un hogar feliz. (Tomado de Administracin de datos con Excel. Carlberg, Conaster, Devenshire) Encuentre usted un caso en donde la utilizacin de una tabla de datos de dos variables permita la resolucin de una situacin problemtica. Buenos ejemplos de uso de tablas de dos variables. Recalcular las tablas de datos Existe un problema cuando hay demasiadas tablas, ya que pueden requerir recursos sustanciales para recalcular lo que puede hacer lento el desempeo de la hoja de trabajo. En Excel se tiene una forma para desactivar los reclculos automticos de la tabla de datos, al tiempo que se mantienen los reclculos automticos para el resto del libro de trabajo (y otros libros de trabajo abiertos), esta opcin es la de Calcule Automatic Except Tables (Automticamente excepto tablas).

Despus de seleccionar esta opcin la tabla ya no refleja de manera automtica los valores actuales de las celdas en otros sitios de la hoja de trabajo. Para actualizar la tabla de datos en una hoja de trabajo, se debe oprimir la tecla F9 o seleccionar el botn Clculo automtico en la ficha Calculation (calcular) del cuadro de dilogo anterior. SECCIN 4.: OBTENIENDO INFORMACIN DE LAS LISTAS Adems de extraer registros seleccionados desde una lista, puede crear frmulas que regresen informacin especfica desde la lista. Trabajaremos con dos tipos de informacin: informacin estadstica e informacin de bsqueda. En cuanto a las frmulas estadsticas; se encuentran: (UTILIZAMOS LAS FRMULAS DE BASE DE DATOS). Informacin en: http://www.aulaclic.es/excel2007/a_15_6_1.htm http://excelforo.blogspot.com/2009/07/funciones-para-bases-de-datos.html Suma Promedio Desviacin estndar de la muestra Desviacin estndar de la poblacin Varianza de la muestra Varianza de la poblacinElaborado por: Prof. Sonia Albarracn

29

Contar las celdas que contienen nmeros Contar las celdas que no estn vacas Multiplicar entre s los datos Funcin: BUSCARV http://www.youtube.com/watch?v=NqcHME0UMU4 http://www.youtube.com/watch?v=1BqV_uVl5wQ http://excelforo.blogspot.com/2009/06/un-ejemplo-de-buscarv-sobre-dos-tablas.html http://www.youtube.com/watch?v=8zI8X6q6FNM&hl=es

Esta funcin nos permite buscar un valor en una primera columna de una matriz, una vez localizado nos muestra dentro de la misma fila el valor que contiene la columna que deseamos obtener. Si la matriz ordenada coloque el nmero 1 como 4to argumento (VERDADERO, de lo contrario 0 (FALSO). Sintaxis: BUSCARV(valor_buscado,matriz_buscar_en,indicador_columnas,[ordenado]) Es decir: BUSCARV(valor que se desea buscar en la matriz,matriz de datos donde buscar los valores,columna donde se encuentra el dato,tipo de ordenamiento de la matriz)

Ilustracin 15: Funcin BUSCARV

En el ejemplo de la Ilustracin 15 se indica que busque el cdigo X-3 en la matriz que se encuentra justo debajo y que devuelva la descripcin (columna 2) de ese cdigo. Un ejercicio para practicar esta funcin se encuentra en las Hojas 1 y 2 de la gua.


30

Funcin : BUSCARH VIDEO: http://www.youtube.com/watch?v=8WYMnrqNwEU&feature=related Esta funcin realiza lo mismo que la funcin anterior, pero con la diferencia que busca los valores en la primera fila de la matriz, de forma horizontal y nos devuelve un valor que est dentro de la misma columna del valor encontrado. Sintaxis: BUSCARH(valor_buscado,matriz_buscar_en,indicador_filas,[ordenado[) Es decir: BUSCARH(valor que se desea buscar en la matriz,matriz de datos donde buscar los valores,fila donde se encuentra el dato,tipo de ordenamiento de la matriz)

Ilustracin 16: Funcin BUSCARH

En el ejemplo de la Ilustracin 16 se indica que busque el cdigo X-3 en la matriz que se encuentra justo debajo y que devuelva la descripcin (fila 2) de ese cdigo. Funcin COINCIDIR Ms informacin en: http://lqrexceltotal.blogspot.com/2007/12/combinar-buscarv-y-coincidir.html Sintaxis: Coincidir(valor_buscado,matriz_buscada,tipo_de_coincidencia) Busca un valor en el rango indicado y devuelve la posicin relativa del elemento dentro de la matriz (rango), siguiendo un tipo de coincidencia. El rango contendr celdas de filas por ejemplo: A30:D30 (valores de la fila 30), o celdas de columnas por ejemplo: A30:A38, (valores de la columna A).Elaborado por: Prof. Sonia Albarracn

31

Utilizaremos COINCIDIR en lugar de las funciones BUSCAR cuando necesitemos conocer la posicin de un elemento en un rango en lugar del elemento en s. Los argumentos de la funcin son los siguientes: Valor_buscado es el valor que se desea encontrar en la tabla o matriz. Puede ser un nmero,un texto o una referencia. Matriz_buscada es una matriz fila o columna formada por el rango de celdas donde buscar los valores Tipo_de_coincidencia puede ser -1, 0, 1 y especifica cmo hace coincidir Microsoft Excel el valor_buscado con los valores del rango. Ver tabla. Si se omite tipo_de_coincidencia, se supondr que es 1. TIPO DE COINCIDENCIA 1 0 -1 SIGNIFICADO COINCIDIR :encuentra el mayor valor que es inferior o igual al valor_buscado. Los valores contenidos en las celdas del rango deben colocarse en orden ascendente. COINCIDIR :encuentra el primer valor que es exactamente igual al valor_buscado. Los valores en el rango pueden estar en cualquier orden COINCIDIR :encuentra el menor valor que es mayor o igual al valor_buscado. Los valores contenidos en las celdas del rango deben colorcarse en orden ascendente

Ilustracin 17: Funcin COINCIDIR


32

Ilustracin 18: Otro ejemplo de la funcin COINCIDIR

Funcin: INDICE Sintaxis: Indice(rango,nm_fila,nm_columna) Devuelve el valor del elemento que se encuentra en la posicin indicada por nm_fila y nm_columna dentro del rango. Si el rango comprende celdas de una columna se omite el argumento nm_columna y slo ponemos el argumento nm_fila, para obtener uno de los valores de la columna. Si el rango comprende celdas de una fila se omite el nm_fila y se requiere el argumento nm_columna. Utilizaremos ambos argumentos, nm_fila y nm_columna cuando el rango comprende filas y columnas entonces INDICE devuelve el valor contenido en la celda de interseccin de los argumentos nm_fila y nm_columna Los argumentos nm_fila y nm_columna deben hacer referencia a una celda contenida en el rango; de lo contrario, INDICE devuelve el error #REF! En la Ilustracin 19, si ponemos =INDICE(A3:C8,1,1) en la celda A10, la funcin devuelve Pedro, es decir el valor contenido A10, fila 1, columna 1, del rango. Si ponemos =INDICE(A3:C8,3,3), la funcin devuelve 15, es decir el valor contenido en la celda C5, fila 3, columna del rango. Si ponemos =INDICE(A3:C8,3), la funcin devuelva Carlos, es decir el valor contenido en la fila 3 y la columna A, es decir A5. Un ejercicio para practicar esta funcin se encuentra en la Hoja 6 de la gua.


33

Ilustracin 19: Funcin INDICE

Ahora observe la Ilustracin 20

Ilustracin 20: Otro ejemplo de la funcin INDICE

Y ver otro ejemplo: en la obtenemos Martes y Lunes con la ayuda de la funcin INDICE con solo aplicar la frmula, seleccionando la tabla e indicando el nmero de fila de la tabla. Martes se encuentra en la tercera fila y lunes en la segunda. Funcin: DESREF http://excelforo.blogspot.com/2009/07/otros-usos-de-desref.html

Funcin: BUSCAR Devuelve un valor procedente de un rango de una fila o columna o de una matriz (matriz: utilizada para crear frmulas sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se organizan en filas y columnas. Un rango de matriz comparte una frmula comn; una constante de matriz es un grupo de constantes utilizadas como un argumento.). La funcin BUSCAR tiene dos formas de sintaxis: vectorial y matricial. La forma vectorial de BUSCAR busca en un rango de una fila o de una columna un valor (vector) y devuelve un valor desde la misma posicin en un segundo rango de una fila o de una columna. La forma matricial de BUSCAR busca el valor especificado en la


34

primera fila o en la primera columna de la matriz y devuelve un valor desde la misma posicin en la ltima fila o columna de la matriz. Adems utilizamos la funcin DESREF, cuyos resultados pueden ser obtenidos como una matriz. Deseamos calcular la inflacin acumulada al final del perodo presentado en la ilustracin. Puesto que la inflacin se debe tratar como un inters compuesto, sera incorrecto afirmar que la inflacin acumulada para este perodo es de: 6.5%+8.3%+7.6%+5.3%+8.7%= 36.4% 6.5%enero

8.3%febrero

7.6%marzo

5.3%abril

8.7%mayo

El clculo de la inflacin a finales de febrero es: 1.065*1.083= 1.153 1 = 0.153 = 15.3% En marzo: 1.065*1.083*1.076= 1.241-1= 0.241= 24.1%, y as sucesivamente. podra usted explicar la funcin anidada que posee la frmula DESREF que relaciona la Inflacin y la Inflacin Acumulada? Utilizacin de los botones de control Ms informacin: http://5incoaprendices.blogspot.com/2008/08/donde-estn-los-controles-de-

formulario.html http://5incoaprendices.blogspot.com/2008/07/utilizar-controles-de-formularios-en.html

La utilizacin de los controles en forma de botn agiliza el manejo de las hojas de clculo. Antes que nada debemos activar la barra de botones (si no lo est ya). Los controles de formularios y activeX en Excel 2007 estn bajo la ficha Programador. Si no la tenemos activada Excel no muestra la ficha Programador, la cual debera estar a la derecha de la ficha Vista. Cuando no est activada, nuestra barra de fichas se muestra de la siguiente manera.

Ilustracin 21: Vista cuando no est encendida la ficha Programador

Para activarla hacemos clic en el Botn Office

Ilustracin 22: Botn Office (mariposita)Elaborado por: Prof. Sonia Albarracn

35

Hacemos clic en Opciones de Excel

Ilustracin 23: Para llegar a las opciones de Excel

Nos aseguramos de que est seleccionada la categora Ms frecuentes A la derecha, marcamos la opcin Mostrar ficha Programador en la cinta de opciones

Ilustracin 24: Para activar la ficha Programador

Hacemos clic en Aceptar La ficha Programador aparecer a la derecha de la ficha Vista Hacemos clic en la ficha Programador

Ilustracin 25: Ficha Programador

Bajo el panel Controles encontraremos la opcin Insertar Hacemos clic y all podremos acceder a ambos tipos de controles


36

Ilustracin 26: Controles

Vamos a disear una hoja de clculo de prstamo para un carro. Esta hoja sera vlida y podra calcular los pagos peridicos mensuales. Tan slo tendramos que introducir o variar las cantidades del precio, adelanto, inters o aos. El problema viene cuando en esta misma hoja podemos: Introducir cantidades exorbitantes como 1.500.000.000.000.000 Borrar sin querer alguna celda que contenga frmulas Introducir palabras como "Perro" en celdas numricas Otras paranoias que se nos ocurran Lo que vamos a hacer es crear la misma hoja, pero de una forma ms "amigable". La hoja ser ms atractiva a la vista, ms cmoda de manejar, y adems no nos permitir introducir barbaridades como las anteriormente expuestas. Para ello utilizaremos los botones de control. En el rango de celdas K3:L8, hemos colocado una lista de precios de vehculos. Lo hicimos alejados de la informacin original para que no nos moleste; adems podemos ocultarla. Vamos a crearle nombres a las celdas donde se encuentran los precios de los vehculos: 1. Seleccione el rango entero (desde K3 hasta L8) 2. Acceda al men Formulas/ Definir nombre/Crear desde la seleccin. 3. Acepte el cuadro de dilogo. Con esto le damos el nombre Vehculo a la lista de vehculos y el de Precio a la lista de precios. Estos nombres nos servirn ms adelante para incluirlos en frmulas, de forma que no utilicemos rangos como K4:K8, sino el nombre del mismo (Vehculo o Precio). Vamos ahora a crear una barra deslizable que nos servir para escoger un vehculo de la lista. 1. Coloque un ttulo en D3: Vehculo 2. Pulse un click en el botn (Cuadro combinado)

3. Trace un rectngulo desde la celda E3 hasta la celda F3 4. Pulse doble Click (manteniendo Control pulsada) sobre el cuadro que acabamos de crear y rellene el cuadro de dilogo que aparece con las siguientes opciones: Rango de entrada: Vehculo (K4:K8)Elaborado por: Prof. Sonia Albarracn

37

Vincular con la celda: H4 Lneas de unin verticales: 8. Especifica el nmero de lneas que aparecer en la lista desplegable 5. Observe ms o menos el resultado hasta ahora:

Ilustracin 27: Utilizacin de los botones de control

Qu hemos hecho? En la opcin Rango de entrada le estamos diciendo a este cuadro de dilogo que "mire" en el rango que hemos definido como Vehculo, es decir: K4:K8. De esta forma, cuando abramos esta lista que estamos creando y escojamos un vehculo, aparecer un nmero en la celda H4. Este nmero ser la posicin en la lista que se encuentra el coche que hayamos escogido. Por ejemplo, si desplegamos la lista y escogemos el Mitsubishi, aparecer en la celda H4 el nmero 3. Esta celda servir como celda de control para hacer otro clculo ms adelante. De igual forma, si escribiramos un nmero en la celda H4, el nombre del vehculo aparecera en la lista desplegable. Recuperacin del precio de la lista 1. Seleccione la celda B3 y escriba: =INDICE(Precio;H4) Observe que en la celda aparece el precio del vehculo escogido en la lista desplegable. Esto es gracias a la funcin =INDICE. Esta funcin busca el nmero que haya en la celda H4 en el rango Precio y nos devuelve el contenido de ese mismo rango. De esta forma slo encontraremos vehculos de una lista definida con unos precios fijos. As no hay posibles equivocaciones. Limitacin de la reduccin para validar valores Por desgracia an podemos introducir un porcentaje inadecuado para la inicial a pagar. 2. Coloque un ttulo en D6: Inicial 3. Pulse un click en el botn (Control de nmero)

4. Trace un rectngulo desde la celda E6 hasta la celda E7 5. Pulse doble Click (manteniendo Control pulsada) sobre el cuadro que acabamos de crear y rellene el cuadro de dilogo que aparece con las siguientes opciones: Valor actual: (muestra el valor que tiene actualmente esta celda) Valor mnimo: 0Elaborado por: Prof. Sonia Albarracn

38

Valor mximo: 20 (se supone una inicial no mayor al 30%) Incremento: 1 Vincular con la celda: H5 Propiedades de la barra de desplazamiento Valor actual: La posicin relativa del cuadro de desplazamiento dentro de la barra de desplazamiento. Valor mnimo: La posicin del cuadro de desplazamiento ms prxima a la parte superior de una barra de desplazamiento vertical o al extremo izquierdo de una barra de desplazamiento horizontal. Valor mximo: La posicin del cuadro de desplazamiento ms lejana a la parte superior de una barra de desplazamiento vertical o al extremo derecho de una barra de desplazamiento horizontal. Incremento: El tamao del espacio que se desplazar el cuadro de desplazamiento cuando se haga clic en la flecha situada en cualquier extremo de la barra de desplazamiento. Cambio de pgina: El tamao del espacio que se desplazar el cuadro de desplazamiento cuando se haga clic entre el cuadro de desplazamiento y una de las flechas de desplazamiento. Vincular con la celda: Devuelve la posicin actual del cuadro de desplazamiento. Este nmero puede utilizarse en una frmula o macro para responder a la posicin del cuadro de desplazamiento. Sombreado 3D: Muestra la barra de sombreado con un efecto de sombreado tridimensional. 6. Acepte el cuadro y pulse Esc para quitar la seleccin del control y poder utilizarlo 7. Pulse sobre las flechas del control recin creado y observe cmo cambia el valor de la celda H5 8. Sitese en la celda B4 y escriba: =H5/100 Esto convierte en porcentaje el valor de H5. 9. Cambie el formato de la celda B4 en porcentaje El control se incrementa slo con nmeros enteros pero es preciso que la inicial se introduzca como un porcentaje. La divisin entre 100 de la celda H5 permite que el control use nmeros enteros y a nosotros nos permite especificar la reduccin como un porcentaje. Creacin de un control que incremente de cinco en cinco Si queremos introducir reducciones por ejemplo del 80%, deberamos ir pulsando la flecha arriba bastantes veces. 1. Acceda a las propiedades del control recin creado 2. Escriba 100 en el cuadro Valor mximo, un 5 en el cuadro Incremento, y acepte. 3. Pulse Esc para desactivar el control Observe que ahora la celda B4 va cambiando de 5 en 5. En cuanto al monto del prstamo, colquese en la celda B5 y escriba en ella: =B3*(1-B4) Limitacin del rdito para validar sus valoresElaborado por: Prof. Sonia Albarracn

39

Vamos a crear una barra de desplazamiento que nos permita presentar los distintos intereses que afectan al prstamo. Nos van a interesar porcentajes que vayan variando de cuarto en cuarto y dentro de un rango del 0% al 20%. Vamos a usar una barra de desplazamiento en vez de un control, ya que posibilitan porcentajes decimales. 1. Coloque un ttulo en D9: Inters 2. Cree una Barra de desplazamiento ms o menos as:

Ilustracin 28: Barra de desplazamiento

3. Acceda a sus propiedades y modifquelas de la siguiente forma: Valor mnimo: 0 Valor mximo: 2000 Incremento: 25 Vincular con celda: H6 Cambio de pgina: 10. El tamao del espacio que se desplazar el cuadro de desplazamiento cuando se haga clic entre el cuadro de desplazamiento y una de las flechas de desplazamiento. 4. Acepte el cuadro de dilogo y pulse Esc para quitar la seleccin 5. Para presentar los intereses seleccione la celda B6 y escriba: =H6/1000 6. Cambie el formato de la celda B6 en porcentaje 7. Con el botn Aumentar decimales , aumntela en 2 decimales.

Vamos a crear un control para los aos: 1. Coloque un ttulo en D11: Aos 2. Cree un nuevo Control numrico y colquelo ms o menos as:


40

Ilustracin 29: Control numrico

1. Acceda a sus propiedades y cmbialas de la siguiente forma: Valor mnimo: 1 Valor mximo: 6 Incremento: 1 Vincular con la celda: H7 2. Acepte el cuadro de dilogo y pulse Esc para quitar la seleccin 3. Seleccione la celda B4 y escriba en ella: =H7. 4. Pruebe este ltimo control y verifique que los aos cambian de uno en uno. En cuanto al pago, inserte la funcin financiera PAGO y coloque los valores para realizar pagos mensuales. Muy bien, el modelo ya est completo. Ya podemos experimentar con varios modelos sin tener que preocuparnos de que podamos escribir entradas que no sean vlidas. De hecho, sin tener que escribir nada en el modelo. Una de las ventajas de una interfaz grfica de usuario es la posibilidad de reducir las opciones para validar valores. Vamos ahora a darle un ltimo toque: Seleccione las columnas desde la G hasta la J y ocltelas. El aspecto final ser el siguiente:

Ilustracin 30: Resultado de la utilizacin de botones de controlElaborado por: Prof. Sonia Albarracn

41

Sin embargo, an existe la posibilidad que alguien coloque valores errados en las celdas que se encuentran en la columna B. Para evitar que esto suceda, vamos a proteger esta celdas, para luego proteger esta hoja. LAS DISTINTAS FORMAS DE PROTECCIN SERN ANALIZADAS MS ADELANTE.

Administracin de datos con la funcin transponer y las opciones de pegado especial http://www.youtube.com/watch?v=t8F7EsY_0z8 Se pueden transponer los datos de una base y tomar decisin sobre el mtodo a utilizar, tomando en consideracin si se desea vincular o n la informacin transpuesta con respecto a la informacin original. Cuando necesitemos pegar una informacin sobre celdas ya rellenas y no deseamos que stas sean reemplazadas, utilizamos la opcin Saltar Blancos de Pegado especial, tal como lo indica la Hoja 10 Protegiendo informacin Proteccin de archivos: Si se realiza la operacin ARCHIVO GUARDAR COMO, se encontrarn distintas opciones para guardar, las cuales le permiten distintos niveles de seguridad: Contrasea de proteccin: La barrera ms exhaustiva que usted puede establecer para un libro de trabajo es la contrasea de proteccin. Si emplea esta contrasea, nadie que no la conozca podr abrir el libro de trabajo. Dentro de Excel no hay manera de burlar la contrasea. Por tanto, cuando utilice esta opcin, est seguro de que podr recordar la contrasea; si la olvida, usted mismo se habr obstruido el paso. (si desea hacer referencia en una frmula a una celda que se encuentra en un libro protegido, es necesario introducir la contrasea) Contrasea contra escritura: Proporciona mejor acceso a un libro de trabajo que la contrasea anterior. Cuando se abre un libro con esta proteccin, se le solicita la contrasea. Si el usuario no conoce la contrasea, deber abrirlo como Slo lectura y no podr guardar el libro con el nombre original. El propsito de esta contrasea es permitir que otros usuarios abran y visualicen el contenido de su libro de trabajo, pero evitar que guarden cualquier cambio que hayan hecho, con el uso del nombre del archivo actual. Se recomienda slo lectura: No se requiere de ninguna contrasea si usted guarda un libro de trabajo con esta casilla seleccionada, cualquier usuario que la abra ser advertido de que usted recomend que el estado sea slo de lectura. El usuario puede escoger No y el archivo podr ser guardado bajo el mismo nombre. Si escoge Si, no podr guardar el archivo con el nombre original.


42

Crear siempre una copia de seguridad: Cuando se guarda un archivo que tenga esta opcin, se crear una copia de seguridad y podremos reconocerla por su nombre: Copia de (nombre del archivo). xlk. Esto es muy conveniente, por supuesto, y proporciona algn grado de proteccin que previene de guardar cambios no deseados en un libro de trabajo. Y SI DESEAMOS UNA PROTECCIN QUE DEJE EL DETALLE DE QUIEN ENTRE SIN PERMISO? An si escoge no proteger un libro de trabajo por medio de una contrasea de proteccin o una contrasea contra escritura, puede prevenir que muchos elementos del libro de trabajo sean cambiados. Puede prevenir de manera alternativa o en adicin, que un usuario visualice ciertos elementos del libro de trabajo. Ocultar: Escoja las filas o columnas que desea ocultar, con el men contextual pida el comando OCULTAR., en REVISION PROTEGER PROTEGER HOJA, coloque la contrasea y ver como no puede ingresar al men que le permita MOSTRAR lo que ocult. Proteccin de libro y hoja de trabajo. o Comenzaremos con PROTEGER HOJA: veremos que tenemos muchas alternativas involucradas. o PROTEGER LIBRO. Las alternativas involucradas son: o Estructura: no podr. Eliminar una hoja, mover una hoja, ocultar una hoja, mostrar una hoja, renombrar una hoja, insertar una hoja. o Ventana: si se ha dividido la hoja en ventanas, a travs de VENTANA DIVIDIR, esta opcin no le permitir modificar la divisin realizada por usted en un libro determinado. Ocultar y bloquear celdas Ocultar: no permite ver la frmula que se haya escrito en la celda. Se coloca la celda o el rango que se desea ocultar, men contextual FORMATO DE CELDAS...etiqueta PROTEGER y marcar OCULTA. Luego deber protegerse la hoja como se indic anteriormente. Bloquear: permite ver la frmula que se haya escrito en la celda, mas no modificarla. El procedimiento es igual al punto anterior marcndose la opcin de BLOQUEADA. SI SE DESEA QUE NO SE VEA EL CONTENIDO NI SE PUEDA MODIFICAR, SE MARCAN LAS DOS OPCIONES. RECUERDE PROTEGER LA HOJA PARA QUE TENGAN EFECTO ESTOS COMANDOS. Opcin para ni siquiera verla, ni en la barra de frmula, ni en la celda. En el men contextual

FORMATO DE CELDAS...NMERO PERSONALIZADA y en el TIPO, se escribe ;;; Esto debe acompaarse con las opciones de ocultar y/o bloquear comentadas en los apartes anteriores. SECCIN 5.: TRABAJANDO CON TABLAS DINMICAS http://www.youtube.com/watch?v=_iOTH87tQHYElaborado por: Prof. Sonia Albarracn

43

http://www.youtube.com/watch?v=t92Sdu0nYhE&feature=related http://www.youtube.com/watch?v=YNJs5wXI2IQ&eurl http://www.youtube.com/watch?v=Z_G2rC4qurk&hl=es

Una tabla dinmica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupacin, representado como una tabla de doble entrada que nos facilita la interpretacin de dichos datos. Es dinmica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentacin de los datos, visualizando o no los datos origen, etc... Para aquellos que tengan conocimientos de Access es lo ms parecido a una consulta de referencias cruzadas, pero con ms interactividad. Para crear una tabla dinmica, Excel nos proporciona las tablas y grficos dinmicos.

Ilustracin 31: Base de datos para realizar una tabla dinmica

Supongamos que tenemos una coleccin de datos de los artculos del almacen con el nmero de referencia y el mes de compra, adems sabemos la cantidad comprada y el importe del mismo. Vamos a crear una tabla dinmica a partir de estos datos para poder examinar mejor las ventas de cada artculo en cada mes. Para ello vamos a la pestaa Insertar y hacemos clic en Tabla dinmica (tambin podemos desplegar el men haciendo clic en la flecha al pie del botn para crear un grfico dinmico).

Ilustracin 32: Insertar una tabla dinmicaElaborado por: Prof. Sonia Albarracn

44

Aparece el cuadro de dilogo de creacin de tablas dinmicas. Desde aqu podremos indicar el lugar donde se encuentran los datos que queremos analizar y el lugar donde queremos ubicarla.

Ilustracin 33: Cuadro de dilogo de tabla dinmica

En nuestro caso indicamos que vamos a seleccionar los datos de un rango de celdas y que queremos ubicarla en una hoja de clculo nueva. Podramos crear una conexin con otra aplicacin para obtener los datos desde otra fuente diferente a Excel. En el caso de seleccionar la opcin Selecciona una tabla o rango debemos seleccionar todas las celdas que vayan a participar, incluyendo las cabeceras. Pulsamos Aceptar para seguir. Se abrir un nuevo panel en la derecha de la pantalla:


45

Ilustracin 34: Para seleccionar los campos de una tabla dinmica.

Desde este panel podemos personalizar la forma en que van a verse los datos en la tabla dinmica. Con esta herramienta podramos contruir una tabla dinmica con la siguiente estructura: - Una fila para cada una de las Referencias de la tabla. - Una columna para cada uno de los Meses de la tabla. - En el resto de la tabla incluiremos el total del Importe para cada Referencia en cada Mes. Para ello simplemente tendremos que arrastrar los elementos que vemos listados a su lugar correspondiente al pie del panel. En este ejemplo deberamos arrastrar el campo REF a y finalmente el campo IMPORTE a la seccin . , el campo MES a

Tras realizar la tabla dinmica este sera el resultado obtenido.

Ilustracin 35: Resultado de una tabla dinmica.


46

Podemos ver que la estructura es la que hemos definido anteriormente, en el campo fila tenemos las referencias, en el campo columnas tenemos los meses y en el centro de la tabla las sumas de los importes. Con esta estructura es mucho ms fcil analizar los resultados. Una vez creada la tabla dinmica nos aparece la pestaa Opciones:

Ilustracin 36: Opciones de una tabla dinmica.

El panel lateral seguir pudindose utilizar, as que en cualquier momento podremos quitar un campo de un zona arrastrndolo fuera. Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados sin casi esfuerzos. Si arrastrsemos a la zona de datos los campos cantidad y total, obtendramos la siguiente tabla, ms compleja pero con ms informacin:

Ilustracin 37: Resultado de una tabla dinmica con ms campos de datos.


47

Puede que no se visualice la tabla de la misma forma, al aadir varios campos en la seccin Valores el rtulo Valores aparecer en una las secciones de rtulos, si aparece en Rtulos de columna despliegue la lista asociada a l y seleccione la opcin Mover a rtulos de fila. Eliminar una tabla dinmica. Para eliminar una tabla dinmica simplemente debemos seleccionar la tabla en su totalidad y presionar la tecla Supr. Otra caracterstica til de las tablas dinmicas es permitir filtrar los resultados y as visualizar unicamente los que nos interesen en un momento determinado. Esto se emplea sobre todo cuando el volumen de datos es importante. Los campos principales en el panel y los rtulos en la tabla estn acompaados, en su parte derecha, de una flecha indicando una lista desplegable. Por ejemplo, si pulsamos sobre la flecha del rtulo Rtulos de columna nos aparece una lista como vemos en la imagen con los distintos meses disponibles en la tabla con una casilla de verificacin en cada uno de ellos para indicar si los queremos ver o no, ms una opcin para marcar todas las opciones en este caso todos los meses. Si dejamos marcados los meses Enero y Febrero, los otros meses desaparecern de la tabla, pero no se pierden, en cualquier momento podemos visualizarlos volviendo a desplegar la lista y marcando la casilla (Seleccionar todo). Para cerrar este cuadro debemos pulsar en Aceptar o sobre Cancelar para cerrar y dejarlo como estaba. Aplicando el filtro a varios campos podemos formar condiciones de filtrado ms complejas, por ejemplo podemos seleccionar ver los artculos con referencia 1236 de Abri


48

Ilustracin 38: Filtro de campo de tabla dinmica.

Obtener promedios en una tabla dinmica Por defecto, al crear una tabla dinmica, Excel nos genera unos totales con sumatoria, puede interesarnos modificar esas frmulas por otras como pueden ser sacar el mximo o el mnimo, el promedio, etc.

Ilustracin 39: Cuadro de dilogo para configurar los campos de una tabla dinmica.

Para hacer esto debemos situarnos en cualquier celda de la zona que queremos rectificar y hacer clic con el botn derecho del ratn, nos aparece un men emergente con diferentes opciones, debemos escoger la opcin Configuracin de campo de valor... y nos aparece un cuadro de dilogo como el que vemos en la imagen. En este cuadro de dilogo podemos escoger cmo queremos hacer el resumen, mediante Suma, Cuenta, Promedio, etc. Tambin podemos abrir el cuadro de dilogo con el botn Opciones.Elaborado por: Prof. Sonia Albarracn

de la pestaa

49

Para crear una grfica de nuestra tabla dinmica deberemos hacer clic en el botn Grfico dinmico de la pestaa Opciones.

Grfico dinmico Para cambiar el formato del grfico a otro tipo de grfico que nos agrade ms o nos convenga ms segn los datos que tenemos. Al pulsar este botn se abrir el cuadro de dilogo de Insertar grfico, all deberemos escoger el grfico que ms nos convenga. Luego, la mecnica a seguir para trabajar con el grfico es la misma que utiliza para cualquier tipo de grficos.

Ilustracin 40: Resultado de un grfico basado en tabla dinmica: grfico dinmico.

Tabla dinmica: Caso de estudio: Encuesta sobre el consumo de aceite de oliva Con el fin de realizar un estudio de mercado, se aplic una encuesta a una muestra 120 personas que realizaban sus compras de vveres en la zona norte de Valencia. Realice tablas dinmicas en donde se muestre la siguiente informacin: 1. Cantidad de hombres que no consumen aceite de oliva. 2. Con respecto al total de personas entrevistadas Que porcentaje no usa aceite de oliva?


50

3. Con respecto al total de personas con edades entre 18 y 28 aos, Qu porcentaje prefiere los aceites extravrgenes? 4. Con respecto al total de personas que prefiere los aceites extravrgenes, Qu porcentaje tiene edades comprendidas entre 18 y 28 aos? 5. Promedio de litros de aceite extravirgen que consumen trimestralmente las mujeres con ingresos entre 2.500.000 y 3.000.000 Bs. Tabla dinmica: Caso de estudio: Estudio del mercado de postres La base de datos suministrada proviene de una encuesta realizada a un grupo de personas que asistieron a una degustacin de postres. En la misma se busca establecer: 1. Cantidad de raciones de postres consumidas semanalmente por las mujeres con edades comprendidas entre 15 y 20 aos. 2. Cantidad de hombres cuyas edades oscilan entre los 26 y 36 aos que consumen bienmesabe. 3. Consumo promedio de raciones de postres de personas entre 15 y 25 aos. 4. Del total de mujeres, qu porcentaje de ellas consumen helado. 5. De las personas que consumen Bienmesabe, qu porcentaje son de sexo masculino. 6. Las personas procedentes de Maracay son las mayores consumidoras de helado en esta muestra. Es correcta esta afirmacin?. Justifique a travs de una tabla dinmica en donde se compare el consumo de helado de esta regin con respecto a los otros sitios de procedencia. 7. Del total general de la muestra,cul es el porcentaje de consumo segn el sexo y el tipo de postre? Anlisis de contingencia con tablas dinmicas Es normal para los campos de control en una tabla dinmica ser variables discretas. Los ejemplos incluyen variables como regin, producto, trminos de ventas y atributos como defectuoso contra no defectuoso. Por lo contrario, los campos de datos son a menudo variables continuas de este tipo: Ingresos, utilidades y medidas de preferencia del producto. Sin embargo, hay muchas instancias donde dos o ms variables discretas de control se combinan en una tabla dinmica con el fin de describir un campo de datos discreto. Imagine que usted es un director de ventas que monitorea el xito contra la cada de los planes de ventas. Tiene un conteo del nmero de comentarios buenos y malos en cada uno de sus cuatro territorios de ventas. Qu hacer para analizar el xito de una propuesta como una funcin de territorio de ventas?Elaborado por: Prof. Sonia Albarracn

51

Para entender el anlisis de contingencia Lo primero que le ocurrir cuando confronte una situacin similar a la del problema de la tasa de xito, es que elaborar una tabla dinmica. La siguiente figura muestra informacin sobre el triunfo de un plan por territorio, adems hay una tabla que resume la informacin.

Un vistazo a los conteos simples sealados en la tabla dinmica, le dir que tal vez tenga un problema en la regin Norte y Sur. Si desea crear una tabla como sta, siga estos pasos: 1. Seleccione cualquier celda en el rango de la tabla. 2. Elija Datos, Asistente para tablas dinmicas. 3. En el Primer paso del asistente, escoja Lista o base ole datos de Microsoft Excel. 4. En el Segundo paso, acepte el rango de la hoja de trabajo predeterminada. 5. En el Tercer paso, arrastre el botn comentario dentro del rea de Columna y dentro del rea Datos. Arrastre el botn Regin dentro del rea de Fila. En el Cuarto paso, introduzca C1 como Celda inicial de la tabla dinmica y seleccione Terminar. Norte y Sur son una molestia. Estas regiones han generado propuestas que, pero parece tener pocos xitos relativos. Es ms fcil ver esto con porcentajes, as que cambie la representacin del rea de Datos de Conteo del porcentaje total, mediante estos pasos: 1. Resalte cualquier celda del rea de Datos de la tabla dinmica. 2. Escoja Datos, Campo de la tabla dinmica y haga clic en Opciones en el cuadro de dilogo Campo de la tabla dinmica. 3. En la lista descendente Mostrar datos como, escoja el porcentaje de fila y elija Aceptar. Esto convierte el conteo sin elaborar en cada celda de tabla, para obtener el porcentaje total del conteo de la fila. Los resultados se muestran en la .


52

Representar el rea de datos de la tabla dinmica como porcentaje de cada fila confirma su estimacin inicial de que Norte y Sur, son un problema. Es muy ilustrativo ver que en Norte, ms del 43% de las propuestas han sido un fracaso. Este tipo de anlisis a veces se denomina anlisis de contingencia, y el diseo de la tabla dinmica, que puede contener conteos y porcentajes, se conoce como tabla de contingencia. La pregunta que resulta es si los conteos de una variable son contingentes sobre otra. En este ejemplo, la tasa de xito aparece como contingente en la regin a la que pertenece el representante de ventas. Si usted trabajara para esta compaa como agente de ventas, preferira alejarse de esa zona. Pero en realidad llegar a esta conclusin? No es posible que las observaciones en la tabla dinmica sean un suceso aleatorio, y la regin Norte slo haya tenido un trimestre o un mes muy malo? Con seguridad es posible, y usted tendr la nocin de que hay algn grado de asociacin o contingencia entre la regin y la tasa de xito de las propuestas. Para realizar un anlisis ms exhaustivo, puede construir una tabla dinmica con ndices que le permitirn calcular las frecuencias esperadas y contrastarlas con las frecuencias observadas. Adems puede calcular el valor de la Prueba Chi para conocer la probabilidad de ocurrencia de la frecuencia observada. SECCIN 6.: CREACIN DE ESCENARIOS Y VISTAS http://excelforo.blogspot.com/search/label/Escenarios http://jldexcelsp.blogspot.com/search?q=escenario Es una forma de responder a la pregunta: Y si..., por ejemplo, y si aumentan los ingresos por ventas de mi negocio; qu le sucedera al flujo de efectivo acumulado (recordar que ste me indica si tengo efectivo sobrante o faltante) Estudimoslo a travs de un caso:Elaborado por: Prof. Sonia Albarracn

53

Manejo de escenarios para un proyecto empresarial El modelo presentado se usa la propuesta porcentaje de ventas para construir un modelo de negocio; es decir, muchas de las variables en el modelo se expresan como porcentaje de los ingresos, por las ventas de cada mes. El costo de la mercanca vendida, por ejemplo, se calcula en las celdas B13:G13, mediante la frmula: = Ingresos * Porcentaje de ventas En este modelo, el valor del porcentaje de ventas es una suposicin crtica particular. Sin importar el mes, ni la cantidad de ingresos, el porcentaje de ventas afecta la cuota de la Utilidad Bruta de la compaa, y de manera eventual su ingreso operativo. Cualquier porcentaje introducido en la celda B26 causa una reduccin en la Utilidad, de ah que B26 se considere crtico en este modelo. Por el contrario, considere los costos fijos de produccin en las celdas B18:G18. Su influencia relativa para operar ingresos es cada vez ms dbil, segn se generen ms ingresos. Sin embargo, para considerar cules son las celdas que constituyen las suposiciones ms crticas para el modelo, con seguridad incluir el porcentaje de ventas, y tender a descontar los costos fijos de produccin. En trminos de manejar las celdas cambiantes del escenario, quiz se concentre ms en el efecto de modificar el porcentaje de ventas y menos en modificar los costos fijos de produccin. Cmo le hara Usted para manejar el porcentaje de ventas? Recuerde que la celda B26 es una de las celdas cambiantes en los escenarios. Tal vez examine a diferentes proveedores de los materiales utilizados en la produccin de sus bienes terminados, y determine cunto le cobrar cada uno por una cantidad cualquiera de material que compre. Despus elaborar al menos dos escenarios: uno que incluya el costo de la cuota del distribuidor ms barato y otro con la del ms caro. Un resumen de escenario desplegar de manera conveniente las variables que hay en el ingreso operativo de su modelo basado en la eleccin del proveedor. La significativa variabilidad le sugerir que ponga mucha atencin a esa opcin; una ligera variacin le indicar que se concentre en maximizar su ingreso operativo. Quiz la responsabilidad para desarrollar este modelo no sea slo suya y la comparta con un gerente de produccin o de ventas. Los tres contribuyen a desarrollar un modelo final. El gerente de produccin realizar un escenario que especifique los valores para costos de produccin variables y fijos; el gerente de ventas crear otro que especifique el ingreso anticipado y usted aceptar la responsabilidad para las celdas cambiantes restantes. El administrador de escenarios es una opcin para combinar escenario, de modo que sus esfuerzos combinados se presenten en un escenario que es, tal vez, el mejor informe que pueda elaborarse. OJO RECORDAR QUE SE DEBEN COLOCAR NOMBRES A LAS CELDAS PARA QUE EN EL RESUMEN DE LOS ESCENARIOS APAREZCAN ESTOS NOMBRES Y NO LAS REFERENCIAS, COMO POR EJEMPLO $B$4 . Los pasos para crear un escenario son: HERRAMIENTAS ESCENARIOS AGREGAR colocarle un nombre al escenario (se acostumbra optimista, pesimista y ms probable), se coloca la o las celdas cambiantes (en este caso, el porcentaje de ventas), que son las que definen el escenario y al aceptar aparecen los valores actuales colocando los valores que se suponen para el escenario que estamos construyendo. Podemos pedir un RESUMEN, donde debemos colocar la celda o el rango de celdas resultantes (en nuestro caso, la utilidad en operacin) y a continuacin aparecer una hoja nueva denominada Resumen de Escenario en donde se exponen los resultados actuales y los resultadosElaborado por: Prof. Sonia Albarracn

54

posibles segn los escenarios previamente determinados. Tambin tenemos el formato de presentacin en tabla dinmica. Tarea: Crear escenarios basados en informacin real sobre, por ejemplo, tasas de inters, inflacin, desempleo, PIB, etc., para determinar su influencia en variables tales como ventas, costos de operacin, costos financieros, margen de ganancia, etc., para este ejemplo. Vistas Excel incorpora un administrador de vistas que permite ver una hoja de clculo con diferentes configuraciones de presentacin en pantalla y de impresin. Tambin se pueden presentar, imprimir y almacenar diferentes vistas de la misma hoja sin necesidad de guardarlas por separado. Una vista guarda los siguientes aspectos de una hoja de clculo: Las celdas seleccionadas as como la celda activa. El tamao y la posicin de la ventana. Muchas de las configuraciones de la orden de la orden Opciones del men Herramientas. Rtulos inmovilizados. Porcentaje de ampliacin o reduccin de las celdas. Esquemas. rea de impresin. Para crear una vista: 1. Ejecute Ver, Vistas personalizadas, ver el siguiente cuadro de dilogo:

2. Haga clic en el botn agregar, se abrir el siguiente cuadro de dilogo:

3. Escriba el nombre que se desea dar a la vista en el cuadro Nombre. Para facilitar la identificacin de las vistas, se recomienda incluir el nombre de la hoja activa en el nombre. 4. Seleccione la opciones deseadas de Incluir en la lista a. Configuracin de Impresora b. Configuracin de filas ocultas, columnas ocultas y filtros. 5. Haga clic en el botn Aceptar.Elaborado por: Prof. Sonia Albarracn

55

Para mostrar una vista: 1. Ejecute Ver, Vistas personalizadas. 2. Seleccione el nombre del cuadro de dilogo Vistas.

3. Haga clic en el botn Mostrar. Para borrar una vista: 1. Ejecute Ver, Vistas personalizadas. 2. Seleccione el nombre de la caja Vistas. 3. Haga clic en el botn eliminar. Para imprimir una vista: 1. Ejecute Ver, Vistas personalizadas. 2. Seleccione el nombre de la caja Vistas. 3. Haga clic en el botn Mostrar. 4. Haga clic en el botn imprimir de la barra de herramientas estndar. Puede trabajar con ele ejemplo de la Fig. 6.4. rea de trabajo Un rea de trabajo es un grupo de libros en los que se guarda informacin sobre los libros de trabajo abiertos incluyendo su organizacin, tamao y posicin de la ventana de documento en la pantalla. Para crear un rea de trabajo: Abra todos los libros de trabajo que desee incluir en el rea de trabajo. Site los libros tal y como desee que aparezcan al abrir el rea de trabajo. Ejecute Archivo, Guardar rea de trabajo. Acepte el nombre sugerido, REANUDAR.XLW, o introduzca el deseado en la caja Nombre del archivo. Haga clic en el botn Guardar. Para abrir un rea de trabajo: Ejecute Archivo, Abrir Seleccione el archivo deseado de la lista de la caja Nombre del archivo. Haga clic en el botn aceptar.Elaborado por: Prof. Sonia Albarracn

56

Para cerrar todos los libros de trabajo abiertos al mismo tiempo: Pulse la tecla antes de desplegar el men archivo. Seleccione cerrar todo. SECCIN 7.: BUSCAR OBJETIVOS Y SOLVER ESTAS DOS FUNCIONES SE ENCUENTRAN EN EL MEN DE HERRAMIENTAS O ACTIVARLAS EN COMPLEMENTOS DEL MEN DE HERRAMIENTAS, SI NO APARECE DEBE REALIZARSE UNA INSTALACIN DE OFFICE COMPLETA. BUSCAR OBJETIVO es una herramienta que de forma simple y efectiva consigue que obtengamos el valor deseado en una celda cambiando para ello el valor de otra celda. Su potencialidad radica en que entre la celda objetivo y la que se ha de cambiar pueden existir un gran nmero de celdas relacionadas, que tambin vern alterado su valor para conseguir el resultado deseado La funcin BUSCAR OBJETIVO tambin se encuentra disponible cuando se grafican variables que son resultado de otras: en un grfico de columnas podemos hacer click en una de ellas, volver a hacer click para luego tomarla del punto superior central y arrastrar hasta el valor deseado; cuando soltamos el click, aparecer el cuadro de dilogo de esta funcin. http://www.mailxmail.com/curso-excel-xp-segunda-parte/programa-solver SOLVER es una herramienta que nos permite optimizar los valores de una hoja en funcin de un conjunto de celdas a las que se pueden indicar que cumplan una serie de restricciones. Es un instrumento muy til para realizar simulaciones y conseguir resultados de forma relativamente fcil utilizando, sin darnos cuenta, mecanismos de clculo complejos. Observemos su funcionamiento mediante un pequeo ejemplo, en el que se desea contratar a un trabajador adicional, de tal manera que, la diferencia de los salarios entre cada uno de los departamentos sea cero; es decir, deseamos gastar lo mismo en la nmina del Grupo: Mercadeo y en la nmina del Grupo: Ventas. Veamos la Fig 7.3 Una vez conseguida la solucin deseada, podemos cambiar los datos de la hoja con los valores calculados por Solver eligiendo la opcin Utilizar solucin de Solver. Adems podemos obtener tres tipos de informes diferentes, de Respuestas (comparacin de los datos iniciales y los obtenidos con Solver), Sensibilidad (nos indica hasta qu grado se ha necesitado llegar en los valores mximos o mnimos de las restricciones) y Lmites (muestra el resultado que se obtiene en la celda objetivo variando cada una de las celdas cambiantes). Los operados que se pueden utilizar al definir las restricciones son < =, >=, =, int, y bin. Los dos primeros se interpretan como el lmite superior e inferior (por ello no existen valores < > solos, sino < = >=), el signo = obliga a que en esa celda se obtenga el valor indicado, los parmetros int y bin sirven para perfilar el valor obtenido, haciendo que el valor obtenido se redondee al entero inferior ms prximo o que sea un nmero en notacin binaria. Cuando se impongan restricciones del tipo int, obligando a obtener nmeros enteros, se puede definir en OPCIONES TOLERANCIA un porcentaje que indique cundo se considera aceptable una solucin a pesar de no ser el ptimo verdadero, de forma que se consiga trabajar con mucha ms rapidez en algunos procesos complicados. Dado que la herramienta Solver no se ha desarrollado para resolver un problema concreto sino con una visin de propsito universal, existen numerosas ocasiones en las que el sistema de trabajo de la macro no se adapta exactamente al problema a resolver, por ello se ha diseado el cuadro de dilogo de OPCIONES, en el que se permite controla la forma de trabajar la macro.Elaborado por: Prof. Sonia Albarracn

57

As, se pueden definir desde el tiempo mximo de clculo que se desea emplear (por defecto 100 segundos), el nmero de iteraciones a realizar, la precisin son que se han de conseguir las restricciones (indicando un cociente entre 0 y 1, de forma que cuantos ms decimales mayor precisin), la tolerancia para los nmeros enteros, ya comentada anteriormente, convergencia (indica si se desea seguir iterando a pesar de que el valor de la celda objetivo se va alejando de lo deseado, se expresa como fraccin entre 0 y 1, con menos decimales ms convergencia), adoptar modelo lineal (con lo que se consigue poder trabajar con un nmero ilimitado de restricciones, se aceleran los clculos y permite tambin solucionar de forma aproximada p

guiadeexcelavanzado

Documents