Ved å kombinere ExcelVLOOKUP fungere medKOLONNE funksjon kan vi lage en oppslagsformel som lar deg returnere flere verdier fra en enkelt rad i en database eller et databasetabell.
01 av 10Returner flere verdier med Excel VLOOKUP
Ved å følge trinnene som er oppført nedenfor, opprettes oppslagsmetoden som vises i bildet ovenfor, som returnerer flere verdier fra en enkelt datapost.
Oppsøkelsesformelen krever at KOLONNE funksjonen er nestet innsiden av VLOOKUP. Nesting en funksjon innebærer å skrive inn den andre funksjonen som en av argumentene for den første funksjonen.
I denne opplæringen, KOLONNE funksjonen blir oppgitt som kolonneindeks Nummer argument for VLOOKUP. Det siste trinnet i opplæringen innebærer å kopiere oppslagstabellen til ytterligere kolonner for å hente tilleggsverdier for den valgte delen.
Oppgi opplæringsdataene
Det første trinnet i opplæringen er å skrive inn dataene i et Excel-regneark. For å følge trinnene i opplæringen, skriv inn dataene som vises i bildet ovenfor i følgende celler:
- Skriv inn det øverste spekteret av data i celler D1 til G1.
- Skriv inn det andre området i celler D4 til G10.
Søke kriterier og oppslag formel opprettet under denne opplæringen vil bli inngått rad 2 av regnearket.
Opplæringen inneholder ikke formateringen sett i bildet, men dette påvirker ikke hvordan oppslagsmetoden fungerer. Informasjon om formateringsalternativer som ligner på de som er sett over, er tilgjengelig i denne grunnleggende Excel-formateringsveiledningen.
03 av 10Opprette et navngitt område for datatabellen
Et navngitt område er en enkel måte å referere til en rekke data i en formel. I stedet for å skrive inn cellehenvisninger for dataene, kan du bare skrive navnet på serien.
En annen fordel ved å bruke et navngitt område er at cellehenvisninger for dette området aldri endrer seg selv når formelen kopieres til andre celler i regnearket. Range navn er derfor et alternativ til å bruke absolutt celle referanser for å forhindre feil når du kopierer formler.
Områdenavnet inkluderer ikke overskriftene eller feltnavnene for dataene (rad 4), men bare selve dataene.
- Fremheve celler D5 til G10 i regnearket for å velge dem.
- Klikk på Navn boks plassert ovenfor kolonne A.
- Type Bord inn i det Navn boks.
- trykk Tast inn tast på tastaturet.
- Celler D5 til G10 Nå har navnet på serien Bord. Vi vil bruke navnet på VLOOKUP tabell array argument senere i opplæringen.
Åpne dialogboksen VLOOKUP
Selv om det er mulig å bare skrive vår oppslagsmetode direkte inn i en celle i et regneark, opplever mange det vanskelig å holde syntaksen rett - spesielt for en kompleks formel som den vi bruker i denne opplæringen.
Et alternativ, i dette tilfellet, er å bruke VLOOKUP dialogboks. Nesten alle Excel-funksjonene har en dialogboks som lar deg legge inn hver av funksjonens argumenter på en egen linje.
Tutorial Steps
- Klikk på celle E2 av regnearket - plasseringen der resultatene av den todimensjonale oppsummeringsformelen vil bli vist.
- Klikk på Formulas-fanen av bånd.
- Klikk på Oppslag og referanse alternativ i bånd for å åpne funksjonsmenyen.
- Klikk på VLOOKUP i listen for å åpne funksjonens dialogboks.
Angir søknadsverdien Argument
Normalt er det se opp verdi samsvarer med et felt av data i den første kolonnen i datatabellen. I vårt eksempel er se opp verdi refererer til navnet på maskinvaredelen som vi ønsker å finne informasjon om. De tillatte typer data for se opp verdi er tekstdata, logiske verdier, tall og cellehenvisninger.
Absolutt Cell Referanser
Normalt, når formler kopieres i Excel, endres cellehenvisninger for å reflektere deres nye plassering. Hvis dette skjer, D2, cellehenvisningen for se opp verdi, vil endres ettersom formelen er kopiert, og skaper feil i celler F2 og G2.
Absolutt cellehenvisninger endres ikke når formler kopieres.
For å forhindre feilene, konverterer vi cellehenvisningen D2 inn i en absolutt cellereferanse. Absolutt cellehenvisninger opprettes ved å trykke på F4 tast på tastaturet. Hvis du gjør det, legger du dollarskilt rundt cellehenvisningen som $ D $ 2.
- Klikk på søkeverdi linje i dialogboksen.
- Klikk på celle D2 å legge til denne cellehenvisningen til søkeverdi linje - dette er cellen der vi skal skrive inn delenavnet som vi søker informasjon om
- Uten å flytte innsettingspunktet, trykk på F4 tast på tastaturet for å konvertere D2 inn i den absolutte cellehenvisningen $ D $ 2.
- Forlate VLOOKUP funksjonsdialogboksen åpnes for neste trinn i opplæringen.
Angir argumentet for tabellarray
Tabellen array er datatabellen som oppsøkelsesformelen søker for å finne informasjonen vi vil ha.Tabellarrangementet må inneholde minst to kolonner med data.
Den første kolonnen inneholder oppslagsgumentargumentet (forrige trinn i opplæringen), mens den andre kolonnen vil bli søkt av oppslagstabellen for å finne informasjonen vi spesifiserer.
Tabellargumentargumentet må angis som enten et område som inneholder cellehenvisninger for datatabellen eller som en rekkevidde.
- Klikk på tabell linje i dialogboksen.
- Type Bord for å angi rekkeviddenavnet for dette argumentet.
- Forlate VLOOKUP funksjonsdialogboksen åpnes for neste trinn i opplæringen.
Nestler COLUMN-funksjonen
Normalt VLOOKUP Returnerer bare data fra en kolonne i en datatabell, og denne kolonnen er angitt av kolonneindeksnummer argument.
I dette eksemplet har vi imidlertid tre kolonner som vi ønsker å returnere data fra, så vi trenger en måte å enkelt endre kolonneindeksnummer uten å redigere vår oppslagsformel - dette er hvor KOLONNE funksjonen kommer inn.
Ved å skrive det inn som kolonneindeksnummer argument, det vil endres ettersom oppslagsmetoden er kopiert fra celle D2 til celler E2 og F2 senere i opplæringen.
Nesting Funksjoner
De KOLONNE Funksjonen virker derfor som VLOOKUP's kolonneindeksnummer argument; Dette oppnås ved å neste KOLONNE fungere inne i VLOOKUP i Col_index_num linje i dialogboksen.
Angir COLUMN-funksjonen manuelt
Når nesting fungerer, tillater ikke Excel oss å åpne dialogboksen for den andre funksjonen for å skrive inn sine argumenter. De KOLONNE funksjon må derfor oppgis manuelt i Col_index_num linje. De KOLONNE funksjonen har bare ett argument - den Henvisning argument som er en celle referanse.
Velge COLUMN-funksjonens referansegrunnlag
De KOLONNE Funksjonens jobb er å returnere nummeret til kolonnen gitt som Henvisning argument. Med andre ord konverterer kolonnebrevet til et tall med kolonne A å være den første kolonnen, kolonne B den andre og så videre.
Siden det første datafeltet er det vi vil returnere, prisen på varen, som er i kolonne to av datatabellen. Vi kan velge cellereferansen for en hvilken som helst celle i kolonne B som Henvisning Argument for å få nummeret 2 for Col_index_num argument.
- I VLOOKUP funksjonsdialogboksen, klikk på Col_index_num linje.
- Skriv inn funksjonsnavnet kolonne etterfulgt av en åpne runde brakett.
- Klikk på celle B1 i regnearket for å angi den cellehenvisningen som Henvisning argument.
- Skriv a lukke runde brakett å fullføre KOLONNE funksjon
- Forlate VLOOKUP funksjonsdialogboksen åpnes for neste trinn i opplæringen.
Skriv inn VLOOKUP Range Lookup Argument
VLOOKUP's range_lookup argument er en logisk verdi (EKTE eller FALSK) som indikerer om du vil VLOOKUP for å finne en eksakt eller en omtrentlig kamp til søkeverdi.
- Hvis EKTE eller hvis dette argumentet utelates: VLOOKUP returnerer enten en eksakt samsvar til søkeverdiEller, hvis en eksakt kamp ikke er funnet, VLOOKUP returnerer neste største verdi. For formelen for å gjøre dette, dataene i den første kolonnen av tabell må sorteres i stigende rekkefølge.
- Hvis FALSK: VLOOKUP vil bare bruke en eksakt samsvar til søkeverdi. Hvis det er to eller flere verdier i den første kolonnen av tabell som samsvarer med oppslagsverdien, brukes den første verdien som er funnet. Hvis en eksakt kamp ikke ble funnet, a # N / A feilen returneres.
I denne opplæringen, siden vi leter etter spesifikk informasjon om et bestemt maskinvareelement, vil vi stille inn range_lookup lik Falsk.
- Klikk på range_lookup linje i dialogboksen.
- Skriv ordet Falsk i denne linjen for å indikere at vi vil VLOOKUP å returnere en eksakt kamp for dataene vi søker.
- Klikk OK for å fullføre oppslagstabellen og lukke dialogboksen.
- Siden vi ennå ikke har angitt oppslagskriteriene i celle D2 en # N / A feil vil være til stede i celle E2.
- Denne feilen blir korrigert når vi legger til oppslagskriteriene i det siste trinnet i opplæringen.
Kopierer oppslagsformelen
Oppsøkelsesformelen er ment å hente data fra flere kolonner av datatabellen samtidig. For å gjøre dette må oppslagsformelen ligge i alle feltene som vi ønsker informasjon om.
I denne opplæringen ønsker vi at den skal hente data fra kolonner 2, 3, og 4 av datatabellen - det er prisen, delenummeret og leverandørens navn når vi skriver inn et delvis navn som søkeverdi.
Siden dataene er lagt ut i et vanlig mønster i regnearket, kan vi kopiere oppslagsformelen i celle E2 til celler F2 og G2.
Når formelen er kopiert, vil Excel oppdatere den relative cellehenvisningen i KOLONNE funksjon (celle B1) for å reflektere formelens nye plassering.
I tillegg endrer ikke Excel absolutt cellehenvisning $ D $ 2 og den nevnte rekkevidde Bord som formelen er kopiert.
Det er mer enn én måte å kopiere data i Excel, men sannsynligvis er den enkleste måten å bruke Fyll håndtaket.
- Klikk på celle E2, hvor oppslagsformelen er plassert, for å gjøre den til den aktive cellen.
- Plasser musepekeren over svart firkant i nederste høyre hjørne - pekeren endres til et plustegn for å angi fyllhåndtaket.
- Klikk på venstre museknapp og dra fyllhåndtaket over til celle G2.
- Utgivelse museknappen og celle F3 bør inneholde den todimensjonale oppsummeringsformelen.
- Hvis det gjøres riktig, celler F2 og G2 bør nå også inneholde # N / A feil som er tilstede i celle E2.
Angi oppslagskriteriene
Når oppslagsmetoden har blitt kopiert til de nødvendige cellene, kan den brukes til å hente informasjon fra datatabellen. For å gjøre det, skriv inn Navn på varen du ønsker å hente inn i søkeverdi celle (D2) og trykk på Tast inn tast på tastaturet.
Når det er gjort, må hver celle som inneholder oppslagstabellen inneholde et annet stykke data om maskinvareelementet du søker etter.
- Klikk på celle D2 i regnearket.
- Type Widget inn i celle D2 og trykk på Tast inn tast på tastaturet.
- Følgende informasjon skal vises i celler E2 til G2.
- E2: $ 14,76 - prisen på en widget
- F2: PN-98769 - delenummeret for en widget
- G2: Widgets Inc. - navnet på leverandøren for widgets
- Test VLOOKUP array formel videre ved å skrive navnet på andre deler inn i celle D2 og observere resultatene i celler E2 til G2
