¿Por qué nadie te contó esto? Funciones CUBO en Excel

¿Por qué nadie te contó esto? Funciones CUBO en Excel

¿Sabías que puedes acceder a los datos del modelo en Excel sin usar tablas dinámicas? En este tutorial aprenderás cómo usar las funciones CUBO en Excel: MIEMBROCUBO, VALORCUBO y CONJUNTOCUBO, además de los fundamentos de MDX para crear informes totalmente personalizados.

Todo el mundo usa tablas dinámicas, pero a veces se quedan cortas en flexibilidad cuando queremos montar presentaciones muy concretas. Ahí es donde entran las funciones cubo de Excel: te permiten acceder directamente al modelo de datos para hacer cálculos y reports que quedan fuera del alcance de una tabla dinámica. En este artículo veremos cómo usar las tres funciones principales —MIEMBRORANGOCUBO, CONJUNTOCUBO y VALORCUBO— para exprimir tu modelo de datos con una libertad que con las tablas dinámicas es prácticamente imposible.

Punto de partida: un modelo de datos en Power Pivot

Como son funciones que atacan al modelo de datos, lo primero es tener un modelo cargado. En el ejemplo se parte de varias tablas (usuarios, calendario, lugares de trabajo, ubicaciones de la empresa y tipos de evento) y dos medidas: horas y días. Esas tablas se suben a Power Pivot y se relacionan formando un modelo en estrella, con sus dimensiones conectadas a la tabla de hechos. Sobre ese modelo se puede trabajar tanto con tablas dinámicas como con funciones cubo.

CONJUNTOCUBO: extraer los valores de una dimensión

CONJUNTOCUBO permite saber qué valores hay en una dimensión por la que queremos preguntar al modelo. Sus argumentos clave son:

  • Conexión: trabajando con el modelo del propio Excel, siempre es "ThisWorkbookDataModel". Si hubiera modelos externos vinculados (por ejemplo de Power BI), también aparecerían para elegirlos.
  • Expresión del conjunto: una expresión MDX. Al abrir comillas, Excel muestra el árbol del modelo y puedes navegar hasta el elemento que necesitas (por ejemplo, los usuarios). Añadiendo .children al final obtienes todos los valores disponibles de esa dimensión.
  • Título: un nombre para identificar el conjunto en pantalla.
  • Orden y ordenar por: opcionales, para ordenar el conjunto según una medida.

El resultado es una única celda que contiene el cubo, pero que aún no muestra los elementos.

Contar y mostrar los elementos del conjunto

Para trabajar con ese cubo hay dos funciones de apoyo:

  1. RECUENTOCONJUNTOCUBO sobre el conjunto creado devuelve cuántos elementos contiene (en el ejemplo, 15 usuarios).
  2. MIEMBRORANGOCUBO devuelve un elemento concreto del cubo según su posición. Si pasas un 1 te da el primero. Para sacarlos todos de golpe, en lugar de un número fijo se usa SECUENCIA con el recuento de elementos, generando la lista completa del 1 al 15.

Ordenar y hacer rankings con MDX

Aprovechando los parámetros de orden, puedes ordenar el conjunto de forma descendente por una medida (todas las medidas viven en el conjunto Measures), de modo que arriba aparezcan, por ejemplo, los usuarios con más horas.

También puedes filtrar con rankings dentro de la propia expresión MDX:

  • TopCount(...; 5; Measures.[Horas]) te quedas con los 5 mejores por horas.
  • BottomCount(...; 3; Measures.[Horas]) te quedas con los que menos horas han hecho.

Como todo va entre comillas, es texto MDX: cambiando el número ajustas automáticamente cuántos elementos recupera el ranking.

VALORCUBO: añadir el valor de una medida

Una lista de elementos suele necesitar una cifra al lado: el resultado de una medida aplicado a cada uno. Eso lo hace VALORCUBO, que recibe la conexión, la medida y todos los contextos que filtran el cálculo:

  • Si solo pasas la medida (Measures.[Horas]) sin contexto, devuelve el total (en el ejemplo, 25.600 horas).
  • Añadiendo como contexto cada usuario del conjunto, devuelve las horas de esa persona concreta.

Combinar con funciones dinámicas

Las funciones cubo se llevan muy bien con los arrays dinámicos. Con MAP puedes recorrer la lista de usuarios y aplicar VALORCUBO a cada elemento usando una LAMBDA: el parámetro de la lambda sustituye el valor fijo por el valor de cada iteración. Así, si amplías el conjunto (de 3 a 7 elementos, por ejemplo), el resultado se expande solo.

Trabajar con segmentadores (slicers y timelines)

Los segmentadores también se integran con las funciones cubo, y es aún más sencillo: el propio segmentador ya hace de conjunto. En CONJUNTOCUBO, en lugar de una expresión MDX, escribes directamente el nombre del segmentador:

  • Los segmentadores de datos se llaman Segmentación de datos... seguidos del campo.
  • Los segmentadores de fecha se llaman Timeline... seguidos del campo.

Sobre ese cubo, RECUENTOCONJUNTOCUBO devuelve el número de elementos seleccionados (no los totales), y MIEMBRORANGOCUBO con SECUENCIA extrae los valores marcados. Con un timeline filtrando por meses, el recuento se mide por día (uno por fila de la tabla calendario): tres meses dan 92 días, un mes da 30. Esto es muy útil incluso como lista de apoyo para alimentar otras funciones con los valores seleccionados.

Montar un report con VALORCUBO y MAKEARRAY

Para un report en cuadrícula se generan dos contextos: filas y columnas. Por ejemplo, un cubo con los periodos del calendario (año-mes) colocados como cabeceras de columna mediante MIEMBRORANGOCUBO y SECUENCIA, y los usuarios como filas.

El cruce se rellena con MAKEARRAY, indicando el número de filas (usuarios) y de columnas (periodos), y una LAMBDA(fila; columna; ...). Dentro se usa VALORCUBO con la medida Horas y dos contextos obtenidos con INDICE sobre las cabeceras (fijando fila o columna según corresponda con #). El resultado se adapta solo al número de empleados y periodos.

Lo mejor es que puedes seguir añadiendo contextos: arrastrando un segmentador de tipos de evento al report, VALORCUBO recalcula según lo seleccionado (vacaciones, formación, baja médica, etc.). Y al ser celdas normales, admiten formato condicional, subtotales y cálculos manuales.

Conclusión

Las funciones cubo abren un mundo de posibilidades que las tablas dinámicas, por su estructura más rígida, no permiten. Con CONJUNTOCUBO defines conjuntos, con MIEMBRORANGOCUBO los recorres y con VALORCUBO calculas medidas aplicando los contextos que quieras, combinándolo todo con funciones dinámicas como SECUENCIA, MAP o MAKEARRAY. Teniendo un modelo de datos único —ideal para la integridad de la información y para mezclar datos de varios orígenes— ganas una flexibilidad para estructurar tus reports que merece la pena explorar.

Más contenido de Excel en InflueXcel