Több érték keresése az Excelben: lépésről lépésre

Ha nagy adathalmazokkal dolgozik a Microsoft Excel programban, kihívást jelenthet több olyan értéket megtalálni, amelyek több feltételnek is megfelelnek. Egyes beépített funkciókat, például a VLOOKUP-t eredetileg úgy tervezték, hogy egyetlen értékkel működjenek.

Többet kombinálhatunk több érték kereséséhez. Ide tartoznak a VLOOKUP, INDEX, MATCH és IF függvények. Az Excel jelenlegi verziói dinamikus tömböket, míg a régebbi verziók tömbképleteket használhatnak.

Ez a cikk pontosan bemutatja, hogyan használhatja ezeket a függvényeket olyan képletekben, amelyek több értéket találnak az adatokban.

Gyerünk!

Tartalomjegyzék

• A VLOOKUP használata több értékkel
  • Mik azok a tömbképletek?
  • VLOOKUP Példa
  • Az Excel legújabb verziói
  • Régebbi verziók
  • Pontos egyezés vs. hozzávetőleges egyezés
  • További információ a VLOOKUP-ról
• Az INDEX és a MATCH használata több érték kereséséhez
  • Az INDEX és a MATCH együttes használata az Excel 365-ben
  • Munka az Excel régebbi verzióival
• Az IF függvény használata több érték keresésére
  • Az Excel jelenlegi verziói
  • Az Excel régebbi verziói
• A SUMPRODUCT használata több kritériumhoz
• 3 gyakori hiba a keresési funkcióknál
  • 1. #N/A hibák
  • 2. #REF! Hibák
  • 3. Körkörös hibák
• Az IFERROR használata keresési függvényekkel
• 7 Speciális keresési technikák
  • 1. Relatív pozíció keresése
  • 2. KIS funkció
  • 3. SOR funkció
  • 4. SZŰRŐ funkció
  • 5. Segítő oszlopok
  • 6. Dinamikus tömbök
  • 7. Power Query
• Végső gondolatok

A VLOOKUP használata több értékkel

A függvényt gyakran használják egyedi értékek keresésére egy adattartományban. Ezzel a keresési képlettel azonban több egyezést is megkereshet az Excelben.

Alapértelmezés szerint csak az első talált egyező értéket adja vissza. A függvényt azonban módosíthatja úgy, hogy több értéket adjon vissza egy tömbképlet segítségével.

Mik azok a tömbképletek?

A tömbképlet olyan képlet, amely számításokat végezhet adattömbökön. Tömbképletnek hívják, mert egyetlen érték helyett eredmények tömbjét is visszaadhatja.

A tömbképlet létrehozásának számos egyszerű lépése van:

1. Válasszon ki egy cellatartományt a kereséshez.

2. Írja be a képletet a képletsávba.

3. Nyomja meg a Ctrl + Shift + Enter billentyűket a befejezéshez.

A tömbképlet szintaxisa hasonló a normál képletéhez, de a képlet körül kapcsos zárójeleket {} tartalmaz. A kapcsos zárójelek azt jelzik, hogy a képlet egy tömbképlet, és értékek tömbjét adja vissza.

A cikkben található példák bemutatják, hogyan kell helyesen használni a tömbképleteket.

VLOOKUP Példa

Példánkban egy munkalap A oszlopában öt elem található:

• alma

• Banán

• Kenyér

• Sárgarépa

• Cseresznye

A feladat annak ellenőrzése, hogy három konkrét gyümölcs szerepel-e ebben a listában: alma, banán és cseresznye.

Az Excel legújabb verziói

A VLOOKUP szintaxisa attól függ, hogy az Excel melyik verzióját használja.

A legújabb verziók ( Excel 365 vagy Excel 2021 ) támogatják a dinamikus tömböket. Ez a funkció lehetővé teszi a képletek számára, hogy több olyan eredményt adjanak vissza, amelyek a szomszédos cellákba „szóródnak”.

Ez a szintaxis (az egyenlőségjel kezdi a képletet):

=VLOOKUP(keresési_érték, táblázat_tömb, oszlop_index_száma, [tartomány_keresése])

• lookup_value: A keresni kívánt érték.

• table_array: A keresni kívánt teljes adattábla.

• col_index_num: A tábla oszlopának száma a táblatömbben, amely tartalmazza a visszaadni kívánt adatokat.

• range_lookup: Nem kötelező. Meghatározza, hogy pontos vagy közelítő egyezést szeretne-e.

