Generisk formel
=BYROW(område;LAMBDA(række;SUM(række)))
Resumé
For at få en matrix af rækketotaler baseret på række af numeriske værdier, kan du bruge en formel baseret BYROW-funktion sammen med funktionerne LAMBDA og SUM. I det viste eksempel er formlen i J2:
=BYROW(data;LAMBDA(rækken;SUM(rækken)))
hvor data er det navngivne område B2:H8. Resultatet er en matrix med syv summer, en for hver række i området, som det ses i kolonne J.
Bemærk: I ældre versioner af Excel kan du bruge MPRODUKT-funktionen, som forklaret nedenfor.
Forklaring
I dette eksempel er målet at returnere en matrix med syv subtotaler, én for hver af byerne nævnt i kolonne A. Tallene, der skal summeres, er indeholdt i data, som er det navngivne område B2:H8. Dette er et eksempel på en opgave, hvor målet er at skabe en matrix af summer i stedet for en enkelt sum. Vi kan ikke bruge en funktion som SUM alene, fordi SUM vil aggregere resultater og returnere en enkelt værdi. I artiklen nedenfor ser vi på to tilgange, en baseret på BYROW-funktionen og en baseret på MMULT-funktionen.
Med funktionen BYROW
I Excel 365 er den mest ligetil måde at generere subtotaler for hver række på med BYROW-funktionen. Formålet med BYROW er at behandle data på en “rækkevis” måde. For eksempel, hvis BYROW får et array med 7 rækker, vil BYROW returnere enkelt array med 7 resultater. I det viste eksempel er formlen i J2:
=BYROW(data;LAMBDA(rækken;SUM(rækken)))
Beregningen udført på hver række leveres af en brugerdefineret LAMBDA-funktion, som skal returnere et enkelt resultat for hver række. I dette eksempel summerer LAMBDA-funktionen, der bruges i BYROW, hver række således:
LAMBDA(rækken;SUM(rækken)) // summer hver række
Resultatet er en række af summer, én pr. række, der løber ind i området J2:J8. Dette resultat er fuldt ud dynamisk. Hvis dataværdierne ændres, eller hvis dataområdet udvides eller trækkes sammen, opdateres outputtet fra BYROW efter behov. Selvom dette eksempel omhandler totaler, kan det samme mønster bruges til at beregne andre oplysninger om rækker, herunder max, min, gennemsnit osv. som dette:
=BYROW(data;LAMBDA(rækken;MAKS(rækken))) // maks
=BYROW(data,LAMBDA(række,MIN(række))) // min
=BYROW(data,LAMBDA(række,MIDDEL(række))) // gennemsnit
Med funktionen MPRODUKT
En anden måde at løse dette problem på er med MPRODUKT-funktionen, som udfører matrixmultiplikation. MPRODUKT tager to arrays, array1 og array2, her skal gælde, at antallet af kolonner i array1 er det samme som antallet af rækker i array2. Den resulterende matrix vil have samme antal rækker som den første matrix og det samme antal kolonner som den anden matrix. MPRODUKT-formlen ser sådan ud:
=MPRODUKT(--data;TRANSPONER((KOLONNE(data)^0)))
Det første array er simpelthen alle værdier i området data, det navngivne område B2:H8:
=MPRODUKT(--data
For at beskytte mod tomme celler, som vil få MPRODUKT til at vise #VÆRDI! fejl, bruger vi en dobbelt negativ (–) for at tvinge eventuelle tomme celler til at være nul.
Dernæst skal vi oprette array2. Det første array indeholder 7 kolonner, så vi skal bruge det andet array, der skal indeholde 7 rækker. Vi ønsker kun en enkelt kolonne med resultater, så det andet array skal være 7 rækker gange 1 kolonne (7 x 1). Fordi vi ikke ønsker at ændre nogen værdier, bør arrayet kun indeholde tallet 1 (dvs. multipliering med 1 ændrer ikke den oprindelige værdi). Array2 genereres med TRANSPONER-funktionen og KOLONNE-funktionen som dette:
TRANSPONER((KOLONNE(data)^0)
Selvom den er lidt kryptisk, er denne syntaks ovenfor en smart måde at udføre opgaven på. Funktionen KOLONNE returnerer en 1 x 7 matrix af kolonnenumre:
KOLONNE(data) // returnerer {2,3,4,5,6,7,8}
Dernæst opløftes disse tal til nul’te potens med eksponentoperatoren (^), hvilket skaber en 1 x 7 matrix af 1ere:
KOLONNE(data)^0) // returnerer {1,1,1,1,1,1,1}
Og TRANSPONER-funktionen vender arrayet fra 1 x 7 til 7 x 1:
TRANSPONER({1;1;1;1;1;1;1}) // returnerer {1;1;1;1;1;1;1}
Resultatet afleveres til MPRODUKT-funktionen som array2. MPRODUKT-funktionen udfører derefter matrixmultiplikation med de to arrays og returnerer en subtotal for hver række:
=MPRODUKT(--data;{1;1;1;1;1;1;1})
returnerer arrayet:
{1878;2073;1517;2230;2304;2086;2450}
Disse værdier returneres til celle J2 og overføres til området J2:J8.
Alternativt kan bruges SEKVENS
En anden måde at konstruere array2 inde i MPRODUKT er med SEKVENS-funktionen som denne:
=MPRODUKT(--data;SEKVENS(KOLONNER(data);1;1;0))
Denne formel fungerer på samme måde, men array2 oprettes med funktionen SEQUENCE direkte:
SEKVENS(KOLONNER(data);1;1;0) // returnerer {1;1;1;1;1;1;1}
Bemærk, at vi bruger funktionen KOLONNER til at fortælle SEKVENS, hvor mange rækker der skal oprettes (7).
