Czyszczenie danych za pomocą funkcji tekstowych

Dane, które uzyskujesz z różnych źródeł, często nie są w formie gotowej do analizy. W tym rozdziale dowiesz się, jak przygotować dane w formie tekstu do analizy.

Najpierw musisz wyczyścić dane. Czyszczenie danych obejmuje usuwanie niepożądanych znaków z tekstu. Następnie musisz uporządkować dane w formie wymaganej do dalszej analizy. Możesz zrobić to samo przez -

Znajdowanie wymaganych wzorów tekstowych za pomocą funkcji tekstowych.
Wyodrębnianie wartości danych z tekstu.
Formatowanie danych za pomocą funkcji tekstowych.
Wykonywanie operacji na danych za pomocą funkcji tekstowych.

Usuwanie niechcianych znaków z tekstu

Podczas importowania danych z innej aplikacji mogą one zawierać znaki niedrukowalne i / lub nadmiar spacji. Nadmiar przestrzeni może być -

wiodące spacje i / lub
dodatkowe spacje między słowami.

Jeśli posortujesz lub przeanalizujesz takie dane, otrzymasz błędne wyniki.

Rozważmy następujący przykład -

Są to surowe dane, które uzyskałeś na temat informacji o produkcie, zawierających identyfikator produktu, opis produktu i cenę. Znak „|” oddziela pole w każdym wierszu.

Po zaimportowaniu tych danych do arkusza programu Excel wygląda to następująco -

Jak widać, wszystkie dane znajdują się w jednej kolumnie. Aby przeprowadzić analizę danych, musisz uporządkować te dane. Jednak początkowo musisz wyczyścić dane.

Musisz usunąć wszelkie niedrukowalne znaki i nadmiarowe spacje, które mogą znajdować się w danych. W tym celu możesz użyć funkcji CLEAN i TRIM.

S.No.	Opis funkcji
1.	CLEAN Usuwa wszystkie niedrukowalne znaki z tekstu
2.	TRIM Usuwa spacje z tekstu

Wybierz komórki C3 - C11.
Wpisz = TRIM (CLEAN (B3)), a następnie naciśnij klawisze CTRL + Enter.

Formuła jest wypełniana w komórkach C3 - C11.

Wynik będzie taki, jak pokazano poniżej -

Znajdowanie wymaganych wzorów tekstowych za pomocą funkcji tekstowych

Aby uporządkować dane, może być konieczne wykonanie określonego dopasowania wzorca tekstowego, na podstawie którego można wyodrębnić wartości danych. Niektóre funkcje tekstowe, które są przydatne w tym celu, to:

S.No.	Opis funkcji
1.	EXACT Sprawdza, czy dwie wartości tekstowe są identyczne
2.	FIND Znajduje jedną wartość tekstową w innej (z uwzględnieniem wielkości liter)
3.	SEARCH Znajduje jedną wartość tekstową w innej (bez rozróżniania wielkości liter)

Wyodrębnianie wartości danych z tekstu

Musisz wyodrębnić wymagane dane z tekstu, aby uzyskać taką samą strukturę. W powyższym przykładzie, powiedzmy, musisz umieścić dane w trzech kolumnach - ProductID, Product_Description i Price.

Możesz wyodrębnić dane na jeden z następujących sposobów -

Wyodrębnianie wartości danych za pomocą Kreatora konwersji tekstu na kolumny
Wyodrębnianie wartości danych za pomocą funkcji tekstowych
Wyodrębnianie wartości danych za pomocą funkcji Flash Fill

Wyodrębnianie wartości danych za pomocą Kreatora konwersji tekstu na kolumny

Możesz użyć Convert Text to Columns Wizard wyodrębnić wartości danych do kolumn programu Excel, jeśli pola są -

Ograniczone znakiem lub
Wyrównane w kolumnach ze spacjami między poszczególnymi polami.

W powyższym przykładzie pola są rozdzielone znakiem „|”. Dlatego możesz użyćConvert Text to Columns czarodziej.

Wybierz dane.
Skopiuj i wklej wartości w tym samym miejscu. Inaczej,Convert Text to Columns przyjmuje funkcje, a nie same dane jako dane wejściowe.

Wybierz dane.
Kliknij Text to Columns w Data Tools grupa pod Data Karta na Wstążce.

Step 1 - Kreator konwersji tekstu na kolumny - pojawia się krok 1 z 3.

Wybierz opcję Rozdzielany.
Kliknij Następny.

Step 2 - Kreator konwersji tekstu na kolumny - pojawia się krok 2 z 3.

Pod Delimiters, Wybierz Other.
W polu obok Otherwpisz znak |
Kliknij Next.

Step 3 - Kreator konwersji tekstu na kolumny - pojawia się krok 3 z 3.

Na tym ekranie możesz wybrać każdą kolumnę danych w kreatorze i ustawić format dla tej kolumny.

Dla Destination, wybierz komórkę D3.
Możesz kliknąć Advanced, i nastaw Decimal Separator i Thousands Separator w Advanced Text Import Settings wyświetlone okno dialogowe.
Kliknij Finish.

Twoje dane, które są konwertowane na kolumny, pojawiają się w trzech kolumnach - D, E i F.

Nazwij nagłówki kolumn jako ProductID, Product_Description i Price.

Wyodrębnianie wartości danych za pomocą funkcji tekstowych

Załóżmy, że pola w danych nie są oddzielone znakiem ani wyrównane w kolumnach ze spacjami między poszczególnymi polami. Możesz użyć funkcji tekstowych do wyodrębnienia wartości danych. Nawet jeśli pola są rozdzielane, nadal możesz używać funkcji tekstowych do wyodrębniania danych.

Niektóre przydatne w tym celu funkcje tekstowe to:

S.No.	Opis funkcji
1.	LEFT Zwraca skrajne lewe znaki z wartości tekstowej
2.	RIGHT Zwraca skrajne prawe znaki z wartości tekstowej
3.	MID Zwraca określoną liczbę znaków z ciągu tekstowego, zaczynając od określonej pozycji
4.	LEN Zwraca liczbę znaków w ciągu tekstowym

Możesz również połączyć dwie lub więcej z tych funkcji tekstowych zgodnie z dostępnymi danymi, aby wyodrębnić wymagane wartości danych. Na przykład, używając kombinacji funkcji LEFT, RIGHT i VALUE lub używając kombinacji funkcji FIND, LEFT, LEN i MID.

W powyższym przykładzie

Wszystkie znaki do pierwszego | podaj nazwę ProductID.
Wszystkie znaki do drugiego | podaj nazwę Cena.
Wszystkie postacie znajdujące się pomiędzy pierwszym | i po drugie | podaj nazwę Opis_produktu.
Każdy | ma spację przed i po.

Obserwując te informacje, możesz wyodrębnić wartości danych, wykonując następujące czynności -

Znajdź pozycję pierwszego | -First | Position
- Możesz użyć funkcji ZNAJDŹ
Znajdź pozycję sekundy | -Second | Position
- Możesz ponownie użyć funkcji ZNAJDŹ
Początek do (First | Position - 2) Znaki tekstu dają ProductID
- Możesz użyć funkcji LEFT
(First | Position + 2) do (Second | Position - 2) Znaki tekstu dają opis produktu
- Możesz użyć funkcji MID
(Second | Position + 2), aby Końcowe znaki tekstu podać cenę
- Możesz użyć funkcji PRAWO

Wynik będzie taki, jak pokazano poniżej -

Możesz zauważyć, że wartości w kolumnie ceny są wartościami tekstowymi. Aby wykonać obliczenia na tych wartościach, musisz sformatować odpowiednie komórki. Możesz zapoznać się z sekcją podaną poniżej, aby zrozumieć formatowanie tekstu.

Wyodrębnianie wartości danych za pomocą funkcji Flash Fill

Korzystanie z programu Excel Flash Fillto inny sposób na wyodrębnienie wartości danych z tekstu. Jednak działa to tylko wtedy, gdy program Excel może znaleźć wzorzec w danych.

Step 1 - Utwórz trzy kolumny dla ProductID, Product_Description i Price obok danych.

Step 2 - Skopiuj i wklej wartości C3, D3 i E3 z B3.

Step 3 - Wybierz komórkę C3 i kliknij Flash Fill w Data Tools grupa na Datapatka. Wszystkie wartości ProductID zostaną wypełnione.

Step 4- Powtórz powyższe kroki dla opisu produktu i ceny. Dane są wypełnione.

Formatowanie danych za pomocą funkcji tekstowych

Program Excel ma kilka wbudowanych funkcji tekstowych, których można używać do formatowania danych zawierających tekst. Należą do nich -

Functions that format the Text as per your need -

S.No.	Opis funkcji
1.	LOWER Konwertuje tekst na małe litery

S.No.	Opis funkcji
1.	UPPER Konwertuje tekst na wielkie litery
2.	PROPER Zamienia na wielką literę pierwszą literę w każdym słowie wartości tekstowej

Functions that convert and/or format the Numbers as Text -

S.No.	Opis funkcji
1.	DOLLAR Konwertuje liczbę na tekst, używając formatu waluty $ (dolar)
2.	FIXED Formatuje liczbę jako tekst ze stałą liczbą miejsc po przecinku
3.	TEXT Formatuje liczbę i konwertuje ją na tekst

Functions that convert the Text to Numbers -

S.No.	Opis funkcji
1.	VALUE Konwertuje argument tekstowy na liczbę

Executing Data Operations with the Text Functions

Być może będziesz musiał wykonać pewne operacje tekstowe na swoich danych. Na przykład, jeśli identyfikatory logowania pracowników zostaną zmienione na nowy format w organizacji, w oparciu o zmianę formatu, może być konieczne dokonanie zamiany tekstu.

Poniższe funkcje tekstowe pomagają w wykonywaniu operacji tekstowych na danych zawierających tekst -

S.No.	Opis funkcji
1.	REPLACE Zastępuje znaki w tekście
2.	SUBSTITUTE Zastępuje stary tekst w ciągu tekstowym nowym tekstem
3.	CONCATENATE Łączy kilka elementów tekstowych w jeden element tekstowy
4.	CONCAT Łączy tekst z wielu zakresów i / lub ciągów, ale nie zawiera separatora ani argumentów IgnoreEmpty.
5.	TEXTJOIN Łączy tekst z wielu zakresów i / lub ciągów i zawiera separator określony przez użytkownika między każdą wartością tekstową, która zostanie połączona. Jeśli separator jest pustym ciągiem tekstowym, ta funkcja skutecznie połączy zakresy.
6.	REPT Powtarza tekst określoną liczbę razy