3 formuły Excel do czyszczenia danych sprzedażowych

Habib Benabdeslam
Habib Benabdeslam
9 min czytania
Formuły Excel do czyszczenia danych sprzedażowych
3 formuły Excel do czyszczenia danych sprzedażowych
Spis treści
  1. Jak wyodrębnić domeny z adresów e-mail
    1. Wyodrębnianie domeny w starszej wersji Excela
    2. Sprawdź wyjątki przed skopiowaniem formuły
    3. Czyszczenie i formatowanie numerów telefonów
    4. Czyszczenie nazw firm
  2. FAQ

Skorzystaj z tych formuł Excel do czyszczenia danych sprzedażowych, gdy przed importem do CRM chcesz wyodrębnić domeny e-mail, ujednolicić numery telefonów i uporządkować nazwy firm. Zachowaj oryginalne kolumny, a każdy wynik zapisuj w nowej kolumnie, aby łatwo sprawdzić wprowadzone zmiany.

Zacznę od kilku kłopotliwych przypadków: numeru telefonu rozpoczynającego się od zera, adresu e-mail bez separatora oraz nazwy firmy zawierającej słowo przypominające przyrostek formy prawnej. Formuła, która działa w pierwszym poprawnym wierszu, po skopiowaniu w dół może uszkodzić takie dane.

W tym poradniku omówimy trzy zadania:

📌 Podsumowanie dla zabieganych

Przygotuj kolumny z danymi sprzedażowymi do kontrolowanego importu do CRM za pomocą trzech precyzyjnych przekształceń w Excelu. Zachowaj wartości źródłowe obok wyników i sprawdź wyjątki, zanim cokolwiek zastąpisz.

  • Dane wejściowe: adresy e-mail, numery telefonów zapisane jako tekst i nazwy firm.
  • Wynik: wyodrębnione domeny, numery telefonów bez wybranych znaków interpunkcyjnych oraz nazwy firm oczyszczone według jasno określonej reguły przyrostków.

Jak wyodrębnić domeny z adresów e-mail

W Excelu domeny z listy adresów e-mail można wyodrębnić na dwa sposoby:

Pierwszy sposób to funkcja TEXTAFTER, dostępna w Microsoft 365, Excelu dla sieci Web i Excelu 2024. Sprawdź aktualną dokumentację funkcji Microsoft, aby upewnić się, że obsługuje ją Twoja wersja programu.

Drugi sposób to połączenie funkcji RIGHT, LEN i FIND — dla wszystkich, którzy nie mogą skorzystać z funkcji „TEXTAFTER”.

Aby wyodrębnić domenę z adresu e-mail:

  1. Wpisz formułę =TEXTAFTER w polu formuły
  2. Wybierz komórkę zawierającą pierwszy adres e-mail (np. B3)
  3. Dodaj @ jako wyszukiwany tekst

Formuła w Excelu będzie wyglądać tak:

=TEXTAFTER(B3,"@")
Domena wyodrębniona z adresu e-mail
Domena wyodrębniona z adresu e-mail

Aby zastosować formułę do wszystkich adresów e-mail, przeciągnij uchwyt w prawym dolnym rogu komórki z pierwotną formułą.

Domeny wyodrębnione z listy adresów e-mail
Domeny wyodrębnione z listy adresów e-mail

Wyodrębnianie domeny w starszej wersji Excela

Teraz pokażę, jak wyodrębnić domenę z adresu e-mail w starszej wersji Excela, która nie obsługuje funkcji „TEXTAFTER”.

Użyjemy formuły =RIGHT(B3,LEN(B3)-FIND("@",B3)).

Wklej ją w polu formuły i zastosuj do komórki zawierającej pierwszy adres e-mail.

Pierwsza domena wyodrębniona za pomocą formuły =RIGHT(B3,LEN(B3)-FIND("@",B3))
Pierwsza domena wyodrębniona za pomocą formuły =RIGHT(B3,LEN(B3)-FIND("@",B3))

Jak działa ta formuła? Funkcja RIGHT pobiera znaki od końca adresu e-mail, a FIND i LEN wspólnie ustalają, ile dokładnie znaków należy wyodrębnić.

Weźmy jako przykład john.doe123@abc.com — ten adres ma łącznie 19 znaków:

LEN(B3) zwraca 19 znaków.

Funkcja FIND wyszukuje symbol @ w adresie john.doe123@abc.com i wskazuje, że jest on 12. znakiem:

FIND("@",B3) zwraca 12.

Odejmujemy 12 od 19, a otrzymana wartość 7 trafia do funkcji RIGHT. Excel pobiera więc siedem ostatnich znaków i zwraca wynik abc.com.

Aby zastosować formułę do wszystkich komórek, przeciągnij w dół uchwyt w prawym dolnym rogu komórki z pierwotną formułą.

Formuła =RIGHT(B3,LEN(B3)-FIND("@",B3)) zastosowana do wyodrębnienia wszystkich domen z listy adresów e-mail
Formuła =RIGHT(B3,LEN(B3)-FIND("@",B3)) zastosowana do wyodrębnienia wszystkich domen z listy adresów e-mail

Jeśli wolisz pracować z plikiem CSV, możesz wyodrębnić domeny w Datablist, a następnie porównać wyniki z kolumną źródłową.

Sprawdź wyjątki przed skopiowaniem formuły

TEXTAFTER(B3,"@") zwraca błąd, jeśli brakuje separatora. Przykładowa formuła ułatwiająca kontrolę to =IFERROR(LOWER(TRIM(TEXTAFTER(B3,"@"))),"Sprawdź e-mail"). Wyodrębnia ona tekst, ale nie potwierdza, czy adres istnieje ani czy można na niego dostarczyć wiadomość. Odrzucaj wiersze zawierające kilka adresów lub kilka znaków @, zamiast uznawać wyodrębnioną końcówkę za wiarygodną domenę firmy.

Przykładowo alex@example.com zwróci example.com, natomiast alex.example.com wymaga sprawdzenia. Zachowaj pełny adres e-mail obok domeny, aby dwie osoby z tej samej firmy nie zostały przypadkowo scalone w jeden kontakt.

Czyszczenie i formatowanie numerów telefonów

Teraz wyczyścimy i sformatujemy numery telefonów w dwóch krokach.

Pierwszy krok polega na oczyszczeniu numerów za pomocą następującej formuły:

=TEXTJOIN("",TRUE,TEXTSPLIT(B3,{"(",")","-"," ","."},,TRUE))

Usuwa ona wyłącznie wymienione znaki i zwraca tekst. Nie dodawaj +0 ani nie konwertuj wyniku na liczbę w inny sposób: spowoduje to usunięcie zer wiodących oraz międzynarodowego znaku +. Oryginalną kolumnę z telefonami również importuj jako tekst — formuła nie odzyska zer, które Excel już usunął. Microsoft opisuje to zachowanie w poradniku dotyczącym zer wiodących.

Funkcja TEXTSPLIT wymaga obsługiwanej wersji Excela. W tym przykładzie użyto angielskich nazw funkcji i przecinków jako separatorów argumentów; lokalna wersja programu może stosować inne nazwy lub separatory. Formuła nie weryfikuje kodów krajów, numerów wewnętrznych ani tego, czy numer jest aktywny.

Tak wygląda zastosowanie tej formuły:

Oczyszczony numer telefonu
Oczyszczony numer telefonu

Zastosuj formułę do wszystkich numerów, przeciągając w dół uchwyt w prawym dolnym rogu pierwotnej komórki, jak pokazano poniżej.

Formuła czyszcząca zastosowana do wszystkich numerów telefonów
Formuła czyszcząca zastosowana do wszystkich numerów telefonów

Przykładowo tekst 020 7946 0958 zmieni się w 02079460958, a +44 (20) 7946-0958 w +442079460958. Numery wewnętrzne przechowuj osobno, a litery i nieoczekiwane znaki oznaczaj do sprawdzenia. Numer telefonu jest identyfikatorem, a nie wartością służącą do obliczeń.

Poniższe zrzuty ekranu przedstawiają opcjonalny amerykański format wyświetlania. Używaj go wyłącznie dla zweryfikowanych, dziesięciocyfrowych numerów z USA i tylko w osobnej kolumnie. W eksporcie międzynarodowym i imporcie do CRM zachowaj oczyszczony tekst — ten etap nie służy do międzynarodowej normalizacji numerów.

Utwórz osobną kolumnę o nazwie Sformatowane numery telefonów.

Nowa kolumna na sformatowane numery telefonów
Nowa kolumna na sformatowane numery telefonów

Wybierz komórkę pod nagłówkiem i użyj skrótu „Command” + „1” w Excelu, aby otworzyć wszystkie opcje formatowania.

Wybierz opcję formatowania „Special”.

Opcje formatowania w Excelu
Opcje formatowania w Excelu

Wybierz „Phone Numbers” oraz kraj, z którego pochodzą numery.

Wybór opcji formatowania numerów jako amerykańskie numery telefonów
Wybór opcji formatowania numerów jako amerykańskie numery telefonów

💡 Ważne

Formaty liczb w Excelu zmieniają jedynie sposób wyświetlania komórek liczbowych. Nie dodają brakującego kodu kraju ani nie weryfikują numeru telefonu. W przypadku listy międzynarodowej zachowaj wartości tekstowe i sprawdź dane o kraju, zanim wybierzesz osobny proces czyszczenia telefonów.

Teraz zamknij panel i sprawdź, czy Excel zastosował format.

Wystarczy upewnić się, że w polu formatu przy tej kolumnie widnieje „Special”. Odpowiednie miejsce zaznaczono na poniższym obrazie.

Kolumna Excela sformatowana pod kątem numerów telefonów z USA
Kolumna Excela sformatowana pod kątem numerów telefonów z USA

Ostatni krok to wpisanie w tej kolumnie formuły, która przeniesie oczyszczone numery do nowej kolumny przygotowanej do wyświetlania numerów z USA.

W tym przykładzie, przeznaczonym wyłącznie do wyświetlania sprawdzonych numerów z USA, formuła =VALUE(D3) przekazuje do kolumny wartość liczbową. Komórkę D3 pozostaw bez zmian jako tekst. Samo odwołanie =D3 nadal zwraca tekst, więc nie zostanie objęte liczbowym formatem numeru telefonu.

Prosta formuła przenosząca numery telefonów do nowej kolumny
Prosta formuła przenosząca numery telefonów do nowej kolumny

Po wpisaniu formuły naciśnij „ENTER”, aby ją zastosować.

Jeśli wszystko zostało wykonane poprawnie, wynik powinien wyglądać następująco:

Sformatowany amerykański numer telefonu w Excelu
Sformatowany amerykański numer telefonu w Excelu

Przeciągnij uchwyt w prawym dolnym rogu komórki w dół, aby zastosować formatowanie do wszystkich numerów telefonów w Excelu, jak pokazano poniżej:

Wszystkie numery telefonów w formacie amerykańskim
Wszystkie numery telefonów w formacie amerykańskim

Czyszczenie nazw firm

Czyszczenie nazw firm to jedno z najważniejszych zadań, które warto wykonać przed rozpoczęciem Outboundu do potencjalnych klientów. Dlatego nie mogło go zabraknąć w tym poradniku.

Stosuj regułę usuwania przyrostków, którą łatwo wyjaśnić. Funkcja SUBSTITUTE zastępuje pasujący tekst wszędzie, gdzie go znajdzie. Przykładowo =SUBSTITUTE("Acme Consulting"," Co","") zwróci Acmensulting, usuwając część właściwej nazwy. Dopasowanie rozróżnia wielkość liter: użycie " co" pozostawi nazwę Acme Consulting bez zmian, chyba że wcześniej zamienisz tekst źródłowy na małe litery. Łańcuch kilku zamian zależy także od ich kolejności. Nie należy stosować tak szerokiego zestawu zamian do kolumny przeznaczonej do CRM.

Jeśli zatwierdzona reguła brzmi usuń znajdujący się na końcu odstęp i skrót LLC, zastosuj:

=IF(RIGHT(TRIM(B4),4)=" LLC",TRIM(LEFT(TRIM(B4),LEN(TRIM(B4))-4)),TRIM(B4))

Ta reguła jest celowo precyzyjna. Acme LLC zmieni się w Acme, natomiast Acme LLC Services i Acme Consulting pozostaną bez zmian. Formuła nie obejmuje zapisu L.L.C., wszystkich form prawnych ani wszystkich krajów. Jeśli wymagają tego Twoje dane, dodaj osobno sprawdzone reguły.

Poniższe zrzuty ekranu przedstawiają starszy przykład oparty na szerokim zastępowaniu tekstu. W wyniku końcowym zastosuj podaną wyżej precyzyjną regułę i przed zaakceptowaniem zmiany porównaj rezultat z oryginalną nazwą.

Formuła zmieniająca nazwy firm na małe litery i usuwająca przyrostki
Formuła zmieniająca nazwy firm na małe litery i usuwająca przyrostki

Jak działa bardziej precyzyjna formuła: TRIM usuwa standardowe spacje z początku i końca tekstu, RIGHT sprawdza cztery ostatnie znaki, a LEFT usuwa wyłącznie zweryfikowane zakończenie. W standardowych formułach Excela porównanie nie rozróżnia wielkości liter. Nie zakładaj, że TRIM usuwa każdy znak odstępu Unicode.

Tak wygląda efekt zastosowania formuły czyszczącej nazwę firmy:

Zastosowana formuła do czyszczenia nazwy firmy
Zastosowana formuła do czyszczenia nazwy firmy

Nie zapomnij przeciągnąć w dół uchwytu w prawym dolnym rogu komórki z pierwotną formułą, aby zastosować ją do wszystkich nazw firm, jak pokazano poniżej:

Zastosowanie formuły czyszczącej do całej listy nazw firm
Zastosowanie formuły czyszczącej do całej listy nazw firm

Pozostaje jeszcze jedna kwestia: nazwy firm nie są zapisane wielkimi literami na początku wyrazów.

Używaj =PROPER(D4) tylko wtedy, gdy oczekiwanym wynikiem jest zapis każdego wyrazu wielką literą. Funkcja zmienia IBM na Ibm, dlatego w razie potrzeby zachowaj oficjalną pisownię marki lub dodaj konkretne wyjątki. Zrzuty ekranu pokazują przekształcenie, ale nie gwarantują uzyskania poprawnej nazwy handlowej.

Formuła zapisująca nazwy firm wielkimi literami na początku wyrazów
Formuła zapisująca nazwy firm wielkimi literami na początku wyrazów

Tak wygląda prawidłowo wykonane przekształcenie:

Zastosowana formuła zmieniająca wielkość liter w nazwie firmy
Zastosowana formuła zmieniająca wielkość liter w nazwie firmy

Na koniec przeciągnij uchwyt w prawym dolnym rogu komórki w dół, aby zastosować formułę do całej listy nazw firm.

Formuła zastosowana do wszystkich nazw firm
Formuła zastosowana do wszystkich nazw firm

Jeśli masz już listę zatwierdzonych starych i nowych nazw, skorzystaj z procesu zamiany wielu wartości, aby zastosować te mapowania.

W przypadku bardziej zróżnicowanych nazw alternatywę znajdziesz w poradniku czyszczenia nazw firm z użyciem AI. Niezależnie od wybranej metody zachowaj oryginalne wartości oraz sprawdź przyrostki form prawnych, akronimy i niejednoznaczne przypadki.

Najczęściej zadawane pytania

Jak wyodrębnić domeny z adresów e-mail w Excelu?

W nowszych wersjach Excela możesz użyć funkcji TEXTAFTER, a w starszych — połączenia funkcji RIGHT, LEN i FIND.

Jak najlepiej wyczyścić numery telefonów w Excelu?

Przechowuj numery telefonów jako tekst, usuwaj wyłącznie wybrane znaki interpunkcyjne i sprawdzaj zera wiodące, kody krajów oraz numery wewnętrzne. Liczbowy format wyświetlania to opcjonalny, osobny przykład przeznaczony tylko dla USA — nie służy do weryfikacji ani międzynarodowej normalizacji numerów.

Czy Excel sformatuje numery telefonów spoza USA?

Nie stosuj amerykańskiego formatu wyświetlania do listy obejmującej wiele krajów. Zachowaj oryginalny tekst oraz informacje o kraju, a następnie wybierz jasno określone reguły normalizacji międzynarodowej lub odpowiednie narzędzie do przetwarzania numerów. Samo formatowanie nie potwierdza poprawności numeru.

Jak usunąć LLC, Inc. i Ltd. z nazw firm w Excelu?

Zacznij od sprawdzonej reguły dotyczącej przyrostka na końcu nazwy, na przykład usuwania końcowego LLC. Unikaj nieograniczonego zastępowania fragmentów tekstu wewnątrz nazw. Wielkość liter traktuj jako osobną decyzję, ponieważ funkcja PROPER zmienia akronimy i oficjalną pisownię marek.

Co zrobić, gdy Excel nie ma funkcji TEXTAFTER?

Możesz użyć formuł zgodnych ze starszymi wersjami Excela, na przykład połączenia funkcji RIGHT, LEN i FIND, albo sprawdzić, czy Twoja bieżąca edycja programu obsługuje TEXTAFTER.