Brug INDEX og MATCH til enkle databaseforespørgsler i Excel

Excel var oprindeligt ikke designet til at være en rigtig database. Dens tidlige databasefunktioner var begrænsede i mængde og kvalitet. Og fordi alle poster i en Excel-database er synlige på skærmen på én gang - hvilket betyder alt i hukommelse på en gang - skal Excel-databaserne være meget små: flere felter med få poster eller få felter med mange poster; og minimale beregninger.

VLOOKUP (vertikal) og HLOOKUP (vandret) var de eneste funktioner, der var tilgængelige for at forespørge en database for specifikke oplysninger. For eksempel kan du søge at finde og udtrække alle poster, der indeholdt salg større end $ 1000, men mindre end $ 5000, men kun på flade filer (kun en database matrix).

Pivot Tables blev udviklet, så brugerne kunne oprette relationelle databaser, som er lettere at søge, bruge mindre hukommelse og give mere nøjagtige resultater. Hvis du ikke har eller har brug for en relationsdatabase, men kræver mere kraftfulde og pålidelige databasefunktioner, prøv disse til en start.

[Yderligere læsning: Din nye pc har brug for disse 15 gratis, fremragende programmer]

Index, Match og Index Match

I Excel returnerer funktionen INDEX et element fra en bestemt position (i en liste, tabel, database).

MATCH-funktionen returnerer positionen af ​​en værdi (i en liste , tabel, database). Og de anvendte INDEX-MATCH-funktioner gør det muligt at udvinde data fra et bord en brise.

Syntaxen for INDEX-funktionen er: INDEX (array, row_num, [column_num]). Arrayet er den række celler, du arbejder med. Row_num er tydeligvis rækkenummeret i det interval, der indeholder de data, du leder efter. Kolonne_numre er kolonnenummeret i det interval, der indeholder de data, du leder efter. INDEX-formlen genkender ikke kolonnebogstaver, så du skal bruge tal (tæller fra venstre).

Syntaksen for MATCH-funktionen er: MATCH (lookup_value, lookup_array, [match_type]). Lookup_value er det tal eller den tekst, du leder efter, hvilket kan være en værdi, en logisk værdi eller en cellehenvisning. Lookup_array er det udvalg af celler, du arbejder med. Match_type bestemmer MATCH-funktionen - det vil sige en nøjagtig kamp eller nærmeste match.

A. INDEX-funktion

I vores eksempel har den berømte Commodore James Norrington et regneark, der sporer alle piratskibene i Caribien. Norringtons liste er arrangeret af skibes kampformationer, der matcher hans nautiske diagrammer af området. Når han ser et skib fremad, går han ind i indeksformlen i regnearket, så han kan identificere skibet og dets kapacitet. I denne første forespørgsel ønsker Norrington at vide, hvilken type skib der er fremme.

1. Vælg en placering (celle eller rækkevidde af celler) for dine forespørgsler (det vil sige funktioner og resultater), og flyt derefter markøren til den pågældende celle. For eksempel: enhver celle på række 18.

2. Indtast INDEX-funktionen (efterfulgt af samme tegn), plus en åbnings parentes, og marker derefter (eller skriv) databasen / tabelområdet som dette: = INDEX (A2: I16

Bemærk: Hvis du vil have en absolut reference betyder i dette tilfælde hardkodning af formlen, så når / hvis den kopieres, ændres rækkevidden ikke), tryk F4 en gang efter hver cellehenvisning. Du kan også markere området: Tryk blot på F4 en gang efter, at du har valgt hele spektret , og de fuldstændige absolutte referencesymboler tilføjes.

3. Indtast derefter et komma for at adskille argumenterne (det vil sige de separate formelstykker), og indtast derefter rækkenummeret og et komma, efterfulgt af kolonne nummeret (ja , det skal være et tal og ikke det sædvanlige kolonnebogstav) og den højre parentes (eller bare tryk på Enter og lad Excel tilføje den endelige parentes for dig). Den komplette formel ser sådan ud: = INDEX ($ A $ 2: $ I $ 16 , 15,2).

Bemærk: Række nummereringen starter med det første nummer i rækken, ikke det første nummer på regnearket. Eksempelvis eve n selvom Cavalleria piratskibet er på Excel række 16, er det faktisk række 15 i vores formel, fordi vores sortiment begynder på A2 og går gennem I16. Hvis A2 er række 1, er A16 række 15).

4. Bemærk, at skibet Norrington var på udkig efter, er en War Sloop.

JD Sartain

Brug indeksfunktionen til at finde specifikke oplysninger i din database.

B. INDEX-serien

Nu kan vi gøre meget mere med denne database tabel. Du behøver ikke omdefinere rækkevidden hver gang du vil vide noget. For at gøre det nemt definerer vi rækkevidden en gang og så navngiver den. Så kan vi blot angive rækkevidden i vores formler.

1. Gå til A2 og fremhæv området A2 til I16.

2. Fra fanen Formler skal du vælge Definer navn fra gruppen Definerede navne.

3. I popup-dialogboksen skal du indtaste et navn for dit interval i feltet Navnfelt.

4. Indtast derefter Scope (hvor rækkevidden er placeret), som er enten Arbejdsbogen eller et af regnearkene i Arbejdsbogen.

5. Indtast en kommentar, hvis det er nødvendigt.

6. Og senest kontroller, at feltet Refers To viser det korrekte navn og interval, og klik derefter på OK .

7. Hvis du vil bekræfte, at dit interval faktisk gemmes i Excel, skal du prøve denne lille test: Tryk på Ctrl + G (GoTo-kommandoen). Vælg Ships i GoTo-dialogboksen, og klik derefter på OK , og Excel fremhæver område A2: I16.

Sådan defineres og gemmes en rækkevidde

C. INDEX med SUM & AVERAGE formler

Norrington vurderer flådens kampmuligheder. Først vil han vide, hvor mange kanoner piraterne har, det gennemsnitlige antal kanoner pr. Skib og det samlede antal besætningsmedlemmer, der bemandner alle disse piratskibe. Han indtaster følgende formler:

1. = SUM (INDEX (Ships, 8)) svarer til 334, det samlede antal kanoner og

2. = AVERAGE (INDEX (Ships, 8)) svarer til 22,27 eller ca. 22,27 kanoner pr. Skib.

3. = SUM (INDEX (Ships, 7)) svarer til 2350, det samlede antal besætninger på alle skibe.

Hvorfor er der et komma, mellemrum, komma mellem skibe og nummer 8, og hvad betyder disse tal? Skibe er området (efterfulgt af et komma), Row-argumentet er tomt (eller et mellemrum) fordi Norrington ønsker alle rækker, og 8 repræsenterer den 8. kolonne over (som er kolonne H, kanoner).

Nogle kan spørge , hvorfor ikke bare indtaste SUM og / eller GEMMEL formlerne nederst i disse kolonner? I dette lille regneark, ja, det ville være lige så nemt. Men hvis regnearket har 5000 rækker og 300 kolonner, vil du gerne bruge INDEX

JD Sartain

03 INDEX-formler ved hjælp af SUM og AVERAGE.

Når rækkevidden er opkaldt, kan Norrington åbne et tomt regneark i denne samme arbejdsbog og skriv sine forespørgsler (formler) i kolonne B (som viser resultaterne i stedet for formlerne) med en beskrivelse, der definerer disse forespørgsler i kolonne A. (Bemærk: Kolonne C viser de egentlige formler, der findes i kolonne B).

Han behøver ikke visuelt at se sin enorme database med 5000 poster eller vente i flere sekunder, mens formlerne beregner. Han kan få alle de oplysninger, han har brug for på sit forespørgselsark. Husk, jo større regnearket, jo langsommere fungerer det, især hvis der er mange formler.

JD Sartain

04 Commodore James Norringtons piratskibsinformation / forespørgselsark.

D. INDEX MATCH med MAX

Nu ønsker Norrington at vide, hvor mange pirater der er på det mest befolket skib, og hvilket skib er det? Han bruger INDEX med MAX formel for at få det højeste antal pirater, men han skal også vide, hvilket skib der bærer dem. Så bruger han INDEX / MATCH med MAX formel for at finde ud af, hvilket skib der har flest pirater ombord.

1. = MAX (INDEX (Ships, 7)) svarer til 300, det højeste antal pirater på et af skibene

2. = INDEX ($ A $ 2: $ A $ 16, MATCH (MAX (Ships), $ G $ 2: G $ 16, 0)) svarer til Royal James, skibet med de fleste pirater ombord

3. = INDEX ($ F $ 2: $ F $ 16, MATCH (MAX (Ships), $ G $ 2: G $ 16, 0)) svarer til Stede Bonnet, kaptajn for Royal James med et piratbesætningsmedlem på 300

JD Sartain

Brug INDEX-MATCH og MAX til at hente specifik info fra din database.