Oszczędność czasu dzięki operacjom tekstowym w programie Excel

Microsoft Excel to podstawowa aplikacja dla każdego, kto musi pracować z wieloma liczbami, od studentów po księgowych. Ale jego przydatność wykracza poza duże bazy danych vs. Excel Excel. Dostęp - czy arkusz kalkulacyjny może zastąpić bazę danych? Excel vs. Dostęp - czy arkusz kalkulacyjny może zastąpić bazę danych? Jakiego narzędzia należy użyć do zarządzania danymi? Access i Excel oferują zarówno filtrowanie danych, sortowanie, jak i zapytania. Pokażemy Ci, który najlepiej pasuje do twoich potrzeb. ; potrafi też robić wiele wspaniałych rzeczy z tekstem. Funkcje wymienione poniżej pomogą Ci analizować, edytować, konwertować i w inny sposób wprowadzać zmiany w tekście oraz zaoszczędzić wiele godzin nudnej i powtarzalnej pracy.

Nawigacja: edycja nieniszcząca | Znaki o połowie i pełnej szerokości | Funkcje postaci | Funkcje analizy tekstu | Funkcje konwersji tekstu | Funkcje edycji tekstu | Funkcje zamiany tekstu | Funkcje układania tekstu | Przykład ze świata rzeczywistego

Edycja nieniszcząca

Jedną z zasad korzystania z funkcji tekstowych Excel jest zasada nieniszczącej edycji. Mówiąc prościej, oznacza to, że za każdym razem, gdy użyjesz funkcji, aby wprowadzić zmiany w tekście w wierszu lub kolumnie, tekst ten pozostanie niezmieniony, a nowy tekst zostanie umieszczony w nowym wierszu lub kolumnie. Na początku może to być nieco dezorientujące, ale może być bardzo cenne, szczególnie jeśli pracujesz z ogromnym arkuszem kalkulacyjnym, którego odtworzenie byłoby trudne lub niemożliwe, jeśli edycja się nie powiedzie.

Chociaż możesz nadal dodawać kolumny i wiersze do stale powiększającego się gigantycznego arkusza kalkulacyjnego, jednym ze sposobów skorzystania z tego jest zapisanie oryginalnego arkusza kalkulacyjnego w pierwszym arkuszu w dokumencie, a następnie edytowane kopie w innych arkuszach. W ten sposób, bez względu na to, ile wprowadzisz zmian, zawsze będziesz mieć oryginalne dane, z których pracujesz.

Znaki o połowie i pełnej szerokości

Niektóre z omawianych tutaj funkcji odwołują się do zestawów znaków jedno- i dwubajtowych, a zanim zaczniemy, przydatne będzie wyjaśnienie, jakie dokładnie są. W niektórych językach, takich jak chiński, japoński i koreański, każdy znak (lub kilka znaków) będzie miał dwie możliwości wyświetlania: jeden zakodowany w dwóch bajtach (znany jako znak o pełnej szerokości) i jeden zakodowany w pojedynczy bajt (połowa szerokości). Różnicę w tych postaciach możesz zobaczyć tutaj:

Jak widać, znaki dwubajtowe są większe i często łatwiejsze do odczytania. Jednak w niektórych sytuacjach komputerowych wymagane jest jedno z tych rodzajów kodowania. Jeśli nie wiesz, co to oznacza lub dlaczego musisz się tym martwić, jest bardzo prawdopodobne, że nie będziesz musiał o tym myśleć. W takim przypadku w poniższych sekcjach znajdują się funkcje, które odnoszą się konkretnie do znaków o połowie szerokości i pełnej szerokości.

Funkcje postaci

Rzadko zdarza się, że pracujesz z pojedynczymi znakami w programie Excel, ale takie sytuacje czasami się pojawiają. A kiedy to robią, musisz poznać te funkcje.

Funkcje CHAR i UNICHAR

CHAR pobiera numer znaku i zwraca odpowiedni znak; na przykład, jeśli masz listę numerów znaków, CHAR pomoże ci zmienić je w postacie, z którymi jesteś bardziej przyzwyczajony. Składnia jest dość prosta:

= CHAR ([tekst])

[tekst] może przybrać formę odwołania do komórki lub znaku; więc = CHAR (B7) i = CHAR (84) oba działają. Zauważ, że podczas używania CHAR użyje kodowania ustawionego na twoim komputerze; więc twoja = CHAR (84) może być inna niż moja (szczególnie jeśli korzystasz z komputera z systemem Windows, ponieważ używam programu Excel dla komputerów Mac).

Jeśli liczba, na którą konwertujesz, jest liczbą znaków Unicode i korzystasz z programu Excel 2013, musisz użyć funkcji UNICHAR. Poprzednie wersje programu Excel nie miały tej funkcji.

Funkcje CODE i UNICODE

Jak można się spodziewać, CODE i UNICODE robią dokładnie odwrotnie niż funkcje CHAR i UNICHAR: przyjmują znak i zwracają numer wybranego kodowania (lub ustawionego domyślnie na komputerze). Ważną rzeczą do zapamiętania jest to, że jeśli uruchomisz tę funkcję na łańcuchu zawierającym więcej niż jeden znak, zwróci jedynie odwołanie do znaku dla pierwszego znaku w ciągu. Składnia jest bardzo podobna:

= CODE ([tekst])

W tym przypadku [tekst] jest znakiem lub łańcuchem. A jeśli chcesz zamiast kodu domyślnego odniesienia do Unicode, użyjesz UNICODE (ponownie, jeśli masz Excel 2013 lub nowszy).

Funkcje analizy tekstu

Funkcje w tej sekcji pomogą uzyskać informacje o tekście w komórce, co może być przydatne w wielu sytuacjach. Zaczniemy od podstaw.

Funkcja LEN

LEN jest bardzo prostą funkcją: zwraca długość łańcucha. Więc jeśli chcesz policzyć liczbę liter w szeregu różnych komórek, to jest to droga. Oto składnia:

 = LEN ([tekst])

Argument [tekst] to komórka lub komórki, które chcesz policzyć. Poniżej widać, że za pomocą funkcji LEN w komórce zawierającej nazwę miasta “Austin,” zwraca 6. Gdy zostanie użyty w nazwie miasta “South Bend,” zwraca 10. Spacja liczy się jako znak z LEN, więc miej to na uwadze, jeśli używasz go do zliczania liter w danej komórce.

Powiązana funkcja LENB robi to samo, ale działa ze znakami dwubajtowymi. Jeśli policzysz serię czterech dwubajtowych znaków z LEN, wynik wyniósłby 8. W przypadku LENB jest to 4 (jeśli masz włączony DBCS jako domyślny język).

Funkcja ZNAJDŹ

Możesz się zastanawiać, dlaczego użyjesz funkcji o nazwie ZNAJDŹ, jeśli możesz po prostu użyć CTRL + F lub Edycja> Znajdź. Odpowiedź leży w specyfice, z jaką można wyszukiwać za pomocą tej funkcji; zamiast przeszukiwać cały dokument, możesz wybrać, od którego znaku każdego łańcucha rozpocznie się wyszukiwanie. Składnia pomoże wyjaśnić tę mylącą definicję:

= ZNAJDŹ ([znajdź_tekst], [w_tekście], [liczba_początkowa])

[find_text] to ciąg, którego szukasz. [w_tekście] to komórka lub komórki, w których Excel będzie szukał tego tekstu, a [liczba_początkowa] to pierwszy znak, na który będzie patrzył. Należy zauważyć, że w tej funkcji rozróżniana jest wielkość liter. Weźmy przykład.

Zaktualizowałem przykładowe dane, aby numer identyfikacyjny każdego ucznia był sześcioznakową sekwencją alfanumeryczną, z których każda zaczyna się od jednej cyfry, M dla “męski,” sekwencja dwóch liter wskazujących poziom osiągnięć ucznia (HP dla wysokiego, SP dla standardowego, LP dla niskiego i UP / XP dla nieznanego) oraz końcowa sekwencja dwóch liczb. Użyjmy ZNAJDŹ, aby wyróżnić każdego ucznia o wysokich osiągach. Oto składnia, której użyjemy:

= ZNAJDŹ („HP”, A2, 3)

Dzięki temu dowiemy się, czy HP pojawia się po trzecim znaku komórki. Zastosowane do wszystkich komórek w kolumnie ID, możemy na pierwszy rzut oka zobaczyć, czy uczeń osiągał wysokie wyniki, czy nie (zauważ, że 3 zwracane przez funkcję to znak, w którym znaleziono HP). ZNAJDŹ można lepiej wykorzystać, jeśli masz większą różnorodność sekwencji, ale masz pomysł.

Podobnie jak w przypadku LEN i LENB, FINDB jest używany do tego samego celu co FIND, tylko z zestawami znaków dwubajtowych. Jest to ważne ze względu na specyfikację określonego znaku. Jeśli używasz DBCS i określasz czwarty znak za pomocą ZNAJDŹ, wyszukiwanie rozpocznie się od drugiego znaku. FINDB rozwiązuje problem.

Pamiętaj, że FIND rozróżnia małe i wielkie litery, więc możesz wyszukać określoną wielkość liter. Jeśli chcesz skorzystać z alternatywy bez rozróżniania wielkości liter, możesz użyć funkcji SZUKAJ, która pobiera te same argumenty i zwraca te same wartości.

Funkcja DOKŁADNA

Jeśli musisz porównać dwie wartości, aby sprawdzić, czy są one takie same, funkcja DOKŁADNA jest potrzebna. Gdy podasz EXACT z dwoma łańcuchami, zwróci PRAWDA, jeśli są dokładnie takie same, i FALSE, jeśli będą różne. Ponieważ w EXACT rozróżniana jest wielkość liter, zwróci FALSE, jeśli podasz ciągi, które czytają “Test” i “test.” Oto składnia EXACT:

= DOKŁADNE ([tekst 1], [tekst 2])

Oba argumenty są dość oczywiste; to ciągi, które chcesz porównać. W naszym arkuszu kalkulacyjnym użyjemy ich do porównania dwóch wyników SAT. Dodałem drugi wiersz i nazwałem go “Zgłoszone.” Teraz przejdziemy do arkusza kalkulacyjnego z EXACT i zobaczymy, gdzie raportowany wynik różni się od oficjalnego wyniku przy użyciu następującej składni:

= DOKŁADNE (G2, F2)

Powtórzenie tej formuły dla każdego wiersza w kolumnie daje nam to:

Funkcje konwersji tekstu

Funkcje te pobierają wartości z jednej komórki i przekształcają je w inny format; na przykład od liczby do ciągu lub od ciągu do liczby. Istnieje kilka różnych opcji, jak to zrobić i jaki jest dokładny wynik.

Funkcja TEKST

TEKST konwertuje dane liczbowe na tekst i pozwala sformatować je w określony sposób; może to być przydatne, na przykład, jeśli planujesz używać danych Excela w dokumencie Worda. Jak zintegrować dane Excela w dokumencie Worda. Jak zintegrować dane Excela w dokumencie Worda. W tygodniu roboczym prawdopodobnie jest wiele razy kopiowanie i wklejanie informacji z Excela do Worda lub odwrotnie. W ten sposób ludzie często tworzą pisemne raporty… Spójrzmy na składnię, a następnie zobaczmy, jak możesz jej użyć:

= TEKST ([tekst], [format])

Argument [format] pozwala wybrać sposób wyświetlania liczby w tekście. Istnieje wiele różnych operatorów, których możesz użyć do sformatowania tekstu, ale będziemy trzymać się prostych tutaj (aby uzyskać szczegółowe informacje, zobacz stronę pomocy Microsoft Office na stronie TEKST). TEKST jest często używany do przeliczania wartości pieniężnych, więc zaczniemy od tego.

Dodałem kolumnę o nazwie “Czesne” który zawiera numer dla każdego ucznia. Sformatujemy tę liczbę na ciąg, który wygląda trochę bardziej, jakbyśmy byli przyzwyczajeni do czytania wartości pieniężnych. Oto składnia, której użyjemy:

