Arbeta med matriser i Excel Det har gått från att vara något "magiskt" och reserverat för avancerade användare till att bli ett vardagligt verktyg tack vare dynamiska matrisfunktioner och ankomsten av LAMBDA och MAPOm alla dessa namn låter bekanta men du inte är helt säker på hur du ska få in dem i ditt kalkylblad, ska vi förklara dem lugnt och med tydliga exempel.
I den här artikeln får du se hur skapa, transformera och analysera matriser med hjälp av LAMBDA, MAP och klassiska matrisformler, från enkla fall (som att tillämpa ett minimum mellan två listor) till mer kraftfulla användningsområden, som filtrera data efter villkorIgnorera fel eller räkna skillnader mellan intervall. Tanken är att du får en arsenal av formler redo att användas i dina Excel-filer, utan rädsla för de fruktade arrayformlerna.
Vad är MAP och hur kombineras det med LAMBDA i Excel?
Funktionen KARTA Det är en av huvudfunktionerna i nya Excel: den låter dig iterera genom en eller flera arrayer (intervall) och returnera en ny matris där varje element är resultatet av att en LAMBDA-funktion tillämpas på de ursprungliga värdena. I praktiken är det som att säga till Excel: "gå cell för cell och utför denna operation med varje värde, och returnera hela mängden som en array."
Den grundläggande syntaxen för MAP på spanska är: =MAP(array1; lambda_o_array<#>)Det första argumentet, matris1, är det område eller den array du vill arbeta med; det sista argumentet måste alltid vara ett LAMBDA eller, i vissa avancerade scenarier, en annan matris. Den LAMBDA-matrisen måste ha en parameter för var och en av de matriser som skickas till MAP.
Det är viktigt att förstå det MAP kan ta emot flera arrayer (till exempel två parallella kolumner) och LAMBDA hanterar bearbetningen av värden som är på samma position. Detta är särskilt användbart när du behöver jämföra, kombinera eller beräkna något från två eller flera datalistor, utan att behöva dra-och-släppa formler eller manuell kopiering.
När LAMBDA-argumentet för MAP inte är väldefinierat, eller antalet parametrar för LAMBDA inte matchar antalet arrayer som skickas till funktionen, returnerar Excel ett fel #VÄRDE! med meddelandet ”Felaktiga parametrar”Detta är vanligtvis det första symptomet på att en parameter saknas eller är över i definitionen av LAMBDA-funktionen.
Hur LAMBDA- och MAP-syntaxen fungerar steg för steg
För att få ut det mesta av MAP behöver du vara bekväm med att använda funktionen. LAMBDAEn Lambda-funktion i Excel låter dig definiera en sorts "anpassad funktion" direkt i en formel, komplett med dess parametrar och interna beräkningar, utan behov av VBA eller makron. MAP förlitar sig på denna Lambda-funktion för att logiskt tillämpa en operation på varje element i arrayerna.
Den allmänna idén är att du skriver något i stil med detta: =MAP(intervall; LAMBDA(x; operation_med_x))Där x Den här parametern representerar varje enskilt värde i arrayen. Så snart du trycker på Enter itererar Excel igenom arrayen och returnerar en annan array av samma storlek där operationen redan är tillämpad. Om flera arrayer används kommer LAMBDA-funktionen att ha flera parametrar (till exempel a och b), och operationen kommer att kombinera dem.
Till exempel stöder MAP även anrop till logiska funktioner såsom OM, OCH, ELLER eller OCH, förutsatt att de används korrekt i arrayläge. Observera att funktioner som OCH eller ELLER, när de används direkt, returnerar ett enda SANT/FALSKT värde; därför används aritmetiska kombinationer (summor och produkter) med villkor i många avancerade scenarier för att imitera ELLER- eller OCH-beteende per element.
Dessutom är MAP helt kompatibel med andra dynamiska matrisfunktioner som t.ex. FILTRERA, BYROW, BYCOL, FÖRMINSKA, SCANNA eller GÖR MATRITTFaktum är att den verkliga kraften kommer när man kedjar samman flera av dessa funktioner, vilket gör att en matris beräknad med MAP kan användas som ett filtreringskriterium, som en ackumulator eller som primär indata till andra beräkningar.
LAMBDA:s interna struktur måste alltid respekteras: först parametrar (namnen du kommer att använda inuti funktionen), separerade med semikolon, och i slutet placerar du beräkningsuttryck som använder dessa parametrar. Om du glömmer en parameter, eller använder en i uttrycket som du inte har deklarerat, kommer du att stöta på felet om felaktiga parametrar igen.
Praktiska exempel med MAP och LAMBDA i matriser
En mycket klassisk användning av MAP är att tillämpa en matematisk-logisk operation på varje element i en matris av talAnta till exempel att du har data i intervallet A1:C2 och du vill att Excel ska beräkna kvadraten av varje värde endast om det är större än 4; annars vill du behålla det ursprungliga värdet.
I den situationen skulle en möjlig formel vara: =MAP(A1:C2; LAMBDA(a; SI(a>4; a*a; a)))MAP läser varje värde från A1:C2 och tilldelar det till parametern a LAMBDA, med hjälp av SI, avgör om värdet ska kvadreras eller lämnas som det är. Resultatet är en ny, helt dynamisk matris av samma storlek, som uppdateras om du ändrar någon av de ursprungliga uppgifterna.
Ett annat intressant scenario är när du behöver inspektera två kolumner med logiska värden (SANT/FALSKT) och veta när båda är SANT samtidigt. Tänk dig att du har en tabell som heter "TabellA", med kolumnerna Kolumn1 och Kolumn2, och du vill ha en tredje kolumn som anger om båda fälten är sanna på varje rad.
Formeln i resultatkolumnen kan vara: =MAP(TabellA; TabellA; LAMBDA(a; b; Y(a; b)))Här itererar MAP genom båda kolumnerna parallellt, och för varje värdepar (a, b) tillämpas OCH-funktionen, som bara returnerar SANT om båda parametrarna är sanna. Resultatet är en vertikal array av SANT/FALSKT-värden som automatiskt anpassar sig till tabellens storlek.
Det är också möjligt att kombinera MAP med andra dynamiska funktioner som t.ex. SCREENING för att utföra avancerade val. Anta till exempel att du har en tabell med två kolumner: Storlek (i D2:D11) och Färg (i E2:E11), och du vill bara behålla de rader där storleken är "Stor" och färgen är "Röd".
Ett elegant sätt att göra detta skulle vara: =FILTER(D2:E11; MAP(D2:D11; E2:E11; LAMBDA(s; c; Y(s=»Stor»; c=»Röd»))))MAP utvärderar varje rad genom att kombinera värdena för storlek (s) och färg (c), returnerar TRUE endast om båda uppfyller villkoren, och FILTER använder TRUE/FALSE-arrayen för att avgöra vilka rader som ska behållas.
Specifikt exempel: minsta element-för-element-förhållande mellan två dynamiska arrayer
Ett mycket vanligt fall när man arbetar med data är att ha två kolumner eller två matriser av samma längd och vill skapa en tredje matris med minimumvärdet för varje elementpar. Eller med andra ord: för varje rad vill du ha MIN(A_i, B_i) mellan värdena MATRIX-A och MATRIX-B.
Tänk dig till exempel dessa två vertikala kolumner och förväntat resultat:
| MATRIX-A (VERTIKAL) | MATRIX-B (VERTIKAL) | RESULTAT (VERTIKAL) |
|---|---|---|
| 4 | 5 | MIN(4; 5) |
| 2 | 1 | MIN(2; 1) |
| 3 | 4 | MIN(3; 4) |
Om båda listorna är statiska kan du skriva =MIN(A1; B1) och dra ner. Men när vi pratar om dynamiska matriser vars storlek ändras, blir denna metod opraktisk. Du behöver en formel som returnerar hela resultatkolumnen på en gång och automatiskt justerar dess storlek när inmatningsmatriserna ändras.
Kombinationen MAP + LAMBDA passar perfekt med detta problem: =MAP(MATRIX_A; MATRIX_B; LAMBDA(x; y; MIN(x; y)))Här itererar MAP genom de två listorna element för element, tilldelar värden till parametrarna x och y, och LAMBDA returnerar MIN(x, y). Resultatet är en ny matris som alltid har samma längd som de ursprungliga matriserna, och anpassar sig utan att man behöver dra formler eller fylla i dem manuellt.
Denna lösning respekterar Excels matrismetod helt och hållet och undviker den största nackdelen med MIN-funktionen, vilken tenderar att returnera ett enda minimivärde När en matris skickas till den; tack vare MAP, får vi utvärderingen att göras rad för rad, vilket returnerar lika många minima som det finns rader.
Avancerade matrisformler med klassiska Excel-funktioner
Även om LAMBDA och MAP har moderniserat arbetet med arrayer, har Excel i åratal möjliggjort kraftfulla arrayformler med mer traditionella funktioner som SUMMA, OM, ESROR, MEDELVÄRDE eller MAX och andra sök- och referensfunktionerSkillnaden idag är att det med dynamiska matriser inte längre är nödvändigt att bekräfta dessa formler med speciella tangentkombinationer (Ctrl+Shift+Enter i äldre versioner); tryck helt enkelt på Enter.
Ett mycket vanligt problem är att summera ett område som innehåller fel (#N/A, #DIV/0!, etc.). Om du använder en direkt SUMMA på det området får du ett fel istället för ett numeriskt resultat. För att åtgärda detta kan du "rensa" arrayen internt med en formel som denna: =SUMMA(OM(ÄRFEL(Data); ""; Data))ISERROR upptäcker motstridiga celler, IF ersätter fel med tomma strängar och lämnar korrekta värden intakta, och SUM adderar bara de senare.
Genom att följa en liknande idé kan du också räkna hur många fel det finns i ett område med namnet Data. En första version skulle vara =SUMMA(OM(ÄRFEL(Data); 1; 0))vilket genererar en array med ettor för varje cell med ett fel och 0or för de utan; SUMMA ger dig det totala antalet fel. Om du utelämnar det tredje argumentet för OM kan formeln lämnas kvar som =SUMMA(OM(ÄRFEL(Data); 1))eftersom OM returnerar FALSKT för celler utan fel, och dessa FALSKT-värden påverkar inte summan.
Det kan förenklas ytterligare tack vare ett litet logiskt knep: =SUMMA(OM(ÄR FEL(Data)*1))I det här fallet utnyttjas det faktum att TRUE*1 är lika med 1 och FALSE*1 är lika med 0; på detta sätt producerar ISERROR(Data) en matris av TRUE/FALSE och när den multipliceras med 1 omvandlas den till nollor och ettor, perfekt för addition.
Villkorliga summor och medelvärden med matrislogik
Matrisformler är också mycket användbara när man gör villkorade summor utan att alltid använda SUMMAOM eller SUMMAOM. Ett klassiskt exempel: summera endast de positiva värdena i ett område som heter Försäljning. Matrisformeln skulle vara =SUMMA(OM(Försäljning>0; Försäljning)), där OM skapar en array med de positiva värdena och returnerar FALSKT för resten, vilket SUMMA ignorerar.
När du vill tillämpa mer än ett villkor samtidigt kan du kombinera jämförelser multiplikativt. För att till exempel summera försäljningsvärden som är större än 0 och mindre än eller lika med 5 kan du använda =SUMMA((Försäljning>0)*(Försäljning<=5)*(Försäljning))Varje jämförelse genererar en SANT/FALSKT-matris, och multiplikationen av dem resulterar i ett enda värde där alla villkor är uppfyllda.
En viktig detalj: denna teknik för att multiplicera villkor kräver att intervallet innehåller endast numeriska värdenOm det finns textceller eller tomma celler som inte hanteras korrekt kan du få oväntade fel. Därför är det alltid en bra idé att förstå typen av område du bearbetar.
Om du behöver ett ELLER-villkor (det vill säga att det ena eller det andra måste vara sant) ändras strategin något. Istället för att multiplicera använder du vanligtvis addition. För att till exempel summera försäljningsvärdena som är mindre än 5 eller större än 15 kan du skriva =SUMMA(OM((Försäljning<5)+(Försäljning>15); Försäljning))Här producerar varje rad som uppfyller minst ett av villkoren ett värde som inte är noll, och IF anses det vara SANT.
I dessa scenarier fungerar de "klassiska" OCH- och ELLER-funktionerna inte bra i matrisformler eftersom de är utformade för att returnera ett enda globalt resultat (SANT eller FALSKT), inte en komplett matris. Därför ligger lösningen i översätt OCH/ELLER-logik till produkter (för Y) eller summor (för O), precis som vi har sett i exemplen.
Du kan också beräkna en medelvärde som ignorerar vissa värden, som nollor. Om du har ett område som heter Försäljning och du vill få medelvärdet av cellerna som inte är noll kan du använda =MEDEL(OM(Försäljning<>0; Försäljning))Funktionen OM konstruerar en array som endast innehåller celler som inte är noll, och MEDELVÄRDE beräknar medelvärdet över den internt filtrerade listan.
Jämför intervall och hitta skillnader med hjälp av matrisformler
Ett annat område där matrisformler är särskilt användbara är inom intervalljämförelseOm du till exempel har två områden med samma antal rader och kolumner, kallade MinaData och DinaData, och du vill veta hur många celler som skiljer sig från varandra, kan du använda en formel som denna: =SUMMA(OM(MinaData=DinaData; 0; 1)).
Detta uttryck genererar en array där varje element är 0 om motsvarande celler är lika, eller 1 om de skiljer sig åt. Sedan adderar SUM alla dessa ettor och nollor, vilket returnerar totalt antal skillnader mellan båda datamängderna; om summan är 0 betyder det att allt matchar exakt.
Om du vill ha en mer kompakt version finns det en vanligt förekommande variant: =SUMMA(1*(MinaData<>DinaData))Här används operatorn <> (inte lika med) för att direkt generera en SANT/FALSKT-matris, som efter att ha multiplicerats med 1 blir en matris av 1:or och 0:or. Återigen, summan av den matrisen visar hur många celler som är olika.
Det är också möjligt att lokalisera positionen för det maximala värdet i ett område med en enda kolumn, till exempel i ett område som heter Data. Ett sätt att uppnå detta med en array är att använda formeln =MIN(OM(Data=MAX(Data); RAD(Data); «»))Tricket är att OM returnerar radnumret där värdet är maximalt och lämnar en tom sträng i resten.
MIN-funktionen, tillämpad på den blandade matrisen av tal och tomma strängar, ignorerar strängarna och returnerar det minsta radnumret av de maximala värdena som hittats, det vill säga den första förekomsten av det maximala värdetOm det finns flera identiska maximum tar MIN raden för det första.
Om du också vill få cellreferens av det maximala värdet, inte bara raden, kan du radbryta ovanstående beräkning i ADDRESS-funktionen: =ADRESS(MIN(OM(Data=MAX(Data); RAD(Data); "")); KOLUMN(Data))Således anger COLUMN(Data) kolumnnumret, och ADDRESS konstruerar den fullständiga referensen, till exempel "$B$7".
Hela denna uppsättning formler visar att även utan moderna funktioner som MAP, matrisoperationer De låter dig lösa komplexa problem med jämförelse, sökning och dataanalys relativt enkelt när du väl förstår logiken bakom SANT/FALSKT, nollor, ettor och länkade villkor.
Genom att kombinera klassiska tekniker med nya funktioner som LAMBDA, MAP, FILTER, REDUCE, SCAN, MAKEARRAY, BYCOL, BYROW och ISOMITTED öppnas en mängd olika möjligheter: från att bygga helt dynamiska beräknade kolumner till att designa små, återanvändbara "moduler" med avancerad logik som automatiskt anpassar sig till storleken på dina data utan upprepade formler. Allt detta gör Excel till ett ännu mer flexibelt verktyg för automatisera komplexa matrisberäkningar utan behov av extern programmering.
