Video: Se Magisk Excel Triks - Slik bruker du Slicer ?? 2024
Den mest populære av Excel 2016 oppslagsfunksjoner er HLOOKUP (for Horizontal Lookup) og VLOOKUP (for Vertical Lookup) -funksjoner. Disse funksjonene ligger i rullegardinmenyen Oppslag og referanse på Formulas-fanen i båndet, samt i Oppslag og referanse-kategorien i dialogboksen Sett inn-funksjon. De er en del av en kraftig gruppe funksjoner som kan returnere verdier ved å slå dem opp i datatabeller.
VLOOKUP-funksjonen søker vertikalt (fra topp til bunn) den venstre kolonnen til et oppslagstabell til programmet finner en verdi som samsvarer med eller overgår den du ser opp. HLOOKUP-funksjonen søker horisontalt (fra venstre til høyre) øverste rad i et oppslagstabell til den finner en verdi som samsvarer med eller overgår den som du ser opp.
VLOOKUP-funksjonen bruker følgende syntaks:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP-funksjonen følger den nesten identiske syntaksen:
HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup]) > I begge funksjonene er
lookup_value argumentet verdien du vil se opp i Oppslagstabellen, og table_array er celleområdet eller navnet på Oppslagstabellen som inneholder både verdi å slå opp og den tilhørende verdien å returnere.
col_index_num angir kolonnen på oppslagstabellen som inneholder verdiene som returneres av VLOOKUP-funksjonen, basert på samsvar med verdien av argumentet lookup_value mot de i argumentet table_array. Du bestemmer argumentet col_index_num som teller hvor mange kolonner denne kolonnen er over til høyre fra den første kolonnen i den vertikale Oppslagstabellen, og du inkluderer den første kolonnen i Oppslagstabellen i denne tellingen. Argumentet
row_index_num angir rækken som inneholder verdiene returneres av HLOOKUP-funksjonen i et horisontalt bord. Du bestemmer argumentet row_index_num ved å telle hvor mange rader i denne raden er fra den øverste raden i det horisontale oppslagstabellen. Igjen, du inkluderer den øverste raden i Lookup-tabellen i denne tellingen. Når du går inn i
col_index_num eller row_index_num argumentene i VLOOKUP og HLOOKUP-funksjonene, overskrider verdien du angir kan overstille totalt antall kolonner eller rader i Oppslagstabellen. Det valgfrie
range_lookup -argumentet i både VLOOKUP og HLOOKUP-funksjonene er det logiske SANT eller FALSK som angir om du vil at Excel skal finne en nøyaktig eller omtrentlig samsvar for lookup_value i table_array.Når du angir SANT eller slipper argumentet range_lookup i VLOOKUP eller HLOOKUP-funksjonen, finner Excel en omtrentlig kamp. Når du angir FALSE som argumentet range_lookup , finner Excel bare eksakte samsvar. Finne omtrentlige kamper gjelder bare når du ser opp numeriske oppføringer (i stedet for tekst) i den første kolonnen eller raden i vertikal eller horisontal oppslagstabell. Når Excel ikke finner en eksakt samsvar i denne søket kolonnen eller raden, finner den neste høyeste verdien som ikke overstiger argumentet
lookup_value , og returnerer deretter verdien i kolonnen eller raden som er angitt av < col_index_num eller row_index_num argumenter. Når du bruker VLOOKUP og HLOOKUP-funksjonene, må teksten eller talloppføringene i oppslagskolonnen eller -raden (det vil si den venstre kolonnen til et vertikalt oppslagstabell eller den øverste raden i et horisontalt oppslagstabell) være unikt. Disse oppføringene må også ordnes eller sorteres i stigende rekkefølge; det vil si alfabetisk rekkefølge for tekstoppføringer, og laveste til høyeste rekkefølge for numeriske oppføringer. Figuren viser et eksempel på bruk av VLOOKUP-funksjonen for å returnere enten en 15% eller 20% -tips fra et tipsbord, avhengig av forhåndsaksentallet av sjekken. Cell F3 inneholder VLOOKUP-funksjonen:
= VLOOKUP (Pretax_Total, Tip_Table, IF (Tip_Percentage = 0, 15, 2, 3))
Denne formelen returnerer spissen av spissen basert på tipsprosenten i celle F1 og foraksaksbeløpet av sjekken i celle F2.
Bruk VLOOKUP-funksjonen til å returnere mengden av spissen du vil legge til fra et oppslagstabell.
For å bruke dette tipsbordet, skriv inn prosentandelen av tipset (15% eller 20%) i celle F1 (kalt Tip_Percentage) og mengden av sjekken før skatt i celle F2 (kalt Pretax_Total). Excel ser deretter opp verdien du angir i Pretax_Total-cellen i den første kolonnen i Oppslagstabellen, som inneholder celleområdet A2: C101 og heter Tip_Table.
col_index_num
i VLOOKUP-funksjonen for å bestemme hvilket tipsbeløp fra den raden i tabellen til gå tilbake til celle F3. Hvis Excel finner ut at verdien som er oppgitt i Pretax_Total-cellen ($ 16, 50 i dette eksemplet) ikke samsvarer nøyaktig med en av verdiene i den første kolonnen til Tip_Table, fortsetter programmet å søke ned sammenligningsområdet til det møter den første verdien som overstiger pretaks-totalen (17,00 i celle A19 i dette eksemplet). Excel flyttes deretter tilbake til forrige rad i tabellen og returnerer verdien i kolonnen som samsvarer med argumentet col_index_num for VLOOKUP-funksjonen. (Dette skyldes at valgfri range_lookup -argumentet er utelatt fra funksjonen.) Merk at tiptabellen eksempelet i figuren bruker en IF-funksjon for å bestemme argumentet for col_index_num
for VLOOKUP-funksjonen i celle F3. IF-funksjonen bestemmer kolonnnummeret som skal brukes i tipsbordet ved å matche prosentandelen som er angitt i Tip_Percentage (celle F1) med 0.15. Hvis de matcher, returnerer funksjonen 2 som argumentet col_index_num , og VLOOKUP-funksjonen returnerer en verdi fra den andre kolonnen (15% kolonne B) i Tip_Table-området. Ellers returnerer IF-funksjonen 3 som argumentet col_index_num , og VLOOKUP-funksjonen returnerer en verdi fra den tredje kolonnen (20% -kolonnen C) i Tip_Table-området. Følgende figur viser et eksempel som bruker HLOOKUP-funksjonen til å slå opp prisen på hvert bakerielement som er lagret i et separat prisoppslagstabell, og deretter returnere den prisen til pris / doz-kolonnen i den daglige salget listen. Cell F3 inneholder den opprinnelige formelen med HLOOKUP-funksjonen som deretter kopieres ned kolonne F: Med HLOOKUP-funksjonen kan du returnere prisen på et bakeriobjekt fra et oppslagstabell.
= HLOOKUP (item, Price_table, 2, FALSE)
lookup_value
argumentet og celleområdet navn Pris tabellen som er gitt til celleområdet I1: M2 er argumentet table_array . Argumentet row_index_num er fordi du vil at Excel skal returnere prisene i den andre raden i prisoppslagstabellen, og det valgfrie range_lookup -argumentet er FALSK fordi varenavnet i den daglige salget listen må samsvare nøyaktig artikkelnavnet i prisoppslagstabellen. Når du bruker HLOOKUP-funksjonen, bruker du pristabellen til å angi prisen per dusin for hvert bakervareelement i Daily Sales-listen, gjør du det veldig enkelt å oppdatere et hvilket som helst salg i listen. Alt du trenger å gjøre er å endre pris / Doz-kostnaden i dette området, og HLOOKUP-funksjonen oppdaterer umiddelbart den nye prisen i Daglig Salgsliste hvor varen er solgt.
