Блог

Мне интересно, есть ли простой способ подсчитать количество строк после фильтрации подмножества столбцов по значению с учетом множественных выборов для данного столбца в объекте таблицы Excel.

Допустим, у меня есть следующая таблица Excel:

 A   B   C
a1  b1  c1
a2  b2  c1
a1  b2  c2
a2  b1  c2
a1  b3  c3
a3  b1  c3
  

сохранено в таблице Excel под именем: Table1 и я хотел бы найти все строки, в которых столбец A имеет значение a1 или a2 а столбец B имеет значение: b1 . Результат должен быть равен 2.

Я могу сделать это с помощью SUMPRODUCT функции и преобразовать логическое значение в [0,1] с помощью -- оператора:

 = SUMPRODUCT(--(Table1[A]="a1"),--(Table1[B]="b1")) 
     SUMPRODUCT(--(Table1[A]="a2"),--(Table1[B]="b1"))
  

В моем реальном примере у меня более трех столбцов, и по крайней мере один из них может удовлетворять нескольким критериям, поэтому мне интересно, есть ли способ сделать это с менее подробным синтаксисом. Например, я пытался сделать что-то подобное, но это не работает:

 = SUMPRODUCT(--(Table1[A]="a1|ab2"),--(Table1[B]="b1"))
  

или

 =SUMPRODUCT(--(Table1[A]=OR("a1", "a2")), --(Table1[B]="b1"))
  

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

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

Согласно моему решению, это было бы что-то вроде этого для подсчета строк в таблице, где для каждого столбца мы фильтруем только по одному значению, за исключением первого столбца A , который мы фильтруем по двум возможным значениям:

 = SUMPRODUCT(--(Table1[A]="a1"),--(Table1[B]="b1"), 
    --(Table1[C]="c1"), ...Table1[Z]="z1"))
    SUMPRODUCT(--(Table1[A]="a2"),--(Table1[B]="b1"), 
    --(Table1[C]="c1"), ...Table1[Z]="z1"))
  

Попробуйте:

 =SUMPRODUCT((Table1[A]="a1") (Table1[A]="a2"),--(Table1[B]="b1"))
  

Поскольку ни в одной из заданных ячеек не может быть одновременно «a1» и «a2», сумма будет равна, 1 если либо из них истинно, и 0 если ни одно из них не истинно