Wspólne formuły czyszczenia danych w programie Excel

formuły programu Excel

Od lat używam tej publikacji jako źródła nie tylko do opisywania, jak robić rzeczy, ale także do prowadzenia rejestru, aby móc później sprawdzić! Dzisiaj mieliśmy klienta, który przekazał nam plik danych klienta, który był katastrofą. Praktycznie każde pole zostało źle sformatowane i; w rezultacie nie mogliśmy zaimportować danych. Chociaż istnieje kilka świetnych dodatków do programu Excel do czyszczenia za pomocą Visual Basic, uruchamiamy pakiet Office dla komputerów Mac, który nie obsługuje makr. Zamiast tego szukamy prostych formuł, które pomogą. Pomyślałem, że podzielę się niektórymi z nich, aby inni mogli z nich korzystać.

Usuń znaki nieliczbowe

Systemy często wymagają wpisywania numerów telefonów w określonym 11-cyfrowym wzorze z kodem kraju i bez znaków interpunkcyjnych. Jednak ludzie często wprowadzają te dane z myślnikami i kropkami. Oto świetny przepis na usunięcie wszystkich znaków nienumerycznych w programie Excel. Formuła sprawdza dane w komórce A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Teraz możesz skopiować wynikową kolumnę i użyć Edycja> Wklej wartości nadpisać dane z odpowiednio sformatowanym wynikiem.

Oceń wiele pól za pomocą operatora OR

Często usuwamy niekompletne rekordy z importu. Użytkownicy nie zdają sobie sprawy, że nie zawsze trzeba pisać złożone formuły hierarchiczne i że zamiast tego można napisać instrukcję OR. W tym przykładzie poniżej chcę sprawdzić A2, B2, C2, D2 lub E2 pod kątem brakujących danych. Jeśli brakuje jakichkolwiek danych, zwrócę 0, w przeciwnym razie 1. To pozwoli mi posortować dane i usunąć niekompletne rekordy.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Przytnij i połącz pola

Jeśli dane mają pola Imię i Nazwisko, ale import zawiera pełne pole nazwy, możesz starannie połączyć te pola za pomocą wbudowanej funkcji Concatenate programu Excel, ale pamiętaj, aby użyć polecenia TRIM, aby usunąć wszelkie puste spacje przed lub po tekst. Całe pole zawijamy TRIM w przypadku, gdy jedno z pól nie zawiera danych:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Sprawdź prawidłowy adres e-mail

Dość prosta formuła, która szuka zarówno znaków @, jak i. w adresie e-mail:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Wyodrębnij imię i nazwisko

Czasami problem jest odwrotny. Twoje dane mają pełne pole z imieniem i nazwiskiem, ale musisz przeanalizować imię i nazwisko. Te formuły szukają spacji między imieniem i nazwiskiem i w razie potrzeby przechwytują tekst. IT obsługuje również, jeśli nie ma nazwiska lub jest pusty wpis w A2.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

I nazwisko:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Ogranicz liczbę znaków i dodaj…

Czy kiedykolwiek chciałeś uporządkować opisy w meta? Jeśli chcesz pobrać zawartość do programu Excel, a następnie przyciąć ją do użycia w polu Meta Description (150 do 160 znaków), możesz to zrobić za pomocą tej formuły z Moje miejsce. Wyraźnie przerywa opis w spacji, a następnie dodaje…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Oczywiście nie są one wyczerpujące… tylko kilka szybkich formuł, które pomogą Ci szybko zacząć! Jakich innych formuł używasz? Dodaj je w komentarzach, a dam ci uznanie, gdy zaktualizuję ten artykuł.

Co o tym myślisz?

Ta strona używa Akismet do redukcji spamu. Dowiedz się, jak przetwarzane są dane komentarza.