Video: Opslagsfunktionen Vlookup i Excel 2024
Den mest populære af opslagsfunktionerne i Excel 2013 er HLOOKUP (til Horizontal Lookup) og VLOOKUP (til Vertical Lookup) funktioner. Disse funktioner er placeret i rullemenuen Opslag og reference på fanebladet Formler i båndet samt i opslag og referencekategorien i dialogboksen Indsæt funktion.
De er en del af en kraftfuld gruppe af funktioner, der kan returnere værdier ved at se dem op i datatabeller.
VLOOKUP-funktionen søger lodret (fra top til bund) den øverste kolonne i et opslagstabell, indtil programmet lokaliserer en værdi, der svarer til eller overstiger den, du ser op. HLOOKUP-funktionen søger vandret (fra venstre mod højre) den øverste række i et opslagstabell, indtil den finder en værdi, der svarer til eller overstiger den, du ser op.
VLOOKUP-funktionen bruger følgende syntaks:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Funktionen HLOOKUP følger den næsten identiske syntaks:
HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup]) > I begge funktioner er argumentet
lookup_value den værdi, du vil se op i opslagstabellen, og table_array er celleintervallet eller navnet på opslagstabellen, der indeholder både værdi for at slå op og den relaterede værdi at returnere.
col_index_num i VLOOKUP-funktionen er nummeret på kolonnen, hvis værdier sammenlignes med argumentet lookup_value i et lodret bord. Argumentet row_index_num i HLOOKUP-funktionen er nummeret på rækken, hvis værdier sammenlignes med lookup_value i et vandret bord. Når du indtaster
col_index_num eller row_index_num argumenterne i VLOOKUP og HLOOKUP-funktionerne, skal du indtaste en værdi større end nul, der ikke overstiger det samlede antal kolonner eller rækker i Opslagstabel. Argumentet valgfri
range_lookup i både VLOOKUP og HLOOKUP-funktionerne er den logiske SAND eller FALSE, der angiver, om Excel skal finde en nøjagtig eller omtrentlig match for lookup_value i table_array. Når du angiver TRUE eller udelad argumentet
range_lookup i VLOOKUP eller HLOOKUP-funktionen, finder Excel en omtrentlig kamp. Når du angiver FALSE som argumentet range_lookup , finder Excel kun eksakte kampe. Find omtrentlige kampe gælder kun, når du kigger op på numeriske poster (i stedet for tekst) i den første kolonne eller række i lodret eller vandret opslagstabellen.Når Excel ikke finder et nøjagtigt match i denne kolonne eller række opslag, finder den den næsthøjeste værdi, der ikke overstiger argumentet
lookup_value , og returnerer derefter værdien i kolonnen eller rækken angivet af < col_index_num eller row_index_num argumenter. Når du bruger VLOOKUP og HLOOKUP-funktionerne, skal teksten eller numeriske indtastninger i kolonnen Kolonne eller række (det vil sige den øverste kolonne i et lodret opslagstabell eller den øverste række i et vandret opslagstabel) være unikt. Disse poster skal også arrangeres eller sorteres i stigende rækkefølge; det vil sige alfabetisk rækkefølge for tekstindtastninger og laveste til højeste rækkefølge for numeriske poster. Figuren viser et eksempel på at bruge VLOOKUP-funktionen til at returnere enten et 15% eller 20% tip fra et tipbord, afhængigt af checksummets pristaks-total. Celle F3 indeholder VLOOKUP-funktionen:
= VLOOKUP (Pretax_Total, Tip_Table, IF (Tip_Percentage = 0, 15, 2, 3))
Denne formel returnerer tipets størrelse baseret på tipprocenten i celle F1 og præaksaksbeløbet for kontrollen i celle F2.
For at bruge dette tipbord skal du indtaste procentdelen af spidsen (15% eller 20%) i celle F1 (ved navn Tip_Percentage) og mængden af tjekken før skat i celle F2 (navngivet Pretax_Total). Excel ser derefter op på den værdi, du indtaster i Pretax_Total cellen i den første kolonne i opslagstabellen, som indeholder celleområdet A2: C101 og hedder Tip_Table.
Excel flytter derefter værdierne i den første kolonne af Tip_Table, indtil den finder en kamp, hvorefter programmet bruger argumentet
col_index_num
i VLOOKUP-funktionen til at bestemme hvilket tip beløb fra den pågældende række i tabellen til vende tilbage til celle F3. Hvis Excel finder ud af, at værdien, der er indtastet i Pretax_Total-cellen ($ 16.50 i dette eksempel), ikke nøjagtigt svarer til en af værdierne i den første kolonne af Tip_Table, fortsætter programmet med at søge ned sammenligningsområdet indtil det møder den første værdi, der overstiger pretax-totalen (17,00 i celle A19 i dette eksempel). Excel flytter derefter tilbage til den forrige række i tabellen og returnerer værdien i kolonnen, der matcher argumentet
col_index_num
i VLOOKUP-funktionen. (Dette skyldes, at det valgfrie range_lookup -argument er udeladt fra funktionen.) Bemærk, at tiptabellen eksempel bruger en IF-funktion til at bestemme argumentet col_index_num
for VLOOKUP-funktionen i celle F3. IF-funktionen bestemmer det kolonnetal, der skal bruges i tipbordet, ved at matche den procentdel, der indtastes i Tip_Percentage (celle F1) med 0. 15. Hvis de matcher, returnerer funktionen 2 som col_index_num
argument og VLOOKUP-funktionen returnerer en værdi fra den anden kolonne (15% kolonne B) i Tip_Table-rækken. Ellers returnerer IF-funktionen 3 som argumentet col_index_num, og VLOOKUP-funktionen returnerer en værdi fra den tredje kolonne (20% kolonnen C) i Tip_Table-området. Følgende figur viser et eksempel, der bruger HLOOKUP-funktionen til at finde op på prisen for hvert bagværk, der er gemt i en separat prisopslagstabel, og derefter returnere denne pris til kolonnen Price / Doz i daglige salgslisten.Celle F3 indeholder den oprindelige formel med HLOOKUP-funktionen, som derefter kopieres ned i kolonne F: = HLOOKUP (item, Price_table, 2, FALSE)
I denne HLOOKUP-funktion er det områdenavn, der er angivet i kolonnen Item I området C3: C62 defineres som argumentet
lookup_value
og celleintervallets navn Pris tabel, der er givet til celleområdet I1: M2 er argumentet table_array . Argumentet row_index_num
er 2 fordi du vil have Excel til at returnere priserne i anden række i tabellen Prisopslag, og det valgfrie range_lookup argument er FALSK, fordi varebetegnelsen i Daglig salgsfortegnelse skal matche nøjagtigt varebetegnelsen i tabellen Prisopslag. Ved at have HLOOKUP-funktionen bruger pristabellen til at indlæse prisen pr. Dusin for hvert bagværksvarer i daglig salgslisten, gør du det meget enkelt at opdatere et hvilket som helst af salget på listen. Alt du skal gøre er at ændre sin pris / Doz-pris i dette område, og HLOOKUP-funktionen opdaterer straks den nye pris i den daglige salgsliste, hvor varen sælges.
