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