Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1536to1540
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Komplexe Zusammenführung und Auswertung

Komplexe Zusammenführung und Auswertung
30.01.2017 18:30:47
Markus
Hallo Forum,
die angefügte Auswertung (Beispieldatei) handhabe ich normalerweise mittels einer Pivot-Tabelle.
Um den Prozess etwas zu automatisieren und um meine VBA-Kenntnisse etwas zu erweitern, würde ich es gerne mittels VBA lösen. Leider konnte ich im Internet keine Vorlage/Beispiel finden und meine VBA-Kenntnisse reichen für die Umsetzung nicht aus.
https://www.herber.de/bbs/user/111013.xlsx
An die VBA Experten: Kann mir da jemand weiterhelfen oder ist die Umsetzung zu komplex? Die farblichen Markierungen dienen nur zur besseren Übersicht.
Besten Dank für eure Mühe.

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Komplexe Zusammenführung und Auswertung
30.01.2017 19:25:16
ChrisL
Hi Markus
Da es darum geht deine VBA-Kenntnisse zu erweitern, extra keine Fertiglösung :)
Auch wenn das Ergebnis nicht ganz Programmierer-freundlich ist, scheint es ein gutes/machbares Beispiel für Einsteiger. Eine kleine Hürde hast du, weil wenn du die Ergenis-Tabelle von oben nach unten füllst, brauchst du das Total bevor die Datensätze geschrieben wurden (darum lass die Summe erstmal weg und wir schauen nachher).
Was klar ist, die Rohdaten würde ich noch in der Tabelle sortieren. Den Vorgang kannst du mit dem Makrorekorder aufzeichen und ggf. anschliessend noch optimieren.
Weiter würde ich mich mit dem Prinzip von For-Next Schleifen (oder Do-Loops) bekannt machen. Dazu verwende ich meist ein Grundgerüst in etwa wie folgt:
Sub t()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim iZeile As Long, LetzteZeile As Long
Set WS1 = Worksheets("Tabelle1")
Set WS2 = Worksheets("Tabelle2")
Application.ScreenUpdating = False
For iZeile = 2 To WS1.Range(Rows.Count, 1).End(xlUp).Row
' hier kommt der Code z.B.
If WS1.Cells(iZeile, 2) = WS1.Cells(iZeile - 1, 2) Then
' die Zelle und eine oberhalb (Modell in Spalte 2=B) ist identisch d.h. neue Werte anfügen
LetzteZeile = WS2.Range(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(LetzteZeile, 1) = "Spalte A befüllen"
Else
' sonst eine Zeile weiter Springen (darum +2 statt +1)
LetzteZeile = WS2.Range(Rows.Count, 1).End(xlUp).Row + 2
WS2.Cells(LetzteZeile, 1) = "Sprung"
End If
Next iZeile
End Sub
Viel Erfolg beim Tüfteln
Chris
Anzeige
AW: Komplexe Zusammenführung und Auswertung
30.01.2017 20:35:29
Markus
Hallo Chris,
also erstmal Danke für deine Hilfe und Unterstützung:
Thema: Sortieren, würdest du die Tabelle1 nach den Modellen oder Ländern sortieren?
Zum Sortieren verwende ich dieses Makro:
Sub SortiereSpalteAufsteigend()
Dim Sortierspalte As String
Dim Bereich As String
Bereich = "A1:C20"
Sortierspalte = "B" 'je nachdem, was für den weiteren Code besser ist
ActiveSheet.Range(Bereich).Sort _
Key1:=Range(Sortierspalte & "1"), Order1:=xlAscending, _
Header:=xlGuess, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub
Was meinst du mit "Total"?
Danke :)
Anzeige
AW: Komplexe Zusammenführung und Auswertung
31.01.2017 08:22:12
ChrisL
Hi Markus
Den Bereich als Text-String zu verwenden geht zwar, "schöner" wäre eine Range-Variable
Dim WS1 As Worksheet, WS2 As Worksheet
Dim rngBereich As Range
Set WS1 = Worksheets("Tabelle1")
Set WS2 = Worksheets("Tabelle2")
Set rngBereich = WS1.Range("A1:C10")
rngBereich.Sort....
Mit Total meine ich die Summe z.B. in Tabelle2!C2. Wenn du Zeilen von oben nach unten füllst, musst du in C2 die Summe eintragen, obwohl C3:C5 noch gar nicht gefüllt sind. Darum müsstest du die Zeile wohl merken (Variable) und wenn der letzte Datensatz je Kategorie gefüllt ist die Summe bilden.
cu
Chris
Anzeige
AW: Komplexe Zusammenführung und Auswertung
31.01.2017 16:56:28
Markus
Hi Chris,
würde das Sortieren auch folgendermaßen funktionieren? Die Ausgangstabelle wird keine feste Länge haben, weshalb ich das Makro "universell" erstellen würde (so sinnvoll oder gleich verwerfen?)
Sub Sortieren()
Dim erste_spalte As Integer, letzte_spalte As Integer
Dim sp As Integer
Dim WS1 As Worksheet, WS2 As Worksheet
Set WS1 = Worksheets("Tabelle1")
Set WS2 = Worksheets("Tabelle2")
erste_spalte = WS1.UsedRange.Column
letzte_spalte = erste_spalte + WS1.UsedRange.Columns.Count - 1
sp = 2
If sp  letzte_spalte Then
MsgBox "Unzulässige Sortierspalte"
Exit Sub
End If
ActiveSheet.UsedRange.Sort Key1:=Columns(sp), Header:=xlGuess
End Sub

Thema: Total
Ich kann mich mit meinem Anfängerwissen noch grob entsinnen, dass es hierfür eine Funktion gab, die "FormulaLocal" hieß?
Sprich wir bräuchten ein Makro á la:
.Range("C2").FormulaLocal = WorksheetFunction.Sum(C3:C + letzteZeile)
Chris, spiele da mit offenen Karten: habe da echt keine Ahnung, wie ich es umsetzen sollte.
Danke dir.
Anzeige
AW: Komplexe Zusammenführung und Auswertung
31.01.2017 18:47:40
ChrisL
Hi Markus
Ich habe es jetzt nicht getestet, aber sieht auf den ersten Blick ganz OK aus. Warum die erste Spalte nicht kleiner 2 sein darf, weiss ich nicht.
Ansonsten... ActiveSheet würde ich nicht verwenden, sondern immer das Tabellenblatt angeben. Wenn du gar keine Angabe machst (wie bei Columns), dann bezieht es sich ebenfalls auf das gerade aktive Blatt.
WS1.UsedRange.Sort Key1:=WS1.Columns(sp), Header:=xlGuess
Zum Total. Müsstest dir überlegen ob du einen fixen Wert oder eine Formel möchtest. Fixwert würde ich empfehlen wenn die Liste lang wird, weil keine Wartezeit infolge Neuberechnung. Bei wenig Daten ist es egal. Momentan hast du noch ein Gemisch aus beidem.
' Deutsche Schreibweise, funktioniert nur mit deutschem Excel
WS2.Cells(gespeicherteZeile, 3).FormulaLocal = "=SUMME(C" & gespeicherteZeile & ":C" & letzteZeile & ")"
' Englische Schreibweise, funktioniert international
WS2.Cells(gespeicherteZeile, 3).Formula = "=SUM(C" & gespeicherteZeile & ":C" & letzteZeile & ")"
' Keine Formel sondern nur Wert eintragen
WS2.Cells(gespeicherteZeile, 3) = WorksheetFunction.Sum(WS2.Range(WS2.Cells(gespeicherteZeile,3),WS2.Range(WS2.Cells(letzteZeile,3)))
cu
Chris
Anzeige
AW: Komplexe Zusammenführung und Auswertung
31.01.2017 18:59:49
Markus
Hi Chris,
also es sind max. 4 verschiedene Modelle vorhanden. Man kann also mit Fixierten arbeiten.
Wie könnte ich nun am besten:
WS2.Cells(gespeicherteZeile, 3).Formula = "=SUM(C" & gespeicherteZeile & ":C" & letzteZeile & ")"
in mein Makro einbauen. Ich muss ja die "gespeicherte Zeile" davor ja irgendwie definieren? Sind vielleicht etwas blöde Fragen, aber bin noch nicht so mit VBA vertraut.
Danke dir.
AW: Komplexe Zusammenführung und Auswertung
01.02.2017 08:34:45
ChrisL
Hi Markus
Blöde Fragen gibt es nicht ;)
An deiner Stelle würde ich erst einmal alles andere erledigen und die Summe zum Schluss. Ich denke wenn du mal die For-Next Schleife und den Übertrag von Tabelle1 auf Tabelle2 verstehst, bist du schon fast am Ziel.
cu
Chris
PS: du schreibst zwar von Fixwerten, aber führst das Codebeispiel mit der Formel an
Anzeige
AW: Komplexe Zusammenführung und Auswertung
01.02.2017 20:21:45
Markus
Hi Chris,
habe heute noch einige Makros probiert und selber herumgetüftelt, aber leider erfolglos. Auf dieser Seite habe ich ein Makro gefunden, welches so etwa in meine Richtung geht:
http://www.j-hennekes.de/1237040.htm
Aber eine Anordnung, wie ich sie gerne hätte, konnte ich auch in diversen Excel Foren nicht finden.
Falls es dir nicht soviel Arbeit macht, kannst du mir bitte das Makro zu meinem Anliegen schicken?
Danke dir.
AW: Komplexe Zusammenführung und Auswertung
02.02.2017 08:59:18
ChrisL
Hi Markus
z.B. so...
Sub tt()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim iZeile As Long, iZähler As Long
Dim LetzteZeile As Long, merkeZeile As Long
Set WS1 = Worksheets("Tabelle1")
Set WS2 = Worksheets("Tabelle2")
Application.ScreenUpdating = False
WS2.Rows("2:65536").Delete
With WS1
LetzteZeile = .Cells(Rows.Count, 1).End(xlUp).Row
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Range("B2:B" & LetzteZeile), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("A2:A" & LetzteZeile), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A1:C" & LetzteZeile)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For iZeile = 2 To LetzteZeile
If .Cells(iZeile, 2)  .Cells(iZeile - 1, 2) Then
'neuer Block
If merkeZeile  0 Then WS2.Cells(merkeZeile, 3).Formula = _
"=SUM(C" & merkeZeile + 1 & ":C" & iZähler & ")"
iZähler = iZähler + 2
merkeZeile = iZähler
WS2.Cells(iZähler, 1) = .Cells(iZeile, 2)
With WS2.Range(WS2.Cells(iZähler, 1), WS2.Cells(iZähler, 3)).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
If WS2.Cells(iZähler, 2) = .Cells(iZeile, 1) Then
' verdichten wenn Land vorhanden
WS2.Cells(iZähler, 3) = WS2.Cells(iZähler, 3) + .Cells(iZeile, 3)
Else
' neuer Datensatz in bestehendem Block
iZähler = iZähler + 1
WS2.Cells(iZähler, 2) = .Cells(iZeile, 1)
WS2.Cells(iZähler, 3) = .Cells(iZeile, 3)
End If
Next iZeile
WS2.Cells(merkeZeile, 3).Formula = "=SUM(C" & merkeZeile + 1 & ":C" & iZähler & ")"
End With
End Sub
cu
Chris
Anzeige
AW: Komplexe Zusammenführung und Auswertung
02.02.2017 18:01:11
Markus
Hi Chris,
besten Dank - funktioniert perfekt!
AW: Komplexe Zusammenführung und Auswertung
02.02.2017 18:24:48
ChrisL
Hi again
Mist, jetzt habe ich selber die Referenzierung zum Tabellenblatt vergessen:
        .SortFields.Add Key:=WS1.Range("B2:B" & LetzteZeile), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=WS1.Range("A2:A" & LetzteZeile), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SetRange WS1.Range("A1:C" & LetzteZeile)
Noch ein paar abschliessende Worte zum Code. Die Tatsache, dass identische Werte verdichtet werden sollen, ist mir erst beim Erstellen aufgefallen. Darum eine If-Abfrage zusätzlich.
Aktiviere mal Tabelle2, dann in den VBA-Editor, setze einen Haltepunkt nach dem Sortieren. Und dann schaust du es dir im Einzelschritt-Modus an (vorgängig ScreenUpdating auskommentieren, damit man die Änderungen sieht).
iZeile bezieht sich auf Tabelle1
iZähler bezieht sich auf die Zeile in Tabelle2
(sorry blöde Bezeichnungen)
Wenn...
If .Cells(iZeile, 2) .Cells(iZeile - 1, 2) Then
...dann zwei Zeilen weiter, neuer Datenblock und die Zeile wird gemerkt und die Linie gezogen.
Wenn...
If WS2.Cells(iZähler, 2) = .Cells(iZeile, 1) Then
...dann ist das Land bereits vorhanden und es wird nur dazu addiert.
Sonst...
Else
...eine Zeile weiter und ein neuer Datensatz.
Und dann noch das "doofe" Total (bin selber nicht ganz glücklich mit der Lösung) :)
Prinzipiell immer dann die Summe vom letzten Datenblock bilden, wenn ein neuer Datenblock erzeugt wird. Nur beim ersten Block gibts noch keinen letzten Block, darum die Abfrage...
If merkeZeile 0 Then
Und weil es ganz zum Schluss keinen neuen Datensatz mehr gibt, muss nach Ablauf der For-Next Schleife, noch einmalig die Summe für den letzten Block eingesetzt werden.
Die Lösung scheint mir für kleinere Datenmengen und unter Inkaufnahme gewisser holpriger Codezeilen akzeptabel und ich hoffe dass du dadurch das Vorgehen halbwegs nachvollziehen kannst.
Müsste ich das grundsätzliche Vorgehen optimieren, würde ich vermutlich mittels Array/Datenfeld an die Sache heran gehen. Array nehmen im Gegensatz zu normalen Variablen mehrere Werte resp. ganze Tabellen auf (Array können ein- oder mehrdimensional sein). Der Vorteil von Array ist, dass sie schnell sind. So könnte man beispielsweise die Daten in mehreren For-Next-Schleifen bearbeiten. Würdest du hingegen beim jetzigen Code das ominöse Total im Nachgang mittels zweiter Schleife lösen, würde sich die Laufzeit fast verdoppeln. Array sind m.E. nicht für Anfänger geeignet, darum hast einen Holper-Code bekommen :)
Dein Link (den ich übrigens sehr passend für die Problemstellung fand), enthält Beispiele mit "Dictionary", was (wie auch Collection) Ähnlichkeiten mit Array hat.
Weiterhin viel Spass mit VBA
Chris
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige