Generieke formule
=OFFSET($B$5,(ROW(A1)*n)-1,0)
Samenvatting
Om waarden te kopiëren of verwijzingen te genereren met een patroon zoals elke 3e rij, elke 7e regel, enz., Kunt u een formule gebruiken die is gebaseerd op de functies OFFSET en ROW. In het getoonde voorbeeld is de formule in D5:
=OFFSET($B$5,(ROW(D1)*3)-1,0)
Die naar beneden in kolom D kan worden gekopieerd om n-de waarden uit kolom B op te halen.
Uitleg
In Excel kunt u formules niet gemakkelijk kopiëren die rijen of kolommen volgens een bepaald patroon moeten overslaan, omdat de verwijzingen in de formule automatisch veranderen volgens de relatie tussen de oorspronkelijke broncel en de nieuwe doelcel. Met een beetje werk is het echter mogelijk om formule-verwijzingen te construeren die specifieke patronen volgen.
In dit geval gebruiken we de OFFSET-functie, die is ontworpen om verwijzingen naar andere cellen of celbereiken op te bouwen op basis van een bepaald startpunt of oorsprong.
In het getoonde voorbeeld is de startcel B5, verstrekt aan OFFSET als het referentieargument en gefixeerd als een absolute referentie, zodat deze niet verandert als de formule wordt gekopieerd:
=OFFSET($B$5
Om de juiste waarde voor het argument rijen te berekenen, gebruiken we een subformule op basis van de functie RIJ:
(ROW(D1)*3)-1
Rij krijgt de eerste cel in dezelfde kolom en retourneert 1. Dit resultaat wordt vermenigvuldigd met n, wat in dit voorbeeld 3 is om 3 te krijgen.
Terwijl de formule door de kolom wordt gekopieerd, wordt de waarde die wordt geretourneerd door ROW met 1 verhoogd, wat het "n-de patroon" creëert.
De reden dat we in elk geval 1 aftrekken, is omdat de OFFSET-functie de referentiecel niet bevat wanneer het rijenargument wordt toegepast. Met andere woorden, compensatie met één rij vanaf A1 geeft A2 terug:
=OFFSET(A1,1,0) // returns A2
Bij het aftrekken van 1 wordt rekening gehouden met dit gedrag.
Vanaf 1
Als je wilt beginnen met kopiëren op de eerste rij, volg dan het n-de patroon, je kunt de formule als volgt aanpassen:
=OFFSET($B$5,(ROW(A1)-1)*n,0)
