manual excel avanzado agosto 2013

123
Excel Avanzado Página 1 EXCEL NIVEL AVANZADO Manual de Referencia Rápida. Marzo del 2013

Upload: guillermo-salazar-mancera

Post on 28-Oct-2015

216 views

Category:

Documents


2 download

TRANSCRIPT

Page 1: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 1

EXCEL NIVEL AVANZADOManual de Referencia Rápida.

Marzo del 2013

Page 2: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 2

MANUAL DE EXCEL 2007.

NIVEL AVANZADO

El presente manual tiene como objetivo guiar al usuario en el

manejo de Excel 2007, haciendo referencia a Herramientas Avanzadas

para el manejo de grandes volúmenes de información como lo son,

funciones y procedimientos de análisis; así como la automatización de

procesos con Macros.

El manual fue desarrollado por los Profesores del Departamento

de Capacitación del Hospital Español, bajo la supervisión de la Lic.

Elvira Diz.

Page 3: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 3

SOCIEDAD DE BENEFICENCIA ESPAÑOLA, I.A.P.

DEPARTAMENTO DE CAPACITACIÓN

NOMBRE:

EQUIPO:

Page 4: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 4

CONTENIDO TEMÁTICO

TABLAS Y GRÁFICOS DINÁMICOS 5

MACROS EN EXCEL 30

CUADROS DE CONTROL – ACTIVE X 45

CREANDO FORMULARIOS Y PROGRAMÁNDOLOS 53

FUNCIONES DE BASE DE DATOS 69

FUNCIONES FINANCIERAS 71

ESCENARIOS – ANÁLISIS Y SI 73

BUSCAR OBJETIVO 75

SOLVER 78

Page 5: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 5

TABLAS DINÁMICAS

Ficha insertar grupo tablas

Los informes de tablas dinámicas permiten al usuario resumir la información por categorías y subcategorías, totalizar los valores para facilitar la toma de decisiones y analizar los datos desde diferentes puntos de vista, permiten manipular grandes cantidades de datos y agregar nuevos campos con fórmulas personalizadas. Es muy ágil el manejo de los formatos para presentar comparaciones, facilitan la organización y el resumen de datos en bases de datos.

COMPONENTES DE LAS TABLAS DINÁMICAS

Campos de filas: Son los campos de base de datos ubicados en el área de Fila, que representan las categorías por las cuales se resume la información.

Campos de columnas: Son los campos de la base de datos ubicados en el área de Columnas, que representan las categorías por las cuales se resume la información

Campos de página: Son los campos de la base de datos ubicados en el área de Página, que permiten mostrar los registros que cumplen un determinado criterio, muy similar al manejo de autofiltros.

Campo de datos: Es el campo de la base de datos ubicado en el área de Datos que se va a totalizar, con las funciones para realizar totales. Normalmente, un campo de datos resume datos numéricos, pero también puede contener texto. Los valores numéricos de forma predeterminada utilizan la función de SUMA y los valores texto tienen como función predeterminada CONTAR.

Page 6: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 6

Elementos calculados: Son aquellas subcategorías de un campo en el área de filas o columnas basadas en fórmulas creadas por los usuarios.

Campos calculados: Corresponden a los campos que se crean en el área de datos con fórmulas creadas por el usuario.

OBJETIVO DE UNA TABLA DINÁMICA

El objetivo de la tabla dinámica es aplicar herramientas de análisis para la toma de decisiones, (costos, presupuestos, inventarios, ventas) en bases de datos robustas, grandes o complejas donde la manipulación y administración de la información es compleja.

CREAR UNA TABLA DINÁMICA

Al crear un informe de tabla o gráfico dinámico, se conecta con un origen de datos y luego se especifica la ubicación del informe.

1. Seleccionar una celda de la base de datos.

2. En la ficha Insertar, en el grupo Tablas, hacer clic en Tabla dinámica y, a continuación, en Tabla dinámica.

3. En el cuadro Seleccione una tabla o un rango; si estaba ubicado en una celda de la base de datos, el rango se visualiza en este cuadro, si la ubicación es una celda vacía se debe seleccionar el rango de celdas o la referencia del nombre de tabla.

Page 7: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 7

4. Si los datos de origen se encuentran en otro archivo en una fuente de conexión externa:

Hacer clic en Elegir conexión.

Se mostrará el cuadro de diálogo Conexiones existentes.

Seleccionar en la lista desplegable Mostrar, de la parte superior del cuadro de diálogo una de las categorías de conexiones existentes o, Todas las conexiones existentes (que es el valor predeterminado).

Seleccionar una conexión del cuadro de lista Seleccionar una conexión y hacer clic en Abrir.

Page 8: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 8

5. En la parte inferior permite activar la ubicación de la tabla dinámica.

Para poner el informe de tabla dinámica en una hoja de cálculo nueva que empiece por la celda A1, hacer clic en Nueva hoja de cálculo.

Para poner el informe de tabla dinámica en una hoja de cálculo existente, seleccionar Hoja de cálculo existente y, a continuación, escribir la primera celda del rango de celdas donde se desea situar el informe de tabla dinámica.

6. Hacer clic en Aceptar.

Excel crea una tabla dinámica en blanco para insertar los campos en las diferentes ubicaciones.

Para ubicar los campos en la tabla dinámica se debe arrastrar los Encabezados de las columnas situados en la Lista de Campos de tabla dinámica con clic sostenido al área de diseño donde aparecen los campos de rótulos de fila, columna, o datos, dependiendo de la información requerida.

Ejemplo:

1. Del cuadro lista de campos, arrastrar el campo artículos a rótulos de fila parte inferior.

2. Del cuadro lista de campos, arrastrar el campo Ciudad a rótulos de columna.

Page 9: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 9

3. Del cuadro lista de campos, arrastrar el campo cantidad a valores.

En la Lista de Campos el botón permite cambiar la forma de visualización de los campos y las áreas de la tabla dinámica.

Para organizar los campos una vez elaborada la tabla dinámica se puede dar clic derecho en el nombre de campo en una de las áreas y, a continuación, seleccionar la opción Mover y elegir una de las opciones siguientes:

MOVER CAMPOS EN UNA TABLA DINÁMICA

Subir Sube el campo una posición en el área.

Bajar Baja el campo una posición en el área.

Mover al principio Mueve el campo al principio del área.

Mover al final Mueve el campo al final del área.

Mover al filtro de informe Mueve el campo al área de filtro de informe.

Page 10: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 10

MOVER CAMPOS EN UNA TABLA DINÁMICA

Mover a rótulos de fila Mueve el campo al área de etiquetas de fila.

Mover a rótulos de Mueve el campo al área de etiquetas decolumna columna.

Mover a valores Mueve el campo al área de valores.

CONFIGURACIÓN DE CAMPO

Para cambiar el nombre de un campo se debe situar sobre el área de diseño y en la celda del título del campo, ubicarse en la barra de fórmulas y escribir el nuevo nombre o en la ficha Opciones, en el grupo Campo activo, hacer clic en el cuadro de texto Campo activo, escribir el nuevo nombre.

Además se puede modificar el nombre situándose en la celda del nombre del campo y con clic derecho seleccionar la opción Configuración de campo; esta posibilidad permite controlar las diversas configuraciones de formato, impresión, subtotales y filtros de los campos de un informe de tabla dinámica. Así mismo obtendremos el cuadro Configuración de campo en la ficha Opciones, en el grupo Campo activo al dar doble clic sobre un nombre de un campo.

El cuadro Configuración de campo cambia su aspecto dependiendo de la ubicación del campo en el diseño de la tabla dinámica.

Para campos de Fila, Columna o Página:

Nombre personalizado: Muestra el nombre del campo actual en el informe de tabla dinámica, o el nombre del origen si no hay ningún nombre personalizado

Subtotales

Automáticos: Utiliza la función predeterminada para el campo.

Ninguno: Muestra el campo sin ningún subtotal.

Personalizados: Habilita la selección de una de las funciones siguientes como subtotal.

Page 11: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 11

FUNCIÓN DESCRIPCIÓN

