Date post: | 05-Jul-2018 |
Category: |
Documents |
Upload: | rodolfo-orellana |
View: | 222 times |
Download: | 0 times |
of 14
8/15/2019 excel intermedio: guia de tablas y filtros
1/14
Página 1
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Crear tablas
Tablas
Cuando se crea una tabla, sus datos se pueden administrar y analizar
independientemente de la información que haya fuera de ella. Por ejemplo, puedes
filtrar las columnas de la tabla, agregar filas para totales, aplicar un formato a la tabla,
etc.
Puedes generar tablas:
Creando una tabla a partir de un rango de celdas.
Aplicando un formato de tabla a un rango.
Actividad 1: Crear tablas
Generar y aplicar formato a tablas.
Crear tablas
1. Descarga el libro Listas y tablas.
2. En la hoja Datos, haz clic dentro de la lista de datos.
3. Luego, en la ficha Insertar, grupo Tablas, haz clic en el botón Tabla.
4. Se muestra un cuadro que señala automáticamente el rango de toda su tabla.
5. Al hacer clic en Aceptar, el rango se muestra con un formato y cada columna se
muestra con unas flechas para aplicar filtros.
Curso virtual: Microsoft Excel
Semana 04: Lista, tabla y filtros
8/15/2019 excel intermedio: guia de tablas y filtros
2/14
Página 2
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Crear tablas
Aplicar formatos a tablas
1. Haz clic dentro de la tabla que acabas de crear.
2. Luego, en la ficha Diseño, ubica al grupo Estilos de tabla.
3. Usa el botón Más para desplegar toda la galería y, a continuación, en el
grupo Claro, Medio u Oscuro, haz clic en el estilo de tabla que deseas
utilizar.
4. Con el mouse, señala alguno de los estilos predefinidos y observa que se
tiene una vista previa directamente sobre la tabla.
5. Haz clic en el Estilo 9 del grupo Claro. Luego haz clic en el Estilo 9 del
grupo Oscuro.
6. Finalmente, aplica el Estilo 10 del grupo Claro.
7. Guarda los cambios efectuados.
8/15/2019 excel intermedio: guia de tablas y filtros
3/14
Página 3
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Crear tablas
Modificar las opciones de estilos
El estilo aplicado a la tabla abarca opciones para las filas de encabezado y totales, asícomo para determinadas columnas.
1. Haz clic sobre la tabla.
2. En la ficha Diseño, ubica al grupo Opciones de estilo de tabla.
3. Ahora, activa y desactiva cada casilla y observa el efecto que produce
sobre la tabla.
Quitar el formato de tabla
Toda tabla posee un estilo o un formato definido. En este ejercicio se verá la forma deeliminar dicho estilo.
1. Haz clic dentro de la tabla.
2. Luego, en la ficha Diseño, ubica el grupo Estilos de tabla.
3. Usa el botón Más para desplegar toda la galería. Elige la opción Borrar
de la parte inferior de la galería.
4. Aplica el formato del grupo Medio, estilo de tabla medio 3 a la tabla.
5. Guarda los cambios.
8/15/2019 excel intermedio: guia de tablas y filtros
4/14
Página 4
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Campos calculados y totales
Actividad 2: Campos calculados y totales
Emplear referencias estructuradas para escribir fórmulas y agregar fila de totales para
obtener valores de resumen.
Columnas calculadas
Cuando escribes fórmulas en un rango convertido a tabla, ya no es necesario copiarlasa las otras filas, Excel automáticamente realiza dicha tarea.
1. Lleva el indicador de celda a la celda G7, escribe el texto Total.
2. En la celda G8 escribe la fórmula:
=D8 + E8 + F8
3. Al presionar Enter , la fórmula se copia a todas las filas de la tabla, esta es
una característica de una tabla.
Otra forma de escribir fórmulas, es usando referencias estructuradas de los títulos decada columna como parte de la fórmula:
1. Lleva el indicador de celda a la celda H7, escribe el texto Costo Total.
2. En la celda H8, escribe =[
3. Observa la lista de campos que se despliega:
4. Selecciona el campo Total y cierre el corchete ( ] ). Luego, multiplica por la
celda F5. Presiona la tecla F4 para convertirla a celda absoluta.
5. La fórmula se verá así:
= [Total] * $F$5
Aplicar formatos de número a la columna
Para aplicar formato a su columna resultante no es necesario hacer un arrastre paraseleccionar todas las celdas.
1. Lleva el puntero del mouse sobre el nombre de la columna. En nuestro
caso, sobre la columna Costo Total.
8/15/2019 excel intermedio: guia de tablas y filtros
5/14
Página 5
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Campos calculados y totales
2. Al mostrarse la flecha, haz clic para seleccionar dicha columna (ver la
imagen).
3. Luego, usa el botón Estilo millares.
4. El resultado debe ser como:
5. Guarda los resultados.
Mostrar y calcular totales
Con tablas es posible obtener totales para sus columnas de datos. Por ejemplo,podrás sumar una columna y obtener el promedio de otra, contar los registros de unacolumna, etc.
1. Haz clic sobre alguna celda de la tabla. En la ficha Diseño activa la opción
Fila de totales.
2. Se muestra una fila al final de la tabla, elige la función Suma.
8/15/2019 excel intermedio: guia de tablas y filtros
6/14
Página 6
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Filtros
3. En la columna Adultos y total aplica la función Suma de la misma manera.
La fila 38 se verá así:
4. Emplea un procedimiento similar y halla los resultados:
Promedio de atenciones a Adultos mayores.
Número de casos atendidos.
El resultado debe ser así:
5. Guarda los cambios efectuados.
Actividad 3: Filtros
Los filtros permiten listar registros que cumplan una determinada condición o criterio.
Por ejemplo, podemos listar sólo los registros de un distrito o aquellos atendidos por
un Especialista.
Aplicar un filtro
Cuando conviertes un rango a tabla, Excel muestra en cada columna un indicador defiltro:
Para mostrar sólo los registros de Los Olivos:
1. Haz clic en el indicador de filtro del campo Distrito.
8/15/2019 excel intermedio: guia de tablas y filtros
7/14
Página 7
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Criterios Personalizados
2. La casilla de Los Olivos debe ser la única seleccionada.
3. Haz clic en Aceptar y observa los cambios. La columna usada para aplicar
filtros, se muestra con un símbolo característico:
4. Para mostrar todos los registros, haga clic en el cuadro de filtros de la
columna Distrito y elige la opción Borrar filtro.
5. Puedes aplicar más de un filtro a su tabla. Por ejemplo, se pide mostrar los
registros de Breña atendidos por un Especialista. En este caso, debe
aplicar filtros a las columnas Distrito y Atención. El resultado se debe
mostrar de acuerdo a:
Observa que la fila de totales se actualiza con el nuevo filtro aplicado,
mostrando siete registros.
Recuerda, para mostrar todos los registros, debes borrar el filtro de las dos
columnas usadas: Distrito y Atención.
Actividad 4: Criterios Personalizados
Ejemplo 1Podemos emplear criterios personalizados para los filtros, por ejemplo que se
muestren los registros donde la atención a niños sea mayor a 15.
Para aplicar criterios personalizados:
1. En la columna Niños, haz clic en la flecha de filtros y elige Filtro deNúmero.
8/15/2019 excel intermedio: guia de tablas y filtros
8/14
Página 8
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Criterios Personalizados
2. Elige la opción Mayor que y en el cuadro Autofiltro personalizado escribe15 al lado de es mayor que…
3. Clic en Aceptar y observa los resultados:
Ejemplo 2
Si deseamos ver los registros de un total de atendidos entre 20 y 30 casos.
1. Cancela el filtro anterior (en la ficha Datos, grupo Ordenar y filtrar haz clic
en filtro).
2. Repite la orden anterior para mostrar los botones de filtros en la fila 7.
3. En la columna Total, haz clic en la flecha de filtros y elige Filtro de
Número.
4. Elige la opción Entre y en el cuadro Autofiltro personalizado escribe
5. Clic en Aceptar y observa los resultados:
8/15/2019 excel intermedio: guia de tablas y filtros
9/14
Página 9
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Criterios Personalizados
6. Borra el filtro aplicado.
Ejemplo 3
Además, podemos usar criterios más elaborados. Por ejemplo, que se muestren los
registros que han atendido más que el promedio.
1. En la columna Total, haz clic en la flecha de filtros y elige Filtro denúmero.
2. Activa la opción Superior del promedio:
3. Haz clic en Aceptar y observa los resultados.
4. Luego, borra el filtro aplicado.
Ejemplo 4
Podemos mostrar los cinco registros con mayor atención.
1. En la columna Total, haz clic en la flecha de filtros y elige Filtro denúmero.
2. Activa la opción Diez mejores:
8/15/2019 excel intermedio: guia de tablas y filtros
10/14
Página 10
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Criterios Personalizados
3. Cambia el cuadro para mostrar:
4. Haz clic en Aceptar y observa que se muestran los registros de mayoratención.
5. Luego, borra el filtro aplicado.
Ejemplo 5
Para mostrar los tres registros de menor atención.
1. En la columna Total, haz clic en la flecha de filtros y elige Filtro denúmero.
2. Activa la opción Diez mejores. 3. Cambia el cuadro para mostrar:
4. Haz clic en Aceptar y observa que se muestran los registros de menoratención.
5. Luego, borra el filtro aplicado.6. Guarda los cambios y cierra Excel.
8/15/2019 excel intermedio: guia de tablas y filtros
11/14
Página 11
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Criterios Personalizados
Aplicando filtros
Descarga el archivo Listado clientes y resuelve cada pregunta en las hojas indicadas.
Caso 1:
Criterios de texto:
1. Listar los clientes cuyo nombre comience con C.
En la hoja 1: campo Nombre, elige filtro de texto, selecciona Comienza por.
Escribe la letra C, luego Aceptar.
Resolver en cada hoja:
2. Listar los clientes cuyo nombre termine en S.A.
3. Listar los clientes que contenga en su nombre el texto Nacional
Criterios de números:
4. Listar los clientes cuyo crédito sea menor a 5 mil
5. Listar los vendedores cuyo crédito sea mayor a 15 mil
6. Listar los clientes cuyo código esté en el intervalo del 30 a 50.
7. Listar los clientes cuyas crédito sean mayores al promedio
8. Listar los 5 clientes con mayores créditos
9. Listar los 3 clientes con menores créditos.
Criterios de fechas:
10. Listar los clientes que ingresaron el año 2009.
8/15/2019 excel intermedio: guia de tablas y filtros
12/14
Página 12
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Criterios Personalizados
11. Listar los vendedores que ingresaron en enero de cualquier año
12. Listar los vendedores que ingresaron antes del 2005
13. Listar los vendedores que ingresaron en el 4to trimestre de cualquier año.
Guarda el libro con el nombre Listado de clientes seguido de tu nombre y apellido. Cierra el
libro.
Caso 2:
Una empresa comercializadora con clientes en todo el mundo, desea analizar sus ventas por
vendedor, estableciendo diversos criterios que permitan responder preguntas como quién es el
vendedor con mayor volumen de ventas o cuáles son las ventas efectuadas a Lima. Así, es
necesario aplicar filtros a la tabla que contiene estos datos.
1. Descarga el archivo Consolidado de ventas.
2. Aplica filtros a la tabla.
3. Realice el procedimiento necesario para resolver las siguientes situaciones:
Criterios simples:
1. Listar los vendedores del Jockey Plaza.
2. Listar los vendedores que ingresaron en el año 2008.
3. Listar los vendedores cuyo destino de ventas sea el Perú (Lima, Arequipa, Cajamarca y
Loreto)
4. Listar los vendedores de MegaPlaza y cuyo destino sea Budapest.
5. Listar los vendedores de Jockey Plaza que ingresaron en el año 2000 y cuyo destino de
ventas sea Miami.
6. Listar los vendedores que ingresaron antes del año 2000.
Criterios integrados de texto:
7. Listar los vendedores cuyo apellido empiecen con C.
8. Listar los vendedores cuyo apellido contengan la letra doble l (ll)
9. Listar los vendedores cuyo apellido terminen con S.
Criterios integrados de números:
10. Listar los vendedores que tengan menos de 30 años.
11. Listar los vendedores cuyo total de ventas sea mayor a 3 mil.
12. Listar los vendedores cuya edad esté entre los 25 y 30 años.
13. Listar los vendedores cuyas ventas sean mayores al promedio.
14. Listar los 4 vendedores con mayores ventas.
15. Listar los 2 vendedores con menores ventas.
Criterios integrados de fechas:
8/15/2019 excel intermedio: guia de tablas y filtros
13/14
Página 13
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Criterios Personalizados
16. Listar los vendedores que ingresaron el año 2008.
17. Listar los vendedores que ingresaron entre el 1-1-2000 al 31-03-2000.
18. Listar los vendedores que ingresaron en abril de cualquier año.
19. Listar los vendedores que ingresaron en el 4to trimestre de cualquier año.
Guarda el libro con el nombre Consolidado de ventas seguido de tu nombre y apellido.
Personalizados
Todos los tipos de campo permiten la creación de filtros personalizados. En este caso se
presenta una ventana que nos permite construir nuestro propio criterio:
Excel permite utilizar comodines que son: (?) para representar un carácter individual y (*) para
representar cualquier serie de caracteres. Los comodines funcionan de la forma como se
muestra en el ejemplo:
Criterios Encuentra a
F* Fermín, Federico
Fer* Fernando
F?r* Fermín, Fernando
?er* Fermín, Fernando
?e* Fermín, Federico
Con las opciones Y y O se pueden establecer dos criterios. Con la opción Y los criterios se
suman (se tiene que cumplir con ambos criterios) y con la opción O va a buscar cualquiera de
los registros que cumplan con los criterios establecidos.
En el ejemplo, se desea mostrar a las ventas hacia Tokio o Pekín:
8/15/2019 excel intermedio: guia de tablas y filtros
14/14
TES - Cesar Oceda - Tatiana Riquelme
Microsoft Excel: Criterios Personalizados
Y para mostrar celdas vacías, puede:
Caso 3:
Emplear filtros con criterios personalizados para resolver las siguientes situaciones:
Criterios Personalizados
20. Listar los clientes cuya fecha de ingreso sea de Julio a agosto del 2008.
21. Listar los clientes cuya fecha de ingreso sea de enero a julio de 2007
22. Listar los clientes cuya 2da letra del nombre sea E.
23. Lista los clientes que tengan 3 letras en su nombre.
24. Listar los clientes que tengan en el código el número 4.
25. Listar los clientes que NO tengan en el código el número 4.
26. Listar los clientes cuyos apellidos terminen en D y sus nombres empiecen con L.
Guarda el libro con el nombre Consolidado de ventas seguido de tu nombre y
apellido. Cierra el libro.