Jak scalić dane z kilku skoroszytów programu Excel. Załóżmy, że masz folder pełen plików programu Excel. Każdy ma jeden arkusz roboczy z różną liczbą wierszy. Możesz zaimportować wszystkie pliki w folderze do jednego skoroszytu programu Excel.
Obejrzyj wideo
- Ponowne odwiedzanie czystych danych dzięki podcastowi Power Query nr 2037
- Dodatek Power Query może teraz łączyć wszystkie pliki programu Excel w folderze.
- Ulepszono: automatycznie usuwają nagłówki ze wszystkich plików oprócz pierwszego.
- Możesz wybrać, który plik zostanie użyty jako plik przykładowy.
- Wybierz arkusz roboczy, tabelę lub nazwany zakres do zaimportowania
- Użyj edytora zapytań, aby wykonać dowolne przekształcenia
- Zamknij i wczytaj, aby połączyć wszystkie pliki
- Później odśwież zapytanie, aby je zaktualizować
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2077: Scalanie skoroszytów
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Zaledwie 40 odcinków temu, gdzie mówiono o czyszczeniu danych za pomocą dodatku Power Query, o tym, jak pobrać całą masę plików CSV i przenieść je do jednego pliku Excel. Cóż, ta funkcja właśnie stała się znacznie lepsza.
Więc mam tutaj dzisiaj folder, aw tym folderze jest cała seria miesięcznych plików Excela, z których 22 przez prawie dwa lata. Muszę połączyć wszystkie te pliki. Teraz wszystkie mają ten sam układ kolumn, dobrze? Więc wszystkie mają 9 kolumn, wszystkie mają takie same nagłówki i tak dalej, w porządku. Więc połączymy to wszystko bez makra.
Jeśli korzystasz z programu Excel 2016, przejdziesz do karty Dane, aby pobrać i przekształcić dane. Jeśli korzystasz z programu Excel 2010 lub 2013, zamierzasz wyjść i pobrać świetny produkt firmy Microsoft o nazwie Power Query. Możesz pobrać całość i zainstalować ją w mniej niż minutę, w porządku.
Więc przyjdziemy tutaj, aby uzyskać dane z pliku, ale nie z jednego skoroszytu. Chcemy go pobrać z takiego folderu. W porządku, oto one. Proszą cię o przejście do folderu, ale mam zamiar kliknąć tam i wkleić Ctrl + V, ponieważ wkleiłem katalog, kliknij OK. Więc pokazali mi podgląd danych, które mam. Otwieram to małe menu Połącz tutaj i wybieram Połącz i edytuj. W porządku, a następnie krok tutaj polega na tym, że wybieramy jeden plik, który jest dobrym, reprezentatywnym plikiem, w porządku, a oni wybierają tylko pierwszy, który jest alfabetyczny. Nie ma znaczenia, który wybierzesz. A potem to przykładowe zapytanie, które tworzymy z pierwszego wybranego pliku, w tym przypadku chcę Sheet1.Jeśli masz wiele arkuszy, możesz powiedzieć Arkusz2 lub arkusz o nazwie Zestawienie dochodów lub Zakres nazw lub tabela. Pokazali mi, jak to wygląda i klikam OK.
Na tym ekranie dużo się dzieje. W rzeczywistości utworzyli tutaj przykładowe zapytanie, ahh Przykładowe zapytanie, a następnie utworzyli to jako funkcję i uruchomili tę funkcję na wszystkim w folderze. Dodają nową kolumnę, która mówi mi, skąd pochodzi plik, aw tym konkretnym przypadku już wiem, skąd się wziął, ponieważ mamy tutaj wypełnioną datę. Więc nie potrzebuję tego, mam zamiar kliknąć prawym przyciskiem i usunąć to. Może są tam jakieś dane, których nie potrzebuję. Może te dane trafią do klienta, więc nie chcę pokazywać Zysk, więc mogę usunąć tę kolumnę, a nawet usunąć kolumnę Koszt, a może jest kilka irytujących rzeczy, takich jak, wiesz, używali złych nagłówków, a ja chcesz zmienić nazwy tych nagłówków. Więc kliknij prawym przyciskiem myszy i zmień nazwę, nazwiemy to QTY dla ilości.Może sprawy są w złej kolejności, może chcę wziąć ten przedmiot i przenieść go w inne miejsce, dobrze? Tylko te wszystkie małe irytujące rzeczy, które możesz mieć i oczywiście tutaj wykonują wszystkie zastosowane kroki, a następnie możesz kontynuować wykonywanie wszystkich czynności Power Query, które musisz zrobić. Ale w tym przypadku jest to po prostu proste. Chcę tylko pobrać dane. Zamierzam więc zamknąć i załadować, minęło kilka sekund i gotowe. Mam swój finał. Posortujmy to tutaj według daty; widzisz, idziemy od stycznia 2018 aż do października 2019. Jakie to fajne.wykonujesz wszystkie zastosowane kroki, a następnie możesz kontynuować wykonywanie wszystkich czynności w dodatku Power Query, które musisz wykonać. Ale w tym przypadku jest to po prostu proste. Chcę tylko pobrać dane. Zamierzam więc zamknąć i załadować, minęło kilka sekund i gotowe. Mam swój finał. Posortujmy to tutaj według daty; widzisz, idziemy od stycznia 2018 aż do października 2019. Jakie to fajne.wykonujesz wszystkie zastosowane kroki, a następnie możesz kontynuować wykonywanie wszystkich czynności w dodatku Power Query, które musisz wykonać. Ale w tym przypadku jest to po prostu proste. Chcę tylko pobrać dane. Zamierzam więc zamknąć i załadować, minęło kilka sekund i gotowe. Mam swój finał. Posortujmy to tutaj według daty; widzisz, idziemy od stycznia 2018 do października 2019. Jakie to fajne.
Oto niesamowita, niesamowita rzecz w tym. Powiedzmy, że coś się zmienia w naszym folderze. Więc wróć tutaj, w plikach miesiąca, dostaję dwa nowe pliki, Wklej, a teraz są tutaj jeszcze dwa rekordy, dwa kolejne pliki Excela, które muszę połączyć. Po prostu wróć do mojego pliku Excel, który obecnie ma 516 wierszy, znajdź MonthFiles i kliknij przycisk Odśwież. I tak szybko mam teraz nowe płyty z listopada i grudnia. Power Query to niesamowite nowe narzędzie bezpłatne dla programu Excel 2010, bezpłatne dla programu Excel 2013. To było piękne, gdy można było łączyć pliki CSV, ale teraz, gdy mogą również łączyć pliki Excela, po prostu fenomenalne.
Dlatego zaktualizowałem moją książkę, Power Excel, o wydanie 2017, które zawiera Power Query, Power BI i wszystkie nowe narzędzia. Sprawdź to. Kliknij „i” w prawym górnym rogu.
W porządku, podsumowanie tego odcinka: wracamy do Czystych danych z podcastem Power Query nr 2037, w którym łączymy wiele plików CSV. Możemy teraz użyć tego samego narzędzia, ulepszonej wersji tego narzędzia, do połączenia wszystkich plików Excela w folderze. Kolejne ulepszenie, zmiana z 2037 roku, automatycznie usuwa nagłówki ze wszystkich plików oprócz pierwszego i promuje te nagłówki w pierwszym pliku. Możesz wybrać, który plik zostanie użyty jako plik przykładowy. W moim przypadku użyłem stycznia. Praca - wybierz arkusz roboczy, tabelę lub nazwany zakres do zaimportowania, a następnie wykonaj dowolne przekształcenia w edytorze zapytań. Klikamy Zamknij i załaduj, połączy wszystkie pliki. A następnie, jeśli w folderze zostanie upuszczonych więcej plików lub niektóre pliki zostaną zabrane lub niektóre pliki ulegną zmianie, po prostu odśwież zapytanie, a wszystkie te kroki zostaną ponownie wykonane. Power Query,absolutnie niesamowite narzędzie.
Chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2077.xlsm
