SUMIF S VYHĽADÁVANÍM Kombinujte SUMIF s funkciou VLOOKUP Excel

Kombinované použitie sumif (vlookup)

Sumif with VLOOKUP is a combination of two different conditional functions, SUMIF is used to sum the cells based on some condition which takes arguments of range which has the data and then the criteria or the condition and cells to sum, instead of the criteria we use VLOOKUP ako kritérium, keď je k dispozícii veľké množstvo údajov vo viacerých stĺpcoch.

SUMIF je funkcia prezentovaná v programe Excel od verzie 2007, ktorá umožňuje sčítať rôzne hodnoty zodpovedajúce kritériám. VLOOKUP je jedným z najlepších vzorcov na zhromažďovanie údajov z iných tabuliek. Ak existuje viac podmienok a stĺpcov, na vykonanie viacerých výpočtov na hárku programu Excel sa použije sumif (vlookup). Nevýhoda funkcie SUMIF, ktorá vracia jediné číslo, je prekonaná použitím VLOOKUP. VLOOKUP pomáha vrátiť akýkoľvek druh údajov z tabuľky na základe zhodných kritérií.

Vysvetlenie

Funkcia SUMIF: Je to trigonometrická a matematická funkcia, ktorá slúži na sčítanie hodnôt, keď je stanovená podmienka pravdivá. Celková hodnota sa získa iba na základe jedného kritéria.

Keď sa zaoberáme funkciou SUMIF v programe Excel, použije sa nasledujúci vzorec

  • Rozsah: Je to rozsah buniek použitých na vyhodnotenie stanovených kritérií
  • Kritérium: Podmienkou je súčet hodnôt. Môže to byť odkaz na bunku, číslo a iná funkcia programu Excel. Keď chceme kombinovať SUMIF a VLOOKUP, namiesto kritérií sa zadá funkcia vlookup
  • Rozsah súčtov: Je to rozsah buniek určených na súčet číselných hodnôt.

Teraz je vzorec upravený na

Vzorec = SUMIF (Rozsah, Vlookup (vyhľadávaná hodnota, pole tabuľky, indexové číslo stĺpca, [hľadanie rozsahu]), [rozsah súčtu])

  • Vyhľadávacia hodnota: Určuje hodnotu, ktorá sa má vyhľadať v tabuľke. Môže to byť referencia alebo hodnota.
  • Pole tabuľky: Je to rozsah tabuľky obsahujúcej dva alebo viac ako dva stĺpce.
  • Číslo indexu stĺpca: Je to relatívny index stĺpca, ktorý sa má určiť na vrátenie požadovaných údajov z konkrétneho stĺpca.
  • [Vyhľadanie rozsahu]: Je 0 alebo 1, aby ste určili, či sa má vrátiť presná hodnota alebo či sa má vrátiť približná hodnota. Pre používateľa je však voliteľná. 0 označuje presnú zhodu a 1 označuje približnú zhodu.

Ako používať SUMIF s funkciou VLOOKUP?

Kombinované použitie sumif (vlookup) je užitočné pri vyhľadávaní údajov na základe jednotlivých kritérií. V programe Excel sa používajú na vykonávanie výpočtov vyhľadávaním údajov mnohými spôsobmi. Tieto sa kolektívne používajú v obchodnom prostredí na vykonávanie rôznych úloh na prijímanie dobrých rozhodnutí. Ak chcete tieto funkcie efektívne využívať spoločne,

Najskôr je potrebné zadať funkciu SUMIF pomocou týchto dvoch metód.

Prvá metóda: Formula musí písať z klávesnice, ako je znázornené na obrázku nižšie.

Druhá metóda: Funkciu SUMIF je možné vložiť z karty „Formula“, ako je to znázornené na obrázku.

Po zadaní funkcie SUMIF sa do funkcie SUMIF zadá vzorec VLOOKUP nahradením prvku „Criteria“. Všetky parametre VLOOKUP vrátane vyhľadávacej hodnoty, poľa tabuľky, indexového čísla stĺpca a vyhľadávania rozsahu. Mali by byť uvedené v zátvorkách, aby sa zabránilo chybám vo vzorci. Rozsah hodnôt, ktoré sa majú sčítať, sú zahrnuté v prvkoch rozsahu súčtov funkcie SUMIF. Nakoniec sú stlačené klávesy CTRL, SHIFT a ENTER, aby sa hodnoty uľahčili ako pole.

Príklady

Tu si môžete stiahnuť tento SUMIF so šablónou VLOOKUP Excel - SUMIF so šablónou VLOOKUP Excel

Príklad č. 1 - Použitie sumif (vlookup) na určenie určitej hodnoty

Tento príklad ukazuje, ako použiť sumif (vlookup) na nájdenie súčtu tržieb v rovnakom mesiaci v rôznych rokoch. Nasledujúce údaje sa v tomto príklade považujú za údaje zobrazené na snímke obrazovky.

Uvažovaná vyhľadávacia tabuľka je uvedená nižšie. Zahŕňala referenčné hodnoty pre mesiace od januára do decembra.

Po zadaní údajov do hlavnej tabuľky a vyhľadávacej tabuľky sa pomocou funkcie SUMIF zistí celkový predaj generovaný v rôznych mesiacoch roka. Tu sa uvažuje s hodnotou vyhľadávania mesiac. Vzorec kombinujúci sumif (vlookup) je zobrazený ako,

Celkové tržby v januári sú stanovené ako 17263,3 . Keď sme zmenili vzhľadovú hodnotu na iný mesiac, vygenerujú sa príslušné celkové tržby.

Príklad č. 2 - Stanovenie súčtu na základe kritérií zhody v rôznych pracovných hárkoch

V tomto príklade sa vyhľadávacia tabuľka a hlavná tabuľka prijímajú v rôznych listoch a nie v jednom hárku. Údaje vyhľadávacej tabuľky sú zobrazené na obrázku nižšie.

Údaje hlavnej tabuľky sú zobrazené na nasledujúcej snímke obrazovky.

Na určenie celkového predaja sa ako vyhľadávacia hodnota použije meno predajcu a na referenčné účely sa použijú ID zamestnancov. Vzorec sa zadáva tak, ako je uvedené nižšie, a pri porovnaní s prvým príkladom sa vo vzorci pozoruje niekoľko zmien.

V tomto príklade sa namiesto výberu vyhľadávacieho poľa uvádza iba Lookup_table. Stlačením troch kláves vrátane CTRL, SHIFT a ENTER získate presné výsledky.

Keď sa meno predajcu zmení na mená uvedené vo vyhľadávaní, súčet tržieb sa zmení a prinesie nový výsledok.

Výhody

Nasledujúce výhody použitia týchto funkcií sú uvedené nižšie.

  • Hodnoty môžeme ľahko extrahovať z inej tabuľky a vykonať výpočty.
  • Stanovenie súčtu hodnôt uvedených v rozsahu, ktorý spĺňa kritériá uvedené v rôznych aspektoch podnikania.

Na čo treba pamätať

  • Pri použití funkcie VLOOKUP by indexové číslo stĺpca nemalo byť nižšie ako 1, aby sa zabránilo chybám.
  • Indexy by sa mali dávať do stĺpcov vyhľadávacej tabuľky označovaním číslami 1, 2, 3 atď.
  • Namiesto zadania klávesu Key by sa mali použiť kombinácie klávesov CTRL + SHIFT + ENTER v programe Excel, pretože VLOOKUP je zadaný ako maticový vzorec.
  • Na extrahovanie hodnôt a určenie súčtu hodnôt poľa je potrebné definovať dve tabuľky vrátane hlavnej a vyhľadávacej.
  • SUMIF poskytuje presné výsledky iba pre číselné údaje, pre iný druh údajov nebude fungovať.