Kuidas Excelis aruannet luua. Kuidas koostada Excelis professionaalse välimusega aruanne

See pole esimene kord, kui Renat on Lifehackeri külalisautor. Varem avaldasime temalt suurepärase materjali treeningplaani koostamise kohta: ja veebipõhiseid ressursse, samuti treeningplaani koostamist.

See artikkel sisaldab lihtsaid nippe, mis võimaldavad. Need on eriti kasulikud neile, kes tegelevad juhtimisaruandlusega, koostavad 1C-st ja muudest aruannetest allalaadimiste põhjal erinevaid analüütilisi aruandeid ning koostavad neist juhtkonna jaoks esitlusi ja diagramme. Ma ei pretendeeri absoluutsele uudsusele – ühel või teisel kujul on neid võtteid ilmselt foorumites või.

Lihtsad alternatiivid funktsioonidele VLOOKUP ja GLOOKUP, kui otsitavad väärtused ei ole tabeli esimeses veerus: LOOKUP, INDEX+MATCH

Tulemus:

AT sel juhul, lisaks funktsioonile CHAR (jutumärkide kuvamiseks) on kasutusel IF (IF) funktsioon, mis võimaldab muuta teksti sõltuvalt sellest, kas on positiivne müügitrend ja funktsioon TEXT, mis võimaldab kuvada number mis tahes vormingus. Selle süntaksit kirjeldatakse allpool:

TEKST ( tähendus; vormingus)

Vorming määratakse jutumärkides samamoodi nagu siis, kui sisestaksite kohandatud vormingu aknasse Format Cells.

Keerulisemaid tekste saab ka automatiseerida. Minu praktikas automatiseeriti juhtimisaruandluse pikki, kuid rutiinseid kommentaare vormingus “INDIKAATOR langes / suurenes plaaniga võrreldes XX võrra, peamiselt tänu FACTOR1 suurenemisele / vähenemisele XX võrra, FACTOR2 suurenemisele / vähenemisele. by YY ..." muutuva tegurite loendiga. Kui kirjutate selliseid kommentaare sageli ja nende kirjutamise protsessi saab algoritmeerida, tasub korraks hämmingut teha, luues valemi või makro, mis säästab vähemalt osa tööst.

Kuidas hoida andmeid igas lahtris pärast ühendamist

Lahtrite liitmisel säilib ainult üks väärtus. Excel hoiatab selle eest lahtrite liitmisel:

Seega, kui teil oli igast lahtrist sõltuv valem, lakkab see pärast nende ühendamist töötamast (näite ridadel 3-4 viga #N/A):

Lahtrite ühendamiseks ja andmete säilitamiseks kõigis neis (võib-olla on teil valem, nagu selles abstraktses näites; võib-olla soovite lahtreid liita, kuid säilitada kõik andmed tuleviku jaoks või peita need tahtlikult), ühendage kõik lahtrid töölehel , valige need ja seejärel kasutage käsku "Format Painter" ja kandke vormindus lahtritesse, mida peate ühendama:

Kuidas koostada kokkuvõtet mitmest andmeallikast

Kui teil on vaja koostada kokkuvõte mitmest andmeallikast korraga, peate lindile või paneelile lisama kiire juurdepääs PivotTable ja PivotChart Wizard, millel on see valik.

Seda saate teha järgmiselt: "Fail" → "Valikud" → "Kiire juurdepääsu tööriistariba" → "Kõik käsud" → "PivotTable-liigendtabeli ja PivotChart-viisard" → "Lisa":

Pärast seda ilmub lindile vastav ikoon, millel klõpsates helistab sama viisard:

Kui klõpsate sellel, kuvatakse dialoogiboks:

Selles peate valima üksuse "Mitmes konsolideerimisvahemikus" ja klõpsama nuppu "Järgmine". Järgmises lõigus saate valida "Loo üks lehe väli" või "Loo leheväljad". Kui soovite igale andmeallikale ise nime välja mõelda, valige teine ​​üksus:

Järgmises aknas lisage kõik vahemikud, mille alusel kokkuvõte koostatakse, ja andke neile nimed:

Pärast seda määrake viimases dialoogiboksis, kuhu pivot-tabeli aruanne paigutatakse - olemasolevale või uuele lehele:

PivotTable-liigendtabeli aruanne on valmis. Filtris "Lehekülg 1" saate vajadusel valida ainult ühe andmeallika:

Kuidas arvutada teksti A esinemiste arvu tekstis B ("MTS-i tariif SuperMTS" - kaks lühendi MTS esinemist)

Selles näites on veerus A mitu tekstirida ja meie ülesanne on välja selgitada, mitu korda igaüks neist sisaldab lahtris E1 asuvat otsinguteksti:

Selle probleemi lahendamiseks võite kasutada keerulist valemit, mis koosneb järgmistest funktsioonidest:

  1. DLSTR (LEN) – arvutab teksti pikkuse, ainsaks argumendiks on tekst. Näide: DLSTR (“masin”) = 6.
  2. SUBSTITUTE – asendab teatud teksti tekstistringis teisega. Süntaks: SUBSTITUTE ( tekst; vana_tekst; uus_tekst). Näide: SUBSTITUTE ("auto";"auto";"")= "mobiil".
  3. UPPER – muudab stringi kõik märgid suurtähtedeks. Ainus argument on tekst. Näide: UPPER("masin") = "MASIN". Seda funktsiooni vajame tõstutundliku otsingu tegemiseks. Lõppude lõpuks, UPPER("masin") = UPPER("masin")

Teatud tekstistringi esinemise leidmiseks teises peate eemaldama kõik selle esinemised algsest stringist ja võrdlema saadud stringi pikkust algse stringiga:

DLSTR (“MTS tariif Super MTS”) – DLSTR ("Supertariif") = 6

Seejärel jagage see erinevus otsitava stringi pikkusega:

6 / DLSTR ("MTS") = 2

See on kaks korda, kui string "MTS" sisaldub originaalis.

Jääb üle see algoritm kirjutada valemikeeles (tekstiga tähistame teksti, milles esinemisi otsime, ja "soovitud" - seda, mille esinemiste arv meid huvitab):

=(DLSTR( tekst)-DLSTR(ASENDAJA(ÜLEMINE( tekst);ÜLEMINE( soovitud);"")))/DLSTR( soovitud)

Meie näites näeb valem välja selline:

=(PIKKE(A2)-PIKK(ASENDAJA(ÜLEMINE(A2),ÜLEMINE($E$1),"")))/PIKKE($E$1)

Exceli pivot-tabelid on võimas aruandlustööriist. See on eriti kasulik juhtudel, kui kasutaja ei tööta valemitega hästi ja tal on raske iseseisvalt andmete analüüsi teha. Selles artiklis vaatleme, kuidas selliseid tabeleid õigesti luua ja millised võimalused on selleks Exceli redaktoris. Selleks ei pea te faile alla laadima. Koolitus on saadaval Internetis.

Esimene samm on tabeli loomine. Soovitav on, et veerge oleks mitu. Sel juhul tuleb mõnda teavet korrata, kuna ainult sel juhul on võimalik sisestatud teavet analüüsida.

Näiteks võtke arvesse samu finantskulusid erinevatel kuudel.

Pivot-tabelite loomine

Sellise tabeli ehitamiseks peate tegema järgmised toimingud.

  1. Alustuseks tuleb see täielikult välja valida.

  1. Seejärel minge vahekaardile "Sisesta". Klõpsake ikooni "Tabel". Valige kuvatavast menüüst "Pivot Table".

  1. Selle tulemusena ilmub aken, kus peate määrama mitu põhiparameetrit pivot-tabeli koostamiseks. Esimese sammuna tuleb valida andmeala, mille alusel analüüs läbi viiakse. Kui olete tabeli varem valinud, lisatakse sellele link automaatselt. Vastasel juhul tuleb see valida.

  1. Seejärel palutakse teil täpselt täpsustada, kus ehitamine toimub. Parem on valida üksus "Olemasolevale lehele", kuna teabe analüüsimine on ebamugav, kui kõik on mitmele lehele laiali. Seejärel peate määrama vahemiku. Selleks klõpsake sisestusvälja kõrval olevat ikooni.

  1. Vahetult pärast seda vajub PivotTable-liigendtabeli viisard väikeseks. Lisaks muutub see ka välimus kursor. Peate tegema hiire vasaku klõpsu mis tahes teile sobivas kohas.

  1. Selle tulemusena asendatakse viide määratud lahtrile automaatselt. Seejärel peate klõpsama akna paremas servas oleval ikoonil, et taastada selle algne suurus.

  1. Seadete lõpetamiseks klõpsake nuppu "OK".

  1. Selle tulemusena näete pivot-tabelitega töötamiseks tühja malli.

  1. Selles etapis peate määrama, milline väli on:
    1. veerg;
    2. string;
    3. väärtus analüüsi jaoks.

Saate valida ükskõik mida. Kõik sõltub sellest, millist teavet soovite saada.

  1. Mis tahes välja lisamiseks peate sellel hiire vasaku klahviga klõpsama ja ilma sõrme lahti laskmata lohistama selle soovitud alale. Kursor muudab oma välimust.

  1. Saate oma sõrme vabastada alles siis, kui läbikriipsutatud ring kaob. Samamoodi peate lohistama kõik tabelis olevad väljad.

  1. Täieliku tulemuse nägemiseks võite sulgeda külgriba seaded. Selleks klõpsake lihtsalt ristil.

  1. Selle tulemusena näete järgmist. Selle tööriista abil saate iga üksuse iga kuu kulude summa kokku võtta. Lisaks on saadaval info kogutulemuse kohta.

  1. Kui teile laud ei meeldi, võite proovida seda veidi teistmoodi ehitada. Selleks tuleb ehitusaladel väljad vahetada.

  1. Sulgege ehitusabi uuesti.

  1. Seekord näeme, et PivotTable on muutunud palju suuremaks, kuna nüüd pole veerud kuud, vaid kulukategooriad.

Kui te ei saa ise tabelit koostada, võite alati loota toimetaja abile. Excelis on võimalik luua sarnaseid objekte automaatrežiim.

Selleks peate tegema järgmist, kuid kõigepealt valige kogu teave tervikuna.

  1. Minge vahekaardile "Sisesta". Seejärel klõpsake ikooni "Tabel". Valige kuvatavas menüüs teine ​​üksus.

  1. Kohe pärast seda ilmub aken, kus on erinevad näited ehitamiseks. Sarnaseid valikuid soovitatakse mitme veeru põhjal. Mallide arv sõltub otseselt nende arvust.

  1. Kui hõljutate kursorit iga üksuse kohal, on tulemuse eelvaade saadaval. See muudab töötamise palju mugavamaks.

  1. Saate valida, mis teile kõige rohkem meeldib.

  1. Valitud valiku sisestamiseks klõpsake lihtsalt nuppu "OK".

  1. Selle tulemusena saate järgmise tulemuse.

Pange tähele, et tabel on loodud uuele lehele. See juhtub iga kord, kui konstruktorit kasutatakse.

Kui lisate (pole vahet, kuidas) pöördetabel, näete tööriistaribal uuel kaardil"Analüüs". See sisaldab tohutul hulgal erinevaid tööriistu ja funktsioone.

Vaatleme igaüks neist üksikasjalikumalt.

Ekraanipildil märgitud nupul klõpsates saate teha järgmist.

  • muuda nime;

  • avage seadete aken.

Valikute aknas näete palju huvitavat.

aktiivne väli

Selle tööriistaga saate teha järgmist.

  1. Kõigepealt peate valima lahtri. Seejärel klõpsake nuppu "Aktiivne väli". Ilmuvas menüüs klõpsake üksust "Väljasuvandid".

  1. Vahetult pärast seda näete järgmist akent. Siin saate määrata toimingu tüübi, mida kasutatakse valitud välja andmete kokkuvõtmiseks.

  1. Lisaks saate kohandada numbrivormingut. Selleks klõpsake vastavat nuppu.

  1. See avab lahtrite vormindamise akna.

Siin saate määrata, millisel kujul soovite teabe analüüsi tulemust kuvada.

Tänu sellele tööriistale saate kohandada rühmitamist valitud väärtuste järgi.

Kleepige viil

Toimetaja Microsoft Excel võimaldab luua interaktiivseid pivot-tabeleid. Sel juhul ei pea te midagi keerulist tegema.

  1. Valige veerg. Seejärel klõpsake nuppu "Insert Slice".
  2. Ilmuvas aknas valige näiteks üks väljapakutud väljadest (edaspidi saate neid valida piiramatul arvul). Pärast millegi valimist aktiveeritakse kohe nupp "OK". Klõpsake sellel.

  1. Selle tulemusena ilmub väike aken, mida saab igale poole liigutada. See pakub kõike võimalikku ainulaadsed väärtused mis on selles valdkonnas. Tänu sellele tööriistale saate summa välja võtta ainult teatud kuude jooksul (antud juhul). Vaikimisi kuvatakse teave kogu aeg.

  1. Võite klõpsata mis tahes üksusel. Vahetult pärast seda muutuvad kõik summa väljal olevad väärtused.

  1. Seega on võimalik valida mis tahes ajavahemik.

  1. Igal ajal saab kõik taastada algsel kujul. Selleks klõpsake paremal asuvat ikooni ülemine nurk see aken.

Antud juhul saime aruannet sorteerida kuude kaupa, kuna meil oli vastav väli. Kuupäevadega töötamiseks on aga võimsam tööriist.

Kui klõpsate tööriistaribal vastavat nuppu, näete suure tõenäosusega seda viga. Fakt on see, et meie tabelis pole ühtegi lahtrit, millel oleks selgesõnaliselt "Kuupäeva" andmevorming.

Näiteks loome väikese tabeli erinevate kuupäevadega.

Seejärel peate koostama pöördetabeli.

Minge tagasi vahekaardile "Sisesta". Klõpsake ikooni "Tabel". Ilmuvas alammenüüs valige meile vajalik valik.

  1. Seejärel palutakse meil valida väärtuste vahemik.

  1. Selleks piisab, kui valida kogu tabel.

  1. Kohe pärast seda asendatakse aadress automaatselt. Siin on kõik väga lihtne, sest see on mõeldud mannekeenidele. Ehitamise lõpetamiseks klõpsake nuppu OK.

  1. Exceli redaktor pakub meile ainult ühte võimalust, kuna tabel on väga lihtne (näite jaoks pole rohkem vaja).

  1. Proovige uuesti klõpsata ikooni "Sisesta ajaskaala" (asub vahekaardil "Analüüs").

  1. Seekord vigu ei tule. Teil palutakse valida sorteerimiseks väli. Märkige ruut ja klõpsake nuppu OK.

  1. Tänu sellele ilmub aken, kus saate liuguri abil valida soovitud kuupäeva.

  1. Valime teise kuu ja andmed puuduvad, kuna tabelis on kõik kulud märgitud ainult märtsikuu kohta.

Kui olete algandmetes muudatusi teinud ja seda mingil põhjusel liigendtabelis ei kuvata, saate neid alati käsitsi värskendada. Selleks klõpsake lihtsalt tööriistaribal vastavat nuppu.

Kui otsustate hoone aluseks olevaid välju muuta, siis on seda palju lihtsam teha seadetes, mitte tabelit kustutada ja uusi eelistusi arvestades uuesti luua.

Selleks klõpsake ikooni "Andmeallikas". Seejärel valige samanimeline menüüelement.

Selle tulemusena ilmub aken, kus saate vajaliku teabekoguse uuesti valida.

Tegevused

Selle tööriista abil saate:

  • tee laud puhtaks;
  • esiletõstmine;
  • liiguta seda.

Arvutamine

Kui tabelis toodud arvutustest ei piisa või need ei vasta teie vajadustele, saate alati teha oma muudatused. Klõpsates selle tööriista ikoonil, näete järgmisi valikuid.

Need sisaldavad:

  • arvutatud väli;

  • arvutatud objekt;

  • arvutusjärjekord (lisatud valemid kuvatakse loendis);

  • kuva valemid (info puudub, kuna valemeid pole lisatud).

Siin saate luua PivotCharti või muuta soovitatud tabelitüüpi.

Selle tööriistaga saate kohandada redaktori tööruumi välimust.

Tänu sellele on teil võimalik:

  • kohandage külgriba kuva väljade loendiga;

  • lubada või keelata pluss/miinus nupud;

  • kohandada väljade päiste kuvamist.

Pivot-tabelitega töötades ilmub lisaks vahekaardile "Analüüs" ka teine ​​- "Disainer". Siin saate muuta oma objekti välimust, kuni see on vaikeväärtusega võrreldes tundmatu.

Saab kohandada:

  • vahesummad:
    • ära näita;
    • kuva allosas kõik kogusummad;
    • kuva päises kõik kogusummad.

  • üldtulemused:
    • keela ridade ja veergude jaoks;
    • luba ridade ja veergude jaoks;
    • luba ainult ridade jaoks;
    • lubada ainult veergude jaoks.

  • aruande paigutus:
    • näidata kokkusurutud kujul;
    • näidata struktuuri kujul;
    • näidata tabeli kujul;
    • korda kõiki elementide silte;
    • ära korda elementide silte.

  • tühjad read:
    • sisestage iga elemendi järele tühi rida;
    • eemaldage iga elemendi järel tühi rida.

  • PivotTable-liigendtabeli stiilivalikud (siin saate iga üksuse lubada/keelata):
    • reapäised;
    • veergude pealkirjad;
    • vahelduvad read;
    • vahelduvad veerud.

  • kohandada elementide stiili.

Selleks, et näha rohkem erinevaid valikuid, tuleb klõpsata selle tööriista paremas alanurgas oleval kolmnurgal.

Kohe pärast seda ilmub tohutu nimekiri. Saate valida ükskõik mida. Kui hõljutate kursorit iga malli kohal, muutub teie tabel (seda tehakse eelvaade). Muudatused ei jõustu enne, kui klõpsate mõnel valikul.

Lisaks saate soovi korral luua oma disainistiili.

Siin saate muuta ka ridade kuvamise järjekorda. Mõnikord on see kuluanalüüsi mugavuse huvides vajalik. Eriti kui loend on väga suur, kuna vajalikku asukohta on lihtsam leida tähestikulises järjekorras kui loendit mitu korda kerida.

Selleks tehke järgmist.

  1. Klõpsake nõutava välja kõrval olevat kolmnurka.
  2. Selle tulemusena näete järgmist menüüd. Siin saate valida soovitud sortimisvaliku ("A kuni Z" või "Z kuni A").

Kui standardvalikust ei piisa, võite klõpsata üksusel " Lisavalikud sorteerimine".

Selle tulemusena näete järgmist akent. Üksikasjalikumate sätete vaatamiseks klõpsake nuppu "Täpsemalt".

Siin on kõik seatud automaatseks. Kui tühjendate selle märkeruudu, saate määrata vajaliku võtme.

Pivot tabelid rakenduses Excel 2003

Ülaltoodud sammud sobivad tänapäevaste toimetajate jaoks (2007, 2010, 2013 ja 2016). AT vana versioon kõik näeb välja teistsugune. Võimalusi on muidugi palju vähem.

Rakenduses Excel 2003 pivot-tabeli loomiseks peate tegema järgmist.

  1. Minge menüü jaotisesse "Andmed" ja valige sobiv üksus.

  1. Selle tulemusena ilmub selliste objektide loomise viisard.

  1. Pärast nupu "Järgmine" klõpsamist avaneb aken, milles peate määrama lahtrite vahemiku. Seejärel klõpsake uuesti nuppu "Järgmine".

  1. Seadete lõpetamiseks klõpsake nuppu "Lõpeta".

  1. Selle tulemusena näete järgmist. Siin peate lohistama väljad sobivatesse piirkondadesse.

  1. Näiteks võite saada järgmise tulemuse.

Selgub, et selliseid aruandeid on palju parem luua tänapäevastes toimetajates.

Järeldus

See artikkel käsitles kõiki Exceli redaktoris pivot-tabelitega töötamise peensusi. Kui midagi ei sobi, võite valida valed väljad või on neid väga vähe – sellise objekti loomiseks on vaja mitut topeltväärtustega veergu.

Kui sellest õpetusest teile ei piisa, Lisainformatsioon leiate Microsofti veebiabist.

Video juhendamine

Neile, kellel on veel vastuseta küsimusi, on allpool lisatud video kommentaaridega ülalkirjeldatud juhiste kohta.

Vaadake artikli videot:


Standardaruanded, mis sisalduvad meie ärianalüüsi (BI) võtmed kätte moodulid võimaldab teil vastata 99%-le kõige populaarsematest kasutajapäringutest.

Kuid soovi korral saavad analüütikud või teised kasutajad koostada oma aruanded Excelis OLAP-i kuubikutel.

Tänu iga meie ärianalüüsi (BI) mooduli keskmes on OLAP-i kuup, millega kasutajad saavad hõlpsasti koostada Excelis mis tahes aruande, kasutades Pivot Tables-i.

Samuti saab salvestada OLAP-kuubikutele Excelis üles ehitatud aruandeid ning uuesti avamisel uuendatakse neid automaatselt värskeimate andmetega alates ettevõtte ärianalüüsi (BI) süsteem .

Näited OLAP-kuubikutele koostatud aruannetest:

Aruannete kirjeldus:

(Aruannete koostamise protsessi kohta vaadake selle artikli videot)

1. Müügiaruanne rublades ja ühikutes:
  • periood: jooksva aasta kohta
  • näitajad: müügi fakt
  • detailides: veerud/kvartal, kuu
  • detailides: müügiliinid/kanalid, kliendid, kaubamärgid, tooted

2. Müügi struktuuri aruanne rublades ja ühikutes (filter):
  • periood: jooksva aasta kohta
  • näitajad: tegelik müük, tegelik müük protsentides
  • detailides: veerud/kvartal, kuu
  • detailides: müügiliinid/kanalid, kaubamärgid

3. Müügiplaani täitmise aruanne rublades ja ühikutes (filter):
  • periood: jooksva aasta kohta
  • näitajad: müügiplaan, müügifakt, plaani täitmine
  • detailides: veerud/kvartal, kuu
  • detailides: müügiliinid/kanalid, kliendid, kategooriad, tooted

4. Müügikasvu aruanne rublades ja ühikutes (filter):
  • periood
  • näitajad
  • detailides: liinid/föderaalringkonnad, piirkonnad, kategooriad, tooted

5. Aruanne müügikasvu kohta aja järgi rublades ja ühikutes (filter):
  • periood: jooksva aasta kohta võrreldes eelmisega
  • näitajad: eelmise aasta müük, müügiplaan, tegelik müük, plaani elluviimine, müügi kasv absoluutses ja suhtelises väärtuses võrreldes eelmise aastaga
  • detailides: read/kvartal, kuu, kategooriad, tooted

6. Saadaolevate arvete aruanne:
  • periood: praeguse kuupäeva jaoks
  • näitajad: dokumendi summa, nõuded kokku, lühiajalised nõuded, tähtaja ületanud nõuded perioodide lõikes - kuni 15 päeva, kuni 30 päeva, kuni 60 päeva, kuni 90 päeva, kuni 180 päeva, kuni 365 päeva või rohkem, nõuete struktuur
  • detailides: müügiliinid/kanalid, vastaspool, dokumendi maksekuupäev, dokumendi kuupäev, dokument

7. Nõuete dünaamika aruanne:
  • periood: jooksva aasta kohta
  • näitajad: nõuded kokku, lühiajalised nõuded, tähtaja ületanud nõuded, nõuete struktuur
  • detailides: read/aasta, nädal, konto

8. Makse laekumise aruanne dokumendi kuupäeva järgi:
  • periood: jooksva aasta kohta
  • näitajad: dokumendi summa, dokumendi maksesumma, makse hälbed absoluutses ja suhtelises väärtuses
  • detailides: read/aasta, kuu, vastaspool, dokumendi tüüp, dokumendi kuupäev, dokumendi number

9. Makse laekumise aruanne maksekuupäeva järgi:
  • periood: jooksva aasta kohta
  • näitajad: maksesumma, makse jaotuse summa, jaotuse hälbed absoluutses ja suhtelises väärtuses
  • detailides: read/aasta, kuu, vastaspool, makse tüüp, maksekuupäev, makse number

10. Laoseisu aruanne:
  • periood: praeguse kuupäeva jaoks
  • näitajad: toote jääk, järelejäänud säilivusaeg protsentides (vastavalt DSO-le), toote jääk aegumiskuupäeva järgi - Tähtaeg 0%, Tähtaeg 10-30%, Tähtaeg 40-60%, Tähtaeg 70-90%, Tähtaeg 99%
  • detailides: read/kategooria, toode, olek, ladu, vastuvõtudokumendi kuupäev

Tere kõigile! Tänane materjal on mõeldud neile, kes jätkavad rakendusprogrammidega töötamist ja ei tea, kuidas Excelis pivot-tabelit teha.

Olles loonud ühise tabeli, mis tahes tekstidokumendid, saate seda analüüsida, luues Excelis pivot-tabeleid.

Exceli pöördetabeli loomine eeldab teatud tingimuste täitmist.

  1. Andmed mahuvad tabelisse, kus on veerud ja loendid nimedega.
  2. Tühje vorme pole.
  3. Peidetud objekte pole.

Kuidas Excelis pöördetabelit teha: samm-sammult juhised

Pivot-tabeli loomiseks vajate:

Loodud on tühi leht, kus näete alade ja väljade loendeid. Pealkirjadest on saanud meie uue tabeli väljad. Pivot-tabel moodustatakse välju lohistades.

Need märgitakse linnukesega ja analüüsi mugavuse huvides vahetate need tabelialadel.


Otsustasin, et teen andmeanalüüsi läbi filtri müüjate kaupa, et oleks selge, kelle poolt ja mis summa eest iga kuu müüdi ning mis toodet.

Valime konkreetse müüja. Hoiame hiirt all ja liigutame välja "Müüja" "Aruandefiltrisse". Uus väli on märgistatud linnukesega ja tabelivaade muutub veidi.


Kategooria "Tooted" paneme stringide kujule. "Reanimedes" teisaldame meile vajaliku välja.


Rippmenüü kuvamiseks on oluline, millises järjekorras me nime määrame. Kui alguses teeme ridades valiku toote kasuks ja seejärel märgime hinna, siis on tooted lihtsalt rippmenüüst ja vastupidi.

Põhitabelis asuvas veerus "Ühikud" kuvati teatud müüja poolt konkreetse hinnaga müüdud kauba kogus.


Näiteks iga kuu müükide kuvamiseks tuleb veerude nimede asemele panna väli "Kuupäev". Valige käsk "Rühm", klõpsates kuupäeval.


Määrake kuupäevaperioodid ja samm. Kinnitame valiku.

Me näeme sellist tabelit.


Liigutame välja "Summa" alale "Väärtused".


Arvude kuvamine muutus nähtavaks ja vajame täpselt numbrivormingut


Parandamiseks valige lahtrid, helistades hiirega aknasse, valige "Numbrivorming".

Valime järgmise akna numbrivormingu ja märgime "Numbrite eraldaja". Kinnitage nupuga "OK".

Pivot tabeli kujundus

Kui märgime linnukese, mis kinnitab mitme objekti valikut korraga, saame korraga töödelda mitme müüja andmeid.


Veergude ja ridade jaoks on võimalik filtrit rakendada. Märkides linnukese ühe tootesordi juurde, saate teada, kui palju sellest üks või mitu müüjat müüvad.


Välja parameetrid konfigureeritakse ka eraldi. Näites näeme, et teatud roma müüja müüs konkreetsel kuul kindla summa eest särke. Hiirt klõpsates avame real "Summa välja järgi ..." menüü ja valime "Väärtusväljade parameetrid".


Järgmisena valige väljal olevate andmete kokkuvõtmiseks "Kogus". Kinnitame valiku.

Vaata tabelit. Selgelt on näha, et ühel kuul müüs müüja särke 2 tk.


Nüüd muudame tabelit ja paneme filtri tööle kuude kaupa. Viime välja "Kuupäev" "Aruandefiltrisse" ja kus on "Veerunimed", on "Müüja". Tabelis kuvatakse kogu müügiperiood või konkreetne kuu.


PivotTable-liigendtabeli lahtrite valimine avab vahekaardi PivotTable-liigendtabeli tööriistad, millel on veel kaks vahekaarti, Valikud ja Kujundus.


Tegelikult saab pivot-tabelite seadistustest rääkida väga pikalt. Muutke oma maitset, saavutades teile mugava kasutamise. Ärge kartke suruda ja katsetada. Saate alati mis tahes toiminguid muuta, vajutades klahvikombinatsiooni Ctrl + Z.

Loodan, et olete kogu materjali õppinud ja nüüd teate, kuidas Excelis pivot-tabelit teha.

Suurte ja keeruliste tabelite analüüsimiseks kasutavad nad tavaliselt . Valemeid saab kasutada ka olemasolevate andmete grupeerimiseks ja analüüsimiseks. Koostame valemite abil lihtsaid aruandeid.

Lähtepunktina kasutame ( Sisesta/ Tabelid/ Tabel), mis sisaldab teavet tootepartiide müügi kohta. Tabeli ridadel on andmed toote partii tarnimise ja selle müügi kohta. Artiklis kasutati sarnast tabelit .

Tabelis on veerud:

  • Toode- kaubapartii nimi, näiteks " apelsinid»;
  • Grupp- tooterühm, näiteks " apelsinid"on gruppi kaasatud" Puuviljad»;
  • kohaletoomise kuupäev– Tarnija poolt Kauba üleandmise kuupäev;
  • Müügipiirkond- Piirkond, kus Kauba saadetis müüdi;
  • Müük- Maksumus, millega oli võimalik Kauba saadetist müüa;
  • Müük- Kauba tegeliku müügi periood Piirkonnas (päevades);
  • Kasum- märge selle kohta, kas Kauba müüdud partiist saadi kasumit.

Läbi Nimehaldur kohandage tabeleid " allika_tabel» (vt näidisfaili).

Koostame valemite abil 5 lihtsat aruannet, mille paigutame eraldi lehtedele.

Aruanne nr 1 Kaupade kogumüük

Leidke iga toote müügi kogusumma.
Ülesanne lahendatakse üsna lihtsalt funktsiooni SUMIF() abil, kuid aruande koostamine ise nõuab teatud oskusi mõne EXCELi tööriistaga töötamisel.

Nii et alustame. Alustuseks peame koostama kaupade nimede loendi. Sest lähtetabeli veerus Toode korduvad nimed, siis peame sellest valima ainult väärtused. Seda saab teha mitmel viisil: valemid (vt artiklit), menüü kaudu Andmed/ Töö andmetega/ Eemalda duplikaadid või koos . Kui kasutate esimest meetodit, siis uute toodete lisamisel lähtetabelisse lisatakse uued nimed automaatselt loendisse. Kuid siin kasutame lihtsuse huvides teist meetodit. Selle jaoks:

  • Minge algse tabeliga lehele;
  • helistama ( Andmed/ Sorteerimine ja filtreerimine/ Täpsem);
  • Täitke väljad alloleval joonisel näidatud viisil: seadke lüliti asendisse Kopeerige tulemus teise kohta; väljale Source Range sisestage $A$4:$A$530; Märkige ruut Ainult unikaalsed rekordid.

  • Kopeerige saadud loend lehele, kuhu aruanne paigutatakse;
  • Sorteeri toodete loend ( Andmed / Sordi ja filtreeri / Sorteeri A kuni Z).

Peaksite saama järgmise loendi.

Lahtris B6 sisestage järgmine valem ja kopeerige see loendi lõppu:

SUMIF(allika_tabel[kaup],A6,allika_tabel[müük])

COUNTIF(allika_tabel[üksus], A6)

Aruanne nr 2 tootemüügist piirkondade kaupa

Leidke iga toote müügi kogusumma piirkondades.
Kasutame Aruande nr 1 jaoks loodud Kaupade nimekirja. Samamoodi saame piirkondade nimede loendi (väljale Algvahemik sisestage $D$4:$D$530).
Kopeerige saadud vertikaalne vahemik asukohta Lõikelaud ja see horisontaalasendisse. Asetage saadud vahemik, mis sisaldab piirkondade nimesid, aruande päisesse.

Lahtris B 8 tutvustame järgmist valemit:

SUMIFS(allika_tabel[müük];
Allikas_tabel[üksus];$A8;
Source_Table[müügipiirkond];B$7)

Valem tagastab lahtrisse paigutatud Toote kogumüügi A8 , lahtris piirkonnas KELL 7 . Pöörake tähelepanu kasutamisele (viited $A8 ja B$7), vajate seda tabeli ülejäänud tühjade lahtrite valemi kopeerimiseks.

Eeltoodud valemi kopeerimine abiga parempoolsetesse lahtritesse ei toimi (seda tehti aruande nr 1 puhul), sest antud juhul rakus C8 valem näeb välja selline:

SUMIFS(Algne_tabel[Müük, päevad];
allikas_tabel[rühm];$A8;
Source_Table[Müük];C$7)

Aruanne nr 3 Toodete filtreerimine kasumlikkuse järgi

Lähme tagasi algse tabeli juurde. Iga Kaubapartii kas teenis kasumit või mitte (vt algse tabeli veergu Kasum). Arvutame müügitulu tooterühmade kaupa sõltuvalt kasumlikkusest. Selleks filtreerime lähtetabeli kirjed välja Kasum järgi valemite abil.

FREQUENCY(allika_tabel[müük, päevad];A7:A12)

Valige valemi sisestamiseks vahemik C6:C12 , siis sisse Vormeli baar sisestage ülaltoodud valem ja klõpsake CTRL+ SHIFT+ SISENEMA.

Sama tulemuse saab tavalise funktsiooni SUMPRODUCT() abil:
=SUMPRODUCT((Allika_tabel[Müük, päevad]>A6)*
(Allikas_tabel[Müük, päevad]<=A7))

Aruanne nr 5 Kaubavarustuse statistika

Nüüd koostame kuu Kauba tarnimise aruande.
Esmalt koostame kuude loendi aastate kaupa. Algses tabelis on kõige varasem tarnekuupäev 07/11/2009. Saate selle arvutada järgmise valemi abil:
=MIN(allika_tabel[tarnekuupäev])

Koostame kuupäevade loendi – alates varaseimast tarnekuupäevast. Selleks kasutame valemit:
=KUUN($5,-1)+1

Selle tulemusena saame kuupäevade loendi - kuude esimesed päevad:

Sobiva lahtrivormingu rakendamisel muutke kuupäevade kuva:

Kuus tarnitud Kaubasaadetiste arvu arvutamise valem:

SUMPRODUCT((Allika_tabel[Tarnekuupäev]>=B9)*
(Allikas_tabel[Tarnekuupäev]

Nüüd lisame read, et lugeda iga aasta mängude koguarv. Selleks muudame tabelit veidi, tuues funktsiooni YEAR() abil eraldi veerus esile tarne tegemise aasta.

Nüüd loome aastate kaupa väljundi jaoks struktuuri menüüelemendi kaudu:

  • Valige muudetud tabeli suvaline lahter;
  • Kõneaken menüüpunkti kaudu Andmed/ Struktuur/ Vahesummad;
  • Täitke väljad nagu näidatud joonisel:

Pärast nuppu OK klõpsamist muudetakse tabelit järgmiselt:

Luuakse iga-aastased vahesummad.
Vajutades lehe vasakus ülanurgas olevaid väikseid nuppe, saate juhtida andmete kuvamist tabelis.

Kokkuvõte:

Loodudega sarnaseid aruandeid saab loomulikult teha kasutades või kasutades filtrid algsesse tabelisse või muude funktsioonide BDSUMM() , BIzVARCH() , COUNT() jne abil. Lähenemisviisi valik sõltub konkreetsest olukorrast.

SEOTUD ARTIKLID