У Кристиана есть диапазон из 18 ячеек, содержащих значения. Он хочет найти сумму 12 самых больших значений в этом диапазоне и задается вопросом, как выполнить задачу.

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

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

=LARGE(A1:A18,2)

Это второй параметр функции, который указывает, какое наибольшее значение по порядку вы хотите. Таким образом, вы можете узнать сумму 12 самых больших значений, используя такую ​​формулу:

=LARGE(A1:A18,1)+LARGE(A1:A18,2)+LARGE(A1:A18,3)+LARGE(A1:A18,4)

+LARGE(A1:A18,5)+LARGE(A1:A18,6)+LARGE(A1:A18,7)+LARGE(A1:A18,8)

+LARGE(A1:A18,9)+LARGE(A1:A18,10)+LARGE(A1:A18,11)+LARGE(A1:A18,12)

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

Например, вы можете просто вычесть шесть наименьших значений из суммы диапазона следующим образом:

=SUM(A1:A18)-SMALL(A1:A18,1)-SMALL(A1:A18,2)-SMALL(A1:A18,3)

-SMALL(A1:A18,4)-SMALL(A1:A18,5)-SMALL(A1:A18,6)

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

=SUMIF(A1:A18,">="&LARGE(A1:A18,12))

=SUMIF(A1:A18,">"&SMALL(A1:A18,6))

Первая формула суммирует все значения, которые больше или равны двенадцатому наибольшему значению в диапазоне. Второй аналогичен по действию; он суммирует все значения, которые больше шестого наименьшего значения.

Эти две формулы отлично работают, если на установленной «границе» нет повторяющихся значений. Однако если существует более одного значения, которое квалифицируется как двенадцатое по величине или шестое по величине, то формулы не вернут ожидаемые суммы. В первой формуле сумма будет слишком большой (поскольку все повторяющиеся значения добавляются к сумме), а во второй формуле сумма будет слишком маленькой (так как все повторяющиеся значения исключены из суммы).

Чтобы решить эту проблему, нужно либо вернуться к одной из более ранних формул (те, которые не используют СУММЕСЛИ), либо изменить формулу СУММЕСЛИ, чтобы учесть возможность дублирования значений:

=SUMIF(A1:A18,">"&LARGE(A1:A18,12))+LARGE(A1:A18,12)

* (12-COUNTIF(A1:A18,">"&LARGE(A1:A18,12)))

Возможно, лучшая формула для получения желаемого результата изменяет ранее использовавшуюся функцию НАИБОЛЬШИЙ:

=SUM(LARGE(A1:A18,{1,2,3,4,5,6,7,8,9,10,11,12}))

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

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (9422) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Summing_Only_the_Largest_Portion_of_a_Range [Суммирование только самой большой части диапазона].