Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Daten per Makro zusortieren

Frage Microsoft Microsoft Office

Mitglied: Schnufflchen

Schnufflchen (Level 1) - Jetzt verbinden

19.02.2011, aktualisiert 21.02.2011, 4406 Aufrufe, 14 Kommentare

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 - Klicke auf das Bild, um es zu vergrößern
7e16155bb6b9df4293c60aabf975dbda - Klicke auf das Bild, um es zu vergrößern

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


ERGÄNZUNG zu Tabelle2:

7dd37d49aee6c8ccdd07a65f251a264b - Klicke auf das Bild, um es zu vergrößern
Mitglied: bastla
19.02.2011 um 11:33 Uhr
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
Bitte warten ..
Mitglied: schuhmann
19.02.2011 um 11:37 Uhr
Hast du schonmal ein Makro aufgezeichnet von deinen Aktionen?
Bitte warten ..
Mitglied: Schnufflchen
19.02.2011 um 13:00 Uhr
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
Bitte warten ..
Mitglied: bastla
19.02.2011 um 15:53 Uhr
Hallo Schnufflchen!

Das könnte so gehen:
01.
Sub Auswertung() 
02.
QTab = "Tabelle1"	'Quelltabelle 
03.
QAbZeile = 2		'erste Zeile mit Daten 
04.
QAG = "A"		'Spalte für "Altersgruppe" 
05.
QAT = "B"		'Spalte für "Anzahl Teile" 
06.
QEW = "C"		'Spalte für "Einkaufswert" 
07.
QMon = "E2"		'Zelle für "Monat" 
08.
 
09.
ZTab = "Tabelle2"	'Zieltabelle 
10.
ZAbZeile = 2		'erste Zeile mit Daten 
11.
ZAbSpalte = "C"		'Spalte für "Januar" 
12.
ZAG = "B"		'Spalte für "Altersgruppe" 
13.
ZAT = "A"		'Spalte für "Anzahl Teile" 
14.
 
15.
Delim = "§" 'Trennzeichen für Key - darf nicht in "AnzahlTeile"- oder "Altersgruppe"-Werten vorkommen 
16.
QZeile = QAbZeile 
17.
Set d = CreateObject("Scripting.Dictionary") '"Dictionary" erstellen 
18.
With Worksheets(QTab) 
19.
    Do While .Cells(QZeile, QAG) <> "" 'alle Datenzeilen durchgehen 
20.
        K = .Cells(QZeile, QAG) & Delim & .Cells(QZeile, QAT) 'Key erstellen 
21.
        If d.Exists(K) Then 'Key vorhanden - Einkaufswert addieren 
22.
            d.Item(K) = d.Item(K) + .Cells(QZeile, QEW) 
23.
        Else 'Key neu - Einkaufswert eintragen 
24.
            d.Add K, .Cells(QZeile, QEW) 
25.
        End If 
26.
        QZeile = QZeile + 1 
27.
    Loop 
28.
    Monat = Range(QMon) 'Monatsnamen auslesen 
29.
End With 
30.
 
31.
With Worksheets(ZTab) 
32.
    ZZeile = ZAbZeile - 1 
33.
    ZSpalte = .Cells(ZZeile, ZAbSpalte).Column 
34.
    Do While .Cells(ZZeile, ZSpalte) <> "" And .Cells(ZZeile, ZSpalte) <> Monat 'Spalte für Monat suchen 
35.
        ZSpalte = ZSpalte + 1 
36.
    Loop 
37.
    If .Cells(ZZeile, ZSpalte) = "" Then 
38.
        MsgBox "Monatsangabe """ & Monat & """ nicht korrekt!", , "Monat falsch!" 
39.
    End If 
40.
    KA = d.Keys 'Keys und ... 
41.
    IA = d.Items '... Werte in Arrays übertragen 
42.
    For i = 0 To d.Count - 1 
43.
        ZZeile = ZAbZeile 
44.
        KP = Split(KA(i), Delim) 'Key wieder aufteilen 
45.
        Do Until .Cells(ZZeile, ZAT) = "" Or (CStr(.Cells(ZZeile, ZAT)) = KP(1) And .Cells(ZZeile, ZAG) = KP(0)) 'Zeile suchen 
46.
            ZZeile = ZZeile + 1 
47.
        Loop 
48.
        .Cells(ZZeile, ZAT) = KP(1) 'Anzahl Teile eintragen 
49.
        .Cells(ZZeile, ZAG).NumberFormat = "@" 'Zelle als "Text" formatieren 
50.
        .Cells(ZZeile, ZAG) = KP(0) 'Altergruppe eintragen 
51.
        .Cells(ZZeile, ZSpalte) = IA(i) '(Summe) Einkaufswert(e) eintragen 
52.
    Next 
53.
End With 
54.
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
Bitte warten ..
Mitglied: Schnufflchen
20.02.2011 um 17:40 Uhr
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
Bitte warten ..
Mitglied: bastla
20.02.2011 um 17:49 Uhr
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 - 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
Bitte warten ..
Mitglied: Schnufflchen
21.02.2011 um 09:39 Uhr
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
Bitte warten ..
Mitglied: bastla
21.02.2011 um 09:43 Uhr
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
Bitte warten ..
Mitglied: Schnufflchen
21.02.2011 um 11:26 Uhr
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
Bitte warten ..
Mitglied: bastla
21.02.2011 um 16:27 Uhr
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:
01.
With Worksheets(ZTab) 
02.
    ZZeile = 1 
03.
    ZSpalte = .Cells(ZZeile, ZAbSpalte).Column 
04.
    Do While .Cells(ZZeile, ZSpalte) <> "" And .Cells(ZZeile, ZSpalte) <> Monat 
05.
        ZSpalte = ZSpalte + 1 
06.
    Loop 
07.
    If .Cells(ZZeile, ZSpalte) = "" Then 
08.
        MsgBox "Monatsangabe """ & Monat & """ nicht korrekt!", , "Monat falsch!" 
09.
    End If 
10.
    KA = d.Keys 
11.
    IA = d.Items 
12.
    For i = 0 To d.Count - 1 
13.
        ZZeile = ZAbZeile 
14.
        KP = Split(KA(i), Delim) 
15.
        Do Until ZZeile > ZBisZeile Or (CStr(.Cells(ZZeile, ZAT)) = KP(1) And .Cells(ZZeile, ZAG) = KP(0)) 
16.
            ZZeile = ZZeile + 1 
17.
        Loop 
18.
        If ZZeile <= ZBisZeile Then 
19.
            .Cells(ZZeile, ZSpalte) = IA(i) 
20.
            .Cells(ZZeile, ZSpalte).NumberFormat = "#,##0.00" 'kann entfallen, wenn das Format bereits vorweg festgelegt wurde 
21.
        End If 
22.
    Next 
23.
End With
Grüße
bastla
Bitte warten ..
Mitglied: Schnufflchen
22.02.2011 um 11:17 Uhr
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
Bitte warten ..
Mitglied: bastla
22.02.2011 um 11:38 Uhr
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
Bitte warten ..
Mitglied: Schnufflchen
22.02.2011 um 13:32 Uhr
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
Bitte warten ..
Mitglied: bastla
22.02.2011 um 14:06 Uhr
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
Bitte warten ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(8)

Tipp von agowa338 zum Thema Windows 10 ...

Ähnliche Inhalte
Cloud-Dienste
Amazon: Dieser Lkw transportiert Daten in die Cloud (1)

Link von Kraemer zum Thema Cloud-Dienste ...

VB for Applications
Excel VBA Sortierung von Daten (5)

Frage von easy4breezy zum Thema VB for Applications ...

VB for Applications
gelöst VBA-Makro verschwindet nach Speichern (5)

Frage von lupi1989 zum Thema VB for Applications ...

Backup
gelöst Datensicherung von Daten, auf denen die ganze Zeit gearbeitet wird (8)

Frage von Windows11 zum Thema Backup ...

Heiß diskutierte Inhalte
Microsoft
Ordner mit LW-Buchstaben versehen und benennen (21)

Frage von Xaero1982 zum Thema Microsoft ...

Windows Update
Treiberinstallation durch Windows Update läßt sich nicht verhindern (17)

Frage von liquidbase zum Thema Windows Update ...

Windows Tools
gelöst Aussendienst Datensynchronisierung (12)

Frage von lighningcrow zum Thema Windows Tools ...

Windows Server
RODC über VPN - Verbindung weg (10)

Frage von stefan2k1 zum Thema Windows Server ...