excel avanzado

MANUAL DE EXCEL AVANZADO INDICEFUNCIONES FUNCIONES TIPOS DE ARGUMENTOS ASISTENTE PARA FUNCIONES FUNCIONES FINANCIERAS FUNCIONES DE BUSQUEDA FUNCIONES LOGICAS BASES DE DATOS INTRODUCCION ESTRUTURA DE DATOS FORMULARIO DE DATOS ORDENAR REGISTROS FILTRO DE REGISTROS SUBTOTALES TABLAS DINAMICAS CREACIN DE TABLAS DINMICAS ACTUALIZACIN DE DATOS CAMBIAR LOS DATOS FUENTE CREACIN DE GRFICOS DESDE LA TABLA DINMICA CAMBIAR LA CONSULTA AGREGAR O ELIMINAR DATOS MOSTRAR PGINA EDICIN DE UN CAMPO FUNCIONES DE BASE DE DATOS ESQUEMAS CREAR ESQUEMAS AUTOMATICAMENTE CREAR ESQUEMAS MANUALES ELIMINAR UN ESQUEMA TRABAJO CON ESQUEMAS ANALISIS DE DATOS TABLAS DE HIPOTESIS TABLAS DE UNA VARIABLE TABLAS DE DOS VARIABLES ADMINISTRADOR DE ESCENARIOS 1 1 1 2 2 4 6 7 7 7 7 8 9 11 12 12 13 13 14 14 14 15 15 16 17 17 17 18 18 19 19 19 19 20

Manual de Microsoft Excel Avanzado v. 97

Pg. 1

CREACIN DE UN ESCENARIO USO DE ESCENARIOS RESUMIR LA INFORMACIN DE LOS ESCENARIOS IMPRESIN DE INFORMES VISTAS ADMINISTRADOS DE INFORMES AGREGAR UN NUEVO INFORME BUSCAR OBJETIVO SOLVER RESOLUCIN DE PROBLEMAS DEFINICIN DE UN PROBLEMA AGREGAR, CAMBIAR O ELIMINAR RESTRICCIONES SOLUCIONAR UN PROBLEMA CON SOLVER AJUSTAR LAS CONFIGURACIONES DE SOLVER RESULTADOS DE SOLVER MODELOS DE UN PROBLEMA CON SOLVER IMPORTACION Y EXPORTACION DE DATOS INTERCAMBIO DE DATOS CON OTRAS APLICACIONES INCRUSTACION DE OBJETOS VINCULACION DE OBJETOS MICROSOFT QUERY INSERTAR DATOS DE QUERY SELECCIN DE TABLAS UNIONES ENTRE TABLAS ASIGNACIN DE CRITERIOS A UNA CONSULTA RECUPERACIN DE DATOS EN LA HOJA DE CLCULO MACROS GRABACION DE UNA MACRO DE USUARIO VISUALIZAR Y DEPURAR UNA MACRO EJECUTAR UNA MACRO AUDITORIAS USO DE RASTREADORES TIPOS DE RASTREADORES ELIMINAR LAS FLECHAS DE RASTREO EJERCICIOS

20 21 21 22 22 22 24 25 25 26 26 27 28 29 29 30 31 31 31 31 32 32 33 34 35 36 37 37 40 41 43 43 43 43 44


Pg. 2

FUNCIONES

& FUNCIONES.Las funciones de la hoja de clculo son herramientas ya creadas, cuya misin es la de tomar decisiones, realizar acciones y ejecutar una serie de operaciones que devolvern valores de manera automtica. La escritura de una funcin est sujeta a la siguiente estructura : =NOMBRE_DE_LA_FUNCION(Argumento1;Arg.2;...Arg.n) donde: Nombre_de_la_funcin: Ser el nombre de la operacin a ejecutar. Argumento: Cada uno de los parmetros que la funcin incluya. Estos parmetros debern ir separados por el signo de ; .

& TIPOS DE ARGUMENTOS.Una funcin puede tener diferentes tipos de argumentos: Nmeros: Con o sin separador decimal. Los valores numricos permiten una precisin de hasta 15 decimales. Texto: Los argumentos de texto incluidos en las funciones, debern de ir obligatoriamente entre comillas dobles. Las cadenas de texto pueden incluir hasta 256 caracteres. Si se introduce un texto no entrecomillado, Excel supondr que es un nombre aplicado a un rango e intentar reemplazarlo por su valor. Valores lgicos: Estos valores son Verdadero y Falso, aunque tambin pueden ser enunciados mediante frmulas lgicas. Referencias: Referencias a celdas individuales o a rangos de celdas. Pueden ser relativas, absolutas o mixtas. Al utilizar referencias, el contenido de las celdas especificadas es tomado como argumento.


Pg. 3

& ASISTENTE PARA FUNCIONES.El Asistente para funciones ayuda en la introduccin de funciones dentro de la hoja de clculo. Mediante una serie de pasos, el asistente ir guiando sobre el uso de una funcin en concreto y de los parmetros que la misma tiene que cumplir. Para ejecutar el asistente, habr que acceder al men Insertar Asistente para funciones. El asistente agrupa las funciones segn sus categoras. Al seleccionar una funcin en concreto, la definicin de la funcin y su lista de argumentos se presentar de forma automtica y dar la posibilidad de introducir dichos argumentos en tiempo real.

& FUNCIONES FINANCIERAS.Estas funciones, permiten efectuar clculos financieros sin necesidad de construir modelos complicados o usar las clsicas frmulas financieras. Estas funciones utilizan una serie de argumentos comunes a todas ellas. Estos definen los diferentes elementos asociados a una operacin financiera. Son los siguientes: Tasa: Tipo de inters nominal por periodo. Nper: Nmero total de periodos de pago. Pago: Importe que se paga en cada periodo. Si el dinero es aportado por uno mismo, el signo aplicado es negativo. Ser positivo si se percibe. Va: Valor actual que se percibe (o se desembolsa) en el comienzo de una operacin. Vf: Valor futuro que se percibe (o desembolsa) al final de una operacin. Tipo: Seala el tipo de operacin respecto al momento de realizar los pagos. El tipo 0 (omisin) indica un tipo que se efectan despus del periodo. El tipo 1 indica un tipo prepagable. Las funciones ms utilizadas son las siguientes: VA Calcula el valor actual de una inversin. El valor actual, ser el valor que posee actualmente la suma de una serie de pagos que se efectuarn en el futuro. La sintaxis de la funcin ser como sigue, aunque lo ms frecuente es utilizar slo los tres primeros argumentos:


Pg. 4

= VA(tasa;nper;pago;vf;tipo)Ejemplo: Realizar el clculo del valor actual de una paga mensual de 25335 pts. Durante

=VA(9%/12;120;25335) -1.999.998 pts.

VF Calcula el valor futuro de una inversin basada en pagos peridicos y constantes y con un tipo de inters tambin constante. Los argumentos va y tipo son opcionales tomndose por defecto cero. =VF(tasa;nper;pago;va;tipo)Ejemplo: Se quiere saber el valor futuro de una aportacin mensual de 25335 pts. mensuales durante diez aos con un inters anual del 9% =VF(9%/12;120;-25335) 4.902.684 pts.

TASA Funcin que calcula el tipo de inters por periodo de una anualidad. Esta funcin se calcula por iteracin (ciclos de clculo) y puede tener cero o ms soluciones. Si los resultados que presenta la funcin no convergen en 20 iteraciones, genera un valor de error. =TASA(nper;pago;va;vf;tipo;estimar) Estimar ser la estimacin de la tasa de inters. Si se omite, se supone que es del 10%.Ejemplo: Sobre un prstamo de 2.000.000 de pts. que se amortiza en pagos mensuales de 25335 pts. durante 10 aos, se quiere saber el tipo de inters aplicado. =TASA(120;-25335;2000000) 0,75% mensual 9% anual

PAGO Calcula el importe del pago correspondiente a una operacin basada en pagos peridicos constantes (como un prstamo) y a un tipo de inters tambin constante. Los argumentos vf y tipo son opcionales. =PAGO(tasa;nper;va;vf;tipo)Ejemplo: Se quiere saber que cantidad mensual se ha de pagar por un prstamo de 2000000 amortizable en 10 aos con un inters del 9% =PAGO(9%/12;120;2000000) -25.335 pts.


Pg. 5

NPER Calcula el nmero de periodos que son necesarios para completar una operacin financiera de rentas. La forma de la funcin es: =NPER(tasa;pago;va;vf;tipo)Ejemplo: Para la compra de una vivienda, se solicita un prstamo de 12000000 de pts. que ser devuelto en pagos mensuales de 150000 con un inters del 11%. Calcular con cuantos pagos quedar el prstamo satisfecho. =NPER(11%/12;-150000;12000000) 145 meses

PAGOINT Devuelve el inters pagado en un periodo especfico por una inversin, basndose en pagos peridicos constantes y en una tasa de inters constante. =PAGOINT(tasa;per;nper;va;vf;tipo)Ejemplo: Se quiere conocer la parte correspondiente al inters del primer pago mensual de un prstamo de 800000 pts. pagadero en tres aos con un inters del 10% anual =PAGOINT(10%/12;1;36;800000) -6.667 pts.

PAGOPRIN Devuelve el pago sobre el capital de una inversin durante un periodo determinado, basndose en pagos peridicos y constantes y en una tasa de inters constante. =PAGOPRIN(tasa;per;nper;va;vf;tipo)Ejemplo: Se desea conocer la parte correspondiente a principal del primer pago mensual de un prstamo de 800000 pts. pagadero en tres aos a un tipo del 10% anual. =PAGOPRIN(10%/12;1;36;800000) -19.147 pts.

& FUNCIONES DE BUSQUEDA.Gracias a estas funciones, es posible realizar operaciones de localizacin de datos en funcin de otros. La ventaja de utilizar estas funciones consiste en la posibilidad de accedera determinados datos en una lista. Las principales son:


Pg. 6

BUSCAR Esta funcin localiza un valor en un rango y devuelve otro valor asociado. La forma es la siguiente: =BUSCAR(Valor a buscar;rango donde se busca;rango resultado) Para que el uso de esta funcin sea correcta, los valores debern estar ordenados ascendentemente. BUSCARH Esta funcin explora la fila superior de una matriz hasta localizar el valor buscado. Una vez localizado, extrae de la columna encontrada el valor de la fila que se indica en la funcin. =BUSCARH(Valor a buscar;Rango m x n;Fila a extraer) BUSCARV Similar a la funcin BUSCARH pero se usa para cuando la bsqueda se realiza en la primera columna de una matriz en lugar de hacerse en la primera fila. COINCIDIR Devuelve la posicin relativa de un elemento que coincide con un valor determinado en una matriz. Los posibles valores de coincidencia son 1, 0 1. =COINCIDIR(Valor a buscar;matriz;tipo de coincidencia) ELEGIR Esta funcin devuelve una opcin entre varias. =ELEGIR(Opcin;Lista de opciones) COLUMNA, FILA, COLUMNAS, FILAS Devuelven el nmero de columna/fila de la referencia o de la seleccin. =COLUMNA(celda) =FILA(celda) =COLUMNAS(seleccin) =FILAS(seleccin)


Pg. 7

& FUNCIONES LOGICAS.Las funciones lgicas evalan una serie de datos mediante una condicin. Utilizan los operadores lgicos de > (mayor), < (menor), (distinto), >= (mayor o igual) y 5;Aprobado;Suspenso)

VERDADERO, FALSO Estas son funciones sin argumento y sitan su valor en la celda seleccionada. =VERDADERO() =FALSO()


Pg. 8

BASES DE DATOS

& INTRODUCCION.En ocasiones, la informacin de una tabla se puede disponer en forma de listas de datos. Este tipo de informacin referente a un mismo tema, se deber de colocar en una misma fila como si de una ficha se tratase. Excel est perfectamente diseado para la construccin de registros de tablas, as como para la incorporacin de datos de bases externas.

& ESTRUCTURA DE DATOS.Para crear una lista de datos, es necesario determinar su estructura. Para ello nicamente hay que crear una fila de ttulos con los nombres de los campos. Estos debern de ser nicos y encontrarse siempre en la primera fila del rango que se use como datos. Posteriormente habr que escribir en las filas sucesivas cada uno de los registros de la base de datos. Es posible definir algn tipo de campo calculado que se ejecutar automticamente al realizar la introduccin de datos.

& FORMULARIO DE DATOS.Una vez creada la estructura de datos para aadir registros tenemos que acudir al men Datos y a la opcin Formulario. Esta opcin posibilita la introduccin de informacin en modo ficha donde se presentarn los nombres de campo a la izquierda de la misma y el contenido de cada uno de los registros a la derecha. Esta modo permite manejar, modificar y eliminar la informacin existente as como aadir datos nuevos. La aplicacin presentar la ventana siguiente:


Pg. 9

En ella, se podrn realizar las siguientes operaciones: Nuevo: Aadir un nuevo registro a la base. Tras escribir los datos del nuevo registro, este pasar a formar parte de la hoja. El paso entre los diferentes campos se realizar mediante el clic de ratn o tecla de tabulador. La tecla Enter validar el registro. Eliminar: Eliminar de la base de datos el registro sobre el que se est situado. Este registro desaparecer de la hoja de manera automtica. Restaurar: Actualiza la informacin de la base de datos. Buscar siguiente / anterior: Permite revisar los registros de la base de datos. Criterios: Permite utilizar criterios de bsqueda para extraer de la base de

& ORDENAR REGISTROS.Puede ser necesario realizar una ordenacin de la informacin de la base de datos. El proceso de ordenacin de los registros lo recoloca mediante criterios de ordenacin manteniendo siempre la integridad de los datos. Para realizar esta operacin y una vez seleccionados los datos (aunque esto no es imprescindible) habr que acudir al men Datos y elegir la opcin Ordenar. La ventana que se presenta es como sigue:


Pg. 10

En la ventana de ordenacin habr que especificar los criterios de ordenacin. Para ello fijaremos el orden de los campos, pudiendo ordenar hasta por tres criterios en formato ascendente o descendente. Si los campos han sido seleccionados o no, habr que indicarlo en esta ventana.

& BUSCAR REGISTROS QUE CUMPLEN UNA DETERMINADACONDICIN: FILTRO DE REGISTROS.Buscar registros significa hacer que aparezcan de todos los registros de la base de datos que cumplan una determinada condicin. Para conseguirlo existen dos formas posibles de establecer unas condiciones para buscar registros que cumplan una determinada condicin:


Pg. 11

1.-

Filtros Automticos.

Con el comando Datos / Filtro / Autofiltro del men datos es posible localizar registros que contengan una informacin especfica. 2.Filtros Avanzados

Es necesario especificar la condicin que deben cumplir. Para ello habr que escribir un rango de criterios en una zona aparte de la tabla, incluso en otra hoja de la siguiente manera:A 1 2 EDAD >18 B

Seleccionar la tabla de datos y acudir al men Datos y la opcin avanzado.

Filtro / Filtro


Pg. 12

Ser necesario elegir el Rango de criterios y el Rango de la lista. Para volver a mostrar todos los datos, seleccionar el men Datos y seleccionar la opcin Filtro / Mostrar todo Si es necesario extraer datos de la tabla que cumplan una determinada condicin para realizar operaciones fuera del contexto de la tabla, repetiremos el proceso anterior pero seleccionando la opcin de copiar a otro lugar. Ser necesario definir el lugar donde se desea realizar la copia. Para realizar la eliminacin de los registros que cumplen una determinada condicin, despus de realizar una bsqueda avanzada, se seleccionarn los datos y se acudir a / Eliminar fila.

& SUBTOTALES.Los subtotales, permiten construir valoraciones por grupos en las listas de datos. Con esta opcin se generar un subtotal para cada cambio en los valores de un campo pudindose utilizar diferentes funciones. Para generar correctamente subtotales ser necesario seguir unos pasos: Ordenar una lista: Ser necesario ordenar los datos en una lista por la columna en subtotal. Calcular los subtotales: Se debern especificar los elementos cuyos subtotales se desea calcular, los valores a resumir y la funcin que se vaya a utilizar en los valores (por defecto SUMA). Una vez ordenada la lista, se har clic en una celda de la lista ordenada y a continuacin en el men Datos Subtotales.


Pg. 13

Mostrar y ocultar los detalles: Cuando se le aaden subtotales automticos a unalista, esta aparecer con formato de esquema lo cual permitir revisar su estructura.

Generar un grfico: Una vez creados los subtotales y presentado el formato deesquema, ser posible generar un grfico con estos datos mediante el asistente para grficos. Segn se vayan modificando los diferentes niveles del esquema, el

& TABLAS DINAMICAS.Una tabla dinmica es una tabla interactiva que se utiliza para generar resmenes y analizar los datos de una tabla o lista. Estas tablas se actualizan cada vez que se modifican los datos originales. CREACION DE TABLAS DINAMICAS Las tablas dinmicas se generan con el Asistente para tablas dinmicas Datos, el cual generar una serie de cuadros interactivos que guiarn al usuario para el uso de los datos que se desea analizar. Este asistente ayuda en la definicin de la organizacin de la tabla permitiendo organizarla usando rtulos de filas y columnas, as como mostrar los datos en los campos. Una tabla dinmica se puede generar a partir de los datos fuente de: Lista o base de datos de Excel. Base de datos externa a Excel, tomando una tabla o archivo creado de forma independiente a l. Para realizar esta operacin, es necesario tener instalado el programa Microsoft QUERY y los controladores de conexin a base de datos (ODBC) adecuados. Rangos de consolidacin mltiple, que permite la consolidacin de datos de varias listas o de varias hojas de clculo. En este caso, los nombres de fila y de columna de los elementos que desee resumir juntos en las listas o en las hojas de clculo


Pg. 14

debern ser idnticos. Al crear la tabla dinmica, no habr que incluir ni filas ni columnas de totales de los datos de origen. Otra tabla dinmica definida en el mismo libro de trabajo.

