Оглавление курса · Функции поиска

OFFSET (СМЕЩ)

Открыть практику в тренажёре →

OFFSET возвращает ссылку на диапазон, смещённый от исходного на заданное число строк и столбцов:

=OFFSET(ссылка; строки; столбцы; [высота]; [ширина])

Пример из мануала: сумма продаж по отделу

Отделы расположены в заголовках столбцов, поэтому SUMIFS неудобен. Берём первый столбец данных и сдвигаем его вправо на позицию отдела, найденную MATCH:

=SUM(OFFSET($B$2:$B$7;0;MATCH(G2;$B$1:$E$1;0)-1))

Можно и с условием по строкам, через SUMPRODUCT:

=SUMPRODUCT(OFFSET($B$2:$B$7;0;MATCH(G2;$B$1:$E$1;0)-1)*($A$2:$A$7=H$1))

Задания в тренажёре

  1. В H2:H4 посчитайте продажи отдела из столбца G за все годы с помощью SUM + OFFSET + MATCH.
  2. В I2:I4 — продажи отдела только за год из I1 (SUMPRODUCT + OFFSET).
  3. В H6 — сумма двух последних значений столбца «Одежда»: сместитесь от B2 на 4 строки вниз, высота 2.
  4. В J2:J4 посчитайте среднее значение продаж отдела (AVERAGE + OFFSET + MATCH).
  5. В H8 выведите последнее значение столбца «Дом»: сместитесь от E1 вниз на количество чисел в E2:E7.

Решить задания в тренажёре →

Проверьте себя

  1. На какой диапазон ссылается =OFFSET(A1;2;1;3;2)?
  2. Что возвращает OFFSET?