Excel - kurs online - oferta dla każdego

Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.


Excel - kurs online. Dlaczego warto?

Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.

Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.

Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.

Generator PESEL NIP i REGON — porada #298

W internecie znajdziesz wiele stron pozwalających wygenerować losowy PESEL. Ponieważ skupiam się na Excelu, dlatego postanowiłem zrobić taki generator w Excelu (przy okazji również generatory NIPREGON).
Żeby wygenerować losowy PESEL musimy znać jego strukturę. Większość z nas wie, że pierwsze 6 cyfr odpowiada za datę urodzenia. Już nie wszyscy wiedzą, że numery miesiąca zmieniają się w zależności od stulecia. Nas interesuje tylko XX wiek – lata 1900–1999 oraz XXI wiek – lata 2000–2099. XX wiek nie zmienia nic w numerowaniu miesięcy, ale XXI wiek dodaje liczbę 20 do numeru miesiąca w roku. Czyli osoba urodzona w 1910-05-23 i 2010-05-23 będzie miała początek PESEL odpowiednio 100523 i 102523. 

Jak to zrobić w Excelu? Przede wszystkim potrzebujemy wylosować datę urodzenia. Świetnie się tu sprawdza funkcja LOS.ZAKR. Której wpisujemy w cudzysłowach daty minimalną i maksymalną pomiędzy którymi chcemy losować:

=LOS.ZAKR("1900–01-01";"2010–01-01")

Porada 298 - Generator PESEL 01

Zanim zaczniemy przekształcać odpowiednio tą datę wylosujmy jeszcze płeć naszego posiadacza PESEL (od tego zależy 10 cyfra PESEL). Posłużymy się tutaj funkcją INDEKS, której na stałe wpiszemy tablicę liter ‘k’ i ‘m’ i będziemy losować albo 1 albo 2 pozycję tablicy (funkcja LOS.ZAKR):

=INDEKS({"k";"m"};LOS.ZAKR(1;2))

Porada 298 - Generator PESEL 02

Teraz zacznijmy przekształcać datę. Będziemy musieli ją podzielić na część odpowiedzialną za rok, miesiąc i dzień. Rok będzie najprostszy, bo wystarczy odpowiednio sformatować wylosowaną datę jako rok za pomocą funkcji TEKST:

=TEKST([Data];"rr")

Porada 298 - Generator PESEL 03

Powyższy zapis wstawia tylko 2 ostatnie cyfry z roku. Teraz przyszła pora na miesiąc. Z nim jest trudniej – możemy wyciągnąć miesiąc z daty za pomocą funkcji MIESIĄC, ale musimy dołożyć mechanizm, który dodawałby 20 dla XXI wieku (inne stulecia poza dwoma wcześniej ustalonymi nas nie interesują). W taki razie wystarczy sprawdzić, czy ROK z daty jest większy od 1999 i jeżeli tak to zwrócić 20, a jeśli nie to 0 za pomocą funkcji JEŻELI. Potrzeba do tego dodać jeszcze numer miesiąca z daty:

JEŻELI(ROK([Data])>1999;20;0)+MIESIĄC([Data])

Niestety to jeszcze nie wystarczy ponieważ dla miesięcy z XX wieku mogą nam wyjść wyniki 1 cyfrowe, a my potrzebujemy mieć zawsze 2 cydry, dlatego powyższą formułę musimy wstawić do funkcji TEKST, która będzie wymuszała wiodące 0. Tak uzyskaną formułę łączymy z cyframi roku:

=TEKST([Data];"rr")&TEKST(JEŻELI(ROK([Data])>1999;20;0)+MIESIĄC([Data]);"00")

Porada 298 - Generator PESEL 04

Potrzebujemy jeszcze dodać 2 cyfry jako dni. Robimy to analogicznie jak rok tylko wpisujemy do funkcji TEKST kod odpowiedzialny za formatowanie dni:

TEKST([Data];"dd")

W ten sposób mamy pełną formułę budującą 6 cyfr numeru PESEL.

=TEKST([Data];"rr")&TEKST(JEŻELI(ROK([Data])>1999;20;0)+MIESIĄC([Data]);"00")&TEKST([Data];"dd")

Porada 298 - Generator PESEL 05

Teraz zajmijmy się wyznaczeniem 4 kolejnych cyfr. Powiedzieliśmy sobie, że 10 cyfra oznacza płeć. Dokładnie liczby {0;2;4;6;8} oznaczają kobietę, a {1;3;5;7;9} mężczyznę. 3 wcześniejsze cyfry to kolejność urodzenia w danym dniu, czyli wystarczy funkcja LOS.ZAKR z odpowiednimi granicami. A musimy pamiętać, że to zawsze muszą być 3 cyfry, więc dodajemy funkcję TEKST, która zapewni ewentualne wiodące zera.

=TEKST(LOS.ZAKR(1;999);"000")

Jak jednak ustalić numer odpowiedzialny za płeć.

Jak się mu przyjrzymy to możemy zobaczyć, że jeśli będziemy losować liczbę z przedziału od 0 do 4 i przemnożymy ją przez 2 to zawsze wyjdzie nam kobieta. W takim razie wystarczy, że sprawdzimy za pomocą funkcji JEŻELI, czy wylosowaliśmy wcześniej kobietę czy mężczyznę i odpowiednio zwracali 0 lub 1 i tą wartość dodali do naszego losowania. 

LOS.ZAKR(0;4)*2+JEŻELI([@Płeć]="k";0;1)

Łącząc powyższe dwie formuły mamy 4 kolejne cyfry numeru PESEL:

=TEKST(LOS.ZAKR(1;999);"000")&LOS.ZAKR(0;4)*2+JEŻELI([@Płeć]="k";0;1)

Porada 298 - Generator PESEL 06

Teraz czeka nas najtrudniejsze zadanie – musimy obliczyć cyfrę kontrolną. Zasada jej obliczania wymaga przemnożenia każdej cyfry przez przypisaną jej wagę, a następnie ich zsumowanie. Wagi możemy zapisać w tablicy {1;3;7;9;1;3;7;9;1;3} ważne, że ta tablica ma w Excelu orientację pionową (każdy średnik oznacza nowy wiersz). 

Już kilka razy wyciągaliśmy pojedyncze znaki z tekstu (np.: porada 295 i 296) z numerem PESEL jest analogicznie, a nawet prościej, bo zawsze mamy 10 cyfr (musimy je tylko połączyć z wcześniejszych obliczeń) i możemy zapisać na stałe tablicę {1;2;3;4;5;6;7;8;9;10} (ta sama orientacja co tablica z wagami, żeby mieć pewność poprawnych wyników). Czyli nasze mnożenie i sumowanie wygląda tak:

=SUMA(FRAGMENT.TEKSTU([@DataP]&[@PłećP];{1;2;3;4;5;6;7;8;9;10};1)*{1;3;7;9;1;3;7;9;1;3})

To jeszcze nie koniec naszych obliczeń, bo z takiego mnożenia i sumowania (swoją drogą dzięki wpisaniu tablic na stałe możemy zatwierdzać tą formułę normalnie, a nie jako formułę tablicową) daje wynik większy od 1 cyfry. W obliczeniach liczby kontrolnej musimy wyciągnąć resztę dzielenia przez 10 (funkcja MOD) i odjąć ją od 10. Ponieważ czasami wynik może wyjść 10, to jeszcze raz musimy go wstawić do funkcji MOD:

=MOD(10-MOD(SUMA(FRAGMENT.TEKSTU([@DataP]&[@PłećP];{1;2;3;4;5;6;7;8;9;10};1)*{1;3;7;9;1;3;7;9;1;3});10);10)

Porada 298 - Generator PESEL 07

Uff. Mamy wszystkie elementy teraz musimy tylko połączyć je razem:

=[@DataP]&[@PłećP]&[@Kontrolna]

Porada 298 - Generator PESEL 08

W załączonym pliku znajdziesz jeszcze mechanizmy losujący numery NIPREGON.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i nie tablicowa — porada #296

Potrzebujemy wyciągnąć wszystkie cyfry z tekstu jako liczbę. Nasze ograniczenia są takie, że nie możemy skorzystać z pomocy funkcji POŁĄCZ.TEKSTY czy ZŁĄCZ. TEKST, a dodatkowo nasza formuła nie może być zatwierdzana za pomocą kombinacji klawiszy Ctrl + Shift + Enter. To ciężkie zadanie, ale już ktoś taką formułę wymyślił:

https://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html
Poniżej opiszemy ją krok po kroku.

Żeby wyciągnąć wszystkie cyfry musimy wyciągnąć każdy znak i sprawdzić, czy jest on cyfrą/liczbą. Możemy to zrobić za pomocą funkcji FRAGMENT.TEKSTU, która wyciąga z testu fragment od wskazanego numeru znaków o wskazanej długości. W tym przykładzie będziemy chcieli wyciągać pojedyncze znaki, czyli długość tekstu = 1. Większym problemem jest to, że potrzebujemy wyciągnąć każdy pojedynczy znak, czyli najpierw wyciągamy pierwszy znak, potem drugi itd, czyli potrzebujemy tablicy znaków, która liczy od 1 do ilości znaków w tekście, który analizujemy.

Żeby to zrobić skorzystamy ze sztuczki, którą znajdziesz w internecie – wykorzystując funkcję ADR.POŚR odwołamy się od wiersza 1 do długości ciągu tekstowego:

=ADR.POŚR("1:"&DŁ(A2))

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 01

Tylko to dużo więcej niż potrzebujemy. Nam wystarczą tylko numery wierszy, czyli wstawiamy powyższą formułę do funkcji WIERSZ.

=WIERSZ(ADR.POŚR("1:"&DŁ(A2)))

Właśnie zbudowaliśmy tablicę numerującą znaki do funkcji FRAGMENT.TEKSTU.

{1;2;3;4;5;6;7;8;9;10;11;12;13;14}

Wystarczy ją tylko w niej umieścić pamiętając, że zawsze wyciągamy pojedyncze znaki:

=FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1)

Uzyskamy dzięki temu tekst rozłożony na pojedyncze znaki (tablicę znaków):

{"5";" ";"S";"u";"k";"i";"e";"n";"k";"a";" ";"1";"0";"0"}

Tylko poważnym problemem jest to, że funkcja FRAGMENT.TEKSTU wszystkie znaki zwraca jako tekst (są w podwójnych cudzysłowach). Na szczęście dowolna operacja matematyczna na liczbie zapisanej jako tekst zamienia ją ponownie na „prawdziwą” liczbę ? Skorzystamy z tutaj podwójnej negacji, czyli dwóch znaków minus przed funkcją. Wynikiem tego będzie tablica liczb i błędów

{5;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;1;0;0}

Teraz możemy sprawdzić gdzie są liczby za pomocą funkcji CZY.LICZBA.

=CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 02

Wynikiem tej formuły jest tablica wartości logicznych PRAWDA (którym chcemy przypisać ich pozycję na liście) i FAŁSZ (którym chcemy przypisać 0)

{PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;PRAWDA}

Żeby to zrobić wystarczy, że przemnożymy ją przez już wcześniej stworzoną tablicę po numerach wierszy 

=CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)))

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 03

Wynikiem tej formuły będzie tablica z pozycjami cyfr i zerami tam gdzie cyfr nie było:

{1;0;0;0;0;0;0;0;0;0;0;12;13;14}

Do naszych potrzeb potrzebujemy ustawić tą tablicę od największych wartości do najmniejszych, czyli skorzystamy z funkcji MAX.K, ale zanim to zrobimy wykonamy pewną sztukę, która sprawi, że na zakończenie nie będziemy musieli zatwierdzać naszej formuły jako formuły tablicowej. Sztuczka ta polega na wstawieniu naszej formuły do funkcji INDEKS i wyciągnięcia całego wiersza danych wpisując w drugim argumencie funkcji INDEKS wartości zero.

=INDEKS(CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)));0)

Nie wiem dokładnie dlaczego ta sztuczka działa, bo podglądając wynik wygląda on tak samo z i bez funkcji INDEKS, ale najważniejsze, że działa. Teraz jak wspomnieliśmy wkładamy naszą formułę do funkcji MAX.K, a jako argument k posłuży nam znów liczenie po kolei wierszy.

MAX.K(INDEKS(CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)));0);WIERSZ(ADR.POŚR("1:"&DŁ(A2))))

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 04

Podglądając wynik zobaczymy najprawdopodobniej dużo zer.

{14;13;12;1;0;0;0;0;0;0;0;0;0;0}

Niestety będą nam te zera przeszkadzać później w funkcji FRAGMENT.TEKSTU, bo nie możemy wyciągać znaków od zerowego znaku (taki nie istnieje). Żeby się ich pozbyć po prostu dodajemy jedynkę do naszej formuły (+1). Jednak jeśli wykonamy taką operacje to przesuną się znaki, które będziemy wyciągać, dlatego dodatkowo dołożymy na początek naszego tekstu zero (0&A2)

=FRAGMENT.TEKSTU(0&A2;MAX.K(INDEKS(CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)));0);WIERSZ(ADR.POŚR("1:"&DŁ(A2))))+1;1)

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 05

Wynikiem tej formuły będą wyciągane cyfry od końca tekstu (od prawej strony) oraz dużo początkowych dodanych zer:

{"0";"0";"1";"5";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0"}

Musimy pamiętać, że to znowu są liczby, które Excel traktuje jako tekst. Musimy te wartości przemnożyć przez liczby w zależności od ich pozycji. Dokładnie pierwszą wartość potrzebujemy przemnożyć przez 1, drugą przez 10, trzecią przez 100 itd. Są to kolejne potęgi 10, więc wystarczy, żebyśmy 10 podnieśli do potęgi pozycji cyfry na liście. Czyli znów korzystamy z listy wierszy:

10^WIERSZ(ADR.POŚR("1:"&DŁ(A2)))/10

Tylko potęgowanie musimy podzielić przez 10, żebyśmy zaczęli od 1, a nie od 10.

{1;10;100;1000;10000;100000;1000000;10000000;100000000;1000000000;10000000000;100000000000;1000000000000;10000000000000}

Połączona formuła wygląda tak:

=FRAGMENT.TEKSTU(0&A2;MAX.K(INDEKS(CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)));0);WIERSZ(ADR.POŚR("1:"&DŁ(A2))))+1;1)*10^WIERSZ(ADR.POŚR("1:"&DŁ(A2)))/10

A w wyniku daje odpowiednią tablicę liczb:

{0;0;100;5000;0;0;0;0;0;0;0;0;0;0}

Którą wystarczy zsumować za pomocą funkcji SUMA.ILOCZYNÓW, żebyśmy nie musieli zatwierdzać formuły jako tablicowej, czyli końcowa formuła wygląda tak:

=SUMA.ILOCZYNÓW(FRAGMENT.TEKSTU(0&A2;MAX.K(INDEKS(CZY.LICZBA(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1))*WIERSZ(ADR.POŚR("1:"&DŁ(A2)));0);WIERSZ(ADR.POŚR("1:"&DŁ(A2))))+1;1)*10^WIERSZ(ADR.POŚR("1:"&DŁ(A2)))/10)

Porada 296 - Jak wyciągnąć wszystkie cyfry z tekstu bez POŁĄCZ.TEKSTY i formuł tablicowych 06

Warto zwrócić uwagę na to, że ta formuła zwraca liczbę, a nie tekst składający się z cyfr i pomija wiodące zera w porównaniu do formuły z porady 295.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Jak wyciągnąć wszystkie cyfry z tekstu POŁĄCZ TEKSTY — porada #295

Przed nami ciężkie zadanie wyciągnięcia wszystkich cyfr. Okazuje się ono dużo prostsze jeśli w Twojej wersji Excel 2016 (lub 365) masz zainstalowany już update z funkcją POŁĄCZ.TEKSTY i ZŁĄCZ.TEKST (ważne! bez Y na końcu, bo ZŁĄCZ.TEKSTY działa inaczej).

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 01

Jeśli masz te funkcję to czytaj dalej. Jeśli nie to zajrzyj do następnej porady (296).

Żeby wyciągnąć wszystkie cyfry musimy wyciągnąć każdy znak i sprawdzić, czy jest on cyfrą/liczbą. Możemy to zrobić za pomocą funkcji FRAGMENT.TEKSTU, która wyciąga z testu fragment od wskazanego numeru znaków o wskazanej długości. W tym przykładzie będziemy chcieli wyciągać pojedyncze znaki, czyli długość tekstu = 1. Większym problemem jest to, że potrzebujemy wyciągnąć każdy pojedynczy znak, czyli najpierw wyciągamy pierwszy znak, potem drugi itd, czyli potrzebujemy tablicy znaków, która liczy od 1 do ilości znaków w tekście, który analizujemy.

Żeby to zrobić skorzystamy ze sztuczki, którą znajdziesz w internecie – wykorzystując funkcję ADR.POŚR odwołamy się od wiersza 1 do długości ciągu tekstowego:

=ADR.POŚR("1:"&DŁ(A2))

Tylko to dużo więcej niż potrzebujemy. Nam wystarczą tylko numery wierszy, czyli wstawiamy powyższą formułę do funkcji WIERSZ.

=WIERSZ(ADR.POŚR("1:"&DŁ(A2)))

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 02

Właśnie zbudowaliśmy tablicę numerującą znaki do funkcji FRAGMENT.TEKSTU.

{1;2;3;4;5;6;7;8;9;10;11;12;13;14}

Wystarczy ją tylko w niej umieścić pamiętając, że zawsze wyciągamy pojedyncze znaki:

=FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1)

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 03

Uzyskamy dzięki temu tekst rozłożony na pojedyncze znaki (tablicę znaków):

{"5";" ";"S";"u";"k";"i";"e";"n";"k";"a";" ";"1";"0";"0"}

Tylko poważnym problemem jest to, że funkcja FRAGMENT.TEKSTU wszystkie znaki zwraca jako tekst (są w podwójnych cudzysłowach). Na szczęście dowolna operacja matematyczna na liczbie zapisanej jako tekst zamienia ją ponownie na „prawdziwą” liczbę ? Skorzystamy z tutaj podwójnej negacji, czyli dwóch znaków minus przed funkcją. Wynikiem tego będzie tablica liczb i błędów

{5;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;#ARG!;1;0;0}

Na szczęście jest nam to na rękę ponieważ nam zależy tylko na liczbach, a tam gdzie pojawił się błąd nie chcemy nic wyświetlać, więc wystarczy, że wstawimy naszą formułę do funkcji JEŻELI.BŁĄD, która zamiast błędów będzie zwracała pusty ciąg tekstowy (dwa podwójne cudzysłowy)

=JEŻELI.BŁĄD(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1);"")

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 04

Teraz formułą zwraca tablicę cyfr i pustych ciągów znaków, 

{5;"";"";"";"";"";"";"";"";"";"";1;0;0}

którą możemy łatwo połączyć za pomocą funkcji ZŁĄCZ.TEKST (przypominam bez Y na końcu) lub POŁĄCZ.TEKSTY jeśli zależy nam, żeby rozdzielać cyfry konkretnym ogranicznikiem, a nie bezpośrednio łączyć je ze sobą, ale w większości sytuacji chcemy je bezpośrednio łączyć, więc formuła będzie wyglądać tak:

=ZŁĄCZ.TEKST(JEŻELI.BŁĄD(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1);""))

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 05

Musimy tylko pamiętać, że jest to formuła tablicowa i musimy ją zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter.

Przykład dla funkcji POŁĄCZ.TEKSTY z przecinkiem jako ogranicznikiem i pomijaniem pustych wartości (wartość logiczna PRAWDA) wygląda tak:

=POŁĄCZ.TEKSTY(",";PRAWDA;JEŻELI.BŁĄD(–FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1);""))

Porada 295 - Jak wyciągnąć wszystkie cyfry z tekstu 06

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP (Excel)

Ile razy ciąg powtarza się w tekście — porada #283

Potrzebujesz dowiedzieć się ile razy dane ciąg tekstowy pojawia się w innym ciągu tekstowym? Formuła na to jest dość prosta i opisywałem ją w pytaniu od widzów nr 79:

=(DŁ($A$2)-DŁ(PODSTAW($A$2;C2;"")))/DŁ(C2)

Ale pod postem na stronie:
https://chandoo.org/wp/2016/07/19/find-out-how-many-times-a-value-is-present-in-a-cell-formulas/

pojawiło się pytanie w komentarzach co w sytuacji, gdy szukamy ciągu powtarzających się znaków np: "AAAA" i natrafimy na powtórzenie 5 razy danego znaku, a nie szukane 4? 

Porada 283 - Ile razy ciąg powtarza się w tekście 01

Formuła opisane w pytaniu widzów 79, zliczy tylko 1 wystąpienie tego ciągu, a co jeśli nam zależy, żeby zliczała każde pojawienie się, czyli ciąg "AAAAA" oznaczałby dwukrotne powtórzenie szukanego ciągu "AAAA" i odpowiednio im więcej razy powtórzy się "A", tym więcej razy mamy odnaleźć szukanych ciągów.

W takiej sytuacji nasza formuła będzie bardziej skomplikowane. Potrzebujemy sprawdzić każdy ciąg długości naszego szukanego ciągu w przeszukiwanym tekście, czyli w naszym przykładzie wyciągnąć 4 znaki od pierwszego znaku, później od drugiego itd. Zaczniemy budować naszą formułę od środka — w pierwszej kolejności potrzebujemy ciąg początkowych znaków, czyli w uproszczeniu 1,2,3…

Żeby zbudować go w Excelu możemy posłużyć się funkcją PRZESUNIĘCIE, która zaczyna od komórki $A$1. Nie chcemy zmienić pozycję naszego zakresu, tylko jego wysokość, dlatego możemy wpisać wartości 0, lub nic w argumenty wiersze i kolumny. Musimy natomiast zmienić wysokość zakresu — żeby uprościć formułę, wysokość będzie równa długości przeszukiwanego ciągu:

=PRZESUNIĘCIE($A$1;;;DŁ(A4))

Porada 283 - Ile razy ciąg powtarza się w tekście 02

Tylko, że nas nie interesują wartości z poszczególnych komórek zakresu, tylko numery wierszy dla poszczególnych komórek:

=WIERSZ(PRZESUNIĘCIE($A$1;;;DŁ(A4)))

Porada 283 - Ile razy ciąg powtarza się w tekście 03

Dzięki temu uzyskaliśmy ciąg {1;2;3;4;…}, czyli numery znaków, od których będziemy wyciągać nasze ciągi do porównania do szukanego ciągu. Wykorzystamy do tego funkcję FRAGMENT.TEKSTU.

=FRAGMENT.TEKSTU(A4;WIERSZ(PRZESUNIĘCIE($A$1;;;DŁ(A4)));DŁ($B$1))

Porada 283 - Ile razy ciąg powtarza się w tekście 04

Przy tak zapisanej formule końcowe ciągi są krótsze od szukanego ciągu i dlatego nie będą mu nigdy równe, 

Porada 283 - Ile razy ciąg powtarza się w tekście 05

ale formuła tu będzie ciut prostsza, a nie ma to wpływu na wynik.

Teraz jak mamy wyciągnięte poszczególne kawałki, możemy je przyrównać do ciągu szukanego przez nas, a następnie możemy je policzyć. Żeby nie potrzebować zatwierdzania jako formuła tablicowa wykorzystamy funkcję SUMA.ILOCZYNÓW, tylko będziemy musieli zamienić wyniki PRAWDA i FAŁSZ porównań na wartości 1 i 0 np. za pomocą podwójnego minusa:

=SUMA.ILOCZYNÓW(–(FRAGMENT.TEKSTU(A4;WIERSZ(PRZESUNIĘCIE($A$1;;;DŁ(A4)));DŁ($B$1))=$B$1))

Porada 283 - Ile razy ciąg powtarza się w tekście 06

Tak właśnie uzyskujemy wynik, którego szukaliśmy.

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak wyciągnąć tekst pomiędzy ostatnim a przedostatnim backslashem — widzowie #104

Szef zlecił Ci zadanie (może ma na imię Robert ;)), żeby wyciągnąć tekst, który się znajduje pomiędzy przedostatnim, a ostatnim backslashem (\). To zadanie wymaga trochę pracy. Żeby było łatwiejsze podzielimy je sobie na trzy części. Najpierw znajdziemy pozycję przedostatniego i ostatniego backslasha, a potem już łatwo wyciągniemy tekst pomiędzy nimi.
Żeby odnaleźć pozycję przedostatniego i ostatniego backslasha najpierw będziemy potrzebować policzyć ile ich jest. Zrobimy to podmieniając wszystkie backslashe w tekście na pusty ciąg tekstowy, czyli po prostu usuniemy je. To uzyskamy za pomocą funkcji PODSTAW:

=PODSTAW(A2;"\";"")

Kolejnym krokiem będzie obliczenie długości tekstu/ścieżki, po usunięciu backslashy, to da nam funkcja DŁ, a żeby uzyskać liczbę backslashy potrzebujemy odjąć tą długość od faktycznej długości tekstu:

=DŁ(A2)-DŁ(PODSTAW(A2;"\";""))

widzowie-104-jak-wyciagnac-tekst-pomiedzy-ostatnim-a-przedostatnim-backslash-01
Założyliśmy sobie, że najpierw odszukamy pozycję przedostatniego backslasha, więc musimy tu jeszcze odjąć jedynkę. Teraz możemy, ten konkretny numer znaku zastąpić takim ciągiem tekstowym, który na pewno nie powtórzy się w przeszukiwanym tekście np.: #@, to znów będzie funkcja PODSTAW:

=PODSTAW(A2;"\";"#@";DŁ(A2)-DŁ(PODSTAW(A2;"\";""))-1)

widzowie-104-jak-wyciagnac-tekst-pomiedzy-ostatnim-a-przedostatnim-backslash-02

Teraz wystarczy, że odnajdziesz pozycję tego nietypowego ciągu znaków (funkcja ZNAJDŹ) i masz pozycję przedostatniego backslasha:

=ZNAJDŹ("#@";PODSTAW(A2;"\";"#@";DŁ(A2)-DŁ(PODSTAW(A2;"\";""))-1))

widzowie-104-jak-wyciagnac-tekst-pomiedzy-ostatnim-a-przedostatnim-backslash-03
Pozycję ostatniego znaku odnajdziesz po prostu nie odejmując jedynki:

=ZNAJDŹ("#@";PODSTAW(A2;"\";"#@";DŁ(A2)-DŁ(PODSTAW(A2;"\";""))))

widzowie-104-jak-wyciagnac-tekst-pomiedzy-ostatnim-a-przedostatnim-backslash-04
Teraz jak mamy już pozycję ostatniego i przedostatniego znaku to łatwo wyciągnąć to co jest pomiędzy nimi za pomocą funkcji FRAGMENT.TEKSTU. Trzeba tylko pamiętać dodać i odjąć jeden, żeby nie wyciągnąć również backslashy:

=FRAGMENT.TEKSTU(A2;C2+1;D2-C2‑1)

widzowie-104-jak-wyciagnac-tekst-pomiedzy-ostatnim-a-przedostatnim-backslash-05

Pozdrawiam
Adam Kopeć
Miłośnik Excela