Jak połączyć dwa arkusze w programie Excel, tak jak w SQL?

Mam dwa arkusze w dwóch różnych plikach Excel. Oba zawierają listę nazwisk, numerów identyfikacyjnych i powiązanych danych. Jedna to główna lista zawierająca ogólne pola demograficzne, a druga to lista zawierająca tylko imię i nazwisko oraz identyfikator oraz adres. Ta lista została sprowadzona z głównej listy przez inne biuro.

Chcę użyć drugiej listy do filtrowania pierwszej. Ponadto chcę, aby wyniki zawierały inne pola z głównego arkusza roboczego obok pól adresowych z drugiego arkusza roboczego. Wiem, jak mogłem to zrobić bardzo łatwo z wewnętrznym złączeniem bazy danych, ale nie jestem pewien, jak to zrobić efektywnie w programie Excel. Jak połączyć dwa arkusze w programie Excel? Punkty bonusowe za pokazanie, jak również wykonywać połączenia zewnętrzne, i wolałbym wiedzieć, jak to zrobić bez potrzeby makra.

Dla 2007+ użytku Data> From Other Sources> From Microsoft Query:

1. wybierz Excel Filei wybierz swój pierwszy cel
2. wybierz kolumny
   (jeśli nie widzisz żadnej listy kolumn, sprawdź Options> System Tables)
3. przejdź do Data> Connections> [wybierz połączenie właśnie utworzony]> Properties> Definition>Command text

Możesz teraz edytować to Command textjako SQL. Nie jestem pewien, jaka składnia jest obsługiwana, ale próbowałem łączenia niejawnego, „wewnętrznego łączenia”, „lewego łączenia” i związków, które wszystkie działają. Oto przykładowe zapytanie:

SELECT *
FROM `C:\Users\Peter\Documents\Excel-to-excel\Source_1.xlsx`.`Sheet1$` a
LEFT JOIN `C:\Users\Peter\Documents\Excel-to-excel\Source_2.xlsx`.`Sheet1$` b
ON a.col2 = b.col2

Poprzyj przyjętą odpowiedź. Chcę po prostu podkreślić „wybierz kolumny (jeśli nie widzisz żadnej listy kolumn, sprawdź Opcje> Tabele systemowe)”

Po wybraniu pliku programu Excel prawdopodobnie pojawi się this data source contains no visible tablesmonit, a dostępne karty i kolumny są puste. Microsoft przyznał, że jest to błąd polegający na tym, że zakładki w plikach programu Excel są traktowane jako „Tabele systemowe”, a opcja „Tabele systemowe” nie jest domyślnie zaznaczona. Więc nie panikuj na tym etapie, wystarczy kliknąć „opcja” i zaznaczyć „Tabele systemowe”, a następnie zobaczysz dostępne kolumny.

WYSZUKAJ.PIONOWO i WYSZUKAJ.PIONOWO można użyć do wyszukiwania pasujących kluczy podstawowych (przechowywanych pionowo lub poziomo) i zwracania wartości z kolumn / wierszy „atrybutów”.

Możesz użyć Microsoft Power Query, dostępnego dla nowszych wersji programu Excel (podobnego do przyjętej odpowiedzi, ale o wiele prostszego i łatwiejszego). Połączenia Power Query dołączają do „scalania”.

Najłatwiejszym sposobem jest utworzenie 2 arkuszy Excela jako tabel Excela. Następnie w programie Excel przejdź do karty wstążki Power Query i kliknij przycisk „Z Excela”. Po zaimportowaniu obu tabel do Power Query wybierz jedną i kliknij „Scal”.

Chociaż uważam, że odpowiedź Aprilliona za pomocą Microsoft Query jest doskonała, zainspirowało mnie to do skorzystania z Microsoft Access do dołączenia do arkuszy danych, co było dla mnie znacznie łatwiejsze.

Oczywiście musisz mieć zainstalowany MS Access.

Kroki:

• Utwórz nową bazę danych Access (lub użyj scratch DB).
• Służy Get External Datado importowania danych Excela jako nowych tabel.
• Użyj, Relationshipsaby pokazać, jak połączone są twoje stoły.
• Ustaw typ relacji, aby dopasować, co chcesz (reprezentujący lewy łączenie itp.)
• Utwórz nowe zapytanie, które łączy twoje tabele.
• Służy External Data->Export to Exceldo generowania wyników.

Naprawdę nie byłbym w stanie tego zrobić bez świetnej odpowiedzi Aprilliona.

W tabelach programu Excel nie można wstępnie tworzyć sprzężeń w stylu SQL z poziomu programu Excel. To powiedziawszy, istnieje wiele sposobów na osiągnięcie tego, co próbujesz zrobić.

W Excelu, jak mówi Reuben, formuły, które prawdopodobnie będą działać najlepiej, to VLOOKUPi HLOOKUP. W obu przypadkach pasujesz do unikalnego wiersza i zwraca on wartość podanej kolumny \ wiersza w lewo \ w dół od znalezionego identyfikatora.

Jeśli chcesz dodać tylko kilka dodatkowych pól do drugiej listy, dodaj formuły do ​​drugiej listy. Jeśli chcesz mieć tabelę stylu „łączenie zewnętrzne”, dodaj VLOOKUPformułę do pierwszej listy za pomocą, ISNAaby sprawdzić, czy wyszukiwanie zostało znalezione. Jeśli pomoc programu Excel nie zawiera wystarczających informacji na temat korzystania z nich w konkretnym przypadku, daj nam znać.

Jeśli wolisz używać SQL, połącz dane z programem bazy danych, utwórz zapytanie i wyeksportuj wyniki z powrotem do Excela. (W programie Access możesz importować arkusze programu Excel lub nazwane zakresy jako tabelę połączoną).

W XLTools.net stworzyliśmy dobrą alternatywę dla MS Query do pracy szczególnie z zapytaniami SQL względem tabel Excel. Nazywa się to zapytaniami SQL XLTools . Jest o wiele łatwiejszy w użyciu niż MS Query i działa naprawdę dobrze, jeśli potrzebujesz tylko utworzyć i uruchomić SQL - bez VBA, bez skomplikowanych manipulacji z MS Query ...

Za pomocą tego narzędzia możesz utworzyć dowolne zapytanie SQL względem tabel w skoroszycie Excela za pomocą wbudowanego edytora SQL i uruchomić je natychmiast z opcją umieszczenia wyniku w nowym lub dowolnym istniejącym arkuszu.

Możesz użyć prawie dowolnego rodzaju złączenia, w tym LEWE DOŁĄCZENIE ZEWNĘTRZNE (tylko PRAWE DOŁĄCZANIE ZEWNĘTRZNE i FULL ZEWNĘTRZNE DOŁĄCZENIE nie jest obsługiwane).

Oto przykład:

W przypadku programu Excel 2007 Użytkownik: Dane> Z innych źródeł> Z zapytania Microsoft> przejdź do pliku Excel

Zgodnie z tym artykułem zapytanie z wersji XLS 2003 może spowodować „To źródło danych nie zawiera widocznych tabel”. błąd, ponieważ arkusze są traktowane jak tabela SYSTEM. Dlatego podczas tworzenia zapytania sprawdzaj opcje „Tabele systemowe” w oknie dialogowym „Kreator zapytań - Wybierz kolumny”.

Aby zdefiniować połączenie: okno dialogowe Microsoft Query> menu Tabela> Połączenia ...

Aby zwrócić dane do oryginalnego arkusza Excel, wybierz „Przywróć dane do arkusza Excel” z okna dialogowego Zapytanie Microsoft> menu Plik.

Jeśli znasz się na bazach danych, możesz użyć programu SQL Server do połączenia obu arkuszy jako serwerów połączonych, a następnie użyć języka T-SQL do wykonania danych zaplecza. Następnie zakończ podłączając program Excel z powrotem do SQL i przeciągnij dane do tabeli (zwykłej lub przestawnej). Możesz również rozważyć użycie Powerpivot; umożliwi połączenia między dowolnymi źródłami baz danych, w tym Excelem używanym jako płaskie bazy danych.

Szukając tego samego problemu natknąłem się na RDBMerge , który moim zdaniem jest przyjaznym dla użytkownika sposobem scalania danych z wielu skoroszytów programu Excel, plików csv i xml w skoroszyt podsumowania.