Mogłoby się wydawać, że przygotowując analizy w głównej mierze będziesz zajmować się analizą, przetwarzaniem i modyfikowaniem danych liczbowych. Należy jednak pamiętać, że wartości tekstowe także mogą być nośnikiem bardzo cennych informacji, co jest bardzo istotne w procesie analizy danych. Aby można było wykorzystać te dane często należy je wstępnie przygotować lub zmodyfikować. W związku z tym, w poniższym artykule przybliżę Ci siedem funkcji dostępnych w Excelu, które moim zdaniem mogą ułatwić przygotowywanie analiz w tym programie.
Najważniejsze informacje
Zanim przejdziemy do szczegółowego omówienia tych funkcji wraz z praktycznymi przykładami ich zastosowania poniżej znajduje się krótka definicja każdej z nich:
- USUŃ.ZBĘDNE.ODSTĘPY – usuwa wszystkie spacje ze wskazanego tekstu, oprócz pojedynczych spacji rozdzielających poszczególne słowa.
- ZNAJDŹ / SZUKAJ.TEKST – znajduje określony znak lub ciąg tekstowy wewnątrz innego ciągu tekstowego i zwraca jego pozycję początkową.
- WARTOŚĆ – konwertuje ciąg tekstowy reprezentujący liczbę na liczbę.
- FUNKCJE POZWALAJĄCE WYODRĘBNIĆ CZĘŚĆ TEKSTU – zbiór funkcji, dzięki którym z określonego ciągu tekstowego można wyciąć jego fragment.
- FUNKCJE ZMIENIAJĄCE WIELKOŚĆ LITER – zbiór funkcji umożliwiający zmianę wielkości liter.
- PODSTAW – pozwala w ciągu tekstowym zamienić wybrany jego fragment na inny.
- ZŁĄCZ.TEKSTY – umożliwia połączenie dwóch lub więcej ciągów tekstowych w jeden dłuższy ciąg tekstowy.
Funkcja USUŃ.ZBĘDNE.ODSTĘPY
Funkcja bardzo ułatwiająca pracę, szczególnie w przypadku importowania danych z innych źródeł lub plików. Dzięki niej w prosty sposób można wyeliminować niepotrzebne i nadmiarowe spacje we wskazanych tekstach. Z tego powodu może być wykorzystywana do wstępnego oczyszczenia danych, które są pobierane z innych źródeł lub jako prosty mechanizm zabezpieczający dla danych wpisywanych ręcznie przez użytkowników (np. w formularzach).
Wyobraź sobie, że masz do dyspozycji dane, które były wprowadzane ręcznie do formularza w Excelu. Niestety, ale formularz nie został odpowiednio zabezpieczony i Użytkownicy mogli wprowadzać dane w dowolny sposób, na skutek czego otrzymałeś następujące dane:
Nie wygląda to najlepiej. Dzięki funkcji USUŃ.ZBĘDNE.ODSTĘPY będziesz mógł w łatwy sposób pozbyć się wszystkich nadmiarowych spacji.
Budowa funkcji
Do poprawnego zadziałania funkcji należy wskazać tylko jeden argument – tekst, z którego Excel ma usunąć zbędne spacje. W celu wyeliminowania spacji z wybranego tekstu wystarczy wskazać go jako argument funkcji.
Przykład
W celu uporządkowania danych wprowadzonych w powyższej tabeli wskażmy jako argument funkcji wartości z kolumn IMIĘ, NAZWISKO i DZIAŁ:
=USUŃ.ZBĘDNE.ODSTĘPY(B3)
=USUŃ.ZBĘDNE.ODSTĘPY(C3)
=USUŃ.ZBĘDNE.ODSTĘPY(D3)
Powyższe formuły odwołują się do kolumn w wierszu 3, ponieważ tam znajdują się wartości, które chcemy uporządkować. Po wpisaniu powyższych formuł i przeciągnięciu ich w dół funkcja zwróci wskazane teksty bez nadmiarowych spacji, zarówno z przodu, jak i tyłu tekstu:
Funkcja ZNAJDŹ / SZUKAJ.TEKST
Bardzo przydatne funkcje, które w połączeniu z innymi funkcjami tekstowymi mogą znacznie ułatwić pracę z wartościami tekstowymi. Działają one bardzo podobnie, jednak najistotniejsza różnica między tymi funkcjami jest taka, że funkcja ZNAJDŹ uwzględnia wielkość liter, a także niedozwolone jest używanie w niej symboli wieloznacznych. Natomiast w funkcji SZUKAJ.TEKST obie opcje są dozwolone.
Reasumując, funkcje ZNAJDŹ i SZUKAJ.TEKST pozwalają zlokalizować wskazany ciąg tekstowy w innym ciągu tekstowym i zwrócić pozycję początkową wskazanego ciągu. Można wykorzystać te funkcje np. do wskazania, na której pozycji w wyrazie „komputer” znajduje się litera „p”.
Budowa funkcji
Obie funkcje składają się z dwóch obowiązkowych argumentów oraz jednego opcjonalnego. Jako argumenty, które muszą zostać wskazane należy wyróżnić tekst_szukany, czyli tekst, który ma zostać znaleziony oraz w_tekście (w przypadku funkcji ZNAJDŹ) lub obejmujący_tekst (w przypadku funkcji SZUKAJ.TEKST) – jest to tekst, w którym ma zostać znaleziony tekst_szukany. Ostatni argument, który może zostać wskazany to liczba_początkowa określający numer znaku, od którego ma zostać rozpoczęte przeszukiwanie.
Przykład
Mając do dyspozycji losową literę z imienia każdego pracownika, dzięki tym dwóm funkcjom można określić, na którym miejscu dana litera znajduje się w tym ciągu tekstowym. A będąc dokładniejszym, na którym miejscu dana litera znajduje się po raz pierwszy w tym ciągu tekstowym. W związku z tym, bazując na imieniu „Jakub” i chcąc uzyskać informacje o położeniu litery „a” funkcje wyglądałyby następująco:
=ZNAJDŹ("a";"Jakub") -> funkcja zwróci 2
=SZUKAJ.TEKST("a";"Jakub") -> funkcja także zwróci 2
Warto pamiętać o ograniczeniu funkcji ZNAJDŹ, która rozróżnia wielkość liter, więc gdyby jako szukany tekst został wpisany argument „A” funkcja zwróciłaby błąd. Natomiast funkcja SZUKAJ.TEKST nadal zadziałałaby poprawnie.
Jak wspomniałem, funkcje te same w sobie nie są zbyt interesujące. Natomiast połączenie ich z innymi funkcjami tekstowymi może dać bardzo przydatne efekty.
Funkcja WARTOŚĆ
Przydatna funkcja, która zamienia ciąg tekstowy reprezentujący liczbę na liczbę. Można ją wykorzystać np. do eliminowania jednostek walutowych z kwot.
Budowa funkcji
Aby funkcja zadziałała poprawnie wystarczy podać jeden argument – odwołać się do tekstu, który ma zostać przekonwertowany.
Przykład
W formularzu pracownicy mogli podać średnią roczną kwotę wydatków na dojazdy do pracy. Zrobili to dodając na końcu kwoty znak „zł”. Aby się pozbyć tego symbolu stosując funkcję WARTOŚĆ należy odwołać się do komórki z wartością i funkcja zwróci samą wartość liczbową.
Funkcje pozwalające wyodrębnić część tekstu
Zbiór funkcji, dzięki którym istnieje możliwość wycinania określonych części ciągów tekstowych i np. ich dalsze wykorzystywanie.
LEWY / PRAWY
Te dwie funkcje pozwalają wyodrębnić z tekstu co najmniej pierwszy znak z ciągu tekstowego z lewej lub prawej strony.
Budowa funkcji
W związku z prostym działaniem, funkcje składają się tylko z dwóch argumentów: tekst i liczba_znaków. Pierwszy argument określa, z którego ciągu tekstowego Excel ma wyodrębnić znaki. Drugi natomiast określi liczbę wyodrębnionych znaków.
Przykład
Aby sprawdzić działanie tej funkcji możemy wyodrębnić ostatnią literę imienia każdego pracownika i na tej podstawie stwierdzić, czy dany pracownik jest kobietą, czy mężczyzną.
Niemal wszystkie polskie kobiece imiona kończą się na „a”, więc stosując to uproszczenie możemy na potrzeby naszego przykładu założyć, że każda osoba, w której imieniu na ostatnim miejscu jest litera „a” jest kobietą. W przeciwnym przypadku zakwalifikujemy osobę jako mężczyznę.
Wykorzystam do tego funkcję JEŻELI. W przypadku nieznajomości tej funkcji zachęcam do zapoznania się z tym artykułem.
Korzystając z funkcji PRAWY wyodrębnię z każdego imienia ostatnią literę i na tej podstawie przypiszę płeć. Reasumując, funkcja będzie wyglądała następująco:
=JEŻELI(PRAWY(W3;1)="a";"Kobieta";"Mężczyzna")
FRAGMENT.TEKSTU
Korzystając z tej funkcji można wyciągnąć z określonego ciągu tekstowego określoną liczbę znaków, począwszy od zdefiniowanego miejsca. Dzięki tej funkcji z łatwością można wyodrębnić X znaków z danego tekstu, znajdujących się na miejscu Y w tym tekście.
Wyobraź sobie, że system do wystawiania faktur w firmie nadaje każdej fakturze niepowtarzalny numer w formacie XXX/NAZWA_DZIAŁU/YYY, gdzie jako X i Y przyjmowane są liczby, natomiast nazwa działu to dwie pierwsze litery nazwy danego działu. Dzięki funkcji FRAGMENT.TEKSTU z łatwością wyodrębnisz z numeru faktury nazwę działu.
Budowa funkcji
Do poprawnego działania funkcji należy określić jej trzy argumenty. Pierwszy z nich to tekst, czyli ciąg tekstowy zawierający znaki, które mają zostać wyodrębnione. Drugi argument to liczba_początkowa określający, od którego znaku w ciągu tekstowym funkcja ma zacząć wyodrębniać tekst. Ostatnim argumentem jest liczba_znaków, który mówi o tym jak długi ma być wyodrębniony tekst. Wskazanie wszystkich argumentów jest obowiązkowe.
Przykład 1
Mając podane numery faktur za pomocą funkcji wyodrębnimy z nich skrótowe nazwy działów w firmie. Funkcja będzie wyglądała następująco:
=FRAGMENT.TEKSTU(T3;5;2)
Ponieważ interesująca nas wartość znajduje się w kolumnie T3, dwuliterowy skrót nazwy działu zaczyna się od 5 znaku tekstowego i skrócona nazwa działu ma 2 litery. Takie rozwiązanie sprawdzi się jednak tylko, gdy potrafimy sami określić argumenty liczba_początkowa oraz liczba_znaków.
Przykład 2
Ponieważ Przykład 1 wymaga od Użytkownika posiadania pewnych informacji, które nie zawsze może on posiadać, zastosujmy poznane wcześniej formuły ZNAJDŹ, SZUKAJ.TEKST oraz LEWY, które niezależnie od położenia interesujących nas wartości wyodrębnią z adresu pracownika ulicę, miasto oraz kod pocztowy.
Z adresu podawanego w formie Nazwa ulicy, kod pocztowy miasto wyodrębnię jego poszczególne elementy. Oto jak powinny wyglądać końcowe funkcje:
Ulica: =LEWY(E3;ZNAJDŹ(",";E3)-1)
Skoro wiem, że nazwa ulicy jest oddzielona od pozostałej części adresu przecinkiem to wykorzystam tę informacji i za pomocą funkcji LEWY i ZNAJDŹ znajdę przecinek w ciągu tekstowym i zwrócę cały tekst z lewej strony od przecinka. Dodanie na końcu „-1” spowoduje, że formuła nie zwróci przecinka.
Kod pocztowy: =FRAGMENT.TEKSTU(E3;SZUKAJ.TEKST("??-???";E3);6)
Miasto: =FRAGMENT.TEKSTU(E3;SZUKAJ.TEKST("??-???";E3)+6;DŁ(E3))
W dwóch powyższych formułach stosuję założenie, że kod pocztowy jest wpisany w formie XX-XXX, ale nie wiem jakie wartości przyjmie ten kod, dlatego używam znaku wieloznacznego „?”, który mówi, że w tym miejscu pojawi się dowolny pojedynczy znak, chociaż na razie nie jestem w stanie określić, jaki to będzie znak.
Wykorzystując tę informację określam, aby funkcja w określonym tekście szukała ciągu znaków w formie XX-XXX i dla wyodrębnienia kodu pocztowego pokazała go całego – stąd 6 na końcu, gdyż ciąg ma 6 znaków. Dla określenia miasta funkcja działa bardzo podobnie – odnajduje ciąg znaków z kodem pocztowym i „przesuwa” o ciąg 6 znaków, aby pokazać nazwę miasta. Końcowy argument DŁ(E3) określa, aby funkcja zwróciła całą nazwę miejscowości. W funkcji zastosowałem także USUŃ.ZBĘDNE.ODSTĘPY, aby pozbyć się nadmiarowych spacji.
Funkcje zmieniające wielkość liter
Przydatne funkcje, dzięki którym istnieje możliwość zmiany sposobu wyświetlania wartości w ciągu tekstowym. Korzystając z nich można sprawić, aby ciąg tekstowy lepiej się prezentował. Funkcja ta może być wykorzystana do poprawienia wartości wpisanych ręcznie do formularza, aby wszystkie pozycje prezentowały się jednakowo.
Budowa funkcji
Wśród tych funkcji warto wyróżnić LITERY.WIELKIE, LITERY.MAŁE, Z.WIELKIEJ.LITERY. W każdej z tych funkcji jako jedyny wymagany argument należy odwołać się do tekstu, który ma zostać skorygowany. W związku z tym składnia funkcji jest bardzo prosta.
Przykład
Wykorzystajmy tę funkcję, aby poprawić wartości tekstowe wprowadzone przez pracowników do formularza. Korzystając z funkcji Z.WIELKIEJ.LITERY sprawię, aby wszystkie imiona i nazwiska pracowników były napisane małymi literami, oprócz pierwszej, która będzie wielką literą.
Dodatkowo skorzystam z funkcji LITERY.WIELKIE, aby nazwy działów były napisane wielkimi literami. Dzięki temu dane prezentują się o wiele lepiej:
Funkcja PODSTAW
Korzystając z funkcji PODSTAW zyskujemy możliwość zamiany jednej wartości na inną bez dodatkowych czynności. Dzięki tej funkcji można określić, aby dany tekst znajdujący się w ciągu tekstowym został zastąpiony przez inny.
Budowa funkcji
Funkcja składa się z 3 wymaganych argumentów i jednego opcjonalnego. W związku z tym, w pierwszej kolejności należy wskazać tekst, czyli ciąg znaków, w którym ma nastąpić zamiana wartości tekstowych. Kolejną wartością wymaganą jest określenie argumentu stary_tekst określający tekst, który ma zostać zastąpiony innym, wskazanym jako nowy_tekst. Ostatnim argumentem, który można wskazać jest wystąpienie_liczba, w którym określa się które wystąpienie danego ciągu znaków ma zostać zastąpione. Jeśli argument nie zostanie określony to wszystkie wystąpienia danego ciągu znaków zostaną zastąpione.
Przykład
Wyobraź sobie sytuację, że na potrzeby jakiegoś zestawienia musisz zmienić sposób numerowania faktur z obecnego XXX/DZIAŁ/YYY na XXX_DZIAŁ_YYY. Z wykorzystaniem funkcji PODSTAW będzie to bardzo proste. Jako tekst należałoby wskazać komórkę z obecnym numerem faktury, jako stary_tekst „/” i jako nowy_tekst „_”. Ponieważ chcemy, aby formuła zastąpiła wszystkie wystąpienia starego symbolu, więc nie wskażemy argumentu opcjonalnego wystąpienie_liczba. Końcowa funkcja powinna wyglądać następująco:
=PODSTAW(T3;"/";"_")
Funkcja ZŁĄCZ.TEKSTY
Ostatnia funkcja tekstowa, którą chciałbym omówić umożliwia łączenie tekstów. Dzięki niej można ze sobą połączyć dwa lub więcej ciągów tekstowych w jeden ciąg.
Budowa funkcji
Funkcja składa się z jednego wymaganego argumentu tekst1, każdy kolejny argument jest opcjonalny. Maksymalnie można wskazać 255 elementów, o długości maksymalnie 8192 znaków. Jako argument funkcja może przyjmować wartość tekstową, liczbową lub być też odwołaniem do komórki.
Przykład
Przy pomocy tej funkcji możemy połączyć ze sobą imiona i nazwiska pracowników, aby znajdowały się w jednej komórce. Aby to zrobić jako tekst1 należy wskazać imię pracownika, jako drugi argument ciąg znaków ” „, który wstawi spację pomiędzy imię i nazwisko pracownika. Natomiast trzecim argumentem będzie oczywiście nazwisko pracownika. Podsumowując, końcowa funkcja powinna wyglądać następująco:
=ZŁĄCZ.TEKSTY(imię_pracownika;" ";nazwisko_pracownika)
Stosując powyższą formułę otrzymamy:
Warto wspomnieć o tym, że dokładnie taki sam efekt można osiągnąć bez użycia wbudowanej formuły, stosując symbol ampersand (&). Używając tego symbolu funkcja wyglądałaby tak:
=imię_pracownika&" "&nazwisko_pracownika
Podsumowując, warto zastanowić się, czym różnią się oba rozwiązania? Nie zauważyłem znaczących różnic używając tych formuł. Przy ZŁĄCZ.TEKSTY możesz skorzystać z opcji wstaw funkcję, co może być trochę wygodniejsze przy łączeniu wielu tekstów.
Podsumowanie
Mam nadzieję, że dzięki temu artykułowi będziesz w stanie korzystać z funkcji tekstowych na co dzień. Dzięki tym funkcjom operacje na wartościach tekstowych mogą być dużo łatwiejsze, a także szybsze. Warto również pamiętać o tym, że możesz wykorzystać te funkcje do wstępnego przygotowania danych, co z pewnością wpłynie na komfort pracy podczas tworzenia różnych raportów i zestawień.
Aby ułatwić Ci poznawanie tych funkcji tutaj możesz pobrać plik, na którym pracowałem podczas tworzenia artykułu.
