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

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


Найти сумму 3-х наибольших значений (из диапазона А2:А20 ) можно с помощью функции НАИБОЛЬШИЙ() (см. файл примера ).

=СУММ(НАИБОЛЬШИЙ($A$2:$A$20;{1;2;3}))

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

На рисунке ниже 3 наибольших значения в списке выделены красным цветом с помощью Условного форматирования .

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

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

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


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

Аноним, 25 сентября 2015 г.
Добрый день! Может кто подскажет, можно ли в в excel написать формулу исходя из следующих требований, а именно: Необходимо найти среднее арифметическое между тремя (по сути эти три значения должны быть минимальными) значениями выборки при условии, что эти три значения находятся в пределах 20% от минимального значения всей выборки. Я понимаю как найти среднюю между тремя минимальными из выборки СРЗНАЧ(НАИМЕНЬШИЙ(D157:Q157;{1;2;3})), а вот как к этому добавить ограничение том, что три числа не должны превышать 20% от минимального мне не понятно.
Аноним, 25 сентября 2015 г.
не уверен, но может поможет "поиск решения"
(только для авторизованных пользователей)

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