Excel keresési képlet több kritériummal

Excel- tömb-képlet használatával létrehozhatunk egy keresési formulát, amely több kritériumot használ az információ vagy adatbázis adatainak kereséséhez.

A tömb-formula magában foglalja a MATCH funkciót az INDEX funkcióban.

Ez a bemutató lépésről lépésre tartalmaz egy olyan keresési képlet létrehozását, amely több kritériumot alkalmaz, hogy egy mintaadatbázist találjon titánmezők szállítójaként.

Az alábbi oktató témák lépéseit követve a fenti képen látható képlet létrehozásával és használatával halad.

01/09

A bemutatóadatok beírása

A tutorial első lépése az adatok Excel munkalapba történő beírása .

A tutorial lépéseinek követéséhez írja be a fenti képen látható adatokat a következő cellákba .

• Adja meg az adatok legfelsõbb tartományát a D1-F2 cellákra
• Adja meg a második tartományt a D5-F11 cellákba

A 3. és 4. sorok üresen maradnak annak érdekében, hogy elférjenek az ebben a bemutatóban létrehozott tömb-képletnek .

A bemutató nem tartalmazza a képen látható formázást, de ez nem befolyásolja a keresési képlet működését.

A fentiekhez hasonló formázási lehetőségekkel kapcsolatos információk megtalálhatók ebben a Basic Excel Formatting Tutorialban.

02/09

INDEX funkció indítása

Az INDEX funkció egyike azon kevés Excel-nek, amely többféle formában van. A függvénynek van egy Array Form és egy Referencia Formája .

Az Array Form visszaadja az aktuális adatokat egy adatbázisból vagy adatlapból, míg a Referencia űrlap adja meg a táblázat cella hivatkozási helyét vagy helyét.

Ebben a bemutatóban az Array formát használjuk, mivel szeretnénk tudni a szállító nevét a titán-kütyüktől, nem pedig az adatbázison lévő cella-hivatkozásról.

Minden űrlapnak eltérő listája van az argumentumokkal , amelyeket a funkció megkezdése előtt ki kell választani.

Bemutató lépések

1. Kattintson az F3 cellára, hogy az aktív cellát létrehozza . Itt adjuk meg a beágyazott függvényt.
2. Kattintson a szalag menü Formulák lapjára.
3. Válassza a Keresés és referencia lehetőséget a szalagból a funkció legördülő listájának megnyitásához.
4. Kattintson a INDEX elemre a listában, hogy megjelenjen a Select Arguments párbeszédpanel .
5. Válassza ki a tömb, row_num, col_num opciót a párbeszédpanelen.
6. Az INDEX funkció párbeszédpanel megnyitásához kattintson az OK gombra.

03/09

Belépés a INDEX Funkció Array argumentumába

Az első argumentum az Array argumentum. Ez az argumentum határozza meg a kívánt adatok keresendő celláinak tartományát .

A bemutatóra ez az argumentum lesz a mintaadatbázisunk.

Bemutató lépések

1. A INDEX funkció párbeszédpanelen kattintson az Array sorra.
2. Jelölje ki a munkalapon a D6-F11 cellákat, hogy belépjen a tartományba a párbeszédpanelen.

04/09

Az elhelyezett MATCH függvény elindítása

Ha egy funkciót beágyaz a másikba, akkor a második vagy a beágyazott függvény párbeszédablakát nem lehet megnyitni a szükséges érvek megadásához.

A beágyazott függvényt az első függvény egyik argumentumaként kell beírni.

Ebben a bemutatóban a beágyazott MATCH függvény és annak érvei az INDEX funkció párbeszédablak második sorában kerülnek beírásra - a Row_num sor.

Fontos megjegyezni, hogy manuálisan a függvények bevitele során a függvény argumentumait elválasztja egymástól vesszővel "," .

A MATCH függvény keresési_érték-argumentumának megadása

A beágyazott MATCH funkció beírásának első lépése a Lookup_value argumentum beírása .

A Lookup_value a keresési kifejezés helyét vagy cellahivatkozását jelenti az adatbázisban.

Általában a Lookup_value csak egy keresési kritériumot vagy kifejezést fogad el. Több feltétel megkereséséhez ki kell terjeszteni a Lookup_value értéket .