ACTUALIZACION DE DATOS EN UNA TABLA DINAMICA Si los datos fuente de una tabla dinmica han cambiado, ser necesario seleccionar una celda en la tabla dinmica y desde el men Datos utilizar la opcin Renovar. Si la tabla dinmica est basada en una lista de Excel y se insertan nuevas lneas y columnas, ser necesario ejecutar de nuevo el asistente para modificar la tabla incluyendo datos nuevos. CAMBIAR LOS DATOS FUENTE Si es necesario modificar los datos fuente sobre una lista Excel, para modificar los datos ser necesario: Situarse sobre una celda de la tabla dinmica. Desplegar el men Datos y seleccionar Asistente para tablas dinmicas. Hacer clic sobre el botn Atrs para regresar al paso 2.


Pg. 15

Determinar nuevos datos fuente y terminar. Para que los nuevos datos se actualicen ser necesario utilizar la opcin Renovar. CREACION DE UN GRAFICO DESDE LA TABLA DINAMICA Excel permite evaluar diferentes niveles de datos en una tabla dinmica y crear un grfico que refleje sus niveles y relaciones. El grfico se modificar cuando se oculten elementos, se presenten detalles o se reorganicen campos en la tabla. Para generar este grfico se debern seguir los siguientes pasos: Seleccionar la tabla, incluyendo en la seleccin los campos de columna y fila. No hay que incluir ningn campo de pgina. Ejecutar el asistente para grficos. Seleccionar el tamao del grfico. CAMBIAR LA CONSULTA Este procedimiento es utilizable si se cre una tabla dinmica a partir de una fuente de datos externa. Las modificaciones de la consulta sern realizadas a travs de Microsft Query. Para realizar este cambio habr que hacer lo siguiente: Seleccionar una celda de la tabla dinmica. Ejecutar el asistente para tablas dinmicas. Pulsar el botn Atrs para acceder al paso 2 del asistente. Pulsar el botn Obtener datos. De manera automtica aparecer Microsoft Query y el conjunto de los resultados. Editar la consulta o el conjunto de resultados en Query y desde el men de Archivo elegir Devolver datos a Microsoft Excel volviendo de nuevo al asistente de tablas dinmicas. Seleccionar la opcin de Terminar. AGREGAR O ELIMINAR DATOS DE UNA TABLA DINAMICA Suponen modificaciones sobre una tabla dinmica ya creada. Para agregar un campo a una tabla dinmica, ser necesario ejecutar el asistente sobre una celda de la tabla dinmica. Desde el paso 3 del mismo, se arrastrarn los botones de los


Pg. 16

Finalizar la tabla quedar actualizada. Si lo que se desea es eliminar algn campo de la tabla, habr que arrastrar fuera de la tabla el campo no deseado cuando se est colocado en el paso 3 del asistente. MOSTRAR LAS PAGINAS EN UNA TABLA DINAMICA Los campos de una tabla dinmica pueden ser presentados en fila, columna o pgina. La distribucin se realiza desde el propio asistente al generar la tabla, o en cualquier momento en su modificacin. Al presentar un campo como elementos a la vez, pero posibilita el desplazarse entre ellos gracias al uso de su flecha desplegable. Un cambio en el elemento del campo, implicar una presentacin de datos distinta. Para definir un campo como pgina se podrn seguir dos procedimientos: Desde el Asistente de tablas dinmicas, arrastrando el campo deseado a la zona pgina. Una vez generada la tabla dinmica y haciendo doble clic sobre el campo deseado modificar su zona de presentacin. EDICION DE UN CAMPO El cuadro de dilogo Campo de la tabla dinmica permite realizar modificaciones concretas sobre el campo seleccionado. Ser posible modificar la orientacin de ese campo dentro de la tabla sencillamente con cambiarlo en esta ventana. Los subtotales que genera un campo dentro de una tabla por defecto son de suma. Desde este cuadro de dilogo ser posible modificar este aspecto actuando sobre la zona de subtotales. Las opciones son: Automticos: Definidos por defecto. Hacen referencia a totales de suma. Personalizados: Donde ser posible utilizar cualquiera de las funciones detalladas en la lista que se presenta a su derecha.


Pg. 17

Ninguno: No aplicara ningn tipo de formula al campo.

& FUNCIONES DE BASE DE DATOS.Existen unas funciones concretas orientadas al manejo de base de datos. La forma general de estas funciones es: BDFUNCION(base_de_datos;nombre_de_campo;criterio) Las funciones de base de datos son las siguientes:

ESTRUCTURA DE LA FUNCIN BDCONTAR BDCONTARA BDEXTRAER BDMAX BDMIN BDPRODUCTO BDPROMEDIO BDSUMA

VALORES QUE DEVUELVE Cuenta las celdas que contienen nmeros Cuenta las celdas que no estn en blanco Extrae valores individuales de una base de datos Devuelve el valor mximo Devuelve el valor mnimo Calcula el producto Calcula el promedio Calcula la suma


Pg. 18

ESQUEMASLos esquemas permiten crear informes resumidos de la hoja de clculo. Es posible realizar una esquematizacin de un rango de datos o de toda la hoja a la vez. Una vez que el esquema est generado, ser posible manejar sus diferentes niveles para visualizar los datos necesarios. La posibilidad de generar esquemas, permite ver y operar slo con aquellas partes de la hoja que nos interesen en un momento determinado. Al aplicarlo, asociamos a la hoja una estructura con varios niveles subordinados unos de otros.

& CREAR ESQUEMAS AUTOMATICAMENTE.Los esquemas automticos los genera Excel tomando las decisiones necesarias para una correcta presentacin de la informacin, agrupndolos segn sus diferentes niveles de datos. Para generar un esquema de manera automtica ser necesario seguir los siguientes pasos: Seleccionar el rango que se desea esquematizar (rango o toda la hoja). Acceder al men Datos, elegir la opcin Agrupar y esquema y seleccionar Autoesquema. Excel tomar de manera automtica todos los datos incluidos en la seleccin y presentar barras de desplazamiento de esquema para visualizar los diferentes niveles.

& CREAR ESQUEMAS MANUALES.En los esquemas manuales, es el usuario el que toma la decisin de que zona de detalle es la que se desea esquematizar. Para realizarlo habr que realizar lo siguiente: Seleccionar las filas o columnas de detalle que estn subordinadas a la columna titular.


Pg. 19

Desde el men Datos elegir la opcin Agrupar y esquema seleccionando Agrupar Si no estn marcadas filas o columnas enteras, Excel presentar un cuadro de dilogo preguntando cual ser el origen del agrupamiento.

& ELIMINAR UN ESQUEMA.Para eliminar uno o ms grupos de esquemas, ser necesario seleccionar las filas o columnas de detalle que estn contenidas en los grupos. Si se desea eliminar todo el esquema, ser necesario seleccionarlo al completo (o toda la hoja de clculo). Una vez seleccionada la zona a tratar utilizaremos el men Datos / Agrupar esquema / Borrar esquema para su eliminacin.

& TRABAJO CON ESQUEMAS.Una vez generado el esquema es posible realizar sobre el diferentes operaciones: Ocultar detalle: Permite cerrar un nivel de esquema concreto. Esta operacin se Datos / Agrupar y esquema / Ocultar detalle, actuando sobre los smbolos del detalle o directamente sobre la barra de nivel. Mostrar detalle: Permite abrir un nivel de esquema cerrado. Esta operacin se podr realizar desde el men Datos / Agrupar y esquema / Mostrar detalle y con las opciones descritas en el punto anterior. Agrupar: Permite crear nuevos niveles en un esquema, agrupando una serie de filas y columnas previamente seleccionadas. Para realizar esta operacin ser Datos / Agrupar y esquema / Agrupar. Desagrupar: Permite eliminar niveles en un esquema. Para realizar esta operacin ser necesario ejecutar la opcin Datos / Agrupar y esquema / Agrupar.


Pg. 20

ANALISIS DE DATOS

& TABLAS DE HIPOTESIS.Una tabla de datos es un rango de celdas donde se presentan los resultados que se obtienen al sustituir distintos valores en una o ms celdas. Las tablas de datos se distinguen en: TABLA DE UNA VARIABLE Estas tablas muestran como afectan los cambios realizados en una variable en una o en varias columnas. Pueden incluir diferentes valores para la variable y varias frmulas. Estas tablas de variables pueden ser realizadas por columna o por fila. Los pasos a seguir sern los siguientes: Seleccionar el rango que contiene la frmula y las posibles variables. Seleccionar la opcin Tabla del men Datos y seleccionar la celda de entrada (columna o fila) segn se hayan introducido las variables. Esta celda ser de la cual dependern los clculos. Para finalizar Elegir Aceptar.

TABLA DE DOS VARIABLES Estas tablas ensean como afectan los cambios que se realizan en dos variables de la frmula. Estas tablas pueden incluir varios valores para cada una de las dos variables, pero slo una puede usar una frmula. La forma de actuar ser la misma que la descrita en las tablas de una variable pero definiendo dos entradas variables, una para fila y otra para columna. El resultado ser una lista combinatoria de los valores originales tratados por ambos valores variables. Es recomendable que dentro de los posibles combinaciones est la original para utilizarla como control.