= TEKST (G2, „$ #, ###”)

Użycie tego ciągu formatującego da nam liczby poprzedzone symbolem dolara i zawierać przecinek za setkami miejsc. Oto, co się stanie, gdy zastosujemy go do arkusza kalkulacyjnego:

Każda liczba jest teraz poprawnie sformatowana. Możesz użyć TEKSTU, aby sformatować liczby, wartości walut, daty, godziny, a nawet pozbyć się nieznaczących cyfr. Szczegółowe informacje na temat wykonywania tych wszystkich czynności można znaleźć na stronie pomocy, do której prowadzi link powyżej.

NAPRAWIONA funkcja

Podobnie jak TEKST, funkcja NAPRAWIONA pobiera dane wejściowe i formatuje je jako tekst; jednak FIXED specjalizuje się w konwertowaniu liczb na tekst i daje kilka konkretnych opcji formatowania i zaokrąglania wyników. Oto składnia:

= NAPRAWIONO ([liczba], [miejsca dziesiętne], [no_commas])

Argument [liczba] zawiera odwołanie do komórki, którą chcesz przekonwertować na tekst. [dziesiętne] to opcjonalny argument, który pozwala wybrać liczbę miejsc po przecinku, które zostaną zachowane w konwersji. Jeśli jest to 3, otrzymasz liczbę taką jak 13.482. Jeśli użyjesz liczby ujemnej w przypadku miejsc dziesiętnych, program Excel zaokrągli liczbę. Przyjrzymy się temu w poniższym przykładzie. [no_commas], jeśli ustawione na PRAWDA, wykluczy przecinki z końcowej wartości.

Wykorzystamy to, aby zaokrąglić wartości czesne użyte w ostatnim przykładzie do najbliższego tysiąca.

= NAPRAWIONO (G2, -3)

Po zastosowaniu do wiersza otrzymujemy rząd zaokrąglonych wartości czesnego:

Funkcja VALUE

Jest to przeciwieństwo funkcji TEKST - pobiera dowolną komórkę i zamienia ją na liczbę. Jest to szczególnie przydatne, jeśli importujesz arkusz kalkulacyjny lub kopiujesz i wklejasz dużą ilość danych, a formatowany jest on jako tekst. Oto jak to naprawić:

= WARTOŚĆ ([tekst])

To wszystko. Excel rozpozna zaakceptowane formaty liczb stałych, czasów i dat i skonwertuje je na liczby, które następnie będą mogły być używane z funkcjami numerycznymi i formułami. To jest dość proste, więc pomińmy przykład.

Funkcja DOLLAR

Podobnie jak funkcja TEKST, DOLLAR konwertuje wartość na tekst, ale dodaje także znak dolara. Możesz także wybrać liczbę miejsc po przecinku, które chcesz uwzględnić:

= DOLLAR ([tekst], [miejsca dziesiętne])

Jeśli pozostawisz argument [dziesiętne] pusty, domyślnie będzie to 2. Jeśli podasz argument ujemny dla argumentu [dziesiętne], liczba zostanie zaokrąglona na lewo od dziesiętnej.

Funkcja ASC

Pamiętasz naszą dyskusję na temat znaków jedno- i dwubajtowych? W ten sposób dokonujesz konwersji między nimi. Ta funkcja konwertuje znaki dwubajtowe o pełnej szerokości na znaki jednobajtowe o połowie szerokości. Można go użyć do zaoszczędzenia miejsca w arkuszu kalkulacyjnym. Oto składnia:

= ASC ([tekst])

Dość proste. Wystarczy uruchomić funkcję ASC na dowolnym tekście, który chcesz przekonwertować. Aby zobaczyć to w działaniu, przekonwertuję ten arkusz kalkulacyjny, który zawiera wiele japońskich katakana - często są one renderowane jako znaki o pełnej szerokości. Zmieńmy je na połowę szerokości.

Funkcja JIS

Oczywiście, jeśli możesz dokonać konwersji w jeden sposób, możesz również dokonać konwersji w drugą stronę. JIS konwertuje znaki o połowie szerokości na znaki o pełnej szerokości. Podobnie jak ASC, składnia jest bardzo prosta:

 = JIS ([tekst])

Pomysł jest dość prosty, więc przejdziemy do następnej sekcji bez przykładu.

Funkcje edycji tekstu

Jedną z najbardziej przydatnych rzeczy, które możesz zrobić z tekstem w programie Excel, jest programowe wprowadzanie do niego zmian. Poniższe funkcje pomogą Ci wprowadzić tekst i uzyskać format najbardziej odpowiedni dla Ciebie.

Funkcje GÓRNA, DOLNA i PRAWIDŁOWA

Są to bardzo proste funkcje do zrozumienia. GÓRA powoduje, że tekst jest pisany wielkimi literami, NISKI powoduje, że jest pisany małymi literami, a PRAWIDŁOWY zapisuje wielką literę w każdym słowie, pozostawiając pozostałe litery małymi literami. Nie ma tutaj żadnego przykładu, więc dam ci tylko składnię:

= GÓRNA / DOLNA / PRAWIDŁOWA ([tekst])

Wybierz komórkę lub zakres komórek, w których znajduje się tekst dla argumentu [tekst], i gotowe.

Funkcja CLEAN

Importowanie danych do Excela zwykle przebiega całkiem dobrze, ale czasami kończy się to znakami, których nie chcesz. Jest to najczęściej występujące, gdy w oryginalnym dokumencie znajdują się znaki specjalne, których Excel nie może wyświetlić. Zamiast przechodzić przez wszystkie komórki zawierające te znaki, możesz użyć funkcji CZYSZCZENIE, która wygląda następująco:

= CZYSZCZENIE ([tekst])

Argument [tekst] jest po prostu lokalizacją tekstu, który chcesz wyczyścić. W przykładowym arkuszu kalkulacyjnym dodałem kilka niedrukowalnych znaków do nazw w kolumnie A, których należy się pozbyć (jeden w wierszu 2 przesuwa nazwę w prawo, a znak błędu w wierszu 3) . Użyłem funkcji CLEAN, aby przenieść tekst do kolumny G bez tych znaków:

Teraz kolumna G zawiera nazwy bez znaków niedrukowalnych. To polecenie jest nie tylko przydatne w przypadku tekstu; często może ci pomóc, jeśli liczby również psują twoje inne formuły; postacie specjalne mogą naprawdę siać spustoszenie w obliczeniach. Jest to niezbędne podczas konwersji z programu Word na program Excel Konwersja programu Word na program Excel: Konwertuj dokument programu Word na plik programu Excel Konwertuj program Word na program Excel: Konwertuj dokument programu Word na plik programu Excel.

Funkcja TRIM

Podczas gdy CLEAN pozbywa się znaków niedrukowalnych, TRIM pozbywa się dodatkowych spacji na początku lub na końcu ciągu tekstowego, które możesz z nimi skończyć, jeśli skopiujesz tekst ze Słowa lub zwykłego dokumentu tekstowego i skończy się czymś lubić ” Data obserwacji ”… Aby to zmienić “Data obserwacji,” wystarczy użyć tej składni:

= TRIM ([tekst])

Gdy go użyjesz, zobaczysz podobne wyniki, jak podczas korzystania z CZYSZCZENIA.

Funkcje zamiany tekstu

Czasami trzeba zastąpić określone ciągi w tekście ciągiem innych znaków. Korzystanie z formuł Excel jest znacznie szybsze niż znajdowanie i zastępowanie Podbij zadania tekstowe „Znajdź i zamień” za pomocą wReplace Podbij zadania tekstowe „Znajdź i zamień” za pomocą wReplace, szczególnie jeśli pracujesz z bardzo dużym arkuszem kalkulacyjnym.

Funkcja SUBSTITUTE

Jeśli pracujesz z dużą ilością tekstu, czasami będziesz musiał wprowadzić kilka poważnych zmian, na przykład podmienić jeden ciąg tekstu na inny. Może zdałeś sobie sprawę, że miesiąc jest nieprawidłowy w szeregu faktur. Lub że wpisałeś czyjeś imię niepoprawnie. W każdym razie czasami trzeba wymienić ciąg. Po to jest SUBSTITUTE. Oto składnia:

= SUBSTITUTE ([tekst], [stary_tekst], [nowy_tekst], [instancja])

Argument [tekst] zawiera lokalizację komórek, w których chcesz dokonać zamiany, a [stary_tekst] i [nowy_tekst] są dość oczywiste. [instancja] pozwala określić konkretną instancję starego tekstu do zastąpienia. Więc jeśli chcesz zastąpić tylko trzecie wystąpienie starego tekstu, wpisz “3)” za ten argument. SUBSTITUTE skopiuje wszystkie inne wartości (patrz poniżej).

Na przykład poprawimy błąd pisowni w naszym arkuszu kalkulacyjnym. Powiedzmy “Honolulu” został przypadkowo napisany jako “Honululu.” Oto składnia, której użyjemy, aby to poprawić:

= SUBSTITUTE (D28, „Honululu”, „Honolulu”)

Oto, co się stanie, gdy uruchomimy tę funkcję:

Po przeciągnięciu formuły do ​​otaczających komórek zobaczysz, że wszystkie komórki z kolumny D zostały skopiowane, z wyjątkiem tych, które zawierały błąd ortograficzny “Honululu,” które zostały zastąpione prawidłową pisownią.

Funkcja REPLACE

WYMIANA jest bardzo podobna do SUBSTITUTE, ale zamiast zamiany określonego ciągu znaków zastąpi znaki w określonej pozycji. Spojrzenie na składnię wyjaśni, jak działa ta funkcja:

= REPLACE ([stary_tekst], [liczba_początkowa], [liczba_znaków], [nowy_tekst])

[stary_tekst] to miejsce, w którym określisz komórki, w których chcesz zamienić tekst. [liczba_początkowa] to pierwszy znak, który chcesz zastąpić, a [liczba_znaków] to liczba znaków, które zostaną zamienione. Za chwilę zobaczymy, jak to działa. [nowy_tekst] to oczywiście nowy tekst, który zostanie wstawiony do komórek - może to być również odwołanie do komórki, co może być bardzo przydatne.

