Excel

Excel SUMPRODUCT Funkcia

Excel Sumproduct Function

Excel SUMPRODUCT funkciaZhrnutie

Funkcia SUMPRODUCT násobí rozsahy alebo polia dohromady a vracia súčet produktov. Znie to nudne, ale SUMPRODUCT je neuveriteľne všestranná funkcia, ktorú je možné použiť na počítanie a sumovanie ako COUNTIFS alebo SUMIFS, ale s väčšou flexibilitou. V rámci programu SUMPRODUCT je možné jednoducho použiť ďalšie funkcie na ďalšie rozšírenie funkcií.



Účel Násobenie, potom súčtové polia Návratová hodnota Výsledok násobených a súčtových polí Syntax = SUMPRODUCT (pole1, [pole2], ...) Argumenty
  • pole 1 - Prvé pole alebo rozsah, ktorý sa má vynásobiť, a potom pridajte.
  • pole2 - [voliteľné] Druhé pole alebo rozsah na vynásobenie a pridanie.
Verzia Excel 2003 Poznámky k použitiu

Funkcia SUMPRODUCT funguje s poľami, ale na zadanie nevyžaduje normálnu syntax poľa (Ctrl + Shift + Enter). Účelom funkcie SUMPRODUCT je vynásobiť, potom sčítať, polia. Ak je dodané iba jedno pole, SUMPRODUCT jednoducho sčíta položky v poli. Je možné dodať až 30 polí.

Keď sa prvýkrát stretnete s SUMPRODUCT, môže sa vám to zdať nudné, zložité a dokonca zbytočné. Ale SUMPRODUCT je úžasne všestranná funkcia s mnohými spôsobmi použitia. Pretože bude pracovať s poľami elegantne a bez sťažností, môžete ho použiť na spracovanie rozsahov buniek šikovnými a elegantnými spôsobmi (pozrite si odkazy na príklady vzorcov na tejto stránke).





Na ilustráciu toho, ako funguje SUMPRODUCT, uvádzame niekoľko bežných príkladov.

SUMPRODUCT pre podmienené sumy a počty

Predpokladajme, že máte nejaké údaje o objednávke v A2: B6, so stavom v stĺpci A, tržbami v stĺpci B:



TO B
1 Štát Predaj
2 VON 75
3 ČO 100
4 TX 125
5 ČO 125
6 TX 150

Pomocou SUMPRODUCT môžete počítať celkové tržby za Texas („TX“) podľa tohto vzorca:

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

A môžeš súčet celkové tržby za Texas („TX“) podľa tohto vzorca:

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

Poznámka: Nenechajte sa zmiasť dvojitým negatívom. Toto je bežný trik používaný v pokročilejších vzorcoch Excelu na vynútenie hodnôt TRUE a FALSE do 1 a 0. Viac pozri nižšie ...

obrátiť os x a y v exceli

V prípade súčtu uvedeného vyššie je tu virtuálna reprezentácia dvoch polí, ktoré ako prvé spracoval SUMPRODUCT:

pole 1 pole2
NEPRAVDA 75
NEPRAVDA 100
PRAVDA 125
NEPRAVDA 125
PRAVDA 150

Každé pole má 5 položiek. Prvé pole obsahuje hodnoty TRUE / FALSE, ktoré vyplývajú z výrazu A2: A6 = 'TX', a druhé pole obsahuje obsah B2: B6. Každá položka v prvom poli bude vynásobená zodpovedajúcou položkou v druhom poli. V súčasnom stave však bude výsledok SUMPRODUCT nulový, pretože s hodnotami TRUE a FALSE sa bude zaobchádzať ako s nulou. Potrebujeme, aby položky v poli1 boli číselné - musia byť „vynútené“ do jednotiek a núl. Tu nastupuje dvojitý negatív.

Použitím dvojitého záporu - (dvojitý unár, pre vás technické typy) sme schopní vynútiť PRAVDU/NEPRAVDU na číselné hodnoty jedna a nula, ako je uvedené vo virtuálnom znázornení nižšie. Posledný stĺpec „Produkt“ predstavuje výsledok vynásobenia dvoch polí dohromady. Sčítaný výsledok, 275, je hodnotou, ktorú SUMPRODUCT vracia.

pole 1 pole2 Výrobok
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Sum 275

Použitím syntaxe zložených zátvoriek pre polia vyzerá príklad po nátlaku takto:

ako napisat ak potom vzorce v exceli
 
= SUMPRODUCT ({0,0,1,0,1},{75,100,125,125,150}) 

a takto po násobení:

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

Tento príklad rozširuje vyššie uvedené nápady podrobnejšie.

SUMPRODUCT s ďalšími funkciami

SUMPRODUCT môže priamo využívať ďalšie funkcie. Môžete vidieť, že SUMPRODUCT sa používa s funkciou LEN na počítanie celkových znakov v rozsahu alebo s funkciami ako ISBLANK, ISTEXT atď. Nie sú to normálne funkcie poľa, ale keď im je daný rozsah, vytvoria „pole výsledkov“. Pretože je SUMPRODUCT navrhnutý tak, aby pracoval s poľami, je schopný vykonávať výpočty na poliach priamo. To môže byť dobrý spôsob, ako ušetriť miesto v pracovnom hárku, pretože eliminujete potrebu stĺpca „pomocník“.

Predpokladajme napríklad, že máte 10 rôznych textových hodnôt v A1: A10 a chcete spočítať celkový počet znakov pre všetkých 10 hodnôt. Do stĺpca B môžete pridať pomocný stĺpec, ktorý používa tento vzorec: LEN (A1) na výpočet znakov v každej bunke. Potom môžete pomocou SUMY spočítať všetkých 10 čísel. Pomocou SUMPRODUCT však môžete napísať vzorec takto:

 
= SUMPRODUCT ( LEN (A1:A10))

Keď sa použije s rozsahom ako A1: A10, LEN vráti pole s 10 hodnotami. Potom SUMPRODUCT jednoducho spočíta všetky hodnoty a vráti výsledok bez toho, aby bol potrebný pomocný stĺpec.

Pozrite sa na príklady nižšie na mnoho ďalších spôsobov, ako používať SUMPRODUCT.

Poznámky:

  1. SUMPRODUCT považuje nečíselné položky v poliach za nuly.
  2. Argumenty poľa musia mať rovnakú veľkosť. V opačnom prípade SUMPRODUCT vygeneruje #HODNOTU! chybová hodnota.
  3. Logické testy vo vnútri polí vytvoria PRAVDIVÉ a NEPRAVDIVÉ hodnoty. Vo väčšine prípadov ich budete chcieť vynútiť 1 a 0.
  4. SUMPRODUCT môže často priamo použiť výsledok iných funkcií (pozri príklady vzorcov nižšie)


^