=СМЕЩ(A1;3;2) (по-английски OFFSET) возвращает ячейку на 3 строки ниже и 2 столбца правее A1, то есть C4. Если указать ещё высоту и ширину, она вернёт целый диапазон, и именно для этого СМЕЩ чаще всего и используют: итоги и средние по диапазону, который сдвигается или растёт. Таблицы на этой странице показывают английскую запись, но формулы в них можно вводить и по-русски, с точкой с запятой.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
=СМЕЩ(A1;E2;F2)3 строки вниз и 2 вправо от A1 приводят в C4, к цене Carrot, $0.80. Поставьте в Cols 0, чтобы получить название Carrot, или в Rows 5, чтобы попасть в строку Milk. Строки и столбцы могут быть отрицательными, тогда сдвиг идёт вверх или назад, а выход за верхний или боковой край листа даёт #ССЫЛКА! (по-английски #REF!; таблицы на этой странице показывают ошибки под английскими именами).
Синтаксис СМЕЩ
=OFFSET(reference, rows, cols, [height], [width])
reference(ссылка): начальная ячейка (или диапазон).rows,cols(смещ_по_строкам, смещ_по_столбцам): на сколько сдвинуться. 0 означает остаться на месте.height,width(высота, ширина): размер возвращаемого диапазона, отсчитанный от ячейки, куда пришёл сдвиг. Если их пропустить, размер будет как уreference.
Сама по себе в ячейке СМЕЩ, которая возвращает несколько ячеек, в Excel 365 выводит их как динамический массив; старые версии обычно показывают #ЗНАЧ! (#VALUE!). Внутри СУММ, СРЗНАЧ, СЧЁТ или МАКС она работает как диапазон.
Сумма последних N строк
Классическая задача для СМЕЩ: итог, который всегда охватывает самые свежие строки, сколько бы их ни добавили. СЧЁТ находит, сколько всего значений, СМЕЩ спускается к первому из последних N, а высота берёт N строк.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
=СУММ(СМЕЩ(B1;СЧЁТ(B2:B13)-E2+1;0;E2;1))Значений 7, поэтому СМЕЩ начинает на 7-3+1, то есть на 5 строк ниже B1, в B6, и берёт 3 строки: May…Jul, 14,900. В русском Excel формула пишется =СУММ(СМЕЩ(B1;СЧЁТ(B2:B13)-E2+1;0;E2;1)). Введите 4900 в B9 (август), и итог сдвинется на Jun, Jul и август, потому что СЧЁТ теперь находит 8. Диапазон B2:B13 оставляет место для остальных месяцев года. В столбце не должно быть пустых ячеек посередине, иначе СЧЁТ недосчитает и окно встанет не туда.
Скользящее среднее
Если протянуть СМЕЩ с отрицательным сдвигом по строкам вниз по столбцу, каждая строка получит окно из строк над ней: здесь среднее текущего месяца и двух предыдущих.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
=СРЗНАЧ(СМЕЩ(B4;-2;0;3;1))C4 усредняет B2:B4 (Jan…Mar), 4,300. Каждая следующая строка сдвигает окно на одну вниз. Замените 3 на 6, а -2 на -5, чтобы получить среднее за шесть месяцев (тогда начинайте формулу со строки 7). Именно этому случаю СМЕЩ вообще не нужна: =СРЗНАЧ(B2:B4), протянутая вниз от C4, делает то же самое, потому что относительные ссылки и так сдвигаются. СМЕЩ оправдывает себя, когда размер окна берётся из ячейки.
Почему ИНДЕКС часто лучше
СМЕЩ летучая: Excel пересчитывает каждую СМЕЩ после любой правки в любом месте книги, потому что не может заранее знать, на какие ячейки она укажет. Лист с тысячами таких формул начинает тормозить. ИНДЕКС тоже возвращает ссылку, и диапазон вида начало:ИНДЕКС(...) растёт так же, но без летучести:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
Обе формулы читают первые E2 строк столбца. В русском Excel: =СУММ(СМЕЩ(B2;0;0;E2;1)) и =СУММ(B2:ИНДЕКС(B2:B13;E2)). Кроме того, СМЕЩ сложнее проверять: «Влияющие ячейки» и цветные рамки, которые Excel рисует при редактировании формулы, показывают начальную ячейку и аргументы, а не диапазон, который СМЕЩ в итоге возвращает. Используйте СМЕЩ для быстрой модели или диапазона диаграммы; в больших книгах предпочитайте ИНДЕКС. Подробнее о возврате диапазонов на странице ИНДЕКС, а ДВССЫЛ это вторая летучая функция для ссылок.
Практика: итог первых N месяцев
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Ваша очередь: В F2 с помощью СМЕЩ внутри СУММ посчитайте итог первых N месяцев, где N стоит в E2.
Часто задаваемые вопросы
Что делает СМЕЩ в Excel?
Возвращает ссылку, которая отстоит от начальной ячейки на заданное число строк и столбцов, при желании с другим размером. =СМЕЩ(A1;3;2) это ячейка на 3 строки ниже и 2 столбца правее A1, то есть C4.
Как посчитать сумму последних N строк в Excel?
Начните с заголовка и спуститесь к первому из последних N значений: =СУММ(СМЕЩ(B1;СЧЁТ(B2:B100)-N+1;0;N;1)). СЧЁТ находит, сколько всего значений, а высота N берёт столько строк. Это работает, только если в столбце нет пропусков.
Почему СМЕЩ летучая?
Excel пересчитывает каждую СМЕЩ после любого изменения в книге, потому что ячейки, на которые она указывает, известны только после её выполнения. В больших книгах это замедляет работу. Диапазон, построенный с ИНДЕКС, например B2:ИНДЕКС(B2:B100;N), делает то же без летучести.
Какие аргументы у СМЕЩ?
СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина]): начальная ячейка, на сколько строк вниз (отрицательное число вверх), на сколько столбцов вправо (отрицательное назад) и, по желанию, размер возвращаемого диапазона.
