SUMIF With VLOOKUP - Kombinálja a SUMIF-et a VLOOKUP Excel funkcióval

A sumif (vlookup) kombinált használata

A Sumif a VLOOKUP segítségével két különböző feltételes függvény kombinációja, a SUMIF-et a cellák összegzésére használják valamilyen feltétel alapján, amely az általunk használt kritériumok helyett olyan tartományi argumentumokat vesz fel, amelyek tartalmazzák az adatokat, majd a feltételeket és a cellákat összegzik VLOOKUP mint kritérium, ha nagy mennyiségű adat áll rendelkezésre több oszlopban.

A SUMIF az Excelben a 2007-es verziótól kezdve bemutatott függvény, amely összegzi a különböző értékeket, amelyek megfelelnek a feltételeknek. A VLOOKUP az egyik legjobb képlet az adatok összegyűjtéséhez más táblákból. Ha több feltétel és oszlop van, a sumif (vlookup) parancsot használja a többszörös számítások elvégzésére az Excel lapon. Az egyetlen számot visszaadó SUMIF függvény hátrányát a VLOOKUP használatával lehet leküzdeni. A VLOOKUP segít bármilyen adatot visszaküldeni egy táblázatból az egyező kritériumok alapján.

Magyarázat

SUMIF függvény: Trigonometria és matematika függvény az értékek összegzése, ha a megállapított feltétel igaz. Az összegértéket csak egy kritérium alapján kapjuk meg.

Amikor a SUMIF függvénnyel foglalkozunk az excelben, a következő képletet használjuk

  • Tartomány: A megállapított kritériumok értékeléséhez használt cellák tartománya
  • Kritériumok: Az értékek összegzésének feltétele. Ez lehet egy cellahivatkozás, szám és egy másik excel függvény. Amikor össze akarjuk kapcsolni a SUMIF és a VLOOKUP elemeket, akkor a vlookup függvényt beírjuk a kritériumok helyére
  • Összegtartomány: A megadott cellatartomány összegzi a numerikus értékeket.

Most a képlet módosítva

Képlet = SUMIF (Tartomány, Vlookup (keresési érték, táblázat tömb, oszlopindex száma, (tartománykeresés)), (összegtartomány))

  • Keresési érték: Megadja a táblázatban keresendő értéket. Lehet referencia vagy érték.
  • Táblázat tömb: A táblázat két vagy több oszlopot tartalmaz.
  • Oszlopindex száma: Az oszlop relatív indexe adja meg a szükséges adatokat egy adott oszlopból.
  • (Range lookup): Vagy 0, vagy 1 adja meg, hogy a pontos értéket adja-e vissza, vagy a hozzávetőleges értéket adja vissza. De opcionális a felhasználó számára. 0 pontos egyezést, 1 pedig hozzávetőleges egyezést jelöl.

Hogyan használható a SUMIF a VLOOKUP funkcióval?

A sumif (vlookup) együttes használata hasznos az adatok egyetlen kritérium alapján történő keresésében. Ezeket az Excel sokféleképpen használja a számítások elvégzésére adatok keresésével. Ezeket együttesen használják az üzleti környezetben különböző feladatok elvégzésére a jó döntések meghozatalához. E funkciók hatékony együttes használatához

Először a SUMIF függvényt kell megadni a két módszer alkalmazásával.

Első módszer: A képletet a billentyűzetről kell beírnia, az alábbi képen látható módon.

Második módszer: A SUMIF függvény beilleszthető a „Képlet” fülről, ahogy a képen látható.

A SUMIF függvény megadása után a VLOOKUP képlet beírásra kerül a SUMIF függvény belsejébe a 'Criteria' elem helyettesítésével. A VLOOKUP összes paramétere, beleértve a keresési értéket, a táblázat tömböt, az oszlop indexszámát és a tartomány keresését. Ezeket zárójelben kell feltüntetni, hogy elkerüljük a képlettel kapcsolatos hibákat. Az összegezendő értéktartományt a SUMIF függvény összegtartomány-elemei tartalmazzák. Végül a CTRL, a SHIFT és az ENTER billentyűk lenyomták az értékeket tömbként.

Példák

1. példa - A sumif (vlookup) együttes használata bizonyos érték meghatározásához

Ez a példa bemutatja, hogyan lehet együtt felhasználni az összeget (vlookup) az ugyanazon hónapban elért eladások összegének megtalálásához a különböző években. A következő adatokat vesszük figyelembe ebben a példában, ahogy a képernyőképen látható.

A figyelembe vett keresési táblázat az alábbiak szerint jelenik meg. Tartalmazta a januártól decemberig tartó hónapok referenciaértékeit.

Az adatok beírása után a fő táblába és a keresőtáblába a SUMIF függvény segítségével meghatározható az év különböző hónapjaiban elért összes eladás. Itt a figyelembe vett keresési érték a hónap. Az összegzőt (vlookup) ötvöző képlet a következő,

A januári hónap teljes eladását 17263,3-ban határozták meg . Amikor megváltoztattuk a megjelenési értéket egy másik hónapra, akkor a megfelelő teljes értékesítés keletkezik.

2. példa - Összeg meghatározása a különböző munkalapok egyezési kritériumai alapján

Ebben a példában a keresési táblázatot és a főtáblázatot különböző lapokban, nem pedig egyetlen lapként vesszük fel. A keresési táblázat adatai az alábbi képernyőképen láthatók.

A fő táblázat adatait az alábbi képernyőkép mutatja.

A teljes eladás meghatározásához az eladó nevét kell keresési értéknek tekinteni, és a munkavállalói azonosítókat használjuk referenciaként. A képletet az alábbiakban bemutatott módon adjuk meg, és az első példához képest kevés változás figyelhető meg a képleten.

Ebben a példában a keresési tömb kiválasztása helyett csak a Keresési_tábla szerepel. A három billentyű lenyomásával, beleértve a CTRL, SHIFT és ENTER pontos eredményeket kap.

Ha az eladó nevét a keresésben említett névre változtatja, az eladások összege változik, és új eredményt hoz.

Előnyök

A következők a funkciók használatának előnyei.

  • Könnyen kivonhatjuk az értékeket egy másik táblázatból a számítások elvégzéséhez.
  • A vállalkozás különböző aspektusaiban említett kritériumoknak megfelelő tartományban bemutatott értékek összegének meghatározása.

Dolgok, amikre emlékezni kell

  • A VLOOKUP funkció használata közben az oszlopindex száma nem lehet alacsonyabb 1-nél a hibák elkerülése érdekében.
  • Az indexeket a keresőtábla oszlopaihoz kell adni, az 1., 2., 3. számokkal stb.
  • Az CTRL + SHIFT + ENTER billentyűkombinációt az excelben kell használni a Key megadása helyett, mivel a VLOOKUP tömbképletként van megadva.
  • Az értékek kibontásához és a tömbértékek összegének meghatározásához két tábla definiálása szükséges, beleértve a Main és a Lookup elemeket.
  • A SUMIF csak a numerikus adatokról nyújt pontos eredményeket, más típusú adatoknál nem fog működni.

érdekes cikkek...