SUMA La suma de los valores. Es la función predeterminada de los valores numéricos.

CONTAR Cuenta las celdas que tengan un valor ya sea numérico o texto

PROMEDIO El promedio de los valores.

MÁX El valor máximo.

MÍN El valor mínimo.

PRODUCTO El producto de los valores.

CONTAR Cuenta las celdas que tengan un valor numérico.NÚMEROS

DESVEST Un cálculo de la desviación estándar de una población, donde la muestra es un subconjunto de toda la población.

Filtros:

Incluir nuevos elementos en el filtro manual:

Activar o desactivar esta casilla de verificación para incluir o excluir nuevos elementos en un informe de tabla dinámica con un filtro aplicado.

Diseño e impresión

Mostrar etiquetas de elementos en formato de esquema: Activar esta casilla de verificación para ver los elementos de los campos en formato de esquema.

Page 12: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 12

Mostrar elementos del campo siguiente en la misma columna (forma compacta): Activar o desactivar esta casilla de verificación para mostrar u ocultar los rótulos del campo siguiente en la misma columna de forma compacta.

Mostrar subtotales en la parte superior de cada grupo: Si se habilita esta casilla de verificación permite mostrar los subtotales en la parte superior de cada grupo.

Mostrar etiquetas de elementos en formato tabular: Active esta casilla de verificación para ver los elementos de los campos en formato tabular. Esta configuración sólo afecta a los campos situados en el área de rótulos de fila.

Insertar línea en blanco después de cada elemento: Activar esta casilla de verificación para insertar una línea en blanco a continuación de cada elemento y aumentar el espaciado de elementos como los subtotales.

Mostrar elementos sin datos: Al activar esta casilla de verificación muestra los elementos que no contengan datos.

Para campos de Valores

Nombre de origen:

Indicar el nombre del campo en el origen de datos.

Nombre personalizado:

Muestra el nombre del campo actual en el informe de tabla dinámica, o el nombre del origen si no hay ningún nombre personalizado.

Resumir Por muestra:Representa la función utilizada para el resumen de los datos en los campos de valor, si se desea modificar, se debe seleccionar nuevamente la función.

Page 13: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 13

D E FIN IR CAMP O S E N T É RM INO S D E %

FUNCIÓN RESULTADO

DIFERENCIA DE Muestra los valores como la diferencia del valor de

Elemento base en Campo base.

% DE Muestra los valores como un porcentaje del valor de

Elemento base en Campo base.

% DE LA Muestra los valores como la diferencia de porcentaje

DIFERENCIA DE del valor de Elemento base en Campo base.

TOTAL EN Muestra los valores de elementos sucesivos en

Campo base como un total.

% DE LA FILA Muestra el valor de cada fila o categoría como un porcentaje del total de la fila o categoría.

% DE LA Muestra todos los valores de cada columna ó serie

COLUMNA como un porcentaje del total de la columna ó serie.

Muestra los valores como un porcentaje del total% DEL TOTAL general de todos los valores o puntos de datos en el

informe.

ÍNDICE Calcula los valores como se indica a continuación:

Función Resultado

((valor en celda) x (Suma total de sumas totales)) / ((Suma total de fila) x (Suma total de columna))

Page 14: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 14

Ejercicio:Determinar en términos de %, las ventas por ciudad, para cada artículo.

1. En la tabla dinámica sobre el campo cantidad que se encuentra en la sección valor, hacer clic sobre la flecha desplegable.

2. Del menú contextual seleccionar configuración del campo valor.

3. En la ventana configuración del campo valor, hacer clic sobre la pestaña mostrar valores como.

4. De la lista desplegableseleccionar % de la fila.

5. Hacer clic sobre el botón Aceptar.

De esta forma la tabla dinámica esta en términos de porcentaje (%) determinando que del 100% de los discos duros, en Bogotá se ha vendido el 44.44%

Page 15: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 15

Agrupar y desagrupar datos

Esta opción le permite agrupar por números o textos una sección de la tabla dinámica.

Desagrupar celdas

1. Seleccionar todo el rango de celdas o clic en el rótulo del campo artículos2.

2. Hacer clic derecho del mouse y seleccionar del menú desagrupar o en la ficha Opciones dar clic en Desagrupar que se encuentra en el grupo de opciones Agrupar.

Se pueden agrupar los elementos de los campos para generar nuevos subconjuntos de valores de resumen adicionales. Los niveles de agrupamiento varían de acuerdo con el tipo de datos que se utiliza:

Page 16: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 16

Agrupar elementos numéricos

1. Seleccionar el campo numérico en el informe de tabla de dinámica que se desea agrupar.

2. En la ficha Opciones, en el grupo Agrupar, hacer clic en Agrupar campos.

3. En el cuadro Comenzar en, escribir el primer elemento que se desee agrupar.

4. En el cuadro Terminar en, escribir el último elemento que se desee agrupar.

5. En el cuadro Por, escribir un número que represente el intervalo incluido en cada grupo.

Agrupar fechas u horas

1. Seleccionar el campo de fecha u hora en el informe de tabla dinámica que se desea agrupar.

2. En la ficha Opciones, en el grupo Agrupar, hacer clic en Agrupar campos.

3. Escribir la primera hora o fecha que se desee agrupar en el cuadroComenzar en y la última en el cuadro Terminar en.

4. En el cuadro Por, hacer clic en uno o más períodos de tiempo para los grupos. La opción de días en la parte inferior permite determinar el Número exacto.

Page 17: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 17

AGRUPAR ELEMENTOS SELECCIONADOS

Seleccionar los elementos del informe de tabla dinámica que se deseen agrupar; para ello, hacer clic en los elementos y arrastrarlos o presionar la tecla <Ctrl> o MAYÚS mientras se hace clic en los elementos.

En la ficha Opciones, en el grupo Agrupar, hacer clic en Agrupar selección.

Ejemplo

1. En la tabla dinámica seleccionar el rango de celdas que corresponde a memorias.

2. Hacer clic derecho del mouse y seleccionar Agrupar, o en la ficha

Opciones dar clic en Agrupar selección que se encuentra en el grupo de opciones Agrupar.

Page 18: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 18

3. Se crea un nuevo campo a la izquierda llamado artículos2, y un grupo llamado Grupo1. Cambiar el rótulo artículos2 por Tipo Artículo, y el rótulo Grupo 1 por Memorias simplemente ubicando el cursor en la celda con el rótulo y sobrescribir.

Desagrupar elementos

Seleccionar los desagrupar.

elementos que se deseen

En la ficha Opciones, en el grupo Agrupar, hacer clic en

Desagrupar. También estas opciones de agrupamiento se

pueden realizar dando clicderecho sobre el campo a agrupar o desagrupar.

Page 19: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 19

MOSTRAR U OCULTAR LOS

DETALLES DE UN INFORME

Cuando existen diferentes categorías en los campos de fila o en los campos de columna, es posible expandir o contraer hasta cualquier nivel de detalle de los datos, e incluso todos los niveles en una sola operación. Si solo se tiene un campo, la opción Expandir, permite activar nuevos campos en el área correspondiente.

Para expandir o contraer un campo se puede ubicar en la Ficha Opciones de Tablas Dinámicas, en el Grupo Campo Activo y seleccionar la opción Expandir todo el Campo o Contraer todo el campo según corresponda. También es viable si se da doble clic sobre la categoría del elemento situado en el área de fila o columna o utilizando el clic derecho del mouse sobre el elemento y seleccionar la opción Expandir/Contraer con las siguientes alternativas:

Para ver los detalles del elemento actual, hacer clic en

Expandir. Para ocultar los detalles del elemento actual,

hacer clic en Contraer.

Para ver los detalles de todos los elementos de un campo, hacer clic enExpandir todo el campo.

Para ocultar los detalles de todos los elementos de un campo, hacer clic en Contraer todo el campo.

Para ver un nivel de detalle posterior al siguiente nivel, hacer clic enExpandir hasta "<nombre del campo>".

Para ocultar un nivel de detalle posterior al siguiente nivel, hacer clic enOcultar hasta "<nombre del campo>".

Page 20: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 20

FORMATOS DE TABLA

DINÁMICA

Si se desea aplicar un formato a la tabla dinámica, estando en una celda del área del diseño, hacer clic en la ficha Inicio, del grupo Estilos y en la opción Dar formato como tabla y seleccionar un modelo.

Para dar un formato al área de valores se puede utilizar el cuadro Configuración de Campo en la opción Formato de Número, si se está ubicado en una celda de esta área, o también es posible seleccionar las celdas a formatear y en la ficha Inicio en el grupo Estilos, seleccionar la opción Estilos de Celdas.

Page 21: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 21

ORDENAR CAMPOS EN TABLAS DINÁMICAS

La opción ordenar, permite organizar la información de acuerdo al tipo de datos del campo, ubicado en cualquier área de la tabla dinámica.

Así mismo la opción Ordenar activa un cuadro de diálogo que permite organizar los elementos de un nivel en una forma que sean fáciles de entender.

En la ficha Opciones, en el grupo Ordenar, realizar uno de los siguientes procedimientos:

Se debe ubicar sobre el área a organizar, luego dar clic en los

íconos Orden ascendente u Orden descendente para ordenar un campo de la tabla dinámica. De acuerdo al tipo de datos se organizan los datos en la tabla.

Para organizar también se puede utilizar la ficha Opciones, en el grupo Ordenar, para esto se deben realizar uno de los siguientes procedimientos:

Si el campo es de tipo Texto

Para ordenar en orden alfanumérico ascendente, hacer clic enOrdenar de A a Z.Para ordenar en orden alfanumérico descendente, hacer clic enOrdenar de Z a A.

Si el campo es de tipo Numérico

Para ordenar en orden alfanumérico ascendente, hacer clic enOrdenar de menor a mayor.Para ordenar en orden alfanumérico descendente, hacer clic enOrdenar de mayor a menor.

Page 22: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 22

Si el campo es Fechas y Horas

Para ordenar en orden alfanumérico ascendente, hacer clic enOrdenar de más antiguos a más recientes.Para ordenar en orden alfanumérico ascendente, hacer clic enOrdenar de más recientes a más antiguos.

ACTUALIZAR TABLA DINÁMICA

Esta opción permite actualizar el contenido de la tabla dinámica para que refleje los cambios en los datos de origen correspondiente. Si la tabla dinámica está basada en datos externos, al actualizarla se ejecutará la consulta para recuperar los datos nuevos o los que hayan cambiado.

Con el teclado se pueden presionar las teclas <Alt> <F5> para actualizar los datos.

Page 23: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 23

CAMBIAR ORIGEN DE DATOS

Si se aumentan registros o eliminan registros en la base de datos que origina la tabla dinámica, se debe volver a seleccionar el origen de datos.

Para modificar el rango de origen de los datos se debe:

- En la ficha Opciones, en el grupo Datos, hacer clic en Cambiar origen de datos y luego en Cambiar origen de datos. Aparecerá el cuadro de diálogo Cambiar origen de datos de tabla dinámica.

Para usar una tabla o rango de celdas diferente de Microsoft Office Excel, hacer clic en Seleccione una tabla o rango y luego especificar la primera celda en el cuadro de texto Tabla o rango.

- Para usar una conexión diferente, hacer clic en Utilice una fuente de datos externa y luego en Elegir conexión.

- Se mostrará el cuadro de diálogo Conexiones existentes.

- En la lista desplegable Mostrar, situada en la parte superior del cuadro de diálogo, seleccionar la categoría de conexiones para la que desea elegir una conexión o seleccionar Todas las conexiones existentes (opción predeterminada).

- Seleccionar una conexión del cuadro de lista Seleccione una conexión y, a continuación, hacer clic en Abrir.

Page 24: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 24

ACCIONES EN LA TABLA DINÁMICA

Si se requiere quitar todos los campos ubicados en el diseño de la tabla dinámica, se debe dar clic en la ficha Opciones, en el grupo Acciones, en la opción Borrar Todo. Si en el campo de página hay filtros activos la opción Borrar Filtros del Grupo Acciones en la ficha Opciones, quita los filtros mostrando nuevamente todos los registros en el informe de tabla dinámica.

Para seleccionar información de una tabla dinámica se debe:

Situar el puntero del mouse en la esquina superior del campo hasta que cambie a una flecha descendente y, a continuación, hacer clic.

En la ficha Opciones, en el grupo Acciones, hacer clic en Seleccionar y, a continuación, hacer clic en lo que se desee seleccionar:

Etiquetas y datos para seleccionar ambos.

Datos para seleccionar sólo los valores de los elementos.

Etiquetas para seleccionar sólo los rótulos de los elementos.

Si se desea cambiar la posición de la tabla dinámica se debe activar la Ficha Opciones en el Grupo Acciones y opción Mover Tabla dinámica, donde en el cuadro de diálogo se puede cambiar la celda de inicio de la tabla dinámica si se quiere ubicar en la misma hoja, o seleccionar la opción Hoja nueva, para insertar una nueva hoja con la tabla dinámica.

Page 25: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 25

ELEMENTOS Y CAMPOS CALCULADOS EN TABLAS DINÁMICAS

Elemento Calculado: Es una categoría adicional de un campo de una tabla dinámica, situado en el área de filas o columnas que utiliza una fórmula que haya creado el usuario.

Campos Calculados: Es un campo ubicado en el área de datos en la tabla dinámica que utiliza una fórmula que se haya creado. Los campos calculados pueden ejecutar cálculos utilizando el contenido de otros campos de la tabla dinámica.

Para crear un elemento calculado a un campo:

1. Si los elementos del campo están agrupados, en la fichaOpciones, en el grupo Agrupar, hacer clic en Desagrupar.

2. Hacer clic en el campo donde se desee agregar el elemento calculado.3. En la ficha Opciones, en el grupo Herramientas, hacer clic en

Fórmulas y, seleccionar la opción Elemento calculado.4. En el cuadro Nombre, escribir un nombre para el elemento calculado.

5. En el cuadro Fórmula, escribir la fórmula para el elemento, en la cual se pueden utilizar operadores y expresiones como en la hoja de cálculo iniciando con el =. Pueden utilizarse constantes y hacer referencia a los datos de la tabla dinámica (elementos), pero no pueden utilizarse referencias de celda o nombres, posteriormente hacer clic en Agregar.

Page 26: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 26

Para crear un campo calculado a un campo:

1. Hacer clic sobre un campo en el área de datos del informe de tabla dinámica.

2. En la ficha Opciones, en el grupo Herramientas, hacer clic en

Fórmulas y, a continuación, hacer clic en Campo calculado.

2. En el cuadro Nombre, escribir un nombre para el campo.

3. En el cuadro Fórmula, escribir la fórmula para el campo, en las cuales se pueden utilizar operadores y expresiones como en la hoja de cálculo iniciando con el =. Pueden utilizarse constantes y hacer referencia a los datos de la tabla dinámica (elementos), pero no pueden utilizarse referencias de celda ó nombres.

5. Hacer clic en Agregar.

Para modificar un elemento o campo calculado, hacer clic en Fórmulas que se encuentra en el grupo Herramientas de la ficha Opciones, a continuación, hacer clic en Elemento o Campo calculado, en el cuadro nombre seleccionar de la lista el nombre del campo a modificar y luego en el campo Fórmula cambiar la fórmula.

Page 27: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 27

Si se tienen varios elementos o fórmulas calculados, ajustar el orden de cálculo haciendo lo siguiente:

1. En la ficha Opciones, en el grupo Herramientas, hacer clic en

Fórmulas y, a continuación, hacer clic en Orden de resolución.

2. Hacer clic en una fórmula y, posteriormente, hacer clic en Subiro en

Bajar.

Para mostrar una lista de todas las fórmulas utilizadas en el informe de tabla dinámica actual.

En la ficha Opciones, en el grupo Herramientas, hacer clic enFórmulas y, a continuación, hacer clic en Crear lista de fórmulas.

En Opciones de Tabla dinámica el último grupo Mostrar u Ocultar permite activar o desactivar la lista de campos, los botones para expandir o contraer elementos y los rótulos de los campos.

GENERAR UN GRÁFICO DINÁMICO

Los gráficos dinámicos se pueden basar sobre tablas dinámicas para observar los informes de una manera diferente, más clara y concisa.

1. Ubicarse en la base de datos.

2. En la ficha insertar, en el grupo tablas, seleccionar de la flecha desplegable tabla dinámica, gráfico dinámico.

Page 28: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 28

3. Aparecerá una ventana en la cual se puede seleccionar el origen de lo que se desea representar o bien de una base dedatos externa, seleccionar el botón de opción.

Seleccionar una tabla o rango y el botón de opción nueva hoja de cálculo y hacer clic sobre el botón Aceptar.

Page 29: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 29

4. Arrastrar el campo cantidad al área de valores.5. Arrastrar el campo artículos al área de campos de ejes x.

Page 30: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 30

MACROS EN EXCEL

Al trabajar con un libro personalizado, es decir, que nos hemos definido con una serie de características específicas como puedan ser el tipo de letra, el color de ciertas celdas, los formatos de los cálculos y características similares, perdemos mucho tiempo en formatear todo el libro si disponemos de muchas hojas.

Con las macros lo que se pretende es automatizar varias tareas y fusionarlas en una sola, añadiendo por ejemplo un botón en nuestro libro que al pulsar sobre él realice todas esas tareas.

GRABAR UNA MACRO

La forma más fácil e intuitiva de crear macros es crearlas mediante la grabadora de macros del que dispone Excel.

Esta grabadora de macros te permite grabar las acciones deseadas que posteriormente las traduce a instrucciones en VBA, las cuales podemos modificar posteriormente si tenemos conocimientos de programación.

Para grabar una macro debemos acceder a la Ficha Vista y despliega el submenú Macros y dentro de este submenú seleccionar la opción Grabar macro... Además de esta opción en el menú podemos encontrar las siguientes opciones:

Ver Macros... Donde accedemos a un listado de las macros creadas en ese libro.

Usar referencias relativas - Con esta opción utilizaremos referencias relativas para que las macros se graben con acciones relativas a la celda inicial seleccionada. Antes de realizar una Macro es muy importante hablar sobre sus seguridades.

Page 31: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 31

Se puede grabar las macros desde la Ficha Programador, si no está disponible, haga lo siguiente para mostrarla:

1. Haga clic en el Botón Microsoft Office y, a continuación,

2.

haga clic en Opciones de Excel.

Opción Más frecuente

3. Active con un visto la opción

Como se visualiza:

4. De clic en el Botón Aceptar.

Se visualiza la pestaña Programador que contiene:

Page 32: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 32

La Ficha se compone de:

Grupo Código:

El grupo Controles

El grupo XML

El grupo Modificar

Page 33: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 33

SEGURIDAD EN MACROS

Para establecer el nivel de seguridad de manera que estén habilitadas temporalmente todas las macros, haga lo siguiente:

En la ficha Programador, en el grupo Código, haga clic enSeguridad de

macros. Se visualiza:

En Configuración de macros, haga clic en Habilitar todas las macros (no recomendado; puede ejecutarse código posiblemente peligroso) y, a continuación, haga clic en Aceptar.

Nota Para ayudar a evitar que se ejecute código potencialmente peligroso, recomendamos que vuelva a cualquiera de las configuraciones que deshabilitan todas las macros cuando termine de trabajar con las macros.

Page 34: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 34

En la ficha Programador, en el grupo Código, haga clic en Grabar macro. En el cuadro Nombre de la macro, escriba un nombre para la macro.

Nota El primer carácter del nombre de la macro debe ser una letra. Los caracteres siguientes pueden ser letras, números o caracteres de subrayado. No se permiten espacios en un nombre de macro, caracteres especiales ni palabras reservadas; puede utilizarse un carácter de subrayado como separador de palabras.

Si utiliza un nombre de macro que también es una referencia de celda, puede aparecer un mensaje indicando que el nombre de la macro no es válido.

Para asignar una combinación de tecla de método abreviado (método abreviado: tecla o combinación de teclas de función, como F5 o CTRL+a, que utiliza para ejecutar un comando. Una tecla de acceso, por lo contrario es un combinación de teclas, como ALT+f, que mueve el enfoque a un menú, comando o control.) Con CTRL para ejecutar la macro, en el cuadro Tecla de método abreviado, escriba cualquier letra en mayúsculas o minúsculas que desee utilizar.

Nota La tecla de método abreviado suplantará a cualquier tecla de método abreviado predeterminada equivalente en Excel mientras esté abierto el libro que contiene la macro.

En la lista Guardar macro en, seleccione el libro en el que desea almacenar la macro.

Sugerencia Si desea que la macro esté disponible siempre que utilice Excel, seleccione Libro de macros personal. Cuando se selecciona Libro de macros personal, Excel crea un libro oculto de macros personal (Personal.xlsb), si no existe todavía, y guarda la macro en este libro. En Microsoft Windows XP, este libro se guarda en la carpeta C:\Documents and Settings\nombre de usuario\Datos de programa\Microsoft\Excel\XLStart para que se pueda cargar automáticamente cada vez que se inicia Excel. En Microsoft Windows Vista, este libro se guarda en la carpeta C:\Usuarios\nombre de usuario\Datos de programa\Microsoft\Excel\XLStart.

Page 35: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 35

Si desea que se ejecute automáticamente una macro del libro de macros personal en otro libro, también debe guardar ese libro en la carpeta XLStart, de forma que ambos libros se abran cuando se inicie Excel.

1. Para incluir una descripción de la macro, escriba el texto que desee en el cuadro Descripción.

2. Haga clic en Aceptar para iniciar la grabación.

3. Realice las acciones que desee grabar.

4. En la ficha Programador, en el grupo Código, haga clic en Detener grabación.

Sugerencia También puede hacer clic en Detener grabación en el lado izquierdo de la barra de estado.

PRACTICA I

Genera las siguientes Macros:

Grabe una Macro que se active con Control + b y que esta macro permita abrir un archivo.

Grabe una Macro que inserte una tabla con datos. Grabe una Macro que abra un archivo existente. Grabe una Macro que abra un nuevo archivo. Grabe una Macro que inserte un logotipo.Grabe una Macro que ordene alfabéticamente una lista de nombres.

Grabe una Macro que imprima un formulario.

Nota: Recuerde que en la versión 2007 se debe guardar como un archivo de MS Excel habilitado para macros, es decir con la extensión .xlsm; caso contrario no guarda el código de las macros.

Page 36: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 36

CÓDIGOS DE UNA MACRO DE EXCEL

Para observar los códigos de una macro debemos seguir los pasos:

1. En primer lugar seleccione la celda B5 antes de empezar la grabación de la Macro, se visualiza:

2. Presione el Botón Grabar Macro del grupoCódigo MS Excel muestra el cuadro de Dialogo Grabar Macro:

3. Ingrese un nombre de la macro por ejemplo saludo

4. En la opción Método Abreviado escriba la letra s, por lo tanto la macro se llamara con Control + s

Page 37: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 37

5. En Guardar macro en: Seleccione en el lugar en donde desea guardar la macro, por ejemplo Este libro.

6. En Descripción puede agregar una descripción de lo que hace la macro, este punto es opcional. Solo le sirve para que usted recuerde acerca de lo que hace la macro, pues este código no es interpretado por el compilador.

7. Presione el botón Aceptar. Excel inicia la grabación del la Macro

8. Trasládese a la celda A1 y escriba Hola compañeros, después presione Enter para aceptar el valor en la celda.

9. Pare la grabación de la macro presionando el botón

Detener Grabación del grupo Código. Excel ha grabado los pasos y ha generado un código.

10.Para visualizar el código generado, presione la tecla Alt + la tecla de función

F11(Alt + F11), o de un clic derecho en la hoja de cálculo:

11. Seleccione la opción Ver código. También puede acceder al grupo Código, al dar clic en la opción Visual Basic.

Page 38: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 38

12. Excel nos traslada al Editor de Visual Basic. Se visualiza:

13. Active los siguientes cuadros o ventanas:

• De clic en el Menú Ver y elija la opción Explorador de Proyectos

• De clic en el Menú Ver y elija la opción Ventana Propiedades

14. Del cuadro Proyecto de doble clic en Módulos o simplemente presione el signo de + que aparece en la opción Módulos. Se activara debajo de Módulos la Opción Modulo1.

Page 39: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 39

15. De doble clic en Modulo1. Se mostrara en el Editor de VisualBasic el código de la macro que grabamos de la siguiente forma:

Sub saludo()'' saludo Macro' Mi primera macro' Autor: Guillermo Salazar Mancera' Acceso directo: CTRL+s'Range("A1").SelectActiveCell.FormulaR1C1 = "Hola

compañeros" End Sub

16. A continuación se da una explicación de lo que ha hecho Excel:

• Sub y End Sub indican el inicio y el final del procedimiento de la macro saludo.

• Todo lo que aparece con un apóstrofe ´ indica que no se tomara en cuenta que es solo texto o comentarios y ese texto aparece en color verde.

• Range("A1").Select Indica que lo primero que hicimos al grabar la macro fue trasladarnos a la celda A1. La orden Range nos permite trasladarnos a una celda.

• ActiveCell.FormulaR1C1 = "Hola compañeros" Esto indica que se escribirá en la celda en que se encuentra el valor de texto Hola compañeros. Todo lo que aparece entre comillas siempre será un valor de texto. La orden ActiveCell.FormulaR1C1 nos permite escribir un valor en la celda activa. Para comprender alteraremos el código dentro del editor de Visual Basic.

Sub saludo()' saludo Macro' Mi primera macro' Autor: Guillermo Salazar Mancera' Acceso directo: CTRL+sRange("A1").SelectActiveCell.FormulaR1C1 = "Hola compañeros" Range("B1").SelectActiveCell.FormulaR1C1 = "Bienvenidos al curso de

Excel" End Sub

Page 40: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 40

17. Al alterar el código y cuando regrese a Excel y ejecute la macro con Control + s.

Hará lo siguiente:

En A1 escribirá Hola compañerosEn B1 escribirá Bienvenidos al curso de Excel

Al alterar el código y cuando regrese a Excel y ejecute la macro conControl + s hará

En A1 escribirá Excel AvanzadoEn B1 escribirá Bienvenidos.

Se visualiza:

Sub saludos()' saludo Macro' Mi primera macro' Autor: Guillermo Salazar Mancera' Acceso directo: CTRL+s' Range("A1").SelectActiveCell.FormulaR1C1 = "Excel Avanzado"' Range("B1").SelectActiveCell.FormulaR1C1 =

"Bienvenidos" End Sub

Al alterar el código y cuando regrese a Excel y ejecute la macro con Control + s hará: En A1 escribirá Excel Avanzado. En B1 escribirá Bienvenidos.

Para salir del editor de clic en el Menú Archivo y elija la opción Cerrar y volver a Microsoft Excel.

Page 41: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 41

Si no desea salir por completo de clic en el botónMicrosoft Excel que se encuentra activado en:

Cuando deseé volver al editor de clic en: la pestaña Programador

De clic en el icono del grupo Código.

PRACTICA II

1. Genere una Macro que escriba un nombre en una celda y lo ponga negrita y observe el Código.

2. Genere una Macro que escriba un nombre en una celda y loCentre y observe el Código.

3. Genere una Macro que escriba un nombre en una celda y cambie el tamaño de la letra a 20 puntos y observa el Código.

Page 42: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 42

CÓDIGOS MÁS COMUNES

Trasladarse a una CeldaRange("A1").Select

Escribir en una CeldaActivecell.FormulaR1C1="Guillermo Salazar Mancera"

Letra NegritaSelection.Font.Bold = True

Letra CursivaSelection.Font.Italic = True

Letra SubrayadaSelection.Font.Underline = xlUnderlineStyleSingle

Centrar TextoWith Selection

.HorizontalAlignment = xlCenter

End With

Alinear a la izquierdaWith Selection

.HorizontalAlignment = xlLeft

End With

Alinear a la DerechaWith Selection

.HorizontalAlignment = xlRight

End With

Page 43: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 43

Tipo de Letra(Fuente)With Selection .Font

.Name = "AGaramond"

End With

Tamaño de Letra(Tamaño de Fuente)With Selection.Font

.Size = 15

End With

CopiarSelection.Copy

PegarActiveSheet.Paste

CortarSelection.Cut

Ordenar AscendenteSelection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Orden DescendenteSelection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Page 44: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 44

BuscarCells.Find(What:=" Guillermo Salazar Mancera ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate

Insertar FilaSelection.EntireRow.Insert

Eliminar FilaSelection.EntireRow.Delete

Insertar ColumnaSelection.EntireColumn.Insert

Eliminar ColumnaSelection.EntireColumn.Delete

Abrir un LibroWorkbooks.Open Filename:="C:\Mis documentos\miarchivo.xls"

Grabar un LibroActiveWorkbook.SaveAs Filename:="C:\Mis documentos\tauro.xls", FileFormat _:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=False

Page 45: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 45

CUADRO DE CONTROL – CONTROLES ACTIVEX

Una de las opciones más interesantes que tiene el Excel es la de utilizar los “cuadros de control”. Los cuadros de control se usan para crear verdaderos programas en Excel y pueden ser de mucha utilidad.

Esta herramienta está ubicada en:

En Excel 2007 se encuentra el grupo Controles de la pestañaProgramador

Este grupo de Controles cuenta con tres opciones muy importantes como:

1. Modo diseño: permitirá trabajar en el diseño de los controles deActiveX

2. Propiedades: permiten activar la propiedad de cada control

3. Ver código: permite agregar código a cada control. Para iniciar cree las hojas: Menú, Ventas y Compras

Page 46: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 46

Seleccione la hoja Menú para allí crear dos botones.

Para trabajar con estos controles en necesario Activar el modo de diseño y dar clic en Insertar, seleccione el Botón de comando.

Esta vez haremos un botón que cuando se presione pase a otra hoja del Excel. Por ejemplo se puede hacer un menú con varios botones que al presionarlos pasen a las distintas opciones.Comencemos...

En la hoja Menú cree dos “botones de

comando”. Por Ejemplo:

Seleccione el primer botón y de un clic derecho en la opción

y muestre las propiedades. Cambie la Propiedad “Caption” por: “Ventas” En Name: btnventas

Page 47: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 47

Seleccione el segundo botón y muestre las propiedades

Cambie la Propiedad “Caption” por:

“Compras” En Name: btncompras

Si realizó bien estos pasos Ud. Debería ver lo siguiente:

Si desea colocar iconos en los botones seleccione la propiedadPicture e inserte una imagen de extensión .ico.

Para que visualice el texto cambie la posición PicturePosition a: 5

Seleccione el primer botón y haga clic en ver código

En esta parte se abrirá el Editor de Visual Basic y debe escribir lo siguiente:

Hoja2.Activate

Page 48: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 48

Cierre el editor de Visual Basic (nota: cada vez que cierre el editor de Visual Basic, hágalo del cuadro de cerrar “X” que está mas arriba, porque puede confundirse y cerrar la ventana de editar código, no se preocupe que no está cerrando Excel.)

Seleccione el segundo botón y haga clic en ver código

Escriba: Hoja3.activate

Salga del modo de diseño y navegue con los botones que programó.

Más adelante utilizaremos estos botones para cargar formulariosdesde VBA en Excel.

O añada el código de las macros que grabó con la grabadora.

PRÁCTICA: CUADROS DE CONTROL.

CONTROL NUMÉRICO.

Para ver el funcionamiento de algunos de losbotones de la barra empezaremos con un ejemplo.

Elija “Control de Número”

Dibuje en la planilla el cuadro donde estará situado el control. PorEj.:

1. Seleccione propiedades Aquí podemos cambiar entre otras cosas.Max: El número máximo que recorrerá este cuadro.Min: El número mínimo.LinkedCell: Celda donde se va a mostrar el numero relacionado con el cuadro de control: Por Ej.: A4.

2. Cierre las “propiedades”.

Page 49: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 49

3. El último paso y el más importante es salir del modo diseño picando en “Modo Diseño”.

Ahora ya puede probar el funcionamiento de este botón.

Si presiona en la flecha hacia la derecha el número en la celda A4 irá aumentando y si presiona en la de la izquierda irá decreciendo.

CUADRO COMBINADO Y CUADRO LISTA

Otro de los botones más útiles que tiene la Barra de herramientas de “Cuadro de Controles” es el “Cuadro Combinado”Prácticamente no existen diferencias entre “Cuadro Combinado” y“Cuadro de Lista” así que lo que dice a continuación se aplica a cualquiera de las dos opciones.

Primera Parte:Escriba en la hoja3 los siguientes datos uno debajo del otro. TelevisorHeladeraLicuadora Monitor Teclado

Nombre a este rango de datos como datos (Para nombrar un rango de datos marque los datos y escriba el nombre en el “Cuadro de nombres”).

Segunda parte:Dibuje un “Cuadro combinado” por ejemplo:

Seleccione propiedades

Page 50: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 50

Busque la propiedad: ListFillRange y escriba: “datos” (sin comillas)En la propiedad LinkedCell Escriba A1 (es la celda donde se mostrará el dato elegido)

Page 51: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 51

Salga del “Modo Diseño” y pruebe este ejemplo:

Podrá comprobar que en el “Cuadro combinado” se encuentran los datos ingresados en la hoja3 y que cuando elige uno, éste se muestra en la cela A1.

Recuerde que esto se aplica tanto a “Cuadro combinado” (arriba) o a “Cuadro de lista” (abajo)

Page 52: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 52

BOTONES DE COMANDO

Esta vez se hará un botón de comando que cuando se presione pase a otra hoja del Excel. Por ejemplo se puede hacer un menú con varios botones que al presionarlos pasen a las distintas opciones.

En la hoja1 cree dos “botones de comando”. Por Ej:

Seleccione el primero botón y muestre las propiedades

Cambie la Propiedad “Caption” por : “Ventas”Seleccione el segundo botón y muestre las propiedades

Cambie la Propiedad “Caption” por : “Compras”Si realizó bien estos pasos Ud. Debería ver lo siguiente:

Seleccione el primer botón y haga clic en ver código

En esta parte se abrirá el Editor de Visual Basic y debe escribir lo siguiente:Hoja2.activate

Cierre el editor de Visual Basic (nota: cada vez que cierre el editor de Visual Basic, hágalo del cuadro de cerrar “X” que está más arriba, porque puede confundirse y cerrar la ventana de editar

Page 53: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 53

código, no se preocupe que no está cerrando Excel.)Seleccione el segundo botón y haga clic en ver código

Escriba:Hoja3.activate

Page 54: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 52

Si ha seguido los pasos deberá ver algo parecido a esto:

Cierre el editor de Visual Basic.Salga del “Modo diseño” y ya puede probar el funcionamiento de los botones.

Note que cuando haga clic en “Ventas” pasará a la hoja2 y cuando haga clic en el botón “Compras” pasará a la hoja3. Se podría también hacer un botón en la hoja2 y uno en la hoja3 que vuelvan al menú.

Page 55: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 53

CREANDO FORMULARIOS Y PROGRAMÁNDOLOS

Ahora aprenderemos a dominar lo máximo de Excel que es crear formularios y programarlos, bueno un formulario es una ventana que se programa por medio de controles y estos controles responden a sucesos que nosotros programamos. Todo esto se encuentra dentro de Visual Basic.

A continuación Muestro como crear un formulario y como programarlo:

1. Presione La Teclas Alt + F11, para entrar al editor de Visual Basic.2. Activa las siguientes opciones:• De clic en el Menú Ver y elija la opción Explorador de Proyectos• De clic en el Menú ver y elija la opción Ventana Propiedades3. Del Menú Insertar elija la Opción UserForm.

Esto inserta el Formulario que programaremos con controles. En el Explorador de Proyectos se observara que se inserto el UserForm.

Page 56: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 54

También cuando de clic en el Formulario USERFORM1 se debe de activar el Cuadro de Herramientas, si no se activa de clic en el Menú Ver y elija la opción Cuadro de Herramientas.

4. Elija del Cuadro de Herramientas el Control Etiqueta el que tiene la A y Arrastre dibujando en el Formulario USERFORM1 la etiqueta. Quedara el nombre Label1, después de un clic en la etiqueta dibujada y podrá modificar el nombre de adentro y pondremos ahí Nombre. Si por error da doble clic en la etiqueta y lo manda a la pantalla de programación de la etiqueta, solo de doble clic en UserForm1 que se encuentra en el Explorador de Proyecto.

5. Elija del Cuadro de Herramientas el control Cuadro de Texto el que tiene ab y arrastre dibujando en el formulario USERFORM1 el cuadro de texto a un lado de la etiqueta que dice Nombre. El cuadro de texto debe de estar vacío y su nombre será Textbox1, el nombre solo aparecerá en el control.

6. Haga los dos pasos anteriores igualmente poniendo Dirección en la Label2 y Teléfono en la Label3 y también dibújeles su Textbox. Esto quedara así después de haberlo hecho.

Page 57: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 55

Si tiene algún problema al dibujar las etiquetas o los cuadros de texto, solo cámbiele el nombre a la etiqueta o el cuadro de texto en la Ventana Propiedades la opción se llama (Name). El Error que marque puede ser Nombre Ambiguo, pero si le cambia el Nombre al control se quitara el error. Puede ponerle cualquier nombre en lugar de Label1.

Los controles como las Etiquetas y Cuadros de Textos pueden modificárseles algunas opciones en la Ventana Propiedades Para hacer esto es necesario tener conocimiento sobre las propiedades de los controles. No altere las propiedades si no las conoce.

7. Elija del Cuadro de Herramientas el control Botón de Comando y Arrastre dibujando en el Formulario USERFORM1 el Botón, después de un clic en el nombre del Botón dibujado y podrá modificar el nombre y pondremos ahí Insertar.

Si por error da doble clic en la Botón y lo manda a la pantalla de programación de la etiqueta, solo de doble clic en UserForm1 que se encuentra en el Explorador de Proyecto.

Así quedara el Formulario formado por los controles:

Page 58: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 56

8. Ahora de doble clic sobre el control Textbox1 para programarlo y después inserte el siguiente código:

Private Sub TextBox1_Change() Range("A9").Select ActiveCell.FormulaR1C1 = TextBox1End Sub

Esto indica que se valla a A9 y escriba lo que hay en el Textbox1

Nota.-Lo que esta en azul lo genera Excel automáticamente, usted solo escribirá lo que esta en Negrita.

Para volver al Formulario y programar el siguiente Textbox de doble clic en UserForm1 que se encuentra en el Explorador de Proyecto, o simplemente de clic en Ver Objeto en el mismo Explorador de Proyecto.

9. Ahora de doble clic sobre el control Textbox2 para programarlo y después inserte el siguiente código:

Private Sub TextBox2_Change() Range("B9").Select ActiveCell.FormulaR1C1 = TextBox2Private Sub

Esto indica que se valla a B9 y escriba lo que hay en el Textbox2.

Para volver al Formulario y programar el siguiente Textbox de doble clic en UserForm1 que se encuentra en el Explorador de Proyecto, o simplemente de clic en Ver Objeto en el mismo Explorador de Proyecto.

10. Ahora de doble clic sobre el control Textbox3 para programarlo y después inserte el siguiente código:

Private Sub TextBox3_Change() Range("C9").Select ActiveCell.FormulaR1C1 = TextBox2End Sub

Page 59: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 57

Esto indica que se valla a C9 y escriba lo que hay en el Textbox3

Para volver al Formulario y programar el Botón de Comando Insertar de doble clic en UserForm1 que se encuentra en el Explorador de Proyecto, o simplemente de clic en Ver Objeto en el mismo Explorador de Proyecto.

11. Ahora de doble clic sobre el control Botón de Comando para programarlo y después inserte el siguiente código:

Private Sub CommandButton1_Click()

‘inserta un renglón

Selection.EntireRow.Insert

‘Empty Limpia Los Textbox

TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty

‘Textbox1.SetFocus Envía el cursor capturar los datos.

al Textbox1 para volver a

TextBox1.SetFocus

End Sub

Nota.-El comando Rem es empleado para poner comentarios dentro de la programación, el comando Empty es empleado para vaciar los Textbox.

12. Ahora presione el botón Ejecutar User/Form que se encuentra en la barra de herramientas o simplemente la tecla de función F5.

Se activará el Userform1 y todo lo que escriba en los Textbox se escribirá en Excel y cuando presione el botón Insertar, se insertara un renglón y se vaciaran los Textbox y después se mostrara el cursor en el Textbox1.

Page 60: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 58

TRABAJANDO CON FORMULAS

Es de suma importancia saber aplicar Formulas en Macros de Excel, ya que la mayoría de las hojas de cálculos las involucran, por ejemplo los Inventarios, las Nominas o cualquier otro tipo de hoja las llevan, es por eso que en la siguiente Fase se muestra como manejar Formulas en Macros de Excel.

Presione La Teclas Alt + F11, para entrar al editor de Visual Basic. Activa las siguientes opciones:• De clic en el Menú Ver y elija la opción Explorador de Proyectos.

• De clic en el Menú ver y elija la opción Ventana Propiedades.

Del Menú Insertar elija la Opción UserForm. Esto inserta el Formulario que programaremos con controles. En el Explorador de Proyecto se observara que se inserto el UserForm.

Ahora crearas un formulario con el siguiente aspecto:

El formulario tendrá:

Tres etiquetas

Tres Textbox

Un Botón de Comando

Los datos que se preguntaran serán Nombre y Edad, los Días Vividos se generaran automáticamente cuando insertes la edad. A continuación se muestra como se deben de programar estos Controles

Page 61: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 59

PROGRAMACIÓN DE LOS CONTROLES:

Private Sub CommandButton1_Click()

Selection.EntireRow.Insert TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus

End Sub

Private Sub TextBox1_Change()

Range("A9").SelectActiveCell.FormulaR1C1 = TextBox1

End Sub

Private Sub TextBox2_Change()

Range("B9").SelectActiveCell.FormulaR1C1 = TextBox2‘aquí se crea la FórmulaTextBox3 = Val(TextBox2) * 365‘El Textbox3 guardara el total de la multiplicación del Textbox2 por 365‘El Comando Val permite convertir un valor de Texto a un Valor Numérico‘Esto se debe a que los Textbox no son Numéricos y debemos deConvertirlos

End Sub

Private Sub TextBox3_Change()

Range("C9").SelectActiveCell.FormulaR1C1 = TextBox3

End Sub

Page 62: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 60

CÓDIGO PARA CARGAR UN FORMULARIO DESDE EXCEL

Una vez que haya diseñado su formulario en el ambiente de Visual Basic Application, se requiere que este sea cargado desde MS Excel, para esto diseñe una interfaz por ejemplo:

Si desee quitar la cuadrícula seleccione en la pestaña Vista en el grupo Zoom, la opción Mostrar un ocultar y desactive (quitar con un

clic el visto) la opción Líneas de cuadrícula

La interfaz lucirá así:

Page 63: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 61

La interfaz debe contener un botón de comando deControles ActiveX.

O una autoforma para poder cargar el diseño del formulario.

Para que el botón de comando se mantenga sin moverse es decir esté fijo y no se imprima, en el modo de Diseño, seleccione el botón de un clic derecho elija la opción Formato de control como se visualiza en la imagen.

Page 64: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 62

Se visualiza el siguiente cuadro de diálogo, seleccione la pestaña Propiedades y active con un punto la opción No mover, ni

cambiar tamaño con las celdas. para que no se imprima el botón desactive (quite el visto) de la opción Imprimir objeto.

Para programar el botón, de doble clic sobre el objeto o de un clic

derecho sobre el objeto en la opción: Como

se observa en la imagen:

Page 65: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 63

Se ingresa al ambiente de Visual Basic Application

Private Sub btnagregar_Click()

End Sub

En donde titila el cursor se digita el siguiente código:

Load UserForm1'Load carga el formularioUserForm1.Show'.show hace visible

En la imagen se visualiza:

Page 66: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 64

ASIGNAR UNA MACRO A UNA AUTOFORMA

El caso es similar al anterior para esto debe crear un módulo en el ambiente de

Visual Basic Application y digitar el código:

Sub Agregar()

Load UserForm1'Load carga el formularioUserForm1.Show'.show hace visible al formulario

End Sub

Se observa en la imagen:

Page 67: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 65

Una vez listo el módulo, se inserta una autoforma en el ambiente de MS Excel y se asigna la macro Agregar que creo en el módulo. Observe la siguiente imagen:

Luego de un clic derecho sobre la autoforma seleccione la opción Asignar macro…

Page 68: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 66

Se visualiza:

Seleccione la macro Agregar.

De clic en Aceptar.

Page 69: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 67

PROTEGER UNA HOJA EN AMBIENTE VBA

Si desea proteger una hoja de Excel para que no se visualice sobretodo si contiene datos confidenciales y que sólo es necesario acceder para una consulta realice lo siguiente:

Seleccione la hoja a ocultar por ejemplo Rol y de un clic derecho en la opción Ver código

Se ingresa al ambiente de VBA en el que debe activar las propiedades de la hoja, se visualiza:

En la propiedad Visible seleccione:0 – xlssheethidden

Pero para conseguir una protección más segura se sugiere colocar una clave al proyecto de VBA.

Page 70: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 68

COLOCAR UNA CLAVE AL PROYECTO DE VBA.

Para esto en el mismo ambiente realice lo siguiente:

Seleccione el proyecto de un clic derecho sobre el proyecto y elija la opciónPropiedades de VBAProject…

Se visualiza:

En el cuadro de diálogo seleccione la pestaña Protección para bloquear el proyecto active con un visto la opción Bloquear proyecto para visualización.

Y para finalizar coloque una contraseña.

De esta forma no podrán visualizar ni la hoja oculta ni el código de programación generado en la aplicación.

Page 71: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 69

FUNCIONES DE BASE DE DATOS

Para poder usar este tipo de funciones, se debe tener la información con estructura de base de datos.

Una base de datos es una lista de datos relacionados en la que las filas de información son registros y las columnas de datos, campos. La primera fila de la lista contiene los rótulos de cada columna.

En las funciones de base de datos, funcionan de manera similar al resto de las funciones, pero en este caso se añade una condición, y la función nos devuelve el valor cuando la condición se cumple.

Page 72: Manual Excel Avanzado Agosto 2013

FUNCIÓN USO

BDCONTAR Cuenta el número de celdas que contienen números en la base de datos.

BDCONTARA Cuenta el número de celdas no vacías de la base de datos.

Extrae de la base de datos un ú n i c o re g i s t ro que cumple los criterios especificados.

Si ningún registro coincide con los criterios, BDEXTRAERBDEXTRAER devuelve el valor de error #¡VALOR!

Si más de un registro coincide con los criterios, BDEXTRAER devuelve el valor de error #¡NUM

BDMAX Devuelve el valor máximo de las entradas seleccionadas de la base de datos.

BDMIN Devuelve el valor mínimo de las entradas seleccionadas de la base de datos.

BDPROMEDIO Devuelve el promedio de las entradas seleccionadas en la base de datos.

BDSUMA Agrega los números de la columna de campo de los registros de la base de datos que cumplen los criterios.

BDPRODUCTO Multiplica los valores de un campo concreto de registros de la base de datos que cumplen los criterios especificados.

BDDESVEST Calcula la desviación estándar a partir de una muestra de entradas seleccionadas en la base de datos.

BDVAR Calcula la varianza a partir de una muestra de entradas seleccionadas de la base de datos.

Excel Avanzado Página 70

Page 73: Manual Excel Avanzado Agosto 2013

FUNCIONES FINANCIERAS

Las funciones financieras ejecutan operaciones contables comunes, tales como determinar los pagos de un préstamo, el valor futuro o el valor neto actual de una inversión, entre muchos otros.

Los argumentos más comunes de las funciones financieras incluyen:

Valor futuro (vf): el valor de la inversión o del préstamo una vez realizados todos los pagos.

Número de períodos (nper): el número total de pagos o períodos de una inversión.

Pago: el importe pagado periódicamente en una inversión o préstamo.

Valor actual (va): el valor de una inversión o préstamo al comienzo del período de inversión. Por ejemplo, el valor presente de un préstamo es el importe principal que se toma prestado.

Interés (interés): el interés o el descuento de un préstamo o una inversión.

Tipo (tipo): el intervalo en que se realizan los pagos durante el período de pago, como al comienzo o al final de mes.

Es importante tener claros los parámetros anteriores para usar este tipo de funciones.

Excel Avanzado Página 71

Page 74: Manual Excel Avanzado Agosto 2013

FUNCIÓN USO

DURACION Devuelve la duración anual de un valor bursátil con pagos de interés periódico.

INT.ACUM Devuelve el interés acumulado de un valor bursátil con pagos de interés periódicos.

INT.ACUM.V Devuelve el interés acumulado de un valor bursátil con pagos de interés al vencimiento.

NPER Esta función permite obtener el número de periodos deuna anualidad. En el caso trabajado sería hallar la cantidad de cuotas del préstamo con las demás condiciones como argumentos.

PAGO Calcula el valor de la cuota fija de un préstamo a una tasa de interés constante y termino fijo. Esto se resume en el concepto de Anualidad.

PAGOINT Esta función permite conocer el monto de interés pagado en un período específico por una inversión basándose en pagos periódicos constantes y en una tasa de interés constante.

PAGOPRIN Calcula la amortización sobre el capital de una inversión durante un período determinado basándose en pagos periódicos y constantes, y en una tasa de interés constante.

Excel Avanzado Página 72

Page 75: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 73

ESCENARIOS – ANÁLISIS Y Si

Un escenario es un conjunto de valores que Excel guarda y puedes sustituir automáticamente en una hoja de cálculo.

Puede crear y guardar diferentes grupos de valores como escenarios en una hoja de cálculo y luego de un escenario a otro para ver distintos resultados.

Una vez que tenga todos los escenarios que necesita, puede crear un informe resumen de escenario que incluya la información de todos los escenarios.

Esta situación se aplica cuando se tienen varios supuestos de petición de un crédito con varios tipos de interés, varios posibles períodos, etc.

a) En la cinta de opciones Datos, seleccionar Análisis Y Si –Administrador de Escenarios

Page 76: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 74

b) Seleccionar Agregar, colocar un nombre al escenario y seleccionar el rango de las celdas cambiantes.

c) A continuación, se desplegará una ventana, en donde se colocarán los datos “supuestos” para ser calculados

d) Para agregar escenarios distintos, se debe Agregar, el número de situaciones necesarias.

e) Al terminar cada una de las situaciones, se oprime Resumen, Excel de manera automática genera una nueva hoja y se visualiza un resultado así:

Page 77: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 75

BUSCAR OBJETIVO

Buscar objetivo, permite cambiar el valor de una celda donde exista fórmula, indicando el valor exacto que el usuario quiere obtener; para ello Excel modifica el valor de otra celda que dependa de la fórmula, se aplica a perfiles financieros, comerciales, estadísticos entre otros.

El objetivo es incrementar las ventas de artículos de la hoja enero. Si se quiere obtener una venta de $ 5.850.000 ¿cuántas memorias deben venderse?

Solución:

1. Ubicarse en la celda H22. Hacer clic en la ficha datos, grupo herramientas de datos, en la flecha desplegable de análisis y si, seleccionar buscar objetivo.

Page 78: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 76

En el cuadro de texto definir la celda escribir h2. En el cuadro de texto con el valor escribir 5850000. En el cuadro de texto para cambiar la celda escribir f2. Hacer clic sobre el botón Aceptar.

