Суммирование наименьших или нижних N значений на основе критериев в Excel

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

Суммирование наименьших или нижних N значений на основе критериев в Excel

Предположим, у меня есть диапазон данных, как показано на скриншоте ниже, и теперь я хочу суммировать три наименьших заказа для продукта Apple.

В Excel для суммирования нижних n значений в диапазоне с критериями можно создать формулу массива, используя функции SUM, SMALL и IF. Общий синтаксис следующий:

• range=criteria: Диапазон ячеек для соответствия определенному критерию;
• values: Список, содержащий нижние n значений, которые вы хотите суммировать;
• N: N-ое минимальное значение.

Чтобы решить вышеупомянутую проблему, примените следующую формулу массива в пустую ячейку:

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

Объяснение формулы:

=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))

• IF(($A$2:$A$14=D2), $B$2:$B$14): Если продукт в диапазоне A2:A14 равен «Apple», он вернет соответствующее число из списка заказов (B2:B14); если продукт не является «Apple», будет отображаться FALSE. Вы получите результат, подобный этому: {800;FALSE;FALSE;FALSE;1000;230;FALSE;FALSE;1600;FALSE;900;FALSE;500}.
• SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}): Эта функция SMALL игнорирует значения FALSE и возвращает три наименьших значения в массиве, поэтому результат будет таким: {230,500,800}.
• SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))=SUM({230,500,800}): Наконец, функция SUM суммирует числа в массиве, чтобы получить результат: 1530.

Советы: Работа с двумя или более условиями:

Если вам нужно суммировать нижние n значений на основе двух или более критериев, просто добавьте другой диапазон и критерии с помощью символа * внутри функции IF, например:

• Range1=criteria1: Первый диапазон ячеек для соответствия первому критерию;
• Range2=criteria2: Второй диапазон ячеек для соответствия второму критерию;
• Range3=criteria3: Третий диапазон ячеек для соответствия третьему критерию;
• values: Список, содержащий нижние n значений, которые вы хотите суммировать;
• N: N-ое минимальное значение.

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

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

Используемые связанные функции:

Больше статей: