Microsoft Office Excel 2016

Carrera: Asistente Informático en Ingeniería y Arq.

Nivel Intermedio-Avanzado

CUARTA PRACTICA

✓ Organizar y administrar datos de una lista o tabla

OBJETIVOS ✓ Aplicar Ordenación, Filtros, Subtotales
✓ Usar funciones para base de datos.

Una de las tareas que con más frecuencia se realizan en una hoja de cálculo es la gestión de listas o
base de datos (de teléfonos, de clientes, de pacientes de ventas, etc), Excel posee un conjunto de
utilidades para la gestión de datos como son la ordenación, filtros y subtotales.
1. ADMINISTRAR UNA LISTA DE DATOS
Cuando se trabaja con hojas de cálculos muy extensas y con muchas columnas, la introducción de
datos se complica y se puede llegar a perder las referencias. Excel dispone de un botón especial
llamado FORMULARIO, que transforma en fichas cada línea de la hoja, facilitando la introducción de
datos y la visualización de la información.
Para activar dicho botón, debe desplegar la barra de herramientas de acceso rápido, Mas
comandos…, despliegue el cuadro de lista Comandos disponibles en: y seleccione Comandos
que no están en la cinta de opciones. Busque el botón Formulario y agregar y Aceptar. Se
agregará un pequeño botón en la barra de herramientas de acceso rápido.
Inserción de datos con el Formulario
Proceda a cargar el archivo COMERCIAL MODA.XLSX (solicítelo a su profesor) y guárdelo con el
nombre COMERCIAL MODA FORMULARIO.
1. La tabla tiene 237 registros.

2. Ubique el cursor en cualquier celda dentro de la tabla y clic en el botón Formulario. Clic en el
botón Nuevo. (Deja en blanco todos los campos)
3. Ingrese los 3 siguiente registros:
Nombre artículo Color Talla Ventas mes Vendedor
Pantalón Jean Negro Extra Large 650 enero Ramirez
Pantalón P Alphi Sport Large 1400 mayo Ramirez
Pantalón P Alphi Sport Short 3500 marzo Miranda
4. Cuando esté en el último campo Vendedor, pulse Nuevo
para ingresar el siguiente registro. Cuando termine de
ingresar el último registro, pulse el botón Cerrar. Ahora
Tendrá 240 registros de datos.

Consulta de datos a través del formulario

1. Ubicar la el cursor de la celda en cualquier parte de la
tabla.
2. Realice las siguientes consultas:
a. Registros cuyas ventas sean igual a 2500. Pulse el
botón Formulario y pulse en botón Criterios. Los
campos se ponen en blanco, escriba en el campo
Ventas: =2500 y clic en Buscar siguiente tantas
veces(cuente) hasta que finalice la búsqueda (16 registros).
b. Clic en Criterios y busque registros cuyos meses sean junio o julio. En el campo Mes debe
escribir: j* y clic en buscar siguiente unas 10 veces.

Tacna, 2019 1 Prof. Manuel Mendoza

 Microsoft Office Excel 2016
Carrera: Asistente Informático en Ingeniería y Arq.
Nivel Intermedio-Avanzado

Como puede observar, este formulario es muy eficiente para el ingreso de datos,más no tanto para
realizar consultas de manera masiva, como si se hará más adelante con filtros avanzados.

2. ORDENACION
La ordenación en una hoja de cálculo consiste en seleccionar del valor mínimo al máximo o del
máximo al mínimo, utilizando la columna en la que se encuentra el cursor. Los campos numéricos se
ordenan por tamaño(1 a 9 ó de 9 a 1) y los textos en forma alfabética (de A a la Z ó de Z a la A).
Cargue el archivo COMERCIAL MODA FORMULARIOS y guárdelo con el nombre COMERCIAL
MODA ORDENACIÓN.
Primero copie todos los datos de la Hoja1 en las 10 siguientes hojas y cambiar Hoja2 por Ord1, Hoja3
por Ord2, Hoja4 por Ord3 y así hasta Hoja11 por Ord10. Pasos para ordenar Ord1:
Realice las siguientes ordenaciones: a. Ubicar el cursor en cualquier celda de la tabla.
b. Ficha Datos, grupo Ordenar y filtrar, Ordenar
c. En la ventana Ordenar, seleccione la columna y
1. Ord1 :Por Nombre artículo (ascendente de A a Z) el criterio a ordenar y Aceptar.
2. Ord2 : Por Ventas (ascendente, de menor a mayor) (si son 2 o más columnas, clic en Agregar nivel)
3. Ord3 : Por Mes (ascendente)
4. Ord4 : Por Nombre artículo
(ascend.), talla (ascendente)
5. Ord5 :Por mes (ascend),
ventas(ascend), por Nombre
artículos (descendente).
6. Ord6 : Por nombre artículo, color y mes todos ascendentes
7. Ord7 : Por ventas (descendente)
8. Ord8 : Por ventas (descendente) y por Vendedor (descendente)
9. Ord9: Por nombre artículo, talla y color (todos descendentes)
10. Ord10: Por nombre artículo(ascendente) y ventas(descendente)

3. FILTRADO DE DATOS
Uno de los problemas con tablas muy grandes es que a menudo es difícil encontrar y extraer los que
datos que necesitamos. Ordenar puede ayudar en cierta forma, pero, se debe seguir trabajando con
toda la tabla. Lo que se desea muchas veces es definir datos con las cuales se va a trabajar, es decir,
mostrar por pantalla sólo los datos que necesitamos ocultando los demás. Excel dispone de la utilidad
de usar filtros que permiten obtener reportes rápidos de datos específicos y según ciertos criterios
AUTOFILTROS.- El autofiltro de Excel filtra los datos tan fácilmente como seleccionar una opción de
una lista desplegable.
Para realizar autofiltros, Ubique el cursor en cualquier parte dentro de la base de datos. Haga clic en
Datos / grupo Ordenar y filtrar/Filtro. En la parte izquierda de los nombres de los campos
automáticamente son agregados flechas desplegables.
1. Cargue el archivo COMERCIAL MODA FORMULARIOS y guárdelo con el nombre COMERCIAL
MODA FILTROS.
2. Primero copie todos los datos de la Hoja1 en las 15 siguientes hojas y cambiar Hoja2 por Filtro1,
Hoja3 por Filtro2, Hoja4 por Filtro3 y así hasta Hoja16 por Filtro15.
3. Ubicar el cursor en cualquier parte dentro de la tabla y proceda a filtrar información tomando en
cuenta los criterios y realizar las siguientes acciones. Deben anotar los resultados.
Filtro1: Filtrar registros mostrando que el importe de Ventas sean iguales a 3000
Filtro2: Filtrar registros donde el importe de Ventas sean iguales a 3000 y Color azul
Filtro3: Registros cuyas Ventas sean mayores de 3400 y menores o iguales que 4500
Filtro4: Registros cuyas Ventas sean menores o igual que 2500 y producto sea “Camisa pc”
Filtro5: Registros cuyo artículo sea “Pantalon Jean”, color negro y del mes de Febrero
Filtro6: Registros cuyo producto sea Pantalón Baronet ó Casaca cuero y color negro
Filtro7: Registros cuya Talla no sean small, medio, large ni short y ventas menores que 800
Filtro8: Registros cuyo producto ó artículo sean camisas y casacas
Filtro9: Pantalones de cualquier tipo de los meses enero, febrero y marzo y ventas <= 1500
Filtro10: Ventas sean los meses enero, marzo, julio.
Filtro11: Pantalón Jean y Casaca cuero, meses junio y julio, ventas >= 1000 y < 4000
Filtro12: Vendedor Zegarra, meses, enero, febrero y marzo, y ventas >=2000
Filtro13: Vendedor Miranda y Ramirez, meses: mayo, junio, julio, ventas entre 800 y 1500
Filtro14: Artículos que empiecen con la letra "C", y vendedores sean Gonzales y Guevara
Filtro15: Las 10 mejores ventas de menor a mayor (anotar el primer y último valor)

Tacna, 2019 2 Prof. Manuel Mendoza

 Microsoft Office Excel 2016
Carrera: Asistente Informático en Ingeniería y Arq.
Nivel Intermedio-Avanzado

FILTROS AVANZADOS.-
El comando Filtro Avanzadas permite filtrar una lista o tabla de datos por medio de criterios más complejos, para
la cual es necesario crear un rango de criterios. Este rango de criterios debe crearse fuera de la tabla de datos
de preferencia encima de los encabezados de la tabla. La diferencia con filtros automáticos es que se puede
extraer los datos a otro lugar u hoja.
Rango de criterios.
Es un rango formado por una fila de rótulos o etiquetas de los encabezados de la tabla a filtrar y por una fila
vacía debajo de estos rótulos donde debe escribir los condiciones de filtrado.
Consideraciones para crear rango de criterios:
1. Debe escribir, de preferencia copiar el o los encabezados de la(s) columna(s) de la tabla en una fila vacía
de preferencia por encima de la tabla o en otra hoja vacía.
2. Ingresar la condición o criterio de filtrado debajo del encabezado.
3. Luego aplicar filtros avanzadas.
Se pueden crear dos tipos de rango de criterios: De comparación múltiples y criterios calculados.
CRITERIOS DE COMPARACIÓN MÚLTIPLE O COMBINADA.-
Permiten seleccionar filas por más de una condición para lo cual es necesario especificar entradas en más de
una celda situada debajo de los rótulos de las columnas en el rango de criterios. Los criterios combinados
pueden ser:
a) Criterios Y lógicos: Para diferentes campos y para un mismo campo
b) Criterios O lógicos: Para diferentes campos y para un mismo campo
c) Criterios Y y O lógicos.
Cargue el archivo COMERCIAL MODA FORMULARIOS y guárdelo con el nombre COMERCIAL
MODA FILTROS AVANZADOS. Dar el nombre MODA al rango A4:F244
A) Criterios Y lógicos.- Cuando los criterios, realizan dos o más entradas en la misma fila.
a. Criterios Y lógicos para diferentes campos. (Distintas columnas y criterios en distintas col.)
Ejemplo:Extraer en una nueva hoja las ventas
mayores a 2000 para el vendedor Miranda. Pasos:
1. En la Hoja2, debe definir el rango de criterios
2. En la Hoja2, clic en cualquier celda (A7)
3. Ficha Datos, Ordenar y filtrar, clic Avanzadas.

