Ved at kombinere Excels VLOOKUP-funktion med COLUMN-funktionen kan du oprette en opslagsformel, der returnerer flere værdier fra en enkelt række i en database eller tabel med data. Lær, hvordan du opretter en opslagsformel, der returnerer flere værdier fra en enkelt datapost.
Instruktionerne i denne artikel gælder for Excel 2019, 2016, 2013, 2010; og Excel til Microsoft 365.
Bundlinje
Opslagsformlen kræver, at COLUMN-funktionen er indlejret inde i VLOOKUP. Indlejring af en funktion involverer at indtaste den anden funktion som et af argumenterne for den første funktion.
Indtast selvstudiedata
I denne øvelse er funktionen COLUMN indtastet som kolonneindeksnummerargumentet for VLOOKUP. Det sidste trin i selvstudiet involverer kopiering af opslagsformlen til yderligere kolonner for at hente yderligere værdier for den valgte del.
Det første trin i denne øvelse er at indtaste dataene i et Excel-regneark. For at følge trinene i denne vejledning skal du indtaste dataene vist på billedet nedenfor i følgende celler:
- Indtast det øverste dataområde i cellerne D1 til G1.
- Indtast det andet område i cellerne D4 til G10.
Søgekriterierne og opslagsformlen, der er oprettet i denne øvelse, er indtastet i række 2 i regnearket.
Denne vejledning inkluderer ikke den grundlæggende Excel-formatering vist på billedet, men dette påvirker ikke, hvordan opslagsformlen fungerer.
Opret et navngivet område til datatabellen
Et navngivet område er en nem måde at henvise til en række data i en formel. I stedet for at skrive cellereferencerne for data, skal du skrive navnet på området.
En anden fordel ved at bruge et navngivet område er, at cellereferencerne for dette område aldrig ændres, selv når formlen kopieres til andre celler i regnearket. Områdenavne er et alternativ til at bruge absolutte cellereferencer for at forhindre fejl ved kopiering af formler.
Områdenavnet inkluderer ikke overskrifter eller feltnavne for dataene (som vist i række 4), kun dataene.
-
Fremhæv celler D5 til G10 i regnearket.
Image -
Placer markøren i navnefeltet over kolonne A, skriv Tabel, og tryk derefter på Enter. Celler D5 til G10 har områdenavnet Table.
Image - Rækkenavnet for VLOOKUP tabelmatrixargumentet bruges senere i denne øvelse.
Åbn VLOOKUP-dialogboksen
Selv om det er muligt at skrive opslagsformlen direkte ind i en celle i et regneark, har mange mennesker svært ved at holde syntaksen ved lige - især for en kompleks formel som den, der bruges i denne øvelse.
Som et alternativ kan du bruge dialogboksen VLOOKUP funktionsargumenter. Næsten alle Excels funktioner har en dialogboks, hvor hvert af funktionens argumenter indtastes på en separat linje.
-
Vælg celle E2 i regnearket. Dette er stedet, hvor resultaterne af den todimensionelle opslagsformel vises.
Image -
På båndet skal du gå til fanen Formulas og vælge Opslag og reference.
Image -
Vælg VLOOKUP for at åbne Function Arguments dialogboksen.
Image - Dialogboksen Funktionsargumenter er det sted, hvor parametrene for VLOOKUP-funktionen indtastes.
Indtast opslagsværdiargumentet
Norm alt matcher opslagsværdien et datafelt i den første kolonne i datatabellen. I dette eksempel refererer opslagsværdien til navnet på den del, som du ønsker at finde information. De tilladte datatyper for opslagsværdien er tekstdata, logiske værdier, tal og cellereferencer.
Absolute cellereferencer
Når formler kopieres i Excel, ændres cellereferencer for at afspejle den nye placering. Hvis dette sker, ændres D2, cellereferencen for opslagsværdien, og skaber fejl i cellerne F2 og G2.
Absolutte cellereferencer ændres ikke, når formler kopieres.
For at forhindre fejlene skal du konvertere cellereferencen D2 til en absolut cellereference. For at oprette en absolut cellereference skal du trykke på F4-tasten. Dette tilføjer dollartegn omkring cellereferencen, såsom $D$2.
-
I dialogboksen Funktionsargumenter skal du placere markøren i tekstboksen lookup_value. Vælg derefter celle D2 i regnearket for at tilføje denne cellereference til lookup_value. Celle D2 er hvor delens navn vil blive indtastet.
Image -
Uden at flytte indsætningspunktet, tryk på F4 tasten for at konvertere D2 til den absolutte cellereference $D$2.
Image - Lad VOPSLAG-funktionsdialogboksen være åben for det næste trin i selvstudiet.
Indtast tabel Array-argumentet
Et tabelarray er den tabel med data, som opslagsformlen søger for at finde den information, du ønsker. Tabellarrayet skal indeholde mindst to kolonner med data.
Den første kolonne indeholder opslagsværdiargumentet (som blev sat op i det forrige afsnit), mens den anden kolonne søges af opslagsformlen for at finde den information, du angiver.
Tabelargumentet skal indtastes som enten et område, der indeholder cellereferencerne for datatabellen, eller som et områdenavn.
For at tilføje tabellen med data til VLOOKUP-funktionen skal du placere markøren i table_array tekstboksen i dialogboksen og skrive Tablefor at indtaste områdenavnet for dette argument.
Nest the COLUMN-funktionen
Norm alt returnerer VLOOKUP kun data fra én kolonne i en datatabel. Denne kolonne er angivet af kolonneindeksnummerargumentet. I dette eksempel er der dog tre kolonner, og kolonneindeksnummeret skal ændres uden at redigere opslagsformlen. For at opnå dette skal du indlejre funktionen COLUMN inde i VLOOKUP-funktionen som argumentet Col_index_num.
Når der indlejres funktioner, åbner Excel ikke den anden funktions dialogboks for at indtaste dens argumenter. KOLONNE-funktionen skal indtastes manuelt. Funktionen COLUMN har kun ét argument, Reference-argumentet, som er en cellereference.
Funktionen COLUMN returnerer nummeret på den kolonne, der er angivet som referenceargumentet. Det konverterer kolonnebogstavet til et tal.
For at finde prisen på en vare, brug dataene i kolonne 2 i datatabellen. Dette eksempel bruger kolonne B som reference til at indsætte 2 i Col_index_num argumentet.
-
I dialogboksen Function Arguments skal du placere markøren i tekstboksen Col_index_num og skrive COLUMN(. (Sørg for at inkludere den åbne runde beslag.)
Image -
I regnearket skal du vælge celle B1 for at indtaste denne cellereference som referenceargument.
Image - Skriv en lukkende runde parentes for at fuldføre funktionen COLUMN.
Indtast VLOOKUP Range Lookup-argument
VLOOKUPs Range_lookup-argument er en logisk værdi (TRUE eller FALSE), der angiver, om VLOOKUP skal finde et eksakt eller omtrentligt match til Lookup_value.
- TRUE eller udeladt: VLOOKUP returnerer et tæt match til Lookup_value. Hvis der ikke findes et nøjagtigt match, returnerer VLOOKUP den næststørste værdi. Dataene i den første kolonne i Table_array skal sorteres i stigende rækkefølge.
- FALSE: VLOOKUP bruger et eksakt match til Lookup_value. Hvis der er to eller flere værdier i den første kolonne i Table_array, der matcher opslagsværdien, bruges den første fundne værdi. Hvis der ikke findes et nøjagtigt match, returneres en N/A-fejl.
I dette selvstudie vil der blive slået op på specifikke oplysninger om et bestemt hardwareelement, så Range_lookup er indstillet til FALSE.
I dialogboksen Funktionsargumenter skal du placere markøren i tekstfeltet Range_lookup og skrive False for at bede VLOOKUP returnere en nøjagtig match for dataene.
Vælg OK for at fuldføre opslagsformlen og lukke dialogboksen. Celle E2 vil indeholde en N/A fejl, fordi opslagskriterierne ikke er blevet indtastet i celle D2. Denne fejl er midlertidig. Det vil blive rettet, når opslagskriterierne tilføjes i det sidste trin i denne øvelse.
Kopiér opslagsformlen, og indtast kriterier
Opslagsformlen henter data fra flere kolonner i datatabellen på én gang. For at gøre dette skal opslagsformlen findes i alle de felter, hvorfra du ønsker information.
For at hente data fra kolonne 2, 3 og 4 i datatabellen (prisen, varenummeret og leverandørens navn), skal du indtaste et delvist navn som Lookup_value.
Da dataene er lagt ud i et almindeligt mønster i regnearket, skal du kopiere opslagsformlen i celle E2 til celler F2 og G2 Efterhånden som formlen kopieres, opdaterer Excel den relative cellereference i funktionen COLUMN (celle B1) for at afspejle formlens nye placering. Excel ændrer ikke den absolutte cellereference (såsom $D$2) og det navngivne område (tabel), når formlen kopieres.
Der er mere end én måde at kopiere data i Excel, men den nemmeste måde er at bruge Fill Handle.
-
Vælg celle E2, hvor opslagsformlen er placeret, for at gøre den til den aktive celle.
Image -
Træk påfyldningshåndtaget hen over til celle G2. Celler F2 og G2 viser N/A-fejlen, der er til stede i celle E2.
Image -
For at bruge opslagsformlerne til at hente information fra datatabellen skal du i regnearket vælge celle D2, skrive Widget, og trykke på Enter.
Image Følgende information vises i cellerne E2 til G2.
- E2: $14,76 - prisen på en widget
- F2: PN-98769 - varenummeret for en widget
- G2: Widgets Inc. - navnet på leverandøren af widgets
-
For at teste VLOOKUP-matrixformlen skal du indtaste navnet på andre dele i celle D2 og observere resultaterne i cellerne E2 til G2.
Image - Hver celle, der indeholder opslagsformlen, indeholder forskellige data om det hardwareelement, du søgte efter.
VLOOKUP-funktionen med indlejrede funktioner som COLUMN giver en effektiv metode til at slå data op inde i en tabel ved at bruge andre data som opslagsreference.
