Summa lahtrid, mis vastavad Exceli SUMPRODUCT mitmele kriteeriumile

Kahe väärtuse vahele jäävate lahtrite summa

SUMPRODUCT funktsioon Excelis on väga mitmekülgne funktsioon, mis annab sõltuvalt funktsiooni viisist erinevaid tulemusi argumendid sisestatakse.

Tavaliselt, nagu nimigi ütleb, korrutab SUMPRODUCT ühe või mitme elemendid massiivid oma toote hankimiseks ja seejärel tooted kokku liidab või summeerib.

Funktsioonide reguleerimisega süntakskuid seda saab kasutada ainult konkreetsetele kriteeriumidele vastavate lahtrite andmete summeerimiseks.

Alates Excel 2007-st on programm sisaldanud kahte funktsiooni - SUMIF ja SUMIFS - mis summeerib andmed lahtrites, mis vastavad ühele või mitmele seatud kriteeriumile.

Mõnikord on aga SUMPRODUCTiga lihtsam töötada, kui on vaja leida mitu samaga seotud tingimust ulatus nagu on näidatud ülaloleval pildil.

SUMPRODUCT funktsiooni süntaks lahtrite summaks

Konkreetsetele tingimustele vastavate lahtrite andmete summeerimiseks SUMPRODUCTi kasutatav süntaks on:

= SUMPRODUCT([tingimus1] * [tingimus2] * [massiiv])

tingimus1, tingimus2 – tingimused, mis peavad olema täidetud enne, kui funktsioon leiab massiivi korrutise.

massiiv – külgnev lahtrite vahemik.

Näide: andmete summeerimine lahtrites, mis vastavad mitmele tingimusele

Ülaltoodud pildi näide lisab andmed lahtritesse vahemikus D1 kuni E6, mis on vahemikus 25 kuni 75.

Funktsiooni SUMPRODUCT sisestamine

Kuna see näide kasutab funktsiooni SUMPRODUCT ebakorrapärast vormi, siis funktsiooni Dialoogikast ei saa kasutada funktsiooni ja selle argumentide sisestamiseks. Selle asemel tuleb funktsioon sisestada käsitsi a-sse tööleht kamber.

1. Klõpsake lahtril B7 töölehel, et see oleks aktiivne rakk;
2. Sisestage lahtrisse B7 järgmine valem:=SUMMATOODE(($A$2:$B$6>25)*($A$2:$B$6<75)*(A2:B6))
3. Vastus 250 peaks ilmuma lahtrisse B7
4. Vastuseks saadi viie numbri (40, 45, 50, 55 ja 60) lisamisega, mis jäävad vahemikku 25–75. Mis kokku on 250

SUMPRODUCT valemi jaotamine

Kui argumentide jaoks kasutatakse tingimusi, hindab SUMPRODUCT iga massiivi elementi tingimuse suhtes ja tagastab a Boole'i ​​väärtus (Õige või vale).

Arvutuste tegemiseks määrab Excel väärtuse 1 nende massiivi elementide jaoks, mis on TRUE (vastavad tingimusele) ja väärtusega 0 massiivi elementide jaoks, mis on FALSE (ei vasta tingimusele).

Näiteks number 40:

• on TRUE esimese tingimuse puhul, seega väärtus 1 on määratud esimeses massiivis;
• on teise tingimuse jaoks TRUE, seega väärtus 1 on määratud teises massiivis.

Number 15:

• on FALSE esimese tingimuse jaoks, seega väärtus 0 on määratud esimeses massiivis;
• on teise tingimuse jaoks TRUE, seega väärtus 1 on määratud teises massiivis.

Iga massiivi vastavad ühed ja nullid korrutatakse kokku:

• Arvu 40 jaoks - meil on 1 x 1, mis tagastab väärtuse 1;
• Arvu 15 puhul on meil 0 x 1, mis tagastab väärtuse 0.

Üheliste ja nullide korrutamine vahemikuga

Seejärel korrutatakse need ühed ja nullid numbritega vahemikus A2: B6.

Seda tehakse selleks, et anda meile numbrid, mille funktsioon liidab.

See toimib, sest:

• 1 korda mis tahes arv võrdub algarvuga
• 0 korda mis tahes arv on võrdne 0-ga

Nii et me lõpetame:

• 1 * 40 = 40
  0 * 15 = 0
  0 * 22 = 0
  1 * 45 = 45
  1 * 50 = 50
  1 * 55 = 55
  0 * 25 = 0
  0 * 75 = 0
  1 * 60 = 60
  0 * 100 = 0

Tulemuste summeerimine

Seejärel võtab SUMPRODUCT vastuse leidmiseks ülaltoodud tulemused kokku.

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