Filtre columnas según la cadena inicial, lea el número de las celdas filtradas y realice operaciones aritméticas para generar un resumen de dos filas en Excel

Question 1

Quizás pruebe esta propuesta de fórmula más corta.

1] En F2, fórmula copiada de arriba a abajo:

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

2] Tu pregunta anterior también se puede resolver con esta misma fórmula, la puse en A19:C23.

En B19, fórmula copiada de arriba a abajo:

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

Y,

Editar

Para eliminar 0

1] Usando formato de celda:

Todas las celdas de fórmula se formatean en >> "Contabilidad" >> "Símbolo", elija "Ninguno" >> "Lugar decimal": haga clic en "1"

2] Usando IFERROR(1(/1.......),"") agregando a la fórmula F2, y conviértase en >>

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

Answer

Quizás pruebe esta propuesta de fórmula más corta.

1] En F2, fórmula copiada de arriba a abajo:

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

2] Tu pregunta anterior también se puede resolver con esta misma fórmula, la puse en A19:C23.

En B19, fórmula copiada de arriba a abajo:

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

Y,

Editar

Para eliminar 0

1] Usando formato de celda:

Todas las celdas de fórmula se formatean en >> "Contabilidad" >> "Símbolo", elija "Ninguno" >> "Lugar decimal": haga clic en "1"

2] Usando IFERROR(1(/1.......),"") agregando a la fórmula F2, y conviértase en >>

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

Question 2

La solución es más complicada porque el rango a procesar es multidimensional. (En su pregunta anterior era solo una dimensión).

Una forma de procesarlo es convertirlo en una matriz unidimensional que se puede hacer usando TEXTJOINy FILTERXML. Existe una limitación en el sentido de que la cadena resultante de la TEXTJOINfunción no puede exceder 32,767los caracteres. Si eso supone un problema, entonces una solución VBA o Power Query podría ser mejor.

Además, FILTERXMLno está disponible en versiones para Mac ni en Excel Online.

Si esas limitaciones no se aplican, entonces

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()]")),"")

Llenar hacia la derecha y hacia abajo
Primero creamos XML(usando TEXTJOIN) para crear un nodo separado para cada celda.
- nuestro xPathargumento devuelve solo aquellos nodos cuyo start-withnombre del Proyecto está en la fila 1.
- Luego usamos SUBSTITUTEpara crear otro XMLbasado en nodos separados por espacios con un xPathque devuelve el último nodo.
- Haz las operaciones matemáticas.

EDITAR:

Si lo hacesNOtiene la FILTERXMLfunción, prueba esta fórmula(O365 Mac o Windows y Excel en línea)que utiliza un método diferente para convertir la matriz 2D en una matriz 1D para su procesamiento:

=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))),"")

Esta fórmula todavía tiene el límite de 32.767 caracteres. Que te encuentres con eso dependerá principalmente de cuántos espacios haya en el nombre del proyecto. La fórmula para calcular es aproximadamente:

a = número promedio de caracteres en la celda
b = número de espacios en la celda
c = número de celdas
```
 (a+b*99) * c
```

Si tuviera, por ejemplo, 15caracteres, 5espacios y 24celdas, eso sería aproximadamente 12,000caracteres.

Answer