Spójrzmy na przykład. W naszym arkuszu kalkulacyjnym legitymacje studenckie mają sekwencje HP, SP, LP, UP i XP. Chcemy się ich pozbyć i zmienić na NP, co zajęłoby dużo czasu przy użyciu SUBSTITUTE lub Find and Replace. Oto składnia, której użyjemy:

= WYMIANA (A2, 3, 2, „NP”)

W odniesieniu do całej kolumny, oto co otrzymujemy:

Wszystkie dwuliterowe sekwencje z kolumny A zostały zastąpione “NP” w kolumnie G.

Funkcje układania tekstu

Oprócz wprowadzania zmian w ciągach, możesz także robić rzeczy z mniejszymi kawałkami tych ciągów (lub używać tych ciągów jako mniejszych kawałków, aby tworzyć większe). Oto niektóre z najczęściej używanych funkcji tekstowych w programie Excel.

Funkcja CONCATENATE

Tego użyłem kilka razy sam. Gdy masz dwie komórki, które należy dodać razem, CONCATENATE jest twoją funkcją. Oto składnia:

= CONCATENATE ([tekst1], [tekst2], [tekst3]…)

Tym, co czyni konkatenację tak przydatną, jest to, że argumenty [tekstowe] mogą być zwykłym tekstem “Arizona,” lub odwołania do komórek, takie jak “A31.” Możesz nawet wymieszać oba. Może to zaoszczędzić wiele czasu, gdy trzeba połączyć dwie kolumny tekstu, na przykład, jeśli trzeba utworzyć “Pełne imię i nazwisko” kolumna z “Imię” i a “Nazwisko” kolumna. Oto składnia, której użyjemy, aby to zrobić:

= CONCATENATE (A2, „”, B2)

Zauważ tutaj, że drugi argument jest pustą spacją (wpisaną jako cudzysłów-spacja-spacja-znak). Bez tego nazwiska byłyby konkatenowane bezpośrednio, bez odstępów między imionami i nazwiskami. Zobaczmy, co się stanie, gdy uruchomimy to polecenie i użyjemy autouzupełniania w pozostałej części kolumny:

Teraz mamy kolumnę z pełnym imieniem i nazwiskiem każdego. Za pomocą tego polecenia można łatwo łączyć numery kierunkowe i numery telefonów, nazwiska i numery pracowników, miasta i stany, a nawet znaki walutowe i kwoty.
W większości przypadków można skrócić funkcję CONCATENATE do pojedynczego znaku handlowego i. Aby utworzyć powyższą formułę za pomocą ampersand, wpiszemy to:

= A2, „” i B2

Możesz go również użyć do łączenia odniesień do komórek i wierszy tekstu, w następujący sposób:

= E2 i „,” i F2 i „, USA”

To pobiera komórki z nazwami miast i stanów i łączy je z “USA” aby uzyskać pełny adres, jak pokazano poniżej.

Funkcje LEWO i PRAWO

Często chcesz pracować tylko z pierwszymi (lub ostatnimi) kilkoma znakami ciągu tekstowego. W LEWO i W PRAWO pozwala to zrobić zwracając tylko określoną liczbę znaków, zaczynając od lewej lub prawej skrajnej postaci w ciągu. Oto składnia:

= LEWO / PRAWO ([tekst], [liczba_znaków])

[tekst] to oczywiście tekst oryginalny, a [liczba_znaków] to liczba znaków, które chcesz zwrócić. Rzućmy okiem na przykład, kiedy możesz chcieć to zrobić. Załóżmy, że zaimportowałeś wiele adresów, z których każdy zawiera zarówno skrót państwowy, jak i kraj. Możemy użyć LEWEGO, aby uzyskać tylko skróty, używając tej składni:

= LEWY (E2, 2)

Oto, jak to wygląda w odniesieniu do naszego arkusza kalkulacyjnego:

Gdyby skrót oznaczał stan, użylibyśmy PRAWEJ w ten sam sposób.

Funkcja MID

MID jest podobny do LEWEGO i PRAWEGO, ale pozwala wyciągać znaki ze środka łańcucha, zaczynając od określonej pozycji. Rzućmy okiem na składnię, aby zobaczyć dokładnie, jak to działa:

= MID ([tekst], [numer_początkowy], [liczba_znaków])

[numer_początkowy] to pierwszy znak, który zostanie zwrócony. Oznacza to, że jeśli chcesz, aby pierwszy znak w ciągu był uwzględniany w wyniku funkcji, będzie to “1.” [num_chars] to liczba znaków po znaku początkowym, które zostaną zwrócone. Zrobimy z tym trochę czyszczenia tekstu. W przykładowym arkuszu kalkulacyjnym mamy teraz tytuły dodane do nazwisk, ale chcielibyśmy je usunąć, aby nazwisko “Pan Martin” zostaną zwrócone jako “Jaskółka oknówka.” Oto składnia:

= MID (A2, 5, 15)

Użyjemy “5” jako znak początkowy, ponieważ pierwsza litera imienia osoby jest piątym znakiem (“Pan. ” zajmuje cztery spacje). Funkcja zwróci kolejne 15 liter, co powinno wystarczyć, aby nie uciąć ostatniej części imienia. Oto wynik w programie Excel:

Z mojego doświadczenia uważam, że MID jest najbardziej przydatny, gdy połączysz go z innymi funkcjami. Powiedzmy, że ten arkusz kalkulacyjny, nie tylko mężczyzn, obejmuje także kobiety, które mogą mieć jedno z nich “Pani.” lub “Pani.” za ich tytuły. Co byśmy wtedy zrobili? Możesz połączyć MID z IF, aby uzyskać imię bez względu na tytuł:

= JEŻELI (LEWO (A2, 3) = „Pani”, MID (A2, 6, 16), MID (A2, 5, 15)

Pozwolę ci dowiedzieć się dokładnie, jak działa ta formuła (może być konieczne przejrzenie operatorów logicznych programu Excel Mini samouczek programu Excel: Używanie logiki logicznej do przetwarzania złożonych danych Mini samouczek programu Excel: Używanie logiki logicznej do przetwarzania złożonych danych Operatory logiczne JEŚLI, NIE , ORAZ i LUB mogą pomóc Ci przejść od początkującego programu Excel do zaawansowanego użytkownika. Wyjaśniamy podstawy każdej funkcji i pokazujemy, jak możesz z nich korzystać, aby uzyskać maksymalne wyniki)..

Funkcja REPT

Jeśli musisz wziąć ciąg i powtórzyć go kilka razy, a wolisz nie wpisywać go w kółko, REPT może ci pomóc. Podaj ciąg REPT (“ABC”) i liczbę powtórzeń (3), a program Excel da dokładnie to, o co prosiłeś (“abcabcabc”). Oto bardzo łatwa składnia:

= REPT ([tekst], [liczba])

[tekst] jest oczywiście łańcuchem podstawowym; [liczba] to liczba powtórzeń. Chociaż nie spotkałem się jeszcze z dobrym użyciem tej funkcji, jestem pewien, że ktoś może jej użyć do czegoś. Podamy przykład, który choć nie jest do końca użyteczny, może pokazać potencjał tej funkcji. Połączymy REPT z “I” stworzyć coś nowego. Oto składnia:

= „**”

Wynik pokazano poniżej:

Przykład z realnego świata

Aby dać ci wyobrażenie o tym, jak możesz użyć funkcji tekstowej w prawdziwym świecie, podam przykład połączenia MID z kilkoma warunkowymi w mojej własnej pracy. Aby uzyskać stopień psychologii podyplomowej, przeprowadziłem badanie, w którym uczestnicy musieli kliknąć jeden z dwóch przycisków, a współrzędne tego kliknięcia zostały zarejestrowane. Przycisk po lewej stronie ekranu oznaczony był literą A, a przycisk po prawej stronie oznaczony był literą B. Każda próba miała poprawną odpowiedź, a każdy uczestnik wykonał 100 prób.

Aby przeanalizować te dane, musiałem sprawdzić, ile prób każdy uczestnik miał rację. Oto jak wyglądał arkusz wyników po drobnym czyszczeniu:

Prawidłowa odpowiedź dla każdej próby jest wymieniona w kolumnie D, a współrzędne kliknięcia są wymienione w kolumnach F i G (są sformatowane jako tekst, co komplikuje sprawy). Kiedy zacząłem, po prostu przeszedłem i ręcznie wykonałem analizę; jeśli kolumna D mówi “opcja” a wartość w kolumnie F była ujemna, wpisałbym 0 (dla “źle”). Gdyby było dodatnie, wpisałbym 1. Odwrotność byłaby prawdą, gdyby kolumna D przeczytała “opcjab.”

Po drobnych majstrach wymyśliłem sposób użycia funkcji MID, aby wykonać to za mnie zadanie. Oto czego użyłem:

= JEŻELI (D3 = "opcja", JEŻELI (MID (F3,2,1) = "-", 1,0), JEŻELI (MID (F3,2,1) = "-", 0,1))

Rozwalmy to. Począwszy od pierwszej instrukcji IF, mamy następujące elementy: “jeśli komórka D3 mówi „optiona”, to [pierwsze warunkowe]; jeśli nie, to [drugi warunek].” Pierwszy warunek mówi: “jeśli drugi znak komórki F3 jest łącznikiem, zwróć true; jeśli nie, zwróć false.” Trzeci mówi “jeśli drugim znakiem komórki F3 jest łącznik, zwróć false; jeśli nie, zwróć true.”

To może chwilę potrwać, ale powinno być jasne. W skrócie, ta formuła sprawdza, czy D3 mówi “opcja”; jeśli tak, a drugi znak F3 jest łącznikiem, funkcja zwraca “prawdziwe.” Jeśli D3 zawiera “opcja” a druga postać F3 nie jest łącznikiem, zwraca “fałszywe.” Jeśli D3 nie zawierać “opcja” a druga postać F3 jest łącznikiem, zwraca “fałszywe.” Jeśli D3 nie mówi “opcja” a druga postać F2 nie jest łącznikiem, zwraca “prawdziwe.”

Oto jak wygląda arkusz kalkulacyjny po uruchomieniu formuły:

Teraz “wynik” kolumna zawiera 1 za każdą próbę, na którą uczestnik odpowiedział poprawnie, oraz 0 za każdą próbę, na którą odpowiedział niepoprawnie. Stamtąd łatwo podsumować wartości, aby zobaczyć, ile dostali.

Mam nadzieję, że ten przykład daje wyobrażenie o tym, jak kreatywnie używać funkcji tekstowych podczas pracy z różnymi typami danych. Moc programu Excel jest prawie nieograniczona. 3 szalone formuły Excela, które robią niesamowite rzeczy 3 szalone formuły Excela, które robią niesamowite rzeczy Formuły Excela mają potężne narzędzie do formatowania warunkowego. W tym artykule omówiono trzy sposoby zwiększenia wydajności za pomocą MS Excel. , a jeśli poświęcisz czas na opracowanie formuły, która spełni Twoje zadanie, możesz zaoszczędzić mnóstwo czasu i wysiłku!

Excel Text Mastery

Excel jest potężny, jeśli chodzi o pracę z liczbami, ale ma również zaskakującą liczbę przydatnych funkcji tekstowych. Jak widzieliśmy, możesz analizować, konwertować, zamieniać i edytować tekst, a także łączyć te funkcje z innymi, wykonywać skomplikowane obliczenia i transformacje.

Z wysyłania wiadomości e-mail Jak wysyłać wiadomości e-mail z arkusza kalkulacyjnego Excel za pomocą skryptów VBA Jak wysyłać wiadomości e-mail z arkusza kalkulacyjnego Excel za pomocą skryptów VBA Nasz szablon kodu pomoże Ci skonfigurować automatyczne wiadomości e-mail z poziomu programu Excel przy użyciu obiektów danych współpracy (CDO) i skryptów VBA. do płacenia podatków Czy płacisz podatki? 5 formuł Excel, które musisz znać, płacąc podatki? 5 formuł Excela, o których musisz wiedzieć, że minęły dwa dni, zanim nadejdą podatki i nie chcesz płacić kolejnej opłaty za opóźnione zgłoszenie. To czas, aby wykorzystać moc Excela, aby wszystko uporządkować. , Excel może pomóc Ci zarządzać całym swoim życiem Jak korzystać z programu Microsoft Excel do zarządzania swoim życiem Jak korzystać z programu Microsoft Excel do zarządzania swoim życiem Nie jest tajemnicą, że jestem fanem Excela. Wiele z tego wynika z faktu, że lubię pisać kod VBA, a program Excel w połączeniu ze skryptami VBA otwierają cały świat możliwości…. A nauka korzystania z tych funkcji tekstowych przybliży Cię o krok do bycia mistrzem Excela.

Daj nam znać, jak używałeś operacji tekstowych w Excelu! Jaka jest najbardziej złożona transformacja, którą dokonałeś?