Kuidas kasutada funktsiooni Exceli SUMPRODUCT -

Kokkuvõte

Funktsioon SUMPRODUCT korrutab vahemikud või massiivid kokku ja tagastab toodete summa. See kõlab igavalt, kuid SUMPRODUCT on uskumatult mitmekülgne funktsioon, mida saab kasutada loendamiseks ja summeerimiseks nagu COUNTIFS või SUMIFS, kuid suurema paindlikkusega. Funktsioonide veelgi laiendamiseks saab SUMPRODUCT-is hõlpsasti kasutada muid funktsioone.

Eesmärk

Korrutage, seejärel summeerige massiivid

Tagastusväärtus

Korrutatud ja summeeritud massiivide tulemus

Süntaks

= SUMPRODUCT (massiiv1, (massiiv2),…)

Argumendid

• massiiv1 - esimene massiiv või vahemik, mis korrutatakse, seejärel lisatakse.
• array2 - (valikuline) Teine massiiv või vahemik korrutamiseks, seejärel lisage.

Versioon

Excel 2003

Kasutusjuhised

Funktsioon SUMPRODUCT töötab massiividega, kuid selle sisestamiseks pole vaja tavalist massiivi süntaksit (Ctrl + Tõst + Enter). Funktsiooni SUMPRODUCT eesmärk on massiivid korrutada, seejärel liita. Kui tarnitakse ainult üks massiiv, summeerib SUMPRODUCT lihtsalt massiivi üksused. Tarnida saab kuni 30 massiivi.

SUMPRODUCTiga esimest korda kokku puutudes võib see tunduda igav, keeruline ja isegi mõttetu. Kuid SUMPRODUCT on hämmastavalt mitmekülgne funktsioon, mida kasutatakse palju. Kuna see tegeleb massiividega graatsiliselt ja kaebusteta, saate seda kasutada lahtrivahemike nutikal ja elegantsel viisil töötlemiseks (vaadake linke selle lehe valeminäidetele).

SUMPRODUCTi toimimise illustreerimiseks on siin mõned levinumad näited.

SUMPRODUCT tingimuslike summade ja loenduste jaoks

Oletame, et teil on A2: B6 mõned tellimuse andmed, veerus A olek, veerus B müük:

	A	B
1	Osariik	Müük
2	UT	75
3	CO	100
4	TX	125
5	CO	125
6	TX	150

SUMPRODUCTi abil saate Texase ("TX") kogumüüki selle valemiga lugeda:

=SUMPRODUCT(--(A2:A6="TX"))

Texase ("TX") kogumüügi saate kokku võtta järgmise valemiga:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Märkus: Ärge segage topeltnegatiivi. See on tavaline trikk, mida kasutatakse täpsemates Exceli valemites, et sundida TÕENE ja VALE väärtusi 1-ks ja 0-ks. Vaadake rohkem allpool …

Ülaltoodud summanäite jaoks on siin kahe massiivi virtuaalne esitus, mida SUMPRODUCT esmalt töödeldi:

massiiv1	massiiv2
VÄÄR	75
VÄÄR	100
TÕSI	125
VÄÄR	125
TÕSI	150

Igal massiivil on 5 üksust. Esimene massiiv sisaldab TRUE / FALSE väärtusi, mis tulenevad avaldisest A2: A6 = "TX", ja teine ​​massiiv on B2: B6 sisu. Iga esimese massiivi üksus korrutatakse teise massiivi vastava üksusega. Kuid praeguses olekus on SUMPRODUCT tulemus null, kuna TRUE ja FALSE väärtusi käsitletakse nullina. Vajame, et massiivi 1 üksused oleksid arvulised - need tuleb sundida ühedeks ja nullideks. Siit tuleb topeltnegatiiv.

Kasutades topeltnegatiivi - (kahekordne unaarne, teie tehniliste tüüpide puhul), võime sundida TÕENE / VALE arvväärtusteks üks ja null, nagu on näidatud allpool toodud virtuaalses kujutises. Viimane veerg "Toode" tähistab kahe massiivi koos korrutamise tulemust. Summeeritud tulemus 275 on väärtus, mille SUMPRODUCT tagastab.

massiiv1		massiiv2		Toode
0	*	75	=	0
0	*	100	=	0
1	*	125	=	125
0	*	125	=	0
1	*	150	=	150
Summa				275

Kasutades massiivide korral lokkisulgude süntaksit, näeb näide pärast sundimist välja selline:

=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150)) 

ja niimoodi pärast korrutamist:

=SUMPRODUCT((0,0,125,0,150))

See näide laiendab ülaltoodud ideid üksikasjalikumalt.

SUMPRODUCT koos muude funktsioonidega

SUMPRODUCT saab otse kasutada muid funktsioone. Võib-olla näete funktsiooni LEN, mida kasutatakse vahemikus olevate tähemärkide loendamiseks funktsiooniga LEN, või funktsioonidega nagu ISBLANK, ISTEXT jne. Need ei ole tavaliselt massiivi funktsioonid, kuid kui neile antakse vahemik, loovad nad "tulemuste massiivi". Kuna SUMPRODUCT on ehitatud massiividega töötamiseks, suudab see massiividega otse arvutada. See võib olla hea viis töölehe ruumi kokkuhoiuks, kaotades vajaduse veeru "abistaja" järele.

Oletame näiteks, et teil on A1-s A10 kümme erinevat tekstiväärtust ja soovite kõigi 10 väärtuse jaoks kokku lugeda tähemärgid. Võite lisada veergu B abistaja veeru, mis kasutab järgmist valemit: LEN (A1), et arvutada igas lahtris olevad märgid. Siis saaksite kõigi 10 numbri liitmiseks kasutada summat. SUMPRODUCTi abil saate siiski kirjutada järgmise valemi:

=SUMPRODUCT(LEN(A1:A10))

Kui seda kasutatakse vahemikus nagu A1: A10, tagastab LEN 10 väärtusega massiivi. Siis summeerib SUMPRODUCT lihtsalt kõik väärtused ja tagastab tulemuse, abistaja veergu pole vaja.

Vaadake allpool näiteid paljudest muudest SUMPRODUCTi kasutamise viisidest.

Märkused:

1. SUMPRODUCT käsitleb massiivides olevaid mitte-numbrilisi üksusi nullidena.
2. Massiivargumendid peavad olema sama suured. Vastasel juhul genereerib SUMPRODUCT #VALUE! vea väärtus.
3. Massiivide sisesed loogilised testid loovad TÕENE ja VALE väärtused. Enamikul juhtudel soovite neid sundida 1-le ja 0-le.
4. SUMPRODUCT võib teiste funktsioonide tulemusi sageli otse kasutada (vt valeminäiteid allpool)