Bruk INDEX og MATCH for enkle databasespørsmål i Excel

Excel ble ikke utformet for å være en ekte database. Dens tidlige databasefunksjoner var begrenset i kvantitet og kvalitet. Og fordi alle poster i en Excel-database er synlige på skjermen samtidig - noe som betyr alt i minnet samtidig - Excel-databasene måtte være veldig små: flere felt med få poster eller få felt med mange poster; og minimale beregninger.

VLOOKUP (vertikal) og HLOOKUP (horisontal) var de eneste funksjonene som er tilgjengelige for å spørre en database for spesifikk informasjon. For eksempel kan du spørre om å finne og trekke ut alle poster som inneholdt salg større enn $ 1000, men mindre enn $ 5000, men bare på flate filer (bare en databasematrise).

Pivottabeller ble utviklet slik at brukerne kunne opprette relasjonsdatabaser, som er lettere å spørre, bruke mindre minne, og gi mer nøyaktige resultater. Hvis du imidlertid ikke har eller trenger en relasjonsdatabase, men krever mer kraftige og pålitelige databasefunksjoner, kan du prøve disse for en start.

[Videre lesing: Den nye PCen trenger disse 15 gratis, gode programmene]

Indeks, Match og Index-kamp

I Excel returnerer INDEX-funksjonen et element fra en bestemt posisjon (i en liste, tabell, database).

MATCH-funksjonen returnerer posisjonen til en verdi (i en liste , tabell, database). Og de INDEX-MATCH-funksjonene som brukes sammen, trekker data fra en tabell til en bris.

Syntaxen for INDEX-funksjonen er: INDEX (array, row_num, [column_num]). Arrayet er rekkevidden av celler som du jobber med. Row_num er tydeligvis radnummeret i området som inneholder dataene du leter etter. Kolonne nummer er kolonnnummeret i området som inneholder dataene du leter etter. INDEX-formelen gjenkjenner ikke kolonnebokstaver, så du må bruke tall (teller fra venstre).

Syntaxen for MATCH-funksjonen er: MATCH (lookup_value, lookup_array, [match_type]). Lookup_value er nummeret eller teksten du leter etter, som kan være en verdi, en logisk verdi eller en cellehenvisning. Lookup_array er serien av celler du jobber med. Match_type bestemmer MATCH-funksjonen, det vil si en nøyaktig kamp eller nærmeste kamp.

A. INDEX-funksjonen

I vårt eksempel har den berømte Commodore James Norrington et regneark som sporer alle piratskipene i Karibia. Norringtons liste er arrangert av skipets kampformasjoner, som matcher hans nautiske kart over området. Når han ser et fartøy fremover, går han inn i indeksformelen i regnearket, slik at han kan identifisere skipet og dets kapasiteter. I dette første spørsmålet vil Norrington vite hvilken type skip som går fremover.

1. Velg et sted (celle eller rekkevidde av celler) for dine spørsmål (det vil si funksjoner og resultater), og flytt deretter markøren til den cellen. For eksempel: hvilken som helst celle på rad 18.

2. Skriv inn INDEX-funksjonen (etterfulgt av likestegnet), pluss en åpnings parentes, og merk deretter (eller skriv) databasen / tabellområdet slik: = INDEX (A2: I16

Merk: Hvis du vil ha en absolutt referanse , i dette tilfellet betyr hardkoding formelen, så når / hvis den kopieres, blir ikke rekkevidden endret), trykk F4 en gang etter hver cellereferanse. Du kan også markere rekkevidde: Bare trykk F4 en gang etter at du har valgt hele spekteret , og de komplette absolutte referansesymbolene blir lagt til.

3. Skriv deretter inn et komma for å skille argumenterne (det vil si de separate formelbitene), og skriv deretter inn radnummeret og et komma, etterfulgt av kolonnnummeret (ja , det må være et tall og ikke det vanlige kolonnebokstaven) og høyre parentes (eller bare trykk Enter og la Excel legge til slutt parentesen for deg). Den komplette formelen ser slik ut: = INDEX ($ A $ 2: $ I $ 16 , 15,2).

Merk: Rå nummereringen starter med det første nummeret i området, ikke det første nummeret på regnearket. For eksempel, eve n selv om Cavalleria-piratskipet er på Excel-rad 16, er det faktisk rad 15 i vår formel fordi vårt sortiment begynner på A2 og går gjennom I16. Hvis A2 er rad 1, er A16 rad 15).

