Przepis na

Minidziennik ucznia w LibreOffice Calc

• Autor: Mirosław Zalewski
• 8 stycznia 2013
• Możliwość komentowania Minidziennik ucznia w LibreOffice Calc została wyłączona

Zbliża się przełom stycznia i lutego, a wraz z nim czas podsumowań semestralnych w edukacji. Artykuł wychodzi naprzeciw oczekiwaniom belfrów i opisuje, jak przy pomocy LibreOffice Calc utworzyć dzienniczek ucznia. Porusza przy tym zagadnienia praktycznego wykorzystania formatowania warunkowego oraz funkcji WYSZUKAJ.PIONOWO.

Chociaż sam nie prowadzę zajęć, jestem przekonany, że taki minidziennik musi spełniać kilka funkcji.

• Powiązanie wszystkich danych z nazwiskiem ucznia. Informacja, że dwóm osobom należy się ocena bardzo dobra jest bezwartościowa bez wiadomości, komu dokładnie.
• Możliwość szybkiej identyfikacji osób, które nie spełniają warunków formalnych zaliczenia (zazwyczaj jest to frekwencja).
• Automatyczne wystawianie stopni na podstawie liczby uzyskanych punktów.

Przykład opisany w niniejszym artykule będzie dostosowany do potrzeb osób prowadzących zajęcia ze studentami. Nauczyciele pracujący na niższych szczeblach systemu edukacji z pewnością uznają arkusz stanowiący efekt prac opisanych w tym artykule za niewystarczający. To ograniczenie ma dwie przyczyny.

Po pierwsze, coraz więcej szkół dysponuje tzw. „e-dziennikami”, które spełniają wszystkie z wymagań podanych powyżej, a także oferują wiele innych przydatnych funkcji. Nauczyciele mogący z nich skorzystać nie muszą tworzyć sobie pliku arkusza kalkulacyjnego.

Po drugie, oczekiwania wykładowców akademickich i nauczycieli z pozostałych uczelni różnią się. Rozbieżności te mają jednak charakter ilościowy, a nie jakościowy. W tym artykule staram się pokazać pewne ogólne mechanizmy i sposób działania narzędzi, które można wykorzystać także w bardziej skomplikowanych sytuacjach. Opisany poniżej plik, po rozszerzeniu o kilka dodatkowych funkcji, odpowiadałby także oczekiwaniom pracowników gimnazjów czy liceów.

Gdy już wyjaśniliśmy wszystkie założenia, pora przejść do tego, na co wszyscy czekają.

Szkielet arkusza

W pierwszej kolejności musimy utworzyć schemat arkusza, na którym później będziemy pracować.

Przede wszystkim, potrzebne nam będą nagłówki kolumn. To one wyznaczać będą charakter danych, jakie musimy wprowadzić. Powinniśmy utworzyć dokładnie tyle — i tylko tyle — kolumn, ile jest nam niezbędne, aby wystawić stopień uczniowi. Na studiach zazwyczaj podstawą do uzyskania wpisu w indeksie jest obecność na zajęciach oraz zaliczenie kolokwium. Często osoby szczególnie aktywne podczas spotkań nagradza się kilkoma dodatkowymi punktami. W związku z tym mój arkusz będzie zawierał następujące kolumny: Imię i nazwisko, Numer indeksu (są to swoiste identyfikatory dla całych wierszy), po jednej kolumnie na każde zajęcia, które odbyły się w semestrze, Liczba punktów z kolokwium, Aktywność, Ocena końcowa.

Następnie kolumny te należy wypełnić danymi. Moja sytuacja jest o tyle trudna, że muszę je wymyślić, jednak żaden nauczyciel nie będzie miał takiego problemu. Obecność ucznia na zajęciach oznaczyłem za pomocą jedynki, zaś nieobecność — pustej komórki.

Przykładowy arkusz, na którym będę pracował, przedstawia ilustracja poniżej.

Warunek formalny zaliczenia — frekwencja

Naszą właściwą pracę możemy rozpocząć od sprawdzenia, którzy studenci nie odwiedzali murów uczelni wystarczająco skrupulatnie. W tej kwestii często działają dwie zasady, i nie inaczej będzie w moim przykładzie. Raz — dopuszczalne są dwie nieobecności, a wszystko powyżej należy usprawiedliwić i/lub odrobić. Dwa — obecność na mniej niż połowie zajęć skutkuje nieklasyfikowaniem i brakiem wpisu do indeksu. Potrzebuję więc informacji, którzy z uczniów nie przestrzegali któregoś z tych zobowiązań. Wykorzystam do tego formatowanie warunkowe.

Zanim jednak zacznę, będę potrzebował dwóch nowych stylów komórek: nieklasyfikowany, dla studentów nieklasyfikowanych (ich nazwiska oznaczę za pomocą koloru czerwonego) oraz nieobecny, dla studentów którzy mają ponad dwie nieobecności (ich nazwiska oznaczę za pomocą koloru pomarańczowego).

Style te będę chciał zaaplikować na komórki zawierające imiona i nazwiska oraz numery indeksów. Zaznaczam więc wszystkie z nich (jednak bez nagłówków kolumn), a następnie z menu wybieram Format → Formatowanie warunkowe → Formatowanie warunkowe....

W sytuacji, gdy do jednej komórki przypisanych jest kilka reguł formatowania warunkowego, zaaplikowany zostanie styl określony w pierwszym spełnionym warunku. Ma to duże znaczenie w opisywanym przykładzie, ponieważ studenci nieklasyfikowani stanowią podzbiór studentów z nadmierną liczbą nieobecności. Innymi słowy, wszyscy studenci nieklasyfikowani mają nadmierną liczbę nieobecności, ale nie wszyscy studenci z nadmierną liczbą nieobecności są nieklasyfikowani.

Wynika z tego, że w pierwszej kolejności muszę utworzyć warunek wyłapujący studentów nieklasyfikowanych. Wybieram więc z listy rozwijanej Formuła jest, zaś w polu formuły wpisuję:

(LICZBA.KOLUMN($C2:$P2)-SUMA($C2:$P2))>(LICZBA.KOLUMN($C2:$P2)/2)

Formuła ta sprawdza, czy liczba nieobecności jest większa niż liczba reprezentująca połowę wszystkich zajęć w semestrze. Jeżeli tak, zaaplikowany zostanie styl nieklasyfikowany.

Wpierw (po lewej stronie formuły) obliczana jest liczba nieobecności. Jest to po prostu różnica liczby kolumn oznaczających kolejne zajęcia oraz sumy wartości w tych kolumnach. Ponieważ do oznaczania obecności użyłem liczby jeden, suma komórek w tym zakresie równa jest liczbie zajęć, na których uczeń był obecny. Gdybym na oznaczenie obecności użył innego symbolu, np. „x”, wtedy musiałbym skorzystać z funkcji LICZ.JEŻELI. Po prawej z kolei obliczana jest liczba reprezentująca połowę wszystkich zajęć w semestrze (liczba kolumn podzielona przez dwa). Na samym końcu sprawdzane jest, czy pierwsza liczba jest większa od drugiej.

Potrzebny jest jeszcze drugi warunek, za pomocą którego oznaczymy tych studentów klasyfikowanych, którzy opuścili zajęcia więcej niż dwa razy. Skonstruowanie odpowiedniej formuły jest prostsze, ponieważ z poprzedniego warunku wiemy już co należy zrobić, aby uzyskać liczbę nieobecności. Uzyskany wynik musimy odnieść do liczby dwa.

(LICZBA.KOLUMN($C2:$P2)-SUMA($C2:$P2))>2

Obliczanie ocen końcowych

Jak wspomniałem, drugim warunkiem uzyskania pozytywnej oceny przez studentów z mojego arkusza jest zaliczenie kolokwium. Ocena ze sprawdzianu jest jednocześnie oceną semestralną, przy czym ewentualne braki w punktach z egzaminu można nadrobić aktywnością na zajęciach. Wypełniłem już kolumny z liczbami punktów, teraz pozostało jedynie przeliczenie ich na oceny.

W tym celu posłużymy się funkcją WYSZUKAJ.PIONOWO. Wyszukuje ona wartość (podaną jako pierwszy argument) w pierwszej od lewej kolumnie zakresu komórek podanego jako drugi argument. Gdy ją znajdzie, odczytuje z jej wiersza komórkę w kolumnie o numerze podanym jako trzeci argument (gdzie kolumna najbardziej z lewej ma numer jeden).

Funkcja WYSZUKAJ.PIONOWO przyjmuje jeszcze czwarty, opcjonalny argument. Jeżeli będzie to zero, wtedy funkcja będzie poszukiwała wartości dokładnie odpowiadającej pierwszemu argumentowi (i zwróci błąd, jeżeli jej nie znajdzie). Gdy jednak ostatni argument zostanie pominięty lub będzie wynosił 1, wtedy funkcja uzna pierwszą kolumnę zakresu za posortowaną rosnąco i zwróci komórkę z ostatniego wiersza w którym wartość jest mniejsza lub równa poszukiwanej. I właśnie z tej właściwości funkcji skorzystamy.

Zanim jednak zaczniemy, musimy jeszcze odpowiedzieć sobie na trzy pytania.

1. Ile punktów można było maksymalnie zdobyć? W moim przykładzie — 40.
2. Jaka jest skala ocen? Ja zastosowałem sześciostopniową skalę wykorzystywaną na moim uniwersytecie (niedostateczny, dostateczny, dostateczny plus, dobry, dobry plus, bardzo dobry).
3. Jakie są progi dla poszczególnych ocen? W swoim przykładzie wykorzystuję następujące progi procentowe: dostateczny — 50%; dostateczny plus — 66%; dobry — 75%; dobry plus — 90%; bardzo dobry — 97%.

Kiedy mamy już wszystkie te informacje, możemy je wprowadzić w naszym arkuszu. Pamiętamy przy tym o sposobie działania funkcji WYSZUKAJ.PIONOWO, z której będziemy korzystać. W jednej komórce wpisujemy minimalną liczbę punktów wymaganych do uzyskania danej oceny (możemy pomyśleć o progach liczbowych jako o przedziałach lewostronnie domkniętych), zaś na prawo od niej — stopień (może to być zarówno liczba, jak i tekst). W kolejnym wierszu robimy to samo dla następnego, wyższego stopnia.

Mój przykład jest o tyle bardziej skomplikowany, że progi wyznaczyłem procentowo. Wykorzystam więc trzy, a nie dwie, kolumny. W pierwszej z nich zapisuję procenty stanowiące dolne granice przedziałów. Na prawo od nich wprowadzam formułę obliczającą, jaką liczbę całkowitą stanowi tenże procent z maksymalnej liczby punktów. Wreszcie w ostatniej kolumnie wpisuję stopień. Takie rozwiązanie umożliwia mi bardzo proste wprowadzanie zmian w przyszłości — wystarczy bowiem zmienić komórki zawierające liczbę możliwych do zdobycia punktów lub progi ocen, aby wszystko samo się przeliczyło.

Dopiero teraz, kiedy przygotowaliśmy zakres z ocenami, możemy skorzystać z funkcji WYSZUKAJ.PIONOWO. Umieszczamy więc kursor w ostatniej kolumnie dziennika i wprowadzamy formułę:

=WYSZUKAJ.PIONOWO($Q2+$R2;$R$25:$S$30;2)

Formuła ta zsumuje liczby punktów z kolokwium i aktywności. Będzie to wartość wyszukiwana w zakresie, podanym jako drugi argument. Na końcu formuła odczyta komórkę znajdującą się w drugiej kolumnie wiersza ze znalezioną wartością. Ponieważ pominęliśmy ostatni argument funkcji, będzie ona usatysfakcjonowana wartościami zarówno równymi, jak i mniejszymi od wyszukiwanej.

Zwróćmy przy tym uwagę, że adres zakresu w drugim argumencie funkcji ma charakter absolutny. Jest to konieczne, aby przeszukiwany adres nie „wędrował” w dół arkusza wraz z kolejnymi wierszami w naszym dzienniczku.

Ostatnim krokiem jest skopiowanie komórki z wprowadzoną formułą i wklejenie jej w pozostałych komórkach w tej kolumnie.

Przygotowany arkusz zgodny jest ze wszystkimi założeniami przedstawionymi na początku wpisu. Jedynie w ramach wykończenia możemy stworzyć jeszcze jedną regułę formatowania warunkowego, za pomocą której oznaczymy tych studentów, którzy nie zaliczyli kolokwium. Zaznaczamy więc komórki w kolumnie Ocena końcowa i w oknie formatowania warunkowego tworzymy warunek aplikujący styl nieklasyfikowany tym komórkom, których wartość jest równa "ndst" (tekst musimy otoczyć podwójnymi cudzysłowami, inaczej warunek nie zostanie spełniony). Uzyskany efekt przedstawia ilustracja poniżej.