Pöördtabelite kasutamine

Varem rääkisin juba sellest, et lahtrile viidates pöördlaud tavalise lingi asemel tagastatakse funktsioon GET.DATA.PUMP.TABLE (vt). Kui olete huvitatud as selle ebamugavuse ületamiseks soovitan viidata nimetatud märkusele. Kui te ei tea, miks nii see juhtub ja ka millised on funktsiooni GET PANEELI ANDMED. TABEL positiivsed küljed, siis pakun fragmendi raamatust Jelen, Alexander. (15. peatükk). Vaadeldav tehnika võimaldab teil toime tulla paljude probleemidega, mis põhjustavad pöördtabelite kasutajatele peavalu, eelkõige:

• PivotTable-liigendtabeli värskendamisel kaob varem rakendatud vormindus. Numbrivormingud on kadunud. Veeru laiuse reguleerimise tulemused kaovad.
• Ei eksisteeri lihtne viis asümmeetrilise pöördtabeli loomine. Ainus võimalus on kasutada nimega komplekte, kuid see meetod on saadaval ainult neile, kes kasutavad andmemudeli pöördtabeleid, mitte tavalisi pöördtabeleid.
• Excel ei mäleta malle. Kui peate PivotTable-liigendtabeleid uuesti ja uuesti looma, peate uuesti rühmitama, rakendama arvutatud väljad ja liikmed ning tegema mitmeid muid sarnaseid toiminguid.

Tegelikult pole kõik siin kirjeldatud asi uus. Pealegi on sarnaseid võtteid kasutatud alates Excel 2002. Kuid minu suhtlus kasutajatega näitab, et vähem kui 1% on nendega tuttavad. Ainus küsimus, mis kasutajatel on, on see, kuidas välja lülitada veider funktsioon PANEELI TABELI ANDMED. Kahju…

Laadige märkus alla vormingus või näited vormingus

Noh, alustame järjekorras.

Kuidas loobuda probleemsest funktsioonist GET DATA. ISIKLIKUD TABELID

Funktsioon GET PIVOTTABLE DATA on juba pikka aega olnud paljude kasutajate peavalu. Ühtäkki muutus ilma ühegi hoiatuseta PivotTable-liigendtabelite käitumine programmis Excel 2002. Niipea, kui hakkate looma valemeid väljaspool PivotTable-liigendtabelit, mis viitavad selle andmetele, tuleb see funktsioon välja kusagilt.

Oletame, et joonisel fig. 1, peate võrdlema 2015. ja 2014. aasta andmeid.

Joonis: 1. Algne pöördlaud

1. Lisage lahtrisse D3 pealkiri "% Height".
2. Kopeerige vorming lahtrist C3 lahtrisse D3.
3. Sisestage lahtrisse D4 võrdusmärk.
4. Klõpsake lahtril C4.
5. Sisestage jagamistoimingu tähistamiseks märk (/ kaldkriips)
6. Klõpsake lahtril B4.
7. Sisestage -1 ja vajutage klahvikombinatsiooni jääda samasse kambrisse. Vormindage tulemus protsentides. Näete, et läänes kasvas sissetulek 43,8% (joonis 2). Pole eriti head tulemused.
8. Kui olete oma esimese valemi sisestanud, valige lahter D4.
9. Topeltklõpsake lahtril paremas alanurgas asuvat väikest ruutu. See ruut tähistab täitekäepidet, mille abil saate aruande terve veeru täitmiseks valemi kopeerida.

Kui olete valemi kopeerimise lõpetanud ja pilgu ekraanile heitnud, saate aru, et midagi on valesti - iga piirkonna langus oli aastaga 43,8% (joonis 3).

Joonis: 3. Kui olete valemi kõigisse veeru lahtritesse kopeerinud, näete, et iga piirkonna langus oli 43,8%

Tõsielus seda tõenäoliselt ei juhtu. Igaüks ütleb teile, et pärast ülaltoodud toimingute tegemist loob Excel valemi \u003d C4 / B4-1. Minge tagasi lahtrisse D4 ja märkage valemiriba (joonis 4). Lihtsalt mingit kuradit! Lihtsat valemit \u003d C4 / B4-1 enam pole. Selle asemel asendab programm keerulise konstruktsiooni funktsiooniga GET.DATA.PUMP.TABLE. Miks annab see valem lahtris D4 õigeid tulemusi, kuid pärast kopeerimist allolevatesse lahtritesse keeldub see töötamast?

Iga kasutaja esimene reaktsioon juhtunule on järgmine: "Mis on see kummaline GET.DATA.PERSONAL.TABLE-konstruktsioon, mis mu aruande rikkus?" Enamik kasutajaid soovib sellest funktsioonist kohe lahti saada. Mõni küsib: "Miks Microsoft selle funktsiooni meile libistas?"

Midagi sellist polnud aastal exceli ajad 2000. Olles hakanud regulaarselt kokku puutuma funktsiooniga GET.DATA.PERSONAL.TABLES, ma lihtsalt vihkasin seda. Kui ühel seminaril keegi minult küsis, kuidas seda saaks eesmärgi heaks kasutada, olin ma tumm. Ma pole endale kunagi sellist küsimust esitanud! Minu ja enamiku Exceli kasutajate arvates oli funktsioon GET.DATA.PUMP.TABLE kurjuse produkt, millel polnud midagi pistmist hea jõududega. Õnneks on selle funktsiooni keelamiseks kaks võimalust.

Funktsiooni GET PIVOTTABLE DATA lukustamine valemi sisestamisega.Funktsiooni GET PIVOTTABLE ilmumise takistamiseks on lihtne viis. Selleks peate looma valemi ilma hiirt või kursorinuppe kasutamata. Järgige lihtsalt neid samme.

1. Minge lahtrisse D4 ja tippige \u003d (võrdusmärk).
2. Sisestage C4.
3. Sisestage a / (jagamiseks ettepoole kaldkriips).
4. Sisestage B4.
5. Sisestage -1.
6. Kliki Sisenema.

Olete nüüd loonud tavalise exceli valem, mida saab kopeerida veeru all olevatesse lahtritesse ja mille abil saate õigeid tulemusi (joonis 5). Nagu näete, saate luua valemeid PivotTable-välistest aladest, mis viitavad PivotTable-liigendtabeli andmetele. Ja kes ei usu, et see on võimalik, laske neil kirjeldatud toiminguid ise teha.

Joonis: 5. Sisestage lihtsalt klaviatuurilt \u003d C4 / B4-1 ja valem töötab vastavalt vajadusele

Mõned kasutajad tunnevad end valemite sisestamise tavapärases järjekorras ebamugavalt. Lisaks on pakutud variant töömahukam. Kui olete üks neist kasutajatest, on teine \u200b\u200bvõimalus teie jaoks ...

Keelake funktsioon GET PANEL.TABLE DATA.Funktsiooni GET PANEL.TABLE DATA saate jäädavalt keelata. Klõpsake menüü lindil Fail → Parameetrid... Avatud aknas ParameetridExcel mine panusele Valemid ja tühjendage valiku kõrval olev ruut Kasuta funktsiooniGetPivotData liigendtabeli linkide jaoks... Kliki Okei.

Alternatiivne võimalus. Klõpsake pöördetabelil ja ilmuvas kontekstuaalsel vahekaardil Analüüs klõpsake nupu kõrval oleval rippmenüül Parameetrid... Tühjendage märkeruut kõrval Looge GetPivotData (joonis 7). Märkeruut on vaikimisi sisse lülitatud.

Miks pakkus Microsoft meile funktsiooni GET PANEL.TABLES.Kui see funktsioon on nii kohutav, siis miks lubas Microsoft selle vaikimisi? Miks nad hoolivad selle funktsiooni toe säilitamisest Exceli uuemates versioonides? Kas nad on teadlikud kasutajate meelsusest? Ja me läheme edasi lõbusasse ossa ...

Rakenduse GET PIVOTTABLE DATA kasutamine pöördtabelite täiustamiseks

Pöördlauad on inimkonna suurepärane leiutis. Pöördtabel luuakse vaid mõne hiireklõpsuga, mis välistab vajaduse kasutada täpsemat filtrit, funktsiooni BDSUMM ja andmetabeleid. Pöördtabelite abil saate tohutute andmete hulgast luua ühe lehe aruandeid. Need eelised varjutavad pöördtabelite mõningaid puudusi, nagu nõrk vormindamine ja vajadus teisendada pöördtabelid väärtusteks täiendavaks kohandamiseks. Joonisel fig. Joonis 8 näitab tüüpilist pöördtabeli loomise protsessi. Sel juhul algab kõik algandmetest. Loome pöördtabeli ja kasutame selle kohandamiseks ja täiustamiseks kõiki võimalikke tehnikaid. Mõnikord teisendame pöördtabeli väärtusteks ja teeme lõpliku vormingu.

Rob Pley (Microsofti arendaja) välja pakutud ja hiljem arutatud uus PivotTable-metoodika on ülalkirjeldatud protsessi täiustuste tulemus. Sel juhul luuakse kõigepealt primitiivne pöördtabel. Te ei pea seda tabelit vormindama. Seejärel viiakse läbi üheastmeline suhteliselt aeganõudev protsess, et luua kenasti vormindatud kest, kuhu mahub lõpparuanne. Pärast seda kasutatakse funktsiooni GET.DATA.PUMP.TABLE kestas oleva aruande kiireks täitmiseks andmetega. Pärast uute andmete saamist saate need lehele panna, värskendada primitiivset pöördtabelit ja printida kestas olev aruanne (joonis 9). Sellel tehnikal on mitmeid vaieldamatuid eeliseid. Näiteks ei pea te muretsema aruande vormindamise pärast kohe pärast selle loomist. Pöördtabelite loomise protsess muutub peaaegu täielikult automatiseeritud.

Järgmistes jaotistes arutatakse, kuidas koostada dünaamiline aruanne, mis kuvab tegelikke andmeid eelmiste kuude kohta ja tulevaste kuude eesmärke.

Looge primitiivne pöördtabel.Esialgsed andmed (joonis 10) esitatakse tehingute kujul, mis sisaldavad teavet kavandatud ja tegelike näitajate kohta iga piirkonna kohta, kus on ettevõtte filiaalid. Planeeritud arvud on üksikasjalikud kuu tasemel, tegelikud arvud aga üksikute päevade tasemel. Kavandatud näitajad luuakse eelolevaks aastaks ja tegelikud näitajad viimaste kuude jaoks. Kuna aruannet värskendatakse iga kuu, on see protsess oluliselt lihtsustatud, kui PivotTable-liigendtabeli andmeallika suurus suureneb, kui uued andmed lisatakse põhja. Vananenud versioonides Exceli loomine sarnane andmeallikas rakendati nimelise dünaamilise vahemiku abil, kasutades funktsiooni OFFSET (vt üksikasju). Kui töötate rakenduses Excel 2013, valige lihtsalt üks andmerakk ja vajutage klahvikombinatsiooni Ctrl + T (tabeli loomine). See loob nimelise andmekogumi, mis laieneb automaatselt uute ridade ja veergude lisamisel.

Nüüd loome pöördtabeli. Funktsioon GET PIVOTTABLE DATA on piisavalt võimas, kuid see võib tagastada ainult need väärtused, mis kuvatakse tegelikus PivotTable-liigendtabelis. See funktsioon ei saa vahemälu skannida üksuste arvutamiseks, mida pole pöördtabelis.

Looge pöördtabel:

1. Valige Meeskond Sisesta → Pöördlaudja seejärel dialoogiboksis Pöördtabeli loomineklõpsake Okei.
2. Valige PivotTable-väljade loendis väli kuupäev... Kuupäevade loend kuvatakse pöördtabeli vasakule küljele (joonis 11).
3. Valige suvaline kuupäevarakk, näiteks A4. Kontekstuaalsel vahekaardil Analüüsasub kontekstuaalsete vahelehtede komplektis Pöördlaudadega töötamine, klõpsake nuppu Grupeerimine valdkonna järgi (Vaata detaile). Dialoogiboksis Grupeerimine valige suvand Kuud (joonis 12). Klõpsake nuppu Okei... Kuude nimed ilmuvad pöördtabeli vasakule küljele (joonis 13).
4. Lohistage väli kuupäev PivotTable-liigendtabeli veergude alale.
5. Lohistage väli Indeks PivotTable-väljade loendi veergude alale.
6. Valige väli Piirkondkuvatakse pöördtabeli vasakus veerus.
7. Valige väli Sissetulekmis kuvatakse alal PivotTable-liigendtabeli väärtused.

Joonis: 11. Alustage rühmitades välja järgi kuupäev

Siinkohal näeb meie pöördtabel välja üsna primitiivne (joonis 14). Mulle ei meeldi väga kirjad Ridade nimed ja Veergude nimed... Summa kohta on ebapraktiline kuvada Jan plaan ja Jan Fakt veerus D jne. Kuid ärge muretsege välimus seda pöördtabelit, sest peale teie ei näe seda keegi teine. Sellest hetkest alates loome aruande ümbrise, mille andmeallikaks saab äsja loodud pöördtabel.

Aruande koore loomine.Sisestage oma töövihikusse tühi leht. Jätame PivotTable-i tööriistad mõneks ajaks kõrvale ja liigume tavaliste Exceli tööriistade juurde. Meie ülesandeks on valemite ja vorminduse abil luua ilus aruanne, mida pole häbi juhile näidata.

Toimige järgmiselt (joonis 15).

