Video: Cursus Excel Les 7: Verticaal zoeken 2024
De meest populaire opzoekfuncties voor Excel 2016 zijn HORIZ.ZOEKEN (voor horizontale raadpleging) en VLOOKUP (voor verticale opzoeking) -functies. Deze functies bevinden zich in de vervolgkeuzelijst Opzoeken en verwijzen op het tabblad Formules van het lint en in de categorie Opzoeken & Verwijzing in het dialoogvenster Functie invoegen. Ze maken deel uit van een krachtige groep functies die waarden kunnen retourneren door ze op te zoeken in gegevenstabellen.
De functie VERT.ZOEKEN zoekt verticaal (van boven naar beneden) naar de meest linkse kolom van een opzoektabel totdat het programma een waarde zoekt die overeenkomt met of groter is dan de waarde waarnaar u opzoekt. De functie HORIZ.ZOEKEN zoekt horizontaal (van links naar rechts) de bovenste rij van een opzoektabel totdat een waarde wordt gevonden die overeenkomt met of groter is dan de waarde waarnaar u zoekt.
De functie VERT.ZOEKEN gebruikt de volgende syntaxis:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
De functie HLOOKUP volgt de vrijwel identieke syntaxis:
HORIZ.ZOEKEN (zoekwaarde, tabelmatrix, rij_index_num, [bereik_zoeklijst]) > In beide functies is het argument
lookup_value de waarde die u in de tabel Lookup wilt opzoeken en table_array is het celbereik of de naam van de lookup-tabel die zowel de tabel waarde om op te zoeken en de gerelateerde waarde om terug te keren.
col_index_num verwijst naar de kolom van de opzoektabel die de waarden bevat die door de functie VERT.ZOEKEN worden geretourneerd op basis van de overeenkomende waarde van het argument lookup_value tegen die in het argument table_array. U bepaalt het col_index_num -argument tellen hoeveel kolommen deze kolom rechts van de eerste kolom van de verticale opzoektabel is en u neemt de eerste kolom van de opzoektabel in dit aantal op. Het argument
row_index_num geeft aan dat de rij met de waarden wordt geretourneerd door de functie HORIZ.ZOEKEN in een horizontale tabel. U bepaalt het argument row_index_num door te tellen hoeveel rijen in deze rij zich in de bovenste rij van de horizontale tabel opzoeken bevinden. Nogmaals, u neemt de bovenste rij van de opzoektabel mee in deze telling. Bij het invoeren van de
col_index_num of row_index_num argumenten in de functies VERT.ZOEKEN en HORIZ.ZOEKEN, overschrijdt de waarde die u invoert niet het totale aantal kolommen of rijen in de tabel Opzoeken. Het optionele argument
Range_lookup in de functies VERT.ZOEKEN en HORIZ.ZOEKEN is de logische WAAR of ONWAAR die aangeeft of u wilt dat Excel een exacte of approximatieve overeenkomst zoekt voor de zoekwaarde in de tabelmatrix.Wanneer u TRUE opgeeft of het argument range_lookup in de VLOOKUP- of HLOOKUP-functie weglaat, vindt Excel een schatting. Wanneer u FALSE opgeeft als argument range_lookup , vindt Excel alleen exacte overeenkomsten. Het zoeken naar benaderde overeenkomsten heeft alleen betrekking op het opzoeken van numerieke gegevens (in plaats van tekst) in de eerste kolom of rij van de verticale of horizontale tabel opzoektabel. Wanneer Excel geen exacte overeenkomst vindt in deze kolom of rij van Opzoeken, wordt de eerstvolgende hoogste waarde gevonden die het argument
lookup_value niet overschrijdt en wordt vervolgens de waarde geretourneerd in de kolom of rij die wordt aangegeven door de < kolomindex_getal of rij-index_getal argumenten. Wanneer u de functies VERT.ZOEKEN en HORIZ.ZOEKEN gebruikt, moeten de tekst- of numerieke gegevens in de kolom Opzoeken of rij (dat is de meest linkse kolom van een verticale opzoektabel of de bovenste rij van een horizontale opzoektabel) uniek zijn. Deze vermeldingen moeten ook in oplopende volgorde worden gerangschikt of gesorteerd; dat wil zeggen, alfabetische volgorde voor tekstinvoeringen en laagste tot hoogste volgorde voor numerieke invoer. De afbeelding toont een voorbeeld van het gebruik van de functie VERT.ZOEKEN om ofwel een tip van 15% of 20% terug te geven van een tiptabel, afhankelijk van het voorax totaal van de controle. Cel F3 bevat de functie VERT.ZOEKEN:
= VERT.ZOEKEN (Pretax_Total, Tip_Tabel, IF (Tip_percentage = 0, 15, 2, 3))
Deze formule retourneert de hoeveelheid van de tip op basis van het tippercentage in cel F1 en de vooringestelde hoeveelheid van de incheckcel F2.
De functie VERT.ZOEKEN gebruiken om de hoeveelheid van de tip die moet worden toegevoegd aan een opzoektabel te retourneren.
Voer voor deze tiptabel het percentage van de tip (15% of 20%) in cel F1 (met de naam Tip_Percentage) en het bedrag van de controle vóór belasting in cel F2 (met de naam Pretax_Total) in. Excel zoekt vervolgens de waarde op die u opgeeft in de cel Pretax_Total in de eerste kolom van de tabel Opzoeken, die het cellenbereik A2: C101 bevat en de naam Tip_Table heeft.
col_index_num
in de functie VERT.ZOEKEN gebruikt om te bepalen welk tipbedrag uit die rij van de tabel naar keer terug naar cel F3. Als Excel constateert dat de waarde die is ingevoerd in de cel Pretax_Total ($ 16,50 in dit voorbeeld) niet exact overeenkomt met een van de waarden in de eerste kolom van Tip_Table, blijft het programma zoeken in het vergelijkingsbereik totdat het de eerste waarde tegenkomt die het pretax-totaal overschrijdt (17,00 in cel A19 in dit voorbeeld). Excel gaat dan terug naar de vorige rij in de tabel en retourneert de waarde in de kolom die overeenkomt met het argument col_index_num van de functie VERT.ZOEKEN. (Dit komt doordat het optionele argument range_lookup is weggelaten uit de functie.) Merk op dat het voorbeeld van de puntentafel in de figuur een IF-functie gebruikt om het argument col_index_num
te bepalen voor de VERT.ZOEKEN-functie in cel F3. De IF-functie bepaalt het kolomnummer dat moet worden gebruikt in de puntentabel door het percentage dat is opgegeven in Tip_Percentage (cel F1) te vergelijken met 0.15. Als ze overeenkomen, retourneert de functie 2 als het argument col_index_num en de functie VERT.ZOEKEN retourneert een waarde uit de tweede kolom (de 15% kolom B) in het bereik Tip_tabel. Anders retourneert de functie ALS de waarde 3 als argument col_index_num en de functie VERT.ZOEKEN retourneert een waarde uit de derde kolom (kolom 20% C) in het bereik Tip_tabel. De volgende afbeelding toont een voorbeeld dat de HORIZ.ZOEKEN-functie gebruikt om de prijs op te zoeken van elk bakkerijitem dat is opgeslagen in een afzonderlijke opzoektabel en vervolgens om die prijs terug te brengen naar de kolom Prijs / Doz van de lijst Dagelijkse verkoop. Cel F3 bevat de oorspronkelijke formule met de functie HORIZ.ZOEKEN die vervolgens naar beneden in kolom F wordt gekopieerd: De functie HORENOVERZICHT gebruiken om de prijs van een bakkerijartikel in een opzoektabel te retourneren.
= HLOOKUP (item, Price_table, 2, FALSE)
lookup_value
en de celbereiknaam Prijstabel die wordt gegeven aan het celbereik I1: M2 is het argument table_array . Het argument row_index_num is 2 omdat u wilt dat Excel de prijzen retourneert in de tweede rij van de tabel Prijzen opzoeken en het optionele bereik_zoek argument is ONWAAR omdat de itemnaam in de dagelijkse verkoop lijst moet exact overeenkomen met de artikelnaam in de tabel Prijzen opzoeken. Als u de functie HORIZ.ZOEKEN gebruikt, gebruikt u het prijzentabelbereik om de prijs per dozijn in te voeren voor elk bakkerijartikel in de lijst Dagelijkse verkoop, maakt u het heel eenvoudig om alle verkopen in de lijst bij te werken. Het enige wat u hoeft te doen is de prijs / Doz-kosten in dit bereik te wijzigen en de HORIZ.ZOEKEN-functie werkt de nieuwe prijs onmiddellijk bij in de lijst met dagelijkse verkopen, waar het artikel ook wordt verkocht.
