Как суммировать самые большие числа в диапазоне

Шесть быстрых и надежных способов суммировать самые высокие значения N в Excel.

Складывание вещей — одно из самых популярных занятий как в деловом мире, так и в повседневной жизни. Неудивительно, что Microsoft Excel оснащен рядом встроенных функций, таких как СУММ, СУММЕСЛИ и СУММЕСЛИМН.

Однако в некоторых ситуациях вам может понадобиться суммировать только определенные числа в диапазоне, например, первые 3, 5, 10 или n. Это может быть проблемой, потому что в Excel нет встроенной функции для этого. Но как всегда ничто не мешает построить свои формулы 🙂

СУММ самых больших 2, 3, 5 или n чисел в диапазоне

Общая формула для суммирования первых n чисел в заданном массиве:

СУММА(БОЛЬШОЙ(диапазон{1,2,3, …, п}))

Например, чтобы получить сумму двух самых больших чисел в диапазоне B2:B15, используйте следующую формулу:

=СУММ(НАИБОЛЬШИЙ(B2:B15, {1,2}))

Суммировать 3 самых больших числа:

=СУММ(НАИБОЛЬШИЙ(B2:B15, {1,2,3}))

Чтобы сделать сумму 5 самых больших чисел:

=СУММ(НАИБОЛЬШИЙ(B2:B15, {1,2,3,4,5}))

На скриншоте ниже показаны все формулы в действии:

Если ранги значений вводятся в предопределенных ячейках, вам нужно будет использовать ссылку на диапазон для к аргумент БОЛЬШОЙ. В этом случае решение необходимо ввести в виде формулы массива, нажав одновременно клавиши Ctrl+Shift+Enter. В Dynamic Array Excel (365 и 2021) это также будет работать как обычная формула.

Например, чтобы получить сумму трех наибольших соседних или несмежных чисел, ранги которых находятся в D2:D4, формула выглядит следующим образом:

=СУММ(БОЛЬШОЙ(B2:B15, D2:D4))

Примечание. Если есть два или более номера, которые делят последнее место, суммируется только первый номер. Например, если первые 3 числа — это 10, 9 и 7, а число 7 встречается более чем в одной ячейке, суммируется только первое вхождение числа 7.

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

Ядром формулы является функция НАИБОЛЬШИЙ, которая определяет N-е наибольшее значение в заданном массиве. Чтобы получить первые n значений, константа массива, такая как {1,2,3}, передается второму аргументу:

БОЛЬШОЙ(B2:B15, {1,2,3})

В результате НАИБОЛЬШИЙ возвращает 3 верхних значения в диапазоне B2:B15, то есть 30, 28 и 27.

Функция СУММ берет его оттуда, складывает 3 числа и выводит итог:

=СУММ({30,28,27})

Найдите сумму наибольших значений с помощью СУММПРОИЗВ

Формула СУММПРОИЗВ для возврата суммы первых N чисел очень похожа:

СУММПРОИЗВ(диапазон{1,2,3, …, п}))

Прелесть SUMPRODUCT в том, что он одинаково хорошо работает с константами массива и ссылками на ячейки. То есть, независимо от того, используете ли вы {1,2,3} или D2:D4 для к внутри БОЛЬШОЙ обычная формула будет хорошо работать во всех версиях Excel:

=СУММПРОИЗВ(БОЛЬШОЙ(B2:B15, {1,2,3}))

Или же

=СУММПРОИЗВ(БОЛЬШОЙ(B2:B15, D2:D4))

Как сложить много верхних чисел

Если вы хотите сложить первые 10 или 20 чисел в диапазоне, перечисление их позиций в константе массива вручную может быть довольно утомительным. В этом случае вы можете создать массив автоматически, используя функции ROW и INDIRECT:

СУММПРОИЗВ(БОЛЬШОЙ(диапазонСТРОКА(ДВССЫЛ(“1:н“))))

СУММА(БОЛЬШОЙ(диапазонСТРОКА(ДВССЫЛ(“1:н“))))

Помните, что комбинация СУММ + НАИБОЛЬШИЙ работает только как формула массива в Excel 2019 и более ранних версиях, поэтому используйте сочетание клавиш Ctrl + Shift + Enter, чтобы завершить ее правильно.

Например, чтобы суммировать 10 самых высоких чисел в диапазоне, используйте одну из следующих формул:

=СУММ(БОЛЬШОЙ(B2:B15, СТРОКА(ДВССЫЛ(“1:10”))))

Или же

=СУММПРОИЗВ(БОЛЬШОЙ(B2:B15, СТРОКА(ДВССЫЛ(“1:10”))))

Чтобы сделать формулу более гибкой, вы можете ввести количество элементов в какой-либо ячейке, скажем, E2, и соединить ссылку на ячейку внутри ДВССЫЛ:

=СУММ(БОЛЬШОЙ(B2:B15, СТРОКА(ДВССЫЛ(“1:”&E2))))

Или же

=СУММПРОИЗВ(БОЛЬШОЙ(B2:B15, СТРОКА(ДВССЫЛ(“1:”&E2))))

В Excel 365 и Excel 2021, где поведение динамического массива является родным для всех функций, и СУММ, и СУММПРОИЗВ прекрасно работают как обычные формулы:

Как суммировать переменное число наибольших значений

Когда в исходном диапазоне недостаточно данных, т.е. н больше, чем общее количество элементов, обсуждавшиеся выше формулы приведут к ошибке:

Чтобы справиться с этим сценарием, выберите одно из следующих решений:

1. Вернуть сумму n наибольших или всех элементов

При таком подходе вы выводите сумму всех существующих значений в случае, если указанное н больше, чем общее количество элементов в диапазоне.

Сначала мы получаем количество всех чисел с помощью COUNT, а затем используем MIN, чтобы вернуть либо н или количество, в зависимости от того, что меньше:

=СУММПРОИЗВ(БОЛЬШОЙ(диапазонСТРОКА(ДВССЫЛ(“1:”&МИН(нСЧИТАТЬ(диапазон)))))

Для нашего примера набора данных формула принимает следующий вид:

=СУММПРОИЗВ(БОЛЬШОЙ(B2:B15, СТРОКА(ДВССЫЛ(“1:”&МИН(E2, СЧЁТ(B2:B15))))))

Поскольку указанный н (15) больше счета (14), все числа в B2:B15 суммируются:

2. Показать собственное сообщение, если недостаточно данных

Чтобы явно было понятно, что н превышает общее количество элементов в списке, вы можете отобразить собственное сообщение, используя функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(СУММПРОИЗВ(БОЛЬШОЙ(B2:B15, СТРОКА(ДВССЫЛ(“1:”&E2)))), “Недостаточно данных”)

Суммируйте самые большие числа в Excel 365 и Excel 2021

Вместо создания громоздкой комбинации СТРОКА + ДВССЫЛ пользователи Excel 365 и Excel 2021 могут использовать функцию ПОСЛЕДОВАТЕЛЬНОСТЬ для создания числового массива на лету:

СУММПРОИЗВ(БОЛЬШОЙ(диапазонПОСЛЕДОВАТЕЛЬНОСТЬ(н)))

Поскольку ПОСЛЕДОВАТЕЛЬНОСТЬ доступна только в новом динамическом массиве Excel, где поведение массива является родным для всех функций, вы можете безопасно заменить СУММПРОИЗВ более краткой СУММОЙ:

СУММА(БОЛЬШОЙ(диапазонПОСЛЕДОВАТЕЛЬНОСТЬ(н)))

Например, чтобы сложить 5 самых больших чисел, используйте эту компактную и элегантную формулу:

=СУММ(БОЛЬШОЙ(B2:B15, ПОСЛЕДОВАТЕЛЬНОСТЬ(E2)))

Как суммировать n лучших значений в таблице Excel

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

Преобразуйте обычный диапазон в таблицу, нажав одновременно клавиши Ctrl+T.
На Дизайн стола вкладка, в Параметры стиля таблицы включите функцию Total Row.
В заголовке столбца, содержащего ваши числа, щелкните значок фильтра, а затем щелкните Числовые фильтры > Топ 10.
Во всплывающем диалоговом окне укажите, сколько основных элементов отображать: