Excel Power Pivot - Guía rápida

Excel Power Pivot es una herramienta eficaz y poderosa que viene con Excel como complemento. Con Power Pivot, puede cargar cientos de millones de filas de datos de fuentes externas y administrar los datos de manera efectiva con su potente motor xVelocity en una forma altamente comprimida. Esto hace posible realizar los cálculos, analizar los datos y llegar a un informe para sacar conclusiones y decisiones. Por lo tanto, una persona con experiencia práctica con Excel podría realizar el análisis de datos de alto nivel y la toma de decisiones en cuestión de minutos.

Este tutorial cubrirá lo siguiente:

Funciones de Power Pivot

Lo que hace que Power Pivot sea una herramienta sólida es el conjunto de funciones. Aprenderá las diversas funciones de Power Pivot en el capítulo: Funciones de Power Pivot.

Datos de Power Pivot de varias fuentes

Power Pivot puede recopilar datos de varias fuentes de datos para realizar los cálculos necesarios. Aprenderá cómo obtener datos en Power Pivot, en el capítulo: Carga de datos en Power Pivot.

Modelo de datos de Power Pivot

El poder de Power Pivot radica en su base de datos: modelo de datos. Los datos se almacenan en forma de tablas de datos en el modelo de datos. Puede crear relaciones entre las tablas de datos para combinar los datos de diferentes tablas de datos para análisis e informes. El capítulo Comprender el modelo de datos (base de datos de Power Pivot) le brinda detalles sobre el modelo de datos.

Gestión de relaciones y modelos de datos

Necesita saber cómo puede administrar las tablas de datos en el modelo de datos y las relaciones entre ellas. Obtendrá los detalles de estos en el capítulo: Administración del modelo de datos de Power Pivot.

Creación de tablas de Power Pivot y gráficos de Power Pivot

Power PivotTables y Power Pivot Charts le brindan una forma de analizar los datos para llegar a conclusiones y / o decisiones.

Aprenderá a crear Power PivotTables en los capítulos: Creación de Power PivotTable y Tablas dinámicas planas.

Aprenderá a crear Power PivotCharts en el capítulo - Power PivotCharts.

Conceptos básicos de DAX

DAX es el lenguaje utilizado en Power Pivot para realizar cálculos. Las fórmulas en DAX son similares a las fórmulas de Excel, con una diferencia: mientras que las fórmulas de Excel se basan en celdas individuales, las fórmulas de DAX se basan en columnas (campos).

Comprenderá los conceptos básicos de DAX en el capítulo: Conceptos básicos de DAX.

Exploración y generación de informes de datos de Power Pivot

Puede explorar los datos de Power Pivot que se encuentran en el modelo de datos con Power PivotTables y Power Pivot Charts. Aprenderá cómo puede explorar e informar datos a lo largo de este tutorial.

Jerarquías

Puede definir jerarquías de datos en una tabla de datos para que sea fácil manejar los campos de datos relacionados juntos en Power PivotTables. Aprenderá los detalles de la creación y el uso de jerarquías en el capítulo - Jerarquías en Power Pivot.

Informes estéticos

Puede crear informes estéticos de su análisis de datos con Power Pivot Charts y / o Power Pivot Charts. Tiene varias opciones de formato disponibles para resaltar los datos importantes en los informes. Los informes son de naturaleza interactiva, lo que permite a la persona que mira el informe compacto ver cualquiera de los detalles necesarios de forma rápida y sencilla.

Aprenderá estos detalles en el capítulo: Informes estéticos con datos de Power Pivot.

Power Pivot en Excel proporciona un modelo de datos que conecta varias fuentes de datos diferentes en función de las cuales se pueden analizar, visualizar y explorar los datos. La interfaz fácil de usar proporcionada por Power Pivot permite a una persona con experiencia práctica en Excel cargar datos sin esfuerzo, administrar los datos como tablas de datos, crear relaciones entre las tablas de datos y realizar los cálculos necesarios para llegar a un informe. .

En este capítulo, aprenderá qué hace que Power Pivot sea una herramienta sólida y buscada por analistas y tomadores de decisiones.

Power Pivot en la cinta

El primer paso para continuar con Power Pivot es asegurarse de que la pestaña POWERPIVOT esté disponible en la cinta. Si tiene Excel 2013 o versiones posteriores, la pestaña POWERPIVOT aparece en la cinta.

Si tiene Excel 2010, POWERPIVOT Es posible que la pestaña no aparezca en la cinta si aún no ha habilitado el complemento Power Pivot.

Complemento de Power Pivot

El complemento Power Pivot es un complemento COM que debe habilitarse para obtener las características completas de Power Pivot en Excel. Incluso cuando aparece la pestaña POWERPIVOT en la cinta, debe asegurarse de que el complemento esté habilitado para acceder a todas las funciones de Power Pivot.

Step 1 - Haga clic en la pestaña ARCHIVO en la cinta.

Step 2- Haga clic en Opciones en la lista desplegable. Aparece el cuadro de diálogo Opciones de Excel.

Step 3 - Siga las instrucciones de la siguiente manera.

• Haz clic en Complementos.

• En el cuadro Administrar, seleccione Complementos COM de la lista desplegable.

• Haga clic en el botón Ir. Aparece el cuadro de diálogo Complementos COM.

• Compruebe Power Pivot y haga clic en Aceptar.

¿Qué es Power Pivot?

Excel Power Pivot es una herramienta para integrar y manipular grandes volúmenes de datos. Con Power Pivot, puede cargar, ordenar y filtrar fácilmente conjuntos de datos que contienen millones de filas y realizar los cálculos necesarios. Puede utilizar Power Pivot como una solución de informes y análisis ad hoc.

La cinta de Power Pivot, como se muestra a continuación, tiene varios comandos, que van desde la gestión del modelo de datos hasta la creación de informes.

La ventana de Power Pivot tendrá la cinta como se muestra a continuación:

¿Por qué Power Pivot es una herramienta potente?

Cuando invoca Power Pivot, Power Pivot crea definiciones de datos y conexiones que se almacenan con su archivo de Excel en un formato comprimido. Cuando se actualizan los datos en la fuente, se actualizan automáticamente en su archivo de Excel. Esto facilita el uso de los datos que se mantienen en otros lugares, pero es necesario para estudiar el estudio de vez en cuando y tomar decisiones. Los datos de origen pueden tener cualquier forma, desde un archivo de texto o una página web hasta las diferentes bases de datos relacionales.

La interfaz fácil de usar de Power Pivot en la ventana de PowerPivot le permite realizar operaciones de datos sin el conocimiento de ningún lenguaje de consulta de base de datos. A continuación, puede crear un informe de su análisis en unos segundos. Los informes son versátiles, dinámicos e interactivos y le permiten investigar más a fondo los datos para obtener información y llegar a conclusiones / decisiones.

Los datos con los que trabaja en Excel y en la ventana de Power Pivot se almacenan en una base de datos analítica dentro del libro de Excel, y un potente motor local carga, consulta y actualiza los datos en esa base de datos. Dado que los datos están en Excel, están disponibles de inmediato para tablas dinámicas, gráficos dinámicos, Power View y otras características de Excel que usa para agregar e interactuar con los datos. Excel proporciona la presentación de datos y la interactividad, y los datos y los objetos de presentación de Excel están contenidos en el mismo archivo de libro de trabajo. Power Pivot admite archivos de hasta 2 GB de tamaño y le permite trabajar con hasta 4 GB de datos en la memoria.

Funciones de Power para Excel con Power Pivot

Las funciones de Power Pivot son gratuitas con Excel. Power Pivot ha mejorado el rendimiento de Excel con funciones de potencia que incluyen lo siguiente:

• Capacidad para manejar grandes volúmenes de datos, comprimidos en archivos pequeños, con una velocidad asombrosa.

• Filtre los datos y cambie el nombre de las columnas y tablas durante la importación.

• Organice las tablas en páginas con pestañas individuales en la ventana de Power Pivot en comparación con las tablas de Excel distribuidas por todo el libro de trabajo o varias tablas en la misma hoja de trabajo.

• Cree relaciones entre las tablas, para analizar los datos en las tablas de forma colectiva. Antes de Power Pivot, uno tenía que confiar en el uso intensivo de la función BUSCARV para combinar los datos en una sola tabla antes de realizar dicho análisis. Esto solía ser laborioso y propenso a errores.

• Agregue potencia a la tabla dinámica simple con muchas características adicionales.

• Proporcione el lenguaje de Expresiones de análisis de datos (DAX) para escribir fórmulas avanzadas.

• Agregue campos calculados y columnas calculadas a las tablas de datos.

• Cree KPI para usar en las tablas dinámicas y los informes de Power View.

Comprenderá las funciones de Power Pivot en detalle en el siguiente capítulo.

Usos de Power Pivot

Puede usar Power Pivot para lo siguiente:

• Para realizar análisis de datos de gran alcance y crear modelos de datos sofisticados.

• Para combinar grandes volúmenes de datos de varias fuentes diferentes rápidamente.

• Realizar análisis de información y compartir los conocimientos de forma interactiva.

• Escribir fórmulas avanzadas con el lenguaje de Expresiones de análisis de datos (DAX).

• Crear indicadores clave de rendimiento (KPI).

Modelado de datos con Power Pivot

Power Pivot proporciona funciones avanzadas de modelado de datos en Excel. Los datos de Power Pivot se administran en el modelo de datos, al que también se hace referencia como base de datos de Power Pivot. Puede usar Power Pivot para ayudarlo a obtener nuevos conocimientos sobre sus datos.

Puede crear relaciones entre las tablas de datos para poder realizar análisis de datos en las tablas de forma colectiva. Con DAX, puede escribir fórmulas avanzadas. Puede crear campos calculados y columnas calculadas en las tablas de datos en el modelo de datos.

Puede definir jerarquías en los datos para usar en cualquier lugar del libro de trabajo, incluido Power View. Puede crear KPI para usar en los informes de tablas dinámicas y Power View para mostrar de un vistazo si el rendimiento está dentro o fuera del objetivo para una o más métricas.

Inteligencia empresarial con Power Pivot

La inteligencia empresarial (BI) es esencialmente el conjunto de herramientas y procesos que las personas utilizan para recopilar datos, convertirlos en información significativa y luego tomar mejores decisiones. Las capacidades de BI de Power Pivot en Excel le permiten recopilar datos, visualizar datos y compartir información con personas de su organización en varios dispositivos.

Puede compartir su libro en un entorno de SharePoint que tenga habilitados los Servicios de Excel. En el servidor de SharePoint, Servicios de Excel procesa y representa los datos en una ventana del navegador donde otros pueden analizar los datos.

La característica más importante y poderosa de Power Pivot es su base de datos: modelo de datos. La siguiente característica importante es el motor de análisis en memoria xVelocity que hace posible trabajar en grandes bases de datos múltiples en cuestión de minutos. Hay algunas características más importantes que vienen con el complemento PowerPivot.

En este capítulo, obtendrá una breve descripción general de las funciones de Power Pivot, que se ilustran en detalle más adelante.

Carga de datos de fuentes externas

Puede cargar datos en el modelo de datos desde fuentes externas de dos formas:

• Cargue datos en Excel y luego cree un modelo de datos de Power Pivot.

• Cargue datos directamente en el modelo de datos de Power Pivot.

La segunda forma es más eficiente debido a la forma eficiente en que Power Pivot maneja los datos en la memoria.

Para obtener más detalles, consulte el capítulo: Carga de datos en Power Pivot.

Ventana de Excel y ventana de Power Pivot

Cuando comience a trabajar con Power Pivot, se abrirán dos ventanas simultáneamente: la ventana de Excel y la ventana de Power Pivot. Es a través de la ventana de PowerPivot que puede cargar datos en el modelo de datos directamente, ver los datos en la vista de datos y en la vista de diagrama, crear relaciones entre tablas, administrar las relaciones y crear informes de Power PivotTable y / o PowerPivot Chart.

No necesita tener los datos en tablas de Excel cuando está importando datos de fuentes externas. Si tiene datos como tablas de Excel en el libro de trabajo, puede agregarlos al modelo de datos, creando tablas de datos en el modelo de datos que están vinculadas a las tablas de Excel.

Cuando crea una tabla dinámica o un gráfico dinámico desde la ventana de Power Pivot, se crean en la ventana de Excel. Sin embargo, los datos aún se administran desde el modelo de datos.

Siempre puede cambiar entre la ventana de Excel y la ventana de Power Pivot en cualquier momento, fácilmente.

Modelo de datos

El modelo de datos es la característica más poderosa de Power Pivot. Los datos que se obtienen de varias fuentes de datos se mantienen en el modelo de datos como tablas de datos. Puede crear relaciones entre las tablas de datos para que pueda combinar los datos en las tablas para análisis e informes.

Aprenderá en detalle sobre el modelo de datos en el capítulo: Comprensión del modelo de datos (base de datos Power Pivot).

Optimización de memoria

El modelo de datos de Power Pivot utiliza almacenamiento xVelocity, que está muy comprimido cuando los datos se cargan en la memoria, lo que hace posible almacenar cientos de millones de filas en la memoria.

Por lo tanto, si carga datos directamente en el modelo de datos, lo hará en la forma eficiente altamente comprimida.

Tamaño de archivo compacto

Si los datos se cargan directamente en el modelo de datos, cuando guarda el archivo de Excel, ocupa muy menos espacio en el disco duro. Puede comparar los tamaños de los archivos de Excel, el primero cargando datos en Excel y luego creando el modelo de datos y el segundo cargando datos directamente en el modelo de datos omitiendo el primer paso. El segundo será hasta 10 veces más pequeño que el primero.

Power PivotTables

Puede crear Power PivotTables desde la ventana de Power Pivot. Las tablas dinámicas así creadas se basan en las tablas de datos en el modelo de datos, lo que hace posible combinar datos de las tablas relacionadas para análisis e informes.

Power PivotCharts

Puede crear Power PivotCharts desde la ventana de Power Pivot. Los gráficos dinámicos así creados se basan en las tablas de datos del modelo de datos, lo que hace posible combinar datos de las tablas relacionadas para su análisis y generación de informes. Los Power PivotCharts tienen todas las características de Excel PivotCharts y muchas más, como botones de campo.

También puede tener combinaciones de Power PivotTable y Power PivotChart.

Lenguaje DAX

La fuerza de Power Pivot proviene del lenguaje DAX que se puede usar de manera efectiva en el modelo de datos para realizar cálculos sobre los datos en las tablas de datos. Puede tener columnas calculadas y campos calculados definidos por DAX que se pueden usar en Power PivotTables y Power PivotCharts.

En este capítulo, aprenderemos a cargar datos en Power Pivot.

Puede cargar datos en Power Pivot de dos formas:

• Cargar datos en Excel y agregarlos al modelo de datos

• Cargue datos en PowerPivot directamente, rellenando el modelo de datos, que es la base de datos de PowerPivot.

Si desea los datos para Power Pivot, hágalo de la segunda manera, sin que Excel ni siquiera lo sepa. Esto se debe a que cargará los datos solo una vez, en formato altamente comprimido. Para comprender la magnitud de la diferencia, suponga que carga datos en Excel agregándolos primero al modelo de datos, el tamaño del archivo es digamos 10 MB.

Si carga datos en PowerPivot y, por lo tanto, en el modelo de datos omitiendo el paso adicional de Excel, el tamaño de su archivo podría ser tan solo de 1 MB.

Fuentes de datos compatibles con Power Pivot

Puede importar datos al modelo de datos de Power Pivot desde varias fuentes de datos o establecer conexiones y / o utilizar las conexiones existentes. Power Pivot admite las siguientes fuentes de datos:

• Base de datos relacional de SQL Server

• Base de datos de Microsoft Access

• Servicios de análisis de SQL Server

• Servicios de informes de SQL Server (SQL 2008 R2)

• Feeds de datos ATOM

• Archivos de texto

• Microsoft SQL Azure

• Oracle

• Teradata

• Sybase

• Informix

• IBM DB2

• Vinculación de objetos e incrustación de bases de datos / Conectividad de bases de datos abiertas

• (OLEDB / ODBC) fuentes

• Archivo de Microsoft Excel

• Archivo de texto

Carga de datos directamente en PowerPivot

Para cargar datos directamente en Power Pivot, realice lo siguiente:

• Abra un nuevo libro de trabajo.

• Haga clic en la pestaña POWERPIVOT en la cinta.

• Haga clic en Administrar en el grupo Modelo de datos.

Se abre la ventana de PowerPivot. Ahora tiene dos ventanas: la ventana del libro de Excel y la ventana de PowerPivot para Excel que está conectada a su libro de trabajo.

• Haga clic en el Home pestaña en la ventana de PowerPivot.

• Hacer clic From Database en el grupo Obtener datos externos.

• Seleccione From Access.

Aparece el Asistente de importación de tablas.

• Busque el archivo de la base de datos de Access.

• Proporcione un nombre de conexión amigable.

• Si la base de datos está protegida con contraseña, complete esos detalles también.

Haga clic en el Next→ botón. El Asistente de importación de tablas muestra las opciones para elegir cómo importar datos.

Haga clic en Seleccionar de una lista de tablas y vistas para elegir los datos que desee importar.

Haga clic en el Next→ botón. El Asistente para importación de tablas muestra las tablas y vistas en la base de datos de Access que ha seleccionado.

Marque la casilla Medallas.

Como puede observar, puede seleccionar las tablas marcando las casillas, obtener una vista previa y filtrar las tablas antes de agregarlas a la tabla dinámica y / o seleccionar las tablas relacionadas.

Haga clic en el Preview & Filter botón.

Como puede ver, puede seleccionar columnas específicas marcando las casillas en las etiquetas de las columnas, filtrar las columnas haciendo clic en la flecha desplegable en la etiqueta de la columna para seleccionar los valores que se incluirán.

• Haga clic en Aceptar.

• Haga clic en el Select Related Tables botón.

• Power Pivot comprueba qué otras tablas están relacionadas con la tabla de Medallas seleccionada, si existe una relación.

Puede ver que Power Pivot encontró que las disciplinas de la mesa están relacionadas con las medallas de la mesa y la seleccionó. Haga clic en Finalizar.

Aparece el asistente de importación de tablas: Importingy muestra el estado de la importación. Esto llevará unos minutos y puede detener la importación haciendo clic en elStop Import botón.

Una vez que se importan los datos, el Asistente de importación de tablas muestra: Successy muestra los resultados de la importación como se muestra en la captura de pantalla a continuación. Haga clic en Cerrar.

Power Pivot muestra las dos tablas importadas en dos pestañas.

Puede desplazarse por los registros (filas de la tabla) utilizando el Record flechas debajo de las pestañas.

Asistente de importación de tablas

En la sección anterior, aprendió cómo importar datos desde Access a través del Asistente de importación de tablas.

Tenga en cuenta que las opciones del Asistente de importación de tablas cambian según la fuente de datos seleccionada para conectarse. Es posible que desee saber de qué fuentes de datos puede elegir.

Hacer clic From Other Sources en la ventana de Power Pivot.

El asistente de importación de tablas - Connect to a Data Sourceaparece. Puede crear una conexión a una fuente de datos o puede utilizar una que ya exista.

Puede desplazarse por la lista de conexiones en el Asistente de importación de tablas para conocer las conexiones de datos compatibles con Power Pivot.

• Desplácese hacia abajo hasta los archivos de texto.

• Seleccione Excel File.

• Haga clic en el Next→ botón. Aparece el Asistente de importación de tablas: Conectarse a un archivo de Microsoft Excel.

• Busque el archivo de Excel en el cuadro Ruta del archivo de Excel.

• Revisa la caja - Use first row as column headers.

• Haga clic en el Next→ botón. Aparece el Asistente de importación de tablas:Select Tables and Views.

• Revisa la caja Product Catalog$. Haga clic en elFinish botón.

Verás lo siguiente Successmensaje. Haga clic en Cerrar.

Ha importado una tabla y también ha creado una conexión al archivo de Excel que contiene varias otras tablas.

Abrir conexiones existentes

Una vez que haya establecido una conexión a una fuente de datos, puede abrirla más tarde.

Haga clic en Conexiones existentes en la ventana de PowerPivot.

Aparece el cuadro de diálogo Conexiones existentes. Seleccione Datos de ventas de Excel de la lista.

Haga clic en el botón Abrir. Aparece el Asistente de importación de tablas mostrando las tablas y vistas.

Seleccione las tablas que desea importar y haga clic en Finish.

Se importarán las cinco tablas seleccionadas. Hacer clicClose.

Puede ver que las cinco tablas se agregan a Power Pivot, cada una en una nueva pestaña.

Crear tablas vinculadas

Las tablas vinculadas son un vínculo activo entre la tabla en Excel y la tabla en el modelo de datos. Las actualizaciones de la tabla en Excel actualizan automáticamente los datos de la tabla de datos en el modelo.

Puede vincular la tabla de Excel a Power Pivot en unos pocos pasos de la siguiente manera:

• Crea una tabla de Excel con los datos.

• Haga clic en la pestaña POWERPIVOT en la cinta.

• Hacer clic Add to Data Model en el grupo Tablas.

La tabla de Excel está vinculada a la tabla de datos correspondiente en PowerPivot.

Puede ver que las Herramientas de tabla con la pestaña - Tabla vinculada se agregan a la ventana de Power Pivot. Si hace clic enGo to Excel Table, cambiará a la hoja de cálculo de Excel. Si hace clic enManage, volverá a la tabla vinculada en la ventana de Power Pivot.

Puede actualizar la tabla vinculada de forma automática o manual.

Tenga en cuenta que puede vincular una tabla de Excel solo si está presente en el libro de trabajo con Power Pivot. Si tiene tablas de Excel en un libro de trabajo separado, debe cargarlas como se explica en la siguiente sección.

Carga desde archivos de Excel

Si desea cargar los datos de los libros de Excel, tenga en cuenta lo siguiente:

• Power Pivot considera el otro libro de Excel como una base de datos y solo se importan hojas de trabajo.

• Power Pivot carga cada hoja de trabajo como una tabla.

• Power Pivot no puede reconocer tablas individuales. Por lo tanto, Power Pivot no puede reconocer si hay varias tablas en una hoja de trabajo.

• Power Pivot no puede reconocer ninguna información adicional que no sea la tabla en una hoja de trabajo.

Por lo tanto, mantenga cada tabla en una hoja de trabajo separada.

Una vez que sus datos en el libro de trabajo estén listos, puede importarlos de la siguiente manera:

• Hacer clic From Other Sources en el grupo Obtener datos externos en la ventana de Power Pivot.

• Proceda como se indica en la sección - Asistente de importación de tablas.

Las siguientes son las diferencias entre las tablas de Excel vinculadas y las tablas de Excel importadas:

• Las tablas vinculadas deben estar en el mismo libro de Excel en el que se almacena la base de datos de Power Pivot. Si los datos ya existen en otros libros de Excel, no tiene sentido utilizar esta función.

• La función de importación de Excel le permite cargar datos de diferentes libros de Excel.

• La carga de datos desde un libro de Excel no crea un vínculo entre los dos archivos. Power Pivot crea solo una copia de los datos durante la importación.

• Cuando se actualiza el archivo de Excel original, los datos en Power Pivot no se actualizarán. Debe configurar el modo de actualización en automático o actualizar los datos manualmente, en la pestaña Tabla vinculada de la ventana de Power Pivot.

Carga desde archivos de texto

Uno de los estilos de representación de datos más populares es el formato conocido como valores separados por comas (csv). Cada fila / registro de datos está representado por una línea de texto, donde las columnas / campos están separados por comas. Muchas bases de datos ofrecen la opción de guardar en un archivo de formato csv.

Si desea cargar un archivo csv en Power Pivot, debe usar la opción Archivo de texto. Suponga que tiene el siguiente archivo de texto con formato csv:

