Excel

SUMPRODUCT s IF

Sumproduct With If

Vzorec programu Excel: SUMPRODUCT s IFGenerický vzorec | _+_ | Zhrnutie

Ak chcete filtrovať výsledky SUMPRODUCT so špecifickými kritériami, môžete použiť jednoduché logické výrazy priamo na polia vo funkcii, namiesto použitia funkcie IF . V uvedenom príklade vzorce v H5: H7 sú:





= SUMPRODUCT (expression,range)

kde nasledujúce pomenované rozsahy sú definované:

 
= SUMPRODUCT (--(color='red'),quantity,price) = SUMPRODUCT (--(state='tx'),--(color='red'),quantity,price) = SUMPRODUCT (--(state='co'),--(color='blue'),quantity,price)

Ak sa radšej vyhýbate pomenovaným rozsahom, použite rozsahy uvedené vyššie ako absolútne referencie . Logické výrazy v H6 a H7 je možné kombinovať, ako je to vysvetlené nižšie.





Vysvetlenie

Tento príklad ilustruje jednu z kľúčových silných stránok funkcie SUMPRODUCT - schopnosť filtrovať údaje pomocou základných logických výrazov namiesto funkcie IF. Vo vnútri SUMPRODUCT, prvý pole je logický výraz, ktorý je možné filtrovať podľa farby „červenej“:

 
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14

Výsledkom sú hodnoty poľa alebo TRUE FALSE, ktoré sú vynútené do jednotiek a núl dvojitý negatívny (-) prevádzka. Výsledkom je toto pole:



 
--(color='red')

Všimnite si, že pole obsahuje 10 hodnôt, jednu pre každý riadok. Jedna označuje riadok, kde je farba „červená“, a nula označuje riadok s akoukoľvek inou farbou.

Ďalej máme ďalšie dve polia: jedno pre množstvo a jedno pre cenu. Spolu s týmito výsledkami z prvého poľa máme:

 
{1010001000}

Pri rozširovaní polí máme:

 
= SUMPRODUCT ({1010001000},quantity,price)

Základným správaním produktu SUMPRODUCT je znásobenie a potom súčet polí. Pretože pracujeme s tromi poľami, môžeme si operáciu vizualizovať podľa nižšie uvedenej tabuľky, kde stĺpec s výsledkami je výsledkom násobenia pole 1 * pole2 * pole3 :

pole 1 pole2 pole3 výsledok
1 10 pätnásť 150
0 6 18 0
1 14 pätnásť 210
0 9 16 0
0 jedenásť 18 0
0 10 18 0
1 8 pätnásť 120
0 9 16 0
0 jedenásť 18 0
0 10 16 0

Oznámenie pole 1 funguje ako filter - nulové hodnoty tu vynulujú hodnoty v riadkoch, kde farba nie je „červená“. Vrátením výsledkov späť do SUMPRODUCT máme:

ako skopírovať funkciu v programe Excel
 
= SUMPRODUCT ({1010001000},{1061491110891110},{15181516181815161816})

Výsledkom je konečný výsledok 480.

Pridanie ďalších kritérií

Kritériá môžete rozšíriť pridaním ďalšieho logického výrazu. Na vyhľadanie napríklad celkového predaja, kde je farba „červená“ a štát „TX“, H6 obsahuje:

 
= SUMPRODUCT ({1500210000120000})

Poznámka: V SUMPRODUCT sa nerozlišujú malé a veľké písmená.

Zjednodušenie pomocou jedného poľa

Profesionáli v programe Excel často často trochu zjednodušia syntax v programe SUMPRODUCT vynásobením polí priamo vo vnútri pole 1 Páči sa ti to:

 
= SUMPRODUCT (--(state='tx'),--(color='red'),quantity,price)

Funguje to, pretože matematická operácia (násobenie) automaticky vynúti PRAVÉ a NEPRAVDIVÉ hodnoty z prvých dvoch výrazov do jednotiek a núl.

Autor Dave Bruns


^