4 Excel keresési funkció a táblázatok hatékony kereséséhez

4 Excel keresési funkció a táblázatok hatékony kereséséhez

Legtöbbször a keresés a Microsoft Excel táblázat nagyon egyszerű. Ha nem tudja csak végigolvasni a sorokat és oszlopokat, használja a Ctrl + F billentyűkombinációt. Ha igazán nagy táblázatkezelővel dolgozik, sok időt takaríthat meg a négy keresési funkció valamelyikének használatával.





Ha már tudja, hogyan kell keresni Excelben a kereső használatával, akkor nem számít, hogy mekkora lesz a táblázat, mindig talál valamit az Excelben!



1. A VLOOKUP funkció

Ez a funkció lehetővé teszi egy oszlop és egy érték megadását, és visszaad egy értéket egy másik oszlop megfelelő sorából (ha ennek nincs értelme, akkor egy pillanat múlva világossá válik). Két példa erre, ha megkeresi az alkalmazott vezetéknevét az alkalmazotti száma alapján, vagy egy telefonszámot a vezetéknév megadásával.





Íme a függvény szintaxisa:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [lookup_value] ez az információ, ami már megvan. Például, ha tudnia kell, hogy egy város melyik államban van, ez lenne a város neve.
  • [tábla_tömb] segítségével megadhatja azokat a cellákat, amelyekben a függvény megkeresi a keresési és visszatérési értékeket. A tartomány kiválasztásakor ügyeljen arra, hogy a tömb első oszlopa tartalmazza a keresési értéket!
  • [col_index_num] a visszatérési értéket tartalmazó oszlop száma.
  • [range_lookup] opcionális argumentum, és 1 vagy 0. Ha 1-et ad meg, vagy kihagyja ezt az argumentumot, a függvény megkeresi a megadott értéket vagy a következő legalacsonyabb számot. Tehát az alábbi képen egy 652 -es SAT -pontszámot kereső VLOOKUP 646 -ot ad vissza, mivel ez a legközelebbi szám a listában, amely kevesebb, mint 652, és a [range_lookup] alapértelmezett értéke 1.

Nézzük meg, hogyan használhatja ezt. Ez a táblázat azonosító számokat, kereszt- és vezetékneveket, városokat, államokat és SAT pontokat tartalmaz. Tegyük fel, hogy szeretné megtalálni a 'Tél' vezetéknevű személy SAT pontszámát. A VLOOKUP megkönnyíti. Íme a képlet, amelyet használni szeretne:



=VLOOKUP('Winters', C2:F101, 4, 0)

Mivel a SAT pontszámok a vezetéknév oszlop negyedik oszlopa, a 4 az oszlopindex argumentum. Ne feledje, hogy amikor szöveget keres, a [range_lookup] érték 0 -ra állítása jó ötlet. Enélkül rossz eredményeket érhet el.

Íme az eredmény:



651 -et adott vissza, a SAT pontszámot a Kennedy Winters nevű tanulóé, aki a 92. sorban szerepel (a fenti betétlapon látható). Sokkal tovább tartott volna a név keresése, mint a szintaxis gyors begépelése!

Megjegyzések a VLOOKUP -hoz

A VLOOKUP használata során érdemes néhány dolgot észben tartani. Győződjön meg arról, hogy a tartomány első oszlopa tartalmazza a keresési értéket. Ha nem szerepel az első oszlopban, a függvény hibás eredményeket ad vissza. Ha az oszlopok jól szerveződnek, ez nem jelenthet problémát.



Ne feledje továbbá, hogy a VLOOKUP csak egy értéket ad vissza. Ha a „Georgia” kifejezést használta volna keresési értékként, akkor visszaadta volna az első grúziai hallgató pontszámát, és nem jelezte, hogy valójában két grúziai diák van.

2. A HLOOKUP funkció

Ha a VLOOKUP megfelelő értékeket talál egy másik oszlopban, a HLOOKUP a megfelelő értékeket egy másik sorban. Mivel általában a legegyszerűbb az oszlopfejléceket végigolvasni, amíg meg nem találja a megfelelőt, és szűrő segítségével nem találja meg, amit keres, a HLOOKUP akkor a legjobb, ha valóban nagy táblázatok vannak, vagy idő szerint szervezett értékekkel dolgozik. .

Íme a függvény szintaxisa:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [lookup_value] az az érték, amelyet ismer, és amelynek megfelelő értéket szeretne találni.
  • [tábla_tömb] azok a cellák, amelyekben keresni szeretne.
  • [sor_index_szám] megadja azt a sort, amelyből a visszatérési érték származik.
  • [range_lookup] ugyanaz, mint a VLOOKUP -ban, hagyja üresen, hogy a legközelebbi értéket kapja, ha lehetséges, vagy írja be a 0 -t, hogy csak a pontos egyezéseket keressen.

Ez a táblázat tartalmaz egy sort az egyes államokra vonatkozóan, valamint egy SAT pontszámot a 2000–2014 -es években. A HLOOKUP segítségével megtalálhatja a 2013 -as Minnesota átlagpontszámát. Így járunk el:

=HLOOKUP(2013, A1:P51, 24)

Amint az alábbi képen látható, a pontszám visszaadódik:

A Minnesotans átlagosan 1014 pontot ért el 2013 -ban. Vegye figyelembe, hogy 2013 nem idézőjelben van, mert ez egy szám, és nem karakterlánc. Ezenkívül a 24 -es Minnesotából származik, a 24. sorban.

Itt van hogyan kell kiszámítani a súlyozott átlagot Excelben .

Megjegyzések a HLOOKUP -hoz

A VLOOKUP -hoz hasonlóan a keresési értéknek a táblázat tömbjének első sorában kell lennie. Ez ritkán jelent problémát a HLOOKUP esetében, mivel általában egy oszlopcímet használ a keresési értékhez. A HLOOKUP szintén csak egyetlen értéket ad vissza.

3-4. Az INDEX és a MATCH függvények

Az INDEX és a MATCH két különböző funkció, de együtt használva sokkal gyorsabbá tehetik a keresést egy nagy táblázatban. Mindkét funkciónak vannak hátrányai, de ezek kombinálásával mindkettő erősségeire építünk.

Először is, mindkét funkció szintaxisa:

=INDEX([array], [row_number], [column_number])
  • [sor] az a tömb, amelyben keresni fog.
  • [sor_szám] és [oszlopszám] a keresés szűkítésére használható (egy pillanat múlva megvizsgáljuk.)
=MATCH([lookup_value], [lookup_array], [match_type])
  • [lookup_value] egy keresési kifejezés, amely lehet karakterlánc vagy szám.
  • [lookup_array] az a tömb, amelyben a Microsoft Excel keresi a keresési kifejezést.
  • [match_type] opcionális argumentum, amely lehet 1, 0 vagy -1. 1 a legnagyobb értéket adja vissza, amely kisebb vagy egyenlő a keresési kifejezéssel. A 0 csak a pontos kifejezést adja vissza, a -1 pedig a legkisebb értéket, amely nagyobb vagy egyenlő a keresési kifejezéssel.

Lehet, hogy nem világos, hogyan fogjuk használni ezt a két funkciót együtt, ezért itt leírom. A MATCH keresési kifejezést vesz fel, és cellahivatkozást ad vissza. Az alábbi képen látható, hogy az F oszlop 646 értékének keresésekor a MATCH 4 értéket ad vissza.

Az INDEX viszont az ellenkezőjét teszi: cellahivatkozást vesz, és visszaadja a benne lévő értéket. Itt láthatja, hogy amikor az INDEX felszólítja, hogy adja vissza a Város oszlop hatodik celláját, az „Anchorage” értéket adja vissza, a 6. sor értékét.

A kettőt egyesítjük, hogy a MATCH cellahivatkozást adjon vissza, az INDEX pedig ezt a hivatkozást használja a cella értékének megkereséséhez. Tegyük fel, hogy emlékszik arra, hogy volt egy diák, akinek vezetékneve Waters volt, és látni szeretné, hogy ennek a tanulónak mi volt a pontszáma. Íme a képlet, amelyet használni fogunk:

a paypal miért nem küld pénzt
=INDEX(F:F, MATCH('Waters', C:C, 0))

Észre fogja venni, hogy az egyezés típusa itt 0 -ra van állítva. Ha karakterláncot keres, akkor ezt szeretné használni. Ezt a funkciót futtatva kapjuk:

Amint a betétből is látható, Owen Waters 1720 pontot ért el, ez a szám akkor jelenik meg, amikor a funkciót futtatjuk. Ez talán nem tűnik túl hasznosnak, ha csak néhány oszlopot nézel át, de képzelje el, mennyi időt takaríthat meg, ha 50 alkalommal kell elvégeznie nagy adatbázis -táblázat amely több száz oszlopot tartalmazott!

Kezdődjenek az Excel keresések

A Microsoft Excelben sok van rendkívül erőteljes funkciók az adatok manipulálásához, és a fent felsorolt ​​négy csak karcolja a felületet. Ha megtanulod használni őket, sokkal könnyebb lesz az életed.

Ha valóban el akarja sajátítani a Microsoft Excel programot, akkor igazán előnyös lehet, ha kéznél tartja az Essential Excel Cheat Sheet -et!

Kép jóváírása: Cico/ Shutterstock

Részvény Részvény Csipog Email Canon vs Nikon: Melyik a jobb márka?

A Canon és a Nikon a két legnagyobb név a kameraiparban. De melyik márka kínálja a kamerák és objektívek jobb választékát?

Olvassa tovább Kapcsolódó témák
  • Termelékenység
  • Táblázat
  • Microsoft Excel
  • Keresési trükkök
A szerzőről Ian Buckley(216 megjelent cikk)

Ian Buckley szabadúszó újságíró, zenész, előadó és videó producer, Berlinben, Németországban él. Amikor éppen nem ír vagy a színpadon, barkácsol elektronikával vagy kóddal, abban a reményben, hogy őrült tudós lesz.

Bővebben: Ian Buckley

Iratkozzon fel hírlevelünkre

Csatlakozz hírlevelünkhöz, ahol technikai tippeket, véleményeket, ingyenes e -könyveket és exkluzív ajánlatokat találsz!

Feliratkozáshoz kattintson ide