Przestań zmagać się z tekstem w Excelu: Te 8 narzędzi zmienia zasady gry
Excel świetnie radzi sobie z liczbami, ale jest irytująco wybredny, jeśli chodzi o tekst. Kiedy traktujesz komórki jak dokument Word, ręcznie wpisując jednostki lub ignorując ukryte spacje, to skazujesz się na porażkę. Oto jak obsługiwać tekst w Excelu, nie psując logiki swojego arkusza.
Konwertowanie „fałszywego” tekstu na liczby
Domyślnie Excel wyrównuje liczby do prawej, a tekst do lewej. Jeśli twoje liczby przylegają do lewej strony komórki lub widzisz zielony trójkąt, Excel uważa je za słowa, więc wszelkie działania arytmetyczne, które wykonasz, mogą nie działać zgodnie z oczekiwaniami.
Aby to naprawić ręcznie, zaznacz komórki, kliknij ikonę wykrzyknika i wybierz „Konwertuj na liczbę.”
Jednak jeśli zaimportowałeś te dane i prawdopodobnie dodasz więcej później, najlepszym rozwiązaniem jest operator podwójnej negacji (--). W dodatkowej kolumnie wpisz znak równości (=), dodaj dwa minusy (--), a następnie kliknij pierwszą komórkę zawierającą „fałszywe liczby”. W moim przypadku stosuję to rozwiązanie w kolumnie Waga.
Jeśli używasz tabeli w Excelu, po naciśnięciu klawisza Enter formuła automatycznie wypełnia komórki w dół i - co najważniejsze - natychmiast przekształca nowe dane wklejone do tabeli.
Jeśli wynik wciąż wygląda jak tekst, musisz zmienić format liczbowy komórki. Zaznacz komórki, a w grupie Liczby na karcie Narzędzia główne wybierz „Ogólne” lub „Liczba”.
Magia niestandardowego formatowania liczb
Wpisywanie jednostek bezpośrednio do komórki (np. „69 lbs”) zamienia dane w ciąg tekstowy, co uniemożliwia wykonywanie obliczeń wykorzystujących tę komórkę.
Aby upewnić się, że Excel rozpozna komórkę jako liczbową, zachowując jednocześnie jednostkę, po usunięciu tekstu zaznacz kolumnę, naciśnij Ctrl+1, a na karcie Liczby w oknie Formatowanie komórek wybierz „Niestandardowy”. Następnie w polu Typ wpisz 0 "lbs" (lub jednostkę, której używasz). Zwróć uwagę na cudzysłowy: mówią one Excelowi, żeby traktował tekst jako etykietę, pozostawiając podstawową liczbę bez zmian.
Gdy klikniesz „OK”, aby zamknąć okno dialogowe, zobaczysz, że mimo iż komórki zawierają jednostki, pasek formuły wyświetla tylko liczby. Potwierdza to, że twoje dane są czyste i gotowe do użycia w obliczeniach.
Zanim przejdziesz w pełni na samodzielne tworzenie kodów niestandardowych, sprawdź inne kategorie na karcie Liczby. W przypadku danych finansowych lub procentowych zawsze używaj wbudowanych formatów zamiast własnych ciągów. Radzą sobie one znacznie lepiej z takimi rzeczami jak kolorowanie liczb ujemnych czy wyrównanie w księgowości niż ręczna poprawka.
Jeśli wolisz bardziej przejrzysty wygląd, po prostu podaj jednostkę w nagłówku tabeli, a dane poniżej pozostaw w postaci surowych liczb.
Ekstrakcja chirurgiczna: nowoczesna rodzina TEXT
Kreator Tekst jako kolumny jest klasyczny, ale jeśli twoje dane się zmienią, musisz ponownie uruchomić kreatora. Jeśli używasz Excela dla Microsoft 365, Excela 2024 lub nowszego, albo Excela w wersji webowej, powinieneś zamiast tego korzystać z funkcji z rodziny TEKST:
Funkcja | Cel |
|---|---|
TEKSTPRZED | Wyodrębnia wszystko przed określonym znakiem. |
TEKSTPO | Wyodrębnia wszystko po określonym znaku. |
|
PODZIEL_TEKST | Dzieli ciąg znaków na wiele kolumn lub wierszy. |
Na przykład, aby wyodrębnić imiona i nazwiska osób z tych adresów e-mail i jednocześnie je kapitalizować, użyj tej formuły:
=PROPER(TEXTBEFORE([@Email],"@"))
To znajduje znak „@” i pobiera wszystko po jego lewej stronie. Ponieważ jest to formuła, pozostaje aktywna — zaktualizuj e-mail lub dodaj nowy, a imię osoby zaktualizuje się natychmiast.
Podobnie, jeśli chcesz wyodrębnić tylko domenę, użyj funkcji TEXTAFTER. Jednak jeśli użyjesz funkcji TEXTSPLIT do podzielenia jednej kolumny na dwie, musisz wykonać tę operację w zwykłych komórkach, ponieważ wynik rozlewający się nie może być wyświetlany w tabeli programu Excel.
Dla ogromnych zbiorów danych użyj Power Query, dedykowanego silnika, który potrafi dzielić, przycinać i przekształcać miliony wierszy tekstu, nie spowalniając przy tym skoroszytu.
Zautomatyzowana higiena danych: Czyszczenie niewidocznego bałaganu
Importowanie danych z zewnętrznych źródeł do Excela często wprowadza niewidoczny śmieć, taki jak spacje i znaki nie drukowane. W tym przykładzie, gdy wykonuję funkcję LICZ.ZNAKÓW na kodzie danych zaimportowanym z internetu, powinienem otrzymać 11: siedem dla słowa „Produkt”, jeden dla spacji w środku i trzy dla „452”. Jednak otrzymuję 15, co oznacza, że istnieją ukryte znaki.
Stareńkie rozwiązanie to połączenie funkcji TRIM (do usuwania dodatkowych spacji) i CLEAN (do usuwania znaków niedrukowalnych):
=TRIM(CLEAN([@Data]))
Jednak to nadal zwraca 12, a nie 11, co oznacza, że istnieje ukryty znak, którego funkcja TRIM całkowicie nie widzi.
Jeśli dane są importowane z internetu, ten tajemniczy znak to niemal zawsze spacja nierozdzielająca, której funkcja TRIM nie wykryje. W takim przypadku użyj funkcji SUBSTITUTE, która może zamienić taką spację w stylu internetowym na standardową przed zastosowaniem trim:
=TRIM(CLEAN(SUBSTITUTE([@Data],CHAR(160)," ")))Poprzez zagnieżdżenie tych funkcji zmuszasz Excel do przekształcenia twardych spacji—CHAR(160)—w coś, co funkcja TRIM może rozpoznać i usunąć. Kiedy ponownie uruchomisz sprawdzenie funkcji LEN, otrzymasz idealną wartość 11.
Łączenie tekstu: Dlaczego TEXTJOIN jest lepszy niż znak &
Stary, tradycyjny sposób łączenia tekstu w Excelu polegał na użyciu znaku & (ampersand):
=[@First]&" "&[@Last]
Jednak jeśli komórka, do której się odwołujesz, jest pusta, otrzymasz ciąg podwójnych spacji lub powtarzających się separatorów.
Bardziej zaawansowanym rozwiązaniem w Excelu dla Microsoft 365, Excelu w wersji internetowej lub Excelu 2019 i nowszych jest funkcja TEXTJOIN. Pozwala ona ustawić separator tylko raz i, co istotne, zawiera ustawienie umożliwiające ignorowanie pustych komórek:
=TEXTJOIN(", ",TRUE,T_Addresses[@[First_Line]:[City]])
Ustawiając drugi argument na TRUE, Excel pomija wszystkie wartości null, zapewniając, że Twoja lista wygląda profesjonalnie, niezależnie od przerw w danych.
Dla długich opisów lub połączonych ciągów kliknij „Zawijaj tekst” na karcie Narzędzia główne, aby dane były widoczne bez ręcznego zmieniania szerokości kolumn. Aby wymusić przerwę w linii, naciśnij Alt+Enter.
Rozpoznawanie wzorców: Flash Fill i Formuła na podstawie przykładu
Kiedy wzorzec tekstu jest zbyt skomplikowany, aby rozwiązać go za pomocą formuły, możesz pozwolić Excelowi wykonać ciężką pracę za Ciebie. W tym przykładzie, gdy tylko zacznę wpisywać kody pocztowe z adresu, Flash Fill wykrywa wzorzec i proponuje wypełnienie kolumny. Wystarczy nacisnąć Tab lub Enter, aby zaakceptować sugestię.
Możesz także nacisnąć Ctrl+E, aby wymusić ten proces w Excelu, jeśli sugestie nie pojawiają się automatycznie.
Narzędzie Formula by Example idzie o krok dalej, faktycznie generując dla Ciebie podstawową formułę. Zacznij wpisywać swój wzorzec, a gdy Excel zasugeruje formułę, kliknij „Zastosuj”. Można argumentować, że jest to bardziej przydatne niż Flash Fill, ponieważ otrzymana formuła reaguje na wszelkie zmiany w danych źródłowych i działa również na dodatkowych danych dodanych do tabeli.
W momencie pisania, Formula by Example jest dostępna dla wszystkich w Excelu w wersji internetowej (wewnątrz tabeli) lub dla osób posiadających licencję Copilot w Excelu na Windows.
Gdy już zapewnisz techniczną poprawność w swoim arkuszu Excel, oczyszczając tekst, ostatnim krokiem jest upewnienie się, że układ nie wpływa na funkcjonalność. Jeśli masz wiersz danych, w którym ta sama wartość tekstowa musi obejmować kilka kolumn, powstrzymaj się od scalania — zamiast tego użyj opcji „Wyśrodkuj w zaznaczeniu”, aby zachować profesjonalny wygląd bez utraty możliwości sortowania i filtrowania.