#excel-formula
Вопрос:
Рассмотрим следующий лист/таблицу:
A B 1 90 71 2 40 25 3 60 16 4 110 13 5 87 82 Я хочу иметь общую формулу в ячейке C1 , которая суммирует наибольшее значение в столбце A (которое есть 110 ) плюс сумму других значений в столбце B (которые являются 71 , 25 , 16 и 82 ). Я был бы признателен, если бы формула не была формулой массива (как в случае, когда требуется Ctrl Shift Enter). У меня нет Office 365, у меня есть Excel 2019.
Моя попытка
Получение наибольшего значения в столбце A легко, мы используем MAX(A1:A5) .
Поэтому формула, которую я хочу получить в ячейке C1 , должна быть примерно такой:
=MAX(A1:A5) SUM(array_of_values_to_be_summed) Получение значений других строк в столбце B (то, что я назвал array_of_values_to_be_summed в предыдущей формуле) — самая сложная часть. Я читал об использовании INDEX , MATCH , их комбинации и получении массивов с помощью скобок и знаков равенства, и я пробовал это, но пока безуспешно.
Например, я заметил, что NOT((A1:A5 = MAX(A1:A5))) это приводит к массиву/списку, содержащему единицы (или TRUE единицы) для относительного положения суммируемых строк и содержащему ноль (или FALSE ) для опущения относительного положения строки. Может быть, это полезно, я не мог найти, как это сделать.
Есть какие-нибудь идеи? Спасибо.
Правка 1 (решение)
Мне удалось получить то, что я хотел. Я просто умножил массив, полученный по NOT формуле, на диапазон B1:B5 . Окончательная формула такова:
=MAX(A1:A5) SUM(NOT((A1:A5 = MAX(A1:A5))) * B1:B5) Правка 2 (повторяющиеся значения)
Я забыл объяснить, что должна делать формула, если в столбце есть дубликаты A . В этом случае первым членом моей окончательной формулы (термином, имеющим MAX функцию) будет тот, чье соответствующее значение в столбце B наименьшее, а значение в столбце B других дубликатов будет использоваться во втором члене (том, который содержит SUM функцию).
Например, рассмотрим следующий лист/таблицу:
A B 1 90 71 2 110 25 3 60 16 4 110 13 5 110 82 Основываясь на приведенной выше таблице, формула должна давать 110 (71 25 16 82) = 304.
Просто для контекста, причина, по которой я хочу такую формулу, заключается в том, что я пишу электронную таблицу, которая автоматически вычисляет номинальный электрический ток устройства защиты от короткого замыкания питателя группы электродвигателей в доме, здании или торговом центре, как того требует статья 430,62(А) Национального электрического кодекса США. Столбец A -это номинальный ток устройства защиты от короткого замыкания в ответвлении цепи каждого двигателя, а столбец B -ток полной нагрузки каждого двигателя.
Комментарии:
1. Другой вариант =MAX(A1:A5) СУММА(B1:B5)-ВПР(МАКС(A1:A5),A1:B5,2,ЛОЖЬ). Но и это, и ваше решение предполагают, что значения в столбце A уникальны. Например, если в столбце A есть 2 строки со значением 110. Не уверен, каков ожидаемый результат для такого случая и существует ли такой случай на самом деле.
2. @AnupamChand Упс, я забыл это объяснить. Я отредактировал свой вопрос, чтобы учесть это. Спасибо за замечание.
Ответ №1:
Вы можете использовать эту формулу
=MAX(A1:A5) SUM(B1:B5) -AGGREGATE(15,6,(B1:B5)/(A1:A5=MAX(A1:A5)),1) 
Основываясь на подсказке @Anupam Chand для дубликатов с максимальным значением, в столбце B также могут быть дубликаты с минимальным значением для соответствующих дубликатов с максимальным значением в столбце A. 🙂 Эта формула будет учитывать это
=SUM(B1:B5) (MAX(A1:A5)-AGGREGATE(15,6,(B1:B5)/(A1:A5=MAX(A1:A5)),1)) *SUMPRODUCT((A1:A5=MAX(A1:A5))*(B1:B5=AGGREGATE(15,6,(B1:B5)/(A1:A5=MAX(A1:A5)),1))) 
Или с более коротким и более читаемым и в целом лучшим стилем @Anupam Chand 🙂
=SUM(B1:B5) (MAX(A1:A5)-MINIFS(B1:B5,A1:A5,MAX(A1:A5))) *COUNTIFS(A1:A5,MAX(A1:A5),B1:B5,MINIFS(B1:B5,A1:A5,MAX(A1:A5))) Объяснение работает для решений для ботов:
Эта SUM часть просто суммирует весь список.
Вторая строка получает максимальное значение для столбца A и соответствующее минимальное значение столбца B для максимальных значений в столбце A и добавляет или вычитает его соответственно.
Третья строка подсчитывает, сколько раз встречается соответствующее минимальное значение для максимального значения, и умножает его на вторую строку.
Комментарии:
1. Спасибо. Я протестировал первую и вторую формулы, и обе сработали. В третьем используются мини-файлы, которых нет в Excel 2019. По какой-то причине вторая формула, которую вы опубликовали, была на немецком языке (я думаю) и использовала точки с запятой.
2. Упс, я забыл перевести формулу на английский (вы правы, это был немецкий). Рад, что смог помочь.
Ответ №2:
Ты можешь попробовать это ?
=MAX(A1:A5) SUM(B1:B5)-MINIFS(B1:B5,A1:A5,MAX(A1:A5)) То, что мы делаем, это добавляем максимальное значение A ко всем строкам B, а затем вычитаем минимальное значение B, где A-максимальное.
Комментарии:
1. К сожалению, в формуле используется функция MINIFS, доступная только для Excel 365, которой у меня нет. В любом случае, спасибо!
Ответ №3:
Если у вас есть Excel 365, вы можете использовать следующую формулу LET
=LET(A,A1:A5, B,B1:B5, MaxA,MAX(A), MinBExclude, MINIFS(B,A,MaxA), sumB1,SUMPRODUCT(B*(A=MaxA)*(Blt;gt;MinBExclude)), sumB2,SUMPRODUCT(B*(Alt;gt;MaxA)), MaxA sumB1 sumB2 A и B являются ярлыками для двух диапазонов
MaxA возвращает максимальное значение для (110)
MinBExclude фильтрует значения столбца B по максимальному значению (25, 13, 82) и возвращает минимальное значение отфильтрованного результата (13)
sumB1 возвращает сумму других значений maxA из столбца B (26 82)
sumB2 возвращает сумму значений из B, где значение в A lt;gt; maxA (71 60)
и, наконец, результат возвращается
Если у вас нет Excel 365, вы можете добавить вспомогательные столбцы для maxA, MinBExclude, sumB1 и sumB2 и получить конечный результат
Комментарии:
1. Извините, у меня нет Office 365. Я только что прояснил это в своем посте.
2. Если вы используете вспомогательные столбцы для каждого «параметра» и его формулы (кроме
AиB), вы можете достичь того же результата, но должны заменитьAи `B их диапазонами в формулах.