Sumceller, der opfylder flere kriterier med Excel SUMPRODUCT

01 af 02

Sumceller der falder mellem to værdier

SUMPRODUCT Oversigt

SUMPRODUCT-funktionen i Excel er en meget alsidig funktion, der giver forskellige resultater afhængigt af, hvordan funktionens argumenter indtastes.

Normalt multiplicerer SUMPRODUCT elementerne i en eller flere arrays for at få deres produkt og tilføjer eller summerer produkterne sammen.

Ved at justere funktionalens syntaks kan den imidlertid kun bruges til at summe kun dataene i celler, der opfylder specifikke kriterier.

Siden Excel 2007 har programmet indeholdt to funktioner - SUMIF og SUMIFS - som vil summe data i celler, der opfylder et eller flere sæt kriterier.

Til tider er SUMPRODUCT imidlertid lettere at arbejde med, når det kommer til at finde flere betingelser relateret til det samme område som vist i billedet ovenfor.

SUMPRODUCT Funktionssyntax til sumceller

Syntaxen bruges til at få SUMPRODUCT til at summe data i celler, der opfylder specifikke forhold, er:

= SUMPRODUCT (condition1 * condition2 * array)

condition1, condition2 - betingelserne, der skal opfyldes før funktionen, finder produktet fra arrayet.

array - et sammenhængende udvalg af celler

Eksempel: Summere data i celler, der opfylder flere betingelser

Eksemplet i billedet ovenfor tilføjer dataene i celler i området D1 til E6, der ligger mellem 25 og 75.

Indtastning af SUMPRODUCT-funktionen

Da dette eksempel bruger en uregelmæssig form for SUMPRODUCT-funktionen, kan funktionens dialogboks ikke bruges til at indtaste funktionen og dens argumenter. I stedet skal funktionen indtastes manuelt i et regneark celle.

1. Klik på celle B7 i regnearket for at gøre det til den aktive celle;
2. Indtast følgende formel i celle B7: = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75) * (A2: B6))
3. Svaret 250 bør forekomme i celle B7
4. Svaret blev ankommet ved at tilføje de fem tal i intervallet (40, 45, 50, 55 og 60), der ligger mellem 25 og 75. Totalen er 250

Breaking down the SUMPRODUCT Formula

Når betingelser anvendes til sine argumenter, evaluerer SUMPRODUCT hvert arrayelement mod betingelsen og returnerer en boolsk værdi (TRUE eller FALSE).

Til beregningernes formål tildeles Excel en værdi på 1 for de array elementer, der er SAND (opfylde betingelsen) og en værdi af 0 for array elementer, der er falske (ikke opfylder betingelsen).

For eksempel er nummeret 40:

• er TRUE for den første betingelse, så en værdi af 1 er tildelt i den første matrix;
• er TRUE for den anden betingelse, så en værdi af 1 er tildelt i den anden matrix.

nummeret 15:

• er FALSE for den første betingelse, så en værdi af 0 er tildelt i den første matrix;
• er TRUE for den anden betingelse, så en værdi af 1 er tildelt i den anden matrix.

De tilsvarende og nuller i hvert array multipliceres sammen:

• For tallet 40 - vi har 1 x 1 tilbage en værdi på 1;
• For nummer 15 - vi har 0 x 1 tilbage en værdi på 0.

Fortsæt læsning herunder

02 af 02

Multiplicere Ones og Zeros af Range

Disse og nuller multipliceres derefter med tallene i området A2: B6.

Dette er gjort for at give os de tal, der vil opsummeres af funktionen.

Dette virker fordi:

• 1 gange et tal er lig med det oprindelige tal
• 0 gange ethvert tal er lig med 0

Så vi ender med:

• 1 * 40 = 400 * 15 = 00 * 22 = 01 * 45 = 451 * 50 = 501 * 55 = 550 * 25 = 00 * 75 = 01 * 60 = 600 * 100 = 0

Summere resultaterne

SUMPRODUCT opsummerer de ovennævnte resultater for at finde svaret.

40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250