Tutorial: Análisis de datos de tabla dinámica con un modelo de datos en Excel 2013

En menos de una hora, puede crear un informe de tabla dinámica de Excel que combine datos de varias tablas. La primera parte de este tutorial lo guiará en la importación y la exploración de datos. En la segunda mitad, usará el complemento Power Pivot para refinar el modelo de datos que subyace tras el informe y aprenderá a agregar cálculos, jerarquías y optimizaciones para la creación de informes en Power View.

Importar algunos datos

Para crear una tabla dinámica, necesitará algunos datos. Vamos a obtener algunos datos de ventas de la base de datos de ejemplo que ha descargado.

  1. Descargue datos de ejemplo (ContosoV2) para este tutorial. Vea Obtener datos de ejemplo para los tutoriales de modelo de datos y DAX para obtener información detallada. Extraiga y guarde los archivos de datos en una ubicación a la que se pueda obtener acceso fácilmente, como Descargas o Mis documentos.

  2. En Excel, abra un libro vacío.

  3. En Excel, haga clic en Datos > Obtener datos externos > Desde Access.

  4. Vaya a la carpeta que contiene los archivos de datos de ejemplo y seleccione ContosoSales.

  5. Haga clic en Abrir. Dado que se está conectando a un archivo de base de datos con varias tablas, aparecerá el cuadro de diálogo Seleccionar tabla para que pueda elegir las tablas que desee importar.

    cuadro de diálogo Seleccionar tabla

  6. En Seleccionar tabla, active Habilitar selección de tablas múltiples.

  7. Elija las tablas y haga clic en Aceptar.

  8. En importar datos, haga clic en Informe de tabla dinámica y haga clic en Aceptar.

¿Qué ha ocurrido?

Es posible que aún no se haya dado cuenta, pero acaba de crear un modelo de datos. Se crea automáticamente cuando importa varias tablas simultáneamente en el mismo informe de tablas dinámicas o trabaja con ellas. El modelo es principalmente transparente en Excel, pero puede verlo y modificarlo directamente mediante el complemento Power Pivot. En Excel, la presencia de un modelo de datos es evidente cuando se observa una colección de tablas en la lista Campos de tabla dinámica. Hay varias maneras de crear un modelo. Vea Crear un modelo de datos en Excel para obtener información detallada.

Explorar datos con una tabla dinámica

La exploración de datos es fácil cuando arrastra campos a las áreas Valores, Columnas y Filas de la lista de campos de tabla dinámica.

  1. En la lista de campos, desplácese hasta que encuentre la tabla FactSales.

  2. Haga clic en SalesAmount. Dado que estos datos son numéricos, Excel coloca SalesAmount automáticamente en el área Valores.

  3. En DimDate, arrastre CalendarYear hasta Columnas.

  4. En DimProductSubcategory, arrastre ProductSubcategoryName hasta Filas.

  5. En DimProduct, arrastre BrandName hasta Filas y colóquelo debajo de la subcategoría.

La tabla dinámica debería tener un aspecto similar al que se muestra en la pantalla siguiente.

Tabla dinámica que muestra datos de ejemplo

Con un esfuerzo mínimo, ahora dispone de una tabla dinámica básica que incluye campos de cuatro tablas distintas. Lo que ha facilitado tanto esta tarea fuero las relaciones existentes entre las tablas. Dado que existían relaciones de tabla en el origen y que importó todas las tablas en una sola operación, Excel pudo volver a crear esas relaciones en el modelo.

Sin embargo, ¿qué sucede si los datos provienen de orígenes distintos o se importan más adelante? Normalmente, puede incorporar datos nuevos al crear relaciones en función de las columnas que coinciden. En el paso siguiente, importará tablas adicionales y conocerá los requisitos y los pasos necesarios para crear relaciones nuevas.

Agregar más tablas

Aprender a configurar las relaciones de tablas requiere disponer de tablas adicionales no conectadas. En este paso, obtendrá los datos restantes que se usan en este tutorial mediante la importación de un archivo de base de datos adicional y el pegado de los datos de otros dos libros distintos.

Agregar categorías de productos

  1. En el libro, abra una nueva hoja. La usará para almacenar datos adicionales.

  2. Haga clic en Datos > Obtener datos externos > Desde Access.

  3. Vaya a la carpeta que contiene los archivos de datos de ejemplo y seleccione ProductCategories. Haga clic en Abrir.

  4. En Importar datos, seleccione Tabla y haga clic en Aceptar.

Agregar datos de geografía

  1. Inserte otra hoja.

  2. En los archivos de datos de ejemplo, abra Geography.xlsx, coloque el cursor en A1 y luego presione Ctrl-Mayús-Fin para seleccionar todos los datos.

  3. Copie los datos al Portapapeles.

  4. Pegue los datos en la hoja vacía recién agregada.

  5. Haga clic en Dar formato como tabla y elija un estilo. Aplicar formato a los datos como tablas le permite asignarles un nombre, lo que le será útil al definir relaciones en un paso posterior.

  6. En Formato como tabla, compruebe que Mi tabla tiene encabezados está seleccionado. Haga clic en Aceptar.

  7. Asigne a la tabla el nombre Geography. En Herramientas de tabla > Diseño, escriba Geography en Nombre de tabla.

  8. Cierre Geography.xlsx para borrarlo del área de trabajo.

Agregar datos del almacén

  • Repita los pasos anteriores con el archivo Stores.xlsx y pegue su contenido en una hoja vacía. Asigne a la tabla el nombre Stores.

Ahora debe tener cuatro hojas. Hoja1 contiene la tabla dinámica, Hoja2 contiene ProductCategories, Hoja3 contiene Geography y Hoja4 contiene Stores. Dado que se tomó su tiempo para asignar nombre a cada tabla, el paso siguiente de crear las relaciones será mucho más sencillo.

Usar los campos de las tablas recién importadas

Puede comenzar inmediatamente a usar campos de las tablas recién importadas. Si Excel no puede determinar cómo incorporar un campo en el informe de tablas dinámicas, se le solicitará que cree una relación de tablas que asocia la tabla nueva con una que ya forma parte del modelo.

  1. En la parte superior de los campos de tabla dinámica, haga clic en Todas para ver la lista completa de tablas disponibles.

  2. Desplácese hasta el final de la lista, donde encontrará las nuevas tablas que acaba de agregar.

  3. Expanda Stores.

  4. Arrastre StoreName hasta el área Filtros.

  5. Observe que Excel le solicita que cree una relación. Esta notificación se produce porque usó campos de una tabla sin relación con el modelo.

  6. Haga clic en Crear para abrir el cuadro de diálogo Crear relaciones.

  7. En Tabla, elija FactSales. En los datos de ejemplo que usa, FactSales contiene información detallada de ventas y costes del negocio de Contoso, así como claves a otras tablas, incluidos los códigos de almacén que también están presentes en el archivo Stores.xlsx que importó en el paso anterior.

  8. En Columna (Foreign), elija StoreKey.

  9. En Tabla relacionada, elija Stores.

  10. En Columna relacionada (Primary), elija StoreKey.

  11. Haga clic en Aceptar.

En segundo plano, Excel está generando un modelo de datos que se puede usar en el libro en cualquier número de tablas dinámicas, gráficos dinámicos o informes de Power View. Fundamental en este modelo son las relaciones de tablas que determinan las rutas de navegación y cálculo que se usan en un informe de tabla dinámica. En la tarea siguiente, creará manualmente relaciones para conectar los datos importados.

Agregar relaciones

Puede crear sistemáticamente relaciones de tablas para todas las tablas nuevas que importe. Si comparte el libro con colegas, estos apreciarán el hecho de tener relaciones predefinidas si conocen los datos menos que usted.

Al crear relaciones manualmente, trabajará con dos tablas a la vez. Para cada tabla, elegirá las columnas que indican a Excel cómo buscar las filas relacionadas en otra tabla.

Su explorador no admite vídeo. Instale Microsoft Silverlight, Adobe Flash Player o Internet Explorer 9.

Relacionar ProductSubcategory y ProductCategory

  1. En Excel, haga clic en Datos > Relaciones > Nuevo.

  2. En Tabla, elija DimProductSubcategory.

  3. En Columna (Foreign), elija ProductCategoryKey.

  4. En Tabla relacionada, elija Table_ProductCategory.accdb.

  5. En Columna relacionada (Primary), elija ProductCategoryKey.

  6. Haga clic en Aceptar.

  7. Cierre el cuadro de diálogo Administrar relaciones.

Agregue categorías a la tabla dinámica.

Aunque el modelo de datos se ha actualizado para incluir tablas adicionales y relaciones, la tabla dinámica aún no las está usando. En esta tarea, agregará ProductCategory a la lista Campos de tabla dinámica.

  1. En Campos de tabla dinámica, haga clic en Todo para mostrar las tablas existentes en el modelo de datos.

  2. Desplácese a la parte inferior de la lista.

  3. En el área Filas, quite BrandName.

  4. Expanda Table_DimProductCategories.accdb.

  5. Arrastre ProductCategoryName al área Filas y colóquelo encima de ProductSubcategory.

  6. En Campos de tabla dinámica, haga clic en Activo para comprobar que las tablas que ha usado ahora son conocidas en la tabla dinámica.

Punto de comprobación: revise lo que aprendió

Ahora tiene una tabla dinámica que incluye datos de varias tablas, que importó en un paso posterior. Para unir todos estos datos, tenía que crear las relaciones entre tablas que Excel usa para correlacionar las filas. Aprendió que tener columnas que proporcionan datos coincidentes es esencial para buscar las filas relacionadas. En los archivos de datos de ejemplo, todas las tablas contienen una columna que se puede usar con este fin.

Aunque la tabla dinámica es funcional, probablemente haya observado varias cosas que podrían ser mejoradas. La lista de campos de tabla dinámica parece tener tablas (DimEntity) y columnas (ETLLoadID) adicionales que no están relacionadas con la empresa de Contoso. Y aún no hemos integrado los datos Geography.

Siguiente: visualizar y extender el modelo mediante Power Pivot

En la siguiente serie de tareas, usará el complemento PowerPivot en Microsoft Excel 2013 de Microsoft Office a fin de extender el modelo. Verá que puede crear relaciones más fácilmente mediante la vista de diagrama que proporciona el complemento. También usará el complemento para crear cálculos y jerarquías, ocultar los elementos que no deben aparecer en la lista de campos y optimizar los datos para informes adicionales.

Nota   El complemento PowerPivot en Microsoft Excel 2013 está disponible en Office Professional Plus. Vea Iniciar el complemento Power Pivot para Microsoft Excel 2013 para obtener más información.

Agregue Power Pivot a la cinta de opciones de Excel. Para ello, habilite el complemento Power Pivot.

  1. Vaya a Archivo > Opciones > Complementos.

  2. En el cuadro Administrar, haga clic en Complementos COM> Ir.

  3. Active la casilla Microsoft Office PowerPivot en Microsoft Excel 2013 para Excel 2013 y haga clic en Aceptar.

La cinta de opciones ahora contiene la pestaña de Power Pivot.

Agregar una relación mediante la vista de diagrama de Power Pivot

  1. En Excel, haga clic en Hoja3 para convertirla en la hoja activa. La Hoja3 contiene la tabla Geography que importó antes.

  2. En la cinta de opciones, haga clic en Power Pivot > Agregar al modelo de datos. Este paso agrega la tabla Geography al modelo. También abre el complemento Power Pivot que usará para realizar los pasos restantes de esta tarea.

  3. Observe que la ventana de Power Pivot muestra todas las tablas del modelo, incluida Geography. Haga clic en un par de tablas. En el complemento, puede ver todos los datos que contiene el modelo.

  4. En la ventana de Power Pivot, en la sección Ver, haga clic en Vista de diagrama.

  5. Use la barra de desplazamiento para cambiar el tamaño del diagrama para poder ver todos los objetos. Observe que dos tablas no están relacionadas con el resto del diagrama: DimEntity y Geography.

  6. Haga clic con el botón derecho del mouse en DimEntity y haga clic en Eliminar. Esta tabla es un artefacto de la base de datos original y no se necesita en el modelo.

  7. Acerque Geography para poder ver todos sus campos. Puede utilizar el control deslizante para hacer mayor el diagrama de la tabla.

  8. Observe que Geography tiene GeographyKey. Esta columna contiene valores que identifican de forma singular cada fila de la tabla Geography. Veamos si otras tablas del modelo también usan esta clave. En caso afirmativo, podemos crear una relación que conecta Geography al resto del modelo.

  9. Haga clic en Buscar.

  10. En Buscar metadatos, escriba GeographyKey.

  11. Haga clic en Buscar siguiente varias veces. Observará que GeographyKey se muestra en la tabla Geography y en la tabla Stores.

  12. Vuelva a posicionar la tabla Geography de modo que esté junto a Stores.

  13. Arrastre la columna GeographyKey desde Stores hasta la columna GeographyKey de Geography. Power Pivot dibuja una línea entre las dos columnas que indica la relación.

En esta tarea, aprendió una nueva técnica para agregar tablas y crear relaciones. Ahora dispone de un modelo totalmente integrado, con todas las tablas conectadas y disponibles para la tabla dinámica de Hoja1.

Sugerencia   En la vista Diagrama, hay varios diagramas extendidos completamente y que muestran columnas como, por ejemplo, ETLLoadID, LoadDate y UpdateDate. Estos campos concretos son artefactos del almacén de datos original de Contoso, agregado para permitir la extracción de datos y la carga de operaciones. No se necesitan en el modelo, por lo que puede eliminarlos. Para ello, selecciónelos, haga clic con el botón secundario del mouse en el campo y haga clic en Eliminar.

Crear una columna calculada

En Power Pivot, puede usar Expresiones de análisis de datos (DAX) para agregar cálculos. En esta tarea, calculará las ganancias totales y agregará la columna calculada que hace referencia a valores de datos de otras tablas. Más adelante, verá cómo usar estas columnas de referencia para simplificar el modelo.

  1. En la ventana de Power Pivot, vuelva a la vista de datos.

  2. Cambie el nombre de la tabla Table_ProductCategories.accdb por un nombre más descriptivo. Hará referencia a esta tabla en los pasos subsiguientes y un nombre más corto facilitará la lectura de los cálculos. Haga clic con el botón derecho del mouse, haga clic en Cambiar nombre, escriba ProductCategories y presione Entrar.

  3. Seleccione la tabla FactSales.

  4. Haga clic en Diseño > Columnas > Agregar.

  5. En la barra de fórmula encima de la tabla, escriba la fórmula siguiente. La opción Autocompletar lo ayuda a escribir nombres completos de columnas y tablas, y enumera las funciones que están disponibles. También puede hacer clic en la columna y Power Pivot agregará el nombre de columna a la fórmula.

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

  6. Cuando haya terminado de crear la fórmula, presione Entrar para aceptar la fórmula.

    Los valores se rellenan para todas las filas de la columna calculada. Si se desplaza hacia abajo por la tabla, verá que las filas pueden tener valores diferentes para esta columna, basado en los datos que hay en cada fila.

  7. Para cambiar el nombre de la columna, haga clic con el botón derecho del mouse en CalculatedColumn1 y seleccione Cambiar nombre de columna. Escriba Profit y presione Entrar.

  8. Ahora seleccione la tabla DimProduct.

  9. Haga clic en Diseño > Columnas > Agregar.

  10. En la barra de fórmulas situada encima de la tabla, escriba la siguiente fórmula.

    = RELATED(ProductCategories[ProductCategoryName])

    La función RELATED devuelve un valor de una tabla relacionada. En este caso, la tabla ProductCategories incluye los nombres de las categorías de productos, que serán de utilidad en la tabla DimProduct cuando cree una jerarquía que incluya información de la categoría. Para obtener más información sobre esta función, vea Función RELATED (DAX).

  11. Cuando haya finalizado de crear la fórmula, presione Entrar para aceptarla.

    Los valores se rellenan para todas las filas de la columna calculada. Si se desplaza hacia abajo por la tabla, verá que cada fila tiene ahora un nombre de categoría de producto.

  12. Cambie el nombre de la columna. Para ello, haga clic con el botón derecho del mouse en CalculatedColumn1 y seleccione Cambiar nombre de columna. Escriba ProductCategory y presione Entrar.

  13. Haga clic en Diseño > Columnas > Agregar.

  14. En la barra de fórmula encima de la tabla, escriba la fórmula siguiente y presione Entrar para aceptarla.

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. Cambie el nombre de la columna. Para ello, haga clic con el botón derecho del mouse en CalculatedColumn1 y seleccione Cambiar nombre de columna. Escriba ProductSubcategory y presione Entrar.

Crear una jerarquía

La mayoría de los modelos incluyen datos que son intrínsecamente jerárquicos. Algunos ejemplos comunes son los datos de calendario, datos geográficos y categorías de productos. Crear jerarquías es útil porque puede arrastrar un elemento (la jerarquía) a un informe en lugar de tener que ensamblarlo y ordenar los mismos campos repetidamente.

  1. En Power Pivot, cambie a la vista de diagrama. Expanda la tabla DimDate para poder ver con más facilidad todos sus campos.

  2. Mantenga presionada la tecla CTRL y haga clic en las columnas CalendarYear, CalendarQuarter y CalendarMonth (necesitará desplazarse hacia abajo por la tabla).

  3. Con las tres columnas seleccionadas, haga clic con el botón secundario en ellas y seleccione Crear jerarquía. Se creará un nodo primario de la jerarquía, Jerarquía 1, en la parte inferior de la tabla y las columnas seleccionadas se copiarán bajo la jerarquía como nodos secundarios.

  4. Escriba Dates como nombre de la nueva jerarquía.

  5. Agregue la columna FullDateLabel a la jerarquía. Haga clic con el botón derecho del mouse en FullDateLabel y seleccione Agregar a la jerarquía. Elija Date. FullDateLabel contiene una fecha completa, que incluye el año, el mes y el día. Compruebe que FullDateLabel aparezca en último lugar de la jerarquía. Ahora tendrá una jerarquía multinivel que incluye año, trimestre, mes y días de calendario individuales.

  6. Todavía en la vista Diagrama, señale la tabla DimProduct y, a continuación, haga clic en el botón Crear jerarquía del encabezado de tabla. Aparecerá un nodo primario de jerarquía vacío en la parte inferior de la tabla.

  7. Escriba Product Categories como nombre de la nueva jerarquía.

  8. Para crear los nodos secundarios de jerarquía, arrastre las columnas ProductCategory y ProductSubcategory a la jerarquía.

  9. Haga clic con el botón derecho en ProductName y seleccione Agregar a la jerarquía. Elija Product Categories.

Ahora que conoce un par de maneras diferentes de crear una jerarquía, vamos a usarlas en la tabla dinámica.

  1. Vuelva a Excel.

  2. En Hoja1 (la hoja que contiene la tabla dinámica), quite los campos en el área Filas.

  3. Reemplácelos con la nueva jerarquía Product Categories en DimProduct.

  4. Asimismo, reemplace CalendarYear en el área Columnas con la jerarquía Dates de DimDate.

Cuando explore los datos ahora, será fácil ver las ventajas del uso de jerarquías. Puede expandir y cerrar áreas diferentes de la tabla dinámica de forma independiente, lo que proporciona más control sobre el modo en que se utiliza el espacio disponible. Además, si agrega una sola jerarquía tanto a Filas como a Columnas, obtendrá un detalle completo e inmediato, sin tener que apilar varios campos para obtener un efecto similar.

Ocultar columnas

Ahora que ha creado una jerarquía Product Categories y la ha colocado en DimProduct, ya no necesita DimProductCategory o DimProductSubcategory en la lista Campos de tabla dinámica. En esta tarea, aprenderá a ocultar tablas y columnas extrañas que ocupan espacio en la lista Campos de tabla dinámica. Al ocultar las tablas y las columnas, se mejora la experiencia de creación de informes sin afectar al modelo que proporciona las relaciones y los cálculos de datos.

Su explorador no admite vídeo. Instale Microsoft Silverlight, Adobe Flash Player o Internet Explorer 9.

Puede ocultar columnas individuales, un intervalo de columnas o la tabla entera. Los nombres de columna y de tabla se atenúan para reflejar que están ocultos para los clientes de informes que usan el modelo. Las columnas ocultas se atenúan en el modelo para indicar su estado, pero seguirán estando visibles en la Vista de datos para que pueda trabajar con ellas.

  1. En Power Pivot, asegúrese de que la vista de datos esté seleccionada.

  2. En las pestañas de la parte inferior, haga clic con el botón derecho en DimProductSubcategory y seleccione Ocultar en herramientas cliente.

  3. Repita con ProductCategories.

  4. Abra DimProduct.

  5. Haga clic con el botón derecho en las columnas siguientes y haga clic en Ocultar en herramientas cliente:

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. Seleccione de manera múltiple las columnas adyacentes. Comience con ClassID y siga con ProductSubcategory hasta el final. Haga clic con el botón derecho en ellas para ocultarlas.

  7. Repita este procedimiento con las demás tablas y quite los identificadores, las claves u otros detalles que no usará en este informe.

Vuelva a Hoja1 de Excel con la lista Campos de tabla dinámica para ver la diferencia. El número de tablas se reduce y DimProduct incluye solo los elementos que es más probable que se usen para analizar las ventas.

Crear un informe de Power View

Los informes de tabla dinámica no son el único tipo de informe que se beneficia de un modelo de datos. Si usa el mismo modelo recién generado, puede agregar una hoja de Power View para probar algunos de los diseños que proporciona.

  1. En Excel, haga clic en Insertar > Power View.

    Nota   Si es la primera vez que usa Power View en este equipo, se le solicitará que habilite el complemento e instale Silverlight en primer lugar.

  2. En los campos Power View, haga clic en la flecha junto a la tabla FactSales y luego haga clic en SalesAmount.

  3. Expanda la tabla Geography y haga clic en RegionCountryName.

  4. En la cinta de opciones, haga clic en Mapa.

  5. Aparece un informe de mapa. Arrastre una esquina para cambiar su tamaño. En el mapa, los círculos azules de diferente tamaño indican rendimiento de ventas para los distintos países o regiones.

Optimizar para informes de Power View

Si realiza unos pocos cambios al modelo, obtendrá respuestas más intuitivas a la hora de diseñar un informe de Power View. En esta tarea, agregará las direcciones URL de sitio web para varios fabricantes y luego categorizará dichos datos como una dirección URL web de modo que se muestre como un vínculo.

Como primer paso, agregue direcciones URL al libro.

  1. En Excel, abra una hoja nueva y copie estos valores:

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Dé formato a las celdas como una tabla y luego asigne un nombre a la dirección URL de la tabla.

  2. Cree una relación entre la dirección URL y la tabla que contiene los nombres de los fabricantes, DimProduct:

    1. Haga clic en Datos > Relaciones. Aparecerá el cuadro de diálogo Crear relaciones.

    2. Haga clic en Nuevo.

    3. En Tabla, seleccione DimProduct.

    4. En Columna, seleccione Manufacturer.

    5. En Tabla relacionada, seleccione URL.

    6. En Columna relacionada (Primary), seleccione ManufacturerID.

Para comparar los resultados de antes y después, inicie un nuevo informe de Power View y agregue FactSales | SalesAmount, dimProduct | Manufacturer, and URL | ManufacturerURL a un informe. Observe que las direcciones URL aparecen como texto estático.

La representación de una dirección URL como hipervínculo activo requiere una categorización. Para categorizar una columna, usará Power Pivot.

  1. En Power Pivot, abra la dirección URL.

  2. Seleccione ManufacturerURL.

  3. Haga clic en Avanzadas > Propiedades de informe > categoría de datos: sin categoría.

  4. Haga clic en la flecha abajo.

  5. Seleccione Dirección URL de sitio web.

  6. En Excel, haga clic en Insertar > Power View.

  7. En los campos Power View, seleccione FactSales | SalesAmount, dimProduct | Manufacturer, and URL | ManufacturerURL. Esta vez, las direcciones URL se muestran como hipervínculos reales.

Otras optimizaciones de Power View son la definición de un conjunto de campos predeterminados para cada tabla y la configuración de las propiedades que determinan si las filas de datos repetidos se agregan o se muestran de forma independiente. Vea Configurar un conjunto de campos predeterminado para informes de Power View y Configurar propiedades del comportamiento de las tablas para informes de Power View para obtener más información.

Crear campos calculados

En la segunda tarea, Explorar datos con una tabla dinámica, hizo clic en el campo SalesAmount de la lista de campos de tabla dinámica. Dado que SalesAmount es una columna numérica, se colocó automáticamente en el área Valores de la tabla dinámica. La suma de SalesAmount entonces estaba lista para calcular los importes de ventas de todos los filtros que se deben aplicar. En este caso, sin filtros inicialmente, pero luego CalendarYear, ProductSubcategoryName y BrandName.

Lo que en realidad hizo fue crear un campo calculado implícito y facilitó el análisis de los importes de ventas de la tabla FactSales contra otros campos tales como, la categoría de productos, la región y las fechas. Excel crea campos calculados implícitos cuando arrastra un campo al área Valores o cuando hace clic en un campo numérico, al igual que hizo con SalesAmount. Los campos calculados implícitos son fórmulas que usan funciones de agregación estándar, tales como SUMA, CONTAR y PROMEDIO, creadas automáticamente.

También hay otros tipos de campos calculados. Puede crear campos calculados explícitos en Power Pivot. A diferencia de un campo calculado implícito, que solo se puede usar en la tabla dinámica en la que se crea, los campos calculados explícitos se pueden usar en cualquier tabla dinámica del libro o en cualquier informe que use el modelo de datos como origen de datos. Con los campos calculados explícitos, creados en Power Pivot, puede usar la opción AutoSuma para crear automáticamente campos calculados mediante agregaciones estándar, o bien, puede crear campos propios mediante una fórmula creada con Expresiones de análisis de datos (DAX).

Como puede imaginar, la creación de campos calculados puede ayudarle a analizar los datos de maneras importantes y significativas. Por lo tanto, comenzaremos a aprender cómo crearlos.

Crear campos calculados en Power Pivot es fácil cuando se usa la opción AutoSuma.

  1. En la tabla FactSales, haga clic en la columna Profit.

  2. Haga clic en Cálculos > AutoSuma. Observe que se creó un nuevo campo calculado denominado Sum of Profit en la celda en el área de cálculo directamente debajo de la columna Profit.

  3. En Excel, en Hoja1, en la lista de campos, en FactSales, haga clic en Sum of Profit.

Ya está. Eso es todo lo necesario para crear un campo calculado mediante una agregación estándar en Power Pivot. Como puede observar, en tan solo un par de minutos ha creado un campo calculado SUM of Profit y lo agregó a la tabla dinámica. De este modo, ha facilitado el análisis de las ganancias en función de los filtros aplicados. En este caso, verá el valor Sum of Profit filtrado por las jerarquías de categoría de productos y fechas.

Sin embargo, ¿qué sucede si necesitase unos análisis más detallados, tal como el número de ventas para un canal, un producto o una categoría concreto? Para ello, deberá crear otro campo calculado que cuente el número de filas, una para cada venta de la tabla FactSales, según los filtros aplicados.

  1. En la tabla FactSales, haga clic en la columna SalesKey.

  2. En Cálculos, haga clic en la flecha hacia abajo en AutoSuma > Contar.

  3. Cambie el nombre del nuevo campo calculado haciendo clic en Count of SalesKey en el área de cálculo y, a continuación, en la barra de fórmula, cambie Count of SalesKey por solo Count y presione Entrar. A diferencia de las columnas calculadas, los nombres de los campos calculados se incluyen como parte de la fórmula de DAX.

  4. En Excel, en Hoja1, en la lista de campos, en FactSales, haga clic en Count.

Observe la nueva columna, Count, que se agrega a la tabla dinámica que muestra el número de ventas según los filtros aplicados. Al igual que con el campo calculado Sum of Profit, verá la columna Count filtrada por las jerarquías de categoría de productos y fechas.

Creemos otro campo. Esta vez, creará un campo calculado que calcule el porcentaje de ventas totales para un contexto o filtro concreto. No obstante, a diferencia de los campos calculados anteriores creados mediante AutoSuma, esta vez entrará manualmente una formula.

  1. En la tabla FactSales, en el área Cálculo, haga clic en una celda vacía. Sugerencia: La celda superior izquierda es un excelente punto de partida para comenzar a colocar los campos calculados, ya que facilita su búsqueda. Puede desplazarse por cualquier campo calculado en el área Cálculo.

  2. En la barra de fórmula, escriba y use IntelliSense para crear la fórmula siguiente: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. Presione ENTRAR para aceptarla.

  4. En Excel, en Hoja1, en la lista de campos, en FactSales, haga clic en Percentage of All Products.

  5. En la tabla dinámica, seleccione las columnas Percentage of All Products.

  6. En la pestaña Inicio, haga clic en Número > Porcentaje. Use dos posiciones decimales para formatear cada nueva columna.

Lo que hace este nuevo campo calculado es calcular el porcentaje de las ventas totales para un contexto de filtro dado. En este caso, nuestro contexto de filtro sigue siendo las jerarquías de categoría de productos y fecha. Por ejemplo, puede observar que los equipos como un porcentaje de las ventas totales de producto han aumentado a lo largo de los años.

Crear fórmulas tanto para las columnas calculadas como los campos calculados será relativamente fácil si está familiarizado con la creación de fórmulas de Excel. Ya sea que esté familiarizado o no con las fórmulas de Excel, una excelente forma de conocer los conceptos básicos de las fórmulas DAX es mediante los pasos de las lecciones de Tutorial rápido: aprenda los fundamentos de DAX en 30 minutos.

Guardar el trabajo

Guarde el libro para que pueda usarlo con otros tutoriales o explorar aún más los conceptos.

Pasos siguientes

Aunque puede importar datos de Excel fácilmente, suele ser más rápido y eficaz realizar la importación con el complemento Power Pivot. Puede filtrar los datos que importa al excluir las columnas que no necesita. También puede decidir si desea usar un generador de consultas o un comando de consultas para recuperar los datos. Como paso siguiente, conozca estas soluciones alternativas: Obtener datos de una fuente de distribución de datos en PowerPivot e Importar datos de Analysis Services o PowerPivot.

Los informes de Power View están diseñados para trabajar con modelos de datos similares al que acaba de crear. Puede obtener más información sobre las visualizaciones de datos enriquecidas que Power View aporta a Excel en: Iniciar Power View en Excel 2013 y Power View: explorar, visualizar y presentar los datos.

Intente mejorar el modelo de datos para crear mejores informes de Power View. Para ello, siga el Tutorial: optimizar el modelo de datos para los informes de Power View.

Se aplica a: Excel 2013



¿Le ha sido útil esta información?

No

¿Cómo podemos mejorarlo?

255 caracteres restantes

Para proteger su privacidad, no incluya información de contacto en sus comentarios. Revisar política de privacidad.

¡Gracias por sus comentarios!

Recursos de soporte técnico

Cambiar idioma