Om welke reden dan ook, als u een bewerking wilt uitvoeren op een bereik met bepaalde indexen, zult u denken aan het gebruik van de INDEX-functie om een reeks waarden op specifieke indexen te krijgen. Zoals dit.
Laten we het begrijpen met een voorbeeld.
Hier heb ik een kolom Tarief. Ik wil waarden optellen op positie 1,3 en 5. Ik schrijf deze formule in C2.
=SOM(INDEX(A2:A11,{1,3,5}))
Verrassend genoeg retourneert de functie INDEX geen array met waarden wanneer we een array als indexen leveren. We krijgen alleen de eerste waarde. En de functie SOM retourneert de som van slechts één waarde.
Dus hoe krijgen we de INDEX-functie die array retourneert. Nou, ik vond deze oplossing op internet en ik begrijp niet echt hoe het werkt, maar het werkt.
De generieke formule om een reeks indexen te krijgen
=SOM(INDEX(bereik,N(IF(1,{getallen})))
Bereik: Het is het bereik waarin u wilt zoeken bij bepaalde indexnummers
Nummer: dit zijn de indexcijfers.
Laten we nu bovenstaande formule toepassen op ons voorbeeld.
Schrijf deze formule in C2.
=SOM(INDEX(A2:A11,N(IF(1,{1,3,5})))
En dit geeft het juiste antwoord als 90.
Hoe het werkt.
Welnu, zoals ik al zei, ik weet niet waarom het werkt en geeft de reeks getallen terug, maar hier wat er gebeurt.
IF(1,{1,3,5}): Dit deel retourneert de array {1,3, 5} zoals verwacht
N(IF(1,{1,3,5})) dit vertaalt zich naar N({1,3,5}) wat ons weer {1,3,5} geeft
OPMERKING: De functie N retourneert het getalequivalent van elke waarde. Als een waarde niet in getallen kan worden omgezet, wordt 0 geretourneerd (zoals tekst). En voor fouten retourneert het een fout.
INDEX((A2:A11,N(IF(1,{1,3,5}))): dit deel vertaalt zich naar INDEX((A2:A11,{1,3,5}) en op de een of andere manier retourneert dit de array { 10,30,50} Zoals je eerder hebt gezien wanneer je INDEX((A2:A11,{1,3,5}) direct in de formule schrijft, geeft het alleen 10 terug. Als je het begrijpt, laat het me dan weten in de comments hieronder.
Hoe celverwijzingen voor indexwaarden te gebruiken om een array te krijgen
In het bovenstaande voorbeeld hebben we de indexen hard gecodeerd in functie. Maar wat als ik het wil hebben van een bereik dat kan veranderen. We vervangen {1,3,5} door bereik? Laten we het proberen.
Hier hebben we deze formule in cel D2:
=SOM(INDEX(A2:A11,N(IF(1,A2:A5)))
Dit retourneert 10. De allereerste waarde van de opgegeven index. Zelfs als we het als matrixformule invoeren met CTRL+SHIFT+ENTER, geeft het hetzelfde resultaat.
Om het te laten werken, voegt u + of -- (dubbele unaire) operator toe voor bereik en voert u het in als matrixformule.
{=SOM(INDEX(A2:A11,N(IF(1,+A2:A5)))}
Dit werkt. Vraag me niet hoe? Het werkt gewoon. Als u me kunt vertellen hoe ik uw naam en uitleg hier zal opnemen.
Dus we hebben geleerd hoe we een array uit de INDEX-functie kunnen halen. Er gebeuren gekke dingen hier in Excel. Als je kunt uitleggen hoe het hier werkt in de opmerkingen hieronder, zal ik het in mijn uitleg opnemen met je naam.
Download bestand:
Gerelateerde artikelen:
Arrays in Excel-formule
Gebruik INDEX en MATCH om waarde op te zoeken
Hoe de LOOKUP-functie in Excel te gebruiken?
Opzoekwaarde met meerdere criteria
Populaire artikels:
De VERT.ZOEKEN-functie in Excel
AANTAL.ALS in Excel 2016
Hoe de SUMIF-functie in Excel te gebruiken?