Блог

В AD столбце у меня есть такая последовательность значений:

 2
3
4
  

Эти значения относятся к строкам в столбце на другой странице.

В каждой строке AE столбца я использую эту формулу:

 =IF(AD1="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"amp;AD1)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"amp;AD1),SUBSTITUTE(SUBSTITUTE(JOIN("|",$V$1:$V$4),"||",""),"|||",""))=TRUE))))

=IF(AD2="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"amp;AD2)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"amp;AD2),SUBSTITUTE(SUBSTITUTE(JOIN("|",$V$1:$V$4),"||",""),"|||",""))=TRUE))))

=IF(AD3="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"amp;AD3)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"amp;AD3),SUBSTITUTE(SUBSTITUTE(JOIN("|",$V$1:$V$4),"||",""),"|||",""))=TRUE))))

  

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

Когда я пытаюсь добавить ARRAYFORMULA , чтобы мне не нужно было иметь формулу в каждой строке, оставляя только in AE1 , значения, которые возвращаются во всех строках, имеют точно такое же значение.

Сбой тестовой формулы:

 =ARRAYFORMULA(IF(AD1:AD="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"amp;AD1:AD)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"amp;AD1:AD),SUBSTITUTE(SUBSTITUTE(JOIN("|",$V$1:$V$4),"||",""),"|||",""))=TRUE)))))
  

Ссылка на пример Spreadhseet:
https://docs.google.com/spreadsheets/d/1qIv6KnLv-EwJQXRrk7ucuqY-XuJhkIHOCtih9FpAg6U/edit?usp=sharing

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

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

 =ArrayFormula(MMULT(
    N(SEQUENCE(D2)>=SEQUENCE(1,D2)),
    ARRAY_CONSTRAIN(
        IF(
            ('Registro Geral'!O2:O<>"")*
                IFNA(MATCH('Registro Geral'!H2:H,V:V,0)),
            'Registro Geral'!O2:O
        )/100,
        D2,
        1
    )
))
  

Почему это работает

Нижняя треугольная матрица выглядит следующим образом

 1 0 0 0 0 ... up to N columns
1 1 0 0 0
1 1 1 0 0
1 1 1 1 0
1 1 1 1 1
... up to N rows
  

Столбец, который вы хотите суммировать, выглядит следующим образом

 Value 1
Value 2
...
Value N
  

Итак, когда вы умножаете два, вы получаете новую матрицу размером N x 1:

 Value 1
Value 1   Value 2
...
Value 1   ...   Value N
  

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