Przez lata korzystałem z Excela do prostych obliczeń i tworzenia prostych tabel. Chociaż korzystałem z popularnych formuł i wykonywałem proste operacje na danych, nigdy nie czułem potrzeby zagłębiania się w bardziej złożone funkcje Excela, dopóki moje projekty nie stały się coraz bardziej złożone.
Wyzwanie, które zapoczątkowało moją eksplorację
Rosnące koszty podzespołów komputerowych w moim regionie – często wyższe niż ceny w Stanach Zjednoczonych z powodu wahań rynkowych i podatków importowych – skłoniły mnie do zbadania różnic cenowych. Chciałem ustalić, czy bardziej opłacalne jest kupowanie bezpośrednio na platformach takich jak Amazon czy Newegg, zamiast u lokalnych sprzedawców detalicznych. W tym celu zebrałem dane cenowe z kilku miesięcy dotyczące podstawowych podzespołów komputerowych, w tym procesorów, kart graficznych i pamięci RAM. To, co początkowo wydawało mi się prostym zadaniem śledzenia, szybko przerodziło się w zniechęcające zadanie.
Zebrane przeze mnie dane były chaotyczne. Pliki każdego sprzedawcy detalicznego miały inny format, co utrudniało ich scalanie. Na przykład Amazon zapisywał daty w formacie MM/DD/RRRR, Newegg używał formatu RRRRMMDD, a mój lokalny sklep Shopee stosował standard DD-MM-RRRR.
Niespójności danych nie kończyły się na formatach dat. Nazwy kolumn były zupełnie inne: Newegg oznaczył ceny jako „retail_price”, Amazon wybrał „unit_price_usd”, a Shopee po prostu „price_php”.Co więcej, formatowanie cen było zróżnicowane – niektóre wpisy wyświetlały symbole walut, takie jak „₱18 600”, a inne były oznaczone zwykłymi liczbami, takimi jak „320”.Nawet nazwy marek były niespójne, pojawiając się w różnych formatach, takich jak „gigabyte”, „GIGABYTE INC.” czy „Gigabyte Tech”.
Ręczne czyszczenie i konsolidowanie tych danych stawało się wyczerpującym zajęciem. Spędzałem niezliczone godziny na kopiowaniu między plikami, zastępowaniu niespójnych wartości i skrupulatnym usuwaniu pustych wierszy. Dodatkowo, przeliczanie cen z PHP na USD wymagało ciągłego sprawdzania aktualnych kursów walut na drugim monitorze. Ten pracochłonny proces był żmudny i obarczony potencjalnymi błędami, co niemal doprowadziło mnie do porzucenia projektu.
Wtedy postanowiłem zapoznać się z funkcją, którą miłośnicy Excela często chwalą – Power Query. Chociaż Excel oferuje wiele zaawansowanych narzędzi, odkryłem, że Power Query rozwiązało moje problemy. Po obejrzeniu filmów instruktażowych na YouTube stało się dla mnie oczywiste, ile czasu mogę zaoszczędzić, wykorzystując Edytor Power Query do uporządkowania zebranych przeze mnie nieuporządkowanych danych.
Moje podejście krok po kroku do korzystania z Power Query
W mgnieniu oka opracowałem prostą procedurę w Edytorze Power Query, która pozwala uporządkować nieuporządkowane pliki CSV i przekonwertować je na spójny, uporządkowany arkusz kalkulacyjny.
Zacząłem od zaimportowania danych do Edytora Power Query. Otworzyłem nowy skoroszyt programu Excel, wybrałem „ Dane” ze wstążki i wybrałem „Z tekstu/CSV”. Po wybraniu pliku CSV kliknąłem „Przekształć dane”, aby przejść do Edytora Power Query.
Pierwszym krokiem była standaryzacja kolumny „Data”.Biorąc pod uwagę, że oba źródła miały osie czasu oddalone od siebie o 12 godzin, zapewnienie jednolitego formatowania dat było niezbędne. Ten proces okazał się niezwykle prosty. Zaznaczyłem kolumnę „Data”, kliknąłem prawym przyciskiem myszy i wybrałem „ Zmień typ > Używając ustawień regionalnych”. W wyświetlonym oknie ustawiłem typ na „Data” i wybrałem „Angielski (Stany Zjednoczone), aby zapewnić spójne formatowanie. Power Query sprawnie identyfikował różne formaty — MM/DD/RRRR, RRRR/MM/DD, DD-MM-RRRR — i z łatwością ujednolicił je do jednego formatu.
Po poprawieniu formatu daty przystąpiłem do czyszczenia kolumny. Ponieważ większość błędów wynikała z mojego narzędzia do scrapowania danych, użyłem filtra „Usuń błędy”, aby wyeliminować błędne wpisy. Ten krok skutecznie usunął wartości null i wszelkie pozostałe problematyczne dane, zapewniając spójną reprezentację daty we wszystkich plikach.
Następnie zająłem się chaotycznymi nazwami marek za pomocą funkcji „ Zamień wartości ”.Po wybraniu odpowiedniej kolumny, kliknąłem prawym przyciskiem myszy, otworzyłem „ Zamień wartości” i wprowadziłem oraz ujednoliciłem wszystkie warianty nazw marek, na przykład przekształcając „gigabyte” i „GIGABYTE INC.” w jeden spójny wpis: „GIGABYTE”.To samo podejście zastosowano w przypadku AMD, skutecznie usprawniając kolumnę „Marka” we wszystkich źródłach danych.
-
Zrzut ekranu autorstwa Jayrica Maninga – nie jest wymagane podanie źródła -
Zrzut ekranu autorstwa Jayrica Maninga – nie jest wymagane podanie źródła
Na koniec ujednoliciłem kolumnę „Cena”. Stanowiło to wyzwanie, ponieważ wiele wpisów to były po prostu liczby oderwane od kontekstu dotyczącego waluty, co komplikowało proces przeliczania. Na szczęście kolumna „Sklep” zapewniała natychmiastowy kontekst, umożliwiając proste przeliczanie cen z Shopee PH na USD.
Aby rozwiązać ten problem, najpierw użyłem funkcji „Zamień wartości”, aby wyeliminować wszystkie symbole walut (np.$, ₱, USD, PHP) z kolumny „Cena”.Po przekonwertowaniu tych wpisów na format dziesiętny poprzez wybranie opcji „ Typ zmiany > Dziesiętny”, utworzyłem nową kolumnę o nazwie „Cena USD” za pomocą opcji „ Kolumna niestandardowa ” na karcie „Kolumna”. Zaimplementowałem następującą formułę:
if [Store] = "Shopee PH"then Number. Round([Price] / 55, 2) else Number. Round([Price], 2)
Klikając OK, przekonwertowałem wszystkie ceny z Shopee PH na USD, aby umożliwić przejrzyste porównanie.
Formuła została stworzona w języku formuł Power Query M. Ten język skryptowy oferuje prostą składnię, która jest stosunkowo łatwa do zrozumienia. Nie trzeba znać całego języka, aby skutecznie z niego korzystać; odwołuję się do niego tylko w razie potrzeby, aby konstruować moje formuły.
Po poprawieniu danych po prostu zamknąłem Edytora usługi Power Query i kliknąłem przycisk Zachowaj, aby zapisać wszystkie zmiany wprowadzone w skoroszycie.
Oszczędność czasu dzięki Power Query
Początkowo wahałem się przed skorzystaniem z Power Query, obawiając się, że wprowadzi on kolejną skomplikowaną funkcję, która pochłonie zbyt wiele mojego czasu. Wbrew oczekiwaniom, okazało się to o wiele prostsze. Zamiast wykonywania niekończących się operacji znajdowania i zamieniania, Power Query pozwolił mi skutecznie oczyścić dane generowane przez moje narzędzia do scrapowania.
Jeszcze bardziej zdumiał mnie fakt, że każda wprowadzona przeze mnie zmiana była udokumentowana, co pozwoliło mi bezproblemowo powtórzyć proces oczyszczania. Działa to zasadniczo jak zautomatyzowany skrypt do oczyszczania danych, przekształcający chaotyczne pliki CSV w uporządkowane arkusze kalkulacyjne – idealne w sytuacjach, gdy potrzebne są niestandardowe zestawy danych generowane za pomocą web scrapingu, gdzie jakość danych często jest niespójna.
Dla tych, którzy często zarządzają uciążliwymi zadaniami oczyszczania danych, niespójnymi formatami lub wieloma źródłami danych, Power Query usprawnia te procesy, tworząc łatwy w zarządzaniu, zautomatyzowany przepływ pracy. Zamiast spędzać godziny tygodniowo na ręcznym naprawianiu problemów, wystarczy jedno kliknięcie „Odśwież”, aby od razu przejść do analizy. To potężna funkcja programu Excel, którą żałuję, że nie wdrożyłem znacznie wcześniej. Gdy przekonasz się o efektywności zautomatyzowanego, łatwo powtarzalnego procesu oczyszczania, nie będziesz już chciał z niego korzystać.
Powiązane artykuły:
Przestań marnować pieniądze: odkryj te niezbędne darmowe aplikacje
13:04
Badanie zalet i wad klonu systemu Windows o otwartym kodzie źródłowym
10:03
Użytkownicy systemu Windows 11 zostaną powiadomieni o wygaśnięciu subskrypcji usługi Microsoft 365
9:40
Dodaj komentarz