Porównanie danych w tabelach

Na prośbę koleżanki z pracy dziś pokażę Wam jak porównać dane zawarte w dwóch tabelach. Częstą sytuacją jest, że dostajemy z różnych źródeł dane które częściowo się na siebie nakładają a oczekiwanym rezultatem naszych działań jest jedna tabela zawierająca rekordy unikatowe i przetworzone w jakiś sposób rekordy zdublowane. Poniżej przykładowe tabele z danymi:

Jak widać obie tabele zawierają projekty które częściowo się dublują. Aby rozpocząć nasze działania musimy odpowiedzieć  sobie na podstawowe pytanie czy każda z tabel posiada analogiczną kolumnę z unikatowymi w obrębie danej tabeli pozycjami. Trochę inaczej będziemy działali w przypadku gdy już w obrębie danej tabeli rekordy się dublują a inaczej jeżeli taki przypadek nie zachodzi. Jak sprawdzić unikatowość rekordów w obrębie tabeli ? Najprościej wykonać to korzystając z funkcji formatowania warunkowego poprzez zaznaczenie interesującej nas kolumny u nas Nr1 a następnie wybranie Menu  Narzędzia główne -> Formatowanie warunkowe -> Reguły wyróżniania komórek -> Duplikowanie się wartości.  Funkcja ta zaznacza na czerwono wszystkie zduplikowane wartości. Jak widać w naszym przypadku żadna z komórek nie została zaznaczona na czerwono a więc numery projektów w Tabeli1 są unikatowe. Analogiczną czynność wykonajmy dla Tabeli2. Tu również okaże się, że dane w kolumnie Nr2 są unikatowe.

Teraz najprostszą możliwą metodą możemy skopiować dane z Tabeli2 do Tabeli1 co spowoduje, że rekordy zdublowane z obu tabel zaznaczą nam się dzięki formatowaniu warunkowemu na czerwono a pozostałe nie. Dzięki temu łatwo znajdziemy duplikaty i będziemy mieli również w jednej tabeli od razu wszystkie unikatowe rekordy. My rozważymy jednak przypadek w którym nie chcemy lub nie możemy skopiować rekordów z jednej tabeli do drugiej gdyż np. boimy się je pomieszać lub obie tabele zawierają różne pozostałe kolumny.

Nasze zadanie możemy zrealizować na dwa sposoby.

Sposób nr 1 znany nam już, czyli przez formatowanie warunkowe. Tym razem zaznaczymy obie kolumny Nr1 oraz Nr2 robimy to wciskając klawisz Ctrl podczas zaznaczania myszką kolumn i znowu wybierzemy Menu  Narzędzia główne -> Formatowanie warunkowe -> Reguły wyróżniania komórek -> Duplikowanie się wartości.

Tym razem uzyskamy następujący efekt

Jak widać Excel znalazł 4 rekordy zdublowane w obu tabelach. Teraz posortujmy tabele tak aby zdublowane rekordy znalazły się na początku tabel. W obu tabelach wykonajmy następujące czynności Rozwijamy menu w kolumnie Nr1 i Nr2 oraz wybieramy Sortuj od najmniejszych do największych

Aby dane w tabelach poukładały się wg rosnącego numeru projektu. I jeszcze raz rozwijamy menu w kolumnie Nr1 i Nr2 oraz wybieramy Sortuj  wg kolorów -> Sortuj według kolorów komórek

Co da nam następujący efekt

Czyli zdublowane rekordy na początku zaznaczone innym kolorem oraz pozostałe rekordy unikatowe. Teraz już nasza decyzja co zrobić, np. pozostawić bez zmian lub zsumować rekordy zdublowane a unikatowe skopiować do jednej tabeli itp.

Sposób 2 wymaga dodania dodatkowych kolumn do naszych tabel. Nazwijmy je odpowiednio PozycjaT2 dla Tabeli1 i PozycjaT1 dla Tabeli2 zgodnie z poniższym widokiem

Teraz w Tabeli1 w pierwszym wierszu kolumny PozycjaT2 wpiszemy =PODAJ.POZYCJĘ([@Nr1];Tabela2[Nr2];0) i analogicznie w Tabeli2 w pierwszym wierszu kolumny PozycjaT1 wpiszemy =PODAJ.POZYCJĘ([@Nr2];Tabela1[Nr1];0) i uzyskamy następujący efekt.

Gdzie numerki oznaczają pozycje w drugiej tabeli na których znajdują się zdublowane elementy a #N/D oznacza, że rekord jest unikatowy (nie znaleziono w drugiej tabeli poszukiwanej wartości). Teraz tak jak poprzednio możemy posortować nasze tabele tym razem po ostatniej kolumnie Sortuj od najmniejszych do największych.

I uzyskamy efekt podobny do poprzedniego z rozdzielonymi rekordami zdublowanymi od unikatowych

Sposób 2 jest nieco trudniejszy gdyż wymaga dodatkowych kolumn ale wykorzystamy go również w przypadku gdy dane w naszych pojedynczych tabelach nie są unikatowe. Podmienimy nieco dane w tabelach i wykonamy jeszcze raz test unikatowości dla naszych pojedynczych tabel uzyskując tym razem następujący efekt

Jak widać w obu tabelach z osobna dane projektów się powtarzają natomiast My nadal jesteśmy zainteresowani tym jak powtarzają się one w obu tabelach razem. Dzięki już wprowadzonym formułom zadanie jest prawie rozwiązane, wystarczy jeszcze raz posortować obie tabele według pierwszej i ostatniej kolumny. Zrobimy to zaznaczając każdą tabelę z osobna oraz wybierając Menu Narzędzie główne-> Sortuj filtruj -> Sortowanie zaawansowane -> Sortowanie niestandardowe -> oraz wybór ostatniej kolumny na pierwszym poziomie sortowanie i pierwszej na poziomie drugim  tak jak zaprezentowano poniżej.

Uzyskacie następujący efekt

Czyli w samej Tabeli1 dubluje się 8 rekordów (na czerwono) a do Tabeli2 dubluje się 6 rekordów (posiadające numerek w kolumnie PozycjaT2). 

Z drugiej strony w samej Tableli2 dubluje się 6 rekordów (na czerwono) a  do Tabeli1 dubluje się 5 rekordów (posiadające numerek w kolumnie PozycjaT1)

Mam nadzieję, że temat nie okazał się skomplikowany. W kolejnym odcinku pokażę Wam jak uzupełniać brakujące dane pomiędzy tabelami.

Jak zawsze zapraszam do korespondencji i zgłaszania własnych tematów w których potrzebujecie pomocy na adres konrad.pogodz@itch.pl

Porównanie danych w tabelach