Hogyan készíthetünk Excel keresési képletet 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ó magában foglalja a lépésenkénti példát egy olyan keresési képlet létrehozására, amely több kritériumot használ a beszállító kereséséheztitán widgetek egy mintaadatbázisban. Kövesse az utasításokat, hogy megtudja, milyen egyszerű a saját egyéni keresési képletek létrehozása.

01/08

Készítsen Excel-munkafüzetet az adatokkal

A bemutató lépéseinek követése érdekében el kell kezdeni a mintaadatok beillesztését a következő cellákba, amint a fenti képen látható. A 3. és 4. sorok üresen maradnak annak érdekében, hogy elférjenek az ebben a bemutatóban létrehozott tömb-képletnek.

• Adja meg az adatok legmagasabb tartományát a cellákba D1 nak nek F2
• Adja meg a második tartományt a cellákba D5 nak nek F11

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/08

Hozzon létre egy INDEX függvényt Excel-ben

A INDEX A funkció az Excel néhány olyan formája, amely többféle formában létezik. A funkció egy Array forma és a Hivatkozási nyomtatvány. 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 a Táblagazdálkodási űrlapot használjuk, mivel szeretnénk megismerni a szállító nevét a titán-kütyüktől, nem pedig az adatbázison lévő cella-hivatkozásról. Kövesse az alábbi lépéseket az INDEX funkció létrehozásához:

1. Kattintson a cellára F3 hogy létrehozza az aktív cellát - itt fogunk beilleszteni a beágyazott függvényt.
2. Kattintson a képletek a szalag menü lapja.
3. Választ Keresés és referencia a szalagsorból a funkció megnyitásához.
4. Kattintson INDEX a listában, hogy fel a Válassza az Argumentumok lehetőséget párbeszédablak.
5. Válaszd a tömb, row_num, col_num opciót a párbeszédpanelen.
6. Kattints rendben megnyitja az INDEX funkció párbeszédpanelt; ez megnyitja a Formula Buildert Excel-ben.
7. Ban,-ben Képletkészítő, kattintson a Sor vonal.
8. Jelölje ki a sejteket D6 nak nek F11, kattintva és húzva, a munkalapon, hogy bejusson a tartományba az építõbe.

Az Excel korábbi verzióiban Képletkészítő helyébe a Funkcióelméletek ablak. Az ablakban ugyanazokat a lépéseket alkalmazza, mint az ebben a bemutatóban.

03/08

Indítsa el a beágyazott MATCH függvényt

Ha egy funkciót beágyaz a másikba, akkor nem lehet megnyitni a második vagy a beágyazott függvény képletkészítőjét o Adja meg a szükséges argumentumokat. A beágyazott függvényt az első függvény egyikének rendezésében kell beírni.

Amikor manuálisan írja be a funkciókat, a függvény argumentumait elválasztja egymástól vesszővel.

A beágyazott MATCH funkció beírásának első lépése a keresési_érték érv. 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; takkor két vagy több cellahivatkozás összekapcsolásával vagy összekapcsolásával történik az ampersand szimbólum - &.

1. Ban,-ben Képletkészítő, kattintson a sorszám vonal.
2. Írja be a funkció nevét MÉRKŐZÉS ezt követi egy nyitott kerek tartó.
3. Kattintson a cellára D3 a cella hivatkozásának beírása a párbeszédpanelbe.
4. Írjon be egy ampersand-ot a cella hivatkozás után D3 egy második cella hivatkozás hozzáadásához.
5. Kattintson a cellára E3 a második cellahivatkozás beírásához.
6. Írjon be vesszőt a cellahivatkozás után E3 a MATCH függvény bejegyzésének befejezéséhez keresési_érték érv.

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

04/08

Végezze el az Nested MATCH függvényt

Ez a lépés a Lookup_array argumentum a beágyazott MÉRKŐZÉS funkció. 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 függvény csak egy tömböt keresi az egyes megadott kifejezésekre, így több tömb beadására használjuk újra az ampersand-ot, és összekapcsoljuk a tömböket.

Ezeket a lépéseket az előző lépésben megadott vessző után kell megadni sorszám vonal a INDEX funkció.

1. Kattintson a sorszám sorban a vessző után a beillesztési pontot az aktuális bejegyzés végén.
2. Jelölje ki a sejteket D6 nak nek D11 a munkalapon, hogy bejusson a tartományba - ez az első tömb, amellyel a funkció keresni.
3. Írjon be egy ampersand-ot a cella hivatkozások után D6: D11 mert azt szeretnénk, hogy a függvény két tömböt keressen.
4. Jelölje ki a sejteket E6 nak nek E11 a munkalapon, hogy bejusson a tartományba - ez a második tömb, amellyel a funkció keresni.
5. Írjon be vesszőt a cellahivatkozás után E3 befejezni a MÉRKŐZÉS függvény Lookup_array érv.
6. Hagyd a INDEX funkció párbeszédpanel megnyílik a következő lépéshez a bemutatóban.

05/08

Adja hozzá a MATCH típusú argumentumot

A MATCH függvény harmadik és utolsó érve a match_type érv; azt mondja Excelnek, hogy hogyan illeszkedjen a keresési_érték a Lookup_array értékekkel - a rendelkezésre álló 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 elhagyják: 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: A MATCH az első olyan értéket találja meg, amely pontosan megegyezik a Lookup_value értékkel. A Lookup_array adatok sorrendben rendezhetők.
• Ha a Match_type = -1: A MATCH a legkisebb értéket találja meg, amely nagyobb vagy egyenlő a Lookup_value értékkel. A Lookup_array adatokat csökkenő sorrendben kell rendezni.

Ezeket a lépéseket az előző lépésben megadott vessző után kell megadni sorszám vonal a INDEX funkció.

1. A vessző után a sorszám line, írja be a nulla - 0 - mivel azt szeretnénk, hogy a beágyazott függvény pontosan illeszkedjen a sejtekben megadott kifejezésekhez D3 és E3.
2. Írjon be egy zárókorongot - ) - befejezni a MÉRKŐZÉS funkció.
3. Hagyd a Képletkészítő nyissa meg a következő lépést a bemutatóban.

06/08

Az INDEX funkció befejezése

Most, hogy a MÉRKŐZÉS akkor a Formula Builder harmadik sorára lépünk, és beírjuk az utolsó argumentumot a INDEX funkció. Ez a harmadik és utolsó érv a oszlop_száma argumentum, amely az Excel-nek az oszlopok számát jelöli a tartományban D6 nak nek F11; itt találja meg a funkció által visszaadott információkat. Ebben az esetben a titán kütyü.

1. Kattintson a oszlop_száma vonal.
2. Írja be a hármas számot - 3 - ezen a vonalon, mivel adatokat keresünk a tartomány harmadik oszlopában D6 nak nek F11.
3. Ismét hagyja el a Képletkészítő nyissa meg a következő lépést a bemutatóban.

07. 08

Az Array képlet létrehozása

A zárás előtt Képletkészítő, beágyazott függvényünket egy tömb-képletre kell kapcsolnunk; ez teszi lehetővé funkciónk számára, hogy több kifejezést keressen az adatlapon. Ebben a bemutatóban két kifejezést szeretnénk egyeztetni: Widgets az 1. és az 1. oszlopokból Titán a 2. oszlopból.

Egy tömbformátum létrehozása az Excel programban a CTRL, VÁLTÁS, és BELÉP billentyűk egyidejű megnyomásával. A gomb megnyomása után a függvényt göndör zárójelek veszi körül, jelezve, hogy a funkció most tömb.

1. Zárd be a Képletkészítő kattintson a Kész gomb.
2. Ezután válassza ki a cellát F4 és nyomja meg a gombot Belép kulcs a képlet megtekintéséhez.
3. Ha a képletet tömbré szeretné konvertálni, egyszerre nyomja meg a gombot CTRL + SHIFT + ENTER a billentyűzeten.
4. Ha helyesen történik a # N / A hiba fog megjelenni az F3 cellában - a cellában, ahol beléptünk a függvénybe.
5. A # N / A a cellában megjelenik a hiba F3 mert a sejtek D3 és E3 üresek. D3 és E3 a sejtek, ahol azt mondtuk, hogy megtalálja a függvényt Lookup_values. Miután ezeket a két cellát hozzáadtuk, a hibát az adatbázisból származó információ váltja fel.

08. 08

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

Végül hozzáadjuk a keresési kifejezéseket a munkalapunkhoz. Amint azt az előző lépésben említettük, a feltételeknek megfelelni akarunk Widgets az 1. és az 1. oszlopokból Titán a 2. oszlopból. Ha és csak akkor, ha képletünk a megfelelő oszlopokban mindkét kifejezéshez illeszkedik az adatbázisban, visszaadja az értéket a harmadik oszlopból.

1. Kattintson a cellára D3.
2. típus Widgets és nyomja meg a gombot Belép gombot a billentyűzeten.
3. Kattintson a cellára E3.
4. típus Titán és nyomja meg a gombot Belép 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 a Titanium Widgeteket.
6. Amikor az F3 cellára kattint, a teljes függvény megjelenik a munkalap feletti képletsávban.

{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}

Példánkban csak egy szállító van a titán-kütyüknél. Ha több szállító is van, az első az adatbázisban felsorolt ​​szállítót visszaküldi a függvény.