Edycja arkuszy kalkulcyjnych¶
OpenOffice.org/LibreOffice Calc¶
Podstawowe pojęcia:
- kolumna, etykieta (nazwa) kolumny
- wiersz, etykieta (nazwa) wiersza
- komórka, adres komórki
- pole edycji komórki
- arkusz, etykieta (nazwa) arkusza
- skoroszyt
- Strony domowe projektów:
Podstawowa obsługa¶
Tworzenie nowego dokumentu (Plik → Nowy → Dokument tekstowy, CTRL + N)
Zapisywanie dokumentu (Plik → Zapisz, CTRL + S
Zapisywanie dokumentu z nową nazwą (Plik → Zapisz jako, CTRL + SHIFT + S)
Otwieranie dokumentu (Plik → Otwórz, CTRL + O)
- Poruszanie się po dokumencie
- Przejście do nastepnej komórki (strzałki)
- Przejście do początku/końca wiersza/kolumny (HOME, END)
- Przejście do początku/końca wiersza/kolumny (CTRL + strzałki)
- Przejście do kolejnej/poprzedniej strony (PGUP, PGDOWN)
- Przejście do początku/końca dokumentu (CTRL + HOME, CTRL + END)
- Zmiany w komórce
- Zatwierdzenie zmian (ENTER) ew. przemieszczenie się na zewnątrz komórki
- Ignorowanie zmian (ESC)
- Zaznaczanie tekstu
- Zaznaczanie komórki (SHIFT + strzałki)
- Zaznaczanie do początku/końca linii (SHIFT + HOME, SHIFT + END)
- Zaznaczanie do początku/końca linii (SHIFT + CTRL + strzałki)
- Zaznaczenie kolejnej/poprzedniej strony (SHIFT + PGUP, SHIFT + PGDOWN)
- Zaznaczanie do początku/końca dokumentu (SHIFT + CTRL + HOME, SHIFT + CTRL + END)
- Zaznaczanie całości dokumentu (CTRL + A)
- Przenoszenie tekstu
- Kopiuj zaznaczenie (CTRL + C)
- Wytnij zaznaczenie (CTRL + X)
- Wklej tekst (CTRL + V)
- Wklej specjalnie (CTRL + SHIFT + V)
- Historia operacji
- Cofnij (CTRL + Z)
- Powtórz cofniętą operacje (CTRL + Y)
Arkusze¶
- Zmień nazwę arkusza z Arkusz1 na Dane (Prawy przycisk myszy na etykiecie arkusza → ...).
- Usuń Arkusz2 i Arkusz3.
- Duplikuj arkusz Dane (Prawy przycisk myszy na etykiecie arkusza → Przenieś/Kopiuj), nazwij nowopowstały arkusz Oryginał, powróć do arkusza Dane.
- Wstaw nowy arkusz i nazwij go Wykresy.
Style i formatowanie¶
Zapisz na dysk i otwórz plik
taryfy.ods
. Upewnij się, że plik nie jest otwrty w trybie tylko do odczytu; jeśli tak jest, zapisz go z inną nazwą (Plik → Zapisz jako)Scalając odpowiednie komórki oraz edytując ich treść zmodyfikuj nagłówki w następujący sposób:
Stwórz styl nagłówkowy i zastosuj go do pierwszego wiersza arkusza (okno Style i formatowanie: (Format → Style i formatowanie, F11)
Ustaw rozmiar czcionki użytego stylu nagłówkowego na pogrubioną czcionkę bezszeryfową rozmiaru 11 punktów, zezwól na podział wiersza, oraz ustaw kierunek tekstu na pionowy (90 stopni), ustaw kolor tła na szary.
Ustaw wysokość wiersza na optymalną (Format → Wiersz → Optymalna wysokość wiersza lub podwójne kliknięcie na krawędź etykiety wiersza),
Wstaw nową kolumnę przed pierwszą kolumną arkusza (Wstaw → Kolumny lub prawy przycisk na etykiecie wiersza → Wstaw kolumny), dodaj do niej nagłówek L.p.
Poniżej nagłówka L.p. wstaw liczbę 1 i ciągnąc za gór komórki ponumeruj wszystkie wiersze z danumi
Ustaw styl nagłówkowy dla nowej kolumny (okno Style i formatowanie: Format → Style i formatowanie (F11)
Ustaw szerokość wszystkich kolumn na optymalną (Format → Kolumna → Optymalna szerokość kolumny lub podwójne kliknięcie na krawędź etykiety kolumny),
Dodaj obramowanie dookoła obszaru zawierającego dane, nagłówki. (Format → Komórki lub CTRL + 1)
Podstawowe funkcje¶
- Pod kolumną
Liczba wiadomości
dodaj kolumny (w kolejnych wierszach) - sumującą wartości kolumny (funkcja
SUMA
), - wyznaczającą minimalną wartość w kolumnie (funckcja
MIN
), - wyznaczającą maksymalną wartość w kolumnie (funkcja
MAKS
), - uśredniającą wartości kolumny (funkcja
ŚREDNIA
), - obliczającą wariancję dla kolumny (funkcja
WARIANCJA
), - obliczającą odchylenie standardowe dla kolumny (funkcja
ODCH.STANDARDOWE
).
- sumującą wartości kolumny (funkcja
- Pod kolumną
- Utwórz te same funkcje dla kolumn (dla każdej z funkcji złap za prawy dolny róg komórki z funkcją i przeciągnij ją w prawo):
Dzień minuty
,Dzień rozmowy
,Dzień opłata
,Wieczór minuty
,Wieczór rozmowy
,Wieczór opłata
,Noc minuty
,Noc rozmowy
,Noc opłata
,Międzynarodowe minuty
,Międzynarodowe rozmowy
,Międzynarodowe opłata
,Liczba rozmów z BOK
,Czas współpracy
.
- Sześć wierszy poniżej kolumny
Plan międzynarodowy
scal tak aby powstały dwa wiersze, a następnie za pomocą funkcjiLICZ.JEŻELI
(aby funkcja potraktowała jakiś ciąg znaków jako napis należy go podać w cudzysłowiu, np"tak"
a w przeciwnym wypadku ciąg znaków zostnie potraktowany jako adres, np.A1
): - górny wiersz niech liczy wszystkie wartości pozytywne,
- dolny wiersz niech liczy wszystkie wartości negatywne.
- Sześć wierszy poniżej kolumny
Utwórz te same funkcje dla kolumn:
Poczta głosowa
iChurn
.Dodaj nową kolumnę o nagłówku
Podsumowanie całkowity koszt połączeń
która podsumowuje całkowity koszt połączeń wykonanych przez klienta, tj. niech sumuje opłaty dzienne, wieczorowe, nocne, międzynarodowe.Dodaj nowy arkusz (Wstaw → Arkusz) o nazwie
Próg
. W komórceA1
tego arkusza wstaw wartość średnią z wartości z kolumnyPodsumowanie całkowity koszt połączeń
.- Jeśli klient wydał na połączenia mniej niż próg wartości to komorka powinna przyjmować wartość
kiepski
,
- Jeśli klient wydał na połączenia mniej niż próg wartości to komorka powinna przyjmować wartość
- Wróć do akusza
Dane
i wstaw nową kolumnę o nagłówku (Podsumowanie Wartość klienta). W kolumnie tej chcemy uzyskać informacje o tym ile warty dla firmy jest klient. Jako próg wartości należy użyć wartości z komórkiA1
z arkuszaPróg
. (Żeby móc napisać funkcję raz i wykorzystać ją dla calej kolumny należy adres zabezpieczyć przed zmianami ustawiając znak dolara przed koordynatą kolumny i wiersza:$A$1
.) Kolumna powinna działać według zasad opisanych poniżej (funkcjaJEŻELI
): - Jeśli klient wydał na połączenia mniej niż próg wartości to komorka powinna przyjmować wartość
kiepski
, - W przeciwnym wypadku i jeśli klient wydał na połączenia dwa razy więcej niż próg wartości to komórka powinna przyjmować wartość
świetny
, - W przeciwnym wypadku, komórka powinna przyjmować wartość
dobry
.
- Jeśli klient wydał na połączenia mniej niż próg wartości to komorka powinna przyjmować wartość
- Wróć do akusza
- Dodaj na spodzie kolumny
Podsumowanie Wartość klienta
wiersze zliczające liczbę wartościdobry
,świetny
ikiepski
w tej kolumnie (i odpowiednio poscalaj komórki w tych wierszach).
Formatowanie warunkowe¶
- Utwórz style do formatowania warunkowego (okno Style i formatowanie: Format → Style i formatowanie, F11):
- Negatywnie (kolor tła: czerwony),
- Pozytywnie (kolor tła: zielony),
- Neutralnie (kolor tła: żółty).
- Zaznacz kolumny
Plan międzynarodowy
,Poczta głosowa Aktywna
i ustaw im formatowanie warunkowe (Format → Formatowanie warunkowe). Ustaw warunki tak, żeby uzyskać następujące następujące formatowanie: - Jeśli wartość komórki jest równa napisowi
nie
, styl komórki: Negatywnie, - Jeśli wartość komórki jest równa napisowi
tak
, styl komórki: Pozytywnie,
- Jeśli wartość komórki jest równa napisowi
- Zaznacz kolumny
- Zaznacz kolumnę
Liczba wiadomości
i ustaw formatowanie warunkowe (Format → Formatowanie warunkowe). Ustaw warunki następujące formatowanie: - Jeśli wartość komórki jest mniejsza od średniej w tej kolumnie (wskaż komórkę ze średnią), styl komórki: Negatywnie,
- Jeśli wartość komórki większa od średniej w tej kolumnie, styl komórki: Pozytywnie,
- Jeśli wartość komórki równa średniej w tej kolumnie, styl komórki: Neutralnie.
- Zaznacz kolumnę
Wykonaj powyższą czynność dla wszystkich kolumn posiadających wyliczone średnie (
Dzień minuty
,Dzień rozmowy
,Dzień opłata
,Wieczór minuty
,Wieczór rozmowy
,Wieczór opłata
,Noc minuty
,Noc rozmowy
,Noc opłata
,Międzynarodowe minuty
,Międzynarodowe rozmowy
,Międzynarodowe opłata
,Liczba rozmów z BOK
,Czas współpracy
).
Wykresy¶
W arkuszu
Wykresy
wstaw wykres słupkowy procentowy pokazujący w jaki sposób całkowity sumaryczny koszty połaczeń rozkłada się na połączenia wykonane w dzień (KolumnaJ
), w nocy (KolumnaM
), wieczorami (KolumnaP
) i połączenia międzynarodowe (KolumnaS
). Weź pod uwagę tylko wartość sumaryczną.- W arkuszu
Wykresy
wstaw wykres słupkowo-liniowy pokazujący - jako słupki średni koszt połączeń wykonanych w dzień (Kolumna
J
), w nocy (KolumnaM
), wieczorami (KolumnaP
) i połączenia międzynarodowe (KolumnaS
), - jako linie maksymalny i minimalnych koszt tych połączeń.
- jako słupki średni koszt połączeń wykonanych w dzień (Kolumna
- W arkuszu
W arkuszu
Wykresy
wstaw wykresy kołowe pokazujące jakie są proporcje wartości w każdej z kolumn:Churn
,Plan międzynarodowy
,Poczta głosowa Aktywna
iWartość klienta
.
Filtry automatyczne¶
Dla całego dokumentu (bez zaznaczenia) utwórz filtr który pokaże tylko wiersze w których
Liczba rozmów z BOK
jest różna od zera (Dane → Filtry → Filtr standardowy
) i dla których w kolumnieStan
znajduje się napisNY
.Usuń filtr.
- Utwórz histogram dla kolumny
Stan
. - Dla kolumny
Stan
utwórz filtr który usunie duplikatyDane → Filtry → Filtr standardowy → Więcej opcji → bez duplikatów
). - Następnie utwórz nowy arkusz o nazwie
Histogram
i skopiuj do niego odfiltrowaną treść kolumnyStan
. - W arkuszu
Dane
usuń filtr dla kolumnyStan
. - W arkuszu
Histogram
wstaw nową kolumnę o nagłówkuLiczba wystąpień
i wypełnij ją informacją o liczbie wystąpień każdego ze stanów z kolumnyStan
z arkuszaHistogram
w kolumnieStan
w arkuszuDane
(funkcjaLICZ.JEŻELI
). - Dodaj w arkuszu
Histogram
wykres kolumnowy przedstawiający liczbę wystąpień stanów.
- Dla kolumny
- Utwórz histogram dla kolumny
Utwórz filtry automatyczne dla wszystkich kolumn w arkuszu
Dane
(Zaznacz obszar arkusza, następnie Dane → Filtry → Autofiltr). Przetestuj możliwości filtrowania kolumn.
Ukrywanie i zabezpieczanie danych¶
- Ukryj kolumny
Kod
orazTelefon
. - Utwórz arkusz o nazwie
Dane osobowe
. Do komórekA1
,A2
,A3
tego arkusza wpisz swoje imię, nazwisko i numer indeksu. - Nakaż aby komórka
A3
została ukryta przed nieporządanym odczytem (Format → Komórka → Zakładka Ochrona komórek → Ukryj wszystko). - Nakaż aby komórka
A1
nie była chroniona (Format → Komórka → Zakładka Ochrona komórek → Chronione). - Zabezpiecz arkusz Dane (Narzędzia → Chroń dokument → Arkusz). Jako hasła do zabezpieczania arkusza użyj:
pinlab
. Co znajduję się w komórceA3
, czy można edytować komórkiA1
,A2
iA3
. - Odbezpiecz arkusz.
Jakie jest zastosowanie dla ukrywania komórek?
Kontrola poprawności danych¶
W arkuszu
Dane
dodaj kolumnęPrzeprowadzona kontrola
obok kolumnyPodsumowanie Wartość klienta
.- W powyższej kolumnie użytkownik arkusza będzie wpisywać czy kontrola została przeprowadzona (Narzędzia → Poprawność danych, zezwalaj: lista):
- Ogranicz możliwości użytkownika, żeby nie można było wpisywać innych wartości niż
tak
,nie
inie dotyczy
. - Ustaw także tekst pomocy wyjaśniający jak korzystać z tej kolumny oraz poinformuj uzytkownika o błędzie w wypadku gdy wartość będzie niepoprawna.
- Ogranicz możliwości użytkownika, żeby nie można było wpisywać innych wartości niż
W arkuszu
Dane
dodaj kolumnęOcena z kontroli [1-10]
.
- W powyższej kolumnie użytkownik arkusza ocenia klienta w skali od
1
do10
(Narzędzia → Poprawność danych, zezwalaj: liczba całkowita). - Ogranicz możliwości użytkownika, żeby nie można było wpisywać innych wartości niż te w zakresie 1-10.
- Napisz krótką pomoc i informację o błędzie.
- W powyższej kolumnie użytkownik arkusza ocenia klienta w skali od
Uzupełnianie według szeregu¶
- Wstaw nowy arkusz o nazwie
Studenci
. - W wierszu 1 wstaw komórki:
Indeks
(A1
),Imię
(B1
),Nazwisko
(C1
),Ocena
(D1
). - W komórce
A2
wstaw95152
, następnie w komórceA3
wstaw95154
. Zaznacz obie komórki i za pomocą myszki i czarnego kwadratu w prawym-dolnym narożniku zaznaczenia rozszerz komórki na obszarA2:A12
. - W komórkach
B2:B12
wstaw (dowolne) imiona. - W komórkach
C2:C5
wstaw (dowolne) cztery nazwiska. Następnie zaznacz wszystkie cztery komórki i za pomocą myszki i czarnego kwadratu w prawym-dolnym narożniku zaznaczenia rozszerz komórki na obszarC2:C12
. - W komórce
D2
wstaw liczbę losową z przedziału od 2 do 5 (funkcja: LOS.ZAKR lub RANDBETWEEN). Następnie zaznacz komórkę i za pomocą myszki i czarnego kwadratu w prawym-dolnym narożniku zaznaczenia rozszerz komórki na obszarD2:D12
.
Sortowanie danych¶
Posortuj dane w arkuszu
Histogram
malejąco wg kolumny Stan.- Posortuj dane w arkuszu
Studenci
: - Po pierwsze rosnąco wg nazwiska.
- W wypadku powtarzających się nazwisk, sortuj rosnąco według imion.
- W przypadku tych samych imion, sortuj rosnąco według numeru indeksu.
- Posortuj dane w arkuszu
Czym się różni sortowanie malejące od rosnącego?
Eksport i import danych¶
- Zapisz dokument.
- Wyeksportuj dokument do formatu PDF. (Plik → Eksportuj jako PDF).
- Wyeksportuj dokument do postaci tekstowej używając przecinka do separacji pól i cudzysłowiu do oznaczania tekstu. (Plik → Zapisz jako → typ: csv)
- Otwórz dokument w postaci tekstowej (CSV), zapisz go jako dokument w formacie ODS.
Dlaczego eksportować dane do postaci tekstowej?
Zadanie indywidualne 4¶
Przygotowanie zbioru danych¶
Przed wykonaniem zadania oblicz resztę z dzielenia swojego numeru indeksu przez 7. Zadanie indywidualne dla danego studentu dotyczy jednego ze zbiorów danych poniżej odpowiadającemu obliczonej reszcie z dzielenia:
Polecenia¶
Dodaj kolumnę z l.p.
Dodaj kolumnę w której wzrost będzie pokazany w metrach, ukryj wzrost w centymetrach.
Dodaj informacje o średniej, maksimum, minimum i odchyleniu standardowym w odpowiednich kolumnach.
Policz kobiety i mężczyzn.
Posortuj dane wg wieku.
Policz BMI dla każdego osobnika.
- Dodaj kolumne w której ręcznie moż na sklasyfikować osobnika do jednej z kategorii (chroń przed wpisaniem innej kategorii):
wygłodzenie
,wychudzenie
,niedowaga
,prawidłowa
,nadwaga
,otyłość
,otyłość kliniczna
,otyłość skrajna
.
(Dla odważnych.) Sklasyfikuj każdego osobnika automatycznie wg BMI używając powyższych klas.
Dodaj autofiltry.
Dodaj formatowanie automatyczne dla wartości poniżej, powyżej i równych przeciętnej w każdej z kolumn.
Wykonaj wykresy pokazujące rozkład populacji na płci, histogram wg wieku, oraz wykres punktowy BMI.
Wpisz swoje imię, nazwisko i indeks w komórce
Z1
i chroń komórkę przed odczytaniem.Zabezpiecz arkusze hasłem
pilab
.Sformatuj plik estetycznie (za pomocą stylów, scalania i ustalania rozmiarów).
Zapisz jako pliki ODS, CSV i PDF.
Dostarczenie wyników¶
Wyślij pliki wynikowe za pomocą poczty elektronicznej (jako załączniki) na adres wykładowcy z tytułem [PIN] Zadanie Indywidualne 4
. Termin przysłania zadania: 29 XI 2014, godz. 23:59. Każdy dzień spóźnienia powoduje utratę 0.5 oceny. Prace będą badane pod względem nadmiernej współpracy, prace kopiowane nie będą przyjmowane do oceny.
Literatura uzupełniająca¶
Zagnieżdżone funkcje JEŻELI¶
Dyskretne klucze¶
Dany jest arkusz:
A B
1 90
2 80
3 70
4 60
5 50
Chcemy napisać funkcję która dla każdego klucza w kolumnie A1
wpisze w kolumnie A2
wartość taką, że:
- jeśli
A1==90
toB1==a
,- jeśli
A1==80
toB1==b
,- jeśli
A1==70
toB1==c
,- jeśli
A1==60
toB1==d
,- jeśli
A1==50
toB1==e
.
Funkcja IF/JEŻELI
¶
Dla pola B1
:
=IF(A1=90; "a";
IF(A1=80; "b";
IF(A1=70; "c";
IF(A1=60; "d";
IF(A1=50; "e"; "?")
)
)
)
)
Dla pól B2
, B3
, itp. przez analogię.
Funkcja LOOKUP
¶
Tworzymy sobie tabelkę opisującą funkcję (jeśli A1
przyjmuje to co jest w E1
to w B1
trzeba wpisać to co jest w F1
etc):
E F
1 90 a
2 80 b
3 70 c
4 60 d
5 50 e
Następnie dla pola B1
:
=LOOKUP(A1; $E$1:$E$5; $F$1:$F$5)
Przez analogię dla B2
, B3
, etc (zmieniając A1
na A2
, A3
, etc).
Klucz w przedziale¶
Dany jest arkusz:
A B
1 90
2 80
3 70
4 60
5 50
Chcemy napisać funkcję która dla każdego klucza w kolumnie A1
wpisze w kolumnie A2
wartość taką, że:
- jeśli
100 > A1 >= 90
toB1==a
,- jeśli
90 > A1 >= 80
toB1==b
,- jeśli
80 > A1 >= 70
toB1==c
,- jeśli
70 > A1 >= 60
toB1==d
,- jeśli
60 > A1 >= 50
toB1==e
.
Funkcja IF/JEŻELI
¶
Dla pola B1
:
=IF(A1<100; "?"
IF(A1<90; "a";
IF(A1<80; "b";
IF(A1<70; "c";
IF(A1<60; "d";
IF(A1<50; "e"; "?")
)
)
)
)
)
Dla pól B2
, B3
, itp. przez analogię.
Funkcje MATCH
i OFFSET
¶
Tworzymy sobie tabelkę opisującą funkcję (jeśli A1
przyjmuje to co jest w E1
to w B1
trzeba wpisać to co jest w F1
etc). Kolumna E
musi być posortowana.
E F
1 90 a
2 80 b
3 70 c
4 60 d
5 50 e
Funkcja MATCH
podaje nam który z kolei wiersz z podanej posortowanej kolumny będzie większy niz/mniejszy niż podana wartość. Więc mając:
=MATCH(A1; $E$1:$E$5; -1)
Jeśli A1 jest w przedziale np. 90-80
(np. A1==87
) to funkcja zwróci
1
. (-1
w trzecim argumencie mówi że kolumna E
jest posortowana
malejąco; gdyby była posortowana rosnąco, to podajemy 1
).
Funkcja OFFSET
poda nam wartość k
-tej komórki od komórki którą podamy. Czyli:
=OFFSET(F1; 3; 0)
Zwróci nam zawartość komórki F4
, co wynika z wartości drugiego argumentu
(F(1+3)
). (Trzeci argument służy do przesuwania się w poziomie.)
Jeśli przekażemy do funkcji OFFSET
liczbę wierszy którą uzyskamy z funkcji
MATCH
to za pomocą tych dwóch funkcji można złożyć funkcję dla pola B1
=OFFSET(F1; MATCH(A1; $E$1:$E$5; -1) - 1; 0)
Należy dokonać korekty wyniku z MATCH
, ponieważ będzie o 1 zbyt duży.
Dla B2
, B3
, etc przez analogię (zmieniając A1
na A2
, A3
, etc).