See discussions, stats, and author profiles for this publication at: [Link]

net/publication/352775236

MS EXCEL AVANZADO Y TABLAS DINÁMICAS MS Excel Avanzado

Presentation · June 2021

CITATIONS READS
0 3,308

2 authors:

Patricia Acosta-Vargas Luis Salvador-Ullauri

Universidad de Las Américas University of Alicante
155 PUBLICATIONS   483 CITATIONS    56 PUBLICATIONS   286 CITATIONS   

SEE PROFILE SEE PROFILE

Some of the authors of this publication are also working on these related projects:

METRICS AND HEURISTICS OF WEB ACCESSIBILITY: CASE STUDY UNIVERSITIES OF LATIN AMERICA View project

Telerahabilitation platform for hip surgery patients View project

All content following this page was uploaded by Patricia Acosta-Vargas on 04 July 2021.

The user has requested enhancement of the downloaded file.

 Autores:
Patricia Acosta-
Vargas., PhD.; Luis
Salvador-Ullauri

MS EXCEL AVANZADO Y TABLAS Google Académico:

[Link]

DINÁMICAS citations?user=16_omfwA
AAAJ&hl=es&oi=ao
E-mail:
acostanp@[Link]
Blog:
[Link]
Excel Avanzado proporciona herramientas y funciones
iciaAcosta/
eficaces que pueden utilizarse para analizar, compartir y
administrar los datos con facilidad. Excel como una Junio 2021
poderosa herramienta de cálculo matemático, estadístico y
financiero que permite manejar grandes volúmenes de datos
para generar reportes elaborados con rapidez y confianza. La
herramienta permitirá resumir comparar, patrones y
tendencias.
 MS Excel Avanzado

CONTENIDO

Contenido
Introducción a MS Excel ...................................................................................................................... 4

Iniciar Excel...................................................................................................................................... 5

Elementos de la pantalla de Excel ................................................................................................... 5

Fórmulas .......................................................................................................................................... 6

Formatos ............................................................................................................................................. 7

Formato de celdas ........................................................................................................................... 7

Personalizar los formatos de las celdas en Microsoft Excel ............................................................ 7

Códigos básicos de formato de número ............................................................................................. 7

Cambiar la forma en que Microsoft Excel muestra los números .................................................... 9

Validación de datos ........................................................................................................................... 11

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

Herramientas de validación de datos ............................................................................................... 12

Celdas con longitud de texto ..................................................................................................... 14

Mensajes de entrada......................................................................................................................... 17

Mensajes de error ..................................................................................................................... 19

Validar números enteros dentro de límites .............................................................................. 22

Comprobar entradas no válidas ................................................................................................ 27

Validar Fechas ........................................................................................................................... 31

Validar Listas ............................................................................................................................. 35

Buscar celdas con validación ..................................................................................................... 39

Borrar validación de datos ........................................................................................................ 41

Fórmulas y funciones .................................................................................................................... 42

Funciones .................................................................................................................................. 42
Ing. Patricia Acosta, PhD. acostanp@[Link] 1
MS Excel Avanzado

Funciones - Sintaxis ................................................................................................................... 43

Funciones Lógicas: Si ................................................................................................................. 45

Más Funciones Lógicas .............................................................................................................. 49

Funciones Anidadas................................................................................................................... 53

Funciones de Base de Datos ...................................................................................................... 56

Función BDCONTAR................................................................................................................... 56

Función BDCONTARA ................................................................................................................ 57

Función BDMAX......................................................................................................................... 58

Función BDMIN ......................................................................................................................... 58

Función BDSUMA ...................................................................................................................... 59

Función BDPROMEDIO .............................................................................................................. 59

Funciones Financieras ............................................................................................................... 60

Función Pago ............................................................................................................................. 60

Función TIR ................................................................................................................................ 61

Función VAN (VNA) ................................................................................................................... 61

Tablas de Amortización ............................................................................................................. 62

Crear Un Informe De Tabla Dinámica........................................................................................ 66

Herramientas De Tabla Dinámica.............................................................................................. 70

Resumir Datos De Un Informe Dinámico .................................................................................. 71

Opciones De Diseño De Un Informe Dinámico ......................................................................... 74

Actualizar Un Informe De Tabla Dinámica ................................................................................ 76

Fórmulas en un Informe de Tabla Dinámica ............................................................................. 84

Gráficos dinámicos ........................................................................................................................ 87

Crear Un Gráfico Dinámico........................................................................................................ 87

Opciones De Diseño De Gráfico Dinámico ................................................................................ 90

Ing. Patricia Acosta, PhD. acostanp@[Link] 2

 MS Excel Avanzado

Referencias ........................................................................................................................................ 93

Ing. Patricia Acosta, PhD. acostanp@[Link] 3

MS Excel Avanzado

Introducción a MS Excel
Una de las aplicaciones informáticas más utilizadas en las empresas son las hojas de
cálculo, entre ellas Microsoft Excel, la presente guía aplica la versión de Microsoft Office
365 que permiten al usuario manipular cualquier tipo de dato o información.

El objetivo de las hojas de cálculo es proporcionar un entorno simple y uniforme para

generar tablas con información y a partir de ellas obtener mediante fórmulas y cálculos
nuevos datos. Las hojas de cálculo permiten a los usuarios manipular grandes cantidades
de información de forma rápida y fácil que permiten visualizar los efectos desde
diferentes perspectivas.

El área de aplicación más importante ha sido hasta ahora el análisis estadístico y

profesional para desarrollar modelos de gestión y tomas de decisiones, entre los que se
puede citar la planificación de proyectos y el análisis financiero, el análisis contable, el
control de balances, la gestión de personal. En cualquier caso, los límites de este tipo de
aplicaciones dependen de la utopía del usuario [1].

Microsoft Excel 365 permite desarrollar modelos personalizados que se pueden adaptar a
las necesidades particulares de cada usuario. El interesado puede decidir lo que desea
hacer y escribir su propio programa al generar flexibilidad y versatilidad de la hoja de
cálculo, convirtiéndola en una herramienta de investigación aplicada, para economistas,
investigadores, financieros, directivos, ingenieros, profesionales e incluso para el hogar.

Ing. Patricia Acosta, PhD. acostanp@[Link] 4

MS Excel Avanzado

Iniciar Excel
Excel se puede iniciar de las maneras siguientes:
1. Se hace un doble clic sobre el acceso directo del Escritorio.

Ilustración 1.- Acceso a MS Excel

2. Ir a la opción de buscar
Digitar Excel

Ilustración 2.- Inicio de MS Excel

Elementos de la pantalla de Excel

Al entrar a MS Excel presenta la siguiente ventana con los siguientes elementos:

Ing. Patricia Acosta, PhD. acostanp@[Link] 5

MS Excel Avanzado

8 9
4 5 7
10 11
3

12

15

14 17
18 13 16

Ilustración 3.- Pantalla inicial de MS Excel

1. Barra de Título
2. Barra de Menú
3. Barra de fórmulas
4. Cuadro de nombres
5. Grupo del Portapapeles
6. Grupo de Fuente
7. Grupo de Alineación
8. Grupo de Formato de Número
9. Grupo de Estilos
10. Grupo de Celdas
11. Grupo de Edición
12. Celda activa
13. Hoja activa
14. Barras de desplazamiento horizontal
15. Barras de desplazamiento vertical
16. Zoom
17. Botones de presentación
17. Barra de estado

La versión de Excel 365 cuenta con hojas de trabajo formadas de celdas, dispuestas por
16.384 columnas y 1.048.576 filas [2].

Fórmulas
Consiste en una secuencia formada por: valores constantes, referencias a otras celdas,
nombres, funciones, operadores. Se pueden realizar diversas operaciones con los datos de
las hojas de cálculo como *, +, -, Sen, Cos, etc.
Ing. Patricia Acosta, PhD. acostanp@[Link] 6
MS Excel Avanzado

En una fórmula se pueden mezclar constantes, nombres, referencias a otras celdas,

operadores y funciones. La fórmula se escribe en la barra de fórmulas y debe empezar
siempre por el signo =.

Formatos

Formato de celdas
Veremos las diferentes opciones disponibles en Excel respecto al cambio de aspecto de las
celdas de una hoja de cálculo y cómo manejarlas para modificar el tipo y aspecto y forma
de visualizar números en la celda.

Personalizar los formatos de las celdas en Microsoft Excel

Para ver Haga clic en
Símbolos de moneda Estilo de moneda

Números como porcentajes Estilo porcentual

Pocos dígitos detrás del separador Reducir decimales

Más dígitos detrás del separador
Aumentar decimales
Tabla 1: Formato de número

Códigos básicos de formato de número

Entre los códigos básico para aplicar en el formato personalizado se identifican los
símbolos:
# Presenta únicamente los dígitos significativos; no muestra los ceros sin valor.
0 (cero) muestra los ceros sin valor.
? Agrega los espacios de los ceros sin valor a cada lado del separador. También puede
utilizarse para las fracciones que tengan un número de dígitos variable.
Para aplicar este formato es esencial definir el separador de decimales y miles
Esta configuración se realiza directamente desde MS Excel, desde el menú Archivo,
Opciones, Avanzadas como se muestra en la Ilustración 4.

Ing. Patricia Acosta, PhD. acostanp@[Link] 7

MS Excel Avanzado

Ilustración 4.- Configuración de decimales y miles en Excel

Para cambiar el punto (.) como separador de decimales se digita el punto (.) en la casilla
que se muestra en la figura, de igual forma para el separador de miles se digita la coma
(,). En la Tabla 2 se muestran algunos ejemplos considerando como separador de
decimales al punto (.).
Digitar Código Visualizar
18024.59 como
18024.6 ####.# 18024.6
7.1 como 7.100 #.000 7.100
.631 como 0.6 0.# 0.6
12 como 12.0 #.0# 12.0
1234.568 como
#.0#
1234.57 1234.57
Tabla 2: Códigos básicos de formato de número

Tabla 3: Visualización de códigos básicos

Ing. Patricia Acosta, PhD. acostanp@[Link] 8

MS Excel Avanzado

Para definir el color de una sección del formato, escriba en la sección el nombre del color
entre corchetes. El color debe ser el primer elemento de la sección.

Tabla 4: Colores de formatos personalizados

Cambiar la forma en que Microsoft Excel muestra los números

1. Seleccione las celdas a las que desea dar formato.
2. Haga clic en el botón derecho Formato de celdas…
3. Para seleccionar un formato elija el Grupo de Formato de Número

Ilustración 5.- Formato de celdas

4. Se visualiza:

Ing. Patricia Acosta, PhD. acostanp@[Link] 9

MS Excel Avanzado

Ilustración 6.- Formato personalizado

5. Seleccione la pestaña Número

6. En Categoría seleccione: Personalizada.
7. Para esto escriba un valor en la celda, por ejemplo, si desea verlo en color azul
escriba entre corchetes. Ejemplo: [Azul]

Ilustración 7.- Editar formato personalizado

8. Observe que los valores ingresados en las celdas se visualizarán en color azul.
9. Si además desea ingresar una condición, por ejemplo, que se visualicen en color
azul todos números con 2 decimales cuyos valores mayores o iguales a 10, caso

Ing. Patricia Acosta, PhD. acostanp@[Link] 10

MS Excel Avanzado

contrario que se visualicen en color rojo. Las condiciones se escribirán así:

[Azul][>=10]#,00;[Rojo] #,00.
Para separar una condición de otra se usa el separador de listas que se sugiere sea
el punto y coma.

Validación de datos
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.

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 11

MS Excel Avanzado

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 8.-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 9.-Grupo “Herramientas de datos”

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 12

MS Excel Avanzado

Ilustración 10.- 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 11.- 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.

Ing. Patricia Acosta, PhD. acostanp@[Link] 13

MS Excel Avanzado

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 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 12.- Opción "Formato de celda"

Ing. Patricia Acosta, PhD. acostanp@[Link] 14

MS Excel Avanzado

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 13.-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...”

Ing. Patricia Acosta, PhD. acostanp@[Link] 15

MS Excel Avanzado

Ilustración 14.- 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 15.- 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.

Ing. Patricia Acosta, PhD. acostanp@[Link] 16

MS Excel Avanzado

Ilustración 16.- 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 17.- Mensaje de error debido al ingreso de un número de RUC incorrecto

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.
Ing. Patricia Acosta, PhD. acostanp@[Link] 17
MS Excel Avanzado

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 18.- 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”

Ing. Patricia Acosta, PhD. acostanp@[Link] 18

MS Excel Avanzado

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

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

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 20.- 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”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 19

MS Excel Avanzado

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 21.- 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 22.- 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:

Ing. Patricia Acosta, PhD. acostanp@[Link] 20

MS Excel Avanzado

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 23.- 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 24.- Estilo "Advertencia"

Ing. Patricia Acosta, PhD. acostanp@[Link] 21

MS Excel Avanzado

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

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:
Ing. Patricia Acosta, PhD. acostanp@[Link] 22
MS Excel Avanzado

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 26.- Selección del rango de celdas a validar

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”

Ing. Patricia Acosta, PhD. acostanp@[Link] 23

MS Excel Avanzado

Ilustración 27.- 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”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 24

MS Excel Avanzado

Ilustración 28.- 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 29.- 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”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 25

MS Excel Avanzado

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 30.- 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 31.- Despliegue del mensaje de error en validación de números

Ing. Patricia Acosta, PhD. acostanp@[Link] 26

MS Excel Avanzado

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 27

MS Excel Avanzado

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”.
• Finalmente, presionar sobre el botón “Aceptar”.

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.

Ing. Patricia Acosta, PhD. acostanp@[Link] 28

MS Excel Avanzado

Ilustración 33.- 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 34.- 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”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 29

MS Excel Avanzado

Ilustración 35.- 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 36.- 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”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 30

MS Excel Avanzado

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

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 “Configuración”.
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”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 31

MS Excel Avanzado

Ilustración 38.- 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.

Ing. Patricia Acosta, PhD. acostanp@[Link] 32

MS Excel Avanzado

Ilustración 39.- 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.

Ing. Patricia Acosta, PhD. acostanp@[Link] 33

MS Excel Avanzado

Ilustración 40.- 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 41.- 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

Ing. Patricia Acosta, PhD. acostanp@[Link] 34

MS Excel Avanzado

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 42.- Opción para aplicar configuración de validación a todas las celdas de
igual formato

Validar Listas
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:

Ing. Patricia Acosta, PhD. acostanp@[Link] 35

MS Excel Avanzado

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”.
6. Finalmente, presionar sobre el botón “Aceptar”.

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 36

MS Excel Avanzado

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

Ilustración 44.- 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 45.- Asignación del nombre "ciudades" a un rango

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 37

MS Excel Avanzado

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 38

MS Excel Avanzado

Ilustración 47.- 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.

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 39

MS Excel Avanzado

Ilustración 48.- 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".

Ing. Patricia Acosta, PhD. acostanp@[Link] 40

MS Excel Avanzado

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

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 41

MS Excel Avanzado

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

Fórmulas y funciones

Funciones
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,

Ing. Patricia Acosta, PhD. acostanp@[Link] 42

MS Excel Avanzado

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

asistente de funciones.

Funciones - Sintaxis
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 51.-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”

Ing. Patricia Acosta, PhD. acostanp@[Link] 43

MS Excel Avanzado

Ilustración 52.- 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 53.- Botón "fx"

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

cuadro de diálogo “Insertar función”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 44

MS Excel Avanzado

Ilustración 54.- 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”.

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)

Ing. Patricia Acosta, PhD. acostanp@[Link] 45

MS Excel Avanzado

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 46

MS Excel Avanzado

Ilustración 55.- 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”.
• Finalmente, presionar sobre el botón “Aceptar”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 47

MS Excel Avanzado

Ilustración 56.-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 57.-Resultado de la ejecución de la función "SI"

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

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 48

MS Excel Avanzado

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.

[Link] Devuelve un valor si la expresión es un error y otro valor si no lo es.

[Link] 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.

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)

Ing. Patricia Acosta, PhD. acostanp@[Link] 49

MS Excel Avanzado

• 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”.

La solución al ejercicio planteado es la siguiente:

• 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.
• 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 “Búsqueda y
referencia”.
• En la sección “Selecciona una función” seleccionar “BUSCARV”.
• Presionar sobre el botón “Aceptar”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 50

MS Excel Avanzado

Ilustración 59.- 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”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 51

MS Excel Avanzado

Ilustración 60.- 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”.
5. Finalmente, presionar sobre el botón “Aceptar”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 52

MS Excel Avanzado

Ilustración 61.- 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 62.- Resultado de usar la función "BUSCARV"

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
Ing. Patricia Acosta, PhD. acostanp@[Link] 53
MS Excel Avanzado

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.

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

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

Ilustración 63.- 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”.
5. Presionar sobre el botón “Aceptar”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 54

MS Excel Avanzado

Ilustración 64.- 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 65.-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

Ing. Patricia Acosta, PhD. acostanp@[Link] 55

MS Excel Avanzado

en blanco, mientras que aquella en las cuales se puede realizar la operación de división
muestran el resultado.

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

función "SI"

Funciones de Base de Datos

Función BDCONTAR
Cuenta [3] las celdas que contienen un número en una columna de una lista o base de
datos y que concuerdan con los criterios especificados.
Sintaxis
BDCONTAR(base_de_datos;nombre_de_campo;criterios)
Base_de_datos es el rango de celdas que compone la base de datos.
Nombre_de_campo indica el campo que se utiliza en la función.
Criterios es el rango de celdas que contiene los criterios de la base de datos.
Puede utilizar cualquier rango en el argumento Criterios mientras éste incluya por lo menos un
rótulo de columna y por lo menos una celda debajo del rótulo de columna que especifique una
condición de columna.
Ejemplo: Encontrar el número de manzanos cuyo alto varía entre 10 y 16 metros. En la siguiente
ilustración se muestra una base de datos de un huerto. Cada registro contiene información acerca
de un árbol.

Ing. Patricia Acosta, PhD. acostanp@[Link] 56

MS Excel Avanzado

Ilustración 67.- BDCONTAR

Función BDCONTARA
Cuenta el número de celdas que no están en blanco dentro de los registros de la base de datos
que cumplen con los criterios especificados.
Sintaxis
BDCONTARA(base_de_datos;nombre_de_campo;criterios)
Base_de_datos es el rango de celdas que compone la base de datos.
Nombre_de_campo indica el campo que se utiliza en la función.
Criterios es el rango de celdas que contiene los criterios de la base de datos. Puede utilizar
cualquier rango en el argumento Criterios mientras éste incluya por lo menos un rótulo de
columna y por lo menos una celda debajo del rótulo de columna que especifique una condición de
columna.
Ejemplo: Encontrar el número de manzanos cuyo alto varía entre 10 y 16 metros y determina el
número de campos Ganancia de esos registros que no están en blanco.

Ilustración 68.- BDCONTARA

Ing. Patricia Acosta, PhD. acostanp@[Link] 57

MS Excel Avanzado

Función BDMAX
Devuelve el valor máximo de las entradas seleccionadas de una base de datos que coinciden con
los criterios.
Sintaxis
BDMAX(base_de_datos;nombre_de_campo;criterios)
Base_de_datos: es el rango de celdas que compone la base de datos
Nombre_de_campo indica el campo que se utiliza en la función.
Criterios es el rango de celdas que contiene los criterios de la base de datos.
Ejemplo: Encontrar la ganancia máxima de manzanos y perales. En la siguiente ilustración se
muestra una base de datos de un huerto. Cada registro contiene información acerca de un árbol.

Ilustración 69.- BDMAX

Función BDMIN
Devuelve el valor mínimo de las entradas seleccionadas de una base de datos que coinciden con
los criterios.
Sintaxis
BDMIN(base_de_datos;nombre_de_campo;criterios)
Base_de_datos: es el rango de celdas que compone la base de datos
Nombre_de_campo indica el campo que se utiliza en la función.
Criterios es el rango de celdas que contiene los criterios de la base de datos.
Ejemplo: Encontrar la ganancia mínima de manzanos con un alto superior a 10 metros.

Ing. Patricia Acosta, PhD. acostanp@[Link] 58

MS Excel Avanzado

Ilustración 70.- BDMIN

Función BDSUMA
Suma los números de una columna de una lista o base de datos que concuerden con las
condiciones especificadas.
Sintaxis
BDSUMA(base_de_datos;nombre_de_campo;criterios)
Base_de_datos es el rango de celdas que compone la base de datos.
Nombre_de_campo indica el campo que se utiliza en la función.
Criterios es el rango de celdas que contiene los criterios de la base de datos.
Puede utilizar cualquier rango en el argumento Criterios mientras éste incluya por lo menos un
rótulo de columna y por lo menos una celda debajo del rótulo de columna que especifique una
condición de columna.
Ejemplo: Encontrar la ganancia total de manzanos.

Ilustración 71.- BDSUMA

Función BDPROMEDIO
Devuelve el promedio de las entradas seleccionadas de una base de datos que coinciden con los
criterios.
Sintaxis

Ing. Patricia Acosta, PhD. acostanp@[Link] 59

MS Excel Avanzado

BDPROMEDIO(base_de_datos;nombre_de_campo;criterios)
Base_de_datos es el rango de celdas que compone la base de datos.
Nombre_de_campo indica el campo que se utiliza en la función.
Criterios es el rango de celdas que contiene los criterios de la base de datos.
Puede utilizar cualquier rango en el argumento Criterios mientras éste incluya por lo menos un
rótulo de columna y por lo menos una celda debajo del rótulo de columna que especifique una
condición de columna.
Ejemplo: Encontrar el rendimiento promedio de manzanos con un alto de más de 10 metros.

Ilustración 72.- BDPROMEDIO

Funciones Financieras

Función Pago
Calcula el pago [4] de un préstamo basándose en pagos constantes y una tasa de interés
constante.
Sintaxis
PAGO(tasa;nper;va;vf;tipo)
Tasa: es la tasa de interés del préstamo.
Nper:es el número total de pagos del préstamo.
Va: es el valor actual.
Vf: es el valor futuro. Si el argumento Vf se omite, se asume que es 0 (o el valor futuro de un
préstamo es cero).
Tipo: es un numero 0 o 1 e indica el vencimiento de pagos.
Tipo:0 al final del período.
Tipo:1 al inicio del período.
Observaciones: El pago devuelto incluye el capital y el interés.

Ing. Patricia Acosta, PhD. acostanp@[Link] 60

MS Excel Avanzado

Ilustración 73.- PAGO

Función TIR
Devuelve la tasa interna de retorno de los flujos de caja representados por los números
del argumento valores. Estos flujos de caja no tienen por qué ser constantes, como es el
caso en una anualidad. Sin embargo, los flujos de caja deben ocurrir en intervalos
regulares, como meses o años. La tasa interna de retorno equivale a la tasa de interés
producida por un proyecto de inversión con pagos (valores negativos) e ingresos (valores
positivos) que ocurren en períodos regulares.
TIR está íntimamente relacionado a VAN, la función valor neto actual.
La tasa de retorno calculada por TIR es la tasa de interés correspondiente a un valor neto
actual 0 (cero).
Ejemplos: Supongamos que desea abrir un restaurante. El costo estimado para la inversión
inicial es de $70.000, esperándose el siguiente ingreso neto para los primeros cinco años:
$12.000; $15.000; $18.000; $ 21.000 y $ 26.000.

Ilustración 74.- TIR

Función VAN (VNA)

Calcula el valor neto [5] presente de una inversión a partir de una tasa de
descuento y una serie de pagos futuros (valores negativos) e ingresos (valores
positivos).
Ing. Patricia Acosta, PhD. acostanp@[Link] 61
MS Excel Avanzado

En el primer caso, se considera una inversión que comienza al principio del periodo. La inversión se
considera de $ 35.000 y se espera recibir ingresos durante los seis primeros años. La tasa de
descuento anual es de 7,50%. En la celda B9 se obtiene el valor neto actual de la inversión.
VNA= VNA(D6;D8:D13)+D7 no se incluye el costo inicial . No se incluye el costo inicial de $ 35.000
como uno de los valores porque el pago ocurre al principio del primer periodo.

Ilustración 75.-VNA al inicio

Segundo caso, se considera una inversión de $ 20.000 a pagar al final del primer periodo y se
recibirá ingresos anuales durante los próximos cinco años. Suponiendo una tasa de descuento
anual del 8,50% se calcula el valor actual de la inversión.
VNA=VNA(F6;F7:F12)

Ilustración 76.- VNA al final

Tablas de Amortización
Amortizar significa reducir gradualmente una deuda o un préstamo a través de pagos periódicos.
Una tabla de amortización permite especificar el detalle de cada uno de los pagos hasta la
liquidación total del préstamo.

Ing. Patricia Acosta, PhD. acostanp@[Link] 62

MS Excel Avanzado

Ilustración 77.- Tabla de amortización

Para generar una tabla de amortización se requieren los siguientes datos: el período, la deuda
inicial, la tasa de interés, el interés, la amortización, el pago y la deuda final.
Los períodos pueden ser de 12, 24, 36 meses.
El interés se calcula multiplicando la deuda inicial y se divide la tasa de interés para el número de
períodos. En este caso Interés=$C$5*D5/12
La amortización se calcula con la deuda inicial dividido para el número de períodos en este caso
Amortización=$C$5/12.
El pago se calcula sumando el interés más la amortización en este caso Pago =E5+F5.
La deuda final se calcula sumando la deuda inicial más el interés menos el pago en este caso
Deuda final =C5+E5-G5.
Para completar la tabla de amortización, en el segundo período se coloca como deuda inicial el
resultado de la deuda final del período uno. La tasa de interés se fija en este caso =+$D$5.
Finalmente, se arrastra las fórmulas desde el período dos al doce de tal forma que la deuda final es
cero, como se muestra en la ilustración 77.

Tablas Dinámicas

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.

Ing. Patricia Acosta, PhD. acostanp@[Link] 63

MS Excel Avanzado

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

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 78.- 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”

Ing. Patricia Acosta, PhD. acostanp@[Link] 64

MS Excel Avanzado

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

Sugerencias Para Crear Un Informe De 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 80.-Tabla dinámica: error por no definir un nombre de columna

No deben existir columnas vacías.

Ing. Patricia Acosta, PhD. acostanp@[Link] 65

MS Excel Avanzado

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.

Crear Un Informe De Tabla Dinámica

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.
La solución al ejercicio planteado es la siguiente:

• 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”.
• Presionar sobre el botón “Aceptar”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 66

MS Excel Avanzado

Ilustración 81.- 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 82.- Estructura de una tabla dinámica

Ing. Patricia Acosta, PhD. acostanp@[Link] 67

MS Excel Avanzado

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 83.- Distribución de campos sobre la tabla dinámica

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).
• Presionar sobre el botón “Aceptar”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 68

MS Excel Avanzado

Ilustración 84.- 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 85.- Diseño clásico de una tabla dinámica

Ing. Patricia Acosta, PhD. acostanp@[Link] 69

MS Excel Avanzado

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 70

MS Excel Avanzado

• 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 87.- 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.

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.
La solución al ejercicio planteado es la siguiente:

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 71

MS Excel Avanzado

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”.
7. Presionar sobre el botón “Aceptar”.

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

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

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 72

MS Excel Avanzado

Ilustración 89.- 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 90.- Cambiando la operación del campo "Valor"

Ing. Patricia Acosta, PhD. acostanp@[Link] 73

MS Excel Avanzado

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

Ilustración 91.- Resultado del promedio del valor

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

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 92.- Selección de un diseño para la tabla dinámica

Ing. Patricia Acosta, PhD. acostanp@[Link] 74

MS Excel Avanzado

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 93.- Aplicación de un formato de esquema

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

Ilustración 94.- 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:

Ing. Patricia Acosta, PhD. acostanp@[Link] 75

MS Excel Avanzado

• 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 95.- 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 96.- Aplicación de subtotales en tablas dinámicas

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 76

MS Excel Avanzado

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. La solución al ejercicio
planteado es la siguiente:

• Seleccionar el reporte dinámico a actualizar.

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

Ilustración 97.-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 98.- Dato a actualizar

Ing. Patricia Acosta, PhD. acostanp@[Link] 77

MS Excel Avanzado

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 99.- 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 100.- Opción "Actualizar"

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 78

MS Excel Avanzado

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. La solución al ejercicio planteado es la siguiente:

• Visualizar en la base los nuevos registros ingresados.

Ilustración 101.- 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 102.- Actualización de la tabla dinámica

Ing. Patricia Acosta, PhD. acostanp@[Link] 79

MS Excel Avanzado

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 103.- Opción "Cambiar origen de datos"

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

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 80

MS Excel Avanzado

Ilustración 104.- 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 105.- Tabla dinámica actualizada

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.

Ing. Patricia Acosta, PhD. acostanp@[Link] 81

MS Excel Avanzado

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. La solución al ejercicio planteado es la siguiente:

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 106.-Agrupación de filas por fechas

Observar el resultado de la agrupación por fechas.

Ing. Patricia Acosta, PhD. acostanp@[Link] 82

MS Excel Avanzado

Ilustración 107.- 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”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 83

MS Excel Avanzado

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

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. La solución al ejercicio planteado es la siguiente:

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

2. Seleccionar la pestaña “Análisis de tabla dinámica”.
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”.
Ing. Patricia Acosta, PhD. acostanp@[Link] 84
MS Excel Avanzado

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 109.- Añadiendo un campo calculado

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

Ilustración 110.- 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:

Ing. Patricia Acosta, PhD. acostanp@[Link] 85

MS Excel Avanzado

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

2. Seleccionar la pestaña “Análisis de tabla dinámica”.
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 111.-Definición del campo calculado "Ingreso total"

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

ingresos”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 86

MS Excel Avanzado

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

Gráficos dinámicos
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.

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. La solución al ejercicio planteado es la siguiente:

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”
5. Presionar sobre el botón “Aceptar”.

Ing. Patricia Acosta, PhD. acostanp@[Link] 87

MS Excel Avanzado

Ilustración 113.- 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 114.- Gráfico dinámico

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

1. Seleccionar el gráfico.

Ing. Patricia Acosta, PhD. acostanp@[Link] 88

MS Excel Avanzado

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”.
5. Presionar sobre el botón “Aceptar”.

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

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

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

Ing. Patricia Acosta, PhD. acostanp@[Link] 89

MS Excel Avanzado

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 117.- Cambio de estilo de gráfico dinámico

Los estilos pueden complementarse seleccionando gamas de colores:

Ing. Patricia Acosta, PhD. acostanp@[Link] 90

MS Excel Avanzado

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

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

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:
Ing. Patricia Acosta, PhD. acostanp@[Link] 91
MS Excel Avanzado

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 119.- 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 bote de pintura.
3. Seleccionar en el menú de colores el color requerido.

Ilustración 120.- Cambio de color de la línea de tendencia

Ing. Patricia Acosta, PhD. acostanp@[Link] 92
 MS Excel Avanzado

Referencias
1. Acosta-Vargas P (2015) Excel aplicado al manejo de datos.
[Link] aplicado a [Link].
Accessed 25 Jun 2021

2. Microsoft (2021) Especificaciones y límites de Excel.

[Link]
1672b34d-7043-467e-8e27-269d656771c3?ui=es-es&rs=es-
es&ad=es#ID0EBABAAA=Newer_versions. Accessed 25 Jun 2021

3. Microsoft (2021) BDCONTAR (función BDCONTAR).

[Link]
fb0d-4d8d-97db-8d5f076eaeb1. Accessed 26 Jun 2021

4. Microsoft (2021) Función PAGO. [Link]

es/office/función-pago-0214da64-9a63-4996-bc20-214433fa6441. Accessed 26 Jun
2021

5. Microsoft (2021) Función NPV. [Link]

npv-8672cb67-2576-4d07-b67b-ac28acf2a568. Accessed 26 Jun 2021

Ing. Patricia Acosta, PhD. acostanp@[Link] 93

View publication stats