Ha egy ideje olvassa az Excel tippjeit, mindig talált valakit, aki az Excel INDEX () és MATCH () függvények használatáról beszél az Excel VLOOKUP helyett. Magamnak szólva mindig túl nehéz volt KÉT új funkció egyszerre történő kipróbálása. De ez klassz trükk. Adj öt percet, és megpróbálom egyszerű angolul elmagyarázni.
A VLOOKUP 30 másodperces áttekintése
Tegyük fel, hogy van egy táblája az alkalmazottak nyilvántartásáról. Az első oszlop egy alkalmazott száma, a fennmaradó oszlopok pedig különféle adatok az alkalmazottról. Bármikor van egy alkalmazott száma a munkalapon, a VLOOKUP segítségével visszaadhat egy adott nullapontot az alkalmazottról. A szintaxis a VLOOKUP (érték, adattartomány, oszlopsz., HAMIS). Azt mondja az Excel-nek: "Lépjen az adattartományba. Keressen egy sort, amelynek (értéke) van az adattartomány első oszlopában. Adja vissza az adott sor (oszlopszáma) th-edik értékét. Miután megértette a dolgot, nagyon egyszerű és hatalmas.
A probléma
Egy nap olyan helyzet áll elő, hogy megvan az alkalmazott neve, de szüksége van a munkavállaló számára. A következő képen van egy neve A10-ben, és meg kell találnia a munkavállalói számot a B10-ben.
Ha a kulcsmező a beolvasni kívánt adatoktól jobbra található, a VLOOKUP nem fog működni. Ha csak a VLOOKUP fogadná el az -1 értéket oszlopszámként, akkor nem lenne probléma. De nem. Az egyik általános megoldás az, hogy ideiglenesen beilleszt egy új A oszlopot, átmásolja a név oszlopot az új A oszlopba, feltölti a VLOOKUP, a Speciális értékek beillesztése elemet, majd törli az ideiglenes A oszlopot. Az Excel profik valószínűleg alvás közben is megtehetik ezt a lépést.
Azt fogom javasolni, hogy tegye meg a kihívást, és próbálja meg használni ezt az egylépéses módszert. Igen, néhány hétig fel kell tenned a képletet a faladon, de ugye ezt a VLOOKUP-tal is régen tetted?
Szerintem ennek oka olyan nehéz, hogy két olyan funkciót használ, amelyeket valószínűleg még soha nem használt. Tehát hadd bontsam két részre.
Először ott van az INDEX () függvény. Ez egy borzalmasan elnevezett függvény. Ha valaki azt mondja, hogy "index", az nem idéz fel semmit, ami hasonló ahhoz, amit ez a funkció végez. Az indexhez három érv szükséges.
=INDEX(data range, row number, column number)
Angolul az Excel megy az adattartományba, és visszaadja a (sorszám) harmadik sor és az (oszlopszám) oszlop metszéspontjában szereplő értéket. Hé, gondold át - ez nagyon egyszerű, igaz? =INDEX($A$2:$C$6,4,2)megadja a B5 értéket.
Alkalmazása INDEX (), hogy mi a probléma, akkor rájövünk, hogy visszatér a munkavállaló számot a megadott tartományból, akkor használd ezt: =INDEX($A$2:$A$6,?,1). Valójában ez a darab annyira triviálisnak tűnik, hogy haszontalannak tűnik. De amikor a kérdőjelet MATCH () függvényre cseréli, akkor megvan a megoldás.
Itt van a szintaxis:
=MATCH(Value, Single-column data range, FALSE)
Azt mondja az Excel-nek: "Keresse meg az adattartományt, és mondja meg nekem a relatív sorszámot, ahol talál egyezést (adatok). Tehát, hogy megtalálja, melyik sorban van az alkalmazott az A10-ben, használja =MATCH(A10,$B$2:$B$6,FALSE). Igen, ez összetettebb, mint az Index , de a VLOOKUP profik sikátorában kell lennie. Ha az A10 "Miller, Bob" -ot tartalmaz, akkor ez a MATCH visszaadja, hogy a B2: B6 tartomány 3. sorában van.
Itt van - a MATCH () függvény megmondja az Index függvénynek, hogy melyik sorba kell nézni - kész. Vegyük az Index függvényt, cseréljük le a kérdőjelünket a MATCH függvényre, és most megtehetjük a VLOOKUP-ek megfelelőjét, ha a kulcsmező nincs a bal oszlopban. Itt van a használni kívánt funkció:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
A falamon található cetlik valójában két vonalként mutatják. Először kiírtam a MATCH () magyarázatát. Ez alatt írtam az INDEX () magyarázatát. Ezután rajzoltam egy tölcséralakot a kettő közé, jelezve, hogy a MATCH () függvény az INDEX () függvény 2. argumentumába esik.
Az első néhány alkalommal, amikor meg kellett tennem az egyiket, megkísértettem, hogy becsapjak egy új ideiglenes A oszlopot odabent, de inkább átéltem a fájdalmat. Gyorsabb és kevesebb manipulációt igényel. Tehát, amikor legközelebb azt szeretné, ha negatív számot írna a VLOOKUP függvénybe, próbálja ki az INDEX és a MATCH ezen furcsa kombinációját a problémák megoldásához.
