Załóżmy, że mam pewne dane w Excelu (a nie w prawdziwej bazie danych). W jednym arkuszu mam dane, w których jedna kolumna działa jako identyfikator, i upewniłam się, że wartości w tej kolumnie są unikalne. W innym arkuszu mam również pewne dane, ponownie z jedną kolumną, którą można potraktować jako identyfikator, i jest ona również unikalna. Jeśli rząd N w arkuszu 1 ma jakąś wartość, a rząd M w arkuszu 2 ma tę samą wartość, jestem pewien, że rząd N i rząd M opisują ten sam obiekt świata rzeczywistego.

O co pytam: jak mogę uzyskać równowartość pełnego sprzężenia zewnętrznego bez pisania makr? Formuły i wszystkie funkcje dostępne za pośrednictwem wstążki są w porządku.

Mały przykład „odtwarzaj dane”:

Arkusz 1:

Dostoyevski    Russia
Pushkin        Russia
Shelley        England
Flaubert       France
Hugo           France
Eichendorff    Germany
Byron          England
Zola           France

Arkusz 2:

Shelley        Percy Bysshe
Eichendorff    Josef Freiherr Von
Flaubert       Gustave
Byron          Lord
Keller         Gottfried
Dostoyevski    Fyodor
Zola           Emile
Balzac         Honoré de

Pożądane wyjście (sortowanie nie jest ważne):

Dostoyevski    Russia   Fyodor
Pushkin        Russia
Shelley        England  Percy Bysshe
Flaubert       France   Gustave
Hugo           France
Eichendorff    Germany  Josef Freiherr von
Byron          England  Lord
Zola           France   Emile
Keller                  Gottfried
Balzac                  Honoré de

Wszystkim, którzy są przerażeni tym scenariuszem: wiem, że jest to niewłaściwy sposób. Jeśli mam jakiś wybór, nie użyłbym do tego Excela. Istnieje jednak wystarczająca liczba sytuacji, w których potrzebne jest pragmatyczne rozwiązanie, nie można zastosować statystyki i lepszego (z punktu widzenia IT) rozwiązania.

Łatwe podejście - standardowe operacje Excela

Najpierw skopiuj / wklej obie kluczowe kolumny z obu tabel do jednego nowego arkusza jako pojedynczej kolumny.

Skorzystaj z opcji „Usuń duplikaty”, aby uzyskać pojedynczą listę wszystkich unikatowych kluczy.

Następnie dodaj dwie kolumny (w tym przypadku), po jednej dla każdej kolumny danych w każdej tabeli. Zalecam również użycie formatu jako opcji tabeli, ponieważ sprawia, że ​​formuły wyglądają o wiele ładniej. Korzystając z vlookup, użyj następującej formuły:

=IFERROR(VLOOKUP([@ID],Sheet4!A:B,2,FALSE),"")

Gdzie Sheet4!A:Breprezentuje dowolną tabelę danych tabeli źródłowej dla każdej odpowiedniej wartości. IFERROR zapobiega brzydkim wynikom # N / A, które pojawiają się, gdy vlookup nie powiedzie się i w tym przypadku zwraca pustą komórkę.

To daje wynikową tabelę.

Arkusz 3:

Arkusz 4:

Dane wynikowe:

Formuły wyników ( Ctrl+ ~przełącza to):

Wbudowane zapytanie SQL

Możesz to również zrobić za pomocą wbudowanego zapytania SQL. Jest ... znacznie mniej przyjazny dla użytkownika, ale może będzie lepszym przykładem użycia. Będzie to prawdopodobnie wymagać sformatowania danych „źródłowych” jako tabel.

1. Kliknij komórkę w nowym arkuszu
2. Przejdź do Dane -> Z innych źródeł -> Z zapytania Microsoft
3. Wybierz pliki Excel * na karcie Bazy danych i naciśnij OK
4. Wybierz skoroszyt
5. Wybierz następujące cztery pola:
   • 
6. Kliknij „Dalej” i „OK” w ładnym, sformatowanym ostrzeżeniu z lat 90
7. Postępując zgodnie z tymi instrukcjami, utwórz pierwsze lewe łączenie zewnętrzne. W moim przypadku używam tabeli „kraje” jako lewego źródła i „nazw” jako prawej.
   • 
   • Daje to tylko niektóre wiersze (ponieważ dołączasz do identyfikatora)

8. Część „utwórz złączenie odejmujące, a następnie dodaj jako połączenie” jest bardziej skomplikowana.

   • Oto konfiguracje łączenia odejmowania:
   • Skopiuj kod SQL tego sprzężenia z przycisku SQL:

   • SELECT countries$.ID, countries$.Val1, names$.ID, names$. Val2 OD {oj C:\Users\Username\Desktop\Book2.xlsx. countries$ countries$DOŁĄCZ DO LEWEGO ZEWNĘTRZNEGO C:\Users\Username\Desktop\Book2.xlsx. names$ names$ON countries$.ID = names$.ID} GDZIE ( names$.ID ma wartość NULL)

9. Wróć do pierwszego utworzonego połączenia zewnętrznego. Ręcznie edytuj kod SQL i

   • dodaj Uniondo dołu
   • Dodaj powyższy tekst połączenia odejmij na dole złącza
10. Naciśnij przycisk „Zwróć dane” bezpośrednio po lewej stronie przycisku SQL
    • Możesz edytować SQL, aby wybrać tylko określone dane, które chcesz w tym momencie. W wyniku łatwiej mi ukryć kolumny.
11. Umieść zapytanie gdzieś i potwierdź jego lokalizację
    • 

Nie dla słabego serca. Ale jeśli chcesz mieć wielką szansę, aby zobaczyć niektóre niezaktualizowane tak długo, jak mógłbyś być żywy, części pakietu Office, jest to świetna szansa.

Jako alternatywne rozwiązanie mogę zasugerować Power Query ? Jest to bezpłatny dodatek do programu Excel firmy Microsoft do wykonywania dokładnie takich czynności. Jego funkcjonalność zostanie również bezpośrednio uwzględniona w programie Excel 2016, więc jest przyszłościowa.

W każdym razie, dzięki Power Query, kroki są dość proste:

1. Zaimportuj obie tabele jako zapytania do edytora zapytań Power Query.
2. Wykonaj na nich transformację Merge Queries, ustawiając odpowiednią kolumnę łączenia i ustawiając typ łączenia jako Full Outer.
3. Załaduj tabelę wyników do nowego arkusza.

Zaletą tego jest to, że po skonfigurowaniu tej opcji, jeśli dokonujesz zmian w swoich bazowych tabelach danych, po prostu naciskasz Dane> Odśwież wszystko, a także twoja karta wyników zapytania mocy jest aktualizowana.

Szybkim sposobem na pół-symulację (sprzężenie zewnętrzne) jest wzięcie drugiej listy i wklejenie jej bezpośrednio pod listą pięści, tzn. Aby wszystkie (klucze podstawowe) znajdowały się w tej samej (głównej) kolumnie. Następnie posortuj tę kolumnę podstawową, skończysz na liście z przeplotem, a następnie wykonasz transponowane równanie JEŻELI (superużytkownik psuje widok układu tabeli ...):

lista A lista B

sam blue tim 32874 tim red mary 5710 chris green gustav 047 fred blue
mary black

kopiuj / wklej / sortuj, wynik wygląda następująco: AB chris green fred blue gustav 047 mary 5710 mary black sam blue tim 32874 tim red

następnie formuła dla komórki c1: (c1) = if (A1 = A2, B2)

wynik wygląda następująco:

ABC chris zielony FALSE fred niebieski FALSE gustav 047 FALSE mary 5710 czarny mary czarny FALSE sam niebieski FALSE tim 32874 czerwony tim czerwony FALSE

Sortuj C, aby pozbyć się falals itp. Jest to podstawowa wersja pomysłu, wystarczy ją rozwinąć, jeśli potrzeba więcej danych. -wag770310