Agregaciones en fórmulas
En esta tema se presentan las agregaciones y se proporciona información general de los tipos de agregaciones posibles con PowerPivot para Excel. PowerPivot para Excel contiene estas herramientas para generar agregaciones:
Puede compilar tablas dinámicas y gráficos dinámicos que se basen en datos PowerPivot. Las tablas dinámicas de Excel son una herramienta muy usada para agrupar y resumir los datos de las hojas de cálculo. PowerPivot se integra con las características de tabla dinámica de Excel y ofrece muchas mejoras.
Puede utilizar el lenguaje de fórmulas DAX para diseñar agregaciones personalizadas. DAX se puede utilizar para crear columnas calculadas en tablas de PowerPivot y para crear medidas en las tablas dinámicas y los gráficos dinámicos.
La última sección de este tema proporciona vínculos a información más detallada sobre cómo generar agregaciones.
Introducción a las agregaciones
Las agregaciones son una manera de contraer, resumir o agrupar datos. Al empezar con los datos sin formato de tablas u otros orígenes de datos, son a menudo planos, lo que significa que hay muchos detalles pero que no se han organizado o se han agrupado de ninguna forma. Esta falta de resúmenes o de estructura podría dificultar el detectar los modelos de los datos. Por consiguiente, una parte importante del trabajo del analista es definir agregaciones que simplifiquen o resuman patrones como respuesta a una cuestión empresarial concreta.
Elegir grupos para la agregación
Al agregar datos, está agrupando estos datos por atributos como producto, precio, región o fecha y, a continuación, define una fórmula válida para todos los datos en el grupo. Por ejemplo, al crear un total para un año, está creando una agregación. Si posteriormente, crea una proporción para este año según el año anterior y la presenta como porcentaje, será un tipo de agregación diferente.
La decisión sobre cómo agrupar los datos depende de las cuestiones empresariales. Por ejemplo, se pueden plantear las siguientes preguntas paras las agregaciones:
Recuentos ¿Cuántas transacciones se efectuaron en un mes?
Promedios ¿Cuál fue el promedio mensual de ventas por comercial?
Valores mínimos y máximos ¿Cuáles fueron las cinco principales zonas de ventas en términos de unidades vendidas?
Para crear un cálculo que responda a estas preguntas, debe disponer de datos detallados que contengan las cifras de recuento o suma, y esos datos numéricos deben estar relacionados de cierta forma con los grupos que utilizará para organizar los resultados.
Si los datos aún no contienen valores que pueda utilizar para agrupar, como una categoría de producto o el nombre de la región geográfica donde se encuentra el almacén, podría ser conveniente introducir grupos para los datos agregando categorías. Al integrar los grupos en Excel, debe escribir o seleccionar de forma manua los grupos que desea utilizar de entre las columnas de la hoja de cálculo.
Sin embargo, en un sistema relacional, las jerarquías como las categorías de productos se suelen almacenar en una tabla diferente a la de hechos o valores. Por lo general, la tabla de categoría se vincula a los datos de hecho a través de algún tipo de clave. Por ejemplo, suponga que descubre que sus datos contienen identificadores de producto pero no los nombres de producto ni sus categorías. Para agregar la categoría a una hoja de cálculo de Excel plana, tendría que copiarla en la columna que contenía los nombres de categoría. Sin embargo, en un libro PowerPivot, puede importar la tabla de categorías de producto a su libro, crear una relación entre la tabla con los datos de número y la lista de categorías de producto y, a continuación, usar las categorías para agrupar los datos. Para obtener más información, vea Relaciones entre tablas.
Elegir una función para la agregación
Después de haber identificado y agregado las agrupaciones que se van a utilizar, debe decidir qué funciones matemáticas desea utilizar para las agregaciones. A menudo, la palabra agregación se utiliza como un sinónimo de las operaciones matemáticas o estadísticas que se utilizan en las agregaciones, como sumas, promedios, mínimos o recuentos. Sin embargo, PowerPivot para Excel le permite crear fórmulas personalizadas para la agregación, además de las agregaciones estándar que se encuentran en Excel.
Por ejemplo, partiendo del mismo conjunto de valores y agrupaciones que se utilizaron en los ejemplos anteriores, podría crear agregaciones personalizadas que respondan a las siguientes preguntas:
Recuentos filtrados ¿Cuántas transacciones se realizaron en un mes, sin contar la ventana de mantenimiento de fin de mes?
Proporciones utilizando los promedios en periodos de tiempo ¿Cuál ha sido el crecimiento o descenso porcentual en ventas con respecto al mismo periodo del año anterior?
Valores mínimos y máximos agrupados ¿Cuáles fueron las principales zonas de venta para cada categoría de producto o para cada promoción comercial?
Agregar agregaciones a fórmulas y tablas dinámicas
Cuando se tiene una idea general de cómo deberían estar agrupados los datos para que sean relevantes y de los valores con los que se desea trabajar, puede decidir si compilar una tabla dinámica o crear cálculos en una tabla. PowerPivot para Excel amplía y mejora la capacidad nativa de Excel para crear agregaciones como sumas, recuentos o promedios. Las agregaciones personalizadas en PowerPivot se pueden crear en la ventana de PowerPivot o en el área de tabla dinámica de Excel.
En una columna calculada, se pueden crear agregaciones que tengan en cuenta el contexto de la fila actual para recuperar filas relacionadas de otra tabla y, a continuación, sumar, contar o promediar los valores de las filas relacionadas.
En una medida, se pueden crear agregaciones dinámicas que usen filtros definidos dentro de la fórmula y filtros impuestos por el diseño de la tabla dinámica y la selección de segmentaciones de datos, encabezados de columna y encabezados de fila.
Para obtener más información, vea Generar fórmulas para cálculos.
Agregar agrupaciones a una tabla dinámica
Al diseñar una tabla dinámica, los campos que representan agrupaciones, categorías o jerarquías se arrastran a la sección de columnas y filas de la tabla dinámica para agrupar los datos. A continuación, los campos que contienen valores numéricos se arrastran al área de valores para que se puedan contar, promediar o sumar.
Si agrega categorías a una tabla dinámica pero los datos de categoría no están relacionados con los datos de hecho, podría obtener un error o resultados extraños. Normalmente, PowerPivot para Excel intentarán corregir el problema, para ello, detectará y sugerirá relaciones automáticamente. Para obtener más información, vea Trabajar con relaciones en tablas dinámicas.
También puede arrastrar los campos hasta las segmentaciones de datos, para seleccionar ciertos grupos de datos y poder verlos. Las segmentaciones de datos son una característica nueva de Excel y de PowerPivot para Excel que le permiten agrupar, ordenar y filtrar de forma interactiva los resultados en una tabla dinámica.
Trabajar con agrupaciones en una fórmula
También puede utilizar agrupaciones y categorías para agregar los datos que se almacenan en tablas si crea relaciones entre las tablas y posteriormente fórmulas que aprovechen esas relaciones para buscar valores relacionados.
Es decir, si se desea crear una fórmula que agrupe los valores según una categoría, primero se debe usar una relación para conectar la tablas que contiene los datos detallados y las tablas que contienen las categorías, y a continuación se genera la fórmula.
Para obtener más información acerca de cómo generar fórmulas que utilicen búsquedas, vea Relaciones y búsquedas en las fórmulas.
Utilizar filtros en agregaciones
Una nueva característica de PowerPivot es la capacidad de aplicar filtros a las columnas y tablas de datos, no solo en la interfaz de usuario y dentro de una tabla dinámica o gráfico, sino también en las mismas fórmulas que utiliza para calcular las agregaciones. Los filtros se pueden utilizar en fórmulas tanto en columnas calculadas como en las medidas.
Por ejemplo, en las nuevas funciones de agregación de DAX, en lugar de especificar valores para hacer sumas o recuentos, puede especificar una tabla completa como el argumento. Si no ha aplicado filtros a esa tabla, la función de agregación se articulará de acuerdo a todos los valores en la columna especificada de la tabla. Sin embargo, en DAX puede crear un filtro dinámico o estático en la tabla, de forma que la agregación se articula de acuerdo a un subconjunto diferente de datos en función de la condición de filtro y el contexto válido en esos momentos.
Al combinar condiciones y filtros en fórmulas puede crear agregaciones que van cambiando en función de los valores que se proporcionan en las fórmulas o en función de la selección de los encabezados de fila y encabezados de columna en una tabla dinámica.
Para obtener más información, vea Filtrar datos en las fórmulas.
Funciones de agregación en Excel y en DAX
En la siguiente tabla se incluye una lista de algunas de las funciones de agregación estándar que ofrece Excel y proporciona los vínculos a la implementación de estas funciones en PowerPivot para Excel. La versión DAX de estas funciones tiene un comportamiento muy parecido al de la versión de Excel, con algunas diferencias menores de sintaxis y tratamiento de determinados tipos de datos.
Funciones de agregación estándar
Función |
Uso |
Devuelve el promedio (la media aritmética) de todos los números de una columna. |
|
Devuelve el promedio (media aritmética) de todos los valores de una columna. Trata texto y valores no numéricos. |
|
Cuenta el número de valores numéricos en una columna. |
|
Cuenta el número de valores de una columna que no están vacías. |
|
Devuelve el mayor valor numérico de una columna. |
|
Devuelve el valor más grande de un conjunto de expresiones evaluado sobre una tabla. |
|
Devuelve el menor valor numérico de una columna. |
|
Devuelve el valor menor de un conjunto de expresiones evaluado sobre una tabla. |
|
Suma todos los números de una columna. |
Funciones de agregación de DAX
DAX incluye funciones de agregación con las que puede especificar una tabla donde se efectuará la agregación. Por lo tanto, en lugar de calcular simplemente la suma o el promedio de los valores de una columna, estas funciones permiten crear una expresión que define dinámicamente los datos que se agregarán.
En la tabla siguiente se enumeran las funciones de agregación disponibles en DAX.
Función |
Uso |
Calcula el promedio de un conjunto de expresiones evaluado en una tabla. |
|
Cuenta el promedio de un conjunto de expresiones evaluado en una tabla. |
|
Cuenta el número de valores en blanco en una columna. |
|
Cuenta el número total de filas de una tabla. |
|
Cuenta el número de filas devuelto de una función de tabla anidada, como una función de filtro. |
|
Devuelve la suma de un conjunto de expresiones evaluadas sobre una tabla. |
Diferencias entre las funciones de agregación de DAX y Excel
Aunque estas funciones tienen los mismos nombres que sus homólogos de Excel, utilizan el motor VertiPaq de PowerPivot y se han reescrito para poder usarse con tablas y columnas. Las fórmulas de DAX no se pueden usar en un libro de Excel y viceversa. Solo se pueden utilizar en la ventana de PowerPivot y en las tablas dinámicas que están basadas en datos de PowerPivot. Además, aunque las funciones tengan nombres idénticos en inglés, el comportamiento puede ser algo distinto. Para obtener más información, vea los temas individuales de referencia de funciones.
La manera en que se evalúan las columnas en una agregación también es diferente del modo en que Excel administra las agregaciones. Esto puede ilustrarse mejor con un ejemplo.
Imagine que desea obtener una suma de los valores de la columna Amount de la tabla Sales y, por tanto, crea la siguiente fórmula:
=SUM('Sales'[Amount])
En el caso más simple, la función recibe los valores en una única columna sin filtrar y el resultado es igual que en Excel, que siempre suma solo los valores de la columna, Amount. Sin embargo, en PowerPivot, la fórmula se interpreta como "Obtener el valor de Amount para cada fila de la tabla Sales y, a continuación, sumar esos valores individuales". PowerPivot evalúa cada fila en la que se realiza la agregación y calcula un solo valor escalar. Después, realiza una agregación con esos valores. Por consiguiente, el resultado de una fórmula puede ser diferente si se han aplicado filtros a una tabla o si los valores se calculan según otras agregaciones que se podrían filtrar. Para obtener más información, vea Contexto de las fórmulas DAX.
Funciones de inteligencia de tiempo de DAX
Además de las nuevas funciones de agregación de tablas descritas en la sección anterior, DAX tiene funciones de agregación que usan las fechas y horas que se especifiquen, para proporcionar inteligencia de tiempo integrada. Estas funciones utilizan intervalos de fechas para obtener valores relacionados y agregar los valores. También puede comparar valores en los intervalos de fechas.
La siguiente tabla incorpora una lista de las funciones de inteligencia de tiempo que se pueden utilizar para la agregación.
Función |
Uso |
Calcula un valor al final del calendario del período dado. |
|
Calcula un valor al final del calendario del período antes del período dado. |
|
Calcula un valor sobre el intervalo que se inicia el primer día del periodo y finaliza en la última fecha de la columna de fecha especificada. |
Las otras funciones de la sección de funciones de inteligencia de tiempo (Funciones de inteligencia de tiempo (DAX)) se pueden utilizar para recuperar fechas o intervalos de fechas personalizados que utilizar en una agregación. Por ejemplo, puede utilizar la función DATESINPERIOD para devolver un intervalo de fechas y usar ese conjunto de fechas como argumento de otra función que calcule una agregación personalizada solo para esas fechas.
Vea también
Conceptos
Otros recursos
Key Concepts