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

Question 1

Может быть, стоит попробовать это более короткое предложение формулы.

1] В F2формуле, скопированной по горизонтали и вертикали:

=SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))

2] Ваш предыдущий вопрос также можно решить с помощью этой же формулы, я поместил ее в A19:C23.

В B19, формула скопирована по горизонтали и по вертикали:

=SUMPRODUCT(0+TEXT(SUBSTITUTE(B$2:B$15,$A19,""),"0%;;;\0"))

И,

Редактировать

Удалить 0

1] Использование формата ячейки:

Все ячейки с формулами форматируются в >> «Учет» >> «Символ», выберите «Нет» >> «Десятичный знак»: нажмите «1»

2] Используя ЕСЛИОШИБКА(1(/1.......),"") добавляем к формуле F2 и получаем >>

=IFERROR(1/(1/SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))),"")

Answer

Может быть, стоит попробовать это более короткое предложение формулы.

1] В F2формуле, скопированной по горизонтали и вертикали:

=SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))

2] Ваш предыдущий вопрос также можно решить с помощью этой же формулы, я поместил ее в A19:C23.

В B19, формула скопирована по горизонтали и по вертикали:

=SUMPRODUCT(0+TEXT(SUBSTITUTE(B$2:B$15,$A19,""),"0%;;;\0"))

И,

Редактировать

Удалить 0

1] Использование формата ячейки:

Все ячейки с формулами форматируются в >> «Учет» >> «Символ», выберите «Нет» >> «Десятичный знак»: нажмите «1»

2] Используя ЕСЛИОШИБКА(1(/1.......),"") добавляем к формуле F2 и получаем >>

=IFERROR(1/(1/SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))),"")

Question 2

Решение более сложное, поскольку диапазон обработки многомерен. (В вашем предыдущем вопросе речь шла только об одном измерении).

Один из способов обработки — превратить его в одномерный массив, что можно сделать с помощью TEXTJOINи FILTERXML. Существует ограничение, заключающееся в том, что строка, полученная в результате TEXTJOINфункции, не может превышать 32,767символов. Если это будет проблемой, то решение VBA или Power Query может быть лучше.

Кроме того, FILTERXMLнедоступно в версиях для Mac и в Excel Online.

Если эти ограничения не применяются, то

F2: =IFERROR(4*SUM(FILTERXML("<t><s>" & SUBSTITUTE(FILTERXML("<t><s>" &TEXTJOIN("</s><s>",TRUE,$B2:$C3) & "</s></t>","//s[starts-with(.,'" & F$1 & "')]")," ","</s><s>") & "</s></t>","//s[last()]")),"")

Заполнить справа и снизу
Сначала мы создаем XML(используя TEXTJOIN) отдельный узел для каждой ячейки
- наш xPathаргумент возвращает только те узлы, start-withимя которых указано в строке 1.
- Затем мы используем SUBSTITUTEдля создания другого узла XMLна основе узлов, разделенных пробелом, с помощью , xPathкоторый возвращает последний узел.
- Выполните математические действия.

РЕДАКТИРОВАТЬ:

Если ты это сделаешьНЕТесть FILTERXMLфункция, попробуйте эту формулу(O365 Mac или Windows и Excel Online)который использует другой метод преобразования двумерного массива в одномерный массив для обработки:

=IFERROR(SUM(4*TRIM(RIGHT(
   SUBSTITUTE(LET(
        seq,IF(SEQUENCE(99)=1,1,(SEQUENCE(99)-1)*99),
        x,TRIM(MID(TEXTJOIN(REPT(" ",99),TRUE,$B2:$C3),seq,99)),
        y, FILTER(x,LEFT(x,LEN(F$1))=F$1),y),
   " ",REPT(" ",99)),99))),"")

Эта формула все еще имеет ограничение в 32 767 символов. Столкнетесь ли вы с этим, будет зависеть в основном от того, сколько пробелов в названии проекта. Формула для вычисления примерно такая:

а = среднее количество символов в ячейке
b = количество пробелов в ячейке
с = количество ячеек
```
 (a+b*99) * c
```

Если бы у вас были, например, 15символы, 5пробелы и 24ячейки, то это составило бы около 12,000символов.

Answer