Как работает функция смещение в excel
В этой статье описаны синтаксис формулы и использование функции СМЕЩ в Microsoft Excel.
Описание
Данная функция возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек. Можно задавать количество возвращаемых строк и столбцов.
Синтаксис
Аргументы функции СМЕЩ описаны ниже.
Ссылка — обязательный аргумент. Ссылка, от которой вычисляется смещение. Аргумент «ссылка» должен быть ссылкой на ячейку или на диапазон смежных ячеек, в противном случае функция СМЕЩ возвращает значение ошибки #ЗНАЧ!.
Смещ_по_строкам Обязательный. Количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента «смещ_по_строкам» задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять строк ниже, чем указано в аргументе «ссылка». Значение аргумента «смещ_по_строкам» может быть как положительным (для ячеек ниже начальной ссылки), так и отрицательным (выше начальной ссылки).
Смещ_по_столбцам Обязательный. Количество столбцов, которые требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента «смещ_по_столбцам» задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять столбцов правее, чем указано в аргументе «ссылка». Значение «смещ_по_столбцам» может быть как положительным (для ячеек справа от начальной ссылки), так и отрицательным (слева от начальной ссылки).
Высота Необязательный. Высота (число строк) возвращаемой ссылки. Значение аргумента «высота» должно быть положительным числом.
Ширина Необязательный. Ширина (число столбцов) возвращаемой ссылки. Значение аргумента «ширина» должно быть положительным числом.
Примечания
Если аргументы «смещ_по_строкам» и «смещ_по_столбцам» выводят ссылку за границы рабочего листа, функция СМЕЩ возвращает значение ошибки #ССЫЛ!.
Если высота или ширина опущена, то предполагается, что используется та же высота или ширина, что и в аргументе «ссылка».
Функция СМЕЩ фактически не передвигает никаких ячеек и не меняет выделения; она только возвращает ссылку. Функция СМЕЩ может использоваться с любой функцией, в которой ожидается аргумент типа «ссылка». Например, с помощью формулы СУММ(СМЕЩ(C2;1;2;3;1)) вычисляется суммарное значение диапазона, состоящего из трех строк и одного столбца и расположенного одной строкой ниже и двумя столбцами правее ячейки C2.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Функция смещение в excel
-
заполнили так. строке «Ссылка». Но, ставим нуль (0). поставим положительное число «СМЕЩ». Появится такое адрес диапазона таблицы, третьего аргументов. рисунке возвращает диапазонФункция этого диапазона. из isFound (Range)?Next i тяжелых таблицах разницаПОИСКПОЗ (MATCH)=СМЕЩ(Точка_отсчета; Сдвиг_вниз; Свиг_вправо; Высота;
и назначения, а
динамичном диапазоне.


в зеленой ячейке
Как сделать динамический

Формула в ячейке Е1

строки (аргументы функции),Если поставили большее диапазон сдвинется вниз. можно полностью, можно таблицу строк и командой сайта office-guru.ru на 5 строк(СМЕЩ) в ExcelMichael_S очень подходит, оказывается. В isFound.Address сидит книги может быть разбирали, для вычисленияЭта функция на выходе F7 должна подсчитываться диапазон в таблице получилась такая. =СМЕЩ(B3;3;1)
то ставим только количество строк или
(на 6 строк).
частично.
Функция «СМЕЩ» в Excel.
Источник: http://www.excel-easy.com/examples/offset.html ниже и на возвращает ячейку или: _Boroda_ адрес обнаруженной ячейки. очень ощутимой (в позиций станций отправления дает ссылку на сумма всех ячеек функцией «СМЕЩ», смотритеЭтой формулой мы
положительное число. столбцов, чем диапазон Если поставим отрицательноеРассмотримЭта функция пригодитсяПеревел: Антон Андронов 1 столбец правее диапазон ячеек, который200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИ(СМЕЩ(H:K;0;ПОИСКПОЗ($B$10;$H$1:$BC$1;0)-1);B14): Адрес Теперь мне надо, разы). Для некоторых и назначения: диапазон, сдвинутый относительно в заданном «окне» в статье «Чтобы
говорим — показатьПосле заполнения диалогового таблицы, то в число (-6), тоаргументы функции «СМЕЩ» в при составлении различныхАвтор: Антон Андронов
ячейки находится на заданномPelena200?’200px’:»+(this.scrollHeight+5)+’px’);»>qq=.Find(«Фамилия, имя,», LookAt:=xlPart).Address скажем, переместиться на случаев быстрее оказываетсяИ, наконец, используем функцию некоей стартовой ячейки
на листе. Для размер таблицы Excel значение ячейки, которая окна, нажимаем кнопку ячейке выйдет сообщение
диапазон поднимется вверхExcel выпадающих списков, графиков,Рассмотрим,A2 расстоянии от указанной: Вариант с именованнымиСтрокаКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>qq=.Find(«Фамилия, имя,», LookAt:=xlPart).Row 2 строки вниз заменить медленную
СМЕЩ ( проезда от Останкино менялся автоматически» здесь.
находится от ячейки «ОК». «#ССЫЛ!.». Например, если (на 6 строк).. т.д.. Пример смотритекак сделать динамический диапазон. Функция ячейки или диапазона диапазонами (сделано доСтолбецКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>qq=.Find(«Фамилия, имя,», LookAt:=xlPart).Column и на одинСМЕЩ, чтобы получить ссылкуТочка_отсчета до Ховрино, какКак и где
B3 (на картинкеПро относительные и стартовая ячейка В1, Заполнять эту строку«Ссылка» в статье «Раскрывающийся в Excel сSUM ячеек. апреля, дальше поmike столбец вправо отна неволатильную на нужное «окно») на определенное количество на рисунке, например, применить функцию «СМЕЩ», ячейка синим цветом)
абсолютные ссылки в а мы указалиобязательно- здесь пишем список в Excel помощью(СУММ) вычисляет суммуФункция аналогии): Привет! текущей ячейки. КакИНДЕКС на листе и строк вниз и нужно будет просуммировать как считать последние ниже на три формулах, читайте в
смещение по строкам
. Если не нужно адрес ячейки, диапазона с картинками» тут.функцию «СМЕЩ» в значений этого диапазона.OFFSET200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИ(ДВССЫЛ($B$10);B13)Прошу подсказки!) это сделать?или другие аналоги. просуммировать все ячейки столбцов вправо. Причем все ячейки в данные таблицы, которые строки и правее
статье «Относительные и – минус 3, сдвигать строку, то смежных ячеек, от
Функция «СМЕЩ» поможетExcel=SUM(OFFSET(A2,5,1,1,2))(СМЕЩ) на рисунке
ЗЫ: не рекомендуетсяЗадача: посчитать кол-воСпасибо!The_Immortal из него: размер диапазона («окна») обведенном зеленым пунктиром добавляются каждый день, на один столбец. абсолютные ссылки в
то ячейки влево ставим нуль (0). которой будет сдвигаться
считать данные из. Функция очень полезная.=СУММ(СМЕЩ(A2;5;1;1;2)) ниже возвращает ячейку, использовать ссылки на символов из диапазона.
nilem: Всех приветствую!Вот и все, задача тоже может задаваться
диапазоне. т.д., читайте в Это, получилась ячейка Excel» здесь. сдвигаться не куда.
«Смещ по столбцам» диапазон (это -
определенном диапазоне. Например, Про эту функциюРезультат: которая находится на столбец целиком Диапазон зависит от
:
Друзья, подскажите, пожалуйста, решена :)
параметрамиКак считать суммму - статье «Примеры функции С6 (зеленая ячейка).О диапазонах смежных, Выйдет ошибка. За- здесь ставим стартовая ячейка). Эту нам нужно считать говорят, что онаПримечание
3 строки нижеikki текущего месяца (Январю200?’200px’:»+(this.scrollHeight+5)+’px’);»>If Not isFound Is незнающему как сместитьсяВ отличие от большинстваВысота
понятно, а вот «СМЕЩ» в Excel».Функцией «СМЕЩ» в несмежных ячеек, смотрите границы таблицы диапазон цифру, на какое
строку заполнять данные только последних смещает диапазон на: Чтобы вернуть значения
и на 2: три варианта. соответствует один диапазон, Nothing Then относительно известного адреса остальных функций Excel,
и как определить диапазонБывают ситуации, когда заранее Excel можно перевернуть статью «Диапазон в не смещается. количество столбцов нужнообязательно
Суммирование по «окну» на листе функцией СМЕЩ (OFFSET)
семи ячеек в заданное количество строк ячеек (без расчета столбца правее ячейкимне больше нравится Февраль — тотisFound.Offset(2, 1).Value = ячейки?СМЕЩШирина ячеек, которые нужно

не известно какие (переместить) данные в Excel».«Высота» сдвинуть диапазон. Если. Адрес ячейки может таблице, которая заполняется и столбцов. суммы), выберите диапазонA2 последний, ибо не же диапазон, но «asdasd» ‘на 2Вот мое безобразие:является волатильной (volatile). просуммировать? Ведь при
именно ячейки на строке справа наТеперь рассмотрим,- число строк поставим положительное число быть относительный, тогда ежедневно.
Ничего не понять такого же размера,. Функция содержит летучих функций смещенный на 4 строки вниз и200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Search() или, как ещеВ нашем случае, если выборе станций он
листе нужно подсчитывать. лево. Смотрите статью
как работает функция «СМЕЩ» в диапазоне, который (например, 3), то диапазон будет полностьюСначала разберем аргументы – где смещает, прежде чем вставитьOFFSET СМЕЩ или ДВССЫЛ столбца вправо, каждому на один столбецFor i = говорят, «летучей» функцией. взять за точку будет постоянно трансформироваться? Например, представим, что
«Как поменять местами в будем сдвигать, считать. диапазон сдвинется вправо

- перемещаться по таблице.
- (условия)
- для чего смещает,
- функцию
- (СМЕЩ) возвращает одну
но там использована следующему месяцу - вправо 1 To Application.ActiveWorkbook.Sheets.Count Обычные функции пересчитываются отсчета ячейку А1,В подобной ситуации может нам нужно реализовать столбцы в Excel».

Excel«Ширина» (на 3 столбца). Абсолютный адрес ячейкифункции «СМЕЩ» в т.д. Скажем простымOFFSET

ячейку, потому что небольшая хитрость -
ещё смещение на’илиWith Worksheets(i).Range(«A1:C30») только в том то: помочь функция в Excel небольшойМожно перевернуть данные.- число столбцов Если поставим отрицательное – тогда верхнийExcel языком. Функция «СМЕЩ»(СМЕЩ). Если вы заданная высота и в заголовке таблице 4 столбца). См.’isFound(3, 2).Value =Set isFound = случае, если меняютсяТочка отсчета = А1СМЕЩ (OFFSET) транспортный калькулятор для в столбце –У нас такая в диапазоне, который число (-8), то
Смещение относительно текущей ячейки (Макросы/Sub)
край диапазона смещаться.
в Excel автоматически хотите, чтобы формула ширина равны 1. даты есть во
файлик в аттаче.
«asdasd» ‘то же
.Find(«Фамилия, имя,», LookAt:=xlPart) ячейки с их
Сдвиг_вниз = 4
, способная выдать ссылку расчета расстояния проезда
снизу-вверх. Об этом таблица с данными.
будем перемещать. диапазон сдвинется влево не будет.
На закладке «Формулы»
меняет в формуле
вернула ячейку или
=OFFSET(A2,3,2,1,1)
всех ячейках.Пример: в отдельную самоеIf Not isFound аргументами. Волатильные жеСвиг_вправо = 2 на «плавающее окно» от одной заданной читайте статью «Как
Напишем формулу, чтобы отображалось
Показатели «Высота» и (на 8 столбцов). «Смещ по строкам» в разделе «Библиотека
Excel адрес диапазона диапазон ячеек, расположенных=СМЕЩ(A2;3;2;1;1)mike Ячейку вписываем месяц.
End If
Is Nothing Then пересчитываются каждый разВысота = 4
— диапазон заданного
станции до другой: перевернуть таблицу в
значение конкретной ячейки. «Ширина» не обязательно Заполнять эту строку- здесь ставим функций» нажимаем на ячеек, относительно конкретной
выше и левееРезультат: : Спасибо!
Этому месяцу определяется
The_Immortal
Как в функции СМЕЩ использовать ссылку? Или другие варианты?
MsgBox («Got it! при изменении
Ширина = 1
размера, расположенный вВ выпадающих списках в Excel». В ячейке Е1 заполнять. Тогда размеробязательно цифру, на какое кнопку «Ссылки и ячейки таблицы. Не точки отсчета, введитеФункцияRustem автоматически соотв. диапазон.
: Благодарю! The address isлюбойЧтобы рассчитать необходимые для определенном месте листа. желтых ячейках F3Можно сложить данные, устанавливаем функцию «СМЕЩ».
диапазона будет такой,
. Если не нужно количество строк нужно массивы». Из появившегося
нужно вручную постоянно отрицательные числа вOFFSET: Вариант. Не такой И с помощью
А можно как-то
» & isFound.Address)ячейки. Само-собой, этоСМЕЩ
Синтаксис у функции и F5 пользователь
вычесть среднее значение, Диалоговое окно функции какой написали в сдвигать столбец, то
сдвинуть диапазон. Если списка выбираем функцию менять в формуле качестве второго и(СМЕЩ) на следующем
Имена диапазонов ячеек
В процессе работы с данными в Excel иногда заранее не известно, сколько данных будет собрано в той или иной таблице. Поэтому мы не всегда можем заранее знать какой диапазон должно охватывать имя. Ведь количество данных может изменяться. Для решения данной задачи следует автоматически изменить именованный диапазон используемых ячеек, в зависимости от количества введенных данных.
КАК В EXCEL СДЕЛАТЬ АВТОМАТИЧЕСКОЕ ИЗМЕНЕНИЕ ДИАПАЗОНА
Допустим, у нас есть объект инвестирования, по которому мы хотим знать суммарную прибыль за весь период его использования. Заранее мы не можем определить период использования инвестиционного объекта. Но нам нужно постоянно следить за общим доходом, который приносит нам данный объект инвестирования.
Сформируйте отчет по доходности нашего объекта инвестирования, так как показано на рисунке: 
Данную задачу можно было бы решить путем суммирования целого столбца B и после появления в нем записей общая сумма изменялась бы автоматически. Но это некорректный способ решения задач в Excel. Во-первых, нам нельзя будет использовать ячейки столбца B для ввода других данных. Во-вторых, так функция суммирования будет нерационально потреблять больше памяти. Это может привести к серьезным проблемам при работе с документом. Самым рациональным решением является использование динамических имен.
Выберите инструмент «Формулы»-«Определенные имена»-«Присвоить имя».
Заполните поля диалогового окна «Создание имени» как на рисунке. Обратите внимание на то, что в поле «Диапазон:» мы используем функцию =СМЕЩ, а в одном из ее параметров используем функцию =СЧЕТ. Пример: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1) 
Переместите курсор в ячейку D2 и введите функцию =СУММ с именем «доход» в ее параметрах. 
Теперь постепенно заполняя ячейки в столбце B, мы можем проследить, как изменяется охват ячеек именем «доход».
ФУНКЦИЯ СМЕЩ В EXCEL
Разберем более детально функции, которые мы вводили в поле диапазон при создании динамического имени.
Функция =СМЕЩ определяет наш диапазон в зависимости от количества заполненных ячеек в столбце B. 5 параметров функции =СМЕЩ(начальная ячейка, смещение размера диапазона по строкам, смещение по столбцам, размер диапазона в высоту,; размер диапазона в ширину): 
«Начальная ячейка» – указывает верхнюю левую ячейку, от которой будет динамически расширяться диапазон как вниз, так и вправо (при необходимости);
«Смещение по строкам» – параметр определяет, на какое количество нужно смещать диапазон по вертикали от начальной ячейки (первого параметра). Значения могут быть нулевыми и отрицательными;
«Смещение по столбцам» – параметр определяет, на какое количество нужно смещать по горизонтали от начальной ячейки. Значения могут быть даже нулевыми и отрицательными;
«Размер диапазона в высоту» – количество ячеек, на которое нужно увеличить диапазон в высоту. По сути, название говорит само за себя;
«Размер диапазона в ширину» – количество ячеек, на которое нужно увеличить в ширину от начальной ячейки.
Последние 2 параметра функции являются необязательными. Если их не заполнять, то диапазон будет состоять из 1-ой ячейки. Например: =СМЕЩ(A1;0;0) – это просто ячейка A1, а параметр =СМЕЩ(A1;2;0) ссылается на A3.
Теперь разберем функцию: =СЧЕТ, которую мы указывали в 4-ом параметре функции: =СМЕЩ.
ЧТО ОПРЕДЕЛЯЕТ ФУНКЦИЯ СЧЕТ

Функция =СЧЕТ($B:$B) автоматически считает количество заполненных ячеек в столбце B.
Таким образом, мы с помощью функции =СЧЕТ() и =СМЕЩ() автоматизируем процесс формирования диапазона для имени «доход», что делает его динамическим. Теперь еще раз посмотрим на нашу формулу, которой мы присвоили имя «доход»: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
Читать данную формулу следует так: первый параметры указывает на то, что наш автоматически изменяемый диапазон начинается в ячейке B2. Следующие два параметра имеют значения 0;0 – это значит, что динамический диапазон не смещается относительно начальной ячейки B2. А увеличивается только его размер по вертикали, о чем свидетельствует 4-тый параметр. В нем находится функция СЧЕТ и она возвращает число равное количеству заполненных ячеек в столбце B. Соответственно количество ячеек по вертикали в диапазоне будет равно числу, которое нам даст функция СЧЕТ. А за ширину диапазона у нас отвечает последний 5-тый параметр, где находиться число 1.
Благодаря функции СЧЕТ мы рационально загружаем в память только заполненные ячейки из столбца B, а не весь столбец целиком. Данный факт исключает возможные ошибки связанные с памятью при работе с данным документом.
ДИНАМИЧЕСКИЕ ДИАГРАММЫ В EXCEL
У нас есть динамическое имя, теперь создадим динамическую диаграмму для данного типа отчета:
Выделите диапазон B2:B6 и выберите инструмент: «Вставка»-«Диаграммы»-«Гистограмма»-«Гистограмма с группировкой».
Щелкните левой кнопкой мышки по любому столбцу гистограммы и в строке формул отобразится функция диаграммы: =РЯД(). 
В строке формул измените параметры функции: =РЯД(Лист1!$B$1;;Лист1!$B$2:$B$7;1) на =РЯД(Лист1!$B$1;;Лист1!доход;1).
В отчет добавьте новую запись в ячейки A8 — «июль», B8 — «77000» соответственно. И убедитесь, что на диаграмме автоматически добавился новый столбец. 
Используя наше динамическое имя «доход» нам удалось создать автоматически изменяемую динамическую диаграмму, которая сама добавляет и отображает новые данные в отчете.
Примеры функции СМЕЩ для прохода по диапазону ячеек в Excel
Функция СМЕЩ в Excel предназначена для создания ссылки на определенную ячейку или диапазон ячеек, находящихся на определенном расстоянии от указанной ячейки, измеряемом в заданном в качестве аргумента количестве столбцов и строк, и возвращает соответствующую ссылку. Синтаксис данной функции позволяет указывать требуемое количество возвращаемых столбцов и строк.
Примеры использования функции СМЕЩ в Excel
Пример 1. В таблицу Excel на протяжении экзамена записываются оценки, которые получили уже сдавшие предмет экзамены. Реализовать алгоритм динамического расчета среднего значения балла за экзамен для группы студентов.
Вид исходной таблицы:

Формула для расчета:
Функция ЕСЛИОШИБКА используется для вывода сообщения об отсутствии данных для расчета. Функция СРЗНАЧ принимает диапазон ячеек, возвращаемых функцией СМЕЩ со следующими параметрами:
- B3 – ссылка на начальную ячейку (точку отсчета);
- 0 – нулевое смещение по строкам;
- 0 – нулевое смещение по столбцам;
- СЧЁТ(B3:B14) – количество ячеек из диапазона B3:B14, которые содержат ненулевые значения, характеризующее высоту диапазона возвращаемых ячеек;
- 1 – ширина диапазона возвращаемых значений (в 1 столбец).
Результаты расчета по мере заполнения данными:

Примечания к примеру:
- Данные должны вноситься последовательно в каждую новую строку, иначе расчет выполняться не будет.
- Пример решения приведен в целях демонстрации использования функции СМЕЩ, решение задачи может быть реализовано более простым и надежным способом.
Динамическое обновление итоговых данных таблицы в Excel
Пример 2. В таблице Excel внесены данные о количестве продаж товаров за все время работы компании. Для еженедельного учета требуется выводить данные о продажах (общая сумма проданных товаров) за прошедшие 7 дней. Реализовать динамическое обновление данных.
Вид исходной таблицы данных:

Для расчета суммы проданных единиц товаров за последние 7 дней используем формулу:
Описание аргументов функции СМЕЩ:
- B3 – ссылка на ячейку, относительно которой выполняется отсчет;
- СЧЁТЗ(B3:B1000)-7 – функция, подсчитывающая количество непустых ячеек в диапазоне B3:B1000 (ячейка B1000 взята условно с запасом для последующих записей). От итогового результат вычитается число 7, поскольку по условию необходимо получить данные за неделю. В целом выражение определяет начальную позицию массива возвращаемых ячеек по вертикали относительно точки отсчета B3;
- 0 – нулевое смещение по столбцам (искомый диапазон ячеек находится в том же столбце, что и точка отсчета B3);
- 7 – высота диапазона возвращаемых ячеек (7 ячеек);
- 1 – ширина диапазона возвращаемых ячеек.
Полученный результат в результате расчетов:

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

Используем элемент управления «Счетчик» для выбора номера месяца. Для этого добавим пункт ленты меню «Разработчик» нажатием правой кнопкой мыши по любому существующему ее элементу:

Выберем пункт настройка ленты и в открывшемся окне установим флажок напротив пункта «Разработчик»:

В ленте добавится новый пункт меню. Выберем его, найдем кнопку «Вставить» и нажмем ее:

Выберем элемент «Счетчик», наведем курсор (вместо стрелки форма «+») на требуемую ячейку (в данном случае A16), выделим область для счетчика. После того, как кнопка мыши будет отпущена, появится заготовка счетчика. Нажмем правой кнопкой по счетчику и выберем пункт «Формат объекта»:

Заполним текущее и минимальное значения, а также установим связь с ячейкой, в которой данный счетчик находится:

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

Для вывода курсов доллара и евро соответственно используем функции:

В результате имеем возможность динамического вывода значений в зависимости от выбранного номера месяца. Примеры работы: