Bevor sich jetzt jemand aufregt: Ich weiß, dass diese Frage schon an die 1000 Mal in verschiedensten Foren gestellt wurde, aber ich habe auch nach langer Suche immer noch keine Antwort finden können, die mir weitergeholfen hätte, deshalb stelle ich sie jetzt noch mal.
Folgendes Problem:
Es liegt eine lange Liste von gelieferten Produkten vor, mitsamt der Lieferadressen, Datum und Anzahl der jeweils gelieferten Produkte. Diese Liste entstand aus einer Ansammlung verschiedener Rechnungsdokumente. Leider gibt es aber Tag, an denen mehrere Lieferungen an dieselbe Adresse gingen, und in diesen Lieferungen waren teilweise die gleichen Produkte enthalten, nur mit unterschiedlicher Stückzahl. Und diese Rechnungen bilden jetzt natürlich auch je einen eigenen Eintrag in meiner Liste.
Nun soll am Ende in der Liste nur noch die Information existieren, wie viele Exemplare welches Produkts an jedem Tag zu welcher Adresse geliefert wurden. Es sollen also alle Zeilen, in denen Adresse (steht in 2 Spalten: Einmal Name des Empfängers und einmal Straße + Hausnr.), Lieferdatum und Artikelnummer gleich sind, in einer Zeile zusammengefasst werden, und die Anzahl soll der Summe der Zahlen entsprechen, die vorher in den jetzt zusammengefassten Zeilen standen. Nochmal ganz deutlich: Ich will NICHT wissen, wie viele Zeilen zusammengefasst wurden, sondern ich will wirklich eine Summe aus den Werten bilden, die vorher in diesen Zeilen standen.
Ich hänge als Beispiel mal eine Datei an. Auf der linken Seite sieht man, wie die Tabelle aussieht (zumindest im Groben und Ganzen), auf der rechten Seite das gewünschte Ergebnis.
Auf meiner Suche nach einer Lösung kamen mir immer wieder Sätze wie "Das kannst du mit ein paar Klicks über eine Pivot-Tabelle lösen" unter die Augen. Solche tollen Ratschläge helfen einem aber nicht weiter, wenn man sich (wie ich) nicht mit Pivot-Tabellen auskennt. Und meine Versuche, das Problem auf diese Weise zu lösen, haben leider nicht zum gewünschten Ergebnis geführt. Wenn einer von euch also einen Weg kennt, das Ganze mit Pivot-Tabellen zu lösen, dann würde ich darum bitten, genau zu beschreiben, wie diese Lösung aussieht.
Vielen Dank im Voraus,
VodeAn
Hallo!
Das kannst du mit ein paar Klicks über eine Pivot-Tabelle lösen!
Und im Ernst:
Dies liest sich bisher (ohne die Datei gesehen zu haben) so, als wenn SUMMEWENNS() das Richtige für Dich wäre.
Ich schaue mir aber jetzt die Mappe an.
Gruß Ralf
Hi!
Mal per "Hand".
1. kopiere die Spalten A:D in in neues Blatt
2. dort Daten, Duplikate entfernen (auf alle vier Spalten anwenden)
3. dann folgende Formel (hier der Einfachheit halber im selben Tabellenblatt):
| A | B | C | D | E | F | G | H | I | J | K |
| 1 | Name | Adresse | Lieferdatum | Art.-Nr. | Anzahl | | Name | Adresse | Lieferdatum | Art.-Nr. | Anzahl |
| 2 | Meier | Beispielstr. 1 | 01.01.2016 | 1.1.1 | 5 | | Meier | Beispielstr. 1 | 42370 | 1.1.1 | 8 |
| 3 | Meier | Beispielstr. 1 | 01.01.2016 | 1.1.2 | 5 | | Meier | Beispielstr. 1 | 42370 | 1.1.2 | 5 |
| 4 | Meier | Beispielstr. 1 | 01.01.2016 | 1.1.3 | 4 | | Meier | Beispielstr. 1 | 42370 | 1.1.3 | 19 |
| 5 | Meier | Beispielstr. 1 | 01.01.2016 | 1.1.1 | 3 | | Schulze | Beispielstr. 2 | 42401 | 1.1.1 | 4 |
| 6 | Meier | Beispielstr. 1 | 01.01.2016 | 1.1.3 | 15 | | Schulze | Beispielstr. 2 | 42401 | 1.1.2 | 75 |
| 7 | Schulze | Beispielstr. 2 | 01.02.2016 | 1.1.1 | 4 | | Schulze | Beispielstr. 2 | 42401 | 1.1.3 | 5 |
| 8 | Schulze | Beispielstr. 2 | 01.02.2016 | 1.1.2 | 75 | | Schulze | Beispielstr. 2 | 42415 | 1.1.1 | 7 |
| 9 | Schulze | Beispielstr. 2 | 01.02.2016 | 1.1.3 | 5 | | Schulze | Beispielstr. 2 | 42415 | 1.1.4 | 8 |
| 10 | Schulze | Beispielstr. 2 | 15.02.2016 | 1.1.1 | 7 | | Müller | Beispielstr. 3 | 42430 | 1.1.1 | 4 |
| 11 | Schulze | Beispielstr. 2 | 15.02.2016 | 1.1.4 | 8 | | Müller | Beispielstr. 3 | 42430 | 1.1.2 | 5 |
| 12 | Müller | Beispielstr. 3 | 01.03.2016 | 1.1.1 | 4 | | Müller | Beispielstr. 3 | 42430 | 1.1.3 | 61 |
| 13 | Müller | Beispielstr. 3 | 01.03.2016 | 1.1.2 | 2 | | | | | | |
| 14 | Müller | Beispielstr. 3 | 01.03.2016 | 1.1.3 | 45 | | | | | | |
| 15 | Müller | Beispielstr. 3 | 01.03.2016 | 1.1.2 | 3 | | | | | | |
| 16 | Müller | Beispielstr. 3 | 01.03.2016 | 1.1.3 | 16 | | | | | | |
| Formeln der Tabelle |
| Zelle | Formel | | K2 | =SUMMEWENNS(E:E;A:A;G2;B:B;H2;C:C;I2;D:D;J2) |
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Ralf
Ich hab einen Augenblick gebraucht, um das zu begreifen, aber ich glaube, ich hab es verstanden. Werde jetzt mal versuchen, das in meiner Produktivdatei umzusetzen.
Vielen Dank erst mal!
Gruß VodeAn
EDIT: Hat funktioniert, DANKE!
Hi,
die andere Lösung funktioniert ja, aber da Du gefragt hast:
(28.01.2016, 13:26)VodeAn schrieb: [ -> ]Wenn einer von euch also einen Weg kennt, das Ganze mit Pivot-Tabellen zu lösen, dann würde ich darum bitten, genau zu beschreiben, wie diese Lösung aussieht.
nun hier die Lösung mit Pivot:
anklicken der Datentabelle
- Menü "Einfügen" - Pivot-Tabelle
- ziehen von "Name", "Adresse", "Lieferdatum", "Art.-Nr." in den Zeilenbereich
- ziehen von "Anzahl" in den Werte-Bereich
- Rechtsklick in die Pivot-Tabelle, Auswahl von PivotTable-Optionen, Register "Anzeige", Haken setzen bei "Klassisches PivotTable-Layout"
- dann Register "Summen & Filter", 2x Haken entfernen bei "Gesamtsummen ..."
- OK drücken
- Rechtsklick in Pivot-Spalte "Name", abklicken von "Teilergebnis ..."
- Rechtsklick in Pivot-Spalte "Adresse", abklicken von "Teilergebnis ..."
- Rechtsklick in Pivot-Spalte "Lieferdatum", abklicken von "Teilergebnis ..."
Dann sieht das Ergebnis so aus:
Tabelle1 | P | Q | R | S | T |
| 15 | Summe von Anzahl | | | | |
| 16 | Name | Adresse | Lieferdatum | Art.-Nr. | Ergebnis |
| 17 | Meier | Beispielstr. 1 | 01.01.2016 | '1.1.1 | 8 |
| 18 | | | | '1.1.2 | 5 |
| 19 | | | | '1.1.3 | 19 |
| 20 | Müller | Beispielstr. 3 | 01.03.2016 | '1.1.1 | 4 |
| 21 | | | | '1.1.2 | 5 |
| 22 | | | | '1.1.3 | 61 |
| 23 | Schulze | Beispielstr. 2 | 01.02.2016 | '1.1.1 | 4 |
| 24 | | | | '1.1.2 | 75 |
| 25 | | | | '1.1.3 | 5 |
| 26 | | | 15.02.2016 | '1.1.1 | 7 |
| 27 | | | | '1.1.4 | 8 |
Schriftart wird in dieser Tabelle nicht dargestellt| Schriftformate | |
| Zelle | Rot | Grün | Blau | Color | Stil | Unterstreichung | Effekte | Durchgestrichen | Schriftart |
| P15: P27, Q15:Q27, R15:R27, S15:S27, T15:T27 | 0 | 0 | 0 | 0 | | | | | Calibri |
Zellen mit Schriftformatierung automatisch werden nicht dargestellt| Excel-Inn.de |
| Hajo-Excel.de |
| XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007 |
| Add-In-Version 21.07 einschl. 64 Bit |
Wenn Du jetzt noch aus dem Datenbereich eine intelligente Tabelle machst (STRG-T), kann die Pivot einfach durch aktualisieren bei weiteren Lieferungen erweitert werden.
Hier Deine Datei umgearbeitet:
[
attachment=3995]
Hallo VodeAn,
mit VBA und nicht als Pivot so:
PHP-Code:
Option Explicit
Public Sub Zusammenfassen()
Dim MyDict As Object ' As Dictionary
Dim vTemp As Variant ' ein temporärer Array
Dim lTemp As Long ' der For/Next Schleifen Index zum Array
Dim sText As String ' Zusammenfassung der ggf. noch doppelten
Dim lLetzte As Long ' die letzte belegte Zeile in Spalte E
Dim lZeile As Long ' der For/Next Schleifen-Index - hier die Zeile
Set MyDict = CreateObject("Scripting.Dictionary")
' die Eingabe-Daten aus Performance-Gründen in ein Array kopieren
With ThisWorkbook.Worksheets("Tabelle1") ' den Tabellenblattnamen ggf. anpassen!
vTemp = .Range("A4:E" & .Cells(.Rows.Count, 1).End(xlUp).Row) ' ggf. anpassen!
End With
' zusammenfassen der Begriffe und addieren der Werte
For lTemp = 1 To UBound(vTemp)
sText = vTemp(lTemp, 1) & "##" & vTemp(lTemp, 2) & "##" & vTemp(lTemp, 3) & "##" & vTemp(lTemp, 4)
MyDict(sText) = MyDict(sText) + Val(vTemp(lTemp, 5))
Next lTemp
Application.ScreenUpdating = False ' kein Bildschirm-Update mehr zulassen
' Ausgeben. Die Zielzellen müssen ggf. angepasst werden
With ThisWorkbook.Worksheets("Tabelle1")
lLetzte = 4 + .Cells(.Rows.Count, 1).End(xlUp).Row
' die im Dictionary gesammelten und addierten Werte ausgeben
.Range("I4").Resize(MyDict.Count) = WorksheetFunction.Transpose(MyDict.keys)
.Range("M4").Resize(MyDict.Count) = WorksheetFunction.Transpose(MyDict.Items)
For lZeile = 4 To 4 + MyDict.Count - 1
vTemp = Split(.Range("I" & lZeile).Value, "##")
.Range("I" & lZeile).ClearContents
.Range("I" & lZeile).Value = vTemp(0)
.Range("J" & lZeile).Value = vTemp(1)
With .Range("K" & lZeile)
.NumberFormat = "DD.MM.YYYY"
.Value = vTemp(2)
End With
.Range("L" & lZeile).Value = vTemp(3)
Next lZeile
' ab Zeile 2 bis zur letzten belegten Zeile alles einschl. D-G aufsteigend sortieren
.Range("I4:M" & lLetzte).Sort _
Key1:=.Range("I4"), Order1:=xlAscending, _
Key2:=.Range("J4"), Order2:=xlAscending, _
Key3:=.Range("K4"), Order3:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=True, Orientation:=xlTopToBottom
.Columns("I:M").EntireColumn.AutoFit
End With
Application.ScreenUpdating = True ' das Bildschirm-Update wieder zulassen
End Sub
Gruß Peter
Hallo VodeAn,
mit VBA und nicht als Pivot so:
PHP-Code:
Option Explicit
Public Sub Zusammenfassen()
Dim MyDict As Object ' As Dictionary
Dim vTemp As Variant ' ein temporärer Array
Dim lTemp As Long ' der For/Next Schleifen Index zum Array
Dim sText As String ' Zusammenfassung der ggf. noch doppelten
Dim lLetzte As Long ' die letzte belegte Zeile in Spalte E
Dim lZeile As Long ' der For/Next Schleifen-Index - hier die Zeile
Set MyDict = CreateObject("Scripting.Dictionary")
' die Eingabe-Daten aus Performance-Gründen in ein Array kopieren
With ThisWorkbook.Worksheets("Tabelle1") ' den Tabellenblattnamen ggf. anpassen!
vTemp = .Range("A4:E" & .Cells(.Rows.Count, 1).End(xlUp).Row) ' ggf. anpassen!
End With
' zusammenfassen der Begriffe und addieren der Werte
For lTemp = 1 To UBound(vTemp)
sText = vTemp(lTemp, 1) & "##" & vTemp(lTemp, 2) & "##" & vTemp(lTemp, 3) & "##" & vTemp(lTemp, 4)
MyDict(sText) = MyDict(sText) + Val(vTemp(lTemp, 5))
Next lTemp
Application.ScreenUpdating = False ' kein Bildschirm-Update mehr zulassen
' Ausgeben. Die Zielzellen müssen ggf. angepasst werden
With ThisWorkbook.Worksheets("Tabelle1")
lLetzte = 4 + .Cells(.Rows.Count, 1).End(xlUp).Row
' die im Dictionary gesammelten und addierten Werte ausgeben
.Range("I4").Resize(MyDict.Count) = WorksheetFunction.Transpose(MyDict.keys)
.Range("M4").Resize(MyDict.Count) = WorksheetFunction.Transpose(MyDict.Items)
For lZeile = 4 To 4 + MyDict.Count - 1
vTemp = Split(.Range("I" & lZeile).Value, "##")
.Range("I" & lZeile).ClearContents
.Range("I" & lZeile).Value = vTemp(0)
.Range("J" & lZeile).Value = vTemp(1)
With .Range("K" & lZeile)
.NumberFormat = "DD.MM.YYYY"
.Value = vTemp(2)
End With
.Range("L" & lZeile).Value = vTemp(3)
Next lZeile
' ab Zeile 2 bis zur letzten belegten Zeile alles einschl. D-G aufsteigend sortieren
.Range("I4:M" & lLetzte).Sort _
Key1:=.Range("I4"), Order1:=xlAscending, _
Key2:=.Range("J4"), Order2:=xlAscending, _
Key3:=.Range("K4"), Order3:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=True, Orientation:=xlTopToBottom
.Columns("I:M").EntireColumn.AutoFit
End With
Application.ScreenUpdating = True ' das Bildschirm-Update wieder zulassen
End Sub
Gruß Peter