Video: Top 10 Excel New Features 2024
Den mest populära av uppslagsfunktionerna i Excel 2013 är HLOOKUP (för Horizontal Lookup) och VLOOKUP (för vertikal sökning). Dessa funktioner finns i rullgardinsmenyn Sök och referens på fliken Formler i bandet och i kategorin Sök och referens i dialogrutan Infoga funktion.
De ingår i en kraftfull grupp av funktioner som kan returnera värden genom att slå upp dem i datatabeller.
VLOOKUP-funktionen söker vertikalt (från topp till botten) längst till vänster i ett Lookup-bord tills programmet hittar ett värde som matchar eller överstiger det du tittar på. HLOOKUP-funktionen söker horisontellt (från vänster till höger) den översta raden i ett uppslagstabell tills den lokaliserar ett värde som överensstämmer med eller överstiger det du tittar på.
VLOOKUP-funktionen använder följande syntax:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Funktionen HLOOKUP följer den nästan identiska syntaxen:
HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup]) > I båda funktionerna är argumentet
lookup_value det värde som du vill leta upp i Lookup-tabellen, och table_array är cellintervallet eller namnet på Lookup-tabellen som innehåller både värde för att leta upp och det relaterade värdet att returnera.
col_index_num i VLOOKUP-funktionen är numret på kolumnen vars värden jämförs med argumentet lookup_value i ett vertikalt bord. Argumentet row_index_num i HLOOKUP-funktionen är numret på raden vars värden jämförs med lookup_value i ett horisontellt bord. När du anger argumenten
col_index_num eller row_index_num i VLOOKUP och HLOOKUP-funktionerna måste du ange ett värde större än noll som inte överstiger det totala antalet kolumner eller rader i Uppslagstabell. Argumentet
range_lookup i både VLOOKUP och HLOOKUP-funktionerna är det logiska SANT eller FALSK som anger om du vill att Excel ska hitta en exakt eller approximativ match för lookup_value i table_array. När du anger TRUE eller släpper argumentet
range_lookup i VLOOKUP eller HLOOKUP-funktionen, hittar Excel en ungefärlig matchning. När du anger FALSE som argumentet range_lookup , hittar Excel bara exakta träffar. Hitta approximativa matchningar gäller endast när du tittar upp numeriska poster (i stället för text) i den första kolumnen eller raden i vertikalt eller horisontellt uppslagstabell.När Excel inte hittar en exakt matchning i kolumnen eller raden Sökning, lokaliserar den nästa högsta värde som inte överstiger argumentet
lookup_value och returnerar sedan värdet i kolumnen eller raden som anges av < kolumnindex eller radindex argument. När du använder VLOOKUP och HLOOKUP-funktionerna måste text eller numeriska poster i kolumnen Kolla eller rad (det vill säga den vänstra kolumnen i ett vertikalt uppslagstabell eller den övre raden i ett horisontellt uppslagstabell) vara unika. Dessa poster måste också ordnas eller sorteras i stigande ordning. det vill säga alfabetisk ordning för textposter och lägsta till högsta order för numeriska poster. Figuren visar ett exempel på att använda VLOOKUP-funktionen för att returnera antingen en 15% eller 20% -tips från ett tipsbord, beroende på kontrollens förhandskontroll. Cell F3 innehåller VLOOKUP-funktionen:
= VLOOKUP (Pretax_Total, Tip_Table, IF (Tip_Percentage = 0, 15, 2, 3))
Denna formel returnerar spetsens mängd baserat på tipsprocenten i cell F1 och Förebeloppet av kontrollen i cell F2.
För att använda detta tipsbord anger du procenten av spetsen (15% eller 20%) i cell F1 (namnet Tip_Percentage) och mängden av kontrollen före skatt i cell F2 (namnet Pretax_Total). Excel ser då upp värdet som du anger i Pretax_Total-cellen i den första kolumnen i Lookup-tabellen, som innehåller cellintervallet A2: C101 och heter Tip_Table.
Excel flyttar sedan ner värdena i den första kolumnen i Tip_Table tills den hittar en matchning, varefter programmet använder argumentet
col_index_num
i VLOOKUP-funktionen för att bestämma vilket tipsbelopp från den raden i tabellen till återgå till cell F3. Om Excel finner att värdet som anges i Pretax_Total-cellen ($ 16, 50 i det här exemplet) inte exakt matchar något av värdena i den första kolumnen i Tip_Table fortsätter programmet att söka ner jämförelsegraden tills det möter det första värdet som överskrider pretax totalen (17 00 i cell A19 i detta exempel). Excel flyttar sedan tillbaka till föregående rad i tabellen och returnerar värdet i kolumnen som matchar argumentet
col_index_num
i VLOOKUP-funktionen. (Detta beror på att valfri range_lookup -argumentet har utelämnats från funktionen.) Observera att exemplet i tiptabellen använder en IF-funktion för att bestämma argumentet col_index_num
för VLOOKUP-funktionen i cell F3. IF-funktionen bestämmer kolumnnumret som ska användas i speltabellen genom att matcha procentandelen i Tip_Percentage (cell F1) med 0. 15. Om de matchar returnerar funktionen 2 som col_index_num
argument och VLOOKUP-funktionen returnerar ett värde från den andra kolumnen (15% kolumnen B) i Tip_Table-intervallet. Annars returnerar IF-funktionen 3 som argumentet col_index_num
och VLOOKUP-funktionen returnerar ett värde från den tredje kolumnen (20% -kolumnen C) i Tip_Table-intervallet. Följande bild visar ett exempel som använder HLOOKUP-funktionen för att leta upp priset på varje bakverk som är lagrat i ett separat prisuppslagstabell och sedan returnera det priset till pris / doz-kolumnen i dagslistan.Cell F3 innehåller den ursprungliga formeln med HLOOKUP-funktionen som sedan kopieras ned kolumn F: = HLOOKUP (item, Price_table, 2, FALSE)
I den här HLOOKUP-funktionen, I intervallet C3: C62 definieras argumentet
lookup_value
och cellintervallnamnet Pristabell som ges till cellintervallet I1: M2 är argumentet table_array . Argumentet row_index_num
är att du vill att Excel ska returnera priserna i den andra raden i prisuppslagstabellen och det valfria argumentet range_lookup är FALK eftersom artikelnamnet i Den dagliga försäljningslistan måste matcha exakt föremålet i tabellen Prisuppsättning. Genom att använda HLOOKUP-funktionen använder du prisklassintervallet för att mata in priset per dussin för varje bagagevarupost i listan Daglig försäljning, vilket gör det mycket enkelt att uppdatera någon av försäljningarna i listan. Allt du behöver göra är att ändra sin pris / Doz-kostnad i det här intervallet, och HLOOKUP-funktionen uppdaterar omedelbart det nya priset i den dagliga försäljningslistan där varan säljs.
