Filtern Sie Spalten basierend auf der Startzeichenfolge, lesen Sie Zahlen aus gefilterten Zellen und führen Sie Rechenoperationen aus, um eine Zusammenfassung von zwei Zeilen in Excel zu generieren

Question 1

Versuchen Sie es vielleicht mit diesem kürzeren Formelvorschlag.

1] In F2wird die Formel quer und nach unten kopiert:

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

2] Ihre vorherige Frage kann auch mit derselben Formel gelöst werden, ich habe sie in A19:C23 eingetragen.

In B19wird die Formel quer und nach unten kopiert:

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

Und,

Bearbeiten

So entfernen Sie 0

1] Verwenden des Zellenformats:

Formatieren Sie alle Formelzellen unter >> „Buchhaltung“ >> „Symbol“, wählen Sie „Keines“ >> „Dezimalstelle“ und klicken Sie auf „1“.

2] Verwenden Sie IFERROR(1(/1.......),"") und ergänzen Sie die Formel F2. Sie erhalten >>

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

Answer

Versuchen Sie es vielleicht mit diesem kürzeren Formelvorschlag.

1] In F2wird die Formel quer und nach unten kopiert:

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

2] Ihre vorherige Frage kann auch mit derselben Formel gelöst werden, ich habe sie in A19:C23 eingetragen.

In B19wird die Formel quer und nach unten kopiert:

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

Und,

Bearbeiten

So entfernen Sie 0

1] Verwenden des Zellenformats:

Formatieren Sie alle Formelzellen unter >> „Buchhaltung“ >> „Symbol“, wählen Sie „Keines“ >> „Dezimalstelle“ und klicken Sie auf „1“.

2] Verwenden Sie IFERROR(1(/1.......),"") und ergänzen Sie die Formel F2. Sie erhalten >>

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

Question 2

Die Lösung ist komplizierter, da der zu verarbeitende Bereich mehrdimensional ist. (In Ihrer vorherigen Frage war es nur eine einzige Dimension.)

TEXTJOINEine Möglichkeit zur Verarbeitung besteht darin, es in ein eindimensionales Array umzuwandeln, was mit und möglich ist FILTERXML. Es gibt eine Einschränkung, da die aus der TEXTJOINFunktion resultierende Zeichenfolge nicht mehr 32,767als Zeichen enthalten darf. Wenn dies ein Problem darstellt, ist eine VBA- oder Power Query-Lösung möglicherweise besser.

Außerdem FILTERXMList es nicht in den Mac-Versionen oder in Excel Online verfügbar.

Wenn diese Einschränkungen nicht gelten, dann

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

Nach rechts und unten ausfüllen
Wir erstellen zunächst XML(mit TEXTJOIN), um für jede Zelle einen separaten Knoten zu erstellen.
- Unser xPathArgument gibt nur die Knoten zurück, die start-withden Projektnamen in Zeile 1 haben.
- Wir verwenden dann, um einen weiteren basierend auf durch Leerzeichen getrennten Knoten mit einem SUBSTITUTEzu erstellen , der den letzten Knoten zurückgibt.XMLxPath
- Führen Sie die mathematischen Operationen durch.

BEARBEITEN:

Wenn Sie tunNICHThaben FILTERXML, versuchen Sie diese Formel(O365 Mac oder Windows und Excel Online)das eine andere Methode verwendet, um das 2D-Array zur Verarbeitung in ein 1D-Array umzuwandeln:

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

Diese Formel hat immer noch die Beschränkung auf 32.767 Zeichen. Ob Sie darauf stoßen, hängt hauptsächlich davon ab, wie viele Leerzeichen der Projektname enthält. Die zu berechnende Formel lautet ungefähr:

a = durchschnittliche Anzahl Zeichen in der Zelle
b = Anzahl der Leerzeichen in der Zelle
c = Anzahl der Zellen
```
 (a+b*99) * c
```

Wenn Sie beispielsweise 15Zeichen, 5Leerzeichen und 24Zellen hätten, käme das auf etwa 12,000Zeichen.

Answer