In dit artikel leren we hoe u meerdere exemplaren van een waarde in Excel kunt opzoeken.
Waarden opzoeken met behulp van de vervolgkeuzelijst?
Hier begrijpen we hoe we verschillende resultaten kunnen opzoeken met behulp van de INDEX-functiematrixformule. Selecteer gewoon de waarde uit de lijst en het bijbehorende resultaat zal daar zijn.
Generieke formule
| {=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(eerste cel van lookup_value_range)+1),ROW(1:1)))} |
Reeks: Het bereik van waaruit u gegevens wilt ophalen.
opzoekwaarde: Uw lookup_value die u wilt filteren.
lookup_value_range: Het bereik waarin u lookup_value wilt filteren.
De eerste cel in het bereik van lookup_value: als uw opzoekwaardebereik $A$5:$A$100 is, dan is het $A$5.
Belangrijk: Alles zou moeten zijn absoluut gerefereerd. opzoekwaarde kan relatief zijn volgens de vereiste.
Voer het in als een matrixformule. Gebruik na het schrijven van de formule de toets CTRL+SHIFT+ENTER om er een matrixformule van te maken.
Voorbeeld van opzoeken voor meerdere resultaten
Ik heb deze leerlinggegevens binnen bereik A2:E14. In cel G1 heb ik een vervolgkeuzelijst met regiowaarden, b.v. Centraal, Oost, Noord, Zuid en West. Nu wil ik, welke regio ik ook heb in G1, een lijst van alle studenten uit die regio moet worden weergegeven in de H-kolom.
Laten we onze variabelen identificeren om meerdere waarden in Excel op te zoeken.
Reeks: $C$2:$C$14
opzoekwaarde : $G$1
lookup_value_range : $A$2:$A$14
De eerste cel van lookup_value bereik: $A$2
Volgens de bovenstaande gegevens is onze formule om meerdere waarden in Excel op te halen:
| {=IFERROR(INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1) ,RIJ(1:1))),"Geen waarde meer")} |
Dit is een matrixformule. Gebruik niet alleen Enter na het typen van de formule. Gebruik CTRL + SHIFT + ENTER samen.
Laten we nu begrijpen HOE HET WERKT.
Ook al ziet de formule er misschien ingewikkeld uit, het idee is simpel. We moeten het indexnummer van elk voorkomen van waarde ophalen en vervolgens waarden ophalen met behulp van de INDEX-functie van Excel.
Daarom is de grootste uitdaging het verkrijgen van een reeks indexnummers van opzoekwaarde. Om indexnummers te krijgen, gebruikten we IF- en ROW-functies. De formule is inderdaad helemaal complex, laten we het opsplitsen.
We willen waarden uit de studentenkolom halen, dus onze array voor de INDEX-functie is $C$2:$C$14.
Nu moeten we rijnummers geven van $A$2:$A$14 (opzoekwaarde) waarin de waarde van G1 bestaat (voor nu, laten we zeggen G1 heeft een centraal in zich).
ALS ($ G $ 1 = $ A $ 2: $ A $ 14, RIJ ($ A $ 2: $ A $ 14): Nu, dit deel retourneert het rijnummer als de waarde van een celtelling van G1 (centraal) binnen bereik $A$2:$A$14 anders keert terug ONWAAR. In dit voorbeeld keert het terug
{2;FALSE;FALSE;FALSE;FALSE;7;8;FALSE;FALSE;11;FALSE;FALSE;FALSE}.
Omdat de bovenstaande array rijnummers bevat van de 1e rij (1:1) en we rijen nodig hebben die beginnen bij onze array (A2:A14). Om dit te doen, gebruiken we -ROW($A$2)+1 in de IF-formule. Dit retourneert een aantal rijen voordat onze array wordt gestart.
Voor dit voorbeeld is dat -1. Als het vanaf A3 zou beginnen, zou het -2 retourneren, enzovoort. Dit getal wordt afgetrokken van elk getal in de array die wordt geretourneerd door IF. Dus eindelijk ALS ($ G $ 1 = $ A $ 2: $ A $ 14, RIJ ($ A $ 2: $ A $ 14) - RIJ ($ A $ 2) + 1) dit zal vertalen naar {1;FALSE;FALSE;FALSE;FALSE;6;7;FALSE;FALSE;10;FALSE;FALSE;FALSE}.
Vervolgens wordt deze array omringd door de SMALL-functie. Deze functie retourneert de N-de kleinste waarde in de gegeven array. Nu hebben we KLEINE({2;FALSE;FALSE;FALSE;FALSE;7;8;FALSE;FALSE;11;FALSE;FALSE;FALSE},RIJ (1:1)). ROW(1:1) retourneert 1. Daarom retourneert de bovenstaande functie de 1e kleinste waarde in de array, namelijk 2.
Wanneer u deze formule in de onderstaande cellen kopieert, wordt ROW(1:1) ROW(2:2) en wordt de op één na kleinste waarde in de array geretourneerd, namelijk 7 enzovoort. Hierdoor kan de functie eerst de eerst gevonden waarde retourneren. Maar als u eerst de laatst gevonden waarde wilt krijgen, gebruik dan de LARGE-functie in plaats van de SMALL-functie.
De INDEX-functie gebruikt nu waarden die zijn geretourneerd boven functies en retourneert eenvoudig elke overeenkomende waarde uit een bereik.
U kunt ook exacte overeenkomsten opzoeken met behulp van de INDEX- en MATCH-functie in Excel. Meer informatie over hoe u hoofdlettergevoelige opzoekingen uitvoert met de functie INDEX & VERGELIJKEN in Excel. U kunt de gedeeltelijke overeenkomsten ook opzoeken met behulp van de jokertekens in Excel.
Ik hoop dat dit artikel over het opzoeken van meerdere exemplaren van een waarde in Excel verhelderend is. Vind hier meer artikelen over het berekenen van waarden en gerelateerde Excel-formules. Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite.
VERT.ZOEKEN gebruiken vanuit twee of meer opzoektabellen | Om uit meerdere tabellen op te zoeken, kunnen we een IFERROR-benadering gebruiken. Om uit meerdere tabellen op te zoeken, wordt de fout gebruikt als een schakeloptie voor de volgende tabel. Een andere methode kan een If-benadering zijn.
Hoe hoofdlettergevoelig opzoeken in Excel te doen | de functie VERT.ZOEKEN van Excel is niet hoofdlettergevoelig en retourneert de eerste overeenkomende waarde uit de lijst. INDEX-MATCH is geen uitzondering, maar het kan worden aangepast om het hoofdlettergevoelig te maken. Laten we eens kijken hoe…
Veelvoorkomende tekst opzoeken met criteria in Excel | De zoekopdracht verschijnt het vaakst in tekst in een bereik dat we de INDEX-MATCH met MODE-functie gebruiken. Hier is de methode.
Populaire artikels :
Hoe de IF-functie in Excel te gebruiken? : De IF-instructie in Excel controleert de voorwaarde en retourneert een specifieke waarde als de voorwaarde WAAR is of retourneert een andere specifieke waarde als ONWAAR.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.