Elettracompany.com

Компьютерный справочник
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Смещ в excel примеры

Функция СМЕЩ в 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.

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

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

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

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

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

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


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


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

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

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


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

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

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

Функция СМЕЩ() в EXCEL

Функция СМЕЩ() часто используется при создании динамических диапазонов . Рассмотрим ее подробнее.

Синтаксис функции СМЕЩ()

  • ссылка — ссылка, от которой вычисляется смещение;
  • смещ_по_строкам — количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку (по умолчанию =0);
  • смещ_по_столбцам – аналогично смещ_по_строкам, только смещение отсчитывается по столбцам (по умолчанию =0);
  • высота — число строк возвращаемой ссылки. Значение аргумента «высота» должно быть положительным числом;
  • ширина — число столбцов возвращаемой ссылки (по умолчанию =1).

Чтобы было понятнее, потренируемся с функцией СМЕЩ() , используя файл примера .

Примеры

Дана исходная таблица с тремя столбцами.

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

Для подсчета суммы значений в столбце Продажа1 запишем формулу: =СУММ(СМЕЩ($B$2;0;0;8;1)) диапазон суммирования — $B$2:$B$9 (левый верхний угол — $B$2 , высота 8 , смещения верхнего угла нет). Результат 34 .

Для подсчета суммы значений в столбце Продажа2 запишем формулу: =СУММ(СМЕЩ($B$2;0; 1 ;8;1)) Теперь левый верхний угол диапазона суммирования смещен от $B$2 на один столбец вправо, т.е. диапазон суммирования стал $C$2:$C$9 . Результат 68 .

Для подсчета суммы значений в столбцах Продажа1 и Продажа2, изменим ширину диапазона. =СУММ(СМЕЩ($B$2;0;0;8; 2 )) указав ширину в 2 ячейки, результат составит 102 , диапазон будет модифицирован в $В$2:$С$9 .

Добавив смещение по строкам (+1), получим результат 99 : =СУММ(СМЕЩ($B$2; 1 ;0;8;2)) диапазон будет модифицирован в $В$3:$С$9 .

Функция СМЕЩ() vs ИНДЕКС()

Пусть имеется диапазон с числами ( А2:А10 ) Необходимо найти сумму первых 2-х, 3-х, . 9 значений. Конечно, можно написать несколько формул =СУММ(А2:А3) , =СУММ(А2:А4) и т.д. Но, записав формулу ввиде:

Читать еще:  Экспорт из mysql в excel

получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений). Вышеуказанная формула эквивалентна формуле =СУММ(A2:ИНДЕКС(A2:A10;4)) , которая, в свою очередь, эквивалентна формуле =СУММ(A2:A5)

Формула ИНДЕКС(A2:A10;4) возвращает ссылку на ячейку А5 .

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

Добрый день!

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

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

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

=СМЕЩ( ссылка; смещение_по_строкам; смещение_по_столбцам; [высота]; [ширина]), где:

  • Ссылка – этот аргумент является обязательным и от него, собственно и начинается вычисление смещения. Данный аргумент должен являться ссылкой на конкретную ячейку или же на определённый диапазон смежных ячеек, иначе функция СМЕЩ вернет вам ошибку #ЗНАЧ!;
  • Смещение по строкам – является обязательным аргументом, он означает то количество строчек, которое нужно отсчитать вниз или вверх и необходимо, чтобы верхняя левая ячейка полученного результата ссылалась на нужную вам ячейку. К примеру, для этого аргумента ставим число 5, это означает что верхняя левая ячейка ссылки, которая возвращается, обязана быть ниже на 5 строк, нежели тот, который вы указали в предыдущем аргументе. Данное значение может быть положительным (это когда смещение происходит ниже начальной ссылки) и отрицательным (это когда выше начальной ссылки);
  • Смещение по столбцам – также обязательный аргумент и означает то количество столбиков которое необходимо отсчитать вправо или же влево, для того чтобы верхняя левая ячейка результата ссылалась на нужную вам ячейку. К примеру, если этот аргумент указан как число 5, то это означает что верхняя левая ячейка ссылки, которая возвращается, смещается на 5 столбиков вправо от значения аргумента «ссылка». Этот аргумент бывает положительным (для размещения справа от первоначальной ссылки) и отрицательным (при размещении слева);
  • Высота – является необязательным аргументом, в котором указывается высота (число строчек) ссылки, которая возвращается. В обязательном порядке этот аргумент обязан быть положительным значением;
  • Ширина — является необязательным аргументом, в котором указывается ширина (число столбиков) ссылки, которая возвращается. В обязательном порядке этот аргумент обязан быть положительным.

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

  • В случае, когда аргументы «смещение_по_строкам» и «смещение_по_столбцам» уводят вашу ссылку за границу вашего рабочего листа, функцией будет возвращена ошибка #ССЫЛ!;
  • Когда аргументы «высота» и «ширина» в формуле не указан, то по умолчанию будет использоваться те же параметры высоты и ширины, как и в аргументе «ссылка»;
  • Функция СМЕЩ в Excel не меняет выделения и не двигает никакие ячейки, она всего лишь возвращает ссылку и ее можно использовать в совмещении с любой функцией, где используется аргумент типа «ссылка».