Page 79: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 77

Page 80: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 78

SOLVER

Es una herramienta que permite en una celda con fórmula buscar un valor óptimo según un grupo de celdas relacionadas con la fórmula, de esta forma se halla la mejor solución a un problema mediante restricciones. Solver entonces ajusta las celdas cambiantes para dar el resultado especificado en el valor objetivo, teniendo en cuenta todas sus restricciones.

Así, Solver, permite hallar la mejor solución a un problema teniendo en cuenta condiciones y restableciendo valores.

El complemento Solver, es un programa que está disponible cuando instala Microsoft Office o Excel. Sin embargo, para ser utilizado en Excel primero se debe cargar.

Para ello, seguir los siguientes pasos:

Habilitar solver

1. Hacer clic sobre el botón de Microsoft office y luego clic en el botón opciones de Excel.

2. Hacer clic en Complementos, en el cuadro Administrar, seleccionarComplementos de Excel.

Page 81: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 79

3. Hacer clic en Ir.

4. En el cuadro Complementos disponibles, activar la casilla de verificación Complemento Solver, a continuación, hacer clic en Aceptar.

5. Hacer clic sobre la ficha datos, botón análisis, solver.

Page 82: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 80

Celda Objetivo: Especificar una celda con fórmula a la que se desee calcular el mínimo valor, el máximo valor o un valor específico.

Valores de: Activar el botón de opción de acuerdo al objetivo, si se desea calcular un valor específico, digitarlo en el cuadro de texto.

Page 83: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 81

Cambiando las Celdas: Especificar las celdas que Microsoft Excel debe calcular para lograr el valor objetivo. Estas celdas deben estar relacionadas directa o indirectamente en la fórmula de la celda objetivo, y deben ser celdas sin fórmulas.

Sujetas a las Siguientes restricciones: Muestra el listado de las restricciones agregadas. Para agregarlas, dar clic en el botón Agregar y especificar la restricción; si se desea cambiar una condición, se debe seleccionar y dar clic en el botón cambiar, y de la misma manera, seleccionar una restricción y dar clic en el botón eliminar, si se desea quitar una restricción.

Agregar: En Referencia de la celda, definir la celda a la que se va a aplicar la restricción, a continuación definir el operador de comparación, <=, =, ó >= y especificar la restricción en el cuadro, ya sea una celda o un valor.

Utilizar Solución de Solver: Al dar clic en Aceptar con ese botón de opción activo, Solver modificará los valores en la hoja de cálculo sin poder deshacerse.

Restaurar valores originales: Solver deja los valores iniciales en la hoja de cálculo.

Guardar escenario: Los nuevos valores sugeridos por solver serán guardados como escenario, de manera que puedan ser analizados mas adelante.

Page 84: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 82

Ejemplo:

La compañía desea para el próximo semestre cumplir una meta comercial, 600.000.000 millones, cuántos artículos se deben vender de cada uno para cumplir esa meta.

1. Ubicarse en la celda h19.2. Realice una suma del campo total =SUMA(H2:H17).3. Hacer clic sobre la ficha datos grupo análisis, solver.4. Seleccionar el botón de opción valores de la celda objetivo y escribir a

600000000.5. Hacer clic sobre el botón estimar.6. Hacer clic sobre el botón resolver.7. Hacer clic Sobre el botón de opción utilizar solución de solver.8. Hacer clic sobre la palabra respuestas.9. Hacer clic sobre el botón Aceptar.

Page 85: Manual Excel Avanzado Agosto 2013

Excel Avanzado Página 83

INFORMES DE SOLVER

Existen tres tipos de informe, informe de respuesta, sensibilidad y límite.

Para el informe de respuesta, solver crea una nueva hoja mostrandolos valores originales y los valores con la solución tanto para celdas cambiantes como para la celda objetivo. En el informe de Sensibilidad solver muestra los nuevos valores de las celdas cambiantes. Informe de límites muestra al usuario los valores nuevos de la celda cambiante y de la celda objetivo, y además, muestra el valor del límite inferior y límite superior de cada celda cambiante y cuál sería en tal caso, el valorobjetivo.