Una de las cosas que más menciono sobre PowerPivot para Excel es la capacidad de agregar tablas de búsqueda a sus conjuntos de datos. La mayoría de las veces, los datos con los que está trabajando no tienen todos los campos que necesita para su análisis. Por ejemplo, puede tener un campo de fecha pero necesita agrupar sus datos por trimestre. Podría escribir una fórmula, pero es más fácil crear una tabla de búsqueda simple dentro del entorno PowerPivot.
También puede usar esta tabla de búsqueda para otra agrupación, como el nombre del mes y la primera / segunda mitad del año. En términos de almacenamiento de datos, en realidad está creando una tabla de dimensión de fecha. En este artículo, le daré un par de tablas de dimensiones de ejemplo para mejorar su proyecto de PowerPivot para Excel.
Nueva tabla de dimensiones de texto (búsqueda)
Consideremos una tabla con datos de pedido. Suponga que la tabla tiene campos para el cliente, la fecha del pedido, el total del pedido y el tipo de pedido. Vamos a centrarnos en el campo de tipo de orden. Supongamos que el campo de tipo de orden incluye valores como:
- Netbooks
- Escritorios
- Monitores
- Proyectores
- Impresoras
- Escáneres
- Cámaras digitales
- Cámaras réflex digitales
- Camaras de cine
- Videocámaras
- Teléfonos de oficina
- Teléfonos inteligentes
- PDAs
- Accesorios para celulares
En realidad, tendría códigos para estos, pero para mantener este ejemplo simple, suponga que estos son los valores reales en la tabla de orden. Con PowerPivot para Excel, podrá agrupar fácilmente sus pedidos por tipo de pedido.
¿Y si quisieras una agrupación diferente? Por ejemplo, suponga que necesita una agrupación de categorías como computadoras, cámaras y teléfonos. La tabla de orden no tiene un campo de categoría, pero puede crearla fácilmente como una tabla de búsqueda en PowerPivot para Excel. La tabla de búsqueda de muestra completa está abajo en tabla 1 .
Aquí están los pasos:
- Paso 1: Necesita una lista distinta del campo de tipo para su tabla de búsqueda. Este será tu campo de búsqueda. Desde su conjunto de datos, cree una lista distinta de valores del campo de tipo de orden. Ingrese la lista distinta de tipos en un libro de Excel. Etiqueta la columna Tipo .
- Paso 2: En la columna junto a su columna de búsqueda (Tipo), agregue el nuevo campo que desea agrupar. En nuestro ejemplo, agregue una columna con una etiqueta llamada Categoría .
- Paso 3: Para cada valor en su lista distinta de valores (tipos en este ejemplo), agregue la correspondiente Categoría valores. En nuestro ejemplo simple, ingrese Ordenadores , Camaras o Los telefonos en el Categoría columna.
- Etapa 4: Copia el Tipo y Categoría Tabla de datos en su portapapeles.
- Paso 5: Abra el libro de Excel con los datos del pedido en PowerPivot para Excel. Inicie la ventana de PowerPivot. Haga clic en Pegar que traerá su nueva tabla de búsqueda. Dale un nombre a la mesa y asegúrate de revisar Utilice la primera fila como encabezados de columna. Hacer clic DE ACUERDO. Ha creado una tabla de búsqueda en PowerPivot.
- Paso 6: Crear una relación entre el Tipo campo en el Orden mesa y el Categoría campo en la tabla de búsqueda. Haga clic en el Diseño cinta y elegir Crear relación. Haga las selecciones en el Crear relación diálogo y haga clic Crear.
Cuando cree una tabla dinámica en Excel basada en los datos de PowerPivot, podrá agrupar por su nuevo Categoría campo. Tenga en cuenta que PowerPivot para Excel solo admite combinaciones internas. Si tienes un tipo de orden faltando en su tabla de búsqueda, todos los registros correspondientes para ese tipo faltarán en cualquier tabla dinámica basada en los datos de PowerPivot. Tendrá que comprobar esto de vez en cuando.
Tabla de dimensión de fecha (búsqueda)
La tabla de búsqueda de fechas probablemente será necesaria en la mayoría de sus proyectos de PowerPivot para Excel. La mayoría de los conjuntos de datos tienen algún tipo de campo (s) de fecha. Hay funciones para calcular el año y el mes.
Sin embargo, si necesita el texto del mes real o el trimestre, debe escribir una fórmula compleja. Es mucho más fácil incluir una tabla de dimensiones de fecha (búsqueda) y compararla con el número del mes en su conjunto de datos principal. Deberá agregar una columna a su tabla de orden para representar el número del mes desde el campo de fecha de orden. La fórmula DAX para mes en nuestro ejemplo es = MES (Fecha de pedido). Esto devolverá un número entre 1 y 12 para cada registro. Nuestra tabla de dimensiones proporcionará valores alternativos, que enlazan con el número del mes. Esto le proporcionará flexibilidad en su análisis. La tabla completa de dimensiones de fecha de muestra se encuentra abajo en Tabla 2 .
La dimensión de la fecha o tabla de búsqueda incluirá 12 registros. La columna del mes tendrá los valores del 1 al 12. Otras columnas incluirán el texto abreviado del mes, el texto del mes completo, el trimestre, etc. Aquí están los pasos:
- Paso 1: Copie la tabla de la Tabla 2 a continuación y péguela en PowerPivot. Puedes crear esta tabla en Excel pero te estoy ahorrando tiempo. Debería poder pegar directamente desde los datos seleccionados a continuación si está utilizando Internet Explorer o Edge. PowerPivot recoge el formato de tabla en mis pruebas. Si está utilizando otro navegador, es posible que deba pegar primero en Excel y copiarlo desde Excel para seleccionar el formato de la tabla.
- Paso 2: Abra el libro de Excel con los datos del pedido en PowerPivot para Excel. Inicie la ventana de PowerPivot. Hacer clic Pegar que traerá su tabla de búsqueda copiada de la tabla de abajo o de Excel. Dale un nombre a la mesa y asegúrate de revisar Utilice la primera fila como encabezados de columna. Hacer clic DE ACUERDO. Ha creado una tabla de búsqueda de fechas en PowerPivot.
- Paso 3: Crear una relación entre el Mes campo en el Orden mesa y el MonthNumber campo en la tabla de búsqueda. Haga clic en el Diseño cinta y elegir Crear relación. Haga las selecciones en el Crear relación diálogo y haga clic Crear.
De nuevo, con la adición de una dimensión de fecha, podrá agrupar los datos en su tabla dinámica utilizando cualquiera de los diferentes valores de la tabla de búsqueda de fechas. Agrupar por trimestre o el nombre del mes será muy fácil.
Tablas de Dimensión de muestra (Búsqueda)
tabla 1
| Tipo | Categoría |
| Netbooks | Computadora |
| Escritorios | Computadora |
| Monitores | Computadora |
| Proyectores y pantallas | Computadora |
| Impresoras, escáneres y fax | Computadora |
| Configuración de la computadora y servicio | Computadora |
| Accesorios de computadoras | Computadora |
| Cámaras digitales | Cámara |
| Cámaras réflex digitales | Cámara |
| Camaras de cine | Cámara |
| Videocámaras | Cámara |
| Accesorios para Cámaras y Videocámaras | Cámara |
| Teléfonos para el hogar y oficina | Teléfono |
| Teléfonos de pantalla táctil | Teléfono |
| Teléfonos inteligentes y PDAs | Teléfono |
Tabla 2
| MonthNumber | MesTextShort | MonthTextFull | Trimestre | Semestre |
| 1 | ene | enero | Q1 | H1 |
| 2 | feb | febrero | Q1 | H1 |
| 3 | mar | marzo | Q1 | H1 |
| 4 | abr | abril | Q2 | H1 |
| 5 | Mayo | Mayo | Q2 | H1 |
| 6 | jun | junio | Q2 | H1 |
| 7 | jul | julio | Q3 | H2 |
| 8 | ago | agosto | Q3 | H2 |
| 9 | sep | septiembre | Q3 | H2 |
| 10 | oct | octubre | Q4 | H2 |
| 11 | nov | noviembre | Q4 | H2 |
| 12 | dic | diciembre | Q4 | H2 |
