Elektroda.pl
Elektroda.pl
X
Please add exception to AdBlock for elektroda.pl.
If you watch the ads, you support portal and users.

Excel - Excel - porównywanie danych

darkdreamss 10 Nov 2013 21:23 4791 21
  • #1
    darkdreamss
    Level 9  
    Witajcie,

    Mam dość duży problem - mam dwie tabele - jedna z ponad 110 tys danymi a druga z 60.

    Każda ma takie same tabele jak

    specjalizacja imie nazwisko nazwa ulica miejscowosc kod pocztowy wojewodztwo telefon fax email www nazwa2 ulica 2 miejscowosc2 kod pocztowy 2 itp do 8

    I teraz chciałabym porównać te dane z tym, że w jednej bazie jest np. ulica wpisana w formacie (przykład tabeli): ul. nowa 2 a w drugiej nowa 2 i muszę porównać te dane czy są te same czy nie - po to aby się nie powtarzały - razem baza ma ponad 170 000 i ciężko to przerobić ręcznie... I druga kwestia dane te trzeba do siebie dopasować jak w jakieś dane w dwóch nazwiskach się powtarzają ale np. jest inny nr a w drugiej tabeli nie ma adresu - to trzeba to połączyć. Tak samo jeśli w dwóch tabelach są te same dane ale już nie ma np. nazwa 2 a rakord pierwszy to zawiera to trzeba to scalić... Mam nadzieje, że mnie rozumiecie. W załączeniu przesyłam przykład dla jasności.
  • #2
    adamas_nt
    Moderator of Programming
    Na początek może filtr zaawansowany wg kolumny pomocniczej (Nr tel. bez kresek):

    Excel - Excel - porównywanie danych

    W tej porcji danych, wiersz bez Nr telefonu jest dublem...
  • #3
    darkdreamss
    Level 9  
    Tylko co w przypadku gdy dwie te same osoby mają podane inne numery lub w jednym rekordzie ma go podany a w drugim nie?
  • #4
    adamas_nt
    Moderator of Programming
    Można napisać makro usuwające duplikaty. Ale na jakich zasadach? Jeśli nie Imię, nie nazwisko, nie Nr telefonu... Teraz widzisz po co wymyślono PESEL. Dla Excela :)

    Brakujące dane można uzupełnić w makro, ale priorytetową sprawą jest rozpoznanie duplikatów (wierszy dot. tej samej osoby).

    Przykład:
    Excel - Excel - porównywanie danych

    Czy to ta sama osoba?
    Wg specjalności - Nie
    Wg ulicy -Nie
    Jeśli sprawdzimy po Imię+Nazwisko+Kod+Miejscowość to - Tak - ale malarz, czy stolarz (dla filtra)?

    Wreszcie może to być stolarz, syn malarza o tym samym nazwisku. Mieszkają razem i maja wspólny telefon. Może stolarz, który jest malarzem na pół etatu. I tak dalej...
  • #5
    darkdreamss
    Level 9  
    Wiem właśnie że to trudne :) ale tu mi się wkradł błąd w przykład -> 1 to stolarz, a drugi to malarz i stolarz - tu chodzi o tą samą osobę :) Tylko w jeden bazie jest dodana specjalizacja a w drugiej nie i inny oczywiście format danych...

    Adamas --> uważasz, że da rade to zrobić za pomocą makra?
  • #6
    adamas_nt
    Moderator of Programming
    darkdreamss wrote:
    Adamas --> uważasz, że da rade to zrobić za pomocą makra?
    Oczywiście. Ale trzeba ustalić jakąś strategię. Zanim wyjaśnię uwaga: im gęściejsze sito, tym dłużej będzie przesiewać, łatwiej o błąd i pracy z pisaniem, testowaniem, poprawianiem więcej.
    Masz jakieś doświadczenie z VBA?

    Teraz strategia. Wiadomo, zaczynamy od imienia i nazwiska. Co może nas spotkać:
    -przypadkowa spacja
    -różnica w wielkości liter
    -brak polskich znaków

    Dalej: kod pocztowy (miasto i województwo można śmiało pominąć)
    -może wystąpić lub nie
    -może być z kreską lub bez

    Następnie: Ulica
    -może wystąpić lub nie
    -trzeba usunąć ew. przedrostek "ul.", "Ul", "ulica", "al", "Aleja", "pl.", "Plac", etc (kropką i wielkością liter się nie przejmuj) Najwygodniej byłoby usunąć wszystko do pierwszej spacji, ale jeśli "ul.Misia Beja"? Trzeba założyć takie wystąpienie.
    -z numerem, bez numeru

    Nr telefonu
    -jest lub brak
    -z kreską, lub bez (to już mieliśmy)
    -z kierunkowym (+48), bez
    -z przedrostkiem "tel."

    itd, itp
    Może wszystkimi siłami forum w kilka dni damy radę :)

    Hmm, ale od czegoś trzeba zacząć, więc napisz jak Ty to widzisz... Nie oszczędzaj klawiatury. Im więcej uwag, tym mniej pracy przy poprawkach.
  • #7
    darkdreamss
    Level 9  
    Nie mam żadnego doświadczenia z VBA... niestety

    O ile mogę usunąć te przedrostki ul to jak teraz wyfiltrować te dane bo przecież może być tak, że dwie inne osoby pracują na tej samej ulicy ale mają inne imię i nazwisko.

    W excelu narazie zrobiłam sobie formułę złącz.tekst tak aby imię i nazwisko pokazywało się razem i dałam formatowanie warunkowe na dublowanie się. i Teraz ręcznie sobie to usuwam ale jest to ponad 45.000 danych i nie każdy Adam Kowalski to ten sam bo mają inną specjalizację i adres :) uff.. dużo tego i szczerze powiedziawszy nie mam pojęcia od czego zacząć :) narazie tak usunęłam sobie (ręcznie) ok 1.000 danych w ciągu trzech dni... :/

    Kolejna sprawa - z numerem telefonu z kreskami nie będzie chyba problemu bo je można usunąć za pomocą zamień na - i tutaj nic nie wpisuje. Tak samo ze spacją - jeśli oczywiście się nie mylę :) Po drugie w wielu rekordach(szczególnie w 2 bazie) występują dwa nr - jeden stacjonarny drugi komórkowy. A w pierwszej albo nie ma albo jest jeden.

    Odnośnie ulicy Misia Beja - są takie przypadki

    Kod pocztowy pozwala również na identyfikację czy mówimy o tej samej osobie a tylko adres się zaktualizował czy nie :)

    Jest to dosyć zagmatfane :)
  • #8
    adamas_nt
    Moderator of Programming
    darkdreamss wrote:
    O ile mogę usunąć te przedrostki ul
    Tym ma się w założeniu zająć makro. Napisz tylko, na co jeszcze możemy się tu natknąć. Czy to, o czym pisałem wyżej wystarczy?

    darkdreamss wrote:
    Jest to dosyć zagmatwane
    Każdy kłębek drutu da się wyprostować. Nie martw się, mamy tu na forum kilku(nastu) mocnych w te klocki. Pewnie za niedługo (dzisiaj Święto) się odezwą ;)
  • #9
    darkdreamss
    Level 9  
    tak, to o czym pisałeś jest najbardziej prawdopodobne dodam od siebie, że może jeszcze być:

    W imieniu - mogą znaleźć się dwa imiona a drugiej bazie jedno


    reszta to to co napisałeś, chociaż występują jeszcze rekordy ze specjalizacją i imieniem i nazwiskiem ale bez reszty danych a w drugiej bazie te wszystkimi i pytanie jak to teraz scalić :)

    Dodano po 2 [minuty]:

    ahh i nie wiem czy wcześniej napisałam, moze też wystąpić jedna i ta sama osoba tylko w bazie pierwszej ma tylko jeden adres firmy a w drugiej ma dwa - ważne jest że wtedy muszą być dwa adresy albo i trzy, cztery jeśli występują.
  • #10
    adamas_nt
    Moderator of Programming
    Słowem pozostaje tylko imię i nazwisko. Najprościej będzie wygenerować tabelkę wynikową, gdzie dla imienia i nazwiska będą wypisane wszystkie znalezione dane (kolumny z nagłówkami masz gotowe).

    Druga sprawa: czemu ma to służyć? Może połączyć to z wyszukiwarką? Np wybierasz w wygodnym formularzu, że potrzebujesz hydraulika w Wałbrzychu, klikasz OK i...
  • #11
    darkdreamss
    Level 9  
    hmm tylko jak to zrobić (wygenerować tabelę wynikową)?

    Muszę zaktualizować bazę danych, którą mam a która jest istotna stąd wyszukiwarka niestety odpada :)

    Dodano po 5 [minuty]:

    ps. tylko czy wtym momencie nie pobierze mi danych dwóch takich samych nazwisk i imion ale z różną specjalizacją i adresem?

    Czy ewentualnie może to być imie i nazwisko oraz specjalizacja? Chociaż z drugiej strony mogą też być te same imienia i nazwiska ale różne specjalizacje.. i tutaj zagwostka ...
  • #12
    adamas_nt
    Moderator of Programming
    Makro można modyfikować na tysiąc sposobów. Póki nie rozwiążesz "zagwozdek"... kicha ;)

    Ale jak już pisałem: od czegoś trzeba zacząć. W załączniku makro (na prostych pętlach, bez regExponent i innych dopalaczy). Celowo starałem się uprościć kosztem wydajności, abyś mógł samodzielnie modyfikować.
    Na razie sprawdza po imię & nazwisko oraz warunkowo kopiuje adres. Przeanalizuj w edytorze (pomogą klawisze [F8], [F9]), dopasuj do własnych potrzeb.
    Dopisanie warunkowego kopiowania pozostałych (Nr.tel, nazwa, specjalizacja) nie powinno sprawić większych kłopotów (miejsca opisane komentarzami).
  • #13
    darkdreamss
    Level 9  
    hmm nie za bardzo wiem o czym mówisz ;)

    Niestety trzeciego wiersza nie pokazało (te same imię i nazwisko ale inna specjalizacja) :(
  • #14
    adamas_nt
    Moderator of Programming
    darkdreamss wrote:
    Niestety trzeciego wiersza nie pokazało (te same imię i nazwisko ale inna specjalizacja) :(
    To akurat żaden problem. Jedna linia z instrukcją If i "zostawi wiersz w spokoju".

    Tu bym się zastanowił. Może dodatkowa kolumna (lub wykorzystać Np 'fax' / kto tego dzisiaj używa/) typu "pozostałe specjalizacje" i tam wpisywać zamiast zostawiać zdublowane wiersze jedynie z tego powodu... Chyba, że masz w zamiarze używać filtrów, wtedy zgoda, powinny zostać.
  • #15
    darkdreamss
    Level 9  
    Czyli co dopisać jesli chodzi np. o specjalizację (i gdzie) i ewentualnie nr telefonu (osobno) :)
  • #16
    adamas_nt
    Moderator of Programming
    Specjalizację w pętli sprawdzającej jak niżej
    Code: vb
    Log in, to see the code
    Reszta danych, telefon- w funkcji 'CopyData'. Prosta podmianka (replace) spacji i kreski oraz porównanie. Miejsce - jeszcze prościej. Jeśli rożne -kopiuj. Przykład masz bodaj w trzech miejscach...
  • #17
    darkdreamss
    Level 9  
    hmm a powiedzmy - jeżeli będę miała imiona, nazwiska i specjalizacje po jednym rekordzie - to czy można wtedy zrobić jakieś makro tak, żeby pobierał mi reszte danych z pierwszej tabeli jeśli znajdzie taki rekord ? W pierwszej tabeli są przedrostki ul. pl. os. - więc może tak będzie szybciej?
  • #18
    adamas_nt
    Moderator of Programming
    darkdreamss wrote:
    hmm a powiedzmy - jeżeli będę miała imiona, nazwiska i specjalizacje po jednym rekordzie - to czy można wtedy zrobić jakieś makro tak, żeby pobierał mi reszte danych z pierwszej tabeli jeśli znajdzie taki rekord ? W pierwszej tabeli są przedrostki ul. pl. os. - więc może tak będzie szybciej?
    Wtedy nie trzeba makr, wystarczą funkcje wyszukujące: WYSZUKAJ.PIONOWO, INDEKS+PODAJ.POZYCJĘ...
  • #19
    darkdreamss
    Level 9  
    czyli mógłbyś jaśniej :)?
  • #20
    adamas_nt
    Moderator of Programming
    Przestaję rozumieć. Jaśniej? Przecież to podstawy podstaw Excela...
  • #21
    darkdreamss
    Level 9  
    ale z tych funkcji akurat nie korzystałam :)

    Dodano po 14 [minuty]:

    się rozpędziłam - niestety nie dam rady tego tak posegregować :( (tych imion, nazwisk i specjalizacji) a myślałam że nie będzie problemu :(
  • #22
    adamas_nt
    Moderator of Programming
    Nie powinno być problemów. Wracamy do początku: filtr zaawansowany + kolumna pomocnicza.

    Excel - Excel - porównywanie danych