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

history

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


Пусть в диапазоне А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}))

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

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

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

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


Комментарии

Только для авторизованных пользователей

(только для авторизованных пользователей)

© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved