Pivottabeller er bra for alle – de kalkulerer raskt og er fleksibelt konfigurert, og designet kan vikles elegant inn i dem, om nødvendig. Men det er også noen få flue i salven, spesielt manglende evne til å lage et sammendrag, der verdiområdet ikke skal inneholde tall, men tekst.
La oss prøve å omgå denne begrensningen og komme opp med et "par krykker" i en lignende situasjon.
Anta at selskapet vårt transporterer produktene sine i containere til flere byer i vårt land og Kasakhstan. Containere sendes ikke mer enn én gang i måneden. Hver beholder har et alfanumerisk nummer. Som innledende data er det en standardtabell som viser leveranser, hvorfra du må lage en slags oppsummering for tydelig å se antall containere sendt til hver by og hver måned:
For enkelhets skyld, la oss gjøre tabellen med de første dataene "smarte" på forhånd ved å bruke kommandoen Hjem – Formater som en tabell (Hjem — Formater som tabell) og gi henne et navn leveransene tab Constructor (Design). I fremtiden vil dette forenkle livet, fordi. det vil være mulig å bruke navnet på tabellen og dens kolonner direkte i formlene.
Metode 1. Den enkleste – bruk Power Query
Power Query er et superkraftig verktøy for å laste og transformere data i Excel. Dette tillegget har vært innebygd i Excel som standard siden 2016. Hvis du har Excel 2010 eller 2013, kan du laste det ned og installere det separat (helt gratis).
Hele prosessen, for klarhet, analyserte jeg trinn for trinn i følgende video:
Hvis det ikke er mulig å bruke Power Query, kan du gå andre veier – gjennom en pivottabell eller formler.
Metode 2. Hjelpeoppsummering
La oss legge til en kolonne til i vår opprinnelige tabell, der vi ved hjelp av en enkel formel beregner antallet av hver rad i tabellen:
Det er klart at -1 er nødvendig, fordi vi har en en-linjes overskrift i tabellen vår. Hvis tabellen ikke er i begynnelsen av arket, kan du bruke en litt mer kompleks, men universell formel som beregner forskjellen i tallene på gjeldende rad og tabelloverskriften:
Nå vil vi på en standard måte bygge en pivottabell av ønsket type basert på våre data, men i verdifeltet vil vi slippe feltet Linjenummer i stedet for det vi ønsker container:
Siden vi ikke har flere containere i samme by i samme måned, vil oppsummeringen vår faktisk ikke gi ut beløpet, men linjenumrene til containerne vi trenger.
I tillegg kan du slå av hoved- og delsummer på fanen Konstruktør – Generelle totaler и delsummer (Design – Grand Totals, Subtotals) og på samme sted bytt sammendraget til et mer praktisk tabelloppsett med knappen Rapporter mockup (Rapportoppsett).
Dermed er vi allerede halvveis til resultatet: vi har en tabell der det, i skjæringspunktet mellom byen og måneden, er et radnummer i kildetabellen, der containerkoden vi trenger ligger.
La oss nå kopiere sammendraget (til samme ark eller et annet) og lime det inn som verdier, og deretter skrive inn formelen vår i verdiområdet, som vil trekke ut beholderkoden med linjenummeret som finnes i sammendraget:
Funksjon IF (HVIS), i dette tilfellet, sjekker at neste celle i sammendraget ikke er tom. Hvis den er tom, skriv ut en tom tekststreng "", dvs. la cellen stå tom. Hvis den ikke er tom, trekk ut fra kolonnen Container kildetabell leveransene celleinnhold etter radnummer ved hjelp av funksjon INDEX (INDEKS).
Kanskje det eneste ikke helt åpenbare poenget her er dobbeltordet Container i formelen. En så merkelig form for skriving:
Rekvisita[[Container]:[Container]]
… er bare nødvendig for å referere til kolonnen Container var absolutt (som en referanse med $-tegn for vanlige "ikke-smarte" tabeller) og gled ikke til nabokolonner når formelen vår ble kopiert til høyre.
I fremtiden, når du endrer dataene i kildetabellen leveransene, må vi huske å oppdatere hjelpesammendraget vårt med linjenummer ved å høyreklikke på det og velge kommandoen Oppdater og lagre (Forfriske).
Metode 3. Formler
Denne metoden krever ikke opprettelse av en mellomliggende pivottabell og manuell oppdatering, men bruker Excels "tunge våpen" - funksjonen SUMMESLIMN (SUMIFS). I stedet for å slå opp radnummer i et sammendrag, kan du beregne dem ved å bruke denne formelen:
Med en viss ekstern voluminøse er dette faktisk en standard brukstilfelle for den selektive summeringsfunksjonen SUMMESLIMNA som summerer radnumrene for den gitte byen og måneden. Igjen, siden vi ikke har flere containere i samme by i samme måned, vil funksjonen vår faktisk ikke gi ut beløpet, men selve linjenummeret. Og så funksjonen som allerede er kjent fra forrige metode INDEX Du kan også trekke ut beholderkoder:
Selvfølgelig, i dette tilfellet trenger du ikke lenger tenke på å oppdatere sammendraget, men på store tabeller er funksjonen SUMMESLI kan være merkbart treg. Da må du slå av automatisk oppdatering av formler, eller bruke den første metoden - en pivottabell.
Hvis utseendet til sammendraget ikke er særlig egnet for rapporten din, kan du trekke ut radnummer fra den inn i slutttabellen ikke direkte, som vi gjorde, men ved å bruke funksjonen FÅ.PIVOT.TABELL.DATA (GET.PIVOT.DATA). Hvordan du gjør dette finner du her.
- Hvordan lage en rapport ved hjelp av en pivottabell
- Hvordan sette opp beregninger i pivottabeller
- Selektiv telling med SUMIFS, COUNTIFS, etc.