Dit artikel is gewijd aan VERT.ZOEKEN-functies. Het zal overwegen stapsgewijze instructies voor de functie VERT.ZOEKEN, genaamd "". In dit artikel zullen we gedetailleerd kijken naar de beschrijving, syntaxis en Voorbeelden van de functie VERT.ZOEKEN in Excel. We zullen ook bekijken hoe we het kunnen gebruiken en kijken naar de belangrijkste fouten en waarom. De Vlookup-functie werkt niet.

Syntaxis en beschrijving van de functie VERT.ZOEKEN in Excel

Dus aangezien de tweede titel van dit artikel “ VLOOKUP-functie in Excel voor dummies", laten we beginnen met uit te zoeken wat de functie VERT.ZOEKEN is en wat deze doet? VLOOKUP-functie in het Engels VERT.ZOEKEN, zoekt de opgegeven waarde op en retourneert de overeenkomstige waarde uit een andere kolom.

Hoe werkt de functie VERT.ZOEKEN?? De functie VERT.ZOEKEN in Excel doorzoekt uw gegevenslijsten op basis van een unieke identificatiecode en geeft u een stukje informatie dat aan die unieke identificatiecode is gekoppeld.

De letter "V" in VPR staat voor "verticaal". Het wordt gebruikt om te differentiëren VLOOKUP- en GLOOKUP-functies, die zoekt naar de waarde in de bovenste rij van de array ("H" staat voor "horizontaal").

De functie VERT.ZOEKEN is beschikbaar in alle versies van Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Syntaxis VERT.ZOEKEN-functies ziet er zo uit:

VERT.ZOEKEN(opzoekwaarde, tabel, kolomnummer, [interval_opzoeken])

Zoals je kunt zien, VLOOKUP-functie heeft 4 parameters of argumenten. De eerste drie parameters zijn vereist, de laatste is optioneel.

1. lookup_waarde is de zoekwaarde.

Dit kan een waarde zijn (getal, datum of tekst) of celreferentie(een verwijzing naar de cel die de opzoekwaarde bevat), of de waarde die door een andere Excel-functie wordt geretourneerd. Bijvoorbeeld:

• Zoek naar een getal: =VERT.ZOEKEN(40; A2:B15; 2) - de formule zoekt naar het getal 40.
• Tekst zoeken: =VERT.ZOEKEN(“appels”, A2:B15, 2) - de formule zoekt naar de tekst “appels”. Houd er rekening mee dat u tekstwaarden altijd tussen "dubbele aanhalingstekens" plaatst.
• Zoeken naar een waarde uit een andere cel: =VERT.ZOEKEN(C2, A2:B15, 2) - de formule zoekt naar de waarde in cel C2.

1. een tabel bestaat uit twee of meer kolommen met gegevens.

Onthoud dat VLOOKUP-functie zoekt altijd naar de gewenste waarde in de eerste kolom van de tabel. Uw tabel kan verschillende waarden bevatten, zoals tekst, datum, getallen of Booleaanse waarden. Waarden hoofdletterongevoelig, wat betekent dat hoofdletters en kleine letters als identiek worden beschouwd.

Onze formule =VERT.ZOEKEN(40, A2:B15, 2) zoekt dus naar "40" in de cellen A2 tot en met A15, omdat A de eerste kolom van de tabel A2:B15 is.

1. column_number - het nummer van de kolom in de tabel waaruit de waarde in de corresponderende rij moet worden geretourneerd.

De meest linkse kolom in de opgegeven tabel is 1, de tweede kolom is 2, de derde is 3, enz.

4. intervalweergave bepaalt of u zoekt naar een exacte overeenkomst (FALSE) of een geschatte overeenkomst (WAAR of weggelaten). Deze laatste parameter is optioneel, maar erg belangrijk.

VLOOKUP-functie in Excel-voorbeelden

Laten we nu eens kijken naar enkele gebruiksscenario's VERT.ZOEKEN-functies voor echte gegevens.

VLOOKUP-functie op verschillende bladen

In de praktijk worden VLOOKUP-formules zelden gebruikt om naar gegevens op één blad te zoeken. Vaker wel dan niet, zult u de relevante gegevens uit een ander blad moeten zoeken en ophalen.

Naar gebruik de VERT.ZOEKEN-functie vanuit een ander Excel-werkblad moet u de naam van het werkblad en een uitroepteken in het tabelargument invoeren vóór het celbereik, bijvoorbeeld =VERT.ZOEKEN(40;Blad2!A2:B15,2). De formule geeft aan dat het zoekbereik A2:B15 zich in Blad2 bevindt.

Uiteraard hoeft u de bladnaam niet handmatig in te voeren. Begin gewoon met het typen van de formule, en als het om het tabelargument gaat, ga dan naar het opzoekwerkblad en selecteer het bereik met uw muis.

De formule die u in de onderstaande afbeelding ziet, zoekt naar tekst in cel A2 ("Product 3") in kolom A (1e kolom van zoekbereik A2:B9) op het werkblad "Prijzen":

VERZOEKEN(A2,Prijzen!$A$2:$B$8,2,FALSE)

VERT.ZOEKEN-functie in Excel - VERT.ZOEKEN-functie op verschillende bladen

Hoe u een benoemd bereik of een tabel gebruikt in VLOOKUP-formules

Als u hetzelfde opzoekbereik in meerdere VERT.ZOEKEN-formules gaat gebruiken, kunt u er rechtstreeks een naam voor maken en invoeren in het tabelargument van uw VERT.ZOEKEN-formule.

Om een ​​benoemd bereik te maken, selecteert u eenvoudigweg de cellen en voert u een naam in het vak Naam links van het paneel Formule in.

VLOOKUP-functie in Excel - Een bereik een naam geven

Nu kunt u de volgende VERT.ZOEKEN-formule schrijven om de prijs van Product 1 te krijgen:

VERT.ZOEKEN("Product 1";Producten;2)

VERT.ZOEKEN-functie in Excel - Voorbeeld van een VERT.ZOEKEN-functie met een bereiknaam

De meeste bereiknamen in Excel zijn van toepassing op de gehele werkmap, dus u hoeft geen werkbladnaam op te geven, zelfs als uw zoekbereik zich op een ander werkblad bevindt. Dergelijke formules zijn veel begrijpelijker. Ook kan het gebruik van benoemde bereiken een goed alternatief zijn voor cellen. Omdat het benoemde bereik niet verandert wanneer de formule naar andere cellen wordt gekopieerd, kunt u er zeker van zijn dat uw zoekbereik altijd correct blijft.

Als u een celbereik hebt geconverteerd naar een Excel-tabel met volledige functionaliteit (tabblad Invoegen --> Tabel), kunt u het zoekbereik met uw muis selecteren, waarna Microsoft Excel automatisch de kolomnamen of tabelnaam aan de formule toevoegt. :

VLOOKUP-functie in Excel - Voorbeeld van de VLOOKUP-functie met tabelnaam

De volledige formule zou er ongeveer zo uit kunnen zien:

VERT.ZOEKEN("Product 1";Tabel6[[Product]:[Prijs]];2)

of zelfs =VERT.ZOEKEN("Product 1";Tabel6;2).

Net als benoemde bereiken zijn kolomnamen constant en veranderen celverwijzingen niet, ongeacht waar de VERT.ZOEKEN-formule wordt gekopieerd.

VLOOKUP-functie met meerdere voorwaarden

Laten we eens overwegen voorbeeld van een functie VERT.ZOEKEN met verschillende voorwaarden. We hebben de volgende invoergegevens:

VLOOKUP-functie in Excel - Brongegevenstabel

Stel dat we moeten gebruiken VLOOKUP-functie met verschillende voorwaarden. Om bijvoorbeeld de prijs van een product te zoeken met behulp van twee criteria: de naam van het product en het type ervan.

1. Te gebruiken VLOOKUP-functie met verschillende voorwaarden het is noodzakelijk om aan het begin een extra kolom in te voegen waarin informatie wordt opgeslagen met de naam en het type product.

Voeg dus op het blad 'Prijzen' een kolom in en voer in cel A2 de volgende formule in:

Met behulp van deze formule verkrijgen we de waarde van de kolommen “Product” en “Type”. Vul alle cellen in.

De opzoektabel ziet er nu als volgt uit:

VERT.ZOEKEN-functie in Excel - Een helperkolom toevoegen

1. Nu schrijven we in cel C2 op het blad “Verkoop” de volgende VERT.ZOEKEN-formule:

