Sztuczki transformacji tekstu w programie Excel

Przez Davida H. Ringstroma, CPA

Dane przychodzą na Twoje biurko z wielu źródeł, a w wielu przypadkach nie są one w takim formacie, jakiego potrzebujesz. Na przykład, użytkownik mógł zostawić włączoną blokadę CAPS LOCK podczas wpisywania szeregu adresów. Albo wyeksportowałeś dane z programu, który wypisuje pola ze spacjami na początku lub na końcu. Na szczęście, nie ma potrzeby ponownego wpisywania listy. Program Excel oferuje cztery łatwe w użyciu funkcje, które mogą szybko przekształcić dane do formatu, którego potrzebujesz.

UPPER – Jak pokazano na rysunku 1, funkcja ta przetwarza tekst na wielką literę, więc wyrażenie Old McDonald’s Farm staje się STARĄ MCDONALD’S FARMĄ. Ta funkcja ma tylko jeden argument, więc wystarczy wpisać =UPPER(x), gdzie x jest komórką, która zawiera tekst, który chcesz przekonwertować.

LOWER – Funkcja ta przekształca tekst na wszystkie małe litery, dzięki czemu Old McDonald’s Farm staje się starą farmą mcdonald’s. Tak jak w przypadku funkcji UPPER, wystarczy użyć formularza =LOWER(x), gdzie x jest komórką, którą chcesz przekształcić na małe litery.

PROPER – Funkcja ta kapitalizuje pierwszą literę każdego słowa, więc wyrażenie stary mcdonald’s farm staje się Old Mcdonald’s Farm. Funkcja ta ma taką samą postać jak pozostałe funkcje: =PROPER(x), gdzie x jest komórką do konwersji. Jednakże, jak widać na Rysunku 1, funkcja PROPER może w niektórych przypadkach stwarzać problemy:

  • Pierwsze D w McDonald’s nie jest skapitalizowane.
  • S w McDonald’s jest pisane wielką literą – oprócz kapitalizacji pierwszej litery słowa, PROPER pisze również kolejną literę po apostrofie lub pojedynczym cytacie.
  • Inne przedmioty, których należy szukać to akronimy lub inicjały, gdzie ABC COMPANY stałoby się Abc Company, a BOB’S TV SERVICE stałoby się Bob’S TV Service
  • .

Będziesz musiał ręcznie usuwać problemy, takie jak ABC lub TV, które są konwertowane na Abc i Tv, ale możesz użyć funkcji Replace w Excelu, aby usunąć inne problemy, jak wyjaśnię za chwilę.

TRIM – Czasami odziedziczysz dane, które mają obce spacje na początku, środku lub końcu ciągu, takie jak “Old McDonald’s Farm”. W takich przypadkach można użyć funkcji TRIM do usunięcia wszystkich obcych spacji – funkcja automatycznie zatrzymuje jedną spację pomiędzy każdym słowem. Funkcja ma postać =TRIM(x), gdzie x jest odniesieniem do komórki.

Rysunek 1: Te cztery funkcje mogą szybko przekształcać tekst bez konieczności ponownego wpisywania.

Porada eksperta : Czasami pomocne jest posiadanie białej spacji, która jest początkiem niektórych słów, jak pokazano na rysunku 2. Zamiast ręcznie wprowadzać spacje na początku komórek od A3 do A5, spróbuj tego zamiast tego: :

  1. Naciśnij Ctrl-1, aby wyświetlić okno dialogowe Format Komórek.
  2. Kliknąć na zakładkę Wyrównanie i ustawić pole Indent na 1 lub 2.
  3. Kliknij OK, a Twój tekst zostanie automatycznie wcięty, jak pokazano na Rysunku 3.
  4. Wszystkie wgniecenia można usunąć w dowolnym momencie, zmieniając wartość Indent z powrotem na 0.

Rysunek 2 : Użyj ustawienia Indent, aby przesunąć tekst w prawo bez użycia spacji.

Rysunek 3 : Ustawić poziom wgłębienia na 1 lub 2, aby utworzyć niewielkie wgłębienie. Przeliczyć wzory na wartości

Zazwyczaj, gdy korzystasz z tych funkcji, masz dane w jednej kolumnie, np. w kolumnie A na rysunku 1. Wpiszesz formułę w kolumnie B, a następnie skopiujesz ją w dół na długość swojej listy. Presto, twoja lista zostanie przekształcona, ale nie spiesz się i nie usuwaj jeszcze kolumny A. Musisz najpierw skopiować formuły z kolumny B do schowka, a następnie użyć polecenia Wklej Specjalny, aby przekonwertować formuły na wartości:

  1. Skopiuj formuły do schowka.
  2. Kliknij prawym przyciskiem myszy na pierwszą skopiowaną komórkę, a następnie wybierz opcję Wklej specjalne.
  3. Jak pokazano na Rysunku 4, można kliknąć dwukrotnie na słowo Wartości, lub kliknąć raz na Wartości, a następnie kliknąć OK.

W tym momencie możesz usunąć dane z kolumny A, ponieważ Twoje przekonwertowane dane są w kolumnie B jako tekst, a nie jako formuły, które odnoszą się do kolumny A. Jeśli zapomnisz przekonwertować formuły z kolumny B na wartości przed usunięciem kolumny A, wtedy skończysz z serią błędów #REF! Po prostu wciśnij Ctrl-Z aby cofnąć usunięcie kolumny, a następnie wykonaj polecenie Wklej Wartości Specjalne.

Rysunek 4 : Polecenie Wklej wartości specjalne konwertuje formuły na tekst.

A PROPER Clean-up

Po przekonwertowaniu formuł na wartości, możesz użyć funkcji Replace, aby rozwiązać takie problemy, jak to brzydkie ‘S w McDonaldzie’ S. Aby to zrobić, naciśnij klawisz Ctrl-H, aby wyświetlić okno dialogowe Replace. Jak pokazano na Rysunku 5, wpisz ‘S w polu Find, a ‘s w polu Replace With. Powtórz ten proces dla wszystkich innych problemów, takich jak zastąpienie Mcd przez McD, Tv przez TV, i tak dalej.

Rysunek 5 : Użyj polecenia Replace, aby szybko oczyścić problemy z kapitalizacją, które wprowadza PROPER.

O autorze:

Przeczytaj więcej artykułów Davida Ringstroma. David H. Ringstrom, CPA stoi na czele firmy Accounting Advisors, Inc. z siedzibą w Atlancie, świadczącej usługi szkoleniowe i doradcze w zakresie baz danych w całym kraju. Skontaktuj się z Davidem pod adresem [chronione pocztą elektroniczną] lub śledź go na Twitter . David mówi na konferencjach o programie Microsoft Excel i prezentuje webcasty dla kilku dostawców CPE, w tym dla partnera AccountingWEB .