Manual Excel Avanzado Agosto 2013

(1)

Excel Avanzado

Excel Avanzado Página 1Página 1

EXCELNIVELAVANZADO

ManualdeReferenciaRápida.

Marzo

(2)

M

ANUAL DE

E

XCEL

2007.

N

IVEL

_A

VANZADO

El prese

El presente nte manual manual tiene tiene como objetivo como objetivo guiar guiar al usual usuario ario en elen el manejo de Excel 2007, haciendo referencia a Herramientas Avanzadas manejo de Excel 2007, haciendo referencia a Herramientas Avanzadas para el manejo de grandes volúmenes de información como lo son, 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 funciones y procedimientos de análisis; así como la automatización de procesos con Macros.

procesos con Macros.

El manual fue desarrollado por los Profesores del Departamento El manual fue desarrollado por los Profesores del Departamento de Ca

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

(3)

Excel Avanzado

Excel Avanzado Página 2 Página 2

M

ANUAL DE

E

XCEL

2007.

N

IVEL

_A

VANZADO

El prese

El presente nte manual manual tiene tiene como objetivo como objetivo guiar guiar al usual usuario ario en elen el manejo de Excel 2007, haciendo referencia a Herramientas Avanzadas manejo de Excel 2007, haciendo referencia a Herramientas Avanzadas para el manejo de grandes volúmenes de información como lo son, 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 funciones y procedimientos de análisis; así como la automatización de procesos con Macros.

procesos con Macros.

El manual fue desarrollado por los Profesores del Departamento El manual fue desarrollado por los Profesores del Departamento de Ca

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

(4)

S

OCIEDAD

D

E

B

ENEFICENCIA

E

SPAÑOLA

,,

I.A.P.

D

EPARTAMENTO DE

C

_APACITACIÓN

N

_OMBRE

::

E

(5)

Excel Avanzado

Excel Avanzado Página 4Página 4

C

ONTENIDOONTENIDO

T

EMÁTICOEMÁTICO

TT

ABLAS YABLAS Y

G

RÁFICOSRÁFICOS

D

INÁMICOSINÁMICOS

5

5 M

M

ACROS ENACROS EN

E

XCELXCEL

30

30 C

C

UADROS DEUADROS DE

C

ONTROLONTROL ––

A

CTIVECTIVE

X

45

45 C

C

REANDOREANDO

FF

ORMULARIOS YORMULARIOS Y

PP

ROGRAMÁNDOLOSROGRAMÁNDOLOS

53

53 FF

UNCIONES DEUNCIONES DE

B

ASE DEASE DE

D

ATOSATOS

69

69 FF

UNCIONESUNCIONES

FF

INANCIERASINANCIERAS

71

71 E

E

SCENARIOSSCENARIOS ––

A

_NÁLISIS_NÁLISIS

Y

Y S

S

_II

73

73 B

B

USCARUSCAR

O

BJETIVOBJETIVO

75

75 S

(6)

T

ABLAS

D

INÁ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.

C

OMPONENTES DE LAS

T

ABLAS

D

INÁ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.

(7)

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.

O

BJETIVO DE UNA

T

ABLA

D

INÁ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.

C

REAR UNA

T

ABLA

D

INÁ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.

(8)

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

(9)

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.

(10)

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.

(11)

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 de

columna columna.

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

C

ONFIGURACIÓN DE

C

AMPO

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.

(12)

F

UNCIÓN

D

ESCRIPCIÓN

S

UMA La suma de los valores. Es la función predeterminada de los_{valores numéricos.}

C

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

P

ROMEDIO El promedio de los valores.

M

ÁX El valor máximo.

M

ÍN El valor mínimo.

P

RODUCTO El producto de los valores.

C

ONTAR Cuenta las celdas que tengan un valor numérico.

NÚMEROS

D

ESV

E

ST 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.

(13)

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.

(14)

D

EFINIR

C

AMPOS EN

T

ÉRMINOS DE

%

F

UNCIÓN

R

ESULTADO

D

IFERENCIA 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.

T

OTAL 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))

(15)

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 desplegable seleccionar % 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%

(16)

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:

(17)

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.

(18)

A

GRUPAR

E

LEMENTOS

S

ELECCIONADOS

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.

(19)

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 clic

derecho sobre el campo a agrupar o desagrupar.

(20)

M

OSTRAR U

O

CULTAR LOS

D

ETALLES DE UN

I

NFORME

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 en

Expandir 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 en

Expandir hasta "<nombre del campo>".

Para ocultar un nivel de detalle posterior al siguiente nivel, hacer clic en

(21)

F

ORMATOS DE

T

ABLA

D

INÁ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.

(22)

O

RDENAR

C

AMPOS EN

T

ABLAS

D

INÁ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 en Ordenar de A a Z.

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

Si el campo es de tipo Numérico

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

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

(23)

Si el campo es Fechas y Horas

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

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

A

CTUALIZAR

T

ABLA

D

INÁ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.

(24)

C

AMBIAR 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.

(25)

A

CCIONES 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.

(26)

E

LEMENTOS Y

C

AMPOS CALCULADOS EN

T

ABLAS

D

INÁ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 ficha

Opciones, 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.

(27)

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.

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.

(28)

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

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 Subir o 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 en

Fó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.

G

ENERAR UN

G

RÁFICO

D

INÁ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.

(29)

Excel Avanzado

3. Aparecerá una ventana en la cual se puede seleccionar el origen 3. Aparecerá una ventana en la cual se puede seleccionar el origen

de

de lo lo que se que se desea desea representar o representar o bien bien de una de una base debase de datos externa,

datos externa, seleccionar el botón de opción.seleccionar el botón de opción.

Seleccionar una tabla o rango

Seleccionar una tabla o rango y el botón de opción nuevay el botón de opción nueva hoja

(30)

4. Arrastrar el campo

4. Arrastrar el campo cantidadcantidad al área de valores.al área de valores. 5. Arrastrar el campo artículos al área de

(31)

Excel Avanzado

M

ACROS ACROS ENEN

E

XCEXCELL

Al

Al trabajar trabajar con con un un libro libro personalizado, personalizado, es es decir, decir, que que nos nos hemoshemos definido con una serie de características específicas como puedan ser el 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 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 características similares, perdemos mucho tiempo en formatear todo el libro si disponemos de muchas hojas.

libro si disponemos de muchas hojas.

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

libro que al pulsar sobre él realice todas esas tareas.

G

RABAR UNARABAR UNA

M

ACRO ACRO

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

mediante la grabadora de macros del que dispone Excel.

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

programación.

Para grabar una macro debemos acceder a la

Para grabar una macro debemos acceder a la Ficha VistaFicha Vista yy despliega el submenú

despliega el submenú MacrosMacros y dentro de este submenú seleccionar lay dentro de este submenú seleccionar la opción

opción Grabar macro...Grabar macro... Además de esta opción en el menú podemosAdemás de esta opción en el menú podemos encontrar las siguientes opciones:

encontrar las siguientes opciones: Ver Macros...

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

creadas en ese libro.

Usar referencias relativas

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

(32)

Se puede grabar las macros desde la

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

está disponible, haga lo siguiente para mostrarla:

1.

1. Haga clic en el Botón Microsoft OfficeHaga clic en el Botón Microsoft Office y,y, aa continuación,continuación, 2.

2.

haga clic en

haga clic en Opciones de Excel.Opciones de Excel. Opción

Opción MásMás frecuefrecuentente

3.

3. Active con un visto la opciónActive con un visto la opción Como se visualiza:

Como se visualiza:

4.

4. De clic en el Botón AceptDe clic en el Botón Aceptar.ar. Se visualiza l

(33)

La Ficha se compone de: Grupo Código:

El grupo Controles

El grupo XML

(34)

S

EGURIDAD EN

M

ACROS

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 en Seguridad 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.

(35)

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

(36)

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.

P

RACTICA

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.

(37)

C

ÓDIGOS DE UNA

M

ACRO DE

E

XCEL

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 grupo Có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

(38)

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.

(39)

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.

(40)

15. De doble clic en Modulo1. Se mostrara en el Editor de Visual Basic 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").Select

ActiveCell.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

Range("A1").Select

ActiveCell.FormulaR1C1 = "Hola compañeros" Range("B1").Select

ActiveCell.FormulaR1C1 = "Bienvenidos al curso de Excel" End Sub

(41)

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ñeros

En B1 escribirá Bienvenidos al curso de Excel

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.

Se visualiza:

Sub saludos()

' saludo Macro ' Mi primera macro

' Range("A1").Select

ActiveCell.FormulaR1C1 = "Excel Avanzado" ' Range("B1").Select

ActiveCell.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.

(42)

Si no desea salir por completo de clic en el botón Microsoft 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.

P

RACTICA

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 lo Centre 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.

(43)

C

ÓDIGOS

M

ÁS

C

OMUNES

Trasladarse a una Celda Range("A1").Select

Escribir en una Celda

Activecell.FormulaR1C1="Guillermo Salazar Mancera"

Letra Negrita Selection.Font.Bold = True Letra Cursiva Selection.Font.Italic = True Letra Subrayada Selection.Font.Underline = xlUnderlineStyleSingle Centrar Texto With Selection .HorizontalAlignment = xlCenter End With Alinear a la izquierda With Selection .HorizontalAlignment = xlLeft End With Alinear a la Derecha With Selection .HorizontalAlignment = xlRight End With

(44)

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 Copiar Selection.Copy Pegar ActiveSheet.Paste Cortar Selection.Cut Ordenar Ascendente

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

Orden Descendente

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

(45)

Buscar

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

Insertar Fila Selection.EntireRow.Insert Eliminar Fila Selection.EntireRow.Delete Insertar Columna Selection.EntireColumn.Insert Eliminar Columna Selection.EntireColumn.Delete Abrir un Libro

Workbooks.Open Filename:="C:\Mis documentos\miarchivo.xls"

Grabar un Libro

ActiveWorkbook.SaveAs Filename:="C:\Mis documentos\tauro.xls", FileFormat _:=xlNormal, Password:="", WriteResPassword:="",

(46)

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ña Programador

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

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

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

(47)

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

(48)

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 propiedad Picture 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:

(49)

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 formularios desde VBA en Excel.

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

P

RÁCTICA

: C

UADROS DE

C

ONTROL

.

CONTROL NUMÉRICO

.

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

Elija “Control de Número”

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

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.

(50)

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. Televisor

Heladera Licuadora 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

Busque la propiedad: ListFillRange y escriba: “datos” (sin comillas)

En la propiedad LinkedCell Escriba A1 (es la celda donde se mostrará el dato elegido)

(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

(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 código, no se preocupe que no está cerrando Excel.)

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

(53)

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

(54)

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 Propiedades

3. 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.

(55)

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.

(56)

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.

(57)

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 = TextBox1 End 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_{= TextBox2} Private 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

(58)

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.

(59)

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

(60)

P

ROGRAMACIÓN DE LOS

C

ONTROLES

:

Private Sub CommandButton1_Click() Selection.EntireRow.Insert TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus End Sub

Private Sub TextBox1_Change() Range("A9").Select

Private Sub TextBox2_Change() Range("B9").Select

ActiveCell.FormulaR1C1 = TextBox2

„aquí se crea la Fórmula

TextBox3 = 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 de

Convertirlos End Sub

Private Sub TextBox3_Change() Range("C9").Select

(61)

C

ÓDIGO

P

ARA

C

ARGAR UN

F

ORMULARIO DESDE

E

XCEL

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

(62)

Excel Avanzado Página 61 La interfaz debe contener un botón de comando de

Controles 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.

(63)

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:

(64)

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 formulario UserForm1.Show

'.show hace visible

(65)

A

SIGNAR UNA

M

ACRO A UNA

A

UTOFORMA

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 formulario UserForm1.Show

'.show hace visible al formulario End Sub

(66)

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…

(67)

Excel Avanzado Página 66 Se visualiza:

Seleccione la macro Agregar.

(68)

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.

(69)

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ón

Propiedades 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.

(70)

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.

(71)

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 único registro que cumple los criterios especificados.

Si ningún registro coincide con los criterios, BDEXTRAER BDEXTRAER _{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.

(72)

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.

(73)

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 de una anualidad. En el caso trabajado sería hallar la cantidad de cuotas del préstamo con las demás condiciones como 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 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

(74)

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

(75)

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í: