10 super schludnych sposobów na czyszczenie danych w arkuszach kalkulacyjnych Excel

Obejrzyj wideo - 10 sposobów na czyszczenie danych w programie Excel

Dane stanowią podstawę każdej analizy wykonywanej w programie Excel. A jeśli chodzi o dane, jest mnóstwo rzeczy, które mogą pójść nie tak – czy to struktura, rozmieszczenie, formatowanie, dodatkowe spacje i tak dalej.

W tym poście na blogu pokażę Ci 10 prostych sposobów czyszczenia danych w Excelu.

#1 Pozbądź się dodatkowych przestrzeni

Dodatkowe przestrzenie są boleśnie trudne do zauważenia. Chociaż możesz w jakiś sposób dostrzec dodatkowe spacje między słowami lub liczbami, spacje końcowe nie są nawet widoczne. Oto zgrabny sposób na pozbycie się tych dodatkowych spacji - Użyj funkcji TRIM.

Składnia: TRIM(tekst)

Funkcja Excel TRIM przyjmuje odwołanie do komórki (lub tekst) jako dane wejściowe. Usuwa początkowe i końcowe spacje oraz dodatkowe spacje między wyrazami (z wyjątkiem pojedynczych spacji).

#2 Wybierz i potraktuj wszystkie puste komórki

Puste komórki mogą spowodować spustoszenie, jeśli nie zostaną wcześniej potraktowane. Często spotykam się z problemami z pustymi komórkami w zestawie danych używanym do tworzenia raportów/dashboardów.

Możesz wypełnić wszystkie puste komórki „0” lub „Niedostępne” lub po prostu chcieć je podświetlić. Jeśli istnieje ogromny zestaw danych, wykonanie tego ręcznie może zająć wiele godzin. Na szczęście istnieje sposób na jednoczesne zaznaczenie wszystkich pustych komórek.

1. Wybierz cały zestaw danych
2. Naciśnij F5 (otworzy się okno dialogowe Przejdź do)
3. Kliknij przycisk Specjalne… (w lewym dolnym rogu). Spowoduje to otwarcie okna dialogowego Przejdź do specjalnego
   
4. Wybierz Puste i kliknij OK
   

Powoduje to zaznaczenie wszystkich pustych komórek w zestawie danych. Jeśli chcesz wpisać 0 lub Niedostępne we wszystkich tych komórkach, po prostu wpisz je i naciśnij Control + Enter (pamiętaj, że jeśli naciśniesz tylko enter, wartość zostanie wstawiona tylko w aktywnej komórce).

#3 Konwertuj liczby przechowywane jako tekst na liczby

Czasami, gdy importujesz dane z plików tekstowych lub zewnętrznych baz danych, liczby są zapisywane jako tekst. Ponadto niektórzy ludzie mają zwyczaj używania apostrofu (‘) przed liczbą, aby nadać jej tekst. Może to spowodować poważne problemy, jeśli używasz tych komórek w obliczeniach. Oto niezawodny sposób na przekonwertowanie tych liczb przechowywanych jako tekst z powrotem na liczby.

1. W dowolnej pustej komórce wpisz 1
2. Zaznacz komórkę, w której wpisałeś 1, i naciśnij Control + C
3. Wybierz komórkę/zakres, który chcesz przekonwertować na liczby
4. Wybierz Wklej -> Wklej specjalnie (Skrót klawiaturowy - Alt + E + S)
5. W oknie dialogowym Wklej specjalnie wybierz Pomnóż (w kategorii Operacje)
   
6. Kliknij OK. To konwertuje wszystkie liczby w formacie tekstowym z powrotem na liczby.

Z opcjami operacji specjalnych wklejania można zrobić o wiele więcej. Oto różne inne sposoby mnożenia w programie Excel za pomocą funkcji Wklej specjalnie.

#4 - Usuń duplikaty

Ze zduplikowanymi danymi można zrobić dwie rzeczy: Zaznacz to lub Usuń to.

• Wyróżnij zduplikowane dane:
  • Wybierz dane i przejdź do Strona główna -> Formatowanie warunkowe -> Reguły podświetlania komórek -> Zduplikowane wartości.
  • Określ formatowanie, a wszystkie zduplikowane wartości zostaną podświetlone.
    
• Usuń duplikaty w danych:
  • Wybierz dane i przejdź do Dane -> Usuń duplikaty.
  • Jeśli Twoje dane mają nagłówki, upewnij się, że pole wyboru w prawym górnym rogu jest zaznaczone.
  • Wybierz Kolumny, z których chcesz usunąć duplikaty i kliknij OK.
    

Spowoduje to usunięcie zduplikowanych wartości z listy. Jeśli chcesz, aby oryginalna lista była nienaruszona, skopiuj i wklej dane w innym miejscu, a następnie zrób to.

Związane z: Kompletny przewodnik po znajdowaniu i usuwaniu duplikatów w programie Excel.

#5 Podświetl błędy

Istnieją 2 sposoby wyróżnienia błędów w danych w programie Excel:

Korzystanie z formatowania warunkowego

1. Wybierz cały zestaw danych
2. Przejdź do Strona główna -> Formatowanie warunkowe -> Nowa reguła
3. W oknie dialogowym Nowa reguła formatowania wybierz „Formatuj tylko komórki zawierające”
4. W Opisie reguły wybierz Błędy z listy rozwijanej
5. Ustaw format i kliknij OK. To podświetla każdą wartość błędu w wybranym zbiorze danych
   

Korzystanie z opcji Przejdź do specjalnych

1. Wybierz cały zestaw danych
2. Naciśnij F5 (otworzy się okno dialogowe Przejdź do)
3. Kliknij przycisk Specjalny w lewym dolnym rogu
4. Wybierz Formuły i odznacz wszystkie opcje z wyjątkiem błędów
   

Zaznacza to wszystkie komórki, które zawierają błąd. Teraz możesz je ręcznie podświetlić, usunąć lub wpisać w nim cokolwiek.

#6 Zmień tekst na małe/górne/prawidłowe litery

Gdy dziedziczysz skoroszyt lub importujesz dane z plików tekstowych, często nazwy lub tytuły nie są spójne. Czasami cały tekst może być pisany małymi/dużymi literami lub może to być kombinacja obu. Możesz łatwo zapewnić spójność, korzystając z tych trzech funkcji:

LOWER() - konwertuje cały tekst na małe litery.
UPPER() - konwertuje cały tekst na wielkie litery.
PROPER() - konwertuje cały tekst na poprawną wielkość liter.

#7 Parsuj dane za pomocą tekstu do kolumny

Kiedy pobierasz dane z bazy danych lub importujesz je z pliku tekstowego, może się zdarzyć, że cały tekst jest ciasny w jednej komórce. Możesz przeanalizować ten tekst na wiele komórek, korzystając z funkcji tekstu do kolumny w programie Excel.

1. Wybierz dane/tekst, które chcesz przeanalizować
2. Przejdź do danych -> Tekst do kolumny (Otwiera kreatora Tekst do kolumn)
   • Krok 1: Wybierz typ danych (wybierz Rozdzielone, jeśli dane nie są równomiernie rozmieszczone i są oddzielone znakami, takimi jak przecinek, myślnik, kropka… ). Kliknij Następny
     
   • Krok 2: Wybierz Ogranicznik (znak oddzielający dane). Możesz wybrać predefiniowany ogranicznik lub cokolwiek innego, korzystając z opcji Inne
     
   • Krok 3: Wybierz format danych. Wybierz także komórkę docelową. Jeśli komórka docelowa nie jest wybrana, bieżąca komórka zostanie nadpisana
     

Związane z: Wyodrębnij nazwę użytkownika z identyfikatora e-mail za pomocą tekstu do kolumny.

#8 Sprawdzanie pisowni

Nic tak nie obniża wiarygodności Twojej pracy, jak błąd ortograficzny.

Użyj skrótu klawiaturowego F7, aby uruchomić sprawdzanie pisowni w zestawie danych.

Oto szczegółowy samouczek dotyczący używania sprawdzania pisowni w programie Excel.

#9 Usuń całe formatowanie

W swojej pracy korzystałem z wielu baz danych, aby uzyskać dane w programie Excel. Każda baza danych miała własne formatowanie danych. Gdy masz wszystkie dane, oto jak możesz usunąć całe formatowanie za jednym razem:

1. Wybierz zestaw danych
2. Przejdź do Strona główna -> Wyczyść -> Wyczyść formaty
   

Podobnie możesz również wyczyścić tylko komentarze, hiperłącza lub treść.

# 10 Użyj funkcji Znajdź i zamień, aby wyczyścić dane w programie Excel

Znajdowanie i zamienianie jest niezbędne, jeśli chodzi o czyszczenie danych. Na przykład możesz zaznaczyć i usunąć wszystkie zera, zmienić odwołania w formułach, znaleźć i zmienić formatowanie itd.

Przeczytaj więcej o tym, jak funkcja Znajdź i zamień może służyć do czyszczenia danych.

Oto moje 10 najlepszych technik czyszczenia danych w programie Excel. Jeśli chcesz poznać więcej technik, oto przewodnik zespołu MS Excel - Czyste dane w Excelu.

Jeśli używasz więcej technik, podziel się nimi z nami w sekcji komentarzy!