Pivot-en abantailak Datu-ereduaren arabera

Excel-en taula dinamikoa eraikitzean, lehenengo elkarrizketa-koadroan, non hasierako barrutia ezartzeko eta taula dinamikoa txertatzeko tokia aukeratzeko eskatzen zaigun, azpian kontrol-lauki nabarmena baina oso garrantzitsua dago - Gehitu datu hauek Datu Ereduari (Gehitu datu hauek Datu eredura) eta, pixka bat gorago, etengailua Erabili liburu honen datu-eredua (Erabili lan-koaderno honetako datu-eredua):

Zoritxarrez, taula dinamikoak aspalditik ezagutzen dituzten eta beren lanean arrakastaz erabiltzen dituzten erabiltzaile askok batzuetan ez dute aukera horien esanahia ulertzen eta ez dute inoiz erabiltzen. Eta alferrik. Azken finean, Datu Eredurako taula dinamikoa sortzeak hainbat abantaila oso garrantzitsuak ematen dizkigu Excel taula dinamiko klasikoaren aldean.

Hala ere, “opil” hauek gertutik aztertu baino lehen, uler dezagun zer den, hain zuzen ere, Datu Eredu hau?

Zer da Datu Eredua

Datu-eredua (MD edo DM = Datu Eredu gisa laburtua) Excel fitxategi baten barruan dagoen eremu berezi bat da, non taula-datuak gorde ditzakezun: taula bat edo gehiago elkarri lotuta, nahi izanez gero. Izan ere, Excel lan-liburu baten barruan txertatutako datu-base txiki bat da (OLAP kuboa). Excel-eko orrietan datuen biltegiratze klasikoarekin alderatuta, datu-ereduak hainbat abantaila esanguratsu ditu:

• Taulak gehienez ere izan daitezke 2 mila milioi lerro, eta Excel orri bat milioi bat baino apur bat gehiago sar daiteke.
• Tamaina erraldoia izan arren, horrelako taulak prozesatzea (iragazkia, ordenatzea, horien gaineko kalkuluak, eraikinaren laburpena, etab.) egiten da. Oso azkar Excel bera baino askoz azkarrago.
• Ereduaren datuekin, kalkulu osagarriak (nahi izanez gero, oso konplexuak) egin ditzakezu erabiliz DAX hizkuntza integratua.
  
• Datu-ereduan kargatutako informazio guztia oso da biziki konprimituta integratutako artxibatzaile berezi bat erabiliz eta jatorrizko Excel fitxategiaren tamaina neurri batean handitzen du.
  

Eredua Microsoft Excel-en integratutako gehigarri berezi baten bidez kudeatzen eta kalkulatzen da - powerpivothorri buruz jada idatzi dudana. Gaitzeko, fitxan developer klik egin COM gehigarriak (Garatzailea — COM gehigarriak) eta markatu dagokion laukia:

fitxak bada developer (Garatzailea)ezin duzu zintaren gainean ikusi, bidez piztu dezakezu Fitxategia – Aukerak – Zinta konfigurazioa (Fitxategia — Aukerak — Pertsonalizatu zinta). COM gehigarrien zerrendan goian agertzen den leihoan Power Pivot ez baduzu, ez dago Microsoft Office-ren bertsioan sartuta 🙁

Agertzen den Power Pivot fitxan, botoi berde argi handi bat egongo da Kudeaketa (Kudeatu), horren gainean klik eginez Power Pivot leihoa irekiko da Excel-en gainean, non uneko liburuaren Datu Ereduaren edukia ikusiko dugu:

Ohar garrantzitsu bat bidean: Excel lan-koaderno batek Datu-eredu bakarra izan dezake.

Kargatu taulak Datu Ereduan

Datuak ereduan kargatzeko, lehenik eta behin taula teklatu-lasterbide "adimentsu" dinamiko batean bihurtzen dugu Ctrl+T eta eman lagunarteko izena fitxan Constructor (Diseinua). Hau beharrezko urratsa da.

Ondoren, hiru metodo hauetako edozein erabil dezakezu aukeratzeko:

• Sakatu botoia Gehitu ereduari (Gehitu datu-ereduari) fitxa powerpivot fitxa Hasiera (Hasiera).
  
• Taldeak aukeratzea Txertatu - Taula dinamikoa (Txertatu — taula dinamikoa) eta aktibatu kontrol-laukia Gehitu datu hauek Datu Ereduari (Gehitu datu hauek Datu ereduan). Kasu honetan, Ereduan kargatutako datuen arabera, taula dinamiko bat ere berehala eraikitzen da.
  
• Aurreratua fitxan Data (Data) egin klik botoian Taula/Barrutitik (Taula/Barrutitik)gure taula Power Query editorean kargatzeko. Bide hau luzeena da, baina, nahi izanez gero, hemen datuen garbiketa, edizio eta era guztietako eraldaketak egin ditzakezu, eta horietan Power Query oso indartsua da.

  Ondoren, orraztutako datuak eredura igotzen dira komandoaren bidez Hasiera — Itxi eta kargatu — Itxi eta kargatu… (Hasiera — Itxi&Kargatu — Itxi&Kargatu…). Irekitzen den leihoan, hautatu aukera Konexio bat sortu besterik ez dago (Sortu konexioa soilik) eta, garrantzitsuena, tick bat jarri Gehitu datu hauek Datu Ereduari (Gehitu datu hauek Datu ereduan).
  

Datu Ereduaren laburpena eraikitzen dugu

Datu-eredu laburtua eraikitzeko, hiru ikuspegi hauetako edozein erabil dezakezu:

• Sakatu botoia laburpen-taula (Taula dinamikoa) Power Pivot leihoan.
• Aukeratu komandoak Excel-en Txertatu - Taula dinamikoa eta aldatu modura Erabili liburu honen datu-eredua (Txertatu — Taula dinamikoa — Erabili lan-koaderno honen datu-eredua).
• Taldeak aukeratzea Txertatu - Taula dinamikoa (Txertatu — taula dinamikoa) eta aktibatu kontrol-laukia Gehitu datu hauek Datu Ereduari (Gehitu datu hauek Datu ereduan). Egungo taula "adimentsua" Ereduan kargatuko da eta Eredu osorako laburpen-taula bat eraikiko da.

Datuak Datu Ereduan nola kargatu eta horren inguruko laburpen bat eraikitzen jakin dugunean, azter ditzagun honek ematen dizkigun onurak eta abantailak.

1. abantaila: Taulen arteko erlazioak formularik erabili gabe

Ohiko laburpen bat iturburu-taula bateko datuak erabiliz soilik eraiki daiteke. Horietako batzuk badituzu, adibidez, salmentak, prezioen zerrenda, bezeroen direktorioa, kontratuen erregistroa, etab., lehenik eta behin taula guztietako datuak bildu beharko dituzu VLOOKUP bezalako funtzioak erabiliz. (VLOOKUP), AURKIBIDEA (AURKIBIDEA), BAKARRIAGO (PARTIDA), SUMMESLIMN (BAURTZAILEAK) eta antzekoak. Hau luzea, neketsua da eta zure Excel datu kopuru handiarekin "pentsamendu" batera eramaten du.

Datu Ereduaren laburpen baten kasuan, dena askoz errazagoa da. Nahikoa da taulen arteko harremanak behin Power Pivot leihoan konfiguratzea, eta listo. Horretarako, fitxan powerpivot sakatu botoia Kudeaketa (Kudeatu) eta gero agertzen den leihoan – botoia Diagrama-ikuspegia (Diagrama ikuspegia). Estekak sortzeko taulen artean zutabe-izenak (eremuak) arruntak (gakoak) arrastatzea geratzen da:

Horren ostean, Datu-ereduaren laburpenean, laburpen-eremuan (errendak, zutabeak, iragazkiak, balioak) erlazionatutako edozein tauletako eremuak bota ditzakezu; dena automatikoki lotuko da eta kalkulatuko da:

2. abantaila: balio bakarrak zenbatu

Taula dinamiko arrunt batek barneratutako kalkulu-funtzioetako bat aukeratzeko aukera ematen digu: batuketa, batez bestekoa, zenbaketa, minimoa, maximoa, etab. Datu-ereduaren laburpenean, zerrenda estandar honi oso funtzio erabilgarria gehitzen zaio, zenbatu ahal izateko. bakarraren kopurua (errepikatzen ez diren balioak). Haren laguntzarekin, adibidez, erraz zenbatu ditzakezu hiri bakoitzean saltzen ditugun ondasunen (sorta) bakarrak.

Egin klik eskuineko botoiarekin eremuan - komandoan Balio-eremuaren aukerak eta fitxan Eragiketa Aukeratu Elementu ezberdinen kopurua (Zenbaketa bereizia):

3. abantaila: DAX formula pertsonalizatuak

Batzuetan, hainbat kalkulu osagarri egin behar dituzu taula dinamikoetan. Ohiko laburpenetan, kalkulatutako eremuak eta objektuak erabiliz egiten da, datu-ereduaren laburpenak DAX hizkuntza berezi batean (DAX = Data Analysis Expressions) neurriak erabiltzen dituen bitartean.

Neurri bat sortzeko, hautatu fitxan powerpivot Komandoa Neurriak - Sortu neurria (Neurriak — Neurri berria) edo, besterik gabe, egin klik eskuineko botoiarekin Pibota-eremuak zerrendako taulan eta hautatu Gehitu neurria (Gehitu neurria) testuinguruko menuan:

Irekitzen den leihoan, ezarri:

• Taularen izenabertan gordeko da sortutako neurria.
• Neurri izena – eremu berrirako ulertzen duzun edozein izen.
• Deskribapena – hautazkoa.
• Formula – garrantzitsuena, hemen eskuz sartzen dugulako, edo botoian klik egiten dugulako fx eta hautatu DAX funtzio bat zerrendatik, eta emaitza kalkulatu beharko luke gero gure neurria Balioak eremura botatzen dugunean.
• Leihoaren beheko aldean, berehala ezar dezakezu zerrendako neurriaren formatua Kategoria.
  

DAX lengoaia ez da beti erraza ulertzen, ez baitu balio indibidualekin funtzionatzen, zutabe eta taula osoekin baizik, hau da, Excel formula klasikoen ondoren pentsamenduaren berregituraketa bat eskatzen du. Hala ere, merezi du, datu kopuru handiak prozesatzeko dituen gaitasunen boterea zaila delako gainestimatzea.

4. abantaila: Eremu pertsonalizatuen hierarkiak

Askotan, txosten estandarrak sortzean, eremuen konbinazio berdinak bota behar dituzu taula dinamikoetan sekuentzia jakin batean, adibidez Urtea-Laurdena-Hilabetea-EgunaEdo Kategoria-ProduktuaEdo Herrialdea-Herria-Bezeroa etab. Datu-ereduaren laburpenean, arazo hau erraz konpontzen da zurea sortuz hierarkiak — Eremu pertsonalizatuak.

Power Pivot leihoan, aldatu diagrama modura botoiarekin Diagrama-ikuspegia fitxa Hasiera (Hasiera — Diagrama ikuspegia), hautatu honekin Ctrl nahi dituzun eremuak eta egin klik eskuineko botoiarekin gainean. Testuinguru-menuak komandoa izango du Sortu hierarkia (Sortu hierarkia):

Sortutako hierarkiari izena aldatu eta saguarekin arrastatu behar diren eremuak, geroago mugimendu batean laburpenera bota daitezke:

5. abantaila: Txantiloi pertsonalizatuak

Aurreko paragrafoko ideiarekin jarraituz, Datu Ereduaren laburpenean, eremu bakoitzerako zure elementu multzoak ere sor ditzakezu. Esate baterako, hirien zerrenda osotik, zure ardura-eremuan daudenen multzoa erraz egin dezakezu. Edo bildu zure bezeroak, zure ondasunak eta abar bakarrik multzo berezi batean.

Horretarako, fitxan Taula dinamikoen analisia goitibeherako zerrendan Eremuak, elementuak eta multzoak dagozkien komandoak daude (Aztertu - Eremuak, Items & Sets — Sortu multzoa errenkada/zutabe elementuetan oinarrituta):

Irekitzen den leihoan, edozein elementuren posizioa hautatuz kendu, gehitu edo alda dezakezu eta sortutako multzoa izen berri batekin gorde dezakezu:

Sortutako multzo guztiak taula dinamikoko eremuen panelean bistaratuko dira beste karpeta batean, eta bertatik libreki arrastatu ahal izango dira edozein taula dinamiko berriko errenkada eta zutabe eremuetara:

6. abantaila: taulak eta zutabeak selektiboki ezkutatu

Hau abantaila txikia den arren, kasu batzuetan oso atsegina den arren. Power Pivot leihoko eremuaren izenan edo taulako fitxan eskuineko botoiarekin klik eginez, komandoa hauta dezakezu Ezkutatu Bezero-tresnetatik (Ezkutatu bezeroaren tresnetatik):

Ezkutuko zutabea edo taula Taula dinamikoko eremuen zerrenda paneletik desagertuko da. Oso erosoa da erabiltzaileari zutabe laguntzaile batzuk ezkutatu behar badituzu (adibidez, kalkulatuak edo erlazioak sortzeko gako-balioak dituzten zutabeak) edo baita taula osoak ere.

7. abantaila. Zulatze aurreratua

Balio-eremuko edozein gelaxketan klik bikoitza egiten baduzu taula dinamiko arrunt batean, orduan Excel-ek orri bereizi batean bistaratzen du gelaxka honen kalkuluan parte hartu zuen iturri-datuen zatiaren kopia bat. Hau oso gauza erabilgarria da, ofizialki Drill-down izenekoa (normalean "porrot" esaten dute).

Datu-ereduaren laburpenean, tresna erabilgarri honek sotilagoa da. Interesatzen zaigun emaitza duen edozein gelaxketan zutik jarrita, ondoan agertzen den lupa duen ikonoan klik egin dezakezu (deitzen da Adierazi joerak) eta, ondoren, hautatu erlazionatutako edozein taulatan interesatzen zaizun eremua:

Horren ondoren, uneko balioa (Eredua = Explorer) iragazki eremura joango da, eta laburpena bulegoek eraikiko dute:

Jakina, prozedura hori askotan errepika daiteke, zure datuetan etengabe sakonduz interesatzen zaizun norabidean.

8. abantaila: Bihurtu Pibota Kubo Funtzioetan

Datu-ereduaren laburpenean gelaxka bat hautatzen baduzu eta gero fitxan hautatu Taula dinamikoen analisia Komandoa OLAP tresnak - Bihurtu formuletara (Aztertu — OLAP tresnak — Bihurtu formuletara), ondoren, laburpen osoa automatikoki formula bihurtuko da. Orain errenkada-zutabe eremuko eremu-balioak eta balio-eremuko emaitzak Datu-eredutik berreskuratuko dira kubo-funtzio bereziak erabiliz: CUBEVALUE eta CUBEMEMBER:

Teknikoki, horrek esan nahi du orain ez garela laburpen batekin ari, formulak dituzten hainbat gelaxka baizik, hau da, laburpenean eskuragarri ez dauden gure txostenarekin erraz egin ditzakegula eraldaketak, adibidez, errenkada edo zutabe berriak txertatu erdian. txostenaren, laburpenaren barruan kalkulu osagarriak egin, nahi duzun moduan antolatu, etab.

Aldi berean, iturri-datuekiko konexioa mantentzen da, noski, eta aurrerantzean formula horiek eguneratuko dira iturriak aldatzen direnean. Edertasuna!

• Plan-egitasmoen azterketa taula dinamiko batean Power Pivot eta Power Query-ekin
• Lerro anitzeko goiburudun taula dinamikoa
• Sortu datu-base bat Excel-en Power Pivot erabiliz