Excel: как использовать sum и offset вместе

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

 =SUM(OFFSET( A1,6,2,5,1 ))

Эта конкретная формула суммирует диапазон значений длиной в 5 строк и шириной в 1 столбец, сдвинутый на 6 строк ниже и на 2 столбца вправо от ячейки A1 .

Следующий пример показывает, как использовать эту формулу на практике.

Пример: как использовать СУММУ и СМЕЩ в Excel

Предположим, у нас есть следующий набор данных в Excel, содержащий информацию об очках, набранных баскетболистами в различных командах и на позициях:

Обратите внимание, что в каждой команде ровно пять игроков.

Предположим, мы хотим сложить значения очков, набранных игроками команды «Шпоры».

Для этого мы можем ввести следующую формулу в ячейку E2 :

 =SUM(OFFSET( A1,6,2,5,1 ))

На следующем снимке экрана показано, как использовать эту формулу на практике:

Формула возвращает значение 92 , которое представляет собой сумму очков игроков команды «Шпоры».

Мы можем подтвердить это, вручную посчитав сумму очков игроков «Шпор»:

Сумма очков за «Шпор»: 6 + 8 + 15 + 40 + 23 = 92 .

Это соответствует значению, рассчитанному по нашей формуле.

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

Помните формулу, которую мы использовали для расчета суммы очков игроков команды «Шпоры»:

 =SUM(OFFSET( A1,6,2,5,1 ))

Вот наглядное объяснение того, как работает формула:

Сначала мы начинаем с ячейки A1 .

Затем идем на 6 клеток ниже этой клетки и на 2 клетки вправо.

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

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

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

Excel: как использовать ИНДЕКС и ПОИСКПОЗ для возврата нескольких значений
Excel: как использовать СМЕЩ и ПОИСКПОЗ вместе
Excel: как использовать ВПР для возврата всех совпадений