David z Florydy zadaje dzisiejsze pytanie:
Mam dwa zeszyty ćwiczeń. Obie zawierają te same dane w kolumnie A, ale pozostałe kolumny są różne. Jak mogę scalić te dwa skoroszyty?
Zapytałem Davida, czy jest możliwe, że jeden skoroszyt zawiera więcej rekordów niż drugi. A odpowiedź brzmi: tak. Zapytałem Davida, czy pole klucza pojawia się tylko raz w każdym pliku. Odpowiedź również brzmi: tak. Dzisiaj rozwiążę ten problem za pomocą dodatku Power Query. Narzędzia Power Query można znaleźć w wersjach programu Excel 2016+ dla systemu Windows w sekcji Pobieranie i przekształcanie na karcie Dane. Jeśli masz wersje programu Excel 2010 lub Excel 2013 dla systemu Windows, możesz pobrać dodatek Power Query dla tych wersji.
Oto skoroszyt Davida 1. Zawiera produkt, a następnie trzy kolumny danych.
Oto skoroszyt Davida 2. Zawiera kod produktu i inne kolumny. W tym przykładzie istnieją dodatkowe produkty w skoroszycie 2, ale rozwiązania będą działać, jeśli skoroszyt zawiera dodatkowe kolumny.
Oto kroki:
-
Wybierz dane, pobierz dane, z pliku, ze skoroszytu:
Załaduj dane z pliku - Przejdź do pierwszego skoroszytu i kliknij OK
- W oknie dialogowym Nawigatora wybierz arkusz po lewej stronie. (Nawet jeśli jest tylko jeden arkusz roboczy, musisz go zaznaczyć.) Zobaczysz dane po prawej stronie.
- W oknie dialogowym Nawigatora otwórz listę rozwijaną Załaduj i wybierz opcję Załaduj do…
- Wybierz opcję Tylko utwórz połączenie i naciśnij przycisk OK.
-
Powtórz kroki 1-5 dla drugiego skoroszytu.
Utwórz połączenie ze skoroszytem Jeśli wykonałeś oba skoroszyty, powinieneś zobaczyć dwa połączenia w panelu Zapytania i połączenia po prawej stronie ekranu programu Excel.
Połączenia do obu skoroszytów Kontynuuj wykonywanie czynności, aby scalić skoroszyty:
-
Dane, pobieranie danych, łączenie zapytań, scalanie.
Scal dwa zapytania z różnymi kolumnami - Z listy rozwijanej u góry w oknie dialogowym Scalanie wybierz pierwsze zapytanie.
- Z drugiej listy rozwijanej w oknie dialogowym Scalanie wybierz drugie zapytanie.
- Kliknij nagłówek Produkt w górnym podglądzie (to jest pole kluczowe. Zauważ, że możesz zaznaczyć wiele pól kluczowych, naciskając Ctrl + kliknięcie)
- Kliknij nagłówek Kod produktu w drugim podglądzie.
-
Otwórz typ połączenia i wybierz opcję Full Outer (All Rows From Both)
Zilustrowano tutaj kroki 8-12 -
Kliknij OK. Podgląd danych nie pokazuje dodatkowych wierszy, a jedynie wielokrotnie pokazuje „Tabela” w ostatniej kolumnie.
To nie wygląda obiecująco - Zauważ, że w nagłówku DavidTwo znajduje się ikona „Rozwiń”. Kliknij tę ikonę.
-
Opcjonalne, ale zawsze odznaczam opcję „Użyj oryginalnej nazwy kolumny jako prefiksu”. Kliknij OK.
Rozwiń pola ze skoroszytu 2 Wyniki są wyświetlane w tym podglądzie:
Wszystkie rekordy z dowolnego skoroszytu - W dodatku Power Query użyj opcji Home, Close & Load.
Oto piękna funkcja: jeśli podstawowe dane w którymkolwiek ze skoroszytów ulegną zmianie, możesz kliknąć ikonę Odśwież, aby pobrać nowe dane do skoroszytu wyników.
Uwaga
Ikona odświeżania jest zwykle ukryta. Przeciągnij lewą krawędź okienka Zapytania i połączenia w lewo, aby wyświetlić ikonę.
Obejrzyj wideo
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2216: Połącz dwa skoroszyty w oparciu o wspólną kolumnę.
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiejsze pytanie zadał David, który był na moim seminarium w Melbourne na Florydzie dla Kapituły Space Coast IIA.
David ma dwa różne zeszyty ćwiczeń, w których kolumna A jest wspólna dla obu z nich. Tak więc, tutaj skoroszyt 1, tutaj skoroszyt 2 - oba mają kod produktu. Ta zawiera elementy, których nie ma pierwsza z nich lub odwrotnie, a David chce połączyć wszystkie kolumny. Mamy więc trzy kolumny tutaj i cztery kolumny tutaj. Umieściłem oba w tym samym skoroszycie, na wypadek, gdybyś pobierał skoroszyt do pracy. Weź każdy z nich, przenieś go do własnego skoroszytu i zapisz.
W porządku, aby połączyć te pliki, użyjemy dodatku Power Query. Dodatek Power Query jest wbudowany w program Excel 2016. Jeśli używasz wersji 10 lub 13 systemu Windows, możesz przejść do firmy Microsoft i pobrać dodatek Power Query. Możesz zacząć od nowego pustego skoroszytu z pustym arkuszem. Zamierzasz zapisać ten plik - Zapisz jako, wiesz, może skoroszyt, aby wyświetlić wyniki połączonych plików .xlsx. W porządku? Zamierzamy zrobić dwa zapytania. Pójdziemy do Data, Get Data, From File, From Workbook, a następnie wybierzemy pierwszy plik. W podglądzie wybierz arkusz, który zawiera Twoje dane, a my nie musimy nic robić z tymi danymi. Po prostu otwórz okno ładowania i wybierz Załaduj do, Tylko utwórz połączenie, kliknij OK. Doskonały. Teraz powtórzymy to dla drugiego elementu - danych, z pliku,Ze skoroszytu wybierz DavidTwo, wybierz nazwę arkusza, a następnie otwórz obciążenie, Załaduj do, Tylko utwórz połączenie. Zobaczysz tutaj, w tym panelu, mamy oba połączenia. W porządku.
Teraz właściwa praca - dane, pobierz dane, połącz zapytania, scal, a następnie w oknie dialogowym scalania wybierz DavidOne, DavidTwo, a następny krok jest całkowicie nieintuicyjny. Musisz to zrobić. Wybierz wspólną kolumnę lub kolumny - czyli Produkt i Produkt. W porządku. A potem bądź tutaj bardzo ostrożny z typem łączenia. Chcę wszystkie wiersze z obu, ponieważ jeden może mieć dodatkowy wiersz i muszę to zobaczyć, a następnie klikamy OK. W porządku. A oto początkowy wynik. Nie wygląda na to, żeby zadziałało; nie wygląda na to, że dodał dodatkowe elementy, które były w pliku 2. I mamy tę kolumnę 5 - teraz jest pusta. Kliknę prawym przyciskiem myszy kolumnę 5 i powiem: Usuń tę kolumnę. Więc otwórz tę ikonę rozwijania i odznacz to pole dla Użyj oryginalnej nazwy kolumny jako prefiksu i BAM! to działa. Zatem dodatkowe elementy, które były w pliku 2, a których nie ma w pliku 1,pojawiają się.
W porządku. W dzisiejszym pliku wygląda na to, że ta kolumna Kod produktu jest lepsza niż ta kolumna Produkt, ponieważ ma dodatkowe wiersze. Ale może nadejść dzień w przyszłości, w którym Workbook 1 będzie zawierał rzeczy, których Workbook 2 nie ma. Więc zostawię tam oba i nie zamierzam pozbyć się żadnych wartości zerowych, ponieważ chociażby ten wiersz na dole wydaje się być całkowicie pusty, w przyszłości może dojść do sytuacji, w której mamy tutaj kilka wartości zerowych, ponieważ czegoś brakuje. W porządku? W końcu Close & Load i mamy szesnaście wierszy.
Powiedzmy, że w przyszłości coś się zmieni. W porządku, więc wrócimy do jednego z tych dwóch plików i zmienię klasę dla Apple na 99, a nawet wstawimy coś nowego i zapiszemy ten skoroszyt. W porządku. A potem, jeśli chcemy, aby nasz plik scalający zaktualizował się, podejdź tutaj - teraz uważaj, kiedy robisz to po raz pierwszy, nie widzisz ikony Odśwież - musisz złapać ten pasek i przeciągnąć go . I zrobimy Odśwież, a 17 rzędów załadowanych, pojawi się arbuz, Apple zmieni się na 99 - to piękna rzecz. Hej, czy chcesz dowiedzieć się więcej o dodatku Power Query? Kup tę książkę autorstwa Kena Pulsa i Miguela Escobara, M jak (DANE) MONKEY. Pozwolę ci przyspieszyć.
Podsumowanie dzisiaj: David z Florydy ma dwa zeszyty ćwiczeń, które chce połączyć; oba mają te same pola w kolumnie A, ale wszystkie pozostałe kolumny są różne; jeden skoroszyt może zawierać dodatkowe elementy, których nie ma w drugim, a Dawid ich potrzebuje; w żadnym z plików nie ma duplikatów; zamierzamy rozwiązać ten problem za pomocą zapytania wspomagającego, więc zacznij od nowego pustego skoroszytu na pustym arkuszu; masz zamiar wykonać trzy zapytania, pierwsze - Dane, Z pliku, Skoroszyt, a następnie Załaduj tylko do utworzonego połączenia; to samo z drugim skoroszytem, a następnie Dane, Pobierz dane, Scal, wybierz dwa połączenia, wybierz kolumnę, która jest wspólna w obu - w moim przypadku Produkt - a następnie z Typ połączenia chcesz połączyć pełne wszystko z pliku 1, wszystko z pliku 2. A potem najpiękniejsze jest to, że zmieniają się podstawowe dane,możesz po prostu odświeżyć zapytanie.
Aby pobrać skoroszyt z dzisiejszego wideo, odwiedź adres URL w opisie YouTube.
Cóż, hej, chcę jak David za przybycie na moje seminarium, chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobierz plik Excel
Aby pobrać plik Excela: connect-based-on-common-column.xlsx
Power Query to niesamowite narzędzie w programie Excel.
Myśl dnia Excela
Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia:
„Zawsze naciskaj F4 podczas odczytywania zakresu lub macierzy w funkcji”
Tanja Kuhn
