Verwenden Sie LEFT im sum_range-Parameter der SUMIF-Funktion (Excel).

431
Geza Kerecsenyi

Ich habe viele Spalten übereinander, so:

Cells on top of each other

Ich würde gerne alle Zellen in diesem Bereich finden, die beginnen 1aund dann zusammenfassen, was folgt. In diesem Beispiel finden wir 1a10und 1a20, unser Ergebnis wäre also 10 + 20 oder 30.

Die schwierige Sache ist, dass es nach dem aein oder zwei Ziffern geben kann, und ich möchte es auch erweitern, um mit zweistelligen Zahlen vor dem aebenfalls zu arbeiten. Wir könnten also

1a10 1a1 11a1 11a10

Ich habe versucht, mit dem zu experimentieren, was ich gelernt habe SUMIF, SUMIFSund SEARCHobwohl ich in der Lage war, (bis zu einem gewissen Grad) herauszufinden, welche Zellen ausgewählt werden sollen, und den Anfang davon abschneidet, insbesondere wenn die Länge des Endes und der Anfang beginnt kann variieren, verließ mich mit einem Durcheinander von gestapelten IFAnweisungen und einer nicht funktionalen Formel.

Leider kann ich keine Makros verwenden und meine Schule ist nur bis zu Excel 2013, aber ich hoffe, dass dies immer noch möglich ist.

Vielen Dank.

0
Ihre Frage ist wirklich klar. "Ich möchte es auch erweitern, um mit zweistelligen Zahlen vor dem a zu arbeiten", müssen Sie also mit 1a oder 1a beginnen? Máté Juhász vor 5 Jahren 1

3 Antworten auf die Frage

3
Forward Ed

Sie können dies mit einer Array-Formel tun

=SUM((LEFT($A$2:$A$8,2)="1a")*(IF(ISNUMBER(--RIGHT($A$2:$A$8,LEN($A$2:$A$8)-2)),RIGHT($A$2:$A$8,LEN($A$2:$A$8)-2),0))) 

Da dies eine Arrayformel ist, müssen Sie Control+ Shift+ Enterstatt nur verwenden Enter.

Da es sich um eine Array-Formel handelt, möchten Sie außerdem auf vollständige Spaltenreferenzen wie z A:A

Sie werden wissen, dass Sie sie richtig eingegeben haben, wenn Sie {} um Ihre Formel sehen. Beachten Sie, dass {} möglicherweise nicht manuell hinzugefügt wird.

POC

AKTUALISIEREN

Wenn Sie die Formel für längere Suchzeichenfolgen (z. B. 22a) robuster gestalten möchten und eine Zelle für die ID-Zeichenfolge verwenden möchten, nach der Sie summieren möchten, können Sie die folgende Formel verwenden.

=SUM((LEFT($A$1:$A$8,LEN(TRIM(C2)))=C2)*(IF(ISNUMBER(--RIGHT($A$1:$A$8,LEN($A$1:$A$8)-LEN(C2))),RIGHT($A$1:$A$8,LEN($A$1:$A$8)-LEN(C2)),0))) 

Beachten Sie, dass dies immer noch eine Matrixformel ist. Außerdem wurde der Bereich um den Datenwert einer einzelnen Ziffer nach dem gesuchten Schlüssel erweitert.

POC2

kleineres Update, geändert -RIGHTin --RIGHT... aber anscheinend wird das - möglicherweise nicht einmal benötigt.

Es funktioniert perfekt. genau das, was ich brauchte und funktioniert einwandfrei mit jeder ID oder Nummer! Ich bin überrascht, wie Sie die Formel so kompakt machen konnten, vor allem, wenn es um etwas so Kompliziertes geht. Geza Kerecsenyi vor 5 Jahren 0
0
user2800

Ich möchte deine Arbeit nicht für dich erledigen, aber ich kann dir einige Vorschläge geben ...

Es gibt verschiedene Möglichkeiten, dies mit einer oder zwei großen, langen Formeln zu tun, aber Sie können es in mehrere Spalten aufteilen, um es verständlicher zu machen. Zum Beispiel könnten Sie in B1 verwenden

=IF(LEFT(A2,2)="1a",1,0)) Dies gibt eine 1 für Ihre Zellen zurück, die mit "1a" beginnen, und eine 0 für diejenigen, die dies nicht tun.

In C1 könnte man verwenden

=LEN(A2) Dadurch wird die Anzahl der Zeichen in A2 zurückgegeben.

Beginnen Sie dann mit dem Schreiben von IF-Anweisungen mit Formeln (Pseudo-Code unten, keine tatsächlichen Formeln).

"If A2=1 AND C1=4, return RIGHT(A2,2)"  "If A2=1 AND C1=3, return RIGHT(A2,1)" 

Alles, was ich tue, ist das Blatt einzurichten, um die "10" von 1a10 in eine eigene Spalte zu bringen. Sie können dann mit den resultierenden Zahlen die gewünschte Algebra ausführen.

Und wie würde ich sie zusammenfassen? Ich würde eine dreidimensionale Struktur benötigen, da ich 20 verschiedene Zahlen vor dem "a" habe, was eine sehr komplizierte Helpersäulenstruktur wäre ... Geza Kerecsenyi vor 5 Jahren 0
"Ich möchte Ihre Arbeit nicht für Sie erledigen, aber ich kann Ihnen einige Vorschläge unterbreiten." Diese Art von Hinweis passt nicht gut auf diese Website. Wenn Sie tatsächlich keine Lösung anbieten möchten, schreiben Sie nur einen Kommentar. Wenn Sie eine Antwort schreiben, dann schreiben Sie sie gut. Jetzt haben Sie mehr Zeit für das Hinweisen als für das Schreiben der Lösung aufgewendet. Ein paar Formeln schreiben heißt nicht "die Arbeit anderer" Máté Juhász vor 5 Jahren 0
@ Máté Juhász - Danke für die Tipps, ich bin noch neu auf der Seite. Hatte nicht genug Ruf, um Kommentare zu posten, als ich meinen ursprünglichen Beitrag hinterlegte. Mein Ansatz für das OP bestand im Wesentlichen darin, jede der erforderlichen Berechnungen / Formeln als separate Spalte auszuführen, um die Logik leichter zu folgen als die oben beschriebenen einzeiligen Arrayformeln. user2800 vor 5 Jahren 0
@Geza Kerecsenyi Wenn du deinen Kommentar weiter oben gibst, dann werden ja die anderen Antworten viel besser funktionieren als meine. user2800 vor 5 Jahren 0
0
Máté Juhász

Hier ist ein anderer Ansatz mit SUBSTITUTE:

=SUM(IFERROR(SUBSTITUTE(A1:A7,C1,"")*1,0))

Wie es funktioniert:

  • =SUM(IFERROR(SUBSTITUTE(A1:A7,C1,"")*1,0))
  • SUBSTITUTE(A1:A7,C1,"")- Entfernt 1aalle Zeilen
  • SUBSTITUTE(...)*1- wandelt Einträge in Zahlen um, wo dies möglich ist (nur Zeilen, in denen 1azuvor entfernt wurde), andere führen zu Fehlern
  • IFERROR(...,0) - wandelt Fehler in um 0

Auch dies ist eine Matrixformel, daher müssen Sie nach der Eingabe STRG + UMSCHALT + EINGABETASTE drücken.