A sumif (vlookup) kombinált használata
A Sumif with VLOOKUP 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 tartományi argumentumokat vesz fel, amelyek tartalmazzák az adatokat, majd a feltételeket vagy a feltételeket és a cellákat összegezzük, az általunk használt kritériumok helyett 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ábla 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: Ez a táblázat két vagy több oszlopot tartalmazó tartománya.
- 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 keresésében az egyetlen kritérium alapján. 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
A SUMIF With VLOOKUP Excel sablonnal itt töltheti le - SUMIF A VLOOKUP Excel sablonnal1. 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 kapunk.
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 meg kell adni a keresőtábla oszlopainak, az 1, 2, 3 és így tovább jelezve.
- 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.