При работе с функцией СМЕЩ возникает закономерный вопрос, почему просто указать диапазон, типа A1:D5, нельзя прямо? А дело в том что причин может быть несколько:

  • Во-первых, в случае, когда точный адрес нам неизвестен. В большинстве случаев нам известна, только лишь стартовая ячейка и иногда отсутствуют знания фактического адреса. В таких ситуациям использовать функцию СМЕЩ вExcel обязательно;
  • Во-вторых, необходимость использования динамических диапазонов. Как вы понимаете указанный диапазон в ссылке A1:D5 и будет возвращать аналогичное значение, то есть он имеет статичную основу, а в некоторых случаях нам нужно динамичный диапазон. Эта необходимость возникает в случаях, когда данные изменяются (добавляются новые строчки и колонки).
Читать еще:  Медиана в excel это

Чтобы функция СМЕЩ в Excel стала вам ближе, рассмотрим ее на основе примера, вы ведете учет топлива в АТП, или транспортного отдела другой компании. Вот вам для заказа или учёта ГСМ нужно отслеживать постоянно расход топлива и сопутствующих материалов. Для этих целей мы создаем простую таблицу следующего вида (я использую небольшие числа для наглядности): Итак, какое же будет среднее значение расхода ГСМ, исходя из того что наши данные расхода находятся в диапазоне B2:B23, то нам можно написать формулу =СРЗНАЧ(B2:B23) и всё у нас получилось. Но, увы, нам очень важно, чтобы изменение в формуле происходили ежедневно и автоматически. Для этого нам нужно использовать функцию СМЕЩ всего один раз для вычисления среднего значения расхода топлива за последнюю неделю и вопрос будет закрыт, для этого нам нужна формула следующего вида:

=СРЗНАЧ(СМЕЩ(B2;СЧЁТЗ(B2:B300) -7;0;7;1))

Эта формула позволит нам найти искомое, и давайте познакомимся с ней более близко. Для начала формула СЧЁТЗ(B2:B300) -7 позволит нам посчитать количество значений и от полученного результата отнимает 7 дней (неделю). Это даст нам возможность определится с диапазоном для вычисления с помощью функции СРЗНАЧ, среднего недельного расхода топлива. А формула СМЕЩ смещает с ячейки В2 на начальную ячейку нашей последней недели. Ну вот, я надеюсь, что функция СМЕЩ в Excel стала вам понятнее и ближе. Если статья вам помогла, жду ваш лайк или комментарий, заранее за это благодарен. С другими не менее интересными функциями вы можете ознакомиться в «Справочнике функций».

До новых встреч на страницах сайта.

«Мало кто из нас может вынести бремя богатства. Конечно, чужого
»
Марк Твен

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция OFFSET (СМЕЩ) в Excel. Как использовать?

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

Что возвращает функция

Возвращает ссылку, которая смещается на заданное количество ячеек.

Синтаксис

=OFFSET(reference, rows, cols, [height], [width]) – английская версия

=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]) – русская версия

Аргументы функции

  • reference (ссылка) – ссылка на ячейку, от которой вы хотите сделать смещение. Это может быть ссылка на ячейку или диапазон смежных ячеек;
  • rows (смещ_по_строкам) – количество строк для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение строк ниже, если отрицательное – выше;
  • cols (смещ_по_столбцам) – количество колонок для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение колонок вправо, если отрицательное число, то влево;
  • [height] ([высота]) – количество строк в указанном диапазоне функции;
  • [width] ([ширина]) – количество колонок в указанном диапазоне функции.

Основной принцип работы функции

Функция СМЕЩ , пожалуй, самая запутанная функция в Excel.

Читать еще:  Вставить вкладку в excel

Давайте разберем ее работу на простом примере игры в шахматы. В шахматах есть фигура Ладья.


Источник фото: Wikipedia

По правилам игры в шахматы, Ладья может ходить только вправо, влево, вниз и вверх. Фигура не может передвигаться по диагонали.

Теперь давайте представим, что нашей Ладье нужно переместиться не строго влево или вправо, а на ячейку, находящуюся по диагонали от изначальной позиции. Что мы будем делать этом случае?

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

Рассмотрим перемещение Ладьи на примере в Excel. Мы хотим начать с ячейки D5 (где находится ладья), а затем перейти на две строки вниз и два столбца вправо и извлечь значение из ячейки. Для этого будем использовать формулу:

=OFFSET(стартовая позиция, на сколько строк сместиться вниз, на сколько столбцов сместиться вправо) – английская версия

=СМЕЩ(стартовая позиция, на сколько строк сместиться вниз, на сколько столбцов сместиться вправо) – русская версия

Как вы видите формула по нашему примеру выглядит так:

=OFFSET(D5,2,2) – английская версия

=СМЕЩ(D5;2;2) – русская версия

Функции задан аргумент старта отсчета с ячейки “D5”, затем смещение на две строки вниз, после этого на две колонки вправо. Так мы переместимся с ячейки “D5” на ячейку “F7”. По завершении перемещения функция выдает значение ячейки “F7”.

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

Давайте рассмотрим простой пример:

Предположим, вы хотите использовать ссылку на ячейку “A1” (желтую), и хотите сослаться на весь диапазон, выделенный синим (C2:E4) в формуле.

Как бы вы это сделали с помощью клавиатуры? Сначала нужно перейти к ячейке C2, а затем выбрать все ячейки в диапазоне “C2:E4”.

Теперь посмотрим, как это сделать, используя формулу OFFSET (СМЕЩ) :

=OFFSET(A1,1,2,3,3) – английская версия

=СМЕЩ(A1;1;2;3;3) – русская версия

Если вы используете эту формулу в ячейке, она вернет #VALUE! Но если вы перейдете в режим редактирования, выберете формулу и нажмите клавишу “F9”, вы увидите, что она возвращает все значения, выделенные синим цветом.

Надеюсь, теперь у вас есть базовое понимание использования функции OFFSET (СМЕЩ) в Excel.

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

Пример 1. Ищем последнюю заполненную ячейку в колонке

Представим, что у вас есть данные в колонке. Для того чтобы отобразить последнее значение в колонке используйте формулу:

=OFFSET(A1,COUNT(A:A)-1,0) – английская версия

=СМЕЩ(A1;СЧЁТ(A:A)-1;0) – русская версия

Эта формула предполагает, что кроме указанных значений нет никаких других, и в этой колонке нет пустых ячеек. Функция работает, подсчитывая общее количество заполненных ячеек и соответствующим образом смещает ячейку “A1”.

Например, в указанном примере есть 8 значений, поэтому функция COUNT(A:A) или СЧЁТ(A:A) возвращает 8. Мы смещаем ячейку “A1” на 7, чтобы получить последнее значение.

Пример 2. Создаем динамический выпадающий список с автоматическим дополнением новых данных

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

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

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

Как сделать такой список:

  • Выберите ячейку, в которой вы хотите создать выпадающий список;
  • Нажмите на вкладку Data => Data Tools => Data Validation;
  • В диалоговом окне Data Validation, в разделе Настройки выберите List из выпадающего списка;
  • В параметрах Source укажите формулу =OFFSET(A1,0,0,COUNT(A:A),1) или =СМЕЩ(A1;0;0;СЧЁТ(A:A);1)
  • Нажмите ОК

Как эта формула работает:

Первые три аргумента функции OFFSET (СМЕЩ) A1, 0, 0. Это означает что начальное значение в ячейке “A1”, которое не смещается ни по строкам и по колонкам (0, 0);
Четвертый аргумент функции указывает на высоту, и здесь функция COUNT (СЧЁТ) возвращает суммарное количество ячеек в диапазоне данных для выпадающего списка. Главное условие – отсутствие пустых ячеек в диапазоне.
Пятый аргумент функции “1”, обозначает ширину диапазона данных, которая в нашем случае равна одной колонке.

Дополнительная информация

  • Функция OFFSET (СМЕЩ) – волатильная функция. Она пересчитывается каждый раз, как только вы открываете Excel файл. Работа этой функции может сильно сказываться на скорости работы всего файла.
  • Если значения высоты и ширины не указаны, функция учитывает только первые три аргумента;
  • Если значения аргументов rows (смещ_по_строкам) и cols (смещ_по_столбцам) отрицательны, то смещение будет происходить в обратную сторону.

Альтернативы функции OFFSET (СМЕЩ) в Excel

Ввиду некоторых ограничений функции, многие из вас рассматривают альтернативные методы:

  • Функция INDEX (ИНДЕКС) также может использоваться для возврата ссылки на ячейку.
  • Excel таблицы: если вы используете структурированные ссылки в таблице Excel, вам не нужно беспокоиться о добавлении новых данных и необходимости корректировки формул.
Ссылка на основную публикацию
Adblock
detector