Informática

INFORMÁTICA 2

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 3

Índice
Presentación 5

Red de contenidos 7

Unidad de Aprendizaje 1 9
INTRODUCCIÓN A MICROSOFT EXCEL 2013

1.1 Tema 1 : Fórmulas y Referencias en Excel 11

1.1.1 : Introducción a Excel 11

1.1.2 : Reconocimiento del Entorno 12

1.1.3 : Fórmulas y Operadores Aritméticos 13

1.1.4 : Referencias de Celdas 17

1.2 Tema 2 : Formato Condicional 23

1.2.1 : Resaltar Reglas de Celdas 26

1.2.2 : Reglas Superiores e Inferiores 37

1.2.3 : Barras de Datos 41

1.2.4 : Conjuntos de Iconos 46

1.3 Tema 3 : Gráficos 57

1.3.1 : Tipos de gráficos 60

1.3.2 : Creación de gráficos 73

Unidad de Aprendizaje 2 89

TABLAS DE DATOS

2.1 Tema 4 : Tabla de Datos y Ordenar 91

2.1.1 : Definición de una Tabla de Datos 91

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 4

2.1.2 : Elementos de una Tabla de Datos 91

2.1.3 : Ordenar Datos por uno o varios Campos 93

2.1.4 : Ordenar por Listas Personalizadas 106

2.2 Tema 5 : Filtros Automáticos y Avanzados 113

2.2.1 : Filtros Automáticos 113

2.2.2 : Filtros Avanzados 128

2.2.3 : Tabla de Criterios 128

2.3 Tema 6 : Agrupamiento de Datos 150

2.3.1 : Subtotales 150

2.4 Tema 7 : Tablas y Gráficos Dinámicos 163

2.4.1 : Tablas Dinámicas 163

2.4.2 : Gráficos Dinámicos 189

Unidad de Aprendizaje 3 197

FUNCIONES LÓGICAS Y DE BÚSQUEDAS

3.1 Tema 8 : Funciones Lógicas 199

3.1.1 : Función Lógica SI() 199

3.1.2 : Función Lógica Y() 205

3.1.3 : Función Lógica O() 210

3.2 Tema 9 : Funciones de Búsquedas 215

3.2.1 : Función BuscarV() 215

3.2.2 : Función BuscarH() 221

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 5

Presentación
Informática es un curso que pertenece a la línea de software de aplicaciones y se
dicta en las carreras de la Escuela de Gestión y Negocios. Proporciona a los alumnos
un conjunto de herramientas dentro de Microsoft Excel 2013 para la manipulación de
fórmulas y datos dentro de las hojas de cálculo con la finalidad de brindar soluciones
a situaciones cotidianas en el entorno laboral.

El presente manual, para el curso de Informática, ha sido diseñado bajo la modalidad

de Unidades de Aprendizaje, las cuales, a la vez, cuentan con un conjunto de temas
que serán desarrollados durante el presente ciclo. En cada Unidad de Aprendizaje,
hallará los logros que se deben alcanzar, el tema a tratar y los respectivos contenidos
que se desarrollarán. Por último, encontrará las actividades que debe realizar en cada
sesión las cuales le permitirán reforzar lo aprendido en la clase.

El curso es eminentemente práctico. Se inicia con el reconocimiento de la aplicación y

la revisión de algunas novedades con relación a su antecesor. Luego, se verán temas,
como por ejemplo, escribir fórmulas con referencia de celdas apropiadas; aplicar
formatos condicionales para resaltar la información; crear y editar gráficos;
herramientas para las tablas de datos; y aplicar funciones lógicas y búsqueda. Con
todo ello, podrá tomar decisiones y ubicar la información contenida en diferentes hojas
de cálculo.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 6

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 7

Red de contenidos

INFORMÁTICA

Introducción a Microsoft Excel 2013

Fórmulas y Referencias en
Excel

Formato Condicional

Gráficos

Tablas de Datos

Tabla de Datos y Ordenar

Filtros Automáticos y
Avanzados

Agrupamiento de Datos

Tablas y Gráficos Dinámicos

Funciones Lógicas y de Búsquedas

Funciones Lógicas

Funciones de Búsquedas

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 8

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 9

UNIDAD

1
INTRODUCCIÓN A MICROSOFT
EXCEL 2013
LOGRO DE LA UNIDAD DE APRENDIZAJE
Al término de la unidad, el alumno conoce el entorno de Excel, utiliza fórmulas
empleando las referencias de celdas. Aplica formato condicional, crea y edita
gráficos para representar los datos.

TEMARIO

1.1 Tema 1 : Fórmulas y Referencias en Excel

1.1.1 : Introducción a Excel

1.1.2 : Reconocimiento del Entorno

1.1.3 : Fórmulas y Operadores Aritméticos

1.1.4 : Referencias de Celdas

1.2 Tema 2 : Formato Condicional

1.2.1 : Resaltar Reglas de Celdas

1.2.2 : Reglas Superiores e Inferiores

1.2.3 : Barras de Datos

1.2.4 : Conjuntos de Íconos

1.3 Tema 3 : Gráficos

1.3.1 : Tipos de gráficos

1.3.2 : Creación de gráficos

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 10

ACTIVIDADES PROPUESTAS

 Los alumnos identifican las partes del entorno de Excel.

 Los alumnos escriben fórmulas aplicando referencias de celda.
 Los alumnos aplican formato condicional.
 Los alumnos crean y modifican los elementos de un gráfico.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 11

1. TEMA 1: FÓRMULAS Y REFERENCIAS EN EXCEL

1.1.1 Introducción a Excel

Microsoft Office Excel es una herramienta de análisis de datos, de

fácil uso y manejo, empleado en instituciones o empresas para
registrar, procesar y compartir información que permita tomar
decisiones adecuadas.

La nueva versión de Excel 2013 tiene las siguientes nuevas

características:

 Aspecto totalmente nuevo

 Diseñado para lograr resultados profesionales con velocidad, por medio de la

herramienta Análisis rápido

 Nuevas plantillas con diseños y contenidos con mayor configuración

 Podremos obtener una vista previa de los datos con formato condicional,
minigráficos o gráficos.

 Uso de Relleno rápido al momento de ingresar una columna de datos

 Trabajo de cada libro dentro de su propia ventana

 Nuevas funciones en las diferentes categorías de función

 Integración con OneDrive al momento de guardar los libros, para ello debe
contar con una cuenta de correo Microsoft.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 12

1.1.2 Reconocimiento del Entorno

En la siguiente gráfica, se muestran las partes de la ventana de Excel 2013:

En una hoja de cálculo, se admiten más de un millón de filas y más de 16,000

columnas para el registro de datos.

Filas

Las filas en Excel 2013 vienen a ser las divisiones horizontales y en total tiene
1’048,576 filas, identificadas con números.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 13

Columnas

Las columnas en Excel 2013 vienen a ser las divisiones verticales y, en total, tiene
16,384 columnas, identificadas con letras mayúsculas desde A hasta XFD.

1.1.3 Fórmulas y Operadores Aritméticos

Fórmulas

Las fórmulas realizan cálculos u otras acciones con la información de la hoja de

cálculo. Toda fórmula inicia con el signo igual (=), seguido de los datos a procesar.

Por ejemplo, la siguiente fórmula multiplica 4 por 6. Luego, resta 2 al resultado.

Después, suma el cociente de 15 y 3. La respuesta final es 27.

Una fórmula puede contener funciones, referencias de celdas, operadores y números

constantes.

Por ejemplo:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 14

Donde:

 Función PRODUCTO () es una función que devuelve la multiplicación de dos

o más números.

 Celdas A1 y A2 son referencias de celdas que devuelven el valor contenido

en ellas (5 y 7 respectivamente).

 Operador Slash (/) es el signo que representa la división de dos valores.

 Número constante, el número 2 se mantendrá fijo durante la operación.

Operadores Aritméticos

Permiten ejecutar operaciones matemáticas básicas, como suma, resta, multiplicación,

división y potenciación. Dichas operaciones, junto a los números, generan resultados
numéricos, tal como se ve en el siguiente cuadro.

Operador Aritmético Operación Ejemplo Resultado

+ (signo más) Suma =4+2 6

– (signo menos) Resta =4-2 2

* (asterisco) Multiplicación =4*2 8

/ (slash) División =4/2 2

% (signo de porcentaje) Porcentaje =20% 0.2

^ (acento circunflejo) Exponenciación =4^2 16

() (paréntesis) Agrupación =(4+2)*3 18

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 15

Otros Operadores

 Operadores de Comparación

Permiten comparar dos valores devolviendo, como resultado, un valor lógico:

VERDADERO o FALSO. Veamos los siguientes elementos:

Operador de Comparación Operación Ejemplo Resultado

= (signo igual) Igual a =4=2 FALSO

> (signo mayor que) Mayor que =4>2 VERDADERO

< (signo menor que) Menor que =4<2 FALSO

>= (signo mayor o igual que) Mayor o igual que =4>=2 VERDADERO

<= (signo menor o igual que) Menor o igual que =4<=2 FALSO

<> (signo distinto de) Diferente o distinto de =4<>2 VERDADERO

 Operador de Concatenación de Texto

Permite unir o concatenar una o varias cadenas de texto con el fin de generar un solo
elemento de texto. Considere que todo valor de texto inicia y termina con las comillas
dobles (“valor de texto”) tal como se ve en el siguiente ejemplo:

Operador de Texto Operación Ejemplo Resultado

Une dos o más

& (Ampersand o Y Comercial) =“Arriba ” & “Perú” Arriba Perú
valores

Precedencia de los operadores en las fórmulas

Las fórmulas calculan los valores en un orden específico. Las fórmulas de Excel inician
con un signo igual (=) indicando al programa que los caracteres siguientes constituyen
una fórmula compuesta por los operandos y los diferentes operadores de cálculo.
Excel calcula la fórmula de izquierda a derecha, según el orden específico de cada
operador de la fórmula.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 16

Si una fórmula contiene operadores con la misma precedencia, por ejemplo, un

operador de multiplicación y otro de división, Excel evaluará los operadores de
izquierda a derecha. Al combinar varios operadores en una única fórmula, Excel
ejecutará las operaciones en el orden que se indica en la siguiente tabla:

Operador Descripción

() Agrupación

- Número negativo (como en –5)

% Porcentaje

^ Exponenciación

*y/ Multiplicación y División

+y- Suma y Resta

& Concatenación

= < > <= >= <> Comparación

Por ejemplo, los paréntesis que rodean la primera parte de la fórmula indican a Excel
que, primero, calcule A1*5 y después, divida el resultado entre la resta de los valores
de las celdas B1 y C1. Finalmente, que multiplique por 2.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 17

1.1.4 Referencias de Celdas

Una referencia identifica una celda o un rango de celdas en una hoja de cálculo e
indica a Microsoft Excel en qué celdas debe buscar los valores o los datos que desea
utilizar en una fórmula.

Las referencias permiten utilizar datos de distintas partes de una hoja de cálculo en
una fórmula o utilizar el valor de una celda en varias fórmulas. También, puede
hacerse referencia a las celdas de otras hojas en el mismo libro y a otros libros.

Por ejemplo:

Donde…

 A1, B1, C1 son referencias de las celdas de la hoja activa y devuelven los
valores 10, 15 y 20, respectivamente.

 Hoja2!C3 es la referencia de celda que devuelve el valor ubicado en la celda

C3 de la Hoja2 del mismo libro.

Las referencias de celdas se clasifican en:

a) Referencias Relativas

Una referencia de celda relativa, en una fórmula, como B1, se basa en la posición
relativa de la celda que contiene la fórmula y de la celda a la que hace referencia. Si
cambia la posición de la celda que contiene la fórmula, se cambia la referencia. Si se
copia la fórmula en filas o columnas, la referencia se ajusta automáticamente.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 18

De forma predeterminada, al elegir una celda que formará parte de una fórmula, estas
tendrán referencias relativas.

Por ejemplo, si copia una fórmula con referencia relativa de la celda C1 a la celda C2,
se ajusta de manera automática la primera fórmula hacia la siguiente celda hacia abajo
(por filas). Caso similar sucede al copiar la fórmula con referencia relativa de la celda
A3 a la celda B3 hacia la derecha (por columnas).

b) Referencias Absolutas

Una referencia de celda absoluta en una fórmula, como $B$1, siempre hace referencia
a una celda en una ubicación específica. Si cambia la posición de la celda que
contiene la fórmula, la referencia absoluta permanece constante. Si se copia la fórmula
en filas o columnas, la referencia absoluta no se ajusta.

De forma predeterminada, al elegir una celda que formará parte de una fórmula, estas
tendrán referencias relativas y será necesario cambiarlas a referencias absolutas,
usando el símbolo del Dólar ($) antes de la letra de la columna y antes del número de
la fila. También, se consigue presionando la tecla F4.

Por ejemplo, si copia una referencia absoluta de la celda B4 a la celda B5, permanece
constante la referencia $B$1.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 19

c) Referencias Mixtas

Una referencia de celda mixta, en una fórmula, presenta los siguientes casos:

 Una columna relativa y una fila absoluta. Por ejemplo, B$2

 Una columna absoluta y una fila relativa. Por ejemplo, $B2

Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia

relativa y la referencia absoluta permanece constante. Si se copia la fórmula en filas o
columnas, la referencia relativa se ajusta, de manera automática y la referencia
absoluta no se ajusta. Para cambiar la referencia se usa el símbolo del Dólar ($) antes
de la letra de la columna o antes del número de la fila; también, se consigue
presionando la tecla F4.

Por ejemplo, si se copia una referencia mixta de la celda B5 a la celda B6 y, luego, a

las celdas C5 y C6, las referencias sin $ se ajustan y las que tienen $ se mantienen
constante, dependiendo del arrastre y sentido al copiar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 20

Actividades

 Escriba las fórmulas con las referencias de celdas necesarias para completar el
siguiente cuadro, considere:

1. IGV, multiplique 18% por el Monto.

2. Total, sume Monto e IGV.

3. Total General, sume todos los valores del Total.

 Escriba las fórmulas con las referencias de celdas necesarias para completar el
siguiente cuadro, considere:

1. Interés, multiplique Capital por el Interés Fijo (celda C3).

2. Monto Soles, sume Capital e Interés.

3. Monto Dólares, divida Monto Soles con el Cambio (C4).

4. Total General, sume los valores de cada columna Capital, Interés,

Monto Soles y Monto Dólares

5. % Capital, divida Capital con el Total General del Capital.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 21

 Escriba una sola fórmula con referencia de celda mixta para completar el
siguiente cuadro, considere:

Ingresos 2014 aumentado por el % Incremento por Mes

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 22

Resumen
 La nueva versión de Excel 2013, ofrece nuevas herramientas que permiten
realizar trabajos de forma sencilla y rápida.

 Las fórmulas son expresiones que se utilizan para realizar cálculos o procesar
los valores contenidos en las celdas de una hoja del libro de trabajo.

 Las fórmulas están conformadas por valores numéricos, llamados operandos;

por operadores; y, con frecuencia, por funciones. Para introducir una fórmula
en una celda, se debe escribir como primer carácter el signo igual (=).

 De forma predeterminada, Excel utiliza referencias de celdas relativas, es decir,

la referencia al ser copiada en otras celdas, se ajustará automáticamente. Para
cambiar la referencia de celdas en una fórmula, se utiliza la tecla de función F4.

Puede revisar los siguientes enlaces para ampliar los conceptos vistos en este tema:

1. https://www.youtube.com/watch?v=LzaejKhMmKM
2. https://www.youtube.com/watch?v=068sK7bdKLU
3. https://www.youtube.com/watch?v=n9DA9eI7gaQ

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 23

1.2 TEMA 2: FORMATO CONDICIONAL

El formato condicional aplicado a un grupo de celdas grafica la tendencia de los datos,

revisar su estado con relación al resto, encontrar y resaltar valores máximos y/o
mínimos.

Al emplear formato condicional, permite fácilmente realizar las siguientes tareas:

 Resaltar los valores de tipo número dentro de un grupo de celdas que sean
superiores o inferiores a una constante específica.

 Resaltar los valores de tipo texto dentro de un grupo de celdas que estén
contenidos en un texto específico.

 Resaltar los valores de tipo fecha dentro de un grupo de celdas que cumplan
una condición, por ejemplo, que sean de la semana pasada, del mes pasado,
entre otros.

 Resaltar los “n” primeros o últimos valores dentro de un grupo de celdas.

 Resaltar los valores por encima o por debajo del promedio en un grupo de
celdas.

 Resaltar los valores máximos o mínimos en un grupo de celdas.

Un formato condicional cambia el aspecto de un rango de celdas en función de

condiciones o criterios. Si la condición es verdadera, en el rango de celdas se aplicará
el formato establecido. Si la condición no se cumple, el rango de celdas no tendrá el
formato establecido.

Por ejemplo, resalte las recaudaciones superiores a 10,000 usando Análisis rápido.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 24

Procedimiento:

 Seleccione los valores de la Recaudación.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 25

 Haga clic en el botón de Análisis rápido ubicado en la esquina inferior derecha

o presione CTRL + Q. Luego, en la pestaña Formato, elija Mayores…

 En el cuadro, escriba el número 10000 y verá cómo los valores que cumplen la
condición son resaltados con los colores predeterminados de fuente y relleno.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 26

Los tipos de regla que pueden considerar al establecer formatos condicionales son los
siguientes:

Tipo de Regla Descripción

Identifica números, fechas y texto específicos

Valor de celda
en un grupo de datos.

Identifica un número o texto modificado de

Valor de celda (con fórmula)
forma dinámica en un grupo de datos.

Valores por encima o por debajo del Identifica los valores por encima o por debajo
promedio del promedio en un grupo de datos.

Determina cuales son los “n” mejores valores

Valores superiores e inferiores
en un grupo de datos.

Resalta las filas únicas o duplicadas en una

Único o duplicado
lista de datos.

Ver la tendencia de los números en un grupo

Conjunto de íconos
de celdas.

Compara por medio de diferentes criterios los

Conjunto de íconos
números en un grupo de celdas.

Rellena con un color dependiendo del valor en

Barras de datos
la celda.

Rellena con diferentes colores cada celda

Escala de 3 colores
dependiendo del valor en el grupo de datos.

Compara los datos en una celda fuera del

Basado en fórmulas
rango de celdas con formato condicional.

Resalta una fila completa en la que deben

Basado en fórmulas
cumplirse una condición.

Además, podemos aplicar los siguientes formatos condicionales con la opción de:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 27

1.2.1 Resaltar Reglas de Celdas

Al elegir esta opción, encontramos las alternativas: Es mayor que, Es menor que,
Entre, Es igual a, Texto que contiene, Una fecha y Duplicar valores, los cuales
permiten comparar los valores numéricos, de texto o fechas con una determinada
condición.

Por ejemplo, dentro de la columna de Ventas, se desea resaltar aquellos valores que
sean inferiores a 2500.

Además, se cuenta, al final, con la opción Más reglas… para indicar otra condición y
personalizar el formato a aplicar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 28

Ejemplo 1:

Resalte las Cantidades Vendidas a partir de 17.

Procedimiento:

 Seleccione los valores de la Cantidad Vendida.

 Seleccione la ficha Inicio y, en el grupo Estilos, elija Formato condicional,

luego, Resaltar reglas de celdas y, después, Más reglas…

 En el cuadro, cambie la condición a mayor o igual que y escriba el valor 17.

Luego, presione el botón Formato… y aplique el formato de su preferencia
(Negrita, color de fuente, color de relleno).

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 29

 Al presionar el botón Aceptar de todos los cuadros, se obtendrá el siguiente

resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 30

Ejemplo 2:

Resalte los productos que contenga Folder.

Procedimiento:

 Seleccione los valores del Producto.

 Seleccione la ficha Inicio y, en el grupo Estilos, elija Formato condicional;

luego, Resaltar reglas de celdas y; después, Texto que contiene…

 En el cuadro, escriba el texto Folder y; luego, cambie por un formato

personalizado de su preferencia (Cursiva, color de fuente, color de relleno).

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 31

 Al presionar el botón Aceptar de todos los cuadros, se obtendrá el siguiente

resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 32

Ejemplo 3:

Resalte las fechas de venta correspondientes al mes de Abril del 2014.

Procedimiento:

 Seleccione los valores de la Fecha de Venta.

 Seleccione la ficha Inicio y, en el grupo Estilos, elija Formato condicional;

luego, Resaltar reglas de celdas y; después, Entre…

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 33

Al usar la opción Un fecha…, solo se tendrá disponible opciones que se

podrán usar con relación a la fecha actual, como Ayer, Hoy, Esta
semana, entre otros.

 En el cuadro, escriba el rango de fechas 01/04/2014 y 30/04/2014. Luego,

cambie por un formato personalizado de su preferencia (Negrita Cursiva, color
de fuente, color de relleno).

 Al presionar el botón Aceptar de todos los cuadros, se obtendrá el siguiente

resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 34

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 35

Ejemplo 4:

Resalte los códigos de vendedor que sean duplicados.

Procedimiento:

 Seleccione los valores del Código.

 Seleccione la ficha Inicio y, en el grupo Estilos, elija Formato condicional;

luego, Resaltar reglas de celdas y; después, Duplicar valores…

 En el cuadro, puede elegir Duplicar o Único. Para este caso, se elige Duplicar.
Luego, cambie por un formato personalizado de su preferencia (Cursiva, color
de fuente, color de relleno).

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 36

 Al presionar el botón Aceptar de todos los cuadros, se obtendrá el siguiente

resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 37

1.2.2 Reglas Superiores e Inferiores

Al elegir esta opción, encontramos las

alternativas: 10 superiores, 10% de
valores superiores, 10 inferiores, 10%
de valores inferiores, Por encima del
promedio y Por debajo del promedio,
los cuales permiten resaltar los
valores más altos o más bajos en un
rango de celdas según un valor
específico que se indique.

Por ejemplo, dentro de la columna de

Ventas, se desea resaltar los 4
valores más altos del mes.

Además, se cuenta, al final, con la opción Más reglas… para indicar otra condición y
personalizar el formato a aplicar.

Ejemplo 1:

Resalte las tres recaudaciones más bajas.

Procedimiento:

 Seleccione los valores de la Recaudación.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 38

 Seleccione la ficha Inicio y, en el grupo Estilos, elija Formato condicional;

luego, Reglas superiores e inferiores y; después, 10 inferiores…

 En el cuadro, escriba 3. Luego, cambie por un formato personalizado de su

preferencia (Negrita, color de fuente, color de relleno).

 Al presionar el botón Aceptar de todos los cuadros, se obtendrá el siguiente

resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 39

Ejemplo 2:

Resalte las ventas por encima del promedio.

Procedimiento:

 Seleccione los valores de las Ventas.

 Seleccione la ficha Inicio y, en el grupo Estilos, elija Formato condicional;

luego, Reglas superiores e inferiores y; después, Por encima del promedio…

 En el cuadro, cambie por un formato personalizado de su preferencia (Cursiva,

color de fuente, color de relleno).

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 40

 Al presionar el botón Aceptar de todos los cuadros, se obtendrá el siguiente

resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 41

1.2.3 Barras de Datos

Al elegir esta opción, podremos

visualizar el valor de una celda con
relación a las demás por medio de la
longitud de la barra de datos que se
aplica en la celda. Una barra más
grande representa un valor más alto y
una barra más corta representa un valor
más bajo. Las barras de datos son útiles
para encontrar números más altos y
más bajos, especialmente, con grandes
cantidades de datos.

Por ejemplo, dentro de la columna de

Ventas, se desea representar los
valores con barra de datos y observar
fácilmente los valores mayores y
menores.

Además, se cuenta al final con la opción Más reglas… para personalizar el formato a
aplicar.

Ejemplo 1:

Resalte con barras de datos la Cantidad vendida.

Procedimiento:

 Seleccione los valores de la Cantidad vendida.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 42

 Seleccione la ficha Inicio y, en el grupo Estilos, elija Formato condicional;

luego, Barras de datos y; después, el color de relleno de su agrado.

 Luego, obtendrá el siguiente resultado.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 43

Ejemplo 2:

Resalte con barras de datos personalizada las Ventas.

Procedimiento:

 Seleccione los valores de las Ventas.

 Seleccione la ficha Inicio y, en el grupo Estilos, elija Formato condicional;

luego, Barras de datos y; después, Más reglas…

 En el cuadro, cambie la apariencia de la barra, como Relleno (degradado),

Color (por el de su agrado), Borde (sólido) y Color de borde (por el de su
agrado).

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 44

 Al presionar el botón Aceptar, se obtendrá el siguiente resultado.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 45

Opcionalmente, podría mostrar sólo la barra y ocultar los valores

numéricos; para ello, en el cuadro, marque la casilla Mostrar solo la
barra.

Se obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 46

1.2.4 Conjuntos de Iconos

Al elegir esta opción, podremos

visualizar un conjunto de íconos para
clasificar los datos en tres, cuatro o
cinco categorías que se definen en
rangos numéricos o porcentuales que
serán representados por un ícono.

Por ejemplo, se desea representar las

Ventas con conjunto de íconos de 3
flechas: la flecha verde hacia arriba
representa valores más altos, la
flecha hacia un lado amarilla
representa valores medios y la flecha
roja hacia abajo representa valores
más bajos.

Además, se cuenta, al final, con la opción Más reglas… para personalizar el formato a
aplicar.

Ejemplo 1:

Resalte con conjuntos de íconos la Recaudación del Mes de Enero.

Procedimiento:

 Seleccione los valores de la Recaudación del Mes de Enero.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 47

 Seleccione la ficha Inicio y, en el grupo Estilos, elija Formato condicional;

luego, Conjuntos de íconos y; después, la opción 3 signos.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 48

 Luego, obtendrá el siguiente resultado.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 49

Ejemplo 2:

Resalte con conjuntos de íconos personalizados (usando porcentajes) la Recaudación

del Mes de Febrero, considere:

 El ícono Cuatro cuadros rellenos ( ), cuando el valor es mayor o igual a 80%.

 El ícono Estrella de plata ( ), cuando el valor es menor a 80% y mayor o igual

a 55%.

 El ícono Círculo rojo ( ), cuando el valor es menor a 33.

Procedimiento:

 Seleccione los valores de la Recaudación del Mes de Febrero.

 Seleccione la ficha Inicio y, en el grupo Estilos, elija Formato condicional;

luego, Conjuntos de íconos y; después, Más reglas…

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 50

 En el cuadro, cambie los íconos y los valores según lo señalado.

 Luego, obtendrá el siguiente resultado.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 51

Ejemplo 3:

Resalte con conjuntos de íconos personalizados (usando números) la Recaudación del

Mes de Marzo, considere:

 El ícono Círculo verde ( ), cuando el valor es mayor a 15000.

 El ícono Semáforo ámbar ( ), cuando el valor es menor o igual a 15000 y

mayor a 12000.

 El icono Diamante rojo ( ), cuando el valor es menor o igual a 12000.

Procedimiento:

 Seleccione los valores de la Recaudación del Mes de Marzo.

 Seleccione la ficha Inicio y, en el grupo Estilos, elija Formato condicional;

luego, Conjuntos de iconos y; después, Más reglas…

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 52

 En el cuadro, cambie los íconos y los valores según lo señalado.

 Luego, obtendrá el siguiente resultado.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 53

Puede modificar y/o eliminar todas las reglas creadas para un rango de
celdas o en la hoja actual. Para ello, seleccione la ficha Inicio y, en el
grupo Estilos elija, Formato condicional; luego, Administrar reglas…

En el cuadro, elija las reglas a mostrar; luego, seleccione la regla

y presione el botón Editar regla o Eliminar regla. Por ejemplo, en
Mostrar reglas de formato para elija Esta hoja; luego, verá las
tres reglas creadas en los ejercicios anteriores.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 54

Uso de Fórmulas

Puede incluir fórmulas para determinar un formato para un grupo de

celdas.

Ejemplo 4:

Resalte toda la fila de datos si la Recaudación mensual promedio es

inferior a 11,000.

Procedimiento:

 Seleccione todos los valores de la tabla de datos.

 Seleccione la ficha Inicio y, en el grupo Estilos, elija

Formato condicional; luego, Nueva regla…

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 55

 En el cuadro, elija Utilice una fórmula que determine las

celdas para aplicar formato. Luego, escriba la fórmula
=$F5<11000 y aplique el formato de su preferencia
(Negrita, color de fuente, color de relleno).

 Luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 56

Resumen
 El formato condicional permite resaltar un grupo de celdas de acuerdo a su
valor.

 Puede aplicarse a valores de tipo texto, números y fechas.

 Tenemos las siguientes opciones de formato condicional: Resaltar reglas de

celdas, Reglas superiores e inferiores, Barras de datos, Conjuntos de Íconos y
el Uso de fórmulas.

 Cada formato condicional es una regla, la cual puede ser editada o eliminada
mediante el cuadro Administrador de reglas de formato condicionales.

Puede revisar los siguientes enlaces para ampliar los conceptos vistos en este tema:

1. https://www.youtube.com/watch?v=Ns0vIc9fenk
2. https://www.youtube.com/watch?v=_3gsH1Vt-6o
3. https://www.youtube.com/watch?v=AsUHNAvlRHo

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 57

1.3 TEMA 3: GRÁFICOS

Los gráficos representan la información contenida

en un cuadro de datos en imágenes que facilitan
a los usuarios analizar, comparar y revisar las
tendencias de los datos. En Excel, los gráficos
pueden ser parte de una hoja de cálculo o podría
ocupar toda una hoja de gráfico.

En la nueva versión de Excel 2013,

es mucho más fácil crear y editar los
gráficos para brindarles un aspecto
profesional. Sólo basta con
seleccionar los datos y, en la opción
de Análisis rápido, se podrá elegir el
gráfico más adecuado para la
información elegida.

Además, se cuenta con el grupo

Gráficos de la ficha Insertar para
elaborar los gráficos más comunes y conocidos por la mayoría de usuarios.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 58

Elementos de los gráficos

Un gráfico contiene varios elementos, algunos de ellos se presentan de forma

predeterminada y otros se pueden agregar o quitar según las necesidades. Para
cambiar la presentación de los elementos del gráfico, puede moverlos a otras
ubicaciones dentro del gráfico o cambiar su tamaño o su formato.

 El Área del gráfico es todo el gráfico junto con todos sus elementos.

 Los Títulos son los textos descriptivos que se alinean con un eje (vertical u
horizontal) o se centra en la parte superior de un gráfico.

 Área de trazado del gráfico

 Los puntos de datos son los valores individuales trazados en un gráfico y

representados con barras, columnas, líneas, sectores, puntos y otras formas
denominadas Marcadores de datos.

 Los Ejes son las líneas que rodean el área de trazado de un gráfico utilizado
como marco de referencia de medida.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 59

 La Etiqueta de datos es la que proporciona información adicional acerca de un

marcador de datos que se puede usar para identificar los detalles de un punto
de datos.

 La Leyenda es el cuadro que identifica los diseños o colores asignados a las

series de datos o categorías de un gráfico.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 60

1.3.1 Tipos de Gráficos

Excel 2013 admite muchos tipos de gráficos. A continuación, se muestra una galería
de ejemplos de gráficos para que pueda determinar qué tipos de gráficos son los
mejores para sus datos.

1.3.1.1 Columnas

Se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo
en un gráfico de columnas. Este tipo de gráfico es útil para mostrar cambios de datos
en un período de tiempo o para ilustrar comparaciones entre elementos.

En los gráficos de columnas, las categorías se organizan en el eje horizontal y los

valores, en el eje vertical. Los gráficos de columnas tienen los siguientes subtipos de
gráfico:

Columna agrupada y Columna agrupada 3D

Estos gráficos comparan los valores entre categorías. Un

gráfico de columna agrupada muestra valores en
rectángulos verticales en 2D. Un gráfico de columnas
agrupadas 3D, de forma simple, muestra los datos con
perspectiva 3D.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 61

Columna apilada y Columna 3D apilada

Estos gráficos comparan las partes del total, además

muestran cómo las partes del total cambian con el
transcurso del tiempo. Puede utilizar un gráfico de
columnas apiladas cuando tiene varias series de datos y
desea destacar el total.

Columna 100% apilada y Columna 3D 100% apilada

Estos gráficos comparan el porcentaje con que contribuye cada valor a un total de
categorías. Puede utilizar estos tipos de gráficos cuando tenga tres o más series de
datos y desee destacar las contribuciones al conjunto, especialmente, si el total es el
mismo para cada categoría.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 62

Columnas 3D

Estos gráficos comparan los valores entre algunas categorías. Utilizan

tres ejes que se pueden modificar (un eje horizontal, un eje vertical y
un eje de profundidad) y comparar puntos de datos. Muestra algunas
columnas en frente de otras.

Cilindro, Cono y Pirámide

Los gráficos de cilindros, conos y pirámides están disponibles en los mismos tipos de
gráficos agrupados, apilados, 100% apilados y en 3D proporcionados para gráficos de
columnas rectangulares, muestran y comparan datos de la misma manera. La única
diferencia es que estos tipos de gráficos muestran formas de cilindro, cono y pirámide
en lugar de rectángulos.

1.3.1.2 Barra

Los gráficos de barras muestran comparaciones entre elementos individuales.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 63

Utilice un gráfico de barras cuando:

 Las etiquetas de eje son largas.

 Los valores que se muestran son duraciones.

Los gráficos de barras tienen los siguientes subtipos de gráfico:

 Barra agrupada y Barra 3D agrupada.

 Barra apilada y Barra 3D apilada.

 Barra 100% apilada y Barra 3D 100% apilada.

 Cilindro, cono y pirámide horizontal.

Considere lo mencionado para los gráficos de columnas.

1.3.1.3 Circular

En un gráfico circular, se pueden representar datos contenidos en una columna o una

fila de una hoja de cálculo. Los gráficos circulares sólo tienen una serie de datos.

Utilice un gráfico circular cuando:

 Solo tenga una serie de datos que desee trazar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 64

 Ninguno de los valores que desea trazar es negativo.

 Casi ninguno de los valores que desea trazar son valores cero.

 No tiene más de siete categorías.

 Las categorías representan partes de todo el gráfico circular.

1.3.1.4 Línea

Los gráficos de línea pueden mostrar datos continuos en el tiempo, establecidos frente
a una escala común y; por tanto, son ideales para mostrar tendencias en datos a
intervalos iguales.

Utilice un gráfico de líneas cuando:

 Las etiquetas de categorías son textos y representan valores que están

separados entre sí, por ejemplo, meses, trimestres o ejercicios fiscales. Este
tipo de gráfico es válido, especialmente, si hay más de una serie. Si solo hay
una, se recomienda utilizar un gráfico de categorías.

 Utilice, también, un gráfico de líneas si tiene etiquetas numéricas con valores

separados entre sí de manera uniforme, especialmente años.

 Si tiene más de diez etiquetas numéricas, utilice, en su lugar, un gráfico de

dispersión.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 65

1.3.1.5 Dispersión XY o de burbujas

Los gráficos de dispersión o de

burbujas muestran la relación entre
los valores numéricos de varias series
de datos o trazan dos grupos de
números como una única serie de
coordenadas XY.

Este gráfico muestra intervalos

desiguales o agrupaciones de datos y
se usa para mostrar y comparar valores numéricos, por ejemplo datos científicos,
estadísticos y de ingeniería.

Utilice un gráfico de dispersión o de burbujas cuando:

 Desea cambiar la escala del eje horizontal.

 Desea convertir dicho eje en una escala logarítmica.

 Los espacios entre los valores del eje horizontal no son uniformes.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 66

 Hay muchos puntos de datos en el eje horizontal.

 Desea mostrar con eficacia datos de hoja de cálculo que incluyen pares o
conjuntos de valores agrupados y ajustar las escalas independientes de un
gráfico de dispersión para revelar más información acerca de los valores
agrupados.

 Desea mostrar similitudes entre grandes conjuntos de datos, en lugar de

diferencias entre puntos de datos.

 Desea comparar muchos puntos de datos, sin tener en cuenta el tiempo, con
una mayor cantidad de datos que se incluya en un gráfico de dispersión. Así,
obtendrá mejores resultados en las comparaciones que realice.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 67

1.3.1.6 Área

Los gráficos de áreas son similares a los gráficos de líneas con la diferencia de que la
zona por debajo de ella se rellena de un color. Los gráficos de áreas destacan la
magnitud del cambio en el tiempo y se pueden utilizar para llamar la atención hacia el
valor total en una tendencia.

Utilice un gráfico de áreas cuando:

 Desee representar y comparar la magnitud del cambio con el transcurso del

tiempo.

 Desee mostrar la tendencia en el tiempo.

1.3.1.7 Radial

El gráfico radial muestra los valores relativos a un punto central. También, es conocido
como gráfico de telaraña o de estrella, debido a su aspecto al representar los datos.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 68

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 69

1.3.1.8 Superficie

El gráfico de superficie es útil cuando se busca combinaciones óptimas entre dos o

más conjuntos de datos. Como en un mapa topográfico, los colores y las tramas
indican áreas que están en el mismo rango de valores.

Puede utilizar un gráfico de superficie cuando ambas categorías y series de datos

sean valores numéricos.

1.3.1.9 Anillo

En un gráfico de anillos, se pueden representar datos organizados únicamente en

columnas o en filas de una hoja de cálculo. Al igual que un gráfico circular, un gráfico
de anillos muestra la relación de las partes con un todo, pero puede contener más de
una serie de datos.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 70

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 71

1.3.1.10 Cotizaciones

Se pueden trazar datos que se organizan en columnas o filas en un orden específico

en una hoja de cálculo en un gráfico de cotizaciones. Este gráfico se utiliza con mayor
frecuencia para mostrar la fluctuación de los precios de las acciones. Por ejemplo, se
tiene el siguiente cuadro de cotizaciones:

Puede insertar el siguiente gráfico de cotizaciones.

Observaciones:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 72

Datos Representación Explicación

En la fecha 10/05/2014, se tiene los La línea central representa los valores

siguientes datos: máximos (línea hacia arriba) y mínimos
(línea hacia abajo).

Se usa un cuadro ascendente (color

blanco) que indica que el valor del
Cierre es mayor al de Apertura.

En la fecha 01/05/2014, se tiene los La línea central representa los valores

siguientes datos: máximos (línea hacia arriba) y mínimos
(línea hacia abajo).

Se usa un cuadro descendente (color

negro) que indica que el valor del
Cierre es menor al de Apertura.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 73

1.3.2 Creación de Gráficos

1.3.2.1 Crear un Gráfico

Procedimiento:

 Para crear un gráfico en Excel, es necesario tener un cuadro con la información

a representar. Por ejemplo:

 Luego, se deben seleccionar los datos que serán las categorías y series del
gráfico. Por ejemplo:

También, puede seleccionar celdas no continuas presionando la tecla

CTRL junto al grupo de celdas a representar. Por ejemplo:

Aquí se representa todas las Áreas junto a los valores del mes de
Mayo.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 74

Aquí, se representa las Áreas de RRHH y Atención al cliente junto a

los valores de los meses de Abril y Junio.

 En la ficha Insertar, grupo Gráficos, elija el tipo de gráfico a usar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 75

 Luego, elija el subtipo del gráfico. Por ejemplo, Tipo Columnas y Subtitpo
Columna Agrupada 3D.

 Se obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 76

1.3.2.2 Editar un Gráfico

Cambio Rápido

En Excel 2013, es posible cambiar en forma rápida los elementos que se muestran en
el gráfico por medio de los botones que aparecen en el extremo superior a la
derecha:

Añada las Etiquetas de datos:

Cambie al Estilo 3:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 77

Oculte el área RRHH y el mes Mayo:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 78

Diseño del Gráfico

Al seleccionar el gráfico, tiene diferentes opciones en la ficha Diseño.

Entre las opciones que se pueden elegir, se encuentran las siguientes :

Oculte la leyenda:

Cambie al Diseño 7:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 79

Cambie al Estilo11:

Intercambie las categorías y series:

Cambie el origen de los datos del gráfico:

Elija otro tipo y subtipo de gráfico:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 80

Elija otra ubicación para el gráfico actual:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 81

Formato del Gráfico

Al seleccionar el gráfico, tiene diferentes opciones en la ficha Formato.

Entre las opciones que se puede elegir, encontramos:

Cambie la configuración del elemento elegido

(Selección Actual) desde el panel que aparece
al lado derecho:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 82

Cambie el Estilo de forma: Relleno, Contorno

y Efectos de forma del elemento elegido.
También, puede elegir más opciones desde el
panel que aparece al lado derecho:

Cambie los Estilos de WordArt: Relleno,

Contorno y Efectos de texto del elemento
elegido. También, puede elegir más opciones
desde el panel que aparece al lado derecho:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 83

Actividades

 En una hoja nueva, escriba la siguiente tabla de datos:

 Seleccione los datos convenientes y cree los siguientes gráficos:

1. Gráfico de Columnas.

2. Gráfico de Áreas.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 84

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 85

3. Gráfico Circular.

 En una hoja nueva, escriba la siguiente tabla de datos:

 Seleccione los datos convenientes y cree los siguientes gráficos:

1. Gráfico de Líneas.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 86

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 87

2. Gráfico de Barra.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 88

Resumen
 Presentar los datos en forma gráfica nos permite compararlos, observar sus
cambios, verlos en forma conjunta y sintetizada.

 Los diferentes tipos y subtipos de gráficos permiten presentar,

convenientemente, los datos contenidos en un cuadro y los más comunes son
Columnas, Barras, Circular, Líneas y Áreas.

 Los gráficos se pueden editar para crear una mejor presentación y apariencia.

Puede revisar los siguientes enlaces para ampliar los conceptos vistos en este tema:

1. https://www.youtube.com/watch?v=C-KRfaOuv3g
2. https://www.youtube.com/watch?v=yvrPkzglvp0
3. https://www.youtube.com/watch?v=zSyXG0BBhWA

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 89

UNIDAD

2
TABLAS DE DATOS
LOGRO DE LA UNIDAD DE APRENDIZAJE
Al término de la unidad, el alumno conoce los elementos de una Tabla de Datos,
ordena y filtra la información, agrupa los datos por medio de subtotales y tablas
dinámicas aplicando diferentes criterios.

TEMARIO

2.1 Tema 4 : Tabla de Datos y Ordenar

2.1.1 : Definición de una Tabla de Datos

2.1.2 : Elementos de una Tabla de Datos

2.1.3 : Ordenar Datos por uno o varios Campos

2.1.4 : Ordenar por Listas Personalizadas

2.2 Tema 5 : Filtros Automáticos y Avanzados

2.2.1 : Filtros Automáticos

2.2.2 : Filtros Avanzados

2.2.3 : Tabla de Criterios

2.3 Tema 6 : Agrupamiento de Datos

2.3.1 : Subtotales

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 90

2.4 Tema 7 : Tablas y Gráficos Dinámicos

2.4.1 : Tablas Dinámicas

2.4.2 : Gráficos Dinámicos

ACTIVIDADES PROPUESTAS

 Los alumnos identifican los elementos de una tabla de datos.

 Los alumnos ordenan una tabla de datos por diferentes campos.
 Los alumnos aplican filtros en una tabla de datos.
 Los alumnos crean subtotales de diferentes niveles.
 Los alumnos crean tablas dinámicas aplicando diferentes criterios en su
estructura.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 91

2.1 TEMA 4: TABLA DE DATOS Y ORDENAR

2.1.1 Definición de una Tabla de Datos

En Excel, una tabla de datos es una colección de datos organizados y que guardan
relación entre sí. Por ejemplo, una tabla de datos con la información del personal de
una empresa, una tabla de datos con la información de los alumnos de una institución
educativa, una tabla con las ventas realizadas en una compañía, etc.

Con la información contenida en una tabla de datos, podemos realizar operaciones

comunes, como ordenar, filtrar, crear subtotales o resumir con tablas dinámicas.

2.1.2 Elementos de una Tabla de Datos

Los elementos de una tabla de datos son los siguientes:

2.1.2.1 Campos

Los campos vienen a ser las columnas que forman la tabla. Cada columna contiene
datos de un mismo tipo, como Textos, Números, Alfanuméricos o Calculados.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 92

2.1.2.2 Registros

Los registros vienen a ser las filas de datos que forman la tabla. Cada fila contiene
datos de diferentes tipos.

La intersección entre un campo y un registro se denomina Dato.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 93

2.1.3 Ordenar Datos por Uno o Varios Campos

En Excel, una herramienta esencial para organizar los datos es el

orden que se puede aplicar por uno o varios campos. Por ejemplo,
ordenar por Apellidos una lista de alumnos o clientes, ordenar por
Ventas una lista de productos vendidos, ordenar por íconos o por
colores de celda una lista de pedidos. En cualquiera de los casos
anteriores, el ordenamiento permite ubicar fácilmente un registro o
grupos de registros y presentar la información de manera conveniente.

Puede ordenar la información:

 Texto, de forma alfabética de la A a la Z o de la Z a la A.

 Números, de menor a mayor o de mayor a menor.

 Fechas y horas, de más antiguas a más recientes o de más recientes a más

antiguas.

 Orden personalizado, mediante una lista creada previamente.

 Color de celda, mediante un color de relleno aplicado a la celda.

 Color de fuente, mediante un color de fuente aplicado al contenido de la celda.

 Ícono de celda, que fue aplicado por medio de formato condicional.

2.1.3.1 Ordenar por un Campo

Es posible ordenar una tabla de datos por solo un campo a la vez.

Ejemplo 1:

Con la siguiente tabla de datos ordene por el campo Distrito de forma ascendente:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 94

Procedimiento

 Ubique la celda activa en el nombre del campo Distrito o en un dato del mismo
campo.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 95

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando Ordenar de A a

Z.

 Luego obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 96

Ejemplo 2:

Con la tabla de datos anterior ordene por el campo Ventas de forma descendente.

Procedimiento

 Ubique la celda activa en el nombre del campo Ventas o en un dato del mismo
campo.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando Ordenar de Z a

A.

 Luego obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 97

2.1.3.2 Ordenar por Varios Campos

Es posible ordenar una tabla de datos por varios campos a la vez, definiendo así
niveles de ordenamiento para generar grupos con un mismo valor.

Ejemplo 1: Ordenar por dos campos

Con la siguiente tabla de datos, ordene por el campo Área de forma ascendente y,
luego, por el campo Sueldo de forma descendente:

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando Ordenar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 98

 En el cuadro Ordenar, defina los niveles de ordenamiento; para ello, en la

primera fila, elija el campo Área. Luego, haga clic en el botón Agregar nivel y
elija el campo Sueldo cambiando el Criterio de ordenación a De mayor a
menor.

Al elegir el campo Área, que es de tipo Texto, el orden por defecto en el

Criterio de ordenación es A a Z, es decir, de forma ascendente. De
manera similar sucedería con los tipos Número y Fecha.

 Haga clic en el botón Aceptar y luego obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 99

Ejemplo 2: Ordenar por tres campos

Con la tabla de datos anterior, ordene por el campo Género de forma descendente;
luego, por el campo Cargo de forma ascendente y; después, por el campo Fecha de
Nacimiento de forma descendente.

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando Ordenar,

elimine los niveles del ejemplo anterior con el botón Eliminar nivel.

 En el cuadro Ordenar, defina los niveles de ordenamiento, para ello:

1. Haga clic en el botón Agregar nivel, elija el campo Género y cambie el

Criterio de ordenación a Z a A.

2. Haga clic en el botón Agregar nivel, elija el campo Cargo y cambie el

Criterio de ordenación a A a Z.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 100

3. Haga clic en el botón Agregar nivel, elija el campo Fecha de

Nacimiento y cambie el Criterio de ordenación a De más recientes a
más antiguos.

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 101

Ejemplo 3: Ordenar por Ícono de celda

Con la tabla de datos anterior, ordene por el campo Sueldo de forma que el color de
los íconos sea Verde, Rojo y Amarrillo.

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando Ordenar,

elimine los niveles del ejemplo anterior con el botón Eliminar nivel.

 En el cuadro Ordenar, defina los niveles de ordenamiento, para ello:

1. Haga clic en el botón Agregar nivel y elija el campo Sueldo; en

Ordenar según, elija Ícono de celda; y en Criterio de ordenación, elija
el ícono Verde.

2. Haga clic en el botón Agregar nivel y elija el campo Sueldo; en

Ordenar según, elija Ícono de celda; y en Criterio de ordenación, elija
el ícono Rojo.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 102

3. Haga clic en el botón Agregar nivel y elija el campo Sueldo; en

Ordenar según, elija Ícono de celda; y en Criterio de ordenación, elija
el ícono Amarrillo.

También, es posible cambiar los valores de la última columna En la

parte superior para que se invierta los resultados y aparezcan En la
parte inferior.

 Haga clic en el botón Aceptar y luego obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 103

Ejemplo 4: Ordenar por Color de celda

Con la siguiente tabla de datos, ordene por el color aguamarina (es posible elegir
cualquier campo, debido a que el color se aplica a todo el registro).

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando Ordenar.

 En el cuadro Ordenar, elija el campo Vendedor; en Ordenar según, elija

Color de celda; y en Criterio de ordenación, elija el color Aguamarina.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 104

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 105

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

 Se vuelve a ingresar al cuadro Ordenar y se cambia la última columna a En la

parte inferior.

 Luego, presione el botón Aceptar y obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 106

2.1.4 Ordenar por Listas Personalizadas

Es posible ordenar por medio de un orden diferente al ascendente o descendente

usando una lista personalizada. Por ejemplo, en un campo Mes, ordenar por Enero,
Febrero, Marzo y Abril; en un campo Cargo, ordenar por Gerente, Jefe, Asistente,
Auxiliar, entre otros casos.

Ejemplo 1: Usar una lista predefinida (existente)

Con la siguiente tabla de datos, ordene por el campo Mes de Venta con el orden
Enero, Febrero, Marzo y Abril.

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando Ordenar.

 En el cuadro Ordenar, elija el campo Mes de Venta; y en Criterio de

ordenación, elija Lista personalizada…

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 107

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 108

 En el cuadro Listas personalizas, elija la lista enero, febrero, marzo,… y

presione el botón Aceptar.

 Al regresar el cuadro Ordenar, se verá lo siguiente:

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 109

Ejemplo 2: Crear una lista personalizada

Con la siguiente tabla de datos, ordene por el campo Área con el orden RRHH,
Sistemas, Ventas y Publicidad.

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando Ordenar.

 En el cuadro Ordenar, elija el campo Área; y en Criterio de ordenación, elija

Lista personalizada…

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 110

 En el cuadro Listas personalizas, escriba los valores de la lista; al finalizar,

presione el botón Agregar; y luego, el botón Aceptar.

 Al regresar el cuadro Ordenar se verá lo siguiente:

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 111

También, es posible invertir el orden de la lista personalizada.

Actividades

 En una hoja nueva, escriba la siguiente tabla de datos. Luego, copie toda la
tabla en las hojas Hoja2, Hoja3, Hoja4, Hoja5, Hoja6 y Hoja7:

 Ordene por los siguientes criterios:

1. En la Hoja1, ordene por Vendedor de forma ascendente.

2. En la Hoja2, ordene por Distrito de forma ascendente y, luego, por

Ventas de forma descendente.

3. En la Hoja3, ordene por Tipo y Distrito de forma ascendente y

Unidades de forma descendente.

4. En la Hoja4, ordene por Mes de Venta según el Color de celda Naranja

en la parte superior.

5. En la Hoja5, ordene por Ventas según el Ícono de celda en el siguiente

orden: Amarrillo, Verde y Rojo.

6. En la Hoja6, ordene por Tipo en el orden que se indica: Enlatados,

Verduras, Secos, Pescado, Carnes y Lácteos.

7. En la Hoja7, ordene por Mes de Venta en el orden que se indica: Abril,

Marzo, Febrero y Enero; y, luego, por Unidades de forma descendente.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 112

Resumen
 Ordenar una tabla de datos permite presentar, de diversas formas, la
información según nuestra necesidad.

 Podemos ordenar según el tipo de datos del campo. Por Texto, de forma
alfabética A-Z o viceversa; por Número, de Menor a mayor o viceversa; y por
Fecha, de Más antiguos a Más recientes o viceversa.

 Adicionalmente, se puede ordenar por Ícono y color de celda.

 También, se dispone del uso de listas personalizadas, ya sea predefinida o

creada según la necesidad.

Puede revisar los siguientes enlaces para ampliar los conceptos vistos en este tema:

1. https://www.youtube.com/watch?v=vhei1uqxpNI
2. https://www.youtube.com/watch?v=XX6che9qAnM
3. https://www.youtube.com/watch?v=ZIiWInMKeKQ

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 113

2.2 TEMA 5: FILTROS AUTOMÁTICOS Y AVANZADOS

Los filtros en Excel permiten definir

criterios en uno o en varios campos
de una tabla de datos, dando como
resultado un grupo de registros que
cumplen dichas condiciones. Por
ejemplo, dentro de una tabla de
datos de un instituto educativo,
podemos filtrar aquellos alumnos que hayan aprobado o desaprobado un determinado
curso. Además, podemos obtener datos de aquellos alumnos que viven en un
determinado distrito, si tienen la condición de becados, etc.

En Excel 2013, tenemos dos formas de aplicar filtros:

2.2.1 Filtros Automáticos

También denominado Autofiltros, permite aplicar criterios simples por medio de la

selección de valores en cada campo. Esto puede ser de manera individual marcando o
desmarcando una casilla, o indicando criterios usando el cuadro Autofiltro
personalizado donde se pueden definir hasta dos condiciones.

Filtrar datos utilizando el comando Autofiltro es una manera fácil y rápida de ubicar un
grupo de información. Para utilizar esta herramienta, se debe elegir la tabla de datos,
activar la opción de Filtros y utilizar las listas desplegables asociadas a los nombres de
los campos.

Ejemplo 1: Filtrar por un campo

Con la siguiente tabla de datos, filtre al personal femenino.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 114

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando Filtro.

 En el botón del campo Género, desmarque la casilla (Seleccionar todo).

Luego, marque la casilla del valor F.

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 115

En la barra de estado, podrá observar el número de registros que

cumplen el criterio.

Este mensaje desaparece al cambiar a otra hoja o al cerrar el libro; para

ver la cantidad de registros, puede usar el siguiente procedimiento:

 Ubíquese en la primera celda del primer resultado.

 Luego, presione la combinación de teclado Control + Shift +

Tecla direccional hacia abajo:

 En la barra de estado observará:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 116

Ejemplo 2: Filtrar por dos campos

Con la tabla de datos anterior, filtre al personal masculino del Distrito San Miguel.

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En el botón del campo Género, desmarque la casilla (Seleccionar todo) y

marque la casilla del valor M. Luego, presione el botón Aceptar.

 En el botón del campo Distrito, desmarque la casilla (Seleccionar todo).

Luego, marque la casilla del valor San Miguel.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 117

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 118

Ejemplo 3: Filtrar por un campo texto

Con la tabla de datos anterior, filtre al personal cuyo apellido Paterno inicie con la letra
“M”.

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En el botón del campo Paterno, elija Filtros de texto / Comienza por…

 En el cuadro Autofiltro personalizado, escriba “M” en el primer criterio

(comienza por).

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 119

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

De manera similar, observe que, también, tiene las

opciones: Es igual a, No es igual a, Termina con,
Contiene, No contiene y Filtro personalizado.

Además, puede hacer uso del comodín * (asterisco)

para representar varios caracteres o letras y del
comodín ? (interrogación), para representar un
carácter o letra.

Ejemplo 4: Filtrar por un campo texto usando comodín *

Con la tabla de datos anterior, filtre al personal cuyo apellido Paterno inicie con la letra
“G”, use el comodin * (asterisco).

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En el botón del campo Paterno, elija Filtros de texto / Es igual a…

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 120

 En el cuadro Autofiltro personalizado, escriba “G*” en el primer criterio (es

igual a).

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 121

Ejemplo 5: Filtrar por un campo texto usando comodín ?

Con la tabla de datos anterior, filtre al personal cuyo segundo carácter en el Distrito
es “a”, use el comodin ? (interrogación).

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En el botón del campo Distrito, elija Filtros de texto / Es igual a…

 En el cuadro Autofiltro personalizado, escriba “?a*” en el primer criterio (es

igual a).

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 122

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 123

Ejemplo 6: Filtrar por un campo numérico

Con la tabla de datos anterior, filtre al personal cuyo Sueldo sea entre 1500 y 2000.

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En el botón del campo Sueldo, elija Filtros de número / Entre…

 En el cuadro Autofiltro personalizado, escriba 1500 en el primer criterio (es

mayor o igual a) y 2000 en el segundo criterio (es menor o igual a).

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 124

De manera similar, observe que, también, tiene las

opciones: Es igual a, No es igual a, Mayor que, Mayor o
igual que, Menor que, Menor o igual que, Diez mejores,
Superior del promedio, Inferior al promedio y Filtro
personalizado.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 125

Ejemplo 7: Filtrar por un campo numérico

Con la tabla de datos anterior, filtre al personal cuyo Sueldo sea inferior al promedio.

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En el botón del campo Sueldo, elija Filtros de número / Inferior al promedio.

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 126

Ejemplo 8: Filtrar por un campo fecha

Con la tabla de datos anterior, filtre al personal que haya nacido antes del año 1980.

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En el botón del campo Fecha Nacimiento, elija Filtros de fecha / Antes…

 En el cuadro Autofiltro personalizado, escriba 01/01/1980 en el primer criterio

(es anterior a).

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 127

Considere que una fecha se expresa dd/mm/aaaa y al momento de

escribir el valor éste debe adecuarse a dicha sintaxis.

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

De manera similar,
observe que también
tiene estas opciones
junto a Todas las
fechas en periodo y
Filtro personalizado.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 128

2.2.2 Filtros Avanzados

Esta herramienta permite aplicar criterios complejos que

no pueden ser resueltos mediante los filtros automáticos.

Para utilizar los filtros avanzados, es necesario escribir

las condiciones para el filtro en un espacio denominado
Tabla de criterios; además, esta opción nos brinda la
posibilidad de copiar los resultados en otro lugar diferente
a la tabla de datos.

2.2.3 Tabla de Criterios

La tabla de criterios tiene una estructura similar a una tabla de datos, compuesta por
columnas y filas, que permite establecer las condiciones que se aplicarán en el filtro
avanzado.

Estructura general

Donde…

En la primera fila, se definen los nombres de los campos que pertenecen a la tabla de
datos.
A partir de la segunda fila, se definen los valores para cada campo. La tabla se lee:
Cada fila tendrá la unión Y, es decir, se cumplirá: Campo 1 con el valor X1 y Campo 2
con el valor Y1 y Campo 3 con el valor Z1, etc.
Cada columna tendrá la unión O, es decir, se cumplirá: Campo 1 con el valor X1 o el
valor X2, etc.

Es recomendable ubicar la tabla de criterios por encima o por debajo de

la tabla de datos, debido a que al aplicar el filtro, las filas de la hoja de
cálculo se ocultan y los criterios definidos lo harán, también.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 129

Ejemplo 1: Filtrar por dos campos (Unión Y)

Con la siguiente tabla de datos, filtre los contadores con sueldo inferior a 2000.

Procedimiento

 En la parte superior de la tabla de datos, escriba la siguiente tabla de criterios:

Puede escribir o copiar cada nombre de campo desde la tabla de datos.

Para valores numéricos, puede utilizar los operadores de comparación:

> (Mayor que), >= (Mayor o igual que), < (Menor que), <= (Menor o igual
que), = (Igual a) y <> (Diferente de).

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 130

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando

Avanzadas.

 En el cuadro Filtro avanzado, verifique que sea correcto el rango de la tabla

de datos en el área Rango de la lista y, luego, seleccione el rango de la tabla
de criterios que aparecerá en el área Rango de criterios.

Observe que los rangos son escritos con referencia absoluta.

También, puede usar el nombre de rango para el Rango de la lista.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 131

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

En la barra de estado, podrá observar el número de registros que

cumplen el criterio.

Este mensaje desaparece al cambiar a otra hoja o al cerrar el libro, para

ver la cantidad de registros puede usar el siguiente procedimiento:

 Ubíquese en la primera celda del primer resultado.

 Luego, presione la combinación de teclado Control + Shift +

Tecla direccional hacia abajo:

 En la barra de estado, observará:

Ejemplo 2: Filtrar por dos campos (Unión O)

Con la tabla de datos anterior, filtre el personal que sea Contador o que tengan sueldo
inferior a 2000.

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 132

 En la parte superior de la tabla de datos, cambie tabla de criterios con los

siguientes valores:

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando

Avanzadas.

 En el cuadro Filtro avanzado, seleccione el nuevo rango de la tabla de

criterios que aparecerá en el área Rango de criterios.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 133

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 134

Ejemplo 3: Filtrar con comodines * o ?

Con la tabla de datos anterior, filtre el personal cuyo apellido paterno inicie con “M” y
su cargo tenga como penúltima letra la “o”.

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En la parte superior de la tabla de datos, cambie tabla de criterios con los

siguientes valores:

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando

Avanzadas.

 En el cuadro Filtro avanzado, seleccione el nuevo rango de la tabla de

criterios que aparecerá en el área Rango de criterios.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 135

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 136

Ejemplo 4: Filtrar números con rangos

Con la tabla de datos anterior, filtre el personal cuyo sueldo sea entre 1200 y 1500.

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En la parte superior de la tabla de datos, cambie tabla de criterios con los

siguientes valores:

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando

Avanzadas.

 En el cuadro Filtro avanzado, seleccione el nuevo rango de la tabla de

criterios que aparecerá en el área Rango de criterios.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 137

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 138

Ejemplo 5: Filtrar fechas

Con la tabla de datos anterior, filtre el personal nacido entre los años 1975 y 1985.

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En la parte superior de la tabla de datos, cambie tabla de criterios con los

siguientes valores:

Considere que una fecha se expresa dd/mm/aaaa y al momento de

escribir el valor éste debe adecuarse a dicha sintaxis.

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando

Avanzadas.

 En el cuadro Filtro avanzado, seleccione el nuevo rango de la tabla de

criterios que aparecerá en el área Rango de criterios.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 139

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

Ejemplo 6: Filtrar con tabla de criterios cruzada

Con la tabla de datos anterior, filtre el personal masculino del distrito de Lima junto al
del Callao con sueldo superior a 1400.

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En la parte superior de la tabla de datos, cambie tabla de criterios con los

siguientes valores:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 140

La tabla de criterios cruzada, nos permite establecer condiciones

complejas que no podrán resolverse con Autofiltro, aquí podremos definir
dos condiciones dando como resultado el cruce o la unión de ambos
criterios.

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando

Avanzadas.

 En el cuadro Filtro avanzado, seleccione el nuevo rango de la tabla de

criterios que aparecerá en el área Rango de criterios.

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 141

Ejemplo 7: Filtrar y copiar los resultados en otra ubicación

Con la tabla de datos anterior, filtre el personal del cargo Marketing cuyo correo sea
de “gmail” junto a los del cargo Administrador que no sean de Lima. Copie los
resultados a partir de la celda M9.

Procedimiento

 Borre el filtro anterior, haga clic en el comando Borrar del grupo Ordenar
y filtrar en la ficha Datos.

 En la parte superior de la tabla de datos, cambie tabla de criterios con los

siguientes valores:

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando

Avanzadas.

 En el cuadro Filtro avanzado, seleccione el nuevo rango de la tabla de

criterios que aparecerá en el área Rango de criterios. Luego, active Copiar a
otro lugar; y, en el área Copiar a, seleccione la celda M9.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 142

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

En la barra de estado, no aparece la cantidad de registros.

Utilice el siguiente procedimiento:

 Ubíquese en la primera celda del primer resultado. (M10)

 Luego, presione la combinación de teclado Control + Shift +

Tecla direccional hacia abajo:

 En la barra de estado, observará:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 143

Ejemplo 8: Filtrar y copiar los resultados de forma personalizada

Con la tabla de datos anterior, filtre el personal del cargo Doctor o Marketing ambos
con sueldo superior a 2000. Copie los resultados a partir de la celda M9 sólo considere
los campos: Paterno, Nombres, Cargo, Distrito y Sueldo.

Procedimiento

 Borre los resultados del filtro anterior, en la celda M9 presione CTRL + * y,

luego, elija Borrar / Borra todo del grupo Modificar en la ficha Inicio.

 En la parte superior de la tabla de datos, cambie tabla de criterios con los

siguientes valores:

 Para los resultados, escriba o copie los campos solicitados a partir de la celda
M9.

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Ordenar y filtrar, elija el comando

Avanzadas.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 144

 En el cuadro Filtro avanzado, seleccione el nuevo rango de la tabla de

criterios que aparecerá en el área Rango de criterios. Luego, active Copiar a
otro lugar y, en el área Copiar a, seleccione el rango M9:Q9.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 145

 Haga clic en el botón Aceptar y, luego, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 146

Actividades

 En una hoja nueva, escriba la siguiente tabla de datos. Luego, copie toda la
tabla para desarrollar cada caso:

 Usando filtro automático o autofiltro obtenga lo siguiente:

1. Filtre los registros cuyo departamento de destino (Dpto Destino) sea

Loreto.

2. Filtre los registros cuyo vendedor se Joaquín Castillo con venta total
superior de 4500.

3. Filtre los registros con precios de venta (Precio Venta) superiores del
promedio.

4. Filtre los registros cuyo código termine en “5”, use comodines.

5. Filtre los registros cuya fecha de venta (Fecha Venta) pertenezca al

primer trimestre del año 2014.

 Usando filtro avanzado obtenga lo siguiente:

1. Filtre los registros cuya fecha de venta (Fecha Venta) pertenezca al

segundo trimestre del año 2014.

2. Filtre los registros cuyo departamento de destino (Dpto Destino) inicie

con “A” o con “L” y su precio de venta (Precio Venta) sea inferior de 40.

3. Filtre los registros cuyo departamento de destino (Dpto Destino) sea

Cuzco con unidades a partir de 80 junto a los del departamento Tacna
con fecha de venta (Fecha Venta) correspondiente al primer bimestre
del año 2014.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 147

4. Filtre los registros del producto Pantalón con medida M junto a los
productos Camisa con venta total hasta 2000. Copie los resultados a
partir de la celda L10.

5. Filtre los registros cuya fecha de venta (Fecha Venta) sea del mes de
Abril con precio de venta (Precio Venta) superior de 60. Copie los
resultados a partir de la celda L10 considere sólo los campos: Producto,
Vendedor, Fecha Venta, Precio Venta y Dpto Destino.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 148

Resumen
 Filtrar una tabla de datos permite presentar sólo los registros que cumplan con
un determinado criterio ya sea para un campo o varios.

 Podemos aplicar filtros de acuerdo al tipo de cada campo. Por Texto, usando
valores fijos o con comodines (*, ?); por Número, usando los operadores de
comparación; por Fechas, usando los operadores de comparación y la sintaxis
dd/mm/aaaa.

 Adicionalmente, se puede aplicar filtros avanzados usando una tabla de

criterios cruzada para condiciones complejas.

 También, podemos copiar los resultados en un lugar distinto de la tabla de

datos y personalizar los campos a mostrar.

Puede revisar los siguientes enlaces para ampliar los conceptos vistos en este tema:

1. https://www.youtube.com/watch?v=3NU0T2V02FY
2. https://www.youtube.com/watch?v=76p5NZgbp-I
3. https://www.youtube.com/watch?v=p_z6y1LZmZM

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 149

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 150

2.3 TEMA 6: AGRUPAMIENTO DE DATOS

2.3.1 Subtotales

La herramienta de Subtotales permite resumir una tabla de datos por uno o varios
campos y para cada resumen
podemos realizar uno o más
cálculos, como por ejemplo obtener
la suma de las unidades vendidas
por cada sucursal, el promedio de
la venta por producto, la cuenta de
los registros de cada sucursal y
departamento, etc.

Los resultados de un subtotal se verán en un formato de esquema,

es decir, por niveles donde se podrán mostrar y ocultar celdas de
acuerdo a lo que necesitemos. Cada nivel se representa por un
número; el último nivel muestra toda la información de la tabla de
datos; el primer nivel muestra el resumen general; y los niveles
intermedios, la información de manera parcial.

Antes de aplicar un subtotal, es importante ordenar la tabla de

datos de acuerdo a los resultados que deseamos obtener, por
ejemplo:
 Si necesitamos la suma de las unidades vendidas por cada sucursal,
previamente, debemos ordenar por sucursal.

 Si necesitamos el promedio de la venta por producto, previamente, debemos

ordenar por producto.

Se aplicará el orden predeterminado, es decir, de forma ascendente.

Recuerde que, también, puede utilizar el orden por lista personalizada

visto anteriormente.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 151

Ejemplo 1: Subtotal con un cálculo en un campo

Con la siguiente tabla de datos, halle el promedio del Sueldo por Cargo.

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 Ordene la tabla de datos por el campo Cargo.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 152

Observe cómo se agrupan los valores en el campo

Cargo. Aquí, se pueden apreciar 5 bloques o grupos
diferentes: Administrador, Contador, Doctor, Ingeniero y
Marketing. Considere que para cada grupo se hará un
cálculo.

 En la ficha Datos, grupo Esquema, elija el comando Subtotal.

 En el cuadro Subtotales, elija Cargo en el área Para cada cambio en; elija
Promedio en el área Usar función; y solo marque la casilla del Sueldo en el
área Agregar subtotal a.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 153

 Haga clic en el botón Aceptar, elija el nivel de esquema 2 y, luego, obtendrá el

siguiente resultado:

Para una mejor presentación de los resultados, se han ocultado las

columnas B, C, D, E, G, H, J lo cual no es necesario hacer al realizar
subtotales.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 154

Ejemplo 2: Subtotal con dos cálculos diferentes en un campo

Con la tabla de datos anterior, halle el número de registros y la suma del Sueldo por
Distrito. Para resolver este ejemplo, usaremos Subtotal anidado, es decir, crearemos
un Subtotal y, luego, añadiremos otro.

Procedimiento

 Quite el subtotal creado en el ejemplo anterior, haga clic en el botón Quitar

todos del cuadro Subtotales.

Observe que, al abrir el cuadro, nos muestra el último subtotal aplicado

en la tabla de dato, pero al cerrar el libro se muestran los valores por
defecto establecidos por Excel.

 Ordene la tabla de datos por el campo Distrito.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 155

 En la ficha Datos, grupo Esquema, elija el comando Subtotal.

 Para el primer subtotal, en el cuadro Subtotales, elija Distrito en el área

Para cada cambio en; elija Cuenta en el área Usar función; y solo marque la
casilla del Distrito en el área Agregar subtotal a. Finalmente, desmarque la
casilla Reemplazar subtotales actuales.

Para realizar el conteo, es recomendable usar el mismo campo usado en

las áreas Para cada cambio en y Agregar subtotal a.

Es recomendable desmarcar la casilla Reemplazar subtotales actuales

desde el primer cálculo, debido a que se añadirá otro Subtotal.

Si no se realiza este paso, el siguiente subtotal reemplazará el actual y

no habrá anidamiento de subtotales.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 156

 Vuelva a elegir el comando Subtotal.

 Para el segundo subtotal, en el cuadro Subtotales, elija Distrito en el área

Para cada cambio en; elija Suma en el área Usar función; y sólo marque la
casilla del Sueldo en el área Agregar subtotal a.

 Haga clic en el botón Aceptar, elija el nivel de esquema 3 y, luego, obtendrá el

siguiente resultado:

Para una mejor presentación de los resultados, se han ocultado las

columnas B, C, D, E, H, J lo cual no es necesario hacer al realizar
subtotales.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 157

Ejemplo 3: Subtotal con un cálculo en dos campos diferentes

Con la tabla de datos anterior, halle el máximo valor del Sueldo por Género y la suma
de los Años de Servicio por Cargo. Para resolver este ejemplo, usaremos Subtotal
anidado, es decir, crearemos un Subtotal y, luego, añadiremos otro.

Procedimiento

 Quite los subtotales creados en el ejemplo anterior, haga clic en el botón

Quitar todos del cuadro Subtotales.

 Ordene la tabla de datos por el campo Género y, luego, por el campo Cargo.

 En la ficha Datos, grupo Esquema, elija el comando Subtotal.

 Para el primer subtotal, en el cuadro Subtotales elija Género en el área Para

cada cambio en; elija Max. en el área Usar función; y sólo marque la casilla
del Sueldo en el área Agregar subtotal a. Finalmente, desmarque la casilla
Reemplazar subtotales actuales.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 158

 Vuelva a elegir el comando Subtotal.

 Para el segundo subtotal, en el cuadro Subtotales, elija Cargo en el área

Para cada cambio en; elija Suma en el área Usar función; y sólo marque la
casilla de los Años de Servicio en el área Agregar subtotal a.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 159

 Haga clic en el botón Aceptar, elija el nivel de esquema 3 y, luego, obtendrá el

siguiente resultado:

Actividades

 En una hoja nueva, escriba la siguiente tabla de datos. Luego, copie toda la
tabla para desarrollar cada caso:

 Usando subtotales obtenga lo siguiente:

1. Halle el número de registros por Producto.

2. Halle el promedio de la Venta Total por Vendedor.

3. Halle el promedio del precio de venta (Precio Venta) por Medida.

4. Halle el total de las Unidades por departamento de destino (Dpto

Destino).

5. Halle el número de registros y el total de las unidades por Vendedor.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 160

6. Halle el mínimo valor del precio de venta (Precio Venta) y Unidades por
Producto.

7. Halle el máximo y mínimo valor de la Venta Total por departamento de

destino (Dpto Destino) y Vendedor.

8. Halle la suma y el promedio de las Unidades por Producto y Medida.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 161

Resumen
 Los Subtotales permiten resumir una tabla de datos y realizar un cálculo para
uno o varios campos.

 Antes de iniciar con los subtotales, es necesario ordenar la tabla de datos de

acuerdo a la necesidad planteada. Sin este primer paso, no aparecerán los
resultados esperados.

 Al utilizar subtotales, se crea un esquema con diferentes niveles los cuales

permiten mostrar y ocultar la información.

 Es importante seguir una secuencia ordenada al momento de crear los

subtotales, ya que, si erramos en un nivel, será necesario quitar todos los
subtotales creados e iniciar de nuevo.

Puede revisar los siguientes enlaces para ampliar los conceptos vistos en este tema:

1. https://www.youtube.com/watch?v=GWvbJKSzHSU
2. https://www.youtube.com/watch?v=XZKih8dDzAg
3. https://www.youtube.com/watch?v=ATZDmbqmFYM

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 162

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 163

2.4 TEMA 7: TABLAS Y GRÁFICOS DINÁMICOS

2.4.1 Tablas Dinámicas

Es una de las principales herramientas utilizadas en el ambiente laboral, debido a que

las tablas dinámicas permiten
presentar la información de una tabla
de datos como un informe de resumen,
en el que se pueden realizar cálculos y
análisis de su contenido, de manera
sencilla. Además, puede convertir,
fácilmente, dicho informe en un gráfico
dinámico para comparar y revisar las
tendencias de los datos para una
correcta toma de decisiones en un
ambiente de producción.

Dentro de una tabla dinámica, podrá realizar las siguientes operaciones:

 Consolidar y resumir los valores de uno o varios campos

 Realzar operaciones de suma, promedio, cuenta, entre otros

 Agrupar los valores de un campo tipo fecha o número

 Crear campos calculados

 Segmentar la información usando los valores de uno o varios campos

Para crear una tabla dinámica, debe tener en cuenta las siguientes secciones o áreas:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 164

Donde…

Área Descripción

Los campos que se ubiquen en esta sección servirán para filtrar el

FILTROS informe de la tabla dinámica. Puede elegir uno o varios valores por
campo.

Cada valor de campo ubicado en esta sección estará distribuido por

FILAS filas. Por ejemplo, si se ubica el campo Género, se generan dos filas:
una para Masculino y otra para Femenino.

Cada valor de campo ubicado en esta sección estará distribuido por

COLUMNAS columnas. Por ejemplo, si se ubica el campo Género, se generan dos
columnas: una para Masculino y otra para Femenino.

Aquí, se consolidan los valores del campo ubicados en esta sección,

VALORES utilizando las funciones de Suma, Cuenta, Promedio, Máx, Min, entre
otras.

En Excel 2013, puede crear una tabla dinámica con sólo seleccionar la tabla de datos
y utilizar la opción de Análisis rápido.

Ejemplo 1: Tabla Dinámica usando Análisis rápido

Con la siguiente tabla de datos, inserte una tabla dinámica usando la opción de
Análisis rápido.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 165

Procedimiento

 Seleccione toda la tabla de datos.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 166

 Haga clic en botón de Análisis rápido ubicado en la esquina inferior derecha

y, en la pestaña Tablas, elija la segunda tabla dinámica recomendada por
Excel.

 En una hoja nueva, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 167

Ejemplo 2: Tabla Dinámica usando las áreas Filas y Valores

Con la tabla de datos anterior, inserte en una nueva hoja un informe de tabla dinámica
que muestre el promedio de la Venta por Categoría, aplique formato de Contabilidad
y el Estilo de tabla dinámica medio 2.

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Tablas, elija el comando Tabla dinámica.

El comando Tablas dinámicas permite elegir un informe recomendado

por Excel muy parecido a los que encontramos en la opción de Análisis
rápido visto en el ejemplo anterior.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 168

 En el cuadro Crear tabla dinámica, verifique el rango de la tabla de datos y

que esté marcada la opción Nueva hoja de cálculo.

También, puede elegir la opción Hoja de cálculo existente para ubicar

la tabla dinámica en una hoja creada anteriormente en el libro.

 Haga clic en el botón Aceptar y obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 169

 Al ubicarse dentro de la tabla dinámica, se muestra, al lado derecho, el panel

Campos de tabla dinámica.

Si cierra este panel, podrá mostrarlo de nuevo usando el comando Lista

de campo ubicado en la ficha Analizar / grupo Mostrar.

 Desde este panel:

1. En la parte superior, podrá ver los nombres de todos los campos de la

tabla de datos y, además, podrá marcar la casilla asociada en cada
campo. Excel, de acuerdo al tipo de dato, lo ubicará en una de las
áreas.

2. En la parte inferior, podrá acceder a cada área de la Tabla dinámica y

administrar los campos agregados, y cambiar la función de los cálculos
de resumen.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 170

 Marque las casillas de los campos Categoría y Venta, observe que los campos
son enviados a las áreas de Filas y Valores respectivamente.

También, puede arrastrar los campos hacia las áreas y viceversa,

incluso, puede arrastrar el campo y cambiarlo a otra área.

De manera predeterminada, la función usada para un campo numérico

es Suma.

 Obtendrá el siguiente resultado como tabla dinámica; para cambiar la función a

promedio, haga doble clic en el campo Suma de Venta.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 171

También, puede elegir la opción Configuración de campo de valor al

dar un clic en el campo Suma de Venta ubicado en el área Valores.

 En el cuadro Configuración de campo de valor, cambie la función Suma a

Promedio.

 Además, desde este cuadro, puede cambiar el formato del número con un clic
en el botón Formato de número.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 172

 Al presionar el botón Aceptar de cada cuadro, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 173

 Cambie el estilo de la tabla eligiendo una opción dentro del grupo Estilos de
tabla dinámica de la ficha Diseño. Para este ejemplo, use Estilo de tabla
dinámica medio 2.

Los estilos de tabla dinámica se clasifican en Claro, Medio y Oscuro.

 Finalmente, tendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 174

Ejemplo 3: Tabla Dinámica usando las áreas Filas, Columnas y Valores

Con la tabla de datos anterior, inserte, en una nueva hoja, un informe de tabla
dinámica que muestre la suma de la Cantidad por Vendedor y Región. Aplique el estilo
de tabla de su preferencia.

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Tablas, elija el comando Tabla dinámica.

 En el cuadro Crear tabla dinámica, verifique el rango de la tabla de datos y

que esté marcada la opción Nueva hoja de cálculo.

 Haga clic en el botón Aceptar, y arrastre los campos Vendedor, Región y

Cantidad hacia las áreas de Filas, Columnas y Valores, respectivamente.

 Aplique el estilo de tabla de su preferencia y obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 175

Ejemplo 4: Tabla Dinámica con Agrupamiento de Fechas

Con la tabla de datos anterior, inserte en una nueva hoja un informe de tabla dinámica
que muestre la suma de la Venta por Mes de Venta y Trimestre. Aplique el estilo de
tabla de su preferencia, cambie el diseño del informe a Tabular y muestre los
subtotales.

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Tablas, elija el comando Tabla dinámica.

 En el cuadro Crear tabla dinámica, verifique el rango de la tabla de datos y

que esté marcada la opción Nueva hoja de cálculo.

 Haga clic en el botón Aceptar, y arrastre los campos Fecha de Venta y Venta
hacia las áreas de Filas y Valores respectivamente.

 Aplique el estilo de tabla de su preferencia. Para agrupar, debe ubicar la celda

activa en el primer resultado del campo fecha.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 176

 Luego, elija el comando Agrupar campos del grupo Agrupar de la ficha

Analizar.

 En el cuadro Agrupar de la lista, seleccione Por Meses y Trimestres.

 Al presionar el botón Aceptar, cambie el formato de número del campo Suma

de Venta a Contabilidad y obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 177

 Cambie el diseño del informe con la opción Mostrar en formato tabular

ubicado en el grupo Diseño de la ficha Diseño.

 Agregue los subtotales con la opción Mostrar todos los subtotales en la

parte inferior del grupo ubicado en el grupo Diseño de la ficha Diseño.

El diseño en forma tabular solo permite mostrar los subtotales en la

parte inferior; en cambio, el diseño en forma de esquema permite
mostrar los subtotales en la parte inferior o en la parte superior.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 178

 Finalmente, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 179

Ejemplo 5: Tabla Dinámica con Agrupamiento de Números

Con la tabla de datos anterior, inserte, en una nueva hoja, un informe de tabla
dinámica que muestre la suma de la Venta por Categoría y agrupada por Cantidad
(inicia en 10 hasta 30 de 5 en 5). Aplique el estilo de tabla y diseño de informe de su
preferencia.

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Tablas, elija el comando Tabla dinámica.

 En el cuadro Crear tabla dinámica, verifique el rango de la tabla de datos y

que esté marcada la opción Nueva hoja de cálculo.

 Haga clic en el botón Aceptar, y arrastre los campos Vendedor, Cantidad,

Categoría y Venta hacia las áreas de Filtros, Filas, Columnas y Valores,
respectivamente.

 Aplique el estilo de tabla de su preferencia, cambie el formato de número a

Contabilidad y, para agrupar, debe ubicar la celda activa en el primer resultado
del campo Suma de Venta.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 180

 Luego, elija el comando Agrupar campos del grupo Agrupar de la ficha

Analizar.

 En el cuadro Agrupar, escriba 10 en la zona Comenzar en. Luego, escriba 5

en la zona Por.

 Cambie el diseño del informe por el de su preferencia. De manera opcional,

puede elegir que se muestren los totales generales por filas y/o columnas
desde la opción Totales generales ubicado en el grupo Diseño de la ficha
Diseño.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 181

 Finalmente, obtendrá el siguiente resultado:

En el campo ubicado en el área Filtros, puede elegir uno o varios

valores según su necesidad.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 182

Ejemplo 6: Tabla Dinámica con Campo Calculado

Con la tabla de datos anterior, inserte, en una nueva hoja, un informe de tabla
dinámica que muestre la suma de la Comisión (el cual se obtiene del 8% de la Venta)
por Categoría y Producto. Aplique el estilo de tabla y diseño de informe de su
preferencia.

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Tablas, elija el comando Tabla dinámica.

 En el cuadro Crear tabla dinámica, verifique el rango de la tabla de datos y

que esté marcada la opción Nueva hoja de cálculo.

 Haga clic en el botón Aceptar, y arrastre los campos Categoría y Producto

hacia el área de Filas.

 Aplique el estilo de tabla de su preferencia y cambie el diseño de informe a

Tabular.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 183

 Para agregar cálculo, elija la opción Campo calculado del comando Campos,
elementos y conjuntos ubicado en el grupo Cálculos de la ficha Analizar.

 En cuadro Insertar campo calculado, escriba el nombre del campo y su

respectiva fórmula: =Venta * 8%

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 184

También, puede hacer doble clic en Venta desde la lista de Campos.

Es válido escribir como fórmula =Venta * 0.08

 Al presionar el botón Aceptar, cambie el formato de número de la Comisión a

Contabilidad y obtendrá el siguiente resultado:

Observe que el campo calculado Comisión forma parte de los campos

en el panel derecho.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 185

Para modificar un campo calculado, debe ingresar al cuadro Insertar

campo calculado, elegir el campo calculado, escribir la nueva fórmula y
presionar el botón Aceptar:

Para eliminar un campo calculado, debe ingresar al cuadro Insertar

campo calculado, elegir el campo calculado y presionar el botón
Eliminar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 186

Ejemplo 7: Tabla Dinámica con Segmentación de Datos

Con la tabla de datos anterior, inserte, en una nueva hoja, un informe de tabla
dinámica que muestre el promedio de la Comisión por Vendedor y Región,
segmentado por Producto. Aplique el estilo de tabla y diseño de informe de su
preferencia.

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Tablas, elija el comando Tabla dinámica.

 En el cuadro Crear tabla dinámica, verifique el rango de la tabla de datos y

que esté marcada la opción Nueva hoja de cálculo.

 Haga clic en el botón Aceptar, y arrastre los campos Vendedor, Región y

Comisión (campo calculado creado en el ejemplo anterior) hacia las áreas de
Filas, Columnas y Valores, respectivamente.

 Cambie la función Suma a Promedio del campo Comisión, además del formato
Contabilidad. Aplique el estilo de tabla de su preferencia y cambie el diseño de
informe a Tabular.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 187

 Para segmentar, elija el comando Insertar Segmentación de datos del grupo

Filtrar de la ficha Analizar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 188

 En el cuadro Insertar Segmentación de datos, marque la casilla del campo

Producto.

 Al presionar el botón Aceptar, aparece el segmento Producto en el cual puede

cambiar su configuración, para que se muestre en dos columnas con el
comando Columnas ubicado en el grupo Botones de la ficha Opciones.
Además, cambie su color con el Estilo de Segmentación de datos, según su
preferencia.

Como podemos apreciar, la segmentación de datos proporciona botones

por cada valor del campo que permiten filtrar los datos de la tabla
dinámica.

Además, la segmentación de datos nos indica el estado actual del

filtrado aplicado, lo cual facilita el entendimiento de lo que se muestra
exactamente en un informe de tabla dinámica filtrado.

Para marcar más de un valor, haga clic en cada valor presionando la

tecla Control (CTRL).

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 189

 Seleccione los valores Equipo de Sonido, PSP, Reproductor DVD y Wii; y,

finalmente, obtendrá el siguiente resultado:

2.4.2 Gráficos Dinámicos

Los gráficos dinámicos representan,

gráficamente, los datos de un informe de
tabla dinámica.

La modificación del diseño es similar al que

se realiza con un informe de tabla
dinámica.

Cuando se realizan cambios en un campo dentro de uno de los informes, también, se

modifica el campo correspondiente del otro informe.

Ejemplo 8: Gráfico Dinámico

Con la tabla de datos anterior, cree la siguiente tabla dinámica y, luego, represente sus
valores mediante un gráfico dinámico.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 190

Procedimiento

 Ubique la celda activa al inicio de la tabla de datos o en un cualquier dato de la

tabla.

 En la ficha Datos, grupo Tablas, elija el comando Tabla dinámica.

 En el cuadro Crear tabla dinámica, verifique el rango de la tabla de datos y

que esté marcada la opción Nueva hoja de cálculo.

 Haga clic en el botón Aceptar y cree la tabla dinámica mostrada.

 Luego, elija el comando Gráfico dinámico ubicado en el grupo Herramientas

de la ficha Analizar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 191

 En el cuadro Insertar gráfico, elija el tipo Barra y, luego, el subtipo Barra 3D

agrupada.

 Al presionar el botón Aceptar, obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 192

En el gráfico dinámico, podemos aplicar las opciones y herramientas

vistas en el Tema 3: Gráficos del presente manual.

Para ocultar los botones, elija la opción Ocultar todos del comando
Botones de campo ubicado en el grupo Mostrar u ocultar de la ficha
Analizar.

Obtendrá el siguiente resultado:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 193

Actividades

 En una hoja nueva, escriba la siguiente tabla de datos. Cada tabla dinámica lo
creará en una hoja nueva:

 Usando tablas dinámicas obtenga lo siguiente:

1. Caso 1

2. Caso 2

3. Caso 3: Considere el campo calculado IGV (18% de la Venta Total).

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 194

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 195

4. Caso 4

5. Caso 5

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 196

Resumen
 Las Tablas dinámicas permiten consolidar la información de una tabla de datos.

 Permite distribuir los campos en Filas, Columnas, Filtro y Valores.

 Tenemos la opción de agrupar campos por fechas o por números.

 Se pueden crear campos calculados para realizar cálculos adicionales con la

información existente.

 La segmentación de datos es una forma de filtrar la información.

Puede revisar los siguientes enlaces para ampliar los conceptos vistos en este tema:

1. https://www.youtube.com/watch?v=810ZvpLSFHo
2. https://www.youtube.com/watch?v=EN_YoGimvX8
3. https://www.youtube.com/watch?v=mnQC5g3tTHM

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 197

UNIDAD

3
FUNCIONES LÓGICAS Y DE
BÚSQUEDA
LOGRO DE LA UNIDAD DE APRENDIZAJE 2
Al término de la unidad, el alumno escribe y/o inserta funciones de manera individual o
anidada para la toma de decisiones y recuperación de datos en una hoja de cálculo.

TEMARIO

3.1 Tema 8 : Funciones Lógicas

3.1.1 : Función Lógica Y()

3.1.2 : Función Lógica O()

3.1.3 : Función Lógica Si()

3.2 Tema 9 : Funciones de Búsquedas

3.2.1 : Función BuscarV()

3.2.1 : Función BuscarH()

ACTIVIDADES PROPUESTAS

 Los alumnos insertan funciones lógicas para la toma de decisión correcta, según
sea el caso.
 Los alumnos insertan funciones de búsquedas para ubicar un dato dentro de una
tabla de datos de manera vertical u horizontal.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 198

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 199

3.1 TEMA 8: FUNCIONES LÓGICAS

Este tipo de funciones son sumamente importantes sobre todo por la frecuencia con
las que se usan. Por lo general, son utilizadas en funciones anidadas.

Puede insertar funciones de la categoría Lógicas utilizando la ficha

Fórmulas y el grupo Biblioteca de Funciones.

3.1.1 Función Lógica SI()

Utilice SI para realizar pruebas condicionales en valores y fórmulas.

La función SI, devuelve un valor si la condición especificada es VERDADERO y otro

valor si dicho argumento es FALSO.

Donde…

 Prueba_lógica es cualquier valor o expresión que pueda evaluarse como

VERDADERO o FALSO. Por ejemplo, A10=100 es una expresión lógica. Este
argumento puede utilizar cualquier operador de comparación.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 200

 Valor_si_verdadero es el valor que se devuelve si el argumento prueba_lógica

es VERDADERO. Por ejemplo, si este argumento es la cadena de texto "Debe
dar sustitutorio" y el argumento prueba_lógica se evalúa como VERDADERO, la
función SI muestra el texto "Debe dar sustitutorio". Este argumento, también,
puede ser otra fórmula, por ejemplo A3*19%.

 Valor_si_falso es el valor que se devuelve si el argumento prueba_lógica es

FALSO. Este argumento tiene las mismas consideraciones que el argumento
Valor_si_verdadero.

Como se vio en temas anteriores, Excel proporciona funciones

adicionales que pueden utilizarse para analizar los datos basándose en
una condición.

Por ejemplo, para contar el número de veces que aparece una cadena
de texto o un número dentro de un rango de celdas, utilice la función de
hoja de cálculo CONTAR.SI.

Para calcular una suma basándose en una cadena de texto o un número

dentro de un rango, utilice la función SUMAR.SI.

Consideraciones

 Es posible anidar hasta 64 funciones SI como argumentos valor_si_verdadero y

valor_si_falso para crear pruebas más complicadas.

 Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la

función SI devuelve el valor devuelto por la ejecución de las instrucciones.

 Si uno de los argumentos de la función SI es una matriz, cada elemento de la

matriz se evaluará cuando se ejecute la instrucción SI.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 201

Ejemplo 1: Función SI()

Escriba la siguiente información en la Hoja1:

Procedimiento

 Seleccione la celda C5.

 Haga clic en el botón Insertar función de la barra de fórmulas.

 Seleccione la categoría Lógicas y la función Si. Luego, haga clic en el botón

Aceptar.

 En el cuadro Argumentos de función, en el área Prueba_logica, escriba

B5>=13. En el área Valor_si_verdadero, escriba “Aprobó el curso” . Y en el
área Valor_si_falso, escriba “Desaprobó el curso”. Luego, haga clic en el
botón Aceptar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 202

 Autollene las celdas siguientes y obtendrá el siguiente resultado:

Cuadro de Notas
CODIGO NOTA COMENTARIO
A120 14 Aprobó el curso
A121 12 Desaprobó el curso
A122 10 Desaprobó el curso
A123 8 Desaprobó el curso

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 203

Ejemplo 2: Función SI()

Escriba la siguiente información en la Hoja2:

Procedimiento

 Seleccione la celda D5.

 Haga clic en el botón Insertar función de la barra de fórmulas.

 Seleccione la categoría Lógicas y la función Si. Luego, haga clic en el botón

Aceptar.

 En el cuadro Argumentos de función, en el área Prueba_logica, escriba

B5=”A”. En el área Valor_si_verdadero, escriba C5*15%. Y en el área
Valor_si_falso, escriba 0. Luego, haga clic en el botón Aceptar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 204

 Autollene las celdas siguientes, aplique formato millares y obtendrá el siguiente

resultado:

Guarde el archivo con el nombre de Funciones Lógicas 1.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 205

3.1.2 Función Lógica Y()

Devuelve VERDADERO si todos los argumentos son VERDADEROS; devuelve

FALSO si uno o más argumentos son FALSOS.

Donde:

Valor_lógico1, Valor_lógico2,…. son entre 1 y 255 condiciones que desea

comprobar y que pueden ser verdadero o falso; y que pueden ser valores lógicos,
matriciales o referencias.

En su forma anidada con la función SI, su sintaxis sería:

Si recuerda, en lógica, la conjunción se representaba a través del símbolo ^ y la

conjunción sólo es verdadera si los componentes son verdaderos. Aquí, se muestra la
tabla de verdad para la conjunción que es, exactamente, la misma regla aplicada en la
función Y().

p q p^q

V V V

V F F

F V F

F F F

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 206

Los argumentos deben evaluarse como valores lógicos: VERDADERO o

FALSO. Los argumentos deben ser matrices o referencias que
contengan valores lógicos.

Si un argumento matricial o de referencia contiene texto o celdas vacías,

dichos valores se pasarán por alto.

Si el rango especificado no contiene valores lógicos, la función Y

devuelve el valor de error #¡VALOR!

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 207

Ejemplo 1: Función Y()

En un archivo nuevo, escriba la siguiente información en la Hoja1:

A continuación, se evaluará si el valor A es mayor que 10 y si el valor B es menor que

5. La respuesta deberá ser VERDADERO según la tabla de verdad de la conjunción.

Procedimiento

 Seleccione la celda C5.

 Haga clic en el botón Insertar función de la barra de fórmulas.

 Seleccione la categoría Lógicas y la función Y. Luego, haga clic en el botón

Aceptar.

 En el cuadro Argumentos de función, en el área Valor_lógico1, escriba

A5>10. En el área Valor_lógico2, escriba B5<5. Luego, haga clic en el botón
Aceptar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 208

 Observe que el resultado es, efectivamente, VERDADERO.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 209

 Autollene las fórmulas a las celdas siguientes (arrastre):

Guarde el archivo con el nombre de Funciones Lógicas 2.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 210

3.1.3 Función Lógica O()

Devuelve VERDADERO si alguno de los argumentos es VERDADERO; devuelve

FALSO si todos los argumentos son FALSOS.

Donde:

Valor_lógico1, Valor_lógico2,… son entre 1 y 255 condiciones que desea

comprobar y que pueden ser verdadero o falso; y que pueden ser valores lógicos,
matriciales o referencias.

En su forma anidada con la función SI, su sintaxis sería:

Si recuerda, en lógica, la disyunción se representaba a través del símbolo v y la

disyunción sólo es falsa si todos los componentes son falsos. En cualquier otro caso,
es verdadera. Aquí, se muestra la tabla de verdad para la disyunción que es,
exactamente, la misma regla aplicada en la función O().

p q p Vq

V V V

V F V

F V V

F F F

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 211

Los argumentos deben evaluarse como valores lógicos, como

VERDADERO O FALSO, o en matrices o referencias que contengan
valores lógicos.

Si un argumento matricial o de referencia contiene texto o celdas vacías,

dichos valores se pasarán por alto.

Si el rango especificado no contiene valores lógicos, O devolverá el valor

de error #¡VALOR!

Puede utilizar la fórmula matricial O para comprobar si un valor aparece

en una matriz. Para introducir una fórmula matricial, presione
CTRL+MAYÚS+ENTRAR

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 212

Ejemplo 1: Función O()

En el archivo anterior, agregue siguiente información en la columna D de la Hoja1:

A continuación, se evaluará si el valor A es mayor que 10 o si el valor B es menor que

5. La respuesta deberá ser VERDADERO según la tabla de verdad de la disyunción.

Procedimiento

 Seleccione la celda D5.

 Haga clic en el botón Insertar función de la barra de fórmulas.

 Seleccione la categoría Lógicas y la función O. Luego, haga clic en el botón

Aceptar.

 En el cuadro Argumentos de función, en el área Valor_lógico1, escriba

A5>10. En el área Valor_lógico2, escriba B5<5. Luego, haga clic en el botón
Aceptar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 213

 Observe que el resultado es, en efecto, VERDADERO.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 214

 Autollene la fórmulas a las celdas siguientes (arrastre):

Guarde el archivo con el mismo nombre de Funciones Lógicas 2.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 215

3.2 TEMA 9: FUNCIONES DE BÚSQUEDAS

Las funciones de búsqueda tienen tanto uso como las funciones lógicas. Esto se debe
a que permiten localizar información asociada a una fila o columna y, con ello, es
posible preparar reportes sencillos.

Puede insertar funciones de la categoría Búsqueda y Referencia,

utilizando la ficha Fórmulas y el grupo Biblioteca de Funciones.

3.2.1 Función BuscarV()

Realiza una búsqueda vertical en la primera columna de un rango o matriz y devuelve

el dato asociado de una columna especificada.

Utilice BUSCARV cuando los valores que desea localizar estén ubicados en una
columna situada a la izquierda de los datos que desea encontrar, es decir, se utiliza en
cuadros organizados verticalmente.

Donde…

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 216

Valor_buscado, es el valor que se busca en la primera columna de la matriz. El

Valor_buscado puede ser un valor, una referencia o una cadena de texto.

 Matriz_buscar_en, es la tabla de información donde se buscan los datos. Utilice

una referencia a un rango o un nombre de rango. Los valores de la primera
columna de matriz_buscar_en pueden ser texto, números o valores lógicos.

 Indicador_columnas, es el número de columna de matriz_buscar_en, desde la

cual debe devolverse el valor coincidente.

 Ordenado, es un valor lógico que especifica si BUSCARV debe localizar una

coincidencia exacta o aproximada. Si se omite o es VERDADERO, devolverá
una coincidencia aproximada. En otras palabras, si no localiza ninguna
coincidencia exacta, devolverá el siguiente valor más alto inferior a
valor_buscado. Si es FALSO, BUSCARV encontrará una coincidencia exacta. Si
no encuentra ninguna, devolverá el valor de error # N/A.

Antes de utilizar la función BUSCARV, se asignará el nombre de las

matrices o rangos a usarse, simplemente, para facilitar la comprensión
de la función.

Ejemplo 1: Función BuscarV()

En un archivo nuevo, escriba la siguiente información en la Hoja1.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 217

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 218

Procedimiento

 Definición de la matriz Artículos, seleccione el rango A6:E11 y asigne el

nombre de ARTÍCULOS.

 Escriba la siguiente información en la Hoja2:

 Utilizando la función BUSCARV, se mostrará el nombre del artículo asociado al

código ingresado. Para ello, seleccione la celda B4.

 Haga clic en el botón Insertar función de la barra de fórmulas.

 Seleccione la categoría Búsqueda y Referencia y la función BUSCARV.

Luego, haga clic en el botón Aceptar.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 219

 En el cuadro Argumentos de función, escriba en cada área la información tal

como se muestra:

Observe que el valor buscado es B3, porque este dato se encuentra en

la primera columna de la matriz llamada ARTICULOS. El indicador de
columnas es 2, porque el nombre del artículo asociado a este código se
encuentra en la segunda columna de la matriz. Ordenado está en 0,
porque la búsqueda que se necesita realizar debe buscar la coincidencia
exacta. Note que, también, podría usar, en lugar de 0, el valor lógico
FALSO.

 Luego, haga clic en el botón Aceptar y observe que el resultado será

Cuaderno 100h.

Actividad

En la Hoja2, utilizando la función BUSCARV, complete las funciones para hallar la

información de la Marca, Precio y Stock que, también, están asociados al código
ingresado en la celda B3.

Guarde el archivo con el nombre de Funciones de Búsquedas.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 220

Ejemplo 2: Función BuscarV()

En el archivo anterior, utilizando la función BUSCARV, complete la información que

corresponde al Mensaje teniendo en cuenta que depende del valor del Stock y será
buscado en la tabla o matriz MENSAJES.

Procedimiento

 Definición de la matriz Mensajes, en la Hoja1 seleccione el rango A21:B24 y

asigne el nombre de MENSAJES.

 En la Hoja2, complete la función de búsqueda con BuscarV en la celda D7 e

indique qué información escribió en cada argumento y por qué.

Argumento Valor Justificación o razón

Valor_buscado

Matriz_buscar_en

Indicador_columnas

Ordenado

Guarde el archivo con el mismo nombre de Funciones de Búsquedas.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 221

3.2.2 Función BuscarH()

Realiza una búsqueda horizontal en la primera fila de un rango o matriz y devuelve el

dato asociado de una fila especificada.

Utilice BUSCARH cuando los valores de comparación se encuentren en la primera fila

de una tabla de datos y desee encontrar información que se encuentre dentro de un
número especificado de filas. Utilice esta función en cuadros organizados
horizontalmente.

Donde:

 Valor_buscado, es el valor que se busca en la primera fila de la matriz.

Valor_buscado puede ser un valor, una referencia o una cadena de texto.

 Matriz_buscar_en, es la tabla de información donde se buscan los datos. Utilice

una referencia a un rango o un nombre de rango. Los valores de la primera fila
de matriz_buscar_en pueden ser texto, números o valores lógicos.

 Indicador_filas, es el número de fila de matriz_buscar_en desde la cual debe

devolverse el valor coincidente. Si indicador_filas es 1, devuelve el valor de la
primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la
segunda fila en matriz_buscar_en y así sucesivamente. Si indicador_filas es
menor que 1, BUSCARH devuelve el valor de error #¡VALOR!; si indicador_filas
es mayor que el número de filas en matriz_buscar_en, BUSCARH devuelve el
valor de error #¡REF!.

 Ordenado, es un valor lógico que especifica si BUSCARH debe localizar una

coincidencia exacta o aproximada. Si se omite o es VERDADERO, devolverá
una coincidencia aproximada. En otras palabras, si no localiza ninguna
coincidencia exacta, devolverá el siguiente valor más alto inferior a
valor_buscado. Si es FALSO, BUSCARH encontrará una coincidencia exacta. Si
no encuentra ninguna, devolverá el valor de error # N/A.

Ejemplo 3: Función BuscarH()

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 222

En el archivo anterior, utilizando la función BUSCARH, muestre el porcentaje de oferta

asociado a la Marca. Para ello, realice lo siguiente:

Procedimiento

 Definición de la matriz Marcas, en la Hoja1 seleccione el rango B15:F17 y

asigne el nombre de MARCAS.

 En la Hoja2, seleccione la celda B7.

 Haga clic en el botón Insertar función de la barra de fórmulas.

 Seleccione la categoría Búsqueda y Referencia y la función BUSCARH.

Luego, haga clic en el botón Aceptar.

 En el cuadro Argumentos de función, escriba en cada área la información tal

como se muestra:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 223

Observe que el valor buscado es B5, porque este dato se encuentra en

la primera fila de la matriz llamada MARCAS. El indicador de filas es
2, porque el porcentaje de oferta asociado a esta marca se encuentra en
la segunda fila de la matriz. Ordenado está en 0, porque la búsqueda
que se necesita realizar debe buscar la coincidencia exacta. Note que,
también, podría usar, en lugar de 0, el valor lógico FALSO.

 Luego, haga clic en el botón Aceptar y observe que el resultado será 0.05.

 Aplique formato de estilo porcentual para tener como resultado 5%.

Actividad

En la Hoja2, utilizando la función BUSCARH, complete la información del regalo que

está asociado a la Marca.

Guarde el archivo con el mismo nombre de Funciones de Búsquedas.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 224

Actividades

 En un archivo nuevo, guárdelo con el nombre Actividad y resuelva los

siguientes ejercicios:

1. Ejercicio 01: En la Hoja1, escriba la siguiente información:

En el campo OBSERVACIÓN, muestre el comentario “Máquina operativa” si se ha

utilizado la máquina; en caso contrario, deberá mostrar “Revisión Pendiente”.

El resultado será el siguiente:

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 225

2. Ejercicio 02: En la Hoja2, escriba la siguiente información:

Utilizando funciones lógicas, escriba “Aprobado” en la columna OBSERVACIÓN si

ambas notas son aprobadas y “Debe dar sustitutorio” si alguna es desaprobatoria.
Considere como mínima nota aprobatoria 13.

El resultado será el siguiente:

Agregue una columna llamada CURSO APROBADO que indique el nombre del curso
que obtuvo nota aprobatoria. Si fueran ambos cursos, deberá mostrar el texto “Aprobó
ambos cursos”.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 226

3. Ejercicio 03: En la Hoja3, escriba la siguiente información:

Utilizando las funciones más convenientes, complete la información faltante, tomando

en cuenta lo siguiente:

1. La fecha de compra debe ingresarse.

2. La forma de pago puede ser Contado o Crédito.

3. Si la forma de pago es al Contado, el % Oferta es de 20%; en caso

contrario, el % Oferta es 0.

4. El %IGV será del 19% si la fecha de compra es menor al 28/7/2010; en

caso contrario, será del 18%.

5. El Importe debe ingresarse y mostrarse con 2 decimales.

6. Deberá mostrar automáticamente la Oferta y el IGV correspondiente.

7. El Total es el importe menos la Oferta.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 227

8. Ejercicio 04: En la Hoja4, escriba la siguiente información:

Se tiene la lista de datos y se desea generar un pequeño reporte de tal modo que, al
ingresar un código a buscar, se muestren automáticamente la descripción y stock
asociados al código.

Asimismo, deberá mostrar una oferta del 15% sólo en el caso de los monitores; en
caso contrario, la oferta será del 5%.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 228

9. Ejercicio 05: En la Hoja5, escriba la siguiente información:

Diseñe un reporte con la información anterior, tomando en cuenta las siguientes

consideraciones:

10. El campo Id.Pedido deberá seleccionarse de una lista.

11. Al elegir un Id.Pedido, los datos del cliente, empleado, fecha de pedido,
forma de envío, Pedido $ y Local deberán mostrarse de manera
automática.

12. Agregue un campo llamado Comentario Especial. Este deberá mostrar

el mensaje correspondiente según la siguiente tabla:

Pedido $ Comentario Especial

< 2500 Revise historial del Cliente.

>=2500 Pedido en parámetros esperados.

13. También, agregue un campo llamado Descuento Local. Este deberá

mostrar 10%, en caso el local sea San Isidro o La Molina; si fuera Lima
o Callao, 15%; y en caso el local no exista, deberá mostrar el siguiente
mensaje: “No existe local”. Resuelva este ejercicio utilizando las
funciones vistas con anterioridad y que sean, estrictamente, necesarias.

CIBERTEC CARRERAS PROFESIONALES

INFORMÁTICA 229

Resumen
 Las funciones lógicas permiten evaluar condiciones y, según el resultado
VERDADERO o FALSO, se realiza algún tipo de cálculo, se muestra un
mensaje, etc.

 Las funciones lógicas son Y(), O(), SI(), estas pueden anidarse una con otra.

 Las funciones de búsquedas permiten ubicar un dato dentro de una tabla de

datos ya sea de forma vertical u horizontal.

 Las funciones de búsquedas son BuscarV() y BuscarH().

Puede revisar los siguientes enlaces para ampliar los conceptos vistos en este tema:

1. https://www.youtube.com/watch?v=Cpf1lLRxNCs
2. https://www.youtube.com/watch?v=5msevDUQz5c
3. https://www.youtube.com/watch?v=pmtQsENsaJg
4. https://www.youtube.com/watch?v=9HzqxonUeaA

CIBERTEC CARRERAS PROFESIONALES