W tym poście nauczymy się zliczać wszystkie litery w tekście, czy też dowolne znaki.
Najwięcej problemu może być z literami, bo mamy litery małe i wielkie, poza tym w polskim języku mamy litery takie jak ą, ę, ś, ć, ń, ź, ż, ł, ó, czyli wszystkie te litery z ogonkami i kreseczkami. Najprostszą metodą na wykonanie tego zadania jest wypisanie wszystkich możliwych znaków w postaci wielkich liter. Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
W pierwszym kroku musimy nasz tekst rozbić na poszczególne znaki. Do tego celu potrzebujemy ciągu liczbowego od 1 do wartości odpowiadającej długości naszego tekstu. Najpierw użyjemy funkcji DŁ, która zwraca liczbę znaków w ciągu znaków. My jednak potrzebujemy zbudować ciąg wartości. Użyjemy tutaj właściwości funkcji WIERSZ, która polega na tym, że jeśli w argumencie odwołanie zaznaczymy zakres A1:A8, to funkcja zwróci tyle wartości, ile zaznaczyliśmy wierszy w tym zakresie. Przykładowy zapis formuły to =WIERSZ(A1:A8) (rys. nr 2)
Rys. nr 2 – właściwość funkcji WIERSZ
Zaznaczyliśmy w zakresie 8 wierszy, więc funkcja WIERSZ w Excelu tablicowym rozlewa się na te właśnie wiersze. Musimy dla tej funkcji zbudować odpowiedni zakres. Potrzebujemy do tego funkcji ADR.POŚR (adres pośredni). Możemy tutaj wykluczyć kolumny, interesują nas tylko wiersze. Argumentem funkcji jest adres_tekst, czyli zaczynami od "1:", od pierwszego wiersza, a dwukropek oznacza, że będziemy szli dalej do kolejnych wierszy. Ten tekst łączymy z funkcją DŁ dla tekstu z komórki A2. Zapis formuły powinien wyglądać następująco:
=ADR.POŚR("1:"&DŁ(A2))
Jeśli podejrzymy sobie wynik formuły funkcji ADR.POŚR za pomocą skrótu klawiszowego F9 otrzymamy ilość znaków 1:11, co widać na rys. nr 3.
Rys. nr 3 – podejrzany wynik formuły
Wychodzimy z podglądu formuły za pomocą skrótu klawiszowego Ctrl+Z. Powyższą funkcję musimy włożyć do funkcji WIERSZ, bo interesuje nas liczba wierszy z tego zakresu. Zapis formuły powinien wyglądać następująco:
=WIERSZ(ADR.POŚR("1:"&DŁ(A2)))
Po zatwierdzeniu formuły otrzymamy dane przedstawione na rys. nr 4.
Rys. nr 4 – rozlane wyniki funkcji WIERSZ
W Excelu tablicowym wyniki rozlewają się automatycznie na odpowiednią ilość komórek (wierszy). W kolejnym kroku musimy dołożyć funkcję FRAGMENT.TEKSTU. Pierwszym argumentem funkcji jest tekst, czyli tekst z komórki A2. Drugi argument to liczba.początkowa, czyli tablica danych, którą zbudowaliśmy za pomocą funkcji WIERSZ (W Excelu tablicowym moglibyśmy użyć funkcji SEKWENCJA, która byłaby prostsza). Trzeci argument funkcji to liczba_znaków, czyli wartość 1, bo chcemy każdy znak wyciągnąć pojedynczo. Zapis formuły powinien wyglądać następująco:
Wyniki funkcji zostały przedstawione na rys. nr 5.
Rys. nr 5 – wyniki funkcji FRAGMENT.TEKSTU
Otrzymaliśmy tablicę, w której każdy znak z tekstu został umieszczony w osobnym wierszu. Teraz będziemy chcieli sprawdzić, czy te znaki znajdują się w przygotowanym przez nas wzorze (rys. nr 6).
Rys. nr 6 – przygotowany wzór wszystkich występujących w języku polskim znaków
Standardowo powinniśmy użyć funkcji ZNAJDŹ, jednak nie możemy tego zrobić, bo ona uwzględnia wielkość liter. Więc musimy użyć drugiej funkcji wyszukującej, czyli SZUKAJ.TEKST, która nie zwraca uwagi na wielkość liter. Pierwszym argumentem funkcji jest szukany_tekst, czyli litery uzyskane z funkcji FRAGMENT.TEKSTU. Drugi argument funkcji to obejmujący_tekst, czyli odwołanie do komórki I2 zablokowane bezwzględnie, gdzie wypisaliśmy wszystkie możliwe znaki. Zapis formuły powinien wyglądać następująco;
Po zatwierdzeniu formuły otrzymamy rozlane wartości, gdzie dana litera została znaleziona (rys. nr 7).
Rys. nr 7 – wyniki funkcji SZUKAJ.TEKST
Otrzymaliśmy liczbę 20, która odpowiada literce A, potem L ma przypisaną liczbę 28, ponownie A, czyli 20. Następnie pojawia się błąd argumentu #ARG!, który wynika z tego, że w naszych wypisanych literach nie było spacji. Żeby pozbyć się tych błędów musimy użyć funkcji CZY.LICZBA, której argumentem jest wartość, czyli wyniki z naszej poprzedniej formuły. Zapis formuły powinien wyglądać następująco:
Otrzymamy wyniki z wartościami logicznymi przedstawione na rys. nr 8.
Rys. nr 8 – wyniki funkcji CZY.LICZBA
Otrzymaliśmy wynik PRAWDA wszędzie tam, gdzie wcześniej mieliśmy liczbę, a wynik FAŁSZ, kiedy w danych był błąd argumentu. Pozostaje nam policzyć wszystkie wartości logiczne PRAWDA. Użyjemy do tego funkcji SUMA.ILOCZYNÓW, ale najpierw musimy zamienić wartości logiczne na liczby 1 i 0 (1‑PRAWDA, 0 — FAŁSZ). Możemy to zrobić za pomocą podwójnej negacji, czyli wpisujemy przed formułą dwa znaki minus. Zapis formuły powinien wyglądać następująco:
Po zatwierdzeniu formuły i skopiowaniu na wiersze poniżej otrzymamy ilość znaków w każdym wierszu, co widać na rys. nr 10.
Rys. nr 10 – ilość liter w każdym wierszu
Jeśli w formule nie chcemy mieć odwołania do komórki I2, to możemy wstawić tekst do naszej formuły w podwójnym cudzysłowie. Taka formuła też będzie działać prawidłowo. Zapis formuły powinien wtedy wyglądać następująco:
Jeśli chcielibyśmy, aby takie znaki jak pojedynczy cudzysłów, czy kropka, przecinek itp. Też się zliczały, wystarczy, że dopiszemy je do naszego ciągu znaków z komórki I2.
Podsumowując, dzięki temu, że korzystamy tutaj z funkcji SZUKAJ.TEKST wystarczy jeden raz wypisać wszystkie litery. Nie ma znaczenia, czy są to wielkie, czy małe litery.
W tym poście zajmiemy się czasem wykonywania procedur i
optymalizacją formuł. Zagadnienie to omówimy na przykładowych danych z rysunku
nr 1.
rys. nr 1 — Przykładowe dane
Pod poradą nr 348, która dotyczyła znalezienia pierwszego
wiersza spełniającego warunek, odbyłem dyskusję z Billem Szysz na temat formuł
i czasu ich wykonywania. Funkcja, z której ja często korzystam (LICZ.JEŻELI)
jest pamięciożerna, czyli obciąża nam procesor. Podobnie działa funkcja
PODAJ.POZYCJĘ , która dla większych zakresów danych działa szybciej.
W filmie Excel sztuczka nr 3 ( https://www.youtube.com/watch?v=CcT51qv8_IA ) pokazałem
jak wykorzystać kod VBA, można go podpiąć i sprawić, aby wykonywał obliczenia –
sprawdzał jak długo dane formuły się liczą.
Zaznaczamy formuły, których chcemy policzyć czas trwania przy
użyciu skrótu klawiszowego Ctrl+A i klikam w makro (rys. nr 2).
rys. nr 2 — Makro
Pojawia nam się wynik obliczeń (rys. nr 3), czyli obliczenia
zostały wykonane w czasie 1,82269 s. Trzeba użyć tego makra kilka razy, aby
sprawdzić średnia z obliczeń, ponieważ czas obliczeń zależy od tego jak jest
obciążony procesor (np. ile mamy uruchomionych programów w danym momencie)
rys. nr 3 — Czas obliczeń funkcji LICZ.JEŻELI
Według moich obliczeń formuła Billa Szysz przelicza się
dłużej (rys. nr 4), bo prawie 5 sekund. Możliwe, że Formuła Billa Szysz jest
szybsza przy jeszcze większej ilości danych.
rys. nr 4 — Czas obliczeń funkcji PODAJ.POZYCJĘ
Bill Szysz zaproponował inne podejście do obliczeń. My tak
naprawdę tylko raz musimy znaleźć ten pierwszy wiersz spełniający warunek.
Kiedy już mamy numer wiersza, to możemy cała skomplikowaną formułę zastąpić
prostą funkcją WIERSZ. W tej funkcji nie podajemy żadnych argumentów tylko
przyrównujemy ją do komórki z wynikiem którego szukamy. Gdy zaznaczymy kolumny
z formuła funkcji WIERSZ otrzymamy wynik 0,005 sekundy, czyli funkcja zwróciła
wynik w tak krótkim czasie (rys. nr 5).
rys. nr 5 — Czas obliczeń funkcji WIERSZ
Podsumowując, nie musieliśmy formuły obliczać 10 tysięcy
razy, wystarczyło policzyć raz, a potem za pomocą funkcji WIERSZ porównać ją z
tym pojedynczym wynikiem. Diametralna zmiana długości czasu wynika, ze zmiany
podejścia do obliczeń. Złota zasada Excela polega na tym, że jeśli potrzebujemy
coś przeliczyć wiele razy ale zawsze jest to ten sam wynik (w naszej sytuacji
ten sam wiersz), to zróbmy to w jednej komórce, a następnie w innej formule
skorzystajmy z tego wyniku.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Kontynuujemy temat wyciągania ostatnich i pierwszych wierszy po warunku z porady 307. Tym razem napiszemy formuły, które pozwolą nam wyciągnąć interesujące nas dane.
Zaczniemy od formuły, która jest dla mnie klasycznym podejściem w podobnych sytuacjach – w funkcji JEŻELIbędziemy sprawdzać dwa interesujące nas warunki – dla dnia i firmy, czyli porównujemy całe kolumny danych do konkretnych wartości. Ponieważ musimy sprawdzić 2 kolumny, dlatego wykonujemy na wynikach operacji porównań mnożenie (pamiętając o kolejności działań dokładamy nawiasy). Jeśli warunki są spełnione to chcemy mieć wartość z kolumny liczba porządkowa, jeśli nie to chcemy domyślną wartość FAŁSZ, więc nic nie wpisujemy:
W formułach korzystamy z tego, że zakres danych jest zamieniony na tabelę Excela (tNotowania) oraz, że mamy kolumnę z liczbą porządkową. Jeśli nie mielibyśmy kolumny z liczbą porządkową musielibyśmy ją stworzyć w formule – odwołanie tNotowania2[L.p] musielibyśmy zamienić na formułę:
WIERSZ(tNotowania[Data])-WIERSZ(B1)
Tak stworzona funkcja JEŻELI zwraca tablicę z numerami porządkowymi, gdzie warunki były spełnione i wartościami FAŁSZ tam, gdzie nie były one spełnione:
Z takiej tablicy wystarczy odpowiednio wyciągnąć minimalną i maksymalną wartość, żeby uzyskać pierwszy i ostatni numer wiersza, który spełnia nasze warunki:
rys. 2 – Wyciąganie numeru pierwszego wiersza za pomocą funkcji MIN
Musimy pamiętać, że nasza formuła jest formuła tablicową, więc żeby zwracała prawidłowe wyniki musimy zatwierdzać ją za pomocą kombinacji klawiszy Ctrl + Shift + Enter.
Jak wyciągnęliśmy numer wiersza, to wystarczy już tylko funkcja INDEKS, żeby pobrać wartość z kolumny Cena:
rys. 3 – Obliczone pierwsze i ostatnie ceny po warunku
Mamy już wynik, ale chce przedstawić jeszcze inny sposób na znalezienie ostatniego wiersza. Pierwszy raz poznałem tą sztuczkę od Billa Szysz(kowskiego). Wykorzystuje ona funkcję WYSZUKAJ i najważniejsze w niej jest to, że nie musimy formuły zatwierdzać jako formuły tablicowej.
Zanim zaczniemy pisać formułę, musimy sobie powiedzieć 2 ważne rzeczy na temat funkcji WYSZUKAJ – zawsze dokonuje dopasowania przybliżonego oraz nie zwraca uwagi na błędy w kolumnie w której szuka.
Dlatego będziemy szukać liczby 2 i kolumnę po której będziemy szukać stworzymy jako wynik podzielenia 1 przez wynik naszych porównań po kolumnach (pamiętając o kolejności operacji matematycznych). Wektorem (tablicą) wynikową będzie kolumna Cena:
rys. 4 – Wyszukiwanie ostatniego wiersza po warunku za pomocą funkcji WYSZUKAJ
Czemu nasza funkcja działa? Przypomnij sobie co robi funkcja WYSZUKAJ.PIONOWO, gdy szukamy na zasadzie przybliżonej wartości, która jest większa od największej liczby jaką mamy w posortowanej kolumnie – zwraca ostatnią wartość. Analogicznie działa funkcja WYSZUKAJ – szuka 2 na liście 1 i błędów dzielenia przez zero:
Tak właśnie wygląda nasz przeszukiwany wektor (kolumna). Jeśli oba warunki są spełnione to mamy dwie wartości PRAWDA. Dowolna operacja matematyczna (np.: dzielenie) zamienia je na 1, czyli 1/(1*1)=1. Ale jeśli warunek nie jest spełniony otrzymujemy wartość FAŁSZ, która przy operacjach matematycznych zamienia się na 0, czyli np.: 1/(1*0) = błąd dzielenia przez zero (#DZIEL/0!). Ponieważ funkcja WYSZUKAJ ignoruje błędy pozostają jej same 1, czyli szukając 2, przy przeszukiwaniu przybliżonym, będzie przeszukiwała po ‘posortowanej’ liście 1, czyli odnajdzie pozycję ostatniej 1, a tym samym zwróci korespondującą do niej wartość z kolumny Cena.
Przedstawimy jeszcze inny sposób na znalezienie pierwszego wiersza. Niestety funkcja WYSZUKAJ tu nie zadziała i będziemy musieli wykorzystać funkcję PODAJ.POZYCJĘ. Będziemy sprawdzać kilka warunków – muszą się zgadzać wartości w kilku kolumnach. Jednym ze sposobów na to jest scalanie szukanych wartości i scalanie kolumn. W ten sposób uzyskamy jedną scaloną wartość, której będziemy szukać w scalonej kolumnie – funkcja PODAJ.POZYCJĘ idealnie się do tego nadaje. Dokładamy jeszcze dopasowanie dokładne i udaje nam się odnaleźć pierwszy wiersz z szukaną wartością, bo dopasowanie dokładne zwraca pierwszą wartość/pierwszy wiersz jaki znajdzie od początku danych:
Pamiętaj są to formuły tablicowe i musisz je zatwierdzić za pomocą kombinacji klawiszy Ctrl + Shift + Enter.
P.S. Po podejrzeniu (klawiszem F9) przykładowa szukana scalona wartość w funkcji PODAJ.POZYCJĘ będzie wyglądać tak:
"42738Acme"
Zamiast daty zobaczyć liczbę, ale nie przejmuj się, gdyż Excel bez problemu sobie z tym radzi, poza tym scalone ze sobą kolumny Data i Firma też mają w sobie liczby, które są odpowiednikiem dat:
{"42738Acme";"42738Małe Kucyki";"42738Puchatek i Przyjaciele";"42738Małe Kucyki";"42738Acme";"42738Małe Kucyki";"42738Acme";"42738Puchatek i Przyjaciele";"42738Acme";"42738Acme";"42738Acme";"42738Małe Kucyki";"42738Acme";"42738Puchatek i Przyjaciele";"42738Acme";"42738Małe Kucyki";"42738Acme";"42739Acme";"42739Puchatek i Przyjaciele";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Puchatek i Przyjaciele";"42739Puchatek i Przyjaciele";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Acme";"42739Małe Kucyki";"42739Puchatek i Przyjaciele";"42739Acme";"42739Puchatek i Przyjaciele";"42739Acme";"42739Acme";"42739Puchatek i Przyjaciele";"42740Acme";"42740Puchatek i Przyjaciele";"42740Małe Kucyki";"42740Puchatek i Przyjaciele";"42740Puchatek i Przyjaciele";"42740Małe Kucyki";"42740Acme";"42740Małe Kucyki";"42740Acme";"42740Acme";"42740Acme";"42740Acme";"42740Małe Kucyki";"42740Acme";"42740Acme";"42740Małe Kucyki"}
Oglądając film z kanału YT Goodly o wykresie lejkowym, który formatuje się warunkowo w zależności od komórki danych, w którą klikniemy stwierdziłem, że też chcę stworzyć taki wykres, ale prościej 😉 Przede wszystkim mój lejek miał się znajdować w komórkach Excela, a nie na wykresie. Pomogły mi informacje, jakie zdobyłem tworząc kurs o wizualizacji i Dashboaradach w Excelu. Uzyskałem taki efekt.
Oczywiście powyższy wykres podświetla na czerwono wiersz, w który klikniemy. Jak uzyskać taki efekt?
Przede wszystkim musimy stworzyć poziomy „lejka” w komórkach. Robimy to za pomocą odpowiedniej formuły i zmiany czcionki.
Zaczniemy od formuły – potrzebujemy powtórzyć pewną ilość razy znak pionowej kreski "|". Robimy to za pomocą funkcji POWT.
Musimy tylko tak dopasować ilość powtórzeń, żeby każdy kolejny poziom lejka miał odpowiednią szerokość, czyli normalizujemy liczbę powtórzeń. Załóżmy, że maksymalnie chcemy powtórzyć 60 razy znak "|" – dla najwyższego poziomu. Każdy kolejny poziom ma mieć proporcjonalnie mniej znaków. Czyli wystarczy, że wartość z aktualnego poziomu podzielimy przez maksymalną wartość ze wszystkich poziomów (domyślnie przez wartość z najwyższego/pierwszego poziomu). Oczywiście musimy jeszcze przemnożyć przez 60:
=POWT("|";B4*60/MAX($B$4:$B$8))
Zwróć uwagę, że czcionka formuły wygląda nietypowo. To dlatego, że standardowa czcionka by się tutaj nie sprawdziła – byłyby za duże odstępy pomiędzy poszczególnymi pionowymi liniami. Dlatego w przykładzie wykorzystana jest czcionka Haettenschweiler (możesz m.in. skorzystać też z czcionek: Britannic Bold, Nyala, Playbill, Stencil)
Mamy paski, ale nie przypominają one lejka. Rozwiązanie jest proste – wystarczy wyrównać tekst do środka.
Zanim zaczniemy malować wiersze dołóżmy jeszcze proste formuły, które będą obliczały procentowy spadek do pierwszego i poprzedniego kroku. Są one bardzo podobne:
=1‑B5/$B$4 i =1‑B5/B4
Różnica polega tylko na tym czy w pierwszej formule zablokujemy bezwzględnie odwołanie do komórki B4 czy nie.
Teraz możemy zająć się krokami potrzebnymi do tego, żeby nasz mały Dashboard formatował się na czerwono, gdy klikniemy w komórkę danego wiersza. Niestety będziemy potrzebowali skorzystać z kodu VBA. Na szczęście to bardzo prosty fragment kodu, który musimy umieścić w module arkusza, na którym znajduje się nasz „lejek”.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = ActiveCell.Row
End Sub
Powyższy kod VBA uruchamia się za każdym razem, gdy zmienimy zaznaczenie na arkuszu i wstawia do komórki A1 numer wiersza aktywnej komórki.
Może Ci się wydawać, że kod ten nie działa na załączonym pliku, ale wynika to tylko z zastosowanej sztuczki – zmianie formatowania liczbowego komórki A1.
Jeśli zaznaczysz komórkę A1 i naciśniesz skrót Ctrl + 1 zobaczysz, że liczby w komórce zostały sformatowane niestandardowo tak, żeby zawsze pokazywać tekst "Lejek sprzedażowy w komórkach".
Bazując na wartości w komórce A1 tworzymy formatowanie warunkowe (na zakresie naszego lejka – A4:E8) korzystające z formuły:
=$A$1=WIERSZ()
Formuła jest bardzo prosta sprawdza czy aktualny wiersz komórki jest równy wartości przechowywanej w komórce A1 jeśli tak, to nakłada formatowanie. I już gotowe — główne zadanie zostało zrealizowane – lejek zmienia kolor.
My jednak chcemy dołożyć jeszcze parę dodatkowym informacji na naszym mini Dashboardzie. Chcemy podawać liczbę klientów straconych do pierwszego i poprzedniego kroku.
Najczęściej podobne informacje podaje się w polach tekstowych – najpierw w jakiejś odległej (niewidocznej na dashboardzie) komórce wykonuje obliczenia, a potem wstawie pole tekstowe i wpisuje się w pasku formuły, że równa się ono komórce z formułą (nie można wpisać formuły bezpośrednio do pola tekstowego).
W naszym przykładzie robimy ciut inną rzecz – w komórce D2 mamy już jedną z potrzebnych formuł:
ale dodatkowo pod nią (a niejako w niej) znajduje się pole tekstowe z tekstem. Komórce zmieniliśmy kolor czcionki na czerwoną i wyrównaliśmy tekst do góry, a w polu tekstowym zostawiliśmy domyślny kolor czcionki.
W tym przykładzie wygląda to w miarę OK, ale korzystanie z komórki ogranicza położenie wyświetlanych wartości, dlatego częściej stosuje się pole tekstowe, które odwołuje się do komórki z odpowiednią formułą/obliczeniem oraz drugiego pola tekstowego, które funkcjonuje jako podpis.
Na koniec jeszcze jedna czynność często wykorzystywana przy Dashboardach – wyłączanie linii siatki (pole wyboru na karcie Widok).
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Bill Szysz pod poradą 294 napisał jeszcze jedną ciekawą formułę na wyciąganie liczb z początku lub końca tekstu. Ważne jest, że formuła, którą zobaczymy wyciąga liczby, czyli nie zwróci wiodących zer. Za to jest prostą formułą w porównaniu do tych, które stosowaliśmy.
Będziemy musieli wyciągać coraz dłuższe ciągi z naszego tekstu odpowiednio z prawej strony (od końca – funkcja PRAWY) lub z lewej strony (od początku – funkcja LEWY). Zwiększającą się długość zapewnimy sobie za pomocą funkcji WIERSZ, która będzie wskazywała na zablokowany zakres – załóżmy, że od komórki A1 do A17, ale śmiało może przedłużyć ten zakres jeśli masz dłuższe teksty. Ważne, że zakres musi zaczynać się od komórki A1 (pierwszego wiersza) i być zablokowanym, żeby się nie przesuwał.
=PRAWY(A2;WIERSZ($A$1:$A$17))
Śmiało możesz zaznaczyć więcej wierszy nisz masz w tekście, bo w takiej sytuacji funkcja PRAWY (i analogicznie LEWY) wyciągają cały tekst.
Wartości zwracane przez funkcje tekstowe są traktowane jako tekst, więc musimy je przekonwertować na liczby. Z wcześniejszych porad znamy już tą sztuczkę – to podwójna negacja – dwa znaki minus. Jeśli dany tekst da się przekonwertować na liczbę to Excel to zrobi. Jeśli jest to niemożliwe, to będzie zwracał błąd:
W takiej tablicy wystarczy znaleźć największą liczbę. Można to zrobić za pomocą funkcji WYSZUKAJ i szukania dużej liczby np.: 9 do 99 potęgi:
=WYSZUKAJ(9^99;–PRAWY(A2;WIERSZ($A$1:$A$17)))
To już cała formuła ? Analogicznie przy szukaniu liczb od początku tekstu musimy zamienić tylko funkcję PRAWY na LEWY i gotowe.
=WYSZUKAJ(9^99;–LEWY(D2;WIERSZ($A$1:$A$17)))
Na koniec trzeba powiedzieć o jeszcze jednym ograniczeniu tego sposobu – jeśli Excel jest wstanie zamienić jakiś zapis na liczbę (datę) za pomocą podwójnej negacji to to zrobi. Np. tekst „23 Czekolada” powinien zwracać poprawnie 23, ale wynik formuły jest całkiem inny:
Wynika to, że w kolejnych wyciąganych tekstach pojawia się zapis „23 Cze”, który Excel tłumaczy sobie jako 23 czerwca bieżącego roku i zamienia na odpowiednią reprezentację liczbową. Stąd taki niespodziewany wynik formuły.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
