Daten per Makro zusortieren
Hallo,
ich bin auf der Suche nach einer Makro Lösung für ein Zuordnungsproblem. Es kann leider nicht einfach in Excel gemacht werden (dann könnte ich es selbst), sondern muss aus Zeitgründen per Makro funktionieren, damit es flexibel und zeitsparend bleibt.
Abhängig von den Kriterien Altersgruppe und AnzahlTeile möchte ich den Einkaufswert eine 2. Tabelle übertragen, die wie angehängt aufgebaut ist.
Den Monat kann man übrigens in der ersten Tabelle noch in einem Feld angeben.
Der Algorithmus soll demnach wie folgt ablaufen:
1.) Wähle den erste Einkaufswert aus und merke dir die dazugehörige Altersgruppe, AnzahlTeile und den Monat (steht in Zelle E2 als Freitextfeld).
2.) kopiere den Einkaufswert
3.) Wechsle in die 2. Tabelle
4.) Suche nach der Zelle, die der Kombination aus 1.) entspricht.
5.) Füge den Wert ein
6.) Wechsle auf die 1. Tabelle und nimm den nächsten Einkaufswert
Ich hoffe, ich habe das Problem klar geschildert und mir kann jemand mit einem Code weiterhelfen, da mein Wissen leider nur für kleine Veränderungen an einem bestehenden Code reicht.
Vielen Dank schon im Voraus für Eure Ideen und Hilfen!
ERGÄNZUNG zu Tabelle2:
ich bin auf der Suche nach einer Makro Lösung für ein Zuordnungsproblem. Es kann leider nicht einfach in Excel gemacht werden (dann könnte ich es selbst), sondern muss aus Zeitgründen per Makro funktionieren, damit es flexibel und zeitsparend bleibt.
Abhängig von den Kriterien Altersgruppe und AnzahlTeile möchte ich den Einkaufswert eine 2. Tabelle übertragen, die wie angehängt aufgebaut ist.
Den Monat kann man übrigens in der ersten Tabelle noch in einem Feld angeben.
Der Algorithmus soll demnach wie folgt ablaufen:
1.) Wähle den erste Einkaufswert aus und merke dir die dazugehörige Altersgruppe, AnzahlTeile und den Monat (steht in Zelle E2 als Freitextfeld).
2.) kopiere den Einkaufswert
3.) Wechsle in die 2. Tabelle
4.) Suche nach der Zelle, die der Kombination aus 1.) entspricht.
5.) Füge den Wert ein
6.) Wechsle auf die 1. Tabelle und nimm den nächsten Einkaufswert
Ich hoffe, ich habe das Problem klar geschildert und mir kann jemand mit einem Code weiterhelfen, da mein Wissen leider nur für kleine Veränderungen an einem bestehenden Code reicht.
Vielen Dank schon im Voraus für Eure Ideen und Hilfen!
ERGÄNZUNG zu Tabelle2:
Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 161069
Url: https://administrator.de/contentid/161069
Ausgedruckt am: 23.11.2024 um 02:11 Uhr
14 Kommentare
Neuester Kommentar
Hallo Schnufflchen!
Wie stellst Du Dir das konkret hinsichtlich "AnzahlTeile" und "Altersgruppe" vor? Außerdem: Kann es vorkommen, dass es mehrere Einträge für den gleichen Zeitraum gibt (wenn etwa in der nicht abgebildeten Zellen E2 und E3 "Februar" stünde)? Falls ja, müssten dann doch vermutlich Summen gebildet werden ...
Grüße
bastla
Ich hoffe, ich habe das Problem klar geschildert
Klarer würde es, wenn die Screenshots vollständig wären (Tabelle1 inkl Angabe des Monats, Tabelle2 mit der Einordnung, so, wie Du sie konkret für Dein Beispiel erreichen möchtest) ...Wie stellst Du Dir das konkret hinsichtlich "AnzahlTeile" und "Altersgruppe" vor? Außerdem: Kann es vorkommen, dass es mehrere Einträge für den gleichen Zeitraum gibt (wenn etwa in der nicht abgebildeten Zellen E2 und E3 "Februar" stünde)? Falls ja, müssten dann doch vermutlich Summen gebildet werden ...
Grüße
bastla
Hallo Schnufflchen!
Das könnte so gehen:
Die Daten eines Monats werden immer überschrieben - falls allerdings ein Eintrag in einer falschen Zeile existieren sollte, wird dieser nicht gelöscht.
Grüße
bastla
Das könnte so gehen:
Sub Auswertung()
QTab = "Tabelle1" 'Quelltabelle
QAbZeile = 2 'erste Zeile mit Daten
QAG = "A" 'Spalte für "Altersgruppe"
QAT = "B" 'Spalte für "Anzahl Teile"
QEW = "C" 'Spalte für "Einkaufswert"
QMon = "E2" 'Zelle für "Monat"
ZTab = "Tabelle2" 'Zieltabelle
ZAbZeile = 2 'erste Zeile mit Daten
ZAbSpalte = "C" 'Spalte für "Januar"
ZAG = "B" 'Spalte für "Altersgruppe"
ZAT = "A" 'Spalte für "Anzahl Teile"
Delim = "§" 'Trennzeichen für Key - darf nicht in "AnzahlTeile"- oder "Altersgruppe"-Werten vorkommen
QZeile = QAbZeile
Set d = CreateObject("Scripting.Dictionary") '"Dictionary" erstellen
With Worksheets(QTab)
Do While .Cells(QZeile, QAG) <> "" 'alle Datenzeilen durchgehen
K = .Cells(QZeile, QAG) & Delim & .Cells(QZeile, QAT) 'Key erstellen
If d.Exists(K) Then 'Key vorhanden - Einkaufswert addieren
d.Item(K) = d.Item(K) + .Cells(QZeile, QEW)
Else 'Key neu - Einkaufswert eintragen
d.Add K, .Cells(QZeile, QEW)
End If
QZeile = QZeile + 1
Loop
Monat = Range(QMon) 'Monatsnamen auslesen
End With
With Worksheets(ZTab)
ZZeile = ZAbZeile - 1
ZSpalte = .Cells(ZZeile, ZAbSpalte).Column
Do While .Cells(ZZeile, ZSpalte) <> "" And .Cells(ZZeile, ZSpalte) <> Monat 'Spalte für Monat suchen
ZSpalte = ZSpalte + 1
Loop
If .Cells(ZZeile, ZSpalte) = "" Then
MsgBox "Monatsangabe """ & Monat & """ nicht korrekt!", , "Monat falsch!"
End If
KA = d.Keys 'Keys und ...
IA = d.Items '... Werte in Arrays übertragen
For i = 0 To d.Count - 1
ZZeile = ZAbZeile
KP = Split(KA(i), Delim) 'Key wieder aufteilen
Do Until .Cells(ZZeile, ZAT) = "" Or (CStr(.Cells(ZZeile, ZAT)) = KP(1) And .Cells(ZZeile, ZAG) = KP(0)) 'Zeile suchen
ZZeile = ZZeile + 1
Loop
.Cells(ZZeile, ZAT) = KP(1) 'Anzahl Teile eintragen
.Cells(ZZeile, ZAG).NumberFormat = "@" 'Zelle als "Text" formatieren
.Cells(ZZeile, ZAG) = KP(0) 'Altergruppe eintragen
.Cells(ZZeile, ZSpalte) = IA(i) '(Summe) Einkaufswert(e) eintragen
Next
End With
End Sub
Grüße
bastla
Hallo Schnufflechen!
Bei meinem Test mit den beiden (aufeinander folgenden) Inhalten der Tabelle1:
und
erhalte ich jedenfalls folgende Tabelle2:
[Edit] Dabei fällt mir auf: Das Zahlenformat für die Einkaufswerte in der Zieltabelle sollte auch noch angepasst werden. Dazu ist vor Zeile 52 einzufügen:
[/Edit]
Grüße
bastla
Allerdings bleibt das Makro immer in der Massagebox hängen, dass der Monat nicht korrekt ist.
Der Grund dafür dürfte sein, dass der Monat nicht korrekt ist - dass, um das zu konkretisieren, der Wert aus der in der Variablen "QMon" festgelegten Zelle nicht in den Zellen ab Spalte "ZAbSpalte" der Zeile "ZAbZeile"-1 (die Angabe "ZAbZeile" bezieht sich auf die Daten, nicht die Überschrift) in der exakt gleichen Schreibweise zu finden ist - ich habe vorausgesetzt, dass die Monatsnamen (wie in Deiner Vorlage) bereits in die Zieltabelle eingegeben wurden.dann wird mit dem Makro derzeit nicht abgeprüft, wo (in welcher Zeile) sich die Kombination aus AnzahlTeile und Altersgruppe befindet oder?
Was bringt Dich zu dieser Annahme?Bei meinem Test mit den beiden (aufeinander folgenden) Inhalten der Tabelle1:
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Altersgruppe | Anzahl Teile | Einkaufswert | ||
2 | 10-15 | 5 | 122,00 | Januar | |
3 | 10-15 | 1 | 23,00 | ||
4 | 16-20 | 3 | 455,00 | ||
5 | 21-25 | 6 | 7812,00 | ||
6 | 21-25 | 6 | 50,00 |
und
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Altersgruppe | Anzahl Teile | Einkaufswert | ||
2 | 10-15 | 5 | 222,00 | Februar | |
3 | 10-15 | 1 | 123,00 | ||
4 | 16-20 | 3 | 555,00 | ||
5 | 21-25 | 4 | 1234,00 | ||
6 | 21-25 | 4 | 100,00 |
erhalte ich jedenfalls folgende Tabelle2:
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Anzahl Teile | Altersgruppe | Januar | Februar | März |
2 | 5 | 10-15 | 122 | 222 | |
3 | 1 | 10-15 | 23 | 123 | |
4 | 3 | 16-20 | 455 | 555 | |
5 | 6 | 21-25 | 7812 | ||
6 | 4 | 21-25 | 1334 |
[Edit] Dabei fällt mir auf: Das Zahlenformat für die Einkaufswerte in der Zieltabelle sollte auch noch angepasst werden. Dazu ist vor Zeile 52 einzufügen:
.Cells(ZZeile, ZSpalte).NumberFormat = "#,##0.00"
Grüße
bastla
Hallo Schnufflchen!
Eigentlich sollte jetzt schon (war ja auch schon immer der Plan ) die richtige Zeile gefunden werden - wenn es also die Kombination "8" in Spalte A und "36-40" in Spalte B bereits gibt, müssten die Monatswerte auch unter den ebenfalls bereits vorgegebenen Monatsnamen eingetragen werden (wobei dort schon vorhandene Werte überschrieben werden) ...
Grüße
bastla
Eigentlich sollte jetzt schon (war ja auch schon immer der Plan ) die richtige Zeile gefunden werden - wenn es also die Kombination "8" in Spalte A und "36-40" in Spalte B bereits gibt, müssten die Monatswerte auch unter den ebenfalls bereits vorgegebenen Monatsnamen eingetragen werden (wobei dort schon vorhandene Werte überschrieben werden) ...
Grüße
bastla
Hallo Schnufflchen!
Na gut - dann brauchen wir noch für "ZAbZeile" den Wert 9 und (nach Zeile 10 eingefügt)
und folgenden veränderten Teil ab der bisherigen Zeile 31:
Grüße
bastla
ich hatte die Tabelle2 wahrscheinlich zu einfach dargestellt.
So kann frau das auch formulieren ...Na gut - dann brauchen wir noch für "ZAbZeile" den Wert 9 und (nach Zeile 10 eingefügt)
ZBisZeile = 22 'bis zu dieser Zeile werden Übereinstimmungen gesucht
With Worksheets(ZTab)
ZZeile = 1
ZSpalte = .Cells(ZZeile, ZAbSpalte).Column
Do While .Cells(ZZeile, ZSpalte) <> "" And .Cells(ZZeile, ZSpalte) <> Monat
ZSpalte = ZSpalte + 1
Loop
If .Cells(ZZeile, ZSpalte) = "" Then
MsgBox "Monatsangabe """ & Monat & """ nicht korrekt!", , "Monat falsch!"
End If
KA = d.Keys
IA = d.Items
For i = 0 To d.Count - 1
ZZeile = ZAbZeile
KP = Split(KA(i), Delim)
Do Until ZZeile > ZBisZeile Or (CStr(.Cells(ZZeile, ZAT)) = KP(1) And .Cells(ZZeile, ZAG) = KP(0))
ZZeile = ZZeile + 1
Loop
If ZZeile <= ZBisZeile Then
.Cells(ZZeile, ZSpalte) = IA(i)
.Cells(ZZeile, ZSpalte).NumberFormat = "#,##0.00" 'kann entfallen, wenn das Format bereits vorweg festgelegt wurde
End If
Next
End With
bastla
Hallo Schnufflchen!
Die entsprechende Monatsspalte wird gesucht, indem in Zeile 1 (siehe Zeile 2 der letzten Code-Anpassung) der Tabelle2, beginnend mit der in "ZAbSpalte" angegebenen Spalte, Zelle für Zelle mit dem aus der Tabelle1 ausgelesenen Monatsnamen verglichen wird, wobei sich zB Unterschiede in Groß-/Kleinschreibung oder ev zusätzliche Leerzeichen auswirken.
Wenn Du die Zelle mit dem gewünschten Monatsnamen aus der Tabelle2 nach E2 der Tabelle1 kopierst, sollte die richtige Spalte gefunden werden ...
Grüße
bastla
Die entsprechende Monatsspalte wird gesucht, indem in Zeile 1 (siehe Zeile 2 der letzten Code-Anpassung) der Tabelle2, beginnend mit der in "ZAbSpalte" angegebenen Spalte, Zelle für Zelle mit dem aus der Tabelle1 ausgelesenen Monatsnamen verglichen wird, wobei sich zB Unterschiede in Groß-/Kleinschreibung oder ev zusätzliche Leerzeichen auswirken.
Wenn Du die Zelle mit dem gewünschten Monatsnamen aus der Tabelle2 nach E2 der Tabelle1 kopierst, sollte die richtige Spalte gefunden werden ...
Grüße
bastla
Hallo Schnufflchen!
Was auch immer der Grund war - an der Formatierung (mit fetter Schrift) lag es sicher nicht, da nur die Zellwerte verglichen werden ...
Eine relativ einfache Möglichkeit, dem Problem beizukommen, ist die Verwendung einer "Gültigkeitsprüfung" für die Zelle E2:
Grüße
bastla
Was auch immer der Grund war - an der Formatierung (mit fetter Schrift) lag es sicher nicht, da nur die Zellwerte verglichen werden ...
Eine relativ einfache Möglichkeit, dem Problem beizukommen, ist die Verwendung einer "Gültigkeitsprüfung" für die Zelle E2:
- Lege zunächst einen Bereichsnamen "Monate" für die Monatsnamen in Tabelle2 (C1:N1) fest.
- Wähle dann unter "Daten / Datenüberprüfung / Einstellungen" für "Zulassen:" die Option "Liste" und gib für "Quelle:" die Formel "
=Monate
" an - et voilà ...
Grüße
bastla