De hele kolom opzoeken en ophalen
Om een hele kolom op te zoeken en op te halen door een specifieke waarde te matchen, INDEX en MATCH formule zal u een plezier doen.
Een hele kolom opzoeken en ophalen op basis van een specifieke waarde
Som een hele kolom op op basis van een specifieke waarde
Verdere analyse van een hele kolom op basis van een specifieke waarde
Een hele kolom opzoeken en ophalen op basis van een specifieke waarde
Om een te krijgen lijst met verkopen in het tweede kwartaal volgens de bovenstaande tabel kunt u eerst de MATCH-functie gebruiken om de positie van de Q2-verkoop terug te geven, en die zal worden ingevoerd in INDEX om de waarden op de positie op te halen.
Algemene syntaxis
=INDEX(return_range,0,MATCH(lookup_value,lookup_array,0))
√ Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter.
- retour_bereik: Het bereik waarvan u wilt dat de combinatieformule de verkooplijst voor het tweede kwartaal retourneert. Hier wordt verwezen naar het verkoopassortiment.
- opzoekwaarde: De waarde die de combinatieformule heeft gebruikt om de bijbehorende verkoopinformatie te vinden. Hier verwijst naar het opgegeven kwartaal.
- lookup_array: Het bereik van cellen waar de overeen moet komen opzoekwaarde. Hier verwijst naar de kwartaalkoppen.
- match_type 0: Dwingt MATCH om de eerste waarde te vinden die exact gelijk is aan de opzoekwaarde.
Om een lijst met verkopen in het tweede kwartaal, 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,0,VERGELIJKEN("Q2",C4:F4, 0))
Of gebruik een celverwijzing om de formule dynamisch te maken:
=INDEX(C5:F11,0,VERGELIJKEN(I5,C4:F4, 0))
Verklaring van de formule
=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))
- OVEREENKOMST(I5,C4:F4,0): De match_type 0 dwingt de MATCH-functie om de positie van . te retourneren Q2, de waarde in I5, binnen bereik C4:F4Dit is 2.
- INHOUDSOPGAVE(C5:F11, 0,OVEREENKOMST(I5,C4:F4,0)) = INDEX(C5:F11, 0,2): De functie INDEX retourneert alle waarden in de 2e kolom van het bereik C5:F11 in een array als deze: {7865;4322;8534;5463;3252;7683;3654}. 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 een hele kolom op op basis van een specifieke waarde
Omdat we nu de verkooplijst bij de hand hebben, om de Totaal verkoopvolume Q2 zou voor ons een gemakkelijke zaak zijn. Het enige wat we hoeven te doen is de SOM-functie aan de formule toe te voegen om alle verkoopwaarden uit de lijst op te tellen.
Algemene syntaxis
=SUM(INDEX(return_range,0,MATCH(lookup_value,lookup_array,0)))
In dit voorbeeld, om de Totaal verkoopvolume Q2, kopieer of voer de onderstaande formule in cel I8 in en druk op Enter om het resultaat te krijgen:
=SOM(INDEX(C5:F11,0,VERGELIJKEN(I5,C4:F4,0)))
Verklaring van de formule
=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
- OVEREENKOMST(I5,C4:F4,0): De match_type 0 dwingt de MATCH-functie om de positie van . te retourneren Q2, de waarde in I5, binnen bereik C4:F4Dit is 2.
- INHOUDSOPGAVE(C5:F11, 0,OVEREENKOMST(I5,C4:F4,0)) = INHOUDSOPGAVE(C5:F11, 0,2): De functie INDEX retourneert alle waarden in de 2e kolom van het bereik C5:F11 in een array als deze: {7865;4322;8534;5463;3252;7683;3654}.
- SOM(INHOUDSOPGAVE(C5:F11, 0,OVEREENKOMST(I5,C4:F4,0))) = SOM({7865;4322;8534;5463;3252;7683;3654}): De SOM-functie telt alle waarden in de array op en krijgt vervolgens het totale verkoopvolume voor Q2, $40,773.
Verdere analyse van een hele kolom op basis van een specifieke waarde
Voor extra verwerking op de Q2-verkooplijst kunt u eenvoudig andere functies zoals SOM, GEMIDDELDE, MAX, MIN, LARGE, enz. aan de formule toevoegen.
Om bijvoorbeeld een gemiddeld verkoopvolume tijdens Q2, kunt u de formule gebruiken:
=GEMIDDELDE(INDEX(C5:F11,0,VERGELIJKEN(I5,C4:F4,0)))
Om erachter te komen hoogste omzet tijdens Q2, gebruik een van de onderstaande formules:
=MAX(INDEX(C5:F11,0,VERGELIJKEN(I5,C4:F4,0)))
OR
=GROOT(INDEX(C5:F11,0,VERGELIJKEN(I5,C4:F4,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 rij opzoeken en ophalen
Als u een hele rij gegevens wilt opzoeken en ophalen door een specifieke waarde te matchen, kunt u de functies INDEX en MATCH gebruiken om een matrixformule te maken.
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
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.
