Jak połączyć dane z dwóch arkuszy programu Excel

W poniedziałek do Twojej skrzynki odbiorczej trafiają dwa pliki. Jeden zawiera zamówienia z zeszłego miesiąca, a drugi – z bieżącego, a ktoś z kierownictwa chce mieć gotowy raport jeszcze przed obiadem. Otwierasz więc oba pliki, zaczynasz kopiować i wklejać, a w połowie pracy zdajesz sobie sprawę, że kolumny nawet nie są tak samo wyrównane. Brzmi znajomo?

Oto kwestia, o której prawie nikt nie wspomina od razu. Za sformułowaniem„połączenie danych z dwóch arkuszy w programie Excel” kryją się dwa zupełnie różne zadania, a wybór niewłaściwego z nich oznacza zmarnowanie całego popołudnia. Jedno polega na ułożeniu wierszy jeden nad drugim. Drugie polega na dopasowaniu kolumn obok siebie przy użyciu wspólnego identyfikatora. Jeśli dobrze zdiagnozujesz problem, sama praca zajmie tylko kilka minut.

Chodzi o to, że ten przewodnik najpierw dzieli zadania według celu, a następnie podaje odpowiednie narzędzie do każdego z nich. Nie trzeba zgadywać.

Jak połączyć dane z dwóch arkuszy w programie Excel: błąd, który popełnia każdy na początku

Większość poradników wybiera jedną metodę i skupia się wyłącznie na niej. Przez sześć akapitów omawiają funkcję VLOOKUP albo pokazują Power Query i w ogóle nie wspominają o funkcjach wyszukiwania. Połowa czytelników trafia na niewłaściwą stronę i opuszcza ją jeszcze bardziej zdezorientowana niż przedtem.

Prawdziwym problemem nie jest samo narzędzie. Chodzi o to, że ludzie kopiują i wklejają dane ręcznie, bo nigdy nie zastanowili się, w jakiej formie te dane są zapisane. A ręczne wklejanie prowadzi do błędów, które łatwo przeoczyć – tu przypadkowo pusty wiersz, tam kolumna przesunięta o jedno miejsce dalej – i nagle suma jest o kilka tysięcy mniejsza, a nikt nie wie, dlaczego.

Zanim więc zrobisz cokolwiek innego, przeprowadź tę szybką kontrolę.

• Dwa arkusze, te same kolumny, różne rekordy (zamówienia ze stycznia + zamówienia z lutego)? Musisz dołączyć wiersze. To zadanie dla Power Query.
• Dwa arkusze, wspólna kolumna z identyfikatorami, różne szczegóły (identyfikatory zamówień w jednym, adresy e-mail klientów w drugim)? Musisz dopasować kolumny. To zadanie dla funkcji XLOOKUP lub INDEX/MATCH.

To jedno pytanie decyduje o wszystkim, co nastąpi później. Jeśli udzielisz na nie błędnej odpowiedzi, żadne umiejętności w zakresie formuł ci nie pomogą.

Zadanie nr 1: Dodawanie wierszy za pomocą Power Query

Jeśli chcesz połączyć dwa arkusze Excela, układając jeden pod drugim, najprostszym rozwiązaniem jest użycie Power Query. Jest wbudowany w program Excel (zakładka „Dane”, od wersji Excel 2016) i aktualizuje się, więc w przyszłym miesiącu wystarczy wstawić nowe dane i kliknąć jeden przycisk zamiast odbudowywać całość od nowa.

Dokumentacja firmy Microsoft omawia podstawy łączenia danych z wielu arkuszy, ale proces dołączania wygląda konkretnie w następujący sposób.

Najpierw przekształć każdy arkusz w prawidłową tabelę. Kliknij w dowolnym miejscu danych, naciśnij Ctrl+T, zaznacz pole wyboru „Nagłówki”. Zrób to dla obu arkuszy. Nadaj im nazwy, które będziesz w stanie rozpoznać, np. „ tblJan ” i „ tblFeb ” w sekcji „Projekt tabeli”.

Następnie załaduj każdą z nich do Power Query. Umieść kursor w pierwszej tabeli i przejdź do sekcji „Dane” > Z tabeli/zakresu. Otworzy się edytor. Nie musisz tu niczego zmieniać – po prostu zamknij okno i załaduj dane wyłącznie jako połączenie (Plik > Zamknij i załaduj do > (Tylko „Utwórz połączenie”). Powtórz tę czynność dla drugiej tabeli.

A teraz samo dołączanie. Dane > Pobierz dane > Łączenie zapytań > Dołącz. Wybierz dwa zapytania i kliknij „OK”. Power Query umieszcza wiersze drugiej tabeli bezpośrednio pod pierwszą, dopasowując kolumny na podstawie nazw nagłówków.

Wczytaj wynik do nowego arkusza. Gotowe. A największą zaletą metody dołączania danych w Excelu za pomocą Power Query jest funkcja odświeżania. Gdy następnym razem otrzymasz zaktualizowane pliki, wklej nowe wiersze do tabel źródłowych i kliknij „Dane” > „Odśwież wszystko”. Połączona tabela zaktualizuje się automatycznie.

„Dodawanie arkuszy nie ma w ogóle nic wspólnego z formułami. Chodzi o to, by poinformować program Excel, że te dwa zestawy danych są tego samego rodzaju, a następnie pozwolić mu na ich comiesięczne łączenie na żądanie”.

Jeśli chcesz połączyć więcej niż dwa arkusze, w przewodniku Trump Excel dotyczącym łączenia arkuszy znajdziesz szczegółowy opis procedury dla wersji z wieloma arkuszami. Zasada działania jest taka sama, różnica polega jedynie na większej liczbie zapytań w liście do dołączania.

Zadanie nr 2: Dopasowanie kolumn za pomocą funkcji XLOOKUP lub INDEX/MATCH

To zadanie ma nieco inny charakter. Dwa arkusze opisują te same rekordy, ale zawierają różne kolumny z danymi na ich temat. Arkusz pierwszy zawiera identyfikatory zamówień i kwoty. Arkusz drugi zawiera te same identyfikatory zamówień oraz adresy e-mail i regiony klientów. Chcesz uzyskać jedną tabelę, w której wszystkie te dane będą ułożone obok siebie.

Mamy tu do czynienia z sytuacją, w której funkcja VLOOKUP jest używana między arkuszami, z tym że VLOOKUP to przestarzała i zawodna metoda. Funkcja XLOOKUP zastąpiła ją dla użytkowników pakietu Microsoft 365 lub programu Excel 2021 i warto z niej korzystać zamiast VLOOKUP.

Załóżmy, że główna tabela znajduje się w arkuszu Arkusz1, a identyfikatory zamówień są w kolumnie A. Adresy e-mail, które chcesz wykorzystać, znajdują się w arkuszu Arkusz2 – identyfikatory w kolumnie A, a adresy e-mail w kolumnie B. W arkuszu Arkusz1, komórka D2:

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not found")

Można to odczytać następująco: pobierz identyfikator z komórki A2, znajdź go w kolumnie „ID” w arkuszu Sheet2 i zwróć pasujący adres e-mail. Ten ostatni argument, „ "Not found" ”, pojawia się wtedy, gdy nie ma dopasowania — co jest o wiele lepszym rozwiązaniem niż brzydki komunikat „ #N/A ” wyświetlany przez funkcję VLOOKUP.

Przeciągnij to w dół kolumny, a każdy wiersz pobierze swój odpowiednik. Proste.

Używasz starszej wersji programu Excel bez funkcji XLOOKUP? Funkcje INDEX i MATCH spełniają tę samą rolę:

=INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0))

Funkcja „ 0 ” w funkcji MATCH wymusza dokładne dopasowanie, co prawie zawsze jest pożądane w przypadku identyfikatorów. Jeśli ją pominiesz, program Excel zastosuje dopasowanie przybliżone, co w przypadku niesortowanych danych po cichu zwraca błędne wartości. Klasyczny błąd, który pozostaje niezauważony.

Warto wiedzieć: Power Query również może wykonać to dopasowanie, korzystając z funkcji „Merge” zamiast „Append”. W przypadku jednorazowego raportu szybciej jest napisać formułę. Jednak w przypadku raportu generowanego co miesiąc, zawierającego tysiące wierszy, funkcja „Merge” w Power Query lepiej radzi sobie ze skalowalnością i nie spowolni pliku tak bardzo, jak zrobiłoby to dziesięć tysięcy formuł XLOOKUP.

Kiedy naprawdę chcesz skonsolidować

dane

w programie

Istnieje trzecia, mniej znana opcja, o której ludzie często zapominają. Jeśli Twoim celem jest skonsolidowanie danych w stylu programu Excel — czyli chcesz uzyskać sumy lub średnie z wielu arkuszy, a nie zachować każdy surowy wiersz — program Excel oferuje do tego specjalne narzędzie. Dane > Konsolidacja.

Wskaż oba zakresy, wybierz funkcję (Suma, Średnia, Liczba), a program dokona agregacji na podstawie pasujących etykiet wierszy i kolumn. Szczegółowy opis tego okna dialogowego znajdziesz w poradniku Spreadsheeto dotyczącym funkcji konsolidacji. To właściwy wybór, gdy nie potrzebujesz szczegółowych danych, a jedynie sumy — na przykład w celu połączenia regionalnych wyników sprzedaży w jedno podsumowanie.

Nie należy jednak korzystać z funkcji „Consolidate”, jeśli w rzeczywistości potrzebne są wszystkie wiersze w nienaruszonym stanie. Funkcja ta agreguje dane, przez co traci się szczegółowość. To zadanie typu „append”, które pełni nieodpowiednią rolę.

Rozwiązywanie problemów z kombajnem

„
” – Problemy pojawiają się w przewidywalny sposób. Oto lista typowych usterek i sposobów ich usuwania.

Powtarzające się nagłówki po dodaniu danych prawie zawsze oznaczają, że nazwy kolumn w obu tabelach źródłowych nie są zgodne. Cofnij się, zmień nazwy tak, aby były dokładnie takie same – łącznie ze spacjami i wielkimi literami – a następnie odśwież stronę.

Wyszukiwania zwracające komunikat „ #N/A ” dla wierszy, o których wiesz, że istnieją? W dziewięciu na dziesięć przypadków przyczyną jest niezgodność typów danych. W jednym arkuszu identyfikator jest zapisany jako tekst, a w drugim jako liczba. Na ekranie wyglądają identycznie, ale program Excel traktuje „1024” i 1024 jako różne wartości. Przekonwertuj dane po jednej stronie, aby oba typy były zgodne.

Dodatkowe puste wiersze, które pojawiają się w połączonej tabeli, zazwyczaj wynikają z pustych wierszy w pierwotnym zakresie danych, które zostały uwzględnione podczas łączenia. Należy je odfiltrować u źródła przed połączeniem.

A jeśli po wykonaniu dużego zadania wyszukiwania plik zacznie działać bardzo wolno, to znak, że warto przejść na Power Query. Tysiące zmiennych formuł przeliczających się przy każdej edycji mogą unieruchomić nawet całkiem wydajny komputer. Firma Ablebits udostępnia dobry przegląd bardziej wymagających scenariuszy scalania, jeśli masz do czynienia z wieloma dużymi skoroszytami.

Najczęściej zadawane pytania

Czy do połączenia dwóch arkuszy powinienem użyć Power Query, czy formuły?
Zależy to od tego, czy jest to zadanie jednorazowe, czy cykliczne. W przypadku pojedynczego raportu, do którego już nigdy nie wrócisz, szybciej będzie napisać formułę. W przypadku zadań, które odtwarzasz co miesiąc, lepszym rozwiązaniem jest Power Query, ponieważ pozwala na odświeżenie danych jednym kliknięciem, zamiast konieczności ponownego tworzenia formuł za każdym razem.

Jaka jest różnica między dołączaniem a scalaniem?
Funkcja „Dołączanie” układa wiersze jeden pod drugim, gdy oba arkusze zawierają ten sam rodzaj rekordów. Funkcja „Scalanie” (lub „Wyszukiwanie”) dopasowuje kolumny obok siebie na podstawie wspólnego identyfikatora, gdy arkusze zawierają różne szczegóły dotyczące tych samych rekordów. To zupełnie różne operacje.

Czy funkcja XLOOKUP jest lepsza od VLOOKUP przy łączeniu arkuszy?
Tak, jeśli ją masz. Funkcja XLOOKUP obsługuje wbudowany komunikat „nie znaleziono”, przeszukuje dane w lewo lub w prawo i nie przestaje działać, gdy ktoś wstawi kolumnę. Funkcja VLOOKUP sprawdza się w przypadku starszych plików, ale nie ma powodu, aby rozpoczynać nową pracę z jej użyciem w programie Excel 2021 lub 365.

Dlaczego moje wyszukiwanie zwraca błędy, skoro dane wyraźnie się zgadzają?
Prawie zawsze chodzi o niezgodność typów danych. W jednym arkuszu klucz jest zapisany jako tekst, a w drugim jako liczba. Wyglądają tak samo, ale program Excel nie traktuje ich jako identyczne. Wystarczy ujednolicić format po obu stronach, a problem z dopasowaniem zostanie rozwiązany.

Czy mogę połączyć arkusze z dwóch różnych skoroszytów?
Tak. Power Query łączy się z plikami zewnętrznymi za pomocą funkcji „Dane” > „Pobierz dane” > „Z pliku” > „Ze skoroszytu”. Po załadowaniu obu zapytań kroki „dodaj” i „połącz” działają dokładnie tak samo.

Podsumowanie:

„
” Połączenie dwóch arkuszy nie jest trudne, gdy przestanie się traktować to jako jeden problem. Najpierw zadaj sobie jedno pytanie: czy chodzi o ułożenie identycznych rekordów jeden pod drugim, czy o dopasowanie identycznych identyfikatorów? W pierwszym przypadku użyj funkcji „Dołącz” w Power Query, w drugim — funkcji XLOOKUP lub INDEX/MATCH, a jeśli potrzebujesz tylko sum, skorzystaj z funkcji „Konsoliduj”. Łącząc dane z dwóch arkuszy w programie Excel, najpierw zdiagnozuj problem, a dopiero potem przystąp do działania. Taka kolejność pozwoli Ci zaoszczędzić całe popołudnie.