Смещение в эксель

Суммирование по «окну» на листе функцией СМЕЩ (OFFSET)

Бывают ситуации, когда заранее не известно какие именно ячейки на листе нужно подсчитывать. Например, представим, что нам нужно реализовать в Excel небольшой транспортный калькулятор для расчета расстояния проезда от одной заданной станции до другой:

В выпадающих списках в желтых ячейках F3 и F5 пользователь выбирает станции отправления и назначения, а в зеленой ячейке F7 должна подсчитываться сумма всех ячеек в заданном «окне» на листе. Для проезда от Останкино до Ховрино, как на рисунке, например, нужно будет просуммировать все ячейки в обведенном зеленым пунктиром диапазоне.

Как считать суммму — понятно, а вот как определить диапазон ячеек, которые нужно просуммировать? Ведь при выборе станций он будет постоянно трансформироваться?

В подобной ситуации может помочь функция СМЕЩ (OFFSET) , способная выдать ссылку на «плавающее окно» — диапазон заданного размера, расположенный в определенном месте листа. Синтаксис у функции следующий:

=СМЕЩ( Точка_отсчета ; Сдвиг_вниз ; Свиг_вправо ; Высота ; Ширина )

Эта функция на выходе дает ссылку на диапазон, сдвинутый относительно некоей стартовой ячейки (Точка_отсчета) на определенное количество строк вниз и столбцов вправо. Причем размер диапазона («окна») тоже может задаваться параметрами Высота и Ширина.

В нашем случае, если взять за точку отсчета ячейку А1, то:

  • Точка отсчета = А1
  • Сдвиг_вниз = 4
  • Свиг_вправо = 2
  • Высота = 4
  • Ширина = 1

Чтобы рассчитать необходимые для СМЕЩ аргументы, давайте сначала применим функцию ПОИСКПОЗ (MATCH) , которую мы уже разбирали, для вычисления позиций станций отправления и назначения:

И, наконец, используем функцию СМЕЩ, чтобы получить ссылку на нужное «окно» на листе и просуммировать все ячейки из него:

Вот и все, задача решена 🙂

В отличие от большинства остальных функций Excel, СМЕЩ является волатильной (volatile) или, как еще говорят, «летучей» функцией. Обычные функции пересчитываются только в том случае, если меняются ячейки с их аргументами. Волатильные же пересчитываются каждый раз при изменении любой ячейки. Само-собой, это отрицательно сказывается на быстродействии. В больших тяжелых таблицах разница по скорости работы книги может быть очень ощутимой (в разы). Для некоторых случаев быстрее оказывается заменить медленную СМЕЩ на неволатильную ИНДЕКС или другие аналоги.

СМЕЩ (функция СМЕЩ)

В этой статье описаны синтаксис формулы и использование функции СМЕЩ в Microsoft Excel.

Данная функция возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек. Можно задавать количество возвращаемых строк и столбцов.

Аргументы функции СМЕЩ описаны ниже.

Ссылка — обязательный аргумент. Ссылка, от которой вычисляется смещение. Аргумент «ссылка» должен быть ссылкой на ячейку или на диапазон смежных ячеек, в противном случае функция СМЕЩ возвращает значение ошибки #ЗНАЧ!.

Смещ_по_строкам Обязательный. Количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента «смещ_по_строкам» задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять строк ниже, чем указано в аргументе «ссылка». Значение аргумента «смещ_по_строкам» может быть как положительным (для ячеек ниже начальной ссылки), так и отрицательным (выше начальной ссылки).

Смещ_по_столбцам Обязательный. Количество столбцов, которые требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента «смещ_по_столбцам» задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять столбцов правее, чем указано в аргументе «ссылка». Значение «смещ_по_столбцам» может быть как положительным (для ячеек справа от начальной ссылки), так и отрицательным (слева от начальной ссылки).

Читать еще:  Подсчет пустых ячеек в excel

Высота Необязательный. Высота (число строк) возвращаемой ссылки. Значение аргумента «высота» должно быть положительным числом.

Ширина Необязательный. Ширина (число столбцов) возвращаемой ссылки. Значение аргумента «ширина» должно быть положительным числом.

Примечания

Если аргументы «смещ_по_строкам» и «смещ_по_столбцам» выводят ссылку за границы рабочего листа, функция СМЕЩ возвращает значение ошибки #ССЫЛ!.

Если высота или ширина опущена, то предполагается, что используется та же высота или ширина, что и в аргументе «ссылка».

Функция СМЕЩ фактически не передвигает никаких ячеек и не меняет выделения; она только возвращает ссылку. Функция СМЕЩ может использоваться с любой функцией, в которой ожидается аргумент типа «ссылка». Например, с помощью формулы СУММ(СМЕЩ(C2;1;2;3;1)) вычисляется суммарное значение диапазона, состоящего из трех строк и одного столбца и расположенного одной строкой ниже и двумя столбцами правее ячейки C2.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Функция СМЕЩ в Excel

Рассмотрим примеры использования функции СМЕЩ в Excel, которая возвращает ссылку смещенную на заданное количество строк и столбцов от исходной ссылки.

Функция СМЕЩ на первый взгляд является довольно сложной для понимания, что зачастую отталкивает пользователя от ее использования и поэтому незаслуженно редко используется.
Тем не менее она может быть очень полезна (например, при создании динамических диапазонов) и при грамотном использовании существенно упрощает работу.

Описание функции СМЕЩ

СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина])
Возвращает ссылку на диапазон, смещенный относительно заданной ссылки на указанное количество строк и столбцов.

  • Ссылка(обязательный аргумент) — ссылка на ячейку или диапазон смежных ячеек;
  • Смещение по строкам(обязательный аргумент) и по столбцам(обязательный аргумент) — число строк и столбцов, на которое результирующий диапазон смещен относительно исходной ссылки; Например, аргументы 4; 3 сместят ссылку на 4 строчки вниз и 3 столбца вправо. При этом оба параметра могут принимать различные значения — положительные (смещение вниз по строкам/вправо по столбцам), нулевые или отрицательные (смещение вверх по строкам/влево по столбцам).
  • Высота(необязательный аргумент) и ширина(необязательный аргумент) — высота (в строках) и ширина (в столбцах) возвращаемого диапазона, по умолчанию высота и ширина совпадают с размерами исходной ссылки; Например, аргументы 5; 2 расширят ссылку до диапазона в 5 ячеек высотой и 2 ячейки шириной.

Разберем несколько примеров, чтобы понять принцип работы функции СМЕЩ:


К примеру, формула =СМЕЩ(A1;0;0;5;4) (на рисунке выделена красным цветом) сдвигает ячейку A1 (аргумент функции №1) на (№2) вниз, на (№3) вправо, получаем диапазон A1 (состоящий из одной ячейки), а затем расширяет его до размера 5 (№4) на 4 (№5), т.е. возвращаемая ссылка принимает вид A1:D5 (на рисунке область также выделена красным цветом).
Аналогично, формула =СМЕЩ(A1;1;2;8;3) (выделена синим цветом) сдвигает ячейку A1 на 1 вниз, на 2 вправо, получаем диапазон C2 и расширяет его до размера 8 на 3, т.е. в результате получаем ссылку C2:E9.

Возникает логичный вопрос, какие же конкретно преимущества дает использование данной функции?
Одним из важных преимуществ является возможность оперировать с динамическими диапазонами, то есть с переменными размерами, которые могут увеличиваться или уменьшаться в процессе работы.

К примеру, предположим, что мы постоянно работаем с динамическими данными — каждый месяц добавляются новые строки или столбцы, и в этом случае работать с фиксированными диапазонами уже не так удобно.

Пример использования функции СМЕЩ

Функция СМЕЩ возвращает ссылку, поэтому может использоваться с другими функциями, в которых среди аргументов есть ссылки.
Поэтому теперь рассмотрим как пользоваться данной формулой вместе с другими на примере стандартных типовых задач.

Читать еще:  Файлы созданные в microsoft excel можно идентифицировать по расширению

Пример 1. Функция ПОИСКПОЗ

Предположим, что у нас имеются данные с подневными продажами компании и мы хотим определить продажи на конкретное число.


Воспользуемся функцией ПОИСКПОЗ для поиска указанной даты (ячейка D2) в диапазоне с датами (A2:A10).
После чего сместим начальную ячейку (в данном случае B2) на рассчитанную величину вниз за вычетом единицы.
Мы дополнительно вычитаем единицу так как показываем именно смещение относительно начальной ячейки, например, чтобы перейти с первой строки на шестую мы смещаемся ровно на пять строк.
В итоге получаем следующий результат:


Идентичного результата можно добиться и с помощью функции ИНДЕКС — формула =ИНДЕКС(B2:B10;ПОИСКПОЗ(D2;A2:A10;0)) вернет точно такой же результат.

Пример 2. Функция СУММ

Возьмем начальные условия как в предыдущем примере, однако теперь мы посчитаем сумму продаж за последние 7 дней.
Можно воспользоваться стандартной формулой СУММ(B4:B10), но при добавлении новых строчек расчет становится неверным и нам придется каждый раз изменять формулу, поэтому мы пойдем по другому пути.
С помощью функции СЧЁТЗ находим последнюю введенную дату (указываем достаточно большой диапазон A2:A100, чтобы была возможность добавлять новые данные).
Из полученного результата вычитаем 7, чтобы найти первую дату искомого диапазона, поэтому производя сдвиг начальной ячейки (B2) на найденную величину и расширяя диапазон до размеров 7 на 1, мы получим данные за 7 последних дней.
Просуммируем их воспользовавшись функцией СУММ:


При добавлении новых данных в таблицу результат будет автоматически пересчитываться:

Особенности применения

Функция СМЕЩ обладает еще одной отличительной особенностью — она является волатильной (пересчитываемой).
В отличие от большинства других функций, которые пересчитываются только в том случае, когда меняются ячейки являющиеся их аргументами, СМЕЩ пересчитывается при изменении любой ячейки.
В связи с этим данная особенность может приводить к замедлению работы книги, поэтому следует применять формулу с осторожностью.

Функция СМЕЩ в Excel, которая может заменить СУММ и сэкономить время

Программу Excel любят за возможность быстро и автоматически считать сложные формулы. Достаточно освоить элементарные команды, чтобы экономить значительное количество времени.

Функция СМЕЩ относится к более сложным массивам, поэтому используется редко. Но она может оказаться очень полезной при подсчете суммы, когда есть необходимость добавлять новые строки.

СМЕЩ и ее синтаксис

Эта функция позволяет возвращать ссылку на диапазон, который смещен относительно исходной ссылки на определенное число столбцов и строк. Определение команды звучит не совсем понятно для среднестатистического пользователя. Синтаксис параметров:

  1. Ссылка: это отправная ячейка. Относительно нее и происходит смещение.
  2. Смещение по строкам: количество строк, на которые нужно спуститься или подняться от отправной ячейки. Если идем вниз – значение будет положительным (от 1 и далее); если вверх – отрицательным (от -1 и далее).
  3. Смещение по столбцам: количество столбцов, на которые нужно уйти вправо или влево от отправной точки. Если вправо – положительное значение; влево – отрицательное.
  4. Высота и ширина: параметры диапазона, который будет возвращен функцией СМЕЩ. Если нам нужна одна ячейка, мы ставим цифры 1 и 1. Если нужна таблица, задаем иные значения, например 3 и 4, 6 и 8 и т.д.

У команды СМЕЩ есть несколько функций, но сегодня мы разберем самую распространенную и востребованную – автоматическое суммирование новых значений из вновь добавляемых строк.

Пример функции СМЕЩ

Тем, кто знакомится с функцией СМЕЩ, поначалу кажется, что ее вполне можно заменить привычной нам функцией СУММ. Но в некоторых случаях СУММ просто не работает. Разберем это на конкретном примере.

На предприятии создается база данных по ценам разных марок зубных паст, закупаемых на складе по разным оптовым ценам. Внизу указывается общая сумма, которую придется заплатить за единицу продукции. Пока нового товара на складе нет, таблица остается неизменной, и функция СУММ отлично справляется со своей задачей.

Читать еще:  Элементы управления activex в excel

Но как только склад предлагает новую марку зубной пасты (назовем ее M), предприятию приходится добавлять дополнительные строки. Если сделать это и ввести новую цифру, то СУММ никак не отреагирует на новую строку (об этом говорит появление зеленого треугольника в верхнем левом углу ячейки и восклицательного знака рядом), потому что команда рассчитана для определенного диапазона. И мы действительно видим, что сумма осталась прежней = 554.

Важно грамотно записать формулу для команды СМЕЩ, чтобы она работала правильно. Действуем по порядку. Наша цель – сосчитать сумму, поэтому функция СУММ будет внешней.

Первым числом в СУММ будет ссылка на первоначальную ячейку, с которой пойдет подсчет. Это D5. После нее записываем команду СМЕЩ, которая будет находиться внутри СУММ.

Начинаем заполнять синтаксис функции СМЕЩ. Первое – это ссылка. Ссылаться будем на саму ячейку, в которой у нас стоит формула, т.е. на D18.

Смещение по строкам указываем в отрицательном значении -1, чтобы второй элемент диапазона суммирования всегда находился в предыдущей ячейке.

Смещение по столбцам нам не нужно, поэтому ставим либо 0, либо просто оставляем пустое место. Не забываем разделять элементы формулы точкой с запятой.

Высоту и ширину не указываем. По умолчанию она будет равна одной ячейке.

После всех действий нажимаем Enter и получаем результат. Функция СМЕЩ просуммировала не только заданный диапазон, но и добавленную строку. Попробуем добавить еще одну, чтобы проверить работу команды. Пусть это будет марка зубной пасты под названием N по цене 51 рубль.

И видим, что сумма увеличилась на 51 (получилось 650), в то время как команда СУММ осталась прежней (554).

Добавим значения стоимости для зубных паст M и N и протянем формулу на остальные ячейки, чтобы знать суммы под всеми столбцами.

СМЕЩ замечательно среагировала на введение новых строк. С таким же успехом можно добавлять значения когда угодно, сохранив таблицу.

На освоение именно этой функции, которая позволяет автоматически прибавлять новые значения к общей сумме, обычно требуется не больше часа. Зато в будущем это помогает существенно экономить время на работе. Мы рассмотрели простейший пример с небольшими числами, но многие предприятия работают с огромными массивами, и пользоваться командой СУММ и уж, тем более, калькулятором, совсем неразумно.

Функция СМЕЩ в Excel

Функция OFFSET (СМЕЩ) в Excel возвращает ячейку или диапазон ячеек, который находится на заданном расстоянии от указанной ячейки или диапазона ячеек.

    Функция OFFSET (СМЕЩ) на рисунке ниже возвращает ячейку, которая находится на 3 строки ниже и на 2 столбца правее ячейки A2. Функция OFFSET (СМЕЩ) возвращает одну ячейку, потому что заданная высота и ширина равны 1.


Функция OFFSET (СМЕЩ) на следующем рисунке возвращает диапазон 1х2, который находится на 5 строк ниже и на 1 столбец правее ячейки A2. Функция SUM (СУММ) вычисляет сумму значений этого диапазона.

Примечание: Чтобы вернуть значения ячеек (без расчета суммы), выберите диапазон такого же размера, прежде чем вставить функцию OFFSET (СМЕЩ). Если вы хотите, чтобы формула вернула ячейку или диапазон ячеек, расположенных выше и левее точки отсчета, введите отрицательные числа в качестве второго и третьего аргументов.

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector