Pivottabeller är bra för alla – de beräknar snabbt och är flexibelt konfigurerade och designen kan lindas elegant in i dem om det behövs. Men det finns också ett fåtal fluga i salvan, i synnerhet oförmågan att skapa en sammanfattning, där värdeområdet inte ska innehålla siffror, utan text.
Låt oss försöka komma runt denna begränsning och komma på ett "par kryckor" i en liknande situation.
Anta att vårt företag transporterar sina produkter i containrar till flera städer i vårt land och Kazakstan. Containers skickas inte mer än en gång i månaden. Varje behållare har ett alfanumeriskt nummer. Som första data finns det en standardtabell över leveranser, från vilken du måste göra någon form av sammanfattning för att tydligt se antalet containrar som skickas till varje stad och varje månad:
För enkelhetens skull, låt oss göra tabellen med de initiala uppgifterna "smarta" i förväg med hjälp av kommandot Hem – Formatera som en tabell (Hem — Formatera som tabell) och ge henne ett namn Leveranser fliken Konstruktör (Design). I framtiden kommer detta att förenkla livet, eftersom. det kommer att vara möjligt att använda namnet på tabellen och dess kolumner direkt i formlerna.
Metod 1. Det enklaste – använd Power Query
Power Query är ett superkraftigt verktyg för att ladda och transformera data i Excel. Detta tillägg har byggts in i Excel som standard sedan 2016. Om du har Excel 2010 eller 2013 kan du ladda ner och installera det separat (helt gratis).
Hela processen, för tydlighetens skull, analyserade jag steg för steg i följande video:
Om det inte är möjligt att använda Power Query kan du gå andra vägar – genom en pivottabell eller formler.
Metod 2. Hjälpsammanfattning
Låt oss lägga till ytterligare en kolumn till vår ursprungliga tabell, där vi med en enkel formel beräknar antalet av varje rad i tabellen:
Uppenbarligen behövs -1, eftersom vi har en enradshuvud i vår tabell. Om din tabell inte är i början av arket kan du använda en lite mer komplex, men universell formel som beräknar skillnaden mellan numren på den aktuella raden och tabellhuvudet:
Nu kommer vi på ett vanligt sätt att bygga en pivottabell av önskad typ baserat på våra data, men i värdefältet släpper vi fältet Linje nummer istället för vad vi vill behållare:
Eftersom vi inte har flera containrar i samma stad under samma månad, kommer vår sammanfattning i själva verket inte att ge ut mängden utan radnumren på de containrar vi behöver.
Dessutom kan du stänga av stor- och delsummor på fliken Konstruktör – Allmänna summor и delsummor (Design – totalsummor, delsummor) och på samma ställe växla sammanfattningen till en mer bekväm tabelllayout med knappen Rapportera mockup (Rapportlayout).
Därmed är vi redan halvvägs till resultatet: vi har en tabell där det, i skärningspunkten mellan stad och månad, finns ett radnummer i källtabellen, där behållarkoden vi behöver ligger.
Låt oss nu kopiera sammanfattningen (till samma ark eller annat) och klistra in den som värden och skriv sedan in vår formel i värdeområdet, vilket extraherar behållarkoden med radnumret som finns i sammanfattningen:
Funktion IF (OM), i det här fallet, kontrollerar att nästa cell i sammanfattningen inte är tom. Om den är tom, mata ut en tom textsträng "", dvs lämna cellen tom. Om den inte är tom, extrahera sedan från kolumnen Behållare källtabell Leveranser cellinnehåll efter radnummer med funktionen INDEX (INDEX).
Den enda inte särskilt uppenbara punkten här är kanske dubbelordet Behållare i formeln. En sådan konstig form av skrivande:
Tillbehör[[Behållare]:[Behållare]]
… behövs bara för att referera till kolumnen Behållare var absolut (som en referens med $-tecken för vanliga "icke-smarta" tabeller) och gled inte till angränsande kolumner när vi kopierade vår formel till höger.
I framtiden, när du ändrar data i källtabellen Leveransermåste vi komma ihåg att uppdatera vår hjälpsammanfattning med radnummer genom att högerklicka på den och välja kommandot Uppdatera & spara (Uppdatera).
Metod 3. Formler
Denna metod kräver inte skapandet av en mellanliggande pivottabell och manuell uppdatering, utan använder Excels "tunga vapen" - funktionen SUMMESLIMN (SUMIFS). Istället för att slå upp radnummer i en sammanfattning kan du beräkna dem med den här formeln:
Med viss extern skrymmande är detta faktiskt ett standardanvändningsfall för den selektiva summeringsfunktionen SUMMESLIMNA som summerar radnumren för den givna staden och månaden. Återigen, eftersom vi inte har flera containrar i samma stad under samma månad, kommer vår funktion i själva verket inte att ge ut beloppet utan själva radnumret. Och sedan funktionen som redan är bekant från den tidigare metoden INDEX Du kan också extrahera behållarkoder:
Naturligtvis, i det här fallet behöver du inte längre tänka på att uppdatera sammanfattningen, men på stora tabeller, funktionen SUMMESLI kan vara märkbart långsam. Då måste du stänga av automatisk uppdatering av formler, eller använda den första metoden - en pivottabell.
Om sammanfattningens utseende inte är särskilt lämplig för din rapport, kan du extrahera radnummer från den till sluttabellen inte direkt, som vi gjorde, utan med hjälp av funktionen HÄMTA.PIVOTTABELLDATA (GET.PIVOT.DATA). Hur du gör detta hittar du här.
- Hur man skapar en rapport med hjälp av en pivottabell
- Hur man ställer in beräkningar i pivottabeller
- Selektiv räkning med SUMIFS, COUNTIFS, etc.