schnufflchen
Goto Top

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.

eed46e8fa28c023e764097d3147f78d8
7e16155bb6b9df4293c60aabf975dbda

Vielen Dank schon im Voraus für Eure Ideen und Hilfen!


ERGÄNZUNG zu Tabelle2:

7dd37d49aee6c8ccdd07a65f251a264b

Content-ID: 161069

Url: https://administrator.de/contentid/161069

Ausgedruckt am: 05.11.2024 um 18:11 Uhr

bastla
bastla 19.02.2011 um 11:33:40 Uhr
Goto Top
Hallo Schnufflchen!
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
schuhmann
schuhmann 19.02.2011 um 11:37:03 Uhr
Goto Top
Hast du schonmal ein Makro aufgezeichnet von deinen Aktionen?
Schnufflchen
Schnufflchen 19.02.2011 um 13:00:36 Uhr
Goto Top
Hallo bastla!

Ich habe die Tabellen etwas ausführlicher aufgenommen und aktualisiert.

Erreichen möchte ich, dass ich eine monatsweise Aufstellung habe wieviele Teile in welcher Altersgruppe mit welchen Gesamteinkaufswert verkauft werden.
Das erste Sortierkriterium ist die Anzahl Teile und soll flexibel sein. Die Altersgruppe ist festgelegt.

Der Monat wird einmalig in Zelle E2 auf der ersten Tabelle an eingetragen und bleibt für den Verlauf des Monats stehen. Am Ende des Monats sollen die Daten per Makro auf die zweite Tabelle in der gewünschten Sortierung weggeschrieben werden. Danach wird Tabelle 1 geleert, in die Zelle E2 wird der nächste Monat geschrieben. Am Ende des Monats sollen die Daten wieder in die zweite Tabelle geschrieben werden, etc. Der Monat auf Tabelle1 hat den Sinn, dass die Spalte der Zielzelle dann gefunden wird. Demnach steht in Tabelle 1 nur einmal und immer in Zelle E2 der Monatsname.

Toll wäre es, wenn es pro AnzahlTeile in der selben Altersgruppe die Einkaufswerte aufaddiert werden würden.

Ist der Sachverhalt nun etwas klarer?
Ist es überhaupt möglich so ein Makro zu schreiben?

LG Schnufflchen
bastla
bastla 19.02.2011 um 15:53:13 Uhr
Goto Top
Hallo Schnufflchen!

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
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
Schnufflchen
Schnufflchen 20.02.2011 um 17:40:50 Uhr
Goto Top
Hallo bastla,

vielen Dank für deine Lösung. Allerdings bleibt das Makro immer in der Massagebox hängen, dass der Monat nicht korrekt ist.

Weiter hab ich noch eine andere Frage: Wenn die Werte in die Zieltabelle (Tabelle2) geschrieben werden, dann wird mit dem Makro derzeit nicht abgeprüft, wo (in welcher Zeile) sich die Kombination aus AnzahlTeile und Altersgruppe befindet oder? Das sollte das Makro nämlich auch können, weil ich die Reihenfolge vorgegeben bekomme und die Werte aus Tabelle1 nicht hintereinander weg schreiben kann.Konkretes Beispiel für Übertragung in Zielzelle in Tabelle2: suche die Zeile, wo AnzahlTeile und Altersgruppe ist gleich wie bei dazugehörigem Einkaufswert aus Tabelle1. Suche danach dazu die Spalte, wo Monat ist gleich wie Monat aus Tabelle1.

Grüße
Schnufflchen
bastla
bastla 20.02.2011 um 17:49:16 Uhr
Goto Top
Hallo Schnufflechen!
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 face-wink - 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:
ABCDE
1AltersgruppeAnzahl TeileEinkaufswert
210-155122,00 Januar
310-15123,00
416-203455,00
521-2567812,00
621-25650,00

und

ABCDE
1AltersgruppeAnzahl TeileEinkaufswert
210-155222,00 Februar
310-151123,00
416-203555,00
521-2541234,00
621-254100,00

erhalte ich jedenfalls folgende Tabelle2:

ABCDE
1Anzahl TeileAltersgruppeJanuarFebruarMärz
2510-15122222
3110-1523123
4316-20455555
5621-257812
6421-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"
[/Edit]

