Как суммировать самые большие числа в диапазоне
Шесть быстрых и надежных способов суммировать самые высокие значения 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.
- Во всплывающем диалоговом окне укажите, сколько основных элементов отображать:
Вот и все! Excel немедленно отфильтрует 10 лучших элементов (или что вы выберете) и автоматически рассчитает их общее количество.
Чтобы проверить результат, сравним итог таблицы с результатом нашей формулы и убедимся, что они полностью совпадают:
Это 6 эффективных способов сложить самые высокие значения в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Суммировать самые большие числа в Excel — примеры (файл .xlsx)