W poniedzia\u0142ek do Twojej skrzynki odbiorczej trafiaj\u0105 dwa pliki. Jeden zawiera zam\u00f3wienia z zesz\u0142ego miesi\u0105ca, a drugi \u2013 z bie\u017c\u0105cego, a kto\u015b z kierownictwa chce mie\u0107 gotowy raport jeszcze przed obiadem. Otwierasz wi\u0119c oba pliki, zaczynasz kopiowa\u0107 i wkleja\u0107, a w po\u0142owie pracy zdajesz sobie spraw\u0119, \u017ce kolumny nawet nie s\u0105 tak samo wyr\u00f3wnane. Brzmi znajomo?<\/p>\n
Oto kwestia, o kt\u00f3rej prawie nikt nie wspomina od razu. Za sformu\u0142owaniem\u201epo\u0142\u0105czenie danych z dw\u00f3ch arkuszy w programie Excel<\/strong>\u201d kryj\u0105 si\u0119 dwa zupe\u0142nie r\u00f3\u017cne zadania, a wyb\u00f3r niew\u0142a\u015bciwego z nich oznacza zmarnowanie ca\u0142ego popo\u0142udnia. Jedno polega na u\u0142o\u017ceniu wierszy jeden nad drugim. Drugie polega na dopasowaniu kolumn obok siebie przy u\u017cyciu wsp\u00f3lnego identyfikatora. Je\u015bli dobrze zdiagnozujesz problem, sama praca zajmie tylko kilka minut.<\/p>\n Chodzi o to, \u017ce ten przewodnik najpierw dzieli zadania wed\u0142ug celu, a nast\u0119pnie podaje odpowiednie narz\u0119dzie do ka\u017cdego z nich. Nie trzeba zgadywa\u0107.<\/p>\n Wi\u0119kszo\u015b\u0107 poradnik\u00f3w wybiera jedn\u0105 metod\u0119 i skupia si\u0119 wy\u0142\u0105cznie na niej. Przez sze\u015b\u0107 akapit\u00f3w omawiaj\u0105 funkcj\u0119 VLOOKUP albo pokazuj\u0105 Power Query i w og\u00f3le nie wspominaj\u0105 o funkcjach wyszukiwania. Po\u0142owa czytelnik\u00f3w trafia na niew\u0142a\u015bciw\u0105 stron\u0119 i opuszcza j\u0105 jeszcze bardziej zdezorientowana ni\u017c przedtem.<\/p>\n Prawdziwym problemem nie jest samo narz\u0119dzie. Chodzi o to, \u017ce ludzie kopiuj\u0105 i wklejaj\u0105 dane r\u0119cznie, bo nigdy nie zastanowili si\u0119, w jakiej formie te dane s\u0105 zapisane. A r\u0119czne wklejanie prowadzi do b\u0142\u0119d\u00f3w, kt\u00f3re \u0142atwo przeoczy\u0107 \u2013 tu przypadkowo pusty wiersz, tam kolumna przesuni\u0119ta o jedno miejsce dalej \u2013 i nagle suma jest o kilka tysi\u0119cy mniejsza, a nikt nie wie, dlaczego.<\/p>\n Zanim wi\u0119c zrobisz cokolwiek innego, przeprowad\u017a t\u0119 szybk\u0105 kontrol\u0119.<\/p>\n To jedno pytanie decyduje o wszystkim, co nast\u0105pi p\u00f3\u017aniej. Je\u015bli udzielisz na nie b\u0142\u0119dnej odpowiedzi, \u017cadne umiej\u0119tno\u015bci w zakresie formu\u0142 ci nie pomog\u0105.<\/p>\n Je\u015bli chcesz po\u0142\u0105czy\u0107 dwa arkusze Excela<\/strong>, uk\u0142adaj\u0105c jeden pod drugim, najprostszym rozwi\u0105zaniem jest u\u017cycie Power Query. Jest wbudowany w program Excel (zak\u0142adka \u201eDane\u201d, od wersji Excel 2016) i aktualizuje si\u0119, wi\u0119c w przysz\u0142ym miesi\u0105cu wystarczy wstawi\u0107 nowe dane i klikn\u0105\u0107 jeden przycisk zamiast odbudowywa\u0107 ca\u0142o\u015b\u0107 od nowa.<\/p>\n Dokumentacja firmy Microsoft omawia podstawy \u0142\u0105czenia danych z wielu arkuszy<\/a>, ale proces do\u0142\u0105czania wygl\u0105da konkretnie w nast\u0119puj\u0105cy spos\u00f3b.<\/p>\n Najpierw przekszta\u0142\u0107 ka\u017cdy arkusz w prawid\u0142ow\u0105 tabel\u0119. Kliknij w dowolnym miejscu danych, naci\u015bnij Ctrl+T, zaznacz pole wyboru \u201eNag\u0142\u00f3wki\u201d. Zr\u00f3b to dla obu arkuszy. Nadaj im nazwy, kt\u00f3re b\u0119dziesz w stanie rozpozna\u0107, np. \u201e Nast\u0119pnie za\u0142aduj ka\u017cd\u0105 z nich do Power Query. Umie\u015b\u0107 kursor w pierwszej tabeli i przejd\u017a do sekcji \u201eDane\u201d > Z tabeli\/zakresu. Otworzy si\u0119 edytor. Nie musisz tu niczego zmienia\u0107 \u2013 po prostu zamknij okno i za\u0142aduj dane wy\u0142\u0105cznie jako po\u0142\u0105czenie (Plik > Zamknij i za\u0142aduj do > (Tylko \u201eUtw\u00f3rz po\u0142\u0105czenie\u201d). Powt\u00f3rz t\u0119 czynno\u015b\u0107 dla drugiej tabeli.<\/p>\n A teraz samo do\u0142\u0105czanie. Dane > Pobierz dane > \u0141\u0105czenie zapyta\u0144 > Do\u0142\u0105cz. Wybierz dwa zapytania i kliknij \u201eOK\u201d. Power Query umieszcza wiersze drugiej tabeli bezpo\u015brednio pod pierwsz\u0105, dopasowuj\u0105c kolumny na podstawie nazw nag\u0142\u00f3wk\u00f3w.<\/p>\n Wczytaj wynik do nowego arkusza. Gotowe. A najwi\u0119ksz\u0105 zalet\u0105 metody do\u0142\u0105czania danych w Excelu za pomoc\u0105 Power Query<\/strong> jest funkcja od\u015bwie\u017cania. Gdy nast\u0119pnym razem otrzymasz zaktualizowane pliki, wklej nowe wiersze do tabel \u017ar\u00f3d\u0142owych i kliknij \u201eDane\u201d > \u201eOd\u015bwie\u017c wszystko\u201d. Po\u0142\u0105czona tabela zaktualizuje si\u0119 automatycznie.<\/p>\n \u201eDodawanie arkuszy nie ma w og\u00f3le nic wsp\u00f3lnego z formu\u0142ami. Chodzi o to, by poinformowa\u0107 program Excel, \u017ce te dwa zestawy danych s\u0105 tego samego rodzaju, a nast\u0119pnie pozwoli\u0107 mu na ich comiesi\u0119czne \u0142\u0105czenie na \u017c\u0105danie\u201d.<\/p><\/blockquote>\n Je\u015bli chcesz po\u0142\u0105czy\u0107 wi\u0119cej ni\u017c dwa arkusze, w przewodniku Trump Excel dotycz\u0105cym \u0142\u0105czenia arkuszy<\/a> znajdziesz szczeg\u00f3\u0142owy opis procedury dla wersji z wieloma arkuszami. Zasada dzia\u0142ania jest taka sama, r\u00f3\u017cnica polega jedynie na wi\u0119kszej liczbie zapyta\u0144 w li\u015bcie do do\u0142\u0105czania.<\/p>\n To zadanie ma nieco inny charakter. Dwa arkusze opisuj\u0105 te same rekordy, ale zawieraj\u0105 r\u00f3\u017cne kolumny z danymi na ich temat. Arkusz pierwszy zawiera identyfikatory zam\u00f3wie\u0144 i kwoty. Arkusz drugi zawiera te same identyfikatory zam\u00f3wie\u0144 oraz adresy e-mail i regiony klient\u00f3w. Chcesz uzyska\u0107 jedn\u0105 tabel\u0119, w kt\u00f3rej wszystkie te dane b\u0119d\u0105 u\u0142o\u017cone obok siebie.<\/p>\n Mamy tu do czynienia z sytuacj\u0105, w kt\u00f3rej funkcja VLOOKUP jest u\u017cywana mi\u0119dzy arkuszami<\/strong>, z tym \u017ce VLOOKUP to przestarza\u0142a i zawodna metoda. Funkcja XLOOKUP zast\u0105pi\u0142a j\u0105 dla u\u017cytkownik\u00f3w pakietu Microsoft 365 lub programu Excel 2021 i warto z niej korzysta\u0107 zamiast VLOOKUP.<\/p>\n Za\u0142\u00f3\u017cmy, \u017ce g\u0142\u00f3wna tabela znajduje si\u0119 w arkuszu Arkusz1, a identyfikatory zam\u00f3wie\u0144 s\u0105 w kolumnie A. Adresy e-mail, kt\u00f3re chcesz wykorzysta\u0107, znajduj\u0105 si\u0119 w arkuszu Arkusz2 \u2013 identyfikatory w kolumnie A, a adresy e-mail w kolumnie B. W arkuszu Arkusz1, kom\u00f3rka D2:<\/p>\n Mo\u017cna to odczyta\u0107 nast\u0119puj\u0105co: pobierz identyfikator z kom\u00f3rki A2, znajd\u017a go w kolumnie \u201eID\u201d w arkuszu Sheet2 i zwr\u00f3\u0107 pasuj\u0105cy adres e-mail. Ten ostatni argument, \u201e Przeci\u0105gnij to w d\u00f3\u0142 kolumny, a ka\u017cdy wiersz pobierze sw\u00f3j odpowiednik. Proste.<\/p>\n U\u017cywasz starszej wersji programu Excel bez funkcji XLOOKUP? Funkcje INDEX i MATCH spe\u0142niaj\u0105 t\u0119 sam\u0105 rol\u0119:<\/p>\n Funkcja \u201e Warto wiedzie\u0107: Power Query r\u00f3wnie\u017c mo\u017ce wykona\u0107 to dopasowanie, korzystaj\u0105c z funkcji \u201eMerge\u201d zamiast \u201eAppend\u201d. W przypadku jednorazowego raportu szybciej jest napisa\u0107 formu\u0142\u0119. Jednak w przypadku raportu generowanego co miesi\u0105c, zawieraj\u0105cego tysi\u0105ce wierszy, funkcja \u201eMerge\u201d w Power Query lepiej radzi sobie ze skalowalno\u015bci\u0105 i nie spowolni pliku tak bardzo, jak zrobi\u0142oby to dziesi\u0119\u0107 tysi\u0119cy formu\u0142 XLOOKUP.<\/p>\n dane<\/strong> <\/p>\n Istnieje trzecia, mniej znana opcja, o kt\u00f3rej ludzie cz\u0119sto zapominaj\u0105. Je\u015bli Twoim celem jest skonsolidowanie danych<\/strong> w stylu programu Excel<\/strong> \u2014 czyli chcesz uzyska\u0107 sumy lub \u015brednie z wielu arkuszy, a nie zachowa\u0107 ka\u017cdy surowy wiersz \u2014 program Excel oferuje do tego specjalne narz\u0119dzie. Dane > Konsolidacja.<\/p>\nJak po\u0142\u0105czy\u0107 dane z dw\u00f3ch arkuszy w programie Excel: b\u0142\u0105d, kt\u00f3ry pope\u0142nia ka\u017cdy na pocz\u0105tku<\/h2>\n
\n
Zadanie nr 1: Dodawanie wierszy za pomoc\u0105 Power Query<\/h2>\n
tblJan<\/code> \u201d i \u201e tblFeb<\/code> \u201d w sekcji \u201eProjekt tabeli\u201d.<\/p>\nZadanie nr 2: Dopasowanie kolumn za pomoc\u0105 funkcji XLOOKUP lub INDEX\/MATCH<\/h2>\n
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, \"Not found\")\n<\/code><\/pre>\n\"Not found\"<\/code> \u201d, pojawia si\u0119 wtedy, gdy nie ma dopasowania \u2014 co jest o wiele lepszym rozwi\u0105zaniem ni\u017c brzydki komunikat \u201e #N\/A<\/code> \u201d wy\u015bwietlany przez funkcj\u0119 VLOOKUP.<\/p>\n=INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0))\n<\/code><\/pre>\n0<\/code> \u201d w funkcji MATCH wymusza dok\u0142adne dopasowanie, co prawie zawsze jest po\u017c\u0105dane w przypadku identyfikator\u00f3w. Je\u015bli j\u0105 pominiesz, program Excel zastosuje dopasowanie przybli\u017cone, co w przypadku niesortowanych danych po cichu zwraca b\u0142\u0119dne warto\u015bci. Klasyczny b\u0142\u0105d, kt\u00f3ry pozostaje niezauwa\u017cony.<\/p>\n\n\n
\n \nMetoda<\/th>\n Wersja programu Excel<\/th>\n Najlepiej nadaje si\u0119 do<\/th>\n Na co nale\u017cy zwr\u00f3ci\u0107 uwag\u0119<\/th>\n<\/tr>\n<\/thead>\n \n XLOOKUP<\/td>\n 365 \/ 2021+<\/td>\n Wyszukiwanie wed\u0142ug identyfikatora, najprostsza sk\u0142adnia<\/td>\n Niedost\u0119pne w wersjach starszych ni\u017c 2021<\/td>\n<\/tr>\n \n INDEX\/MATCH<\/td>\n Wszystkie wersje<\/td>\n To samo zadanie, dzia\u0142a wsz\u0119dzie<\/td>\n Dwie funkcje do zagnie\u017cd\u017cania<\/td>\n<\/tr>\n \n VLOOKUP<\/td>\n Wszystkie wersje<\/td>\n Tylko pliki starszych wersji<\/td>\n Nieprawid\u0142owe dzia\u0142anie w przypadku zmiany po\u0142o\u017cenia kolumn<\/td>\n<\/tr>\n \n Scalanie w Power Query<\/td>\n 2016+<\/td>\n Du\u017ce zbiory danych, z mo\u017cliwo\u015bci\u0105 od\u015bwie\u017cania<\/td>\n Trudniejsza pierwsza konfiguracja<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n Kiedy naprawd\u0119 chcesz skonsolidowa\u0107<\/h2>\n
w programie<\/h2>\n