Pg. 21

& ADMINISTRADOR DE ESCENARIOS.El administrador de escenarios es otra de las herramientas que utiliza Excel para facilitar el anlisis de los modelos de la hoja. Un escenario ser un conjunto de valores gracias a los cuales se carga un modelo. CREACION DE UN ESCENARIO Para la creacin de escenarios ser necesario activar la hoja de clculo sobre la que deseamos actuar. Al acceder al men Escenarios, Excel presentar el siguiente cuadro de dilogo:

Si elegimos la opcin de Agregar, aparecer una ventana de recogida de informacin para el nuevo escenario donde ser necesario incluir: Nombre del escenario: Datos referentes a cada uno de los nombres del escenario. Celdas cambiantes: Donde se introducir la referencia de las celdas (rango) que se desee cambiar. Estas celdas sern las variables de nuestro escenario.


Pg. 22

Proteccin: Permite definir niveles de proteccin para el mismo. Es posible definirlo evitar cambios) o hacerlo no visible en la lista de escenarios (Ocultar). Una vez aadido la nueva definicin de escenario ser necesario realizar la asignacin de sus valores. Estos se realizarn desde el siguiente cuadro:

USO DE ESCENARIOS Si se desea ver que sucede en un modelo cuando se cumpla un escenario u otro, ser necesario realizar los siguientes pasos: Acceder al men de Herramientas Escenarios. Esta opcin presenta la ventana de

Seleccionar el escenario que se desea aplicar. Hacer clic sobre el botn Mostrar. Al mostrar un escenario ya creado, los valores de la hoja sern sobreescritos por los resultados generados. Es conveniente generar un escenario con los valores actuales para poder recuperarlos en cualquier momento. RESUMIR LA INFORMACION DE LOS ESCENARIOS Es posible obtener un informe de todas las variables de los escenarios. Este informe generar un resumen de los mismos, as como los resultados que producirn cada uno. La forma

Abrir el men de Herramientas Escenarios. Hacer clic sobre la opcin Resumen.


Pg. 23

Del cuadro de dilogo que presenta la aplicacin, se introducirn las referencias que deseamos analizar y seleccionar el tipo de informe (Resumen o Tablas dinmica). Aceptar el cuadro. Excel generar una nueva hoja de clculo con los datos resumen solicitados. Esta nueva hoja de clculo se podr formatear para conseguir la apariencia deseada.

Administrador de informes, seleccionar el informe deseado y hacer clic sobre el botn Imprimir.

& VISTAS.Una vista es una forma sencilla de poder ver los datos con distintas opciones de presentacin. Es posible manipular, enviar a impresin y almacenar diferentes vistas sin necesidad de guardarlas en hojas separadas. Para crear una nueva vista habr que acceder al men Ver Vistas personalizadas. El cuadro de dilogo que presenta la aplicacin incluye las siguientes opciones: Vistas: Presenta todas las vistas disponibles para el libro activo. Mostrar: Ensea la vista seleccionada en el cuadro Vistas. Agregar: Crea una nueva vista en la que se pueden guardar las opciones definidas de impresin y opciones de visualizacin de la hoja. Eliminar: Elimina la vista seleccionada.

& ADMINISTRADOR DE INFORMES.El Administrador de informes es una utilidad orientada a la impresin de documentos. Gracias a esta herramienta se podrn disear informes sobre una secuencia de hojas, de vistas (donde es posible guardar diferentes configuraciones en el modo de ver e imprimirlas) y de escenarios. Al Adminstrador de informes se accede mediante el men Ver Administrador de informes. Si esta opcin de men no estuviera implementada, habra que crearla. Para ello es


Pg. 24

Herramientas Macros automticas. La ejecucin de macros automticas presentar la siguiente ventana:

Desde la ventana de las Macros automticas, se activar la opcin de Administrador de informes y tras Aceptar, esta opcin se encontrar disponible en el men Ver. Una vez ejecutado el Adminsitrador de informes, el programa presentar un cuadro de dilogo con una serie de opciones: Imprimir: Enviar a impresora el informe seleccionado. Agregar: Crear un nuevo informe dentro del libro actual con las opciones deseadas. Editar: Permitir la modificacin de un informe seleccionado. Eliminar: Borrar un determinado informe, quedando no disponible para el libro.


Pg. 25

