Pivot-tabelid sobivad kõigile – need arvutavad kiiresti ja on paindlikult konfigureeritud ning vajadusel saab disaini elegantselt sisse kerida. Aga on ka üksikuid kärbseid, eelkõige oskamatus koostada kokkuvõtet, kus väärtusala ei peaks sisaldama numbreid, vaid teksti.
Proovime sellest piirangust mööda hiilida ja mõelda sarnases olukorras “paar karku”.
Oletame, et meie ettevõte veab oma tooteid konteinerites mitmesse meie riigi ja Kasahstani linna. Konteinereid saadetakse mitte rohkem kui kord kuus. Igal konteineril on tähtnumbriline number. Algandmetena on standardne tarnete loetelu, millest tuleb teha mingi kokkuvõte, et selgelt näha igasse linna ja iga kuu saadetud konteinerite arvu:
Mugavuse huvides muudame algandmetega tabeli eelnevalt käsu abil “targaks”. Avaleht – vorminda tabelina (Avaleht — vorminda tabelina) ja anna talle nimi Tarned tab Ehitaja (Kujundus). Tulevikus muudab see elu lihtsamaks, sest. tabeli nime ja selle veerge saab kasutada otse valemites.
1. meetod. Lihtsaim – kasutage Power Queryt
Power Query on ülivõimas tööriist andmete laadimiseks ja teisendamiseks Excelis. See lisandmoodul on Excelisse vaikimisi sisse ehitatud alates 2016. aastast. Kui teil on Excel 2010 või 2013, saate selle eraldi alla laadida ja installida (täiesti tasuta).
Selguse huvides analüüsisin kogu protsessi samm-sammult järgmises videos:
Kui Power Queryt pole võimalik kasutada, siis võib minna teisiti – pivot-tabeli või valemite kaudu.
2. meetod. Abikokkuvõte
Lisame oma algsesse tabelisse veel ühe veeru, kus lihtsa valemi abil arvutame tabeli iga rea arvu:
Ilmselgelt on -1 vaja, sest meie tabelis on üherealine päis. Kui teie tabel pole lehe alguses, võite kasutada veidi keerukamat, kuid universaalset valemit, mis arvutab praeguse rea ja tabeli päise numbrite erinevuse:
Nüüd koostame standardsel viisil meie andmete põhjal soovitud tüüpi pivot-tabeli, kuid väärtusväljal jätame välja Liini number selle asemel, mida me tahame konteiner:
Kuna meil ei ole samal kuul samas linnas mitut konteinerit, siis tegelikult ei anna meie kokkuvõte välja mitte koguse, vaid meile vajalike konteinerite reanumbrid.
Lisaks saate vahekaardil põhi- ja vahesummad välja lülitada Konstruktor – üldsummad и Vahesummad (Disain – üldsummad, vahesummad) ja samas kohas lülitada kokkuvõte nupuga mugavamale tabelipaigutusele Teata maketist (Aruande paigutus).
Seega oleme tulemuseni juba poolel teel: meil on tabel, kus linna ja kuu ristumiskohas on lähtetabelis rea number, kus asub meile vajalik konteinerkood.
Nüüd kopeerime kokkuvõtte (samale või teisele lehele) ja kleepime selle väärtustena ning seejärel sisestame väärtuste alale oma valem, mis ekstraheerib konteineri koodi kokkuvõttes leitud reanumbri järgi:
funktsioon IF (KUI), sel juhul kontrollib, et kokkuvõtte järgmine lahter poleks tühi. Kui tühi, siis väljasta tühi tekstistring “”, st jäta lahter tühjaks. Kui see pole tühi, siis ekstraktige veerust Konteiner lähtetabel Tarned lahtri sisu reanumbri järgi funktsiooni abil INDEX (INDEX).
Võib-olla on ainus mitte väga ilmne punkt siin topeltsõna Konteiner valemis. Selline kummaline kirjutamisvorm:
Varud[[konteiner]:[konteiner]]
… on vajalik ainult veerule viitamiseks Konteiner oli absoluutne (nagu $-märkidega viide tavaliste "mittenutikate" tabelite jaoks) ja ei libisenud meie valemit paremale kopeerides naaberveergudele.
Edaspidi lähtetabelis olevate andmete muutmisel Tarned, peame meeles pidama, et uuendame oma abikokkuvõtet reanumbritega, paremklõpsates sellel ja valides käsu Värskenda ja salvesta (Värskenda).
Meetod 3. Valemid
See meetod ei nõua vahepealse pivot tabeli loomist ja käsitsi värskendamist, vaid kasutab Exceli raskerelva – funktsiooni SUMMESLIMN (SUMIFS). Kokkuvõttes reanumbrite otsimise asemel saate need arvutada järgmise valemi abil:
Mõne välise mahuga on see tegelikult valikulise summeerimisfunktsiooni tavaline kasutusjuht SUMMESLIMNA, mis summeerib antud linna ja kuu reanumbrid. Jällegi, kuna meil ei ole samal kuul samas linnas mitut konteinerit, siis tegelikult annab meie funktsioon välja mitte summa, vaid rea numbri enda. Ja siis juba eelmisest meetodist tuttav funktsioon INDEX Samuti saate eraldada konteinerite koode:
Loomulikult ei pea sel juhul enam mõtlema kokkuvõtte uuendamisele, vaid suurtel tabelitel on funktsioon SUMMESLI võib olla märgatavalt aeglane. Seejärel peate valemite automaatse värskendamise välja lülitama või kasutama esimest meetodit – pivot tabelit.
Kui kokkuvõtte välimus ei ole teie aruande jaoks eriti sobiv, saate sellest reanumbrid lõpptabelisse eraldada mitte otse, nagu meie tegime, vaid funktsiooni abil GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Kuidas seda teha, leiad siit.
- Kuidas luua aruannet liigendtabeli abil
- Arvutuste seadistamine liigendtabelites
- Valikuline loendamine SUMIFS-i, COUNTIFS-iga jne.