Błędnie napisane wyrazy, uporczywe spacje końcowe, niechciane prefiksy, nieprawidłowa wielkość liter oraz niedrukowane znaki wywierają złe pierwsze wrażenie. A to nawet nie jest pełna lista problemów, które mogą sprawić, że dane będą wyglądały niechlujnie. Zakasaj rękawy. Nadszedł czas na wiosenne porządki w arkuszach programu Microsoft Excel.
Podstawowe informacje na temat oczyszczania danych
Nie zawsze masz kontrolę nad formatem i typem danych zaimportowanych z zewnętrznego źródła danych, takiego jak baza danych, plik tekstowy lub strona internetowa. Aby można było analizować dane, często trzeba je oczyścić. Na szczęście program Excel ma wiele funkcji ułatwiających takie formatowanie danych, które spełni Twoje wymagania. Czasami to zadanie jest proste i istnieje określona funkcja, która wykona je za Ciebie. Na przykład możesz użyć modułu sprawdzania pisowni, aby poprawić błędnie napisane wyrazy w kolumnach zawierających komentarze lub opisy. Możesz też szybko usunąć zduplikowane wiersze za pomocą okna dialogowego Usuwanie duplikatów.
W innych przypadkach może być konieczne zmodyfikowanie jednej lub kilku kolumn przy użyciu formuły, aby przekonwertować zaimportowane wartości na nowe. Na przykład jeśli chcesz usunąć spacje końcowe, możesz utworzyć nową kolumnę w celu oczyszczenia danych przy użyciu formuły, wypełnić nową kolumnę w dół, przekonwertować formuły na wartości, a następnie usunąć oryginalną kolumnę.
Poniżej podano podstawowe czynności czyszczenia danych:
Zaimportuj dane z zewnętrznego źródła danych.
Utwórz kopię oryginalnych danych w osobnym skoroszycie.
Upewnij się, że dane są w formacie tabelarycznym zawierającym wiersze i kolumny, przy czym: każda kolumna zawiera podobne dane, wszystkie kolumny i wiersze są widoczne i nie ma pustych wierszy w wyświetlanym zakresie. Aby uzyskać najlepsze wyniki, należy użyć tabeli programu Excel.
Najpierw wykonaj zadania, które nie wymagają modyfikacji kolumn, takie jak sprawdzanie pisowni lub zadania z użyciem okna dialogowego Znajdź i zamień.
Następnie wykonaj zadania wymagające modyfikacji kolumn. Oto ogólne instrukcje dotyczące modyfikowania kolumn:
- Wstaw nową kolumnę (B) obok oryginalnej (A), która wymaga oczyszczenia.
- U góry nowej kolumny (B) dodaj formułę, która przekształci dane.
- Wypełnij nową kolumnę (B) w dół formułą. W tabeli programu Excel zostanie automatycznie utworzona kolumna obliczeniowa z wypełnionymi wartościami.
- Zaznacz nową kolumnę (B), skopiuj ją, a następnie wklej jako wartości w nowej kolumnie (B).
- Usuń pierwotną kolumnę (A). To spowoduje przekonwertowanie nowej kolumny B na kolumnę A.
Aby okresowo czyścić to samo źródło danych, rozważ zarejestrowanie makra lub napisanie kodu w celu zautomatyzowania całego procesu. Dostępne są również liczne zewnętrzne dodatki napisane przez inne firmy wymienione w sekcji Dostawcy, których użycie możesz rozważyć, jeśli nie masz czasu ani zasobów, aby samodzielnie zautomatyzować proces.
| Więcej informacji | Opis |
|---|---|
| Automatyczne wypełnianie danych w komórkach arkusza | Pokazuje sposób użycia polecenia Wypełnij. |
|
Tworzenie i formatowanie tabel Zmienianie rozmiaru tabeli przez dodawanie lub usuwanie wierszy i kolumn Używanie kolumn obliczeniowych w tabeli programu Excel |
Przedstawiają sposób tworzenia tabeli programu Excel i dodawania lub usuwania kolumn lub kolumn obliczeniowych. |
| Tworzenie makra | Przedstawia kilka sposobów na zautomatyzowanie powtarzających się zadań za pomocą makra. |
Sprawdzanie pisowni
Moduł sprawdzania pisowni służy nie tylko do znajdowania błędnie napisanych wyrazów, ale też do znajdowania wartości, które nie są używane spójnie, takich jak nazwa produktu lub firmy, poprzez dodanie tych wartości do słownika niestandardowego.
| Więcej informacji | Opis |
|---|---|
| Sprawdzanie pisowni i gramatyki | Pokazuje, jak poprawić błędnie napisane wyrazy w arkuszu. |
| Dodawanie wyrazów do modułu sprawdzania pisowni przy użyciu słowników niestandardowych | Wyjaśnia, jak używać słowników niestandardowych. |
Usuwanie zduplikowanych wierszy
Zduplikowane wiersze są typowym problemem podczas importowania danych. Przed usunięciem zduplikowanych wartości warto przefiltrować arkusz z uwzględnieniem unikatowych wartości, aby upewnić się, że wyniki są poprawne.
| Więcej informacji | Opis |
|---|---|
| Filtrowanie w celu znalezienia wartości unikatowych lub usuwanie wartości zduplikowanych | Przedstawia dwie ściśle powiązane procedury: filtrowanie z uwzględnieniem unikatowych wierszy i usuwanie zduplikowanych wierszy. |
Znajdowanie i zamienianie tekstu
Warto usunąć typowy ciąg wiodący, taki jak etykieta, po której następuje dwukropek i spacja lub sufiks, na przykład wyrażenie w nawiasie wtrącone na końcu ciągu, które jest przestarzałe lub niepotrzebne. Możesz to zrobić znajdując wystąpienia tego tekstu, a następnie zamieniając go na brak tekstu lub inny tekst.
| Więcej informacji | Opis |
|---|---|
|
Sprawdzanie, czy komórka zawiera tekst (bez uwzględniania wielkości liter) Sprawdzanie, czy komórka zawiera tekst (z uwzględnieniem wielkości liter) |
Pokazuje, jak używać polecenia Znajdź i innych funkcji w celu znalezienia tekstu. |
| Usuwanie znaków z tekstu | Pokazuje, jak używać polecenia Zamień i innych funkcji w celu usunięcia tekstu. |
| Znajdowanie i zamienianie tekstu i liczb w arkuszu | Pokazuje, jak korzystać z okien dialogowych Znajdowanie i Zamienianie. |
|
ZNAJDŹ, ZNAJDŹB SZUKAJ.TEKST, SZUKAJ.TEKST.B ZASTĄP, ZASTĄP.B PODSTAW LEWY, LEWYB PRAWY, PRAWY.B DŁ, DŁ.B FRAGMENT.TEKSTU, FRAGMENT.TEKSTU.B |
Są to funkcje, za pomocą których można wykonywać różne zadania związane z modyfikacją ciągu, takie jak znajdowanie i zamienianie ciągu podrzędnego w obrębie ciągu znaków, wyodrębnianie części ciągu lub określanie długości ciągu. |
Zmienianie wielkości liter w tekście
Czasami tekst jest bardzo nieuporządkowany, zwłaszcza jeśli chodzi o wielkości liter w tekście. Za pomocą jednej lub kilku z trzech funkcji wielkości liter możesz przekonwertować tekst tak, aby był pisany małymi literami, jak adresy e-mail, wielkimi literami, jak kody produktów, lub rozpoczynał się z wielkiej litery, jak nazwiska lub tytuły książek.
| Więcej informacji | Opis |
|---|---|
| Zmienianie wielkości liter | Pokazuje sposób użycia trzech funkcji wielkości liter. |
| LITERY.MAŁE | Konwertuje wszystkie duże litery w ciągu tekstowym na małe. |
| Z.WIELKIEJ.LITERY | Zmienia w wielką literę pierwszą małą literę tekstu i wszystkie inne litery w tekście następujące po znaku innym niż litera. Wszystkie inne litery są konwertowane na małe litery. |
| LITERY.WIELKIE | Konwertuje znaki tekstu na wielkie litery. |
Usuwanie z tekstu spacji i znaków niedrukowanych
Czasami wartości tekstowe zawierają znaki wiodące, końcowe lub wiele osadzonych znaków spacji (wartości zestawu znaków Unicode 32 i 160) lub znaki niedrukowane (wartości zestawu znaków Unicode od 0 do 31, 127, 129, 141, 143, 144 i 157). Podczas sortowania, filtrowania lub wyszukiwania danych te znaki mogą przyczyniać się do osiągania nieoczekiwanych wyników. Na przykład w zewnętrznym źródle danych użytkownicy mogą wprowadzić błędy typograficzne przez dodanie przypadkowo znaków spacji, albo dane tekstowe zaimportowane ze źródeł zewnętrznych mogą zawierać znaki niedrukowane osadzone w tekście. Ponieważ te znaki trudno zauważyć, nieoczekiwane wyniki mogą być trudne do zrozumienia. Aby usunąć niepożądane znaki, możesz użyć połączenia funkcji USUŃ.ZBĘDNE.ODSTĘPY, OCZYŚĆ i PODSTAW.
| Więcej informacji | Opis |
|---|---|
| KOD | Zwraca wartość kodu liczbowego pierwszego znaku w ciągu tekstowym. |
| OCZYŚĆ | Usuwa z tekstu pierwsze 32 znaki niedrukowane w 7-bitowym kodzie ASCII (wartości od 0 do 31). |
| USUŃ.ZBĘDNE.ODSTĘPY | Usuwa z tekstu 7-bitowy znak spacji ASCII (wartość 32). |
| PODSTAW | Funkcja PODSTAW służy do zamiany znaków Unicode o wyższych wartościach (127, 129, 141, 143, 144, 157 i 160) na 7-bitowe znaki ASCII, dla których zaprojektowano funkcje USUŃ.ZBĘDNE.ODSTĘPY i OCZYŚĆ. |
Naprawianie liczb i znaków liczbowych
Istnieją dwa główne problemy z liczbami, których rozwiązanie może wymagać czyszczenia danych: liczba została przypadkowo zaimportowana w postaci tekstu i znak minus musi zostać zmieniony zgodnie z normą Twojej organizacji.
| Więcej informacji | Opis |
|---|---|
| Konwertowanie liczb przechowywanych jako tekst na liczby | Pokazuje, jak zmienić liczby, które są sformatowane jako tekst i w tej postaci przechowywane w komórkach, co może powodować problemy przy obliczeniach i utrudniać sortowanie, na format liczbowy. |
| KWOTA | Konwertuje liczbę na format tekstowy i powoduje zastosowanie symbolu waluty. |
| TEKST | Konwertuje wartość na tekst w określonym formacie liczb. |
| ZAOKR.DO.TEKST | Zaokrągla liczbę do podanej liczby miejsc dziesiętnych, formatuje liczbę do postaci dziesiętnej z użyciem przecinka i spacji, oraz zwraca wynik w postaci tekstowej. |
| WARTOŚĆ | Konwertuje ciąg tekstowy reprezentujący liczbę na liczbę. |
Wprowadzanie dat i godzin
Ponieważ istnieje wiele różnych formatów daty, które można pomylić z kodami liczbowymi lub innymi ciągami zawierającymi kreski ułamkowe czy łączniki, daty i godziny często należy przekonwertować i ponownie sformatować.
| Więcej informacji | Opis |
|---|---|
| Zmienianie systemu lub formatu daty albo interpretacji dat z rokiem dwucyfrowym | Opisuje, jak działa system dat w programie Office Excel. |
| Konwertowanie wartości czasu | Pokazuje sposób konwertowania różnych jednostek czasu. |
| Konwertowanie dat przechowywanych jako tekst na daty | Pokazuje, jak zmienić daty, które są sformatowane jako tekst i w tej postaci przechowywane w komórkach, co może powodować problemy przy obliczeniach i utrudniać sortowanie, na format daty. |
| DATA | Zwraca kolejne liczby porządkowe, które reprezentują datę. Jeśli komórka miała format Ogólny przed wprowadzeniem funkcji, to wynik zostanie sformatowany jako data. |
| DATA.WARTOŚĆ | Konwertuje datę w formie tekstu na liczbę kolejną. |
| GODZINA | Zwraca określony czas jako liczbę dziesiętną. Jeśli komórka miała format Ogólny przed wprowadzeniem funkcji, to wynik zostanie sformatowany jako data. |
| CZAS.WARTOŚĆ | Zwraca liczbę dziesiętną czasu reprezentowanego przez ciąg tekstowy. Liczba dziesiętna to wartość z zakresu od 0 do 0,99999999, reprezentująca czas od 0:00:00 (12:00:00 AM) do 23:59:59 (11:59:59 PM). |
Scalanie i dzielenie kolumn
Typowym zadaniem po zaimportowaniu danych z zewnętrznego źródła danych jest scalenie dwóch lub więcej kolumn w jedną, albo podzielenie jednej kolumny na dwie lub więcej kolumn. Na przykład możesz podzielić kolumnę zawierającą imię i nazwisko na dwie: jedną z imieniem i drugą z nazwiskiem. Możesz też podzielić kolumnę zawierającą pole adresu na osobne kolumny zawierające ulicę, miasto, region i kod pocztowy. Wykonanie odwrotnej czynności także jest możliwe. Możesz scalić kolumny Imię i Nazwisko osoby w jedną zawierającą imię i nazwisko lub połączyć adres z oddzielnych kolumn w jedną kolumnę. Inne często używane wartości, które mogą wymagać scalenia w jedną kolumnę lub podzielenia na wiele kolumn, to kody produktów, ścieżki plików lub adresy IP.
| Więcej informacji | Opis |
|---|---|
|
Łączenie imion i nazwisk Łączenie tekstu i liczb Łączenie tekstu z datą lub godziną Łączenie kilku kolumn przy użyciu funkcji |
Pokazują typowe przykłady łączenia wartości z dwóch lub większej liczby kolumn. |
| Dzielenie tekstu i umieszczanie go w różnych kolumnach przy użyciu Kreatora konwersji tekstu na kolumny | Pokazuje, jak wykorzystać tego kreatora do dzielenia kolumn według różnych typowych ograniczników. |
| Dzielenie tekstu i umieszczanie go w różnych kolumnach za pomocą funkcji | Pokazuje, jak korzystać z funkcji LEWY, FRAGMENT.TEKSTU, PRAWY, WYSZUKIWANIE i DŁ w celu podzielenia nazwy kolumny na dwie lub więcej kolumn. |
| Łączenie lub dzielenie zawartości komórek | Pokazuje, jak używać funkcji ZŁĄCZ.TEKSTY, operatora & (handlowe „i”) oraz Kreatora konwersji tekstu na kolumny. |
| Scalanie komórek lub podział scalonych komórek | Pokazuje, jak używać poleceń Scal komórki, Scal wszystkie oraz Scal i wyśrodkuj. |
| ZŁĄCZ.TEKSTY | Łączy dwa lub więcej ciągów tekstowych w jeden. |
Przekształcanie i zmienianie kolejności kolumn i wierszy
Większość funkcji analizy i formatowania w programie Office Excel zakłada, że dane znajdują się w jednej, płaskiej, dwuwymiarowej tabeli. Czasami trzeba zamienić wiersze na kolumny, a kolumny na wiersze. W innych przypadkach dane nie są zorganizowane w formacie tabelarycznym i potrzebny jest sposób przekształcenia danych z formatu nietabelarycznego na tabelaryczny.
| Więcej informacji | Opis |
|---|---|
| TRANSPONUJ | Zwraca pionowy zakres komórek jako zakres poziomy lub odwrotnie. |
Uzgadnianie danych w tabeli przez dopasowanie lub dołączanie
Od czasu do czasu administratorzy baz danych używają programu Office Excel do znajdowania i poprawiania błędów dopasowania, gdy łączone są co najmniej dwie tabele. Może to obejmować uzgadnianie dwóch tabel z różnych arkuszy, na przykład w celu wyświetlenia wszystkich rekordów w obu tabelach lub porównania tabel i znalezienia wierszy, które nie są zgodne.
| Więcej informacji | Opis |
|---|---|
| Wyszukiwanie wartości na liście danych | Przedstawia typowe sposoby wyszukiwania danych przy użyciu funkcji wyszukiwania. |
| WYSZUKAJ | Zwraca wartość z zakresu jednowierszowego lub jednokolumnowego albo z tablicy. Funkcja WYSZUKAJ ma dwie formy składni: wektorową i tablicową. |
| WYSZUKAJ.POZIOMO | Wyszukuje wartość w górnym wierszu tabeli lub tablicy wartości, a następnie zwraca wartość w tej samej kolumnie z wiersza określonego w tabeli lub w tablicy. |
| WYSZUKAJ.PIONOWO | Wyszukuje wartość w pierwszej kolumnie tablicy i zwraca wartość w tym samym wierszu innej kolumny w tablicy. |
| INDEKS | Zwraca wartość lub odwołanie do wartości, która znajduje się wewnątrz tablicy lub zakresu. Istnieją dwie formy funkcji INDEKS: tablicowa i odwołaniowa. |
| PODAJ.POZYCJĘ | Zwraca względną pozycję elementu w tablicy, która jest zgodna z określoną wartością w określonej kolejności. Gdy chcesz poznać położenie elementu w zakresie, a nie położenie samego elementu, użyj funkcji PODAJ.POZYCJĘ zamiast jednej z funkcji WYSZUKAJ. |
| PRZESUNIĘCIE | Zwraca odwołanie do zakresu, który jest podaną liczbą wierszy lub kolumn począwszy od komórki lub zakresu komórek. Zwrócone odwołanie może być pojedynczą komórką lub zakresem komórek. Można określić liczbę zwracanych wierszy i kolumn. |
Dostawcy
Oto częściowa lista dostawców produktów, które są używane do czyszczenia danych na różne sposoby.
Uwaga
Firma Microsoft nie zapewnia wsparcia dla produktów innych firm.