• Haga clic en la pestaña PowerPivot.

• Haga clic en la pestaña Inicio en la ventana de PowerPivot.

• Hacer clic From Other Sourcesen el grupo Obtener datos externos. Aparece el Asistente de importación de tablas.

• Desplácese hacia abajo hasta Archivos de texto.

• Haga clic en Archivo de texto.

• Haga clic en el Next→ botón. Aparece el Asistente de importación de tablas con la pantalla Conectar a archivo plano.

• Busque el archivo de texto en el cuadro Ruta del archivo. Los archivos csv suelen tener la primera línea que representa los encabezados de columna.

• Marque la casilla Usar la primera fila como encabezados de columna, si la primera línea tiene encabezados.

• En el cuadro Separador de columnas, el valor predeterminado es Coma (,), pero en caso de que su archivo de texto tenga cualquier otro operador como Tabulador, Punto y coma, Espacio, Dos puntos o Barra vertical, elija ese operador.

Como puede observar, hay una vista previa de su tabla de datos. Haga clic en Finalizar.

Power Pivot crea la tabla de datos en el modelo de datos.

Cargando desde el portapapeles

Suponga que tiene datos en una aplicación que Power Pivot no reconoce como fuente de datos. Para cargar estos datos en Power Pivot, tiene dos opciones:

• Copie los datos en un archivo de Excel y use el archivo de Excel como fuente de datos para Power Pivot.

• Copie los datos para que estén en el portapapeles y péguelos en Power Pivot.

Ya aprendió la primera opción en una sección anterior. Y esta es preferible a la segunda opción, como encontrará al final de esta sección. Sin embargo, debe saber cómo copiar datos del portapapeles a Power Pivot.

Suponga que tiene datos en un documento de Word de la siguiente manera:

Word no es una fuente de datos para Power Pivot. Por lo tanto, realice lo siguiente:

• Seleccione la tabla en el documento de Word.

• Cópielo y péguelo en la ventana de PowerPivot.

los Paste Preview Aparece el cuadro de diálogo.

• Dar el nombre como Word-Employee table.

• Revisa la caja Use first row as column headers y haga clic en Aceptar.

Los datos copiados en el portapapeles se pegarán en una nueva tabla de datos en Power Pivot, con la pestaña - Tabla Word-Empleado.

Suponga que desea reemplazar esta tabla con contenido nuevo.

• Copie la tabla de Word.

• Haga clic en Pegar Reemplazar.

Aparece el cuadro de diálogo Pegar vista previa. Verifique el contenido que está usando para reemplazar.

Haga clic en Aceptar.

Como puede observar, el contenido de la tabla de datos en Power Pivot se reemplaza por el contenido del portapapeles.

Suponga que desea agregar dos nuevas filas de datos a una tabla de datos. En la tabla del documento de Word, tiene las dos filas de noticias.

• Seleccione las dos filas nuevas.

• Haga clic en Copiar.

• Hacer clic Paste Appenden la ventana de Power Pivot. Aparece el cuadro de diálogo Pegar vista previa.

• Verifique el contenido que está usando para agregar.

Haga clic en Aceptar para continuar.

Como puede observar, el contenido de la tabla de datos en Power Pivot se adjunta con el contenido en el portapapeles.

Al comienzo de esta sección, dijimos que copiar datos a un archivo de Excel y usar una tabla vinculada es mejor que copiar desde el portapapeles.

Esto se debe a las siguientes razones:

• Si usa una tabla vinculada, conoce la fuente de los datos. Por otro lado, no sabrá la fuente de los datos más adelante o si es utilizada por otra persona.

• Tiene información de seguimiento en el archivo de Word, como cuándo se reemplazan los datos y cuándo se agregan los datos. Sin embargo, no hay forma de copiar esa información en Power Pivot. Si copia los datos primero en un archivo de Excel, puede conservar esa información para su uso posterior.

• Mientras copia desde el portapapeles, si desea agregar algunos comentarios, no puede hacerlo. Si copia primero al archivo de Excel, puede insertar comentarios en su tabla de Excel que estarán vinculados a Power Pivot.

• No hay forma de actualizar los datos copiados del portapapeles. Si los datos son de una tabla vinculada, siempre puede asegurarse de que los datos estén actualizados.

Actualizar datos en Power Pivot

Puede actualizar los datos importados de las fuentes de datos externas en cualquier momento.

Si desea actualizar solo una tabla de datos en Power Pivot, haga lo siguiente:

• Haga clic en la pestaña de la tabla de datos.

• Haga clic en Actualizar.

• Seleccione Actualizar en la lista desplegable.

Si desea actualizar todas las tablas de datos en Power Pivot, haga lo siguiente:

• Haga clic en el botón Actualizar.

• Seleccione Actualizar todo en la lista desplegable.

Un modelo de datos es un nuevo enfoque introducido en Excel 2013 para integrar datos de varias tablas, creando de manera efectiva una fuente de datos relacionales dentro de un libro de Excel. Dentro de Excel, el modelo de datos se utiliza de forma transparente, proporcionando datos tabulares utilizados en tablas dinámicas y gráficos dinámicos. En Excel, puede acceder a las tablas y sus valores correspondientes a través de las listas de campos de tabla dinámica / gráfico dinámico que contienen los nombres de las tablas y los campos correspondientes.

El uso principal del modelo de datos en Excel es su uso por parte de Power Pivot. El modelo de datos se puede considerar como la base de datos de Power Pivot y todas las funciones de energía de Power Pivot se administran con el modelo de datos. Todas las operaciones de datos con Power Pivot son de naturaleza explícita y se pueden visualizar en el modelo de datos.

En este capítulo, comprenderá el modelo de datos en detalle.

Excel y modelo de datos

Solo habrá un modelo de datos en un libro de Excel. Cuando trabaja con Excel, el uso del modelo de datos está implícito. No puede acceder directamente al modelo de datos. Solo puede ver las múltiples tablas en el modelo de datos en la lista Campos de tabla dinámica o gráfico dinámico y usarlas. La creación del modelo de datos y la adición de datos también se realizan de forma implícita en Excel, mientras obtiene datos externos en Excel.

Si desea ver el modelo de datos, puede hacerlo de la siguiente manera:

• Haga clic en la pestaña POWERPIVOT en la cinta.

• Haga clic en Administrar.

El modelo de datos, si existe en el libro de trabajo, se mostrará como tablas, cada una con una pestaña.

Note- Si agrega una tabla de Excel al modelo de datos, no transformará la tabla de Excel en una tabla de datos. Se agrega una copia de la tabla de Excel como tabla de datos en el modelo de datos y se crea un vínculo entre los dos. Por lo tanto, si se realizan cambios en la tabla de Excel, la tabla de datos también se actualiza. Sin embargo, desde el punto de vista del almacenamiento, hay dos tablas.

Power Pivot y modelo de datos

El modelo de datos es inherentemente la base de datos de Power Pivot. Incluso cuando crea el modelo de datos desde Excel, solo crea la base de datos de Power Pivot. La creación del modelo de datos y / o la adición de datos se realiza de forma explícita en Power Pivot.

De hecho, puede administrar el modelo de datos desde la ventana de Power Pivot. Puede agregar datos al modelo de datos, importar datos de diferentes fuentes de datos, ver el modelo de datos, crear relaciones entre las tablas, crear campos calculados y columnas calculadas, etc.

Crear un modelo de datos

Puede agregar tablas al modelo de datos desde Excel o puede importar datos directamente a Power Pivot, creando así las tablas del modelo de datos de Power Pivot. Puede ver el modelo de datos haciendo clic en Administrar en la ventana de Power Pivot.

Comprenderá cómo agregar tablas desde Excel al modelo de datos en el capítulo - Carga de datos a través de Excel. Comprenderá cómo cargar datos en el modelo de datos en el capítulo: Carga de datos en Power Pivot.

Tablas en el modelo de datos

Las tablas en el modelo de datos se pueden definir como un conjunto de tablas que mantienen relaciones entre ellas. Las relaciones permiten combinar datos relacionados de diferentes tablas con fines de análisis e informes.

Las tablas del modelo de datos se denominan tablas de datos.

Una tabla en el modelo de datos se considera como un conjunto de registros (un registro es una fila) formado por campos (un campo es una columna). No puede editar elementos individuales en una tabla de datos. Sin embargo, puede agregar filas o agregar columnas calculadas a la tabla de datos.

Tablas de Excel y tablas de datos

Las tablas de Excel son solo una colección de tablas separadas. Puede haber varias tablas en una hoja de trabajo. Se puede acceder a cada tabla por separado, pero no es posible acceder a los datos de más de una tabla de Excel al mismo tiempo. Esta es la razón por la que cuando crea una tabla dinámica, se basa en una sola tabla. Si necesita utilizar los datos de dos tablas de Excel en conjunto, primero debe combinarlos en una sola tabla de Excel.

Por otro lado, una tabla de datos coexiste con otras tablas de datos con relaciones, lo que facilita la combinación de datos de varias tablas. Las tablas de datos se crean al importar datos a Power Pivot. También puede agregar tablas de Excel al modelo de datos mientras crea una tabla dinámica obteniendo datos externos o de varias tablas.

Las tablas de datos en el modelo de datos se pueden ver de dos formas:

• Vista de datos.

• Vista de diagrama.

Vista de datos del modelo de datos

En la vista de datos del modelo de datos, cada tabla de datos existe en una pestaña separada. Las filas de la tabla de datos son los registros y las columnas representan los campos. Las pestañas contienen los nombres de las tablas y los encabezados de las columnas son los campos de esa tabla. Puede realizar cálculos en la vista de datos utilizando el lenguaje de Expresiones de análisis de datos (DAX).

Vista de diagrama del modelo de datos

En la vista de diagrama del modelo de datos, todas las tablas de datos están representadas por cuadros con los nombres de las tablas y contienen los campos de la tabla. Puede organizar las tablas en la vista de diagrama con solo arrastrarlas. Puede ajustar el tamaño de una tabla de datos para que se muestren todos los campos de la tabla.

Relaciones en el modelo de datos

Puede ver las relaciones en la vista de diagrama. Si dos tablas tienen una relación definida entre ellas, aparece una flecha que conecta la tabla de origen con la tabla de destino. Si desea saber qué campos se utilizan en la relación, simplemente haga doble clic en la flecha. La flecha y los dos campos de las dos tablas están resaltados.

Las relaciones de tabla se crearán automáticamente si importa tablas relacionadas que tengan relaciones de clave primaria y externa. Excel puede utilizar la información de relación importada como base para las relaciones de tabla en el modelo de datos.

También puede crear relaciones explícitamente en cualquiera de las dos vistas:

• Data View - Usando el cuadro de diálogo Crear relación.

• Diagram View - Haciendo clic y arrastrando para conectar las dos tablas.

Create Relationship Dialog Box

En una relación, están involucradas cuatro entidades:

• Table - La tabla de datos desde la que comienza la relación.

• Column - El campo de la tabla que también está presente en la tabla relacionada.

• Related Table - La tabla de datos donde termina la relación.

• Related Column- El campo de la tabla relacionada que es el mismo que el campo representado por Columna en la tabla. Tenga en cuenta que los valores de Columna relacionada deben ser únicos.

En la vista de diagrama, puede crear la relación haciendo clic en el campo de la tabla y arrastrándolo a la tabla relacionada.

Aprenderá más sobre las relaciones en el capítulo Gestión de tablas de datos y relaciones con Power Pivot.

El uso principal de Power Pivot es su capacidad para administrar las tablas de datos y las relaciones entre ellas, para facilitar el análisis de los datos de varias tablas. Puede agregar una tabla de Excel al modelo de datos mientras crea una tabla dinámica o directamente desde la cinta de PowerPivot.

Puede analizar datos de varias tablas solo cuando existen relaciones entre ellas. Con Power Pivot, puede crear relaciones desde la Vista de datos o la Vista de diagrama. Además, si eligió agregar una tabla al Power Pivot, también debe agregar una relación.

Agregar tablas de Excel al modelo de datos con tabla dinámica

Cuando crea una tabla dinámica en Excel, se basa solo en una única tabla / rango. En caso de que desee agregar más tablas a la tabla dinámica, puede hacerlo con el modelo de datos.

Suponga que tiene dos hojas de trabajo en su libro de trabajo:

• Uno que contiene los datos de los vendedores y las regiones que representan, en una tabla- Vendedor.

• Otro que contiene los datos de ventas, región y mes, en una tabla - Ventas.

Puede resumir las ventas, en términos de vendedor, como se indica a continuación.

• Haga clic en la tabla - Ventas.

• Haga clic en la pestaña INSERTAR en la cinta.

• Seleccione tabla dinámica en el grupo tablas.

Se creará una tabla dinámica vacía con los campos de la tabla de ventas: región, mes y monto del pedido. Como puede observar, hay unaMORE TABLES comando debajo de la lista Campos de tabla dinámica.

• Haga clic en MÁS TABLAS.

los Create a New PivotTableaparece el cuadro de mensaje. El mensaje que se muestra es: para usar varias tablas en su análisis, se debe crear una nueva tabla dinámica utilizando el modelo de datos. Haga clic en Sí

Se creará una nueva tabla dinámica como se muestra a continuación:

En Campos de tabla dinámica, puede observar que hay dos pestañas: ACTIVE y ALL.

• Haga clic en la pestaña TODOS.

• En la lista Campos de tabla dinámica aparecen dos tablas: Ventas y Vendedor, con los campos correspondientes.

• Haga clic en el campo Vendedor en la tabla Vendedor y arrástrelo al área FILAS.

• Haga clic en el campo Mes en la tabla Ventas y arrástrelo al área FILAS.

• Haga clic en el campo Monto del pedido en la tabla Ventas y arrástrelo al área ∑ VALORES.

Se crea la tabla dinámica. Aparece un mensaje en los campos de la tabla dinámica:Relationships between tables may be needed.

Haga clic en el botón CREAR junto al mensaje. losCreate Relationship Aparece el cuadro de diálogo.

• Debajo Table, seleccione Ventas.

• Debajo Column (Foreign) cuadro, seleccione Región.

• Debajo Related Table, seleccione Vendedor.

• Debajo Related Column (Primary) cuadro, seleccione Región.

• Haga clic en Aceptar.

Su tabla dinámica de las dos tablas en dos hojas de trabajo está lista.

Además, como dijo Excel al agregar la segunda tabla a la tabla dinámica, la tabla dinámica se creó con el modelo de datos. Para verificar, haga lo siguiente:

• Haga clic en la pestaña POWERPIVOT en la cinta.

• Hacer clic Manageen el grupo Modelo de datos. Aparece la Vista de datos de Power Pivot.

Puede observar que las dos tablas de Excel que utilizó para crear la tabla dinámica se convierten en tablas de datos en el modelo de datos.

Agregar tablas de Excel de un libro de trabajo diferente al modelo de datos

Suponga que las dos tablas: Vendedor y Ventas están en dos libros de trabajo diferentes.

Puede agregar la tabla de Excel de un libro de trabajo diferente al modelo de datos de la siguiente manera:

• Haga clic en la tabla de ventas.

• Haga clic en la pestaña INSERTAR.

• Haga clic en Tabla dinámica en el grupo Tablas. losCreate PivotTable Aparece el cuadro de diálogo.

• En el cuadro Tabla / Rango, escriba Ventas.

• Haga clic en Nueva hoja de trabajo.

• Marque la casilla Agregar estos datos al modelo de datos.

• Haga clic en Aceptar.

Obtendrá una tabla dinámica vacía en una nueva hoja de trabajo con solo los campos correspondientes a la tabla de ventas.

Ha agregado los datos de la tabla de ventas al modelo de datos. A continuación, debe obtener los datos de la tabla de vendedor también en el modelo de datos de la siguiente manera:

• Haga clic en la hoja de trabajo que contiene la tabla de ventas.

• Haga clic en la pestaña DATOS en la cinta.

• Haga clic en Conexiones existentes en el grupo Obtener datos externos. Aparece el cuadro de diálogo Conexiones existentes.

• Haga clic en la pestaña Tablas.

Debajo This Workbook Data Model, 1 tablese muestra (esta es la tabla de Ventas que agregó anteriormente). También encontrará los dos libros de trabajo que muestran las tablas en ellos.

• Haga clic en Vendedor en Salesperson.xlsx.

• Haga clic en Abrir. losImport Data Aparece el cuadro de diálogo.

• Haga clic en Informe de tabla dinámica.

• Haga clic en Nueva hoja de trabajo.

Puedes ver que la caja ... Add this data to the Data Modelestá marcado e inactivo. Haga clic en Aceptar.

Se creará la tabla dinámica.

Como puede observar, las dos tablas están en el modelo de datos. Es posible que deba crear una relación entre las dos tablas como en la sección anterior.

Agregar tablas de Excel al modelo de datos desde la cinta de PowerPivot

Otra forma de agregar tablas de Excel al modelo de datos es hacer so from the PowerPivot Ribbon.

Suponga que tiene dos hojas de trabajo en su libro de trabajo:

• Uno que contiene los datos de los vendedores y las regiones que representan, en una tabla - Vendedor.

• Otro que contiene los datos de ventas, región y mes, en una tabla - Ventas.

Puede agregar estas tablas de Excel al modelo de datos primero, antes de realizar cualquier análisis.

• Haga clic en la tabla de Excel - Ventas.

• Haga clic en la pestaña POWERPIVOT en la cinta.

• Haga clic en Agregar al modelo de datos en el grupo Tablas.

Aparece la ventana de Power Pivot, con la tabla de datos Salesperson agregada. Además, aparece una pestaña: Tabla vinculada en la cinta de opciones de la ventana de Power Pivot.

• Haga clic en la pestaña Tabla vinculada en la cinta.

• Haga clic en Tabla de Excel: Vendedor.

Puede encontrar que se muestran los nombres de las dos tablas presentes en su libro de trabajo y que el nombre Vendedor está marcado. Esto significa que la tabla de datos Vendedor está vinculada a la tabla de Excel Vendedor.

Hacer clic Go to Excel Table.

Aparece la ventana de Excel con la hoja de trabajo que contiene la tabla de vendedores.

• Haga clic en la pestaña de la hoja de trabajo Ventas.

• Haga clic en la tabla de ventas.

• Haga clic en Agregar al modelo de datos en el grupo Tablas de la cinta.

La tabla de Excel Sales también se agrega al modelo de datos.

Si desea realizar un análisis basado en estas dos tablas, como sabe, debe crear una relación entre las dos tablas de datos. En Power Pivot, puede hacer esto de dos maneras:

• Desde la vista de datos

• Desde la vista de diagrama

Crear relaciones desde la vista de datos

Como sabe, en la Vista de datos, puede ver las tablas de datos con registros como filas y campos como columnas.

• Haga clic en la pestaña Diseño en la ventana de Power Pivot.

• Haga clic en Crear relación en el grupo Relaciones. losCreate Relationship Aparece el cuadro de diálogo.

• Haga clic en Ventas en el cuadro Tabla. Esta es la tabla desde donde comienza la relación. Como sabe, Columna debe ser el campo que está presente en la tabla relacionada Vendedor que contiene valores únicos.

• Haga clic en Región en el cuadro Columna.

• Haga clic en Vendedor en el cuadro Tabla vinculada relacionada.

La columna vinculada relacionada se completa automáticamente con Región.

Haga clic en el botón Crear. Se crea la relación.

Crear relaciones desde la vista de diagrama

Crear relaciones desde la vista de diagrama es relativamente más fácil. Siga los pasos dados.

• Haga clic en la pestaña Inicio en la ventana de Power Pivot.

• Haga clic en Vista de diagrama en el grupo Vista.

La Vista de diagrama del modelo de datos aparece en la ventana de Power Pivot.

• Haga clic en Región en la tabla Ventas. La región en la tabla de Ventas está resaltada.

• Arrastre a Región en la tabla Vendedor. También se resalta la región en la tabla Vendedor. Aparece una línea en la dirección que arrastró.

• Aparece una línea desde la tabla Ventas a la tabla Vendedor indicando la relación.

Como puede ver, aparece una línea desde la tabla Ventas a la tabla Vendedor, indicando la relación y la dirección.

Si desea conocer el campo que forma parte de una relación, haga clic en la línea de relación. La línea y el campo en ambas tablas están resaltados.

Gestión de relaciones

Puede editar o eliminar una relación existente en el modelo de datos.

• Haga clic en la pestaña Diseño en la ventana de Power Pivot.

• Haga clic en Administrar relaciones en el grupo Relaciones. Aparece el cuadro de diálogo Administrar relaciones.

Se muestran todas las relaciones que existen en el modelo de datos.

Para editar una relación

• Haga clic en una relación.

• Haga clic en el Editbotón. losEdit Relationship Aparece el cuadro de diálogo.

• Realice los cambios necesarios en la relación.

• Haga clic en Aceptar. Los cambios se reflejan en la relación.

Para eliminar una relación

• Haga clic en una relación.

• Haga clic en el botón Eliminar. Aparece un mensaje de advertencia que muestra cómo las tablas afectadas por la eliminación de la relación afectarían a los informes.

• Haga clic en Aceptar si está seguro de que desea eliminar. Se elimina la relación seleccionada.

Actualización de datos de Power Pivot

Suponga que modifica los datos en la tabla de Excel. Puede agregar / cambiar / eliminar los datos en la tabla de Excel.

Para actualizar los datos de PowerPivot, haga lo siguiente:

• Haga clic en la pestaña Tabla vinculada en la ventana de Power Pivot.

• Haga clic en Actualizar todo.

La tabla de datos se actualiza con las modificaciones realizadas en la tabla de Excel.

Como puede observar, no puede modificar datos en las tablas de datos directamente. Por lo tanto, es mejor mantener sus datos en tablas de Excel que están vinculadas a las tablas de datos cuando las agrega al modelo de datos. Esto facilita la actualización de los datos en tablas de datos a medida que actualiza los datos en tablas de Excel.

Power PivotTable se basa en la base de datos de Power Pivot, que se denomina modelo de datos. Ya ha aprendido las potentes funciones del modelo de datos. El poder de Power Pivot está en su capacidad para resumir datos del modelo de datos en Power PivotTable. Como ya sabe, el modelo de datos puede manejar grandes datos que abarcan millones de filas y provienen de diversas entradas. Esto permite que Power PivotTable resuma los datos desde cualquier lugar en cuestión de minutos.

Power PivotTable se parece a PivotTable en su diseño, con las siguientes diferencias:

• PivotTable se basa en tablas de Excel, mientras que Power PivotTable se basa en tablas de datos que forman parte del modelo de datos.

• PivotTable se basa en una única tabla o rango de datos de Excel, mientras que Power PivotTable se puede basar en varias tablas de datos, siempre que se agreguen al modelo de datos.

• La tabla dinámica se crea desde la ventana de Excel, mientras que Power PivotTable se crea desde la ventana de PowerPivot.

Crear una tabla dinámica de energía

Suponga que tiene dos tablas de datos: vendedor y ventas en el modelo de datos. Para crear una tabla PowerPivot a partir de estas dos tablas de datos, proceda de la siguiente manera:

• Haga clic en la pestaña Inicio en la cinta de opciones en la ventana de PowerPivot.

• Haga clic en Tabla dinámica en la cinta.

• Seleccione Tabla dinámica en la lista desplegable.

Aparece el cuadro de diálogo Crear tabla dinámica. Como puede observar, se trata de un cuadro de diálogo sencillo, sin consultas sobre los datos. Esto se debe a que Power PivotTable siempre se basa en el modelo de datos, es decir, las tablas de datos con las relaciones definidas entre ellas.

Seleccione Nueva hoja de trabajo y haga clic en Aceptar.

Se crea una nueva hoja de trabajo en la ventana de Excel y aparece una tabla dinámica vacía.

Como puede observar, el diseño de Power PivotTable es similar al de PivotTable. losPIVOTTABLE TOOLS aparecen en la cinta, con ANALYZE y DESIGN pestañas, idénticas a las de tabla dinámica.

La lista de campos de la tabla dinámica aparece en el lado derecho de la hoja de trabajo. Aquí encontrará algunas diferencias con respecto a la tabla dinámica.

Campos de Power PivotTable

La lista de campos de la tabla dinámica tiene dos pestañas: ACTIVO y TODOS que aparecen debajo del título y encima de la lista de campos. losALL La pestaña está resaltada.

Tenga en cuenta que el ALLLa pestaña muestra todas las tablas de datos en el modelo de datos y la pestaña ACTIVE muestra todas las tablas de datos que se eligen para la Power PivotTable en cuestión. Como Power PivotTable está vacío, significa que aún no se ha seleccionado ninguna tabla de datos; por lo tanto, de forma predeterminada, se selecciona la pestaña TODAS y se muestran las dos tablas que están actualmente en el Modelo de datos. En este punto, si hace clic en elACTIVE pestaña, la lista Campos estaría vacía.

• Haga clic en los nombres de las tablas en la lista Campos de tabla dinámica debajo de TODOS. Aparecerán los campos correspondientes con casillas de verificación.

• Cada nombre de mesa tendrá el símbolo en el lado izquierdo.

• Si coloca el cursor sobre este símbolo, se mostrarán la fuente de datos y el nombre de la tabla del modelo de esa tabla de datos.

• Arrastre Vendedor de la tabla Vendedor al área FILAS.

• Haga clic en el ACTIVE lengüeta.

Como puede observar, el campo Vendedor aparece en la tabla dinámica y la tabla Vendedor aparece debajo del ACTIVE pestaña como se esperaba.

• Haga clic en el ALL lengüeta.

• Haga clic en Mes y Monto del pedido en la tabla Ventas.

Nuevamente, haga clic en la pestaña ACTIVO. Ambas tablas, Ventas y Vendedor, aparecen debajo deACTIVE lengüeta.

• Arrastre Mes al área COLUMNAS.

• Arrastre Región al área FILTROS.

• Haga clic en la flecha junto a TODOS en el cuadro de filtro Región.

• Haga clic en Seleccionar varios elementos.

• Seleccione Norte y Sur y haga clic en Aceptar.

Ordene las etiquetas de las columnas en orden ascendente.

Power PivotTable se puede modificar de forma dinámica, explorar y reportar datos.

DAX (Data Analysis eXpression)language es el lenguaje de Power Pivot. Power Pivot usa DAX para el modelado de datos y es conveniente que lo use para BI de autoservicio. DAX se basa en tablas de datos y columnas en tablas de datos. Tenga en cuenta que no se basa en celdas individuales en la tabla como es el caso de las fórmulas y funciones en Excel.

Aprenderá los dos cálculos simples que existen en el modelo de datos: columna calculada y campo calculado en este capítulo.

Columna calculada

La columna calculada es una columna del modelo de datos que se define mediante un cálculo y que amplía el contenido de una tabla de datos. Se puede visualizar como una nueva columna en una tabla de Excel definida por una fórmula.

Ampliación del modelo de datos mediante columnas calculadas

Suponga que tiene datos de ventas de productos por región en tablas de datos y también un catálogo de productos en el modelo de datos.

Cree una Power PivotTable con estos datos.

Como puede observar, Power PivotTable ha resumido los datos de ventas de todas las regiones. Suponga que desea conocer la ganancia bruta obtenida con cada uno de los productos. Conoces el precio de cada producto, el costo al que se vende y la cantidad de unidades vendidas.

Sin embargo, si necesita calcular la ganancia bruta, debe tener dos columnas más en cada una de las tablas de datos de las regiones: precio total del producto y ganancia bruta. Esto se debe a que la tabla dinámica requiere columnas en las tablas de datos para resumir los resultados.

Como sabe, el precio total del producto es el precio del producto * número de unidades y la ganancia bruta es la cantidad total - precio total del producto.

Debe usar expresiones DAX para agregar las columnas calculadas de la siguiente manera:

• Haga clic en la pestaña East_Sales en la Vista de datos de la ventana Power Pivot para ver la tabla de datos East_Sales.

• Haga clic en la pestaña Diseño en la cinta.

• Haga clic en Agregar.

La columna del lado derecho con el encabezado - Agregar columna está resaltada.

Tipo = [Product Price] * [No. of Units] en la barra de fórmulas y presione Enter.

Una nueva columna con encabezado CalculatedColumn1 se inserta con los valores calculados por la fórmula que ingresó.

• Haga doble clic en el encabezado de la nueva columna calculada.

• Cambiar el nombre del encabezado como TotalProductPrice.

Agregue una columna calculada más para la ganancia bruta de la siguiente manera:

• Haga clic en la pestaña Diseño en la cinta.

• Haga clic en Agregar.

• La columna del lado derecho con el encabezado - Agregar columna está resaltada.

• Tipo = [TotalSalesAmount] − [TotaProductPrice] en la barra de fórmulas.

• Presione Entrar.

Una nueva columna con encabezado CalculatedColumn1 se inserta con los valores calculados por la fórmula que ingresó.

• Haga doble clic en el encabezado de la nueva columna calculada.

• Cambie el nombre del encabezado como Beneficio bruto.

Agregue las columnas calculadas en el North_Salestabla de datos de forma similar. Consolidando todos los pasos, proceda de la siguiente manera:

• Haga clic en la pestaña Diseño en la cinta.

• Haga clic en Agregar. La columna del lado derecho con el encabezado - Agregar columna está resaltada.

• Tipo = [Product Price] * [No. of Units] en la barra de fórmulas y presione Entrar.

• Se inserta una nueva columna con el encabezado CalculatedColumn1 con los valores calculados por la fórmula que ingresó.

• Haga doble clic en el encabezado de la nueva columna calculada.

• Cambiar el nombre del encabezado como TotalProductPrice.

• Haga clic en la pestaña Diseño en la cinta.

• Haga clic en Agregar. La columna del lado derecho con el encabezado - Agregar columna está resaltada.

• Tipo = [TotalSalesAmount] − [TotaProductPrice]en la barra de fórmulas y presione Entrar. Una nueva columna con encabezadoCalculatedColumn1 se inserta con los valores calculados por la fórmula que ingresó.

• Haga doble clic en el encabezado de la nueva columna calculada.

• Cambiar el nombre del encabezado como Gross Profit.

Repita los pasos dados anteriormente para la tabla de datos South Sales y la tabla de datos West Sales.

Tiene las columnas necesarias para resumir la Utilidad Bruta. Ahora, cree la Power PivotTable.

Puede resumir el Gross Profit eso se hizo posible con las columnas calculadas en Power Pivot y todo se puede hacer en unos pocos pasos sin errores.

También puede resumirlo por región para los productos como se indica a continuación:

Campo calculado

Suponga que desea calcular el porcentaje de beneficio obtenido por cada región por producto. Puede hacerlo agregando un campo calculado a la Tabla de datos.

• Haga clic debajo de la columna Beneficio bruto en el East_Sales tabla en la ventana de Power Pivot.

• Tipo EastProfit: = SUM ([Gross Profit]) / sum ([TotalSalesAmount]) en la barra de fórmulas.

• Presione Entrar.

El campo calculado EastProfit se inserta debajo de la columna de Beneficio Bruto.

• Haga clic derecho en el campo calculado - EastProfit.

• Seleccione Format de la lista desplegable.

Aparece el cuadro de diálogo Formateo.

• Seleccione Number en Categoría.

• En el cuadro Formato, seleccione Porcentaje y haga clic en Aceptar.

El campo calculado EastProfit tiene el formato de porcentaje.

Repita los pasos para insertar los siguientes campos calculados:

• NorthProfit en la tabla de datos North_Sales.

• SouthProfit en la tabla de datos South_Sales.

• WestProfit en la tabla de datos West_Sales.

Note - No puede definir más de un campo calculado con un nombre dado.

Haga clic en Power PivotTable. Puede ver que los campos calculados aparecen en las tablas.

• Seleccione los campos: EastProfit, NorthProfit, SouthProfit y WestProfit de las tablas de la lista Campos de tabla dinámica.

• Organice los campos de manera que el beneficio bruto y el beneficio porcentual aparezcan juntos. La Power PivotTable tiene el siguiente aspecto:

Note - el Calculate Fields fueron llamados Measures en versiones anteriores de Excel.

En el capítulo anterior, aprendió cómo crear una Power PivotTable a partir de un conjunto normal de tablas de datos. En este capítulo, aprenderá cómo explorar datos con Power PivotTable, cuando las tablas de datos contienen miles de filas.

Para una mejor comprensión, importaremos los datos de una base de datos de acceso, que sabe que es una base de datos relacional.

Carga de datos de la base de datos de Access

Para cargar datos de la base de datos de Access, siga los pasos dados:

• Abra un nuevo libro en blanco en Excel.

• Haga clic en Administrar en el grupo Modelo de datos.

• Haga clic en la pestaña POWERPIVOT en la cinta.

Aparece la ventana de Power Pivot.

• Haga clic en la pestaña Inicio en la ventana de Power Pivot.

• Hacer clic From Database en el grupo Obtener datos externos.

• Seleccione From Access de la lista desplegable.

Aparece el Asistente de importación de tablas.

• Proporcionar Friendly connection nombre.

• Busque el archivo de base de datos de Access, Events.accdb, el archivo de base de datos de eventos.

• Haga clic en el botón Siguiente>.

los Table Import El asistente muestra opciones para elegir cómo importar datos.

Hacer clic Select from a list of tables and views to choose the data to import y haga clic en Next.

los Table ImportEl asistente muestra todas las tablas de la base de datos de Access que ha seleccionado. Marque todas las casillas para seleccionar todas las tablas y haga clic en Finalizar.

los Table Import El asistente muestra - Importingy muestra el estado de la importación. Esto puede tardar unos minutos y puede detener la importación haciendo clic en elStop Import botón.

Una vez que se completa la importación de datos, aparece el Asistente de importación de tablas: Successy muestra los resultados de la importación. Hacer clicClose.

Power Pivot muestra todas las tablas importadas en diferentes pestañas en la Vista de datos.

Haga clic en la Vista de diagrama.

Puede observar que existe una relación entre las tablas: Disciplines and Medals. Esto se debe a que, cuando importa datos de una base de datos relacional como Access, las relaciones que existen en la base de datos también se importan al modelo de datos en Power Pivot.

Crear una tabla dinámica a partir del modelo de datos

Cree una tabla dinámica con las tablas que ha importado en la sección anterior de la siguiente manera:

• Haga clic en Tabla dinámica en la cinta.

• Seleccione Tabla dinámica de la lista desplegable.

• Seleccione Nueva hoja de trabajo en el cuadro de diálogo Crear tabla dinámica que aparece y haga clic en Aceptar.

Se crea una tabla dinámica vacía en una nueva hoja de trabajo en la ventana de Excel.

Todas las tablas importadas que forman parte del modelo de datos de Power Pivot aparecen en la lista Campos de tabla dinámica.

• Arrastrar el NOC_CountryRegion campo en la tabla Medallas al área COLUMNAS.

• Arrastre Disciplina de la tabla Disciplinas al área FILAS.

• Filtre la disciplina para mostrar solo cinco deportes: tiro con arco, saltos, esgrima, patinaje artístico y patinaje de velocidad. Esto se puede hacer en el área Campos de tabla dinámica o desde el filtro Etiquetas de fila en la propia tabla dinámica.

• Arrastre Medalla de la tabla Medallas al área VALORES.

• Seleccione Medalla de la tabla Medallas nuevamente y arrástrela al área FILTROS.

La tabla dinámica se completa con los campos agregados y en el diseño elegido de las áreas.

Explorando datos con tabla dinámica

Es posible que desee mostrar solo aquellos valores con Cuenta de medallas> 80. Para realizar esto, siga los pasos dados:

• Haga clic en la flecha a la derecha de Etiquetas de columna.

• Seleccione Value Filters de la lista desplegable.

• Seleccione Greater Than…. de la segunda lista desplegable.

• Haga clic en Aceptar.

los Value FilterAparece el cuadro de diálogo. Escriba 80 en el cuadro de la derecha y haga clic en Aceptar.

La tabla dinámica muestra solo aquellas regiones con un número total de medallas superior a 80.

Puede llegar al informe específico que desea de las diferentes tablas en solo unos pocos pasos. Esto fue posible gracias a las relaciones preexistentes entre las tablas de la base de datos de Access. Al importar todas las tablas de la base de datos juntas al mismo tiempo, Power Pivot recreó las relaciones en su modelo de datos.

Resumen de datos de diferentes fuentes en Power Pivot

Si obtiene las tablas de datos de diferentes fuentes o si no importa las tablas de una base de datos al mismo tiempo, o si crea nuevas tablas de Excel en su libro de trabajo y las agrega al modelo de datos, debe crear las relaciones entre las tablas que desea utilizar para su análisis y resumen en la tabla dinámica.

• Cree una nueva hoja de trabajo en el libro de trabajo.

• Cree una tabla de Excel - Deportes.

Agregue la tabla de deportes al modelo de datos.

Crea una relación entre las tablas Disciplines and Sports con el campo SportID.

Agregar el campo Sport a la tabla dinámica.

Baraja los campos - Discipline and Sport en el área FILAS.

Ampliación de la exploración de datos

Puedes conseguir la mesa Events también en una mayor exploración de datos.

Crea una relación entre las tablas Events y Medals con el campo DisciplineEvent.

Agregar una mesa Hosts al libro de trabajo y al modelo de datos.

Ampliación del modelo de datos mediante columnas calculadas

Para conectar la tabla Hosts a cualquiera de las otras tablas, debe tener un campo con valores que identifiquen de forma única cada fila en la tabla Hosts. Como no existe tal campo en la tabla de hosts, puede crear una columna calculada en la tabla de hosts para que contenga valores únicos.

• Vaya a la tabla Hosts en la Vista de datos de la ventana de PowerPivot.

• Haga clic en la pestaña Diseño en la cinta.

• Haga clic en Agregar.

La columna más a la derecha con el encabezado Agregar columna está resaltada.

• Escriba la siguiente fórmula DAX en la barra de fórmulas = CONCATENATE ([Edition], [Season])

• Presione Entrar.

Se crea una nueva columna con el encabezado CalculatedColumn1 y la columna se llena con los valores resultantes de la fórmula DAX anterior.

Haga clic con el botón derecho en la nueva columna y seleccione Cambiar nombre de columna en la lista desplegable.

Tipo EditionID en el encabezado de la nueva columna.

Como puede ver, la columna EditionID tiene valores únicos en la tabla Hosts.

Crear una relación con columnas calculadas

Si tiene que crear una relación entre el Hosts mesa y el Medals tabla, la columna EditionIDtambién debería existir en la tabla de Medallas. Cree una columna calculada en la tabla Medallas de la siguiente manera:

• Haga clic en la tabla Medallas en la Vista de datos de Power Pivot.

• Haga clic en la pestaña Diseño en la cinta.

• Haga clic en Agregar.

Escriba la fórmula DAX en la barra de fórmulas = YEAR ([EDITION]) y presione Entrar.

Cambie el nombre de la nueva columna que se crea como Año y haga clic en Add.

• Escriba la siguiente fórmula DAX en la barra de fórmulas = CONCATENATE ([Year], [Season])

• Cambie el nombre de la nueva columna que se crea como EditionID.

Como puede observar, la columna EditionID en la tabla Medallas tiene valores idénticos a la columna EditionID en la tabla Hosts. Por lo tanto, puede crear una relación entre las tablas - Medallas y Deportes con el campo EditionID.

• Cambie a la vista de diagrama en la ventana de PowerPivot.

• Cree una relación entre las tablas - Medallas y Anfitriones con el campo que se obtiene de la columna calculada es decir EditionID.

Ahora puede agregar campos de la tabla Hosts a Power PivotTable.

Cuando los datos tienen muchos niveles, a veces resulta complicado leer el informe de tabla dinámica.

Por ejemplo, considere el siguiente modelo de datos.

Crearemos una Power PivotTable y una Power PivotTable aplanada para comprender los diseños.

Crear una tabla dinámica

Puede crear una Power PivotTable de la siguiente manera:

• Haga clic en la pestaña Inicio en la cinta de opciones de la ventana de PowerPivot.

• Haga clic en tabla dinámica.

• Seleccione Tabla dinámica en la lista desplegable.

Se creará una tabla dinámica vacía.

• Arrastre los campos: Vendedor, Región y Producto de la lista Campos de tabla dinámica al área FILAS.

• Arrastra el campo - TotalSalesAmount desde las Tablas - Este, Norte, Sur y Oeste hasta el área de ∑ VALORES.

Como puede ver, es un poco engorroso leer un informe de este tipo. Si el número de entradas aumenta, más difícil será.

Power Pivot proporciona una solución para una mejor representación de los datos con la tabla dinámica plana.

Crear una tabla dinámica plana

Puede crear una tabla dinámica Power Flat de la siguiente manera:

• Haga clic en la pestaña Inicio en la cinta de opciones de la ventana de PowerPivot.

• Haga clic en tabla dinámica.

• Seleccione Flattened PivotTable de la lista desplegable.

Create Flattened PivotTableAparece el cuadro de diálogo. Seleccione Nueva hoja de trabajo y haga clic en Aceptar.

Como puede observar, los datos se aplanan en esta tabla dinámica.

Note- En este caso Vendedor, Región y Producto están en el área FILAS solo como en el caso anterior. Sin embargo, en el diseño de tabla dinámica, estos tres campos aparecen como tres columnas.

Explorar datos en una tabla dinámica plana

Suponga que desea resumir los datos de ventas del producto: Aire acondicionado. Puede hacerlo de una manera simple con la tabla dinámica plana de la siguiente manera:

• Haga clic en la flecha junto al encabezado de la columna: Producto.

• Marque la casilla Aire acondicionado y desmarque las otras casillas. Haga clic en Aceptar.

La tabla dinámica plana se filtra a los datos de ventas del aire acondicionado.

Puedes hacer que parezca más aplanado arrastrando ∑ VALUES al área FILAS desde el área COLUMNAS.

Cambie el nombre de los nombres personalizados de los valores de suma en el ∑ VALUES área para hacerlos más significativos de la siguiente manera:

• Haga clic en un valor de suma, por ejemplo, Sum of TotalSalesAmount para East.

• Seleccione Configuración de campo de valor en la lista desplegable.

• Cambie el nombre personalizado a East TotalSalesAmount.

• Repita los pasos para los otros tres valores de suma.

También puede resumir el número de unidades vendidas.

• Arrastre No. of Units al área ∑ VALUES de cada una de las tablas: East_Sales, North_Sales, South_Sales y West_Sales.

• Cambie el nombre de los valores a Núm. Total de unidades Este, Núm. Total de unidades Norte, Núm. Total de unidades Sur y Núm. Total de unidades Oeste respectivamente.

Como puede observar, en las dos tablas anteriores, hay filas con valores vacíos, ya que cada vendedor representa una sola región y cada región está representada solo por un único vendedor.

• Seleccione las filas con valores vacíos.

• Haga clic derecho y haga clic en Ocultar en la lista desplegable.

Se ocultarán todas las filas con valores vacíos.

Como puede observar, aunque las filas con valores vacíos no se muestran, la información sobre el vendedor también se ocultó.

• Haga clic en el encabezado de la columna - Vendedor.

• Haga clic en la pestaña ANALIZAR en la cinta.

• Haga clic en Configuración de campo. Aparece el cuadro de diálogo Configuración de campo.

• Haga clic en la pestaña Diseño e impresión.

• Revisa la caja - Repeat Item Labels.

• Haga clic en Aceptar.

Como puede observar, se muestra la información del vendedor y se ocultan las filas con valores vacíos. Además, la columna Región del informe es redundante, ya que los valores de la columna Valores se explican por sí mismos.

Arrastre el campo Regiones fuera del área.

Invierta el orden de los campos: Vendedor y Producto en el área FILAS.

Ha llegado a un informe conciso que combina datos de seis tablas en Power Pivot.

Un gráfico dinámico basado en el modelo de datos y creado a partir de la ventana de Power Pivot es un gráfico dinámico de energía. Aunque tiene algunas características similares a Excel PivotChart, hay otras características que lo hacen más poderoso.

En este capítulo, aprenderá acerca de Power PivotCharts. De ahora en adelante nos referiremos a ellos como PivotCharts, por simplicidad.

Crear un gráfico dinámico

Suponga que desea crear un gráfico dinámico basado en el siguiente modelo de datos.

• Haga clic en la pestaña Inicio en la cinta de opciones en la ventana de Power Pivot.

• Haga clic en tabla dinámica.

• Seleccione PivotChart de la lista desplegable.

los Create PivotChartAparece el cuadro de diálogo. Seleccione Nueva hoja de trabajo y haga clic en Aceptar.

Se crea un gráfico dinámico vacío en una nueva hoja de trabajo en la ventana de Excel.

Como puede observar, todas las tablas del modelo de datos se muestran en la lista Campos del gráfico dinámico.

• Haga clic en la tabla Vendedor en la lista Campos del gráfico dinámico.

• Arrastre los campos - Vendedor y Región al área AXIS.

En el gráfico dinámico aparecen dos botones de campo para los dos campos seleccionados. Estos son los botones del campo Axis. El uso de botones de campo es para filtrar los datos que se muestran en el gráfico dinámico.

Arrastrar TotalSalesAmount de cada una de las cuatro tablas: East_Sales, North_Sales, South_Sales y West_Sales al área de ∑ VALUES.

Lo siguiente aparece en la hoja de trabajo:

• En el gráfico dinámico, el gráfico de columnas se muestra de forma predeterminada.

• En el área LEYENDA, se agregan ∑ VALORES.

• Los valores aparecen en la leyenda del gráfico dinámico, con el título Valores.

• Los botones de campo de valor aparecen en el gráfico dinámico. Puede eliminar la leyenda y los botones de campo de valor para una apariencia más ordenada del gráfico dinámico.

• Haga clic en el botón en la esquina superior derecha del gráfico dinámico. losChart Elements aparece la lista desplegable.

Desmarque la casilla Leyenda en la lista Elementos del gráfico. La leyenda se elimina del gráfico dinámico.

• Haga clic derecho en los botones del campo de valor.

• Seleccione Ocultar botones de campo de valor en el gráfico en la lista desplegable.

Se eliminan los botones de campo de valor del gráfico.

Note- La visualización de los botones de campo y / o la leyenda depende del contexto del gráfico dinámico. Debe decidir qué se debe mostrar.

Lista de campos del gráfico dinámico

Como en el caso de Power PivotTable, la lista de campos de Power PivotChart también contiene dos pestañas: ACTIVO y TODOS. En la pestaña TODOS, se muestran todas las tablas de datos del modelo de datos de Power Pivot. En la pestaña ACTIVO, se muestran las tablas desde las que se agregan los campos al gráfico dinámico.

Asimismo, las áreas son como en el caso de Excel PivotChart. Hay cuatro áreas:

• AXIS (Categories)

• LEGEND (Series)

• ∑ VALUES

• FILTERS

Como ha visto en la sección anterior, Legend se rellena con ∑ Values. Además, los botones de campo se agregan al gráfico dinámico para facilitar el filtrado de los datos que se muestran.

Filtros en el gráfico dinámico

Puede utilizar los botones de campo Eje en el gráfico para filtrar los datos que se muestran. Haga clic en la flecha del botón del campo Eje - Región.

La lista desplegable que aparece tiene el siguiente aspecto:

Puede seleccionar los valores que desea mostrar. Alternativamente, puede colocar el campo en el área FILTROS para filtrar los valores.

Arrastre el campo Región al área FILTROS. El botón Filtro de informe - Región aparece en el gráfico dinámico.

Haga clic en la flecha del botón Filtro de informe - Región. La lista desplegable que aparece tiene el siguiente aspecto:

Puede seleccionar los valores que desea mostrar.

Rebanadores en gráfico dinámico

El uso de Slicers es otra opción para filtrar datos en Power PivotChart.

• Haga clic en la pestaña ANALIZAR debajo de las herramientas DIAGRAMA PIVOT en la cinta.

• Haga clic en Insertar rebanador en el grupo Filtro. losInsert Slicer Aparece el cuadro de diálogo.

Todas las tablas y los campos correspondientes aparecen en el cuadro de diálogo Insertar rebanador.

Haga clic en el campo Región en la tabla Vendedor en el cuadro de diálogo Insertar rebanador.

La cortadora para el campo Región aparece en la hoja de trabajo.

Como puede observar, el campo Región todavía existe como un campo Eje. Puede seleccionar los valores que desea mostrar haciendo clic en los botones Slicer.

Recuerde que puede hacer todo esto en unos minutos y también de forma dinámica gracias al modelo de datos de Power Pivot y las relaciones definidas.

Herramientas de gráficos dinámicos

En Power PivotChart, las HERRAMIENTAS DE PIVOTCHART tienen tres pestañas en la cinta en comparación con dos pestañas en Excel PivotChart -

• ANALYZE

• DESIGN

• FORMAT

La tercera pestaña - FORMATO es la pestaña adicional en Power PivotChart.

Haga clic en la pestaña FORMATO en la cinta.

Las opciones de la cinta en la pestaña FORMATO son todas para agregar esplendor a su gráfico dinámico. Puede utilizar estas opciones con prudencia, sin aburrirse demasiado.

Power Pivot le ofrece diferentes combinaciones de Power PivotTable y Power PivotChart para la exploración, visualización y generación de informes de datos. Ha aprendido las tablas dinámicas y los gráficos dinámicos en los capítulos anteriores.

En este capítulo, aprenderá a crear combinaciones de tabla y gráfico desde la ventana de Power Pivot.

Considere el siguiente modelo de datos en Power Pivot que usaremos para las ilustraciones:

Gráfico y tabla (horizontal)

Con esta opción, puede crear un Power PivotChart y una Power PivotTable, uno al lado del otro horizontalmente en la misma hoja de trabajo.

• Haga clic en la pestaña Inicio en la ventana de Power Pivot.

• Haga clic en tabla dinámica.

• Seleccione Gráfico y tabla (horizontal) de la lista desplegable.

Aparece el cuadro de diálogo Crear gráfico dinámico y tabla dinámica (horizontal). Seleccione Nueva hoja de trabajo y haga clic en Aceptar.

Un gráfico dinámico vacío y una tabla dinámica vacía aparecen en una nueva hoja de trabajo.

• Haga clic en el gráfico dinámico.

• Arrastrar NOC_CountryRegion desde la mesa de Medallas hasta el área AXIS.

• Arrastre Medalla de la tabla Medallas al área ∑ VALORES.

• Haga clic derecho en el gráfico y seleccione Change Chart Type de la lista desplegable.

• Seleccione Gráfico de área.

• Cambie el título del gráfico a Total No. of Medals − Country Wise.

Como puede ver, Estados Unidos tiene el mayor número de medallas (> 4500).

• Haga clic en la tabla dinámica.

• Arrastre Deporte desde la tabla Deportes al área FILAS.

• Arrastre Medalla de la tabla Medallas al área ∑ VALORES.

• Arrastrar NOC_CountryRegion desde la mesa de medallas hasta la zona de FILTROS.

• Filtrar el NOC_CountryRegion campo al valor EE. UU.

Cambiar el PivotTable Report Diseño para Outline Formar.

• Anule la selección de Deporte en la tabla Deportes.

• Arrastre Género de la tabla Medallas al área FILAS.

Gráfico y tabla (vertical)

Con esta opción, puede crear un Power PivotChart y una Power PivotTable, uno debajo de otro verticalmente en la misma hoja de trabajo.

• Haga clic en la pestaña Inicio en la ventana de Power Pivot.

• Haga clic en tabla dinámica.

• Seleccione Gráfico y tabla (vertical) en la lista desplegable.

los Create PivotChart and PivotTable (Vertical)Aparece el cuadro de diálogo. Seleccione Nueva hoja de trabajo y haga clic en Aceptar.

Un gráfico dinámico vacío y una tabla dinámica vacía aparecen verticalmente en una nueva hoja de trabajo.

• Haga clic en el gráfico dinámico.

• Arrastre Año desde la tabla Medallas al área EJE.

• Arrastre Medalla de la tabla Medallas al área ∑ VALORES.

• Haga clic derecho en el gráfico y seleccione Cambiar tipo de gráfico en la lista desplegable.

• Seleccione Gráfico de líneas.

• Marque la casilla Etiquetas de datos en los elementos del gráfico.

• Cambie el título del gráfico a Total No. of Medals – Year Wise.

Como puede observar, el año 2008 tiene el mayor número de medallas (2450).

• Haga clic en la tabla dinámica.

• Arrastre Deporte desde la tabla Deportes al área FILAS.

• Arrastre Género de la tabla Medallas al área FILAS.

• Arrastre Medalla de la tabla Medallas al área ∑ VALORES.

• Arrastre Año desde la tabla Medallas al área FILTROS.

• Filtre el campo Año al valor 2008.

• Cambie el diseño del informe de tabla dinámica a formulario de esquema.

• Filtre el campo Deporte con filtros de valor a Mayor o igual a 80.

Una jerarquía en el modelo de datos es una lista de columnas anidadas en una tabla de datos que se consideran un solo elemento cuando se utilizan en una tabla dinámica de energía. Por ejemplo, si tiene las columnas: País, Estado, Ciudad en una tabla de datos, se puede definir una jerarquía para combinar las tres columnas en un campo.

En la lista Campos de Power PivotTable, la jerarquía aparece como un campo. Por lo tanto, puede agregar solo un campo a la tabla dinámica, en lugar de los tres campos de la jerarquía. Además, le permite subir o bajar los niveles anidados de forma significativa.

Considere el siguiente modelo de datos para las ilustraciones de este capítulo.

Crear una jerarquía

Puede crear jerarquías en la vista de diagrama del modelo de datos. Tenga en cuenta que puede crear una jerarquía basada en una única tabla de datos.

• Haga clic en las columnas: Deporte, Disciplina y Evento en la tabla de datos Medalla en ese orden. Recuerde que el orden es importante para crear una jerarquía significativa.

• Haz clic derecho en la selección.

• Seleccione Crear jerarquía en la lista desplegable.

Se crea el campo de jerarquía con los tres campos seleccionados como niveles secundarios.

Cambiar el nombre de una jerarquía

Para cambiar el nombre del campo de jerarquía, haga lo siguiente:

• Haga clic derecho en Hierarchy1.

• Seleccione Cambiar nombre en la lista desplegable.

Tipo EventHierarchy.

Crear una tabla dinámica con una jerarquía en el modelo de datos

Puede crear una Power PivotTable utilizando la jerarquía que creó en el modelo de datos.

• Haga clic en la pestaña Tabla dinámica en la cinta de opciones en la ventana de Power Pivot.

• Haga clic en Tabla dinámica en la cinta.

los Create PivotTableAparece el cuadro de diálogo. Seleccione Nueva hoja de trabajo y haga clic en Aceptar.

Se crea una tabla dinámica vacía en una nueva hoja de trabajo.

En la lista Campos de tabla dinámica, EventHierarchyaparece como un campo en la tabla Medallas. Los otros campos de la tabla Medallas se contraen y se muestran como Más campos.

• Haga clic en la flecha frente a EventHierarchy.

• Haga clic en la flecha frente a Más campos.

Se mostrarán los campos bajo EventHierarchy. Todos los campos de la tabla Medallas se mostrarán en Más campos.

Como puede observar, los tres campos que agregó a la jerarquía también aparecen debajo More Fieldscon casillas de verificación. Si no desea que aparezcan en la lista Campos de tabla dinámica enMore Fields, debe ocultar las columnas en la tabla de datos - Medallas en la vista de datos en la ventana de Power Pivot. Siempre puedes mostrarlos cuando quieras.

Agregue campos a la tabla dinámica de la siguiente manera:

• Arrastrar EventHierarchy al área FILAS.

• Arrastre Medalla al área ∑ VALUES.

Los valores del campo Deporte aparecen en la tabla dinámica con un signo + delante de ellos. Se muestra el recuento de medallas de cada deporte.

• Haga clic en el signo + antes de Aquatics. Se mostrarán los valores del campo DisciplineID debajo de Acuáticos.

• Haga clic en el niño D22 que aparece. Se mostrarán los valores del campo Evento debajo de D22.

Como puede observar, el recuento de medallas se da para los eventos, que se resumen a nivel de padres: DisciplineID, que se resumen más a fondo a nivel de padres: el deporte.

Crear una jerarquía basada en varias tablas

Suponga que desea mostrar las disciplinas en la tabla dinámica en lugar de los ID de disciplina para que sea un resumen más legible y comprensible. Para hacer esto, debe tener el campo Disciplina en la tabla de Medallas que, como sabe, no lo es. El campo de disciplina está en la tabla de datos de Disciplinas, pero no puede crear una jerarquía con campos de más de una tabla. Pero hay una forma de obtener el campo obligatorio de la otra tabla.

Como es de su conocimiento, las tablas - Medallas y Disciplinas están relacionadas. Puede agregar el campo Disciplina de la tabla Disciplinas a la tabla Medallas, creando una columna usando la relación con DAX.

• Haga clic en la vista de datos en la ventana de Power Pivot.

• Haga clic en la pestaña Diseño en la cinta.

• Haga clic en Agregar.

La columna - Agregar columna en el lado derecho de la tabla está resaltada.

Tipo = RELATED (Disciplines [Discipline])en la barra de fórmulas. Una nueva columna -CalculatedColumn1 se crea con los valores como valores del campo Disciplina en la tabla Disciplinas.

Cambie el nombre de la nueva columna así obtenida en la tabla Medallas como Disciplina. A continuación, debe eliminar DisciplineID de la jerarquía y agregar Discipline, que aprenderá en las siguientes secciones.

Eliminar un nivel secundario de una jerarquía

Como puede observar, la jerarquía es visible solo en la vista de diagrama y no en la vista de datos. Por lo tanto, puede editar una jerarquía solo en la vista de diagrama.

• Haga clic en la vista de diagrama en la ventana de Power Pivot.

• Haga clic derecho en DisciplineID en EventHierarchy.

• Seleccione Remove from Hierarchy de la lista desplegable.

Aparece el cuadro de diálogo Confirmar. Hacer clicRemove from Hierarchy.

El campo DisciplineID se elimina de la jerarquía. Recuerde que ha eliminado el campo de la jerarquía, pero el campo de origen todavía existe en la tabla de datos.

A continuación, debe agregar el campo Disciplina a EventHierarchy.

Agregar un nivel secundario a una jerarquía

Puede agregar el campo Disciplina a la jerarquía existente - EventHierarchy de la siguiente manera -

• Haga clic en el campo de la tabla Medallas.

• Arrástrelo al campo Eventos a continuación en la Jerarquía de eventos.

El campo Disciplina se agrega a EventHierarchy.

Como puede observar, el orden de los campos en EventHierarchy es Deporte-Evento-Disciplina. Pero, como usted sabe, tiene que ser Deporte-Disciplina-Evento. Por lo tanto, debe cambiar el orden de los campos.

Cambiar el orden de un nivel secundario en una jerarquía

Para mover el campo Disciplina a la posición después del campo Deporte, haga lo siguiente:

• Haga clic con el botón derecho en el campo Disciplina en EventHierarchy.

• Seleccione Mover hacia arriba en la lista desplegable.

El orden de los campos cambia a Deporte-Disciplina-Evento.

Tabla dinámica con cambios en la jerarquía

Para ver los cambios que realizó en EventHierarchy en la tabla dinámica, no necesita crear una nueva tabla dinámica. Puede verlos en la propia tabla dinámica existente.

Haga clic en la hoja de trabajo con la tabla dinámica en la ventana de Excel.

Como puede observar, en la lista Campos de tabla dinámica, los niveles secundarios de EventHierarchy reflejan los cambios que realizó en la Jerarquía en el modelo de datos. Los mismos cambios también se reflejan en la tabla dinámica en consecuencia.

Haga clic en el signo + delante de Acuáticos en la tabla dinámica. Los niveles secundarios aparecen como valores del campo Disciplina.

Ocultar y mostrar jerarquías

Puede optar por ocultar las jerarquías y mostrarlas cuando lo desee.

• Desmarque la casilla Jerarquías en el menú superior de la vista de diagrama para ocultar las jerarquías.

• Marque la casilla Jerarquías para mostrar las jerarquías.

Creación de una jerarquía de otras formas

Además de la forma en que creó la jerarquía en las secciones anteriores, puede crear una jerarquía de otras dos formas.

1. Haga clic en el botón Crear jerarquía en la esquina superior derecha de la tabla de datos Medallas en la vista de diagrama.

Se crea una nueva jerarquía en la tabla sin ningún campo en ella.

Arrastre los campos Año y Temporada, en ese orden, a la nueva jerarquía. La jerarquía muestra los niveles secundarios.

2. Otra forma de crear la misma jerarquía es la siguiente:

• Haga clic con el botón derecho en el campo Año en la tabla de datos de Medallas en la vista de diagrama.

• Seleccione Crear jerarquía en la lista desplegable.

Se crea una nueva jerarquía en la tabla con Año como campo secundario.

Arrastre la temporada de campo a la jerarquía. La jerarquía muestra los niveles secundarios.

Eliminar una jerarquía

Puede eliminar una jerarquía del modelo de datos de la siguiente manera:

• Haga clic derecho en la jerarquía.

• Seleccione Eliminar de la lista desplegable.

los ConfirmAparece el cuadro de diálogo. Hacer clicDelete from Model.

La jerarquía se elimina.

Cálculos usando jerarquía

Puede crear cálculos utilizando una jerarquía. En EventsHierarchy, puede mostrar el número de medallas en un nivel secundario como un porcentaje del número de medallas en su nivel principal de la siguiente manera:

• Haga clic con el botón derecho en un valor de Conteo de medallas de un evento.

• Seleccione Configuración de campo de valor en la lista desplegable.

Aparece el cuadro de diálogo Configuración de campo de valor.

• Haga clic en el Show Values As lengüeta.

• Seleccione% del total de la fila principal de la lista y haga clic en Aceptar.

Los niveles secundarios se muestran como el porcentaje de los totales principales. Puede verificar esto sumando los valores porcentuales del nivel secundario de un padre. La suma sería del 100%.

Profundizar y desglosar una jerarquía

Puede profundizar y profundizar rápidamente en los niveles de una jerarquía utilizando la herramienta Exploración rápida.

• Haga clic en un valor del campo Evento en la tabla dinámica.

• Haga clic en la herramienta Exploración rápida, que aparece en la esquina inferior derecha de la celda que contiene el valor seleccionado.

los Explore box with Drill Upaparece la opción. Esto se debe a que desde Event solo puede desglosar, ya que no hay niveles secundarios debajo.

Hacer clic Drill Up.

Los datos de la tabla dinámica se desglosan hasta la disciplina.

Haga clic en la herramienta Exploración rápida, que aparece en la esquina inferior derecha de la celda que contiene un valor.

Aparece el cuadro Explorar con las opciones Drill Up y Drill Down mostradas. Esto se debe a que desde Disciplina puede profundizar en Deporte o profundizar en Evento.

De esta manera, puede subir y bajar rápidamente en la jerarquía.

Puede crear informes estéticos de su análisis de datos con Power Pivot Data que se encuentra en Data Model.

Las características importantes son:

• Puede utilizar gráficos dinámicos para producir informes visuales de sus datos. Puede utilizar los diseños de informes para estructurar sus tablas dinámicas para que sean fáciles de leer.

• Puede insertar Slicers para filtrar datos en el informe.

• Puede usar un Slicer común para el gráfico dinámico y la tabla dinámica que se encuentran en el mismo informe.

• Una vez que su informe final esté listo, puede optar por ocultar las rebanadoras de la pantalla.

Aprenderá cómo obtener informes con las opciones que están disponibles en Power Pivot en este capítulo.

Considere el siguiente modelo de datos para las ilustraciones de este capítulo.

Informes basados ​​en Power PivotChart

Cree un Power PivotChart de la siguiente manera:

• Haga clic en la pestaña Inicio en la cinta de opciones en la ventana de PowerPivot.

• Haga clic en tabla dinámica.

• Seleccione PivotChart de la lista desplegable.

• Hacer clic New Worksheet en el cuadro de diálogo Crear gráfico dinámico.

Se crea un gráfico dinámico vacío en una nueva hoja de trabajo en la ventana de Excel.

• Arrastre Deporte desde la tabla Medallas al área del Eje.

• Arrastre Medalla de la Tabla de medallas al área ∑ VALORES.

• Haga clic en la pestaña ANALIZAR en HERRAMIENTAS PIVOTABLES en la cinta.

• Haga clic en Insertar rebanador en el Grupo de filtros. Aparece el cuadro de diálogo Inset Slicers.

• Haga clic en el campo NOC_CountryRegion en la mesa de Medallas.

• Haga clic en Aceptar.

Aparece Slicer NOC_CountryRegion.

• Seleccione EE. UU.

• Arrastre Género de la tabla Medallas al área GÉNERO.

• Haga clic con el botón derecho en el gráfico dinámico.

• Seleccione Cambiar tipo de gráfico en la lista desplegable.

Aparece el cuadro de diálogo Cambiar tipo de gráfico.

Haga clic en Columna apilada.

• Insertar rebanador para campo deportivo.

• Arrastre Disciplina de la tabla Disciplinas al área EJE.

• Elimina el campo Sport del área AXIS.

• Seleccione Acuáticos en el Slicer - Sport.

Diseño de informe

Cree la tabla dinámica de la siguiente manera:

• Haga clic en la pestaña Inicio en la cinta de opciones de la ventana de PowerPivot.

• Haga clic en la tabla dinámica.

• Haga clic en Tabla dinámica en la lista desplegable. Aparece el cuadro de diálogo Crear tabla dinámica.

• Haga clic en Nueva hoja de trabajo y haga clic en Aceptar. Se crea una tabla dinámica vacía en una nueva hoja de trabajo.

• Arrastre NOC_CountryRegion desde la tabla Medallas al área AXIS.

• Arrastre Deporte de la tabla Medallas al área COLUMNAS.

• Arrastre Disciplina de la tabla Disciplinas al área COLUMNAS.

• Arrastre Medalla al área ∑ VALUES.

Haga clic en el botón de flecha junto a Column Labels y seleccione Acuáticos.

• Haga clic en el botón de flecha junto a Etiquetas de fila.

• Seleccione Filtros de valor en la lista desplegable.

• Seleccione Mayor o igual a en la segunda lista desplegable.

Escriba 80 en el cuadro junto a El recuento de medallas es mayor o igual que en el cuadro de diálogo Filtro de valor.

• Haga clic en la pestaña DISEÑO en HERRAMIENTAS PIVOTABLES en la cinta.

• Haga clic en Subtotales.

• Seleccione Do Not Show Subtotals desde la lista desplegable.

La columna Subtotales - Acuáticos Total se elimina.

Haga clic en Diseño de informe y seleccione Show in Outline Form de la lista desplegable.

Marque la casilla Filas con bandas.

Los nombres de los campos aparecen en lugar de las etiquetas de fila y de columna y el informe parece autoexplicativo.

Usando una rebanadora común

Cree un gráfico dinámico y una tabla dinámica uno al lado del otro.

• Haga clic en la pestaña Inicio en la cinta de opciones en la pestaña PowerPivot.

• Haga clic en tabla dinámica.

• Seleccione Gráfico y tabla (horizontal) de la lista desplegable.

Aparece el cuadro de diálogo Crear gráfico dinámico y tabla dinámica (horizontal).

Seleccione Nueva hoja de trabajo y haga clic en Aceptar. Un gráfico dinámico vacío y una tabla dinámica vacía aparecen uno al lado del otro en una nueva hoja de trabajo.

• Haga clic en Gráfico dinámico.

• Arrastre Disciplina de la tabla Disciplinas al área EJE.

• Arrastre Medalla de la tabla Medallas al área ∑ VALORES.

• Haga clic en tabla dinámica.

• Arrastre Disciplina de la tabla Disciplinas al área FILAS.

• Arrastre Medalla de la tabla Medallas al área ∑ VALORES.

• Haga clic en la pestaña ANALIZAR en HERRAMIENTAS PIVOTABLES en la cinta.

• Haga clic en Insertar rebanador. Aparece el cuadro de diálogo Insertar rebanadores.

• Haga clic en NOC_CountryRegion y Sport en la tabla de medallas.

• Haga clic en Aceptar.

Aparecen dos segmentaciones: NOC_CountryRegion y Sport. Organícelos y ajústelos para que se alineen correctamente junto a la tabla dinámica.

• Seleccione EE. UU. En NOC_CountryRegion Slicer.

• Seleccione Aquatics en el Sport Slicer. La tabla dinámica se filtra a los valores seleccionados.

Como puede observar, el gráfico dinámico no se filtra. Para filtrar PivotChart con los mismos filtros, no necesita insertar Slicers nuevamente para PivotChart. Puede utilizar las mismas segmentaciones que ha utilizado para la tabla dinámica.

• Haga clic en NOC_CountryRegion Rebanadora.

• Haga clic en el OPTIONS pestaña en SLICER TOOLS en la cinta.

• Hacer clic Report Connectionsen el grupo Slicer. losReport Connections Aparece el cuadro de diálogo de NOC_CountryRegion Slicer.

Puede ver que todas las tablas dinámicas y gráficos dinámicos del libro se enumeran en el cuadro de diálogo.

• Haga clic en el gráfico dinámico que se encuentra en la misma hoja de trabajo que la tabla dinámica seleccionada y haga clic en Aceptar.

• Repita para Sport Slicer.

El gráfico dinámico también se filtra a los valores seleccionados en las dos segmentaciones.

A continuación, puede agregar detalles al gráfico dinámico y la tabla dinámica.

• Haga clic en el gráfico dinámico.

• Arrastre Género al área LEYENDA.

• Haga clic con el botón derecho en el gráfico dinámico.

• Seleccione Cambiar tipo de gráfico.

• Seleccione Columna apilada en el cuadro de diálogo Cambiar tipo de gráfico.

• Haga clic en la tabla dinámica.

• Arrastre Evento al área FILAS.

• Haga clic en la pestaña DISEÑO en HERRAMIENTAS PIVOTABLES en la cinta.

• Haga clic en Diseño de informe.

• Seleccione Formulario de esquema de la lista desplegable.

Seleccionar objetos para mostrar en el informe

Puede optar por no mostrar las segmentaciones en el informe final.

• Haga clic en el OPTIONS pestaña en SLICER TOOLS en la cinta.

• Haga clic en Panel de selección en el grupo Organizar. El Panel de selección aparece en el lado derecho de la ventana.

Como puede observar, el símbolo aparece junto a los objetos en el Panel de selección. Esto significa que esos objetos son visibles.

• Haga clic en el símbolo junto a NOC_CountryRegion.

• Haga clic en el símbolo junto a Deporte. El símbolo se cambia a para ambos. Esto significa que la visibilidad de las dos cortadoras está desactivada.

Cierre el panel de selección.

Puede ver que los dos Slicers no están visibles en el Informe.