AGREGAR UN NUEVO INFORME Para aadir un nuevo informe, ser necesario utilizar el botn Agregar de la ventana del Administrador de informes. Esta opcin presentar un nuevo cuadro de dilogo donde se definirn todos los aspectos del informe a generar. Las posibilidades que ofrece este cuadro son: Nombre del informe: Permite asignar un nombre al informe de nueva creacin. Agregar seccin: Una seccin es cualquier combinacin deseada de una hoja, presentacin o escenario seleccionado al crear un informe. Un informe puede contener diferentes secciones. Para cada seccin ser necesario especificar: Hoja: Definir la hoja del libro que se desea para la actual seccin. Vista: Permite seleccionar una determinada vista almacenada en esa hoja. Escenario: Permite escoger un escenario definido en esa hoja. Tanto en vista como en escenario, ser posible seleccionarlo de una lista desplegable. Secciones de este informe: Caja en la que se podrn visualizar las diferentes secciones definidas para el informe actual. Mover hacia arriba / abajo: Desplaza hacia arriba / abajo la seccin seleccionada en el cuadro Secciones de este informe, decidiendo as su orden de presentacin. Usar nmero de pgina consecutivos: Si esta opcin no se encuentra activada, Excel iniciar la numeracin en 1 en cada seccin del informe. En caso contrario, la

Eliminar: Suprime una seccin previamente definida.


Pg. 26

& BUSCAR OBJETIVO.La bsqueda de objetivos es uno de los primeros aspectos del anlisis de los datos en una hoja de clculo. Los objetivos permiten realizar tanteos o estimaciones sobre datos es decir, buscar un resultado especfico de una celda ajustando el valor de otra celda. Para realizar una bsqueda de objetivos, ser necesario: Acceder al men Herramientas Buscar objetivo. En el cuadro de dilogo que presenta la aplicacin se deber: Definir celda: Introducir la referencia de la celda que tenga la frmula que desee resolver. Con el valor: Introducir el resultado buscado. Para cambiar la celda: Escribir la referencia de la celda que contenga el valor que se desea ajustar. Hacer clic en el botn Aceptar. Con esto Excel comienza un tanteo modificando los valores de la celda definida en Para cambiar la celda y recalculando continuamente la hoja hasta alcanzar el objetivo.

& SOLVER.El Solver es una poderosa herramienta de Excel que permite, mediante la asignacin de recursos, optimizar los datos (resolver datos). Gracias al Solver es posible determinar el uso ms adecuado de recursos escasos para poder as aumentar los objetivos deseados tales como ganancias o bien de reducir por ejemplo, los costes no deseados.


Pg. 27

RESOLUCION DE PROBLEMAS CON SOLVER Es necesario seguir una serie de pasos: Especificar una celda objetivo que se desea disminuir, aumentar o ajustar a un determinado valor. Especificar las celdas cambiantes que se debern de modificar hasta que se encuentre una solucin. Es posible definir hasta 200 celdas cambiantes. Especificar las celdas restringidas que debern mantenerse dentro de ciertos lmites o que debern satisfacer ciertos valores claves. Es posible definir hasta 500 restricciones (dos por cada celda cambiante ms cien adicionales), representando un total no mayor que 1000 celdas por problema. Una vez resuelto el problema, ser posible generar tres tipos de informes resumiendo los resultados. DEFINICION DE UN PROBLEMA CON SOLVER Para una correcta definicin de un problema de Solver habr que actuar de la siguiente forma: Seleccionar la opcin Solver del men Herramientas. En la caja de entrada Celda objetivo, se introducir una referencia de celda o un nombre para cada objetivo. Si el valor de la celda objetivo se desea lo ms elevado posible, habr que seleccionar . Si se pretende el valor ms bajo, se elegir y si la celda objetivo ha de tener un valor determinado, se usar la opcin igual a escribiendo a continuacin el valor deseado. Dentro de la caja de entrada Cambiando las celdas se escribir un nombre o una referencia para cada celda cambiante. Si se ha introducido ms de una referencia, estas debern de ir separadas por comas. Si lo que se desea es que Solver proponga lasManual de Microsoft Excel Avanzado v. 97

Pg. 28

celdas cambiantes segn su criterio y basndose en la celda objetivo, habr que hacer estimar. Es posible definir hasta un mximo de 200 celdas cambiantes. En el cuadro Sujetas a las siguientes restricciones, ser posible agregar cualquier restriccin que se desee aplicar. Estas restricciones sern aplicables a la celda objetivo, celdas cambiantes o a cualquier otras celda relacionada directa o indirectamente con el problema. AGREGAR, CAMBIAR O EILIMINAR RESTRICCIONES En el Solver, una restriccin es una condicin que la solucin deber cumplir de manera obligatoria, por ejemplo A1

excel avanzado

Documents