Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Postleitzahlen zusammenfügen und ungültige herausfiltern
#1
Hallo zusammen! 15
Mir liegt eine Excel-Liste mit Umsätzen nach Postleitzahlen vor. Nun habe ich folgendes Problem: Manche Postleitzahlen sind doppelt; sind unvollständig oder fehlerhaft.
Mein Ziel ist es ein Tabellen zu erstellen als Eingabe, also hier sollen meine vorliegenden Daten hereinkopiert werden. Auf dem Zweiten Tabellenblatt sollen die Daten
überarbeitet zur Verfügung stehen, sodass ich diese wieder entnehmen kann und damit weiterarbeiten kann.

Folgende Formatierungen benötige ich jetzt:
1. Doppelt vergebenen Postleitzahlen inklusive Umsatz zusammenfügen.
2. Ungültige Postleitzahlen herausfiltern und den Umsatz auf alle von mir vergebenen Postleizahlen verteilen.

So sieht die Tabelle ungefähr aus:
A               B
PLZ          Umsatz
26340+    3455
26000     3461
XXX         3405
263166   3405
26384     3466
26a86     3465
26388    145

Ich hoffe ihr könnt mir helfen, ich habe schon so gut wie alles Mögliche ausprobiert 20
Antwortento top
#2
Hallo,


die zweite Frage zuerst:

das Aufsummieren mehrerer Umsätze derselben PLZ geht am einfachsten mit einer Pivot-Tabelle.

Für das Umgruppieren der ungültigen PLZ müssten mathematische Regeln definiert werden.

mfg
Antwortento top
#3
(16.01.2017, 15:42)Fennek schrieb: Hallo,


die zweite Frage zuerst:

das Aufsummieren mehrerer Umsätze derselben PLZ geht am einfachsten mit einer Pivot-Tabelle.

Für das Umgruppieren der ungültigen PLZ müssten mathematische Regeln definiert werden.

mfg

Vielen Dank schonmal für die Hilfe! Ich kenne mich damit leider überhaput nicht aus und wie müsste ein Pivot-Tabelle aussehen? 20
Antwortento top
#4
Hallo,

hier dürfte VBA Einsatz vonnöten sein.

Wie definierst Du falsche PLZ.
Hast Du eine Liste mit gütigen PLZ, mit der man die PLZ in Deiner Liste vergleichen kann.

Über die Verteilung und wie es genau ablaufen soll, solltest Du Dich auch etwas ausführlicher auslassen.
Gruß Atilla
Antwortento top
#5
(16.01.2017, 15:57)atilla schrieb: Hallo,

hier dürfte VBA Einsatz vonnöten sein.

Wie definierst Du falsche PLZ.
Hast Du eine Liste mit gütigen PLZ, mit der man die PLZ in Deiner Liste vergleichen kann.

Über die Verteilung und wie es genau ablaufen soll, solltest Du Dich auch etwas ausführlicher auslassen.


Hallo atilla,
vielen Dank für Deine AntwortSmile

Mit einer falschen PLZ meine ich z.B. 6-stellige oder wenn Buchstaben oder sogar ganz aus Wörtern bestehen.

Aufteilung:
Kommt die PLZ in meiner Liste des öfter als einmal vor, soll EXCEL diese in einer Zeile zusammenführen, insbesondere die Spalte mit den Umsätzen.
Bei undefinierbaren PLZ, also PLZ die es nicht gibt oder die fehlerhaft sind, sollen auf alle anderen richtigen PLZ verteilt werden.
 
Vielen Dank für deine Hilfe 35 35
Antwortento top
#6
Bitte nicht zitieren (quoten).

Ich fürchte das es eine lange Thread werden wird.
Antwortento top
#7
Hallo,

dann stell bitte eine Beispielmappe ein an der man sich austoben kann.

Und eine Frage noch: Es kann nicht sein, dass z.B 1,23 € auf 2543 PLZ verteilt werden muss? Was ich damit sagen will, nach der Verteilung können die Summen nicht mehr die gleichen sein, wie zu Beginn.
Gruß Atilla
Antwortento top
#8
Das ist richtig, die Summen sollen komplett verteilt werden, wenn diese nicht den PLZ zugeordnet werden können.

Ich habe ein Tabellenblatt mit den gesamten Postleitzahlen aus Deutschland für die Überprüfung beigefügt.


Angehängte Dateien
.xls   Excel_PLZ.xls (Größe: 1,71 MB / Downloads: 6)
Antwortento top
#9
Hallo,

unten stehenden Code in ein allgemeines Modul eingeben und starten:


Code:
Option Explicit

Sub aufsummieren_und_verteilen()
Dim lngZSumme As Long, lngZPlz As Long
Dim strgBereich As String
Dim dblS As Double
Dim dKey As String
Dim D As Object
Dim i As Long
Set D = CreateObject("Scripting.Dictionary")

With Sheets("PLZ DE komplett")
 lngZPlz = .Cells(Rows.Count, 1).End(xlUp).Row
 strgBereich = "'PLZ DE komplett'!" & .Range("A2:A" & lngZPlz).Address
End With

With Sheets("Tabelle1")
   lngZSumme = .Cells(Rows.Count, 1).End(xlUp).Row
   .Range("D3:D" & lngZSumme).FormulaLocal = "=Zählenwenn(" & strgBereich & ";A3)"

 For i = 3 To lngZSumme
   If .Cells(i, 4) > 0 Then
     dKey = .Cells(i, 1)
     D(dKey) = D(dKey) + .Cells(i, 2)
   Else
     dblS = dblS + .Cells(i, 2)
   End If
 Next i
 .Range("D3:D" & lngZSumme).ClearContents
End With

With Sheets("Tabelle überarbeitet")
 .Cells.ClearContents
 .Range("A1:C1") = Array("PLZ", "Summe Umsatz", "Kummilierte Summe")  'Hier in der Zeile fehlte ganz am Anfang der Zeile ein Punkt (nachgetragen 17:34)
 .Range("A2:A" & D.Count + 1) = Application.Transpose(D.keys)
 .Range("B2:B" & D.Count + 1) = Application.Transpose(D.items)
 For i = 1 To D.Count
   .Cells(i + 1, 3).Value = .Cells(i + 1, 2).Value + Application.WorksheetFunction.Round(dblS / D.Count, 2)
 Next i
End With

End Sub


Die Verteilung der Umsätze wird auf zwei Stellen gerundet berechnet.
Gruß Atilla
Antwortento top
#10
Hallo noch einmal,

oder ohne die Schleife am Ende so:


Code:
Option Explicit

Sub aufsummieren_und_verteilen()
Dim lngZSumme As Long, lngZPlz As Long
Dim strgBereich As String
Dim dblS As Double
Dim dKey As String
Dim D As Object
Dim i As Long
Set D = CreateObject("Scripting.Dictionary")

With Sheets("PLZ DE komplett")
 lngZPlz = .Cells(Rows.Count, 1).End(xlUp).Row
 strgBereich = "'PLZ DE komplett'!" & .Range("A2:A" & lngZPlz).Address
End With

With Sheets("Tabelle1")
   lngZSumme = .Cells(Rows.Count, 1).End(xlUp).Row
   .Range("D3:D" & lngZSumme).FormulaLocal = "=Zählenwenn(" & strgBereich & ";A3)"

 For i = 3 To lngZSumme
   If .Cells(i, 4) > 0 Then
     dKey = .Cells(i, 1)
     D(dKey) = D(dKey) + .Cells(i, 2)
   Else
     dblS = dblS + .Cells(i, 2)
   End If
 Next i
 .Range("D3:D" & lngZSumme).ClearContents
End With

With Sheets("Tabelle überarbeitet")
 .Cells.ClearContents
 .Range("A1:C1") = Array("PLZ", "Summe Umsatz", "Kummilierte Summe")
 .Range("A2:A" & D.Count + 1) = Application.Transpose(D.keys)
 .Range("B2:B" & D.Count + 1) = Application.Transpose(D.items)
 .Range("C2:C" & D.Count + 1).FormulaLocal = "=B2+Runden(" & dblS / D.Count & ";2)"
 .Range("C2:C" & D.Count + 1).Value = .Range("C2:C" & D.Count + 1).Value
End With

End Sub
Gruß Atilla
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste