Cálculos, funciones y filtros avanzados
Introducción
Ya hemos visto cómo agrupar, filtrar la información, y cómo insertar un campo calculado en una tabla dinámica. Este capítulo le va a permitir profundizar en estas técnicas, y para ello vamos a utilizar los siguientes archivos:
-
Una base de datos de facturación: Facturación.xlsx.
-
Una lista de las ventas diarias de un distribuidor de productos de mantenimiento: PdtsMantenimiente.xlsx.
-
Una lista de estancias realizadas por una entidad de formación: ListaCursos.xlsx.
Conocimientos necesarios |
Técnicas de creación de tablas dinámicas |
Lo que va a aprender |
Elementos calculados La función IMPORTARDATOSDINÁMICOS |
Filtrar, agrupar y calcular porcentajes
Abra el archivo Facturación.xlsx.
![images/OST10_196.png images/OST10_196.png](https://www.eni-training.com/download/cbae4bfa-8865-45fa-914e-69ab74f1f280/images/OST10_196.png?id=AAEAAAD%2f%2f%2f%2f%2fAQAAAAAAAAAMAgAAAE1FbmkuRWRpdGlvbnMuTWVkaWFwbHVzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAJ0VuaS5FZGl0aW9ucy5NZWRpYXBsdXMuQ29tbW9uLldhdGVybWFyawIAAAAHcGlzVGV4dAlwaWR0ZURhdGUBAA0CAAAABgMAAAAwdGVzdCB2Ml84IC0gZmZhNjQzOWQtZDZjYi00NzYxLWJhYTMtZWZkZDNiZDcwMWM25rSq0F7p2IgL)
La hoja Facturas de este libro contiene información relativa a cada factura emitida por la empresa en dos años (2020 y 2021). Consideraremos que estamos en 2022 y que nuestro origen de datos es fijo, ya que no emitiremos facturas con fecha de 2020 y 2021.
Nuestro objetivo es realizar comparativas anuales.
Volumen de negocio anual por tipo de cliente
Uno de los primeros datos que debe conocer un administrador es el porcentaje de variación del volumen de negocio de un tipo de cliente respecto al volumen efectuado por el mismo tipo de cliente el año anterior.
Para obtener una legibilidad óptima, es recomendable adoptar una disposición que agrupe las cifras por tipos y, dentro de los tipos, que se detalle todo por año.
Inserte la tabla dinámica en una nueva hoja tal y como se muestra en la siguiente imagen (arrastre solo los campos TIPO CLIENTE y FECHA FACTURA al área Filas; los elementos Trimestres y Años se insertarán automáticamente):
![images/OST10_197.png images/OST10_197.png](https://www.eni-training.com/download/cbae4bfa-8865-45fa-914e-69ab74f1f280/images/OST10_197.png?id=AAEAAAD%2f%2f%2f%2f%2fAQAAAAAAAAAMAgAAAE1FbmkuRWRpdGlvbnMuTWVkaWFwbHVzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAJ0VuaS5FZGl0aW9ucy5NZWRpYXBsdXMuQ29tbW9uLldhdGVybWFyawIAAAAHcGlzVGV4dAlwaWR0ZURhdGUBAA0CAAAABgMAAAAwdGVzdCB2Ml84IC0gZmZhNjQzOWQtZDZjYi00NzYxLWJhYTMtZWZkZDNiZDcwMWM25rSq0F7p2IgL)
La tabla dinámica se presenta a continuación:
![images/OST10_198.png images/OST10_198.png](https://www.eni-training.com/download/cbae4bfa-8865-45fa-914e-69ab74f1f280/images/OST10_198.png?id=AAEAAAD%2f%2f%2f%2f%2fAQAAAAAAAAAMAgAAAE1FbmkuRWRpdGlvbnMuTWVkaWFwbHVzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAJ0VuaS5FZGl0aW9ucy5NZWRpYXBsdXMuQ29tbW9uLldhdGVybWFyawIAAAAHcGlzVGV4dAlwaWR0ZURhdGUBAA0CAAAABgMAAAAwdGVzdCB2Ml84IC0gZmZhNjQzOWQtZDZjYi00NzYxLWJhYTMtZWZkZDNiZDcwMWM25rSq0F7p2IgL)
Finalice la tabla dinámica modificando las etiquetas y dando formato a los números.
![images/OST10_199.png images/OST10_199.png](https://www.eni-training.com/download/cbae4bfa-8865-45fa-914e-69ab74f1f280/images/OST10_199.png?id=AAEAAAD%2f%2f%2f%2f%2fAQAAAAAAAAAMAgAAAE1FbmkuRWRpdGlvbnMuTWVkaWFwbHVzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAJ0VuaS5FZGl0aW9ucy5NZWRpYXBsdXMuQ29tbW9uLldhdGVybWFyawIAAAAHcGlzVGV4dAlwaWR0ZURhdGUBAA0CAAAABgMAAAAwdGVzdCB2Ml84IC0gZmZhNjQzOWQtZDZjYi00NzYxLWJhYTMtZWZkZDNiZDcwMWM25rSq0F7p2IgL)
Si desea hacer un seguimiento periódico durante todo el año, configure un rango dinámico para poder integrar fácilmente las nuevas facturas en su tabla dinámica....
Los elementos calculados
El libro PdtsMantenimiento.xlsx le va a permitir insertar elementos calculados en nuestra tabla dinámica.
Imagine que es responsable de una empresa que distribuye productos de mantenimiento y debe hacer un seguimiento de la cifra de negocios. La hoja VENTAS de este libro se ha extraído de su gestión comercial.
![images/OST10_209.png images/OST10_209.png](https://www.eni-training.com/download/cbae4bfa-8865-45fa-914e-69ab74f1f280/images/OST10_209.png?id=AAEAAAD%2f%2f%2f%2f%2fAQAAAAAAAAAMAgAAAE1FbmkuRWRpdGlvbnMuTWVkaWFwbHVzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAJ0VuaS5FZGl0aW9ucy5NZWRpYXBsdXMuQ29tbW9uLldhdGVybWFyawIAAAAHcGlzVGV4dAlwaWR0ZURhdGUBAA0CAAAABgMAAAAwdGVzdCB2Ml84IC0gZmZhNjQzOWQtZDZjYi00NzYxLWJhYTMtZWZkZDNiZDcwMWM25rSq0F7p2IgL)
Vendemos 8 familias de productos:
-
AMBIENTADORES
-
DESINFECTANTES
-
JABONES
-
LIMPIADORES CRISTALES
-
LIMPIADORES SANITARIOS
-
LIMPIADORES SUELOS
-
RECICLAJE
-
SECADORES
Los sectores son:
-
ESTE
-
NORTE
-
OESTE
-
CENTRO
-
SUR
Los tipos de clientes son:
-
AERONÁUTICO
-
AGRICULTURA
-
AGROALIMENTARIO
-
AUTOMÓVIL
-
CONSTRUCCIÓN
-
EDUCACIÓN
-
RESTAURACIÓN
-
SALUD
-
SERVICIOS
La tabla dinámica que vamos a crear es muy simple. Vamos a calcular el volumen de negocio de cada sector por familia de productos.
Cree una tabla dinámica, modifique el nombre de las etiquetas y dé formato a los números como en la siguiente imagen:
![images/OST10_210.png images/OST10_210.png](https://www.eni-training.com/download/cbae4bfa-8865-45fa-914e-69ab74f1f280/images/OST10_210.png?id=AAEAAAD%2f%2f%2f%2f%2fAQAAAAAAAAAMAgAAAE1FbmkuRWRpdGlvbnMuTWVkaWFwbHVzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAJ0VuaS5FZGl0aW9ucy5NZWRpYXBsdXMuQ29tbW9uLldhdGVybWFyawIAAAAHcGlzVGV4dAlwaWR0ZURhdGUBAA0CAAAABgMAAAAwdGVzdCB2Ml84IC0gZmZhNjQzOWQtZDZjYi00NzYxLWJhYTMtZWZkZDNiZDcwMWM25rSq0F7p2IgL)
Nuestro propósito es definir, para el año próximo, los objetivos de venta de cada familia de productos para cada sector.
El objetivo se calcula respecto al volumen o cifra de negocio realizado este año:
-
CENTRO +10 %
-
OTROS SECTORES +5 %
Un campo calculado permite realizar cálculos entre diferentes campos de una tabla dinámica. Un elemento calculado permite realizar cálculos con elementos de un mismo campo.
Vamos a insertar...
La función IMPORTARDATOSDINAMICOS
Objetivo
Imagine que dirige una academia de formación; al final de cada año, evalúa con cada uno de sus formadores externos los servicios que han proporcionado. Obviamente, menciona la calidad de los servicios, pero también la facturación alcanzada.
No quiere mostrarles las cifras del resto de los formadores, pero sí cuál es el coste de su trabajo respecto del coste total del conjunto de formadores.
Para estudiar el uso de la función IMPORTARDATOSDINAMICOS, vamos a utilizar el archivo ListaCursos.xlsx, cuyas primeras filas presentamos en esta captura:
![images/OST10_221.png images/OST10_221.png](https://www.eni-training.com/download/cbae4bfa-8865-45fa-914e-69ab74f1f280/images/OST10_221.png?id=AAEAAAD%2f%2f%2f%2f%2fAQAAAAAAAAAMAgAAAE1FbmkuRWRpdGlvbnMuTWVkaWFwbHVzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAJ0VuaS5FZGl0aW9ucy5NZWRpYXBsdXMuQ29tbW9uLldhdGVybWFyawIAAAAHcGlzVGV4dAlwaWR0ZURhdGUBAA0CAAAABgMAAAAwdGVzdCB2Ml84IC0gZmZhNjQzOWQtZDZjYi00NzYxLWJhYTMtZWZkZDNiZDcwMWM25rSq0F7p2IgL)
Cree esta tabla dinámica en una nueva hoja, modifique el nombre de las etiquetas y dé formato a los números como en la siguiente imagen.
![images/OST10_222.png images/OST10_222.png](https://www.eni-training.com/download/cbae4bfa-8865-45fa-914e-69ab74f1f280/images/OST10_222.png?id=AAEAAAD%2f%2f%2f%2f%2fAQAAAAAAAAAMAgAAAE1FbmkuRWRpdGlvbnMuTWVkaWFwbHVzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAJ0VuaS5FZGl0aW9ucy5NZWRpYXBsdXMuQ29tbW9uLldhdGVybWFyawIAAAAHcGlzVGV4dAlwaWR0ZURhdGUBAA0CAAAABgMAAAAwdGVzdCB2Ml84IC0gZmZhNjQzOWQtZDZjYi00NzYxLWJhYTMtZWZkZDNiZDcwMWM25rSq0F7p2IgL)
Modifique el nombre de la hoja creada por ESTADO.
Si le presenta la tabla dinámica tal y como se ha creado, cada uno conocerá el volumen de negocio de sus compañeros.
Necesitamos construir una tabla que extraiga los datos de la tabla dinámica. Cuando se seleccione el nombre de un formador en una lista desplegable, solo deben aparecer los datos relativos a dicho formador.
Un pequeño gráfico ilustrará los datos.
A continuación, puede ver la tabla y el gráfico que se deben crear.
![images/OST10_231.png images/OST10_231.png](https://www.eni-training.com/download/cbae4bfa-8865-45fa-914e-69ab74f1f280/images/OST10_231.png?id=AAEAAAD%2f%2f%2f%2f%2fAQAAAAAAAAAMAgAAAE1FbmkuRWRpdGlvbnMuTWVkaWFwbHVzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAJ0VuaS5FZGl0aW9ucy5NZWRpYXBsdXMuQ29tbW9uLldhdGVybWFyawIAAAAHcGlzVGV4dAlwaWR0ZURhdGUBAA0CAAAABgMAAAAwdGVzdCB2Ml84IC0gZmZhNjQzOWQtZDZjYi00NzYxLWJhYTMtZWZkZDNiZDcwMWM25rSq0F7p2IgL)
Introduzca los primeros elementos de la tabla en la hoja ESTADO a partir de la columna I.
![images/OST10_223.png images/OST10_223.png](https://www.eni-training.com/download/cbae4bfa-8865-45fa-914e-69ab74f1f280/images/OST10_223.png?id=AAEAAAD%2f%2f%2f%2f%2fAQAAAAAAAAAMAgAAAE1FbmkuRWRpdGlvbnMuTWVkaWFwbHVzLCBWZXJzaW9uPTEuMC4wLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49bnVsbAUBAAAAJ0VuaS5FZGl0aW9ucy5NZWRpYXBsdXMuQ29tbW9uLldhdGVybWFyawIAAAAHcGlzVGV4dAlwaWR0ZURhdGUBAA0CAAAABgMAAAAwdGVzdCB2Ml84IC0gZmZhNjQzOWQtZDZjYi00NzYxLWJhYTMtZWZkZDNiZDcwMWM25rSq0F7p2IgL)
Configure en J4 la lista desplegable que permitirá seleccionar un formador.
Coloque el cursor en J4.
En la pestaña Datos, grupo Herramientas de datos, haga clic en el botón Validación de datos.
En la sección Permitir del cuadro de diálogo...