Использование логики ИЛИ для массива в качестве аргумента в Sumproduct

Это может сработать:

 = {SUMPRODUCT(--('Raw data'!C:C=Landgebruik!A2);--IFERROR(MATCH('Raw data'!O:O;{20;21;22;23;40};0)>0;0);'Raw data'!S:S)}

Это нужно ввести как формулу массива.

Вы можете разделить его на два SUMIFS, как указано в комментарии. Если все значения являются целыми числами, то сравнение «Необработанных данных»!O:O с 20, 21, 22 и 23 аналогично проверке на >=20 и <=23. Значение 40 должно быть сделано отдельно.

=SUMIFS('Raw Data'!S:S,'Raw Data'!C:C,Landgebruik!A2,'Raw Data'!O:O,"> = "&20,'Raw Data'!O:O,"< = "&23)
+SUMIFS('Raw Data'!S:S,'Raw Data'!C:C,Landgebruik!A2,'Raw Data'!O:O,40)

в моем регионе

или

=SUMIFS('Raw Data'!S:S;'Raw Data'!C:C;Landgebruik!A2;'Raw Data'!O:O;"> = "&20;'Raw Data'!O:O;"< = "&23)
+SUMIFS('Raw Data'!S:S;'Raw Data'!C:C;Landgebruik!A2;'Raw Data'!O:O;40)

в вашем регионе.

Это работает только тогда, когда несколько критериев являются последовательными целыми числами.

Соображения скорости

SUMIFS считается примерно в пять раз быстрее, чем sumproduct, поэтому может быть предпочтительным вариантом для больших наборов данных как показано здесь

Вы могли бы утверждать, что более общее предложение (эффективно) пяти СУММЕСЛИМН в СУММЕ от @ BrakNicku должно быть примерно таким же быстрым, как один СУММПРОИЗВ, но СУММ(СУММЕСЛИМН), вероятно, все равно выиграет, потому что такие формулы, как СУММЕСЛИМН, обрабатывают ссылки на полный столбец более эффективно. чем формулы массива.

Несмотря на то, что это уже делалось сотни раз, эй, может быть, Microsoft изменила формулы или что-то в этом роде.

Я неравнодушен к методу, предложенному Джерри и Я, поскольку они чертовски просты и лаконичны, но вы платите большие затраты на производительность.

Формула Тома кажется мне уродливой, но она была намного быстрее, примерно в 4 раза быстрее, чем мой первоначальный пример. Мы смогли включить {} в формулу Тома, но чтобы заставить ее работать, нам пришлось обернуть функцию sumifs функцией суммы. Это значительно замедлило формулу, но сделало ее красивее.

У z32a7ul тоже было отличное решение. Мне очень нравится использование -- и я научился использовать |s для поиска текста и только этого текста. На первый взгляд я думал, что он не будет работать на таком номере, как 2323, но он работает.

Пример макета был следующим:

A1:A5000 был залит LandgeBruik,

B1:B5000 был заполнен 40-ками.

C1:5000 был заполнен единицами.

Результаты:

=SUMPRODUCT((A1:A5000 = "LandgeBruik")*(B1:B5000 = {20,21,22,23,40})*C1:C5000)

Прошло 19,186031 секунд | 59 818 073 тика

{=SUM(IF(A1:A5000 = "Landgebruik",1,0)*IF(B1:B5000 = {20,21,22,23,40},1,0)*C1:C5000)}

Прошло 26,124411 секунд | 81 450 506 тиков

{=SUM((A1:A5000 = ""Landgebruik"")*(B1:B5000 = {20,21,22,23,40})*C1:C5000)}

Прошло 21,111835 секунд | 65 822 330 тиков

"=SUMIFS(C1:C5000,B1:B5000,"">=20"",B1:B5000,""<=23"",A1:A5000,""=Landgebruik"")+SUMIFS(C1:C5000,B1:B5000,""=40"",A1:A5000,""=Landgebruik"")"

Прошло 6,732804 секунды | 20 991 490 тиков

"=SUM(SUMIFS(C1:C5000,A1:A5000,"Landgebruik",B1:B5000,{21,22,23,24,40}))"

Прошло 16,954528 секунд | 52 860 709 тиков

"=SUMPRODUCT(--(A1:A5000 = ""Landgebruik""),--NOT(ISERROR(FIND(""|""&B1:B5000&""|"",""|20|21|22|23|40|""))),C1:C5000)"

Прошло 11,822379 секунд | 36 859 729 тиков

Вы можете внести небольшое изменение в текущую формулу; замените ; на * (-- также не нужны в этом конкретном случае):

=SUMPRODUCT(('Raw data'!C:C=Landgebruik!A2)*('Raw data'!O:O = {20;21;22;23;40})*'Raw data'!S:S)

И это должно работать.

Когда вы передаете отдельные параметры в SUMPRODUCT, каждый параметр должен иметь одинаковый размер. Но когда вы умножаете их таким образом, это приводит к принудительному вычислению и расширению массивов.

Например, если вы возьмете два массива, 5x1 и 1x5, вы получите результирующий массив 5x5:

Я хочу дать ответ на этот вопрос, попросив у ОП некоторые разъяснения, потому что английский не является моим основным языком, и я думаю, что что-то неправильно понял.

Итак, что я сделал, чтобы смоделировать ситуацию, сделал новую рабочую тетрадь с 2 листами.

Один лист назван Landgebruik и получил значение A2, и я сделал это:

Второй лист называется Raw data. Я скрываю некоторые столбцы, чтобы использовать только столбцы C, O и S. В столбце SI вводились только значения, равные 1. В столбце OI случайным образом вводились значения, равные {20,21,22,23,40}, а в столбце CI случайным образом вводились значения, равные A или B. И это выглядит так это (обратите внимание, я скрываю некоторые столбцы):

И вопрос хотел бы суммировать значения в столбце S, но только если столбец O равен 20, или 21, или 22, или 23 или 40, а столбец C равен Landgebruik!A2 (в моем тесте значение есть буква A)

Мы можем использовать формулу массива для фильтрации данных в столбце S, а затем, после фильтрации, суммировать значения, соответствующие требованиям. В моем тесте правильным результатом будет 8, потому что только 8 значений в столбце S соответствуют требованиям столбцов C и O. На изображении правые строки выделены желтым цветом.

ОП уже сделал это, но хочет знать, есть ли более короткая/элегантная формула.

Самая короткая формула, которую я нашел, выглядит так:

=SUM(IF($O$2:$O$28 = {20;21;22;23;40};IF($C$2:$C$28=Landgebruik!$A$2;$S$2:$S$28)))

This is an array formula, so it must be inserted pressing CTRL+SHIFT+ENTER or it won't work!

КАК ЭТО РАБОТАЕТ:

Первый IF принимает все значения в столбце S и игнорирует все, где эквиваленты в столбце O не равны 20, 21, 22, 23 или 40. Второй IF берет этот новый массив и игнорирует все значения, где эквиваленты в столбце C не равны Landgebruik!$A$2. Итоговый массив суммируется функцией SUM

Я пытался объяснить как можно лучше. Я надеюсь, что вы можете адаптировать это к вашим потребностям.

Для этого можно использовать текстовый поиск:

--NOT(ISERROR(FIND('Raw data'!O:O,"2021222340")))

Но вы должны быть осторожны, чтобы более короткий идентификатор не был неправильно найден в более длинном идентификаторе, например. если вы хотите искать среди идентификаторов { 123, 456, 789 }, то 12 не считается среди идентификаторов. Таким образом, простой текстовый поиск, подобный приведенному выше, не сработает. Вам нужен символ-разделитель, чтобы разбить строку идентификаторов. Обычно я использую для этой цели символ вертикальной черты, так как я не могу вспомнить ни одного случая, когда он встречался в исходном тексте файла Excel, и потому что это делает формулу удобочитаемой:

