Привет. Сегодня расскажу про функцию OFFSET, она работает и в Excel, и в Google-таблицах. В русском Excel функция называется СМЕЩ (от "смещение") и как результат, выдает ячейку или диапазон заданные со смещением от исходной ячейки (или диапазона). В статье будет описание функции и примеры использования. Расскажу, как эта функция может быть полезна.
Функция СМЕЩ принимает до 5 аргументов, первые 3 обязательные, остальные два опциональные:
=СМЕЩ(<ссылка>;<смещение по строкам>;<смещение по столбцам>;<высота>;<ширина>)
Аргументы функции
<ссылка> - ссылка на исходный диапазон. Может быть задана как напрямую, абсолютная или относительная, например: $A$1 или B2, с помощью функции ИНДЕКС (INDEX) или с помщью функции ДВССЫЛ (INDIRECT). Пример ниже будет указывать на ячейку B1 на листе "Лист1"
=СМЕЩ(ДВССЫЛ("'Лист1'!A1"),0,1)
<смещение по строкам> и <смещение по столбцам> - целое число, может быть отрицальным, нулем или положительным, говорит на сколько от исходной ссылки нужно сдвинуться по вертикали (смещение по строкам) и по горизонтали (смещение по столбцам). Если число положительное, то смещение происходит слева направо и сверху вниз (в сторону увеличения буквенного значения и индексов ячейки). Если отрицательное, то наоборот. Если значение смещения равно нулю, то смещения по данной координате не будет. К примеру, СМЕЩ(A1,0,0) будет указывать на ячейку A1.
Если задать ссылку и эти два параметра, опустив последующие два, то размеры получившегося диапазона будут такие же, как и размеры изначального. Если изначално <ссылка> указывала на ячейку - будет ячейка. Если указывала на диапазон, то будет диапазон.
<высота> и <ширина> - целое число, может быть отрицальным, нулем или положительным, отвечает за то, насколько будет расширен (или сужен при отрицательных аргументах) диапазон относительно изначально заданного.
Особенности
- Если итоговый диапазон находится за пределами рабочего листа, то функция вернет ошибку.
- Если ширина или высота итогового диапазона нулевая, вернет ошибку.
- Функция пересчитывается каждый раз при изменении рабочего листа, поэтому лучше минимизировать ее использование в очень больших таблицах, иначе она может замедлять работу файла.
Примеры использования
С помощью функции СМЕЩ можно посчитать результат за период:
В ячейке E3 (и ниже) формула, которая вычисляет количество дней в месяце:
=КОНМЕСЯЦА(ДАТА(2021;D3;1);0)-ДАТА(2021;D3;1)+1
В ячейке F3 (и ниже) формула:
=СУММА(СМЕЩ($A$2;ЕСЛИ(D3=1;0;СУММА(СМЕЩ($E$3;0;0;D3-2)));1;E3))
Суть формулы: просумировать диапазон, который отстоит от ячейки A2 на одну ячейку вправо, на столько ячеек вниз, сколько дней в сумму в предыдущих месяцах и высотой в количество дней в текущем месяце.
Пример 2: двухуровневые выпадающие списки (только в Excel)
Предположим, мы хотим вести учет продаж по категориям и подкатегориям товара. А категории и подкатегории удобно выбирать с помощью выпдающих списков. С выпадающим списком для категории проблем быть не должно (я подробно рассказывал об этом здесь), а как сделать выпдающий список для подкатегории, если она зависит от основной категории?
Добавим проверку данных для столбца A - список из колонки H. Можно его задать еще точнее, чтобы не было пустых строк:
=СМЕЩ($H$2;0;0;СЧЕТЗ($H:$H)-1;1)
Второй список задать сложнее: нам нужно в колонке H найти выбранную категорию (с помощью ПОИСКПОЗ), затем сдвинуться относительно найденной подкатегории на две колонки правее и выбрать столько ячеек справа, сколько указано в колонке I для этой категории. В колонке I с помощью формулы СЧЕТЗ считаем сколько введено подкатегорий. Итоговая формула для списка проверки данных для подкатегорий будет следующая:
=СМЕЩ($H$1;ПОИСКПОЗ($A2;$H:$H;0)-1;2;1;СМЕЩ($H$1;ПОИСКПОЗ($A2;$H:$H;0)-1;1))
Пример 3: ссылаемся только на четные строки
Допустим, в первом столбце у нас какие-то данные, а вто втором столбце мы хотим указать данные из первого столбца, но только для четных столбцов. Это очень просто сделать с помощью функции СМЕЩ:
=СМЕЩ($A$1;СТРОКА()*2-1;0)
На этом на сегодня все. Все примеры доступны для скачивания по ссылке: https://disk.yandex.ru/d/VCTTafoR1GbvJA
