Excel - Jak rozdzielić adresy na kod pocztowy, miasto, ulicę?

Witam,

Może mi ktoś pomoże.
Muszę rozdzielić dane na kod pocztowy, miasto oraz ulicę i dom.
Kod pocztowy wiem jak zrobić, z miastem już nie zawsze, bo miasto może mieć dwa wyrazy np. Lubań Śląski lub jedno np. Częstochowa.

88-430 JANOWIEC WIELKOPOLSKI POZNAŃSKA 1
73-102 STARGARD WITKOWO PIERW PAPIEŻA JANA PAWŁA II 34
86-170 NOWE K ŚWIECIA OS.NADARCIAŃSKIE 11C LOK.2

A ile masz tych miejscowości? Może warto zrobić ich słownik, a potem wiedząc co jest miejscowością będzie łatwiej.

A w jakiej formie (graficznej), formacie, w jakim pliku ? to wszyscy mają zgadywać ? -

Generalnie tego typu zadania spokojnie można rozwiązać przy pomocy wyrażeń regularnych - ale jak ? to bez przykładowego pliku nawet trudno proponować

Wystąp z roszczeniem do dostawcy tych danych, niech wstawi np średniki. Chyba że to ty sam.

Dziadowsko zaprojektowane dane i rozwiązywanie zagadnień w środowisku źle nadającym się do programowania

kinggustav napisał:

A ile masz tych miejscowości? Może warto zrobić ich słownik, a potem wiedząc co jest miejscowością będzie łatwiej.

Rekordów jest około 3 mln. Myślałem, żeby wykorzystać szukanie pionowe po kodzie pocztowym i w ten sposób wyciągnąć miejscowość, ale w pod jeden kod pocztowy może być wiele miejscowości.

Dodano po 2 [minuty]:

Prot napisał:

A w jakiej formie (graficznej), formacie, w jakim pliku ? to wszyscy mają zgadywać ? -

Generalnie tego typu zadania spokojnie można rozwiązać przy pomocy wyrażeń regularnych - ale jak ? to bez przykładowego pliku nawet trudno proponować

Zapisane to jest w excelu, każdy ciąg w pojedynczym wierszu, wszystko w tej samej kolumnie.
Przez ciąg rozumiem kod pocztowy, miejscowość, ulicę i numer, jak w przykładzie:
88-430 JANOWIEC WIELKOPOLSKI POZNAŃSKA 1
73-102 STARGARD WITKOWO PIERW PAPIEŻA JANA PAWŁA II 34
86-170 NOWE K ŚWIECIA OS.NADARCIAŃSKIE 11C LOK.2

Jeżeli interesuje Cię zastosowanie wyrażeń regularnych w Excelu to proponuję zapoznać się z wątkiem https://www.elektroda.pl/rtvforum/topic3396113.html

Jest zawsze problem z utworzeniem kodu wzorca ale przy większej ilości danych z reguły można znaleźć jakieś prawidłowości i ująć to wzorcem RegEx.

Tu nie widać prawidłowości więc RegExpy na nic. Jak dla mnie to pole do ręcznej roboty. Jeśli to dane z jakiejś kartoteki i nazwa danej miejscowości jest w każdym wystąpieniu taka sama to jest szansa na zrobienie tego w jakimś realnym czasie. Oddzielenie ulicy i numeru domu jest nierealne.
Tak jak już powiedziano: najpierw podzielić wiersz na miejscowość z kodem i resztę, potem wydzielić kod z miejscowości.
1.Pozbyć się nadmiarowych spacji.
2.Całość posortować.
3.Dla pierwszego wiersza danej miejscowości podzielić wiersz na spacji po nazwie miejscowości. Ja bym zrobił odpowiednie formuły które dzieliły by wiersz na podstawie podanego numeru spacji.
4. Wypełnić w dół do ostatniego wiersza danej miejscowości
5. Powtarzać operacje 3 i 4 dla każdej miejscowości.
6. Formuły zamienić na tekst
7. Kolumnę z kodem i miejscowością podzielić na pierwszej spacji od lewej.
8. Koniec

Naprawdę masz 3 mln. wierszy w Excelu? Jaka to wersja? Nie wiedziałem że już taką zrobili. A powielonych nazw tam nie ma, bo to dużo miejscowości?

