MICROSOFT EXCEL

Avanzado
BASE DE DATOS

Guía del Estudiante: EXCEL AVANZADO - BASE DE DATOS

Obra de la Editorial EIGER
ESCUELA INTERNACIONAL DE GERENCIA
Calle Enrique Lembecke N° 145- San Isidro
Lima - Perú
Teléfono : 442-01-09
Web : [Link]
E-mail : gestionpedagogica@[Link]
Todos los derechos reservados. Prohibida la reproducción total
o parcial de esta obra por cualquier medio, sin la autorización
expresa de EIGER.
 INDICE
MS. EXCEL AVANZADO
BASE DE DATOS

UNIDAD DIDÁCTICA UNIDAD DIDÁCTICA

CREACIÓN DE DOCUMENTOS CONTABLES GESTIÓN DE HOJAS DE CALCULO
DIGITALES.

CAPACIDAD TERMINAL CAPACIDAD TERMINAL

Elabora documentos comerciales, inserta Crea tablas dinámicas y usa hipervínculos en
gráficos, usa fórmulas y funciones. documentos y/o formularios.

CRITERIOS DE EVALUACIÓN CRITERIOS DE EVALUACIÓN

• Ingresa y modifica datos en celdas, crea • Usa fórmulas para cálculos matemáticos y
y edita tablas, formatos de formularios y lógicos en facturas, usando hipervínculos.
facturas, inserta imágenes y crea gráficos. • Selecciona la información que resumirá
• Aplica fórmulas para cálculos básicos en la Tabla Dinámica de la base de datos
porcentuales y mercantiles de acuerdo a la e imprime.
operación comercial.

CAPITULO 1 CAPITULO 3
Contenidos: Contenidos:
• Referencias • Funciones de Búsqueda
• Selección de Rangos • Octava, Novena y Décima Práctica
• Funciones Estadísticas
• Primera, Segunda, Tercera y Cuarta CAPITULO 4
Práctica Contenidos:
• Ordenar datos
• Filtro de datos
• Subtotales
CAPITULO 2 • Funciones de base de datos
Contenidos: • Undécima, Duodécima y Décima Tercera
• Funciones de Fecha y Hora Práctica
• Funciones de Texto
• Funciones Lógicas CAPITULO 5
• Quinta, Sexta y Séptima Práctica Contenidos:
• Tablas Dinámicas
• Gráficos Dinámicos
• Décimo Cuarta y Décimo Quinta Práctica
 Capítulo 1
Nociones Generales
 Microsoft Excel - Avanzado
BASE DE DATOS

Introducción
En el presente material educativo complementaremos lo aprendido con anterioridad en el Libro de Excel I.
En la presente unidad haremos un breve recuento de los temas antes aprendidos.

Nociones Generales
Tipo de datos
En una hoja de cálculo, los distintos datos que podemos introducir son:
Valores Constantes: Es un dato que se introduce directamente en una celda. Puede ser un número,
una fecha u hora o un texto.
Fórmulas: Es una secuencia formada por: valores, constantes, referencias a otras celdas, nombres u
operadores. Es una técnica básica para el análisis de datos. Se pueden realizar diversas operaciones con
los datos de las hojas de cálculo como sumas, restas, multiplicaciones, etc.

Operadores
Símbolos que se utilizan para indicar una determinada acción. Esta acción puede ser una operación
matemática, una comparación, etc. Se clasifican en:
• Operadores matemáticos: suma(+), resta(-), multiplicación(*), división(/),
potenciación(^).
• Operadores de comparación: menor que (<), menor o igual que (<=), mayor que (>),
mayor o igual que (>=), igual que (=), diferente de (<>).
• Operador de concatenación: Ampersand(&).
• Operador de agrupación: paréntesis.
• Operadores lógicos: y, o, no.

Referencias
Son los enlaces a una determinada celda y que pueden ubicarse en cualquier hoja del libro de Excel. Las
referencias se utilizan preferentemente en una fórmula y/o función, en consecuencia los mismos toman
los valores que las celdas contienen. Existen tres tipos de referencia:

Referencia Relativa:
Son los tipos de referencia que se utilizan con mayor regularidad y que cambian si la fórmula, que los
contiene, se copia a otra celda.

En el primer cuadro multiplicamos la celda A2 POR 0.18 LA FORMULA LA INGRESAMOS EN la celda B2, en el segundo
CUADRO hemos copiado la fórmula a lo largo de la columna B, en el tercero vemos las fórmulas en detalle.

4
 Microsoft Excel - Avanzado
BASE DE DATOS

Referencia Absoluta:
Son las referencias que se utilizan cuando las celdas referenciales, que son parte de una fórmula, no
deben cambiar al copiar la fórmula a otra celda. Esta acción se logra al adicionar el símbolo dólar ($) antes
de la etiqueta de la columna y la etiqueta de la fila.

Esta vez estamos utilizando la celda B1 para multiplicar a cada uno de los precios y así obtener el I.G.V.,
como la vamos a utilizar en todos los precios, utilizamos el signo $, creando así la referencia.

Referencia Mixta:
Es la mezcla entre la referencia relativa y la referencia absoluta (total). Es el nombre que le asignan algunos
autores a la referencia absoluta vertical y a la referencia absoluta horizontal. Usualmente estas referencias
son cuando se “fijan” (digitar el $) las filas y no la columna de la referencia (referencia absoluta horizontal)
o cuando se fija la columna y no a la fila (referencia absoluta vertical).

En este ejemplo se utilizan la

proyección de inflación con los
precios e incrementamos con
su mismo precio para obtener
el precio incrementado de
acuerdo a su inflación.

5 5
 Microsoft Excel - Avanzado
BASE DE DATOS

Referencia a otra Hoja:

Para hacer referencia a celdas de otras hojas debemos indicar el nombre de la hoja seguido del signo de
exclamación y el nombre de la celda.
Hoja2!D2 Esta referencia está vinculando a la celda D2 de la hoja Hoja2.
Planilla2012!E3 Esta referencia está vinculando a la celda E3 de la hoja Planilla2012.

Referencia a otro Libro:

Para hacer referencia a celdas de otros libros debemos indicar el nombre del libro entre corchetes,
seguido del nombre de la hoja seguido del signo de exclamación y el nombre de la celda.
Esta referencia indica que la celda se encuentra en el archivo
[presupuesto2012]Hoja1!E3 o libro de Excel llamado Presupuesto2012, en la Hoja1 y en la
celda E3.

Selección de Rangos
Direcciones de celda:
La dirección de una celda está constituida por el nombre de la página a la cual pertenece, seguida de
la letra de la columna y el número de la fila en la cual se encuentra. Por ejemplo:

Hoja1!F4 Celda que se encuentra en la columna F, fila 4 de la hoja de cálculo Hoja1.

Enero!G16 Celda que se encuentra en la columna G, fila 16 de la hoja de cálculo Enero.
A9 Celda que se encuentra en la columna A, fila 9 de la hoja de cálculo actual.
Para hacer referencia a celdas que estén en la misma hoja de cálculo, no es necesario escribir el nombre
de la hoja, pues esto sería redundante.

Direcciones de bloque de celda:

Un bloque de celda es un conjunto de celdas adyacentes que forman una región rectangular. La dirección
de un bloque se obtiene haciendo referencia a la dirección de una celda que esté en una esquina del
bloque, y la dirección de la celda que está en la esquina. Ambas direcciones deben ir separadas por el
símbolo “:” (dos puntos ortográficos”. Por ejemplo:

A4:B6 Bloque de celdas de dos columnas y tres filas.

B8:B12 Bloque de celdas de una columna y cuatro filas.
A7:A7 Bloque de celdas de una sola celda.

Operadores de Referencia:
Microsoft Excel usa dos puntos (B12:C36) para designar un rango. Se pueden usar comas para
seleccionar rangos múltiples (B12:C36, F14:H26) en muchas funciones.

Crear nombres de rango a través de cuadro de nombres:

Las fórmulas y las funciones en muchas ocasiones hacen mención a celdas o también a rangos de celda.
Cuando una celda o un rango de celdas es mencionado varias veces en nuestras fórmulas, entonces lo
recomendable es darle un nombre a este rango, y de allí en adelante en vez de escribir las coordenadas
de celda, se escribe el nombre de rango que se ha asignado. Una vez nombrado un rango, este podrá ser
usado de allí en adelante en cualquier fórmula o en cualquier orden de menú de Excel cuando se tenga
que hacer referencia a ese rango.

6
 Microsoft Excel - Avanzado
BASE DE DATOS

Asignar nombre a una celda

A continuación vamos a asignar nombres en la siguiente tabla que se muestra, para esto hacemos lo
siguiente:
1. Escribir los siguientes datos en una nueva hoja de cálculo.

2. Asignar nombre “CarneCal” a la celda B4. Para eso ubicarse en la celda B4, hacer clic en la
pestaña Fórmula, luego a la opción Asignar nombre a un rango y luego a Definir nombre…,
tal como se muestra en la figura.

Seleccionamos primero la celda y luego

1 nos vamos a la pestaña Fórmula.

3. Aparecerá el siguiente cuadro de diálogo, escribir un nombre para la celda, en este caso es
CARNECAL, y luego darle clic en Aceptar.

Terminado de asignar el nombre le

damos clic en Aceptar.

4. Para hallar cuantas calorías hay en 100 Gr. de carne de res y carne de pollo la fórmula sería la
siguiente:
Celda B10: =B5+B6

Pero en adelante vamos a utilizar los nombres de rangos definidos, entonces la fórmula sería la
siguiente:

Celda B10: =PolloCal + CarneCal

Y así sería si deseamos saber la cantidad de grasas o de proteínas.

7 7
 Microsoft Excel - Avanzado
BASE DE DATOS

Asignar nombre a rango de celda

Vamos ahora a trabajar con rangos de celdas, para esto vamos a tomar el ejercicio anterior, y hacemos lo
siguiente:

1. Asignar nombre “CALORIAS” al rango de celdas B4:B6, seleccionamos primero el bloque de

celdas B4:B6 y repetimos los pasos para asignar nombre a las celdas. Tal como nos muestra la
siguiente figura.

Seleccionamos primero el rango

celda y luego nos vamos a la pestaña
1 Fórmula.

2. En la ventana que nos aparece, escribir el nombre para el rango de celdas y luego clic en Aceptar.

Asignar el nombre CALORIAS y luego

clic en Aceptar.

3. Realizar el mismo procedimiento para asignar nombre al rango de celdas..

Rango Nombre
C4:C6 PROTEINAS
D4:D6 GRASAS

4. Completar la tabla con los siguientes datos.

8
 Microsoft Excel - Avanzado
BASE DE DATOS

5. Ahora resolvemos el siguiente problema. Por ejemplo si deseamos saber el total de calorías al
consumir 300 gramos de carne, pollo y pescado, la fórmula sería la siguiente.

Celda B15: =SUMA(B4:B6)*3

Pero en adelante vamos a utilizar los nombres de rangos definidos, entonces la fórmula sería la
siguiente:
Celda B15: =SUMA(CALORIAS)*3

6. Al resolver toda la tabla quedaría así.

1
2 Contenido nutricional de ciertas carnes (en 100 Gr.)
3 Kcal Proteína Grasa
4 Carne de res 250 27.2 16.3
5 Pechuga de pollo 145 25.4 6.3
6 Pescado 100 18 1
7
8 Si consume 300 gramos
9 Nutriente Total
Los nombres que se le asignen a los
10 Calorías =SUMA(CALORIAS)*3 rangos son referenciales, pueden ser el
11 Proteínas =SUMA(PROTEINAS)*3 que más creamos conveniente.
12 Grasa =SUMA(GRASAS)*3
13

Crear nombres de celdas o rangos desde el cuadro de nombres

Es mucho más fácil crear nombre de rango desde el cuadro de nombres; para hacerlo hacemos lo
siguiente:

1. Vamos a agregar una columna más (E) y vamos a escribir carbohidratos y colocamos los valores
que se ven a continuación.
2. Seleccionar desde E4 hasta E6, clic en el cuadro de nombres y escribir el nombre a asignar
(CARBOHIDRATOS), luego presionar ENTER.

En el cuadro de nombres escribir Se agregan estos datos y

CARBOHIDRATOS y luego luego se seleccionan los datos
presionar Enter. agregados.

9 9
 Microsoft Excel - Avanzado
BASE DE DATOS

Primera Práctica
1. Ingresar los siguientes datos en una hoja de cálculo a la cual llamaremos PROYECCIONES DE PESCA.

PESQUERA EXALMAR S.A.A.

AÑOS
ESPECIE
2006 2007 2008 2009 2010 2011 2012 2013 2014
Salmón 1778
Trucha 17881822
Cangrejo 110910274
Mariscos 37542701
Pulpo 20450
Anchoveta 50880112
Merluza 88235623
14% 10% -5% -2% 10% 8% -12% 7%

2. Ingresar los siguientes datos en una hoja a la cual llamaremos RESUMEN.

% POR ESPECIE

DE PESCA

3. Calcular lo siguiente y escribir la fórmula que está empleando al costado. Trabajar con selección de rangos.
a. En la hoja PROYECCIONES DE PESCA, calcule las proyecciones, obteniendo el porcentaje que se pide más la
cantidad original, hacer año por año.
PROYECCIONES DE PESCA! C5:
PROYECCIONES DE PESCA! D5:
PROYECCIONES DE PESCA! E5:

b. En la Hoja RESUMEN, calcular la suma de cada especie. Utilice nombre de rangos.

Resumen!B4:
c. En la hoja RESUMEN, calcular el promedio de pesca de cada especie.
Resumen!C4:
d. En la hoja RESUMEN, calcular la suma total de pesca y el promedio total.
Resumen!B11:
Resumen!C11:
e. En la hoja RESUMEN, calcular el porcentaje que representa el Total de Pesca de cada especie con respecto al
TOTAL FINAL.
Resumen!D4:
Resumen!D5:

10
 Segunda Práctica 3. Hallar lo siguiente y escribir al costado
la función y/o fórmula empleada:

1. Ingresar los siguientes datos en una hoja de cálculo a la cual llamaremos INGRESO2013. a. EL TOTAL DE VENTAS, sumar todas las
Control de Ventas 2011 ventas de los trimestres:
_____________________________

b. El STOCK FINAL, es la diferencia del

STOCK INICIAL y el TOTAL DE VENTAS:

______________________________

c. Asignar un nombre a cada rango de

venta.
BASE DE DATOS

d. El TOTAL DE VENTAS (C7) es la

referencia de la hoja INGRESO2011.

e. EL PRECIO DE VENTAS, es el 132% del

PRECIO COSTO:

______________________________
Microsoft Excel - Avanzado

f. El INGRESO BRUTO, es el TOTAL DE

VENTAS multiplicado por el PRECIO
VENTA:
2. Ingresar los siguientes datos en una hoja a la cual llamaremos RESUMEN2013
______________________________

g. La GANANCIA, es el 32% del PRECIO

COSTO multiplicado por el TOTAL DE
VENTAS:

______________________________

h. El I.G.V., es el 18% del INGRESO BRUTO:

______________________________

i. El INGRESO NETO, es la suma del

INGRESO BRUTO menos el I.G.V.:

______________________________

11
11
 Microsoft Excel - Avanzado
BASE DE DATOS

Funciones Estadísticas
Operadores
En el curso de Excel I se han tocado las siguientes funciones estadísticas:
• CONTAR
• CONTARA
• [Link]
• MAX
• MIN
• MODA
• PROMEDIO
• [Link]
• [Link]
• [Link]

Los argumentos, usos, aplicaciones y ejercicios se ha visto y desarrollado en el curso de Excel I, ahora se
expondrá dos nuevas funciones estadísticas y sus respectivos usos.

[Link]:
Esta función cuenta el número de celdas en uno o más rangos que pueden tener uno o más criterios.

Sintaxis:
=[Link](rango1, criterio1, rango2, criterio2, …)

Donde:
Rango1: es el primer rango de celdas a usar para contar.
Criterio1: el primer criterio, ingresado como texto, que determina que es lo que debe contar, el
primer criterio sólo afecta al primer rango.
Rango2: es el segundo rango de celdas a usar para contar.
Criterio2: es el segundo criterio, ingresado como texto, de que determina que es lo que debe
contar, el segundo criterio sólo afecta al segundo rango.

[Link]:
Esta función suma celdas de uno o más rangos que tienen uno o más criterios.

Sintaxis:

=[Link](rango_suma, rango1, criterio1, rango2, criterio2, …)

Donde:
Rango_ suma: es el rango del cual los valores a sumar son tomados. Excel suma solo aquellas
celdas que corresponden al criterio donde se van a tomar.
Rango1: el primer rango de celdas que se van a usar para los criterios de suma.
Criterio1: es el primer criterio, ingresado como forma de texto, que determina que celdas va a
sumar. Excel aplica los criterios del rango1.
Rango2: es el segundo rango de celdas que se van a usar para los criterios de suma.
Criterio2: es el primer criterio, ingresado como forma de texto, que determina que celdas va a
sumar. Excel aplica los criterios del rango2.

12
 Microsoft Excel - Avanzado
BASE DE DATOS

Ejercicio de aplicación con funciones estadísticas

Veamos ahora un ejemplo para poder entender mejor estas dos funciones.

1. Vamos a ingresar los siguientes datos en una hoja de cálculo.

2. Vamos a calcular lo siguiente, calculamos el Total de Hombres (C21) y el Total de Mujeres (C22),
con la función [Link] podemos calcular estos montos. Posteriormente vamos a calcular
cuántos hombres viajan por TACA, aquí vamos a utilizar la función [Link], nos
ubicamos en la celda C24 y escribimos lo siguiente, el resultado será tal y como se muestra a
continuación.

=[Link](C5:C18,”M”)

=[Link](C5:C18,”F”)

=[Link](C5:C18,”M”,D5:D18,” TACA”)

3. Lo mismo vamos a realizar para contar cuántos hombres viajan por Iberia, es la misma fórmula
sólo cambiamos los criterios.

13 13
 Microsoft Excel - Avanzado
BASE DE DATOS

4. Ahora vamos a calcular cuánto ha sido el ingreso por TACA (F21) y por LAN (F22), en esta
oportunidad la función [Link] ayudará a calcular los montos. Posteriormente vamos a
calcular el monto de TACA por Vuelos Nacionales, aquí vamos a utilizar la función SUMAR.
[Link], nos ubicamos en la celda F24 y escribimos lo siguiente, el resultado será tal y
como se muestra a continuación.

=[Link](D5:D18,” TACA”,F5:F18)

=[Link](D5:D18,”LAN”,F5:F18)

=[Link](F5:F18,D5:D18,” TACA”,E5:E18,”NACIONAL”)

5. Lo mismo vamos a realizar para calcular el monto de ingreso de LAN por vuelos nacionales,
sólo cambiamos los criterios. Al final el resultado es el que se muestra.

6. En las funciones [Link] y [Link] no importa el orden de los

criterios, no hay una importancia en determinar quién es el primer, segundo o tercer criterio,
según sea el caso.

14
 Microsoft Excel - Avanzado
BASE DE DATOS

Tercera Práctica
1. Ingresar los siguientes datos en la siguiente hoja de cálculo.

2. Calcular lo siguiente y escribir la función e. Utilizar la función [Link],

que va a emplear: para calcular las cantidades que se piden
a. El SUBTOTAL se calcula multiplicando la desde la celda I20 hasta la celda I23.
CANTIDAD por el PRECIO. I20:
I5: I21:
b. El I.G.V. se calcula sacando el 18% al I22:
SUBTOTAL. I23:
J5: f. Utilizar la función [Link]
c. El NETO se calcula sumando el SUBTOTAL para calcular los montos que se piden
más el I.G.V. desde la celda I25 hasta I28.
K5: I25:
d. Desde la celda C20 hasta la celda C23 I26:
calcular las cantidades que se piden con la I27:
función [Link].
C20:
C21:
C22:
C23:

15 15
 Microsoft Excel - Avanzado
BASE DE DATOS

Cuarta Práctica

1. Ingresar los siguientes datos en la siguiente hoja de cálculo.

2. Calcular los siguientes datos y escribir la función que está utilizando.

a. Calcular el PROMEDIO excluyendo la mínima nota.
G6:
b. Calcular la Mensualidad según el cuadro que se muestra. Utilizar función SI.
H6:
c. Calcular el Total de Hombres:
d. Calcular el Total de Mujeres:
e. Calcular el Total de Hombres en Excel:
f. Calcular el Total de Mujeres en Access:
g. Calcular el Total de Hombres aprobados en Windows:
h. Calcular cuánto pagan los hombres en Windows:
i. Calcular cuánto pagan las mujeres en Excel:
j. Calcular cuánto pagan los aprobados en Access:
k. Calcular cuánto pagan los aprobados en Windows:
l. Hallar los respectivos porcentajes.

Nota: para obtener el porcentaje, se debe dividir el Total de Hombres entre la cantidad
Total de Personas. Hacer lo mismo para cada porcentaje.

16
Microsoft Excel - Avanzado
BASE DE DATOS

Capítulo 2
Funciones Lógicas
Fecha, Texto y Lógicas

17 17
 Microsoft Excel - Avanzado
BASE DE DATOS

Funciones de Fecha y Hora

En Excel I vimos como utilizar las siguientes funciones de fecha y hora como por ejemplo:
• AHORA
• HOY
• AÑO
• MES
• DIA
• DIASEM
• DIAS360
Se aprenderá en esta sección a interactuar con las fechas, así se podrá manejar sumar días a una fecha o
restar dos fechas para hallar el intervalo de días entre ellas.

Operando con fechas

Al sumar números a una celda que contenga fechas, Excel considera que se suman días y el resultado se
representa con formato de fecha.

En el siguiente ejemplo se pide calcular el VENCIMIENTO de un pago determinado a partir de una fecha
dada. Para realizar ésto se suma a la FECHA DE PRESTAMO el NUMERO DE DIAS. La fórmula a utilizar y el
resultado se muestran a continuación.

En la celda C3 se
coloca la siguiente
fórmula: =A3+B3

En el siguiente ejemplo se desea incrementar a la FECHA DE PRÉSTAMOS las SEMANAS para calcular el
VENCIMIENTO. La fórmula a utilizar y el resultado se muestran a continuación.

En la celda C11 se
coloca la siguiente
fórmula: =A11+B11*7

18
 Microsoft Excel - Avanzado
BASE DE DATOS

Nos aparecen datos que no tienen relación con la fecha, el resultado mostrado no es erróneo, es más,
es correcto; sólo que hay que darle el formato correcto, cambiamos el formato a FECHA CORTA y esto
es lo que se muestra a continuación.

En la pestaña Inicio, en la sección Número, desglosamos las opciones de formato y

seleccionamos Fecha corta, dando como resultado la fecha correcta.

[Link]:
Para sumar meses se está considerando 30 días a cada mes. Ésto se aplica en algunos campos, sobre
todo en contabilidad. Para sumar meses exactos se utiliza ésta función.

Sintaxis:

=[Link](Fecha_inicial, meses)

En el caso que quiera sumar meses, se puede considerar que cada mes tiene 30 días. La fórmula a
utilizar y el resultado se muestran a continuación.

FECHA
PRÉSTAMO MESES VENCIMIENTO
19
20 02-jul 5 02/12/2012
21 07-jul 2 07/12/2012
22 05-jul 5 05/12/2012
23 06-jul 2 06/12/2012
24 03-jul 1 03/12/2012
25
En la celda C20 se coloca la
siguiente fórmula:
=[Link](A20,B20)

19 19
 Microsoft Excel - Avanzado
BASE DE DATOS

Excel permite restar celdas que contengan fechas, de esa forma se podrá calcular el intervalo de días
entre dos fechas.
A continuación se pide calcular los días de atraso de una fecha determinada. La fórmula a utilizar así
como el resultado se muestra a continuación.

En la celda F3 se coloca la
siguiente fórmula:
=D3-E3

Calculando edades

Una de las aplicaciones de las funciones de fecha es calcular la edad de una persona. En el siguiente
ejercicio veremos cómo se logra esto.

1. Ingresar los siguientes datos en una hoja de cálculo.

2. Vamos a calcular la Edad de estas personas, para esto nos colocamos en la celda C3 y escribimos
la siguiente fórmula. El resultado es el siguiente.

En la celda C3 se coloca la
siguiente fórmula: =HOY()-B3
Utilizamos la función HOY
para capturar la fecha actual
del sistema y la restamos con
la fecha de nacimiento.

20
 Microsoft Excel - Avanzado
BASE DE DATOS

3. El resultado que nos aparece son los días que tenemos hasta la fecha. Para convertir el resultado
a años, modificamos la fórmula de la siguiente manera, y el resultado es el siguiente.

En la celda C3 se coloca
la siguiente fórmula:
=(HOY()-B3)/365

4. Finalmente, deseamos quedarnos con la parte entera de este resultado, para esto utilizaremos
la siguiente función y el resultado es el siguiente.

En la celda C3 se coloca
la siguiente fórmula:
=ENTERO((HOY()-B3)/365)

Utilizamos la función ENTERO porque sólo queremos la parte entera de la división.

Funciones de Texto
En Excel I se tocaron las siguientes funciones de texto:
• Concatenar
• Derecha
• Extrae
• Izquierda
• Largo
• Mayusc
• Minusc
• NomPropio

Ahora tocaremos tres nuevas funciones de texto.

21 21
 Microsoft Excel - Avanzado
BASE DE DATOS

Largo:
Función que devuelve el número de caracteres de su argumento (Texto).
Sintaxis:
=LARGO(Texto)

Repetir:
Función que permite repetir una cadena de caracteres (Texto) un número (número_de_veces)
determinado de veces.

Sintaxis:

=REPETIR(Texto, número_de_veces)

En la celda H6 se calcula que el

texto Jorge se repita 4 veces.

Hallar:
Función que permite determinar la ubicación de un carácter de una cadena de texto dentro de otra
cadena de texto, de modo que pueda utilizar la función EXTRAE para cambiar el texto.

Sintaxis:
=Hallar(texto_buscado, dentro_del_texto,[Posición_inicial_búsqueda])

En el primer recuadro hemos hallado la posición de la letra p, mientras que en el segundo

recuadro hemos calculado la posición de la letra v, de la misma forma se realiza para las demás
opciones, siguiendo la lógica de las dos primeras.

22
 Microsoft Excel - Avanzado
BASE DE DATOS

Quinta Práctica
1. Ingresar los siguientes datos en dos hojas de cálculo.

Código: Será igual al primer carácter del apellido mas los 2 últimos caracteres del nombre
mas un guión y el sexo
T. Servico: será en años (teniendo en cuenta la fecha que ingreso a trabajar y el año actual)
Sueldo: A todo el personal casado con hijos 1700, al resto 1500
Bonif: A los varones 20% del sueldo por hijos, al resto 15% del sueldo
Escol: Será de 30% del sueldo al personal con más de 8 años de servicio al resto 20%
Dcto: 6% del sueldo al personal que no sea soltero, al resto 8%
Neto: Hallar el neto redondeado a cero decimales
Hallar El promedio de edades de los varones
Suma De sueldo de los casados
Número De personas sin hijos
Promedio De Bonificación de los solteros

2. Para el segundo cuadro:

Para calcular el SEXO (D6), se debe extraer esta información del código, en este caso es
la última letra la que me indica que sexo es la persona. Lo mismo se hará con el NIVEL
(penúltima), GRADO (antepenúltima), etc.
En la celda H6 concatenar el Nombre y los Apellidos.
En I6 calcular la longitud del nombre de H6.

23 23
 Microsoft Excel - Avanzado
BASE DE DATOS

Funciones Lógicas
Estas funciones son importantes ya que permiten determinar la realización de una determinada acción
en base al resultado lógico (VERDADERO/FALSO) obtenido.
Entre las funciones lógicas más utilizadas tenemos:

SI:
Nos permite realizar una pregunta lógica, la cual pueda tener dos posibles resultados, Verdadero o
Falso; y actuar de una u otra forma según la respuesta obtenida.

Sintaxis:
=Si(prueba_lógica, [valor_si_verdadero],[valor_si_falso])
La función SI, ha sido vista en el libro de Excel I, a continuación se mostrará un ejercicio simple para
comprenderla mejor. Lo que se busca en este ejercicio es que nos muestre si el alumno está aprobado
o desaprobado de acuerdo a su nota, para estar aprobado se necesita como mínimo 10.5, por lo tanto
se escribe la siguiente condición.

En la celda C2 se coloca la función: =SI(B2>=10.5, “Aprobado”, “Desaprobado”), dando como

resultado lo que se muestra en el recuadro de la derecha.

Y:
Esta función suele utilizarse conjuntamente con la función Si. Nos permite realizar en lugar de una
pregunta, varias a la vez. Y sólo se devolverá el argumento verdadero cuando todas sus condiciones sean
verdaderas, basta que una sea falsa para que todo se convierta a falso.

Sintaxis:
=Y([valor_lógico1],[valor_lógico2],[valor_lógico3],…)
En el siguiente ejemplo se va a evaluar dos números y preguntar si ambos son mayores a 10, si ambos
números son mayores a 10 arrojará verdadero; caso contrario, arrojará falso.

En la celda C12 se coloca la función: =Y(A12>10,B12>1),

dando como resultado lo que se muestra en el recuadro de la derecha.

24
 Microsoft Excel - Avanzado
BASE DE DATOS

O:
Esta función se suele utilizar conjuntamente con la función SI. Con ella también se podrá hacer varias
preguntas dentro de la función SI. O sólo devolverá el argumento falso cuando todas sus condiciones
sean falsas, basta que una sea verdadera para que sea verdadero todo.
Sintaxis:
=O([valor_lógico1],[valor_lógico2],[valor_lógico3]…)
En el siguiente ejemplo es el mismo que se ha expuesto para la función, sólo que se ha cambiado la
función. Arrojará verdadero si uno de los dos es verdadero, caso contrario arrojará falso.

En la celda C12 se coloca la función: =O(A12>10,B12>10),

dando como resultado lo que se muestra en el recuadro de la derecha.

No:
Invierte el valor lógico, por ejemplo si el valor lógico es VERDADERO, la función NO devuelve FALSO.
Sintaxis:
=NO (Valor_Lógico)

ESBLANCO:
Esta función devuelve el valor lógico VERDADERO si el valor de una celda es vacía, de lo contrario devuelve
FALSO. Usaremos esta función para evitar errores si alguna de las celdas de una fórmula está vacía.
En el siguiente ejemplo veamos como la función ESBLANCO trabaja conjuntamente con la función SI.

En este ejercicio aparente no hay ningún

problema, para hallar el monto en dólares
se divide la celda C5 con la celda C3.

Si se borra la información en la celda C3,

nos aparecerá el error en la celda C7, para
contrarrestarlo, se utilizará la función
ESBLANCO.

En la celda C7 se coloca la siguiente función:

=SI(ESBLANCO(C3),0,C5/C3)
y de esta manera habremos solucionado
el problema.

25 25
 Microsoft Excel - Avanzado
BASE DE DATOS

Ejercicio de aplicación funciones lógicas

1. Ingresar los siguientes datos en dos hojas de cálculo.

2. Obtenemos la Mensualidad de la siguiente manera: si la especialidad es Contabilidad la

mensualidad será 600 soles y si es Administración 700 soles. Nos ubicamos en la celda H3 y
escribimos la siguiente función.

H3: =SI(H3=”Contabilidad”;600;700)

3. Los resultados de las Mensualidades se mostrarán de la siguiente manera:

4. Calculemos el Descuento por Categoría, para ello tendremos en cuenta estas primeras dos
condiciones: si la especialidad es contabilidad y la categoría es A, el descuento será de 10% de
la mensualidad; si la especialidad es contabilidad y la categoría es B, el descuento será de 15%
de la mensualidad. Para este caso utilizamos las funciones SI e Y de la siguiente manera:

26
 Microsoft Excel - Avanzado
BASE DE DATOS

5. Con la función SI generamos las condiciones y la función Y nos permite evaluar dos criterios a
la vez.

6. Agregaremos las dos últimas condiciones: si la especialidad es Administración y la categoría

es A será el 12% de la mensualidad; si la especialidad es Administración y la categoría es B el
descuento será de 13%. Toda la función será como se muestra.

I3: =SI(Y(F3=”Contabilidad”;G3=”A”);H3*0.1;SI(Y(F3=”Contabilidad”;G3=”B”);H3*0.15;
SI(Y(F3=”Administración”;G3=”A”);H3*0.12;H3*0.13)))

7. Los resultados quedan de la siguiente manera.

8. Calculemos el aumento especial, para ello tendremos en cuenta las siguientes condiciones.
Si es de la categoría A y gana 600 soles se le dará un 5% de aumento; si es de la categoría B y
gana 600 soles se le dará 4%; si gana 700 sin importar la categoría se le dará 3%. Para este caso
utilizaremos las funciones SI y O. Tal como se muestra de la siguiente manera.

J3 =SI(O(G3=”A”;H3=600);H3*0.05;SI(O(G3=”B”;
H3=600);H3*0.04;SI(H3=700; H3*0.03;0)))

27 27
 Microsoft Excel - Avanzado
BASE DE DATOS

9. Los resultados se mostrarán de la siguiente manera.

10. Finalmente obtendremos el Monto a Pagar restando la Mensualidad con el Descuento por
categoría y sumándole el Aumento Especial.

K3 =H3-I3+J3

11. El resultado general de su tabla será el siguiente. Agregar los formatos de contabilidad para
que se observe una mejor presentación.

28
 Microsoft Excel - Avanzado
BASE DE DATOS

Sexta Práctica
1. Ingresar los datos faltantes.

29 29
30
Sétima Práctica
1. Ingresar los siguientes datos en dos hojas de cálculo.
BASE DE DATOS
Microsoft Excel - Avanzado

Se deben jalar estos datos de la Hoja de la Base de Datos

 Microsoft Excel - Avanzado
BASE DE DATOS

BASE DE DATOS

PRÁCTICA DIRIGIDA
ESTUDIANTE : .....................................................................................................................................................................
DOCENTE :.................................................................................. BONO N°: ........................................................
FRECUENCIA :................................................................................. HORARIO: ......................................................

1. Completar los siguientes enunciados:

• Para aplicar a una fórmula una referencia absoluta, pulsamos la tecla de función ..........
• Para iniciar a escribir una fórmula o función, iniciamos con el signo .................
• Al aplicar a una fórmula una referencia absoluta, se agrega el símbolo ..................
• Para mostar la ventana de Administrador de nombres, se pulsa las teclas ......................

2. Resuelva las siguientes operaciones usando referencias:

• En la hoja3, sumar el contenido de la celda A5(hoja 1) con la celda A5(hoja 2)

• En la hoja3, obtener el 10% de la celda A12(hoja 2)

• En la hoja3, sumar de la hoja1(las celdas de B2 hasta B12)

• En la hoja3, sumar de la hoja1(C4 al C17) más hoja2 (C4 al C17)

3. Calcular: (Utilice el cuadro de la página 13 “Aeropuerto Internacional Jorge Chávez”)

• Cuantos hombres viajan a destino Internacional

• A Cuantos hombres les costo el pasaje más de 200 soles.

31 31
32
•
•

Observaciones:
A.

B.
BASE DE DATOS

4. Resolver el siguiente cuadro:

Cuantos vuelos Nacionales de TACA

C.
Microsoft Excel - Avanzado

D.
Calcular el monto de LAN por vuelos Nacionales

E.

A. .....................................................................................................................................................................
B. .....................................................................................................................................................................
C. .....................................................................................................................................................................
D. .....................................................................................................................................................................
E. ......................................................................................................................................................................

...............................................................................................................................................................................................
Microsoft Excel - Avanzado
BASE DE DATOS

Capítulo 3
Funciones Búsqueda

33 33
 Microsoft Excel - Avanzado
BASE DE DATOS

Funciones de Búsqueda
Son las funciones que nos permiten la búsqueda de datos dentro de una hoja de cálculo.
Entre las funciones más utilizadas tenemos:

Buscar: Devuelve un valor de un rango, de una fila o una columna, o hasta de una matriz. La función
BUSCAR tiene dos formas de sintaxis: la forma vectorial y la matricial.
Sintaxis Vectorial: Un vector es un rango de una sola fila o columna. La forma vectorial de BUSCAR busca
un valor en un rango de una columna o una fila (denomina vector) y devuelve un valor desde la misma
posición en un segundo rango de una columna o una fila. Use esta forma de la función BUSCAR cuando
desee especificar el rango que incluya los valores que desea buscar. La otra forma de la función BUSCAR
busca automáticamente en la primera columna o fila.
Sintaxis:
=BUSCAR(valor_buscado, vector_de_comparación,[vector_resultado])

Veamos el siguiente ejemplo, se quiere que en la columna observación se muestre el texto que
corresponda a su estado civil.

Nos ubicamos en la celda C4 y escribimos lo siguiente: =BUSCAR(B4,$E$4:$E$6,$F$4:$F$6)

Donde:
B4: Es el dato que se buscará en el rango de comparación.
E4:E6 Es el rango de comparación para la búsqueda.
F4:F6 Es el rango de resultado de nuestra búsqueda al cumplirse una condición.
Se coloca el signo $ como referencia absoluta, puesto que, al momento de jalar la función, no ocasione
ninguna dificultad al mostrar los resultados.

Los datos en la Tabla de Valores tienen que estar ordenados

alfabéticamente para que la función trabaje con normalidad.

34
 Microsoft Excel - Avanzado
BASE DE DATOS

Sintaxis Matricial: La forma matricial de BUSCAR busca el valor especificado en la primera fila o columna
de una matriz y devuelve un valor de la misma posición en la última fila o columna de la matriz. Use esta
forma de BUSCAR cuando los valores que desea buscar están en la primera fila o columna de la matriz.
Use la otra forma de BUSCAR cuando desea especificar la ubicación de la columna o fila.

Sintaxis:
=BUSCAR(valor_buscado, matriz)
Del mismo ejemplo anterior, nos ubicaremos en la celda C4 y empleamos la siguiente fórmula:

=BUSCAR(B4,$E$4:$F$6)

Donde:
B4 es el dato que se buscará en el rango comparación.
$E$4 es el rango resultado de nuestra búsqueda al cumplirse una condición (primera columna del rango).
$F$6 es el rango de resultado de nuestra búsqueda (última columna del rango).

Funciones BUSCARH y BUSCARV

BUSCARH:
Busca un valor en la fila superior de una tabla o una matriz. Use BUSCARH cuando los valores de comparación
se encuentren en una fila en la parte superior de una tabla de datos y desee encontrar información que se
halle dentro de un número especificado de filas. La H de BUSCARH significa “horizontal”.

Sintaxis:
=BUSCARH(valor_buscado, rango_datos, indicar_filas, [ordenado])

Veamos cómo trabaja la función BUSCARH, para eso escribimos lo siguiente en una hoja de cálculo.

35 35
 Microsoft Excel - Avanzado
BASE DE DATOS

Se desea mostrar los precios de cada producto, estos precios lo vamos a obtener de la matriz que se
encuentra desde E3 hasta H4. Nos ubicamos en la celda C2 y escribimos lo siguiente.

=BUSCARH(B2,$E$3:$H$4,2,FALSO)

Donde:
B2 corresponde al dato buscado.
$E$3:$H$4 es el rango de búsqueda.
2 es la fila donde encontraremos el resultado.
FALSO indica si es los datos en la matriz no están ordenados alfabéticamente, cómo es el caso de este
ejemplo, si los datos en la fila 3 desde E hasta H hubieran estado ordenados, se omite el FALSO.

Al pulsar la tecla ENTER y al jalar hacia las demás celdas, se obtiene lo siguiente.

BUSCARV:
Puede usar la función BUSCARV para buscar la primera columna de un rango de celdas y, a continuación,
devolver un valor de cualquier celda de la misma fila del rango.
Sintaxis:
=BUSCARV(valor_buscado, rango_datos, indicador_columnas, [ordenado])

Veamos cómo trabaja la función BUSCARH, para eso escribimos lo siguiente en una hoja de cálculo.

36
 Microsoft Excel - Avanzado
BASE DE DATOS

Queremos mostrar los sueldos de acuerdo a la especialidad que los estudiantes pertenecen, los sueldos
se encuentran desde F3 hasta F5, entonces nos ubicamos en C2 y escribimos lo siguiente.

=BUSCARV(B2,$E$3:$F$5,2)
Donde:
C2 es la celda donde ingresaremos el código que se va a buscar.
E3:F5 es el rango de celdas de búsqueda.
2 es el número de la columna donde encontraremos el resultado.

Al pulsar la tecla ENTER y al jalar hacia las demás celdas, se obtiene lo siguiente.

Para la versión de Excel 2010, la función BUSCAR, BUSCARH

y BUSCARV fueron cambiadas por las funciones CONSULTA,
CONSULTAH y CONSULTAV respectivamente. Sin embargo, ésto trajo
muchas confusiones a los usuarios; es por eso que Microsoft decidió
regresar las funciones BUSCAR, BUSCARH y BUSCARV al Excel 2010.
Esto se dio a partir de la versión Service Pack 1 de Office.

37 37
 Microsoft Excel - Avanzado
BASE DE DATOS

Ejercicio de aplicación funciones búsqueda

1. Ingresamos la siguiente información en una hoja de cálculo que llamaremos NOTAS.

2. Ingresamos la siguiente información en una hoja de cálculo que llamaremos REGISTROS.

3. Cada vez que escribamos el código deberá aparecer el Nombre del alumno, su nota y la
observación, si la nota es mayor a 10 estará aprobado, de lo contrario desaprobado. Primero
nos vamos a la hoja de NOTAS y calculamos todos los promedios, se calcularán de la siguiente
manera:
a. I6: =PROMEDIO(F6:H6)
b. M6: =PROMEDIO(J6:L6)
c. Q6: =PROMEDIO(N6:P6)
d. R6: =(I6+M6+Q6)/3

38
 Microsoft Excel - Avanzado
BASE DE DATOS

4. Ahora pasemos a la hoja REGISTROS, ni bien escribamos el código deberá aparecer el nombre
completo de la persona, por lo tanto hacemos lo siguiente, seleccionamos la celda D6 y
escribimos lo siguiente:

Donde:
C4 es el valor a buscar.
NOTAS!A6:E15 es
la referencia que se
hace a la hoja NOTAS
y matriz que estamos
escogiendo.
4 es la columna que se
encuentra los nombres.
=BUSCARV(C4,NOTAS!A6:R15,4))

5. Note como queda el resultado:

Nos aparece #N/A por

que en la celda C4 no hay
ninguna información y
por lo tanto no hay valor
a buscar. Podemos evitar
este error escribiendo un
valor en la celda C4.

6. Otra forma de evitar el error en el paso 6 sin necesidad de escribir nada en la celda C4 es
escribiendo lo siguiente:

Con la función SI,

haciendo la primera
condición blanco
podemos evitar el error
mostrado anteriormente
sin necesidad de escribir
nada en la celda C4.

=SI(C4=””,””,BUSCARV(C4,NOTAS!A6:R15,4))

39 39
 Microsoft Excel - Avanzado
BASE DE DATOS

7. Si escribimos un código nos aparecerá sólo el nombre, si queremos que nos aparezca el
nombre completo tenemos que combinar la función CONCATENAR y la función BUSCARV. De
esta manera la función queda de la siguiente manera.

=SI(C4=””,””,CONCATENAR(BUSCARV(C4,NOTAS!A6:R15,4),”“,
BUSCARV(C4,NOTAS!A6:R15,2),” “,BUSCARV(C4,NOTAS!A6:R15,3)))

8. Calculemos ahora las notas de Windows, Word y Power Point, escribiendo lo siguiente en cada
celda respectivamente:
a. D10: =SI(C4=””,””,BUSCARV(C4,NOTAS!A6:R15,9))
b. D12: =SI(C4=””,””,BUSCARV(C4,NOTAS!A6:R15,13))
c. D14: =SI(C4=””,””,BUSCARV(C4,NOTAS!A6:R15,17))
d. D16: =SI(C4=””,””,BUSCARV(C4,NOTAS!A6:R15,18))

9. Para calcular la observación se tendrá en cuenta que si la Nota es mayor o igual a 11 estará
aprobado, caso contrario, estará desaprobado. Se escribirán las siguientes fórmulas.
a. F10: =SI(D10>=11,”APROBADO”,”DESAPROBADO”)
b. F12: =SI(D12>=11,”APROBADO”,”DESAPROBADO”)
c. F14: =SI(D14>=11,”APROBADO”,”DESAPROBADO”)
d. F16: =SI(D16>=11,”APROBADO”,”DESAPROBADO”)

10. Cuando escribimos in código nos muestra el siguiente resultado.

40
 Microsoft Excel - Avanzado
BASE DE DATOS

Octava Práctica

1. Ingresar la siguiente información en una nueva hoja de cálculo que llamaremos VUELOS.

2. Ingresaremos la siguiente información en una hoja de cálculo que llamaremos TICKETAVION.

3. Se le pide llenar la TARJETA DE EMBARQUE con los siguientes datos:

a. El Pasajero AGUIRRE TORRES, Miguel, viaja en el vuelo VUEL-14.
4. Hacer tres tarjetas, colocar a tres compañeros de clase que vayan en los siguientes vuelos:
a. El primero en el vuelo VUEL-18.
b. El segundo en el vuelo VUEL-12.
c. El tercero en el vuelo VUEL-07.
5. En OPERADOR, colocar el nombre del alumno.

41 41
42
Novena Práctica
1. Ingresar la siguiente información en una hoja de cálculo, a la cual llamaremos BASE_TRABAJADORES.

Calcular:
• El SUELDO BRUTO se halla de
acuerdo al cargo, si es ASESOR, 1200;
OPERADOR 1500 y SECRETARIA 1300.
• Las HORAS TRABAJADAS NORMALES
son los DIAS TRABAJADOS
multiplicados por un jornal de 8 horas.
BASE DE DATOS

• El Aporte de AFP será, para INTEGRA

12.56% del Sueldo Bruto; para
HORIZONTE 12.35% y para RIMAC
12.66%.ç
• La RETENCION EXTRAORDINARIA
y ESSALUD se obtiene del SUELDO
Microsoft Excel - Avanzado

BRUTO.
2. Ingresar la siguiente información en una hoja de cálculo, a la cual llamaremos BOLETA_PAGO.

Calcular:
• Llenar los datos automáticamente con la función BUSCARV, ni
bien escriba el DNI del trabajador, la boleta de pago debe llenarse
automáticamente.
• El monto para La HORA 25% es de S/.10., para la HORA 35% es de
S/.12, multiplicar los motos dados con la cantidad que hay en la
hoja BASE_TRABAJADORES.
• La REMUNERACIÓN BÁSICA es el SUELDO BRUTO.
• El TOTAL DE INGRESOS es la suma del SUELDO BRUTO con el
monto de las HORAS 25% Y 35%.
• El TOTAL DESCUENTOS se obtiene sumando los APORTES DE
DESCUENTOS.
• El TOTAL NETO es el TOTAL DE INGRESOS menos la suma del
TOTAL DESCUENTOS y el TOTAL DE APORTES.
 Décima Práctica
1. Ingresar la siguiente información en una hoja de cálculo que llamaremos 3 Ingresar la siguiente información en una hoja de cálculo que
PRODUCTOS. llamaremos FACTURA.
BASE DE DATOS

El PRECIO
BRUTO se
calculará
dividiendo el
PRECIO NETO
Microsoft Excel - Avanzado

entre 1.18.

2. Ingresar la siguiente información en una hoja de cálculo que llamaremos

Completar la factura con las fórmulas respectivas
CLIENTES.
utilizando las funciones BUSCARV y SI. Considerar cómo
obtener el IMPORTE, SUBTOTAL, I.G.V. y TOTAL. Recordar
que el Precio Unitario es el PRECIO BRUTO de la hoja
Productos.
Hacer dos facturas, la primera considerar que Comercial
INRESA compró el día 15/07/2012 tres Cocinas INDURAMA
y una refrigeradora COLDEX. En la segunda la Distribuidora
Mínimo E.I.R.L. compró diez Ollas Arroceras PRACTICA,
cinco Aspiradoras ELECTROLUX y un Microondas DAEWOO.

43
43
 Microsoft Excel - Avanzado
BASE DE DATOS

Capitulo 4

Capitulo 4
Base de Datos

44
 Microsoft Excel - Avanzado
BASE DE DATOS

Ordenar Datos
Es una opción que nos permitirá ordenar los datos que forman parte de la Base de Datos creada en una
hoja de excel, estos datos se pueden ordenar por uno o más campos o columnas de manera ascendente
o descendente. Sin importar el tipo de dato que tengamos (alfanumérico, número, fecha, etc.). Vamos a
trabajar la opción de ordenar datos, para esto debemos seguir los siguientes pasos:

1. Ingresar los siguientes datos en una hoja de cálculo.

2. Seleccione toda la tabla que acaba de ingresar. Desde B3 hasta G18.

3. En la cinta de opciones seleccione la pestaña DATOS y a continuación ubique la sección

ORDENAR y FILTRAR.

4. Visualizará los siguientes botones.

Ascendente

Descendente

45 45
 Microsoft Excel - Avanzado
BASE DE DATOS

5. Hacemos clic en la opción ascendente y esto es lo que se nos va a mostrar.

Ordenamiento de datos con varios criterios.

Si queremos que los datos aparezcan ordenados por un campo diferente al primero o si deseamos
ordenar los datos por más de un campo, Excel brinda una herramienta que nos facilita esta opción. A
continuación mostraremos cómo funciona esta herramienta.
1. De los datos que hemos ingresado, seleccionamos toda la tabla nuevamente (no importa si los
datos están ordenados ascendente o descendentemente) y hacemos clic en la pestaña DATOS y
seleccionamos la opción Ordenar.

Hacer clic en esta opción

2. A continuación nos aparece el siguiente cuadro de diálogo.

Aquí se muestran los

campos por los cuales los
datos se pueden ordenar.

Puedo ordenar los datos

según sus valores, color
de la celda, etc.

Puedo ordenar de forma

ascendente o descendente.

46
 Microsoft Excel - Avanzado
BASE DE DATOS

3. Seleccionar la opción SEXO y luego ACEPTAR, a continuación se nos muestra lo siguiente.

Datos ordenados de
acuerdo al campo
SEXO.

4. Si deseamos ordenar los datos por más de un campo, ejemplo SEXO y luego SUELDO,
seleccionamos la tabla nuevamente, nos vamos a la pestaña DATOS, la opción ORDENAR,
seleccionamos SEXO y luego cliqueamos en la opción AGREGAR NIVEL y escogemos la opción
SUELDO, finalmente le damos clic en ACEPTAR. Se nos muestra lo siguiente.

Primero seleccionamos
AGREGAR NIVEL

Luego seleccionamos SUELDO

Este es el resultado, los

datos primeros están
ordenados por SEXO y
luego están ordenados
por SUELDO.

47 47
 Microsoft Excel - Avanzado
BASE DE DATOS

Filtrar Datos
Los filtros nos permiten extraer una porción de información de una hoja de cálculo, para ver los datos
que cumplan con una determinada condición que hayamos establecido. Para ello veremos primero la
opción AUTOFILTRO.

1. Ingrese los siguientes datos en una hoja de cálculo.

2. Luego nos vamos a la pestaña DATOS y activamos la opción de filtros. Note como cambie el
diseño de la tabla de datos.

Luego de hacer clic en esta opción, nos aparece la tabla de la derecha.

3. Los encabezados de cada lista mostrará una flecha que nos permitirá desplegar los criterios
del filtrado. Por ejemplo buscaremos las personas que son de Lima. Desplegamos la lista que
corresponda a LUGAR DE PROCEDENCIA y desactive todas las casillas de verificación a excepción
de LIMA. Posteriormente hacemos clic en el botón ACEPTAR y se mostrará lo siguiente.

Luego de seguir esos pasos, el resultado

es la tabla en la parte superior.
3

48
 Microsoft Excel - Avanzado
BASE DE DATOS

Filtros Avanzados
Los filtros avanzados nos permiten extraer una porción de información de una hoja de cálculo, utilizando
una condición más compleja. Esto implica que la condición esté escrita en una de las celdas libres.
Podemos utilizar estos filtros de la siguiente manera.
1. Utilizamos los mismos datos del ejemplo anterior.
2. Queremos extraer a todas las personas nacidas posteriormente de 1990 (es decir a partir del
01/01/1991). Entonces escribimos esta condición debajo de los datos antes vistos.

Esta condición puede ir en

cualquier lado de la hoja de
cálculo, debemos escribir
exactamente el nombre del
campo, de lo contrario el filtro
nos saldrá nulo.

3. Nos vamos a DATOS y dentro de la sección ORDENAR Y FILTRAR haremos clic en el botón
AVANZADAS.

4. Se mostrará el siguiente cuadro de diálogo.

Realiza el filtro en la misma

ubicación de los datos.

Copia el resultado filtrado

a una ubicación que se Rango de las celdas que
especifique. contienen los datos.

Rango del criterio a filtrar.

Rango de la celda donde se

iniciará la copia del resultado.

49 49
 Microsoft Excel - Avanzado
BASE DE DATOS

5. Seleccionamos cada uno de los rangos con el botón, para esto tenemos que el Rango de la lista
es de $A$2:$G$17; el Rango de criterios $F$19:$F$20; y lo copiamos en la celda $A$22. Una vez
terminado damos clic en el botón ACEPTAR. Teniendo el siguiente resultado.

De seguir los pasos en este

cuadro, el resultado será el que
se muestra al lado derecho.

Subtotales
Los subtotales son una herramienta muy valiosa al momento que nosotros necesitamos realizar
cálculos. Usando subtotales podemos hacer lo siguiente:

Agrupamiento de datos: Mostrar Subtotales y Totales por diferentes grupos de la lista. Hacer diferentes
tipos de cálculos como por ejemplo contar los ítems, calcular su promedio, etc.

Preparando los datos para calcular Subtotales: Para que los Subtotales trabajen correctamente, la
tabla de datos deberá estar ordenada por el campo que se desea agrupar. Asimismo la tabla deberá
contener en la primera fila los nombres de los campos.

Vamos a realizar un ejemplo aplicando Subtotales:

1. Escribimos los siguientes datos en una hoja de cálculo.

50
 Microsoft Excel - Avanzado
BASE DE DATOS

2. En la tabla ordenar los datos por el campo CATEGORÍA, es importante antes de calcular
subtotales, ordenar los registros por la columna de datos que se calculará subtotales.

Ordenamos los datos de

acuerdo a la CATEGORÍA
y le damos aceptar.

3. Hacer clic en la pestaña Datos, luego en el botón Subtotal. Se presentará el siguiente cuadro de
diálogo.
a. Elegir el campo categoría.
b. Elegir la función a utilizar.
c. Activar campos numéricos donde se totalizará según la función elegida.

Selecciona el campo que está ordenado.

Se elige la función a utilizar.

Activar campos numéricos donde se

totalizará según la función indicada.

51 51
 Microsoft Excel - Avanzado
BASE DE DATOS

4. Finalmente hacer clic en el botón Aceptar. Se mostrarán los datos agrupados de la siguiente
manera:

Manejo de los niveles de esquema

Utilice los botones para mostrar los registros por niveles de subtotales.

Si elige el botón 1 se mostrará sólo el total general.

Si elige el botón 2 se mostrará sólo los totales por categoría.
Si elige el botón 3 se mostrará todos los registros.

Múltiples subtotales por categoría

1. Ingresar los siguientes datos en una nueva hoja de cálculo.

2. Agrupar los registros por el campo de datos COMPROB.

3. Nos vamos a la pestaña DATOS, botón Subtotales, se presenta el siguiente cuadro de diálogo.

52
 Microsoft Excel - Avanzado
BASE DE DATOS

Selecciona el campo que está ordenado.

Se elige la función a utilizar.

Activar campos numéricos donde

se totalizará según la función
indicada. Se activarán cuatro
campos para este ejercicio.

4. Se mostrará
como resultado
lo siguiente,
se mostrará
los registros
totalizados por tipo
de comprobante
de pago (campo de
datos “COMPROB.”)
y se totaliza por
los campos:
SUBTOTAL, DTO.,
I.G.V. y TOTAL.

5. Si eligen el
botón 2 se
mostrará sólo
los totales por
el campo tipo
COMPROB.
tal y cómo se
muestra en la
figura.

La función SUBTOTALES utiliza como primer parámetro valores numéricos (1 al 11) el cual representa una
determinada función,
según el cuadro respectivo: 1er Parámetro Función 1 Parámetro Función
1 PROMEDIO 7 DESVEST
2 CONTAR 8 DESVESTP
3 CONTARA 9 SUMA
4 MAX 10 VAR
5 MIN 11 VARP
6 PRODUCTO

53 53
 Microsoft Excel - Avanzado
BASE DE DATOS

Undécima Práctica

1. Ingresar la siguiente información en una nueva hoja de cálculo que llamaremos VUELOS.

2. Filtrar en una nueva hoja, para cada caso:

• Solteros (varones) que están en una AFP.
• Casados (mujeres) que están en el SNP.
• Hombres con hijos.
• Mujeres sin hijos.
• Varones cuyo sueldo sea mayor a 1500.
• Trabajadores estables solteros.
• Trabajadores contratados casados.
• Trabajadores varones mayores de 30.
• Trabajadores mujeres menores de 25.

3. Copie sus datos digitados inicialmente en una Hoja Nueva y ordene los registros por Condición
Laboral y hallar los Subtotales.

4. Copie sus datos digitados inicialmente en una hoja nueva y ordene los registros por Estado
Civil y hallar los Subtotales.

54
 Microsoft Excel - Avanzado
BASE DE DATOS

Duodécima Práctica

1. Ingresar la siguiente información en una nueva hoja de cálculo que llamaremos VUELOS.

2. Filtrar en la misma hoja de cálculo lo siguiente:

a. Los empleados cuya categoría sea A.
b. Los empleados cuya categoría no sea A.
c. Los empleados cuyo Sueldo Básico sea mayor a 1000 nuevos soles.
d. Los empleados cuyo Sueldo Básico sea menor a 1000 nuevos soles.
e. Los empleados cuyo Sueldo Básico sea entre 1000 y 2000 soles.

3. Hallar los siguientes subtotales en una hoja nueva de cálculo.

a. La suma y promedio de las Sueldos Básicos por Categoría.
b. La suma y promedio de los sueldos Básicos por Condición.

55 55
 Microsoft Excel - Avanzado
BASE DE DATOS

Funciones Base de Datos

Cada una de estas funciones denominadas colectivamente funciones BD, usa tres argumentos: base_
de_datos, nombre_de_campo y criterios. Estos argumentos se refieren a los rangos de la hoja de cálculo
empleados en la función para base de datos.

Sintaxis:
=BDfunción(base_de_datos, nombre_de_campo, criterios)
Donde:

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

Nombre_de_campo: Indica qué columna se utiliza en la función. Escriba el rótulo de la columna entre
comillas, como por ejemplo “Edad” o “Rendimiento”, o un número, sin las comillas, que represente la
posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente.

Criterios: Es el rango de celdas que contiene las condiciones especificadas. Puede utilizar cualquier rango
en el argumento Criterios mientras éste incluya por lo menos un rótulo de columna y al menos una celda
debajo del rótulo de columna en la que se pueda especificar una condición de columna.

En el siguiente cuadro se visualizan las funciones de base de datos que Excel nos muestra, recodando
que todas las funciones de base de datos tienen la misma sintaxis, sólo cambian el nombre de la función.

Nombre de la Función Descripción Sintaxis

Cuenta las celdas que contienen números en un
Contar campo (columna) de registros de una lista o base de BDCONTAR
datos que cumplen las condiciones especificadas.

Cuenta las celdas que no están en blanco de un

Contara campo (columna) de registros de una lista o base de BDCONTARA
datos que cumplen las condiciones especificadas.

Extrae un único valor de una columna de una lista

Extraer o una base de datos que cumpla las condiciones BDEXTRAER
especificadas.

Devuelve el valor máximo de un campo (columna)

Máximo de registros en una lista o base de datos que cumple BDMAX
las condiciones especificadas.

Devuelve el valor mínimo de un campo (columna)

Mínimo de registros en una lista o base de datos que cumple BDMIN
las condiciones especificadas.

Multiplica los valores de un campo (columna) de

Producto registros de una lista o base de datos que cumplen BDPRODUCTO
las condiciones especificadas.

56
 Microsoft Excel - Avanzado
BASE DE DATOS

Nombre de la Función Descripción Sintaxis

Calcula la desviación estándar de una población

Desviación Estándar de basándose en una muestra y utilizando los números
BDDESVEST
una muestra de un campo (columna) de registros en una lista o base
de datos que cumplen las condiciones especificadas.

Calcula la desviación estándar de una población

basándose en toda la población y utilizando los
Desviación Estándar de
números de un campo (columna) de registros de una BDDESVESTP
una población
lista o base de datos que cumplen las condiciones
especificadas.

Suma los números de un campo (columna) de

Suma registros de una lista o base de datos que cumplen las BDSUMA
condiciones especificadas.

Calcula la varianza de una población basándose en

una muestra y utilizando los números de un campo
Varianza de una muestra BDVAR
(columna) de registros de una lista o base de datos
que cumplen las condiciones especificadas.

Calcula la varianza de una población basándose en

toda la población y utilizando los números de un
Varianza de una población BDVARP
campo (columna) de registros de una lista o una base
de datos que cumplen las condiciones especificadas.

Utilizando las funciones de base de datos

1. Vamos a trabajar las funciones de base de datos, para ésto copie la siguiente base de datos en
una nueva hoja de cálculo.

FECHA
ID APELLIDOS NOMBRES DNI DIRECCIÓN TELÉFONO RUC INSCRIPCIÓN VENTAS CIUDAD
1
2 C14 CAMPOS GUTTER MARIELA 09842332 AV RIVA AGUERO 1126 4525585 17892678 12/08/79 S/. 635.15 AREQUIPA
3 C18 IZAGA SALAZAR JOFFRE LUIS 01466321 AV. JAVIER PRADO OESTE 1658 4228956 56433977 23/04/55 S/. 3,672.41 AREQUIPA
4 C19 TORRES LEON ERNESTO 03121678 AV. JUPITER 154 5539041 64328676 22/08/76 S/. 368,542.65 AREQUIPA
5 C01 ALVAREZ BARDALES GONZALO 09632541 AV. DE LA ROSA TORO 1056 3464604 56748908 12/12/75 S/. 5,623.22 LIMA
6 C04 SANTOS FRIAS JOSE LUIS 02156646 JR. LIBERTADORES 532 4428906 35690928 12/08/79 S/. 6,589.25 LIMA
7 C06 GARCIA GUILLEN JANETH CHRISTINA 08653215 AV. JAVIER PRADO ESTE 447 4220795 54799872 01/08/71 S/. 8,542.36 LIMA
8 C26 PALMA YAÑEZ GLADYS MILAGROS 09862221 AV. VELASCO ASTETE 236 4379801 12589762 30/11/55 S/. 36,574.25 NAZCA
9 C30 BRICEÑO RIVADENEYRA SANDRA 09455244 AV. LA PAZ 2184 5789054 88899771 18/04/69 S/. 6,583.24 NAZCA
10 C31 ARNAO SOLIS LUIS ANTONIO 04572131 AV. LOS HEROES 551 4669856 89093334 05/05/75 S/. 356.24 PIURA
11 C03 FLORES TASAYCO GUILLERMO 02365894 AV. PRINCIPAL 546 4756304 65904378 06/03/50 S/. 68,547.25 TACNA
12 S16 DIAZ NAVARRO ARMANDO M. 06545244 AV. BAQUIJANO Y CARRILLO 195 46706522 02/03/77 S/. 2,458.15 TRUJILLO
13 S48 CAMACO CASTAÑEDA PAMELA 07954456 AV. MANUEL QUIMPER476 22564893 22/09/50 S/. 35,621.41 TRUJILLO
14 C09 GARAY TAFUR MARIA CLAUDIA 09621152 AV. JESUS MORALES 1027 2760984 87150628 25/12/56 S/. 7,652.36 TUMBES
15 C29 PEREZ NAVEROS ANA FIORELLA 07545565 AV. LA MARINA 2945 4649870 89953438 30/12/75 S/. 962.35 TUMBES
16 C35 CAMPOS SOUSA GISSELA 09854221 AV. BENAVIDES 654 4467389 76453290 11/04/45 S/. 3,641.98 TUMBES

FECHA
ID APELLIDOS NOMBRES DNI DIRECCIÓN TELÉFONO RUC INSCRIPCIÓN VENTAS CIUDAD
18
19 FLORES TASAYCO LIMA
20

23 BDCONTAR 3
24 BDDESVEST 1487.12
25 BDEXTRAER 68547.25
26 BDMAX 8542.36
27 BDMIN 5623.22
28 BDPRODUCTO 3.16518E+11
29 BDPROMEDIO 6918.276667
30 BDSUMA 20754.83
31 BDVAR 2211538.495

57 57
 Microsoft Excel - Avanzado
BASE DE DATOS

2. Vamos a poner nombre a la base de datos, para este caso se llamará PRINCIPAL, la base de datos
se compone desde A1 hasta J16.
3. Veamos en primer lugar cómo trabaja la función BDCONTAR, vamos a contar todas las ventas de
la ciudad de Lima, para esto nos posicionamos en la celda C23 y digitamos la fórmula que nos
aparece en el recuadro de la parte inferior, cómo resultado nos mostrará lo siguiente.

=BDCONTAR(PRINCIPAL,”Ventas”,J18:J19)

4. En segundo lugar vamos a calcular la desviación estándar de las ventas en la ciudad de Lima, para
esto nos posicionamos en la celda C24 y digitamos la función que nos aparece en el recuadro de
la parte inferior, cómo resultado nos mostrará lo siguiente.

=BDDESVEST(PRINCIPAL,”Ventas”,J18:J19)

5. Ahora vamos a extraer cuánto ha vendido el Sr. Flores Tasayco, nos ubicamos en la celda C25
y digitamos la función que nos aparece en el recuadro de la parte inferior, cómo resultado nos
mostrará lo siguiente.

=BDEXTRAER(PRINCIPAL,”Ventas”,B18:B19)

6. Ahora vamos a calcular la máxima y la mínima venta de la ciudad de Arequipa, cambiamos Lima
(J19) y digitamos Arequipa, luego nos posicionamos en la celda C26 y digitamos la función que
nos aparece en el recuadro de la parte inferior, luego nos ubicamos en la celda C27 y digitamos
la función que nos aparece en el recuadro de la parte inferior, cómo resultado nos mostrará lo
siguiente.

=BDMAX(PRINCIPAL,”Ventas”,J18:J19)

=BDMIN(PRINCIPAL,”Ventas”,J18:J19)

58
 Microsoft Excel - Avanzado
BASE DE DATOS

7. Quiero hallar el producto de las ventas de la ciudad de Tumbes, cambiamos Arequipa (J19)
y digitamos Tumbes, luego nos posicionamos en la celda C28 y digitamos la función que nos
aparece en el recuadro de la parte inferior, cómo resultado nos mostrará lo siguiente.

=BDPRODUCTO(PRINCIPAL,”Ventas”,J18:J19)

8. Quiero hallar el promedio de las ventas de la ciudad de Nazca, cambiamos Tumbes (J19) y
digitamos Nazca, luego nos posicionamos en la celda C29 y digitamos la función que nos aparece
en el recuadro de la parte inferior, cómo resultado nos mostrará lo siguiente.

=BDPROMEDIO(PRINCIPAL,”ventas”,J18:J19)

9. Vamos a hallar la suma de ventas de la ciudad de Lima, cambiamos Nazca (J19) y digitamos Lima,
luego nos posicionamos en la celda C30 y digitamos la función que nos aparece en el recuadro de
la parte inferior, cómo resultado nos mostrará lo siguiente.

=BDSUMA(PRINCIPAL,”Ventas”,J18:J19)

10. Finalmente vamos a hallar la varianza de las ventas en la ciudad de Lima, nos posicionamos en
la celda C31 y digitamos la función que nos aparece en el recuadro de la parte inferior, cómo
resultado nos mostrará lo siguiente.

=BDVAR (PRINCIPAL,”Ventas”,J18:J19)

59 59
60
Décimo Tercera Práctica
1. Ingresar los siguientes datos en una hoja de cálculo.
BASE DE DATOS
Microsoft Excel - Avanzado

2. Calcular lo siguiente:
• Con funciones de base de datos calcular lo
que se está solicitando.
• Tener cuidado al momento de utilizar los
criterios que se están pidiendo.
Microsoft Excel - Avanzado
BASE DE DATOS

Capitulo 5
Tablas Dinámicas

61 61
 Microsoft Excel - Avanzado
BASE DE DATOS

Tablas Dinámicas
Una tabla dinámica es una hoja de cálculo interactiva que resume rápidamente grandes cantidades de
datos usando el formato y los métodos de cálculo que el usuario elija. Puede actualizar la tabla dinámica
a medida que cambian los datos fuentes.

Generar tablas dinámicas: Una tabla dinámica crea un resumen de datos usando los métodos de
cálculo, o funciones de resumen que el usuario seleccione, ejemplo SUMA, PROMEDIO, etc. También se
puede controlar cómo se calculan los subtotales y los totales. Cuando cambian los datos fuentes, puede
fácilmente actualizar o volver a calcular los datos de la tabla dinámica.

Funcionamiento de una tabla dinámica: Los campos y los datos que estos contienen controlan la
organización de los datos. Se debe especificar qué datos se desea incluir y de qué modo deben estar
organizados eligiendo de la tabla de origen o de la lista los campos y los elementos (conjunto de datos
únicos que contienen los campos) que aparecen en la tabla.

Diseño de la tabla: Filas, Columnas, Páginas, Datos: Campos que se utilizará como nombres de
referencia del campo de fila del campo de columna y del campo de páginas en la tabla dinámica. Estos
campos generalmente contienen un conjunto limitado de valores de texto.
Los Filtros de informe contienen campos de datos que se han de resumir. Generalmente suelen ser
campos que contienen datos tipo texto.
Utilizar la cantidad de campos que desee de la lista. Los elementos de la lista fuente se convierten en
rótulos de filas o de columnas en la tabla dinámica resultante. Estos rótulos, o elementos de la tabla
dinámica son subcategorías de un campo de una tabla dinámica, al igual que subcategorías del campo
de la lista fuente.
Los datos numéricos, y en algunos casos los datos de texto, se resumen en el área de datos de la tabla
dinámica.

Campos que se utilizan como nombres de

referencia de la tabla fuente.

Filtro de informe, contienen datos que se han

de resumir.

Rótulos de fila y columna, aquí se colocan

las subcategorías de un campo de la tabla
dinámica.

Área de Datos de la tabla dinámica, aquí se

colocan datos numéricos y en algunos casos,
texto.

62
 Microsoft Excel - Avanzado
BASE DE DATOS

Campos de fila, campos de columna: Los campos y los elementos que se incluyen en una tabla
dinámica determinan de qué modo se van a resumir los datos en el área de datos.

Cómputo del campos de datos: Si el campo de datos que se elige de la lista o de la tabla fuente contiene
datos numéricos, EXCEL usa la función SUMA para calcular los valores en el área de datos de la tabla
dinámica. Si el campo de datos que elige contiene elementos de texto, EXCEL usa la función CUENTA
para contabilizar los elementos fuente.

Los campos de página filtran la presentación de datos: Para filtrar la presentación de los datos en una
tabla dinámica, utilice un campo de página. El campo de página divide la tabla dinámica en páginas
individuales, permitiéndole ver uno por uno los datos que corresponden a un elemento. Los campos de
página se crean junto con la tabla dinámica.

Generando una tabla dinámica.

1. Vamos a trabajar las funciones de base de datos, para esto copie la siguiente base de datos en
una nueva hoja de cálculo, luego seleccione desde A4 hasta I45.

63 63
 Microsoft Excel - Avanzado
BASE DE DATOS

2. Seleccione la Ficha INSERTAR de la Cinta de Opciones y haga clic en el botón TABLA DINÁMICA.

3. Se mostrará el siguiente cuadro de diálogo, luego de comprobar que todo está en orden, le damos
clic en el botón ACEPTAR.

Rango de la tabla de datos que se

creó anteriormente.

Permite mostrar la tabla dinámica

en otra hoja diferente a la de
los datos iniciales. Es preferible
seleccionar esta opción

4. Ahora debemos indicar qué datos deseamos que aparezcan en columna, fila o en el interior de
la tabla (Valores). En nuestro caso deseamos cruzar los datos de FAENA con ZONA y calcular los
salarios.
Señala el botón ZONA en la lista de campos y arrástralo hasta Rótulo de Fila. Haz lo mismo con
el botón FAENA, arrástralo hasta Rótulo de Columna. Por último, arrastra el campo SALARIOS a
la zona de Valores. Por defecto el campo SALARIO presenta la operación SUMA, sin embargo, es
posible cambiarlo a otro tipo de operación. Efectuadas estas operaciones se aprecia lo siguiente:

64
 Microsoft Excel - Avanzado
BASE DE DATOS

Cambio de operaciones
Observa que en la esquina superior izquierda de la tabla creada figura un botón rotulado como “Suma
de Salario”. Se va a contar los vehículos en lugar de sumarlos. Para conseguirlo cliquea en el botón
CONFIGURACIÓN DE CAMPO. Aparecerá una ventana de opciones. En la lista RESUMIR CAMPO DE
VALOR POR: elige CUENTA en lugar de SUMA.

Del mismo modo podemos 2

trabajar para hallar el
promedio de salario, el salario
máximo o el salario mínimo
de las zonas.

Aplicar filtros
Es la acción que consiste en mostrar los datos necesarios omitiendo los otros datos que no se desean
visualizar. Esta acción es muy similar a las opciones de autofiltro y filtro personalizado que se desarrollaron
con anterioridad. Para proceder a filtrar los datos procederemos de la siguiente forma.

65 65
 Microsoft Excel - Avanzado
BASE DE DATOS

1. Seleccionar el cuadro combinado Rótulos de Fila (celda A4).

2. Al desglosar las opciones desactivamos la opción SELECCIONAR TODO y luego activamos CENTRO
y ESTE, luego seleccionamos ACEPTAR.

Seleccionamos primero esta opción.

Desactivamos la opción Seleccionar

Todo; y luego activamos las opciones
CENTRO y ESTE.

Cuando hayamos configurado

todo lo que hemos deseado,
seleccionamos Aceptar.

3. Se nos muestra el siguiente cuadro.

La opción de filtrado está activado; si deseamos

mostrar toda la tabla, desglosamos nuevamente
y activamos SELECCIONAR TODO y seleccionamos
Aceptar.

66
 Microsoft Excel - Avanzado
BASE DE DATOS

Gráficos Dinámicos
Permite crear un gráfico tomando como fuente de datos la tabla dinámica. Un informe de Gráfico Dinámico
representa gráficamente los datos de un informe de tabla dinámica (informe de tabla dinámica: informe
de EXCEL interactivo de tablas cruzadas que resume y analiza datos, como registros de una base de datos,
de varios orígenes, incluidos los que son externos a Excel.)

1. Para crear un gráfico de nuestra tabla dinámica deberemos hacer clic en el botón Gráfico
Dinámico de la ficha OPCIONES. Seleccionamos el gráfico que más nos convenga, para este
ejemplo se usará COLUMNA AGRUPADA EN 3D. Luego cliquemos el botón ACEPTAR.

2. Se muestra lo siguiente:

Cada vez que ingresamos un gráfico dinámico

siempre aparecerá un Panel de filtros, aquí se
podrán hacer filtrado de datos, que se verán
reflejados en nuestro gráfico dinámico.

67 67
 Microsoft Excel - Avanzado
BASE DE DATOS

Décimo Cuarta Práctica

1. Ingresar los siguientes datos en una hoja de cálculo.

2. Usando tablas dinámicas se le pide hallar lo siguiente, colocar al costado la cantidad obtenida

a. Número de mujeres: _____________

b. Número de hombres: _____________

c. Cuánto ganan en total los hombres: _____________

d. Cuánto ganan en total las mujeres: _____________

f. Promedio de ingresos por trabajadores estables: _____________

g. Promedio de ingresos por trabajadores contratados: _____________

h. Cuántos trabajadores están en la categoría A: _____________

i. Cuántos trabajadores están en la categoría B: _____________

j. Cuántos trabajadores están en la categoría C: _____________

k. Cuántos trabajadores están en la categoría D: _____________

l. Cuántos trabajadores están en la categoría E: _____________

68
 Microsoft Excel - Avanzado
BASE DE DATOS

Décimo Quinta Práctica

1. Ingresar los siguientes datos en una hoja de cálculo.

2. Usando tablas dinámicas se pide hallar lo siguiente, escribir las cantidades al costado:
a. Total de Ventas por mes: Abril: _______ Mayo: ______
b. Total de Ventas por cliente: Totus: ______ Wong: ______ Vea: _______ Metro: _____
c. Total de Ventas por lugar: San Miguel: _____ Lima: _______ Chorillos: ________

d. Total de Ventas mensuales por cliente:

Abril- Wong _____ Abril – Metro _____ Abril – Vea _____ Abril – Totus _____
Mayo – Wong _____ Mayo – Metro _____ Mayo – Vea _____ Mayo - Totus _____

e. Total de Ventas mensuales por lugar

Abril – San Miguel _____ Abril – Lima _____ Abril – Chorrillos _____
Mayo – San Miguel _____ Mayo – Lima _____ Mayo – Chorrillos _____

3. Crear los siguientes gráficos dinámicos:

a. Total de Ventas por clientes.
b. Total de Ventas por lugar.
c. Total de Ventas por vendedor.
d. Total de Ventas por marca.

69 69
 Microsoft Excel - Avanzado

ELABORAR PROYECTO

NOTA: El proyecto deberá estar conformado por un máximo de tres estudiantes.

DESCRIPCIÓN DEL PROYECTO:

...................................................................................................................................................................................................
..................................................................................................................................................................................................
..................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
................................................................. .................................................................................................................................
.................................................................. ................................................................................................................................
................................................................... ...............................................................................................................................
.................................................................... ..............................................................................................................................
...................................................................................................................................................................................................
 PRÁCTICA CALIFICADA

ESTUDIANTE : .....................................................................................................................................................................
DOCENTE :.................................................................................. BONO N°: ........................................................
FRECUENCIA :................................................................................. HORARIO: ......................................................

DESCRIBA LAS PREGUNTAS A DESARROLLAR:

1. ..............................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
2. ..............................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
3. ..............................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
4. ..............................................................................................................................................................................................
...................................................................................................................................................................................................
............................................................................................................................................................................................. .....
.............................................................................................................................................................................................. ....
............................................................................................................................................................................................... ...
................................................................................................................................................................................................ ..
.................................................................................................................................................................................................
5. .............................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
6. ..............................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
7. ..............................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
8. ..............................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
9. ..............................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
10. ............................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................
...................................................................................................................................................................................................