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