manual excel avanzado

Material Excel Avanzado

2Excel Avanzado 2007

ESCENARIOS

Crear escenarios para análisis Y si

Los escenarios forman parte de una serie de comandos a veces denominados herramientas de análisis Y si (análisis Y si: proceso de cambio de los valores de celdas para ver cómo afectan esos cambios al resultado de fórmulas de la hoja de cálculo. Por ejemplo, variar la tasa de interés que se utiliza en una tabla de amortización para determinar el importe de los pagos.). Un escenario es un conjunto de valores que Microsoft Office Excel guarda y puede sustituir automáticamente en la hoja de cálculo. Puede utilizar los escenarios para prever el resultado de un modelo de hoja de cálculo. Puede crear y guardar diferentes grupos de valores en una hoja de cálculo y, a continuación, pasar a cualquiera de estos nuevos escenarios para ver distintos resultados.

Crear escenarios Por ejemplo, usted podría utilizar un escenario si quisiera preparar un presupuesto, pero no supiera con exactitud sus ingresos. En este caso, con un escenario podría definir valores diferentes para dichos ingresos y, seguidamente, pasar de un escenario a otro para realizar un análisis Y si.

Pasos para Crear un escenario1. Clic en Menú Datos\Herramientas de datos\Análisis Y si\ Administrador de Escenarios.2. Clic Botón Agregar.3. Digitar nombre del Escenario.4. En celdas cambiantes: Seleccionar las celdas que se modificarán para el escenario.5. Colocar un comentario del contenido del escenario.6. Clic en Aceptar.7. En cada celda digitar el valor correspondiente.8. Clic en Aceptar.9. Clic en Aceptar.

Nota Importante: no se pueden seleccionar celdas cambiantes que tengan un cálculo ya que este se pierde.

Pasos para Mostrar un escenario (ver el cambio entre escenarios)1. Clic en Menú Datos\Herramientas de datos\Análisis Y si\ Administrador de Escenarios.2. Seleccionar el nombre del escenario a mostrar3. Clic Botón Mostrar.

Pasos para Modificar un escenario1. Clic en Menú Datos\Herramientas de datos\Análisis Y si\ Administrador de Escenarios.2. Selecciono el nombre del escenario a modificar.3. Clic Botón Modificar.4. Sigo los pasos correspondientes.

Pasos para Eliminar un escenario1. Clic en Menú Datos\Herramientas de datos\Análisis Y si\ Administrador de Escenarios.2. Selecciono el nombre del escenario a modificar.3. Clic Botón Eliminar.4. Clic Botón Cerrar.

Pasos para combinar escenarios (Permite combinar escenarios de hojas diferentes)1. Deben de existir escenarios en diferentes hojas.2. Clic en Menú Datos\Herramientas de datos\Análisis Y si\ Administrador de Escenarios.3. Clic Botón Combinar.4. Elegir la hoja que contiene el otro escenario.5. Clic en Aceptar. Inmediatamente se pasa el escenario a la otra hoja.

Pasos para Crear un resumen de los escenarios (permite crear una hoja con el resultado de todos los escenarios de esa hoja)

1. Clic en Menú Datos\Herramientas de datos\Análisis Y si\ Administrador de Escenarios.2. Clic Botón Resumen.3. Marcar opción Resumen.


4. Seleccionar celdas que den un resultado (debe de contener un cálculo) de no tener un cálculo se deja vacio.5. Clic en Aceptar.6. Inmediatamente se crea una hoja nueva con todos los escenarios.

Pasos para cambiar los Nombres de Celdas1. Seleccionar la celda2. Clic en el Cuadro de Nombres3. Digito el nuevo nombre (no puede estar separado, se pueden unir palabras con el guión bajo)4. Presiono la tecla Enter.

Pasos para Modificar o Eliminar el nombre de una celda1. Clic en Menú de Formulas/ Nombres definidos/ Administrador de nombres2. Selecciono el nombre a cambiar y clic en botón Editar, digito el nuevo nombre y Aceptar.3. Si es Eliminar, selecciono el nombre y clic en el botón Eliminar.4. Aceptar.

BÚSQUEDA DE OBJETIVOSObtener el resultado deseado ajustando un valor utilizando Buscar objetivo. Si conoce el resultado que desea de una fórmula, pero no del valor de entrada que necesita la fórmula para obtener dicho resultado, puede utilizar la función Buscar objetivo.

Pasos para la Búsqueda de Objetivos1. Clic en Menú Datos\Herramientas de datos\Análisis Y si\Buscar Objetivos.2. En Definir celda: se coloca la celda que contiene un cálculo y es la que deseamos que cambie.3. En Con el valor: digitamos el numero que deseamos obtener.4. En Para cambiar la celda: Seleccionamos la celda que deseamos que cambie su valor para lograr el número

deseado5. Clic en Aceptar.

AUDITORIA DE FÓRMULASEn ocasiones, comprobar si las fórmulas son precisas o buscar el origen de un error puede resultar difícil si la fórmula utiliza celdas precedentes o dependientes.

Pasos para Rastrear Precedentes (permite señalar cuáles celdas confeccionan un cálculo)1. Seleccionar una celda que contenga un cálculo.2. Clic en Menú Fórmulas\Auditoría de Fórmulas\Rastrear precedentes.

Pasos para Rastrear Dependiente (Permiten señalar en que cálculo se está usando dicha celda)1. Seleccionar una celda que contenga un cálculo o un dato fijo.2. Clic en Menú Fórmulas\Auditoría de Fórmulas\Rastrear dependientes.

Pasos para Comprobar Errores1. Seleccionar una celda que contenga un error.2. Clic en Menú Fórmulas\Auditoría de Fórmulas\Comprobar errores.3. Clic en Mostrar pasos del cálculo (indica como se está realizando el cálculo, el usuario debe de saber interpretar

dicho error)4. Clic en Evaluar.5. Clic en Cerrar

Pasos para Mostrar de donde viene el error1. Seleccionar una celda que contenga un error.2. Clic en Menú Fórmulas\Auditoría de Fórmulas\Comprobar errores (Flechita).3. Clic en Rastrear error.

Notas importantes: En ambos casos se señalarán con flechas dichas celdas. Pasado cierto tiempo las flechas desaparecen solas, o puede usar la opción quitar flechas para borrarlas.


Colores de las flechas: Flechas de color Azul: se muestran cuando las celdas usas están en la misma hoja donde se aplica la auditoría. Flechas de Color Negro punteada: se muestran cuando las celdas usas están en otra hoja diferente donde se

aplica la auditoría. Flechas Rojas: Se muestran cuando en un cálculo se está usando una celda que genero un error en su cálculo.

VALIDACIÓN DE DATOSEvitar la entrada de datos no válidos en una hoja de cálculo. Introduce una expresión que limita los valores que pueden ser introducidos en el campo. Requiere de un mensaje de error relacionado con la validación.

Pasos para Aplicar validación de datos:1. Se debe de seleccionar el rango a validar estando vacío.2. Clic en Menú Datos\Validación de datos.3. Se debe de seleccionar la condición que permita realizar la validación:

a. Números Enteros: solo permite ingresar números que no tengan decimales. b. Decimales: permite ingresar números con decimales.c. Fechas: permite validar fechas.d. Horas: permite validar horase. Listas: permite ingresar una lista de datos. Debe de separar cada valor por una coma (,) o seleccionar

un rango de celdas que contenga los valores que desea mostrar en la lista.f. Longitud de texto: permite contar la cantidad de texto que se puede digitar.

4. En todos debe de elegir el Dato (operador lógico que necesita)5. Digitar valor Mínimo y Máximo: depende del dato seleccionado.6. Puede usar funciones dentro.7. Se debe de indicar el mensaje que permita solucionar el error de la validación (Clic en Mensaje de error).8. Clic en Aceptar.

Pasos para eliminar una validación1. Seleccionar el rango que valido nuevamente.2. Clic en Menú Datos\Validación de datos.3. Clic en el botón Borrar todos.

CARGAR O DESCARGAR PROGRAMAS DE COMPLEMENTOSExisten diferentes tipos de programas de complementos para Microsoft Office Excel que proporcionan comandos y características opcionales:

Los complementos de Excel, como las Herramientas para análisis y Solver, están disponibles cuando se instala Microsoft Office o Excel.

Se pueden descargar complementos adicionales de Excel desde Descargas en Microsoft Office Online. Los programadores y los proveedores de soluciones generalmente diseñan complementos COM personalizados

y complementos de automatización.

Debe cargar un complemento de Excel para poder utilizarlo. Tras cargar un complemento, el programa y sus comandos están disponibles en la ficha Datos, en el grupo Análisis (Herramientas para análisis y programa de complemento Solver), o en la ficha Fórmulas, en el grupo Soluciones (Asistente para suma condicional, Herramientas para el euro, Eurotool, Asistente para búsquedas).

Cargar o descargar un programa de complemento de Excel

1. Haga clic en el Botón Microsoft Office y, a continuación, haga clic en Opciones de Excel. 2. Haga clic en la categoría Complementos. 3. En el cuadro Administrar, haga clic en Complementos de Excel y, a continuación, en Ir. 4. Para cargar un complemento de Excel, haga lo siguiente:

En el cuadro Complementos disponibles, active la casilla de verificación situada junto al complemento que desea cargar y, a continuación, haga clic en Aceptar.

Si el complemento que desea utilizar no aparece en la lista del cuadro Complementos disponibles, haga clic en Examinar y, a continuación, busque el complemento.


SUBTOTALESMicrosoft Excel puede calcular automáticamente valores de subtotales y de totales generales en una lista. Cuando se insertan subtotales automáticos, 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. Después pueden calcularse los subtotales de cualquier columna que contenga números. Si los datos no están organizados en una lista, o si sólo necesita un total, puede utilizar Autosuma en lugar de subtotales automáticos.

Pasos para obtener Subtotales:1. Debe de ordenar la tabla por la columna a obtener el subtotal.2. Seleccionar toda la tabla incluyendo las etiquetas.3. Clic en Menú Datos\Esquema\Subtotal.4. En la opción para cada cambio en se elige la columna ordenada.5. Luego se elije el tipo de función a usar.6. Se elige la o las columnas a calcular.7. Clic en Aceptar.

Pasos para eliminar los Subtotales1. Ubicar el cursor dentro de la tabla que tiene subtotales2. Clic en Menú Datos\Esquema\Subtotal.3. Clic botón Quitar todos.

CONTROLES DEL PROGRAMADORMicrosoft Excel proporciona varios controles para las hojas de diálogo. Puede utilizar estos controles en hojas de cálculo para ayudar a seleccionar los datos. Por ejemplo, los cuadros desplegables, cuadros de lista, controles de número y barras de desplazamiento son útiles para seleccionar los elementos de una lista de datos.

Función Indice(matriz; num_fila; num_columna)

Si agrega un control a una hoja de cálculo y lo vincula a una celda, puede devolver un valor numérico para la posición actual del control.

Matriz= Datos de la tabla, no se toman en cuenta las etiquetas de la tabla.Num_fila= Número de la fila que quiere que cambie.Num_columna= Número de la columna que quiere que cambie.

Pasos para habilitar la Ficha del Programador (Para utilizar los controles de formularios)1. Clic en el Botón de Microsoft Office2. Clic en Opciones de Excel. 3. En Más Frecuentes, haga clic en la casilla Mostrar ficha Programador en la cinta para activarla4. Clic en Aceptar.

Pasos para Crear la macro (acción o conjunto de acciones utilizados para automatizar tareas. Las macros se graban en el lenguaje de programación de Visual Basic para Aplicaciones)

1. Clic Menú Vista\Macro\Grabar Macro2. Digitar el nombre de la macro.3. Guardarla en este libro.4. Digitar la descripción de la macro.5. Clic en Aceptar.6. Hacer lo que deseamos que la macro guarde (si es un formato no podemos cambiarnos de celda)7. Clic Menú Vista\Macro\Detener Macro

Pasos para agregar un Botón (permite agregar un botón al cual se le agregará una macro para su funcionamiento)1. Dibujamos el botón.2. Seleccionamos la macro3. Aceptar.4. Si la macro no está lista, cerramos la ventana, creamos la macro y luego5. Clic derecho sobre el botón6. Asignar macro.


7. Seleccionar la macro.

Pasos para agregar un Cuadro Combinado (Permite mostrar la lista de elementos)1. Dibujamos el cuadro combinado.2. Clic derecho sobre el cuadro combinado.3. Clic en Formato del control4. Clic Etiqueta Control5. Rango de entrada: se selecciona la lista con los valores que deseamos mostrar.6. Vincular con la celda a: seleccionar una celda que maneje la posición del valor a mostrar.7. Líneas de unión verticales: indicar cuantos valores queremos mostrar.8. Clic en Cuadrito Sombreado en 3D para que se vea bonito.9. Clic en Aceptar.