ex-or napisał:

Oddzielenie ulicy i numeru domu jest nierealne.

To jest proste jak konstrukcja cepa

Wystarczy wykorzystać darmową bazę kodów pocztowych - przykładowo
,
z której dla każdego kodu masz podaną dokładnie nazwę miejscowości - pozostała część ciągu stanowi "nazwę ulicy i numer domu"

Prot napisał:

To jest proste jak konstrukcja cepa

Chodziło mi o oddzielenie ulicy od nr domu. Nie wiem czy autorowi na tym zależy, ale mówię, żeby była jasność. No chyba, że dopuszczalne są błędy i może być ich dużo. Ale wtedy dlaczego nie pójść dalej i nie założyć, że chodzi o otrzymanie "czegokolwiek". Wtedy można skorzystać np. z...

Prot napisał:

Wystarczy wykorzystać darmową bazę kodów pocztowych

Prot napisał:

dla każdego kodu masz podaną dokładnie nazwę miejscowości

No ale chyba jednak nie można ponieważ (a tyś tej bazy nawet nie przejrzał):

fourste napisał:

Myślałem, żeby wykorzystać szukanie pionowe po kodzie pocztowym i w ten sposób wyciągnąć miejscowość, ale w pod jeden kod pocztowy może być wiele miejscowości.

Może wystarczyć dla części rekordów, z tym, że ustalenie dla których wymaga decyzji człowieka. Tak czy inaczej bez ręcznej rzeźby się nie obędzie.

Dane - tekst jako kolumny jako separator spacja, gdy skończy, od 2 do prawdopodobnie 4, może 5 kolumny będą czlony miejscowości, złącz je w nowej kolumnie =a2 & " " & a3 itd,
Gotowe

ex-or napisał:

No ale chyba jednak nie można

ex-or napisał:

fourste napisał:
ale w pod jeden kod pocztowy może być wiele miejscowości

To stwierdzenie jest fałszywe

Może być wiele adresów dla jednego kodu, ale miejscowość tylko jedna

Moderowany przez Marek_Skalski:

Proszę nie wprowadzać w błąd. W polskim systemie pocztowym, może być wiele miejscowości w obrębie jednego kodu pocztowego. Przypisanie nazwy miejscowości tylko na podstawie kodu pocztowego jest bardzo ryzykowne. W większości gmin w Polsce, małe wioski mają wspólny kod pocztowy, nie mają ulic, a tylko numery domów. Zmiana nazwy miejscowości sprawi, że dane adresowe będą błędne.
Nie jest problemem, gdy ktoś popełni błąd. Problemem jest, gdy ktoś, tak jak Kolega, uporczywie wprowadza w błąd, ironizuje i prowokuje bezsensowną kłótnię na forum.

3.1.9. Nie ironizuj i nie bądź złośliwy w stosunku do drugiej strony dyskusji. Uszanuj odmienne zdanie oraz inne opinie na forum.

1. https://kody.poczta-polska.pl/#/
2. w pole Kod pocztowy wpisać 73-102
3. w pole Wpisz kod wpisać kod (wielkość liter nie ma znaczenia)
4. klinąć Szukaj
5. Podumać nad fałszem kolumny "Miejscowość"

Wtrącę i swoje 3 grosze...
Lata temu porządkowałem bazę dla jakiejś firmy telemarketingowej. Dane były bardzo podobne, czyli ciurkiem bez spacji. Kolejność moich działań.
1. Ściągnąłem csv z kodami pocztowymi. Jest tego sporo w Internecie, należy wyszukać plik z układem kod, miasto, ulica
2. Napisałem makro pozostawiające tylko kod i miasto. Często występowały z Nr oddziału poczty (Tarnów 1, Tarnów 2, itp), należało więc dodatkowo usunąć cyfry, wytrymować.

Dalej (Gdy baza kodów gotowa):
3. Wyszukiwanie (funkcja) w wyniku dawało ilość spacji w nazwie miasta. Dlatego, że zapis bywał różny. Tu mamy Np "Nowe k. Świecia", w bazie było "Nowe koło Świecia".