Grüße
bastla
Schnufflchen
Schnufflchen 21.02.2011 um 09:39:17 Uhr
Goto Top
Hallo bastla,

das mit dem Monat funktioniert jetzt bei mir auch. hatte einen Schreibfehler drin *doof*.

Aber das mit dem zusortieren in tabelle 2 ist noch nicht das, was ich haben möchte.
In Tabelle 2 hab ich in meiner Ausgangstabelle schon von Anfang an in den Spalten A und B festgelegte Werte, denen die Werte aus Tabelle 1 zusortiert werden. D.h. da kann bei AnzahlTeile z.B. auch schon 8 mit ner Altersgruppe 35-40 drinstehen. In tabelle2 wird das nicht erst durch das Vorkommen in tabelle1 gefüllt. Das liegt u.a. auch daran, dass von den Werten im Anschluss noch Grafiken gezogen werde, die halt nur auf vorher definierte Datenbereiche zugreifen.
Ist das zu kompliziert, das noch zu realisieren?

LG Nadja
bastla
bastla 21.02.2011 um 09:43:32 Uhr
Goto Top
Hallo Schnufflchen!

Eigentlich sollte jetzt schon (war ja auch schon immer der Plan face-wink) 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
Schnufflchen
Schnufflchen 21.02.2011 um 11:26:35 Uhr
Goto Top
Hallo bastla,

ich hatte die Tabelle2 wahrscheinlich zu einfach dargestellt. Wenn ich das Makro so wie es jetzt ist ausführe, dann werden die Daten immer ab Zeile 2 in genau der Reihenfolge eingefügt wie Sie in Tabelle1 vorkommen. Allerdings habe ich eine vorgefertigte Zieltabelle mit hinterlegten Formeln, Leerzeilen etc. Dann überschreibt das Makro einfach ab Zeile 2. Wenn ich "ZAbZeile" anpasse, dass ab Zeile9 die Werte eingegeben werden sollen, dann wird derzeit ab Zeile 9 alles aus Tabelle1 hingeschrieben.
Weißt du wie ich das meine, ich glaube nämlich, dass wir in dem Punkt ein bisschen aneinander vorbei reden. Die Reihenfolge der Daten aus Tabelle1 soll nämlich gerade nicht beibehalten werden, sondern flexibel in Tabelle2 niedergeschrieben werden. Wenn Werte nicht übertragen werden können, weil es dafür keine AnzahlTeile und Altersgruppen-Kombination gibt, dann fallen diese Werte unter den tisch. Das ist so gewollt.

Als Ergänzung habe ich oben noch einen Screenshot von tabelle2 hinzugefügt

LG Schnufflchen
bastla
bastla 21.02.2011 um 16:27:06 Uhr
Goto Top
Hallo Schnufflchen!
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
und folgenden veränderten Teil ab der bisherigen Zeile 31:
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
Grüße
bastla
Schnufflchen
Schnufflchen 22.02.2011 um 11:17:43 Uhr
Goto Top
Hallo bastla!

HAMMER!!! Du bist echt super. Dankeschön.
Ein mini-Problem hab ich noch: Das mit dem Monat funktioniert bei mir schon wieder nicht. Ich habs jetzt schon mehrfach nachgeschaut und ich hab kein Schreibfehler drin und die Tabellen sehen bei mir ja auch so aus wie in den Screenshots. Kannst du das nochmal nachschauen.

Ansonsten bin ich voll happy!

Ganz großes Danke!

LG Schnufflchen
bastla
bastla 22.02.2011 um 11:38:24 Uhr
Goto Top
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
Schnufflchen
Schnufflchen 22.02.2011 um 13:32:30 Uhr
Goto Top
Hallo bastla,

aha, ich fettgeschrieben ist also auch nicht gleich.
Aber wenn mans weiß, dann funktionierts und das ist die Hauptsache!
Super, vielen lieben Dank. Du hast mir wirklich sehr geholfen!

Liebe Grüße
Schnufflchen
bastla
bastla 22.02.2011 um 14:06:30 Uhr
Goto Top
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:
  • 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