Vi kender alle den berømte stjerne i Excel -funktioner VLOOKUP. Det bruges ofte til at slå værdier op med et unikt id. Men dette er ikke den eneste funktion, der kan bruges til at slå værdier op i Excel. Der er mange andre funktioner og formler, der kan bruges til at slå værdi op. I denne artikel vil jeg introducere dig med alle disse Excel -opslagsfunktioner og formler. Nogle er endda bedre end VLOOKUP -funktionen i Excel. Så læs til sidst.
1. Funktionen VLOOKUP i Excel
Den første excel -opslagsfunktion er naturligvis VLOOKUP -funktionen. Denne funktion er berømt af en grund. Vi kan bruge denne funktion til at gøre mere end bare et opslag. Men den grundlæggende opgave for denne funktion er at slå værdier i tabellen op, fra venstre mod højre.
Syntaks for VLOOKUP -funktion:
| = OPLYSNING (opslagsværdi, tabel_array, col_index_number, [range_lookup]) |
Opslag_værdi: Den værdi, som du vil søge efter i den første kolonne i Table Array.
Table_array: Tabellen, hvor du vil slå op/søge
col_index_number: Kolonnetallet i tabel -array, hvorfra du vil hente resultater.
[range_lookup]: FALSKT, hvis du vil søge efter den nøjagtige værdi, SAND, hvis du vil have et omtrentligt match.
Fordele ved VLOOKUP:
- Let at bruge.
- Hurtig
- Flere anvendelser
- Bedst til at slå værdier op i lodret rækkefølge.
Ulemper:
- Det bruges kun til lodret opslag
- Det returnerer kun den første matchede værdi.
- Statisk indtil den bruges med MATCH -funktionen.
- Værdier fra opslagværdien til venstre kan ikke slås op.
Du kan læse mere om denne Excel -opslagsformel her.
2. Excel HLOOKUP -funktionen
HLOOKUP -funktionen er den manglende del af VLOOKUP -funktionen. HLOOKUP -funktionen bruges til at søge værdier vandret. Med andre ord, når du vil slå en værdi op i Excel ved at matche værdi i kolonner og få værdier fra rækker, så bruger vi HLOOKUP -funktionen. Dette er præcis det passende for VLOOKUP -funktionen.
Syntaks for HLOOKUP
=HLOOKUP(opslagsværdi, tabel array, række indeks nummer, [område_opslag])
- opslagsværdi: Den værdi, du leder efter.
- Tabelarray: Tabellen, hvor du leder efter værdien.
- Rækkeindeksnummer: Rækketallet i tabellen, som du vil hente data fra.
- [range_lookup]: det er matchtypen. 0 for det nøjagtige match og 1 for omtrentlig kamp.
Fordele ved HLOOKUP -funktion:
- Det kan søge værdier vandret.
- Let at bruge.
- Flere anvendelser
- Hurtig
Ulemper:
- Det bruges kun til vandret opslag
- Det returnerer kun den første matchede værdi.
- Statisk indtil den bruges med MATCH -funktionen.
- Værdier over opslagsværdierne i tabellen kan ikke slås op.
Du kan lære mere om denne Excel -opslagsfunktion her.
3. INDEX-MATCH opslagsformlen
Hvor VLOOKUP og HLOOKUP ikke kan nå, kan denne formel nå. Dette er den bedste opslagsformel i Excel indtil Excel 2016 (XLOOKUP er på vej).
Den generiske formel for INDEX MATCH
= INDEX (Resultatområde, MATCH (opslagsværdi, opslagsområde, 0))
Result_Range: Det er intervallet, hvorfra du vil hente værdi.
Opslag_værdi: Det er den værdi, du vil matche.
Opslag_Range:Det er et område, hvor du vil matche opslagsværdien.
Fordele ved INDEX-MATCH opslagsformel:
- Kan slå op i fire retninger. Det kan slå værdier til venstre og op af opslagsværdien.
- Dynamisk.
- Ingen grund til at definere række- eller kolonneindekset.
Ulemper:
- Det kan være svært for nye brugere.
- Bruger to Excel -funktioner i kombination. Brugere skal forstå, hvordan funktionen INDEX og MATCH fungerer.
Du kan lære mere om denne formel her.
4: Excel OFFSET-MATCH opslagsformel
Dette er en anden formel, der kan bruges til at slå værdier dynamisk op. Denne Excel -opslagsformel bruger OFFSET -funktionen som ankerfunktion og MATCH som en føderfunktion. Ved hjælp af denne formel kan vi dynamisk hente værdier fra en tabel ved at slå op i rækker og kolonner.
Generisk formel,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
StartCell:Dette er startcellen i opslagstabellen. Lad os sige, at hvis du vil slå op i område A2: A10, så er StartCellen A1.
RowLookupValue: Dette er den opslagsværdi, du vil finde i rækker underStartCell.
RowLookupRange:Dette er det område, hvor du vil slå RowLookupValue op. Det er området nedenfor StartCell (A2: A10).
ColLookupVærdi: Dette er den opslagsværdi, du vil finde i kolonner (overskrifter).
ColLookupRange:Dette er det område, hvor du vil slå ColLookupValue op. Det er området på højre side af StartCell (som B1: D1).
Fordele ved denne Excel -opslagsteknik:
- Hurtig
- Kan søge vandret og lodret.
- Dynamisk
Ulemper:
- Kompleks for nogle mennesker.
- Har brug for at forstå funktionen af OFFSET -funktionen og MATCH -funktionen.
Du kan lære mere om denne opslagsformel her i detaljer.
5: Excel LOOKUP -formel Flere værdier
Alle ovenstående opslagsformler returnerer den første fundne værdi fra arrayet. Hvis der er mere end en kamp, returnerer de ikke andre kampe. I så fald kommer denne formel til handling for at redde dagen. Denne formel returnerer alle de matchede værdier fra listen i stedet for kun det første match.
Denne formel bruger INDEX, ROW og IF -funktioner som hovedfunktioner. IFERROR -funktionen kan valgfrit bruges til at håndtere fejl.
Generisk formel
{= INDEX (array, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (first cell of lookup_value_range) +1), ROW (1: 1))))}
Array: Området, hvorfra du vil hente data.
opslagsværdi: Din opslagsværdi, som du vil filtrere.
opslag_værdi_range: Det område, du vil filtrere lookup_value i.
Den første celle i lookup_value -området: hvis dit lookup_value -område er $ A $ 5: $ A $ 100, så er det $ A $ 5.
Vigtig: Alt skal være absolut refereret. opslagsværdi kan være relativ i henhold til krav.
Indtast det som en matrixformel. Efter at have skrevet formel skal du trykke på CTRL+SKIFT+ENTER -tasten for at gøre det til en matrixformel.
Som du kan se i gif'en, returnerer den alle kampe fra excel -tabellen.
Fordele:
- Returnerer med flere matchede værdier fra Excel -tabellen.
- Dynamisk
Ulemper:
- Det er for komplekst til, at en ny bruger kan forstå det.
- Bruger matrixformel
- Behov for at definere det mulige antal output og anvende denne formel som en formel med flere celler (ikke i Excel 2019 og 365).
- Langsom.
Du kan lære mere om denne formel her i detaljer.
6: VLOOKUP-CHOOSE Opslag i Excel-formel
Så de fleste siger, at det ikke er muligt at slå værdier op fra venstre for opslagsværdien i Excel ved hjælp af VLOOKUP -funktion. Jeg er ked af at sige det, men de tager fejl. Vi kan slå op til venstre for opslagsværdien i Excel ved hjælp af VLOOKUP -funktion ved hjælp af funktionen VÆLG.
Generisk formel:
= VLOOKUP (opslagsværdi, VÆLG ({1, 2}, opslag_område, req_range), 2, 0)
opslagsværdi : værdi at kigge efter
opslag_område : område, hvor man kan søge opslagsværdi
req_range : område, hvor tilsvarende værdi er påkrævet
2 : anden kolonne, num repræsenterer req_range
0 : se efter det nøjagtige match
I denne formel opretter vi dybest set en virtuel tabel inde i formlen ved hjælp af funktionen VÆLG. Funktionen VÆLG opretter en tabel med to kolonner. Den første kolonne indeholder opslagsområdet, og den anden kolonne indeholder resultatområdet.
Fordele ved VLOOKUP-CHOOSE opslagsformel:
- Du kan slå op til venstre for opslagsværdi
- Hurtig
- Let
Ulemper:
- VÆLG -funktionen bruges sjældent. Brugere skal forstå, hvordan det fungerer.
Du kan lære om denne opslagsformel her.
Så ja fyre, det er de forskellige opslagsfunktioner og formler. Det er ikke alt. Der kan være mange flere opslagsformler i Excel, der kan oprettes ved hjælp af forskellige kombinationer af Excel -formler. Hvis du har nogen særlige opslagsteknikker, kan du dele i kommentarfeltet herunder. Vi vil inkludere det i vores artikel med dit navn.
Jeg håber, det var nyttigt og informativt. Hvis du er i tvivl om denne artikel, så spørg mig i kommentarfeltet herunder.
10+ nye funktioner i Excel 2019 og 365: Selvom de tilgængelige funktioner i Excel 2016 og ældre er nok til at udregne enhver form for beregning og automatisering, bliver formlerne nogle gange vanskelige. Disse slags mindre, men vigtige ting løses i Excel 2019 og 365.
17 Ting om Excel VLOOKUP: VLOOKUP er bare ikke en opslagsformel, det er mere end det. VLOOKUP har mange andre evner og kan bruges til flere formål. I denne artikel undersøger vi alle de mulige anvendelser af VLOOKUP -funktionen.
10+ kreative avancerede Excel -diagrammer til at rokke dit dashboard: Excel er et kraftfuldt datavisualiseringsværktøj, der kan bruges til at oprette fantastiske diagrammer i Excel. Disse 15 avancerede excel -diagrammer kan bruges til at fascinere dine kolleger og chefer.
4 Creative Target Vs Achievement Charts i Excel: Mål vs præstationsdiagrammer er de mest grundlæggende og vigtige diagrammer i enhver virksomhed. Så det er bedre at sætte dem på den mest kreative måde. Disse 4 kreative diagrammer kan få dine dashboards til at rocke præsentationen.
Populære artikler:
50 Excel -genveje til at øge din produktivitet | Få hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges Excel VLOOKUP -funktion| Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges Excel COUNTIF -funktion| Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel | Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.
