Riktlinjer för och exempel på matrisformler

Riktlinjer för och exempel på matrisformler

Obs!: Vi vill kunna erbjuda dig bästa möjliga supportinnehåll så fort som möjligt och på ditt språk. Den här sidan har översatts med hjälp av automatiserad översättning och kan innehålla grammatiska fel eller andra felaktigheter. Vår avsikt är att den här informationen ska vara användbar för dig. Vill du berätta för oss om informationen är till hjälp längst ned på sidan? Här är artikeln på engelska som referens.

En mat ris formel är en formel som kan utföra flera beräkningar på ett eller flera objekt i en matris. Du kan betrakta en matris som en rad eller kolumn med värden, eller en kombination av rader och kolumner med värden. Mat ris formler kan returnera antingen flera resultat eller ett enda resultat.

Från och med uppdateringen för september 2018 för Office 365, spill dem automatiskt antingen ned eller i angränsande celler. Den här ändringen fungerar också med flera nya funktioner för dynamisk matris. Dynamiska mat ris formler, oavsett om de använder befintliga funktioner eller funktioner för dynamisk matris, måste bara skrivas in till en enda cell och sedan bekräftas genom att trycka på RETUR. Tidigare måste du först markera hela utmatnings området och sedan bekräfta formeln med CTRL + SKIFT + RETUR. De kallas ofta för CSE -formler.

Du kan använda mat ris formler för att utföra komplexa uppgifter, till exempel:

  • Skapa snabbt exempel data mängder.

  • Räkna antalet tecken i ett cell område.

  • Summera tal som uppfyller vissa villkor, till exempel de lägsta värdena i ett område eller tal som faller mellan en övre och en undre gräns.

  • Summera varje värde i ett intervall med värden.

I följande exempel visas hur du skapar mat ris formler med flera celler och en cell. Där det är möjligt har vi inkluderat exempel med några av de dynamiska mat ris funktionerna, samt befintliga mat ris formler som har angetts som både dynamiska och äldre matriser.

Hämta våra exempel

Ladda ned en exempel arbets bok med alla exempel på mat ris formler i den här artikeln.

I den här övningen lär du dig att använda flercells- och encellsmatrisformler för att beräkna en uppsättning försäljningssiffror. I de första stegen används en flercellsformel för att räkna ut delsummor. I den andra delen används en encellsformel för att räkna ut en totalsumma.

  • Flercellsmatrisformel

    Mat ris funktion med flera celler i cell H10 = F10: F19 * G10: G19 för att beräkna antal bilar som säljs med enhets pris

  • Här beräknar vi total försäljning av kopplings-och sedaner för varje säljare genom att ange = F10: F19 * G10: G19 i cell H10.

    När du trycker på RETURvisas resultatet i cellerna H10: H19. Observera att spill området är markerat med en kant linje när du markerar en cell i spill området. Det kan också hända att formlerna i cellerna H10: H19 är nedtonade. Det finns bara referenser, så om du vill justera formeln måste du Markera cell H10, där huvud formel.

  • Mat ris formel med en cell

    Matris formel med en cell för att beräkna en total summa med = summa (F10: F19 * G10: G19)

    Skriv eller kopiera och klistra in = Sum (F10: F19 * G10: G19)i cell H20 i exempel arbets boken och tryck sedan på RETUR.

    I det här fallet multiplicerar Excel värdena i matrisen (cell området F10 till G19) och använder sedan funktionen Summa för att summera summorna. Resultatet blir en totalsumma på 1 590 000 $ i försäljning.

    I det här exemplet kan du se hur effektiv den här typen av formel kan vara. Tänk dig till exempel att du har 1 000 rader med data. Du kan summera delar av eller hela den här informationsmängden genom att skapa en matrisformel i en enda cell, i stället för att dra formeln ned över alla 1 000 raderna. Observera också att formeln med en cell är helt oberoende av formeln med flera celler (formeln i cellerna H10 till H19). Det här är en annan fördel med att använda matrisformler – flexibilitet. Du kan ändra de andra formlerna i kolumn H utan att påverka formeln i H20. Det kan också vara bra att ha fristående funktioner så här, eftersom det kan verifiera att dina resultat är korrekta.

  • Dynamiska mat ris formler ger också dessa fördelar:

    • Konsekvens    Om du klickar på någon av cellerna från H10 visas samma formel. Med konsekvens blir resultatet också mer korrekt.

    • Säkerhet    Du kan inte skriva över en komponent i en matris formel med flera celler. Klicka till exempel på cell H11 och tryck på Delete. Det går inte att ändra matrisens utdata. Om du vill ändra den måste du markera cellen längst upp till vänster i en matris eller i cell H10.

    • Mindre filstorlekar    Ofta kan du använda en enda matrisformel i stället för flera delformler. Exempel: med bil Sales-exemplet används en mat ris formel för att beräkna resultaten i kolumn E. Om du har använt vanliga formler som = F10 * G10, F11 * G11, F12 * G12, etc., har du använt elva olika formler för att beräkna samma resultat. Det här är inte ett stort erbjudande, men vad händer om du hade tusentals rader att summera? Då kan den göra stor skillnad.

    • Effektivitet    Mat ris funktionerna är ett effektivt sätt att bygga komplexa formler. Mat ris formeln = SUM (F10: F19 * G10: G19) är samma som den här: = SUM (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * G18, F19 * G19).

    • Utanför    Dynamiska mat ris formler spiller automatiskt till Utdataområde. Om dina källdata finns i en Excel-tabell ändras dina dynamiska mat ris formler automatiskt när du lägger till eller tar bort data.

    • #SPILL! synkroniseringsfel    Dynamiska matriser introducerade #SPILL!vilket indikerar att det avsedda spill området blockeras av någon anledning. När du löser blockage spiller formeln automatiskt.

Matriskonstanter är komponenter i matrisformler. Du skapar en matriskonstant genom att ange en lista över element och sedan manuellt omge listan med klammerparenteser ({ }), på det här sättet:

= {1; 2; 3; 4; 5} eller = {"januari", "februari", "mars"}

Om du avgränsar elementen med kommatecken skapar du en vågrät matris (en rad). Om du avgränsar elementen med semikolon skapar du en lodrät matris (en kolumn). Om du vill skapa en matris med två dimensioner begränsar du objekten på varje rad med kommatecken och begränsar varje rad med semikolon.

I procedurerna nedan får du öva dig på att skapa vågräta, lodräta och tvådimensionella konstanter. Vi visar exempel med funktionen sekvens för att automatiskt skapa matriskonstanter, samt manuellt angivna matriskonstanter.

  • Skapa en vågrät konstant

    Använd arbetsboken från tidigare exempel eller skapa en ny arbetsbok. Markera en tom cell och retur = sekvens (1,5). Med funktionen sekvens skapas en matris med 1 rad x 5 kolonn som = {1; 2; 3; 4; 5}. Följande resultat visas:

    Skapa en vågrät matriskonstant med = sekvens (1, 5) eller = {1; 2; 3; 4; 5}

  • Skapa en lodrät konstant

    Markera en tom cell med rummet nedanför och skriv = sekvens (5)eller = {1; 2; 3; 4; 5}. Följande resultat visas:

    Skapa en lodrät matriskonstant med = sekvens (5) eller = {1; 2; 3; 4; 5}

  • Skapa en tvådimensionell konstant

    Markera en tom cell med rummet till höger och nedanför den och skriv = sekvens (3,4). Följande resultat visas.

    Skapa en 3-rad med 4 kolumn mat ris konstant med = sekvens (3,4)

    Du kan också ange: eller = {1; 2; 3; 4; 5, 6, 7, 8; 9, 10, 11, 12}, men du vill veta var du skriver semikolon och kommatecken.

    Som du kan se ger alternativet SEKVENSER större fördelar jämfört med att ange dina matriskonstanter manuellt. I huvudsak sparar det dig tid, men det går också att minska den manuella inmatningen. Det är också lättare att läsa, särskilt när semikolon avgränsarna kan vara svåra att skilja på.

Här är ett exempel som använder matriskonstanter som en del av en större formel. Gå till konstanten i ett formel kalkyl blad i exempel arbets boken eller skapa ett nytt kalkyl blad.

I cell D9 angav vi = sekvens (1, 5, 3, 1), men du kan också ange 3, 4, 5, 6 och 7 i cellerna A9: H9. Det finns ingenting speciellt med den speciella urvals markeringen, vi valde något annat än 1-5 för differentiering.

I cell E11 anger du = Summa (D9: H9 * Sequence (1, 5))eller = Sum (D9: H9 * {1; 2; 3; 4; 5}). Formlerna returnerar 85.

Använda matriskonstanter i formler. I det här exemplet användes vi = Summa (D9: H (* SEQUENCE (1,5))

Med funktionen sekvens skapas motsvarigheten till matriskonstanten {1; 2; 3; 4; 5}. Eftersom Excel utför operationer i uttryck som omges av parenteser, är nästa två element som ska spelas upp de cell värden i D9: H9 och operatorn för multiplikation (*). I det här läget multipliceras värdena i den sparade matrisen med de motsvarande värdena i konstanten. Det motsvarar:

= Summa (D9 * 1; E9 * 2; F9 * 3; G9 * 4; H9 * 5)eller = Summa (3 * 1; 4 * 2; 5 * 3; 6 * 4; 7 * 5)

Slutligen adderar funktionen Summa värdena och returnerar 85.

Om du inte vill använda den lagrade matrisen och samtidigt behålla åtgärden i minnet kan du ersätta den med en annan matriskonstant:

= Summa (sekvens (1; 5; 3; 1) * sekvens (1; 5))eller = Summa ({3, 4, 5, 6, 7} * {1; 2; 3; 4; 5})

Element som du kan använda i matriskonstanter

  • Matriskonstanter kan innehålla tal, text, logiska värden (till exempel sant och falskt) och fel värden som #N/A. Du kan använda siffror i heltals-, decimal-och vetenskapliga format. Om du inkluderar text måste du omge den med citat tecken ("text").

  • Matriskonstanter kan inte innehålla ytterligare matriser, formler eller funktioner. Med andra ord: de kan endast innehålla text eller tal som separeras av semikolon eller omvänt snedstreck. I Excel visas ett varningsmeddelande när du anger en formel, till exempel {1;2;A1:D4} eller {1;2;SUMMA(Q2:Z8)}. Numeriska värden kan inte innehålla procenttecken, dollartecken, punkter eller parenteser.

Ett av de bästa sätten att använda matriskonstanter är att namnge dem. Namngivna konstanter kan vara mycket enklare att använda och namnet kan dölja lite av komplexiteten med matrisformlerna för andra användare. Så här gör du när du vill namnge en matriskonstant och använda den i en formel:

Gå till formler _GT_ definierade namn > Definiera namn. I rutan namn skriver du Kvartal1. Ange följande konstant i rutan Refererar till (kom ihåg att skriva in klammerparenteserna manuellt):

={”Januari”,”Februari”,”Mars”}

Dialog rutan bör nu se ut så här:

Lägga till en namngiven matriskonstant från formler > definierade namn > namn hanteraren > ny

Klicka på OKoch markera sedan en rad med tre tomma celler och skriv = Kvartal1.

Följande resultat visas:

Använd en namngiven matriskonstant i en formel, till exempel = Kvartal1, där Kvartal1 har definierats som = {"januari", "februari", "mars"}

Om du vill att resultatet ska spilla lodrätt i stället för vågrätt kan du använda =transponera(Kvartal1).

Om du vill visa en lista på 12 månader, som du kan använda när du skapar ett bok slut, kan du basera det aktuella året med funktionen sekvens. Det viktigaste med den här funktionen är att även om bara månaden visas är det ett giltigt datum som du kan använda i andra beräkningar. De här exemplen finns i exempel på namngivna mat ris -och urvals kalkyl blad för snabb exempel .

= TEXT (datum (år (i dag ()), sekvens (1; 12); 1); "MMM")

Använd en kombination av funktionerna TEXT, datum, år, idag och serie för att skapa en dynamisk lista med 12 månader

Med funktionen datum kan du skapa ett datum utifrån det innevarande året, sekvensen skapar en matriskonstant från 1 till 12 för januari till och med december och sedan konverterar visnings formatet till "MMM" (Jan, feb, Mar osv.). Om du vill visa hela månads namnet, till exempel januari, använder du "mmmm".

När du använder en namngiven konstant som en mat ris formel måste du komma ihåg att ange likhets tecknet, som i = Kvartal1, inte bara Kvartal1. Om du inte gör det tolkas matrisen som en textsträng och formeln fungerar inte som avsett. Tänk på att du kan använda kombinationer av funktioner, text och tal. Det beror på hur kreativ du vill ha.

I exemplen nedan visas några sätt som du kan använda matriskonstanter i matrisformler. I några av exemplen används funktionen TRANSPONERA för att konvertera rader till kolumner och vice versa.

  • Flera objekt i en matris

    Enter = sekvens (1; 12) * 2eller = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} * 2

    Du kan också dividera med (/), lägga till (+) och subtrahera med (-).

  • Upphöja elementen i en matris i kvadrat

    Retur = sekvens (1, 12) ^ 2eller = {1; 2; 3; 4; 5, 6, 7, 8; 9, 10, 11, 12} ^ 2

  • Hitta kvadratroten av ett objekt i en matris

    Enter =sqrt(sekvens (1, 12) ^ 2)eller = sqrt ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} ^ 2)

  • Transponera en endimensionell rad

    Retur = transponera (sekvens (1; 5))eller = transponera ({1; 2; 3; 4; 5})

    Även om du angav en vågrät matriskonstant konverteras matriskonstanten till en kolumn med funktionen TRANSPONERA.

  • Transponera en endimensionell kolumn

    Retur = transponera (sekvens (5; 1))eller = transponera ({1; 2; 3; 4; 5})

    Även om du angav en lodrät matriskonstant konverteras konstanten till en rad med funktionen TRANSPONERA.

  • Transponera en tvådimensionell konstant

    Retur = transponera (sekvens (3; 4))eller = transponera ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12})

    Med funktionen TRANSPONERA konverteras varje rad till en serie kolumner.

Det här avsnittet innehåller exempel på grundläggande matrisformler.

  • Skapa en matris från befintliga värden

    I följande exempel förklaras hur du använder mat ris formler för att skapa en ny matris från en befintlig matris.

    Retur = sekvens (3, 6, 10, 10)eller = {10, 20, 30, 40, 50, 60, 70, 80, 90100110120; 130140150160170180}

    Skriv {(inledande klammerparentes) innan du skriver 10 och} (höger klammerparentes) efter att du skrivit 180, eftersom du skapar en matris med tal.

    Skriv sedan = D9 #eller = D9: i11 i en tom cell. En 3 x 6-matris med celler visas med samma värden som visas i D9: D11. #-Tecknet kallas för operatorn spill områdeoch det är Excel'st sätt att referera till hela mat ris området i stället för att behöva skriva ut det.

    Använda den spill områdes operator (#) för att referera till en befintlig matris

  • Skapa en matriskonstant från befintliga värden

    Du kan ta med resultatet från en mat ris formel med spill och konvertera den till dess komponent delar. Markera cell D9 och tryck sedan på F2 för att växla till redigerings läge. Sedan trycker du på F9 för att konvertera cell referenserna till värden, som Excel konverterar till en matriskonstant. När du trycker på RETURska formeln, = D9 #, nu vara = {10, 20, 30; 40, 50, 60, 70, 80, 90}.

  • Räkna tecken i ett cellområde

    I följande exempel visas hur du räknar antalet tecken i ett cell område. Detta inkluderar blank steg.

    Räkna antalet tecken i ett intervall och andra matriser för att arbeta med text strängar

    = SUMMA (LÄNGD

    I det här fallet returnerar funktionen längd längden på varje text sträng i varje cell i området. Funktionen Summa adderar sedan värdena och visar resultatet (66). Om du vill få medelvärdet för antalet tecken kan du använda:

    = MEDEL (LÄNGD

  • Innehållet i den längsta cellen i området C9: C13

    = INDEX (C9: C13; PASSA (MAX (LÄNGD (1, C9: C13); LÄNGD (: C13); 0); 1)

    Formeln fungerar endast när ett dataområde innehåller en enda cellkolumn.

    Låt oss titta närmare på formeln, från de inre elementen och arbeta utåt. Funktionen längd Returnerar längden för varje objekt i cell området D2: D6. Med funktionen Max beräknas det största värdet bland de objekten, som motsvarar den längsta text strängen, som finns i cell D3.

    Det är nu det börjar bli komplicerat. Funktionen passa beräknar förskjutningen (den relativa positionen) för den cell som innehåller den längsta text strängen. För detta behövs tre argument: ett sökvärde, en sökmatris och en passningstyp. Funktionen PASSA söker efter sökvärdet i sökmatrisen. I det här fallet är sökvärdet den längsta textsträngen:

    MAX (LÄNGD

    och den strängen finns i den här matrisen:

    LÄNGD (C9: C13)

    Argumentet matcha typ i det här fallet är 0. Matchnings typen kan vara 1, 0 eller 1-värde.

    • 1 – returnerar det största värdet som är mindre än eller lika med uppslags val

    • 0 – returnerar det första värdet exakt lika med uppslags värde

    • -1-returnerar det minsta värdet som är större än eller lika med det angivna uppslag svärdet

    • Om du utelämnar passningstypen används 1 som standard.

    Slutligen använder funktionen index följande argument: en matris och ett rad-och kolumn nummer inom matrisen. Cell området C9: C13 ger matrisen, funktionen passa ger cell adressen och det sista argumentet (1) anger att värdet kommer från den första kolumnen i matrisen.

    Om du vill hämta innehållet i den minsta text strängen ersätter du MAX värdet i exemplet ovan med min.

  • Hitta de n lägsta värdena i ett område

    Det här exemplet visar hur du hittar de tre minsta värdena i ett cell område, där en matris med exempel data i cellerna B9: B18has har skapats med: = int (slump mat ris(10, 1) * 100). Observera att slump mat ris är en volatile-funktion, så att du får en ny uppsättning slumptal varje gång Excel beräknar.

    Excel mat ris formel för att hitta det N:te minsta värdet: = liten (B9 #, sekvens (D9))

    Retur = liten (B9 #, sekvens (D9), = liten (B9: B18, {1; 2; 3})

    Den här formeln använder en matriskonstant för att utvärdera den mindre funktionen tre gånger och returnerar de 3 medlemmar som finns i matrisen i cellerna B9: B18, där 3 är ett variabel värde i cell D9. Om du vill hitta fler värden kan du öka värdet i funktionen sekvens eller lägga till fler argument till konstanten. Du kan också använda ytterligare funktioner med den här formeln, till exempel SUMMA eller MEDEL. Till exempel:

    = SUMMA (LITEN (B9 #, SEKVENS (D9))

    = MEDEL (LITEN (B9 #, SEKVENS (D9))

  • Hitta de n högsta värdena i ett område

    Om du vill hitta de största värdena i ett område kan du ersätta den lilla funktionen med funktionen stora. Dessutom används funktionerna RAD och INDIREKT i exemplet nedan.

    Retur = Large (B9 #, rad (indirekt ("1:3"))) eller = stor (B9: B18, Row (indirekt ("1:3")))

    I det här läget kan det vara bra att känna till lite grann om funktionerna RAD och INDIREKT. Du kan använda funktionen RAD när du vill skapa en matris med upprepade heltal. Välj till exempel en tom och ange:

    =RAD(1:10)

    Formeln skapar en kolumn med 10 efterföljande heltal. Om du vill se ett möjligt problem infogar du en rad ovanför området som innehåller mat ris formeln (det vill säga ovanför rad 1). Rad referenser justeras i Excel och formeln genererar nu heltal från 2 till 11. Du kan lösa problemet genom att lägga till den indirekta funktionen till formeln:

    =RAD(INDIREKT("1:10"))

    Funktionen indirekt använder text strängar som argument (vilket är det område 1:10 som omges av citat tecken). Textvärden justeras inte när du infogar rader eller flyttar matrisformeln på något annat sätt. Resultatet blir att funktionen RAD alltid genererar den matris med heltal som du vill ha. Du kan lika enkelt använda SEKVENSER:

    = SEKVENS (10)

    Vi undersöker formeln som du använde tidigare – = stor (B9 #, rad (indirekt ("1:3"))) — från de inre parenteserna och arbetar utåt: funktionen indirekt returnerar en uppsättning text värden, i det här fallet värdena 1 till 3. Med funktionen rad i Omvandla skapas en matris med tre celler. Funktionen stor använder värdena i cell området B9: B18 och beräknas tre gånger, en gång för varje referens som returneras av funktionen rad. If you want to find more values, you add a greater cell range to the INDIRECT function. Precis som med de små exemplen kan du använda den här formeln med andra funktioner, till exempel Summa och medelvärde.

  • Summera ett område som innehåller felvärden

    Funktionen Summa i Excel fungerar inte när du försöker att summera ett område som innehåller ett felvärde, till exempel #VALUE! eller #N/A. Det här exemplet visar hur du summerar värdena i ett område med namnet data som innehåller fel:

    Använd matriser för att hantera fel. Om du till exempel = Summa (om (ÄRFEL (data), "", data), summeras området med namnet data även om det innehåller fel, som #VALUE! eller #NA!.

  • =SUMMA(OM(ÄRFEL(Data),"",Data))

    Formeln skapar en ny matris som innehåller de ursprungliga värdena minus eventuella felvärden. Om vi börjar från de innersta funktionerna och jobbar oss utåt söker funktionen ÄRFEL efter fel i cellområdet (Data). Funktionen OM returnerar ett visst värde om ett villkor som du anger valideras som SANT och ett annat värde om det valideras till FALSKT. I det här fallet returneras tomma strängar ("") för alla felvärden eftersom de valideras till SANT, och de återstående värdena från området (Data) returneras, eftersom de valideras till FALSKT, vilket innebär att de inte innehåller felvärden. Funktionen SUMMA räknar sedan ut totalsumman för den filtrerade matrisen.

  • Räkna antalet felvärden i ett område

    Det här exemplet är som föregående formel, men returnerar antalet fel värden i ett område med namnet data i stället för att filtrera bort dem:

    =SUMMA(OM(ÄRFEL(Data),1,0))

    Den här formeln skapar en matris som innehåller värdet 1 för de celler som innehåller fel och värdet 0 för celler som inte innehåller fel. Du kan förenkla formeln och få samma resultat genom att ta bort det tredje argumentet till funktionen OM, på det här sättet:

    =SUMMA(OM(ÄRFEL(Data),1))

    Om du inte anger argumentet returnerar funktionen IF värdet FALSKT om en cell inte innehåller ett felaktigt värde. Du kan förenkla formeln ännu mer:

    =SUMMA(OM(ÄRFEL(Data)*1))

    Den här varianten fungerar eftersom SANT*1=1 och FALSKT*1=0.

I vissa situationer kan du behöva summera värden baserat på villkor.

Du kan använda matriser för att beräkna utifrån vissa villkor. = Summa (om (Sales>0, försäljning)) summerar alla värden som är större än 0 i ett område med namnet försäljning.

Till exempel summerar mat ris formeln bara positiva heltal i ett område med namnet försäljning, som representerar cellerna E9: E24 i exemplet ovan:

=SUMMA(OM(Försäljning>0,Försäljning))

Med funktionen om skapas en matris med positiva och falskta värden. Funktionen SUMMA ignorerar praktiskt taget de falska värdena eftersom 0+0=0. Cellområdet som du använder i den här formeln kan bestå av vilket antal rader och kolumner som helst.

Du kan också summera värden som uppfyller fler än ett villkor. Till exempel beräknar den här mat ris formeln värden som är större än 0 och mindre än 2500:

= SUM ((Sales>0) * (Sales<2500) * (försäljning))

Kom ihåg att den här formeln returnerar ett fel om området innehåller en eller flera celler som inte innehåller tal.

Du kan också skapa matrisformler som använder ett slags ELLER-villkor. Du kan till exempel summera värden som är större än 0 eller mindre än 2500:

= Summa (om (((Sales>0) + (Sales<2500), försäljning))

Det går inte att använda funktionerna OCHoch ELLER direkt i matrisformler eftersom dessa funktioner returnerar ett enda resultat, antingen SANT eller FALSKT, och matrisfunktionerna kräver resultatmatriser. Du kan lösa problemet genom att använda den logik som visas i den föregående formeln. Med andra ord kan du utföra matematik operationer, till exempel addition eller multiplikation på värden som uppfyller villkoret eller eller och.

I det här exemplet beskrivs hur du tar bort nollvärden från ett område när du behöver räkna ut ett genomsnitt för värdena i det området. I formeln används ett dataområde med namnet Försäljning:

=MEDEL(OM(Försäljning<>0,Försäljning))

Funktionen OM skapa en matris med värden som inte är lika med 0 och lämnar sedan dessa värden vidare till funktionen MEDEL.

I den här matrisformeln jämförs värdena i två cellområden med namnen Minadata och Dinadata och antalet skillnader mellan de två returneras. Om innehållet i de två områdena är identiskt returnerar formeln 0. För att använda den här formeln måste cell områdena ha samma storlek och samma dimension. Om t ex data är ett område med 3 rader x 5 kolumner måste Dinadata också vara 3 rader x 5 kolumner:

=SUMMA(OM(Minadata=Dinadata,0,1))

Formeln skapar en ny matris i samma storlek som de områden som du jämför. Funktionen OM fyller i matrisen med värdet 0 och värdet 1 (0 för felaktiga matchningar och 1 för identiska celler). Funktionen SUMMA returnerar summan av värdena i matrisen.

Du kan förenkla formeln så här:

= Summa (1 * (MyData<>YourData))

Precis som formeln som räknar felvärden i ett område, fungerar den här formeln eftersom SANT*1=1 och FALSKT*1=0.

Den här matrisformeln returnerar radnumret för det högsta värdet i ett enkolumnsområde med namnet Data:

=MIN(OM(Data=MAX(Data);RAD(Data);""))

Funktionen OM skapar en ny matris som motsvarar området Data. Om en motsvarande cell innehåller det högsta värdet i området innehåller matrisen radnumret. I annat fall innehåller matrisen en tom sträng (""). Funktionen MIN använder den nya matrisen som sitt andra argument och returnerar det lägsta värdet, som motsvarar radnumret för det högsta värdet i området Data. Om området Data innehåller identiska högsta värden returnerar formeln raden för det första värdet.

Om du vill returnera den faktiska celladressen för ett maxvärde kan du använda den här formeln:

=ADRESS(MIN(OM(Data=MAX(Data);RAD(Data);""));KOLUMN(Data))

Du hittar liknande exempel i exempel arbets boken med skillnaderna mellan data uppsättnings kalkyl bladet.

I den här övningen lär du dig att använda flercells- och encellsmatrisformler för att beräkna en uppsättning försäljningssiffror. I de första stegen används en flercellsformel för att räkna ut delsummor. I den andra delen används en encellsformel för att räkna ut en totalsumma.

  • Flercellsmatrisformel

Kopiera hela tabellen nedan och klistra in den i cell a1 i ett tomt kalkyl blad.

Försäljning Person

Bil Skriv in

Nummer Säljs

Enhet Pris

Totalt Försäljning

Barnhill

Sedan

5

33000

Kupé

4

37000

Ingle

Sedan

6

24000

Kupé

8

21000

Jordan

Sedan

3

29000

Kupé

1

31000

Pica

Sedan

9

24000

Kupé

5

37000

Sanchez

Sedan

6

33000

Kupé

8

31000

Formel (slutsumma)

Slutsumma

'=SUMMA(C2:C11*D2:D11)

=SUMMA(C2:C11*D2:D11)

  1. Om du vill visa total försäljning av koppling och sedaner för varje säljare markerar du cellerna E2: E11, anger formeln = C2: C11 * D2: D11och trycker sedan på CTRL + SKIFT + RETUR.

  2. Om du vill visa total summan för alla försäljningar markerar du cell F11, anger formeln = Summa (C2: C11 * D2: D11)och trycker sedan på CTRL + SKIFT + RETUR.

När du trycker på CTRL + SKIFT + RETURomger Excel formeln med klammerparenteser ({}) och infogar en förekomst av formeln i varje cell i det markerade området. Det går väldigt snabbt, så det du ser i kolumn E är den totala försäljningssumman för varje biltyp per försäljare. Om du markerar E2 och sedan E3, E4 och så vidare ser du att samma formel visas: {=C2:C11*D2:D11}

Summorna i kolumn E beräknas av en matrisformel

  • Skapa en encellsmatrisformel

Skriv in följande formel i cell D13 i arbets boken och tryck sedan på CTRL + SKIFT + RETUR:

=SUMMA(C2:C11*D2:D11)

I det här fallet multiplicerar Excel värdena i matrisen (cell intervallet C2 till D11) och använder sedan funktionen Summaför att summera summorna. Resultatet blir en totalsumma på 1 590 000 $ i försäljning. I det här exemplet kan du se hur effektiv den här typen av formel kan vara. Tänk dig till exempel att du har 1 000 rader med data. Du kan summera delar av eller hela den här informationsmängden genom att skapa en matrisformel i en enda cell, i stället för att dra formeln ned över alla 1 000 raderna.

Observera också att formeln med en cell är helt oberoende av formeln med flera celler (formeln i cellerna E2 till E11). Det här är en annan fördel med att använda matrisformler – flexibilitet. Du kan ändra formlerna i kolumn E eller ta bort kolumnen helt utan att påverka formeln i D13.

Matrisformler har dessutom följande fördelar:

  • Konsekvens    Om du klickar i några celler nedanför E2 kan du se att samma formel används. Med konsekvens blir resultatet också mer korrekt.

  • Säkerhet    Du kan inte skriva över en komponent i en flercellsmatrisformel. Klicka till exempel på cell E3 och tryck på Delete. Du måste antingen markera hela cellområdet (E2 till E11) och ändra formeln för hela matrisen, eller låta matrisen vara som den här. Som en säkerhets åtgärd måste du trycka på CTRL + SKIFT + RETUR för att bekräfta eventuella ändringar i formeln.

  • Mindre filstorlekar    Ofta kan du använda en enda matrisformel i stället för flera delformler. I arbetsboken används till exempel en matrisformel för att beräkna resultatet i kolumnen E. Om du hade använt standardformler (till exempel =C2*D2, C3*D3, C4*D4) hade du behövt använda 11 olika formler för att räkna ut samma resultat.

I allmänhet används vanlig formelsyntax i matrisformler. De börjar alltid med ett likhetstecken (=) och du kan använda de flesta av de inbyggda Excel-funktionerna i matrisformler. Skillnaden är att när du använder en mat ris formel trycker du på CTRL + SKIFT + RETUR för att ange formeln. När du gör det omges matrisformeln med klammerparenteser – om du skriver in klammerparenteserna manuellt konverteras formeln till en textsträng och fungerar inte.

Mat ris funktionerna är ett effektivt sätt att bygga komplexa formler. Matrisformeln =SUMMA(C2:C11*D2:D11) har samma resultat som det här: =SUMMA(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Viktigt!: Tryck på CTRL + SKIFT + RETUR när du vill ange en mat ris formel. Detta gäller både encells- och flercellsformler.

När du arbetar med flercellsformler ska du dessutom tänka på det här:

  • Markera cellområdet där resultatet ska visas innan du anger formeln. Detta gjorde du när du skapade flercellsmatrisformeln när du markerade cell E2 till E11.

  • Du kan inte ändra innehållet i en enskild cell i en matrisformel. Prova själv genom att markera cell E3 i kalkylbladet och trycka på Delete. Ett felmeddelande om att du inte kan ändra en del av matrisen visas.

  • Du kan flytta eller ta bort en hel matrisformel, men det går inte att flytta eller ta bort delar av den. Med andra ord: om du vill minska ned en matrisformel tar du först bort formeln och börjar sedan om från början.

  • Om du vill ta bort en mat ris formel markerar du hela formel området (till exempel E2: E11) och trycker på Delete.

  • Du kan inte infoga tomma celler i eller ta bort celler från en mat ris formel.

Ibland kan du behöva utöka en matrisformel. Markera den första cellen i det befintliga mat ris området och Fortsätt tills du har markerat hela området som du vill utöka formeln till. Tryck på F2 för att redigera formeln och tryck sedan på CTRL + SKIFT + RETUR för att bekräfta formeln när du har justerat formel området. Knappen är att markera hela området, med början i cellen längst upp till vänster i matrisen. Cellen längst upp till vänster är den som redige ras.

Matrisformler är mycket effektiva, men det finns några nackdelar:

  • Du kan glömma att trycka på CTRL + SKIFT + RETUR. Det kan hända till och med de mest erfarna Excel-användarna. Remember to press this key combination whenever you enter or edit an array formula.

  • Andra användare av arbets boken kanske inte förstår dina formler. I praktiken förklaras inte mat ris formler i ett kalkyl blad. Om andra personer behöver ändra arbets böckerna bör du därför antingen undvika mat ris formler eller se till att de som känner till olika mat ris formler och förstår hur de ska ändras.

  • Beroende på datorns kapacitet kan stora matrisformler göra att beräkningar går långsammare.

Matriskonstanter är komponenter i matrisformler. Du skapar en matriskonstant genom att ange en lista över element och sedan manuellt omge listan med klammerparenteser ({ }), på det här sättet:

={1,2,3,4,5}

Nu när du vet att du måste trycka på CTRL + SKIFT + RETUR när du skapar mat ris formler. Eftersom matriskonstanter är komponenter i matrisformler omger du konstanterna med klammerparenteser manuellt genom att skriva in dem. Du använder sedan CTRL + SKIFT + RETUR för att ange hela formeln.

Om du avgränsar elementen med kommatecken skapar du en vågrät matris (en rad). Om du avgränsar elementen med semikolon skapar du en lodrät matris (en kolumn). Om du vill skapa en tvådimensionell matris avgränsar du elementen på varje rad med kommatecken och varje rad med semikolon.

Här är en matris på en enda rad: {1; 2; 3; 4}. Här är exempel på en kolumn: {1\2\3\4}. Och här är ett exempel på två rader och fyra kolumner: {1;2;3;4\5;6;7;8}. Den första raden i den två raden är 1, 2, 3 och 4 och den andra raden är 5, 6, 7 och 8. Ett omvänt snedstreck används för att avgränsa de båda raderna, mellan elementet 4 och elementet 5.

Precis som med matrisformler kan du använda matriskonstanter med de flesta av de inbyggda funktionerna i Excel. I avsnitten nedan får du en förklaring på hur du skapar de olika typerna av konstanter och hur du använder konstanterna med funktioner i Excel.

I procedurerna nedan får du öva dig på att skapa vågräta, lodräta och tvådimensionella konstanter.

Skapa en vågrät konstant

  1. Markera cellerna a1 till och med E1 i ett tomt kalkyl blad.

  2. Ange följande formel i Formel fältet och tryck sedan på CTRL + SKIFT + RETUR:

    ={1,2,3,4,5}

    I det här fallet ska du skriva in de inledande och avslutande klammerparenteserna ({}), så läggs den andra gruppen till i Excel.

    Följande resultat visas.

    Vågrät matriskonstant i formel

Skapa en lodrät konstant

  1. Markera en kolumn med fem celler i kalkylbladet.

  2. Ange följande formel i Formel fältet och tryck sedan på CTRL + SKIFT + RETUR:

    ={1;2;3;4;5}

    Följande resultat visas.

    Lodrät matriskonstant i matrisformel

Skapa en tvådimensionell konstant

  1. Markera ett cellblock som är fyra kolumner brett och tre rader högt.

  2. Ange följande formel i Formel fältet och tryck sedan på CTRL + SKIFT + RETUR:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

    Följande resultat visas.

    Tvådimensionell matriskonstant i matrisformel

Använda konstanter i formler

Här är ett enkelt exempel som använder konstanter:

  1. Skapa ett nytt kalkylblad i exempelarbetsboken.

  2. Skriv 3 i cell A1 och sedan 4 i B1, 5 i C1, 6 i D1 och 7 i E1.

  3. Skriv in följande formel i cell A3 och tryck sedan på CTRL + SKIFT + RETUR:

    =SUMMA(A1:E1*{1,2,3,4,5})

    Lägg märke till att konstanten omges med ytterligare två klammerparenteser, eftersom du angav den som en matrisformel.

    Matrisformel med matriskonstant

    Värdet 85 visas i cell A3.

I nästa avsnitt kan du se hur formeln fungerar.

Formeln som du precis använde innehåller flera delar.

Syntax för matrisformel med matriskonstant

1. Funktion

2. Sparad matris

3. Operator

4. Matriskonstant

Det sista elementet innanför parentesen är matriskonstanten: {1;2;3;4;5}. Kom ihåg att matriskonstanter inte omges med klammerparenteser av Excel: det måste du göra själv. Kom ihåg att när du har lagt till en konstant i en mat ris formel trycker du på CTRL + SKIFT + RETUR för att ange formeln.

Eftersom åtgärder utförs på uttryck som omges av parenteser först blir de nästa två elementen som kommer in i ekvationen de värden som sparas i kalkylbladet (A1:E1) och operatorn. I det här läget multipliceras värdena i den sparade matrisen med de motsvarande värdena i konstanten. Det motsvarar:

=SUMMA(A1*1;B1*2;C1*3;D1*4;E1*5)

Slutligen summeras värdena med funktionen SUMMA och summan 85 visas i cell A3:

För att undvika att använda den sparade matrisen och för att bara spara åtgärden helt och hållet i minnet ersätter du den sparade matrisen med en annan matriskonstant:

=SUMMA({3,4,5,6,7}*{1,2,3,4,5})

Om du vill prova det kopierar du funktionen, markerar en tom cell i arbets boken, klistrar in formeln i Formel fältet och trycker på CTRL + SKIFT + RETUR. Du kan se samma resultat som du gjorde i den tidigare övningen med matrisformeln:

=SUMMA(A1:E1*{1,2,3,4,5})

Matriskonstanter kan innehålla tal, text, logiska värden (till exempel SANT och FALSKT) och felvärden (till exempel #SAKNAS). Du kan använda heltal, decimaltal och tal i exponentialform. Om du använder text måste du omge texten med dubbla citattecken (").

Matriskonstanter kan inte innehålla ytterligare matriser, formler eller funktioner. Med andra ord: de kan endast innehålla text eller tal som separeras av semikolon eller omvänt snedstreck. I Excel visas ett varningsmeddelande när du anger en formel, till exempel {1;2;A1:D4} eller {1;2;SUMMA(Q2:Z8)}. Numeriska värden kan inte innehålla procenttecken, dollartecken, punkter eller parenteser.

Ett av de bästa sätten att använda matriskonstanter är att namnge dem. Namngivna konstanter kan vara mycket enklare att använda och namnet kan dölja lite av komplexiteten med matrisformlerna för andra användare. Så här gör du när du vill namnge en matriskonstant och använda den i en formel:

  1. Klicka på Definiera namn i gruppen Definierade namn på fliken Formler.
    Dialog rutan Definiera namn visas.

  2. I rutan Namn skriver du Kvartal1.

  3. Ange följande konstant i rutan Refererar till (kom ihåg att skriva in klammerparenteserna manuellt):

    ={”Januari”,”Februari”,”Mars”}

    Innehållet i dialogrutan ser nu ut så här:

    Dialogrutan Redigera namn med formel

  4. Klicka på OK och markera en rad med tre tomma celler.

  5. Skriv in följande formel och tryck sedan på CTRL + SKIFT + RETUR.

    =Kvartal1

    Följande resultat visas.

    Namngiven matris som anges som formel

När du använder en namngiven konstant som en matrisformel ska du komma ihåg att ange likhetstecknet. Om du inte gör det tolkas matrisen som en textsträng och formeln fungerar inte som avsett. Kom också ihåg att du kan använda kombinationer av text och tal.

Om matriskonstanterna inte fungerar kan du undersöka om följande problem har uppstått:

  • Vissa element kanske inte har avgränsats med rätt tecken. Om du utelämnar ett kommatecken eller ett semikolon, eller om du placerar ett på fel ställe, kanske inte matriskonstanten skapas korrekt eller så visas ett varnings meddelande.

  • Du kan ha markerat ett cellområde som inte motsvarar antalet element i konstanten. Om du till exempel markerar en kolumn med sex celler som ska användas i en konstant med fem celler, visas felvärdet #SAKNAS i den tomma cellen. Omvänt gäller att om du markerar för få celler utelämnas de värden som saknar en motsvarande cell.

I exemplen nedan visas några sätt som du kan använda matriskonstanter i matrisformler. I några av exemplen används funktionen TRANSPONERA för att konvertera rader till kolumner och vice versa.

Multiplicera alla element i en matris

  1. Skapa ett nytt kalkylblad och markera ett block med tomma celler som är fyra kolumner brett och tre rader högt.

  2. Skriv in följande formel och tryck sedan på CTRL + SKIFT + RETUR:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*2

Upphöja elementen i en matris i kvadrat

  1. Markera ett block med tomma celler som är fyra kolumner brett och tre rader högt.

  2. Skriv följande mat ris formel och tryck sedan på CTRL + SKIFT + RETUR:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    Du kan också ange den här matrisformeln som använder operatorn cirkumflex (^):

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

Transponera en endimensionell rad

  1. Markera en kolumn med fem tomma celler.

  2. Skriv in följande formel och tryck sedan på CTRL + SKIFT + RETUR:

    =TRANSPONERA({1,2,3,4,5})

    Även om du angav en vågrät matriskonstant konverteras matriskonstanten till en kolumn med funktionen TRANSPONERA.

Transponera en endimensionell kolumn

  1. Markera en rad med fem tomma celler.

  2. Skriv in följande formel och tryck sedan på CTRL + SKIFT + RETUR:

    =TRANSPONERA({1;2;3;4;5})

Även om du angav en lodrät matriskonstant konverteras konstanten till en rad med funktionen TRANSPONERA.

Transponera en tvådimensionell konstant

  1. Markera ett block med celler som är tre kolumner brett och fyra rader högt.

  2. Ange följande konstant och tryck sedan på CTRL + SKIFT + RETUR:

    =TRANSPONERA({1,2,3,4;5,6,7,8;9,10,11,12})

    Med funktionen TRANSPONERA konverteras varje rad till en serie kolumner.

Det här avsnittet innehåller exempel på grundläggande matrisformler.

Skapa matriser och matriskonstanter från befintliga värden

I exemplet nedan får du veta hur du använder matrisformler för att skapa länkar mellan cellområden i olika kalkylblad. Du får också se hur du skapar en matriskonstant från samma uppsättning värden.

Skapa en matris från befintliga värden

  1. Markera cellerna C8:E10 i ett kalkylblad i Excel och ange den här formeln:

    ={10,20,30;40,50,60;70,80,90}

    Glöm inte att ange { (vänster klammerparentes) innan du anger 10 och } (höger klammerparentes) efter att du anger 90 eftersom du skapar en matris med tal.

  2. Tryck på CTRL + SKIFT + RETUR, vilket anger den här matrisen med tal i cell området C8: E10 genom att använda en mat ris formel. I ditt kalkyl blad ska C8 till E10 se ut så här:

    10

    20

    halvtimme

    40

    50

    60

    70

    80

    90

  3. Markera cellområdet C1 till E3.

  4. Ange följande formel i Formel fältet och tryck sedan på CTRL + SKIFT + RETUR:

    =C8:E10

    En 3x3 cell mat ris visas i cellerna C1 till E3 med samma värden som visas i C8 till E10.

Skapa en matriskonstant från befintliga värden

  1. Med celler C1: Välj C3, tryck på F2 för att växla till redigerings läge. 

  2. Tryck på F9 för att konvertera cell referenserna till värden. Excel converts the values into an array constant. Formeln ska nu vara = {10, 20, 30; 40, 50, 60, 70, 80, 90}.

  3. Tryck på CTRL + SKIFT + RETUR för att ange matriskonstanten som en mat ris formel.

Räkna tecken i ett cellområde

I följande exempel visas hur du räknar antalet tecken, inklusive mellanslag, i ett cellområde.

  1. Kopiera den här tabellen och klistra in den i cell A1 i ett kalkylblad.

    Data

    Det här är ett

    antal celler som

    samlas

    för att bilda en

    enda mening.

    Totalt antal tecken i A2:A6

    =SUMMA(LÄNGD(A2:A6))

    Innehåll i längsta cell (A3)

    =INDEX(A2:A6,MATCHA(MAX(LÄNGD(A2:A6)),LÄNGD(A2:A6),0),1)

  2. Markera cell A8 och tryck på CTRL + SKIFT + RETUR om du vill visa det totala antalet tecken i cellerna A2: A6 (66).

  3. Markera cell A10 och tryck sedan på CTRL + SKIFT + RETUR om du vill visa innehållet i de längsta cellerna A2: A6 (cell A3).

Följande formel används i cell A8 och här räknas det totala antalet tecken (66) i cellerna A2 till och med A6.

=SUMMA(LÄNGD(A2:A6))

I det här fallet returnerar funktionen LÄNGD längden på varje textsträng i var och en av cellerna i området. Funktionen Summa adderar sedan värdena och visar resultatet (66).

Hitta de n lägsta värdena i ett område

I exemplet visas hur du hittar de tre lägsta värdena i ett cellområde.

  1. Ange några slumptal i cellerna a1: A11.

  2. Markera cellerna C1 till C3. This set of cells will hold the results returned by the array formula.

  3. Skriv in följande formel och tryck sedan på CTRL + SKIFT + RETUR:

    = LITEN (A1: A11; {1; 2; 3})

Den här formeln använder en matriskonstant för att utvärdera den mindre funktionen tre gånger och returnerar den minsta (1), den andra minsta (2) och tredje minsta (3) medlemmar i matrisen som finns i cellerna a1: A10 för att hitta fler värden. matriskonstant. Du kan också använda ytterligare funktioner med den här formeln, till exempel SUMMA eller MEDEL. Till exempel:

= SUMMA (LITEN (A1: A10; {1; 2; 3})

= MEDEL (SMÅ (A1: A10; {1; 2; 3})

Hitta de n högsta värdena i ett område

Om du vill hitta de högsta värdena i ett område kan du ersätta funktionen MINSTA med funktionen STÖRSTA. Dessutom används funktionerna RAD och INDIREKT i exemplet nedan.

  1. Markera cellerna D1 till och med D3.

  2. Skriv den här formeln i Formel fältet och tryck sedan på CTRL + SKIFT + RETUR:

    =LARGE(A1:A10,ROW(INDIRECT("1:3")))

I det här läget kan det vara bra att känna till lite grann om funktionerna RAD och INDIREKT. Du kan använda funktionen RAD när du vill skapa en matris med upprepade heltal. Välj till exempel en tom kolumn med 10 celler i övnings arbets boken, Skriv den här mat ris formeln och tryck sedan på CTRL + SKIFT + RETUR:

=RAD(1:10)

Formeln skapar en kolumn med 10 efterföljande heltal. Om du vill se ett möjligt problem infogar du en rad ovanför området som innehåller mat ris formeln (det vill säga ovanför rad 1). Excel justerar rad referenserna och returnerar heltalen från 2 till 11. Du kan lösa problemet genom att lägga till den indirekta funktionen till formeln:

=RAD(INDIREKT("1:10"))

I funktionen INDIREKT används textsträngar som argument (vilket är anledningen till att området 1:10 omges av dubbla citattecken). Textvärden justeras inte när du infogar rader eller flyttar matrisformeln på något annat sätt. Resultatet blir att funktionen RAD alltid genererar den matris med heltal som du vill ha.

Låt oss ta en titt på formeln som du använde tidigare – = stor (A5: A14, rad (indirekt ("1:3"))) — från de inre parenteserna och arbeta utåt: funktionen indirekt returnerar en uppsättning text värden, i det här fallet värdena 1 till 3. The ROW function in turn generates a three-cell columnar array. Funktionen stora använder värdena i cell området A5: A14 och beräknas tre gånger, en gång för varje referens som returneras av funktionen rad . The values 3200, 2700, and 2000 are returned to the three-cell columnar array. If you want to find more values, you add a greater cell range to the INDIRECT function.

Precis som i tidigare exempel kan du använda den här formeln med andra funktioner, till exempel Summa och medelvärde.

Hitta den längsta textsträngen i ett cellområde

Gå tillbaka till exempel strängen ovan, Skriv in följande formel i en tom cell och tryck på CTRL + SKIFT + RETUR:

=INDEX(A2:A6,MATCHA(MAX(LÄNGD(A2:A6)),LÄNGD(A2:A6),0),1)

Texten "bunt med celler som visas.

Låt oss titta närmare på formeln, från de inre elementen och arbeta utåt. Funktionen längd Returnerar längden för varje objekt i cell området a2: A6. Med funktionen Max beräknas det största värdet bland objekten, som motsvarar den längsta text strängen, som finns i cell A3.

Det är nu det börjar bli komplicerat. Med funktionen PASSA beräknas adressen (den relativa positionen) för den cell som innehåller den längsta textsträngen. För detta behövs tre argument: ett sökvärde, en sökmatris och en passningstyp. Funktionen PASSA söker efter sökvärdet i sökmatrisen. I det här fallet är sökvärdet den längsta textsträngen:

(MAX (LÄNGD

och den strängen finns i den här matrisen:

LÄNGD (A2: A6)

Argumentet för passningstypen är 0. Passningstypen kan bestå av värdet 1, 0 eller -1. Om du anger 1 returnerar PASSA det högsta värdet som är lägre än eller lika med sökvärdet. Om du anger 0 returnerar PASSA det första värdet som är exakt lika med sökvärdet. Om du anger -1 returnerar PASSA det lägsta värdet som är högre än eller lika med det angivna sökvärdet. Om du utelämnar passningstypen används 1 som standard.

Slutligen används de här argumenten av funktionen INDEX: en matris samt ett värde som anger rad och kolumn inom den matrisen. Cell området a2: A6 ger matrisen, funktionen passa ger cell adressen och det sista argumentet (1) anger att värdet kommer från den första kolumnen i matrisen.

Det här avsnittet innehåller exempel på avancerade matrisformler.

Summera ett område som innehåller felvärden

Funktionen SUMMA i Excel fungerar inte när du försöker summera ett område som innehåller ett felvärde, till exempel #N/A. I det här exemplet får du veta hur du summerar värdena i ett område med namnet Data som innehåller fel.

=SUMMA(OM(ÄRFEL(Data),"",Data))

Formeln skapar en ny matris som innehåller de ursprungliga värdena minus eventuella felvärden. Om vi börjar från de innersta funktionerna och jobbar oss utåt söker funktionen ÄRFEL efter fel i cellområdet (Data). Funktionen OM returnerar ett visst värde om ett villkor som du anger valideras som SANT och ett annat värde om det valideras till FALSKT. I det här fallet returneras tomma strängar ("") för alla felvärden eftersom de valideras till SANT, och de återstående värdena från området (Data) returneras, eftersom de valideras till FALSKT, vilket innebär att de inte innehåller felvärden. Funktionen SUMMA räknar sedan ut totalsumman för den filtrerade matrisen.

Räkna antalet felvärden i ett område

Det här exemplet påminner om formeln ovan, men den returnerar antalet felvärden i ett område med namnet Data i stället för att filtrera ut dem:

=SUMMA(OM(ÄRFEL(Data),1,0))

Den här formeln skapar en matris som innehåller värdet 1 för de celler som innehåller fel och värdet 0 för celler som inte innehåller fel. Du kan förenkla formeln och få samma resultat genom att ta bort det tredje argumentet till funktionen OM, på det här sättet:

=SUMMA(OM(ÄRFEL(Data),1))

Om du inte anger argumentet returnerar funktionen IF värdet FALSKT om en cell inte innehåller ett felaktigt värde. Du kan förenkla formeln ännu mer:

=SUMMA(OM(ÄRFEL(Data)*1))

Den här varianten fungerar eftersom SANT*1=1 och FALSKT*1=0.

Summera värden baserat på villkor

I vissa situationer kan du behöva summera värden baserat på villkor. I den här matrisformeln summeras till exempel enbart positiva heltal i ett område med namnet Försäljning:

=SUMMA(OM(Försäljning>0,Försäljning))

Funktionen OM skapar en matris med positiva värden och falska värden. Funktionen SUMMA ignorerar praktiskt taget de falska värdena eftersom 0+0=0. Cellområdet som du använder i den här formeln kan bestå av vilket antal rader och kolumner som helst.

Du kan också summera värden som uppfyller fler än ett villkor. I den här matrisformeln beräknas till exempel värden som är högre än 0 och lägre än eller lika med 5:

=SUMMA((Försäljning>0)*(Försäljning<=5)*(Försäljning))

Kom ihåg att den här formeln returnerar ett fel om området innehåller en eller flera celler som inte innehåller tal.

Du kan också skapa matrisformler som använder ett slags ELLER-villkor. Du kan till exempel summera värden som är lägre än 5 och högre än 15:

=SUMMA(OM((Försäljning<5)+(Försäljning>15),Försäljning))

Funktionen OM hittar alla värden som är lägre än 5 och högre än 15 och lämnar sedan vidare dessa värden till funktionen SUMMA.

Det går inte att använda funktionerna OCHoch ELLER direkt i matrisformler eftersom dessa funktioner returnerar ett enda resultat, antingen SANT eller FALSKT, och matrisfunktionerna kräver resultatmatriser. Du kan lösa problemet genom att använda den logik som visas i den föregående formeln. Med andra ord: du utför matematiska operationer, till exempel addition eller multiplikation, på värden som uppfyller villkoret ELLER eller OCH.

Beräkna ett genomsnitt som utesluter nollvärden

I det här exemplet beskrivs hur du tar bort nollvärden från ett område när du behöver räkna ut ett genomsnitt för värdena i det området. I formeln används ett dataområde med namnet Försäljning:

=MEDEL(OM(Försäljning<>0,Försäljning))

Funktionen OM skapa en matris med värden som inte är lika med 0 och lämnar sedan dessa värden vidare till funktionen MEDEL.

Beräkna antalet skillnader mellan två cellområden

I den här matrisformeln jämförs värdena i två cellområden med namnen Minadata och Dinadata och antalet skillnader mellan de två returneras. Om innehållet i de två områdena är identiskt returnerar formeln 0. Om du vill använda den här formeln måste cellområdena ha samma storlek och samma dimension (om Minadata till exempel är ett område med 3 rader och 5 kolumner måste Dinadata också bestå av 3 rader och 5 kolumner):

=SUMMA(OM(Minadata=Dinadata,0,1))

Formeln skapar en ny matris i samma storlek som de områden som du jämför. Funktionen OM fyller i matrisen med värdet 0 och värdet 1 (0 för felaktiga matchningar och 1 för identiska celler). Funktionen SUMMA returnerar summan av värdena i matrisen.

Du kan förenkla formeln så här:

= Summa (1 * (MyData<>YourData))

Precis som formeln som räknar felvärden i ett område, fungerar den här formeln eftersom SANT*1=1 och FALSKT*1=0.

Hitta platsen för det högsta värdet i ett område

Den här matrisformeln returnerar radnumret för det högsta värdet i ett enkolumnsområde med namnet Data:

=MIN(OM(Data=MAX(Data);RAD(Data);""))

Funktionen OM skapar en ny matris som motsvarar området Data. Om en motsvarande cell innehåller det högsta värdet i området innehåller matrisen radnumret. I annat fall innehåller matrisen en tom sträng (""). Funktionen MIN använder den nya matrisen som sitt andra argument och returnerar det lägsta värdet, som motsvarar radnumret för det högsta värdet i området Data. Om området Data innehåller identiska högsta värden returnerar formeln raden för det första värdet.

Om du vill returnera den faktiska celladressen för ett maxvärde kan du använda den här formeln:

=ADRESS(MIN(OM(Data=MAX(Data);RAD(Data);""));KOLUMN(Data))

Bekräftelser

Delar av den här artikeln är baserade på en serie olika Excel-kolumner i Power-användare skrivna med Karl Nilsson blir och anpassade från kapitel 14 och 15 i Excel 2002-formler, en bok som skrivits av John Walkenbach, en tidigare Excel-MVP.

Behöver du mer hjälp?

Du kan alltid fråga en expert i Excel Tech Community, få support i Answers-communityn eller föreslå en ny funktion eller förbättringar på Excel User Voice.

Mer information finns i

Dynamiska matriser och beteenden för matriser och spill

Formler i dynamisk matris jämfört med äldre CSE-mat ris formler

Funktionen FILTER

Funktionen SLUMPMATRIS

Funktionen SEKVENS

Funktionen ENKEL

Funktionen SORTERA

Funktionen SORTERAEFTER

Funktionen UNIK

Felet #SPILL! i Excel

Översikt över formler

Utöka dina Office-kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×