Beneficis de Pivot per model de dades

Quan es construeix una taula dinàmica a Excel, al primer quadre de diàleg, on se'ns demana que establim l'interval inicial i escollim un lloc per inserir la taula dinàmica, hi ha una casella de selecció poc visible però molt important a continuació: Afegiu aquestes dades al model de dades (Afegiu aquestes dades al model de dades) i, una mica més amunt, l'interruptor Utilitzeu el model de dades d'aquest llibre (Utilitzeu el model de dades d'aquest llibre de treball):

Malauradament, molts usuaris que s'han familiaritzat amb les taules dinàmiques durant molt de temps i les fan servir amb èxit en el seu treball de vegades no entenen realment el significat d'aquestes opcions i mai les fan servir. I en va. Després de tot, crear una taula dinàmica per al model de dades ens ofereix diversos avantatges molt importants en comparació amb la taula dinàmica clàssica d'Excel.

Tanmateix, abans de considerar aquests "bollos" de prop, primer entenem què és, de fet, aquest model de dades?

Què és un model de dades

Model de dades (abreujat com MD o DM = Model de dades) és una àrea especial dins d'un fitxer Excel on podeu emmagatzemar dades tabulars: una o més taules enllaçades, si ho desitja, entre elles. De fet, es tracta d'una petita base de dades (cub OLAP) incrustada dins d'un llibre d'Excel. En comparació amb l'emmagatzematge clàssic de dades en forma de taules normals (o intel·ligents) en fulls d'Excel mateix, el model de dades té diversos avantatges significatius:

• Les taules poden ser de fins a 2 milions de línies, i un full d'Excel pot cabre una mica més d'1 milió.
• Malgrat la mida gegantina, es realitza el processament d'aquestes taules (filtrat, classificació, càlculs, resum de l'edifici, etc.) molt ràpid Molt més ràpid que el mateix Excel.
• Amb les dades del Model, podeu realitzar càlculs addicionals (si voleu, molt complexos) mitjançant llenguatge DAX integrat.
  
• Tota la informació carregada al model de dades és molt fortament comprimit utilitzant un arxivador integrat especial i augmenta força moderadament la mida del fitxer Excel original.
  

El model es gestiona i calcula mitjançant un complement especial integrat a Microsoft Excel: powerpivotsobre el qual ja he escrit. Per activar-lo, a la pestanya revelador clic Complements COM (Desenvolupador: complements COM) i marca la casella corresponent:

Si pestanyes revelador (Desenvolupador)no el podeu veure a la cinta, podeu activar-lo a través Fitxer – Opcions – Configuració de la cinta (Fitxer — Opcions — Personalitza la cinta). Si a la finestra que es mostra a dalt a la llista de complements COM no teniu Power Pivot, no s'inclou a la vostra versió de Microsoft Office 🙁

A la pestanya Power Pivot que apareix, hi haurà un gran botó verd clar Management (Gestionar), fent clic a la qual s'obrirà la finestra Power Pivot a sobre d'Excel, on veurem el contingut del Model de dades del llibre actual:

Una nota important al llarg del camí: un llibre de treball d'Excel només pot contenir un model de dades.

Carregueu taules al model de dades

Per carregar dades al model, primer convertim la taula en una drecera de teclat "intel·ligent" dinàmica Ctrl+T i posa-li un nom amigable a la pestanya constructor (Disseny). Aquest és un pas obligatori.

A continuació, podeu utilitzar qualsevol dels tres mètodes per triar:

• prem el botó Afegeix al model (Afegir al model de dades) llengüeta powerpivot llengüeta Home (Inici).
  
• Selecció d'equips Insereix - Taula dinàmica (Insereix — Taula dinàmica) i activeu la casella de selecció Afegiu aquestes dades al model de dades (Afegiu aquestes dades al model de dades). En aquest cas, segons les dades carregades al Model, també es construeix immediatament una taula dinàmica.
  
• A la pestanya Avançat dades (Data) feu clic al botó De Taula/Range (Des de la taula/gama)per carregar la nostra taula a l'editor de Power Query. Aquest camí és el més llarg, però, si es vol, aquí podeu realitzar neteja de dades addicionals, edició i tot tipus de transformacions, en les quals Power Query és molt fort.

  A continuació, les dades pentinades es pengen al model mitjançant l'ordre Inici — Tancar i carregar — Tancar i carregar a... (Inici — Tancar i carregar — Tancar i carregar a...). A la finestra que s'obre, seleccioneu l'opció Només cal crear una connexió (Només crea connexió) i, el més important, posar una paparra Afegiu aquestes dades al model de dades (Afegiu aquestes dades al model de dades).
  

Construïm un resum del model de dades

Per crear un model de dades resum, podeu utilitzar qualsevol dels tres enfocaments:

• Premeu el botó taula resum (Taula dinàmica) a la finestra Power Pivot.
• Seleccioneu ordres a Excel Insereix - Taula dinàmica i canviar al mode Utilitzeu el model de dades d'aquest llibre (Insereix — Taula dinàmica — Utilitza el model de dades d'aquest llibre de treball).
• Selecció d'equips Insereix - Taula dinàmica (Insereix — Taula dinàmica) i activeu la casella de selecció Afegiu aquestes dades al model de dades (Afegiu aquestes dades al model de dades). La taula "intel·ligent" actual es carregarà al model i es crearà una taula resum per a tot el model.

Ara que hem descobert com carregar dades al model de dades i crear-ne un resum, analitzem els avantatges i avantatges que això ens ofereix.

Benefici 1: Relacions entre taules sense utilitzar fórmules

Un resum normal només es pot crear utilitzant dades d'una taula font. Si teniu diversos d'ells, per exemple, vendes, llista de preus, directori de clients, registre de contractes, etc., primer haureu de recollir dades de totes les taules en una usant funcions com ara BUSCAR V. (VISUALITZACIÓ), ÍNDEX (ÍNDEX), MÉS EXPOSADA (PARTIDA), SUMMESLIMN (SUMIFS) i similars. Això és llarg, tediós i condueix el vostre Excel a un "pensament" amb una gran quantitat de dades.

En el cas d'un resum del Model de Dades, tot és molt més senzill. N'hi ha prou amb configurar les relacions entre taules un cop a la finestra Power Pivot, i ja està. Per fer-ho, a la pestanya powerpivot prem el botó Management (Gestionar) i després a la finestra que apareix, el botó Vista de gràfic (Vista de diagrama). Queda per arrossegar noms de columnes (camps) comuns (claus) entre taules per crear enllaços:

Després d'això, al resum del model de dades, podeu llançar a l'àrea de resum (files, columnes, filtres, valors) qualsevol camp de qualsevol taules relacionades; tot s'enllaçarà i es calcularà automàticament:

Benefici 2: comptar valors únics

Una taula dinàmica normal ens dóna l'oportunitat de triar una de les diverses funcions de càlcul incorporades: suma, mitjana, recompte, mínim, màxim, etc. Al resum del model de dades, s'afegeix una funció molt útil a aquesta llista estàndard per comptar els nombre de valors únics (no repetitius). Amb la seva ajuda, per exemple, podeu comptar fàcilment el nombre d'articles únics de mercaderies (gama) que venem a cada ciutat.

Feu clic amb el botó dret al camp - comanda Opcions de camp de valor i a la pestanya operació Escollir Nombre d'elements diferents (Recompte diferent):

Benefici 3: Fórmules DAX personalitzades

De vegades, heu de realitzar diversos càlculs addicionals a les taules dinàmiques. En els resums habituals, això es fa mitjançant camps i objectes calculats, mentre que el resum del model de dades utilitza mesures en un llenguatge DAX especial (DAX = Expressions d'anàlisi de dades).

Per crear una mesura, seleccioneu a la pestanya powerpivot Comando Mesures: crear una mesura (Mesures — Nova mesura) o simplement feu clic amb el botó dret a la taula de la llista Camps dinàmics i seleccioneu Afegeix una mesura (Afegir mesura) al menú contextual:

A la finestra que s'obre, configureu:

• Nom de la taulaon s'emmagatzemarà la mesura creada.
• Nom de la mesura – qualsevol nom que entengui per al camp nou.
• Descripció – opcional.
• Fórmula – el més important, perquè aquí o entrem manualment, o premem el botó fx i seleccioneu una funció DAX de la llista, que hauria de calcular el resultat quan després llencem la nostra mesura a l'àrea Valors.
• A la part inferior de la finestra, podeu definir immediatament el format de nombre de la mesura de la llista categoria.
  

El llenguatge DAX no sempre és fàcil d'entendre perquè no funciona amb valors individuals, sinó amb columnes i taules senceres, és a dir, requereix una certa reestructuració del pensament després de les fórmules clàssiques d'Excel. No obstant això, val la pena, perquè el poder de les seves capacitats per processar grans quantitats de dades és difícil de sobreestimar.

Benefici 4: jerarquies de camps personalitzades

Sovint, quan creeu informes estàndard, heu de llançar les mateixes combinacions de camps a taules dinàmiques en una seqüència determinada, per exemple. Any-Quatrimestre-Mes-Dia, O Categoria-Producte, O País-Ciutat-Client etc. Al resum del model de dades, aquest problema es resol fàcilment creant el vostre jerarquies - conjunts de camps personalitzats.

A la finestra Power Pivot, canvieu al mode gràfic amb el botó Vista de gràfic llengüeta Home (Inici — Vista de diagrama), seleccioneu amb Ctrl camps desitjats i feu-hi clic amb el botó dret. El menú contextual contindrà l'ordre Crear jerarquia (Crear jerarquia):

Es pot canviar el nom de la jerarquia creada i arrossegar-hi amb el ratolí els camps requerits, de manera que més tard en un moviment es poden llançar al resum:

Benefici 5: Plantilles personalitzades

Continuant amb la idea del paràgraf anterior, en el resum del Model de dades, també podeu crear els vostres propis conjunts d'elements per a cada camp. Per exemple, a partir de tota la llista de ciutats, podeu fer fàcilment un conjunt de només aquelles que es troben a la vostra àrea de responsabilitat. O recolliu només els vostres clients, els vostres béns, etc. en un conjunt especial.

Per fer-ho, a la pestanya Anàlisi de la taula dinàmica a la llista desplegable Camps, elements i conjunts hi ha ordres corresponents (Analitzar - Camps, Items i conjunts: creeu un conjunt basat en elements de fila/columna):

A la finestra que s'obre, podeu eliminar, afegir o canviar selectivament la posició de qualsevol element i desar el conjunt resultant amb un nom nou:

Tots els conjunts creats es mostraran al panell Camps de la taula dinàmica en una carpeta independent, des d'on es poden arrossegar lliurement a les àrees de files i columnes de qualsevol taula dinàmica nova:

Benefici 6: amaga selectivament taules i columnes

Tot i que això és un avantatge petit, però molt agradable en alguns casos. Si feu clic amb el botó dret al nom del camp o a la pestanya de la taula de la finestra Power Pivot, podeu seleccionar l'ordre Amaga del kit d'eines del client (Amaga de les eines del client):

La columna o taula oculta desapareixerà del panell Llista de camps de la taula dinàmica. És molt convenient si necessiteu ocultar a l'usuari algunes columnes auxiliars (per exemple, calculades o columnes amb valors clau per crear relacions) o fins i tot taules senceres.

Avantatge 7. Desglossament avançat

Si feu doble clic a qualsevol cel·la de l'àrea de valors d'una taula dinàmica normal, l'Excel mostrarà en un full separat una còpia del fragment de dades d'origen que va participar en el càlcul d'aquesta cel·la. Aquesta és una cosa molt útil, anomenada oficialment Drill-down (en generalment diuen "falla").

Al resum del model de dades, aquesta pràctica eina funciona de manera més subtil. En situar-se en qualsevol cel·la amb el resultat que ens interessa, podeu fer clic a la icona amb una lupa que apareix al costat (s'anomena Express Tendències) i, a continuació, seleccioneu qualsevol camp que us interessi en qualsevol taula relacionada:

Després d'això, el valor actual (Model = Explorador) passarà a l'àrea de filtres i les oficines crearan el resum:

Per descomptat, aquest procediment es pot repetir moltes vegades, aprofundint constantment en les vostres dades en la direcció que us interessa.

Benefici 8: Converteix funcions de pivot en cub

Si seleccioneu qualsevol cel·la al resum per al model de dades i després seleccioneu a la pestanya Anàlisi de la taula dinàmica Comando Eines OLAP: Converteix a fórmules (Analitzar — Eines OLAP — Convertir a fórmules), tot el resum es convertirà automàticament en fórmules. Ara, els valors de camp de l'àrea de fila-columna i els resultats de l'àrea de valors es recuperaran del model de dades mitjançant les funcions especials del cub: CUBEVALUE i CUBEMEMBER:

Tècnicament, això vol dir que ara no estem davant d'un resum, sinó de diverses cel·les amb fórmules, és a dir, podem fer fàcilment qualsevol transformació amb el nostre informe que no estigui disponible al resum, per exemple, inserir noves files o columnes al mig. de l'informe, feu qualsevol càlcul addicional dins del resum, organitzeu-los de la manera desitjada, etc.

Al mateix temps, la connexió amb les dades d'origen, és clar, es manté i en el futur aquestes fórmules s'actualitzaran quan canviïn les fonts. La bellesa!

• Anàlisi dels fets del pla en una taula dinàmica amb Power Pivot i Power Query
• Taula dinàmica amb capçalera multilínia
• Creeu una base de dades a Excel amb Power Pivot