Funciones GETPIVOTDATA vs CUBO

Hemos podido crear tablas dinámicas en Excel desde mediados de la década de 1990. Desde entonces, la gente ha estado preguntando cómo extraer datos de tablas dinámicas mediante fórmulas. Por muy buenas que sean las tablas dinámicas para el análisis, no siempre son las mejores para la presentación, razón por la cual extraer datos mediante una fórmula es tan útil. Actualmente tenemos 3 opciones para esto, vinculación de celdas estándar, funciones GETPIVOTDATA y CUBE.

En esta publicación, compararemos estos tres métodos. Esta no es una mirada en profundidad a ningún enfoque en particular, sino que pretende resaltar las diferencias para que pueda tomar las mejores decisiones para su escenario.

Tabla de contenido

• Datos de ejemplo
• Fórmulas estándar
• Extraer datos de la tabla dinámica
• Cambiando datos
• Función OBTENER DATOS VIVO
• Activar/desactivar GETPIVOTDATA
• Extraer datos de la tabla dinámica
• Cambiando datos
• Cambiar la función GETPIVOTDATA
• Funciones del CUBO
• Crear una tabla dinámica con el modelo de datos
• Convertir tabla dinámica en fórmulas
• Cambiando datos
• Cambiar las funciones del CUBO
• ¿Cuál es el mejor?

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0053 GETPIVOTDATA vs Funciones CUBE – Start.xlsx

Tener acceso

Datos de ejemplo

Todos los ejemplos de esta publicación utilizan el mismo conjunto de datos inicial. Esto está disponible en el archivo de descarga.

A partir de ese conjunto de datos, creé una tabla dinámica:

Tenga en cuenta que el proceso de creación de la tabla dinámica requiere un paso adicional cuando se utilizan las funciones CUBE. Te explicaré esto en la sección correspondiente.

Fórmulas estándar

Las tablas dinámicas existen en la cuadrícula de Excel y cada celda de la cuadrícula tiene su propia referencia de celda. Por lo tanto, el método más sencillo para obtener valores de una tabla dinámica es utilizar referencias de celda estándar dentro de una fórmula.

Extraer datos de la tabla dinámica

Como ya se indicó, podemos vincular cualquier elemento de la tabla dinámica utilizando la referencia de celda.

En la captura de pantalla anterior, la fórmula en la celda M3 es:

=G3

Esta fórmula extrae el valor de 36 de la tabla dinámica, que es la suma de valores de Carolyn en Inglaterra.

Cambiando datos

Ahora cambiemos nuestros datos de origen. Para este escenario, supongamos que Christian no está en Brasil, sino en Finlandia. Entonces, en nuestra fuente, cambie las celdas B3 y B7 de Brasil a Finlandia, luego haga clic en Actualizar datos todos .

Mira la captura de pantalla de arriba. Nuestra referencia de celda a G3 se ha mantenido. Pero el punto de datos en esa celda ha cambiado, por lo que ahora apuntamos a una celda vacía. La suma de valores de Carolyn en Inglaterra está en la celda F3, en lugar de en G3.

La principal lección de la vinculación de celdas es que las referencias de celda no cambian cuando cambia la posición de los datos subyacentes.
Nota:
Podríamos usar una fórmula como ÍNDICE / COINCIDIR / COINCIDIR para realizar una búsqueda bidimensional y asegurarnos de extraer los datos correctos para Carolyn en Inglaterra. Sin embargo, esto anula el beneficio de utilizar una tabla dinámica.

Para evitar este problema, podemos utilizar uno de los otros métodos de extracción.

Función OBTENER DATOS VIVO

GETPIVOTDATA es la función en la que la mayoría de nosotros pensamos cuando necesitamos extraer valores de una tabla dinámica.

Activar/desactivar GETPIVOTDATA

Podemos configurar Excel para que cree automáticamente la función GETPIVOTDATA al hacer clic en una celda de la tabla dinámica en una fórmula. Para hacer esto, seleccione cualquier celda en una tabla dinámica y haga clic en Opciones de análisis de tabla dinámica Generar GetPivotData para asegurarse de que la opción esté activada.

Ahora, cada vez que nos vinculamos a una celda de la tabla dinámica, la fórmula GETPIVOTDATA se crea automáticamente.

Extraer datos de la tabla dinámica

Cambiemos nuestros datos de origen a su estado original y actualicemos la tabla dinámica. A continuación, usemos GETPIVOTDATA para extraer la celda G3 de la tabla dinámica.

En la captura de pantalla anterior, la celda M3 contiene la fórmula:

=GETPIVOTDATA("Valor",$E$1,"Nombre","Carolyn","País","Inglaterra")

Al hacer clic en la celda G3, automáticamente se nos creó la función GETPIVOTDATA. Los elementos individuales se desglosan de la siguiente manera

• =GETPIVOTDATA – el nombre de la función
• “Valor” : el nombre del campo de valor
• $E$1 : la celda superior izquierda de la tabla dinámica
• “Nombre” : el nombre del primer campo dinámico
• “Carolyn” – el elemento específico del primer Pivot Field
• “País” : el nombre del segundo campo pivote
• “Inglaterra” – el elemento específico del segundo campo pivote

Si hay más campos, podemos continuar agregándolos en pares, tal como se indicó anteriormente con “País” e “Inglaterra”.

Cambiando datos

Ahora cambiemos nuevamente nuestros datos de origen de Brasil a Finlandia y hagamos clic en Actualizar datos todos .

Si bien la celda que contiene el punto de datos ha cambiado de G4 a F4, a través de GETPIVOTDATA, no nos vinculamos a una celda. En cambio, nos vinculamos a la suma de valor de Carolyn en Inglaterra; por lo tanto, el resultado devuelto se mantiene.

Cambiar la función GETPIVOTDATA

Podemos hacer que GETPIVOTDATA sea más flexible usando celdas de la hoja de trabajo en lugar de nombres de campos codificados.

Vincular campos dinámicos a celdas

Vinculemos un elemento del campo País a una celda.

En la captura de pantalla anterior, Inglaterra se ingresó en la celda M5. GETPIVOTDATA ha cambiado a lo siguiente:

=GETPIVOTDATA("Valor",$E$1,"Nombre","Carolyn","País", M5 )

La fórmula hace referencia a la celda M5, que contiene el elemento de campo.

Podemos cambiar esto a España, o EE.UU., etc., y el GETPIVOTDATA se recalculará automáticamente.

Vincular campos de datos a celdas

Entonces, ¿qué pasa si vinculamos el campo de datos a una celda?

¡Oh! No funciona. La celda M7 ahora contiene el texto Valor, que es el nombre del campo en la sección de valor de la tabla dinámica.

La fórmula en la celda M3 es:

=GETPIVOTDATA( M7 ,$E$1,"Nombre","Carolyn","País",M5)

Pero no todo está perdido. Existe una solución extraña en la que podemos agregar una cadena de texto vacía al principio o al final de la referencia de celda, y funcionará.

Si cambiamos la fórmula de la celda M3 a la siguiente, funcionará:

=GETPIVOTDATA(M7 "" ,$E$1,"Nombre","Carolyn","País",M5)

Pruébelo usted mismo; ¡funciona!

Funciones del CUBO

Comencemos esta sección volviendo a nuestro conjunto de datos original.

Una tabla dinámica puede mostrar información almacenada en Pivot Cache o en el modelo de datos. Estos son los dos métodos para almacenar los datos de la tabla dinámica. En la creación, Pivot Cache se usa de forma predeterminada, pero al hacer clic en una sola casilla de verificación, podemos usar el modelo de datos en su lugar.

El Modelo de Datos es un motor más moderno y eficiente para el manejo de datos. Por lo tanto, deberíamos intentar utilizar el modelo de datos siempre que podamos.

Las funciones CUBE son un grupo de funciones que pueden extraer datos del modelo de datos. Dado que las tablas dinámicas y las fórmulas pueden usar la misma fuente, las funciones CUBE crean el equivalente a extraer valores de una tabla dinámica.

Hay 7 funciones de CUBO, pero para esta publicación solo veremos dos: CUBEMEMBER y CUBEVALUE.

Crear una tabla dinámica con el modelo de datos

Si pensamos utilizar fórmulas CUBE, debemos crear una tabla dinámica de la forma correcta.

Seleccione una celda en el conjunto de datos, luego haga clic en Insertar tabla dinámica

En la ventana Tabla dinámica desde tabla o rango, seleccione la opción Agregar estos datos al modelo de datos y luego haga clic en Aceptar.

Eso es todo; sólo hay un clic adicional. La tabla dinámica se creará utilizando el modelo de datos en lugar de la caché dinámica.

Podemos agregar campos dinámicos y campos de valor de la forma habitual.

Convertir tabla dinámica en fórmulas

Una tabla dinámica creada a partir de un modelo de datos se puede convertir en fórmulas CUBE.

Seleccione una celda en la tabla dinámica y luego haga clic en Tabla dinámica Analizar Herramientas OLAP Convertir a fórmulas .

Si su tabla dinámica tiene un campo en la sección Filtros, aparecerá el cuadro de diálogo Convertir a fórmulas. No hemos utilizado la sección Filtros para este ejemplo, por lo que este cuadro no aparecerá. Si es así para su escenario, seleccione la opción que desee y haga clic en Convertir .

La tabla dinámica ahora se convertirá en fórmulas CUBE. ¡Ya no existe la tabla dinámica!

En breve cubriremos cómo cambiar manualmente estas fórmulas.

Nota:
Si bien las fórmulas se crearon mediante la conversión de una tabla dinámica, si aprendemos la sintaxis requerida para las fórmulas CUBE, podemos escribirlas nosotros mismos como otras funciones estándar.

Cambiando datos

Ahora cambiemos nuestros datos de origen nuevamente, de Brasil a Finlandia, luego hagamos clic en Actualizar datos .

Como ya no tenemos tabla dinámica, es el resultado de las fórmulas las que se actualizan. Dado que ya no hay un elemento de campo llamado Brasil en nuestro conjunto de datos, ahora se muestra como #N/A.

Pruébate a ti mismo que esto funciona. Cambie un número en los datos de origen y haga clic en Actualizar datos todos ; Los resultados de la fórmula se actualizarán.

Cambiar las funciones del CUBO

El proceso de transformación para cambiar la tabla dinámica a fórmulas utiliza dos funciones diferentes: CUBEMEMBER y CUBEVALUE. Echaremos un vistazo a cada uno por separado.

MIEMBRO DEL CUBIO

La fórmula CUBEMEMBER se utiliza para las dimensiones/campos.

La fórmula en la celda G2 es:

=MEMEROCUBO("EsteModeloDeDatosDeLibroDeTrabajo","[Tabla1].[País].[Inglaterra]")

Esta fórmula se desglosa de la siguiente manera:

• = CUBEMEMBER – el nombre de la función
• "ThisWorkbookDataModel" : se refiere al nombre del modelo de datos que se utiliza. Al utilizar esta metodología, el valor siempre será “ThisWorkbookDataModel”
• “[Tabla1].[País].[Inglaterra]” : esta es una declaración MDX que hace referencia al elemento de campo Inglaterra en el campo País de una tabla llamada Tabla1.

La fórmula en la celda E3 es:

=MIEMBROCUBO("EsteModeloDeDatosLibroDeTrabajo","[Tabla1].[Nombre].[Carolyn]")

Observe que esta es una estructura muy similar a la celda G2 que vimos arriba. La diferencia es que nos referimos al elemento de campo de Carolyn en el campo Nombre de una tabla llamada Tabla1.

La fórmula de la celda E1 difiere ligeramente:

=MEMEROCUBO("ThisWorkbookDataModel","[Medidas].[Suma de valor]")

La celda E1 no es un campo/dimensión por el que filtrar, sino el cálculo que se aplicará. Por tanto, tiene un formato ligeramente diferente.

“[Medidas].[Suma de valor]” : esta es la sintaxis para hacer referencia a una medida implícita creada en una tabla dinámica. El primer elemento entre corchetes siempre será Medidas, con el nombre del cálculo en el segundo conjunto de corchetes.

Para las funciones CUBO, cualquier cosa entre comillas dobles es un valor de texto y puede vincularse a una celda o concatenarse con otras cadenas de texto.

VALOR DEL CUBO

La fórmula CUBEVALUE recupera valores del modelo de datos según el contexto de filtro creado por las funciones CUBEMEMBER a las que está vinculado.

La fórmula en la celda G3 es:

=VALORCUBO("Estemodelodedatosdellibro",$E$1,$E3,G$2)

Esta fórmula se desglosa de la siguiente manera:

• =VALORCUBO – el nombre de la función
• “ThisWorkbookDataModel” : se refiere al nombre del modelo de datos que se utiliza
• $E$1,$E3,G$2 : todos estos son enlaces de celda a las funciones CUBEMEMBER relevantes anteriores

La función CUBEMEMBER proporciona los campos a usar y CUBEVALUE recupera el valor para ese contexto de filtro.
Nota:
Es posible cambiar la referencia al cálculo a realizar, por ejemplo, cambiando de [Medida].[Suma de Valor] a [Medida].[Valor Máximo]. Sin embargo, hay una peculiaridad a tener en cuenta.

A menos que se escriban medidas explícitas utilizando DAX en PowerPivot, todos los cálculos implícitos deben crearse dentro de una tabla dinámica antes de hacer referencia a ella en una fórmula. Si quisiéramos usar [Medidas]. [Valor máximo] primero necesitaríamos crear una tabla dinámica, con ese cálculo incluido.

Una vez creada, esa medida/cálculo implícito está disponible para que lo utilicen las funciones CUBE.

¿Cuál es el mejor?

¿Cuál es la mejor opción a utilizar? Desafortunadamente,…. Eso depende.

Lo mejor es relativo a su escenario. Dado que el modelo de datos es más eficiente en el manejo de datos que Pivot Cache, ese siempre sería mi método preferido. Además, existe el beneficio adicional de que este es un trampolín para usar la herramienta Power Pivot en Excel.

Esta fue una introducción a las diferencias entre las funciones GETPIVOTDATA y CUBE para extraer datos de una tabla dinámica. Pero no termine su viaje aquí, hay mucho más que aprender:

• Consejos Trucos para escribir fórmulas CUBEVALUE – Campus de Excel: https://www.excelcampus.com/cubevalue-formulas/
• Fórmulas cúbicas: las mejores fórmulas de Excel que no estás usando - Macrodinaria: https://macrordinary.ca/2020/08/19/cube-formulas-the-best-excel-formulas-youre-not-using/
• Introducción a las funciones de Cube para PowerPivot – BI Gorilla: https://gorilla.bi/excel/cube-functions/
• Cómo utilizar la tabla dinámica – GetPivotData – Contexturas: https://www.contextures.com/xlpivot06.html
• Función GETPIVOTDATA de Excel: Excel Jet: https://exceljet.net/excel-functions/excel-getpivotdata-function

---