4. Merk at typen skip Norrington var på utkikk etter, er en krigsdrepp.

JD Sartain

Bruk indeksfunksjonen til å finne spesifikk informasjon i databasen.

B. INDEX-serien

Nå kan vi gjøre mye mer med denne databasetabellen. Du trenger ikke å omdefinere rekkevidden hver gang du vil vite noe. For å gjøre det enkelt, definerer vi rekkefølgen en gang og så navngir den. Da kan vi bare sette navnet på serien i våre formler.

1. Gå til A2 og markere området A2 til og med I16.

2. Fra Formulas-fanen velger du Definer navn fra gruppen Definert navn.

3. I popup-dialogboksen skriver du inn et navn for rekkevidde i feltet Navnfelt.

4. Deretter skriver du inn Omfanget (hvor området ligger), som er enten arbeidsboken eller et av regnearkene i arbeidsboken.

5. Skriv inn en kommentar, hvis nødvendig.

6. Og sist bekreft at feltet Refers To viser riktig navn og område, og klikk deretter OK .

7. Hvis du vil bekrefte at rekkevidden din faktisk er lagret i Excel, prøv denne lille testen: Trykk Ctrl + G (GoTo-kommandoen). Velg Skip i GoTo-dialogboksen, klikk deretter OK , og Excel gjenspeiler rekkevidden A2: I16.

Slik definerer og lagrer du et område

C. INDEX med SUM & AVERAGE formler

Norrington vurderer flåtenes kampfunksjoner. Først vil han vite hvor mange kanoner piratene har, gjennomsnittlig antall kanoner per skip, og totalt antall mannskap bemanner alle piratskipene. Han skriver inn følgende formler:

1. = SUM (INDEX (Ships, 8)) tilsvarer 334, totalt antall kanoner og

2. = AVERAGE (INDEX (Skip, 8)) tilsvarer 22,27, eller ca 22,27 kanoner per skip.

3. = SUM (INDEX (Skip, 7)) tilsvarer 2350, totalt antall besetninger på alle skip.

Hvorfor er det et komma, mellomrom, komma mellom skip og nummer 8, og hva betyr disse tallene? Skip er Range (etterfulgt av et komma), Row-argumentet er tomt (eller et mellomrom) fordi Norrington ønsker alle rader, og 8 representerer 8. kolonne over (som er kolonne H, Kanoner).

Noen kan spørre , hvorfor ikke bare skrive inn SUM og / eller AVERAGE formlene nederst i disse kolonnene? I dette lille regnearket, ja, det ville være like enkelt. Men hvis regnearket har 5000 rader og 300 kolonner, vil du bruke INDEX

JD Sartain

03 INDEX-formler med SUM og AVERAGE.

Når rekkevidden er oppkalt, kan Norrington åpne et tomt regneark i denne samme arbeidsboken og skriv sine spørsmål (formler) i kolonne B (som viser resultatene i stedet for formlene) med en beskrivelse som definerer disse spørsmålene i kolonne A. (Merk: Kolonne C viser de faktiske formlene som er i kolonne B).

Han trenger ikke visuelt å se sin enorme database med 5000 poster eller vent i flere sekunder mens formlene beregner. Han kan få all den informasjonen han trenger fra spørreskjemaet. Husk, jo større regnearket, jo tregere fungerer det, spesielt hvis det finnes mange formler.

JD Sartain

04 Commodore James Norringtons piratskipinformasjon / spørringsark.

D. INDEX MATCH med MAX

Nå ønsker Norrington å vite hvor mange pirater er på det mest befolket skipet, og hvilket skip er det? Han bruker INDEX med MAX-formelen for å få det høyeste antallet pirater, men han trenger også å vite hvilket skip som bærer dem. Så bruker han INDEX / MATCH med MAX-formelen for å finne ut hvilket skip som har flest pirater om bord.

1. = MAX (INDEX (Skip, 7)) er 300, det høyeste antallet pirater på ett av skipene

2. = INDEX ($ A $ 2: $ A $ 16, MATCH (MAX (Skip), $ G $ 2: G $ 16, 0)) er lik Royal James, skipet med de fleste pirater ombord

3. = INDEX ($ F $ 2: $ F $ 16, MATCH (MAX (Skip), $ G $ 2: G $ 16, 0)) er lik Stede Bonnet, kaptein av Royal James med et piratbesetning på 300

JD Sartain

Bruk INDEX-MATCH og MAX for å hente spesifikk informasjon fra databasen.