Загрузите образец книги
В этом руководстве будет показано, как увеличивать количество ссылок на ячейки с помощью функции СМЕЩЕНИЕ в Excel и Google Таблицах.
Ссылка на ячейку приращения
Если при копировании формул по строкам или столбцам ссылки на ячейки не заблокированы, они будут перемещаться пропорционально. Если вы хотите увеличивать ссылки на ячейки с разными приращениями, вы можете использовать функцию OFFSET.
Приращение ссылки на ячейку в столбце
Сначала рассмотрим пример. В этом примере ссылки на ячейки будут увеличиваться на 4 строки за раз с помощью функций OFFSET и ROW:
| 1 | = СМЕЩЕНИЕ ($ B $ 2, (СТРОКА (B2) -1) * 4,0) |
Посмотрим, как именно работает эта формула.
СТРОКА Функция
Если у вас есть только полезные данные в каждом nth row необходимо увеличить ссылку на ячейку на n. Мы можем использовать функцию ROW, чтобы найти соответствующие номера строк.
| 1 | = (СТРОКА (B2) -1) * 4 |
В приведенном выше примере нам нужны данные только из каждых 4th ряд (n = 4). Начиная с заголовка набора данных (здесь ячейка B2), используйте приведенную выше формулу, чтобы найти номера строк для каждых 4th ряд.
СМЕЩЕНИЕ Функция
Функция OFFSET возвращает значение ячейки, которое представляет собой указанное количество строк и столбцов из данной ячейки.
| 1 | = СМЕЩЕНИЕ ($ B $ 2, D3,0) |
Здесь мы начинаем с заголовка набора данных (ячейка B2) и увеличиваем строку на 4 (ячейка D3). Поскольку мы остаемся в том же столбце, аргумент cols этой функции установлен в 0. $ s в «$ B $ 2» блокирует ссылку на ячейку, так что ячейка E3 также начинается с заголовка данных при вычислении смещения.
Объединение этих функций дает нам исходную формулу:
| 1 | = СМЕЩЕНИЕ ($ B $ 2, (СТРОКА (B2) -1) * 4,0) |
Увеличить ссылку на ячейку в строке
Если вы хотите увеличить количество ссылок на ячейки более чем на 1 при заполнении формул в строке, вы можете объединить функцию СМЕЩЕНИЕ с функцией СТОЛБЕЦ.
| 1 | = СМЕЩЕНИЕ ($ B $ 2,0; (COLUMN (B2) -1) * 4) |
Эта формула работает точно так же, как и приведенная выше, для увеличения ссылки на ячейку в столбце. Единственное отличие состоит в том, что аргумент rows функции равен нулю, а аргумент cols увеличивается на 4.
Ссылка на ячейку в Google Таблицах
Эти формулы работают в Google Таблицах точно так же, как и в Excel.
