Geschatte overeenkomst in twee richtingen met meerdere criteria
In deze zelfstudie zullen we het hebben over het zoeken naar een geschatte overeenkomst op basis van meerdere criteria die zowel in kolommen als rijen in een Excel-spreadsheet worden vermeld, met behulp vanf INDEX, MATCH en IF functies.
Hoe een tweerichtingsovereenkomst met meerdere criteria in Excel uit te voeren?
Om erachter te komen kledingmaat voor een vrouw wie is 165.5cm lang en weegt 55kg volgens de bovenstaande tabel, kunt u twee MATCH-functies aan de formule toevoegen: één met een ALS-functie krijgt het rijnummer (geslacht en lengte), de andere krijgt het kolomnummer (gewicht). Dan vindt INDEX de corresponderende maat volgens de coördinaten. (Merk op dat het rijnummer voor het kolomnummer staat in een INDEX-formule.)
Algemene syntaxis
=INDEX(return_range,MATCH(lookup_value1,IF(lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter.
- retour_bereik: Het bereik waarvan u wilt dat de combinatieformule de grootte retourneert. Hier verwijst naar het maatbereik.
- opzoekwaarde: De waarde die de formule heeft gebruikt om de positie van de overeenkomstige grootte te vinden. Hier wordt verwezen naar de informatie over het opgegeven geslacht, lengte en gewicht.
- lookup_array: Het cellenbereik met de waarden om te vergelijken met de opzoekwaarde. Hier verwijst naar het geslacht, de lengte en het gewicht.
- match_type: 1 of -1.
1 of weggelaten (standaard), MATCH vindt de grootste waarde die kleiner is dan of gelijk is aan de opzoekwaarde. De waarden in de zoek_array moet in oplopende volgorde worden geplaatst.
-1, MATCH vindt de kleinste waarde die groter is dan of gelijk is aan de opzoekwaarde. De waarden in de zoek_array moet in aflopende volgorde worden geplaatst.
Om erachter te komen kledingmaat voor de vrouw wie is 165.5cm lang en weegt 55kg, kopieer of voer de onderstaande formule in de cel M9 in en druk op Ctrl + Shift + Enter om het resultaat te krijgen:
=INDEX(D5:J16,BIJ ELKAAR PASSEN(M6,INDIEN(B5: B16=M5,C5: C16),1),OVEREENKOMST(M7,D4:J4, 1))
Verklaring van de formule
=INDEX(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- IF(B5:B16=M5,C5:C16): De ALS-functie controleert elke waarde in B5: B16 als ze overeenkomen met de waarde in de cel M5, Female. Als dat het geval is, houdt de IF-functie de overeenkomstige hoogtes in de lijst in C5: C16; Zo niet, dan keert IF terug ONWAAR. De formule retourneert een matrix zoals deze: {157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
- WEDSTRIJD(M6,IF(B5:B16=M5,C5:C16), 1) = WEDSTRIJD(M6,{157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, 1): De match_type 1 dwingt de MATCH-functie om de positie te retourneren van de grootste waarde die kleiner is dan of gelijk is aan 165.5 (de waarde in de cel M6) in de reeks. MATCH komt dus terug 4, de positie van de waarde 165.
- GELIJK(M7,D4:J4,1): De MATCH-functie retourneert 3, aangezien de grootste waarde die kleiner is dan of gelijk is aan de waarde in M7, 55, is bij de 3e positie van het bereik D4:J4.
- INHOUDSOPGAVE(D5:J16,WEDSTRIJD(M6,IF(B5:B16=M5,C5:C16), 1),GELIJK(M7,D4:J4,1)) = INDEX(D5:J16,4,3): De INDEX-functie haalt de waarde op het snijpunt van de 4de rij en 3rd kolom in het groottebereik D5:J16, wat de waarde in de cel is F8, M.
Gerelateerde functies
De Excel INDEX-functie retourneert de weergegeven waarde op basis van een bepaalde positie uit een bereik of een matrix.
De Excel MATCH-functie zoekt naar een specifieke waarde in een celbereik en retourneert de relatieve positie van de waarde.
De ALS-functie is een van de eenvoudigste en handigste functies in de Excel-werkmap. Het voert een eenvoudige logische test uit die afhankelijk is van het vergelijkingsresultaat, en het retourneert één waarde als een resultaat WAAR is, of een andere waarde als het resultaat FALSE is.
Verwante formules
Opzoeken in twee richtingen met INDEX en MATCH
Om iets te zoeken in zowel rijen als kolommen in Excel, of we zeggen om een waarde op te zoeken op de kruising van de specifieke rij en kolom, kunnen we de hulp van INDEX- en MATCH-functies gebruiken.
Dichtstbijzijnde overeenkomst opzoeken
Om te zoeken naar de beste overeenkomst van een opzoekwaarde in een numerieke dataset in Excel, kunt u de functies INDEX, MATCH, ABS en MIN samen gebruiken.
Zoek de meest overeenkomende waarde op met meerdere criteria
In sommige gevallen moet u mogelijk de dichtstbijzijnde of geschatte overeenkomstwaarde opzoeken op basis van meer dan één criterium. Met de combinatie van INDEX-, MATCH- en IF-functies kunt u het snel voor elkaar krijgen in Excel.
Opzoeken met meerdere criteria met INDEX en MATCH
Als je te maken hebt met een grote database in een Excel-spreadsheet met meerdere kolommen en rijbijschriften, is het altijd lastig om iets te vinden dat aan meerdere criteria voldoet. In dit geval kunt u een matrixformule gebruiken met de functies INDEX en VERGELIJKEN.
Geschatte overeenkomst met INDEX en MATCH
Er zijn momenten waarop we geschatte overeenkomsten in Excel moeten vinden om de prestaties van werknemers te evalueren, de scores van studenten te beoordelen, de verzendkosten te berekenen op basis van gewicht, enz. In deze tutorial zullen we het hebben over het gebruik van de INDEX- en MATCH-functies om de resultaten die we nodig hebben.
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...
Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)
- Een seconde om te schakelen tussen tientallen geopende documenten!
- Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Edge en Firefox.
