Завантажте зразок робочої книги
Цей підручник покаже, як збільшити посилання на клітинки за допомогою функції OFFSET у Excel та Google Таблицях.
Збільшити посилання на клітинку
Під час копіювання формул у рядках або стовпцях, якщо посилання на клітинку не заблоковані, вони будуть рухатися пропорційно. Якщо ви хочете збільшити посилання на клітинки з різними кроками, ви можете скористатися функцією OFFSET.
Збільшити посилання на клітинку у стовпці
Спочатку розглянемо приклад. У цьому прикладі буде збільшуватися посилання на клітинки на 4 рядки одночасно за допомогою функцій OFFSET і ROW:
1 | = OFFSET ($ B $ 2, (ROW (B2) -1)*4,0) |
Давайте подивимось, як саме працює ця формула.
Функція ROW
Якщо у вас є лише корисні дані у кожному nго рядок, вам потрібно збільшити посилання на клітинку на n. Ми можемо використовувати функцію ROW для пошуку відповідних номерів рядків.
1 | = (РЯД (B2) -1)*4 |
У наведеному вище прикладі нам потрібні лише дані з кожних 4го рядок (n = 4). Починаючи з заголовка вашого набору даних (тут, клітинка В2), використовуйте формулу вище, щоб знайти номери рядків для кожних 4го рядок.
Функція OFFSET
Функція OFFSET повертає значення комірки, яке є заданою кількістю рядків і стовпців із даної комірки.
1 | = ЗМІЩЕННЯ ($ B $ 2, D3,0) |
Тут ми починаємо з заголовка набору даних (комірка B2) і збільшуємо рядок на 4 (комірка D3). Оскільки ми залишаємось в одному стовпці, аргумент cols цієї функції встановлено на 0. $ s у “$ B $ 2” блокує посилання на клітинку, так що комірка E3 також починається з заголовка даних під час обчислення зміщення.
Поєднання цих функцій дає нам оригінальну формулу:
1 | = OFFSET ($ B $ 2, (ROW (B2) -1)*4,0) |
Збільшити посилання на клітинку в рядку
Якщо ви хочете збільшити посилання на клітинки більш ніж на 1 під час заповнення формул у рядку, ви можете об’єднати функцію OFFSET з функцією COLUMN.
1 | = ЗМІЩЕННЯ ($ B $ 2,0, (СТОЛОЧКА (B2) -1)*4) |
Ця формула працює точно так само, як і вище, для збільшення посилання на клітинку у стовпці. Єдина відмінність полягає в тому, що аргумент рядків функції встановлений на нуль, а аргумент cols збільшується на 4.
Збільшити посилання на клітинки в Таблицях Google
Ці формули працюють точно так само в Google Таблицях, як і в Excel.