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:
http://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 pobrać dane przycisku, który uruchomił makro – odcinek #18

Dziś chcemy poznać kod, który umożliwi nam odczytanie danych przycisku, który uruchomił makro, dzięki czemu będziemy mogli odczytać informacje bezpośrednio np: z tekstu przechowywanym w przycisku, a nie zamieszczać tych informacji w kodzie VBA.
Dodatkowo będziemy mogli podpiąć to samo makro pod wiele przycisków/kształtów i w zależności od tego, który przycisk klikniesz wpiszą się inne informacje.

Zrobimy to na uproszczonym przykładzie akcji, jakie mogą wykonywać koszykarze z kosmicznego meczu. Mamy 3 przyciski akcji i 5 przycisków zawodników. Odpowiednio przyciski akcji są podpięte pod makro Akcja, a przyciski zawodników pod makro Zawodnik.

VBA 18 - Jak pobrać dane przycisku, który uruchomił makro 01

Dwie najważniejsze dla nas linijki kodu z tych dwóch makr to:
Przycisk = Application.Caller
NazwaPrzycisku = ActiveSheet.Shapes(Przycisk).TextFrame.Characters.Text

Najpierw pobieramy „wewnętrzną” nazwę przycisku (obiektu) który uruchomił makro, a później wykorzystujemy tą nazwę, żeby wyciągnąć dokładną informację o tekście przechowywanym w kształcie/przycisku. Ot i cała filozofia. Reszta kodu służy temu, żeby informacje wstawiały się w odpowiednich miejscach.

Przy tym kodzie ważna jest jeszcze informacja, że warto, żeby tekst w kształtach nie był wpisywany ręcznie tylko pobierany z komórek Excela.

VBA 18 - Jak pobrać dane przycisku, który uruchomił makro 02

Dzięki temu szybko możemy zmienić np: drużynę i jej wszystkich zawodników po prostu przekopiowując w komórki powiązane zawodników z drugiej drużyny.

Sub Zawodnik()
Dim Przycisk As String, NazwaPrzycisku As String
'Pobiera "wewnętrzną" nazwę przycisku/kształtu
Przycisk = Application.Caller
'na podstawie tej nazwy wyciągamy tekst wpisany w tym kształcie
NazwaPrzycisku = ActiveSheet.Shapes(Przycisk).TextFrame.Characters.Text

If Range(„C2”).Value = „” Then
Range(„C2”).Value = NazwaPrzycisku
Else
Range(„C1”).End(xlDown).Offset(1, 0).Value = NazwaPrzycisku
End If
End Sub

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Wyciąganie drugiego imienia – Tekst jako kolumny i JEŻELI działaj jak umiesz – porada #282

Mówi się, że przeciętny użytkownik Excela zna około 2% jego możliwości. Dla nas nie jest istotne ile tych procent faktycznie jest, ale żeby nauczyć się korzystać z tego co umiesz, bo w Excelu, jak w życiu, jest wiele sposobów na rozwiązanie problemów, które stoją przed Tobą. Dlatego w tym wpisie chodzi przede wszystkim o to, żeby nakłonić Cię do myślenia, żebyś wykorzystał swoje szare komórki i umiejętności do rozwiązania problemu, a nie żebyś załamywał ręce.

Zrobimy to na przykładzie podziału danych osobowych na pierwsze imię, drugie imię i nazwisko. Robimy założenie, że znamy polecenie Tekst jako kolumny i JEŻELI.

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-01

W pierwszym kroku wystarczy, że dane osobowe podzielimy za pomocą polecenia Tekst jako kolumny z karty Dane

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-02

według ogranicznika,

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-03

którym będzie spacja

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-04

na kolumny i wstawimy do komórki B2.

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-05

Nasz podział nie będzie idealny, bo czasami będziemy mieli 2, a czasami 3 kolumny, ale tutaj z pomocą przyjdzie nam jeszcze funkcja JEŻELI. Najpierw będziemy sprawdzać czy jest drugie imię – jeśli jest wypełniona 3 kolumna to znaczy, że jest drugie imię, co się przekłada na formułę:

=JEŻELI(D3=””;””;C3)

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-06

W podobny sposób, jeśli 3 kolumna nie jest pusta oznacza to, że jest w niej nazwisko, a jeśli jest to znaczy, że nazwisko jest w 2 kolumnie, czyli możemy odpowiednio napisać taką formułę:

=JEŻELI(D2<>„”;D2;C2)

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-07

Może nie jest to najelegantszy sposób na rozwiązanie tego problemu, ale w większości sytuacji wystarczający i przede wszystkim opiera się, z założenia, o znane nam funkcjonalności Excela 😉

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak zaznaczyć 5 komórek w ciągu, które spełniają warunek – widzowie #108

Dostałem zapytania jak zaznaczyć ciąg 5 (lub więcej) komórek, które spełniają warunek (np: są puste). Jeśli możesz sobie pozwolić, że nad danymi, które sprawdzasz jest odpowiednia ilość wierszy, gdzie nie będzie spełniony sprawdzany warunek, to zadanie sprowadza się do liczenia najdłuższego ciągu w zakresie, który się przesuwa – porada 263:
https://www.youtube.com/watch?v=as9mztMnTsw

Końcowa formuła tablicowa (musisz zatwierdzić Ctrl + Shift + Enter):

=MAX(CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(A1:A9);WIERSZ(A1:A9);””);JEŻELI(CZY.PUSTA(A1:A9);””;WIERSZ(A1:A9))))>4

Jednak czasami zdarza się sytuacja, gdzie musisz martwić się o sufit, czyli górę arkusza, żeby Twój zakres nie wyszedł poza niego. W takiej sytuacji musisz najpierw zbudować zakres, który zatrzyma się na pierwszym wierszu arkusza i nie będzie szedł wyżej. Przyda się Ci do tego funkcja INDEKS.

Tablicą na którą będziemy patrzeć jest cała kolumna A (odwołanie A:A). Musimy tylko wybrać odpowiedni wiersz. W górę musimy przesunąć się 4 wiersze w górę (dla ciągu 5 komórek spełniających nasz warunek), ale nie dalej niż wiersz 1. Wystarczy, że do funkcji MAX wstawimy formułę WIERSZ()-4 oraz 1, funkcja WIERSZ bez podania argumentów zwraca wiersz komórki, w której znajduje się formuła. Czyli pierwsza komórka zakresu to:

=INDEKS(A:A;MAX(WIERSZ()-4;1))

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-01

Tu trzeba wspomnieć, że funkcja INDEKS zwraca faktycznie odwołanie do komórki, a nie jej wartość, dzięki czemu jeśli dołożymy dwukropek możemy za pomocą funkcji INDEKS budować zakresy. Druga komórka jest łatwiejsza do zbudowania, bo to tylko WIERSZ()+4, czyli:

=INDEKS(A:A;WIERSZ()+4)

łącząc dwie powyższe formuły mamy zbudowany dynamiczny zakres:

=INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4)

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-02

Teraz musimy sprawdzić, czy komórki z zakresu spełniają warunek, tu jest on prosty – wystarczy wstawić naszą formułę do funkcji CZY.PUSTA. Ważniejszy jest kolejny krok, gdzie jeśli dana komórka spełnia nasz warunek, to chcemy, żeby został zwrócony numer wiersza tej komórki. Będziemy potrzebować funkcji JEŻELI i funkcji WIERSZ po naszym zbudowanym zakresie. Jeśli komórka nie spełnia warunku chcemy uzyskać pusty ciąg znaków („”), czyli całość sprowadza się do formuły:

=JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));””)

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-03

Zbudowana przez nas formuła posłuży jako tablica danych do funkcji CZĘSTOŚĆ, natomiast jako tablicę przedziałów będziemy chcieli mieć odwrotność zbudowanej formuły, czyli pusty ciąg znaków ma być wtedy, gdy warunek jest spełniony, a numer wiersza wtedy, gdy warunek nie jest spełniony.

=CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));””);JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));””;WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4))))

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-04

Pozostaje sprawdzić, jaki jest maksymalny ciąg (wstawić naszą formułę do funkcji MAX) i pamiętać żeby zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter ponieważ jest to formuła tablicowa.

Niestety tej formuły nie przyjmie nam formatowanie warunkowe, bo dynamicznie budujemy zakres za pomocą funkcji INDEKS i musimy sobie zostawić formułę w kolumnie pomocniczej, by sprawdzać, czy dla danego wiersza maksymalny ciąg spełnia nasze kryteria.

Ponieważ te komplikacje występują przypomnę jeszcze raz, że łatwiejszym rozwiązaniem może być dostawienie wierszy na początku arkusza, żebyśmy od pierwszej sprawdzanej komórki mogli zaznaczyć cały sprawdzany zakres, który będzie się przesuwał z formułą w dół, czyli przykładowa formuła:

=MAX(CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(A1:A9);WIERSZ(A1:A9);””);JEŻELI(CZY.PUSTA(A1:A9);””;WIERSZ(A1:A9))))>4

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Zmiana ludzkiej tabelki na bardziej bazodanową – funkcje Excela – porada #281

Często dostaje dane, które są zapisany w wygodny dla człowieka sposób, ale bardzo niewygodny dla Excela.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-01

Na podstawie danych nie da się stworzyć Tabeli Przestawnej i innych analiz danych dostępnych w Excelu. Trzeba je najpierw przekształcić . W poradzie 280 opisałem jak to można zrobić za pomocą Power Query, ale ponieważ nie wszyscy mogą zainstalować ten dodatek poniżej znajdziesz opisany sposób jako możesz to zrobić za pomocą formuł w Excelu. Specjalnie dlatego nagrałem ten film na Excelu 2007, żeby pokazać, że jest to możliwe.

W pierwszej kolejności musimy usunąć scalanie nagłówków, żeby późniejsze formuły były prostsze.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-02

Później musimy zaznaczyć tylko puste komórki z nagłówków. Najprościej to zrobisz najpierw naciskając skrót klawiszowy Ctrl + G, a następnie z okna, które się pojawi wybierzesz przycisk Specjalnie.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-03

W kolejnym oknie, które się pojawi musisz wybrać polecenie Puste i zatwierdzić wybór przyciskiem OK.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-04

Teraz musisz wpisać znak = w komórkę i kliknąć na komórkę obok i zatwierdzić formułę skrótem Ctrl + Enter. Dzięki temu formuła odpowiednio przesunięta zostanie wstawiona we wszystkie puste komórki.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-05

Następnie potrzebujemy skopiować nagłówki i wkleić je jako wartości.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-06

Teraz możemy się zająć pisaniem formuł do tabeli. Zaczniemy od numeru entomologa. Wiemy, że jest ich 20 i muszę się systematycznie powtarzać, żeby dla każdego entomologa (jego numeru) w wierszu znalazł się jeszcze miesiąc i typ owada. Ponieważ mamy 20 entomologów, 3 typu owadów i 12 miesięcy, to w sumie będziemy potrzebować 720 wierszy.

W takim razie jak zapewnimy sobie cykliczne powtarzanie numerów entomologów? W pierwszej kolejności musimy liczyć wiersze (ILE.WIERSZY($A$2:A2)), a następnie wyciągać z nich resztę z dzielenia przez 20 (funkcja MOD), bo tyle w przykładzie mamy numerów.

Musimy tylko dokonać drobnych korekt, żeby numery powtarzały się od 1 do 20.

=MOD(ILE.WIERSZY($A$2:A2)-1;$H$1)+1

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-07

Teraz możemy przejść do następnej kolumny, musimy dla każdej serii numerów wstawić miesiąc z kolejnej kolumny, czyli dla pierwszej serii numerów mamy wstawić miesiąc z pierwszej kolumny, dla następnej z drugiej itd.

Będzie nam do tego przydatna funkcja INDEKS, która będzie wskazywała na nagłówki z miesiącami. Będziemy wyciągać zawsze informację z pierwszego wiersza, więc jedynym problemem będzie wybranie odpowiedniej kolumny.

Na szczęście funkcja INDEKS nie patrzy na część dziesiętną liczby, dlatego wystarczy, że odrobinę zmodyfikujemy formułę z numerów entomologów – zamiast wyciągać resztę z dzielenia, będziemy dzielić przez ilość entomologów.

=INDEKS(Dane!$B$3:$AK$3;1;(ILE.WIERSZY($C$1:C1)-1)/$H$1+1)

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-08

W analogiczny sposób wybieramy typy owadów. Tylko patrzymy na wiersz niżej.

=INDEKS(Dane!$B$4:$AK$4;1;(ILE.WIERSZY($C$1:C1)-1)/$H$1+1)

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-09

Uff. Została nam ostatnia formuła. Tym razem musimy pobrać dane z tabeli, czyli w naszym przykładzie zmyśloną ilość owadów, które złapał entomolog, w danym miesiącu, danego typu.

Znów pomoże nam funkcja INDEKS, ale będziemy musieli wybierać różne numery wiersza, ale to już mamy w pierwszej kolumnie (numery entomologów), a numer kolumny wybieramy tak jak wcześniej, czyli nasza formuła przybierze postać:

=INDEKS(Dane!$B$5:$AK$24;A2;(ILE.WIERSZY($C$1:C1)-1)/$H$1+1)

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-10

Nareszcie konie pracy 😀 Uważam, że to jest skomplikowany proces i jeśli tylko możesz skorzystać z dodatku Power Query zobacz poradę 280 gdzie jest opisany proces konwersji danych za pomocą właśnie Power Query.

Pozdrawiam
Adam Kopeć
Miłośnik Excela