[Excel/OO] Jak porównać kilka wierszy w 2 arkuszach?


(Anthony20) #1

Witam,

mam następujący problem.

Załóżmy arkusz 1.

Koluma A - Firma

Kolumna B - Ulica

Kolumna C - Kod pocztowy

Kolumna D - Miasto

Kolumna E - Czy jest w drugim?

i w tym arkuszu kilka danych

Oraz arkusz drugi podobnie:

Koluma A - Firma

Kolumna B - Ulica

Kolumna C - Kod pocztowy

Kolumna D - Miasto

bez kolumny E.

1/ Jaką instrukcję napisać, ażeby w kolumnie E w pierwszym arkuszu wyrzucało mi, że takie firmy są w arkuszu drugim. Chodzi mi o porównanie wszystkich rzeczy, tj. zarówno nazwy jak i adresu. (czyli warunek musi być spełniony w jednym rekordzie dla wszystkich kolumn)

2/ Czy jest możliwe znalezienie firm o podobnej nazwie, ale pod tym załóżmy tym samym adresem (np. z jakimś prawdopodobieństwem).

Chodzi mi o to, że w pierwszym arkuszu może być np. Spółdzielnia Sopex, a w drugim Sopex Spółdzielnia. (czytałem o funkcji WYSZUKAJ.PIONOWO).

Będę wdzięczny za pomoc.


(Rafnix) #2

Witaj,

Ad. 1

Przyjmując, że drugi arkusz nazywa się arkusz2 to możesz zrobić to tak:

=JEŻELI(Arkusz2!A1=A1;PRAWDA;FAŁSZ)

Krótkie wyjaśnienie: jeśli komórka A1 w arkusz2 ma taką samą wartość co A1 tego arkusza to zwraca nam Prawda w przeciwnym wypadku Fałsz

Nie wiem czy chcesz te dane porównywać pojedynczo czy wszystkie razem? Jeśli pojedynczo to w tym co wyżej napisałem zmień adres komórek dla odpowiednich kolumn. Jeśli wszystkie dane mają się zgadzać to musisz rozbudować trochę IFa.

=JEŻELI(Arkusz2!A1=A1;Jeżeli(Arkusz2!B1=B1;Prawda;Fałsz);FAŁSZ) i tak dalej pod wynik Prawda podstawiaj kolejne warunki do sprawdzenia.

Ad. 2

Jeszcze nie wiem, ale jak się dowiem to Ci powiem :slight_smile:


(Anthony20) #3

A da się to załatwić makrami?


(Tomek Matz) #4

@miecio1998

AD 1) Powiedzmy, że w arkuszu nr 1 mamy wiersz z firmą o nazwie Spółdzielnia Sopex. Czy w arkuszu nr 2 wiersz z tą nazwą firmy może pojawić się maksymalnie jeden raz, czy też więcej razy? Jeśli więcej niż jeden raz to obawiam się, że można to będzie zrobić tylko przy użyciu VBA (odpowiednie makro). Jeśli tylko jeden raz to spróbuję Ci skleić jakąś formułę.

AD 2) Tutaj nie widzę innej możliwości jak VBA (odpowiednie makro).


(Anthony20) #5

Przykład:

Ad 1/

Arkusz 1:

1 | Spółdzielnia Sopex | Karmazynowa 1 | 30-300 | Warszawa | tutaj wynik = tak (tożsame z wierszem 3, w wierszu 2 chodzi o inną firmę o tej samej nazwie)

2 | ABIMO Sp. z o.o | Królewska 33 | 00-999 | Wrocław | tutaj wynik = nie

3 | Traficco | Jana 4 | 09-391 | Szczecin | tutaj wynik = nie

4 | Klepsydra | Długa 3 / 13 | 11-301 | Kraków | tutaj wynik = nie (inaczej nazwa i ulica jest w firmie 1 arkusza drugiego)

itd

Ark 2:

1 | Klepsydra Inc. | Długa 3/13 | 11-301 | Kraków

2 | Spółdzielnia Sopex | Królika 3 | 01-039 | Tomaszów Mazowiecki

3 | Spółdzielnia Sopex | Królewska 33 | 30-300 | Warszawa

Czyli porównujemy każdą firmę z arkusza 1 ze wszystkimi z 2 i dajemy tak jeśli wszystkie kolumny danych tej firmy się zgadzają.

Czy do tego potrzeba makro? nie wiem... chodzi mi o najlepsze i najefektywniejsze rozwiązanie :slight_smile:

Ad 2/

Może i chodzi o makro w tym wypadku, chodzi o to, że w przypadku firmy Klepsydra ktoś zapisał jako Klepsydra, a ktoś jako Klepsydra Inc. robiąc również spacje w adresie. I tutaj może wyrzucać w kolumnie wyniku wartość "podobna".


(Tomek Matz) #6

Ja rozumiem o co Ci chodzi :slight_smile: No ale dzięki za ten bardziej szczegółowy przykład. Czyli w arkuszu 2 może pojawić się kilka razy ta sama nazwa firmy. Niestety tego się obawiałem. W związku z tym wydaje mi się, że pozostaje tylko makro. Gdyby się te nazwy nie powtarzały to mógłbyś użyć, np. takiej formuły

=JEŻELI(NIE(CZY.BRAK(PODAJ.POZYCJĘ(A2;Arkusz2!A2:A7;0)));JEŻELI(INDEKS(Arkusz2!B2:B7;PODAJ.POZYCJĘ(A2;Arkusz2!A2:A7))=B2;JEŻELI(INDEKS(Arkusz2!C2:C7;PODAJ.POZYCJĘ(A2;Arkusz2!A2:A7))=C2;JEŻELI(INDEKS(Arkusz2!D2:D7;PODAJ.POZYCJĘ(A2;Arkusz2!A2:A7))=D2;"Tak";"Nie");"Nie");"Nie");"Nie")

Jak będę miał czas to napisze Ci dziś makro do punktu 1 (ale niczego nie obiecuję). Punkt 2 to jest już więcej roboty. Może ktoś inny się tym zajmie.


(Rafnix) #7

Ad. 1

@matzu dobrze kombinuje i ma rację co do pkt 1 ponieważ parametr tabela_tablica musi zawierać niepowtarzające się wartości.

Ad. 2

tutaj nie widzę innej możliwości jak VBA i makro w nim. Tu masz do porównania stringi, które trzeba będzie dzielić np. po spacji (Spółdzielnia Sopex, Sopex Spółdzielnia) by ze sobą porównać.


(Tomek Matz) #8

AD 1 i 2)

Utwórz sobie nowy moduł i nazwij go, np. Functions, a następnie umieść w nim taki kod:

' 0 - różne

' 1 - podobne

' 2 - identyczne

Public Function DuplicateExists(ByVal row As Range, ByVal tabel As Range) As Integer

Dim result, compareResult As Integer

Dim original, copy As String

Dim i, j As Integer


result = 0


For i = 1 To tabel.Rows.Count

    For j = 1 To row.Columns.Count

        original = row.Cells(1, j).Value

        copy = tabel.Cells(i, j).Value


        compareResult = Compare(original, copy)

        If compareResult = 0 Then

            result = 0

            Exit For

        ElseIf Not result = 1 Then

            result = compareResult

        End If

    Next j

    If result = 1 Or result = 2 Then Exit For

Next i


DuplicateExists = result

End Function


Private Function Compare(ByVal value1 As String, ByVal value2 As String) As Integer

Dim i, j, minStringLength, result As Integer

Dim value1Split, value2Split As Variant

Dim value1NoSpaces, value2NoSpaces, ignoredStrings As String


result = 0

value1NoSpaces = Replace(Trim(value1), " ", "")

value2NoSpaces = Replace(Trim(value2), " ", "")

ignoredStrings = "inc.;" ' łańcuchy znaków muszą być wstawiane bez spacji

minStringLength = 3


If Len(value1NoSpaces & "") = 0 And Len(value2NoSpaces & "") = 0 Then

    result = 2

ElseIf Len(value1NoSpaces & "") = 0 Or Len(value2NoSpaces & "") = 0 Then

    result = 0

ElseIf StrComp(value1, value2, vbBinaryCompare) = 0 Then

    result = 2

ElseIf StrComp(value1NoSpaces, value2NoSpaces, vbTextCompare) = 0 Then

    result = 1

Else

    value1Split = Split(Trim(value1), " ")

    value2Split = Split(Trim(value2), " ")


    For i = LBound(value1Split) To UBound(value1Split)

        If Not (Len(value1Split(i) & "") < minStringLength Or _

        InStr(1, ignoredStrings, value1Split(i), vbTextCompare) > 0) Then

            For j = LBound(value2Split) To UBound(value2Split)

                If Not Len(value2Split(j) & "") < minStringLength And _

                StrComp(value1Split(i), value2Split(j), vbTextCompare) = 0 Then

                    result = 1

                End If

            Next j

        End If

    Next i

End If


Compare = result

End Function

Następnie w arkuszu nr 1 w kolumnie E wstaw taki kod: =DuplicateExists(A2:D2;Arkusz2!$A$2:$D$4)

Funkcja DuplicateExists zwraca wartość 2, gdy w tabeli Arkusz2!$A$2:$D$8 istnieje identyczny wiersz jak A2:D2.

Funkcja DuplicateExists zwraca wartość 1, gdy w tabeli Arkusz2!$A$2:$D$8 istnieje wiersz podobny do A2:D2.

Funckja DuplicateExists zwraca wartość 0, gdy w tabeli Arkusz2!$A$2:$D$8 nie istnieje wiersz podobny do A2:D2.

Potestuj i daj znać, czy wszystko jest OK.


(Anthony20) #9

Oj działa działa :slight_smile:

Wysłałem info na priv, dzięki wielkie za pomoc!