Narzędzie Informatyki 250
zajęcia IV — 22 kwietnia 2006
arkusze kalkulacyjne
Arkusz kalkulacyjny to dokument składający się z komórek. W każdej komórce może znaleźć się liczba, napis lub formuła. Formuły to wzory pozwalające wyliczyć wartości na podstawie zawartości innych komórek.
Arkuszem kalkulacyjnym nazywa się też program do tworzenia i edycji dokumentów-arkuszy kalkulacyjnych. W pakiecie Microsoft Office arkusz kalkulacyjny nazywa się Excel, a w pakiecie OpenOffice — OpenCalc.
Kolumny arkusza oznaczane są literami A, B, C itd., zaś wiersze — kolejnymi liczbami. Pozwala to przypisać każdej komórce jednoznaczny adres, np. lewa górna komórka ma adres A1, piąta komórka w trzecim wierszu — E3. Niekiedy istnieje potrzeba odwołania się do całego bloku komórek, używamy wówczas następującej notacji: róg bloku, dwukropek, przeciwległy róg bloku. Np. A2:C3 oznacza blok złożony z sześciu komórek: A2, A3, B2, B3, C2, C3.
formuły
Jeśli chcemy wpisać w komórce liczbę lub napis, po prostu przechodzimy do żądanej komórki i wpisujemy nową wartość. Podobnie wpisujemy formułę, przy czym formuła musi zacząć się od znaku =, po którym następują wzór, w którym mogą pojawiać się liczby, adresy komórek i bloków, operatory arytmetyczne (+ - * /), funkcje.
Przykłady formuł:
| formuła | wyjaśnienie |
|---|---|
| =A2+1 | wartość komórki A2 powiększona o jeden |
| =(B4+C11)*A2 | |
| =SUMA(A2:C3) | suma liczb zawartych w sześciu komórkach bloku A2:C3 |
| =SUMA(A2:C3,G11:G20,Z1) | suma liczb w: sześciu komórkach bloku A2:C3, dziesięciu komórkach bloku G11:G20, komórce Z1 |
| =MAX(A1:D100)-MIN(A1:D100) | różnica wartości największej i najmniejszej w bloku A1:D100 |
| =ŚREDNIA(C1:D12) | średnia wartość komórek w bloku C1:D12 |
(W wersji angielskiej programu zamiast SUMA i ŚREDNIA mamy odpowiednio SUM i AVG.)
Zauważmy, że po wpisaniu formuły zostanie natychmiast wyliczona jej wartość. Wartość ta będzie wyświetlana w komórce (samą formułę można odczytać u góry w polu edycyjnym). Co więcej, przy każdej zmianie wartości komórki, której adres pojawia się w formule, formuła jest ponownie automatycznie wyliczana!
Ćwiczenie W polach A1:L1 wpisać kolejne nazwy miesięcy. W polach A2:L2 wpisać (wymyślone) temperatury. W pola M2, N2, O2, P2 wpisać formuły wyliczające odpowiednio: różnicę między temperaturą marca i września, średnią temperaturę, minimalną temperaturę, maksymalną temperaturę. Sprawdzić, czy zmiana w wartościach temperatur prowadzi do ponownego wyliczenia formuły.
powielanie formuły
Bardzo użyteczne polecenia to Edycja | Wypełnij w dół/Wypełnij w lewo. Powodują one powielenie zawartości komórek pierwszego rzędu zaznaczonego bloku (odpowiednio: pierwszej kolumny zaznaczonego bloku) „w dół” (odpowiednio: „w lewo”).
Wypełnianie w dół/w lewo dobrze współgra z tym, że adresy komórek są traktowane względnie. Na przykład jeśli w komórce G3 znajduje się formuła =A1+B3, to po wypełnieniu w dół bloku G3:G100, w komórce G4 znajdzie się formuła =A2+B4, w komórce G5 — =A3+B5 itd. Jeśli chcemy adresować bezwzględnie, litera i/lub liczba w adresie powinna być poprzedzona znakiem $. Na przykład gdybyśmy w omawianym przykładzie wpisali formułę =A1+$B$3 i wypełnili w dół, w komórce G4 mielibyśmy formułę =A2+$B$3, w G5 — =A3+$B$3 itd.
Ćwiczenie W bloku A11:A510 wpisać 500 razy słowo „liczba”, w bloku B11:B510 — kolejne liczby od 1 do 500, a w bloku C11:C510 — kwadraty liczb od 1 do 500.
Ćwiczenie Dla danych z pierwszego ćwiczenia dopisać blok A3:P3, zawierający odpowiednie temperatury przeliczone na stopnie Fahrenheita (wzór: F = 9/5 * C + 32).
wczytywanie danych z pliku tekstowego
Czasami istnieje potrzeba wczytania (importowania) „surowych” danych z pliku tekstowego. Zakłada się, że kolejne wiersze pliku tekstowego odpowiadają rzędom arkusza kalkulacyjnego, zaś pola wiersza oddzielone separatorem — komórkom. Separatorem na ogół są spacje, znaki tabulacji, przecinki, średniki. Aby wczytać taki plik tekstowy jako arkusz kalkulacyjny, należy (w Excelu) wybrać polecenie Plik | Otwórz, następnie w liście rozwijanej wybrać plik tekstowy i wybrać nazwę pliku, po kliknięciu przycisku OK, pojawią się kolejne okna dialogowe, w których użytkownik może ustalić opcję wczytywania. Najważniejsze jest tutaj wybranie stosownego separatora.
Uwaga: W trzecim kroku może być konieczne ustalenie separatora dziesiętnego (trzeba kliknąć przycisk Zaawansowane). Domyślnie w polskiej wersji jest to przecinek, ale często w danych część ułamkową oddziela kropka!
formatowanie komórki
Istnieje możliwość ustalenia czcionki, koloru, tła, obramowania itd. dla komórki lub bloku komórek. Wystarczy wybrać komórkę/blok, nacisnąć prawy klawisz myszy i wybrać polecenie Formatuj komórki. Dzięki temu można „uładnić” arkusz.
wykresy
Aby stworzyć wykres, należy zaznaczyć interesujący nas blok komórek, a następnie wybrać polecenie Wstaw|Wykres.
W tym momencie uruchamiany jest Kreator wykresów: w czterech krokach można wybrać parametry wykresu. Najpierw wybieramy typ wykresu (liniowy, słupkowy, kołowy itd.). Bardzo istotny jest krok drugi. W kroku tym wybieramy, jakie bloki komórek mają zostać odzwierciedlone na wykresie. Przede wszystkim można wybrać serie (na wykresie słupkowym seriom odpowiadać będą słupki różnego koloru, na wykresie liniowym — łamane różnego koloru). Wybieramy także, jakie fragmenty zostaną użyte jako podpisy (np. pod słupkami), a jakie jako nazwy serii. Bardzo często automatyczny wybór tych parametrów przez program jest trafny i nie trzeba nic zmieniać.
W dwóch kolejnych krokach wybieramy dodatkowe parametry, a potem klikamy przycisk Zakończ.
Zauważmy, że wstawiony wykres "pływa" na powierzchni arkusza.
zadanie
Stworzyć arkusz kalkulacyjny na podstawie pliku tekstowego (jaki dokładnie plik — patrz poniżej). Dopisać kilka formuł (np. wyliczających sumy, różnice, średnie, minima, maksima itd. — oczywiście tak, by miało to sens matematyczny). Stworzyć jeden lub dwa wykresy. Należy dostarczyć arkusz jako plik oraz jako plik PDF i wydruk.
Uwaga: „Złośliwie” poprzestawiałem kolumny lub wiersze, tak że nie są one uporządkowane (np. lata są nie po kolei). Należy przywrócić właściwe uporządkowanie (ręcznie lub za pomocą polecenia Sortuj).
Aby ustalić, jaki plik tekstowy ma stanowić podstawę arkusza, należy wziąć resztę z dzielenia swojego numeru indeksu przez 5. Oto kolejne pliki w zależności od reszty:
- 0 — dane0.txt (ludność w Siedlcach w latach 1990-2001),
- 1 — dane1.txt (liczba wypadków z udziałem pieszych),
- 2 — dane2.txt (opady w calach na wyspie Anguilla),
- 3 — dane3.txt (statystyka podejrzanych wg Komendy Głównej Policji),
- 4 — dane4.txt (inwestycje zagraniczne w Bułgarii w mln dolarów).
Do zdobycia 8 punktów. Oceniane będą: poprawność danych i wyników, sensowność użytych formuł i wykresów, estetyka. Termin zdania projektu mija 8 czerwca 2006.
Patrz także godziny dyżurów, podczas których można zdawać projekt.