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
#11
Hallo [b]atilla![/b]

Vielen Dank für Deine Hilfe. Wogenau muss ich den Code eingeben? Habe noch nie mit VBA gearbeitet.
Ich habe mal nach einer Anleitung gesucht:



Wie fügt man ein Makro in das Codefenster der Tabelle ein?
  • während Excel aktiv ist, ist die Tastenkombination Alt + F11 zu drücken, damit öffnet sich das Fenster der Entwicklungsumgebung für VBA
  • mit Tastenkombination Strg + R den Projektexplorer öffnen und aktivieren
  • dort die gewünschte Datei per Doppelklick markieren, sie ist dann blau unterlegt
  • nun Doppelklick unterhalb dieser Datei auf das gewünschte Tabellenblatt, auf welches sich das Makro auswirken soll
  • nun ist in der rechten Fensterhälfte das Codefenster dieser Tabelle sichtbar. In diese kann der Code eingefügt werden
  • speichern und Visual Basic mit Tastenkombination Alt + F4 schließen
  • das Makro steht nun in der Datei zur Verfügung

Ist das so richtig? Bei mir klappt es nicht :s
Antworten Top
#12
(16.01.2017, 16:12)snb schrieb: Ich fürchte das es eine lange Thread werden wird.

d'accord!

@Dennis:
Mal davon abgesehen, dass Du die VBA-Lösung nicht pflegen können wirst, wenn sich mal etwas ändert:
Warum packst Du das Problem nicht bei der Wurzel und lässt erst gar keine ungültigen PLZ bei der Eingabe zu?
Dafür gibt es Daten, Datenüberprüfung.
Benenne die PLZ-Spalte mit PLZ
Eingabespalte J markieren, sie erhält die benutzerdefinierte Datenüberprüfung =ZÄHLENWENN(PLZ;J1)

Gruß Ralf
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Antworten Top
#13
Sehr cool!
Ich habe es hinbekommen :100:
Vielen, vielen Dank dafür Laola
Antworten Top
#14
Eine Frage noch:
 
Meine Tabelle hat leider nicht nur zwei Spalten. Sie geht bis FK. Wie kann ich dden Code so ändern das alle anderen Zahlen mit bearbeitet werden. Ich habe wie ganz zu
Anfang beschrieben eine Spalte A mit Postleitzahlen und anschließend immer drei zusammenhängende Spalten nach Abteilungen. Also Spalte B Barverkäufe, Spalte C
Kaufverträge und D Gesamtsummen aus Spalte B und C.

Wie müsste da der Code aussehen? :20:


Vielen Dank im Vorraus Blush
Antworten Top
#15
Was muss an dem Code geändert werden, damit die Spalten die angesprochen werden sollen geändert werden.
Ich habe alles Mögliche versucht, doch bei mir heißt es immer „Typen unverträglich“ Huh

Vielen Dank im Vorraus :21:
Antworten Top
#16
Hallo,

aber warum stellst Du hier eine Beispielmappe ein, die nicht die tatsächliche Struktur des Originals wiedergibt?  :@
Zum Verständnis und Weiterführung meiner Arbeit bitte erneut eine Beispielmappe einstellen.

Ich kann aufgrund Deiner Beschreibungen nicht sagen, wie und wo der Code geändert werden muss, und wie aufwändig es sein wird.
Gruß Atilla
Antworten Top
#17
Hallo

anbei eine Beispieldatei von mir.  Sie mag auf den ersten Blick etwas umstaendlich sein, denn ich bin meinen eigenen Lösungsweg gegangen.  
Mein Code besteht aus mehreren Makros und 3 Buttons.  Zur Grundidee von mir:

ich kopiere mir zuerst alle PLZ in "Tabelle überarbeitet" Spalte F, und prüfe welche fehlerhaft sind. Die werden seitlich aufgelistet, und in der Auflistung gelöscht.  Dann addiere ich zuerst einmal die Summen der gültigen PLZ. Mein Programm sucht auch in der PLZ Liste ob es für die fehlerhaften eine gültige PLZ findet. Die wird als "mögliche PLZ" aufgelistet. Findet mein Code nur eine gültige, kann man die in der Spalte "Ok" mit ok als gültig bestaetigen, und mit dem Button "mögliche Add" dazuaddieren.  Nur der verbleinde Rest wird dann Kumuliert. D.h., durch die Anzahl der gültgen PLZ geteilt und dazuaddiert. Mag vieleicht umstaendlicher sein, aber man sieht sofort welche gültig sind, und welche falsch!
 
@Attila   Sorry Attila, erst mal ein Lob an dich und deinen Progammierstil.  Vom Wissen und dem modernen Excel hast du wesentlich mehr drauf wie ich!!  Da komme ich als alter Praktiker nicht mehr mit!  z.B. was Scipting bedeutet ist mir schleierhaft.  Ich habe dein Makro aber aus Neugiereinmal durchlaufen lassen und war verdutzt??  Da fehlen m.E. eine Menge PLZ, die ich als gültig akzeptiert habe.  Ich kann dein Programm aber nicht durchblicken um zu erkennen was da anders ist, und warum du ein anderes Ergebnis hast.  Dein Code ist mir einfach zu hoch.  Prüfe ihn bitte selbst, wenn du willst.

Zitat:@snb   Ich fürchte das es eine lange Thread werden wird.    Stimmt, solange, bis er durch eine gute Lösung beendet wird!

Würde mich sehr freuen wenn wir jetzt der Lösung naeher kommen. Wenn nicht, habe ich es wenigstens versucht ...
mfg  Gast 123


Angehängte Dateien
.xlsm   Excel_PLZ F.xlsm (Größe: 599,57 KB / Downloads: 3)
Antworten Top
#18
Hallo Gast,

natürlich prüfe ich bei einer solchen Aufgabe das Ergebnis.
Für mich sind alle werte aus Spalte A keine PLZ, wenn sie nicht in der Liste aller PLZ zu finden sind.
Wenn das anders ist, dann soll der TE es mitteilen.

Die Prüfung erfolgt mit einer einfache Funktion:
=ZÄHLENWENN('PLZ DE komplett'!$A$2:$A$14957;A3)

Bis auf die Verteilung ist die Aufgabe mittels einer Pivotttabelle in zwei Minuten erledigt.
Die Verteilung ist danach manuell mit Copy Paste und eine Formel auch schnell getan.

Arbeitsblatt mit dem Namen 'Tabelle1'
 HIJK
2  Daten 
3istPLZSumme von Umsatz €Anzahl von ist
4FALSCH26001100001
5 26009100011
6 26019200222
7 26021200242
8 26027200262
9 26033200282
10 26103100081
11 26105100101
12 26108100121
13 26109100131
14 26112100141
15 26113100161
16 26115100171
17 226123100231
18 261036100091
19 261125100151
20 261258100251
21 261316100291
22 26016+100021
23 2a037100071
24 BERLIN100181
25 XXX100111
26FALSCH Ergebnis 26033026
27WAHR26125100241
28 26127100261
29 26129100271
30 26131100281
31WAHR Ergebnis 401054
32Gesamtergebnis 30043530
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Und diesen Weg gehe ich bei solchen Aufgaben meistens um meine Ergebnisse zu prüfen.
Filter der Tabelle in Kombination mit Formeln können auch sehr Hilfreich sein

Und noch eine allgemeine Bemerkung:
Warum macht ihr euch so viele Gedanken darüber, ob ein Thread lang werden wird.
Solange es weiterführende Einlassungen sind, spielt die Länge doch gar keine Rolle.


@dennis1793
Verstehe ich Dich richtig, dass zur Berechnung der Summen die Werte aus Spalte D genutzt werden sollen?

Wenn ja, dann ist die Änderung sehr einfach.
Schau und vergleiche, dann siehst Du wo ich etwas geändert habe:

Code:
Option Explicit

Sub aufsummieren_und_verteilen()
Dim i As Long, lngZSumme As Long, lngZPlz As Long
Dim dblS As Double
Dim strgBereich As String
Dim dKey As String
Dim D As Object

Set D = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
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("G3:G" & lngZSumme).FormulaLocal = "=Zählenwenn(" & strgBereich & ";A3)" 'Hilfsspalte G
 
 For i = 3 To lngZSumme
   If .Cells(i, 7) > 0 Then 'Hilfsspalte G (7) wird geprüft
     dKey = .Cells(i, 1)
     D(dKey) = D(dKey) + .Cells(i, 4) 'Summen aus Spalte D (4)
   Else
     dblS = dblS + .Cells(i, 4)  'Summen aus Spalte D (4)
   End If
 Next i
 
 .Range("G3:G" & lngZSumme).ClearContents  'inhalt Hilfsspalte G wird wieder gelöscht
End With

'Ausgabe
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
Application.ScreenUpdating = True
Set D = Nothing
End Sub


Wo man Anpassungen machen musste, erkennst Du an den Kommentaren hinter einzelnen Codezeilen.
Gruß Atilla
Antworten Top
#19
Hallo Atilla,

eine schöne Prüftabelle, und du bist sehr aufmerksam.  Ich war wohl zu voreilig und habe nicht daran gedacht auch die 5 stelligen PLZ auf Richtigkeit in der PLZ Tabelle zu prüfen.  Ich entschuldige mich bei dir, dein Programm ist eindeutig besser. Weiterhin frohes schaffen ... 

mfg  Gast 123
Antworten Top
#20
Lightbulb 
Guten Morgen! Man seid ihr früh wach  :31:

Okay das stimmt, das tut mir leid. Ich wollte Euch mit meiner Tabelle nicht erschlagen  :72:

Die Funktion sollte die selbe sein, so wie in deiner Formatierung. Das heißt die PLZ aus Spalte A filtern und die Umsätze dann aus allen anderen Zellen bearbeiten. Also soll die Formatierung
einfach nur erweitert werden. Da ich mich mit VBA überhaupt nicht auskenne, bin ich davon ausgegangen, dass ich den Code einfach auf die anderen Spalten kopieren kann. Dass es nicht so
einfach ist, hätte ich nicht gedacht Undecided

Kurz zur Info:
Zeile 1: Geschäftsjahr
Zeile 2: Warengruppen
Zeile 3: Verkauf
Zeile 4: Verkaufsart (Barverkauf, Kommission und Gesamtumsatz der Warengruppe)

Die Tabelle beinhaltet Umsätze ab dem Jahr 2014 (B-AQ). 2015 (AR-CG), 2016 (CH-DV) und 2017 (DW-FK). Das Jahr 2016 und 2017 beinhaltet keine Barverkäufe für die Warengruppe
„02 Anbauwände“ (CO und ED) daher ist der Hintergrund zur Info markiert.

Ich habe den ersten Code benutzt und ein wenig verändert. Zum Beispiel die Messagebox beim Öffnen der Datei, damit jeder der mit dieser Tabelle arbeitet kurz eingewiesen wird. Des
Weiteren habe ich die Überschriften für das neue Tabellenblatt schon einmal vorbereitet (siehe im Code).
Die Daten wie im ersten Tabellenblatt zu sehen, können im Vorfeld nicht bearbeitet werden, da sie aus einem Programm entnommen werden. Also soll Excel ab A5 die Zahlen entnehmen
und bearbeiten, dabei die Überschriften weiter nicht beachten.

Ich hoffe ich habe mich klar genug ausgedrückt, wie die Formatierung aussehen soll.
 
Vielen, vielen Dank schon mal im Voraus! :100:


Angehängte Dateien
.xlsm   Excel_PLZ - Kopie.xlsm (Größe: 1,88 MB / Downloads: 5)
Antworten Top


Gehe zu:


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