excel avanzado con tablas dinamicas y macrossaccec.com/descargas/practica/excel macros.pdfde esta...

EXCEL AVANZADO CON

TABLAS DINAMICAS Y

MACROS

Luis Salvador-Ullauri – Patricia Acosta-Vargas [email protected]

LUIS SALVADOR-ULLAURI – PATRICIA ACOSTA-VARGAS 1

Contenido 1.1 Validación de datos ......................................................................................................3

1.1.1 Introducción .........................................................................................................3

1.1.2 ¿Cuándo es útil la validación de datos? ................................................................3

1.1.3 Herramientas de validación de datos ...................................................................3

1.1.4 Celdas con longitud de texto ................................................................................5

1.1.5 Mensajes de entrada ............................................................................................9

1.1.6 Mensajes de error ..............................................................................................10

1.1.7 Validar números enteros dentro de límites ........................................................14

1.1.8 Comprobar entradas no válidas ..........................................................................17

1.1.9 Validar Fechas .....................................................................................................21

1.1.10 Validar Listas .......................................................................................................25

1.1.11 Buscar celdas con validación ..............................................................................28

1.1.12 Borrar validación de datos ..................................................................................29

1.2 Fórmulas y funciones ..................................................................................................30

1.2.1 Funciones ...........................................................................................................30

1.2.2 Funciones - Sintaxis ............................................................................................31

1.2.3 Funciones Lógicas: Si ..........................................................................................33

1.2.4 Más Funciones Lógicas .......................................................................................36

1.2.5 Funciones De Búsqueda Y Referencia: Buscarv ..................................................36

1.2.6 Vídeo: Función Buscarv.......................................................................................39

1.2.7 Funciones De Información: Eserror ....................................................................39

1.2.8 Funciones Anidadas ............................................................................................41

1.3 Protección de un documento Excel ............................................................................44

1.3.1 Protección de un documento Excel ....................................................................44

1.3.2 Protección de una Hoja de Cálculo .....................................................................44

1.3.3 Protección de un Libro ........................................................................................48

1.3.4 Protección de acceso a un libro ..........................................................................49

1.4 Crea un informe de tabla dinámica ............................................................................51

1.4.1 Tablas Dinámicas ................................................................................................51

1.4.2 Sugerencias Para Crear Un Informe De Tabla Dinámica .....................................53

1.4.3 Crear Un Informe De Tabla Dinámica .................................................................54

1.4.4 Cambiar El Diseño Del Informe Dinámico A Vista Clásica ...................................57

1.4.5 Herramientas De Tabla Dinámica .......................................................................58

1.4.6 Resumir Datos De Un Informe Dinámico ............................................................59


1.4.7 Opciones De Diseño De Un Informe Dinámico ...................................................62

1.4.8 Actualizar Un Informe De Tabla Dinámica ..........................................................65

1.4.9 Cambiar El Origen De Datos Un Informe De Tabla Dinámica ..............................67

1.4.10 Agrupar Campos En Un Informe De Tabla Dinámica ..........................................70

1.4.11 Configuración De Campo De Valor .....................................................................72

1.4.12 Fórmulas En Un Informe De Tabla Dinámica ......................................................73

1.5 Gráficos dinámicos .....................................................................................................76

1.5.1 Gráficos Dinámicos .............................................................................................76

1.5.2 Crear Un Gráfico Dinámico .................................................................................77

1.5.3 Opciones De Diseño De Gráfico Dinámico ..........................................................79

1.5.4 Agregar Líneas De Tendencia En Un Gráfico Dinámico .......................................80

1.6 Creación de macros ....................................................................................................83

1.6.1 Crear Macros En Ms Excel ..................................................................................83

1.6.2 Preparar El Libro De Excel Para Trabajar Con Macros .........................................83

1.6.3 Grabar Macros ....................................................................................................86

1.6.4 Ejecutar Macros ..................................................................................................91

1.6.5 Acceder Al Ambiente De Visual Basic .................................................................93

1.6.6 Modificar Macros ...............................................................................................96

1.6.7 Códigos Más Comunes En Macros ......................................................................99

1.6.8 Asignar A Una Forma ........................................................................................101

1.6.9 Asignar Una Macro A Un Botón De Formulario ................................................105

1.6.10 Caso de estudio ................................................................................................108

1.7 Introducción a Visual Basic Applications ..................................................................115

1.7.1 Crear Macros En Ms Excel ................................................................................115

1.7.2 Preparar El Libro Para Trabajar Con Controles Activex .....................................116

1.7.3 Trabajar Con Controles Activex ........................................................................118

1.7.4 Cambiar De Apariencia Al Botón De Comando .................................................121

1.7.5 Insertar Un Icono En El Botón De Comando .....................................................123

1.7.6 Insertar código en el botón de comando ..........................................................126

1.7.7 Crear Formularios .............................................................................................128

1.7.8 Cargar Un Formulario En Excel .........................................................................132

1.7.9 Programar Un Formulario .................................................................................134


1.1 Validación de datos

1.1.1 Introducción Microsoft Office Excel es una herramienta eficaz que se puede usar para analizar y compartir

información para tomar decisiones oportunas en el área ejecutiva y empresarial.

Entre las herramientas de Excel está la “Validación de datos” que se usa para controlar el tipo

de datos o los valores que los usuarios pueden escribir en una determinada celda.

La “Validación de datos” es sumamente útil cuando se desea compartir un libro con otros

miembros de la organización y se desea que los datos que se escriban en él sean exactos y

coherentes.

Por ejemplo, es posible que se desee restringir la entrada de datos a un intervalo determinado

de fechas, limitar las opciones con una lista o asegurarse de que sólo se escriben números

enteros positivos.

En este libro se aprenderá cómo funciona la “Validación de datos” en Excel y se describirá

brevemente las diferentes técnicas de validación de datos disponibles.

1.1.2 ¿Cuándo es útil la validación de datos? Como ya se mencionó anteriormente la “validación de datos” es sumamente útil cuando se

desea compartir un libro o archivo de Ms Excel con otros miembros de la organización y se desea

que los datos que se escriban en él sean exactos y coherentes.

Se puede aplicar la “validación de datos” para los siguientes casos:

1 Restringir los datos a elementos predefinidos de una lista.

2 Restringir los números que se encuentren fuera de un intervalo específico.

3 Restringir las fechas que se encuentren fuera de un período de tiempo específico.

4 Restringir las horas que se encuentren fuera de un período de tiempo específico.

5 Limitar la cantidad de caracteres de texto.

6 Validar datos según fórmulas o valores de otras celdas.

1.1.3 Herramientas de validación de datos La herramienta de “Validación de datos” se encuentra en la ficha “Datos” en el grupo

de “Herramientas de datos”.


Ilustración 1.-Ficha Datos

El grupo “Herramientas de datos” contiene:

• Texto en columnas

• Relleno rápido

• Quitar duplicados

• Validación de datos

• Consolidar

• Relaciones

• Administrar modelo de datos

En la siguiente ilustración se muestra el grupo “Herramientas de datos” colapsado (en pantalla

pequeñas) y sin colapsar (en pantallas amplias):

Ilustración 2.-Grupo “Herramientas de datos”

La opción “Validación de datos” contiene una sub-opción “Validación de datos”:


Ilustración 3.- Sub-opción "Validación de datos"

Al dar clic en la sub-opción “Validación de datos” se visualiza el cuadro de diálogo “Validación

de datos” que contiene tres pestañas:

• Configuración.

• Mensaje de entrada.

• Mensaje de error.

La siguiente ilustración muestra la caja de diálogo titulada “Validación de datos”:

Ilustración 4.- Caja de diálogo "Validación de datos"

1 La pestaña “Configuración” permite especificar el criterio de validación.

2 La pestaña “Mensaje de entrada” permite configurar el mensaje de entrada que alertará

al usuario sobre el tipo de datos que puede ingresar.

3 La pestaña “Mensaje de error” permite configurar el mensaje de error en el caso de que

el usuario ingrese datos fuera del criterio de validación.

1.1.4 Celdas con longitud de texto Introducción


La validación de datos permite que Excel supervise el ingreso de información en una hoja de

cálculo sobre la base de un conjunto de criterios previamente establecidos.

Se aprenderá a validar una celda con una determinada longitud de texto.

Práctica

En la proforma que solicita el RUC del cliente, se puede configurar una celda para permitir

únicamente el ingreso del número de RUC (Registro Único del Contribuyente; para el caso de

Ecuador) con una longitud de texto de máximo 13 (trece) caracteres.

Además, cuando el usuario selecciona la celda puede ser alertado mediante la configuración de

un mensaje de entrada. De igual forma, si el usuario trata de ingresar un texto mayor a 13

caracteres, es posible configurar la validación para que Excel emita un mensaje de error.

La práctica que descargo anteriormente tiene dos hojas: “Proforma CON validación” donde se

encuentra un caso resuelto y “Proforma SIN validación” en donde está invitado a probar la

primera práctica.

La solución al ejercicio planteado es la siguiente:

• Seleccione la celda C5 y haga clic sobre el botón derecho.

• Seleccione la opción “Formato de celda…” sobre el menú desplegable.

Ilustración 5.- Opción "Formato de celda"

En el cuadro de diálogo “Formato de celdas”,


• Haga clic en la pestaña “Número”.

• Seleccione “Texto”, pues es necesario que la celda reciba trece “caracteres”.

• Presiones sobre el botón “Aceptar”.

De esta manera la celda C5 tiene formato tipo texto.

Ilustración 6.-Dar formato texto a la celda C5

Para validar la celda con longitud de texto igual a 13 caracteres se realiza lo siguiente:

• Seleccione la celda “C5”

• Seleccione la pestaña “Datos”

• En el grupo de “Herramientas de datos”, seleccione “Validación de datos”.

• Haga clic en la opción “Validación de datos...”

Ilustración 7.- Opción "Validación de datos"

Se presenta el cuadro de diálogo “Validación de datos”.


• Seleccione la primera pestaña en este caso “Configuración”.

• En “Permitir”, seleccione de la lista desplegable la opción “Longitud del texto”.

• En la opción “Datos”, seleccione “igual a”.

• En “Longitud de texto” digite el número “13”.

• Para finalizar haga clic en el botón “Aceptar”.

Ilustración 8.- Validación por "Longitud de texto"

Ahora puede probar el funcionamiento de la validación:

Digite en la celda “C5” el siguiente número de RUC: “1234567890001”, si el valor ingresado tiene

la longitud correcta no se desplegará ningún mensaje de error.

Ilustración 9.- Ingreso de un número de RUC correcto

Ahora, para el caso en que el número de RUC sobrepase el número de caracteres configurados,

observe el mensaje de error que emite Excel.


Ingrese el número: 18024529440012345

Ilustración 10.- Mensaje de error debido al ingreso de un número de RUC incorrecto

1.1.5 Mensajes de entrada Al escribir datos no válidos en una celda el mensaje de error desplegado dependerá de cómo se

haya configurado la validación de datos.

Es posible mostrar un mensaje de entrada cuando el usuario selecciona una celda configurada

con validación de datos.

Este tipo de mensaje aparece cerca de la celda. Los mensajes de entrada se usan para orientar

a los usuarios acerca del tipo de datos que deben escribirse en una determinada celda.

Ilustración 11.- Mensaje de ingreso de datos

Para configurar un mensaje de entrada realice lo siguiente:

Seleccione la celda a configurar en este caso la celda “C5” y haga clic en la pestaña Datos. En el

grupo de “Herramientas de datos”, seleccione “Validación de datos”.

Luego realice las siguientes acciones:


1.- En el cuadro de diálogo “Validación de datos”, haga clic en la pestaña “Mensaje de

entrada”.

2.- Active con un visto la casilla de verificación “Mostrar mensaje de entrada al

seleccionar la celda”.

3.- En la opción “Título”, digite el título para el mensaje de entrada, por ejemplo, RUC.

4.- En “Mensaje de entrada”, digite el texto para el mensaje de entrada, por ejemplo; “Por

favor ingrese un número que contenga 13 dígitos”.

5.- Presione sobre el botón “Aceptar”

Ilustración 12.- Configuración del mensaje de entrada

Ahora se puede visualizar el mensaje de entrada cuando se selecciona la celda “C5”.

1.1.6 Mensajes de error Este tipo de mensaje aparece cuando el usuario escribe datos no válidos.

El mensaje que aparece por omisión ante un error es el siguiente:

Ilustración 13.- Mensaje de error por omisión


Para ingresar de nuevo los datos se debe presionar sobre el botón hacer clic en “Reintentar”,

mientras para salir de este cuadro de diálogo se debe presionar sobre el botón “Cancelar”.

Para personalizar el mensaje de error seleccionar la celda a configurar en este caso la celda “C5”

y hacer clic en la pestaña “Datos”. Luego en el grupo de “Herramientas de datos”

seleccionar “Validación de datos” y en el cuadro de diálogo “Validación de datos” realizar las

siguientes acciones

1. Hacer clic en la pestaña “Mensaje de error”.

2. Activar con un visto la casilla de verificación “Mostrar mensaje de error si se

introducen datos no válidos”.

3. En la opción “Título”, digitar el título para el mensaje de error, por ejemplo: RUC.

4. En “Mensaje de error”, digitar el texto para el mensaje de error, por ejemplo; “El

número de RUC sólo pude contener 13 caracteres”.

5. Finalmente, presionar sobre el botón “Aceptar”

Ilustración 14.- Configuración del "Mensaje de error"

Si se ingresan un número distinto a 13 dígitos en la celda “C5” se desplegará el mensaje de

error configurado.


Ilustración 15.- Mensaje de error en validación de datos

Los mensajes de error pueden configurarse en tres estilos. Los estilos de mensajes de error son

los siguientes:

1. Estilo Detener: Este tipo de error permite detener el ingreso de datos. Evita que los usuarios

escriban datos no válidos en una celda. Un mensaje de alerta “Detener” presenta dos

opciones “Reintentar” y “Cancelar”.

Ilustración 16.- Estilo "Alto"

2. Estilo Advertencia: Advierte a los usuarios que los datos que se han escrito no son válidos,

pero no les impide escribirlos.

Cuando aparece un mensaje de alerta Advertencia, los usuarios pueden hacer clic en “Sí” para

aceptar la entrada no válida, en “No” para editarla o en “Cancelar” para quitarla.


Ilustración 17.- Estilo "Advertencia"

3. Estilo Información: Informa al usuario que los datos que ha escrito no son válidos, pero no le

impide escribirlos. Este tipo de mensaje de error es el más flexible.

Cuando aparece un mensaje de estilo Información, el usuario puede hacer clic

en “Aceptar” para aceptar el valor no válido o en “Cancelar” para rechazarlo.

Ilustración 18.- Estilo de mensaje "Información"

Los mensajes de entrada y de error sólo aparecen cuando los datos se escriben directamente en

las celdas.

No aparecen en los siguientes casos:

• El usuario escribe datos en la celda mediante copia o relleno.

• Una fórmula en la celda calcula un resultado que no es válido.


• Una macro (macro: acción o conjunto de acciones utilizados para automatizar

tareas) especifica datos no válidos en la celda.

1.1.7 Validar números enteros dentro de límites Introducción

La validación de datos permite que Excel supervise el ingreso de información en una hoja de

cálculo sobre la base de un conjunto de criterios previamente establecidos.

En este caso se aprenderá a validar un rango de celdas con números enteros dentro de límites

permitidos.

Práctica:

Validar el rango de D10 a D19 con números enteros entre 1 y 20. De tal forma que se bloquee

el ingreso de datos no permitidos.

Ilustración 19.- Selección del rango de celdas a validar

El libro descargado anteriormente, contiene dos hojas: “FACTURA con validación” que es el

ejercicio resuelto y “FACTURA sin validación” en donde le invito a realizar la práctica.

En esta práctica se aprenderá a validar el rango de D10 a D19 con números enteros entre 1 y 20.

Para llegar a la solución se debe realizar lo siguiente:

1. Seleccionar las celdas que se desea validar. En este caso seleccionar el rango de

D10 a D19.

2. En el grupo “Herramientas de datos” de la ficha “Datos”, seleccionar la

opción “Validación de datos”.

3. En el cuadro de diálogo “Validación de datos”, hacer clic en la pestaña

“Configuración”.

4. En el cuadro “Permitir”, seleccionar “Número entero”.

5. En el cuadro “Datos”, seleccionar el tipo de restricción que se desea configurar.

Por ejemplo, para definir los límites superior e inferior, seleccionar “entre”.

6. Escribir el valor mínimo, máximo o específico que desea permitir.

7. Finalmente presionar sobre el botón “Aceptar”


Ilustración 20.- Validación por números enteros

Para especificar cómo se desea administrar los valores en blanco (nulos), se activa o desactiva

la casilla “Omitir blancos”.

Ahora se debe configurar el “Mensaje de entrada” mediante los siguientes pasos dentro de la

caja de diálogo “Validación de datos”:

1. Seleccionar la pestaña “Mensaje de entrada”.

2. Hacer clic en la casilla de “Mostrar mensaje de entrada al seleccionar la celda”.

3. Digitar un “Título” para tu mensaje de entrada.

4. Digitar un texto para el “Mensaje de entrada”.

5. Finalmente, hacer clic en “Aceptar”.


Ilustración 21.- Configuración de un mensaje de entrada

Se puede observar que, al seleccionar una celda, se visualiza el mensaje configurado en la

pestaña mensaje de entrada.

Ilustración 22.- Mensaje de entrada en validación de números

Ahora para configurar el mensaje de error se realiza lo siguiente:

1. Seleccionar la pestaña “Mensaje de error”.

2. Hacer clic en la casilla de “Mostrar mensaje de error si se introducen datos no

válidos”.


3. Seleccionar el “Estilo” de error.

4. Digitar el “Título” que se visualizará en la ventana de error.

5. Digitar un texto para el “Mensaje de error”.

6. Finalmente, presionar sobre el botón “Aceptar”.

Ilustración 23.- Configuración de mensaje de error en validación de números

Se puedes observar, que, al ingresar un dato no permitido, Excel despliega el cuadro de diálogo

con el mensaje de error.

Ilustración 24.- Despliegue del mensaje de error en validación de números

1.1.8 Comprobar entradas no válidas Introducción


Al recibir hojas de cálculo de usuarios que pueden haber introducido datos no válidos, Excel

permite configurar la presentación de círculos rojos alrededor de los datos que no cumplan los

criterios de validación. De tal forma que se agilite la búsqueda de errores en las hojas de

cálculo.

Para esto se utilizarán los botones “Rodear con un círculo datos no válidos” y “Borrar círculos

de validación” en la barra de herramientas Auditoría.

Práctica:

Validar el rango de D10 a D19 con números enteros entre 1 y 20.

De tal forma que permita el ingreso de otros valores bajo previa confirmación. Aplicar el estilo

de Advertencia.

Ilustración 25.- Descripción de práctica de validación de números

En esta práctica se aprenderá a validar el rango de D10 a D19 con números enteros entre 1 y 20.

De tal forma que permita el ingreso de otros valores bajo previa confirmación. Se aplicará el

estilo de Advertencia.

Para dar solución al problema planteado se debe realizar las siguientes acciones:

• Seleccionar las celdas que se desea validar. En este caso el rango de D10 a D19.

• En el grupo “Herramientas de datos” de la ficha “Datos”, seleccionar “Validación

de datos”.

• En el cuadro de diálogo “Validación de datos”, seleccionar la

pestaña “Configuración”.

• En el cuadro “Permitir”, seleccionar “Número entero”.

• En el cuadro “Datos”, seleccionar el tipo de restricción que se desea aplicar. Por

ejemplo, para definir los límites superior e inferior, seleccionar “entre”.


• Escribir el valor mínimo, máximo que se desea permitir.

Para especificar cómo se desea administrar los valores en blanco (nulos), activar o desactivar la

casilla “Omitir blancos”.

Ahora se debe configurar el “mensaje de entrada”.

Para esto se realiza lo siguiente:

• Seleccionar la pestaña “Mensaje de entrada”.

• Seleccionar la casilla de “Mostrar mensaje de entrada al seleccionar la celda”.

• Digitar un “Título” para el mensaje de entrada (máximo 225 caracteres).

• Digitar un texto para el “Mensaje de entrada” (máximo 225 caracteres).

• Finalmente, presionar sobre el botón “Aceptar”.

Luego se debe configurar el “mensaje de error”.

Para esto se realizan las siguientes acciones:

• Seleccionar la pestaña “Mensaje de error”.

• Seleccionar la casilla de “Mostrar mensaje de error si se introducen datos no

válidos”.

• Seleccionar el “Estilo” de error. En este caso el estilo es “Advertencia”.

• Digitar el “Título” que se visualizará en la ventana de advertencia.

• Digitar un texto para el “Mensaje de advertencia”.


Para probar que todo funciona como debe, ingresar un dato no permito en una de las celdas

configuradas anteriormente. Se puede observar que despliega el mensaje de

error configurado.

Ilustración 26.- Ingreso de un dato no permitido en validación de números


Como parte de este ejercicio ingresar datos permitidos y no permitidos, de tal forma que se

pueda comprobar las entradas no válidas.

Ilustración 27.- Ingreso de datos válidos e inválidos

Para determinar qué valores no cumplen la regla de validación, realizar lo siguiente:

• Seleccionar la ficha “Datos” y en el grupo de “Herramientas de datos”, seleccionar

“Validación de datos”.

• Seleccionar “Rodear con un círculo datos no válidos”.

Ilustración 28.- Opción "Rodear con un círculo datos no válidos"

Después de aplicar la última instrucción todos los datos inválidos ingresados aparecerán

rodeados de un círculo.


Ilustración 29.- Datos inválidos rodeados de un círculo

Este círculo es de utilidad para mostrar de forma temporal y visual los datos que contiene una

hoja de cálculo que no cumplen las reglas de validación.

El círculo desaparecerá cuando se corrijan los datos de las celdas.

Al guardar el archivo se deja de mostrar los círculos rojos.

Para borrar los círculos de validación, seleccionar la opción “Borrar círculos de validación”.

Ilustración 30.- Opción "Borrar círculos de validación"

1.1.9 Validar Fechas

Introducción

Al validar fechas se puede restringir la entrada de datos a una fecha dentro de un período de

tiempo.

En este caso se aprenderá a validar fechas dentro de un rango permitido.

Práctica

Validar la celda “F4”, en un período de tiempo entre la fecha actual y 4 días desde la fecha actual.

Además cuando el usuario seleccione la celda se puede alertar al usuario mediante la


configuración de un “mensaje de entrada”. De igual forma, si el usuario trata de ingresar una

fecha no permitida, se puedes configurar para que Excel emita un” mensaje de error”, en este

caso aplica el estilo “Información”.

Después de seleccionar la celda que se desea validar, en el grupo “Herramientas de datos” de

la ficha “Datos”, seleccionar la opción “Validación de datos”. Luego realizar las siguientes

acciones:

1. En el cuadro de diálogo “Validación de datos”, seleccionar la ficha


2. En el cuadro “Permitir” seleccionar “Fecha”.

3. En el cuadro “Datos”, seleccionar el tipo de restricción que se desea, en este caso

“entre”.

4. Escribir “=HOY()” en “Fecha inicial”.

5. Escribir “=HOY()+4” en “Fecha final”.

6. Presionar sobre el botón “Aceptar”.

Ilustración 31.- Validación de fechas

Para especificar cómo se desea administrar los valores en blanco (nulos), activar o desactivar la

casilla “Omitir blancos”.

Ahora se debe configurar el mensaje de entrada.

Para esto realizar lo siguiente:

1. Seleccionar la pestaña “Mensaje de entrada”.

2. Seleccionar la casilla de “Mostrar mensaje de entrada al seleccionar la celda”.

3. Digitar un “Título” para el mensaje de entrada (máximo 225 caracteres).

4. Digitar un texto para el mensaje de entrada, (máximo 225 caracteres).

5. Finalmente, presionar sobre el botón Aceptar.


Ilustración 32.- Configuración del mensaje de entrada en validación de fechas

Para configurar el mensaje de error, de tal forma que permita el ingreso de otros valores bajo

previa confirmación realizar lo siguiente:

1. Seleccionar la pestaña “Mensaje de error”.

2. Seleccionar la casilla de “Mostrar mensaje de error si se introducen datos no

válidos”.

3. Seleccionar el “Estilo” de error. En este caso usar el estilo “Advertencia”.

4. Digitar el “Título” que se visualizará en la ventana de advertencia.

5. Digitar un texto para el “Mensaje de error”.

6. Finalmente, presionar sobre el botón Aceptar.


Ilustración 33.- Configuración de mensaje de error en validación de fecha

Es necesario configurar el formato de la celda de fecha según “día/mes/año” de tal manera que

se visualice de esta manera 06/Jun/10.

Ilustración 34.- Aplicación del formato fecha sobre la celda validada

Observación: Si se cambia la configuración de validación para una celda, automáticamente se

pueden aplicar los cambios a todas las demás celdas que tienen la misma configuración. Para

ello, abrir el cuadro de diálogo “Validación de datos” y, a continuación, activar la casilla “Aplicar

estos cambios a otras celdas con la misma configuración”.


Ilustración 35.- Opción para aplicar configuración de validación a todas las celdas de igual formato

1.1.10 Validar Listas Introducción

Esta herramienta permite que Excel supervise el ingreso de información en una hoja de cálculo

sobre la base de un conjunto de criterios previamente establecidos.

Se puedes crear una lista de entradas que se aceptarán en una celda de la hoja de cálculo y a

continuación, restringir la celda para que acepte únicamente las entradas de la lista mediante

el comando del menú “Datos” opción “Validación”.

El usuario que introduzca los datos puede hacer una selección en la lista.

Práctica:

Validar la celda C5, con una lista de datos desde I6 a I15 localizada en la misma hoja. Al validar

le permitirá seleccionar el número de RUC (Registro Único del Contribuyente) de la lista

desplegable.

Además, cuando el usuario seleccione la celda puedes alertarlo al configurar un mensaje de

entrada. De igual forma, si el usuario trata de ingresar un RUC no permitido, se puedes

configurar la validación para que Excel emita un mensaje de error, en este se aplicará el estilo

grave.

Para resolver el ejercicio planteado realizar lo siguiente:

1. Seleccionar la celda que desea validar.

2. En el grupo “Herramientas de datos” de la ficha “Datos”, seleccionar “Validación

de datos”.


3. En el cuadro de diálogo “Validación de datos”, seleccionar la

ficha “Configuración”.

4. En el cuadro “Permitir”, seleccionar “Lista”.

5. En “Origen”, seleccionar el rango de datos que será parte de la lista desplegable de

la celda “C5”.


Ilustración 36.- Configuración para validación por listas

Luego de validar se puede visualizar el contenido de la lista desplegable en la celda “C5”.

Ilustración 37.- Lista de valores mediante validación

Si se desea utilizar otra hoja de cálculo para escribir la lista se puede definir un nombre sobre

el rango utilizado.

Por ejemplo, validar la celda “F6” con las ciudades localizadas en la hoja “Ciudades”.

Para solucionar este caso en forma simple se puede definir un nombre.

Para definir un nombre seguir los siguientes pasos

• Seleccionar el rango de celdas al cual se desea asignar un nombre.

• En este caso seleccionar el rango de “A1” a “A5” de la hoja “Ciudades”.


• Seleccionar el “Cuadro de Nombres” localizado en el extremo izquierdo de la “Barra

de fórmulas”.

• Escribir el nombre de las celdas, por ejemplo, “Ciudades” y presionar la tecla “ENTER”.

Ilustración 38.- Asignación del nombre "ciudades" a un rango

Para realizar la validación por listas usando el nombre creado proceder de la siguiente manera:

1. Seleccionar la celda donde se desea crear la lista desplegable.

2. En el grupo “Herramientas de datos” de la ficha “Datos”, seleccionar la opción

“Validación de datos”.

3. En el cuadro de diálogo “Validación de datos” seleccionar la

ficha “Configuración”.

4. En el cuadro “Permitir”, seleccionar “Lista”.

5. Para especificar la ubicación de la lista de entradas válidas, siga uno de los

procedimientos siguientes:

6. Si la lista está en la hoja de cálculo actual, escribir una referencia a la lista en el

cuadro “Origen”.

7. Si la lista está en otra hoja de cálculo, escribir el nombre definido para la lista

en el cuadro “Origen”.

8. En ambos casos, debe asegurarse de que la referencia o el nombre está

precedido del signo igual (=). Por ejemplo, escribir “=ciudades”.

9. Asegurarse de que esté activada la casilla de verificación “Celda con lista

desplegable”.


Ilustración 39.- Validación por listas

Para especificar si la celda se puede dejar en blanco, activar o desactivar la casilla de

verificación “Omitir blancos”.

Otra opción es mostrar un mensaje de entrada cuando se haga clic en la celda.

Ilustración 40.- Despliegue de una lista de validación desplegable

Notas:

El ancho de la lista desplegable está determinado por el ancho de la celda que tiene la validación

de datos.

El número máximo de entradas que puede tener en una lista desplegable es 32767.

Si la lista de validación está en otra hoja de cálculo y se desea evitar que los usuarios la vean o

realicen cambios en ella, se puede ocultar y proteger dicha hoja de cálculo.

1.1.11 Buscar celdas con validación Para buscar todas las celdas con validación de datos realizar lo siguiente:

En el grupo “Edición” de la ficha “Inicio”, presionar sobre la flecha situada junto a la opción

“Buscar y seleccionar” y, a continuación, seleccionar la opción “Ir a especial”. Luego,

seleccionar “Validación de datos” y la opción “Todos”.


Ilustración 41.- Opción "Ir a Especial"

Si se encuentran celdas que contienen validación de datos, estas celdas se señalan; en caso

contrario, se mostrará el mensaje "No se encontraron celdas".

Ilustración 42.- Identificación de celdas con validación de datos

1.1.12 Borrar validación de datos

Para borrar o quitar la validación de datos, realizar lo siguiente:


• Seleccionar las celdas donde ya no se desea validar datos.

• En el grupo “Herramientas de datos” de la ficha “Datos”, seleccionar “Validación

de datos”.

• En el cuadro de diálogo “Validación de datos”, seleccionar la

ficha “Configuración” y, a continuación, seleccionar la opción “Borrar todos”.

Ilustración 43.- Eliminando validación de datos de una celda

1.2 Fórmulas y funciones

1.2.1 Funciones Introducción

Microsoft Office Excel es una herramienta eficaz que permite analizar datos en el área ejecutiva

y empresarial. Entre las opciones con las que cuenta Ms Excel, están las funciones, muy útiles al

momento agilizar operaciones y cálculos para tomar decisiones oportunas.

Esta unidad es importante en el ámbito del curso, pues en su comprensión y manejo, está la

base de Ms Excel.

El comprender el trabajo de las funciones, ahorra tiempo, pues ya no se tiene que hacer cálculos

que realizan muchas de estas funciones. Por eso, esta unidad es fundamental para la buena

utilización de Excel.

El propósito de esta sección es familiarizar al lector sobre el manejo de funciones ya definidas

por Ms Excel, con el propósito de agilizar la creación de hojas de cálculo, mediante la

comprensión de la sintaxis de éstas, así como la aplicación y utilidad del asistente de funciones.


1.2.2 Funciones - Sintaxis

Introducción

Entra las herramientas con la que cuenta Ms Excel están las funciones. Estas permiten realizar

operaciones complejas de forma sencilla, empleando valores numéricos, o de texto.

Una función como cualquier dato se puede escribir directamente en la celda si se conoce su

sintaxis, pero Excel 2016 dispone de una ayuda o asistente para utilizarlas y así resulta más fácil

trabajar con ellas.

Sintaxis de una función

Casi todas las funciones tienen una estructura similar y esta estructura se describe a

continuación:

El nombre de la función está antecedido del signo igual =. Después del nombre están

los argumentos de la función, estos se colocan entre paréntesis y están separados por comas (,)

punto y comas (;) o dos puntos ( : ); depende de cómo esté configurado el separador de listas en

Ms Windows.

Ilustración 44.-Ejemplo de sintaxis de la función SUMA

Si se desea especificar una función en una celda proceder de la siguiente manera:

• Situarse en la celda donde se desea incluir una función

• Seleccionar la opción “Insertar función” que se encuentra en la pestaña “Fórmulas”


Ilustración 45.- Opción "Insertar función"

También se puede seleccionar el botón “fx” que se encuentra junto a la barra de fórmulas.

Ilustración 46.- Botón "fx"

Una vez realizadas cualquiera de las acciones descritas anteriormente se desplegará el cuadro

de diálogo “Insertar función”.


Ilustración 47.- Caja de diálogo "Insertar función"

Excel permite buscar la función que se requiere escribiendo una breve descripción de esta en el

recuadro “Buscar una función”, luego de lo cual se debe presionar sobre el botón “Ir”. De esta

forma, no es necesario conocer cada una de las funciones que incorpora Excel ya que mostrará

en el cuadro “Seleccionar una función”, las funciones que tienen que ver con la descripción

escrita.

Para que la lista de funciones no sea tan extensa se puede seleccionar previamente una

categoría de la lista desplegable “O seleccionar una categoría”. Esto hará que en la lista

“Seleccionar una función” sólo aparezcan las funciones de la categoría elegida y se reduzca

por lo tanto el número de opciones para elegir. Si no estamos muy seguros de la categoría se

puede seleccionar “Todo”.

1.2.3 Funciones Lógicas: Si .

La función “SI” devuelve un valor si la condición especificada es evaluada como “VERDADERO” y

otro valor si dicho argumento es evaluado como “FALSO”.

Sintaxis

SI(prueba_lógica; valor_si_verdadero; valor_si_falso)

Prueba_lógica: Es cualquier valor o expresión que pueda evaluarse como

“VERDADERO” o “FALSO”.

Valor_si_verdadero: Es el valor que se devuelve la función si el argumento


“Prueba_lógica” es “VERDADERO”.

Valor_si_falso: Es el valor que se devuelve la función si el argumento “Prueba_lógica”

es “FALSO”.

Práctica

En la columna observación se debe visualizar el texto “APROBADO” si el promedio es

mayor o igual a 7, caso contrario se debe visualizar “REPROBADO”.

Primero se debe plantear el ejercicio e identificar los argumentos de la función

lógica “SI”:

Una vez identificados los argumentos de la función; la solución al ejercicio planteado es

la siguiente:

• Seleccionar la celda en donde se aplicará la función “SI”.

• Presionar sobre el botón “fx” para visualizar el cuadro de diálogo “Insertar

función”.

• En la sección “O seleccionar una categoría” seleccionar “Lógicas”.

• En la sección “Selecciona una función”, seleccionar “SI”.

• Presionar sobre el botón “Aceptar”.

Ilustración 48.- Selección de la función "SI"

Se visualiza el cuadro de diálogo “Argumentos de función”, dentro de cada argumento

especificar lo siguiente:

• En la sección “Prueba_lógica” digitar la condición que identificada

anteriormente en el problema; en este caso “F2>7”. Pues en la celda “F2” se

tiene el valor del promedio. Recuerdar que la condición del ejercicio solicita los

promedios mayores a 7.

• En la sección “Valor_si verdadero” ingresar la condición a devolver si la prueba

lógica es verdadera. En este caso digitar “APROBADO”.


• En la sección “Valor_si_falso” indicar lo que se devolverá si la prueba lógica es

falsa. En este caso digitar “REPROBADO”.


Ilustración 49.-Parámetros de la función "SI"

Copiando la fórmula a todas las celdas inferiores el resultado que se visualiza es el

siguiente:

Ilustración 50.-Resultado de la ejecución de la función "SI"

La estructura de la función SI queda de la siguiente forma:

Ilustración 51.- Estructura de una función típica "SI"


1.2.4 Más Funciones Lógicas A continuación, se muestra una lista de otras funciones pertenecientes a esta

categoría.

FUNCIÓN DESCRIPCIÓN

FALSO Devuelve el valor lógico FALSO.

NO Cambia FALSO por VERDADERO y VERDADERO por FALSO.

O Comprueba si alguno de los argumentos es VERDADERO y devuelve VERDADERO o FALSO. Devuelve FALSO si todos los argumentos son FALSO.

SI Comprueba si se cumple una condición y devuelve un valor si se evalúa como VERDADERO y otro valor si se evalúa como FALSO.

SI.ERROR Devuelve un valor si la expresión es un error y otro valor si no lo es.

SI.ND Devuelve el valor que se especifica, si la expresión se convierte en #N/A. De lo contrario, devuelve el resultado de la expresión.

VERDADERO Devuelve el valor lógico VERDADERO.

XO Devuelve un “Exclusive Or” (O exclusivo) lógico de todos los argumentos.

Y Comprueba si todos los argumentos son VERDADEROS y devuelve VERDADERO o FALSO. Devuelve FALSO si alguno de los argumentos es FALSO.

1.2.5 Funciones De Búsqueda Y Referencia: Buscarv Función BuscarV

La función “BUSCARV”, busca un valor específico en la primera columna de una matriz y devuelve, en la misma fila, un valor de otra columna de dicha matriz. La “V” de “BUSCARV” significa búsqueda vertical. Nota: En Excel 2010 sin el Service Pack 1 la función se llama CONSULTAV

Sintaxis

BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)

• Valor_buscado: Es el valor que se va a buscar en una matriz. El mismo que debe

estar en la primera columna de una matriz.

• Matriz_buscar_en: Es la matriz en la que se buscará el “Valor_buscado”.

• Indicador_columnas: Es el número de la columna desde la cual debe devolverse

el valor coincidente. Si el argumento “Indicador_columnas” es igual a 1, la

función devuelve el valor de la primera columna. Si el argumento

“Indicador_columnas” es igual a 2, devuelve el valor de la segunda columna y

así sucesivamente.

• Ordenado: Es un valor lógico que especifica si “BUSCARV” va a buscar con

coincidencia exacta o aproximada. Cero (0) o FALSO, indica que se debe usar una

coincidencia exacta en la búsqueda. Uno (1) o VERDADERO indica que debe

usarse una coincidencia aproximada en la búsqueda.


“BUSCARV” devuelve “#N/A” si el dato a buscar no se encuentra en la “Matriz_buscar_en”.

Práctica

Al digitar el número de “RUC” sobre el formulario de proforma, visualizar el nombre del

cliente, la dirección y el teléfono. Los datos se obtendrán de la base de datos

“CLIENTES”.

Recordar que para aplicar la función “BUSCARV” el dato a buscar debe estar en la

primera columna de la matriz de búsqueda. En este caso observar que el número

de “RUC” está localizado en la primera columna de la matriz CLIENTES. caso contrario

no se podrá aplicar la función “BUSCARV”.


• Selecciona la celda en donde se aplicará la función “BUSCARV”. En nuestro

caso dicha celda es la “C4” pues allí se colocará el nombre del cliente buscado

por medio de su RUC.


función”.

• En la sección “O seleccionar una categoría” seleccionar “Búsqueda y

referencia”.

• En la sección “Selecciona una función” seleccionar “BUSCARV”.


Ilustración 52.- Selección de la función "BUSCARV"

Se visualiza el cuadro de diálogo “Argumentos de función” allí realizar las siguientes

acciones:


1. En la sección “Valor_buscado” seleccionar el valor a buscar. En este caso

seleccionar la celda que contiene el número de “RUC”, es decir la celda “C5”,

pues será en esta celda donde se especifique el valor a buscar.

2. En la sección “Matriz_buscar_en” seleccionar la base de datos que se

encuentra en la hoja “CLIENTES”.

Ilustración 53.- Selección de la matriz de búsqueda

3. En “Indicador_columnas” digitar el número de columna en la que se

encuentra el nombre del cliente. Para este caso digitar dos (2). Pues en la

columna 2 está el nombre del cliente. En la número 3 está la dirección y en el 4

el teléfono.

4. En la sección “Ordenado” digitar el cero o “FALSO” para que la función

“BUSCARV” devuelva el nombre exacto del cliente al que corresponda ese

número de “RUC”.



Ilustración 54.- Argumentos de la función "BUSCARV"

Al especificar un número válido de “RUC” sobre la celda “C5” se desplegará el nombre

del cliente a quien pertenece en la celda “C4”.

Ilustración 55.- Resultado de usar la función "BUSCARV"

1.2.6 Vídeo: Función Buscarv Este vídeo explica paso a paso cómo aplicar la función “BUSCARV”.

1.2.7 Funciones De Información: Eserror Función EsError

La función “ESERROR”, comprueba si un valor es un error (#N/A, #¡VALOR!, #¡REF!, #¡DIV!, #¡NUM!, #¿NOMBRE? O #NULO!) y devuelve “VERDADERO” si encuentra un error en una expresión o “FALSO” si no encuentra un error.

Sintaxis

ESERROR(valor) Valor: Es el valor que se desea probar. El argumento de valor puede ser una celda vacía (en blanco), un error, un valor lógico, texto, un número, un valor de referencia, o un nombre que haga referencia a alguno de los anteriores.


Práctica Aplicar la función ESERROR para la operación de división Valor1/Valor2, donde Valor2 puede ser cero.

Para iniciar la práctica descargar el ejercicio mediante el siguiente enlace. AQUÍ. (17.funcion_eserror.xlsx) La solución del ejercicio planteado es la siguiente:

• Seleccionar la celda en donde se aplicarás la función “ESERROR”.


función”.

• En la sección “O seleccionar una categoría” seleccionar “Información”.

• En la sección “Selecciona una función”, seleccionar “ESERROR”.


Ilustración 56.- Selección de la función "ESERROR"

Se visualizará la baja de diálogo “Argumentos de función” donde se debe seleccionar

el valor a comprobar (dentro de la sección Valor), en este caso la referencia “C2” y

luego se debe presionar sobre el botón “Aceptar”.


Ilustración 57.- Parámetro de la función "ESERROR"

El resultado de aplicar la función “ESERROR” para comprobar una celda donde se

realiza una división por cero es “VERDADERO”.

Ilustración 58.- Resultado de aplicar la función "ESERROR"

1.2.8 Funciones Anidadas .

En algunos casos, es necesario utilizar una función como uno de los argumentos de otra

función.

Los argumentos son los valores que utiliza una función para llevar a cabo operaciones o

cálculos. El tipo de argumento que utiliza una función es específico de esa función. Los

argumentos más comunes que se utilizan en las funciones son números, texto,

referencias de celda y nombres.

Cuando se utiliza una función anidada como argumento, esta deberá devolver el mismo

tipo de valor que el que utilice el argumento.

Práctica

Realizar la validación de una división de números Valor1 y Valor 2, utilizando la función

“SI” y la función “ESERROR”, de tal manera que si un error es detectado en dicha

operación se conserve la celda en blanco, caso contrario que se despliegue el valor de la

operación realizada.

Para iniciar la práctica descargar el ejercicio mediante el siguiente enlace. AQUÍ

(18.funcion_eserror_anidada.xlsx).

https://aula2.virtualepn.edu.ec/pluginfile.php/116183/mod_book/chapter/17571/Copia%20de%2010funcion_ESERROR_anidada.xlsx

https://aula2.virtualepn.edu.ec/pluginfile.php/116183/mod_book/chapter/17571/Copia%20de%2010funcion_ESERROR_anidada.xlsx


La práctica descargada anteriormente, contiene dos hojas: “ERROR_resuelto” que

contiene el ejercicio resuelto y “ERROR_sin_resolver” en donde puede ser realizada la

práctica.

Primero se debe plantear el problema a resolver en el ejercicio e identificar los

argumentos de función lógica “SI”:

Ilustración 59.- Planteamiento del problema

Como se puede observar el primer argumento de la función “SI” requiere el uso de la

función “ESERROR”, pues está última permite verificar si una operación tiene o no error.

Una vez identificados los argumentos de la función; la solución al ejercicio planteado es

la siguiente:

1. Seleccionar la celda en donde se aplicará la función “SI”.

2. Presionar sobre el botón “fx” para visualizar el cuadro de diálogo “Insertar

función”.

3. En la sección “O seleccionar una categoría” seleccionar “Lógicas”.

4. En la sección “Selecciona una función” seleccionar “SI”.



Ilustración 60.- Selección de la función "SI"

Para anidar la función “ESERROR” dentro de la función “SI”.

• En la sección “Prueba_lógica” ubicar el cursor en la casilla adyacente y

escribir “ESERROR( C2 )” para indicar que se está verificando la validez de la

celda “C2” mediante la función “ESERROR”.

• En la sección “Valor_si_verdadero”, ingresar el valor vacío mediante un par

de comillas.

• En la sección “Valor_si_falso”, ingresar la referencia a la celda “C2”.

• Presionar sobre el botón “Aceptar”

Ilustración 61.-Parámetros de la función "SI" con la función "ESERROR" como primer parámetro.

Para observar la evaluación realizada sobre todas las celdas de la columna “Valor

1/Valor 2” copiar la fórmula en las celdas inferiores. Aquellas celdas que presentan

error aparecen en blanco, mientras que aquella en las cuales se puede realizar la

operación de división muestran el resultado.


Ilustración 62.- Resultado de usar la función "ESERROR" anidada dentro de la función "SI"

Más ejercicios sobre funciones anidadas pueden descargarse desde el siguiente enlace.

AQUÍ. (19.funciones_logicas_anidadas.xlsx)

1.3 Protección de un documento Excel

1.3.1 Protección de un documento Excel Cuando se comparte un libro con otras personas pertenecientes a la organización, en

ocasiones es necesario asegurar la integridad de ciertas partes del libro para que no

puedan ser modificadas. En casos extremos incluso, el acceso a todo el libro debe ser

restringido mediante una clave de acceso. En esta sección se revisará como realizar la

protección de un documento Excel.

1.3.2 Protección de una Hoja de Cálculo Para proteger parte de una hoja de cálculo, contra las modificaciones que se pretendan

realizar por una o varias personas, es necesario proteger primero toda la hoja. Esto se

puede realizar seleccionando todas las celdas de la hoja.

Ilustración 63.- Selección de todas las celdas de una hoja.


Una vez seleccionadas todas las celdas de la hoja, haciendo clic derecho sobre

cualquier parte de la selección se elige la opción “Formato de Celdas”.

Ilustración 64.- Selección de la opción "Formato de celdas"

Ahora se requiere seleccionar la pestaña “Proteger”, seleccionar los cuadros

“Bloqueada y Oculta” y luego presionar sobre el botón “Aceptar”.


Ilustración 65.- Protección de la hoja de cálculo mediante bloqueo de celdas.

Luego es necesario seleccionar todas las celdas que se desea desproteger, es decir

sobre las cuales se quiere dar permiso de acceso y escritura esto se logra haciendo clic

sobre cada una de ellas, mientras se mantiene presionada la tecla Control.

Ilustración 66.- Selección de celdas a desproteger


Una vez seleccionadas las celdas a desproteger, se procede nuevamente a seleccionar

la opción “Formato de celdas” que se encuentra en el menú desplegable que se

obtiene al hacer clic derecho sobre la selección. Dentro de la carpeta “Proteger”,

deseleccionar las opciones “Bloqueada y Oculta”, para liberar las celdas seleccionadas.

Ilustración 67.- Desprotección de las celdas seleccionadas

Para que la protección tenga efecto, se bebe seleccionar la opción “Proteger hoja”,

que se encuentra dentro de la cinta “Revisar”.

En el cuadro de diálogo “Proteger hoja”, se debe establecer la contraseña de

desbloqueo, seleccionar la opción “Proteger hoja y contenido de celdas bloqueadas”

y se deben seleccionar las opciones:

• Seleccionar celdas bloqueadas

• Seleccionar celdas desbloqueadas

Y presionar sobre el botón “Aceptar”, luego reingresar la clave en la caja de diálogo

“Confirmar contraseña” y presionar sobre el botón “Aceptar” de dicha caja.


Ilustración 68.- Protección con contraseña de la hoja de cálculo

Cuando se intenta escribir sobre una celda bloqueada se despliega un mensaje de

error.

Ilustración 69.- Mensaje de error desplegado al tratar de modificar celda protegida

1.3.3 Protección de un Libro

En algunas ocasiones es requerido proteger la estructura de un libro, esto es, que no se

permita, ni borrar ni agregar hojas al mismo. Para proteger un libro se debe seleccionar

la opción “Proteger libro”, que se encuentra dentro de la cinta “Revisar”.

Se debe especificar que se desea proteger la “Estructura”, del libro y para ello es

necesario escribir una contraseña y presionar sobre el botón “Aceptar”. Además, será

necesario especificar nuevamente la contraseña, para que esta quede registrada.


Ilustración 70.- Opción "Proteger Libro"

1.3.4 Protección de acceso a un libro Para proteger el acceso a un libro se debe seleccionar sobre la opción “Guardar como”,

que se encuentra dentro de la cinta “Archivo”. Antes de guardar el documento se debe

seleccionar la opción “Opciones generales” que se encuentra dentro de la lista

desplegable “Herramientas”. Entre otras cosas se debe:

Seleccionar la caja de chequeo “Crear siempre una copia de seguridad”, y luego se

deben especificar dos contraseñas:

• Contraseña de apertura

• Contraseña de escritura

Luego, se deberá presionar sobre el botón “Aceptar”, para reingresar ambas

contraseñas como confirmación.


Ilustración 71.- Menú "Herramientas" el momento de guardar un archivo Excel

Finalmente, se debe guardar el documento con su respectivo nombre.

Ilustración 72.- Guardado del archivo Excel


Cuando se desea abrir un documento protegido contra acceso se debe especificar la

contraseña respectiva. También es necesario especificar la contraseña respectiva para

escritura y de esta manera tener un acceso completo.

Ilustración 73.- Apertura de un libro protegido

1.4 Crea un informe de tabla dinámica

1.4.1 Tablas Dinámicas

Introducción

Microsoft Office Excel es una herramienta eficaz que permite analizar datos en el área

ejecutiva y empresarial.

Las tablas dinámicas brindan la posibilidad de resumir, analizar, explorar y presentar

datos de resumen.

A través de los informes de gráfico dinámico se pueden ver los datos de resumen

contenidos en un informe de tabla dinámica para realizar comparaciones, patrones y

tendencias, a través de los cuales se puede optimizar tareas repetitivas.

Un informe de tabla dinámica es una forma interactiva de resumir rápidamente grandes

volúmenes de datos. Se utiliza un informe de tabla dinámica para analizar datos

numéricos en profundidad y para responder preguntas no anticipadas sobre los

datos.¿Qué Es Un Informe De Tabla Dinámica?

Introducción Un informe de tabla dinámica es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Los informes de tabla dinámica permiten analizar datos numéricos en profundidad y responder preguntas no anticipadas sobre los datos.


Un informe de tabla dinámica permite organizar la información cuando se desea comparar totales relacionados, sobre todo si se tiene una lista de varios datos para sumar y se desea realizar comparaciones distintas con los datos obtenidos. En el informe de tabla dinámica que se muestra a continuación, se puede visualizar fácilmente cómo a partir de un conjunto de datos que incluyen el departamento, número de empleados, sueldo y bono, se genera una tabla dinámica donde se calculan los totales respectivos del número de empleados, sueldo y bono por departamento.

Ilustración 74.- Uso de tablas dinámicas

En los informes de tabla dinámica, cada columna o campo de los datos de origen se convierte en un campo de tabla dinámica. En el ejemplo anterior, la columna “DEPARTAMENTO” se convierte en el campo “DEPARTAMENTO” y es utilizado en la sección “Filas”. Cada registro de “DEPARTAMENTO” se resume en un solo elemento “DEPARTAMENTO”. En la sección “Valores” se añaden los campos “EMPLEADOS” y “SUELDO”. Los valores de las columnas “EMPLEADOS” y “SUELDO” se suman mostrándose el total por cada “Departamento”. En este caso no se ha utilizado el campo “BONO”


Ilustración 75.- Campos de la tabla dinámica

1.4.2 Sugerencias Para Crear Un Informe De Tabla Dinámica

Introducción

A continuación, se resumen los requisitos sugeridos para generar una tabla dinámica. Se sugiere que todas las columnas estén rotuladas. Pues los títulos se convertirán en los campos de la tabla dinámica. Si una columna no contiene nombre el momento de generar la tabla dinámica se generará el siguiente mensaje de error:


Ilustración 76.-Tabla dinámica: error por no definir un nombre de columna

No deben existir columnas vacías.

Se sugiere que el archivo esté en su formato plano más sencillo.

Los nombres de las columnas deben estar relacionados con la información que

contiene cada columna.

Características de un informe dinámico

Entre las principales características de un informe dinámico están:

• Permite la actualización automática de datos.

• Maneja filtros avanzados.

• Permite generar diversos tipos de informe para resumir los datos.

1.4.3 Crear Un Informe De Tabla Dinámica

Introducción

Un informe de tabla dinámica permite:

• Consultar grandes cantidades de datos.

• Calcular el subtotal, agregar datos numéricos y resumir datos.

• Expandir y contraer niveles de datos para destacar los resultados de interés.

• Desplazar filas a columnas y columnas a filas para obtener resúmenes

diferentes de los datos de origen.


• Filtrar, ordenar, agrupar y dar formato a los subconjuntos de datos para poder

obtener la información de interés.

Práctica

En una empresa han solicitado un informe de tabla dinámica filtrado por “fechas” de

todas las “oficinas”, que permita analizar el total de “saldos” por cada una de ellas.

Para iniciar la práctica descargar el ejercicio, mediante el siguiente enlace. AQUÍ.

(21.base_datos.xlsx)

La práctica descargada anteriormente, contiene la hoja “Datos”.


• Seleccionar una celda en el área de la base de datos de origen.

• Seleccionar la ficha “Insertar” y en el grupo “Tablas” seleccionar “Tabla

dinámica”.

• Se visualiza el cuadro de diálogo “Crear tabla dinámica”.

• En la sección “Tabla o rango” observar que se marcan los datos de la base de

origen. Si no están marcados, es necesario seleccionarlos.

• Seleccionar la opción “Nueva hoja de cálculo”.


Ilustración 77.- Creación de una tabla dinámica

El informe dinámico se crea en una nueva hoja, de acuerdo con lo seleccionado

anteriormente.

Observar que los rótulos de las columnas se han transformado en campos de la tabla

dinámica.

A medida que se seleccionan los campos estos se van ubicando en la tabla dinámica.


Ilustración 78.- Estructura de una tabla dinámica

También se pueden arrastrar los campos entre las áreas de la lista de campo de la tabla

dinámica.

Para resolver los solicitado se coloca el campo fecha dentro de la sección “Filtros”, el

campo “Oficina” dentro de la sección “Filas” y el campo “Saldo” en la sección “Valores”.

De esta manera se pueden visualizar la suma de los saldos de cada oficina al tiempo

que se puede seleccionar como filtro las fechas que se desea mostrar.

Ilustración 79.- Distribución de campos sobre la tabla dinámica


1.4.4 Cambiar El Diseño Del Informe Dinámico A Vista Clásica

Se puede rediseñar el informe, por ejemplo, para cambiar a la vista clásica. Para ello realizar lo siguiente:

• Hacer clic derecho sobre cualquier celda de la tabla dinámica.

• Seleccionar en el menú desplegable “Opciones de tabla dinámica”.

• En el cuadro de diálogo “Opciones de tabla dinámica”, activar con un visto la

opción “Diseño de tabla dinámica clásica” (permite arrastrar campos en la

cuadrícula).


Ilustración 80.- Diseño de vista clásica para la tabla dinámica

El diseño clásico de una tabla dinámica se visualiza como se muestra en la siguiente

ilustración:


Ilustración 81.- Diseño clásico de una tabla dinámica

1.4.5 Herramientas De Tabla Dinámica Al seleccionar una tabla dinámica se activan las herramientas de tabla dinámica.

Estas herramientas se encuentran en las carpetas:

• Análisis de tabla dinámica.

• Diseño.

En la ficha de “Análisis de tabla dinámica”, están los grupos:

• Tabla dinámica

• Campo activo

• Grupo

• Filtrar

• Datos

• Acciones

• Cálculos

• Herramientas

• Mostrar

Ilustración 82.- Grupos de la carpeta "Análisis de tabla dinámica"

• La sección Tabla dinámica, permite manejar el diseño, formatos, totales,

filtros, formas de visualizar la tabla dinámica e impresiones.


• La sección Campo activo, permite expandir, contraer y configurar un campo de

la tabla dinámica.

• La sección Grupo, permite agrupar o desagrupar los campos de una tabla

dinámica.

• La sección Filtrar, tiene las herramientas para filtrar la información por medio

de segmentación de datos y escala de tiempo.

• La sección Datos, tiene las opciones para cambiar y actualizar el origen de una

tabla dinámica.

• La sección Acciones, tiene las opciones para borrar, seleccionar o mover un

reporte dinámico.

• La sección Cálculos, tiene las herramientas para crear fórmulas y administrar

herramientas OLAP.

• La sección Herramientas, tiene las herramientas para generar gráficos y tablas

dinámicos recomendadas.

• La sección Mostrar, permite listar los campos, visualizar los botones y

encabezados de los campos de la tabla dinámica.

En la ficha de “Diseño” están los grupos:

• Diseño

• Opciones de estilo de tabla dinámica

• Estilos de tabla dinámica

Ilustración 83.- Grupos de la carpeta "Diseño"

• La sección Diseño, permite aplicar diseños de informe, totales y subtotales.

• La sección Opciones de estilos de tabla dinámica, permite activar o desactivar

encabezados de filas y columnas de un informe dinámico.

• La sección Estilos de tabla dinámica permite aplicar estilos rápidos a una tabla

dinámica.

1.4.6 Resumir Datos De Un Informe Dinámico Al trabajar con informes dinámicos, una de las opciones muy utilizadas es la de resumir

los datos.

. Práctica

.


En un concesionario han solicitado un informe dinámico que resuma el total de las

ventas de autos por modelos además se debe indicar la semana en la que fueron

vendidos, el valor y el número de autos.

Luego han solicitado un reporte con el promedio de ventas de cada tipo de vehículo.

Para iniciar la práctica descargar el ejercicio, mediante el siguiente enlace. AQUI.

(22.ventas_autos.xlsx)

En la práctica descargada anteriormente, contiene la hoja “ventas”.


1. Seleccionar una celda en el área de datos de la base de origen.

2. Seleccionar la ficha “Insertar”.

3. Seleccionar el grupo “Tablas”.

4. Seleccionar “Tabla dinámica”.

5. En el cuadro de diálogo “Crear tabla dinámica”, dentro de la sección “Tabla o

rango” se observa que se marcan los datos de la base de origen. Si no están

marcados, se deben seleccionar.

6. Seleccionar la opción “Nueva hoja de cálculo”.


Ilustración 84.- Creación de una tabla dinámica

https://aula2.virtualepn.edu.ec/pluginfile.php/116199/mod_book/chapter/17581/Copia%20de%202ventas_autos-1.xlsx

https://aula2.virtualepn.edu.ec/pluginfile.php/116199/mod_book/chapter/17581/Copia%20de%202ventas_autos-1.xlsx


• Arrastrar el campo “SEMANA” y “Tipo Vehículo” a “Filas”.

• Arrastra los campos “VALOR y CHASIS” a la sección “Σ Valores”.

Ilustración 85.- Asignación de campos en una tabla dinámica

Como se puede observar para conocer el número de autos vendidos se coloca el

campo “CHASIS”, este campo contiene información de tipo texto de tal forma que

automáticamente se aplica la función “Cuenta”.

Ahora para calcular el promedio de ventas de cada tipo de vehículo se realiza lo

siguiente:

• Hacer clic derecho en el campo a resumir, en este caso “VALOR”.

• En el menú desplegable seleccionar la opción “Resumir por”.

• Seleccionar la opción “Promedio.”


Ilustración 86.- Cambiando la operación del campo "Valor"

En “Σ Valores” se visualiza el campo “Valor” resumido como promedio.

Ilustración 87.- Resultado del promedio del valor

Ahora como ejercicio se puede resumir las ventas por el valor mínimo.

1.4.7 Opciones De Diseño De Un Informe Dinámico

Las opciones de diseño de la herramienta de tablas dinámicas permiten mejorar la

presentación de un informe dinámico.

Como un ejemplo realizar lo siguiente:

• Seleccionar el informe dinámico al que deseas aplicar un nuevo estilo.

• Seleccionar la ficha Diseño.

• Elegir el estilo que se desea aplicar.


Ilustración 88.- Selección de un diseño para la tabla dinámica

También se puede aplicar la opción “Diseño de informe”, para esto realizar lo siguiente:

• Seleccionar la ficha “Diseño”.

• Seleccionar el grupo “Diseño”.

• Seleccionar la opción “Diseño de informe”.

• Seleccionar “Mostrar en forma de esquema”.

Ilustración 89.- Aplicación de un formato de esquema

Visualizar el resultado obtenido luego de aplicar el “Diseño de informe”.


Ilustración 90.- Resultado de aplicar un formato esquema

En el diseño de tablas dinámicas se puede visualizar los subtotales de los campos del informe

dinámico, para esto realizar lo siguiente:

• Seleccionar la ficha “Diseño”.

• Seleccionar el grupo “Diseño”.

• Seleccionar “Subtotales”.

• Seleccionar “Mostrar todos los subtotales en la parte inferior del grupo”.

Ilustración 91.- Opción "Mostrar todos los subtotales en la parte inferior del grupo"

Luego de aplicar los subtotales, estos se pueden visualizar debajo de cada grupo de datos.


Ilustración 92.- Aplicación de subtotales en tablas dinámicas

1.4.8 Actualizar Un Informe De Tabla Dinámica La característica más importante de una tabla dinámica es que sus datos pueden actualizarse en

forma automática.

Práctica

A la empleada Belén Salvador le han subido el sueldo básico de 400 a 1000 dólares en

los meses de enero, febrero y marzo. Se necesita que el informe dinámico se actualice

automáticamente con los nuevos datos registrados en la base.

Para iniciar la práctica descargar el ejercicio, mediante el siguiente enlace. AQUI.

(23.actualizar_datos.xlsx)

La práctica descargada anteriormente, contiene las hojas: “Datos” y “Tabla dinámica”.


• Seleccionar el reporte dinámico a actualizar.

• Prestar atención cual es el valor del dato a actualizar.

https://aula2.virtualepn.edu.ec/pluginfile.php/116199/mod_book/chapter/17584/Copia%20de%204actualizar_datos.xlsx

https://aula2.virtualepn.edu.ec/pluginfile.php/116199/mod_book/chapter/17584/Copia%20de%204actualizar_datos.xlsx


Ilustración 93.-Valores a modificar en la tabla dinámica

Ingresar a la base de datos y observar el dato a actualizar para la empleada Belén

Salvador en los meses enero, febrero y marzo. Observar que su sueldo básico es de

400 dólares.

Ilustración 94.- Dato a actualizar

En la base de datos digitar el nuevo sueldo básico, en este caso ingresar 1000 en cada

mes. Observar que automáticamente los ingresos suben a 1132 dólares.

Ilustración 95.- Modificación del sueldo

Si se observa el reporte dinámico, se notará que aún no se ha actualizado el dato. Para

realizar la actualización automática realizar lo siguiente:

1. Seleccionar una celda de la tabla dinámica.

2. Seleccionar la pestaña “Análisis de tabla dinámica”

3. Seleccionar la opción “Actualizar” dentro del grupo “Actualizar”.

4. Observar el nuevo valor en la tabla dinámica


Ilustración 96.- Opción "Actualizar"

1.4.9 Cambiar El Origen De Datos Un Informe De Tabla Dinámica La característica más importante de una tabla dinámica es que puede actualizar los datos de

forma automática. Pero ¿Qué sucede cuando en la base de datos se han ingresado nuevos

registros?

A pesar de que se actualice el informe dinámico no se visualizan los nuevos registros

ingresados en la base.

¿Cómo se soluciona este caso?

Práctica

En la base de roles se han ingresado diez registros. Solicitan realizar la actualización del

informe dinámico.

Para iniciar la práctica descargar el ejercicio, mediante el siguiente enlace AQUI.

(24.origen_datos.xlsx)

La práctica descargada anteriormente, contiene las hojas: “Tabla dinámica” y “Datos”.


• Visualizar en la base los nuevos registros ingresados.


Ilustración 97.- Nuevos registros ingresados en la base de datos

• Ahora, tratar de actualizar el informe dinámico.

• Para ello seleccionar una celda de la tabla dinámica, hacer clic derecho y

seleccionar la opción “Actualizar”.

Ilustración 98.- Actualización de la tabla dinámica

Como se puede observar no se han actualizado los nuevos registros ingresados.

Para resolver este problema realizar las siguientes acciones:

• Seleccionar una celda del reporte dinámico a actualizar.

• Seleccionar la pestaña “Análisis de tabla dinámica”.

• Seleccionar la opción “Cambiar origen de datos” que se encuentra en el grupo

“Datos”.


Ilustración 99.- Opción "Cambiar origen de datos"

En la caja de diálogo “Cambiar origen de datos de tabla dinámina” seleccionar el

nuevo rango de datos y presionar sobre el botón “Aceptar”.

Ilustración 100.- Cambio de rango de origen de datos

Una vez modificado el rango de origen de datos se puede apreciar los nuevos valores

en la tabla dinámica.


Ilustración 101.- Tabla dinámica actualizada

1.4.10 Agrupar Campos En Un Informe De Tabla Dinámica . Es muy posible que se requiera contar con informes que resuman la información por años,

trimestres, meses, fechas.

Excel, permite con sólo conocer una fecha, agrupar los campos indicados anteriormente.

Práctica

En la base de ventas de autos. Solicitan realizar un informe dinámico que permita

resumir la información por trimestres y meses.

Para iniciar la práctica descargar el ejercicio, mediante el siguiente enlace. AQUÍ.

(25.ventas_agrupar.xlsx)

La práctica descargada anteriormente, contiene las hojas: “Tabla dinámica” y “Ventas”.


1. Seleccionar una celda relacionada con fechas dentro de la tabla dinámica.

2. Seleccionar la pestaña “Análisis de tabla dinámica”.

3. Dentro del grupo “Grupo”, seleccionar la opción “Crear grupo de selección”

4. En la caja de diálogo “Agrupar”, seleccionar los campos para la agrupación

(Meses y trimestres).

5. Presionar sobre el botón “Aceptar”


Ilustración 102.-Agrupación de filas por fechas

Observar el resultado de la agrupación por fechas

Ilustración 103.- Agrupación por Trimestre y meses

Para desagrupar el campo fecha realizar lo siguiente:

Seleccionar el campo “trim1”, hacer clic derecho dicha celda y en el menú desplegable

seleccionar la opción “Desagrupar”.


Ilustración 1043.- Opción "Desagrupar"

1.4.11 Configuración De Campo De Valor

Es muy posible que se requiera contar con informes que resuman la información con respecto

al porcentaje total de una columna.

.

Práctica

. Calcular el porcentaje de total de producción en cada país para el año 2008

Para iniciar la práctica descargar el ejercicio, mediante el siguiente enlace AQUÍ.

(26.porcentaje_columna.xlsx)


1. Seleccionar el campo “Suma de 2008” de la tabla dinámica que se encuentra

en la hoja “Tabla dinámica”

2. Hacer clic derecho y selecciona “Configuración de campo de valor”.

3. En la caja de diálogo “Configuración de campo valor” seleccionar la carpeta

“Mostrar valores como”.

4. Seleccionar la opción “% del total general” desde la lista desplegable.


https://aula2.virtualepn.edu.ec/pluginfile.php/116199/mod_book/chapter/17588/Copia%20de%209porcentajecolumna.xlsx


Ilustración 1054.- Configuración de "% de total general"

El resultado de la operación es una columna con los porcentajes de cada uno de los

datos de la columna respecto al total.

Ilustración 1065.- Resultado de aplicar la opción "% del total general"

1.4.12 Fórmulas En Un Informe De Tabla Dinámica

Los informes dinámicos permiten generar fórmulas, y reutilizar los campos calculados.

Práctica


En el informe dinámico de roles han solicitado que se calcule el 10% de los ingresos. Y

el total de los ingresos, que es igual a los ingresos más el 10% de los ingresos

calculados anteriormente.

Para iniciar la práctica descargar el ejercicio, mediante el siguiente enlace AQUI. (

27.formulas_tdinamicas.xlsx)

La práctica descargada anteriormente, contiene las hojas “Tabla dinámica” y “Datos”.




3. Seleccionar el grupo “Cálculos”.

4. Seleccionar la opción “Campos, elementos y conjuntos”.

5. Seleccionar la opción “Campo calculado...”.

6. En el cuadro de diálogo “Insertar campo calculado”, añadir un nombre al

nuevo campo, por ejemplo “10% ingresos”.

7. En la lista “Campos” seleccionar el campo a insertar, por ejemplo “Ingresos”.

8. Presionar sobre el botón “Insertar campo”.

9. Completar la operación de cálculo dentro de la sección “Fórmula”.

10. Presionar sobre el botón “Sumar” para añadir el nuevo campo.

11. Presionar sobre el botón “Aceptar” para finalizar.

Ilustración 107.- Añadiendo un campo calculado

https://aula2.virtualepn.edu.ec/pluginfile.php/116199/mod_book/chapter/17587/Copia%20de%206formulas_tdinamicas.xlsx


Puede observarse que en la tabla dinámica de ha añadido el nuevo campo.

Ilustración 108.- Tabla dinámica con un campo calculado

Ahora para calcular el total de los ingresos, que es igual a los ingresos más el valor de

campo añadido anteriormente (10% ingresos) se debe realizar lo siguiente:



3. Seleccionar el grupo “Cálculos”.

4. Seleccionar la opción “Campos, elementos y conjuntos”.

5. Seleccionar la opción “Campo calculado...”.

6. En el cuadro de diálogo “Insertar campo calculado”, añadir un nombre al

nuevo campo, por ejemplo “Total ingresos”.

7. En la lista “Campos” seleccionar el campo a insertar, por ejemplo “Ingresos”.

8. Presionar sobre el botón “Insertar campo”.

9. Completar la operación de cálculo dentro de la sección “Fórmula” añadiendo

el signo + y a continuación el campo calculado “10% Ingresos”

10. Presionar sobre el botón “Sumar” para añadir el nuevo campo.

11. Presionar sobre el botón “Aceptar” para finalizar.


Ilustración 109.-Definición del campo calculado "Ingreso total"

Ahora se puede observar el resultado de añadir el campo calculado “Total ingresos”.

Ilustración 110.- Inclusión del campo calculado "Total ingresos"

1.5 Gráficos dinámicos

1.5.1 Gráficos Dinámicos

Introducción

Microsoft Office Excel es una herramienta eficaz que permite analizar datos en el área

ejecutiva y empresarial.


Los gráficos dinámicos brindan la posibilidad de resumir, analizar, explorar y presentar

datos de forma visual.

Un informe de gráfico dinámico representa gráficamente los datos de un informe de

tabla dinámica, que en este caso se denomina el informe de tabla dinámica asociado.

Objetivos

• Aplicar las herramientas de gráficos dinámicos de Ms Excel.

• Aplicar diseños en gráficos dinámicos.

• Agregar líneas de tendencia.

Contenidos

• Introducción.

• Crear un gráfico dinámico.

• Opciones de diseño.

• Agregar líneas de tendencia.

1.5.2 Crear Un Gráfico Dinámico

Un gráfico dinámico permite resumir de forma visual los datos de un informe

dinámico.

.

Práctica

.En una empresa han solicitado con urgencia un informe gráfico de los ingresos

mensuales de cada uno de los departamentos.


(28.graficos_dinamicos.xlsx)

La práctica descargada anteriormente, contiene las hojas “Tabla dinámica” y “Datos”.


1. Seleccionar cualquier celda de la tabla dinámica.

2. Seleccionar la pestaña “Insertar”.

3. Seleccionar la opción “Gráfico dinámico” del grupo “Gráficos”.

4. En la caja de diálogo “Insertar gráfico” seleccionar un tipo de gráfico. Por

ejemplo “Columnas”



Ilustración 111.- Inserción de gráfico dinámico

Inmediatamente se puede observar el gráfico generado a partir de los datos de la tabla

dinámica.

Ilustración 112.- Gráfico dinámico

Para ubicar el gráfico en otra hoja realizar lo siguiente:

1. Seleccionar el gráfico.


2. Seleccionar la pestaña “Diseño”

3. Seleccionar la opción “Mover gráfico”.

4. En la caja de diálogo “Mover gráfico”, seleccionar la opción “Hoja

nueva”.


Ilustración 113.- Opción "Mover gráfico"

Observar cómo se crea una nueva hoja para ubicar el gráfico.

Ilustración 114.- Gráfico dinámico sobre la hoja "Gráfico"

1.5.3 Opciones De Diseño De Gráfico Dinámico Excel incorpora herramientas que permiten mejorar la apariencia de un gráfico dinámico.

Para lograr aplicar un nuevo estilo al gráfico dinámico realizar lo siguiente:

1. Seleccionar el gráfico.


2. Seleccionar la pestaña “Diseño”.

3. Seleccionar un estilo.

Ilustración 115.- Cambio de estilo de gráfico dinámico

Los estilos pueden complementarse seleccionando gamas de colores:

Ilustración 116.-Selección de colores para gráfico dinámico

1.5.4 Agregar Líneas De Tendencia En Un Gráfico Dinámico Introducción

. Las líneas de tendencia son representaciones gráficas de las tendencias de los datos que se pueden usar para analizar problemas de predicción.


Dicho análisis también se denomina análisis de regresión. Mediante el análisis de regresión, puede ampliar el significado de una línea de tendencia de un gráfico más allá de los datos reales para predecir valores futuros. Las líneas de tendencia permiten mostrar hacia donde tienden los datos. Por ejemplo, se podría calcular la tendencia de los ingresos de una empresa. Una línea de tendencia es más fiable cuando su valor R al cuadrado es 1 o está cerca de 1. Cuando se ajusta una línea de tendencia a los datos, Excel calcula automáticamente su valor R al cuadrado basado en una fórmula. Si se desea, se puede mostrar ese valor en el gráfico. Una línea de tendencia se puede aplicar en determinados gráficos. Cuando esta opción está desactiva en el gráfico, quiere decir que no es posible aplicar esta característica en el tipo de gráfico actual.

. Práctica .

En la empresa te han solicitado con urgencia un informe gráfico de los ingresos

mensuales de cada uno de los departamentos. Además, quieren conocer cuál es la

tendencia de los ingresos.

Para resolver este problema realiza lo siguiente:

1. Seleccionar la serie de datos a la cual aplicarás la línea de tendencia,

para el ejemplo a la serie de “Ingresos” y hacer un clic derecho sobre

la serie.

2. Seleccionar la opción “Agregar línea de tendencia...” sobre el menú

desplegable.

3. Seleccionar el tipo de línea de tendencia dentro del recuadro

“Opciones de línea de tendencia”.


Ilustración 117.- Opción "Agregar línea de tendencia"

Se pueden modificar varias características de la línea de tendencia, por ejemplo para

modificar el color de la línea proceder de la siguiente manera:

1. Seleccionar la línea de tendencia.

2. En el recuadro “Opciones de línea de tendencia”, activar la

casilla “Formato de línea de tendencia” que se representa con un

balde de pintura.

3. Seleccionar en el menú de colores el color requerido.

Ilustración 118.- Cambio de color de la línea de tendencia

Otras características visuales pueden ser modificadas desde esta sección.


1.6 Creación de macros

1.6.1 Crear Macros En Ms Excel

Introducción

Microsoft Office Excel, permite trabajar con macros. Lo interesante de usar macros es que permiten automatizar tareas. Añadiendo un botón en un libro se puede lograr que al ser pulsarlo realice una serie tareas muchas de ellas repetitivas. Es posible que, en el área de trabajo de una empresa, se realicen tareas repetitivas y cálculos complicados con las hojas Excel, desperdiciando mucho tiempo en ejecutarlos, además de resúmenes de datos e informes hechos de manera manual. Al aplicar las macros, se puede mediante la pulsación sobre un botón lograr que en cuestión de segundos todas esas tareas se completen En esta unidad de estudio de macros se aprenderá a automatizar y realizar tareas complejas, aumentando la eficiencia y eficacia del trabajo.

1.6.2 Preparar El Libro De Excel Para Trabajar Con Macros

Introducción

Una macro es una serie de instrucciones que se almacenan para que se puedan

ejecutar de forma secuencial mediante una sola llamada u orden de ejecución.

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.

Términos que se utilizan cuando se crean Macros:

VBA: Visual Basic para Aplicaciones, el lenguaje de código de macros.

Módulo: Contenedor para almacenar macros, asociado a un libro.

Práctica

Habilitar la pestaña “Programador”, guardar un libro habilitado para ejecutar macros y

configurar las seguridades de las macros.

Lo primero que se debe hacer antes de empezar a grabar macros, editarlas, mejorarlas,

programarlas y crear aplicaciones en Excel es habilitar la pestaña “Programador”.

Para habilitar la pestaña “Programador”, se debe hacer clic derecho sobre la cinta de

opciones y seleccionar la opción “Personalizar cinta de opciones” en el menú

desplegable.


En la caja de diálogo “Opciones de Excel” se debe habilitar la casilla “Programador”

Ilustración 119.- Habilitación de la carpeta "Programación"

La cinta de opciones “Programador” tienen los siguientes grupos:

• Código

• Complementos

• Controles

• XML

Ilustración 120.- Cinta "Programador"

Una vez habilitada la carpeta “Programador”, los documentos que contengan macros

y aplicaciones deben ser guardados como “Libro de Excel habilitado para macros”.


Para ello se debe seleccionar la carpeta “Archivo” y las opciones “Guardar como” y

“Examinar”. En la caja de diálogo “Guardar como” se debe seleccionar en la sección

“Tipo”, la opción “Libro de Excel habilitado para macros”.

Ilustración 121.- Guardado como "Libro de Excel habilitado para macros"

Cuando se ejecuta un libro de Excel que contienen macros, se debe ser cuidadoso ya que

este libro podría contener programas maliciosos. Por eso si no se conoce la procedencia

del archivo es preferible tomar ciertas medidas de seguridad. Para administrar las

opciones de seguridad relacionadas con la ejecución de las macros en el momento de la

apertura del libro se debe realizar lo siguiente:

1. Seleccionar ficha “Programador”

2. Seleccionar la opción “Seguridad de macros” que se encuentra en el grupo

“Código”.

3. Seleccionar la opción “Configuración de macros” dentro de la caja de

diálogo “Centro de confianza”.

4. Dentro de la sección “Configuración de macros” se puede elegir la forma

como serán tratadas las macros en el momento de abrir un libro de Excel.

Por ejemplo, se puede configurar que dichas macros sean deshabilitadas

previa notificación.

5. Finalmente se debe presionar sobre el botón “Aceptar”


Ilustración 122.- Configuración de la seguridad de las macros

1.6.3 Grabar Macros

Introducción

La forma más fácil e intuitiva de crear macros es crearlas mediante el grabador de

macros del que dispone Excel.

Ilustración 123.-Opción “Grabar macro”

El grabador de macros permite grabar las acciones deseadas que posteriormente las

traduce a instrucciones en VBA, las cuales se pueden modificar posteriormente si se

tiene conocimientos de programación.


Cuando se graba una macro, la grabadora de macros graba todas las acciones

realizadas sobre la hoja de cálculo y sobre el libro en general.

Práctica

Crear una macro que permita dar a cualquier celda el formato de letra “Comic Sans

MS”, de tamaño 18, color negro.

Para realizar este ejercicio se deben seguir los siguientes pasos:

1. Seleccionar la celda “C4” y digitar “Hola amiguit@s”.

2. Seleccionar la carpeta “Programador”

3. Seleccionar la opción “Grabar macro” que se encuentra en el grupo

“Código”.

Ilustración 124.-Opción “Grabar macro” de la pestaña “Programador”

Otra opción para grabar macros es hacer presionar sobre el icono que se encuentra en

la parte inferior izquierda de la barra de estado.

Ilustración 125.- Botón “Grabar macro” de la “barra de estado”

Cuando se presiona sobre el botón “Grabar macro” se despliega la caja de diálogo

“Grabar macro” en donde se debe realizar las siguientes acciones:


1. En la sección “Nombre de la macro”, digitar un nombre para la macro.

El nombre de la macro no debe iniciar con números, no debe contener espacios en

blanco, ni caracteres especiales (@, &), ni palabras reservadas del lenguaje de

programación (else, if, end).

2. En la sección “Método abreviado” ingresar una combinación de teclas para la

ejecución de la macro. Por ejemplo: “Ctrl+Shift+F”

3. En la sección “Guardar macro en” seleccionar “En este libro” para que la macro se

guarde en el archivo actual.

4. En la sección “Descripción” digitar un texto que describa lo que realiza la macro;

este texto es opcional.


Ilustración 126.- Caja de diálogo “Grabar macro”

Al iniciar la grabación el ícono de la parte inferior izquierda de la barra de estado

cambia de forma indicando que la grabación se encuentra en ejecución y que se puede

utilizar el nuevo ícono ara detenerla.


Ilustración 127.- Cambio de botón en la barra de estado durante grabación de macro.

Durante la grabación ejecutar las siguientes acciones para cumplir con el ejercicio

propuesto:

• Seleccionar la celda que contiene el texto de prueba.

• Seleccionar la pestaña “Inicio”

• Seleccionar el tipo y tamaño de letra

• Seleccionar el color marrón

Ilustración 128.- Grabación de una macro para cambiar formato a contenido de celda

Una vez ejecutadas las acciones de grabación seleccionar cualquiera de las opciones

disponibles para detener la macro. Esto es, usando la opción “Detener macro” de la

pestaña “Programador” o presionando sobre el botón para detener la grabación que

se encuentra en la barra de estado.


Ilustración 129.- Dos formas para detener una macro

Normalmente es recomendable usar el botón que se encuentra en la parte izquierda

sobre la barra de estado, en la parte inferior de la hoja.

Ilustración 130.- Botón para detener una macro

Si se deja el texto de la celda “C4” en su formato original y luego se ejecuta la macro se

podrá observar como la macro cambia el formato del texto. En el siguiente tema se

explicará como ejecutar la macro.


Ilustración 131.- Resultado de la aplicación de una macro.

1.6.4 Ejecutar Macros

Introducción

Una vez detenida la grabación de la macro, se puede probar su funcionamiento.

Práctica

Para probar la macro creada en la sección anterior realizar las siguientes acciones:

• Digitar un texto en una celda vacía

• Seleccionar la celda donde se digitó el texto

Ilustración 132.- Texto a formatear mediante la ejecución de la macro

Presionar simultáneamente las teclas asignadas en el método abreviado, para este

ejemplo “Ctrl+Shift+F”


Ilustración 133.- Texto formateado después de la ejecución de la macro

Otra forma de ejecutar la macro es mediante la opción “Macros” que se encuentra

dentro de la pestaña “Programador”. Y dentro del cuadro de diálogo “Macro”

selecciona el nombre de l macro y presionar sobre el botón “Ejecutar”.

• Selecciona la ficha Desarrollador.

• En el grupo Código, selecciona Macros.

Ilustración 134.- Ejecución de la macro mediante la opción "Macros"


Se puede descargar la solución al ejercicio, mediante el siente enlace AQUI.

(29.macros.xlsm)

1.6.5 Acceder Al Ambiente De Visual Basic

Introducción

Cuando se desea modificar o revisar el código generado en una macro, es necesario

ingresar al ambiente de “Visual basic”.

Práctica

Ingresar al ambiente “Visual basic” y revisar el código de la macro “formato”.

Realizar las siguientes acciones:

• Seleccionar la ficha “Programador”.

• Dentro del grupo “Código” seleccionar la opción “Macros”

• En el cuadro de diálogo “Macro”, seleccionar el nombre de la macro a modificar.

• Presionar sobre el botón “Modificar”.

Ilustración 135.- Opción "Modificar macro"

El código generado por la macro tiene una serie de instrucciones en lenguaje “Visual

basic”. Muchas de las líneas de código son fáciles de entender. Todas las macros

inician con la palabra “Sub” seguido del nombre de la macro.


Ilustración 136.- Código generado por la grabadora de macros

Para regresar al ambiente de MS Excel se debe presionar sobre el botón “Excel” que se

encuentra en la parte superior izquierda de la ventana. También se puede usar la

combinación de teclas “Alt+F11”.


Ilustración 137.- Botón para retornar a la hija Excel

Otra forma de acceder al ambiente de Visual Basic es:

• Seleccionar el nombre de la carpeta de cualquier hoja del libro de Excel.

• Hacer clic derecho.

• Seleccionar la opción “Ver código”.

Ilustración 138.- Opción "Ver código"


Una tercera forma de acceder al ambiente “Visual Basic” es:

• Seleccionar la carpeta “Programador”

• Seleccionar la opción “Visual Basic” que se encuentra en el grupo

“Código”.

Ilustración 139.- Opción "Visual basic"

Una vez dentro del ambiente “Visual basic” una forma segura de encontrar el código

de la macro grabada es seleccionar el ícono de “Explorador de proyectos” y hacer

doble clic sobre el “Módulo1”.

Ilustración 140.- Selección del "Modulo1"

1.6.6 Modificar Macros

Introducción Si una macro es grabada usando el mínimo de acciones necesarias, el código generado será

mucho más sencillo de entender que si se graban muchas acciones erráticas.

Práctica


Modificar la macro “Formato” de tal manera que use otro tipo y tamaño de letra sin volver

a grabar la macro.

Para realizar esta tarea ingresar a la opción “Macros” de la pestaña “Programador” y

dentro de la caja de diálogo “Macro” seleccionar el nombre de la macro “formato” y

presionar sobre el botón “Modificar”.

Ilustración 141.- Modificación de la macro "formato"

El código de la macro “Formato” contienen las siguientes instrucciones:


Ilustración 142.- Código de la macro "formato"

Todas las macros que se generan inician con la instrucción “Sub” seguido del nombre de

la macro y unos paréntesis, mientras que en la parte final del código aparece la

instrucción “End Sub”

El texto en color verde, son comentarios que se puede colocar para clarificar el

significado del código. Estos comentarios no son instrucciones por tanto no se ejecutan.

Los comentarios normalmente están precedidos el apostrofe.

Como puede observarse cada atributo del formato tienen asignado un valor. No es difícil

imaginar que el atributo “.Name” se refiere al nombre de la fuente, que el atributo

“.Size” se refiere al tamaño de la letra. Por tanto, para cambiar el tipo y tamaño de letra

basta con cambiar los valores asignados a estos atributos. En este ejercicio se cambiará

el tipo de letra “Arial” y el tamaño de letra a 24


Ilustración 143.- Cambio en el código de la macro

Al ejecutar el nuevo código de la macro sobre uno de los textos modificados

anteriormente el resultado es visible.

Ilustración 144.- Aplicación del nuevo código de la macro "formato"

1.6.7 Códigos Más Comunes En Macros

A continuación, se muestra una lista de muchos códigos comúnmente usados para programar

macros.


Trasladarse a una Celda

Range("A1").Select

Escribir en una Celda

Activecell.FormulaR1C1="Paty Acosta"

Letra Negrita

Selection.Font.Bold = True

Letra Cursiva

Selection.Font.Italic = True

Letra Subrayada

Selection.Font.Underline = xlUnderlineStyleSingle

Centrar Texto

Selection.HorizontalAlignment = xlCenter

Alinear a la izquierda

Selection.HorizontalAlignment = xlLeft

Alinear a la Derecha

Selection.HorizontalAlignment = xlRight

Tipo de Letra(Fuente)

Selection .Font.Name = "AGaramond"

Tamaño de Letra(Tamaño de Fuente)

Selection.Font.Size = 15

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


Buscar

Cells.Find(What:="Paty Acosta", 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:="", ReadOnlyRecommended:= _

False, CreateBackup:=False

1.6.8 Asignar A Una Forma

Introducción

Una macro puede ser asignada a una forma gráfica de tal manera que la hacer clic

sobre dicha forma se ejecute la macro.

Práctica

Asignar la macro creada con anterioridad, a una forma gráfica.


1. Selecciona la carpeta “Insertar”.

2. Seleccionar el grupo “Ilustraciones”.

3. Seleccionar la opción “Formas”

4. Seleccionar y dibujar sobre la hoja una forma.


Ilustración 145.- Creación de una forma

Haciendo clic derecho sobre la forma creada, seleccionar la opción “Asignar macro” del

menú desplegable.


Ilustración 146.- Opción "Asignar macro"

En el cuadro de diálogo “Asignar macro”, seleccionar el nombre de la macro a asignar

y presionar sobre el botón “Aceptar”.


Ilustración 147.- Asignación de una macro a la forma creada

Observar que al presionar sobre la forma creada la macro se ejecuta sobre el texto

seleccionado.

Ilustración 148.- Ejecución de la macro por medio de la forma


1.6.9 Asignar Una Macro A Un Botón De Formulario .

Introducción

Una macro puede ser asignada a un botón de formulario.

. Práctica

. Realizar una macro que calcule el valor de la “cantidad” por el “valor” del producto.

Luego permitir que se copie la fórmula y asignar la macro a un botón de formulario.


(30.boton_formulario.xlsm)


• Seleccionar la ficha “Programador”.

• Seleccionar la opción “Grabar macro”.

• En el cuadro de diálogo “Grabar macro”, dentro de la sección “Nombre”

digitar “Calcular_valor”.

• En la sección “Guardar macro en” seleccionar “Este libro”.

• En la sección “Descripción” ingresar un comentario opcional.


Ilustración 149.- Grabación de macro

1. Digitar en la celda “F14” la siguiente fórmula (si la operación E14*D14 presente error, se pondrá el valor de cero): =SI.ERROR(E14*D14;0)

https://aula2.virtualepn.edu.ec/pluginfile.php/116215/mod_book/chapter/17604/Copia%20de%20facturacion_paraejercicio.xlsm


2. Arrastrar dicha fórmula hacia abajo hasta la celda “F23”.

3. Detener la grabación presione sobre el botón de la barra del estado.

Ilustración 150.-Acciones que realiza la macro

Asignar la macro a un botón de “Controles de formulario”

1. Seleccionar la carpeta “Programador”.

2. Seleccionar la opción “Insertar”.

3. En el grupo de “Controles de formulario”; seleccionar el control “Botón”.


Ilustración 151.- Selección de control "Botón"

1. Dibujar el botón sobre un área de la hoja de cálculo. 2. En la caja de diálogo “Asignar macro” seleccionar asociado al botón:

“calcular_valor”. 3. Presionar sobre el botón “Aceptar”.

Ilustración 152.- Asignación de la macro a un control tipo botón

1. Cambiar el nombre que aparece sobre el botón.

2. Borrar el contenido de la columna Total, es decir el rango “F14:F23”.


Ilustración 153.- Preparación del botón para la prueba

1.6.10 Caso de estudio Introducción

El siguiente caso de estudio requiere del siguiente libro de Excel, el cual puede ser

descargado desde el siguiente enlace. AQUÍ. (31.facturacion.xlsm)

Esta planificado que la aplicación que se va a desarrollar permita:

• La actualización de los productos al conocer el código.

• Facturar a los clientes registrados.

• Guardar el monto facturado en la base de resumen

facturación

• Limpiar la factura.

Se cuenta con las hojas:

• Factura

• Productos

• Clientes

• Resumen factura

Ilustración 154.- Carpetas del proyecto "Caso de estudio"

Para resolver este caso de estudio se requiere aplicar validación de listas con

definición de nombres en la celda “B14”.

1. Seleccionar la celda “B14”.

2. Seleccionar la pestaña “Datos”.


3. Seleccionar la opción “Validación de datos”.

4. En el cuadro de diálogo “Validación de datos” seleccionar la carpeta


5. Dentro de la sección “Permitir” seleccionar “Lista”.

6. En la sección “Origen” digitar “=Item”.

7. También se puede seleccionar la columna “Item” de la primera columna

de la hoja “Producto”. A los datos de dicha columna se le ha asignado el

nombre “Item”.


9. Arrastrar la validación en el rango “B14:B23”.

Ilustración 155.- Validación por "Lista" de la celda "B14"

Ahora es necesario aplicar algunas fórmulas en las distintas celdas del formulario.

En la celda “C14” aplicar la fórmula:

=SI.ERROR(BUSCARV(B14;Productos!$A$2:$C$73;2;0);" ")

Esta fórmula busca la descripción del producto en el rango “Productos!$A$2:$C$73”

dado el número de “Item” almacenado en la celda “B14”. Por eso se selecciona la

segunda columna de dicho rango.

En la celda “E14” aplicar la fórmula:

=SI.ERROR(BUSCARV(B14;Productos!$A$2:$C$73;3;0);" ")

Esta fórmula es similar a la anterior, pero retorna el precio del producto. Por eso

selecciona la tercera columna del rango.

Para calcular el precio total se utiliza sobre la celda “F14” la siguiente fórmula:

=SI.ERROR(D14*E14;0)

Esta fórmula multiplica el precio por la cantidad de producto.


Ilustración 156.- Definición de fórmulas

Arrastrar las fórmulas hasta la fila 23 de modo que se apliquen al espacio destinado

para el detalle.

Ilustración 157.- Aplicación de fórmulas al espacio del detalle de la factura

Cada vez que se selecciona un ítem y se añade una cantidad, automáticamente se

insertan la descripción, el valor unitario del item y se calcula el valor total.

Ilustración 158.- Llenado automático del detalle

Adicionalmente, en la parte inferior de la factura se deben llenar las fórmulas

correspondientes al “Subtotal”, “Descuento”, “Impuesto” y “Total”.

La fórmula del “Subtotal” es muy simple y corresponde a la suma de los valores de la

columna “Valor Total”, esta fórmula se ubica en la celda “F24”.

=SUMA(F14:F23)

La fórmula de descuento que se ubica en la celda “F25” es la siguiente:

=SI(F9="CONTADO";SI(D25="MAYORISTA";F24*20%;F24*10%);SI(D25="MAYORISTA";F24*5%;F24*0%))


Esta fórmula usa los contenidos de las celdas “F9” y “D25” para establecer los valores

de descuento. La celda “F9” contiene una lista de dos valores (configurada por validación

de lista): “CONTADO” y “CRÉDITO”. La celda “D25” contiene una lista de dos valores

(configurada por validación de lista): “MAYORISTA” y “MINORISTA”. La fórmula evalúa

si la factura es de “CONTADO” y es para un “MAYORISTA” entonces el descuento es de

20%, y si es para un “MINORISTA” el descuento es de 10%. En el caso de que no sea de

“CONTADO” (es decir, es a “CRÉDITO”) y es para un “MAYORISTA” entonces el

descuento es de 5%, y si es para un “MINORISTA” es de 0% (es decir no hay descuento).

La fórmula del “lmpuesto”, es el “Subtotal” multiplicado por el 12%:

=F24*12%

Finalmente, la fórmula del total es la suma del “Subtotal” y del “Impuesto” y la resta del

“Descuento”.

=F24-F25+F26

Ilustración 159.- Fórmula a aplicarse sobre el formulario

De igual manera, cada vez que se seleccione el número de “RUC” de un cliente deberán

llenarse los campos de “NOMBRE”, “DIRECCIÓN” y “TELÉFONO” del cliente. Para ellos

se usa la función “BUSCARV” con una fórmula similar a la siguiente:

=SI.ERROR(BUSCARV(C9;Clientes!$A$1:$D$160;2;FALSO);"")

Donde el número de columna para el “NOMBRE” es 2, para la “DIRECCIÓN” es 3 y para

el “TELÉFONO” es 4.


Ilustración 160.- Fórmula para encontrar el "NOMBRE" del cliente

Ahora, se creará una macro para “LIMPIAR” el formulario. Dado que todas las fórmulas

dependen de los valores de algunas listas, para limpiar el formulario basta con limpiar

los valores de dichas listas.

Las listas de las cuales depende todo el formulario son:

• RUC: Celda “C9”

• Forma de pago: Celda “F9”

• Distribuidor: Celda “D25”

• Item: Rango “B14:B23”

• Cantidad: Rango “D14:D23”

De modo que para limpiar el formulario se deben limpiar dichas celdas. Por tanto, el

código de la macro “LIMPIAR” es:

Sub LIMPIAR()

Range("C9").ClearContents

Range("F9").ClearContents

Range("D25").ClearContents

Range("B14:B23").ClearContents

Range("D14:D23").ClearContents

End Sub

A continuación, se añadirá un botón “ACTUALIZAR” para guardar la información

principal de cada factura dentro de una tabla. La información para guardar es: “RUC”,

“FECHA”, “FORMA DE PAGO”, “DISTRIBUIDOR”, “SUBTOTAL”, “DESCUENTO”,

“IMPUESTO” y “TOTAL” de cada factura.

Para ello se utilizará un control tipo “Botón” del grupo “ActiveX”.

1. Seleccionar la pestaña “Programador”.

2. Seleccionar la opción “Insertar”.

3. Seleccionar el control “Botón” del grupo “Controles ActiveX”.

4. Dibujar el botón sobre la hoja de cálculo.

5. Abrir las “Propiedades del control”.

6. Cambiar la propiedad “Caption”.


Ilustración 161.- Inserción de un control "Botón ActiveX"

Para insertar el resumen de cada factura se deben realizar las acciones siguientes:

1. Seleccionar la hoja “Resumen factura”

2. Crear las cabeceras de los campos que conformarán la tabla y seleccionar las

cabeceras y un par de filas en blanco.

3. Seleccionar la carpeta “Insertar”

4. Seleccionar el grupo “Tablas”

5. Seleccionar la opción “Tabla”

6. En la caja de diálogo “Crear tabla” seleccionar “La tabla tiene encabezados”.



Ilustración 162.- Crear tabla para registrar "Resumen de factura"

Cada vez que se presione sobre el botón “ACTUALIZAR” se deben copiar los datos de la

factura al lugar correspondiente de la tabla, pero no se puede grabar una macro porque

copiaría las fórmulas y no los valores. Además, es necesario añadir una nueva línea la

tabla cada vez que se añadan datos a la tabla. Por tanto, el código para realizar esta

macro se ingresa haciendo doble clic sobre el botón y es el siguiente:

Private Sub btnACTULIZAR_Click()

Sheets("Resumen factura").Range("A2").Value = Sheets("Factura").Range("C9").Value

Sheets("Resumen factura").Range("B2").Value = Sheets("Factura").Range("F8").Value

Sheets("Resumen factura").Range("C2").Value = Sheets("Factura").Range("F9").Value

Sheets("Resumen factura").Range("D2").Value = Sheets("Factura").Range("C25").Value

Sheets("Resumen factura").Range("E2").Value = Sheets("Factura").Range("F24").Value

Sheets("Resumen factura").Range("F2").Value = Sheets("Factura").Range("F25").Value

Sheets("Resumen factura").Range("G2").Value = Sheets("Factura").Range("F26").Value

Sheets("Resumen factura").Range("H2").Value = Sheets("Factura").Range("F27").Value

Sheets("Resumen factura").Range("A2:H2").ListObject.ListRows.Add (1)

End Sub

Los botones programados para macros son dos “LIMPIAR” y “ACTUALIZAR”. El primero

de un control de formulario común y el segundo es un control de formulario ActiveX. El

primero limpia el formulario, y el segundo copia los datos generales de la factura en la

tabla ubicada en la hoja “Resumen factura”.


Ilustración 163.- Formulario de factura con dos botones

La hoja “Resumen factura” contiene los valores de las facturas almacenadas después

de aplicar el botón “ACTUALIZAR”.

Ilustración 164.- Contenido de la hoja "Resumen factura"

1.7 Introducción a Visual Basic Applications

1.7.1 Crear Macros En Ms Excel .

Introducción

Microsoft Office, permite trabajar con macros. Lo que se pretende es automatizar varias tareas, añadiendo por ejemplo un botón programable en el ambiente de Visual Basic Applications, que al pulsar sobre él realice las tareas programadas. Al familiarizarse con el ambiente de Visual Basic Applications, se puede editar varias

macros, llamar y ejecutar macros desde botones programables.


En esta unidad de estudio se revisará a automatizar y realizar tareas complejas,

aumentando la eficiencia y eficacia del trabajo.

Objetivos

• Aplicar las herramientas de Macros con VBA.

• Editar macros básicas para tareas repetitivas.

• Asignar botones de Controles ActiveX para ejecutar macros con un clic.

• Aplicar las herramientas de VBA para crear macros.

Contenidos

• Introducción.

• Preparar el libro para Controles Activex.

• Controles Activex.

• Cambiar de apariencia al botón.

• Insertar un icono en el botón.

• Insertar código en un botón.

• Crear formularios.

• Cargar un formulario en Excel.

• Programar un formulario.

• Trabajar con fórmulas.

• Proteger un proyecto de VBA con clave.

1.7.2 Preparar El Libro Para Trabajar Con Controles Activex . Introducción

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

Lo primero que se debe hacer para trabajar con los “cuadros de control” en Excel, es

preparar el libro. Para esto sigue el siguiente proceso:

Habilitar la pestaña “Programador” haciendo clic derecho sobre la cinta de opciones y

seleccionado la opción “Personalizar la cinta de opciones…”


Ilustración 165.- Opción "Personalizar la cinta de opciones"

Luego habilitar la pestaña “Programador” en la caja de diálogo “Opciones de Excel” y

presionar sobre el botón “Aceptar”

Ilustración 166.- Habilitación de la pestaña "Programador"

Una vez habilitada la pestaña “Programador”, guardar el libro como “Libro de Excel

habilitado para macros”.

1. Seleccionar la pestaña “Archivo”

2. Presionar sobre la opción “Guardar como”.

3. Presionar sobre la opción “Examinar”.

4. En la caja de diálogo “Guardar como” seleccionar en la sección “Tipo” la

opción “Libro de Excel habilitado para macros”.

5. Presionar sobre el botón “Guardar”.


Ilustración 167.- Guardado de un libro habilitado para macros

Finalmente, configurar las seguridades para el manejo de macros.

• Seleccionar la pestaña “Programador”

• Seleccionar la opción “Seguridad de macros”

• Dentro de la caja de diálogo “Centro de confianza”, seleccionar la opción

“Configuración de macros”

• Seleccionar “Deshabilitar todas las macros con notificación”

• Presionar sobre el botón “Aceptar”

Ilustración 168.- Configuración de las seguridades para uso de macros

1.7.3 Trabajar Con Controles Activex . Introducción


. La herramienta de “Controles ActiveX” está ubicada en el grupo “Controles” de la pestaña “Programador”, en la opción “Insertar”.

Ilustración 169.- Menú de "Controles ActiveX"

El grupo “Controles” de la pestaña “Programador” tienen las siguientes opciones:

• Insertar.- Contiene un menú con los objetos de formulario y los “Controles

ActiveX”.

• Modo Diseño.-Permite editar las propiedades de los “Controles ActiveX”

• Propiedades.- Despliega la lista de principales propiedades de los “Controles

ActiveX” para su edición.

• Ver código.- Permite la edición del código de programación de los “Controles

ActiveX”

• Ejecutar cuadro de diálogo.- Es una opción para ejecutar cuadros de diálogo de

versiones antiguas de Excel como la versión 5.0.


Ilustración 170.- Opciones del grupo "Controles"

.

Práctica

En una empresa han solicitado diseñar un menú con varios botones que al presionarlos pasen a las distintas opciones ubicadas en varias hojas de cálculo. Estas hojas de cálculo son “Menú”, “Ventas” y “Tabla dinámica”. Para dar solución al problema propuesto realizar lo siguiente:

• Crear las hojas: “Menú”, “Ventas” y “Tabla dinámica”.

• Insertar un botón desde la lista “Controles ActiveX” que se encuentra en la

Opción “Insertar” de la pestaña “Programador”.

• Editar las propiedades del botón. Esto se puede lograr pulsando sobre la

opción “Modo Diseño” que se encuentra en la pestaña “Programador” y

activando la caja de “Propiedades”.

Ilustración 171.- Inserción de un botón de comando ActiveX

Colocar un nombre al botón modificando la propiedad “(Name)”. El nombre no debe

contener caracteres especiales, espacios, ni debe ser una palabra reservada del

lenguaje de programación. Un buen nombre podría ser: “btnVentas”. Con el

nombre definido en esta propiedad se hará referencia al botón desde el código de

programación de Visual Basic.

Cambiar el texto que se encuentra sobre el botón. Para ello modificar la propiedad

“Caption”. El valor de esta propiedad podría ser “Ventas”.


Ilustración 172.- Modificación de las propiedades del botón "Ventas"

1.7.4 Cambiar De Apariencia Al Botón De Comando . Si se desea cambiar la apariencia del botón, se puede iniciar cambiando el color de fondo del

botón, el color del texto, el estilo del texto, y el tamaño de la fuente.

Para cambiar el color de fondo del botón realizar lo siguiente:

1. Seleccionar la opción “Modo Diseño” de la pestaña “Programador”.

2. Seleccionar el botón.

3. Seleccionar el botón “Propiedades de control” que se encuentra a la derecha

del botón “Modo Diseño”.

4. En el cuadro de diálogo de “Propiedades”, seleccionar la

propiedad “Backcolor”.

5. En la lista se visualizan dos grupos de colores: colores del “Sistema” y de la

“Paleta”.

6. De los colores de la “Paleta” seleccionar un color.

Ilustración 173.- Aplicación de color de fondo en un botón


Para cambiar el color de la fuente realizar lo siguiente:


2. Hacer clic derecho sobre el botón.

3. Seleccionar la opción “Propiedades” del menú desplegable

4. En el cuadro de diálogo de “Propiedades”, seleccionar la

propiedad “Fontcolor”.

5. En la lista se visualizan dos grupos de colores: colores del “Sistema” y de la

“Paleta”.

6. De los colores de la “Paleta” seleccionar un color.

Ilustración 174.- Configuración de un color para el texto del botón

Para cambiar el tipo, estilo y tamaño de la fuente realiza lo siguiente:


2. Hacer clic derecho sobre el botón.

3. Seleccionar la opción “Propiedades” del menú desplegable

4. En el cuadro de diálogo de “Propiedades”, seleccionar la propiedad “Font”.

5. En la caja de diálogo se presentan las secciones: “Fuente”, “Estilo de fuente” y

“Tamaño”.

6. Cambiar estas opciones según las necesidades.



Ilustración 175.- Cambio de tipo de letra del texto de un botón

Una vez definida la apariencia de un botón basta con copiar el modelo para obtener otros

botones similares. Luego se debe definir la propiedad “(Name)” y la propiedad “Caption” de la

copia según las necesidades.

Ilustración 176.- Copia de un botón modelo para generar otros botones

1.7.5 Insertar Un Icono En El Botón De Comando .


Para insertar un icono en el botón de “Controles ActiveX”, se sugiere preparar un conjunto de

iconos con extensión .ico. Para descargar algunos iconos utilizar el siguiente

enlace: AQUÍ. (iconos.zip)

Para insertar una imagen sobre el botón se deben ejecutar las siguientes acciones:

1. Seleccionar la pestaña “Programador”.

2. Seleccionar la opción “Modo Diseño”

3. Seleccionar el botón.

4. Seleccionar el botón “Propiedades de control” que se encuentra a la derecha

del botón “Modo Diseño”.

5. En el cuadro de diálogo de “Propiedades”, seleccionar la propiedad “Picture”.

Ilustración 177.- Selección de la propiedad "Picture"

Al abrirse la caja de diálogo “Cargar imagen” se debe buscar una imagen con de extensión

“.ico” y luego de seleccionarla presionar sobre el botón “Abrir”


Ilustración 178.- Selección de una imagen

Se puede seleccionar la ubicación de la imagen dentro del botón seleccionando una de las

opciones mostradas dentro de la propiedad “PicturePosition” disponible en la caja de diálogo

“Propiedades”.


Ilustración 179.- Propiedad "PicturePosition"

Para descargar la solución al ejercicio utilice el siguiente enlace AQUÍ.

(32.controles_activex.xlsm)

1.7.6 Insertar código en el botón de comando . Si se deseas navegar entre las hojas Menú, Tabla dinámica y Ventas se requiere insertar un

código de programación en los botones creados.

Para insertar el código realizar lo siguiente:

1. Seleccionar la pestaña “Programador”


3. Hacer doble clic sobre el botón

4. Observar el ambiente de desarrollo con la rutina asignada al botón.


Ilustración 180.- Ingreso a la subrutina del botón

La instrucción para activar la hoja de cálculo llamada “Tablas dinamicas” es la siguiente:

Sheets("Tablas dinámicas").Activate

Se añade dicha instrucción a la subrutina perteneciente al botón que activará la hoja “Tablas

dinámicas” y ahora dicho botón ya tiene funcionalidad. Para regresar a la hoja de cálculo se

presiona sobre el botón Excel que se encuentra en la parte superior izquierda de la barra de

controles.

Ilustración 181.- Programación del botón

Para que el botón funcione el “Modo Diseño” que se encuentra en la pestaña “Programador”

debe estar desactivado. Se pulsa sobre el botón programado y se activa la hoja de cálculo

“Tablas dinámicas”.


Ilustración 182.- Prueba de la programación del botón

1.7.7 Crear Formularios . Introducción

. Con Excel se pueden crear cajas diálogo en las que se deben añadir elementos de formulario y programar dichos controles. Estos controles interactuarán con las hojas de cálculo. Una serie de eventos pueden ser programados sobre cada control. Esta programación determina la funcionalidad del programa o aplicación. El lenguaje de programación utilizado para codificar las acciones a realizarse es Visual Basic.

Practica

Crear un “formulario de usuario” para ingresar los datos de una agenda telefónica y almacenarlos en una tabla. La tabla debe tener las columnas: “Nombre”, “Dirección”, y “Teléfono”. Además, debe contener inicialmente dos filas sin datos.

Para realizar esta actividad se deben realizar las siguientes acciones:

1. Seleccionar el rango que se transformará en tabla. 2. Seleccionar la pestaña “Insertar”. 3. Seleccionar la opción “Tabla” 4. En la caja de diálogo “Crear tabla”, seleccionar la opción “La tabla tiene

encabezados”. 5. Presionar sobre el botón “Aceptar”.


Ilustración 183.- Creación de una tabla para alojar los datos del formulario

Para crear el formulario de usuario:

1. Presionar la combinación de teclas “Alt+F11”.

2. Seleccionar el botón “Insertar Userform”.

3. Seleccionar la opción “Userform”

Ilustración 184.-Creación de un formulario de usuario.


Una vez creado el formulario de usuario se deben añadir algunos componentes. Dichos

componentes son:

1. Tres etiquetas, una para cada dato.

2. Tres cuadros de texto, una para cada dato a recolectar.

3. Dos botones, uno para insertar los datos en la tabla y otro para salir del

formulario.

Ilustración 185.- Componentes del formulario

Estos componentes se seleccionan en el “Cuadro de herramientas” y se dibujan sobre el

formulario. Para cambiar las propiedades de cualquier componente se puede usar la caja

de diálogo “Propiedades”.

Por ejemplo, para cambiar el título del formulario se debe seleccionar el formulario y luego

cambiar el valor de la propiedad “Caption” por el nuevo título.


Ilustración 186.- Cambio del título del formulario de usuario

Cada componente, incluyendo el formulario tiene un nombre. Este nombre es la forma

como se identifica dicho componente desde el lenguaje de programación. Por tanto, es

necesario conocer el nombre de cada componente. El nombre del componente puede ser

visualizado y modificado desde la caja de diálogo “Propiedades” desde la propiedad

“(Name)”.

Ilustración 187.- Definición del nombre del botón "Salir"

En el formulario creado y sus componentes se establecerán los siguientes nombres con

el objeto de identificarlos desde el lenguaje de programación:

NOMBRE COMPONENTE

lblNombre Etiqueta Nombre lblDireccion Etiqueta Dirección lblTelefono Etiqueta Teléfono tbxNombre Cuadro de texto Nombre tbxDireccion Cuadro de texto Dirección tbxTelefono Cuadro de texto Teléfono btnInsertar Botón Insertar btnSalir Botón Salir


Es necesario programar el botón “Salir” para que cierre el formulario cuando este sea

presionado. Haciendo doble clic sobre el botón “Salir” añadir el siguiente código.

Userform1.hide

De tal manera que la rutina debe verse como la siguiente:

Private Sub btnSalir_Click()

UserForm1.Hide

End Sub

1.7.8 Cargar Un Formulario En Excel . . Introducción

.

Una macro es una serie de instrucciones que se almacenan para que se puedan

ejecutar de forma secuencial mediante una sola llamada u orden de ejecución.

A continuación se revisará como programar un botón para cargar un

formulario de usuario desde una hoja de cálculo.

Práctica

Crear un botón ActiveX para desplegar un formulario de usuario.

Junto a la tabla se creará un botón que cargue el formulario de usuario creado. Para

ello se ejecutan las siguientes acciones:


2. Seleccionar la opción “Insertar”

3. Seleccionar el botón perteneciente a los “Controles ActiveX”

4. Dibujar el botón junto al formulario.

5. Asignar un texto que diga “Ingresar datos” modificando la propiedad

“Caption”.


Ilustración 188.- Botón para llamar al formulario

Una vez añadido el botón se debe insertar el código que permitirá llamar al formulario

cuando se presione sobre el botón:



3. Hacer doble clic sobre el botón.

4. Añadir las siguientes instrucciones a la rutina del botón:

Load UserForm1

UserForm1.Show

Ilustración 189.- Programación de botón para llamar al formulario

Deshabilitando el “Modo Diseño” y presionando sobre el botón “Agregar datos” se

despliega el formulario. Presionando el botón “Salir” el formulario se oculta.


Ilustración 190.- Uso de un botón para desplegar el formulario

1.7.9 Programar Un Formulario Introducción

Los componentes de un formulario pueden ser programados de tal manera que se

añada alguna funcionalidad a la interfaz de usuario. Aunque todos los componentes

pueden ser programado es común programar los botones de tal manera que al

presionarlos ejecuten alguna tarea.

Práctica

Mediante macros, usando el formulario de usuario utilizado anteriormente se

programará el botón “Insertar” para lograr que los campos ingresados en el formulario

sean insertados en la tabla definida en la hoja de cálculo.


Ilustración 191.- Relación entre los campos del formulario y las columnas de la tabla

Para ello ejecutar las siguientes acciones:

1. Seleccionar la carpeta “Programador”

2. Seleccionar la opción “Visual Basic” o presionar la combinación de teclas

“ALT+F11”

3. En el ambiente de programación de “Visual Basic”, hacer doble clic sobre

“UserForm1” que se encuentra dentro del grupo “Formularios” en la caja

del “Proyecto”.

4. Hacer doble clic sobre el botón “Insertar”

5. Añadir las siguientes líneas de código:

Sheets("Hoja1").Range("B5").FormulaR1C1 = tbxNombre.Text

Sheets("Hoja1").Range("C5").FormulaR1C1 = tbxDireccion.Text

Sheets("Hoja1").Range("D5").FormulaR1C1 = tbxTelefono.Text

Range("B5:D5").ListObject.ListRows.Add (1)


Ilustración 192.- Inserción del código para insertar datos en la tabla

Las instrucciones añadidas son fáciles de entender. El texto contenido dentro de la caja

de texto “tbxNombre” es tomado mediante la propiedad “Text” y es colocado como

contenido de la celda “B5” de la hoja “Hoja1”.

El texto de la caja “tbxNombre” se representa por “tbxNombre.text”, donde

“tbxNombre” es el nombre del componente y “Text” es su propiedad. Por tanto, para

identificar una propiedad de un componente se usa el nombre del componente seguido

por un punto y el nombre de la propiedad.

Por otro lado, para identificar una hoja se usa la función “Sheets”, dentro de

paréntesis y entre comillas se escribe el nombre de la hoja. Por ejemplo, la “Hoja1” es

identificada por la expresión:

Sheets(“Hoja1”)

Para identificar una celda o rango de celdas se usa la función “Range”, dentro de

paréntesis y entre comillas se escribe el nombre de la celda o rango de celdas. Por

ejemplo, la celda “B5” es identificada por la expresión:

Range(“B5”)

Ahora si se desea identificar la celda “B5” perteneciente a la “Hoja1” la expresión que

debe usarse es:

Sheets(“Hoja1”).Range(“B5”)


La celda o rango de celdas tienen propiedades. La propiedad “FormulaR1C1”

representa el contenido de la celda. Por lo que el contenido de la celda “B5”

perteneciente a la “Hoja1” se representa como:

Sheets(“Hoja1”).Range(“B5”).FormulaR1C1

Si se desea colocar algo sobre el contenido de dicha celda basta con asignarle dicho

valor mediante el signo igual. Por ejemplo, si sobre la celda en mención se desea

colocar el texto “Hola mundo” la expresión quedaría de la siguiente manera:

Sheets(“Hoja1”).Range(“B5”).FormulaR1C1=”Hola mundo”

Pero lo que realmente se desea colocar en dicha celda es el texto que esta dentro de l

caja “tbxNombre”. Por lo que la expresión resultante es:

Sheets(“Hoja1”).Range(“B5”).FormulaR1C1=tbxNombre.Text

Finalmente, la expression:

Range("B5:D5").ListObject.ListRows.Add (1)

Permite añadir una fila en el rango “B5:D5” sobre el objeto tabla definido en dicho

rango.

Cuando se pulsa sobre el botón “Agregar Datos” que se encuentra en la hoja de

cálculo se despliega el formulario de usuario. Y cuando se pulsa sobre el botón

“Insertar” que se encuentra sobre el formulario, se añaden datos a la tabla que se

encuentra sobre la hoja.


Ilustración 193.- Ejecución de la aplicación "Agenda"

excel avanzado con tablas dinamicas y macrossaccec.com/descargas/practica/excel macros.pdfde esta...

Documents