Beneficios de Pivot por modelo de datos

Al crear una tabla dinámica en Excel, en el primer cuadro de diálogo, donde se nos pide que establezcamos el rango inicial y elijamos un lugar para insertar la tabla dinámica, hay una casilla de verificación discreta pero muy importante a continuación: Agregue estos datos al modelo de datos (Agregue estos datos al modelo de datos) y, un poco más arriba, el interruptor Usar el modelo de datos de este libro (Use el modelo de datos de este libro de trabajo):

Desafortunadamente, muchos usuarios que han estado familiarizados con las tablas dinámicas durante mucho tiempo y las usan con éxito en su trabajo, a veces no entienden realmente el significado de estas opciones y nunca las usan. Y en vano Después de todo, crear una tabla dinámica para el modelo de datos nos brinda varias ventajas muy importantes en comparación con la tabla dinámica clásica de Excel.

Sin embargo, antes de considerar estos "bollos" de cerca, primero comprendamos qué es, de hecho, este modelo de datos.

¿Qué es un modelo de datos?

Modelo de datos (abreviado como MD o DM = modelo de datos) es un área especial dentro de un archivo de Excel donde puede almacenar datos tabulares: una o más tablas vinculadas, si lo desea, entre sí. De hecho, esta es una pequeña base de datos (cubo OLAP) incrustada dentro de un libro de Excel. En comparación con el almacenamiento clásico de datos en forma de tablas regulares (o inteligentes) en hojas de Excel, el modelo de datos tiene varias ventajas significativas:

• Las mesas pueden ser de hasta 2 mil millones de líneas, y una hoja de Excel puede contener un poco más de 1 millón.
• A pesar del tamaño gigantesco, el procesamiento de dichas tablas (filtrado, clasificación, cálculos en ellas, construcción de resumen, etc.) se realiza muy rapido Mucho más rápido que el propio Excel.
• Con los datos en el Modelo, puede realizar cálculos adicionales (si lo desea, muy complejos) usando lenguaje DAX incorporado.
  
• Toda la información cargada en el modelo de datos es muy fuertemente comprimido utilizando un archivador incorporado especial y aumenta bastante moderadamente el tamaño del archivo de Excel original.
  

El modelo es administrado y calculado por un complemento especial integrado en Microsoft Excel: PowerPivotsobre el que ya he escrito. Para habilitarlo, en la pestaña revelador clic complementos COM (Desarrollador: complementos COM) y marque la casilla correspondiente:

Si pestañas revelador (Desarrollador)no puede verlo en la cinta, puede encenderlo Archivo – Opciones – Configuración de cinta (Archivo — Opciones — Personalizar cinta). Si en la ventana que se muestra arriba en la lista de complementos COM no tiene Power Pivot, entonces no está incluido en su versión de Microsoft Office 🙁

En la pestaña de Power Pivot que aparece, habrá un gran botón verde claro Administración (Gestionar), al hacer clic en el cual se abrirá la ventana de Power Pivot en la parte superior de Excel, donde veremos el contenido del Modelo de datos del libro actual:

Una nota importante en el camino: un libro de Excel solo puede contener un modelo de datos.

Cargue tablas en el modelo de datos

Para cargar datos en el modelo, primero convertimos la tabla en un atajo de teclado dinámico "inteligente" Ctrl+T y dale un nombre descriptivo en la pestaña Constructor (Diseño). Este es un paso obligatorio.

A continuación, puede utilizar cualquiera de los tres métodos para elegir:

• presiona el botón Agregar al modelo (Agregar al modelo de datos) de la pestaña. PowerPivot de la pestaña. Inicio (Home).
  
• Eligiendo equipos Insertar – Tabla dinámica (Insertar: tabla dinámica) y active la casilla de verificación Agregue estos datos al modelo de datos (Agregue estos datos al modelo de datos). En este caso, de acuerdo con los datos cargados en el Modelo, también se construye inmediatamente una tabla dinámica.
  
• En la pestaña Avanzado Datos (Fecha) haga clic en el botón Desde tabla/rango (De tabla/rango)para cargar nuestra tabla en el editor de Power Query. Esta ruta es la más larga, pero, si lo desea, aquí puede realizar limpiezas de datos adicionales, edición y todo tipo de transformaciones, en las que Power Query es muy fuerte.

  Luego, los datos combinados se cargan en el Modelo mediante el comando Inicio — Cerrar y cargar — Cerrar y cargar en… (Inicio — Cerrar y cargar — Cerrar y cargar a…). En la ventana que se abre selecciona la opción Solo crea una conexión (Solo crear conexión) y, lo más importante, poner una marca Agregue estos datos al modelo de datos (Agregue estos datos al modelo de datos).
  

Construimos un resumen del Modelo de Datos

Para crear un modelo de datos de resumen, puede utilizar cualquiera de los tres enfoques:

• presiona el botón tabla resumen (Tabla dinámica) en la ventana de Power Pivot.
• Seleccionar comandos en Excel Insertar – Tabla dinámica y cambiar al modo Usar el modelo de datos de este libro (Insertar — Tabla dinámica — Usar el modelo de datos de este libro).
• Eligiendo equipos Insertar – Tabla dinámica (Insertar: tabla dinámica) y active la casilla de verificación Agregue estos datos al modelo de datos (Agregue estos datos al modelo de datos). La tabla "inteligente" actual se cargará en el modelo y se creará una tabla de resumen para todo el modelo.

Ahora que hemos descubierto cómo cargar datos en el modelo de datos y crear un resumen sobre ellos, exploremos los beneficios y ventajas que esto nos brinda.

Beneficio 1: Relaciones entre tablas sin usar fórmulas

Un resumen regular solo se puede crear utilizando datos de una tabla de origen. Si tiene varios de ellos, por ejemplo, ventas, lista de precios, directorio de clientes, registro de contratos, etc., primero deberá recopilar datos de todas las tablas en una usando funciones como BUSCARV (VLOOKUP), ÍNDICE (ÍNDICE), MÁS EXPUESTO (JUEGO), VERANO (SUMAR.SI.SI) y similares. Esto es largo, tedioso y convierte su Excel en un "pensamiento" con una gran cantidad de datos.

En el caso de un resumen del Modelo de Datos, todo es mucho más sencillo. Basta con configurar las relaciones entre las tablas una vez en la ventana de Power Pivot, y listo. Para ello, en la pestaña PowerPivot presiona el botón Administración (Gestionar) y luego en la ventana que aparece – el botón Vista de carta (Vista de diagrama). Queda por arrastrar nombres (campos) de columnas comunes (clave) entre tablas para crear enlaces:

Después de eso, en el resumen del modelo de datos, puede incluir en el área de resumen (filas, columnas, filtros, valores) cualquier campo de cualquier tabla relacionada; todo se vinculará y calculará automáticamente:

Beneficio 2: contar valores únicos

Una tabla dinámica regular nos da la oportunidad de elegir una de varias funciones de cálculo integradas: suma, promedio, conteo, mínimo, máximo, etc. En el resumen del modelo de datos, se agrega una función muy útil a esta lista estándar para contar el número de valores únicos (no repetitivos). Con su ayuda, por ejemplo, puede contar fácilmente la cantidad de artículos únicos de bienes (rango) que vendemos en cada ciudad.

Haga clic derecho en el campo – comando Opciones de campo de valor y en la pestaña Operación Elige Número de elementos diferentes (Recuento distinto):

Beneficio 3: fórmulas DAX personalizadas

A veces, debe realizar varios cálculos adicionales en tablas dinámicas. En los resúmenes regulares, esto se hace usando campos y objetos calculados, mientras que el resumen del modelo de datos usa medidas en un lenguaje DAX especial (DAX = Expresiones de análisis de datos).

Para crear una medida, seleccione en la pestaña PowerPivot Comando Medidas – Crear medida (Medidas — Nueva medida) o simplemente haga clic con el botón derecho en la tabla en la lista de campos dinámicos y seleccione Añadir medida (Añadir medida) en el menú contextual:

En la ventana que se abre, configura:

• Nombre de la tabladonde se almacenará la medida creada.
• Nombre de la medida – cualquier nombre que entienda para el nuevo campo.
• Descripción - Opcional.
• Fórmula – lo más importante, porque aquí ingresamos manualmente o hacemos clic en el botón fx y seleccione una función DAX de la lista, que debería calcular el resultado cuando arrojemos nuestra medida al área de Valores.
• En la parte inferior de la ventana, puede establecer inmediatamente el formato de número para la medida en la lista Categoría.
  

El lenguaje DAX no siempre es fácil de entender porque no opera con valores individuales, sino con columnas y tablas completas, es decir, requiere cierta reestructuración del pensamiento según las fórmulas clásicas de Excel. Sin embargo, vale la pena, porque el poder de sus capacidades en el procesamiento de grandes cantidades de datos es difícil de sobrestimar.

Beneficio 4: Jerarquías de campos personalizados

A menudo, al crear informes estándar, debe lanzar las mismas combinaciones de campos en tablas dinámicas en una secuencia determinada, por ejemplo Año-Trimestre-Mes-Díao Categoría-Productoo País-Ciudad-Cliente etc. En el resumen del modelo de datos, este problema se resuelve fácilmente creando su propio jerarquías — conjuntos de campos personalizados.

En la ventana de Power Pivot, cambie al modo gráfico con el botón Vista de carta de la pestaña. Inicio (Inicio — Vista de diagrama), seleccione con Ctrl campos deseados y haga clic derecho sobre ellos. El menú contextual contendrá el comando Crear jerarquía (Crear jerarquía):

La jerarquía creada se puede renombrar y arrastrar con el mouse los campos requeridos, para que luego en un solo movimiento se puedan arrojar al resumen:

Beneficio 5: Plantillas personalizadas

Siguiendo con la idea del párrafo anterior, en el resumen del Modelo de Datos, también puedes crear tus propios conjuntos de elementos para cada campo. Por ejemplo, de la lista completa de ciudades, puede hacer fácilmente un conjunto de solo aquellas que se encuentran en su área de responsabilidad. O recopile solo sus clientes, sus productos, etc. en un conjunto especial.

Para ello, en la pestaña Análisis de tablas dinámicas en la lista desplegable Campos, elementos y conjuntos hay comandos correspondientes (Analizar — Campos, Ielementos y conjuntos: cree un conjunto basado en elementos de fila/columna):

En la ventana que se abre, puede eliminar, agregar o cambiar la posición de cualquier elemento de forma selectiva y guardar el conjunto resultante con un nuevo nombre:

Todos los conjuntos creados se mostrarán en el panel Campos de tabla dinámica en una carpeta separada, desde donde se pueden arrastrar libremente a las áreas de filas y columnas de cualquier tabla dinámica nueva:

Beneficio 6: ocultar tablas y columnas de forma selectiva

Aunque esta es una ventaja pequeña, pero muy agradable en algunos casos. Al hacer clic derecho en el nombre del campo o en la pestaña de la tabla en la ventana de Power Pivot, puede seleccionar el comando Ocultar del kit de herramientas del cliente (Ocultar de las herramientas del cliente):

La columna o tabla oculta desaparecerá del panel Lista de campos de la tabla dinámica. Es muy conveniente si necesita ocultar al usuario algunas columnas auxiliares (por ejemplo, columnas calculadas o con valores clave para crear relaciones) o incluso tablas completas.

Beneficio 7. Desglose avanzado

Si hace doble clic en cualquier celda en el área de valores de una tabla dinámica normal, Excel muestra en una hoja separada una copia del fragmento de datos de origen que participó en el cálculo de esta celda. Esto es algo muy útil, oficialmente llamado Drill-down (en general dicen "fallar").

En el resumen del modelo de datos, esta útil herramienta funciona de manera más sutil. Al pararnos en cualquier celda con el resultado que nos interese, podemos hacer clic en el ícono con lupa que aparece al lado (se llama Expresar tendencias) y luego seleccione cualquier campo que le interese en cualquier tabla relacionada:

Después de eso, el valor actual (Modelo = Explorador) irá al área de filtro y el resumen se construirá por oficinas:

Por supuesto, dicho procedimiento se puede repetir muchas veces, profundizando constantemente en sus datos en la dirección que le interesa.

Beneficio 8: convertir funciones de pivote a cubo

Si selecciona cualquier celda en el resumen para el modelo de datos y luego selecciona en la pestaña Análisis de tablas dinámicas Comando Herramientas OLAP: convertir a fórmulas (Analizar — Herramientas OLAP — Convertir a fórmulas), todo el resumen se convertirá automáticamente en fórmulas. Ahora los valores de campo en el área de fila-columna y los resultados en el área de valor se recuperarán del modelo de datos usando las funciones de cubo especiales: VALORCUBO y MIEMBROCUBO:

Técnicamente, esto significa que ahora no estamos tratando con un resumen, sino con varias celdas con fórmulas, es decir, podemos hacer fácilmente cualquier transformación con nuestro informe que no esté disponible en el resumen, por ejemplo, insertar nuevas filas o columnas en el medio. del informe, hacer cálculos adicionales dentro del resumen, organizarlos de la forma deseada, etc.

Al mismo tiempo, la conexión con los datos de origen, por supuesto, permanece y en el futuro estas fórmulas se actualizarán cuando cambien las fuentes. ¡La belleza!

• Análisis plan-fact en una tabla dinámica con Power Pivot y Power Query
• Tabla dinámica con encabezado de varias líneas
• Crear una base de datos en Excel usando Power Pivot