Pasos para crear un Control de Número (permite aumentar o disminuir una posición, es muy útil cuando se usa la función Índice)

1. Dibujamos el Control de número.2. Clic derecho sobre el control de número.3. Clic en Formato del control4. Clic Etiqueta Control5. Valor actual: 16. Valor mínimo: 17. Valor máximo: Cantidad total de datos.8. Incremento: 19. Vincular con la celda a: seleccionar una celda que maneje la posición del valor a mostrar.10. Clic en Cuadrito Sombreado en 3D para que se vea bonito.11. Clic en Aceptar.

Pasos para agregar un Cuadro de lista (Permite mostrar la lista de elementos de una forma desplegada)1. Dibujamos el cuadro de lista.2. Clic derecho sobre el cuadro de lista.3. Clic en Formato del control4. Clic Etiqueta Control5. Rango de entrada: se selecciona la lista con los valores que deseamos mostrar.6. Vincular con la celda a: seleccionar una celda que maneje la posición del valor a mostrar.7. Tipo de selección: Simple8. Clic en Cuadrito Sombreado en 3D para que se vea bonito.9. Clic en Aceptar.

Pasos para crear una Barra de desplazamiento (permite desplazarse entre los elementos)1. Dibujamos la barra de desplazamiento.2. Clic derecho sobre la barra de desplazamiento.3. Clic en Formato del control4. Clic Etiqueta Control5. Valor actual: 16. Valor mínimo: 17. Valor máximo: Cantidad total de datos.8. Incremento: 19. Cambio de página: se puede dejar con el valor que tiene.10. Vincular con la celda a: seleccionar una celda que maneje la posición del valor a mostrar.11. Clic en Cuadrito Sombreado en 3D para que se vea bonito.12. Clic en Aceptar.

Pasos para crear una Casilla de Verificación (Permite activar o desactivar una opción, devuelve un Verdadero con la opción activada y un False con la opción desactivada)

1. Dibujamos la o las Casillas de verificación necesarias.2. Clic derecho sobre la casilla de verificación.3. Clic en Formato del control4. Clic Etiqueta Control


5. Valor: Sin activar6. Vincular con la celda a: seleccionar una celda que maneje la posición del valor a mostrar.7. Clic en Cuadrito Sombreado en 3D para que se vea bonito.8. Clic en Aceptar.9. Se repite todo para cada una de las casillas usadas

Nota: El resultado de este control se puede usar con funciones lógicas como la del SI para obtener otro resultado.

Pasos para crear un Botón de Opción (Permite elegir una opción a la vez, devuelve un 1 como valor activado y un 0 como valor desactivado)

1. Dibujamos el o los Botones de opción necesarios.2. Clic derecho sobre el botón de opción.3. Clic en Formato del control4. Clic Etiqueta Control5. Valor: Sin activar6. Vincular con la celda a: seleccionar una celda que maneje la posición del valor a mostrar.7. Clic en Cuadrito Sombreado en 3D para que se vea bonito.8. Clic en Aceptar.

Nota: El resultado de este control se puede usar con funciones lógicas como la del SI para obtener otro resultado.

MODIFICAR DATOS DE UNA TABLAPara modificar o introducir nuevos datos en la tabla podemos teclear directamente los nuevos valores sobre la ella, o bien podemos utilizar un formulario de datos. Esta segunda opción viene muy bien sobre todo si la lista es muy grande.

Un formulario de datos: es un cuadro de diálogo que permite al usuario escribir o mostrar con facilidad una fila entera de datos (un registro).

A continuación se presenta una tabla simple para el ingreso de información

Para abrir el formulario de datos, tenemos que posicionarnos en la lista para que esté activa, y pulsar en el icono Formulario .

Como esta opción no está directamente disponible en la Cinta de opciones, podemos añadirla a la Barra de acceso rápido, de la forma que ya vimos. Pulsando el Botón Office > Opciones de Excel > Personalizar, y Agregar el icono Formulario..., en la sección de Comandos que no están en la cinta de opciones.

Al crear el formulario, disponemos de siguientes botones:Nuevo: Sirve para introducir un nuevo registro.Eliminar: Eliminar el registro que está activo. Restaurar: Deshace los cambios efectuados. Buscar anterior: Se desplaza al registro anterior. Buscar siguiente: Se desplaza al siguiente registro. Criterios: Sirve para aplicar un filtro de búsqueda.Cerrar: Cierra el formulario.


TABLAS DINÁMICASUna tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc...

Para crear una tabla dinámica, Excel nos proporciona las tablas y gráficos dinámicos.

Aparece el cuadro de diálogo de creación de tablas dinámicas. Desde aquí podremos indicar el lugar donde se encuentran los datos que queremos analizar y el lugar donde queremos ubicarla.

En nuestro caso indicamos que vamos a seleccionar los datos de un rango de celdas y que queremos ubicarla en una hoja de cálculo nueva.

Podríamos crear una conexión con otra aplicación para obtener los datos desde otra fuente diferente a Excel.

En el caso de seleccionar la opción Selecciona una tabla o rango debemos seleccionar todas las celdas que vayan a participar, incluyendo las cabeceras.

Clic en Aceptar para seguir.

Se abrirá un nuevo panel en la derecha de la pantalla:

9Excel Avanzado 2007Obtener otro tipo de cálculo en una tabla dinámica Por defecto, al crear una tabla dinámica, Excel nos genera unos totales con sumatorio, puede interesarnos modificar esas fórmulas por otras como pueden ser sacar el máximo o el mínimo, el promedio, etc.

Para hacer esto debemos situarnos en cualquier celda de la zona que queremos rectificar y hacer clic con el botón derecho del ratón, nos aparece un menú emergente con diferentes opciones, debemos escoger la opción Configuración de campo de valor... y nos aparece un cuadro de diálogo como el que vemos en la imagen.

En este cuadro de diálogo podemos escoger cómo queremos hacer el resumen, mediante Suma, Cuenta, Promedio, etc.

También podemos abrir el cuadro de diálogo con el botón de la pestaña Opciones.

Gráficos con tablas dinámicas

Para crear una gráfica de nuestra tabla dinámica deberemos hacer clic en el botón Gráfico dinámico de la pestaña Opciones.

Al pulsar este botón se abrirá el cuadro de diálogo de Insertar gráfico, allí deberemos escoger el gráfico que más nos convenga (porcentual o comparativo).

Luego, la mecánica a seguir para trabajar con el gráfico es la misma que en el tema de gráficos de Excel básico.

Para todo tipo de gráfico

1. Menú Diseño\Diseño Rápido2. Elegir el diseño para agregar el título.3. Digitar el título del gráfico.

Para un gráfico circular

4. Seguir pasos anteriores.5. Menú Presentación\Etiquetas de datos.6. Elegir la forma para mostrar los valores.


DESARROLLO DE SOLUCIONES CON VISUAL BASIC PARA APLICACIONES

En la ficha Programador, haga clic en Visual Basic para iniciar el trabajo de aplicaciones por medio de código programado.Seleccione la opción Visual Basic

Una vez abierto el editor de Visual Basic se debe insertar un módulo de trabajo que es donde se almacena el código de las funciones o procedimientos de las macros. Para insertar un módulo accedemos al menú Insertar → Módulo.A continuación debemos plantearnos si lo que vamos a crear es una función o si por el contrario es un procedimiento.

Ejemplos de definiciones de funciones en Visual BasicEjemplo sin parámetros de entrada

Ejemplo

con parámetros de entrada

Algunos Tipos de Datos Integer = Entero (-32.768 hasta 32.767) Boolean=Lógico (verdadero o false)Double=Número (decimales largos) String= Texto Largo Date=Fecha Long=Número enteros largos (-2.147.483.648 hasta 2.147.483.647)

Public Function prueba()

prueba = 1 + 2

End Function

Una vez concretado que es lo que vamos a crear, accedemos al menú Insertar → Función

Una vez seleccionado el tipo de procedimiento y el ámbito presionamos sobre Aceptar y se abre el editor de Visual Basic donde escribimos las instrucciones necesarias para definir la macro.

Public Function prueba(num1 As Integer, num2 As Integer)

prueba = num1 + num2

End Function

Devuelve el resultado de sumar

1 + 2 = 3

Devuelve el resultado de sumar un número cualquiera + otro número cualquiera, siempre y cuando sean enteros

manual excel avanzado

Education