Palindromy w Excelu

by Marcin

Palindromy w Excelu

Dzisiaj o palindromach, czyli wyrazach (zdaniach), które można czytać zarówno od początku, jak też od końca (wspak). Cała niezwykłość polega oczywiście na tym, że czytane od końca nie tracą one sensu – brzmią i wyglądają tak samo jak czytane od początku. Dla jasności, kilka przykładów palindromów:rotator, manam, oko w oko.

Dzisiejsze zadanie polega na przygotowaniu formuły zwracającej wartość PRAWDA, kiedy wskazany ciąg tekstowy jest palindromem. Dodatkowo, bazując na kolumnie zawierającej różne zdania i wyrazy (wśród nich palindromy) przygotujemy listę zawierającą tylko palindromy. Mój arkusz, przed rozpoczęciem pracy, wygląda mniej więcej tak jak na obrazku poniżej.

Palindromy w Excelu

Sprawdzając czy dany ciąg jest palindromem będę porównywał, po kolei, litery z początku oraz końca wyrazu. Pierwsza litera musi być równa ostatniej, druga przedostatniej etc. Jeżeli te warunki są spełnione, wyraz (zdanie) uznam za palindrom.

Poniżej pierwsza z przygotowanych przeze mnie formuł (formuła tablicowa).

=SUMA((LITERY.WIELKIE(FRAGMENT.TEKSTU(PODSTAW(B8;” „;””);WIERSZ(ADR.POŚR(„1:”&DŁ(PODSTAW(B8;” „;””))));1))=LITERY.WIELKIE(FRAGMENT.TEKSTU(PODSTAW(B8;” „;””);MODUŁ.LICZBY(WIERSZ(ADR.POŚR(„1:”&DŁ(PODSTAW(B8;” „;””))))-(DŁ(PODSTAW(B8;” „;””))+1));1)))*1)=DŁ(PODSTAW(B8;” „;””))

Jak to działa ?

Porównuję każdą literę z początku wyrazu (zdania) z odpowiednią (symetrycznie umieszczoną) literą z końca. Jeżeli litery są takie same otrzymuję wartość PRAWDA dla każdego porównania. Każdą z otrzymanych wartości logicznych mnożę następnie przez 1 (wartość logiczną zamieniam na liczbę, którą mogę użyć w działaniach matematycznych).Na koniec wszystko do siebie dodaję. Jeżeli suma będzie równa długości ciągu oznaczać to będzie, że odpowiedni warunek spełniony został dla wszystkich liter (każde porównanie zwróciło wartość PRAWDA) i wyraz (zdanie) jest palindromem. Chcąc uniezależnić wynik od wielkości liter skorzystałem z funkcji LITERY.WIELKIE. Funkcją PODSTAW usuwam wszystkie spacje z ciągu.

Niedoskonałości

Formuła zdaje się działać (w każdym razie dla tych palindromów, dla których ja testowałem), ale widać dwie niedoskonałości.

1.
Przede wszystkim formuła nie zadziała prawidłowo ze zdaniami zawierającymi kropki i przecinki. Widać to wyraźnie na rysunku poniżej. W przypadku pojedynczych wyrazów oraz zdań bez znaków przestankowych formuła zwraca poprawne wyniki. W pozostałych jednak przypadkach (zaznaczyłem je na czerwono) nie działa tak jak powinna.

Palindromy w Excelu

Problem naprawić można usuwając z każdego ciągu nie tylko spacje, ale także kropki i przecinki. Wszystko co trzeba zrobić to zamienić

PODSTAW(B8;” „;””)

na

PODSTAW(PODSTAW(PODSTAW(B8;” „;””);”,”;””);”.”;””)

Kiedy jednak próbuję wstawić tę formułę do mojej funkcji okazuje się, że Excel reaguje błędem. Spowodowany jest on tym (w każdym razie według mnie), że Excel (w wersji 2003 przynajmniej) akceptuje nie więcej niż 7 poziomów zagnieżdżonych funkcji. W moim przypadku, w dwóch miejscach, ta ilość zostałaby przekroczona.

Można oszukać nieco Excela i zamiast wstawiać funkcje bezpośrednio do formuły, zdefiniować najpierw nazwę i to właśnię tę nazwę użyć w formule sprawdzającej palindromy.

Zdefiniowałem więc nazwę „tekst” i jako „odwołuje się do” wpisałem formułę

=PODSTAW(PODSTAW(PODSTAW(B8;” „;””);”,”;””);”.”;””)

Palindromy w Excelu

2
Ponieważ formuła, porównując litery, „przesuwa” się od pierwszej litery do ostatniej oraz od ostatniej do pierwszej, połowa znaków ciągu porównywana jest ze sobą dwa razy. Zupełnie niepotrzebnie. Można temu zapobiec dokonując operacji porównania tylko dla połowy ciągu.

Poniżej poprawiona formuła tablicowa, korzystająca zarówno ze zdefiniowanej wcześniej nazwy, jak też uwzględniająca uwagę z punktu 2.

=SUMA((LITERY.WIELKIE(FRAGMENT.TEKSTU(tekst;WIERSZ(ADR.POŚR(„1:”&ZAOKR(DŁ(tekst)/2;0)));1))=
LITERY.WIELKIE(FRAGMENT.TEKSTU(tekst;MODUŁ.LICZBY(WIERSZ(ADR.POŚR(„1:”&ZAOKR(DŁ(tekst)/2;0)))
-(DŁ(tekst)+1));1)))*1)=ZAOKR(DŁ(tekst)/2;0)

Na rysunku poniżej możesz zobaczyć, że zwracane wyniki są teraz prawidłowe.

Palindromy w Excelu

Lista palindromów

Na koniec pozostaje jeszcze tylko, w osobnej kolumnie, wyświetlić aktualizująca się automatycznie listę palindromów. Korzystam w tym celu z kolumny pomocniczej.

Po zaznaczeniu zakresu F8:F18 (ilość wierszy równa ilości wierszy z ciągami, które analizuję) wpisałem następującą formułę tablicową:

=MIN.K(JEŻELI($D$8:$D$18;WIERSZ($D$8:$D$18);””);
WIERSZ(ADR.POŚR(„1:”&ILE.LICZB(JEŻELI($D$8:$D$18;WIERSZ($D$8:$D$18);””)))))

Dla każdego znalezionego palindromu zwraca ona numer wiersza, w którym palindrom się znajduje. Następnie do komórki E8 wpisałem formułę:

=JEŻELI(CZY.BŁĄD(ADR.POŚR(ADRES(F8;2)));””;ADR.POŚR(ADRES(F8;2)))

(możesz także skorzystać z prostszej formuły:

=JEŻELI(CZY.BŁĄD(INDEKS($B:$B;F8));””;INDEKS($B:$B;F8))

i skopiowałem w dół na pozostałe komórki.

W wyniku otrzymałem listę wszystkich palindromów znajdujących się na mojej, pierwotnej, liście wyrazów (zdań).

Palindromy w Excelu

Na koniec, jak zwykle, zachęcam do podzielenia się ze mną Waszymi pomysłami na rozwiązanie przedstawionego problemu.

Marcin

Download

{ 3 comments… read them below or add one }

armal84

Nie wiem czemu, ale łatwiej było mi to opanować w VBE. Poniżej moje wypociny 🙂

Sub oko3()
Dim i, j As Integer
Range(„B18”).Clear
Range(„B16”) = LCase(Application.WorksheetFunction.Substitute(Range(„B17″), ” „, „”))

l = Len(Range(„B16”))
l2 = l / 2
l3 = Round(l2, 0)
j = 1

Do
If Mid(Range(„B16”), j, 1) Mid(Range(„B16”), (l – j + 1), 1) Then
Range(„B18”) = „Nie jest palindromem”
End If
j = j + 1
Loop Until j = l3

If Range(„B18”) = „” Then
Range(„B18”) = „jest to palindrom”
End If
Range(„B16”).Clear

End Sub

Oczywiście działa tylko na jednej kolumnie, ale po niewielkich poprawkach można sprawić, aby działało na wybranym zakresie.
Pozdrawiam.

armal84

Jasna rzecz – chodziło o komórkę, a nie kolumnę 🙂

pd

kobyła ma mały bok 🙂

Leave a Comment

Previous post:

Next post: