Všeobecný vzorec
=OFFSET($B$5,(ROW(A1)*n)-1,0)
Zhrnutie
Na kopírovanie hodnôt alebo generovanie referencií so vzorom ako každý 3. riadok, každý 7. riadok atď. Môžete použiť vzorec založený na funkciách OFFSET a ROW. V zobrazenom príklade je vzorec v D5:
=OFFSET($B$5,(ROW(D1)*3)-1,0)
Ktoré je možné skopírovať nadol do stĺpca D, aby sa získali n-té hodnoty zo stĺpca B.
Vysvetlenie
V programe Excel nemôžete ľahko kopírovať vzorce, ktoré potrebujú preskočiť riadky alebo stĺpce nasledujúce po určitom vzore, pretože odkazy vo vzorci sa automaticky zmenia podľa vzťahu medzi pôvodnou zdrojovou bunkou a novou cieľovou bunkou. Avšak s trochou práce je možné vytvoriť odkazy na vzorce, ktoré sa riadia konkrétnymi vzormi.
V tomto prípade používame funkciu OFFSET, ktorá je navrhnutá na vytváranie odkazov na iné bunky alebo rozsahy buniek na základe daného východiskového bodu alebo pôvodu.
V zobrazenom príklade je počiatočnou bunkou B5, ktorá je ako referenčný argument poskytnutá OFFSET a je fixovaná ako absolútny odkaz, takže sa pri kopírovaní vzorca nezmení:
=OFFSET($B$5
Na výpočet správnej hodnoty argumentu riadky použijeme podformulár založený na funkcii ROW:
(ROW(D1)*3)-1
Riadok má prvú bunku v rovnakom stĺpci a vráti hodnotu 1. Tento výsledok sa vynásobí číslom n, ktoré je v tomto príklade číslom 3 a získa hodnotu 3.
Keď je vzorec skopírovaný nadol do stĺpca, hodnota vrátená pomocou ROW sa zvyšuje o 1, čo vytvára „n-tý vzor“.
Dôvod, pre ktorý odčítame 1, je v každom prípade ten, že funkcia OFFSET nezahŕňa referenčnú bunku, keď sa použije argument riadky. Inými slovami, odsadenie o jeden riadok od A1 vráti A2:
=OFFSET(A1,1,0) // returns A2
Odčítaním 1 sa toto správanie zohľadňuje.
Počnúc 1
Ak chcete začať kopírovať v prvom riadku, postupujte podľa každého n-tého vzoru a vzorec môžete upraviť takto:
=OFFSET($B$5,(ROW(A1)-1)*n,0)
