SUMIFS с несколькими массивами или списком «ИЛИ»
Хотел бы создать функцию sumifs Excel, которая принимает массивы/списки в качестве входных данных для нескольких критериев. Сейчас использую версию 2019 года.
Провел небольшое исследование и подумал об использовании sumifs(sum_range,criteria_range,{x;y},...) или sumifs(sum_range,criteria_range,{x,y},...).
Пример, по сути, ожидал, что все формулы дадут результат 4.
Чтобы понять разницу между {x;y} и {x,y} в формуле sumifs.
Чем вызвана разница в результатах при использовании другой формулы
Любая формула Excel, которую я мог бы использовать для суммирования нескольких критериев, и все они требуют массивов/списков в качестве входных данных. (Например, суммируя столбец А, со столбцом B, имеющим в качестве входных данных XX или YY, и столбец C, имеющий в качестве входных данных ZZ или WW, и столбец D, имеющий в качестве входных данных UU, TT или UT. Думал что-то ниже, но это не работает:
sumifs(A1:A100,B1:B100,{"XX","YY"},C1:C100,{"ZZ","WW"},D1:D100,{"UU","TT","UT"})
В ходе исследования я мог бы поставить лайк sumifs(A1:A100,B1:B100,{"XX","YY"},C1:C100,TRANSPOSE({"ZZ","WW"})), но не смог найти ответа по трем и более критериям.
Все 6 формул не вернут результат 4. Только 2nd и 4th будут возвращаться, как указано, когда критерий сначала является вертикальным, он будет суммироваться с обеими комбинациями y и h, следовательно, создавая комбинацию 4, то есть {'f" "y"}, {"f" "h"}, {"g" "y"} и {"g" "h"}, что означает оба первого вертикального массива. можно использовать либо "y" and "h", как сказал выше @Rory Sir, FILTER() здесь лучше всего!
Извиняюсь, должен был упомянуть ранее, что использую версию 2019 года. Только что проверил и заметил, что ФИЛЬТР недоступен для моей версии. Любое другое предложение высоко ценится!
Я бы предложил вспомогательный столбец, используя такую формулу: =AND(OR(B1 = {"XX","YY"}),OR(C1 = {"ZZ","WW"}),OR(D1 = {"UU","TT","UT"})), тогда вы можете использовать ее в СУММЕСЛИ в поисках ИСТИНА.
Ответы 2
С таким синтаксисом вы не можете использовать более двух массивов. Поскольку у вас нет ФИЛЬТРА, на мой взгляд, самый простой вариант — использовать вспомогательный столбец с такой формулой:
=AND(OR(B1 = {"XX","YY"}),OR(C1 = {"ZZ","WW"}),OR(D1 = {"UU","TT","UT"}))
который вернет TRUE/FALSE для каждой строки. Затем просто используйте этот столбец как столбец с одним критерием в формуле СУММЕСЛИ.
Простите, если я неправильно понял необходимость, но разве это не сработает?
=SUMPRODUCT(C1:C5,((A1:A5 = "f") + (A1:A5 = "g")) * ((B1:B5 = "h")+(B1:B5 = "y")))
МММУЛЬТ
с более гибким способом ввода критериев, например {…}
=SUMPRODUCT(
C1:C5,
(
MMULT(--(A1:A5 = {"f", "g"}), SEQUENCE(COLUMNS({"f", "h"}), , 1, 0)) *
MMULT(--(B1:B5 = {"h", "y"}), SEQUENCE(COLUMNS({"h", "y"}), , 1, 0))
)
)
Обновление 6 августа 2024 г.
MMULT, используемый здесь, используется для получения итоговых значений по строкам путем умножения каждого значения в каждой строке на1, полученного изSEQUENCE(…, а затем суммирования.- Чтобы добавить критерий с одним значением, просто добавьте умножение с условием, например
* --(c1:c5 = "x") - Чтобы добавить несколько значений, добавьте их с помощью
MMULT --используется для преобразованияTRUE/FALSEв1/0
Спасибо за это! Просто интересно, может ли вышеизложенное обслуживать более двух списков/массивов?
Надеясь, что вы спросите - доберемся до этого немного позже, после утреннего кофе;) По сути, вы должны добавить умножение для каждого дополнительного условия (с MMULT).
Вы не можете использовать более двух массивов. Если у вас есть версия, которая его поддерживает, использование FILTER часто является самым простым решением.