W dzisiejszym odcinku mówiłem o prostej aplikacji dziennika ocen w programie Excel. Chociaż koncepcja jest prosta, w dzienniku ocen znajduje się kilka formuł, które mogą Cię potknąć. Dziennik ocen wygląda następująco:
Nagłówki pionowe
Nagłówki w wierszu 2 są odwracane na bok za pomocą polecenia Format - Komórki - Wyrównanie. Na karcie Wyrównanie zmień orientację na 90 stopni.
Wiersz możliwych punktów
Ważne jest, aby w arkuszu kalkulacyjnym umieścić wiersz zawierający łączną liczbę punktów możliwych do uzyskania za każde zadanie. Nie wpisuj punktów możliwych do uzyskania za zadanie, dopóki nie wprowadzisz ocen za to zadanie. Na pierwszym rysunku powyżej możliwe punkty znajdują się w wierszu 17.
Suma formuł w kolumnie H.
Wprowadź funkcję = SUMA w H4, aby zsumować wyniki pierwszego ucznia. Skopiuj tę formułę w dół kolumny H dla każdego ucznia i dla możliwych linii punktów. Program Excel automatycznie zmienia odniesienia względne w formule, aby wskazywały następny wiersz itp.
Tricky: średnie wzory w kolumnie I
Wzór na I4 może wydawać się prosty. Możesz spróbować wejść =H4/H17, ale spowoduje to problem.
Po skopiowaniu tej formuły w dół o kilka wierszy przestaje działać. Tutaj w wierszu 6 formuła została zmieniona na =H6/H19. To prawda, że Excel zmienił H4 na H6, ale chcesz, aby Excel zawsze bezwzględnie wskazywał na H17 jako dzielnik w obliczeniach.
Wróć i edytuj formułę w I4. Możesz wybrać I4 i nacisnąć F2 lub po prostu kliknąć dwukrotnie I4.
Gdy punkt wstawiania znajduje się tuż za H17, naciśnij klawisz F4. Excel wstawia znaki dolara, aby zmienić H17 na 17 USD.
Znak dolara przed literą H mówi, że bez względu na kierunek kopiowania wzoru zawsze chcesz wskazać kolumnę H.
Znak dolara przed 17 mówi, że bez względu na kierunek kopiowania wzoru zawsze chcesz wskazać wiersz 17.
Nazywa się to odniesieniem bezwzględnym.
Po skopiowaniu tej formuły do wszystkich uczniów program Excel zawsze prawidłowo dzieli przez łączną liczbę punktów możliwych w wierszu 17.
Zamiana procentów na oceny literowe
Użyjesz funkcji WYSZUKAJ.PIONOWO, aby przekonwertować procenty na ocenę literową. Istnieją dwa sposoby korzystania z funkcji WYSZUKAJ.PIONOWO i 90% czasu na tej stronie używam wersji, w której Excel musi znaleźć dokładne dopasowanie w tabeli odnośników. Ta wersja używa jednak wersji WYSZUKAJ.PIONOWO, w której tabela zawiera zakresy danych.
W tej wersji tabela przeglądowa musi być sortowana w kolejności od najniższej do najwyższej. Jak widać poniżej, wymaga to odwrócenia skali ocen w szkole i odwrócenia jej do góry nogami. W poniższej tabeli wiersz 4 mówi, że każdy wynik powyżej 0 otrzyma F. Wiersz 5 przejmuje kontrolę i mówi, że każdy wynik powyżej 65% otrzyma D. W rzędzie 6 wszystko, co ma 69% lub więcej, jest D + . Podstawową ideą jest to, że Excel znajdzie liczbę równą lub niższą od wyniku ucznia.
- Aby utworzyć WYSZUKAJ.PIONOWO w J4, zacznij od
=VLOOKUP( - Pierwszym argumentem jest wynik ucznia w
I4. - Następnym argumentem jest zakres z twoją tabelą przeglądową. W tym przykładzie tabela jest w formacie
L4:M12. - Chcesz, aby formuła zawsze wskazywała ten sam zakres, więc naciśnij klawisz F4, aby zmienić go na
$L$4:$M$12. - Ostatnim argumentem jest numer kolumny z Twojej tabeli z oceną literową. W tym przykładzie jest to plik
2. - Formuła jest taka
=VLOOKUP(I4,$L$4:$M$12,2).
- Skopiuj wzór dla wszystkich uczniów.
Wersja, której użyłem w programie, jest mała - z kilkoma studentami i tylko kilkoma zadaniami. Możesz pobrać tę mniejszą wersję, aby zobaczyć, jak działają formuły. Kliknij prawym przyciskiem myszy plik CFHGrades.zip .
