Перейти к содержимому

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

Author: Xiaoyang Last Modified: 2025-08-07
sum top 3 values based on criteria

Обычно мы можем суммировать n наибольших значений из диапазона ячеек, используя простую формулу, но пробовали ли вы когда-нибудь суммировать n наибольших значений на основе некоторых критериев? В этой статье я представлю несколько формул для суммирования n наибольших значений с определенными критериями в Excel.

Сумма 3 или n наибольших значений на основе одного условия с помощью формул

Сумма 3 или n наибольших значений на основе нескольких критериев с помощью формул


Сумма 3 или n наибольших значений на основе одного условия с помощью формул

Чтобы суммировать 3 или n наибольших значений с одним условием, вы можете применить следующие формулы:

Общая формула выглядит так:

=СУММПРОИЗВ(НАИБОЛЬШИЙ((диапазон=критерий)*(значения);{1;2;3;N}))
  • диапазон=критерий это диапазон ячеек, который соответствует определенному критерию.
  • значения это список числовых значений, содержащий наибольшие значения для суммирования.
  • N представляет N-ое наибольшее значение.

1. Пожалуйста, примените эту формулу в пустую ячейку:

=СУММПРОИЗВ(НАИБОЛЬШИЙ(($A$2:$A$15=D2)*($B$2:$B$15);{1;2;3}))
  • Советы: В этой формуле:
  • $A$2:$A$15=D2: это диапазон, который соответствует определенному критерию.
  • $B$2:$B$15: это список значений, содержащий наибольшие значения, которые вы хотите суммировать.
  • 1,2,3: указывает на три наибольших значения, которые вы хотите суммировать.

2. Затем нажмите клавишу Enter, чтобы получить результат общей суммы трех наибольших значений, как показано на скриншоте:

Sum top 3 or n values based on one condition with formula

Примечание: Приняв вышеуказанную формулу, вы также можете использовать формулу массива для выполнения этой задачи:

=СУММ(ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ(($A$2:$A$15=D2);$B$2:$B$15);{1;2;3});0))

Не забудьте нажать клавиши Ctrl + Shift + Enter вместе, чтобы получить правильный результат.


Сумма 3 или n наибольших значений на основе нескольких критериев с помощью формул

Если вам нужно суммировать n наибольших значений на основе двух или более критериев, например, я хочу суммировать 3 наибольших заказа продукта KTE, где продавец — Kerry, как показано на следующем скриншоте:

sample data

Для решения этой задачи общая формула выглядит так:

=СУММПРОИЗВ(НАИБОЛЬШИЙ((диапазон1=критерий1)*(диапазон2=критерий2)*(значения);{1;2;3;N}))
  • диапазон1=критерий1 это первый диапазон ячеек, который соответствует определенному критерию1.
  • диапазон2=критерий2 это второй диапазон ячеек, который соответствует определенному критерию2.
  • значения это список числовых значений, содержащий наибольшие значения для суммирования.
  • N представляет N-ое наибольшее значение.

1. Пожалуйста, примените следующую формулу в пустую ячейку:

=СУММПРОИЗВ(НАИБОЛЬШИЙ(($A$2:$A$15=E2)*( $B$2:$B$15=F2)*($C$2:$C$15);{1;2;3}))

2. И затем нажмите клавишу Enter, и вы получите нужный результат:

Sum top 3 or n values based on multiple criteria with formula

Примечание: Вы также можете использовать формулу массива для решения этой задачи:

=СУММ(ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ(($A$2:$A$15=E2)*( $B$2:$B$15=F2); $C$2:$C$15);{1;2;3});0))

Пожалуйста, не забудьте нажать клавиши Ctrl + Shift + Enter вместе, чтобы получить правильный результат.



Больше статей о связанных функциях суммирования:

  • Суммирование с несколькими критериями в одном столбце
  • В Excel функция СУММЕСЛИ является полезной функцией для нас, чтобы суммировать ячейки с несколькими критериями в разных столбцах, но с помощью этой функции мы также можем суммировать ячейки на основе нескольких критериев в одном столбце. В этой статье я расскажу, как суммировать значения с более чем одним критерием в том же столбце.
  • Суммирование с одним или несколькими критериями в Excel
  • В Excel суммирование значений на основе одного или нескольких критериев является обычной задачей для большинства из нас. Функция СУММЕСЛИ может помочь нам быстро суммировать значения на основе одного условия, а функция СУММЕСЛИМН помогает суммировать значения с несколькими критериями. В этой статье я опишу, как суммировать с одним или несколькими критериями в Excel.
  • Суммирование, если соседняя ячейка равна, пуста или содержит текст в Excel
  • При использовании Microsoft Excel вам может понадобиться суммировать значения, где соседняя ячейка равна определенному критерию в определенном диапазоне, или суммировать значения, где соседняя ячейка пуста или содержит текст. В этом руководстве мы предоставим вам формулы для решения этих проблем.
  • Суммирование на основе частичного совпадения в Excel
  • Возьмем приведенные ниже данные в качестве примера. Я хочу суммировать значения ячеек в столбце B, когда в столбце A содержится частичное совпадение «KTE». Как этого достичь? В этой статье я расскажу, как суммировать значения ячеек на основе частичного совпадения в рабочем листе Excel.
  • Суммирование значений ячеек между двумя заданными датами в Google Sheets
  • В моей таблице Google у меня есть два столбца, которые содержат столбец с датами и столбец с заказами. Теперь я хочу суммировать ячейки столбца заказов на основе столбца дат. Например, суммировать значения между 2018/5/15 и 2018/5/22, как показано на следующем скриншоте. Как решить эту задачу в Google Sheets?

Лучшие инструменты для повышения производительности Office

Kutools для Excel решает большинство ваших проблем и увеличивает вашу продуктивность на 80%

  • Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
  • Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
  • Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
  • Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
  • Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
  • Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
  • Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
  • Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...
kte tab 201905
  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов в новых вкладках одного окна, а не в новых окнах.
  • Увеличивает вашу продуктивность на 50% и экономит сотни кликов мышью каждый день!
officetab bottom