1. Lahtrisse A1 sisestage aruande nimi - Planeeritud ja tegelikud näitajad piirkonniti.
2. Minge vahekaardile Kodu, klõpsake nuppu Lahtrite stiilid vali formaat Pealkiri 1.
3. Lahtrisse A2 sisestage valem \u003d EONTHS (TÄNA (); 0). See funktsioon tagastab jooksva kuu viimase päeva. Näiteks kui loete neid ridu 14. augustil 2014, kuvatakse lahtris A2 kuupäev 31. august 2014.
4. Valige lahter A2. Dialoogiboksi kuvamiseks vajutage klahvikombinatsiooni Ctrl + 1 Lahtrivorming... Vahekaardil Arv klõpsake üksusel Kõik formaadid... Sisestage kohandatud numbrivorming järgmiselt "Alates kuust" MMMM "kavandatud näitajad" (joonis 16). Selle tulemusena näeb arvutatud kuupäev välja nagu tekst.
5. Lahtrisse A5 sisestage pealkiri Piirkond.
6. Sisestage veeru A ülejäänud lahtritesse piirkonna päised. Piirkonna päised peavad vastama liigendtabelis näidatud piirkonna nimedele.
7. Vajaduse korral lisage veergu sildid osakondade kaupa.
8. Aruande lõpus lisage rida Ettevõtte jaoks kokku.
9. Lahtrisse B4 sisestage valem \u003d DATE (AASTA ($ A $ 2); VEERG (A1); 1). See valem tagastab kuupäevad 01/01/2014, 01/02/2104 jne, jooksva aasta kõigi 12 kuu esimesed päevad.
10. Valige lahter B4. Akna avamiseks vajutage klahvikombinatsiooni Ctrl + 1 Lahtrivorming... Vahekaardil Arv Peatükis Kõik formaadid sisestage kohandatud numbrivorming Mmm... Selles vormingus kuvatakse kolmetäheline kuu nimi. Joondage tekst lahtrist paremale.
11. Kopeerige lahtri B4 sisu vahemikku C4: M4. Pöördtabeli ülaosas kuvatakse rida kuude nimedega.
12. Lahtrisse B5 sisestage valem \u003d IF (KUU (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Faktning praeguse ja tuleviku jaoks - Plaan.
13. Lisage lahtrisse N5 pealkiri Tulemus... Lahtrisse O4 - Tulemus, O5 - Plaan, P5 - % hälve.
14. Sisestage tavapärased Exceli valemid, mida kasutatakse osakonna kogusummade, ettevõtte kogu rea, veeru üldkogumi ja variatsiooni% arvutamiseks:
    1. lahtrisse B8 sisestage valem \u003d SUM (B6: B7) ja kopeerige see rea teistele lahtritele;
    2. lahtrisse N6 sisestage valem \u003d SUM (B6: M6) ja kopeerige see veeru teistesse lahtritesse;
    3. lahtrisse P6 sisestage valem \u003d IFERROR ((N6 / O10) -1; 0) ja kopeerige see veeru teistesse lahtritesse;
    4. lahtrisse B13 sisestage valem \u003d SUM (B10: B12) ja kopeerige see rea teistele lahtritele;
    5. lahtrisse B17 sisestage valem \u003d SUM (B15: B16) ja kopeerige see rea teistele lahtritele;
    6. lahtrisse B19 sisestage valem \u003d SUM (B6: B18) / 2 ja kopeerige see rea muudesse lahtritesse.
15. Rakendage pealkirja 4 stiil veerus A olevatele siltidele ja 4. ja 5. rea pealkirjadele.
16. Lahtrite B6: O19 vahemiku jaoks valige numbrivorming # ## 0.
17. Valige veerus P olevate lahtrite jaoks arvuvorming 0,0%.

Nii oleme joonisel fig. 15. See aruanne sisaldab kõiki nõutavaid vorminguid. Järgmine jaotis näitab, kuidas funktsiooni GET PIVOTTABLE DATA saada aruande täitmiseks.

Joonis: 15. Enne valemite GET.DATA.PERSONAL.TABLES lisamist teavitage pakendit

Funktsiooni GET PIVOTTABLE DATA abil aruandekesta andmete täitmiseks.Nüüdsest saate kogeda funktsiooni GET PANEL.TABLE DATA kasutamise kõiki eeliseid. Kui olete selle funktsiooni lubanud märkeruudu tühjendanud, naaske vastava sätte juurde ja naaske märkeruut (vt kirjeldust joonistel 6 või 7).

Valige aruandekesta lahter B6. See lahter vastab Kirde piirkonnale ja jaanuari tegelikele näitajatele.

1. Valemi sisestamise alustamiseks sisestage \u003d (võrdusmärk).
2. Minge pöördtabeli lehele ja klõpsake lahtrit, mis vastab kirdepiirkonnale ja jaanuari tegelikele näitajatele - C12 (joonis 17).
3. Vajutage klahvi Sisenemavalemi sisestamise lõpetamiseks ja aruande kestale naasmiseks. Selle tulemusena lisab Excel lahtrisse B6 funktsiooni GET PIVOTTABLE DATA. Lahtris kuvatakse väärtus 277 435 dollarit.

Pidage seda numbrit meeles, kuna see on vajalik valemi tulemustega võrdlemisel, mida muudate hiljem. Programmi loodud valemil on järgmine vorm: \u003d GET.DATA.PUMP.TABLE ("Sissetulek"; 'Joon. 11-14'! $ A $ 3; "Piirkond"; "Kirde"; "Kuupäev" ; 1; "Indikaator"; "Fakt"). Kui olete seni eiranud funktsiooni GET PIVOTTABLE DATA, on aeg seda lähemalt uurida. Joonisel fig. 18 seda valemit kuvatakse redigeerimisrežiimis koos vihjega.

Funktsiooni argumendid:

• Andmete väli. Välja pöördtabeli väärtuspiirkonnast. Pange tähele: sel juhul kasutatakse välja Sissetulek, kuid mitte Väljal Sissetulek summa.
• Pivot_tabel. Selle parameetriga küsib Microsoft teilt: "Millist pöördtabelit soovite kasutada?" Piisab ühe liigendtabeli lahtrite määramisest. Kanne „Joon. 11-14 '! $ A $ 3 viitab pöördtabeli esimesele lahtrile, kuhu andmed sisestatakse. Kuna meie puhul saate määrata mis tahes pöördetabeliga seotud lahtri, jätke argument muutmata. Lahtri aadress $ A $ 3 sobib igati.
• 1. väli; element 1. Automaatselt loodud valemis valitakse välja nimi Piirkondja välja väärtusena - Kirdes... Siin peitub funktsiooniga GET DATA.PUMP.TABLE töötamisel tekkivate probleemide põhjus. Automaatvalitud väärtusi ei saa kopeerida, kuna need on kõvakodeeritud. Seega, kui kopeerite valemeid kogu aruandealale, peate neid käsitsi muutma. Asendage kirdeosa lahtriviitega kujul $ A6. Pange dollari märk veeru A ette, saate valemi veeru lahtritesse kopeerimisel kindlaks teha, kas viite reaosa saab muuta.
• 2. väli; punkt 2. See argumentide paar määratleb välja kuupäev väärtusega 1. Kui algne PivotTable-liigendtabel oli rühmitatud kuude järgi, jääb kuu väljale algne välja nimi kuupäev... Kuu arvuline väärtus on 1, mis vastab jaanuarile. Vaevalt on soovitatav sellist väärtust kasutada tohutute valemite loomisel, mis on seatud kümnetesse või isegi sadadesse aruande lahtritesse. Parem on kasutada valemit, mis arvutab välja väärtused kuupäev, nagu lahtris B4 olev valem. Sellisel juhul võite 1 asemel kasutada valemit KUU (4 dollarites). Numbrile 4 eelnev dollari märk näitab, et valemiga saab väljale väärtusi omistada kuupäev põhineb teistel kuudel, kuna valem kopeeritakse rea lahtritesse.
• 3. väli; element 3. Sel juhul määratakse välja nimi automaatselt Indeks ja välja väärtus Fakt... Need väärtused on jaanuari kohta õiged, kuid järgnevatel kuudel tuleb välja väärtuseks muuta plaan. Muutke välja kodeeritud väärtust Fakt lingile B $ 5.
• 4. väli; Element 4. Neid argumente ei kasutata, kuna põllud on läbi.

Uus valem on näidatud joonisel fig. 19. Minutiga loodi ühe väärtusega töötamiseks mõeldud kodeeritud valemi asemel paindlik valem, mida saab kopeerida kõikidesse andmekogumi lahtritesse. Vajutage klahvi Sisenemaja saate sama tulemuse nagu enne valemi redigeerimist. Muudetud valemil on järgmine vorm: \u003d GET.PUMP.TABLE DATA ("Sissetulek"; 'Joon. 11-14'! $ A $ 3; "Piirkond"; $ A6; "Kuupäev"; KUU (B $ 4) ; "Indikaator"; B $ 5)

Joonis: 19. Pärast redigeerimise lõppu sobib valem GET.DATA.PERSONAL.TABLE kopeerimiseks kõikidesse vahemiku lahtritesse

Kopeerige valem tühjadesse lahtritesse veergudes B: M, kus arvutatakse tulemused. Nüüd, kui aruanne sisaldab reaalarvusid, saate veergude laiuseid lõplikult kohandada.

Järgmises etapis seadistame lõplike sihtmärkide arvutamiseks valemi GET.PUMP.TABLE. Kui kopeerite valemi lihtsalt lahtrisse O6, kuvatakse tõrketeade #REF! Selle vea põhjuseks on see sõna Tulemus lahtris O4 pole kuu nimi. Funktsiooni GET PIVOTTABLE DATA õige toimimise tagamiseks peab nõutav väärtus olema PivotTable-liigendtabelis. Kuid kuna algses pöördetabelis on väli Indeks on veergude ala teine \u200b\u200bandmeväli Plaani kokkuvõte tegelikult puudub. Teisaldage väli Indeks nii et see saab veerualal esimeseks (joonis 20).

Joonis: 20. Reguleerige veerualal olevate väljade paigutust nii, et kuvatakse veerg Üldplaan

Võrdle joonisega fig. 14. Seal veeru piirkonnas oli esimene väli kuupäev, mis tõi kaasa asjaolu, et algul rühmitati veerud kuupäeva järgi ja iga kuu jooksul kava / fakti järgi. Nüüd on esimene väli Indikaator ja kokkuvõttes on veerud esimesed Plaan, sees sorditud kuude järgi ja seejärel kõik veerud Fakt.

Tulles tagasi aruande ümbriku lehele, seiske lahtris O6, tippige \u003d (võrdusmärk) ja pöörduge pöördtabeli lehel lahtrisse N12, mis vastab Kirde piirkonna kavandatud tulemustele. Kliki Sisenema... Tulemuseks on valem \u003d GET.DATA.PERSONAL.TABLES ("Sissetulek"; "Joon. 11-14"! $ A $ 3; "Piirkond"; "Kirde"; "Indikaator"; "Plaan"). Muutke seda: \u003d SAADA. ANDMED.KOKKUVÕTE.TABELID ("Sissetulek"; ’Joon. 11-14 ′! $ A $ 3;„ Piirkond “; $ A6;„ Indikaator “; O $ 5). Kopeerige see valem veeru O teistesse lahtritesse (joonis 21). Pange tähele, et isegi kui liigutate PivotTable-aruande erinevaid alasid, töötab ümbris õigesti. Muidugi, kui muudate mõne pöördvälja passiivseks, ei saa shell sellega hakkama ...

Joonis: 21. Lõpparuanne, mida saab juhile esitada

Teil on nüüd hästi vormindatud aruandekomplekt, mis kasutab dünaamilise PivotTable-liigendtabeli väärtusi. Kuigi aruande esialgne loomine võttis üsna kaua aega, kulus selle värskendamiseks vaid mõni minut.

Aruande värskendamine.Aruande värskendamiseks tulevaste kuude andmetega toimige järgmiselt.

1. Sisestage tegelikud arvud algse andmekogumi alla. Kuna lähteandmed on tabelivormingus, levitatakse tabeli vormindamine automaatselt uutesse andmeridadesse. Samuti laiendab see algse PivotTable-liigendtabeli määratlust (Exceli failis lisasin juba kogu aasta tegelikud arvud).
2. Minge pöördtabelisse. Paremklõpsake ja valige Värskenda... Pöördtabeli välimus muutub, kuid see on okei.
3. Minge aruande kestale. Põhimõtteliselt on aruande värskendamiseks kõik juba tehtud, kuid tulemuste testimine ei tee paha. Muutke lahtri A2 valem näiteks järgmiseks: \u003d EON MONTHS (TODAY () +31 ; 0) ja vaadake, mis juhtub.

Lisades iga kuu uusi tegelikke müügiandmeid, ei pea te muretsema vormingute, valemite jms loomise pärast. Kirjeldatud aruande värskendamise protsess on nii lihtne, et unustate igaveseks igakuised aruannete koostamisel tekkinud probleemid. Ainus probleem võib tekkida ettevõtte ümberkorraldamisel, mille tulemusena võivad pöördtabelisse ilmuda uued piirkonnad. Valemite korrektse töö tagamiseks kontrollige, kas teie aruande kokkuvõte vastab pöördtabeli koguarvule. Uue piirkonna ilmumisel lisage see lihtsalt pakitud lehele ja lohistage sobivad valemid.

Ma ei arvanud, et ütlen kunagi järgmist: „Funktsioon GET.DATA.PUMP.TABLE on suurim õnnistus. Kuidas me enne teda eksisteerisime? "

Originaalis olid Jelena algandmed korraldatud nii, et edasised valemid töötasid õigesti alles 2015. aasta juulis. Selle märkuse juurde lisatud Exceli failis muutsin nii algandmeid kui ka mõningaid valemeid nii, et kõik toimis, olenemata kuupäevast, millal katsetate lisatud Exceli faili. Paraku pidid valemid olema keerulised.

Varem rääkisin juba sellest, et pöördtabeli lahtrile viitamisel tagastatakse normaalse lingi asemel funktsioon GET.PIVOTTABLE DATA (vt). Kui olete huvitatud as selle ebamugavuse ületamiseks soovitan viidata nimetatud märkusele. Kui te ei tea, miks nii see juhtub ja ka millised on funktsiooni GET PANEELI ANDMED. TABEL positiivsed küljed, siis pakun fragmendi raamatust Jelen, Alexander. (15. peatükk). Vaadeldav tehnika võimaldab teil toime tulla paljude probleemidega, mis põhjustavad pöördtabelite kasutajatele peavalu, eelkõige:

• PivotTable-liigendtabeli värskendamisel kaob varem rakendatud vormindus. Numbrivormingud on kadunud. Veeru laiuse reguleerimise tulemused kaovad.
• Asümmeetrilise pöördtabeli loomiseks pole lihtsat viisi. Ainus võimalus on kasutada nimega komplekte, kuid see meetod on saadaval ainult neile, kes kasutavad andmemudeli pöördtabeleid, mitte tavalisi pöördtabeleid.
• Excel ei mäleta malle. Kui peate PivotTable-liigendtabeleid uuesti ja uuesti looma, peate uuesti rühmitama, rakendama arvutatud väljad ja liikmed ning tegema mitmeid muid sarnaseid toiminguid.

Tegelikult pole kõik siin kirjeldatud asi uus. Pealegi on sarnaseid võtteid kasutatud alates Excel 2002. Kuid minu suhtlus kasutajatega näitab, et vähem kui 1% on nendega tuttavad. Ainus küsimus, mis kasutajatel on, on see, kuidas välja lülitada veider funktsioon PANEELI TABELI ANDMED. Kahju…

Laadige märkus alla vormingus või näited vormingus

Noh, alustame järjekorras.

Kuidas loobuda probleemsest funktsioonist GET DATA. ISIKLIKUD TABELID

Funktsioon GET PIVOTTABLE DATA on juba pikka aega olnud paljude kasutajate peavalu. Ühtäkki muutus ilma ühegi hoiatuseta PivotTable-liigendtabelite käitumine programmis Excel 2002. Niipea, kui hakkate looma valemeid väljaspool PivotTable-liigendtabelit, mis viitavad selle andmetele, tuleb see funktsioon välja kusagilt.

Oletame, et joonisel fig. 1, peate võrdlema 2015. ja 2014. aasta andmeid.

Joonis: 1. Algne pöördlaud

1. Lisage lahtrisse D3 pealkiri "% Height".
2. Kopeerige vorming lahtrist C3 lahtrisse D3.
3. Sisestage lahtrisse D4 võrdusmärk.
4. Klõpsake lahtril C4.
5. Sisestage jagamistoimingu tähistamiseks märk (/ kaldkriips)
6. Klõpsake lahtril B4.
7. Sisestage -1 ja vajutage klahvikombinatsiooni jääda samasse kambrisse. Vormindage tulemus protsentides. Näete, et läänes kasvas sissetulek 43,8% (joonis 2). Pole eriti head tulemused.
8. Kui olete oma esimese valemi sisestanud, valige lahter D4.
9. Topeltklõpsake lahtril paremas alanurgas asuvat väikest ruutu. See ruut tähistab täitekäepidet, mille abil saate aruande terve veeru täitmiseks valemi kopeerida.

Kui olete valemi kopeerimise lõpetanud ja pilgu ekraanile heitnud, saate aru, et midagi on valesti - iga piirkonna langus oli aastaga 43,8% (joonis 3).

Joonis: 3. Kui olete valemi kõigisse veeru lahtritesse kopeerinud, näete, et iga piirkonna langus oli 43,8%

Tõsielus seda tõenäoliselt ei juhtu. Igaüks ütleb teile, et pärast ülaltoodud toimingute tegemist loob Excel valemi \u003d C4 / B4-1. Minge tagasi lahtrisse D4 ja märkage valemiriba (joonis 4). Lihtsalt mingit kuradit! Lihtsat valemit \u003d C4 / B4-1 enam pole. Selle asemel asendab programm keerulise konstruktsiooni funktsiooniga GET.DATA.PUMP.TABLE. Miks annab see valem lahtris D4 õigeid tulemusi, kuid pärast kopeerimist allolevatesse lahtritesse keeldub see töötamast?

Iga kasutaja esimene reaktsioon juhtunule on järgmine: "Mis on see kummaline GET.DATA.PERSONAL.TABLE-konstruktsioon, mis mu aruande rikkus?" Enamik kasutajaid soovib sellest funktsioonist kohe lahti saada. Mõni küsib: "Miks Microsoft selle funktsiooni meile libistas?"

Excel 2000 päevil midagi sellist ei juhtunud. Kui hakkasin regulaarselt GETDATA.PERSONAL.TABLES-iga kokku puutuma, vihkasin seda lihtsalt. Kui ühel seminaril keegi minult küsis, kuidas seda saaks eesmärgi heaks kasutada, olin ma tumm. Ma pole kunagi sellist küsimust esitanud! Minu ja enamiku Exceli kasutajate arvates oli funktsioon GET.DATA.PUMP.TABLE kurjuse produkt, millel polnud midagi pistmist hea jõududega. Õnneks on selle funktsiooni keelamiseks kaks võimalust.

Funktsiooni GET PIVOTTABLE DATA lukustamine valemi sisestamisega.Funktsiooni GET PIVOTTABLE ilmumise takistamiseks on lihtne viis. Selleks peate looma valemi ilma hiirt või kursorinuppe kasutamata. Järgige lihtsalt neid samme.

1. Minge lahtrisse D4 ja tippige \u003d (võrdusmärk).
2. Sisestage C4.
3. Sisestage a / (jagamiseks ettepoole kaldkriips).
4. Sisestage B4.
5. Sisestage -1.
6. Kliki Sisenema.

Nüüd olete loonud tavalise Exceli valemi, mille saate korrektsete tulemuste saamiseks kopeerida allolevatesse veergude lahtritesse (joonis 5). Nagu näete, saate luua valemeid PivotTable-välistest aladest, mis viitavad PivotTable-liigendtabeli andmetele. Ja kes ei usu, et see on võimalik, laske neil kirjeldatud toiminguid ise teha.

Joonis: 5. Sisestage lihtsalt klaviatuurilt \u003d C4 / B4-1 ja valem töötab vastavalt vajadusele

Mõned kasutajad tunnevad end valemite sisestamise tavapärases järjekorras ebamugavalt. Lisaks on pakutud variant töömahukam. Kui olete üks neist kasutajatest, on teine \u200b\u200bvõimalus teie jaoks ...

Keelake funktsioon GET PANEL.TABLE DATA.Funktsiooni GET PANEL.TABLE DATA saate jäädavalt keelata. Klõpsake menüü lindil Fail → Parameetrid... Avatud aknas ParameetridExcel mine panusele Valemid ja tühjendage valiku kõrval olev ruut Kasuta funktsiooniGetPivotData liigendtabeli linkide jaoks... Kliki Okei.

Alternatiivne võimalus. Klõpsake pöördetabelil ja ilmuvas kontekstuaalsel vahekaardil Analüüs klõpsake nupu kõrval oleval rippmenüül Parameetrid... Tühjendage märkeruut kõrval Looge GetPivotData (joonis 7). Märkeruut on vaikimisi sisse lülitatud.

Miks pakkus Microsoft meile funktsiooni GET PANEL.TABLES.Kui see funktsioon on nii kohutav, siis miks lubas Microsoft selle vaikimisi? Miks nad hoolivad selle funktsiooni toe säilitamisest Exceli uuemates versioonides? Kas nad on teadlikud kasutajate meelsusest? Ja me läheme edasi lõbusasse ossa ...

Rakenduse GET PIVOTTABLE DATA kasutamine pöördtabelite täiustamiseks

Pöördlauad on inimkonna suurepärane leiutis. Pöördtabel luuakse vaid mõne hiireklõpsuga, mis välistab vajaduse kasutada täpsemat filtrit, funktsiooni BDSUMM ja andmetabeleid. Pöördtabelite abil saate tohutute andmete hulgast luua ühe lehe aruandeid. Need eelised varjutavad pöördtabelite mõningaid puudusi, nagu nõrk vormindamine ja vajadus teisendada pöördtabelid väärtusteks täiendavaks kohandamiseks. Joonisel fig. Joonis 8 näitab tüüpilist pöördtabeli loomise protsessi. Sel juhul algab kõik algandmetest. Loome pöördtabeli ja kasutame selle kohandamiseks ja täiustamiseks kõiki võimalikke tehnikaid. Mõnikord teisendame pöördtabeli väärtusteks ja teeme lõpliku vormingu.

Rob Pley (Microsofti arendaja) välja pakutud ja hiljem arutatud uus PivotTable-metoodika on ülalkirjeldatud protsessi täiustuste tulemus. Sel juhul luuakse kõigepealt primitiivne pöördtabel. Te ei pea seda tabelit vormindama. Seejärel viiakse läbi üheastmeline suhteliselt aeganõudev protsess, et luua kenasti vormindatud kest, kuhu mahub lõpparuanne. Pärast seda kasutatakse funktsiooni GET.DATA.PUMP.TABLE kestas oleva aruande kiireks täitmiseks andmetega. Pärast uute andmete saamist saate need lehele panna, värskendada primitiivset pöördtabelit ja printida kestas olev aruanne (joonis 9). Sellel tehnikal on mitmeid vaieldamatuid eeliseid. Näiteks ei pea te muretsema aruande vormindamise pärast kohe pärast selle loomist. Pöördtabelite loomise protsess muutub peaaegu täielikult automatiseeritud.

Järgmistes jaotistes arutatakse, kuidas koostada dünaamiline aruanne, mis kuvab tegelikke andmeid eelmiste kuude kohta ja tulevaste kuude eesmärke.

Looge primitiivne pöördtabel.Esialgsed andmed (joonis 10) esitatakse tehingute vormis, mis sisaldab teavet kavandatud ja tegelike näitajate kohta iga piirkonna kohta, kus on ettevõtte filiaalid. Planeeritud arvud on üksikasjalikud kuu tasemel, tegelikud arvud aga üksikute päevade tasemel. Kavandatud näitajad luuakse eelolevaks aastaks ja tegelikud näitajad viimaste kuude jaoks. Kuna aruannet värskendatakse iga kuu, on see protsess oluliselt lihtsustatud, kui PivotTable-liigendtabeli andmeallika suurus suureneb, kui uued andmed lisatakse põhja. Exceli vanemates versioonides loodi selline andmeallikas nimelise dünaamilise vahemiku abil, kasutades funktsiooni OFFSET (vt üksikasju). Kui töötate rakenduses Excel 2013, valige lihtsalt üks andmerakk ja vajutage klahvikombinatsiooni Ctrl + T (tabeli loomine). See loob nimelise andmekogumi, mis laieneb automaatselt uute ridade ja veergude lisamisel.

Nüüd loome pöördtabeli. Funktsioon GET PIVOTTABLE DATA on piisavalt võimas, kuid see võib tagastada ainult need väärtused, mis kuvatakse tegelikus PivotTable-liigendtabelis. See funktsioon ei saa vahemälu skannida üksuste arvutamiseks, mida pole pöördtabelis.

Looge pöördtabel:

1. Valige Meeskond Sisesta → Pöördlaudja seejärel dialoogiboksis Pöördtabeli loomineklõpsake Okei.
2. Valige PivotTable-väljade loendis väli kuupäev... Kuupäevade loend kuvatakse pöördtabeli vasakule küljele (joonis 11).
3. Valige suvaline kuupäevarakk, näiteks A4. Kontekstuaalsel vahekaardil Analüüsasub kontekstuaalsete vahelehtede komplektis Pöördlaudadega töötamine, klõpsake nuppu Grupeerimine valdkonna järgi (Vaata detaile). Dialoogiboksis Grupeerimine valige suvand Kuud (joonis 12). Klõpsake nuppu Okei... Kuude nimed ilmuvad pöördtabeli vasakule küljele (joonis 13).
4. Lohistage väli kuupäev PivotTable-liigendtabeli veergude alale.
5. Lohistage väli Indeks PivotTable-väljade loendi veergude alale.
6. Valige väli Piirkondkuvatakse pöördtabeli vasakus veerus.
7. Valige väli Sissetulekmis kuvatakse alal PivotTable-liigendtabeli väärtused.

Joonis: 11. Alustage rühmitades välja järgi kuupäev

Siinkohal näeb meie pöördtabel välja üsna primitiivne (joonis 14). Mulle ei meeldi väga kirjad Ridade nimed ja Veergude nimed... Summa kohta on ebapraktiline kuvada Jan plaan ja Jan Fakt veerus D jne. Kuid ärge muretsege selle pöördtabeli välimuse pärast, sest keegi teine \u200b\u200bei näe seda peale teie. Sellest hetkest alates loome aruande ümbrise, mille andmeallikaks saab äsja loodud pöördtabel.

Aruande koore loomine.Sisestage oma töövihikusse tühi leht. Jätame PivotTable-i tööriistad mõneks ajaks kõrvale ja liigume tavaliste Exceli tööriistade juurde. Meie ülesandeks on valemite ja vorminduse abil luua ilus aruanne, mida pole häbi juhile näidata.

Toimige järgmiselt (joonis 15).

1. Lahtrisse A1 sisestage aruande nimi - Planeeritud ja tegelikud näitajad piirkonniti.
2. Minge vahekaardile Kodu, klõpsake nuppu Lahtrite stiilid vali formaat Pealkiri 1.
3. Lahtrisse A2 sisestage valem \u003d EONTHS (TÄNA (); 0). See funktsioon tagastab jooksva kuu viimase päeva. Näiteks kui loete neid ridu 14. augustil 2014, kuvatakse lahtris A2 kuupäev 31. august 2014.
4. Valige lahter A2. Dialoogiboksi kuvamiseks vajutage klahvikombinatsiooni Ctrl + 1 Lahtrivorming... Vahekaardil Arv klõpsake üksusel Kõik formaadid... Sisestage kohandatud numbrivorming järgmiselt "Alates kuust" MMMM "kavandatud näitajad" (joonis 16). Selle tulemusena näeb arvutatud kuupäev välja nagu tekst.
5. Lahtrisse A5 sisestage pealkiri Piirkond.
6. Sisestage veeru A ülejäänud lahtritesse piirkonna päised. Piirkonna päised peavad vastama liigendtabelis näidatud piirkonna nimedele.
7. Vajaduse korral lisage veergu sildid osakondade kaupa.
8. Aruande lõpus lisage rida Ettevõtte jaoks kokku.
9. Lahtrisse B4 sisestage valem \u003d DATE (AASTA ($ A $ 2); VEERG (A1); 1). See valem tagastab kuupäevad 01/01/2014, 01/02/2104 jne, jooksva aasta kõigi 12 kuu esimesed päevad.
10. Valige lahter B4. Akna avamiseks vajutage klahvikombinatsiooni Ctrl + 1 Lahtrivorming... Vahekaardil Arv Peatükis Kõik formaadid sisestage kohandatud numbrivorming Mmm... Selles vormingus kuvatakse kolmetäheline kuu nimi. Joondage tekst lahtrist paremale.
11. Kopeerige lahtri B4 sisu vahemikku C4: M4. Pöördtabeli ülaosas kuvatakse rida kuude nimedega.
12. Lahtrisse B5 sisestage valem \u003d IF (KUU (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Faktning praeguse ja tuleviku jaoks - Plaan.
13. Lisage lahtrisse N5 pealkiri Tulemus... Lahtrisse O4 - Tulemus, O5 - Plaan, P5 - % hälve.
14. Sisestage tavapärased Exceli valemid, mida kasutatakse osakonna kogusummade, ettevõtte kogu rea, veeru üldkogumi ja variatsiooni% arvutamiseks:
    1. lahtrisse B8 sisestage valem \u003d SUM (B6: B7) ja kopeerige see rea teistele lahtritele;
    2. lahtrisse N6 sisestage valem \u003d SUM (B6: M6) ja kopeerige see veeru teistesse lahtritesse;
    3. lahtrisse P6 sisestage valem \u003d IFERROR ((N6 / O10) -1; 0) ja kopeerige see veeru teistesse lahtritesse;
    4. lahtrisse B13 sisestage valem \u003d SUM (B10: B12) ja kopeerige see rea teistele lahtritele;
    5. lahtrisse B17 sisestage valem \u003d SUM (B15: B16) ja kopeerige see rea teistele lahtritele;
    6. lahtrisse B19 sisestage valem \u003d SUM (B6: B18) / 2 ja kopeerige see rea muudesse lahtritesse.
15. Rakendage pealkirja 4 stiil veerus A olevatele siltidele ja 4. ja 5. rea pealkirjadele.
16. Lahtrite B6: O19 vahemiku jaoks valige numbrivorming # ## 0.
17. Valige veerus P olevate lahtrite jaoks arvuvorming 0,0%.

Nii oleme joonisel fig. 15. See aruanne sisaldab kõiki nõutavaid vorminguid. Järgmine jaotis näitab, kuidas funktsiooni GET PIVOTTABLE DATA saada aruande täitmiseks.

Joonis: 15. Enne valemite GET.DATA.PERSONAL.TABLES lisamist teavitage pakendit

Funktsiooni GET PIVOTTABLE DATA abil aruandekesta andmete täitmiseks.Nüüdsest saate kogeda funktsiooni GET PANEL.TABLE DATA kasutamise kõiki eeliseid. Kui olete selle funktsiooni lubanud märkeruudu tühjendanud, naaske vastava sätte juurde ja naaske märkeruut (vt kirjeldust joonistel 6 või 7).

Valige aruandekesta lahter B6. See lahter vastab Kirde piirkonnale ja jaanuari tegelikele näitajatele.

1. Valemi sisestamise alustamiseks sisestage \u003d (võrdusmärk).
2. Minge pöördtabeli lehele ja klõpsake lahtrit, mis vastab kirdepiirkonnale ja jaanuari tegelikele näitajatele - C12 (joonis 17).
3. Vajutage klahvi Sisenemavalemi sisestamise lõpetamiseks ja aruande kestale naasmiseks. Selle tulemusena lisab Excel lahtrisse B6 funktsiooni GET PIVOTTABLE DATA. Lahtris kuvatakse väärtus 277 435 dollarit.

Pidage seda numbrit meeles, kuna see on vajalik valemi tulemustega võrdlemisel, mida muudate hiljem. Programmi loodud valemil on järgmine vorm: \u003d GET.DATA.PUMP.TABLE ("Sissetulek"; 'Joon. 11-14'! $ A $ 3; "Piirkond"; "Kirde"; "Kuupäev" ; 1; "Indikaator"; "Fakt"). Kui olete seni eiranud funktsiooni GET PIVOTTABLE DATA, on aeg seda lähemalt uurida. Joonisel fig. 18 seda valemit kuvatakse redigeerimisrežiimis koos vihjega.

Funktsiooni argumendid:

• Andmete väli. Välja pöördtabeli väärtuspiirkonnast. Pange tähele: sel juhul kasutatakse välja Sissetulek, kuid mitte Väljal Sissetulek summa.
• Pivot_tabel. Selle parameetriga küsib Microsoft teilt: "Millist pöördtabelit soovite kasutada?" Piisab ühe liigendtabeli lahtrite määramisest. Kanne „Joon. 11-14 '! $ A $ 3 viitab pöördtabeli esimesele lahtrile, kuhu andmed sisestatakse. Kuna meie puhul saate määrata mis tahes pöördetabeliga seotud lahtri, jätke argument muutmata. Lahtri aadress $ A $ 3 sobib igati.
• 1. väli; element 1. Automaatselt loodud valemis valitakse välja nimi Piirkondja välja väärtusena - Kirdes... Siin peitub funktsiooniga GET DATA.PUMP.TABLE töötamisel tekkivate probleemide põhjus. Automaatvalitud väärtusi ei saa kopeerida, kuna need on kõvakodeeritud. Seega, kui kopeerite valemeid kogu aruandealale, peate neid käsitsi muutma. Asendage kirdeosa lahtriviitega kujul $ A6. Pange dollari märk veeru A ette, saate valemi veeru lahtritesse kopeerimisel kindlaks teha, kas viite reaosa saab muuta.
• 2. väli; punkt 2. See argumentide paar määratleb välja kuupäev väärtusega 1. Kui algne PivotTable-liigendtabel oli rühmitatud kuude järgi, jääb kuu väljale algne välja nimi kuupäev... Kuu arvuline väärtus on 1, mis vastab jaanuarile. Vaevalt on soovitatav sellist väärtust kasutada tohutute valemite loomisel, mis on seatud kümnetesse või isegi sadadesse aruande lahtritesse. Parem on kasutada valemit, mis arvutab välja väärtused kuupäev, nagu lahtris B4 olev valem. Sellisel juhul võite 1 asemel kasutada valemit KUU (4 dollarites). Numbrile 4 eelnev dollari märk näitab, et valemiga saab väljale väärtusi omistada kuupäev põhineb teistel kuudel, kuna valem kopeeritakse rea lahtritesse.
• 3. väli; element 3. Sel juhul määratakse välja nimi automaatselt Indeks ja välja väärtus Fakt... Need väärtused on jaanuari kohta õiged, kuid järgnevatel kuudel tuleb välja väärtuseks muuta plaan. Muutke välja kodeeritud väärtust Fakt lingile B $ 5.
• 4. väli; Element 4. Neid argumente ei kasutata, kuna põllud on läbi.

Uus valem on näidatud joonisel fig. 19. Minutiga loodi ühe väärtusega töötamiseks mõeldud kodeeritud valemi asemel paindlik valem, mida saab kopeerida kõikidesse andmekogumi lahtritesse. Vajutage klahvi Sisenemaja saate sama tulemuse nagu enne valemi redigeerimist. Muudetud valemil on järgmine vorm: \u003d GET.PUMP.TABLE DATA ("Sissetulek"; 'Joon. 11-14'! $ A $ 3; "Piirkond"; $ A6; "Kuupäev"; KUU (B $ 4) ; "Indikaator"; B $ 5)

Joonis: 19. Pärast redigeerimise lõppu sobib valem GET.DATA.PERSONAL.TABLE kopeerimiseks kõikidesse vahemiku lahtritesse

Kopeerige valem tühjadesse lahtritesse veergudes B: M, kus arvutatakse tulemused. Nüüd, kui aruanne sisaldab reaalarvusid, saate veergude laiuseid lõplikult kohandada.

Järgmises etapis seadistame lõplike sihtmärkide arvutamiseks valemi GET.PUMP.TABLE. Kui kopeerite valemi lihtsalt lahtrisse O6, kuvatakse tõrketeade #REF! Selle vea põhjuseks on see sõna Tulemus lahtris O4 pole kuu nimi. Funktsiooni GET PIVOTTABLE DATA õige toimimise tagamiseks peab nõutav väärtus olema PivotTable-liigendtabelis. Kuid kuna algses pöördetabelis on väli Indeks on veergude ala teine \u200b\u200bandmeväli Plaani kokkuvõte tegelikult puudub. Teisaldage väli Indeks nii et see saab veerualal esimeseks (joonis 20).

Joonis: 20. Reguleerige veerualal olevate väljade paigutust nii, et kuvatakse veerg Üldplaan

Võrdle joonisega fig. 14. Seal veeru piirkonnas oli esimene väli kuupäev, mis tõi kaasa asjaolu, et algul rühmitati veerud kuupäeva järgi ja iga kuu jooksul kava / fakti järgi. Nüüd on esimene väli Indikaator ja kokkuvõttes on veerud esimesed Plaan, sees sorditud kuude järgi ja seejärel kõik veerud Fakt.

Tulles tagasi aruande ümbriku lehele, seiske lahtris O6, tippige \u003d (võrdusmärk) ja pöörduge pöördtabeli lehel lahtrisse N12, mis vastab Kirde piirkonna kavandatud tulemustele. Kliki Sisenema... Tulemuseks on valem \u003d GET.DATA.PERSONAL.TABLES ("Sissetulek"; "Joon. 11-14"! $ A $ 3; "Piirkond"; "Kirde"; "Indikaator"; "Plaan"). Muutke seda: \u003d SAADA. ANDMED.KOKKUVÕTE.TABELID ("Sissetulek"; ’Joon. 11-14 ′! $ A $ 3;„ Piirkond “; $ A6;„ Indikaator “; O $ 5). Kopeerige see valem veeru O teistesse lahtritesse (joonis 21). Pange tähele, et isegi kui liigutate PivotTable-aruande erinevaid alasid, töötab ümbris õigesti. Muidugi, kui muudate mõne pöördvälja passiivseks, ei saa shell sellega hakkama ...

Joonis: 21. Lõpparuanne, mida saab juhile esitada

Teil on nüüd hästi vormindatud aruandekomplekt, mis kasutab dünaamilise PivotTable-liigendtabeli väärtusi. Kuigi aruande esialgne loomine võttis üsna kaua aega, kulus selle värskendamiseks vaid mõni minut.

Aruande värskendamine.Aruande värskendamiseks tulevaste kuude andmetega toimige järgmiselt.

1. Sisestage tegelikud arvud algse andmekogumi alla. Kuna lähteandmed on tabelivormingus, levitatakse tabeli vormindamine automaatselt uutesse andmeridadesse. Samuti laiendab see algse PivotTable-liigendtabeli määratlust (Exceli failis lisasin juba kogu aasta tegelikud arvud).
2. Minge pöördtabelisse. Paremklõpsake ja valige Värskenda... Pöördtabeli välimus muutub, kuid see on okei.
3. Minge aruande kestale. Põhimõtteliselt on aruande värskendamiseks kõik juba tehtud, kuid tulemuste testimine ei tee paha. Muutke lahtri A2 valem näiteks järgmiseks: \u003d EON MONTHS (TODAY () +31 ; 0) ja vaadake, mis juhtub.

Lisades iga kuu uusi tegelikke müügiandmeid, ei pea te muretsema vormingute, valemite jms loomise pärast. Kirjeldatud aruande värskendamise protsess on nii lihtne, et unustate igaveseks igakuised aruannete koostamisel tekkinud probleemid. Ainus probleem võib tekkida ettevõtte ümberkorraldamisel, mille tulemusena võivad pöördtabelisse ilmuda uued piirkonnad. Valemite korrektse töö tagamiseks kontrollige, kas teie aruande kokkuvõte vastab pöördtabeli koguarvule. Uue piirkonna ilmumisel lisage see lihtsalt pakitud lehele ja lohistage sobivad valemid.

Ma ei arvanud, et ütlen kunagi järgmist: „Funktsioon GET.DATA.PUMP.TABLE on suurim õnnistus. Kuidas me enne teda eksisteerisime? "

Originaalis olid Jelena algandmed korraldatud nii, et edasised valemid töötasid õigesti alles 2015. aasta juulis. Selle märkuse juurde lisatud Exceli failis muutsin nii algandmeid kui ka mõningaid valemeid nii, et kõik toimis, olenemata kuupäevast, millal katsetate lisatud Exceli faili. Paraku pidid valemid olema keerulised.

Pöördtabelite puhul on funktsioon GET.DATA.PIVOTTABLE funktsioon, mis tagastab pöördtabeli aruandesse salvestatud andmed.

Funktsioonile kiire juurdepääsu saamiseks peate lahtrisse sisestama võrdusmärgi (\u003d) ja valima pöördetabelis vajaliku lahtri. Excel genereerib funktsiooni GET PIVOTTABLE DATA automaatselt.

Keela GetPivotData loomine

Funktsiooni GET PIVOTTABLE automaatse genereerimise väljalülitamiseks valige liigendtabelis ükskõik milline lahter, klõpsake vahekaarti Töötamine pöördtabelitega -\u003e Valikudrühmale Pöördlaud.Klõpsake vahekaardi kõrval olevat allanoolt Parameetrid.Tühjendage rippmenüüs ruut LooGetPivotData.

Lahtriviidete kasutamine funktsioonis GET DATA.PUMP.TABLE

Funktsiooni GET.DATA.PUMP.TABLE üksuste või väljade nimede määramise asemel võite viidata töölehe lahtritele. Allpool toodud näites sisaldab lahter E3 toote nimetust ja lahtris E4 olev valem viitab sellele. Selle tulemusena tagastatakse tortide kogumaht.

PivotTable-väljade viidete kasutamine

Pöördtabeli üksuste linkide toimimise kohta pole küsimusi. Probleemid tekivad, kui tahame viidata andmeväljale.

Näites sisaldab lahter E3 andmevälja nime "Kogus" ja oleks tore viidata sellele lahtrile funktsioonis, selle asemel, et välja nimi oleks valemis GET.DATA.PUMP.TABLE.

Kui aga esimest argumenti muuta andmevälilahtrisse E3 viidates tagastab Excel meile vea #REF!

GET.PERSONAL.TABEL ANDMED (E3; $ A $ 3)

Probleem lahendatakse lihtsalt tühja stringi (“”) lisamisega lahtriviite algusesse või lõppu.

GET.PERSONAL.TABEL ANDMED (E3 & ""; $ A $ 3)

Valemi lihtne parandus tagastab õige väärtuse.

Kuupäevade kasutamine funktsioonis GET.DATA.PUMP.TABLE

Kui kasutate funktsioonis GET PIVOTTABLE DATA kuupäevi, võib teil olla probleeme isegi siis, kui kuupäev kuvatakse PivotTable-liigendtabelis. Näiteks on alltoodud valem kuupäev “04/21/2013” \u200b\u200bja pöördtabel sisaldab välja, kus on müügikuupäevad. Lahtris E4 olev valem tagastab siiski vea.

GET.DATA.PERSONAL.TABLE ("Köide"; $ A $ 3; "Kuupäev"; "21.04.2013")

Kuupäevadega seotud vigade vältimiseks võite kasutada ühte järgmistest meetoditest.

• Kuupäevavormingute võrdlemine valemis ja liigendtabelis
• Kasutage funktsiooni DATEVALUE
• Kasutage funktsiooni DATE
• Vaadake õige kuupäevaga lahtrit

Kuupäevavormingute võrdlemine valemis ja pöördtabelis.

Õige tulemuse saamiseks veenduge funktsiooni GET PIVOTTABLE DATA kasutamisel, et valemi argumendi ja PivotTable-vormingu kuupäevavormingud oleksid samad.

Lahtris E4 kasutab valem kuupäevavormingut “PP.MM.YYYY” ja selle tulemusena tagastati õige teave.

Funktsiooni DATEVALUE kasutamine

Valemisse kuupäeva käsitsi sisestamise asemel saate kuupäeva tagastamiseks lisada funktsiooni DATEVALUE.

Lahtrisse E4 sisestatakse kuupäev funktsiooni DATEVALUE abil ja Excel tagastab vajaliku teabe.

RECEIVE.SUMMARY.TABLE.DATA ("Köide"; $ A $ 3; "Kuupäev"; DATEVALUE ("04.21.2013"))

Funktsiooni DATE kasutamine

Valemisse kuupäeva käsitsi sisestamise asemel võite kasutada funktsiooni KUUPÄEV, mis tagastab vajaliku teabe õigesti.

RECEIVE.SUMMARY.TABLE.DATA ("Köide"; $ A $ 3; "Date"; DATE (2013; 4; 21))

Kuupäevaraku viide

Kuupäeva käsitsi valemis sisestamise asemel võite viidata lahtrile, mis sisaldab kuupäeva (mis tahes vormingus, mida Excel tõlgendab andmeid kuupäevadeks). Lahtris E4 toodud näites viitab valem lahtrile E3 ja Excel tagastab õiged andmed.

RECEIVE.SUMMARY.TABLE.DATA ("Köide"; $ A $ 3; "Kuupäev"; E3)