Ekstrakcja i czyszczenie danych jest pierwszym i najważniejszym zadaniem każdego projektu analizy danych. W moim pięcioletnim doświadczeniu otrzymałem dane, które wyglądały jak śmieci, ale używając funkcji Excela PRAWO i LEWO udało mi się przekształcić je w sensowne raporty.
Czyszczenie danych w programie Excel jest łatwe. Tutaj wyjaśnię, w jaki sposób można wyodrębnić konkretny tekst w Excelu 2016 z komórki i pozostawić po sobie śmieciowe dane.
Na powyższym obrazku dostaję dane w Excelu. To ma Nazwa, Wiek, a jeśli osoba posiada jakiekolwiek ubezpieczenie lub nie (T/N). Aby użyć tych danych, muszę wyodrębnić dane w różnych kolumnach. Każda kolumna powinna zawierać tylko jeden rodzaj wartości. Gdy to zrobimy, możemy tworzyć raporty przestawne w programie Excel, które będą nam lepiej informować o danych. Więc zacznijmy.
Funkcja Excel PRAWA do wyodrębniania danych z prawej strony tekstu
Zacznijmy od najłatwiejszego. W tym przykładzie mamy „Ma ubezpieczenie?” dane na końcu ciągu. A to tylko jeden znak (T lub N). Aby wyodrębnić te dane, użyjemy WŁAŚCIWEJ formuły, którą zapewnia Excel. Funkcja PRAWA jest dostępna w programach Excel 2016, Excel 2013, Excel 2010 i starszych wersjach programu Excel.
Funkcja PRAWA w programie Excel przyjmuje tylko dwa argumenty. Najpierw TEKST, a następnie LICZBA ZNAKÓW, które chcesz wyodrębnić z prawej strony w programie Excel.
Formuła ogólna:
=PRAWO(tekst;liczba_znaków)
Tutaj mamy nasze tekst w komórce B3 a liczba znaków, które chcemy wyodrębnić, to 1. Po prostu napisz tę formułę w Excelu w komórce D3 lub gdziekolwiek chcesz mieć te dane.
=PRAWY(B3,1)
Otrzymasz swój wynik w komórce D3. po prostu skopiuj lub przeciągnij tę formułę w komórkach poniżej.
Funkcja Excel LEWY do wyodrębniania danych z lewej strony tekstu
Teraz określmy nasz wiek. Zanim wyjaśnię, pokażę ci wzór. Skopiuj tę formułę w żądanych komórkach.
=LEWO(PRAWO(B3,4);2)
W powyższym wzorze połączyliśmy funkcje LEFT i RIGHT, aby uzyskać nasz konkretny tekst z ciągu. Tak to wygląda.
Teraz zrozummy tę słabą formułę.
Każda funkcja działa od wewnątrz do zewnątrz. Zacznijmy więc od:
PRAWO(B3,4): Po prostu wyodrębnia 4 znaki z danego tekstu. To daje nam 30_Y. Dlaczego 4? Ponieważ wiemy, że wiek to tylko jeden z dwóch znaków, a mamy 1 „_” i 1 Y lub N.
LEWO(PRAWO(B3,4),2): Teraz mamy nasz tekst, który zaczyna się od wieku, który ma 2 znaki. Tutaj wkracza funkcja LEFT. to trwa PRAWO(B3,4) jako argument tekstowy i wyodrębnia z niego 2 znaki. To daje nam 30 za pierwszą komórkę. Skopiuj formułę w komórkach poniżej, a otrzymasz resztę danych, jak pokazano na powyższym obrazku.
Wzór ogólny dla LEWEJ:
=LEWO(tekst;Liczba_znaków)
Podobnie jak funkcja PRAWY w programie Excel LEWA funkcja programu Excel również przyjmuje dwa takie same argumenty. Po prostu wyodrębnia dane z lewej strony podanego ciągu.
Funkcja LEWY i prawy Excel do wyodrębniania nieznanej długości tekstu z ciągu
Po prostu wklej tę formułę w żądanych komórkach, aby wyodrębnić nazwę z tekstu w programie Excel.
=LEWO(B3;DŁ(B3)-5)
W powyższych przykładach znaliśmy dokładną liczbę tekstów do wyodrębnienia z podanego tekstu. Ale teraz nie wiemy, jak długa jest nazwa. Co można zrobić?
Wiemy, że po nazwie następuje dokładnie 5 znaków. 1 dla spacji, 2 dla wieku, 1 dla podkreślenia „_” i jeden dla Y.
Jeśli możemy uzyskać całą liczbę tekstu, a następnie odjąć od tego 5, otrzymamy dokładną liczbę tekstu dla nazwy.
Do znalezienia długości tekstu w Excelu możemy użyć funkcji LEN. Funkcja LEN programu Excel zwraca liczbę zaangażowanych znaków.
Składnia:
=DŁ(tekst)
Więc DŁ(B3) zwraca 20.
LEN(B3)-5 zwraca 15. A to długość nazwy w komórce B3
W końcu LEWY(B3, DŁ(B3)-5) daje nam Georgina Schalk w rezultacie. Gdy skopiujesz formułę w komórkach poniżej, otrzymasz wszystkie nazwy.
Teraz możesz przygotowywać takie raporty na podstawie wyodrębnionych danych za pomocą tabel przestawnych. Mówi nam, że połowa naszych pacjentów ma ubezpieczenie, a druga połowa nie.
LEWA i PRAWA FUNKCJA programu Excel jest bardzo wydajna i przydatna do czyszczenia danych. W połączeniu z funkcją LEN, funkcją FIND i funkcją SUBSTITUTE programu Excel mogą wyodrębnić dowolny podciąg w ciągu ciągu w programie Excel.
W przyszłości zobaczymy bardziej złożoną ekstrakcję danych w Excelu. Na razie pa. I tak, zostaw swoje przemyślenia w sekcji komentarzy poniżej. Chętnie Ci pomogę.