4 Формулы для Sum Top N значений в Excel

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

Если вы хотите суммировать 3 значения в Excel, вы можете сделать это, просто вычислив 3 верхних значения с помощью функции НАИБОЛЬШИЙ и просуммировав их.

Но это не единственный способ. Вы можете суммировать верхние N значений в Excel, используя несколько формул. Все они будут использовать функцию НАИБОЛЬШИЙ в качестве ключевой. См. Изображение выше. Давайте изучим их все.

Сумма первых 3 или первых N значений с использованием функции СУММЕСЛИ

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

=SUMIF(range,»>=»&LARGE(range,n))

Диапазон: * Диапазон — это диапазон, из которого вы хотите суммировать верхние N значений.

n: это количество основных значений, которые вы хотите суммировать. Если вы хотите суммировать 3 верхних значения, то n * равно 3.

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

Пример 1: Использование СУММЕСЛИ для суммирования трех крупнейших продаж. У нас есть данные о продажах за разные месяцы. Нам нужно подвести итоги продаж топ-3. Мы используем приведенную выше общую формулу.

Диапазон: C2: C13, n — 3. Тогда формула будет иметь вид:

=SUMIF(C2:C13,»>=»&LARGE(C2:C13,3))

Это вернется к 696020, что правильно. Если вы хотите суммировать верхние значения переменных N и N записано в D2, то формула будет иметь вид:

=SUMIF(C2:C13,»>=»&LARGE(C2:C13,D2))

он суммирует все значения, которые больше или равны значению верхнего N значения.

Как это работает? Функция НАИБОЛЬШИЙ возвращает верхнее N-е значение в заданном диапазоне. В приведенной выше формуле функция НАИБОЛЬШИЙ возвращает третье по величине значение в диапазоне C2: C13 *, которое равно 212940. Теперь формула:

=SUMIF(C2:C13,»>=»&212940)

Теперь функция СУММЕСЛИ просто возвращает СУММУ значений, больших или равных 212940, то есть 696020.

Если вы хотите добавить несколько условий, чтобы суммировать верхние 3 или N значений, используйте функцию СУММЕСЛИМН.

Сумма первых 3 или первых N значений с помощью функции SUM Вышеупомянутый метод довольно прост и быстр в использовании, когда нужно суммировать первые 3 или N значений из списка. Но становится сложно суммировать верхние произвольные N значений, например верхнее 2-е, 4-е и 6-е значение из диапазона Excel. Здесь функция СУММ или СУММПРОИЗВ выполняет работу.

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

=SUM(LARGE(range,\{1,2,3}))

или

=SUMPRODUCT(LARGE(range,\{1,2,3}))

Диапазон: * Диапазон — это диапазон, из которого вы хотите суммировать верхние N значений.

\ {1,2,3}: * Это основные значения, которые вы хотите подвести. Вот 1-й, 2-й и 3-й.

Итак, давайте воспользуемся этой формулой в приведенном выше примере.

Пример 2: Суммирование наибольших 1-го, 2-го и 3-го значений из диапазона Excel. Итак, мы используем ту же таблицу, что и в примере выше. Диапазон по-прежнему C2: C13, и три верхних значения, которые мы хотим суммировать, являются 1-м, 2-м и 3-м наибольшими значениями в диапазоне.

=SUMPRODUCT(LARGE(C2:C13,\{1,2,3}))

Он возвращает тот же результат, что и раньше, 696020. Если вы хотите суммировать 2-е и 4-е самые верхние значения, формула будет иметь вид:

=SUMPRODUCT(LARGE(C2:C13,\{2,4}))

И результат будет 425360.

Вы можете заменить функцию СУММПРОИЗВ на функцию СУММ в приведенном выше случае. Но если вы хотите дать ссылку на верхние значения, вам нужно будет использовать CTRL + SHIF + ENTER, чтобы превратить его в формулу массива.

\{=SUM(LARGE(C2:C13,E2:E4))}

Как это работает?

Функция НАИБОЛЬШИЙ принимает массив ключевых значений и возвращает массив этих верхних значений. Здесь большая функция возвращает значения \ {241540; 183820; 38740} как верхние 2-е, 4-е и 6-е значения. Теперь формула становится:

=SUM(\{241540;183820;38740})

или

=SUMPRODUCT(\{241540;183820;38740})

Это суммирует значения, возвращаемые функцией НАИБОЛЬШИЙ.

Суммирование большого диапазона верхних значений в Excel Допустим, вы хотите суммировать значения верхних 10–20 значений, все включено. В этом случае вышеупомянутые методы станут более суетливыми и долгими. Лучше всего использовать приведенную ниже общую формулу для суммирования верхних значений:

=SUMPRODUCT(LARGE(range,ROW(INDIRECT(«start:end»))))

