Excel - c.d.

Podstawowe pojęcia Excela - streszczenie.
(patrz też poprzednia lekcja)

• arkusz kalkulacyjny to tabela komórek
• każda komórka ma adres (np: A1, B12, AC1001)
• zakresy komórek (=prostokątne obszary) także mają adresy (np: A1:B10, B10:D20)
• do komórki można wpisać:
  • dane:
    • liczby (np: 123, 30/12/2003 - daty są liczbami !)
    • napisy (np: Ala ma kota, '123)
  • formuły:
    • w formułach odwołujemy się do komórek źródłowych poprzez ich adresy
      (np: =A1+A2+123)
    • formuły mogą zawierać funkcje niekiedy odwołujące się do zakresów komórek
      (np: =A1+A2+123+SUMA(B1:B10))
    • wyróżniamy formuły:
      • arytmetyczne (np: = A1 + A2 * A3)
      • tekstowe (np: = A1 & A2 & "qwe"; symbol "&" oznacza konkatenacje czyli łączenie napisów)
    • wyróżniamy adresy:
      • względne (np: A1, A1:B10),
      • bezwzględne (np: $A$1, $A$1:$B$10)
      • mieszane (np: A$1, $A1)
• komórki można formatować
  • ustalać sposób wyświetlania liczb, dat; wyrównywanie, obramowanie itp
• komórki można kopiować i przemieszczać (za pośrednictwem schowka)
  • podczas kopiowania / przemieszczania komórek z formułami
    adresy względne mogą ulegać zmianom (patrz tutaj)

...................................................................
 

Wykresy w Excelu.

Można tworzyć bardzo wyrafinowane wykresy czerpiące dane z arkusza ...

Poniższy wykres czerpie dane z dwóch kolumn,
    pierwsza zawiera etykiety,
    druga zawiera serię danych obrazowaną przy pomocy "słupków";

Tego typu proste wykresy tworzy się prawie automatycznie; wystarczy:

1. przygotować dane (kolumnę etykiet i kolumnę lub kolumny zawierające serie danych)
2. zaznaczyć przygotowane dane
3. nacisnąć guzik ; włączy się kreator wykresów ...
4. ... naciskać guzik "Dalej", "Dalej", ..., aż do szczęśliwego "Zakończ"

Jeśli na jednym wykresie chcemy zobaczyć kilka serii danych
to wystarczy przygotować dodatkowe kolumny z danymi:

Zadanie 51

Zadanie 52

Już zgromadzono dane za miesiąc styczeń ...
Następnie będą gromadzone dana za miesiąc luty ...
Często się zdarza że w lutym przychodzi ten sam pacjent który już odwiedził przychodnie w styczniu.

Nasze zadania: przygotować formularz na miesiąc luty, który automatycznie będzie uzupełniał nr PESEL zaraz po wpisaniu nazwiska (o ile to jest możliwe).

Wskazówka: użyj funkcji WYSZUKAJ.PIONOWO(); jej dokładny opis znajdziesz w pomocy; NIE należy zakładać że dane w tablicy są posortowane !.

Przykład użycia funkcji WYSZUKAJ.PIONOWO(); takie formuły:

dadzą taki efekt:

Uwaga: oczywiście przy wprowadzaniu formuł należy się posiłkować kopiowaniem komórek z formułami (korzystać z efektu zmian adresów względnych).

Zadanie 53 (trudne)

• imię + nazwisko,
• nr indeksu
• liczbę punktów (uzyskanych w kolokwium)

Maksymalna liczba punktów jest przechowywana w B1 (w A1 jest stosowna etykieta "maksymalna liczba pkt =").
Próg ocen pozytywnych (w procentach) jest przechowywany w B2; uczniowie którzy otrzymali więcej niż B2 % wszystkich punktów dostaną ocenę pozytywna; oceny pozytywne czyli (3.0; 3,5; 4.0; 4,5; 5,0) mają przydzielone zakresy równej długości, np jeśli próg wynosi 30% to oceny przydzielamy następująco:

3	30,00%
3,5	44,00%
4	58,00%
4,5	72,00%
5	86,00%

Zakładamy że jest możliwość dopisywania nowych studentów i nie powinno to mieć negatywnego wpływu na obliczenia w arkuszu ! (jednak nie stosujemy tutaj dynamicznego tworzenia adresów jak w zadaniu 28).

Naszym zadaniem jest:

1. obliczyć zakresy ocen; podajemy jedynie maksymalną liczbę pkt oraz próg ocen pozytywnych z których te zakresy wynikają (dzięki temu można będzie łatwo eksperymentować "jak by się zmienił rozkład ocen gdybyśmy zmienili próg ...")
2. automatycznie przydzielić studentom oceny (oceny zależą od uzyskanej punktacji, maksymalnej liczby pkt, oraz od progu ocen pozytywnych)
3. obliczyć rozkład ocen oraz zobrazować go przy pomocy wykresu.
4. zaznaczyć studentów z oceną 2.0 strzałką "<==" (żeby można ich było łatwo znaleźć)

Wskazówki do zadania 53:
Do przydzielania ocen użyj funkcji WYSZUKAJ.PIONOWO().

Do obliczania częstości występowania ocen użyj funkcji CZĘSTOŚĆ(); jest to szczególna funkcja zwracająca tablicę (dlatego być może łatwiej będzie wykonać to zadanie przy pomocy bardziej elementarnych środków: można po prostu policzyć ile jest ocen 2.0, 3.0, 3.5, ... używając funkcji JEŻELI() i SUMA()).

Do wskazywania studentów z oceną 2.0 pomocna będzie funkcja JEŻELI().

Oczywiście staraj się korzystać z dobrodziejstwa jakim jest mechanizm kopiowania formuł (i zmiany adresów względnych)

(Dokładne opisy funkcji znajdziesz w Pomocy !)

Zadanie 54

Wskazówki:
Do automatycznego wystawiania ocen użyj funkcji WYSZUKAJ.PIONOWO().
Np w komórce D4 powinna być formuła:

=WYSZUKAJ.PIONOWO(C4;$G$4:$H$9;2);

funkcja ta znajduje element C4 w tabeli $G$4:$H$9 i zwraca odpowiadający mu element w kolumnie nr 2 tabeli $G$4:$H$9.

Do obliczania "częstości występowania ocen" użyj funkcji CZĘSTOŚĆ( tablica_dane; tablica_przedziały).
Jest to specjalna funkcja zwracająca tablicę (a nie pojedynczą wartość); powinna ona być użyta następująco:

=CZĘSTOŚĆ(D4:D29;G12:G17);
        ''' zobaczymy na poniższym obrazku czym są te zakresy !!

a wprowadzić ją do komórek arkusza trzeba w specjalny sposób: zaznaczyć zakres H12:H17, potem wpisać formułę i nacisnąć: Ctrl + Shift + Enter.

W kolumnie E należy wprowadzić formuły wpisujące "<==" dla ocen niedostatecznych. Wystarczy użyć funkcji JEŻELI() w następujący sposób:

=JEŻELI(D4=2;"<==";"")

Na końcu należy utworzyć wykres kołowy oparty na tabeli G12:H17; zadbać aby serie danych były brane z kolumn (a nie z wierszy), oraz żeby pierwsza kolumna była traktowana jako etykiety; zwiększyć rozmiar czcionki legendy !.

Przy okazji: jak "obramowywać" komórki arkusza ? Odp: pasek narzędzi "Obramowania" i pisaczek.


 

Zadanie 55 (łatwe)

Zadanie 56

Zadanie 57

Powinno to wyglądać jak na poniższym rysunku; wykorzystaj mechanizm zamiany adresu względnego podczas kopiowania komórek z formułami (w rzeczywistości musisz użyć adresów mieszanych, np A$1 czy $A1).