Zawsze używałem Excela do szybkich obliczeń i tworzenia prostych tabel. Ale poza popularnymi formułami i podstawowymi technikami manipulacji danymi, nigdy nie czułem potrzeby uczenia się dodatkowych funkcji Excela – dopóki moje projekty nie stały się bardziej złożone.
Szybkie linki
Problem, który w końcu sprawił, że zwróciłem na niego uwagę
Ze względu na szereg czynników rynkowych i cła importowe, zakup podzespołów komputerowych w mojej okolicy jest często droższy niż w Stanach Zjednoczonych. Chciałem wiedzieć, o ile więcej płacę za te same podzespoły i czy lepiej zamawiać je bezpośrednio z Amazon lub Newegg zamiast u lokalnych sprzedawców. Zebrałem więc dane dotyczące cen głównych podzespołów komputerowych (procesorów, kart graficznych i pamięci RAM) z kilku miesięcy, które zazwyczaj importują lokalne sklepy. Prosty projekt śledzenia, prawda? Nieprawda.
Szybko skończyłem z kompletnym bałaganem danych. Każdy sprzedawca eksportował swoje dane, używając różnych konwencji formatowania, co praktycznie uniemożliwiało scalanie plików. Amazon podawał daty w formacie MM/DD/RRRR, Newegg używał formatu RRRRMMDD, a Shopee (mój lokalny sklep) używał formatu DD-MM-RRRR.
Niespójności na tym się nie kończyły. Nazwy kolumn były bardzo zróżnicowane. Newegg oznaczał ceny jako „retail_price”, Amazon używał „unit_price_usd”, a Shopee „price_php”. Formatowanie cen było równie problematyczne – niektóre pliki wyświetlały „₱18,600 320” wraz z symbolami walut, podczas gdy inne wyświetlały zwykłe liczby, takie jak „XNUMX”. Nawet nazwy marek nie były spójne, pojawiając się jako „gigabyte”, „GIGABYTE INC.” lub „Gigabyte Tech” dla tego samego producenta w różnych plikach.
Ręczne czyszczenie i scalanie tych danych zajęło mi już wiele godzin. Musiałem kopiować i wklejać między plikami, wyszukiwać i zastępować niespójne wartości oraz usuwać puste wiersze jeden po drugim. Konwersja PHP na USD w celu porównania cen oznaczała ciągłe sprawdzanie kursów walut na innym ekranie. Ogólnie rzecz biorąc, praca była żmudna, podatna na błędy i prawie mnie zniechęciła.
Wtedy w końcu pomyślałem o wykorzystaniu jednej z funkcji, o której zawsze mówią entuzjaści Excela – Power Query. Wiele innych zaawansowanych funkcji oferowanych przez program ExcelSłyszałem jednak, że Power Query to idealne narzędzie do rozwiązania mojego konkretnego problemu. Po obejrzeniu kilku samouczków na YouTube od razu zdałem sobie sprawę, ile czasu mógłbym zaoszczędzić, gdybym zaczął używać Edytora Power Query do uporządkowania wszystkich nieuporządkowanych danych zebranych z internetu. Dzięki Power Query mogę teraz łatwo importować dane z różnych źródeł, konwertować je do standardowego formatu i efektywnie analizować, oszczędzając cenny czas i wysiłek w moich projektach analizy cen podzespołów komputerowych.
Jak używać narzędzia Power Query do oczyszczania niestrukturalnych danych?
Po pewnym czasie zdecydowałem się na prosty, krok po kroku proces w edytorze Power Query. Oto jak uporządkowałem moje nieuporządkowane eksporty CSV i przekształciłem je w spójny, dobrze zorganizowany arkusz kalkulacyjny.
Najpierw zaimportowałem dane do edytora Power Query, otwierając pusty skoroszyt i klikając Dane Na wstążce wybierz Z tekstu/CSVNastępnie wybrałem plik CSV i kliknąłem Przekształć dane Aby otworzyć go za pomocą edytora Power Query.
Zacząłem od poprawienia kolumny z datą. Ponieważ zbierałem dane z dwóch źródeł z 12-godzinną różnicą czasu, musiałem ujednolicić daty. Okazało się to całkiem proste. Zdefiniowałem kolumnę Data, kliknij prawym przyciskiem myszy, aby otworzyć menu kontekstowe i wybierz Zmień typ > Używanie ustawień regionalnychW menu podręcznym ustawiłem typ na Data I to zostało zdeterminowane Angielskie Stany Zjednoczone) Aby zapewnić spójne formatowanie, dodatek Power Query automatycznie rozpoznaje różne formaty, takie jak MM/DD/RRRR, RRRR/MM/DD, oraz zmienne używające symboli, takich jak DD-MM-RR, a następnie łączy je wszystkie w jeden format daty.
Teraz, gdy ustaliłem już format daty, pozostało mi tylko uporządkowanie kolumny. tam Różne sposoby czyszczenia arkusza kalkulacyjnego w programie ExcelPonieważ jednak wszystkie błędy wynikały z błędnych wpisów wygenerowanych przez mój program do scrapowania, zdecydowałem się po prostu na zastosowanie filtra. Usuń błędy Aby usunąć te wpisy. Ten krok pozwolił mi usunąć wartości null i wszelkie pozostałe problematyczne dane, które nie zostały prawidłowo zarejestrowane, dzięki czemu we wszystkich moich plikach pojawiły się czyste, spójne daty.
Następnie zająłem się bałaganem związanym z nazwą marki, nadając jej pewną funkcję. Zastąp wartościTak jak poprzednio, wybrałem kolumnę docelową, a następnie kliknąłem prawym przyciskiem myszy, aby otworzyć menu kontekstowe i wybrałem Zastąp wartościW oknie, które się pojawi, wprowadź w polu nieprawidłową wartość. Wartość do znalezienia i moja standardowa wartość w polu Zamień na pole.
Zrobiłem to jeszcze dwa razy i w końcu przekonwertowałem wszystkie wpisy „gigabyte” i „GIGABTYE Inc.” na jeden, spójny „GIGABYTE” we wszystkich moich plikach. To samo zrobiłem z AMD i teraz cała kolumna „Marka” dla GPU używa standardowych nazw marek.
Power Query: Jak zaoszczędziło mi to wiele godzin pracy
Jednym z powodów, dla których unikałem Power Query, było to, że myślałem, że to kolejna złożona funkcja, której opanowanie zajęłoby dużo czasu. Okazało się jednak, że jest o wiele łatwiejsza, niż się spodziewałem. Zamiast uruchamiać niezliczone polecenia „znajdź i zamień”, mogę użyć Power Query do szybkiego i automatycznego czyszczenia danych z moich narzędzi do gromadzenia danych.
Najbardziej zaskoczyło mnie w Power Query to, że każde polecenie, które wykonywałem, było rejestrowane i można je było powtarzać w kółko. To w zasadzie daje zautomatyzowany skrypt czyszczący, który może zamienić chaotyczne pliki CSV w czyste, uporządkowane arkusze kalkulacyjne – idealne, jeśli pracujesz nad… Twórz niestandardowe zestawy danych za pomocą web scrapinguponieważ narzędzia te często generują nieczytelne dane.
Dla każdego, kto zmaga się z cyklicznym oczyszczaniem danych, niespójnymi formatami lub wieloma źródłami danych, Power Query przekształca te obciążenia w prosty, zautomatyzowany proces. Zamiast poświęcać godziny tygodniowo na ręczne poprawki, możesz po prostu kliknąć „Odśwież” i rozpocząć analizę. To funkcja programu Excel, którą chciałbym wdrożyć już dawno temu. Gdy raz przekonasz się o mocy zautomatyzowanego, powtarzalnego skryptu czyszczącego, nie będziesz chciał już wracać. Power Query to potężne narzędzie oszczędzające czas i wysiłek w przetwarzaniu danych, oferujące zaawansowane rozwiązania do efektywnego oczyszczania i transformacji danych.
