Du kanske känner till Microsoft-avdelningen vi-som-inte- alltid-ger-nya-funktioner-jättecoola-och-snitsiga-namn? Dynamiska matriser, där har du ett till exempel. Japp, du ser rätt. Detta är ännu ett tillfälle då namnet får en att somna, fast funktionen är så sjukt cool att du snart lär sprätta igång igen.
Dynamiska matriser (också kallade spillfunktioner) är funktioner som genererar mer än en rad som svar. Om du i A1 skriver =10+5 förväntar du dig ett svar. Alltså ett enda svar. Inte flera rader eller flera kolumner.
Här nedan kommer några av mina favoriter. Alla dessa kan ta en annan spillfunktions resultat som input, men de måste inte göra det. De kan ta vanliga cellintervaller också, som A1:A10. Du kan också använda en eller flera av kolumnerna inuti en tabell som argument. Så om du har en tabell du har döpt till Tabell1 och den har en kolumn med rubriken Skådisar, kan du använda Tabell1[Skådisar] som indata.
Felmeddelandet SPILL är något som de flesta som använder en modern version av Excel har sett någon gång. Och det är kopplat till exakt det här. Du har skrivit en spillfunktion (also known as dynamisk matris-funktion) som försöker svara med fler rader än vad den får plats med. Exempelvis kanske den försöker ge tillbaka sju rader, men så har du skrivit något i en av de sju cellerna som Excel försöker skriva i. Då får den inte plats med alla svaren och skriver istället just SPILL.
Unik
Om du i B1 istället skriver =UNIK(A1:A10) kommer funktionen att svara med flera rader. Den kommer faktiskt att ge tillbaka allt innehåll som finns i cellerna A1 till A10, men endast ta med varje namn/produkt eller vad det nu står skrivet i A1 till A10 en enda gång var.
Om du har en lista med namn och några namn står med mer än en gång, kommer du att få en kortare lista i B1. Du kanske får sju rader i B1 och då i B2, B3 och så vidare hela vägen ner till B7, om det är så att det finns sju unika namn i A1 till A10. Okej, okej, okej – varför ligger David vaken på nätterna och tänker på detta? Så här ligger det till. Normalt sett brukar du skriva in en funktion i en cell och få ett svar. Om du exempelvis skriver =1+1 i en cell får du ett svar enbart i den cellen. Nu till specialen. Excel kan nämligen svara i flera celler. Det innebär också att du kan ta svaret (som är flera rader) och skicka in raderna i nästa funktion. Du kan åberopa B1 från exemplet ovan som B1# för att få alla rader som B1 fått fram.
Unik är för övrigt intressant, eftersom du kan få den att antingen jämföra rader (vilket är standardinställningen) eller kolumner. Detta styr du med argument två (det första argumentet är såklart vilket område den ska jobba med), där du anger sant för att den ska gå på kolumner och falskt för rader.
Sortera
Sortera kan använda en spillfunktions resultat som input, men den måste inte det. I en annan cell kan du skriva =sortera(B1#) för att få hela listan inte bara rensad från dubbletter, utan också sorterad i alfabetisk ordning.
Om du vill kan du ge funktionen fler argument. Det andra argumentet är vilken kolumn eller rad den ska sortera på. Det här är bra om du exempelvis har flera kolumner (kanske förnamn, efternamn och mejladress), vill att den ska sortera på efternamnskolumnen och även vill kunna välja hur den ska sortera.
Sorteringsordningen styr du med det tredje argumentet – ange 1 för att sortera A–Ö och -1 för Ö–A. Det sista argumentet är extra snurrigt och används om du vill att funktionen ska sortera per rader eller per kolumner.
=sortera(A1:C10 ; 1; 1 )
Sortera innehållet i A1 till C10 på första kolumnen och sortera från A–Ö.
=sortera(A1:C10 ; 2; -1 )
Sortera innehållet i A1 till C10 på andra kolumnen och sortera från Ö–A.
Tips!
Du kan faktiskt sortera på flera nivåer. Säg att du har efternamn i kolumn 2 och förnamn i kolumn 1, då kan du sortera på både efternamn och förnamn genom att skriva
=SORTERA( SORTERA(A1:B10 ; 2) ; 1).
Filter
Filter heter likadant både i svenska och engelska Excel, och det är nästa fantastiska spillfunktion. Den filtrerar ett område och ger dig endast de rader som du inte vill filtrera bort. Det här är näst intill genialt, för du kan till exempel säga =FILTER( A1:B10 ; A1:A10=”Filip”) för att få alla rader där det står Filip i kolumnen, och då får du också det som står bredvid Filip i B-kolumnen. Du kan också skriva =FILTER( A1:B10 ; A1:A10<>”Filip” ) för att få alla rader – förutom dem där det står Filip.
Om du vill ta bort alla rader där Filip står i A-kolumnen – och det också står 103 i B-kolumnen – skriver du =FILTER(A1:B10;(A1:A10<>”Filip”)+(B 1:B10<>103)). Du lägger alltså ihop de villkor som ska filtreras bort.
Om du istället multiplicerar villkoren tar funktionen antingen bort där det står Filip i A-kolumnen eller Göran i B-kolumnen. Funktionen =FILTER(A1:B10;(A1:A10< >”Filip”)*(B1:B10<>”Göran”)) gör alltså att du får alla rader där det inte står Filip och alla rader där det inte står Göran i B-kolumnen.
Alibaba Kebab Hamburgerbar Intercity Salon
Under tio år av min barndom bodde jag och min familj i Ludvika i södra Dalarna. Detta var på det neonfärgade åttiotalet. I Ludvika fanns det en hamburgerbar som hette Alibaba Kebab Hamburgerbar Intercity Salon. Den person, vem det nu var, som döpte detta härliga ställe hade verkligen ett sinne för överraskande snygga satsmelodier.
Säg att du idag är restaurangchef på just den här restaurangen och ska kika lite på menyerna – som du såklart bearbetar i Excel.
Du har huvudrätten i A-kolumnen, tillbehöret i B och typen av sallad i C. Nu vill du filtrera fram alla måltider som innehåller pommes. Då kan du skriva =FILTER(A1:C25 ; B1:B25=”Pommes”).
Vill du få fram alla måltider som innehåller antingen pommes i B-kolumnen eller tomat i C-kolumnen kan du skriva =FILTER(A1:C25 ; (B1:B25=”Pommes”)+(C1 :C25=”Tomatskiva”)).
Är du mer sugen på nyttigare alternativ, där du får ris som tillbehör och också röd sås från C-kolumnen? Skriv då =FILTER(A1:C25 ;(B1:B25=”Ris”)*(C1:C25=”Röd sås”)).
Slumpmatris
Jag använder en slumpmatris i nästan varje torsdagstips jag spelar in, för ofta behöver jag ett antal siffror. Slumpmatrisen skapar helt enkelt ett antal rader och kolumner med slumptal mellan två värden. För att skapa två kolumner med tio rader av slumptal mellan 50 och 100 skriver du =SLUMPMATRIS(10; 2; 50; 100; SANT).
Ordet sant beskriver att det bara ska vara heltal utan decimaler.
Textjoin
Här är en till funktion som är otroligt fiffig. Den här slår ihop dynamiska matriser igen så att du kan få alla celler på en rad. Du kan dessutom bestämma om du vill ha skiljetecken mellan varje cell du slår ihop. Om du exempelvis har en namnlista och vill ha ett mellanslag mellan varje namn kan du helt enkelt få det. Funktionen heter för övrigt textjoin både på svenska och engelska.
=TEXTJOIN(” ”; sant; B1#) ger dig alla rader eller kolumner som B1:s spillfunktion svarar med och ett mellanslag mellan varje. Det andra argumentet (sant) bestämmer om funktionen ska hoppa över tomma celler eller om den ska ha med dem i uppräkningen.
Sekvens
Skriv =SEKVENS(10) så får du tio siffror – en på varje rad. Det är faktiskt mycket mer användbart än det låter. Om du behöver en lista på alla år från 1945 till 2024, skriv =SEKVENS(80)+1944 (en sekvens börjar med 1, så 1944 + 1 blir första talet).
Vill du få tio kolumner istället för rader? Då ska du skriva in siffran tio som argument två så här: =SEKVENS ( ; 10). Du kan även kombinera exempelvis fem rader med tre kolumner genom att skriva =SEKVENS(5;3).
Hstack och vstack
Jag skulle ha kunnat fortsätta hur länge som helst med just dynamiska matriser och spillfunktioner, men jag måste väl lämna lite innehåll till nästa bok? Hur som helst – vi tar två grymt bra funktioner till: hstack och vstack.
De här funktionerna slår ihop två områden och lägger dem bredvid varandra – eller ovanför och under varandra. Det handlar om att slå samman två vanliga områden med celler (exempelvis A1:A5 och C7:C9).
Vill du skapa ett dynamiskt område av dessa två kan du skriva =HSTACK(A1:A5 ; C7:C9 ). Källan in i hstack kan vara två spillfunktioner, kanske där antalet kolumner och rader varierar över tiden. Antingen för att deras indata ändras eller för att de filtrerar fram något. Nu vill du slå ihop dem och få ett dynamiskt område som är resultatet av båda två. Då kan du enkelt göra det med =HSTACK(A1# ; B1# ) för att få dem bredvid varandra i sidled (ledtråden är h som i horisontellt).
Vill du istället ha dem ovanpå varandra använder du vstack. ”Jaha”, tänker du, ”vad är det som är så himla bra med att kunna styra det vertikala och horisontella?” Jo, säg att du redan har ett medlemsregister. Nu får du även en lista med potentiella medlemmar. Din uppgift är att skicka ut ett innehåll till båda listorna. Då kan du enkelt kombinera dem med en vstack.
Det fina är också att områdena inte måste vara lika stora. Du kan slå ihop ett område med tre kolumner och tre rader med ett annat område med fem kolumner och åtta rader.
Sedan kan du såklart också sortera hela klabbet: =SORTERA(VSTACK(… Fatta vad bra!
Ps. Jag känner en kille som byggt en sudokulösare enbart med hjälp av dynamiska matriser. Say no more.
Pps. Det stämmer att detta är en nygammal funktion. Det har funnits liknande funktioner i äldre versioner av Excel, men de blev aldrig så här bra.
Vill du veta mer?
Det du har läst är ett utdrag från boken Mer Excel helt enkelt. Här hittar du ett smakprov från boken, länkar till var du kan köpa den och även information om författaren.