Retningslinjer for og eksempler på matrixformler

To become an Excel power user, you need to know how to use array formulas, which can perform calculations that you can't do by using non-array formulas. The following article is based on a series of Excel Power User columns written by Colin Wilcox and adapted from chapters 14 and 15 of Excel 2002 Formulas, a book written by John Walkenbach, an Excel MVP.

Denne artikel indeholder

Få mere at vide om matrixformler

Få mere at vide om matrixkonstanter

Oprette en- og todimensionale konstanter

Brug af grundlæggende matrixformler

Anvende avancerede matrixformler

Få mere at vide om matrixformler

This section introduces array formulas and explains how to enter, edit, and troubleshoot them.

Hvorfor anvende matrixformler?

If you have experience using formulas in Excel, you know that you can perform some fairly sophisticated operations. For example, you can calculate the total cost of a loan over any given number of years. However, if you really want to master formulas in Excel, you need to know how to use array formulas. You can use array formulas to do complex tasks, such as:

  • Tælle antallet af tegn i et celleområde.

  • Lægge de tal sammen, der opfylder bestemte kriterier, f.eks. at de skal være de laveste værdier i et område, eller at det skal være tal inden for et bestemt interval.

  • Lægge hver n'te værdi i et område sammen.

Bemærk: You may see array formulas referred to as "CSE formulas," because you press CTRL+SHIFT+ENTER to enter them into your workbooks.

Hurtig introduktion til matrixer og matrixformler

If you've done even a little programming, you've probably run across the term array. For our purposes, an array is a collection of items. In Excel, those items can reside in a single row (called a one-dimensional horizontal array), a column (a one-dimensional vertical array), or multiple rows and columns (a two-dimensional array). You cannot create three-dimensional arrays or array formulas in Excel.

An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and use the array formula to calculate a column or row of subtotals. You can also place an array formula in a single cell and then calculate a single amount. An array formula that resides in multiple cells is called a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.

I eksemplerne i næste afsnit kan du se, hvordan du kan oprette matrixformler med en eller flere celler.

Prøv det!

I denne øvelse kan du se, hvordan du kan bruge matrixformler i en eller flere celler til at beregne nogle salgstal. På de første trin bruges en formel med flere celler til at beregne et sæt subtotaler. På de næste trin bruges en formel med én celle til at beregne en hovedtotal.

Oprette en matrixformel med flere celler

  1. Open a new, blank workbook.

  2. Copy the example worksheet data, and then paste it into the new workbook starting at cell A1.

Sælger

Biltype

Antal solgt

Enhedspris

Samlet salg

Pedersen

Sedan

-5

2200

Coupé

4

1800

Ingle

Sedan

6

2300

Coupé

8

1700

Jordan

Sedan

3

-2000

Coupé

1

1600

Pica

Sedan

9

2150

Coupé

-5

1950

Sanchez

Sedan

6

DKK 2.250

Coupé

8

-2000

  1. Use the Paste Options button Knapflade that appears nearby to match the destination formatting.

  2. To multiply the values in the array (the cell range C2 through D11), select cells E2 through E11, and then enter the following formula in the formula bar:

    =C2:C11*D2:D11

  3. Tryk på Ctrl+Skift+Enter.

Excel surrounds the formula with braces ({ }) and places an instance of the formula in each cell of the selected range. This happens very quickly, so what you see in column E is the total sales amount for each car type for each salesperson.

Eksempeldata

Oprette en matrixformel med én celle

  1. In cell A13 of the workbook, type Total Sales.

  2. In cell B13, type the following formula, and then press CTRL+SHIFT+ENTER:

    =SUM(C2:C11*D2:D11)

In this case, Excel multiplies the values in the array (the cell range C2 through D11) and then uses the SUM function to add the totals together. The result is a grand total of $111,800 in sales. This example shows how powerful this type of formula can be. For example, suppose you have 15,000 rows of data. You can sum part or all of that data by creating an array formula in a single cell.

Also, notice that the single-cell formula (in cell B13) is completely independent of the multi-cell formula (the formula in cells E2 through E11). This demonstrates another advantage of using array formulas — flexibility. You can take any number of actions, such as changing the formulas in column E or deleting that column altogether, without affecting the single-cell formula.

Matrixformler har også følgende fordele:

  • Konsistens    Hvis du klikker på en af cellerne fra E2 og nedad, vises den samme formel. Denne konsistent kan sikre en større nøjagtighed.

  • Safety    You cannot overwrite a component of a multi-cell array formula. For example, click cell E3 and press DELETE. You have to either select the entire range of cells (E2 through E11) and change the formula for the entire array, or leave the array as is. As an added safety measure, you must press CTRL+SHIFT+ENTER to confirm the change to the formula.

  • Smaller file sizes    You can often use a single array formula instead of several intermediate formulas. For example, the workbook you created for this exercise uses one array formula to calculate the results in column E. If you had used standard formulas (such as =C2*D2), you would have used 11 different formulas to calculate the same results.

Et nærmere kig på syntaksen i matrixformler

In general, array formulas use standard formula syntax. They all begin with an equal (=) sign, and you can use any of the built-in Excel functions in your array formulas. The key difference is that when using an array formula, you must press CTRL+SHIFT+ENTER to enter your formula. When you do this, Excel surrounds your array formula with braces — if you type the braces manually, your formula will be converted to a text string, and it will not work.

The next thing you need to understand is that array functions are a form of shorthand. For example, the multi-cell function that you used earlier is the equivalent of:

=C2*D2
=C3*D3

and so on. The single-cell formula in cell B13 condenses all of those multiplication operations, plus the arithmetic required to add those subtotals: =E2+E3+E4, and so on.

Rules for entering and changing array formulas

The primary rule for creating an array formula is worth repeating: Press CTRL+SHIFT+ENTER whenever you need to enter or edit an array formula. That rule applies to both single-cell and multi-cell formulas.

Whenever you work with multi-cell formulas, you also need to follow these rules:

  • You must select the range of cells to hold your results before you enter the formula. You did this in step 3 of the multi-cell array formula exercise when you selected cells E2 through E11.

  • You cannot change the contents of an individual cell in an array formula. To try this, select cell E3 in the sample workbook and press DELETE.

  • You can move or delete an entire array formula, but you cannot move or delete part of it. In other words, to shrink an array formula, you first delete the existing formula and then start over.

    Tip: Hvis du vil slette en matrixformel, skal du markere hele formlen, f.eks. =C2:C11*D2:D11, trykke på Delete og derefter trykke på Ctrl+Skift+Enter.

  • You cannot insert blank cells into or delete cells from a multi-cell array formula.

Udvide en matrixformel

At times, you may need to expand an array formula. (Remember that you cannot shrink an array formula.) The process is not complicated, but you must remember the rules listed in the previous section.

  1. In the sample workbook, clear any text and single-cell formulas that are located below the main table.

  2. Paste these additional lines of data into the workbook starting at cell A12. Use the Paste Options button Knapflade that appears nearby to match the destination formatting.

Toth

Sedan

6

2500

Coupé

7

1900

Wang

Sedan

4

2200

Coupé

3

-2000

Young

Sedan

8

2300

Coupé

8

2100

  1. Markér det celleområde, der indeholder den aktuelle matrixformel (E2:E11) plus de tomme celler (E12:E17) ud for de nye data. Du skal med andre ord markere cellerne E2:E17.

  2. Tryk på F2 for at skifte til redigeringstilstand.

  3. In the formula bar, change C11 to C17, change D11 to D17, and then press CTRL+SHIFT+ENTER . Excel updates the formula in cells E2 through E11 and places an instance of the formula in the new cells, E12 through E17.

    Eksempeldata

Ulemper ved matrixformler

Array formulas can seem magical, but they also have some disadvantages:

  • You may occasionally forget to press CTRL+SHIFT+ENTER. Remember to press this key combination whenever you enter or edit an array formula.

  • Other users may not understand your formulas. In practice, array formulas are typically undocumented in a worksheet, so if other people need to modify your workbooks, you should either avoid array formulas or make sure those users understand how to change them.

  • Afhængigt af computeren hastighed og hukommelse kan store matrixformler gøre beregningerne langsommere.

For more information about working array formulas, see the following:

Toppen af siden

Få mere at vide om matrixkonstanter

I dette afsnit får du en introduktion til matrixkonstanter, og det beskrives, hvordan du opretter, redigerer og foretager fejlfinding af dem.

En kort introduktion til matrixkonstanter

Array constants are a component of array formulas. You create array constants by entering a list of items and then manually surrounding the list with braces ({ }), like so:

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

Earlier in this article, we emphasized the need to press CTRL+SHIFT+ENTER when you create array formulas. Because array constants are a component of array formulas, you surround the constants with braces manually by typing them. You then use CTRL+SHIFT+ENTER to enter the entire formula.

If you delimit (separate) the items by using commas, you create a horizontal array (a row). If you delimit the items by using semicolons, you create a vertical array (a column). To create a two-dimensional array, you delimit the items in each row by using commas, and you delimit each column by using semicolons.

As with array formulas, you can use array constants with any of the built-in functions that Excel provides. The following sections explain how to create each kind of constant and how to use these constants with functions in Excel.

Oprette en- og todimensionale konstanter

Med den følgende procedure får du øvelse i at oprette vandrette, lodrette og todimensionale konstanter.

Oprette en vandret konstant

  1. Brug projektmappen fra de tidligere eksempler, eller opret en ny projektmappe.

  2. Markér cellerne A1 til og med E1.

  3. Skriv følgende formel på formellinjen, og tryk derefter på Ctrl+Skift+Enter:

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

    Bemærk: I dette tilfælde skal du skrive klammeparenteserne ({ }).

Følgende resultat vises:

Vandret matrixkonstant i formel

You may wonder why you can't just type the numbers manually. The Use constants in formulas section, later in this article, demonstrates the advantages of using array constants.

Oprette en lodret konstant

  1. Markér en kolonne med fem celler i projektmappen.

  2. Skriv følgende formel på formellinjen, og tryk på Ctrl+Skift+Enter:

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

Følgende resultat vises:

Lodret matrixkontakt i matrixformel

Oprette en todimensional konstant

  1. Markér en blok celler i projektmappen på fire kolonner i bredden og tre rækker i højden.

  2. Skriv følgende formel på formellinjen, og tryk derefter på Ctrl+Skift+Enter:

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

Der vises følgende resultat:

Todimensional matrixkonstant i matrixformel

Brug af konstanter i formler

Now that you are familiar with entering array constants, here is a simple example that uses what we've discussed:

  1. Open a blank worksheet.

  2. Copy the following table starting at cell A1. Use the Paste Options button Knapflade that appears nearby to match the destination formatting.

3

4

-5

6

7

  1. In cell A3, enter the following formula, and then press CTRL+SHIFT+ENTER:

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

Notice that Excel surrounds the formula with another set of braces, because you entered it as an array formula.

Matrixformel med matrixkonstant

Værdien 85 vises i celle A3. I næste afsnit beskrives det, hvordan formlen virker.

Et nærmere kig på syntaksen i matrixkonstanter

Den formel, du netop har brugt, består af flere dele.

Syntaksen for en matrixformel med en matrixkonstant

1. Funktion

2. Lagret matrix

3. Operator

4. Matrixkonstant

The last element inside the parentheses is the array constant: {1,2,3,4,5}. Remember that Excel does not surround array constants with braces; you must do this. Also remember that after you add a constant to an array formula, you press CTRL+SHIFT+ENTER to enter the formula.

Da Excel først udfører handlinger på de udtryk, der er angivet i parentes, vil de næste to elementer, der behandles, være de værdier, der er gemt i projektmappen (A1:E1) og operatoren. På dette tidspunkt ganger formlen værdierne i den lagrede matrix med de tilsvarende værdier i konstanten. Dette svarer til:

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

Til sidst lægger funktionen SUM værdierne sammen, og summen 85 vises i celle A3.

Hvis du vil undlade at bruge den lagrede matrix og holde hele behandlingen i hukommelsen, kan du erstatte den lagrede matrix med en anden matrixkonstant:

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

To try this, copy the function, select a blank cell in your workbook, paste the formula into the formula bar, and then press CTRL+SHIFT+ENTER. You see the same result as you did in the earlier exercise that used the array formula =SUM(A1:E1*{1,2,3,4,5}).

De elementer, du kan bruge i konstanter

Array constants can contain numbers, text, logical values (such as TRUE and FALSE), and error values ( such as #N/A). You can use numbers in the integer, decimal, and scientific formats. If you include text, you must surround that text with double quotation marks (").

Array constants cannot contain additional arrays, formulas, or functions. In other words, they can contain only text or numbers that are separated by commas or semicolons. Excel displays a warning message when you enter a formula such as {1,2,A1:D4} or {1,2,SUM(Q2:Z8)}. Also, numeric values cannot contain percent signs, dollar signs, commas, or parentheses.

Navngive matrixkonstanter

Possibly the best way to use array constants is to name them. Named constants can be much easier to use, and they can hide some of the complexity of your array formulas from beginning users. To name an array constant and use it in a formula, do the following:

  1. Klik på Definer navn i gruppen Definerede navne under fanen Formler.
    Dialogboksen Definer navn vises.

  2. Skriv Kvartal1 i feltet Navn.

  3. Skriv følgende konstant (husk at skrive klammeparenteserne manuelt) i feltet Refererer til:

    ={"Januar","Februar","Marts"}

    Indholdet i dialogboksen skulle se sådan ud:

    Dialogboksen Rediger navn med formel

  4. Klik på OK.

  5. On the worksheet, select a row of three blank cells.

  6. Skriv følgende formel, og tryk derefter på Ctrl+Skift+Enter:

    =Kvartal1

Følgende resultat vises:

Navngivet matrix skrevet som formel

When you use a named constant as an array formula, remember to enter the equal sign. If you don't, Excel interprets the array as a string of text. Finally, keep in mind that you can use combinations of text and numbers.

Fejlfinding af matrixkonstanter

Hold øje med følgende problemer, hvis dine matrixkonstanter ikke virker:

  • Some elements might not be separated with the proper character. If you omit a comma or semicolon, or if you put one in the wrong place, the array constant may not be created correctly or you may see a warning message.

  • You may have selected a range of cells that doesn't match the number of elements in your constant. For example, if you select a column of six cells for use with a five-cell constant, the #N/A error value appears in the empty cell. Conversely, if you select too few cells, Excel omits the values that don't have a corresponding cell.

Matrixkonstanter i brug

I de følgende eksempler kan du se nogle eksempler på brug af matrixkonstanter i matrixformler. Nogle af eksemplerne bruger funktionen TRANSPONER til at konvertere rækker til kolonner og omvendt.

Multiplicer hvert enkelt element i en matrix

  1. Markér en blok med tomme celler på fire kolonner i bredden og tre rækker i højden.

  2. Skriv følgende formel, og tryk derefter på Ctrl+Skift+Enter.

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

Tag kvadratroden af elementerne i en matrix

  • Markér en blok med tomme celler på fire kolonner i bredden og tre rækker i højden.

  • Skriv følgende matrixformel, og tryk derefter på Ctrl+Skift+Enter.

    ={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 eventuelt angive denne matrixformel, som bruger indsætningstegnet (^):

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

Transponere en endimensional række

  1. Markér en kolonne med fem tomme celler.

  2. Skriv følgende formel, og tryk derefter på Ctrl+Skift+Enter.

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

Selvom du har angivet en vandret matrixkonstant, konverterer funktionen TRANSPONER matrixkonstanten til en kolonne.

Transponere en endimensional kolonne

  1. Markér en række med fem tomme celler.

  2. Skriv følgende formel, og tryk derefter på Ctrl+Skift+Enter.

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

Selvom du har angivet en lodret matrixkonstant, konverterer funktionen TRANSPONER matrixkonstanten til en række.

Transponere en todimensional konstant

  1. Markér en blok med celler på tre kolonner i bredden og fire rækker i højden.

  2. Skriv følgende konstant, og tryk på Ctrl+Skift+Enter.

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

Funktionen TRANSPONER konverterer hver række til en række kolonner.

Toppen af siden

Brug af grundlæggende matrixformler

I dette afsnit får du eksempler på grundlæggende matrixformler.

Introduktion

Use the data in this section to create two sample worksheets.

  1. Open an existing workbook or create a new workbook, and make sure it contains two blank worksheets.

  2. Copy the data in the following table, and paste it into the worksheet starting at cell A1.

04:00

the quick

-1

".2"

3

4

(DKK 1.200)

brown fox

-5

6

7

8

3200

jumped over

9

10 %

1.1

1.2

475

the lazy

13

14

15 %

-16

DKK 5.000

power user

-2000

6:00

1700

DKK 800

2700

Your finished worksheet should look like this.

Komplette eksempeldata

  1. Name the first worksheet Data, and name a second blank worksheet Arrays.

Oprette matrixer og matrixkonstanter fra eksisterende værdier

I det følgende eksempel beskrives det, hvordan du kan bruge matrixformler til at oprette links mellem celleområder i forskellige regneark. Det viser dog også, hvordan du opretter en matrixkonstant fra det samme sæt af værdier.

Oprette en matrix ud fra eksisterende værdier

  1. In your sample workbook, select the Arrays worksheet.

  2. Markér celleområde C1 til og med E3.

  3. Skriv følgende formel på formellinjen, og tryk derefter på Ctrl+Skift+Enter:

    =Data!E1:G3

Følgende resultat vises:

Resultater i regnearket Matrixer

The formula links to the values stored in cells E1 through G3 on the Data worksheet. The alternative to this multi-cell array formula is to place a unique formula in each cell of the Arrays worksheet, as follows.

=Data!E1

=Data!F1

=Data!G1

=Data!E2

=Data!F2

=Data!G2

=Data!E3

=Data!F3

=Data!G3

If you change some of the values on the Data worksheet, those changes appear on the Arrays worksheet. Remember that to change any values on the Data worksheet, you have to follow the rules for editing array formulas. For more information about those rules, see the section Learn about array formulas.

Oprette en matrixkonstant ud fra eksisterende værdier

  1. On the Arrays worksheet, select cells C1 through E3.

  2. Tryk på F2 for at skifte til redigeringstilstand.

  3. Press F9 to convert the cell references to values. Excel converts the values into an array constant.

  4. Tryk på Ctrl+Skift+Enter for at oprette matrixkonstanten som en matrixformel.

Excel replaces the =Data!E1:G3 array formula with the following array constant:

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

The link has been broken between the Data and Arrays worksheets, and the array formula has been replaced by an array constant.

Tælle antal tegn i et celleområde

I følgende eksempel kan du se, hvordan du kan tælle antallet af tegn, herunder antal mellemrum, i et celleområde.

  • On the Data worksheet, enter the following formula in cell C7, and then press CTRL+SHIFT+ENTER:

    =SUM(LEN(C1:C5))

The value 47 appears in cell C7.

In this case, the LEN function returns the length of each text string in each of the cells in the range. The SUM function then adds those values together and displays the result in the cell that contains the formula, C7.

Finde den n'te mindste værdi i et område

I dette eksempel kan du se, hvordan du kan finde de tre mindste værdier i et celleområde.

  1. On the Data worksheet, select cells A12 through A14.

    This set of cells will hold the results returned by the array formula.

  2. Skriv følgende formel på formellinjen, og tryk derefter på Ctrl+Skift+Enter:

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

The values 400, 475, and 500 appear in cells A12 through A14, respectively.

Denne formel bruger en matrixkonstant til at evaluere funktionen MINDSTE tre gang og returnerer den mindste (1), næstmindste (2) og tredjemindste (3) værdi i matrixen i cellerne A1:A10. Hvis du vil finde flere værdier, kan du føje flere argumenter til konstanten og et tilsvarende antal resultatceller til området A12:A14. Du kan også bruger flere funktioner med denne formel, f.eks. SUM eller MIDDEL. Eksempler:

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

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

Finde den n'te største værdi i et område

Hvis du vil finde de største værdier i et område, kan du erstatte funktionen MINDSTE med funktionen STØRSTE. I det følgende eksempel bruges funktioner RÆKKE og INDIREKTE også.

  1. On the Data worksheet, select cells A12 through A14.

  2. Press DELETE to clear the existing formula but leave the cells selected.

  3. Skriv denne formel på formellinjen, og tryk derefter på Ctrl+Skift+Enter:

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

The values 3200, 2700, and 2000 appear in cells A12 through A14, respectively.

At this point, it may help to know a bit about the ROW and INDIRECT functions. You can use the ROW function to create an array of consecutive integers. For example, select an empty column of 10 cells in your practice workbook, enter this array formula in cells A1:A10, and then press CTRL+SHIFT+ENTER:

=RÆKKE(1:10)

Formlen opretter en kolonne med ti efterfølgende heltal. Hvis du vil se et potentielt problem, kan du indsætte en række over det område, der indeholder matrixformlen, dvs. over række 1. Excel justerer rækkereferencerne, og formlen opretter heltal fra 2 til 11. Du kan løse det problem ved at indsætte funktionen INDIREKTE i formlen:

=RÆKKE(INDIREKTE("1:10"))

Funktionen INDIREKTE bruger tekststrenge som argumenter (hvilket er grunden til, at intervallet 1:10 er angivet i anførselstegn). Excel justerer ikke tekstværdier, når du indsætter rækker eller flytter matrixformlen. Det medfører, at funktionen RÆKKE altid opretter det ønskede interval af heltal.

Let us examine the formula that you used earlier — =LARGE(A1:A10,ROW(INDIRECT("1:3"))) — starting from the inner parentheses and working outward: The INDIRECT function returns a set of text values, in this case the values 1 through 3. The ROW function in turn generates a three-cell columnar array. The LARGE function uses the values in the cell range A1:A10, and it is evaluated three times, once for each reference returned by the ROW function. 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.

Og endelig kan du bruge denne formel med andre funktioner, f.eks. SUM og MIDDEL.

Finde den længste tekststreng i et celleområde

This example finds the longest string of text in a range of cells. This formula works only when a data range contains a single column of cells.

  • On the Data worksheet, clear the existing formula from cell C7, enter the following formula in that cell, and then press CTRL+SHIFT+ENTER:

    =INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1)

The value jumped over appears in cell C7.

Let us examine the formula, starting from the inner elements and working outward. The LEN function returns the length of each of the items in the cell range C1:C5. The MAX function calculates the largest value among those items, which corresponds to the longest text string, which is in cell C3.

Nu begynder det at blive mere kompliceret. Funktionen SAMMENLIGNING beregner forskydningen (den relative placering) af den celle, som indeholder den længste tekststreng. Dette kræver tre argumenter: en opslagsværdi, en opslagsmatrix og en sammenligningstype. Funktionen SAMMENLIGNING søger efter den angivne opslagsværdi i opslagsmatrixen. I dette tilfælde er opslagsværdien den længste tekststreng:

( MAX( LEN(C1:C5))

og strengen findes i denne matrix:

LEN( C1:C5)

Argumentet for sammenligningstypen er 0. Sammenligningstypen kan være værdien 1, 0 eller -1. Hvis du angiver 1, returnerer SAMMENLIGNING den største værdi, der er mindre end eller lig med opslagsværdien. Hvis du angiver 0, returnerer SAMMENLIGNING den første værdi, der er lig med opslagsværdien. Hvis du angiver -1, finder SAMMENLIGNING den mindste værdi, der er større end eller lig med den angivne opslagsværdi. Hvis du ikke angiver en værdi for matchningstypen, anvendes værdien 1.

Finally, the INDEX function takes these arguments: an array, and a row and column number within that array. The cell range C1:C5 provides the array, the MATCH function provides the cell address, and the final argument (1) specifies that the value comes from the first column in the array.

Toppen af siden

Anvende avancerede matrixformler

I dette afsnit kan du finde eksempler på avancerede matrixformler.

Opsummere et område, der indeholder fejlværdier

Funktionen SUM i Excel virker ikke, hvis du forsøger at opsummere et område, der indeholder fejlværdier, f.eks. #I/T. I dette eksempel kan du se, hvordan du kan opsummere værdierne i det navngivne område Data, som indeholder fejl.

=SUM(HVIS(ER.FEJL(Data);"";Data))

The formula creates a new array that contains the original values minus any error values. Starting from the inner functions and working outward, the ISERROR function searches the cell range (Data) for errors. The IF function returns a specific value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. In this case, it returns empty strings ("") for all error values because they evaluate to TRUE, and it returns the remaining values from the range (Data) because they evaluate to FALSE, meaning that they don't contain error values. The SUM function then calculates the total for the filtered array.

Tælle antal fejlværdier i et område

Dette eksempel minder om den foregående formel, men her returneres antallet af fejlværdier i det navngivne område Data i stedet for, at de filtreres fra:

=SUM(HVIS(ER.FEJL(Data),1,0))

This formula creates an array that contains the value 1 for the cells that contain errors and the value 0 for the cells that don't contain errors. You can simplify the formula and achieve the same result by removing the third argument for the IF function, like so:

=SUM(HVIS(ER.FEJL(Data),1))

Hvis du ikke angiver dette, returnerer funktionen HVIS værdien FALSK, hvis en celle ikke indeholder en fejlværdi. Det er muligt at forenkle formlen endnu mere:

=SUM(HVIS(ER.FEJL(Data)*1))

Denne version virker, fordi SAND*1=1 og FALSK*1=0.

Lægge værdier sammen baseret på betingelser

You may need to sum values based on conditions. For example, this array formula sums just the positive integers in a range named Sales:

=SUM(HVIS(Salg>0,Salg))

Funktionen HVIS opretter en matrix med positive og falske værdier. Funktionen SUM ignorerer de falske værdier, fordi 0+0=0. Det celleområde, du bruger i denne formel, kan bestå af et vilkårligt antal rækker og kolonner.

Du kan også lægge værdier sammen, der opfylder mere end én betingelse. Denne matrixformel beregner værdier større end 0 og mindre end eller lig med 5:

=SUM((Salg>0)*(Salg<=5)*(Salg))

Husk på, at denne formel returnerer en fejl, hvis området indeholder en eller flere celler, som ikke indeholder tal.

Du kan også oprette matrixformler, der bruger en type af ELLER-betingelse. Du kan f.eks. lægge de værdier sammen, som er mindre end 5 og større end 15:

=SUM(HVIS((Salg<5)+(Salg>15),Salg))

Funktionen HVIS finder alle de værdier, der er mindre end 5 og større end 15 og overfører derefter disse værdier til funktionen SUM.

Vigtigt: You cannot use the AND and OR functions in array formulas directly because those functions return a single result, either TRUE or FALSE, and array functions require arrays of results. You can work around the problem by using the logic shown in the previous formula. In other words, you perform math operations, such as addition or multiplication, on values that meet the OR or AND condition.

Beregne et gennemsnit, der udelader nulværdier

I dette eksempel kan du se, hvordan du kan fjerne nulværdier fra et område, når du skal finde middelværdien af værdierne i området. Formlen bruger et dataområde med navnet Salg:

=MIDDEL(HVIS(Salg<>0;Salg))

Funktionen HVIS opretter en matrix med de værdier, der ikke er lig med 0, og den overfører derefter disse værdier til funktionen MIDDEL.

Tælle antal forskelle mellem to celleområder

This array formula compares the values in two ranges of cells named MyData and YourData and returns the number of differences between the two. If the contents of the two ranges are identical, the formula returns 0. To use this formula, the cell ranges must be the same size and of the same dimension:

=SUM(HVIS(MineData=DineData,0,1))

Formlen opretter en ny matrix med samme størrelse som de områder, du sammenligner. Funktionen HVIS udfylder matrixen med værdien 0 og værdien 1 (0 for forskellige celler og 1 for identiske celler). Funktionen SUM returnerer derefter summen af værdierne i matrixen.

Du kan forenkle formlen på denne måde:

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

På samme måde som den formel, der tæller antal fejlværdier i et område, virker denne formel, fordi SAND*1=1 og FALSK*1=0.

Finde placeringen af den største værdi i et område

Denne matrixformel returnerer rækkenummeret for den største værdi i området Data, som består af én kolonne:

=MIN(HVIS(Data=MAKS(Data);RÆKKE(Data);""))

Funktionen HVIS opretter en ny matrix, der svarer til området Data. Hvis en tilsvarende celle indeholder den største værdi i området, indeholder matrixen rækkenummeret. Ellers indeholder matrixen en tom streng (""). Funktionen MIN bruger den nye matrix som det andet argument og returnerer den mindste værdi, hvilket svarer til rækkenummeret med den største værdi i Data. Hvis den største værdi i området Data findes i flere celler, returnerer formlen den række, som indeholder den første forekomst af værdien.

Hvis du vil returnere den faktiske celleadresse for den største værdi, skal du bruge denne formel:

=ADRESSE(MIN(HVIS(Data=MAKS(Data);RÆKKE(Data);""));KOLONNE(Data))

Toppen af siden

Del Facebook Facebook Twitter Twitter Mail Mail

Var disse oplysninger nyttige?

Fantastisk! Har du mere feedback?

Hvordan kan vi forbedre det?

Tak for din feedback!

×