Как Sum верхней или нижней части N значений с критериями

Posted on: 2020年10月22日 2020年11月12日
Tags: summing

В предыдущей статье мы узнали, как суммировать верхние или нижние N значений.

В этой статье мы пытаемся суммировать верхние или нижние N значений с помощью критерия.

Сумма значений TOP N с критериями

Как решить проблему?

Для этой статьи нам потребуется использовать link: / summing-excel-sumproduct-function [SUMPRODUCT function]. Теперь составим формулу из этих функций. Здесь нам дается диапазон и критерии. Нам нужно получить 5 верхних значений в диапазоне и получить сумму значений на основе заданных критериев.

_Общая формула: _

= SUMPRODUCT ( LARGE ( (list = criteria) * (range), { 1 , 2 , .... ,n } } )

список: список критериев Критерии: критерии для соответствия диапазон: диапазон значений значения: числа, разделенные запятыми, например, если вы хотите найти 3 верхних значения, используйте \ {1, 2, 3}.

Пример:

Здесь у нас есть значения набора данных из A1: D50.

Во-первых, нам нужно найти пять верхних значений с помощью функции НАИБОЛЬШИЙ, которая соответствует городу «Бостон», а затем выполнить операцию суммирования для этих 5 значений. Теперь мы будем использовать следующую формулу, чтобы получить сумму _Use the Formula: _

= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity) , { 1 , 2 , 3 , 4 , 5 } ) )

_Пояснение: _

Город «Бостон» соответствует указанному диапазону города. Это возвращает массив истинных и ложных.
link: / logic-formulas-excel-large-function [НАИБОЛЬШИЙ] функция возвращает 5 первых числовых значений из диапазона количества и возвращает массив в функцию СУММПРОИЗВ.

СУММПРОИЗВ \ {193, 149, 138, 134, 123} * Функция СУММПРОИЗВ получает массив из 5 верхних значений, причем массив из 5 верхних чисел возвращает СУММ этих чисел.

Здесь диапазон «Город и количество» указан как именованный диапазон. Нажмите Enter, чтобы получить СУММУ первых 5 чисел.

Как вы можете видеть на снимке выше, эта сумма равна 737. Сумма значений 193 + 149 + 138 + 134 + 123 = 737.

Вы можете проверить приведенные выше значения в наборе данных, используя параметр фильтра Excel.

Примените фильтр к заголовку «Город и количество» и нажмите кнопку со стрелкой в появившемся заголовке города. Следуйте инструкциям, указанным ниже.

Шаги:

Выберите ячейку заголовка Город. Примените фильтр с помощью сочетания клавиш Ctrl + Shift + L. Щелкните стрелку, которая отображается как параметр фильтра.
Выберите вариант (Выбрать все).
Выбирайте только город Бостон.
Выберите заголовок количества сейчас.
Отсортируйте список от наибольшего к наименьшему, и вы сможете просмотреть все 5 лучших значений, которые мы вычислили с помощью формулы.

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

БОЛЬШИЕ N чисел

Вышеупомянутый процесс используется для вычисления суммы нескольких чисел сверху. Но для вычисления n (большого) количества значений в большом диапазоне.

Используйте формулу:

= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity), ROW ( INDIRECT ( "1:10" ) )

Здесь мы генерируем сумму 10 лучших значений, получая массив от 1 до 10 \ {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} с помощью функций ROW & INDIRECT Excel.

Здесь у нас есть сумма первых 10 чисел, которая дает 1147.

Сумма последних N значений с критериями

Как решить проблему?

Для этой статьи нам потребуется использовать link: / summing-excel-sumproduct-function [SUMPRODUCT function]. Теперь составим формулу из этих функций. Здесь нам дан диапазон, и нам нужно уменьшить 5 значений в диапазоне и получить сумму значений.

Общая формула:

{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }

Диапазон: диапазон значений Значения: числа, разделенные запятыми, например, если вы хотите найти 3 нижних значения, используйте \ {1, 2, 3}.

Пример:

Все это может сбивать с толку. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже.

Здесь у нас есть диапазон значений от A1: D50.

Здесь диапазон «Город и количество» указан при использовании инструмента Excel с именованным диапазоном.

Во-первых, нам нужно найти пять нижних значений с помощью функции SMALL, которая соответствует критериям, а затем выполнить операцию суммирования для этих 5 значений. Теперь мы воспользуемся следующей формулой, чтобы получить сумму. Используйте формулу:

{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }

НЕ используйте фигурные скобки вручную. Фигурные скобки накладываются с помощью Ctrl Shift + Enter вместо Enter.

Пояснение:

ссылка: / статистические-формулы-excel-small-function [МАЛЕНЬКИЙ]

Функция с функцией ЕСЛИ возвращает 5 нижних числовых значений, которые соответствуют городу «Бостон», и возвращает массив функции СУММ.

СУММ (\ {23, 27, 28, 28, 30}))

Функция SUM получает массив нижних 5 значений, который имеет массив нижних 5 чисел, возвращает SUM тех чисел, которые используются с CTRL + SHIFT + ENTER.

Здесь диапазон «Город и количество» указан как именованный диапазон. Нажмите Ctrl + Shift + Enter, чтобы получить СУММУ нижних 5 чисел, поскольку это формула массива.

Как вы можете видеть на снимке выше, эта сумма равна 136.

Вышеупомянутый процесс используется для вычисления суммы нескольких чисел снизу. Но для вычисления n (большого) количества значений в большом диапазоне.

Используйте формулу:

{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , ROW ( INDIRECT ("1:10") ) ) ) }

НЕ используйте фигурные скобки вручную. Используйте Ctrl + Shift + Enter вместо Enter.

Здесь мы генерируем сумму 10 нижних значений, получая массив от 1 до 10 \ {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} с помощью функций ROW & INDIRECT Excel.

Здесь у нас есть сумма 10 нижних чисел, которая дает 155.

Вот некоторые наблюдения, показанные ниже.

Примечания:

Формула работает только с числами.
Формула работает, только если в поисковой таблице нет дубликатов. Функция СУММПРОИЗВ рассматривает нечисловые значения (например, текст abc) и значения ошибок (например, #NUM!, #NULL!) Как нулевые значения.
Функция СУММПРОИЗВ рассматривает логическое значение ИСТИНА как 1 и Ложь как 0.
Массив аргументов должен иметь ту же длину, что и функция.

Надеюсь, эта статья о том, как вернуть сумму пяти верхних значений или пяти нижних значений с критериями в Excel, будет пояснительной. Дополнительные статьи о функциях СУММПРОИЗВ можно найти здесь. Поделитесь своим запросом ниже в поле для комментариев. Мы поможем вам.

Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected]

Как Sum верхней или нижней части N значений с критериями

Сумма значений TOP N с критериями

Как решить проблему?

Пример:

_Пояснение: _

СУММПРОИЗВ \ {193, 149, 138, 134, 123} * Функция СУММПРОИЗВ получает массив из 5 верхних значений, причем массив из 5 верхних чисел возвращает СУММ этих чисел.

БОЛЬШИЕ N чисел

Сумма последних N значений с критериями

Как решить проблему?

Пример:

СУММ (\ {23, 27, 28, 28, 30}))

Похожие статьи

Популярные статьи