Excel: как использовать срзнач и смещ вместе

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

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

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

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

Пример: Как использовать СРЗНАЧ и СМЕЩ в Excel

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

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

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

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

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

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

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

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

Среднее количество очков за «Шпор»: (6 + 8 + 15 + 40 + 23) / 5 = 18,4 .

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

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

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

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

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

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

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

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

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

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

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

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