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 matrisformel är en formel som kan utföra flera beräkningar på en eller flera objekt i en matris. Du kan se en matris som en rad eller kolumn med värden eller en kombination av rader och kolumner med värden. Matrisformler kan returnera flera resultat eller ett enda resultat.

Börja med September 2018 uppdatering för Office 365, kommer en formel som kan returnera flera resultat automatiskt spiller dem nedåt eller tvärs över till angränsande celler. Den här ändringen i beteende är också tillsammans med flera nya dynamiska matrisfunktioner. Dynamiska matrisformler, behöver oavsett om de använder befintliga funktioner eller dynamisk matrisfunktioner bara ange till en cell och sedan bekräftat genom att trycka på RETUR. Tidigare, äldre matrisformler kräver först att markera hela utdata-intervall och bekräfta formeln med Ctrl + Skift + Retur. De är ofta kallas IBLAND för formler.

Du kan använda matrisformler för att utföra komplexa, bland annat:

  • Snabbt skapa exempel datamängder.

  • Räkna antalet tecken i ett cellområde.

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

  • Summera var n: E värde i ett intervall med värden.

Följande exempel visar hur du skapar flercells och single matrisformler. Om möjligt, innehåller exempel med delar av dynamiska matrisfunktioner samt befintliga matrisformler som angetts som både dynamiska och äldre matriser.

Hämta våra exempel

Hämta en exempelarbetsbok med alla matris formel exemplen 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

    Flercells matrisfunktion i cell H10 = F10:F19 * G10:G19 att beräkna antalet bilar som sålts av Enhetspris

  • Här vi beräkning av totalförsäljning av kupéer och sedaner för varje säljare genom att ange = F19:F19 * G10:G19 i cell H10.

    När du trycker på RETUR visas resultatet spiller nedåt till celler H10:H19. Observera att oljesanering området är markerat med en kantlinje när du markerar en cell i området oljesanering. Du kanske också märker att formler i celler H10:H19 är nedtonade. De är bara där för referens, så om du vill justera formeln måste du Markera cell H10, där bildbakgrunden formeln finns.

  • Enskild cell matrisformel

    Enskild cell matrisformel för att beräkna en totalsumma med =SUM(F10:F19*G10:G19)

    I cell H20 i exempelarbetsboken, Skriv eller kopiera och klistra in =SUM(F10:F19*G10:G19)och tryck sedan på RETUR.

    I det här fallet Excel multiplicerar värdena i matris (cellområdet F10 till G19) och använder funktionen Summa för att lägga till summor tillsammans. Resultatet är totalsumman av $1,590,000 i försäljning.

    Det här exemplet visar hur kraftfulla den här typen av formeln kan vara. Anta att du har 1 000 rader med data. Du kan du summera en del av eller alla dessa data genom att skapa en matrisformel i en enskild cell i stället för att dra formeln genom de 1 000 raderna. Lägg också märke till att enskilda celler formeln i cellen H20 är helt oberoende av flera formeln i (formel i cell H10 till H19). Det här är en annan fördel med att använda matrisformler – flexibilitet. Du kan ändra andra formler i kolumn H utan att påverka formeln i H20. Det kan också vara bra att ha oberoende summor så här, enligt det lättare att validera precisionen i resultatet.

  • Dynamiska matrisformler har dessutom följande fördelar:

    • Ännu mer konsekvent    Om du klickar på någon av cellerna från H10 nedåt, visas samma formel. Den ännu mer konsekvensen kan försäkra större precision.

    • Säkerhet    Du kan inte skriva över en del av en flercellsmatrisformel. Till exempel klickar du på cell H11 och trycker på Delete. Excel ändras inte den matrisen utdata. Om du vill ändra det, måste du markera den övre vänstra cellen i matrisen eller cell H10.

    • Mindre filstorlekar    Du kan ofta använda en enda matrisformel i stället för flera mellanliggande formler. Till exempel används bil försäljning exemplet en matrisformel för att beräkna resultat i kolumn E. Om du hade använt vanliga formler t.ex = F10 * G10, F11 * G11, F12 * G12, etc., skulle du har använt 11 olika formler för beräkning av samma resultat. Det är inte intressant, men vad händer om du hade tusentals rader till totalt? Sedan kan det vara en stor skillnad.

    • Effektivitet    Matrisfunktioner kan vara ett effektivt sätt att skapa komplexa formler. Matrisen formeln =SUM(F10:F19*G10:G19) är det samma som det här: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Engelska    Dynamiska matrisformler kommer automatiskt spiller till utdataområdet som. Om dina källdata i en Excel-tabell ändrar sedan din dynamiska matrisformler automatiskt storlek som du lägger till eller ta bort data.

    • #SPILL! fel    Dynamiska matriser infördes den #SPILL! fel, vilket anger att området avsedda oljesanering blockeras av någon anledning. När du löser blockering formeln automatiskt att spiller.

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 separera objekt genom att använda kommatecken, skapar du en vågrät matris (en rad). Om du separera objekt genom att använda semikolon skapar du en lodrät matris (en kolumn). Om du vill skapa en tvådimensionell matris Avgränsa objekten i varje rad med kommatecken och avgränsa varje rad med semikolon.

Följande procedurer får du öva på att skapa vågräta, lodräta och tvådimensionella konstanter. Vi ska visa exempel med SEKVENS funktionen att automatiskt generera matriskonstanter samt matriskonstanter dem manuellt.

  • Skapa en vågrät konstant

    Använd arbetsboken från tidigare exempel eller skapa en ny arbetsbok. Markera en tom cell och ange =SEQUENCE(1,5). Funktionen SEKVENS skapar en 1 rad med 5 kolumn matris samma som = {1,2,3,4,5}. Följande resultat visas:

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

  • Skapa en lodrät konstant

    Markera en tom cell med rum under den och ange =SEQUENCE(5)eller = {1; 2; 3; 4; 5}. Följande resultat visas:

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

  • Skapa en tvådimensionell konstant

    Markera en tom cell med plats till höger och under den och ange =SEQUENCE(3,4). Följande resultat visas:

    Skapa en 3 rader med 4 kolumner matriskonstant med =SEQUENCE(3,4)

    Du kan också skriva: eller = {1,2,3,4; 5,6,7,8; 9,10,11,12}, men du bör ta hänsyn till var du sparar semikolon jämfört med kommatecken.

    Som du ser ger alternativet SEKVENS betydande fördelarna i stället för att ange din matris konstanta värden manuellt. Främst, sparar du tid, men det kan också att minska fel vid manuell inmatning. Det är också lättare att läsa, särskilt när semikolon kan vara svårt att skilja från kommaavgränsare.

Här är ett exempel som använder matriskonstanter som en del av en större formel. Gå till kalkylbladet konstant i en formel eller skapa ett nytt kalkylblad i exempelarbetsboken.

Vi angav =SEQUENCE(1,5,3,1)i cell D9, men du kan också ange 3, 4, 5, 6 och 7 i celler A9:H9. Det finns inget särskilt om det särskilt tal urvalet, vi valde bara något annat än 1-5 för skillnader.

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

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

Funktionen SEKVENS bygger motsvarigheten till matrisen konstant {1,2,3,4,5}. Eftersom Excel prioritetsordningen för operationer för uttryck inom parentes först, är de nästa två element som kommer in play cellvärden i D9:H9 och multiplikationsoperatorn (*). Vid den här tidpunkten multiplicerar formeln värdena i den sparade matrisen med motsvarande värden i konstanten. Det är motsvarigheten till:

=SUM(D9*1,e9*2,F9*3,G9*4,H9*5)eller =SUM(3*1,4*2,5*3,6*4,7*5)

Slutligen kan funktionen Summa adderar värdena och returnerar 85.

Om du vill undvika att använda den sparade matrisen och spara åtgärden helt och hållet i minnet kan ersätta du den med en annan matriskonstant:

=SUM(Sequence(1,5,3,1)*Sequence(1,5))eller =SUM({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 felvärden som #saknas. Du kan använda siffror i heltal, decimal och exponentiellt format. Om du lägger till text, måste du omge det med citattecken (”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.

En av de bästa sätten att använda matriskonstanter är namnge dem. Namngivna konstanter kan vara mycket enklare att använda, och de kan dölja några av komplexiteten i din matrisformler från andra. Om du vill namnge en matriskonstant och använda den i en formel, gör du följande:

Gå till formler > 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”}

Dialogrutan bör se ut så här:

Lägg till en namngiven matriskonstant från formler > definierade namn > Namnhanteraren > ny

Klicka på OK och sedan markera en rad med tre tomma celler och ange = Kvartal1.

Följande resultat visas:

Använda en namngiven matriskonstant i en formel, som = Kvartal1 om Kvartal1 har definierats som = {”januari”, ”februari”, ”mars”}

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

Om du vill visa en lista med 12 månader, som du kan använda när du skapar en årsredovisning baserar du något av det aktuella året med funktionen SEKVENS. Det ser bra ut jag om den här funktionen är trots att bara månaden som visar det är ett giltigt datum bakom som du kan använda i andra beräkningar. Du hittar de här exemplen i de namngivna matriskonstant och snabbt exempel dataset kalkylblad i exempelarbetsboken.

=TEXT(Date(Year(Today()),Sequence(1,12),1),"MMM")

Använda en kombination av funktionerna TEXT, datum, år, dag och SEKVENS för att bygga en dynamisk lista över 12 månader

Det här använder funktionen datum för att skapa ett datum baserat på det aktuella året, SEKVENS skapas en matriskonstant från 1 till 12 för januari till December och sedan funktionen TEXT omvandlar visningsformat till ”mmm” (Jan, Feb, Mar, etc.). Om du vill visa hela månadens namn, till exempel januari, använder du ”mmmm”.

När du använder en namngiven konstant som en matrisformel Kom ihåg att ange likhetstecknet, som i = Kvartal1, inte bara Kvartal1. Om du inte Excel tolkar matrisen som en textsträng och formeln fungerar inte som förväntat. Slutligen, Kom ihåg att du kan använda kombinationer av funktioner, text och siffror. Allt beror på hur creative som du vill ha.

Följande exempel visar några olika sätt som du kan lägga till matriskonstanter ska användas i matrisformler. Vissa av exemplen använder funktionen transponera för att konvertera rader till kolumner och vice versa.

  • Flera varje element i en matris

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

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

  • Upphöja elementen i en matris i kvadrat

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

  • Hitta kvadratroten av kvadrerade objekt i en matris

    Ange =rot(SEQUENCE(1,12)^2), eller =SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)

  • Transponera en endimensionell rad

    Ange =TRANSPOSE(SEQUENCE(1,5))eller =TRANSPOSE({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

    Ange =TRANSPOSE(SEQUENCE(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

    Ange =TRANSPOSE(SEQUENCE(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

    Följande exempel förklarar hur du använder matrisformler för att skapa en ny matris från en befintlig matris.

    Ange =SEQUENCE(3,6,10,10)eller = {10,20,30,40,50,60; 70,80,90,100,110,120; 130,140,150,160,170,180}

    Glöm inte att ange {(Inledande klammerparentes) innan du anger 10 och} (höger klammerparentes) efter att du skrivit 180, eftersom du skapar en matris med tal.

    Ange = D9 #eller = D9:I11 i en tom cell. En 3 x 6 cellmatris visas med samma värden som visas i D9: D11. Tecknet # kallas hamnat områdesoperatoroch det är Excels sätt att referera till matrisområdet hela istället att skriva ut.

    Använda hamnat områdesoperator (#) för att referera till en befintlig matris

  • Skapa en matriskonstant från befintliga värden

    Du kan ta resultaten av en hamnat matrisformel och konvertera som till dess beståndsdelar. Markera cell D9 och sedan trycka på F2 för att växla till redigeringsläge. Därefter trycker du på F9 för att konvertera cellreferenser till värden som Excel konverterar sedan till en matriskonstant. När du trycker på RETUR, formeln = D9 #, nu vara = {10,20,30; 40,50,60; 70,80,90}.

  • Räkna tecken i ett cellområde

    Följande exempel illustrerar hur du räknar antalet tecken i ett cellområde. Detta omfattar blanksteg.

    Räkna antalet tecken i ett område och andra matriser för att arbeta med textsträngar

    = SUMMA (LEN(C9:C13))

    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 värdena sedan och visar resultat (66). Om du vill få Genomsnittligt antal tecken använder du:

    = MEDEL (LEN(C9:C13))

  • Innehåll i längsta cell i området C9:C13

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

    Den här formeln fungerar bara när ett dataområde innehåller en kolumn med celler.

    Nu ska vi titta närmare på formeln, från och med inre element och arbeta utåt. Funktionen längd returnerar längden på vart och ett av objekten i cellområdet D2: D6. Funktionen MAX beräknar det största värdet bland de objekt som motsvarar den längsta textsträngen som visas i cell D3.

    Här är där saker nå komplicerat. Funktionen passa beräknar förskjutning (relativ position) i den cell som innehåller den längsta textsträngen. Om du vill göra det krävs tre argument: en uppslagsvärdet, en sökning matris och en matchningstyp av. Funktionen PASSA söker LETAUPP matrisen för det angivna uppslagsvärdet. I det här fallet är uppslagsvärdet längsta textsträngen:

    MAX(LEN(C9:C13)

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

    LEN(C9:C13)

    Argumentet matcha typ är i det här fallet 0. Passa typen kan vara en 1, 0 eller värdet-1.

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

    • 0 – returnerar det första värdet som är exakt lika med letauppvärde

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

    • Om en matchningstyp utelämnas antas 1.

    Slutligen kan funktionen INDEX har följande argument: en matris och ett rad- och tal i denna matris. Cellområdet C9:C13 ger matrisen, funktionen PASSA innehåller celladressen och det sista argumentet (1) anger att värdet kommer från den första kolumnen i matrisen.

    Om du vill få innehållet i minsta textsträngen kan ersätta du MAX 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 lägsta värdena i ett cellområde, där en matris med exempeldata i celler B9:B18has har skapats med: = INT (RANDARRAY(10,1) * 100). Observera att RANDARRAY är ej beständiga funktioner, så får du en ny uppsättning slumptal varje gång Excel beräknar.

    Matrisformel för att hitta det n: te minsta värdet i Excel: =SMALL(B9#,SEQUENCE(D9))

    Ange =SMALL(B9#,SEQUENCE(D9)= små (B9:B18, {1; 2; 3})

    Den här formeln använder en matriskonstant för att beräkna funktionen minsta tre gånger och returnera minsta 3 medlemmar i matrisen i celler B9:B18 där 3 är en variabel värdet i cell D9. Om du vill söka efter fler värden, kan du öka värdet i funktionen SEKVENS eller Lägg till fler argument konstanten. Du kan också använda ytterligare funktioner med den här formeln, till exempel Summa eller medel. Till exempel:

    =SUM(SMALL(B9#,SEQUENCE(D9))

    =AVERAGE(SMALL(B9#,SEQUENCE(D9))

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

    Du kan ersätta funktionen minsta med hjälp av funktionen störstaom du vill hitta de högsta värdena i ett område. I följande exempel används dessutom funktionerna rad och indirekt .

    Ange = stor (B9 #, rad (indirekt (”1:3”))), eller = stor (B9:B18,ROW(INDIRECT("1:3")))

    Det kan hjälpa vid den här tidpunkten kunskaper om funktionerna rad och indirekt. Du kan skapa en matris med fel i följd heltal med hjälp av funktionen rad. Till exempel markera en tom och ange:

    =ROW(1:10)

    Formeln skapar en kolumn med 10 fel i följd heltal. Om du vill visa ett problem, infoga en rad ovanför det område som innehåller matrisformeln (det vill säga ovanför rad 1). Referenserna rad och formeln nu genererar heltal från 2 till 11. Lös problemet genom du lägga till indirekt i formeln:

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

    Funktionen indirekt använder textsträngar som argument (som är varför område 1:10 omges av citattecken). Excel justerar inte textvärden när du infogar rader eller på annat sätt flytta matrisformeln. Funktionen rad genererar därför alltid matris med heltal som du vill använda. Du kan lika enkelt använda SEKVENS:

    =SEQUENCE(10)

    Nu ska vi undersöka den formel som du använt tidigare – = stor (B9 #, rad (indirekt (”1:3”))) – från och med inre parenteser och arbeta utåt: fältet indirekt funktionen returnerar en uppsättning textvärden, i det här fallet värdena 1 till 3. Funktionen rad genererar en matris med tre cell kolumn i tur och ordning. Funktionen största använder värdena i cellområdet B9:B18 och den utvärderas tre gånger, en gång för varje referens som returneras av funktionen rad. Om du vill söka efter fler värden du lägger till en större cellområde indirekt. Precis som med små exempel kan du använda den här formeln med andra funktioner, till exempel summa och medel.

  • 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 #saknas. Det här exemplet visar hur du summera värden inom ett område med namnet Data som innehåller fel:

    Använda matriser för att ta itu med fel. Till exempel =SUM(IF(ISERROR(Data),"",Data) summera område med namnet Data även om den 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 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.

Du kan behöva summera värden baserat på villkor.

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

Den här matrisformeln summerar exempelvis bara positivt heltal i ett område med namnet Försäljning, som representerar celler E9:E24 i exemplet ovan:

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

Funktionen om skapa en matris med positiva och false-värden. Funktionen SUMMA ignorerar princip false-värden eftersom 0 + 0 = 0. Cellområdet som du använder i den här formeln kan bestå av valfritt antal rader och kolumner.

Du kan även summera värden som uppfyller flera villkor. Till exempel beräknar den här matrisformeln värden som är större än 0 och mindre än 2500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

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 en typ av eller-villkor. Du kan till exempel summera värden som är större än 0 eller mindre än 2500:

=SUM(IF((Sales>0)+(Sales<2500),Sales))

Du kan inte använda funktionen och och eller funktionerna i matrisformler direkt eftersom dessa funktioner returnerar ett enda resultat SANT eller FALSKT och matrisfunktioner kräver matriser i resultatlistan. Du kan kringgå problemet genom att använda logiken som visas i formeln ovan. Med andra ord du utför matematiska operationer som addition eller multiplikation på värden som uppfyller rutan eller eller och-villkor.

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.

Den här matrisformeln jämför värden i två områden med celler som heter minadata och Dinadata och returnerar antalet skillnader mellan två. Om innehållet i två områden är identiska, returnerar formeln 0. Om du vill använda den här formeln måste cellområden vara samma storlek och i samma dimension. Till exempel om minadata är ett intervall med 3 rader efter 5 kolumner, måste Dinadata också vara 3 rader efter 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*(Minadata<>Dinadata))

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))

Liknande exempel hittar du i exempelarbetsboken på kalkylbladet skillnader mellan datamängder .

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 kalkylblad.

Försäljning Person

Bil Typ

Tal Sålda

Enhet Pris

Total 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 totalförsäljning av kupéer och sedaner för varje säljare, markera cell E2: E11, anger formeln = C2: C11 * D2: D11, och tryck sedan på Ctrl + Skift + Retur.

  2. Om du vill visa totalsumma för alla sålda markerar du cell F11, anger formeln =SUM(C2:C11*D2:D11)och tryck sedan på Ctrl + Skift + Retur.

När du trycker på Ctrl + Skift + Retur Excel omger formeln med klammerparenteser ({}) och infogar en instans av formeln i varje cell i det markerade området. Detta händer mycket snabbt så att det visas i kolumn E är försäljningssiffrorna för varje biltyp för varje säljare. Om du väljer E2 och välj sedan E3, E4 och så vidare, visas 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 arbetsboken och tryck sedan på Ctrl + Skift + Retur:

=SUMMA(C2:C11*D2:D11)

I det här fallet Excel multiplicerar värdena i matris (cellområdet C2 D11) och använder funktionen Summaför att lägga till summor tillsammans. Resultatet är totalsumman av $1,590,000 i försäljning. Det här exemplet visar hur kraftfulla den här typen av formeln kan vara. Anta att du har 1 000 rader med data. Du kan du summera en del av eller alla dessa data genom att skapa en matrisformel i en enskild cell i stället för att dra formeln genom de 1 000 raderna.

Lägg också märke till att enskilda celler formel i cell D13 är helt oberoende av flera formeln i (formel i cell E2 till E11). Det här är en annan fördel med att använda matrisformler – flexibilitet. Du kan ändra formler 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 del av en flercellsmatrisformel. Till exempel klickar du på cell E3 och tryck på Ta bort. Du måste antingen markera hela området med celler (E2 via E11) och ändra formeln för hela matrisen eller lämna matrisen som är. Som ett mått för ökad säkerhet måste du trycka på Ctrl + Skift + Retur för att bekräfta en ändring 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ända matrisformler standard formelsyntax. Alla börjar med ett likhetstecken (=) och du kan använda de flesta inbyggda Excel-funktioner i din matrisformler. Den viktigaste skillnaden är att när du använder en matrisformel kan du trycka på Ctrl + Skift + Retur om du vill ange formeln. När du gör detta Rubrikalternativ din matrisformel med klammerparenteser – om du skriver in klammerparenteserna manuellt formeln konverteras till en textsträng och fungerar inte.

Matrisfunktioner kan vara ett effektivt sätt att skapa komplexa formler. Matrisen formeln =SUM(C2:C11*D2:D11) är det samma som det här: =SUM(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 ska skriva en matrisformel. Detta gäller både enskilda celler och flercells formler.

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 matrisformel markerar du hela formeln området (till exempel E2: E11) och tryck på Ta bort.

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

Ibland kan behöva du utöka en matrisformel. Markera den första cellen i den befintliga matrisområde 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 sedan trycka på CTRL + SKIFT + RETUR för att bekräfta formeln när du har justerat området formel. Lösningen är att markera hela området med den övre vänstra cellen i matrisen början. Den övre vänstra cellen är det som redigeras.

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

  • Du kanske kan glömma att trycka på Ctrl + Skift + Retur. Det kan uppstå även de mest Erfarna Excel-användarna. Kom ihåg att trycka på den här tangentkombinationen när du ansluter till eller redigera en matrisformel.

  • Andra användare av arbetsboken kanske inte förstår dina formler. I praktiken förklaras matrisformler vanligen inte i ett kalkylblad. Därför om andra personer måste du ändra dina arbetsböcker, bör du antingen undvika matrisformler eller kontrollera personer veta om eventuella matrisformler och förstå hur du kan ändra dem om det behövs.

  • 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 bör vet du du måste trycka på Ctrl + Skift + Retur när du skapar matrisformler. Eftersom matriskonstanter är en del av matrisformler, omger du konstanter med klammerparenteser genom att skriva dem manuellt. Du kan sedan använda 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 i en enda rad: {1,2,3,4}. Här är en matris i en enda kolumn: {1; 2; 3; 4}. Och här är en matris med två rader och fyra kolumner: {1,2,3,4; 5,6,7,8}. Den första raden är 1, 2, 3 och 4 i matrisen två raden och den andra raden är 5, 6, 7 och 8. Ett semikolon särskiljer två rader mellan 4 och 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 kalkylblad.

  2. Skriv in följande formel i formelfältet och tryck sedan på Ctrl + Skift + Retur:

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

    I det här fallet du ska skriva in inledande och avslutande klammerparenteser ({}) och Excel lägger till den andra uppsättningen åt dig.

    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. Skriv in följande formel i formelfä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. Skriv in följande formel i formelfä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 i parentesen är matriskonstanten: {1,2,3,4,5}. Kom ihåg att Excel inte omge matriskonstanter med klammerparenteser; faktiskt inskrivning. Kom ihåg att när du lägger till en konstant en matrisformel kan du trycka på Ctrl + Skift + Retur om du vill 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 här kopiera funktionen, markera en tom cell i arbetsboken, klistra in formeln i formelfältet och tryck sedan på Ctrl + Skift + Retur. Ser du samma resultat som du gjorde i tidigare Övning som används 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.

Något av det bästa sättet att använda matriskonstanter är att namnge dem. Namngivna konstanter kan vara mycket enklare att använda, och de kan dölja några av komplexiteten i din matrisformler från andra. Om du vill namnge en matriskonstant och använda den i en formel, gör du följande:

  1. Klicka på Definiera namn på fliken formler i gruppen Definierade namn.
    Dialogrutan 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 avgränsas med rätt tecken. Om du utelämnar ett kommatecken eller semikolon eller om du placerar en felplacerade matriskonstanten skapat inte på rätt sätt eller det hända att ett varningsmeddelande visas.

  • 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.

Följande exempel visar några olika sätt som du kan lägga till matriskonstanter ska användas i matrisformler. Vissa av exemplen använder 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 in följande matrisformel 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, så lägger till den här matris med tal i cellområdet C8:E10 med hjälp av en matrisformel. I kalkylbladet, C8 till E10 bör se ut så här:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Markera cellområdet C1 till E3.

  4. Skriv in följande formel i formelfältet och tryck sedan på Ctrl + Skift + Retur:

    =C8:E10

    En 3 x 3-matris av celler 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: C3 markerad, tryck på F2 för att växla till redigeringsläge.

  2. Tryck på F9 för att konvertera cellreferenserna på värden. Excel konverterar värdena i en matriskonstant. Formeln bör nu = {10,20,30; 40,50,60; 70,80,90}.

  3. Tryck på Ctrl + Skift + Retur för att ange matriskonstanten som en matrisformel.

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 sedan på Ctrl + Skift + Retur om du vill se 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 räknar det totala antalet tecken (66) i cellerna A2 till och med A6.

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

I det här fallet returnerar du använder funktionen längd längden på varje textsträng i var och en av cellerna i området. Funktionen Summa adderar värdena sedan och visar resultat (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 vissa slumptal i celler A1:A11.

  2. Markera cellerna C1 till C3. Det här värdet av cellerna innehåller resultat som returneras av matrisformeln.

  3. Skriv in följande formel och tryck sedan på Ctrl + Skift + Retur:

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

Den här formeln använder en matriskonstant om du vill beräkna funktionen för små tre gånger och återgå minsta (1), andra minsta (2) och tredje minsta (3) medlemmar i matrisen som finns i cellerna a1: a10 att söka efter fler värden kan du lägga till fler argument till den konstant. 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 (LITEN (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 D3.

  2. Ange den här formeln i formelfältet och tryck sedan på Ctrl + Skift + Retur:

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

Nu kan det hjälpa bör känna till lite rad - och indirekt funktioner. Du kan skapa en matris med fel i följd heltal med hjälp av funktionen rad . Till exempel markera en tom kolumn med 10 celler i övningskalkylbladet, ange den här matrisformeln och tryck sedan på Ctrl + Skift + Retur:

=RAD(1:10)

Med formeln skapas en kolumn med 10 upprepade heltal. Om du vill se ett problem som skulle kunna uppstå kan du infoga en rad ovanför området som innehåller matrisformeln (dvs. ovanför rad 1). Radreferenserna justeras och heltal från 2 till 11 genereras. Korrigera problemet genom att lägga till funktionen INDIREKT i 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.

Nu ska vi ta en titt på den formel som du använt tidigare, = stor (A5:A14,ROW(INDIRECT("1:3"))) – från och med inre parenteser och arbeta utåt: indirekt returnerar funktionen en uppsättning textvärden, i det här fallet värdena 1 till 3. Funktionen rad genererar i tur och ordning en cell tre kolumner matris. Funktionen stor använder värdena i cellområdet A5:A14 och den utvärderas tre gånger, en gång för varje referens som returneras av funktionen rad . Värdena 3200, 2700 och 2000 returneras till tre cell kolumner matrisen. Om du vill söka efter fler värden du lägger till en större cellområde funktionen indirekt .

Med tidigare exempel använder som den här formeln med andra funktioner, till exempel Summa och medel.

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

Gå tillbaka till tidigare textsträng exempel, Skriv in följande formel i en tom cell och trycker på Ctrl + Skift + Retur:

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

Texten ”bunch of cells that” visas.

Nu ska vi titta närmare på formeln, från och med inre element och arbeta utåt. Du använder funktionen längd returnerar längden på vart och ett av objekten i cellområdet a2: a6. Funktionen MAX beräknar det största värdet bland de objekt som motsvarar den längsta textsträngen som visas 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 (LEN(A2:A6))

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

LEN(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 funktionen INDEX har följande argument: en matris och ett rad- och tal i denna matris. Cellområdet a2: a6 innehåller matrisen, funktionen Passa innehåller celladressen 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*(Minadata<>Dinadata))

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äftelse

Delar av den här artikeln har baserat på en serie Excel Power User kolumner skrivet av Colin Wilcox och anpassas 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

Dynamiska matrisformler kontra äldre CSE matrisformler

Funktionen FILTER

Funktionen SLUMPMATRIS

Funktionen SEKVENS

Funktionen ENKEL

Funktionen SORTERA

Funktionen SORTERAEFTER

Funktionen UNIK

#SPILL!-fel 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.

×