Jak połączyć dwa arkusze w Excelu tak jak w SQL?

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

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

Możesz teraz edytować to Command text jako SQL. Nie wiem jaka składnia jest obsługiwana, ale próbowałem ukrytych połączeń, “wewnętrznych połączeń”, “lewych połączeń” 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 zaakceptowaną odpowiedź. Chcę tylko podkreślić, że “wybierz kolumny (jeśli nie widzisz żadnej listy kolumn, upewnij się, że zaznaczyłeś Opcje > Tabele systemowe)”

Po wybraniu pliku excel, bardzo prawdopodobne jest, że zobaczysz zachętę this data source contains no visible tables, a dostępne karty i kolumny nie są żadne. Microsoft przyznał, że jest to błąd , że karty w plikach Excela są traktowane jako “Tablice systemowe”, a opcja “Tablice systemowe” nie jest domyślnie zaznaczona. Więc nie panikuj na tym etapie, wystarczy kliknąć “opcja” i zaznaczyć “Tablice systemowe”, a następnie zobaczyć dostępne kolumny.

VLOOKUP i HLOOKUP mogą być użyte do wyszukiwania pasujących kluczy głównych (zapisanych pionowo lub poziomo) i zwrócenia wartości z kolumn/wierszy “atrybutów”.

Można użyć Microsoft Power Query, dostępny dla nowszych wersji programu Excel (podobnych do przyjętej odpowiedzi, ale znacznie prostszych i łatwiejszych). Wywołania Power Query łączą się z “łączeniem”.

Najprostszym sposobem jest posiadanie 2 arkuszy Excela jako tabel w Excelu. Następnie w Excelu przejdź do zakładki Power Query i kliknij przycisk “Z Excela”. Po zaimportowaniu obu tabel do Power Query, wybierz jedną z nich i kliknij ‘Połącz’.

Choć uważam, że odpowiedź Aprillion'a przy użyciu Microsoft Query jest doskonała, to jednak zainspirowała mnie do użycia Microsoft Access, aby dołączyć do arkuszy danych, które uznałem za znacznie łatwiejsze.

Oczywiście musisz mieć zainstalowany MS Access.

Kroki:

• Stwórz nową bazę danych Access (lub użyj scratch DB).
• Użyj Get External Data, aby zaimportować dane Excela jako nowe tabele.
• Użyj Relationships, aby pokazać, jak łączą się Twoje tabele.
• Ustaw typ relacji, aby dopasować go do Twoich potrzeb (reprezentujący lewe połączenie itp.)
• Stwórz nowe zapytanie, które łączy Twoje tabele.
• Użyj External Data->Export to Excel, aby wygenerować wyniki.

Naprawdę nie mógłbym tego zrobić bez świetnej odpowiedzi Aprillion.

W XLTools.net stworzyliśmy dobrą alternatywę dla MS Query do pracy zwłaszcza z zapytaniami SQL w stosunku do tabel Excela. Nazywa się ona XLTools SQL Queries . Jest ona o wiele łatwiejsza w użyciu niż MS Query i działa naprawdę dobrze, jeśli po prostu trzeba stworzyć i uruchomić SQL - bez VBA, bez skomplikowanych manipulacji z MS Query…

Za pomocą tego narzędzia można stworzyć dowolne zapytanie SQL względem tabel w skoroszycie Excela używając wbudowanego edytora SQL i uruchomić go natychmiast z opcją umieszczenia wyniku na nowym lub istniejącym arkuszu.

Możesz użyć prawie każdego typu połączenia, w tym LEFT OUTER JOIN (tylko RIGHT OUTER JOIN oraz FULL OUTER JOIN nie są obsługiwane).

Oto przykład:

Nie można preformować styl SQL łączy się z tabelami Excela z poziomu Excela. Istnieje jednak wiele sposobów, aby osiągnąć to, co próbujesz zrobić.

W Excelu, jak mówi Reuben, formuły, które prawdopodobnie zadziałają najlepiej to VLOOKUP i HLOOKUP. W obu przypadkach, dopasowujesz na unikalnym wierszu i zwraca on wartość danej kolumny w lewo od znalezionego id.

Jeśli chcesz dodać tylko kilka dodatkowych pól do drugiej listy, to dodaj formuły do drugiej listy. Jeśli chcesz dodać tabelę w stylu “połączenie zewnętrzne”, to dodaj formułę VLOOKUP do pierwszej listy z ISNA, aby sprawdzić czy znaleziono odnośnik. Jeśli pomoc Excela nie daje wystarczających informacji na temat tego, jak je wykorzystać w danej instancji, daj nam znać.

Jeśli wolisz używać kodu SQL, następnie połącz dane z programem bazodanowym, utwórz zapytanie i wyeksportuj wyniki z powrotem do Excela. (W Access możesz zaimportować arkusze Excela lub Zakresy nazwane w postaci tabeli połączeniowej).

Dla użytkownika Excela 2007: Dane > Z innych źródeł > Z zapytania Microsoft > przeglądaj do pliku Excela

Zgodnie z ten artykuł , zapytanie z XLS wersja 2003 może spowodować błąd “To źródło danych nie zawiera żadnych widocznych tabel”, ponieważ Twoje arkusze są traktowane jako tabela SYSTEM. Sprawdź więc opcję “Tabele systemowe” w oknie dialogowym “Kreator zapytań - wybierz kolumny”, gdy tworzysz zapytanie, które będzie działało poprawnie.

Aby zdefiniować swoje połączenie: Okno dialogowe Microsoft Query > Menu tabeli > Połączenia…

Aby zwrócić dane do oryginalnego arkusza Excela, wybierz “Zwróć dane do arkusza Excela” z okna dialogowego Microsoft Query > Menu Plik.

Szukając tego samego problemu natknąłem się na RDBMerge , który moim zdaniem jest przyjaznym dla użytkownika sposobem na połączenie danych z Multiple Excel Workbooks, plików csv i xml w Summary Workbook.

Jeśli jesteś wystarczająco zaznajomiony z bazami danych, możesz użyć SQL Server do połączenia obu arkuszy jako Linked Servers, a następnie użyć T-SQL do wykonania back-endowej pracy z danymi. Następnie zakończ pracę podłączając Excel z powrotem do SQL i przeciągnij dane do tabeli (zwykłej lub obrotowej). Możesz również rozważyć użycie Powerpivota, który pozwoli na łączenie dowolnych źródeł danych - w tym Excel używany jako płaska baza danych.