Használja az INDEX és a MATCH alkalmazást az egyszerű adatbázis lekérdezésekhez az Excel-ben

Eredetileg az Excel nem volt igazi adatbázis. A korai adatbázis-funkciók mennyisége és minősége korlátozott volt. És mivel egy Excel adatbázison minden rekord egyszerre látható a képernyőn - ez azt jelenti, hogy mindegyik a memóriában egyszerre volt - az Excel adatbázisoknak nagyon kicsinek kellett lenniük: több rekordot tartalmazó mezőt vagy néhány rekordot tartalmazó mezőt; és a minimális számítások.

A VLOOKUP (függőleges) és a HLOOKUP (vízszintes) funkciók voltak az egyedüli funkciók, amelyek az adatbázis specifikus információk lekérdezésére állnak rendelkezésre. Például lekérdezhetnéd, hogy olyan lekérdezéseket kereshetsz és kiválaszthatok, amelyek több 1000 dolláros, de kevesebb mint 5000 dolláros értéket tartalmaztak, de csak lapos fájlokban (csak egy adatbázis mátrix).

Pivot táblákat fejlesztettek ki, hogy a felhasználók relációs adatbázisokat hozhassanak létre, amelyek könnyebb lekérdezni, kevesebb memóriát használni, és pontosabb eredményeket szolgáltatni. Ha azonban nincs relációs adatbázisa, vagy szükséged van rá, de erősebb és megbízhatóbb adatbázisfunkciókat igényel, próbáld meg ezeket az indításhoz.

[További olvasmány: Az új PC-nek szüksége van ezekre a 15 ingyenes, kiváló programra]

Index, Match és Index Match

Az Excelben az INDEX függvény egy adott pozícióból (egy listából, táblából, adatbázisból) visszaküldi az elemet.

A MATCH függvény egy érték helyét (egy listában , táblázat, adatbázis). És az INDEX-MATCH funkciók együtt használják, hogy egy tábláról táplálják az adatokat.

Az INDEX funkció szintaxisa: INDEX (tömb, sor_num, [oszlop_num]). A tömb azon cellák tartománya, amelyekkel dolgozik. A Row_num nyilván az a sorszám, amely a keresett adatok között szerepel. Az oszlop_num az oszlopok száma a keresett adatok között. Az INDEX képlet nem ismeri fel az oszlopokat, ezért számokat kell használnia (balról számolva).

A MATCH függvény szintaxisa: MATCH (lookup_value, lookup_array, [match_type]). A lookup_value a keresett szám vagy szöveg, amely érték, logikai érték vagy cellahivatkozás lehet. A lookup_array az a cellatartomány, amelyen dolgozik. A match_type meghatározza a MATCH függvényt - azaz pontos egyezést vagy a legközelebbi mérkőzést.

A. INDEX funkció

Példánkban a híres Commodore James Norringtonnak van egy táblázata, amely nyomon követi az összes kalózhajót a Karib-térségben. A Norrington listáját a hajók harci formációi rendezik, amelyek megfelelnek a térség tengeri térképének. Amikor látja, hogy egy hajó halad előre, az indexképet a táblázathoz, így képes azonosítani a hajót és annak kapacitásait. Ebben az első lekérdezésben Norrington szeretné tudni, hogy milyen hajó halad tovább.

1. Válasszon ki egy helyet (cellát vagy cellatartományt) a lekérdezésekhez (azaz funkciókhoz és találatokhoz), majd vigye a kurzort a cellába. Például: bármelyik cella a 18. sorban.

2. Adja meg az INDEX funkciót (az egyenlő jel előzze meg) és egy nyitó zárójelet, majd jelölje ki (vagy írja be) az alábbi adatbázis / táblázat tartományt: = INDEX (A2: I16

Megjegyzés: Ha abszolút hivatkozást szeretne , ebben az esetben a képlet kemény kódolását jelenti, tehát amikor / ha másolt, a tartomány nem változik), az egyes cellahivatkozások után egyszer nyomja meg az F4 billentyűt. , és a teljes abszolút referencia szimbólumokat adjuk hozzá

3. Ezután írjuk be a vesszőt az argumentumok elválasztásához (vagyis az önálló képletdarabok), majd írjuk be a sorszámot és vesszőt, majd az oszlop számát (igen , a számnak kell lennie, nem pedig a szokásos oszloplevélnek és a jobb oldali zárójelnek (vagy csak nyomja meg az Enter billentyűt, és hagyja, hogy az Excel adja hozzá a záró zárójelet.) A teljes képlet így néz ki: = INDEX ($ A $ 2: $ I $ 16 , 15,2).

Megjegyzés: A sorszámozás a tartomány első számával kezdődik, nem a szám első számán, például az eve n bár a Cavalleria kalózhajó az Excel 16 sorban van, valójában a 15. sor a képletünkben, mert tartományunk A2-ben kezdődik és átmegy az I16-on. Ha az A2 az 1. sor, akkor az A16 a 15. sor)

4. Ne feledje, hogy a Norrington által keresett hajó típusa War Sloop.

JD Sartain

Az index funkció segítségével egyedi információkat találhat az adatbázisban.

B. INDEX tartományok

Most sokkal többet tehetünk az adatbázis táblával. Nem kell újra átírnia a tartományt minden egyes alkalommal, amikor tudni akar valamit. Ennek megkönnyítése érdekében meghatározzuk a tartományt, majd nevezzük el. Ezután a formulák nevét csak a képletekben adhatjuk meg.

1. Menjen az A2-be, és jelölje ki az A2-I16 tartományt.

2. A Formulák lapon válassza a Név megadása lehetőséget a Meghatározott nevek csoportból.

3. A felugró párbeszédpanelen írja be a tartomány nevét a Név mezőbe.

4. Ezután adja meg a Scope (ahol a tartomány található), amely vagy a Munkafüzet vagy a Munkafüzet egyik munkalapja.

5. Írjon be egy megjegyzést, ha szükséges.

6. Végül pedig ellenőrizze, hogy a Témák mezőben megjelenik-e a helyes név és tartomány, majd kattintson a OK gombra.

7. Ha szeretné ellenőrizni, hogy a tartományt valóban mentette-e az Excel-ben, próbálja meg ezt a kis tesztet: Nyomja meg a Ctrl + G (a GoTo parancsot). Válassza a Hajók lehetőséget a GoTo párbeszédablakban, majd kattintson a OK gombra, és az Excel újra kiemeli az A2: I16 tartományt.

A tartomány meghatározása és mentése

C. INDEX SUM & AVERAGE képletekkel

Norrington értékeli a flotta harci képességét. Először azt akarja tudni, hogy hány ágyúnak vannak a kalózok, a hajónkénti ágyúk átlagos száma és a legénységnek az összes kalózhajója. A következő képleteket veszi fel:

1. = SUM (INDEX (Hajók, 8)) egyenlő a 334-gyel, az összes ágyúval és

2-vel. = ÁTLAG (INDEX (Hajók, 8)) 22.27, vagy kb. 22.27 ágyú egy hajónként.

3. = SUM (INDEX (Hajók, 7)) egyenlő 2350-el, az összes hajó legénységének összesített száma.

Miért van vessző, hely, vessző a hajók és a 8-as szám között, és mit jelent ez a szám? A hajók a Range (vesszővel követve), a sor argumentuma üres (vagy szóköz), mert a Norrington minden sorozatot akar, a nyolc pedig a 8. oszlopot mutatja (H oszlop, Cannons). , miért nem írja be az SUM és / vagy az ÁTLAGOS képleteket az oszlopok alján? Ebben a kis táblázatban, igen, ugyanolyan egyszerű lenne. De ha a táblázatban 5000 sor és 300 oszlop van, akkor INDEX

JD Sartain

03 INDEX képleteket kell használni a SUM és az AVERAGE használatával.

Miután megadta a tartomány nevét, a Norrington megnyithatja az üres táblázatot ugyanazt a munkafüzetet, és írja le a B oszlopban szereplő lekérdezéseket (formulák), amelyek leírása az eredményt azokra a lekérdezésekre írja le. (Megjegyzés: A C oszlop a B oszlopban szereplő tényleges képleteket mutatja).

Nem kell vizuálisan látnia az 5000 rekord nagy adatbázisát, vagy várni néhány másodpercig, amíg a képletek kiszámítják. Minden szükséges információt megkaphat a lekérdező lapjából. Ne feledje, hogy minél nagyobb a táblázat, annál lassabban működik, különösen ha sok képlet létezik.

JD Sartain

04 Commodore James Norrington kalózhajó információi / lekérdező lapja.

F. INDEX MATCH MAX

Most Norrington tudni akarja, hogy hány kalóz van a legelterjedtebb hajón, és melyik hajó van? A MAX formulával a INDEX-ot használja a legtöbb kalózhoz, de tudnia kell, melyik hajót hordozza. Tehát a MAX formulával a INDEX / MATCH-ot használja, hogy megtudja, melyik hajó rendelkezik a legtöbb kalózzal a fedélzeten. = MAX (INDEX (Hajók, 7)) egyenlő 300, a kalózok legnagyobb száma az egyik hajón

2. = INDEX ($ A $ 2: $ A $ 16, MATCH (MAX (Hajók), $ G $ 2: G $ 16, 0)) egyenlő a Royal James, a hajó a legtöbb kalózok fedélzetén

3. = INDEX ($ F $ 2: $ F $ 16, MATCH (MAX (hajók), $ G $ 2: G $ 16, 0)) egyenlő Stede Bonnet, a királyi James százados kapitánya 300

JD Sartain

Használja az INDEX-MATCH és a MAX-ot, hogy adatokat gyűjtsön az adatbázisból.