Dobra baza danych jest zawsze ustrukturyzowana. Oznacza to, że różne podmioty mają różne tabele. Chociaż Excel nie jest narzędziem bazy danych, ale jest często używany do przechowywania małych porcji danych.
Wiele razy pojawia się potrzeba łączenia tabel w celu zobaczenia relacji i uzyskania przydatnych informacji.
W bazach danych takich jak SQL, Oracle, Microsoft Access itp. łatwo jest łączyć tabele za pomocą prostych zapytań. Ale w Excelu nie mamy JOIN, ale nadal możemy dołączać do stołów w Excelu. Używamy funkcji Excela do łączenia i łączenia tabel danych. Być może jest to bardziej konfigurowalne scalanie niż SQL. Zobaczmy techniki łączenia tabel Excela.
Scal dane w programie Excel za pomocą funkcji WYSZUKAJ.PIONOWO
Aby scalić 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 scalić te tabele.
Rozważ te dwie tabele poniżej. Jak możemy połączyć te tabele danych w programie Excel?
Tabela zamówień zawiera szczegóły zamówienia, a tabela Customer zawiera dane klienta. Musimy przygotować tabelę, która powie, które zamówienie należy do którego klienta, jego punktów, numeru domu i daty dołączenia.
Wspólny identyfikator w obu tabelach to ID klienta które można wykorzystać do scalania tabel w programie Excel.
Istnieją trzy metody łączenia tabel przy użyciu funkcji WYSZUKAJ.PIONOWO.
Pobierz każdą kolumnę z indeksem kolumn
W tej metodzie użyjemy prostej funkcji WYSZUKAJ.PIONOWO, aby dodać te tabele do jednej tabeli. Aby pobrać nazwę, napisz tę formułę.
[Klienci to Arkusz1!$I$3:$M$15.]
=WYSZUKAJ.PIONOWO(B3;Klienci,2,0)
Aby scalić punkty w tabeli, napisz tę formułę.
=WYSZUKAJ.PIONOWO(B3;Klienci,3,0)
Aby scalić numer domu w tabeli, napisz ten wzór.
=WYSZUKAJ.PIONOWO(B3;Klienci,4,0)
Tutaj połączyliśmy dwie tabele w programie Excel, każda kolumna jedna po drugiej w tabeli. Jest to przydatne, gdy masz tylko kilka kolumn do scalenia. Ale gdy masz wiele kolumn do scalenia, może to być gorączkowe zadanie. Tak więc, aby połączyć wiele tabel, mamy różne podejścia.
Scal tabele za pomocą funkcji WYSZUKAJ.PIONOWO i KOLUMNA Funkcja.
Jeśli chcesz pobrać wiele sąsiednich kolumn, użyj tej formuły.
=WYSZUKAJ.PIONOWO(wyszukiwana_wartość,tablica_tabeli,KOLUMNA()-n,0)
Tutaj funkcja COLUMN po prostu zwraca numery kolumn, w których zapisana jest formuła.
n to dowolna liczba, która dostosowuje numer kolumny w tablicy tabeli,
W naszym przykładzie wzór na scalanie tabel będzie wyglądał następująco:
=WYSZUKAJ.PIONOWO($B3,Klienci,KOLUMNA()-2,0)
Po napisaniu tej formuły nie będziesz musiał ponownie pisać formuły dla innych kolumn. Po prostu skopiuj go we wszystkich innych komórkach i kolumnach.
Jak to działa
Ok! W pierwszej kolumnie potrzebujemy nazwy, która jest drugą kolumną w tabeli klientów.
Tutaj głównym czynnikiem jest KOLUMNA()-2. Funkcja COLUMN zwraca numer kolumny bieżącej komórki. Piszemy formułę w D3, stąd otrzymamy 4 z KOLUMNA(). Następnie odejmujemy 2, co daje 2. Stąd w końcu nasz wzór upraszcza się do =WYSZUKAJ.PIONOWO($B3,Klienci,2,0).
Kiedy skopiujemy tę formułę w kolumnach E, otrzymamy formułę jako =WYSZUKAJ.PIONOWO($B3,Klienci,3,0). Który pobiera trzecią kolumnę z tabeli klientów.
Scal tabele za pomocą funkcji WYSZUKAJ.PIONOWO-MATCH
Ten jest moim ulubionym sposobem łączenia tabel w programie Excel za pomocą funkcji WYSZUKAJ.PIONOWO. W powyższym przykładzie pobraliśmy kolumnę w serialu. Ale co by było, gdybyśmy musieli pobrać losowe kolumny. W takim przypadku powyższe Techniki będą bezużyteczne. Technika WYSZUKAJ.PIONOWO-MATCH wykorzystuje nagłówki kolumn do scalania komórek. Jest to również nazywane WYSZUKAJ.PIONOWO z dynamicznym indeksem Col.
W naszym przykładzie tutaj formuła będzie taka.
=WYSZUKAJ.PIONOWO($B3;Klienci, DOPASUJ(D$2,$J$2:$N$2,0),0)
Tutaj po prostu używamy funkcji MATCH, aby uzyskać odpowiedni numer kolumny. Nazywa się to Dynamiczne WYSZUKAJ.PIONOWO.
Używanie INDEX-MATCH do scalania tabel w programie Excel
INDEX-MATCH jest bardzo potężnym narzędziem do wyszukiwania i często jest określany jako lepsza funkcja WYSZUKAJ.PIONOWO. Możesz użyć tego do łączenia dwóch lub więcej tabel. Umożliwi to również scalenie kolumn z lewej strony tabeli.
To był krótki samouczek dotyczący scalania i łączenia tabel w programie Excel. Zbadaliśmy kilka sposobów łączenia dwóch lub więcej tabel w programie Excel. Zachęcamy do zadawania pytań dotyczących tego artykułu lub innych pytań dotyczących programu Excel 2019, 2016, 2013 lub 2010 w sekcji komentarzy poniżej.
Funkcje JEŻELI, ISNA i WYSZUKAJ.PIONOWO w programie Excel
Funkcja JEŻELI.BŁĄD i WYSZUKAJ.PIONOWO w programie Excel
Jak pobrać cały wiersz dopasowanej wartości w programie Excel?
Popularne artykuły :
50 skrótów Excela, aby zwiększyć produktywność : Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak używać tFunkcja WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.