V tomto článku sa naučíme Ako používať funkciu IF namiesto funkcie SUMPRODUCT a SUMIFS v programe Excel.
Scenár:
Jednoducho povedané, pri práci s dlhým rozptýleným súborom údajov niekedy potrebujeme nájsť súčet čísel s určitými kritériami. Napríklad nájdenie súčtu platov na konkrétnom oddelení alebo viacnásobné kritérium dátumu, názvu, oddelenia alebo dokonca číslovania údajov, ako sú platy pod hodnotou alebo množstvo nad hodnotou. Na to zvyčajne používate funkciu SUMPRODUCT alebo SUMIFS. Ale neverili by ste, že rovnakú funkciu vykonávate aj so základnou funkciou Excelu, ak je funkcia IF.
Ako problém vyriešiť?
Musíte premýšľať, ako je to možné, vykonávať logické operácie nad tabuľkovými poliami pomocou funkcie IF. Ak je funkcia v programe Excel veľmi užitočná, prevedie vás náročnými úlohami v programe Excel alebo v iných kódovacích jazykoch. IF funkcia testuje podmienky na poli zodpovedajúcom požadovaným hodnotám a vráti výsledok ako pole zodpovedajúce skutočným podmienkam ako 1 a nepravdivým ako 0.
Na tento problém použijeme nasledujúce funkcie:
- Funkcia SUM
- IF funkcia
Budeme požadovať tieto vyššie uvedené funkcie a nejaký základný zmysel pre prácu s dátami. logické podmienky na polia je možné použiť pomocou logických operátorov. Tieto logické operátory pracujú s textom aj s číslami. Nasleduje generický vzorec. { } curly braces je magický nástroj na vykonávanie vzorcov poľa s funkciou IF.
Všeobecný vzorec:
| { = SUM (AK ((logický_1) * (logický_2) * … * (logický_n), súčet_poľ))) } |
Poznámka: Pre zložené rovnátka ( { } ) Použiť Ctrl + Shift + Enter pri práci s poľami alebo rozsahmi v programe Excel. Štandardne sa vo vzorci vygenerujú kučeravé zátvorky. NEPOKÚŠAJTE sa pevne kódovať znaky zložených zátvoriek.
Logický 1: testuje podmienku 1 na poli 1
Logický 2: testuje podmienku 2 na poli 2 a podobne
sum_array: pole, vykoná sa súčet operácie
Príklad:
Toto všetko môže byť mätúce na pochopenie. Otestujme teda tento vzorec spustením na príklade uvedenom nižšie. Tu máme údaje o dodaných produktoch do rôznych miest spolu s príslušnými poliami kategórií a množstvami. Tu máme údaje a musíme nájsť množstvo cookies odoslaných do Bostonu, kde je množstvo väčšie ako 40.
Tabuľka údajov a tabuľka kritérií sú zobrazené na obrázku vyššie. Na pochopenie účelu sme pre pomenované polia použili pomenované rozsahy. Menované rozsahy sú uvedené nižšie.
Tu :
Mesto definované pre pole A2: A17.
Kategória definovaná pre pole B2: A17.
Množstvo definované pre pole C2: C17.
Teraz ste pripravení dosiahnuť požadovaný výsledok pomocou nižšie uvedeného vzorca.
Použite vzorec:
| { = SUM (IF ((Mesto = "Boston") * (Kategória = "Cookies") * (Množstvo> 40), Množstvo)) } |
Vysvetlenie:
- City = "Boston": skontroluje hodnoty v mestskom rozsahu, aby sa zhodovali s "Boston".
- Kategória = "Cookies": skontroluje, či sa hodnoty v kategórii Kategória zhodujú s "Cookies".
- Množstvo> 40: skontroluje hodnoty v rozsahu množstva na ma
- Množstvo je pole, kde sa požaduje súčet.
- Funkcia IF kontroluje všetky kritériá a hviezdička char (*) znásobuje všetky výsledky poľa.
= SUM (IF ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
- Teraz funkcia IF vracia iba množstvá zodpovedajúce 1 s a zvyšok sa ignoruje.
- Funkcia SUM vráti SUM.
Teraz sa množstvo zodpovedajúce 1 iba sčíta, aby sa dosiahol výsledok.
Ako vidíte, vráti sa množstvo 43, ale do „Bostonu“ boli doručené tri objednávky súborov cookie s množstvom 38, 36 a 43. Potrebovali sme súčet množstva, kde je množstvo nad 40. Vzorec teda vráti iba 43. Teraz pomocou iných kritérií získajte SUM kvantum pre mesto: „Los Angeles“ a kategóriu: „Bary“ a množstvo menšie ako 50.
Použite vzorec
| { = SUM (IF ((Mesto = "Los Angeles") * (Kategória = "Bary") * (Množstvo <50), Množstvo)) } |
Ako vidíte, vzorec ako výsledok vráti hodnoty 86. To je súčet dvoch objednávok spĺňajúcich podmienky s množstvom 44 a 42. Tento článok ukazuje, ako nahradiť vnorený vzorec IF jediným IF vo vzorci poľa. Toto je možné použiť na zníženie zložitosti zložitých vzorcov. Tento konkrétny problém sa však dá ľahko vyriešiť pomocou funkcie SUMIFS alebo SUMPRODUCT.
Použitie funkcie SUMPRODUCT:
Funkcia SUMPRODUCT vracia súčet zodpovedajúcich hodnôt v poli. Získame teda polia, ktoré vrátia hodnoty 1 s a True a hodnoty 0 s hodnoty False. Posledná suma teda bude zodpovedať tam, kde všetky tvrdenia sú pravdivé.
Použite vzorec:
| = SUMPRODUCT ( - (Mesto = "Boston"), - (Kategória = "Cookies"), - (Množstvo> 40), Množstvo) |
-: operácia používaná na konverziu všetkých TRUE na 1 s a False na 0.
Funkcia SUMPRODUCT znova skontroluje SUM množstva vráteného funkciou SUMA a IF vysvetlenou vyššie.
Podobne pre druhý príklad je výsledok rovnaký.
Ako vidíte, funkcia SUMPRODUCT môže vykonávať rovnakú úlohu.
Tu sú všetky pozorovacie poznámky týkajúce sa použitia vzorca.
Poznámky:
- Pole sum_array vo vzorci funguje iba s číslami.
- Ak vzorec vráti chybu #HODNOTA, vo vzorci musí byť prítomná kontrola zložených zátvoriek, ako je uvedené v príkladoch v článku.
- Negatívny znak (-) zmení hodnoty, TRUE alebo 1 s na FALSE alebo 0 s a FALSE alebo 0 s na TRUE alebo 1 s.
- Operácie ako sa rovná ( = ), menej ako sa rovná ( <= ), väčší než ( > ) alebo sa nerovná () je možné vykonať v rámci použitého vzorca, iba s číslami.
Dúfam, že tento článok o tom, ako používať funkciu IF namiesto funkcie SUMPRODUCT a SUMIFS v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o súhrnných vzorcoch. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi prvými na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku
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 : Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať tFunkcia VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.