Beneficios de Pivot by Data Model

Ao construír unha táboa dinámica en Excel, no primeiro cadro de diálogo, onde se nos pide que establezamos o intervalo inicial e elixamos un lugar para inserir a táboa dinámica, hai unha caixa de verificación discreta pero moi importante a continuación: Engade estes datos ao modelo de datos (Engadir estes datos ao modelo de datos) e, un pouco máis alto, o interruptor Use o modelo de datos deste libro (Utilice o modelo de datos deste libro):

Desafortunadamente, moitos usuarios que están familiarizados coas táboas dinámicas durante moito tempo e que as utilizan con éxito no seu traballo ás veces non entenden realmente o significado destas opcións e nunca as usan. E en balde. Despois de todo, crear unha táboa dinámica para o modelo de datos ofrécenos varias vantaxes moi importantes en comparación coa clásica táboa dinámica de Excel.

Non obstante, antes de considerar estes "bollos" de preto, primeiro entendamos que é, en realidade, este modelo de datos?

Que é un modelo de datos

Modelo de datos (abreviado como MD ou DM = Modelo de datos) é unha área especial dentro dun ficheiro Excel onde pode almacenar datos tabulares: unha ou máis táboas ligadas, se o desexa, entre si. De feito, esta é unha pequena base de datos (cubo OLAP) incrustada nun libro de Excel. En comparación co almacenamento clásico de datos en forma de táboas regulares (ou intelixentes) en follas de Excel, o modelo de datos ten varias vantaxes significativas:

• As táboas poden ser de ata 2 millóns de liñas, e unha folla de Excel pode caber algo máis de 1 millón.
• A pesar do tamaño xigantesco, realízase o procesamento de tales táboas (filtrado, clasificación, cálculos sobre elas, resumo do edificio, etc.) moi rápido Moito máis rápido que o propio Excel.
• Cos datos do Modelo, pode realizar cálculos adicionais (se o desexa, moi complexos) usando linguaxe DAX incorporada.
  
• Toda a información cargada no modelo de datos é moi fortemente comprimido usando un arquivador incorporado especial e aumenta moderadamente o tamaño do ficheiro de Excel orixinal.
  

O modelo é xestionado e calculado por un complemento especial integrado en Microsoft Excel - PowerPivotsobre o que xa escribín. Para activalo, na pestana revelador prema Complementos COM (Desenvolvedor - Complementos COM) e marque a caixa correspondente:

Se fichas revelador (Desenvolvedor)non podes velo na cinta, podes acendelo a través Ficheiro - Opcións - Configuración da cinta (Ficheiro — Opcións — Personalizar cinta). Se na xanela que aparece arriba na lista de complementos COM non tes Power Pivot, entón non está incluído na túa versión de Microsoft Office 🙁

Na pestana Power Pivot que aparece, haberá un gran botón verde claro xestión (Xestionar), premendo sobre a cal abrirase a xanela Power Pivot enriba de Excel, onde veremos o contido do Modelo de datos do libro actual:

Unha nota importante ao longo do camiño: un libro de Excel só pode conter un modelo de datos.

Cargar táboas no modelo de datos

Para cargar datos no modelo, primeiro transformamos a táboa nun atallo de teclado dinámico "intelixente". Ctrl+T e ponlle un nome amigable na pestana Construtor (Deseño). Este é un paso obrigatorio.

Entón podes usar calquera dos tres métodos para escoller:

• Preme o botón Engadir ao modelo (Engadir ao modelo de datos) aba PowerPivot aba casa (Inicio).
  
• Selección de equipos Inserir - Táboa dinámica (Inserir — Táboa dinámica) e activa a caixa de verificación Engade estes datos ao modelo de datos (Engadir estes datos ao modelo de datos). Neste caso, segundo os datos cargados no Modelo, tamén se constrúe inmediatamente unha táboa dinámica.
  
• Na pestana Avanzado Data (Data) prema no botón De Táboa/Range (De mesa/rango)para cargar a nosa táboa no editor de Power Query. Este camiño é o máis longo, pero, se o desexa, aquí pode realizar limpeza de datos adicionais, edición e todo tipo de transformacións, nas que Power Query é moi forte.

  A continuación, os datos peiteados cárganse no Modelo mediante o comando Inicio — Pechar e cargar — Pechar e cargar en… (Inicio — Pechar e cargar — Pechar e cargar en…). Na xanela que se abre, seleccione a opción Só ten que crear unha conexión (Só crear conexión) e, o máis importante, poñer unha garrapata Engade estes datos ao modelo de datos (Engadir estes datos ao modelo de datos).
  

Elaboramos un resumo do modelo de datos

Para crear un modelo de datos resumido, pode utilizar calquera dos tres enfoques:

• Prema o botón táboa resumo (Táboa dinámica) na xanela Power Pivot.
• Seleccione comandos en Excel Inserir - Táboa dinámica e cambiar ao modo Use o modelo de datos deste libro (Inserir — Táboa dinámica — Use o modelo de datos deste libro).
• Selección de equipos Inserir - Táboa dinámica (Inserir — Táboa dinámica) e activa a caixa de verificación Engade estes datos ao modelo de datos (Engadir estes datos ao modelo de datos). A táboa "intelixente" actual cargarase no modelo e crearase unha táboa resumo para todo o modelo.

Agora que descubrimos como cargar datos no Modelo de datos e elaborar un resumo sobre el, imos explorar os beneficios e vantaxes que nos proporciona.

Beneficio 1: Relacións entre táboas sen utilizar fórmulas

Un resumo normal só se pode crear utilizando datos dunha táboa de orixe. Se tes varios deles, por exemplo, vendas, lista de prezos, directorio de clientes, rexistro de contratos, etc., primeiro terás que recoller os datos de todas as táboas nunha soa usando funcións como BUSCAR V. (BÚSQUEDA V), ÍNDICE (ÍNDICE), MÁIS EXPOÑA (Coincidir), SUMMESLIMN (SUMIFS) e similares. Isto é longo, tedioso e leva o teu Excel a un "pensamento" cunha gran cantidade de datos.

No caso dun resumo do Modelo de Datos, todo é moito máis sinxelo. Basta con configurar as relacións entre táboas unha vez na xanela Power Pivot e xa está. Para iso, na pestana PowerPivot prema o botón xestión (Xestionar) e despois na xanela que aparece - o botón Vista gráfica (Vista de diagrama). Queda por arrastrar os nomes de columnas (campos) comúns (claves) entre táboas para crear ligazóns:

Despois diso, no resumo do modelo de datos, podes engadir na área de resumo (filas, columnas, filtros, valores) todos os campos de calquera táboa relacionada: todo ligarase e calcularase automaticamente:

Beneficio 2: Conta valores únicos

Unha táboa dinámica normal dános a oportunidade de escoller unha das varias funcións de cálculo integradas: suma, media, conta, mínimo, máximo, etc. No resumo do modelo de datos, engádese unha función moi útil a esta lista estándar para contar os número de únicos (valores que non se repiten). Coa súa axuda, por exemplo, podes contar facilmente o número de artigos únicos de mercadorías (rango) que vendemos en cada cidade.

Fai clic co botón dereito no campo - comando Opcións de campo de valor e na pestana Operación Escoller Número de elementos diferentes (Recuento distinto):

Beneficio 3: fórmulas DAX personalizadas

Ás veces tes que realizar varios cálculos adicionais en táboas dinámicas. Nos resumos habituais, isto faise mediante campos e obxectos calculados, mentres que o resumo do modelo de datos usa medidas nunha linguaxe DAX especial (DAX = Data Analysis Expressions).

Para crear unha medida, seleccione na pestana PowerPivot Mando Medidas - Crear medida (Medidas — Nova medida) ou simplemente fai clic co botón dereito na táboa na lista Campos dinámicos e selecciona Engadir medida (Engadir medida) no menú contextual:

Na xanela que se abre, establece:

• Nome da táboaonde se almacenará a medida creada.
• Nome da medida – calquera nome que entenda para o novo campo.
• descrición - opcional.
• Fórmula – o máis importante, porque aquí ou entramos manualmente, ou prememos no botón fx e seleccione unha función DAX da lista, que debería calcular o resultado cando lancemos a nosa medida na área de Valores.
• Na parte inferior da xanela, pode configurar inmediatamente o formato de número para a medida na lista categoría.
  

A linguaxe DAX non sempre é fácil de entender porque non opera con valores individuais, senón con columnas e táboas enteiras, é dicir, require algunha reestruturación do pensamento despois das fórmulas clásicas de Excel. Non obstante, paga a pena, porque o poder das súas capacidades para procesar grandes cantidades de datos é difícil de sobreestimar.

Beneficio 4: xerarquías de campos personalizadas

Moitas veces, ao crear informes estándar, tes que engadir as mesmas combinacións de campos a táboas dinámicas nunha secuencia determinada, por exemplo Ano-Trimestre-Mes-Díaou Categoría-Produtoou País-Cidade-Cliente etc. No resumo do modelo de datos, este problema resólvese facilmente creando o teu propio xerarquías - conxuntos de campos personalizados.

Na xanela Power Pivot, cambia ao modo gráfico co botón Vista gráfica aba casa (Inicio — Vista de diagrama), seleccione con Ctrl campos desexados e fai clic co botón dereito sobre eles. O menú contextual conterá o comando Crear xerarquía (Crear xerarquía):

Pódese renomear á xerarquía creada e arrastrar nela co rato os campos necesarios, para que posteriormente, nun movemento, poidan ser lanzados ao resumo:

Beneficio 5: stencils personalizados

Continuando coa idea do parágrafo anterior, no resumo do Modelo de datos, tamén pode crear os seus propios conxuntos de elementos para cada campo. Por exemplo, a partir da lista completa de cidades, pode facilmente facer un conxunto só das que están na súa área de responsabilidade. Ou recolle só os teus clientes, os teus produtos, etc. nun conxunto especial.

Para iso, na pestana Análise da táboa dinámica na lista despregable Campos, elementos e conxuntos hai ordes correspondentes (Analizar - Campos, Items & Sets: crea un conxunto baseado en elementos de fila/columna):

Na xanela que se abre, pode eliminar, engadir ou cambiar de forma selectiva a posición de calquera elemento e gardar o conxunto resultante cun novo nome:

Todos os conxuntos creados mostraranse no panel Campos da táboa dinámica nun cartafol separado, desde onde se poden arrastrar libremente ás áreas de filas e columnas de calquera nova táboa dinámica:

Beneficio 6: ocultar selectivamente táboas e columnas

Aínda que esta é unha vantaxe pequena, pero moi agradable nalgúns casos. Facendo clic co botón dereito no nome do campo ou na pestana da táboa da xanela Power Pivot, pode seleccionar o comando Ocultar do Kit de ferramentas do cliente (Ocultar das ferramentas do cliente):

A columna ou táboa ocultas desaparecerá do panel Lista de campos da táboa dinámica. É moi conveniente se precisa ocultar ao usuario algunhas columnas auxiliares (por exemplo, calculadas ou columnas con valores clave para crear relacións) ou incluso táboas enteiras.

Beneficio 7. Desglose avanzado

Se fai dobre clic en calquera cela da área de valores nunha táboa dinámica normal, Excel mostra nunha folla separada unha copia do fragmento de datos de orixe que estivo implicado no cálculo desta cela. Esta é unha cousa moi útil, chamada oficialmente Drill-down (en xeralmente din "fallar").

No resumo do modelo de datos, esta ferramenta útil funciona de forma máis sutil. Parado en calquera cela co resultado que nos interese, podes facer clic na icona cunha lupa que aparece ao seu carón (chámase Tendencias expresas) e, a continuación, seleccione calquera campo que lle interese en calquera táboa relacionada:

Despois diso, o valor actual (Modelo = Explorador) pasará á área de filtro e o resumo será construído polas oficinas:

Por suposto, tal procedemento pódese repetir moitas veces, afondando constantemente nos teus datos na dirección que che interese.

Beneficio 8: converte funcións de pivote en cubo

Se selecciona algunha cela no resumo para o modelo de datos e despois selecciona na pestana Análise da táboa dinámica Mando Ferramentas OLAP - Converter en fórmulas (Analizar — Ferramentas OLAP — Converter en fórmulas), entón todo o resumo converterase automaticamente en fórmulas. Agora os valores de campo na área de fila-columna e os resultados na área de valor recuperaranse do modelo de datos usando as funcións especiais do cubo: CUBEVALUE e CUBEMEMBER:

Tecnicamente, isto significa que agora non estamos ante un resumo, senón con varias celas con fórmulas, é dicir, podemos facer facilmente co noso informe calquera transformación que non estea dispoñible no resumo, por exemplo, inserir novas filas ou columnas no medio. do informe, realice os cálculos adicionais dentro do resumo, dispoñaos da forma que desexe, etc.

Ao mesmo tempo, a conexión cos datos de orixe, por suposto, mantense e no futuro estas fórmulas actualizaranse cando cambien as fontes. A beleza!

• Análise do plan nunha táboa dinámica con Power Pivot e Power Query
• Táboa dinámica con cabeceira multiliña
• Crea unha base de datos en Excel usando Power Pivot