Dobra baza danych jest zawsze uporządkowana. Oznacza to, że różne podmioty mają różne tabele. Chociaż Excel nie jest narzędziem bazodanowym, ale często jest używany do utrzymywania małych porcji danych.
Wielokrotnie mamy potrzebę łączenia tabel w celu zobaczenia relacji i uzyskania użytecznych informacji.
W bazach danych takich jak SQL, Oracle, Microsoft Access, itp, łatwo jest łączyć tabele za pomocą prostych zapytań. Jednak w Excelu nie mamy JOINów, ale nadal możemy łączyć tabele w Excelu. Używamy Funkcji Excela do łączenia i dołączania tabel danych. Być może jest to bardziej konfigurowalne łączenie niż SQL. Zobaczmy techniki łączenia tabel Excela.
Łączenie danych w Excelu przy użyciu funkcji VLOOKUP
Aby połączyć dane w Excelu, powinniśmy mieć co najmniej jeden wspólny czynnik/id w obu tabelach, abyśmy mogli użyć go jako relacji i połączyć te tabele.
Rozważmy te dwie tabele poniżej. Jak możemy połączyć te tabele danych w Excelu?
Tabela Zamówienie zawiera szczegóły zamówienia, a tabela Klient zawiera szczegóły klienta. Musimy przygotować tabelę, która powie nam, które zamówienie należy do którego klienta, poda jego nazwę, punkty, numer domu i datę dołączenia.
Wspólnym identyfikatorem w obu tabelach jest Cust.ID, który może być użyty do łączenia tabel w Excelu.
Istnieją trzy metody łączenia tabel za pomocą funkcji VLOOKUP.
Pobieranie każdej kolumny za pomocą indeksu kolumny
W tej metodzie użyjemy prostego VLOOKUP, aby dodać te tabele do jednej tabeli. Aby pobrać nazwę napisz następującą formułę.
Aby połączyć punkty w tabeli, napisz następującą formułę.
Aby połączyć nr domu w tabeli, napisz następującą formułę.
Tutaj połączyliśmy dwie tabele w Excelu, każdą kolumnę po kolei w tabeli. Jest to przydatne, gdy masz tylko kilka kolumn do scalenia. Ale kiedy masz wiele kolumn do połączenia, to jest może być gorączkowe zadanie. Więc aby połączyć wiele tabel mamy różne podejścia.
Łączenie tabel przy użyciu funkcji VLOOKUP i COLUMN.
Gdy chcesz pobrać wiele sąsiadujących kolumn, użyj tej formuły.
Tutaj funkcja COLUMN zwraca numery kolumn, w których zapisana jest formuła.
N jest dowolną liczbą, która dostosowuje numer kolumny w tablicy,
W naszym przykładzie formuła dla łączenia tabel będzie wyglądała następująco:
Gdy napiszesz tę formułę, nie będziesz musiał pisać jej ponownie dla innych kolumn. Wystarczy skopiować ją do wszystkich innych komórek i kolumn.
Jak to działa
Ok! W pierwszej kolumnie potrzebujemy nazwę, która jest drugą kolumną w tabeli klientów.
Głównym czynnikiem jest tutaj COLUMN()-2. Funkcja COLUMN zwraca numer kolumny w bieżącej komórce. My piszemy formułę w D3, stąd z funkcji COLUMN() otrzymamy 4. Następnie odejmujemy 2, co daje 2. Stąd ostatecznie nasza formuła upraszcza się do =VLOOKUP($B3,Klienci,2,0).
Gdy skopiujemy tą formułę do kolumn E otrzymamy formułę jako =VLOOKUP($B3,Klienci,3,0). Która pobiera trzecią kolumnę z tabeli klientów.
Łączenie tabel przy użyciu funkcji VLOOKUP-MATCH
To jest mój ulubiony sposób łączenia tabel w Excelu przy użyciu funkcji VLOOKUP. W powyższym przykładzie, pobieraliśmy kolumny seryjnie. Ale co by było gdybyśmy musieli pobrać losowe kolumny. W takim przypadku powyższa technika będzie bezużyteczna. Technika VLOOKUP-MATCH wykorzystuje nagłówki kolumn do łączenia komórek. Nazywa się to również VLOOKUP z dynamicznym indeksem kol.
Dla naszego przykładu tutaj, formuła będzie wyglądać tak.
Tutaj po prostu używamy funkcji MATCH, aby uzyskać odpowiedni numer kolumny. Nazywa się to dynamicznym VLOOKUP.
Użycie funkcji INDEX-MATCH do łączenia tabel w Excelu
Funkcja INDEX-MATCH jest bardzo potężnym narzędziem wyszukiwania i często jest określana jako lepsza funkcja VLOOKUP. Możesz go użyć do łączenia dwóch lub więcej tabel. Pozwoli Ci to również na połączenie kolumn z lewej strony tabeli.
To był szybki tutorial o łączeniu i scalaniu tabel w Excelu. Omówiliśmy kilka sposobów łączenia dwóch lub więcej tabel w Excelu. Zapraszamy do zadawania pytań na temat tego artykułu lub jakichkolwiek innych zapytań dotyczących Excela 2019, 2016, 2013 lub 2010 w sekcji komentarzy poniżej.
IF, ISNA i funkcja VLOOKUP w Excelu
IFERROR i funkcja VLOOKUP w Excelu
Jak odzyskać cały wiersz dopasowanej wartości w Excelu
Popularne artykuły :
50 skrótów w Excelu, aby zwiększyć swoją produktywność : Szybciej wykonuj swoje zadania. Te 50 skrótów sprawi, że będziesz pracować jeszcze szybciej w Excelu.
Jak korzystać z funkcji VLOOKUP w Excelu : Jest to jedna z najbardziej używanych i popularnych funkcji Excela, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji COUNTIF w Excelu : Licz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby policzyć konkretne wartości. Funkcja Countif jest niezbędna do przygotowania tablicy rozdzielczej.
Jak korzystać z funkcji SUMIF w Excelu : Jest to kolejna funkcja niezbędna do przygotowania tablicy rozdzielczej. Pomaga ona w sumowaniu wartości w określonych warunkach.