Winietka trochę inaczej (3/4) – Funkcje tekstowe w Excelu

W serii wpisów Winietka trochę inaczej… opowiadałem jak szybko i dość łatwo przygotować sobie wizytówki dla swoich uczniów. W Winietka trochę inaczej (1/4) – Pamiętaj imiona swoich uczniów pisałem o samym pomyśle i jak pomaga on przede wszystkim w budowaniu relacji z uczniami i sprawnym uczeniu się ich imion, a w Winietka trochę inaczej (2/4) – Korespondencja seryjna w Wordzie pokazywałem jak można łatwo przygotować takie kartki właśnie za pomocą korespondencji seryjnej starego dobrego Worda. Zapowiadałem też wpis, w którym przedstawię sposób przetwarzania takiej listy uczniów ściągniętej z dziennika w Excelu (lista, która posługuję się w tym poście zawiera miks najbardziej popularnych imion i nazwisk w Polsce – żadna osoba na niej nie jest więc prawdziwa, choć prawdopodobieństwo, że ktoś rzeczywiście się tak nazywa jest więcej niż znaczące).

Oczywiście całą zabawę można zrobić szybciej i łatwiej z Chatem GPT ale po pierwsze natrafimy wtedy na problem ochrony danych osobowych, a po drugie znajomość paru podstawowych narzędzi Excela potrafi być naprawdę przydatna w wielu różnych okolicznościach i robota nauczyciela nie jest tutaj żadnym wyjątkiem.

W ostatnim wpisie tej serii podałem gotowe formuły:

=JEŻELI(CZY.BŁ(ZNAJDŹ(” „;A2;ZNAJDŹ(” „;A2)+1));PRAWY(A1;DŁ(A2)-ZNAJDŹ(” „;A2));FRAGMENT.TEKSTU(A2;ZNAJDŹ(” „;A2)+1;ZNAJDŹ(” „;A2;ZNAJDŹ(” „;A2)+1)-ZNAJDŹ(” „;A2)-1))

i

=LEWY(A2;ZNAJDŹ(” „;A2)-1)

Sprawdźmy więc jak można do nich dojść krok po kroku.

Naszą listę uczniów wklejamy do komórki A2 excelowego arkusza (komórkę A1 pozostawiając na nagłówek, który w przykładowym pliku nazwałem Imię i nazwisko z dziennika). Oczywiście dostajemy listę poszczególnych wierszy, z których do dalszej pracy wybieram dwa przykładowe (komórki zaznaczone na zielono):

Zwróćmy uwagę, że (tak jak to ma miejsce w rzeczywistości) mamy osobę o jednym imieniu i osobę o dwóch imionach. Chcemy rozdzielić na osobne komórki pierwsze imię i nazwisko każdej z nich (i te podwójne imiona stanowią największą trudność). Wykorzystamy do tego celu proste funkcje tekstowe Excela (a następnie kilka funkcji logicznych – ale o tym przeczytasz już w kolejnym poście).

Funkcje, których potrzebujemy to:

ZNAJDŹ – funkcja zwracająca w postaci liczby położenie zadanego znaku w jakimś ciągu znaków

LEWY – funkcja zwracająca ciąg znaków o określonej długości z lewej strony jakiegoś ciągu znaków

DŁ – funkcja zwracająca długość zadanego ciągu znaków

PRAWY – funkcja zwracająca ciąg znaków o określonej długości z prawej strony jakiegoś ciągu znaków

FRAGMENT.TEKSTU – funkcja zwracająca fragment tekstu o danej długości i pozycji początkowej z jakiegoś ciągu znaków

Za pomocą tych funkcji rozdzielimy imię i nazwisko.

Najłatwiejsze do uzyskania będzie samo nazwisko dla każdego ucznia. W tym celu najpierw ustalimy położenie pierwszej spacji, a później odetniemy od lewej strony ciąg znaków o takiej właśnie długości (spacja minus jeden). W komórce B2 wpisujemy:

=ZNAJDŹ(” „;A2)

Ta funkcja działa na dwóch zmiennych (umieszczonych w nawiasach i rozdzielonych średnikiem – pierwszą jest szukany ciąg znaków w cudzysłowie, czyli u nas spacja, drugą adres komórki z tekstem w którym tej spacji szukamy). Formułę możemy przekopiować dla wszystkich rekordów z listy przeciągając ją lub dwukrotnie klikając na czarny kwadracik w prawym dolnym rogu zaznaczonej komórki.

Powyższy screen pokazuje formuły, a efekt w pliku będzie wyglądał tak:

Pomiędzy tymi dwoma trybami wyświetlania możemy się przełączać kombinacją klawiszy ctrl + ~.

Gdy znamy już położenie spacji możemy odciąć lewą część łańcucha znaków wpisując do komórki C2 formułę

=LEWY(A2;B2-1)

Tutaj również mamy dwa argumenty – pierwszy wskazuje na tekst z którego chcemy wyciąć fragment, drugi określa jego długość (położenie spacji minus jeden). Dlaczego minus jeden? Nie odejmując tej jedynki dostalibyśmy nazwisko ze spacją na końcu, a tego nie chcemy.

Wygląda to tak –

formuły:

i efekt:

Teraz zaczyna się zabawa ponieważ musimy jakoś oddzielić naszych uczniów o dwóch imionach od uczniów z jednym. Tym co w jakiś sposób różnicuje te ciągi znaków jest obecność (lub nie) drugiej spacji. Poszukajmy jej.

W komórce D2 wpisujemy znaną nam już formułę

=ZNAJDŹ(” „;A2;B2+1)

Tym razem działa ona na trzech zmiennych. Pierwszą z nich znowu jest szukany ciąg znaków, czyli spacja, drugą przeszukiwany tekst (a właściwie adres komórki, w której on się znajduje), a trzecią (używaną opcjonalnie) jest położenie pierwszego znaku od którego zaczynamy szukać. Za pierwszym razem szukaliśmy od początku tekstu, więc ta zmienna nie była potrzebna (domyślnie było to 1), teraz szukamy od pierwszej znalezionej spacji, więc wpisujemy jej położenie, które mamy już w komórce B2. (Funkcje te można zagnieździć. Jak to zrobić napiszę w kolejnym poście poświęconym temu tematowi już wkrótce).

Formuły wyglądają następująco:

a efekt ich obliczenia tak (zwróćmy uwagę, że w komórce D3 dostajemy błąd wartości #ARG! – w łańcuchu znaków Zawisza Filip mamy tylko jedną spację):

W kolumnie E wpisałem funkcję logiczną podobnie jak w kolumnie I. Aby nie rozdmuchać zbytnio obszerności tego wpisu zajmiemy się nimi w kolejnym poście. Tak samo zrobimy z kolumnami K i J, w których łączymy teksty i zagnieżdżamy formuły – o tym następnym razem, a teraz skupmy się jeszcze na kolumnach F, G i H.

Kolejną rzeczą, którą chcemy zrobić jest więc wycięcie pierwszego imienia.

W sytuacji gdy uczeń ma tylko jedno imię sprawa jest prosta. Najpierw ustalamy całkowitą długość łańcucha znaków zawierającego nazwisko i imię lub imiona.

W komórce F2 wpisujemy

=DŁ(A2)

Tutaj mamy tylko jeden argument, który adresuje komórkę zawierającą interesujący nas ciąg znaków.

Dostajemy efekt w postaci liczby dla każdego wiersza:

Teraz w przypadku ucznia o jednym imieniu wystarczy w komórce G2 użyć formuły

=PRAWY(A2;F2-B2)

Tutaj jako pierwszą zmienną znów wpisujemy adres komórki, w której znajduje się tekst, a jako drugą różnicę długości tekstu i położenia pierwszej spacji.

Ta formuła nie będzie oczywiście działała prawidłowo dla ucznia, który ma dwoje imion (tak jak w przypadku ciągu znaków Hanna Maria w komórce G2). W przypadku ucznia z jednym imieniem (komórka G3 – Filip) wszystko jest ok i to jest nasz końcowy rezultat.

Jak zatem uzyskać tylko pierwsze imię w sytuacji gdy imiona są dwa? Tutaj posłużymy się formułą

=FRAGMENT.TEKSTU(A2;B2+1;F2-D2)

którą wpiszemy do komórki H2. Operuje ona na trzech argumentach. W pierwszym wpisujemy adres komórki z tekstem, w drugim wskazujemy miejsce, od którego chcemy wycinać tekst (w naszym przypadku to pierwszy znak po pierwszej spacji – czyli adres B2 z położeniem pierwszej spacji plus jeden). Trzeci argument to długość łańcucha znaków (długość całego tekstu obliczona w komórce F2 minus położenie drugiej spacji obliczone w komórce D2).

Otrzymujemy taki efekt, że dla ucznia o dwóch imionach odseparowane jest jedno – pierwsze (u nas Hanna w komórce H2), natomiast gdy uczeń ma jedno imię, ta formułą zwraca błąd (#ARG! w komórce H3) – bo w wyjściowym ciągu znaków nie ma drugiej spacji, do położenia której odwołuje się ta formuła.

Mamy więc dla każdego ucznia odseparowane jego nazwisko (w kolumnie C) i pierwsze imię (dla uczniów z jednym imieniem w kolumnie G, a dla uczniów z dwoma imionami w kolumnie H).

Teraz musimy rozpoznać kiedy jest jedno, a kiedy dwoje imion i wziąć wartość z odpowiedniej komórki. Tutaj kończy się rola funkcji tekstowych, a pierwsze skrzypce zaczynają grać funkcje logiczne. O tym jednak (i o tym jak całość zrobić „jednym strzałem zagnieżdżając wszystkie formuły w jednej) przeczytasz w poście Winietka trochę inaczej (4/4) – Funkcje logiczne w Excelu.