De hele rij opzoeken en ophalen
Om een hele rij gegevens op te zoeken en op te halen door een specifieke waarde te matchen, kunt u de gebruiken INDEX en MATCH functies om een matrixformule te maken.
Een hele rij opzoeken en ophalen op basis van een specifieke waarde
Tel een hele rij op op basis van een specifieke waarde
Verdere analyse van een hele rij op basis van een specifieke waarde
Een hele rij opzoeken en ophalen op basis van een specifieke waarde
Om het te krijgen lijst van verkopen gemaakt door Jimmy volgens de bovenstaande tabel kunt u eerst de MATCH-functie gebruiken om de positie van de verkopen van Jimmy terug te geven, en die naar INDEX zullen worden gevoerd om de waarden op de positie op te halen.
Algemene syntaxis
=INDEX(return_range,MATCH(lookup_value,lookup_array,0),0)
√ Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter.
- return_range: Het bereik dat de hele rij bevat die u wilt retourneren. Hier wordt verwezen naar het verkoopassortiment.
- opzoekwaarde: De waarde die de combinatieformule heeft gebruikt om de bijbehorende verkoopinformatie te vinden. Hier verwijst naar de opgegeven verkoper.
- lookup_array: Het bereik van cellen waar de overeen moet komen opzoekwaarde. Hier verwijst naar het naambereik.
- match_type 0: Dwingt MATCH om de eerste waarde te vinden die exact gelijk is aan de opzoekwaarde.
Om de lijst met verkopen gemaakt door Jimmy, kopieer of voer de onderstaande formule in cel I6 in, druk op Ctrl + Shift + Enter, en dubbelklik vervolgens op de cel en druk op F9 om het resultaat te krijgen:
=INDEX(C5:F11,BIJ ELKAAR PASSEN("Jimmy",B5: B11,0),0)
Of gebruik een celverwijzing om de formule dynamisch te maken:
=INDEX(C5:F11,BIJ ELKAAR PASSEN(I5,B5: B11,0),0)
Verklaring van de formule
=INDEX(C5:F11,MATCH(I5,B5:B11,0),0)
- OVEREENKOMST(I5,B5:B11,0): De match_type 0 dwingt de MATCH-functie om de positie van . te retourneren Jimmy, de waarde in I5, binnen bereik B5: B11Dit is 7 sinds Jimmy is bij de 7th positie.
- INHOUDSOPGAVE(C5:F11,GELIJK(I5,B5:B11,0),0) = INDEX(C5:F11,7,0): De functie INDEX retourneert alle waarden in de 7e rij van het assortiment C5:F11 in een array als deze: 6678,3654,3278,8398 {}. Houd er rekening mee dat om de matrix zichtbaar te maken in Excel, u moet dubbelklikken op de cel waar u de formule hebt ingevoerd en vervolgens op drukt F9.
Tel een hele rij op op basis van een specifieke waarde
Nu we alle verkoopinformatie hebben gemaakt door Jimmy, om de jaarlijkse verkoopvolume gemaakt door Jimmy, we kunnen gewoon de SOM-functie aan de formule toevoegen om alle verkoopwaarden uit de lijst op te tellen.
Algemene syntaxis
=SUM(INDEX(return_range,MATCH(lookup_value,lookup_array,0),0))
In dit voorbeeld, om de jaarlijkse verkoopvolume gemaakt door Jimmy, kopieer of voer de onderstaande formule in cel I7 in en druk op Enter om het resultaat te krijgen:
=SOM(INDEX(C5:F11,BIJ ELKAAR PASSEN(I5,B5: B11,0),0))
Verklaring van de formule
=SUM(INDEX(C5:F11,MATCH(I5,B5:B11,0),0))
- OVEREENKOMST(I5,B5:B11,0): De match_type 0 dwingt de MATCH-functie om de positie van . te retourneren Jimmy, de waarde in I5, binnen bereik B5: B11Dit is 7 sinds Jimmy is bij de 7th positie.
- INHOUDSOPGAVE(C5:F11,OVEREENKOMST(I5,B5:B11,0), 0) = INHOUDSOPGAVE(C5:F11,7, 0): De functie INDEX retourneert alle waarden in de 7e rij van het assortiment C5:F11 in een array als deze: 6678,3654,3278,8398 {}. Houd er rekening mee dat om de matrix zichtbaar te maken in Excel, u moet dubbelklikken op de cel waar u de formule hebt ingevoerd en vervolgens op drukt F9.
- SOM(INHOUDSOPGAVE(C5:F11,OVEREENKOMST(I5,B5:B11,0)),0) = SOM(6678,3654,3278,8398 {}): De SOM-functie telt alle waarden in de array op en krijgt dan het jaarlijkse verkoopvolume van Jimmy, $ 22,008.
Verdere analyse van een hele rij op basis van een specifieke waarde
Voor extra verwerking van de verkopen van Jimmy kunt u eenvoudig andere functies zoals SUM, AVERAGE, MAX, MIN, LARGE, enz. aan de formule toevoegen.
Om bijvoorbeeld de gemiddelde van Jimmy's omzet voor elk kwartaal, kunt u de formule gebruiken:
=GEMIDDELDE(INDEX(C5:F11,BIJ ELKAAR PASSEN(I5,B5: B11,0),0))
Om erachter te komen laagste omzet gemaakt door Jimmy, gebruik een van de onderstaande formules:
=MIN(INDEX(C5:F11,BIJ ELKAAR PASSEN(I5,B5: B11,0),0))
OR
=KLEIN(INDEX(C5:F11,BIJ ELKAAR PASSEN(I5,B5: B11,0),0),1)
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.
Verwante formules
De hele kolom opzoeken en ophalen
Om een hele kolom op te zoeken en op te halen door een specifieke waarde te matchen, zal een INDEX- en MATCH-formule u een plezier doen.
Exacte match met INDEX en MATCH
Als u de in Excel vermelde informatie over een specifiek product, film of persoon, enz. wilt achterhalen, moet u goed gebruik maken van de combinatie van INDEX- en MATCH-functies.
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.
U weet misschien dat u de functies INDEX en VERGELIJKEN kunt combineren, of de functie VERT.ZOEKEN kunt gebruiken om waarden op te zoeken in Excel. De zoekopdrachten zijn echter niet hoofdlettergevoelig. Dus om een hoofdlettergevoelige match uit te voeren, moet u profiteren van de EXACT- en KIEZEN-functies.
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Wilt u uw dagelijkse werkzaamheden snel en perfect afronden? Kutools voor Excel biedt 300 krachtige geavanceerde functies (werkmappen combineren, optellen op kleur, celinhoud splitsen, datum converteren, enzovoort ...) en 80% tijd voor u besparen.
- Ontworpen voor 1500 werkscenario's, helpt u 80% Excel-problemen op te lossen.
- Verminder elke dag duizenden toetsenbord- en muisklikken, verlicht uw vermoeide ogen en handen.
- Word een Excel-expert in 3 minuten. U hoeft geen pijnlijke formules en VBA-codes meer te onthouden.
- 30 dagen onbeperkte gratis proefperiode. 60 dagen geld-terug-garantie. Gratis upgrade en ondersteuning gedurende 2 jaar.
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, Firefox en New Internet Explorer.
