Scalanie dwóch list bez duplikatów

Klasyczna sytuacja: masz dwie listy, które trzeba połączyć w jedną. Co więcej, na początkowych listach mogą znajdować się zarówno elementy unikalne, jak i pasujące (zarówno między listami, jak i wewnątrz), ale na wyjściu trzeba uzyskać listę bez duplikatów (powtórzeń):

Przyjrzyjmy się tradycyjnie kilku sposobom rozwiązania tak powszechnego problemu – od prymitywnego „na czole” do bardziej złożonego, ale eleganckiego.

Metoda 1: Usuń duplikaty

Możesz rozwiązać problem w najprostszy sposób – ręcznie skopiuj elementy obu list w jedną, a następnie zastosuj narzędzie do powstałego zbioru. Usuń duplikaty z zakładki Dane (Dane — Usuń duplikaty):

Oczywiście ta metoda nie zadziała, jeśli dane w listach źródłowych często się zmieniają – po każdej zmianie trzeba będzie powtórzyć całą procedurę. 

Metoda 1a. Stół obrotowy

Ta metoda jest w rzeczywistości logiczną kontynuacją poprzedniej. Jeśli listy nie są bardzo duże, a maksymalna liczba elementów w nich jest z góry znana (na przykład nie więcej niż 10), możesz połączyć dwie tabele w jedną za pomocą bezpośrednich linków, dodać kolumnę z kolumnami po prawej stronie i zbuduj tabelę podsumowującą na podstawie tabeli wynikowej:

Jak wiecie, tabela przestawna ignoruje powtórzenia, więc na wyjściu otrzymamy połączoną listę bez duplikatów. Kolumna pomocnicza z 1 jest potrzebna tylko dlatego, że Excel może tworzyć tabele podsumowań zawierające co najmniej dwie kolumny.

Gdy oryginalne listy zostaną zmienione, nowe dane trafią do połączonej tabeli za pośrednictwem bezpośrednich linków, ale tabela przestawna będzie musiała zostać zaktualizowana ręcznie (kliknij prawym przyciskiem myszy – Zaktualizuj i zapisz). Jeśli nie potrzebujesz przeliczania w locie, lepiej skorzystać z innych opcji.

Metoda 2: Formuła tablicowa

Możesz rozwiązać problem za pomocą formuł. W takim przypadku przeliczenie i aktualizacja wyników nastąpi automatycznie i natychmiast po zmianach w oryginalnych listach. Dla wygody i zwięzłości nadajmy nazwy naszym listom. Lista 1 и Lista 2za pomocą Menedżer nazw Zakładka formuła (Formuły — Menedżer nazw — Utwórz):

Po nazwaniu formuła, której potrzebujemy, będzie wyglądać tak:

Na pierwszy rzut oka wygląda to przerażająco, ale w rzeczywistości wszystko nie jest takie przerażające. Pozwólcie, że rozwinę tę formułę w kilku wierszach, używając kombinacji klawiszy Alt+Enter i wcięcia ze spacjami, tak jak to zrobiliśmy, na przykład tutaj:

Logika tutaj jest następująca:

• Formuła INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) wybiera wszystkie unikalne elementy z pierwszej listy. Gdy tylko się skończą, zaczyna dawać błąd #N/D:

  

• Formuła INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2);0)) wyodrębnia unikatowe elementy z drugiej listy w ten sam sposób.
• Zagnieżdżone w sobie dwie funkcje IFERROR implementują wyjście najpierw z unikalnych z listy 1, a następnie z listy 2 po kolei.

Zwróć uwagę, że jest to formuła tablicowa, tzn. po wpisaniu należy ją wpisać w komórce, która nie jest zwyczajna Wchodzę, ale ze skrótem klawiaturowym Ctrl+Shift+Wchodzę a następnie skopiuj (przeciągnij) w dół do komórek podrzędnych z marginesem.

W angielskiej wersji programu Excel ta formuła wygląda tak:

=JEŻELI.BŁĄD(JEŻELIBŁĄD(INDEKS(Lista1, PODAJ.POZYCJĘ(0;LICZ.JEŻELI($E$1:E1.List1); 0)), INDEKS(Lista2, PODAJ.POZYCJĘ(0; LICZ.JEŻELI($E$1:E1;List2); 0)) ), „”) 

Wadą tego podejścia jest to, że formuły tablicowe zauważalnie spowalniają pracę z plikiem, jeśli tabele źródłowe mają dużą (kilkaset lub więcej) liczbę elementów. 

Metoda 3. Power Query

Jeśli listy źródłowe zawierają dużą liczbę elementów, na przykład kilkaset lub tysiące, zamiast powolnej formuły tablicowej lepiej zastosować zupełnie inne podejście, a mianowicie narzędzia dodatków Power Query. Ten dodatek jest domyślnie wbudowany w program Excel 2016. Jeśli masz program Excel 2010 lub 2013, możesz go pobrać i zainstalować osobno (bezpłatnie).

Algorytm działań jest następujący:

1. Otwórz osobną zakładkę zainstalowanego dodatku Zapytanie o moc (jeśli masz Excel 2010-2013) lub po prostu przejdź do zakładki Dane (jeśli masz Excel 2016).
2. Wybierz pierwszą listę i naciśnij przycisk Z tabeli/zakresu (z zakresu/tabeli). Zapytani o stworzenie „inteligentnego stołu” z naszej listy, zgadzamy się:

   

3. Otworzy się okno edytora zapytań, w którym możesz zobaczyć załadowane dane i nazwę zapytania Tabela 1 (możesz to zmienić na własne, jeśli chcesz).
4. Kliknij dwukrotnie nagłówek tabeli (słowo Lista 1) i zmień jego nazwę na dowolną inną (na przykład Ludzie). Co dokładnie nazwać, nie jest ważne, ale wymyśloną nazwę należy zapamiętać, ponieważ. będzie musiał zostać użyty ponownie później podczas importowania drugiej tabeli. Scalanie dwóch tabel w przyszłości będzie działać tylko wtedy, gdy ich nagłówki kolumn będą zgodne.
5. Rozwiń listę rozwijaną w lewym górnym rogu zamknij i pobierz i wybierz Zamknij i załaduj w… (Zamknij i załaduj do…):

   

6. W następnym oknie dialogowym (może wyglądać trochę inaczej – nie przejmuj się), wybierz Po prostu stwórz połączenie (Tylko utwórz połączenie):

   

7. Całą procedurę (punkty 2-6) powtarzamy dla drugiej listy. Podczas zmiany nazwy nagłówka kolumny ważne jest, aby użyć tej samej nazwy (People), co w poprzednim zapytaniu.
8. W oknie Excela na karcie Dane lub na karcie Zapytanie o moc Dodaj Pobierz dane – połącz żądania – dodaj (Pobierz dane — Scal zapytania — Dołącz):

   

9. W wyświetlonym oknie dialogowym wybierz nasze żądania z list rozwijanych:

   

10. W efekcie otrzymamy nowe zapytanie, w którym dwie listy zostaną połączone pod sobą. Pozostaje usunąć duplikaty za pomocą przycisku Usuń wiersze – Usuń duplikaty (Usuń wiersze — Usuń duplikaty):

    

11. Gotowe zapytanie można zmienić po prawej stronie panelu opcji, nadając mu rozsądną nazwę (w rzeczywistości będzie to nazwa tabeli wynikowej) i wszystko można wgrać do arkusza za pomocą polecenia zamknij i pobierz (Zamknij i wczytaj):

    

W przyszłości, przy jakichkolwiek zmianach lub dodatkach do oryginalnych list, wystarczy kliknąć prawym przyciskiem myszy, aby zaktualizować tabelę wyników.

• Jak zbierać wiele tabel z różnych plików za pomocą dodatku Power Query
• Wyodrębnianie unikalnych przedmiotów z listy
• Jak porównać ze sobą dwie listy pod kątem dopasowań i różnic?