4. Podstawiałem kod i miasto z bazy kodów pocztowych oraz resztę ciągu źródłowego od odpowiedniej spacji po kodzie (przed kodem były dane imienne).

Wskaźnik błędu, z tego co pamiętam, wynosił ~0,001%

Cytat:

Może być wiele adresów dla jednego kodu, ale miejscowość tylko jedna

@Prot
Sprawdź np kod 55-081,55-100,55-140,
Pod jednym kodem nawet kilkanaście miejscowości, gdzie powtarza się kod, ulica i nr budynku, różni się tylko miejscowościa

Chyba najbardziej wiarygodne dane o pna będą w bazie Teryt, można pobrać za darmo.

Dotnetvb napisał:

Sprawdź np kod 55-081,55-100,55-140,

Autor rzucił temat i pewnie "dziubie" te swoje 3 mln wierszy na piechotę - a tu rozgorzała taka głęboka dyskusja

Korzystam z bazy kodów pocztowych pl.wikisource.org
a tu całkiem logicznie ujęte są tylko miejscowości, które mają urząd pocztowy

Zajrzyj na Teryt lub na stronie Poczty Polskiej wpisz jakiś kod, tam będą wszystkie miejscowości z danego pna
http://kody.poczta-polska.pl/#/result

Dyskusja rozgorzała, a rozwiązania nie ma.
Pod jeden kod pocztowy może podlegać kilka miejscowości - więc pomysł z kodami nie zawsze będzie prawdziwy, a nazwa miejscowości może mieć jedno lub więcej słów - więc szukanie ich po spacji nie ma sensu. Może ktoś zna rozwiązanie?

Można by się tak pobawić

najpierw wyodrębnić PNA, następnie



W wyniku otrzymamy listę par stringów:
JANOWIEC WIELKOPOLSKI POZNAŃSKA 1;Janowiec Wielkopolski
STARGARD WITKOWO PIERW PAPIEŻA JANA PAWŁA II 34;Stargard

następnie, nawet funkcją w excelu z każdej pary, z pierwszej wartości usunąć stringi =drugiej wartości


W wyniku otrzymamy:
POZNAŃSKA 1;Janowiec
WITKOWO PIERW PAPIEŻA JANA PAWŁA II 34;Stargard

Może to jeszcze nie idealnie, lecz już coś by było. Kwestia zabawy i potestowania, na podstawie 3 wierszy ciężko też stwierdzić jakich metod użyć.
W przypadku Witkowa, trzeba by pomyśleć, może jeszcze raz przepuścić wynik przez porównanie ze słownikiem miejscowości itp itd...

Przy użyciu VBA, wyrażeń regularnych i wykorzystując metodę słownikową można uzyskać przekształcenia jak na zrzucie
2019-03...png (67.8 kB)Musisz być zalogowany, aby pobrać ten załącznik.

Całość wraz ze słownikiem kodów pocztowych w załączonym pliku
PodAdr.7z (416.78 kB)Musisz być zalogowany, aby pobrać ten załącznik.

W sporej części pomoże odmiana wyrazów, jeśli bowiem rozpatrywać np. taki "Stargard Szczeciński", to wiadomo, że jeśli w wyrazie po "Stargard" wystąpi taka a nie inna końcówka to te dwa wyrazy stanowią "całość".
No i nie ma co się zapierać przed użyciem danych z sieci o nazwach miejscowości, z których to można sobie zrobić słownik porównawczy.
Trzeba wykorzystać wszystko co możliwe: wyrażenia r., konstrukcje porównawcze (Like), funkcje i co tam tylko komu wpadnie do głowy ... jak się bowiem nie zadbało o czytelność danych, to cudów nie ma, trzeba się pomęczyć, a nie tylko liczyć na forumowe wunderwaffe ... ...

... o ile oczywiście uda się te 3 mln linii zmieścić w excelu ... ... rozumiem, że pochodzą one ze źródła nie-excelowego ?

Edycja:

http://ksng.gugik.gov.pl/urzedowe_nazwy_miejscowosci.php
http://knmiof.mswia.gov.pl/kn/baza-nazw-miejs...rzedowych-nazw-miejscowosci-i-ich-czesci.html

Siemka, moglbys wstawic gdzies ta tabele zebym mogl sie jej przyjzec?

Pozdrawiam