Bekijk deze figuur:
Stel dat u hiervan een rapport wilt maken alsof u op Regio hebt gefilterd. Dat wil zeggen, als u filtert op Noord, ziet u:
Maar wat als u een op formules gebaseerde versie van hetzelfde wilde?
Dit is het resultaat dat u zoekt in de kolommen I: K:
Het is duidelijk hetzelfde rapport, maar er zijn hier geen gefilterde items. Als je een nieuw rapport over Oost zou willen, zou het leuk zijn om de waarde in G1 simpelweg in Oost te veranderen:
Hier is hoe het moet. Allereerst is het niet gedaan met VERT.ZOEKEN. Dus ik loog over de titel van deze techniek!
Kolom F werd niet eerder weergegeven en kan worden verborgen (of ergens anders worden verplaatst zodat het het rapport niet verstoort).
Wat wordt getoond in kolom F zijn de rijnummers van waar G1 wordt gevonden in kolom A; dat wil zeggen, welke rijen bevatten de waarde "Noord"? Deze techniek maakt gebruik van boven de cel, dus het moet beginnen in tenminste rij 2. Het overeenkomt met de waarde “Noorden” tegen kolom A, maar in plaats van de hele kolom, gebruik dan een offset-functie: OFFSET($A$1,F1,0,1000,1).
Aangezien F1 0 is, is OFFSET(A1,0,0,1000,1)dit A1: A1000. (De 1000 is willekeurig, maar groot genoeg om de klus te klaren - je kunt er elk ander nummer van maken).
De waarde 2 in F2 is waar de eerste "Noord" is. U wilt aan het einde ook de waarde van F1 weer optellen, maar dit is tot nu toe nul.
De 'magie' komt tot leven in cel F3. U weet al dat het eerste noorden in rij 2 wordt gevonden. U wilt dus beginnen met zoeken op twee rijen onder A1. U kunt dat doen door 2 op te geven als het tweede argument van de OFFSET-functie.
De formule in F3 verwijst automatisch naar de 2 die is berekend in cel F2: wanneer u de formule naar beneden kopieert, ziet u =OFFSET($A$1,F2,0,1000,1)welke OFFSET($A$1,2,0,1000,1)A3: A1000 is. Dus je vergelijkt Noord met dit nieuwe bereik en het vindt Noord in de derde cel van dit nieuwe bereik, dus de WEDSTRIJD geeft 3.
Door de waarde uit de cel erboven, F2, weer op te tellen, zie je de 3 plus de 2 of 5, de rij die het tweede noorden bevat.
Deze formule is ver genoeg ingevuld om alle waarden te krijgen.
Dat geeft je de rijnummers waar alle Noord-records zijn gevonden.
Hoe vertaal je die rijnummers naar de resultaten in de kolommen I tot en met K? Het wordt allemaal gedaan met een enkele formule. Voer deze formule I2: =IFERROR(INDEX(A:A,$F2),””). Kopieer naar rechts en kopieer vervolgens naar beneden.
Waarom IFERROR gebruiken? Waar is de fout? Let op cel F6 - het bevat # N / A (daarom zou je kolom F willen verbergen) omdat er na rij 15 geen Noorden meer zijn. Dus als kolom F een fout is, retourneer dan een spatie. Pak anders de waarde op uit kolom A (en indien rechts gevuld, B & C).
De $ F2 is een absolute verwijzing naar kolom F, dus het vulrecht verwijst nog steeds naar kolom F.
Dit gastartikel is van Excel MVP Bob Umlas. Het is een van zijn favoriete technieken uit zijn boek Excel Outside the Box.
Excel buiten de doos »