--NOT(ISERROR(FIND("|"&'Raw data'!O:O&"|","|20|21|22|23|40|")))

Примеры:

'Необработанные данные'!O:O равно 20 => |21| находится в |20|21|22|23|40|

'Необработанные данные'!O:O равно 2 => |2| не найден в |20|21|22|23|40|

(Если ваши идентификаторы могут включать в себя символ вертикальной черты, вы можете использовать CHR(1), давно забытый код ASCII для SOH, означающий начало заголовка; конечно, он менее удобочитаем.)

Вся формула:

=SUMPRODUCT(--('Raw data'!C:C=Landgebruik!A2),--NOT(ISERROR(FIND("|"&'Raw data'!O:O&"|","|20|21|22|23|40|"))),'Raw data'!S:S)

(Извините, мой Excel использует вместо ;)

Если вас интересует производительность (скорость вычислений) и вы не боитесь вычисления матриц, вы можете использовать MMULT:

=SUMPRODUCT(--('Raw data'!C:C=Landgebruik!A2),MMULT(--('Raw data'!O:O = {20,21,22,23,24}),TRANSPOSE({1,1,1,1,1})),'Raw data'!S:S)

Объяснение:

Во-первых, вы создаете матрицу 1048576×5, где значение в i-й строке и j-м столбце равно 1, если идентификатор в «Исходных данных»! O: i-я строка O: O совпадает с j-й значение в перечислении {20,21,22,23,24}, 0 в противном случае.

Во-вторых, вы умножаете это на вектор из 1 (5 1, потому что {20,21,22,23,24} содержит пять элементов), что означает, что вы принимаете все пять значений.

В-третьих, из приведенного выше вы получаете вектор, где i-й элемент равен 1, если идентификатор входит в число допустимых значений, 0 в противном случае, и вы помещаете этот вектор рядом с другими в свой СУММПРОИЗВ.

(Извините, мой Excel использует ',' вместо ';'. Если вы хотите сократить формулу, вы можете написать {1;1;1;1;1} вместо ТРАНСП({1,1,1,1, 1}). Но вам нужно выяснить, что ваш Excel использует вместо ';' для разделения строк, скорее всего, '.'.)

Примечание. Это может повысить скорость расчета, если вы ссылаетесь на диапазон, который фактически содержит значения, а не на весь столбец, например. «Исходные данные»!C1:C123 вместо «Исходные данные»!C:C.

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

Обновлять

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

Я закомментировал другие формулы, пока выполнял этот код VBA для измерения времени:

Public Sub MeasureCalculationTime()
    Dim datStart As Date: datStart = Now

    Dim i As Long: For i = 1 To 1000
        Application.Calculate
    Next i

    Dim datFinish As Date: datFinish = Now
    Dim dblSeconds As Double: dblSeconds = (datFinish - datStart) * 24 * 60 * 60
    Debug.Print "Calculation finished at " & datFinish; " took " & dblSeconds & " seconds"
End Sub

В этом сценарии MMULT оказался не самым быстрым.

Тем не менее, я хотел бы отметить, что он является наиболее гибким, потому что

1. Вы можете использовать его с переключателями: вы ссылаетесь на диапазон ячеек вместо {1,1,1,1,1}, и вы сможете очень быстро включать/исключать идентификаторы в выборе. Например, вы вставили в A1:A5 {20,21,22,23,24} и рядом с ним в B1:B5 {1,1,1,1,1}. Если вы хотите исключить 21, то вы переписываете B2 в 0, если хотите включить, вы записываете обратно в 1.

2. Вы можете использовать более сложные критерии, где вам нужно сравнить несколько уровней. Нравится:

   =СУММПРОИЗВ(MMULT(--(CarId=CarOwner),--(CarOwner=ListOfJobs),--(ListOfJobs=JobsByDepartment),--(DepartmentIncludedInSelection=1)),FuelConsumment)

Примечание. Вышеприведенная строка является просто псевдокодом, MMULT имеет только два параметра.