range: * Диапазон — это диапазон, из которого вы хотите просуммировать верхние N значений.

«start: end»: * Это текст диапазона максимальных значений, которые вы хотите суммировать. Если вы хотите, чтобы значения были от 3 до 6, то start: end будет «3: 6».

Давайте посмотрим на эту формулу в действии:

Пример 3: Суммируйте первые 3–6 значений продаж:

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

=SUMPRODUCT(LARGE(C2:C13,ROW(INDIRECT(«3:6»))))

Это возвращает значение 534040. Это сумма 3-го, 4-го, 5-го и 6-го наибольших значений из диапазона C2: C13.

Как это работает?

Во-первых, функция КОСВЕННО превращает текст «3: 6» в фактический диапазон 3: 6.

Далее функция ROW возвращает массив номеров строк в диапазоне 3: 6, который будет \ {3,4,5,6}. Затем функция НАИБОЛЬШИЙ возвращает 3-е, 4-е, 5-е и 6-е наибольшие значения из диапазона C2: C13. Наконец, функция СУММПРОИЗВ суммирует эти значения, и мы получаем результат.

=SUMPRODUCT(LARGE(C2:C13,ROW(INDIRECT(«3:6»))))

=SUMPRODUCT(LARGE(C2:C13,\{3,4,5,6}))*

=SUMPRODUCT(\{212940;183820;98540;38740})

=534040

Бонус: суммирование первых N значений с помощью функции ПОСЛЕДОВАТЕЛЬНОСТЬ Функция ПОСЛЕДОВАТЕЛЬНОСТЬ введена в Excel 2019 и 365. Она возвращает последовательность чисел. Мы можем использовать его, чтобы получить верхние N значений, а затем просуммировать их.

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

=SUMPRODUCT(LARGE(range,SEQUENCE(num_values,,[start_num], [steps]))))

range: * Диапазон — это диапазон, из которого вы хотите просуммировать верхние N значений.

num_values: это количество верхних значений, которые вы хотите суммировать.

[start_num]: * Это начальный номер серии. Это необязательно. Если вы его опустите, серия начнется с 1.

[шаги]: это разница между следующим числом и текущим числом. По умолчанию это 1.

Если мы воспользуемся этой общей формулой для получения того же результата, что и в предыдущем примере, формула будет иметь вид

=SUM(LARGE(C2:C13,SEQUENCE(4,,3)))

Он вернет значение 534040.

Как это работает?

Это просто. Функция SEQUENCE возвращает серию из 4 значений, которые начинаются с 3 с интервалом 1. Она возвращает массив \ {3; 4; 5; 6}. Теперь функция НАИБОЛЬШИЙ возвращает наибольшие значения соответствующих чисел. Наконец, функция СУММ суммирует эти значения, и мы получаем результат 534040.

=SUM(LARGE(C2:C13,SEQUENCE(4,,3)))*

=SUM(LARGE(C2:C13,\{3;4;5;6}))*

=SUM(\{212940;183820;98540;38740})*

=534040

Так что да, ребята, вот как вы можете суммировать первые 3, 5, 10, … N значений в Excel. Я пытался объяснить. Надеюсь, это вам поможет. Если у вас есть какие-либо сомнения относительно этой темы или любой другой темы, связанной с excel VBA, спросите в разделе комментариев ниже. Я часто отвечаю на вопросы.

Статьи по теме:

link: / summing-sum-if-larger-than-0-in-excel [Сумма, если больше 0 в Excel] | Чтобы суммировать только положительные значения, нам нужно суммировать значения, которые больше 0. Для этого мы будем использовать функцию СУММЕСЛИ в Excel.

link: / summing-how-to-sum-last-30-days-sales-in-excel [Как суммировать продажи за последние 30 дней в Excel] | Иногда также суммируются стандартные 30-дневные интервалы. В этом случае хорошей идеей будет иметь формулу, которая динамически показывает сумму за последние 30 дней. В этой статье мы узнаем, как суммировать значения за последние 30 дней в Excel.

link: / summing-how-to-sum-last-n-columns-in-excel [Как суммировать последние n столбцов в Excel] | Иногда задается условие, например

когда нам нужно получить сумму последних 5 или n чисел из столбца.

Вы можете легко решить эту проблему с помощью функций Excel.

link: / summing-how-to-sum-by-matching-row-and-column [Как суммировать путем сопоставления строки и столбца в Excel] | Как мы суммируем значения, когда нам нужно сопоставить столбец и строку. В этой статье мы узнаем, как суммировать совпадающие строки и столбцы.

4 Формулы для Sum Top N значений в Excel

Сумма первых 3 или первых N значений с использованием функции СУММЕСЛИ

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

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

Как это работает?

Пример 3: Суммируйте первые 3–6 значений продаж:

Как это работает?

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

Как это работает?

Статьи по теме:

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

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