Door Excel's te combinerenVLOOKUP functie met deKOLOM functie kunnen we een opzoekformule maken waarmee u meerdere waarden uit een enkele rij van een database of tabel met gegevens kunt retourneren.
01 van 10Retourneer meerdere waarden met Excel VERT.ZOEKEN
Als u de onderstaande stappen volgt, wordt de opzoekformule gemaakt in de bovenstaande afbeelding die meerdere waarden uit één gegevensrecord retourneert.
De lookup-formule vereist dat de KOLOM functie is genest binnen in VLOOKUP. Bij het nesten van een functie wordt de tweede functie ingevoerd als een van de argumenten voor de eerste functie.
In deze zelfstudie, de KOLOM functie zal worden ingevoerd als de kolom index aantal argument voor VLOOKUP. De laatste stap in de zelfstudie bestaat erin de opzoekformule naar extra kolommen te kopiëren om extra waarden voor het gekozen onderdeel op te halen.
Voer de zelfstudiegegevens in
De eerste stap in de zelfstudie is om de gegevens in te voeren in een Excel-werkblad. Om de stappen in de tutorial te volgen, voert u de gegevens in de bovenstaande afbeelding in in de volgende cellen:
- Voer het bovenste bereik van gegevens in cellen D1 naar G1.
- Voer het tweede bereik in cellen D4 naar G10.
De zoekcriteria en de lookup-formule die tijdens deze zelfstudie is gemaakt, worden ingevoerd rij 2 van het werkblad.
De zelfstudie bevat niet de opmaak die te zien is in de afbeelding, maar dit heeft geen invloed op de werking van de opzoekformule. Informatie over opmaakopties vergelijkbaar met die hierboven zijn te zien in deze eenvoudige Excel opmaak zelfstudie.
03 of 10Een benoemd bereik voor de gegevenstabel maken
Een benoemd bereik is een eenvoudige manier om naar een gegevensbereik in een formule te verwijzen. In plaats van de celverwijzingen voor de gegevens in te typen, kunt u gewoon de naam van het bereik typen.
Een tweede voordeel voor het gebruik van een benoemd bereik is dat de celverwijzingen voor dit bereik nooit veranderen, zelfs wanneer de formule naar andere cellen in het werkblad wordt gekopieerd. Bereiknamen zijn daarom een alternatief voor het gebruik van absolute celverwijzingen om fouten te voorkomen bij het kopiëren van formules.
De bereiknaam bevat niet de koppen of veldnamen voor de gegevens (rij 4) maar alleen de gegevens zelf.
- highlight cellen D5 naar G10 in het werkblad om ze te selecteren.
- Klik op de Naamvak gelegen boven kolom A.
- Type Tafel in de Naamvak.
- druk de invoeren toets op het toetsenbord.
- Cellen D5 naar G10 hebben nu de bereiknaam van Tafel. We zullen de naam gebruiken voor de VLOOKUP tabel array argument later in de zelfstudie.
Het dialoogvenster VLOOKUP openen
Hoewel het mogelijk is om onze opzoekformule rechtstreeks in een cel in een werkblad in te voeren, vinden veel mensen het moeilijk om de syntaxis recht te houden - vooral voor een complexe formule zoals die we in deze zelfstudie gebruiken.
Een alternatief is in dit geval om de VLOOKUP dialoog venster. Bijna alle functies van Excel hebben een dialoogvenster waarin u elk van de argumenten van de functie op een afzonderlijke regel kunt invoeren.
Zelfstudiestappen
- Klik op cel E2 van het werkblad - de locatie waar de resultaten van de tweedimensionale opzoekformule worden weergegeven.
- Klik op de Tabblad Formules van de lint.
- Klik op de Lookup & Reference optie in de lint om de vervolgkeuzelijst met functies te openen.
- Klik op VLOOKUP in de lijst om het dialoogvenster van de functie te openen.
Het opzoeken van het waardeargument Argument
Normaal gesproken is de opzoeken waarde komt overeen met een gegevensveld in de eerste kolom van de gegevenstabel. In ons voorbeeld, de opzoeken waarde verwijst naar de naam van het hardwaregedeelte waarover we informatie willen vinden. De toegestane soorten gegevens voor de opzoeken waarde zijn tekstgegevens, logische waarden, getallen en celverwijzingen.
Absolute celverwijzingen
Wanneer formules in Excel worden gekopieerd, veranderen de celverwijzingen normaal gesproken om hun nieuwe locatie weer te geven. Als dit gebeurt, D2, de celverwijzing voor de opzoeken waarde, wordt gewijzigd terwijl de formule wordt gekopieerd om fouten te maken cellen F2 en G2.
Absolute celverwijzingen veranderen niet wanneer formules worden gekopieerd.
Om de fouten te voorkomen, zetten we de celverwijzing om D2 in een absolute celverwijzing. Absolute celverwijzingen worden gecreëerd door op te drukken F4 toets op het toetsenbord. Hiermee voegt u dollartekens toe rond de celverwijzing, zoals $ D $ 2.
- Klik op de opzoekwaarde regel in het dialoogvenster.
- Klik op cel D2 om deze celverwijzing toe te voegen aan de opzoekwaarde regel - dit is de cel waarin we de onderdeelnaam typen waarover we informatie zoeken
- Zonder het invoegpunt te verplaatsen, drukt u op F4 toets op het toetsenbord om te converteren D2 in de absolute celverwijzing $ D $ 2.
- Verlaat de VLOOKUP functiedialoogvenster open voor de volgende stap in de zelfstudie.
Het tabelmatrixargument invoeren
De tabelmatrix is de tabel met gegevens die de opzoekformule doorzoekt om de gewenste informatie te vinden.De tabelmatrix moet ten minste twee kolommen met gegevens bevatten.
De eerste kolom bevat het zoekwaardeargument (vorige stap in de zelfstudie), terwijl de tweede kolom door de opzoekformule wordt doorzocht om de informatie te vinden die we hebben opgegeven.
Het tabelmatrixargument moet worden ingevoerd als een bereik met de celverwijzingen voor de gegevenstabel of als een bereiknaam.
- Klik op de table_array regel in het dialoogvenster.
- Type Tafel om de bereiknaam voor dit argument in te voeren.
- Verlaat de VLOOKUP functiedialoogvenster open voor de volgende stap in de zelfstudie.
De COLUMN-functie nesten
Normaal gesproken VLOOKUP geeft alleen gegevens uit één kolom van een gegevenstabel en deze kolom wordt ingesteld door de kolom index nummer argument.
In dit voorbeeld hebben we echter drie kolommen waarvan we gegevens willen retourneren, zodat we een manier nodig hebben om de. Gemakkelijk te wijzigen kolom index nummer zonder onze opzoekformule te bewerken - dit is waar de KOLOM functie komt binnen.
Door het in te voeren als de kolom index nummer argument, het zal veranderen naarmate de opzoekformule wordt gekopieerd cel D2 naar cellen E2 en F2 later in de tutorial.
Nestelfuncties
De KOLOM functie fungeert daarom als VLOOKUP's kolom index nummer argument; dit wordt bereikt door de KOLOM functie binnenkant van VLOOKUP in de kolomindex_getal regel van het dialoogvenster.
De COLUMN-functie handmatig invoeren
Bij het nesten van functies, staat Excel ons niet toe om het dialoogvenster van de tweede functie te openen om zijn argumenten in te voeren. De KOLOM De functie moet daarom handmatig worden ingevoerd in de kolomindex_getal lijn. De KOLOM functie heeft slechts één argument - de Referentie argument dat een celverwijzing is.
Het referentie-argument van de COLUMN-functie kiezen
De KOLOM functie van de functie is om het nummer van de gegeven kolom als de Referentie argument. Met andere woorden, het zet de kolomletter om in een getal met kolom A als de eerste kolom, kolom B de tweede enzovoort.
Sinds het eerste gegevensveld is wat we willen teruggeven de prijs van het artikel, dat zich in kolom twee van de gegevenstabel bevindt. We kunnen de celverwijzing voor elke cel in kiezen kolom B als de Referentie Argument om het nummer te krijgen 2 voor de kolomindex_getal argument.
- In de VLOOKUP functiedialoogvenster, klikt u op de kolomindex_getal lijn.
- Typ de functienaam kolom gevolgd door een open de ronde beugel.
- Klik op cel B1 in het werkblad om die celverwijzing in te voeren als de Referentie argument.
- Type A ronde beugel sluiten om het te voltooien KOLOM functie
- Verlaat de VLOOKUP functiedialoogvenster open voor de volgende stap in de zelfstudie.
Het betreden van het VLOOKUP-bereikopzoekargument
VLOOKUP's range_lookup argument is een logische waarde (TRUE of VALSE) die aangeeft of u wilt VLOOKUP om een exacte of een geschatte overeenkomst te vinden met de Opzoekwaarde.
- Als TRUE of als dit argument wordt weggelaten: VLOOKUP retourneert een exacte overeenkomst met de OpzoekwaardeOf, als er geen exacte overeenkomst wordt gevonden, VLOOKUP retourneert de op een na grootste waarde. Voor de formule om dit te doen, de gegevens in de eerste kolom van table_array moet in oplopende volgorde worden gesorteerd.
- Als VALSE: VLOOKUP gebruikt alleen een exacte overeenkomst met de Opzoekwaarde. Als er twee of meer waarden zijn in de eerste kolom van table_array die overeenkomen met de opzoekwaarde, wordt de eerste gevonden waarde gebruikt. Als er geen exacte overeenkomst wordt gevonden, # N / A fout wordt geretourneerd.
In deze tutorial zullen we instellen, omdat we specifieke informatie over een bepaald hardwareproject zoeken range_lookup gelijk aan vals.
- Klik op de range_lookup regel in het dialoogvenster.
- Typ het woord vals in deze regel om aan te geven dat we willen VLOOKUP om een exacte overeenkomst te retourneren voor de gegevens die we zoeken.
- Klik OK om de opzoekformule in te vullen en het dialoogvenster te sluiten.
- Aangezien we de zoekcriteria nog niet hebben ingevoerd in cel D2 een # N / B fout zal aanwezig zijn in cel E2.
- Deze fout wordt gecorrigeerd wanneer we de opzoekcriteria toevoegen in de laatste stap van de zelfstudie.
De opzoekformule kopiëren
De lookup-formule is bedoeld om tegelijkertijd gegevens uit meerdere kolommen van de gegevenstabel op te halen. Om dit te doen, moet de opzoekformule voorkomen in alle velden waarvan we informatie willen.
In deze zelfstudie willen we dat gegevens worden opgehaald kolommen 2, 3, en 4 van de gegevenstabel - dat is de prijs, het onderdeelnummer en de naam van de leverancier wanneer we een gedeeltelijke naam invoeren als Opzoekwaarde.
Omdat de gegevens in een normaal patroon in het werkblad zijn neergelegd, kunnen we de opzoekformule kopiëren in cel E2 naar cellen F2 en G2.
Terwijl de formule wordt gekopieerd, werkt Excel de relatieve celverwijzing in de KOLOM functie (cel B1) om de nieuwe locatie van de formule weer te geven.
Ook verandert Excel de absolute celverwijzing niet $ D $ 2 en het benoemde bereik Tafel zoals de formule wordt gekopieerd.
Er is meer dan een manier om gegevens in Excel te kopiëren, maar de eenvoudigste manier is om de Vul handvat.
- Klik op cel E2, waar de lookup-formule zich bevindt, om er de actieve cel van te maken.
- Plaats de muis aanwijzer over de zwart vierkant in de rechterbenedenhoek - de aanwijzer verandert in een plusteken om de vulgreep aan te duiden.
- Klik met de linkermuisknop en sleep de vulgreep over naar cel G2.
- Vrijlating de muisknop en cel F3 zou de tweedimensionale lookup-formule moeten bevatten.
- Als het correct is gedaan, cellen F2 en G2 zou nu ook de # N / A fout die aanwezig is in cel E2.
De zoekcriteria invoeren
Nadat de opzoekformule naar de vereiste cellen is gekopieerd, kan deze worden gebruikt om informatie uit de gegevenstabel op te halen. Hiertoe typt u de naam van het item je wilt terughalen in de Opzoekwaarde cel (D2) en druk op de invoeren toets op het toetsenbord.
Als u klaar bent, moet elke cel die de opzoekformule bevat een ander stuk gegevens bevatten over het hardware-item waarnaar u zoekt.
- Klik op cel D2 in het werkblad.
- Type widget in cel D2 en druk op de invoeren toets op het toetsenbord.
- De volgende informatie moet worden weergegeven in cellen E2 naar G2.
- E2: $ 14.76 - de prijs van een widget
- F2: PN-98769 - het onderdeelnummer voor een widget
- G2: Widgets Inc. - de naam van de leverancier voor widgets
- Test de VLOOKUP matrixformule verder door de naam van andere onderdelen in te voeren cel D2 en observeren van de resultaten in cellen E2 naar G2
