Загальна формула
=OFFSET($B$5,(ROW(A1)*n)-1,0)
Резюме
Для копіювання значень або генерування посилань із шаблоном, як кожен 3-й рядок, кожен 7-й рядок тощо, ви можете використовувати формулу на основі функцій OFFSET та ROW. У наведеному прикладі формула в D5 має вигляд:
=OFFSET($B$5,(ROW(D1)*3)-1,0)
Які можна скопіювати в стовпець D до n-тих значень з колонки B.
Пояснення
В Excel ви не можете легко скопіювати формули, які потрібно пропустити рядки або стовпці за певним зразком, оскільки посилання у формулі автоматично змінюватимуться відповідно до співвідношення між початковою вихідною коміркою та новою цільовою коміркою. Однак, трохи попрацювавши, можна побудувати посилання на формули, які відповідають певним зразкам.
У цьому випадку ми використовуємо функцію OFFSET, яка призначена для побудови посилань на інші комірки або діапазони комірок на основі заданої початкової точки або початку.
У наведеному прикладі початковою коміркою є B5, надана OFFSET як еталонний аргумент і фіксована як абсолютна посилання, тому вона не буде змінюватися під час копіювання формули:
=OFFSET($B$5
Для обчислення правильного значення аргументу рядків ми використовуємо підформулу на основі функції РЯД:
(ROW(D1)*3)-1
Рядку дається перша комірка в тому ж стовпці і повертається 1. Цей результат множиться на n, що в цьому прикладі дорівнює 3, щоб отримати 3.
Коли формула копіюється вниз у стовпець, значення, яке повертається РЯДКОМ, збільшується на 1, що і створює "n-й шаблон".
Причиною того, що ми віднімаємо 1 у кожному випадку, є те, що функція OFFSET не включає посилальну комірку, коли застосовується аргумент рядків. Іншими словами, зміщення на один рядок з А1 повертає А2:
=OFFSET(A1,1,0) // returns A2
Віднімання 1 враховує цю поведінку.
Починаючи з 1
Якщо ви хочете розпочати копіювання з першого рядка, а потім дотримуйтесь кожного n-го шаблону, ви можете налаштувати формулу так:
=OFFSET($B$5,(ROW(A1)-1)*n,0)