Konkrét példánk a következő képletet használja:

=KERESÉS({"alma","banán","cseresznye"},A1:A5,1,HAMIS)

Amikor a képletet beírja a B1 cellába, az eredmények a C1 és D1 cellákba kerülnek. Ez a kép a példát mutatja működés közben:

Régebbi verziók

Ha az Excel egy régebbi verzióját használja, amely nem támogatja a dinamikus tömböket (pl. Excel 2019 vagy korábbi), kissé eltérő megközelítést kell alkalmaznia a tömbképletekkel.

Kovesd ezeket a lepeseket:

1. Kattintson arra a cellára, ahol az első elem eredményét szeretné megjeleníteni (pl. B oszlop).

2. Írja be a következő képletet az Enter billentyű lenyomása nélkül: =VLOOKUP({“alma”,”banán”,”cseresznye”}, A1:A5, 1, FALSE)

3. Nyomja meg a Ctrl + Shift + Enter billentyűket, hogy ezt tömbképletté alakítsa.

4. Másolja ki a B1 cellát, és illessze be az alábbi cellába (vagy használja a kitöltő fogantyúkat).

Ha a Ctrl + Shift + Enter billentyűkombinációt használja, az Excel kapcsos zárójeleket ad a képlet köré. Ez azt jelzi, hogy ez egy tömbképlet.

Pontos egyezés vs. hozzávetőleges egyezés

Alapértelmezés szerint a VLOOKUP függvény hozzávetőleges egyezést használ. Ez azt jelenti, hogy a talált legközelebbi egyezést adja vissza, még akkor is, ha a cellaértékek nem egyeznek pontosan.

Ha pontos egyezést szeretne végrehajtani, a range_lookup argumentumot FALSE értékre állíthatja.

Ne feledje, hogy a hozzávetőleges egyezések rendezett számértékekkel működnek a legjobban. Általában nem megfelelő, ha a cella értéke szöveg.

További információ a VLOOKUP-ról

Ha többet szeretne megtudni erről a sokoldalú funkcióról, tekintse meg ezeket a cikkeket:

Most, hogy beállította a VLOOKUP funkciót, vessünk egy pillantást két másik funkcióra, amelyek más módon tudják végrehajtani, amit csinál: INDEX és MATCH.

Az INDEX és a MATCH használata több érték kereséséhez

Az INDEX és a MATCH függvények kombinálásával több sorban több értéket találhat.

Az Excel INDEX függvénye értéket vagy hivatkozást ad vissza egy megadott tartományon belüli cellára.

=INDEX(tömb, sor_száma, [oszlop_száma])

• tömb: Az érték keresendő cellatartománya.

• sor_száma: Az a sorszám a tömbön belül, amelyből értéket kell visszaadni.

• oszlop_száma: (Nem kötelező) Az oszlop száma a tömbön belül, amelyből értéket kell visszaadni. Ha kihagyja, a függvény a teljes sort adja vissza.

Az Excel MATCH függvénye egy adott tartományon belüli érték pozícióját adja vissza.

=MATCH(keresési_érték, keresési_tömb, [egyezési_típus])

• lookup_value: A keresési_tömbben keresendő érték.

• lookup_array: A keresési_érték keresendő cellatartománya.

• match_type: (Opcionális) A végrehajtandó egyezés típusa. Ha kihagyja, a függvény pontos egyezést hajt végre.

Az INDEX és a MATCH együttes használata az Excel 365-ben

Az INDEX és a MATCH együttes használatához több érték kereséséhez az Excelben, tömbképletet kell használnia.

A korábbi mintaadatokkal együttműködve ez a képlet az Excel 365-ben:

=INDEX(A1:A5, MATCH({"alma","banán","cseresznye"}, A1:A5, 0))

A fenti példa a következőképpen bomlik:

• INDEX: ez egy megadott tartományban lévő cella értékét adja vissza egy adott sor- és oszlopszám alapján. Ebben az esetben az A1:A5 tartomány értéket adja vissza.

• A1:A5: Ez az a meghatározott táblatartomány, ahol az értéket keresi, és ahonnan az eredményt a rendszer visszaadja.

• MATCH: ez egy megadott elemet keres egy cellatartományban, és visszaadja az elem relatív pozícióját a tartományban.

• {“alma”,”banán”,”cseresznye”}: ez a tömbállandó, amely tartalmazza a kikeresni kívánt értékeket.

• A1:A5: ez az a tartomány, ahol a MATCH megkeresi a tömbállandó értékeit.

• 0: ez a MATCH függvény egyezési típusa. Ebben az esetben ez 0, ami azt jelenti, hogy pontos egyezést keres a szoros egyezés helyett.

 A képen a képlet látható működés közben:

Munka az Excel régebbi verzióival

Ha olyan régebbi Excel-fájlt használ, amely nem támogatja a dinamikus tömböket (pl. Excel 2019 vagy korábbi), akkor más megközelítést kell alkalmaznia.

Mivel a régebbi verziók nem támogatják, hogy a képletek a szomszédos cellákba „szóródjanak”, a használatot három különálló képletre kell felosztania.

Kovesd ezeket a lepeseket:

1. Kattintson arra a cellára, ahol az első elem eredményét szeretné elérni (pl. B1 cella)

2. Írja be az alábbi képletet:

3. =INDEX(A1:A5, MATCH(“alma”, A1:A5, 0))

4. Nyomja meg az Enter billentyűt a képlet végrehajtásához.

5. Írja be ezt a képletet a B2 cellába: =INDEX(A1:A5, MATCH("banán", A1:A5, 0))

6. Írja be ezt a képletet a B3 cellába: =INDEX(A1:A5, MATCH(“cseresznye”, A1:A5, 0))

Ezen a képen a B3 cellahivatkozás látható:

Az INDEX és a MATCH függvények nem az egyetlenek, amelyek lehetővé teszik több érték megtalálását. A következő részben megvizsgáljuk, hogyan használhatja alternatívaként az IF függvényt.

Az IF függvény használata több érték keresésére

Egy másik módja annak, hogy bizonyos feltételek alapján több cellaértéket keressen, az IF függvény használata más függvényekkel.

Az IF függvény lehetővé teszi több feltétel tesztelését, és a tesztek eredményétől függően különböző eredményeket ad vissza.

Tegyük fel például, hogy van egy értékesítési adatok táblázata a Termék és Értékesítés oszlopokkal. A három termék közül kettőnél szeretné megkeresni és összesíteni az eladási összeget.

Az Excel jelenlegi verziói

Ha az IF függvény segítségével meg szeretné keresni az Értékesítés oszlop összegét, ahol a termék „Apple” vagy „Banana”, használhat egy tömbképletet IF, SUM és OR függvényekkel.

Feltéve, hogy az adatok az A1 cellában kezdődnek, használja a következő képletet:

=SZUM(HA((A2:A4=”alma”)+(A2:A4=”banán”), B2:B4, 0))

Az (A2:A4=”Alma”)+(A2:A4=”Banán”) szakasz létrehoz egy tömböt, amelynek értéke 1, ha az A2:A4 tartomány cellája „alma” vagy „banán”, egyébként pedig 0. .

Az IF utasítás a tömb argumentum minden elemét ellenőrzi. Ha az érték 1 (vagyis a termék „Apple” vagy „Banana”), akkor az Értékesítés oszlopban szereplő megfelelő értéket veszi fel (B2:B4 tartomány); egyébként 0 kell.

A SUM függvény összeadja az IF függvény értékeit, hatékonyan összegezve az „Apple” és a „Banana” értékesítési értékeit.

Ez a kép a képletet mutatja működés közben a keresési tartományban:

Az Excel régebbi verziói

Az Excel 2019 vagy korábbi verziójában tömbképletet kell használnia. Kovesd ezeket a lepeseket:

1. Írja be a képletet, de ne nyomja meg az Enter billentyűt.

2. Nyomja meg a Ctrl + Shift + Enter billentyűket, hogy tömbképletté alakítsa.

Az Excel zárójeleket {} ad hozzá a képlethez, jelezve, hogy ez egy tömbképlet.

Ezután megvizsgáljuk, hogyan használhatja a SUMPRODUCT-ot több érték kikeresésére a kritériumok alapján. Gyerünk!

A SUMPRODUCT használata több kritériumhoz

A SUMPRODUCT funkció lehetővé teszi több érték keresését is több kritérium alapján.

Mivel nem igényel tömbképletet, a szintaxis az Excel verziójától függetlenül ugyanaz.

Ugyanazokat az adatokat használva, mint az előző példában, a képlet így néz ki:

=SZUMTERMÉK((A2:A4=”Alma”)+(A2:A4=”Banán”), B2:B4)

