Kathy hat ein Arbeitsblatt, das Informationen zu allen Teilen in ihrem Lager enthält. In diesem Blatt werden Teilenummern in Spalte A im Format 12345 XXX angezeigt, wobei XXX einen Standortcode darstellt. Dies bedeutet, dass sie mehrere Einträge für dieselben Teilenummern im Arbeitsblatt haben könnte, aber jeder Eintrag einen anderen Ort für dieses Teil darstellt. Kathy benötigt eine Formel, die die mit jeder Teilenummer verknüpften Werte unabhängig vom Standortcode summiert. Daher benötigt sie eine Möglichkeit, die Mengenspalte für die Teile 12345 ABC, 12345 DEF, 12345 GHI usw. zu summieren.

Sie braucht eine Möglichkeit, dies zu tun, ohne den Standortcode in eine andere Spalte aufzuteilen.

Es gibt mehrere Möglichkeiten, um die gewünschte Antwort zu erhalten. Nehmen Sie für die Beispiele in diesem Tipp an, dass sich die Teilenummern in Spalte A befinden (wie von Kathy angegeben) und dass sich die Mengen für jedes Teil in Spalte B befinden. Diese Mengen müssen auf der Grundlage von nur a summiert werden Teil dessen, was sich in jeder Zelle in Spalte A befindet. Außerdem können Sie die gewünschte Teilenummer (abzüglich des Ortscodes) in Zelle D2 eingeben.

Die erste mögliche Lösung besteht darin, die Funktion SUMPRODUCT folgendermaßen zu verwenden:

=SUMPRODUCT(--(VALUE(LEFT(A2:A49,FIND(" ",A2:A49)))=D2),B2:B49)

Diese Formel prüft die Werte im Bereich A2: A49. Sie sollten sicherstellen, dass dieser Bereich den Bereich Ihrer tatsächlichen Daten widerspiegelt. Wenn Sie die Formel so verallgemeinern, dass alle Spalten A und B angezeigt werden (wie in A: A und B: B), wird der Fehler #VALUE angezeigt, da versucht wird, die Formel auf leere Zellen in den Spalten anzuwenden .

Sie können ein ähnliches Ergebnis erzielen, indem Sie eine Array-Formel wie die folgende verwenden:

=SUM(B:B*(LEFT(A2:A49,5)=TEXT(D2,"@")))

Denken Sie auch hier daran, dass dies eine Array-Formel ist. Sie müssen sie also durch Drücken von Umschalt + Strg + Eingabetaste eingeben. Beachten Sie auch, dass diese Formel den Wert in D2 für den Vergleich in Text konvertiert. Dies wurde in der vorherigen Formel nicht durchgeführt, da dort der aus Spalte A ausgewählte Teilstring mit der Funktion VALUE in einen numerischen Wert konvertiert wurde.

Sie können auch die DSUM-Funktion verwenden, um eine Arbeitsformel zu erstellen. Nehmen wir an, dass die Teilenummern (Spalte A) eine Spaltenüberschrift in Zelle A1 haben.

Kopieren Sie diese Spaltenüberschrift (z. B. „Teilenummer“) in eine andere Zelle im Arbeitsblatt, z. B. Zelle D1. Geben Sie in Zelle D2 die Teilenummer ohne Standortcode gefolgt von einem Sternchen ein. Beispielsweise könnten Sie „12345 *“ (ohne Anführungszeichen) in Zelle D2 eingeben. Wenn diese Spezifikation eingerichtet ist, können Sie diese Formel verwenden:

=DSUM($A$1:$B$49,$B$1,D1:D2)

Diese Formel verwendet die Spezifikation in Zelle D2 (die Zeichen 12345 gefolgt von irgendetwas) als Schlüssel, zu dem Werte aus Spalte B summiert werden sollen.

Wenn Sie in Zelle D2 dieselbe Spezifikation hätten wie beim DSUM-Ansatz, könnten Sie eine sehr einfache SUMIF-Funktion auf folgende Weise verwenden:

=SUMIF(A:A,D2,B:B)

Beachten Sie, dass Sie mit diesem Ansatz die vollständigen Spaltenbereiche (A: A und B: B) in der Formel verwenden können.

Wenn Ihre Teilenummern (in Spalte A) in ihrem Format nicht so konsistent sind, wie Sie möchten, erstellen Sie möglicherweise besser eine benutzerdefinierte Funktion, um Ihre Mengen zu finden. Wenn Ihre Teilenummern beispielsweise nicht immer gleich lang sind oder wenn die Teilenummern sowohl Ziffern als auch Buchstaben oder Bindestriche enthalten können, ist eine UDF der richtige Weg. Das folgende Beispiel funktioniert hervorragend. Es gibt an, ob mindestens ein Leerzeichen im Wert vorhanden ist. (Kathy gab an, dass ein Leerzeichen die Teilenummer vom Standortcode trennte.)

Function AddPrtQty(ByVal Parts As Range, PartsQty As Range, _   FindPart As Variant) As Long     Dim Pos As Integer     Dim Pos2 As Integer     Dim i As Long     Dim tmp As String     Dim tmpSum As Long     Dim PC As Long

PC = Parts.Count     If PartsQty.Count <> PC Then         MsgBox "Parts and PartsQty must be the same length", vbCritical         Exit Function     End If

For i = 1 To PC         Pos = InStr(1, Parts(i), " ")

Pos2 = InStr(Pos + 1, Parts(i), " ")



If Pos2 > Pos And Len(Parts(i)) > Pos + 1 Then             tmp = CStr(Trim(Left(Parts(i), Pos2 - 1)))

ElseIf Pos > 0 And Len(Parts(i)) > 0 Then             tmp = CStr(Trim(Left(Parts(i), Pos - 1)))

End If

If CStr(Trim(tmp)) = CStr(Trim(FindPart)) Then             tmpSum = tmpSum + PartStock(i)

End If     Next i

AddPrtQty = tmpSum End Function

Um die Funktion zu verwenden, rufen Sie sie in Ihrem Arbeitsblatt mit zwei Bereichen und der gewünschten Teilenummer auf:

=AddPrtQty(A2:A49,B2:B49,"GB7-QWY2")

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (11469) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.

Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Summierung basierend auf einem Teil der Informationen in einer Zelle.