VERT.ZOEKEN(A2Prijzen!$A$1:$D$8,4,FALSE)

We vullen de resterende cellen in en als resultaat krijgen we prijzen voor elk product in overeenstemming met het type:

VLOOKUP-functie in Excel - VLOOKUP-voorbeeld met meerdere voorwaarden

Laten we er nu naar kijken Functiefouten VERT.ZOEKEN.

Waarom werkt de functie VERT.ZOEKEN niet?

In dit deel van het artikel zullen we bekijken waarom De Vlookup-functie werkt niet en mogelijk Functiefouten VERT.ZOEKEN.

Fouttype	Oorzaak	Oplossing
	De locatie van de kolom waarin wordt gezocht, is onjuist	De tabelkolom waarin wordt gezocht MOET de meest linkse zijn. Verplaats de kolom waarnaar u zoekt helemaal links van de tabel. Of maak links in de tabel een extra duplicaatkolom.
	Het tafelbereik staat niet vast	Als de eerste waarde correct werd weergegeven en na het uitrekken van de formule VERT.ZOEKEN de fout #N/A in sommige cellen wordt aangetroffen, is het tabelbereik niet opgelost. Gebruik ($) om een ​​tabelbereik te vergrendelen, zodat de formule hetzelfde bereik gebruikt wanneer deze wordt ingevuld. Of gebruik
	Kon geen exacte overeenkomst vinden (indien aanwezig). time-lapse-kijken zoeken naar exacte geselecteerde waarde (0)
	IN time-lapse-kijken er wordt gezocht naar de dichtstbijzijnde waarde (1), en de tabel waarop wordt gezocht, is niet gesorteerd.	Sorteer de eerste kolom van de tabel in oplopende volgorde van namen.
		Gebruik de functies TYPE of SPACE.
	De kolomnummerwaarde overschrijdt het aantal kolommen in de tabel	Controleer het kolomnummer dat de geretourneerde waarde bevat.
	Er ontbreken aanhalingstekens in de formule	Als u gebruik maakt van als de gewenste waarde geen cellink, maar tekst, dan moet deze tussen aanhalingstekens staan. Bijvoorbeeld: VERT.ZOEKEN("Product 1"; Prijzen!$A$2:$B$8,2,0)

Ik hoop dat nu zelfs voor dummies VERT.ZOEKEN-functie in Excel zal duidelijk zijn.

Batjanov Denis Als gastauteur vertelt hij in dit bericht hoe je gegevens in de ene Excel-tabel kunt vinden en deze in een andere kunt extraheren, en onthult hij ook alle geheimen van de verticale weergavefunctie.

Gebruik de functie KOLOM om de extractiekolom op te geven

Als de tabel waarin u gegevens ophaalt met VERT.ZOEKEN dezelfde structuur heeft als de opzoektabel, maar simpelweg minder rijen bevat, kan VERT.ZOEKEN de functie COLUMN() gebruiken om automatisch de op te halen kolomnummers te berekenen. In dit geval zijn alle VLOOKUP-formules hetzelfde (aangepast voor de eerste parameter, die automatisch verandert)! Houd er rekening mee dat de eerste parameter een absolute kolomcoördinaat heeft.

Een samengestelde sleutel maken met &»|»&

Als er tegelijkertijd in meerdere kolommen moet worden gezocht, is het noodzakelijk om voor de zoekopdracht een samengestelde sleutel te maken. Als de geretourneerde waarde geen tekst zou zijn (zoals het geval is met het veld "Code"), maar numeriek, dan zou de handiger SUMIFS-formule hiervoor geschikt zijn en zou de samengestelde kolomsleutel helemaal niet nodig zijn.

Dit is mijn eerste artikel voor Lifehacker. Als je het leuk vond, dan nodig ik je uit om mijn website te bezoeken, en ik zal ook graag in de reacties lezen over je geheimen over het gebruik van de VLOOKUP-functie en dergelijke. Bedankt. :)

Werken met een overzichtstabel houdt in dat u waarden uit andere tabellen erin haalt. Als er veel tabellen zijn, zal de handmatige overdracht enorm veel tijd in beslag nemen, en als de gegevens voortdurend worden bijgewerkt, zal dit een Sisyphean-taak zijn. Gelukkig is er een VLOOKUP-functie die de mogelijkheid biedt om automatisch gegevens op te halen. Laten we eens kijken naar specifieke voorbeelden van hoe deze functie werkt.

De naam van de functie VERT.ZOEKEN staat voor “verticale kijkfunctie”. In het Engels is de naam VLOOKUP. Deze functie zoekt naar gegevens in de linkerkolom van het onderzochte bereik en retourneert vervolgens de resulterende waarde in de opgegeven cel. Simpel gezegd kunt u met VLOOKUP waarden herschikken van een cel in de ene tabel naar een andere tabel. Laten we kijken hoe we de functie VERT.ZOEKEN in Excel kunnen gebruiken.

Voorbeeld van het gebruik van VERT.ZOEKEN

Laten we eens kijken hoe de functie VERT.ZOEKEN werkt aan de hand van een specifiek voorbeeld.

We hebben twee tafels. De eerste is een inkooptabel waarin de namen van voedingsmiddelen worden geplaatst. De volgende kolom na de naam bevat de hoeveelheid van het product dat moet worden gekocht. Vervolgens komt de prijs. En in de laatste kolom - de totale kosten van de aankoop van een specifiek product, die worden berekend met behulp van de formule van het vermenigvuldigen van de hoeveelheid met de prijs die al in de cel is ingevoerd. Maar we zullen gewoon de prijs moeten verhogen met behulp van de functie VERT.ZOEKEN uit de aangrenzende tabel, die een prijslijst is.

Zoals u kunt zien is de prijs van aardappelen vanuit de prijslijst aan de tabel toegevoegd. Om bij andere productnamen niet zo'n ingewikkelde procedure te hoeven doorlopen, gaan we gewoon in de rechter benedenhoek van de gevulde cel staan ​​zodat er een kruisje verschijnt. We tekenen dit kruis helemaal onderaan de tafel.

We hebben dus alle benodigde gegevens van de ene tabel naar de andere gehaald met behulp van de functie VERT.ZOEKEN.

Zoals u kunt zien, is de functie VERT.ZOEKEN niet zo ingewikkeld als het op het eerste gezicht lijkt. Het gebruik ervan begrijpen is niet erg moeilijk, maar het beheersen van deze tool bespaart u veel tijd bij het werken met tabellen.

Microsoft Excel heeft veel verschillende functies die het werk van de gebruiker veel gemakkelijker maken, en in dit artikel zullen we het over een van hen hebben. Het heet VERT.ZOEKEN, en als het in het Engels is, dan VERT.ZOEKEN.

De functie VERT.ZOEKEN brengt waarden over van de ene tabel naar specifieke cellen in een andere. Laat me het in meer detail uitleggen: in de eerste tabel selecteert u de waarde die u moet vinden in de linkerkolom van de tweede. Als er overeenkomsten zijn, draagt ​​VLOOKUP de waarde over van de kolom die u in deze rij hebt opgegeven naar de eerste tabel. Hoewel de definitie een beetje verwarrend is, is het gebruik van de functie niet zo moeilijk. Laten we een paar voorbeelden bekijken.

Omdat de functie meestal wordt gebruikt om de kolom met de prijs in te vullen, die wordt aangegeven in individuele prijslijsten, nemen we het volgende voorbeeld. Zo staat er een tafel met fruit (oranje), waarbij per stuk wordt aangegeven hoeveel kilogram we willen bestellen. Dienovereenkomstig moet u voor elk fruit de prijs noteren op de prijslijst (groen) van de leverancier en vervolgens berekenen hoeveel de aankoop gaat kosten. Het is moeilijk om elk artikel te bekijken en gegevens over te dragen, vooral als er duizenden regels met goederen zijn, daarom gebruiken we VLOOKUP.

Klik op de bovenste cel in de eerste tabel in de kolom Prijs en vervolgens op de knop “fx” in de formulebalk om het venster Functiewizard te openen.

Het volgende dat we doen is de argumenten in de voorgestelde velden schrijven.

Zet cursief in het veld "Zoekwaarde" en markeer in de eerste tabel de waarde waarnaar we zullen zoeken. Ik heb deze appel.

In de regel "Tabel" moet u degene selecteren waaruit de gegevens zullen worden gehaald - selecteer niet de koptekst. Merk op dat de meest linkse kolom moet bestaan ​​uit de waarden waarnaar we op zoek zijn. Dat wil zeggen, ik heb een appel en ander fruit nodig, wat betekent dat hun lijst in de linkerkolom van het geselecteerde gebied moet staan.

Zodat nadat we de formule hebben geschreven en deze over de hele kolom hebben uitgerekt, het geselecteerde bereik niet naar beneden beweegt, moeten we de koppelingen absoluut maken: selecteer de gegevens in het veld en druk op "F4". Nu is het adres van de cellen absoluut geworden, is het teken “$” eraan toegevoegd en zal het bereik niet verschuiven.

Waar het kolomnummer staat, plaatst u het nummer dat in de tweede tabel overeenkomt met de kolom waaruit u gegevens wilt overbrengen. Mijn prijslijst bestaat uit fruit en prijs, ik heb de tweede nodig, dus ik zet het cijfer “2”.

IN "Time-lapse" We schrijven "FALSE" - als u naar exacte overeenkomsten moet zoeken, of "TRUE" - als de waarden bij benadering kunnen zijn. Voor ons voorbeeld kiezen we de eerste. Als u in dit veld niets opgeeft, wordt standaard het tweede geselecteerd. Klik vervolgens op "OK".

Let hierbij op het volgende: werkt u met cijfers en geeft u “Waar” op, dan moet de tweede tabel (dit is onze prijslijst) oplopend gesorteerd worden. Bij het zoeken naar bijvoorbeeld 5.25 wordt 5.27 gevonden en worden de gegevens uit deze regel overgenomen, al kan er ook een getal onder 5.2599 staan ​​- maar de formule zoekt niet verder, omdat hij denkt dat de onderstaande getallen alleen maar groter zijn .

Hoe werkt VERT.ZOEKEN? Het neemt de gewenste waarde (appel) en zoekt ernaar in de meest linkse kolom van het opgegeven bereik (lijst met fruit). Als er een overeenkomst is, wordt de waarde uit dezelfde rij gehaald, alleen de kolom gespecificeerd in de argumenten (2), en overgebracht naar de cel die we nodig hebben (C2). De formule ziet er als volgt uit:

VERZOEKEN(A2,$G$2:$H$12,2,FALSE)

Nu kunt u het het vereiste aantal lijnen uitrekken door aan de rechter benedenhoek te trekken.

Alle prijzen zijn conform de namen van de vruchten van de prijslijst naar de inkooptabel overgebracht.

Als je in de eerste tabel de namen hebt van producten die niet in de prijslijst staan ​​(voor mij zijn dit groenten), dan zal tegenover deze punten de VLOOKUP-formule de fout #N/A opleveren.

Terwijl u kolommen aan het werkblad toevoegt, veranderen de gegevens voor het Tabel-argument van de functie automatisch. In het voorbeeld wordt de prijs 2 kolommen naar rechts verschoven. Laten we een willekeurige cel met een formule selecteren en zien dat in plaats van $G$2:$H$12 het nu $I$2:$J$14 is.

Laten we nu eens kijken hoe we met de functie VERT.ZOEKEN in Excel en met vervolgkeuzelijsten kunnen werken. Eerst moet u een vervolgkeuzelijst maken. Selecteer de cel, open bovenaan “Gegevens” en klik op de knop "Gegevenscontrole".

In het geopende venster "Gegevenstype" zal er "Lijst" zijn, hieronder geven we het brongebied aan - dit zijn de namen van fruit, dat wil zeggen de kolom die zowel in de eerste als in de tweede tabel staat. Klik op "OK".

Ik selecteer F2 en voeg de functie VERT.ZOEKEN in. Het eerste argument is de gemaakte lijst (F1).

De tweede is een leverancierstabel met aangegeven prijzen. Houd er rekening mee dat de linkerkolom hier moet overeenkomen met de gegevens waaruit de vervolgkeuzelijst bestaat.

Het bleek zoiets als een zoektocht: we selecteren een vrucht en VPR vindt de prijs ervan in de prijslijst.

Als je dezelfde prijzen had en de leveranciers je een nieuwe lijst gaven, dan moet je op de een of andere manier zien wat er is veranderd. Handmatig doen kost veel tijd, maar met behulp van de betreffende functie kan alles heel snel gedaan worden. U moet één kolom toevoegen en er nieuwe waarden naar overbrengen, en vervolgens eenvoudig de gegevens vergelijken.

Klik op een cel in kolom D en voeg een nieuwe in.

Ik noemde het Nieuwe Prijs - de nieuwe prijzen zullen hier staan, en de oude prijzen zullen in de kolom aan de linkerkant worden getoond. Mijn nieuwe tabel bevindt zich op een ander blad, zodat u kunt begrijpen hoe u VERT.ZOEKEN kunt gebruiken als de gegevens zich op verschillende bladen bevinden. Selecteer in de toegevoegde kolom de eerste lege cel.

We voegen de functie in en specificeren de argumenten. Eerst waar we naar zullen zoeken, in het voorbeeld een appel (A2). Om een ​​bereik uit de nieuwe prijslijst te selecteren, plaatst u de cursor in het veld “Tabel” en gaat u naar het gewenste blad, ik heb “Blad1”.

Gebruik de muis om de gewenste kolommen en rijen te selecteren, zonder kopjes.

Vervolgens maken we absolute verwijzingen naar cellen: “Blad1!$A$2:$B$12” . Selecteer de regel en druk op "F4" om een ​​dollarteken aan de celadressen toe te voegen. We geven kolom (2) aan en schrijven “FALSE”.

Klik aan het einde op de knop "OK".

Nu staan ​​twee kolommen met de nieuwe en oude prijzen naast elkaar en kunt u een visuele vergelijking maken, of door bepaalde formules toe te passen, of voorwaardelijke opmaak.

Ik hoop dat ik stapsgewijze instructies heb gegeven over het gebruik en toepassen van de functie VERT.ZOEKEN in Excel, en nu is alles duidelijk voor je.

Beoordeel dit artikel:

(13 beoordelingen, gemiddeld: 5,00 van de 5)

Webmaster. Hoger onderwijs met een graad in informatiebeveiliging. Auteur van de meeste artikelen en computerlessen

We weten allemaal hoe nuttig het is. Waarschijnlijk wordt de helft van alle acties in Excel ermee uitgevoerd. Deze functie heeft echter een aantal beperkingen. Een VERT.ZOEKEN kijkt bijvoorbeeld alleen naar de meest linkse kolom in een tabel of zoekt slechts naar één voorwaarde. Maar wat als we een waarde moeten retourneren die aan twee voorwaarden voldoet? In dit geval zullen we onze toevlucht moeten nemen tot enkele trucjes. We zullen over deze trucs praten in het artikel van vandaag.

We zullen dus kijken naar vier opties voor het creëren van een substitutiefunctie met twee voorwaarden:

1. De functie SUMPRODUCT gebruiken

Nou, we beginnen met het eenvoudigste.

Een extra kolom gebruiken

In de meeste gevallen worden complexe problemen eenvoudiger en beter beheersbaar als je ze in kleine stukjes opsplitst. Hetzelfde geldt voor het bouwen van formules in Excel.

Laten we eens kijken naar een klassiek voorbeeld. We hebben een tabel met verkopen per maand en stad. En we moeten de verkoopwaarde bepalen die overeenkomt met twee voorwaarden: maand - februari en stad - Samara.

Het gebruik van de functie VERT.ZOEKEN in zijn klassieke vorm zal ons niet helpen, omdat deze alleen een waarde kan retourneren die aan slechts één voorwaarde voldoet. Een extra kolom zal ons helpen uit deze situatie te komen, waarin we de waarden van de kolommen Maand en Stad zullen combineren. Om dit te doen, schrijft u de formule =B2&C2 in cel A2 en breidt u deze formule uit naar cel A13. Nu kunnen we de waarden van kolom A gebruiken om de vereiste waarde te retourneren. We schrijven de formule in cel G3:

VERZOEKEN(G1A2:D13;4;0)

Deze formule combineert de twee voorwaarden van de cellen G1 en G2 in één rij en bekijkt deze in kolom A. Zodra de gewenste voorwaarde is gevonden, retourneert de formule de waarde uit de vierde kolom van tabel A1:D13, d.w.z. kolom Verkoop.



Gebruik de SELECT-functie om een ​​nieuwe opzoektabel te maken

Als het gebruik van een extra kolom om wat voor reden dan ook geen optie voor ons is, kunnen we .

Als u de functie SELECT gebruikt, wordt er een nieuwe tabel gemaakt waarin u de kolomwaarden kunt bekijken Maand En Stad al samengevoegd. Onze formule zal er als volgt uitzien:

VERZOEKEN(G1SELECTIE((1,2),B2:B13&C2:C13,D2:D13),2,0)

Het belangrijkste punt van deze formule is het gedeelte CHOICE((1;2);B2:B13&C2:C13;D2:D13), dat twee dingen doet:

1. Combineert kolomwaarden Maand En Stad in één array: JanMoskou, FebMoskou...
2. Voegt twee arrays samen tot een tabel met twee kolommen.

Het resultaat van deze functie is een tabel die er als volgt uitziet:



Nu is de formule duidelijker geworden.

BELANGRIJK: Omdat we een matrixformule hebben gebruikt, kunt u, wanneer u klaar bent met het invoeren van de formule, op Ctrl+Shift+Enter drukken om het programma onze bedoelingen te laten weten. Nadat u op deze toetsencombinatie heeft gedrukt, plaatst het programma automatisch accolades aan het begin en einde van de formule.

Gebruik van de INDEX- en MATCH-functies

De derde methode, die we zullen overwegen, omvat ook het gebruik van een matrixformule en maakt gebruik van de functies INDEX en SEARCH.

De formule zal er als volgt uitzien.

INDEX(D2:D13,VERGELIJKEN(1,(B2:B13=G1)*(C2:C13=G2),0))

Laten we eens kijken naar wat elk onderdeel van deze formule doet.

Bekijk eerst de functie VERGELIJKEN(1;(B2:B13=G1)*(C2:C13=G2);0). In dit geval wordt de waarde van cel G1 opeenvolgend vergeleken met elke waarde van cellen in het bereik B2:B13 en retourneert TRUE als de waarden overeenkomen en FALSE als dat niet het geval is. Dezelfde vergelijking wordt gemaakt met de waarde van cel G2 en het bereik C2:C13. Vervolgens vergelijken we beide arrays, bestaande uit TRUE en FALSE. De combinatie WAAR * WAAR geeft ons het resultaat 1 (WAAR). Laten we naar de onderstaande afbeelding kijken, die zal helpen het werkingsprincipe duidelijker uit te leggen.



Nu kunnen we zien waar de string die aan beide voorwaarden voldoet, zich bevindt. De functie MATCH vindt de positie van 1 in de resultaatarray en retourneert 6 omdat de 1 voorkomt in de zesde rij. Vervolgens retourneert de functie INDEX de waarde van de zesde rij van het bereik D2:D13.

SUMPRODUCT gebruiken

Een van de krachtigste Excel-formules. Ik heb zelfs een apart artikel gewijd aan deze formule. Onze vierde manier om meerdere voorwaarden te gebruiken is door een formule te schrijven met de functie SUMPRODUCT. En het zal er zo uitzien:

SOMPRODUCT((B2:B13=G1)*(C2:C13=G2);D2:D13)

Het werkingsprincipe van deze formule is vergelijkbaar met het werkingsprincipe van de vorige benadering. Er wordt een virtuele tabel gemaakt waarin de waarden van de cellen G1 en G2 worden vergeleken met respectievelijk de bereiken B2:B13 en C2:C13. Vervolgens worden beide arrays vergeleken en wordt een array van enen en nullen verkregen, waarbij er één wordt toegewezen aan de rij waarin beide voorwaarden overeenkomen. Vervolgens wordt deze virtuele array vermenigvuldigd met het bereik D2:D13. Omdat onze virtuele array slechts één 1 in de zesde rij heeft, retourneert de formule het resultaat 189.

Deze functie werkt niet als er tekstwaarden zijn in het bereik D2:D13.

Om te begrijpen hoe deze formule werkt, raad ik aan het artikel over de SUMPROIZ-functie te lezen.

RESULTAAT

Dus welke methode moet je gebruiken? Hoewel ze allemaal betrouwbaar werken, geef ik de voorkeur aan de eerste methode. In mijn dagelijkse werk werk ik het liefst met bestanden die gemakkelijk te begrijpen en veranderlijk zijn. Beide vereisten voldoen aan de voorwaarden van de eerste benadering.

Voor een beter begrip van de formules kunt u ze in het artikel van vandaag bekijken.