4. En Rango de lista, escribe MODA .

5. Rango de criterios, debe seleccionar B3:C4
6. Activar el botón Copiar a otro lugar
(Se han extraído 29 registros) 7. Clic en campo Copiar a: de la Hoja2,
b. Criterios Y lógicos para un mismo campo. selecciona el rango vacío A7:F7 y Aceptar.
(Criterios para una misma columna, debe
copiarse nuevamente la misma columna en forma contigua y criterios en distintas columnas)
Ejemplo: Se desea extraer en una nueva hoja las ventas comprendidas entre 2800 y 3500
1. En la Hoja3 debe escribir los criterios correspondientes tal como se indican en la
ilustración siguiente y extraer los registros.
2. En la Hoja3, y en rango A7:F7 debe extraerse los datos. (36 registros)

Tacna, 2019 3 Prof. Manuel Mendoza

 Microsoft Office Excel 2016
Carrera: Asistente Informático en Ingeniería y Arq.
Nivel Intermedio-Avanzado

B) Criterios O lógicos.- Cuando los criterios se realizan en dos o más filas

a. Criterio lógico O para diferentes campos.(Distintas columnas y criterios en distintas filas)
Ejemplo: Seleccionar o extraer ventas menores a 1000 ó sean del mes de enero

El rango de criterios ahora es

desde B3:C5. (se extrajo 75
registros)
b. Criterio lógico O para un mismo campo.- (Una columna y criterios en distintas filas)
Ejemplo: Se desea extraer en una nueva hoja las ventas NO comprendidas entre 2800 y
3500.

El rango de criterios ahora es

desde B3:B5. (se extrajo 204 reg)

C) Criterios Y y O lógicos .- (Distintas columnas y criterios en distintas columnas; y criterios en

distintas filas).Los criterios se realizan en dos entradas y en dos o más filas.
Ejemplo: Se desea extraer en una nueva hoja las ventas mayores o iguales a 2500 y que
correspondan a los meses de junio o julio

El rango de criterios ahora es

desde B3:C5 (se extraje 26
registros)

TAREA.- Realice un filtro avanzado que extraiga registros para artículos cuya primera letra empiece
con "C", sean de Talla Medio y sean de losmeses de enero o febrero. (Rpta.:8 registros)

Tacna, 2019 4 Prof. Manuel Mendoza

 Microsoft Office Excel 2016
Carrera: Asistente Informático en Ingeniería y Arq.
Nivel Intermedio-Avanzado

4. RESUMEN DE DATOS CON SUBTOTALES

Una vez ordenada y/o filtrada la tabla o base de datos, algunas veces es necesario crear informes
resumen, a través del comando Subtotales.
Subtotales es un comando que permite añadir líneas de subtotal a cada grupo de datos de la lista o
tabla de datos. La orden subtotales crea también totales generales.
Para obtener subtotales primero se debe ordenar la tablasegún criterio de ordenación (puede
aplicar luego un filtro) y luego aplicar subtotales.
Para ejecutar Subtotales ubique el cursor en cualquier parte dentro
de la lista de datos, luego hacer clic en la ficha Datos, grupo
esquema y clic en Subtotal.
1. Cargue el archivos COMERCIAL MODA FORMULARIOS,
guárdelo con el nombre COMERCIAL MODA SUBTOTALES.
2. Primero copie todos los datos de la Hoja1 en las 10 siguientes
hojas y cambiar Hoja2 por Subt1, Hoja3 por Subt2, Hoja4 por
Subt3 y así hasta Hoja16 por Subt15.
3. Ejemplo demostrativo: Se desea obtener los subtotales de
cada artículo y su total general.
a. De la Hoja1, ordena primeramente la tabla por Nombre
artículo.
b. En la Hoja1, Ubica el cursor en cualquier parte dentro de la
tabla.
c. Ficha Datos, grupo esquema y clic en subtotal. Aparece la
ventana de subtotales. Debe elegir y/o marcar los datos tal
como se aprecia en la ilustración de la derecha.
Para ver el
resumen se
puede
esquematizar
la tabla
pulsando el 2.
Se obtiene
todos los
subtotales de
cada artículo.

4. Ubicar el cursor en cualquier parte dentro de la tabla de las siguientes hojas y proceda a aplicar
subtotales a las siguientes acciones. Deben anotar los resultados.
a) Subt1 : Monto de las Ventas de los siguientes meses:
Enero: __________ Febrero:__________ Marzo: __________ Abril : __________
b) Subt2 : Monto de las Ventas realizado por los Vendedores:
Gonzales: _________Guevara:________ Miranda:_______ Ramirez:_______ Zegarra:_____
c) Subt3: Monto de las ventas de los siguientes artículos
Camisa jh:__________ Camisa pc:__________ Casaca cuero:__________
Pantalón Baronet:________ Pantalón jean: _________ Pantalón Alphi:_________

Tacna, 2019 5 Prof. Manuel Mendoza

 Microsoft Office Excel 2016
Carrera: Asistente Informático en Ingeniería y Arq.
Nivel Intermedio-Avanzado

d) Subt4: Monto de las Ventas por talla:

Samll:_______ Short:________ Medio:_______ Large:_______ Extra large:________
e) Subt5 : Cuántos artículos se vendieron para:
Camisa jh:________Camisa pc:_______ Pantalón Jean:________ Pantalón Baronet:_______
f) Subt6 : Cuántos artículos se vendieron para los siguientes vendedores:
Gonzales: _____ Guevara:______ Miranda:______ Ramirez:______ Zegarra:______
g) Subt7 : Monto de las ventas sólo meses enero, febrero y marzo y por vendedores:
Enero-Gonzales: _______Febrero-Gonzales:________ Marzo-Gonzales:_________
Enero-Guevara:_______ Febrero-Guevara:_________ Marzo-Guevara:_________
Marzo-Ramirez:________ Febrero-Zegarra:________ Marzo-Zegarra:_________
h) Subt8 :Revisando el esquema o nivel de anidamiento averiguar la venta mínima por vended.
Gonzales:______ Guevara:______ Miranda:______ Ramirez:______ Zegarra:______
i) Subt9 : Averiguar la cantidad de productos vendidos por mes:
Enero:_____ Febrero:_____ Marzo:_____ Abril:_____ Mayo:_____ Junio:_____ Julio:_____
j) Subt10 : Promedio de Ventas de sólo Camisa jh y para meses mayo, junio y julio:
Mayo:__________ Junio:__________ Julio:__________ Promedio General:_________

5. FUNCIONES PARA BASE DE DATOS

Muchas veces la ordenación, filtrado y subtotales a pesar de ser herramientas muy útiles cuando se
trabaja en una misma tabla, pero a veces es necesario trabajar con más tablas de datos o se desea
hacer referencia a las tablas de datos y obtener datos de manera puntual, es entonces cuando se
puede utilizar funciones de base de datos.
Excel posees 12 funciones que operan sobre los registros de la base de datos. Todas ellas tienen el
mismo formato. Veremos las funciones BDSUMA, BDCONTAR,BDPROMEDIO, BDMAX y BDMIN.
FUNCION BDSUMA.- Suma los números de un campo (columna) de registros de una lista o base de
datos que cumplen las condiciones especificadas.
Sintaxis: BDSUMA(base_de_datos, nombre_de_campo,criterios)
Ejemplo demostrativo:
1. Cargue el archivo COMERCIAL MODA FORMULARIO y guárdelo con el nombre COMERCIAL
MODA FUNCIONES BD. Poner nombre de rango BDMODA al rango A4:F244.
2. En la hoja2 proceda a crear el siguiente criterio de la ilustración siguiente. (A2:C4)
3. En base a dicho criterio y sólo para el artículo Camisa jh, se desea obtener la cantidad de
artículos vendidos, la suma total, promedio, la venta máxima y la venta mínima.

TAREA CALIFICADA
Use funciones de base de datos y con archivo COMERCIAL MODA FUNCIONES BD, realice las
siguientes acciones:
1. Defina un criterio para las ventas mayores a 2000 y sean de enero o de febrero o marzo y en
base a dicho criterio obtener suma total y el promedio. Rpta: 160615 y 3277.85
FIN DE LA PRACTICA

Tacna, 2019 6 Prof. Manuel Mendoza