Pivotborde er gode for alle – de beregner hurtigt og er konfigureret fleksibelt, og designet kan efter behov vikles elegant ind i dem. Men der er også et par flue i salven, især manglende evne til at lave et resumé, hvor værdiområdet ikke skal indeholde tal, men tekst.
Lad os prøve at omgå denne begrænsning og komme med et "par krykker" i en lignende situation.
Antag, at vores virksomhed transporterer sine produkter i containere til flere byer i vores land og Kasakhstan. Containere sendes højst én gang om måneden. Hver beholder har et alfanumerisk nummer. Som indledende data er der en standardtabel med leveringer, hvorfra du skal lave en form for oversigt for tydeligt at se antallet af containere sendt til hver by og hver måned:
Lad os for nemheds skyld gøre tabellen med de indledende data "smart" på forhånd ved hjælp af kommandoen Hjem – Formater som en tabel (Hjem — Formater som tabel) og giv hende et navn Leverancer fanen Constructor (Design). I fremtiden vil dette forenkle livet, fordi. det vil være muligt at bruge navnet på tabellen og dens kolonner direkte i formlerne.
Metode 1. Den nemmeste – brug Power Query
Power Query er et superkraftigt værktøj til at indlæse og transformere data i Excel. Denne tilføjelse har været indbygget i Excel som standard siden 2016. Hvis du har Excel 2010 eller 2013, kan du downloade og installere den separat (helt gratis).
Hele processen, for klarhedens skyld, analyserede jeg trin for trin i følgende video:
Hvis det ikke er muligt at bruge Power Query, så kan du gå andre veje – gennem en pivottabel eller formler.
Metode 2. Hjælpeoversigt
Lad os tilføje en kolonne mere til vores oprindelige tabel, hvor vi ved hjælp af en simpel formel beregner antallet af hver række i tabellen:
Det er klart, at der er brug for -1, fordi vi har en en-linjes overskrift i vores tabel. Hvis din tabel ikke er i begyndelsen af arket, kan du bruge en lidt mere kompleks, men universel formel, der beregner forskellen i tallene på den aktuelle række og tabeloverskriften:
Nu vil vi på en standard måde bygge en pivottabel af den ønskede type baseret på vores data, men i værdifeltet vil vi droppe feltet Linjenummer i stedet for hvad vi ønsker container:
Da vi ikke har flere containere i samme by i samme måned, vil vores oversigt faktisk ikke oplyse mængden, men linjenumrene på de containere, vi skal bruge.
Derudover kan du slå hoved- og subtotaler fra på fanen Konstruktør – Generelle totaler и subtotaler (Design – hovedtotaler, subtotaler) og samme sted skifter oversigten til et mere bekvemt tabellayout med knappen Rapportér mockup (Rapportlayout).
Dermed er vi allerede halvvejs til resultatet: Vi har en tabel, hvor der i skæringspunktet mellem by og måned er et rækkenummer i kildetabellen, hvor den containerkode, vi skal bruge, ligger.
Lad os nu kopiere oversigten (til det samme ark eller et andet) og indsætte det som værdier, og derefter indtaste vores formel i værdiområdet, som vil udtrække beholderkoden ved linjenummeret fundet i oversigten:
Funktion IF (HVIS), i dette tilfælde, kontrollerer, at den næste celle i oversigten ikke er tom. Hvis den er tom, så udskriv en tom tekststreng "", dvs. lad cellen være tom. Hvis den ikke er tom, så udtræk fra kolonnen Container kildetabel Leverancer celleindhold efter rækkenummer ved hjælp af funktion INDEX (INDEKS).
Måske er det eneste ikke særlig indlysende punkt her dobbeltordet Container i formlen. Sådan en mærkelig form for skrivning:
Forsyning[[Beholder]:[Beholder]]
… er kun nødvendig for at referere til kolonnen Container var absolut (som en reference med $-tegn for almindelige "ikke-smarte" tabeller) og gled ikke til nabokolonner, når vi kopierede vores formel til højre.
I fremtiden, når du ændrer dataene i kildetabellen Leverancer, skal vi huske at opdatere vores hjælpeoversigt med linjenumre ved at højreklikke på den og vælge kommandoen Opdater & Gem (Opdater).
Metode 3. Formler
Denne metode kræver ikke oprettelse af en mellemliggende pivottabel og manuel opdatering, men bruger Excels "tunge våben" - funktionen SUMMESLIMN (SUMIFS). I stedet for at slå rækkenumre op i en oversigt, kan du beregne dem ved hjælp af denne formel:
Med en vis ydre omfang er dette faktisk et standardbrugstilfælde for den selektive summeringsfunktion SUMMESLIMNA, der summerer rækkenumrene for den givne by og måned. Igen, da vi ikke har flere containere i samme by i samme måned, vil vores funktion faktisk ikke udlevere beløbet, men selve linjenummeret. Og så den funktion, der allerede er kendt fra den tidligere metode INDEX Du kan også udtrække containerkoder:
Selvfølgelig skal du i dette tilfælde ikke længere tænke på at opdatere oversigten, men på store tabeller er funktionen SUMMESLI kan være mærkbart langsom. Så bliver du nødt til at slå automatisk opdatering af formler fra, eller bruge den første metode - en pivottabel.
Hvis opsummeringens udseende ikke er særlig velegnet til din rapport, så kan du trække rækkenumre fra den ind i sluttabellen ikke direkte, som vi gjorde, men ved hjælp af funktionen FÅ.PIVOT.TABEL.DATA (GET.PIVOT.DATA). Hvordan du gør dette kan du finde her.
- Sådan opretter du en rapport ved hjælp af en pivottabel
- Sådan opsætter du beregninger i pivottabeller
- Selektiv tælling med SUMIFS, COUNTIFS osv.