Az (A2:A4=”Alma”)+(A2:A4=”Banán”) szakasz létrehoz egy tömböt, amelynek értéke 1, ha az A2:A4 tartomány cellája „alma” vagy „banán”, egyébként pedig 0. .

A SUMPRODUCT függvény a tömb elemeit megszorozza az Értékesítés oszlop megfelelő elemeivel (B2:B4 tartomány). Ezután összeadja a kapott értékeket, és hatékonyan összegzi az „Apple” és a „Banana” értékesítési értékeit.

Az alábbi képlet működés közben mutatja be:

Az Excel-funkciók csodálatosak, ha az elvárásoknak megfelelően működnek, de néha hibákba ütközhet. A következő részben bemutatunk néhány gyakori hibát, és bemutatjuk, hogyan kezelheti őket.

3 gyakori hiba a keresési funkcióknál

A keresési funkciók néha olyan hibákat adnak vissza, amelyek frusztrálóak és időigényesek lehetnek a hibaelhárításhoz. A három leggyakoribb hiba, amellyel találkozhat:

1. #N/A hibák

2. #REF! Hibák

3. Körkörös hibák

1. #N/A hibák

A #N/A hiba akkor fordul elő, ha a keresési érték nem található a keresési tömbben.

A hiba előfordulásának több oka is lehet, többek között:

• a keresési érték hibásan van írva vagy helytelen.

• a keresési tömb nincs növekvő sorrendbe rendezve.

• a keresési érték nem szerepel az adatkészletben.

Ha a keresési érték nem szerepel az adatkészletben, ez hasznos információ. A tapasztalatlan Excel-felhasználók azonban azt gondolhatják, hogy az #N/A azt jelenti, hogy valami elromlott a képletben. A következő rész bemutatja, hogyan teheti ezt felhasználóbarátabbá.

2. #REF! Hibák

A bíró! hiba akkor fordul elő, amikor a keresési tömböt vagy a visszatérési tömböt töröljük vagy áthelyezzük.

Ez a hiba a cellahivatkozások frissítésével javítható a keresési függvényben.

3. Körkörös hibák

Amikor a függvényeket összetett képletekben kombinálja, az Excel jelezheti, hogy körkörös hivatkozással rendelkezik.

Ezeket könnyebben kivizsgálhatja, ha használja a címre vonatkozó útmutatónkat.

Az IFERROR használata keresési függvényekkel

Az IFERROR függvény hasznos eszköz a keresési függvények hibáinak kezelésére. Lehetővé teszi egy visszaadandó érték vagy képlet megadását, ha a keresési függvény hibát ad vissza.

Az IFERROR függvény szintaxisa a következő:

=IFERROR(érték, érték_ha_hiba)

· érték: értékelni kívánt érték vagy képlet.

· Value_if_error: az az érték vagy képlet, amelyet vissza kell adni, ha az első argumentum hibát ad vissza.

Tegyük fel például, hogy van egy VLOOKUP függvénye, amely több értéket keres egy táblázatban. Az alábbi képen az egyik érték nem létezik a keresett adattartományban.

Amint látható, megjelenik a #N/A hibaüzenet, ami megzavarhatja a tapasztalatlan Excel-felhasználókat.

Ehelyett az IFERROR segítségével megjeleníthet egy üres cellát vagy egy „Nem található” üzenetet a következő szintaxissal:

=IFERROR(VLOOKUP(keresési_érték, táblázat_tömb, oszlopindex, HAMIS), "Nem található")

Ebben a példában, ha a VLOOKUP függvény hibát ad vissza, az IFERROR függvény a „Nem található” üzenetet adja vissza.

Ez a kép a képletet mutatja működés közben. A B oszlopban található a hiányzó érték, míg a C és a D oszlopban találtak egyezést.

Eddig nagyon sok területet megtettünk, és végre készen állsz a kikeresések fejlettebb technikáinak elsajátítására, ami a következő rész témája.

7 Speciális keresési technikák

Több érték megkeresése az Excelben kihívást jelenthet, különösen nagy adatkészletek kezelésekor. Lassú feldolgozás esetén teljesítményproblémák léphetnek fel.

Hét fejlett keresési technikát használhat a folyamat egyszerűbbé és hatékonyabbá tételéhez.

• Relatív pozíció keresése

• KIS funkció

• ROW funkció

• SZŰRŐ funkció

• Segítő oszlopok

• Dinamikus tömbök

• Power Query

1. Relatív pozíció keresése

Az egyik legegyszerűbb a relatív pozíciókeresés használata. Ez magában foglalja a sorok és oszlopok eltolásainak megadását egy adott cellához képest a kívánt érték megkereséséhez.

Ha például van egy adattáblázata, és egy adott cellától két sorral lejjebb és három oszloppal jobbra lévő értéket szeretne megkeresni, használhatja a következő képletet:

=ELTOLÁS(cella, 2, 3)

2. KIS funkció

Egy másik hasznos technika a többszörös érték kereséséhez a SMALL függvény használata. Ez a függvény az n-edik legkisebb értéket adja vissza egy cellatartományban.

Ha ezt a funkciót más keresési függvényekkel, például INDEX és MATCH kombinálja, több értéket kereshet meghatározott feltételek alapján.

Például a következő képlet megkeresi a második legkisebb értéket egy bizonyos feltételnek megfelelő cellatartományban:

=INDEX(adatok, EGYEZÉS(KICSI(HA(feltételek, tartomány), 2), tartomány, 0))

3. SOR funkció

A ROW függvény több érték keresésére is használható az Excelben. Ez a függvény egy adott cella sorszámát adja vissza, amely felhasználható az adattáblázat celláira való hivatkozásra.

Például a következő képlet egyedi azonosító alapján keres egy értéket egy táblázatban:

=INDEX(adatok, EGYEZÉS(egyedi_azonosító, adat[egyedi_azonosító], 0), oszlop_index_száma)

4. SZŰRŐ funkció

A SZŰRŐ funkció nem érhető el az Excel régebbi verzióiban.

Az Excel 365-ben cellatartomány szűrésére használható bizonyos feltételek alapján, és csak azokat az értékeket adja vissza, amelyek megfelelnek ezeknek a feltételeknek. Ez a szintaxis és három argumentum:

=SZŰRŐ(tömb, tartalmazza, [ha_üres])

• tömb: A szűrni kívánt konkrét adatok.

• include: Az include argumentum a tömbre alkalmazni kívánt feltételek vagy feltételek.

• [if_empty] (opcionális): A visszaadandó érték, ha egyetlen sor vagy oszlop sem felel meg az include argumentumban megadott feltételeknek.

Például a következő képlet a példaadatokon dolgozik, hogy az első oszlop három eleme közül kettőre találjon egyezést, és összegezze a megfelelő értékeket a második oszlopban.

=SZUM(SZŰRŐ(B2:B4, (A2:A4="Alma")+(A2:A4="Banán")))

Ez a kép azt mutatja, hogy hány sor illeszkedik, és a különböző értékek összege:

5. Segítő oszlopok

Egy segédoszlop segítségével több mezőt egyesíthet egy függvényen belül, például a VLOOKUP.

Tegyük fel, hogy a kereszt- és vezetéknevekkel külön lapon dolgozik. Összefűzheti őket egy segédoszlopban, amelyre a végső képletben hivatkozunk.

6. Dinamikus tömbök

Amint azt a korábbi példákban megtudta, a Microsoft 365-felhasználók kihasználhatják a dinamikus tömbök előnyeit az Excelben végzett többszörös értékkereséshez.

A dinamikus tömbök lehetővé teszik több érték visszaadását egyetlen képletből, így könnyebben kereshet nagy mennyiségű adatot.

7. Power Query

A Power Query egy hatékony eszköz az Excelben, amely több feltétel alapján is használható értékek visszaadására.

Ez a videó például rendetlen adatokat talál egy táblázatban, és megtisztítja azokat.

Végső gondolatok

Ezzel véget is értünk a több érték Excelben való keresésének művészetében való mélyrelépésünknek! Miután elsajátította a VLOOKUP, INDEX, MATCH és tömbképleteket, úgy találja magát az összetett adatkészletek között, mint egy forró késsel a vajban.

A kulcs az, hogy megértsük az egyes képletek szintaxisát és logikáját. Ne feledje, hogy ezek a képletek bonyolultak lehetnek, ezért fontos, hogy szánjon rá időt, és alaposan tesztelje le képleteit, mielőtt fontos adatelemzést végezne rájuk.

Az Excel egy hatékony eszköz, de csak annyira jó, mint a felhasználó. Tehát folytassa ezeket a készségeket, kísérletezzen, és hamarosan a többértékű keresés mestere lesz. Következő alkalomig törje meg ezeket a számokat, és tegye meg az Excelt helyette a kemény munkát!