V tomto článku sa naučíme SÚČET, AK pomocou vyhľadávacích hodnôt v programe Excel.
Jednoducho povedané, pri práci s rôznymi tabuľkami údajov niekedy potrebujeme vypočítať hodnoty v prvej tabuľke v inej tabuľke. Podmienkou je získať súčet hodnôt v prvej tabuľke pomocou hodnôt druhej tabuľky
V tomto článku budeme potrebovať nasledujúce funkcie:
- Funkcia SUMPRODUCT
- Funkcia SUMIF
Funkcia SUMPRODUCT je matematická funkcia v programe Excel. Funguje na viacerých rozsahoch. Znásobí zodpovedajúce polia a potom ich pridá.
Spolu = (A1 * B1 * C1 *… + A2 * B2 * C2 * … + …)
Syntax:
= SUMPRODUCT (pole1, [pole2], …)
pole: Je to rozsah alebo zoznam hodnôt.
Funkcia SUMIF v programe Excel vráti SÚČET čísiel, ktoré splnili uvedené kritériá. Rozsah SUM a rozsah kritérií môžu byť rovnaké alebo odlišné.
Všeobecný vzorec Excel SUMIF:
= SUMIF (rozsah_kritérií, rozsah, rozsah_súčtu)
rozsah_kritérií: rozsah, v ktorom sa uplatňuje podmienka.
kritériá: podmienka, ktorá sa má použiť na rozsah_kritérií.
rozsah_súčtov: rozsah súčtov na základe kritérií.
Teraz z týchto funkcií vytvoríme vzorec. Tu poskytneme údaje a potrebovali sme súčet výsledkov, kde sa hodnota zhoduje s hodnotou vo vyhľadávacej tabuľke.
Všeobecný vzorec:
= SUMPRODUCT (SUMIF (výsledok, záznamy, sum_nums))
výsledok : hodnoty, ktoré sa majú zhodovať vo vyhľadávacej tabuľke
zaznamenať : hodnoty v prvej tabuľke
sum_nums : hodnoty na súčet podľa zodpovedajúcich hodnôt.
Skúsme tento vzorec spustiť na príklade
Tu vypočítavame celkové body futbalového mužstva pomocou tabuľky bodov alebo vyhľadávacej tabuľky.
Tu je výsledok (B8: B10) daný ako pomenovaný rozsah a body (C8: C10) daný ako pomenovaný rozsah.
Teraz použijeme nasledujúci vzorec na získanie SUMU bodov za mesiac 1 v bunke F3
Použite vzorec:
= SUMPRODUCT (SUMIF (výsledok, B3: E3, body))
Vysvetlenie:
- Funkcia SUMIF porovná výsledok s hodnotami v B3: E3.
- Potom sa body zodpovedajúce výsledku vrátia do funkcie SUMPRODUCT.
- Funkcia SUMPRODUCT vracia funkcii SÚčet hodnôt zadaných ako pole.
Ako tu vidíte, celkový počet bodov, ktoré tím dosiahol v prvom mesiaci, je 7.
3 (Š) + 0 (D) + 1 (H) + 3 (Š) = 7 bodov
Skopírujte vzorec do iných buniek, vyberte bunky, ktoré preberajú prvú bunku, v ktorej je vzorec už použitý, použite klávesovú skratku Ctrl + D alebo v programe Excel použite možnosť bunky nadol.
Ako vidíte na obrázku vyššie, body dosiahnuté v 4 rôznych mesiacoch.
Získajte SÚČET všetkých bodov po dobu 4 mesiacov.
To znamená, že tím získal celkom 18 bodov za 4 mesiace.
Alternatívna metóda
Namiesto použitia funkcie SUMPRODUCT použite metódu SUMA a pole.
Použite vzorec:
{= SUM (SUMIF (výsledok, B3: E3, body))}
Curly braces je metóda poľa navrhujúca, aby funkcia vypočítala všetky vrátené hodnoty pomocou funkcie SUMIF.
Poznámka: Nie manuálne použite symboly zložených zátvoriek. Použi Ctrl + Shift + Enter použiť metódu poľa v programe Excel.
Ako vidíte z vyššie uvedeného vzorca, celkové body môžete získať pomocou tabuľky podmieneného vyhľadávania.
Poznámky:
- Vzorec funguje iba s číslami.
- Vzorec funguje iba vtedy, ak vo vyhľadávacej tabuľke nie sú žiadne duplikáty
- Funkcia SUMPRODUCT považuje nečíselné hodnoty za 0 s.
- Funkcia SUMPRODUCT považuje logickú hodnotu TRUE za 1 a False za 0.
- Pole argumentov musí mať rovnakú dĺžku, inak bude mať funkcia
Dúfam, že tento článok o tom, ako vrátiť súčet iba zo vzorcov v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o funkciách SUMPRODUCT. Podeľte sa o svoj dotaz nižšie v poli pre komentáre. Pomôžeme vám.
Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SUMU po vynásobení hodnôt vo viacerých poliach v programe Excel.
SUM, ak je dátum medzi : Vráti SÚčet hodnôt medzi danými dátumami alebo obdobím v programe Excel.
Suma, ak je dátum väčší ako daný dátum: Vráti SÚčet hodnôt po danom dátume alebo období v programe Excel.
2 spôsoby súčtu podľa mesiacov v programe Excel: Vráti SÚčet hodnôt v rámci daného konkrétneho mesiaca v programe Excel.
Ako sčítať viac stĺpcov s podmienkou: Vráti SÚčet hodnôt vo viacerých stĺpcoch s podmienkou v programe Excel
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu
Upravte rozbaľovací zoznam
Absolútna referencia v Exceli
Ak s podmieneným formátovaním
Ak so zástupnými znakmi
Vlookup podľa dátumu
V programe Excel 2016 preveďte palce na nohy a palce
Pripojte meno a priezvisko v Exceli
Spočítajte bunky, ktoré sa zhodujú s A alebo B
