Сумма трех наибольших значений (без учета повторов) в MS EXCEL

Сложим три (четыре, пять и т.д.) наибольших значений из диапазона. Если в диапазоне имеются повторы, то учитывать их не будем.

Пусть в диапазоне А2:А20 имеется список числовых значений (см. файл примера).

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

Необходимо помнить особенность функции НАИБОЛЬШИЙ() при работе со списками чисел, среди которых имеются повторы. Например, если имеется исходный массив {1;2;3;6;6;7}, то третьим наибольшим (по версии функции НАИБОЛЬШИЙ()) будет считаться 6, а не 3. Все правильно и логично, но иногда об этом забывают. С человеческой точки зрения третьим наибольшим будет все-таки, наверное, 3 (т.е. повторы не учитываются), именно такой подход мы и будем применять в этой статье.

Для этого нужно сформировать список уникальных (без повторов) значений в столбце B с помощью формулы =ЕСЛИ(СЧЁТЕСЛИ(A$2:$A2;A2)=1;A2;"") Напротив повторов в столбце В будут пустые ячейки, точнее со значением Пустой текст.

Формула для подсчета 3-х наибольших без учета повторов выглядит так:

=СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B20;{1;2;3}))

В файле примера с помощью Условного форматирования подсчитываемые значения выделяются красным.

Если необходимо сложить три, четыре, пять и т.д. наибольших значений, то необходимо все время переписывать вышеуказанную формулу. Для придания динамизма (см. файл примера, ячейка E2) формулу можно переписать следующим образом:
=СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B20;СТРОКА(ДВССЫЛ("A1:A"&E2))))

Теперь число наибольших значений, которые нужно просуммировать, вводится в ячейке E2. Часть формулы СТРОКА(ДВССЫЛ("A1:A"&E2)) создает массив последовательных чисел, начиная с 1 и заканчивая значением в ячейке E2.

В приведенном примере, различия в подсчете суммы наибольших с учетом и без учета повторов, начинаются с 4-х наибольших. Для наглядности суммируемые значения выделены в каждом диапазоне разными цветами.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (3 оценок)
Яндекс.Метрика