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

Prawie rok temu rozpocząłem serię wpisów o winietkach, których używam do szybkiego uczenia się imion swoich uczniów. Moment roku szkolnego, w którym publikuję ostatni post cyklu, a także pierwszy, z ubiegłorocznego października nie jest przypadkowy. W tym czasie właśnie jako nauczyciele zaczynamy się uczyć imion swoich nowych uczniów i przypominać dotychczasowych. Ma to tym większą wagę z im większą liczbą klas pracujemy, czego w tym „sezonie” jestem najlepszym przykładem – pierwszy raz w życiu nie mam ani jednej godziny matematyki, za to na fizyce w różnych szkołach umilam czas 720 uczniom! Nauczenie się wszystkich imion w miesiąc graniczy z niemożliwością więc mój winietkowy patent ma dla mnie w tym roku szczególne znaczenie.

Jeżeli więc zastanawiasz się jak szybko poznać swoich uczniów i nie kaleczyć lekcji tekstami, że „jak właśnie kolega powiedział”, ta seria jest dla Ciebie. Zachęcam Cię do przeczytania najpierw wcześniejszych wpisów:

Winietka trochę inaczej (1/4) – Pamiętaj imiona swoich uczniów

Winietka trochę inaczej (2/4) – Korespondencja seryjna w Wordzie

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

Ten wpis, jako ostatni, dotyczy użytych w opisywanych plikach Excela funkcji logicznych i, już na samym końcu, zagnieżdżania formuł. Zaczynamy!

W poprzednim wpisie serii stworzyliśmy listę naszych uczniów z dziennika i używając funkcji tekstowych porozdzielaliśmy ich imiona i nazwiska. Otrzymaliśmy osobną kolumnę C z nazwiskiem, kolumnę G z pierwszym imieniem, gdy jest jedno i kolumnę H z pierwszym imieniem, wtedy gdy są dwa.

Działają one jednak tylko dla „swojego” przypadku, więc musi się zdarzać, że zwracają błędy lub wartości, które nie do końca nam odpowiadają (na przykład dwa imiona zamiast jednego). Aby pozbyć się tego problemu posłużymy się funkcjami logicznymi.

Będziemy używać funkcji:

CZY.BŁ – funkcja sprawdzająca czy wartość komórki jest jednym z popularnych w Excelu typów błędu (nas interesuje #ARG!),

JEŻELI – funkcja wykonująca test logiczny i zwracająca różne wartości w zależności od tego czy zadany warunek jest spełniony czy nie.

Zaczynamy od sprawdzenia czy dany uczeń ma jedno imię czy dwa. Można to zrobić na kilka sposobów ale najłatwiej będzie wykorzystać do tego wartości w kolumnie D wskazującej położenie drugiej spacji w ściągniętym z dziennika ciągu znaków. Gdy uczeń ma tylko jedno imię, drugiej spacji nie będzie i w komórce pojawi się błąd. Widać to na poniższym screenie:

Komórka D2 wskazuje położenie spacji na 14 miejscu, natomiast komórka D3 zawiera błąd #ARG! – w komórce E2 wpisujemy więc:

=CZY.BŁ(D2)

Ta funkcja ma tylko jeden argument i sprawdza czy komórka D2 zwraca błąd. Formułę oczywiście kopiujemy do wszystkich komórek, których potrzebujemy.

Jeżeli więc w interesującej nas komórce funkcja rozpoznaje błąd, jej wartością jest PRAWDA, jeżeli zaś nie – FAŁSZ. Można to zobaczyć poniżej:

Teraz w zależności od wartości tej funkcji (czyli w zależności od tego ile jest imion) możemy wybrać dane z odpowiedniej kolumny (G lub H).

W komórce I2 wpisujemy:

=JEŻELI(E2;G2;H2)

Składnia tej funkcji jest bardzo ciekawa. Mamy tu bowiem trzy argumenty: pierwszy to sprawdzenie warunku logicznego (w naszym przypadku po prostu wskazujemy komórkę E2 bo warunek logiczny jest już sprawdzony właśnie tam), drugi – to wartość, którą ma zwrócić komórka gdy warunek jest prawdziwy (wskazujemy komórkę, która poprawnie wyświetla imię gdy jest jedno – G2), a trzeci – gdy jest to fałsz (czyli poprawne pierwsze imię gdy są dwa – komórka H2).

W efekcie dostaniemy kolumnę z prawidłowymi imionami niezależnie od ich ilości (kolumna I).

Teraz wystarczy połączyć te wartości za pomocą funkcji tekstowych (kolumna K), o których już mówiliśmy w poprzednim poście.

A czy trzeba się aż tak rozwlekać na wszystkie te kolumny i robić tak rozbudowaną bazę? Czy nie dałoby się tego zrobić „jednym strzałem”?

Oczywiście, że można to zrobić i do tego posłuży nam zagnieżdżanie formuł. Wystarczy zauważyć, że wartość jednej funkcji może być argumentem dla kolejnej (dokładnie tak jak przy składaniu funkcji jak się ktoś tam chce grzebać trochę w matematyce).

Jako pierwszego argumentu funkcji =JEŻELI(E2;G2;H2) z naszego przykładu zamiast adresu E2 możemy użyć formuły, która pod tym adresem się kryje, czyli CZY.BŁ(D2). W komórce D2 mamy jednak wartość funkcji ZNAJDŹ(” „;A2;B2+1), którą też możemy wpisać zamiast tego właśnie adresu. Na tym jednak nie koniec, bo z kolei ta funkcja używa adresów A2 (który jest adresem wyjściowym i zawiera oryginalny ciąg znaków ściągnięty z dziennika) i B2. Komórka B2 przechowuje natomiast wartość funkcji ZNAJDŹ(” „;A2). Dopiero gdy w miejsce wszystkich tych adresów wpiszemy (w praktyce przekopiujemy) znajdujące się tam formuły odnoszące się do komórki A2, dojdziemy do samego końca (a w zasadzie początku) zagnieżdżania. Tak samo musimy postąpić również z pozostałymi dwoma argumentami, czyli G2 i H2. Gdy wpiszemy do komórki J2 taką pozagnieżdżaną formułę, czyli:

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

uzyskamy efekt identyczny jak w komórce I2,co widać na poniższym screenie.

Faktem jest, że wygląda ona strasznie ale nie musimy wpisywać jej ręcznie. Wystarczy przekopiować odpowiednie funkcje z poszczególnych komórek. Żeby sobie ułatwić to zadanie możemy użyć omawianego już w poprzednim wpisie skrótu Ctrl + ~ i zaznaczyć interesującą nas komórkę, a wszystkie potrzebne informacje natychmiast zostaną podświetlone. Oczywiście można utworzyć formułę załatwiającą wszystko od początku do końca i wyświetlającą na przykład pierwsze imię i nazwisko rozdzielone spacją tak jak w kolumnie L (jak na screenie).

Aby osiągnąć taki efekt w komórce L2 trzeba wpisać formułę:

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

Zwróć uwagę, że odnosi się ona tylko do komórki A2, czyli tej, w której trzymamy oryginalne dane. Kopiujemy ją na całą kolumnę i listę uczniów mamy gotową.

Co z nią robić dalej?

Teraz odpalamy starego dobrego Worda i tworzymy winietki z użyciem korespondencji seryjnej. A jak sobie z tym poradzić mówiliśmy już w poście Winietka trochę inaczej (2/4) – Korespondencja seryjna w Wordzie.

Życzę miłego, interesującego i skutecznego poznawania swoich uczniów! A żeby to poznawanie Ci dodatkowo umilić, poniżej zamieszczam plik, z którego korzystałem przy omawianiu wszystkich funkcji. Znajdziesz w nim wzorce formuł, których możesz użyć tworząc własną bazę uczniów. Wystarczy, że przekopiujesz je do odpowiednich komórek swojego skoroszytu. Powodzenia!