Listen konsolidieren und Werte summieren
Matt
würde gerne noch einmal zu folgendem Beispiel ein paar Fragen stellen...
Hier zunächst der alte Thread:
"
Moin,
da ich nicht so recht fündig wurde, ob diese Aufgabenstellung schon einmal in ähnlicher Form beschrieben wurde, versuche ich es auf diesem Weg. Sollte das Problem doch anderswo beschrieben sein, dann reicht mir als Antwort natrlich auch nur ein Hinweis (Link) dorthin...
Folgendes "Problem":
Ich würde gerne 225 einzelne Excel-Dateien konsolidieren. Es handelt sich um Lieferscheine, die alle auf einer Vorlage basieren, bei der alle Produkte aufgelistet sind Spalten: Produkt, Kommentar, Abgabemenge, Preis, Anzahl, Gesamt. In der Vorlage sind alle 394 Produkte vorgepflegt, zusammen mit Kommentar, Abgabemenge , Anzahl (leer) und Preis. Für die Lieferscheine wurden dann jeweils für die relevanten Produkte die verkaufte Anzahl eingetragen. Die Gesamtspalte ist natürlich die Multiplikation aus Preis x Anzahl. Um die Lieferschein auf die betroffenen Produkte zu beschränken sind die anderen "leeren" Positionen (keine Anzahl eingetragen) über die Filterfunktion in Anzahl ausgeblendet.
Alle 225 Lieferscheine sollen nun so zusammengeführt werden, dass die Gesamtanzahl der verkauften Produkte ermittelbar ist.
Wie mache ich das am besten? VBA oder mit bekannten Formeln (SVERWEIS, SUMME); allerdings schreckt mich da die Anzahl der Dateien ab, die alle eintragen werden müssten.
Mein Überlegung war eine for-Schleife, die alle einzelnen Dateien durchläuft und per SVERWEIS prüft, ob das Produkt im Lieferschein einen "Anzahl" eintrag hat und dann den Wert zum bestehenden Wert in der Kosolidierungsdatei zusammenführt, d.h. addiert. Leider habe ich k.A. wie das geht... :-(
Vorlage.xls:
Produkt | Kommentar | Abgabemenge | Preis | Anzahl | Gesamt
abc | xoxo | 50g | 1,50 | |
def | yoyo | 100g | 1,00 | |
ghi | zozo | 25g | 2,00 | |
jkl | xoxo | 10g | 3,00 | |
Lieferschein1.xls:
Produkt | Kommentar | Abgabemenge | Preis | Anzahl | Gesamt
abc | xoxo | 50g | 1,50 | 2 | 3,00
ghi | zozo | 25g | 2,00 | 1 | 2,00
Lieferschein2.xls (andere Datei):
Produkt | Kommentar | Abgabemenge | Preis | Anzahl | Gesamt
abc | xoxo | 50g | 1,50 | 1 | 1,50
def | yoyo | 100g | 1,00 | 2 | 2,00
Die Konsolodierung soll dann natürlich folgendes stehen:
Produkt | Kommentar | Abgabemenge | Preis | Anzahl | Gesamt
abc | xoxo | 50g | 1,50 | 3 | 4,50
def | yoyo | 100g | 1,00 | 2 | 2,00
ghi | zozo | 25g | 2,00 | 1 | 2,00
jkl | xoxo | 10g | 3,00 | 0 | 0,00
Danke für die Ratschläge und Hilfe!
Matt
Hallo
so z.B.
- Alle Dateien des gewählten Verzeichnisses nacheinander in das gemeinsame Tabellenblatt kopieren
- Filtern nach Spalte E =0
- Diese weglöschen
- Filter wieder raus
Sub alle_Dateien_Verzeichnis()
Dim dlg As FileDialog
Dim Si, Ext$, Datei$, TB1, TB2, SP%, LR1%, LR2%
Set TB1 = ActiveSheet
If TB1.AutoFilterMode Then TB1.AutoFilterMode = False ' Autofilter ggf ausschalten
SP = 1 ' Spalte A
Set dlg = Application.FileDialog(msoFileDialogFolderPicker) 'Verzeichnis wählen
If dlg.Show = True Then
For Each Si In dlg.SelectedItems 'Die Abfrage für den selektierten Eintrag
Ext = "*.csv*" 'Dateiextension (auch .xlsm von XL 2007 berücksichtigt)
Si = IIf(Right(Si, 1) = "\", Si, Si & "\")
Datei = Dir(Si & Ext)
Do While Len(Datei) > 0
LR1 = TB1.Cells(Rows.Count, SP).End(xlUp).Row 'letzte Zeile der Spalte
Workbooks.Open Filename:=Si & Datei
Set TB2 = ActiveSheet
LR2 = TB2.Cells(Rows.Count, SP).End(xlUp).Row 'letzte Zeile der Spalte
TB2.Rows("2:" & LR2).Copy TB1.Rows(LR1 + 1)
Workbooks(Datei).Close SaveChanges:=False
Datei = Dir() ' nächste Datei
Loop
Next
LR1 = TB1.Cells(Rows.Count, SP).End(xlUp).Row 'letzte Zeile der Spalte
TB1.Columns("A:F").AutoFilter
TB1.Range("$A$1:$F$" & LR1).AutoFilter Field:=5, Criteria1:="0"
TB1.Rows("2:" & LR1).Delete Shift:=xlUp
If TB1.AutoFilterMode Then TB1.AutoFilterMode = False ' Autofilter ggf ausschalten
End If
End Sub
Gruß UweD
"
---
Vielen Dank für die Hilfe!!! Das Makro erleichtert die Konsolidierung ungemein. Allerdings summiert es nicht die Werte, wenn die Einträge in verschiedenen Tabellen mehrfach vorkommen. Macht aber nix, da kann man dann ja mit einer Pivot-Tabelle arbeiten.
Mich interessiert nun noch...
Wie kann ich einstellen, dass ich erst ab bspw. Zeile 17 anfangen möchte den Inhalt in die Zusammenfassungsdatei zu kopieren (weil der Tabellenkopf nicht immer wieder mit kopiert werden soll)?
Wie kann ich Teile der Tabellenkopf-Informationen in eine neue Spalte einfügen lassen.
Ich habe stets in Zeile 18, Spalte B die Lieferscheinnummer und in Spalte F das Datum stehen. Diese Information würde ich gerne noch als neue Spalte für jeden Eintrag hinzufügen.
Um das o.g. Beispiel zu zitieren, soll es dann in etwa so aussehen:
Lieferschein1.xls:
Lieferschein 001 01.01.12
Produkt | Kommentar | Abgabemenge | Preis | Anzahl | Gesamt
abc | xoxo | 50g | 1,50 | 2 | 3,00
ghi | zozo | 25g | 2,00 | 1 | 2,00
Lieferschein2.xls (andere Datei):
Lieferschein 002 02.01.12
Produkt | Kommentar | Abgabemenge | Preis | Anzahl | Gesamt
abc | xoxo | 50g | 1,50 | 1 | 1,50
def | yoyo | 100g | 1,00 | 2 | 2,00
Ausgehend mit dem Makro von UweD habe ich dann folgende Zusammenfassung, die ich um zwei Spalten (Lieferschein & Datum) ergänzen würde:
Lieferschein| Datum | Produkt | Kommentar | Abgabemenge | Preis | Anzahl | Gesamt
001 | 01.01.12 | abc | xoxo | 50g | 1,50 | 2 | 3,00
001 | 01.01.12 | ghi | zozo | 25g | 2,00 | 1 | 2,00
002 | 02.01.12 | abc | xoxo | 50g | 1,50 | 1 | 1,50
002 | 02.01.12 | def | yoyo | 100g | 1,00 | 2 | 2,00
Wenn ich dann eine Pivot erstelle, müsste ich doch die einzelnen Produkte summieren können, nicht wahr?
abc | xoxo | 50g | 1,50 | 3 | 4,50
def | yoyo | 100g | 1,00 | 2 | 2,00
ghi | zozo | 25g | 2,00 | 1 | 2,00
jkl | xoxo | 10g | 3,00 | 0 | 0,00
Danke vielmals für eure Hilfe,
Matt