Ezt úgy végezzük, hogy kettő vagy több cellahivatkozást összekapcsolunk, és az " & " jelzésű jeleket használjuk.

Bemutató lépések

1. A INDEX funkció párbeszédpanelen kattintson a Row_num sorra.
2. Írja be a függvény nevek egyezését, majd egy nyitott kerek " ( "
3. Kattintson a D3 cellára, hogy beírja a cella hivatkozást a párbeszédpanelbe.
4. Adjon meg egy " C " cellahivatkozást és egy második cella hivatkozást.
5. Kattintson az E3 cellára, hogy belépjen a második cella hivatkozásra a párbeszédpanelen.
6. Írjon be egy vesszőt "," az E3 cellahivatkozás után, hogy befejezze a MATCH függvény Lookup_value argumentumának bejegyzését.
7. Hagyja az INDEX funkció párbeszédpanelt nyitva a következő lépéshez a bemutatóban.

A tutorial utolsó lépésben a Lookup_values ​​a munkalap D3 és E3 celláiba kerül.

05/09

A MATCH függvény Lookup_array felvétele

Ez a lépés magában foglalja a Lookup_array argumentum hozzáadását a beágyazott MATCH függvényhez.

A Lookup_array az a cellatartomány, amelyet a MATCH függvény keresi, hogy megtalálja a Lookup_value argumentumot, amelyet a bemutató előző lépéseiben adtak hozzá.

Mivel a Lookup_array argumentum két keresési mezőjét azonosította, ugyanezt kell tenni a Lookup_array esetében is . A MATCH funkció csak egy tömböt keresi az egyes megadott kifejezésekhez.

Több tömb beírásához ismét használjuk az " & " karaktereket, és összekapcsoljuk a tömböket.

Bemutató lépések

Ezeket a lépéseket az INDEX funkció párbeszédpanel Row_num sorának előző lépésében szereplő vesszővel kell megadni.

1. A vessző után kattintson a Row_num sorra, hogy a beillesztési pontot az aktuális bejegyzés végén helyezze el.
2. Jelölje ki a munkalapon a D6-D11-es cellákat a tartományba. Ez az első olyan tömb, amelyet a keresésnek kell keresnie.
3. Írjon be egy " D " és " D11 " cellahivatkozást a " & " jelre, mert szeretnénk, ha a függvény két tömböt keres.
4. Jelölje ki a munkalapon E6-E11 cellákat a tartományba. Ez a második tömb, amelyet a keresésnek kell keresnie.
5. Írjon be egy vesszőt "," az E3 cellahivatkozás után, hogy befejezze a MATCH függvény Lookup_array argumentumának bejegyzését.
6. Hagyja az INDEX funkció párbeszédpanelt nyitva a következő lépéshez a bemutatóban.

06/09

Az egyezés típusa és a MATCH függvény kiegészítése

A MATCH függvény harmadik és utolsó érve a Match_type argumentum.

Ez az argumentum megmutatja az Excel-nek, hogy hogyan illeszkedjen a keresési_értékhez a Lookup_array értékekkel. A lehetőségek: 1, 0 vagy -1.

Ez az érv nem kötelező. Ha kihagyja, a függvény az alapértelmezett 1 értéket használja.

• ha a Match_type = 1 vagy kihagyásra kerül: a MATCH a legnagyobb értéket találja, amely kisebb vagy egyenlő a Lookup_value értékkel. A Lookup_array adatokat növekvő sorrendben kell rendezni .
• ha a match_type = 0: MATCH megtalálja az első értéket, amely pontosan megegyezik a Lookup_value értékkel. A Lookup_array adatok sorrendben rendezhetők.
• ha a Match_type = -1: MATCH a legkisebb értéket találja, amely nagyobb vagy egyenlő a Lookup_value értékkel. A Lookup_array adatokat csökkenő sorrendben kell rendezni.

Bemutató lépések

Ezeket a lépéseket az INDEX funkció párbeszédpanel Row_num sorának előző lépésében szereplő vesszővel kell megadni.

1. A Row_num sorban lévő vesszővel írja be a " 0 " nulla értéket, mivel azt szeretnénk, hogy a beágyazott függvény pontosan illeszkedjen a D3 és E3 cellákba bevitt kifejezésekhez.
2. Írja be a záró kerekek " ) " a MATCH funkció befejezéséhez.
3. Hagyja az INDEX funkció párbeszédpanelt nyitva a következő lépéshez a bemutatóban.

07, 09

Vissza az INDEX funkcióhoz

Most, hogy a MATCH funkció megtörtént, a nyitott párbeszédpanel harmadik sorára lépünk, és beírjuk az INDEX funkció utolsó argumentumát .

Ez a harmadik és utolsó argumentum az oszlop_num argumentum, amely az oszlopok számát a D6-tól az F11-ig terjedő tartományban mutatja meg , ahol megtalálja a funkció által visszaadott információkat. Ebben az esetben a titánvégszerzők szállítója.

Bemutató lépések

1. Kattintson a párbeszédablak Column_num sorára.
2. Írja be a " 3 " (nincs idézet) számot ebben a sorban, mivel a D6-F11 tartomány harmadik oszlopában keresünk adatokat.
3. Ne kattintson az OK gombra, vagy zárja be az INDEX funkció párbeszédpanelt. Nyitottnak kell maradnia a bemutató következő lépésében - a tömb-képlet létrehozásához.

08, 09

Az Array képlet létrehozása

Mielőtt bezárná a párbeszédpanelt, beágyazott függvényünket tömb-képletnek kell beírnunk .

A tömb-képlet lehetővé teszi, hogy több kifejezést keressen az adatok táblázatban. Ebben a bemutatóban két kifejezést találunk: az 1. oszlopból és a 2. oszlopból származó titoktartók.

Egy tömbformátum létrehozása az Excel programban a billentyűzeten található CTRL , SHIFT és ENTER billentyűk lenyomásával történik.

A gombok összenyomásának hatása az, hogy a függvényt gömbölyű zárójelekkel körülveszi: {} jelezve, hogy most egy tömb formula.

Bemutató lépések

1. Ha a befejezett párbeszédpanel továbbra is megnyílik a bemutató előző lépéséből, nyomja meg és tartsa lenyomva a CTRL és a SHIFT billentyűket a billentyűzeten, majd nyomja le és engedje fel az ENTER billentyűt.
2. Ha helyesen történik, akkor a párbeszédpanel bezárul, és egy # N / A hiba jelenik meg az F3 cellában - a cellában, ahol beléptünk a funkcióba.
3. A # N / A hiba megjelenik az F3 cellában, mert a D3 és E3 cellák üresek. D3 és E3 azok a cellák, ahol azt mondtuk a függvénynek, hogy megtalálják a Lookup_values-ot a tutorial 5. lépésben. Miután ezeket a két cellát hozzáadtuk, a hibát az adatbázisból származó információ váltja fel.

09. 09. sz

A Keresési feltételek hozzáadása

A tutorial utolsó lépése, hogy hozzáadja a keresési kifejezéseket a munkalapunkhoz.

Amint azt az előző lépésben említettük, az 1. oszlopból és a 2. oszlop Titánból származó Widgetek kifejezést szeretnénk megfeleltetni.

Ha és csak akkor, ha képletünk megfelel az adatbázis megfelelő oszlopainak mindkét kifejezéséhez, visszaadja az értéket a harmadik oszlopból.

Bemutató lépések

1. Kattintson a D3 cellára.
2. Írja be a widgeteket, és nyomja meg az Enter gombot a billentyűzeten.
3. Kattintson az E3 cellára.
4. Írja be a Titanium-t, és nyomja meg az Enter gombot a billentyűzeten.
5. A szállító neve Widgets Inc. megjelenik az F3 cellában - a funkció helye, mivel ez az egyetlen beszállító felsorolja, aki eladja a Titanium Widgeteket.
6. Ha az F3 cellára kattint, akkor a teljes funkció
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   megjelenik a munkalap feletti képletsávban .

Megjegyzés: A példánkban csak egy beszállító volt a titán kütyüinek. Ha több szállító is van, az első az adatbázisban felsorolt ​​szállítót visszaküldi a függvény.