Analiza i Prezentacja Danych, Laboratorium 4
Arkusz kalkulacyjny - podstawy stosowania reguł arytmetycznych
i funkcji oraz formatowania komorek
Wytyczna 1:
Przed przystąpieniem do rozwiązywania zadań należy:
1. Utworzyć nowy folder i nazwać go (imię_nazwisko_Lab_numer);
2. Wszystkie pliki powiązane z danym laboratorium zapisywać w lokalizacji utworzonej według punktu 1.
Wytyczna 2:
TworzÄ…c nowy dokument (plik dokumentu tekstowego, arkusza kalkulacyjnego, bazy danych, prezentacji
multimedialnej) należy go zapisać według następującego wzorca
AiPDLabNrZadNr#ImięNazwiskoKierunekNrGrupy (przykład: AiPDLab1Zad1-3#FilipSzafraniecMiBM1).
Dodatkowo należy pamiętać o podpisaniu pliku z zadaniem imieniem i nazwiskiem we Właściwościach
zaawansowanych pliku w karcie Podsumowanie (Plik ö' WÅ‚aÅ›ciwoÅ›ci ö' WÅ‚aÅ›ciwoÅ›ci zaawansowane ö'
Podsumowanie). Na rysunku 1 przedstawiono przykład podpisania pliku z rozwiązanym zadaniem.
Rys. 1. Karta Podsumowanie w Zaawansowanych właściwościach dokumentu tekstowego
Wytyczna 3:
Wytyczna dotyczy osób pracujących na jednostkach obliczeniowych udostępnionych przez Uczelnię. Po
zakończeniu pracy należy:
1. Zarchiwizować wyniki swoich prac na własnym zewnętrznym nośniku danych, lub przesyłając je na
własny adres e-mail, czy dysk działający w chmurze bądz
w inny legalny sposób;
2. Trwale usunąć z przestrzeni dyskowej katalog utworzony według wytycznej 1 w punkcie 1.
1
Analiza i Prezentacja Danych, Laboratorium 4
Zadanie 1
1. Pobrać ze strony Katedry Mechaniki Precyzyjnej (http://kmp.wm.tu.koszalin.pl) pliki do wykonania
ćwiczeń z laboratorium numer 4 i wyodrębnić je do katalogu utworzonego według wytycznej 1.
2. Otworzyć Microsoft Excel. Zapisać nowo utworzony arkusz kalkulacyjny oraz podpisać go we
właściwościach zaawansowanych według wytycznej 2.
3. Zmienić nazwę Arkusza1 na Studenci1 i usunąć pozostałe arkusze.
4. Zaimportować do arkusza Studenci1 dane z pliku wskazanego przez prowadzącego zajęcia znajdującego
siÄ™ w katalogu DaneDoZadania (Numer klucza 1). Importowane dane zawierajÄ… ogranicznik w postaci
znaku średnika (;) i separator dziesiętny w postaci kropki (.).
5. Sformatować dane w arkuszu i utworzyć nagłówki kolumn według rysunku 2:
Rys. 2. Nagłówki kolumn w arkuszu kalkulacyjnym dla danych zaimportowanych z pliku
·ð Dodawanie indeksu dolnego: Zaznaczyć tekst ö' Wybrać z menu kontekstowego Formatuj komórki& ö'
Czcionka ö' Indeks dolny,
·ð Zastosować czcionkÄ™ Arial, 10 pkt,
·ð Nagłówki pogrubić,
·ð Dla nagłówków wÅ‚Ä…czyć zawijanie tekstu (NarzÄ™dzia główne ö' Zawijaj tekst),
·ð We wszystkich komórkach wyrównać zawartość w pionie i poziomie zgodnie z rysunkiem 2,
·ð Aby zmienić orientacjÄ™ tekstu w komórkach należy: Zaznaczyć komórk(Ä™)i ö' Wybrać z menu
kontekstowego Formatuj komórki& ö' Wyrównanie ö' Orientacja,
·ð W komórce U2 wstawić formuÅ‚Ä™ zwracajÄ…cÄ… bieżącÄ… datÄ™ i godzinÄ™ (FormuÅ‚y ö' Biblioteka formuÅ‚ ö'
Data i godzina),
·ð Ustawić dla tej komórki wÅ‚asny format liczb (Zaznaczyć komórk(Ä™)i ö' Wybrać z menu kontekstowego
Formatuj komórki& ö' Liczby ö' Niestandardowe ö' Wprowadzić maskÄ™ formatu w pole Typ: zgodnie
z rysunkiem 2 ö' OK),
·ð Skopiować zawartość z komórki U2 i Wkleić specjalnie& ö' WartoÅ›ci i formaty liczb do komórki A2,
·ð Scalić komórki A2:E3 i U2:X3,
2
Analiza i Prezentacja Danych, Laboratorium 4
·ð Zastosować obramowanie komórek wedÅ‚ug rysunku 2 (NarzÄ™dzia główne ö' Obramowania ö' WiÄ™cej
obramowań)
6. Obliczyć wartość średniej arytmetycznej w komórce U5 dla ocen dla pierwsze(go)j Student(a)ki na
liÅ›cie i zaokrÄ…glić tÄ™ wartość do dwóch miejsc po przecinku (FormuÅ‚y ö' Biblioteka formuÅ‚ ö' Matem.
i tryg.),
7. Obliczyć wartość średniej ważonej w komórce V5 dla ocen dla pierwsze(go)j Student(a)ki na liście oraz
dla wag F3:T3 według zależności 1 i zaokrąglić tę wartość do dwóch miejsc po przecinku:
5Ø\Ü15ØdÜ1+5Ø\Ü25ØdÜ2+ï"+5Ø\Ü5Ø[Ü5ØdÜ5Ø[Ü
5Øß = , (1)
5ØdÜ
5ØdÜ1+5ØdÜ2+ï"+5ØdÜ5Ø[Ü
·ð gdzie: o  wartość oceny z i-tego laboratorium, w  wartość wagi przypisanej do i-tego laboratorium, n -
i i
liczba laboratoriów,
·ð zastosować bezwzglÄ™dne adresowanie komórek przechowujÄ…cych wartoÅ›ci wag np.:
- zablokowanie wartości komórki F3 w pionie i poziomie $F$3,
- zablokowanie wartości komórki F3 w pionie $F3,
- zablokowanie wartości komórki F3 w poziomie F$3,
·ð Nie trzeba odrÄ™cznie wpisywać znaku $. Można kliknąć wybranÄ… komórkÄ™, a nastÄ™pnie użyć klawisza
funkcyjnego F4,
·ð WpisujÄ…c formuÅ‚Ä™ należy pamiÄ™tać o kolejnoÅ›ci wykonywania dziaÅ‚aÅ„ arytmetycznych.
8. W komórce W5 obliczyć wartość różnicy między wartością średniej ważonej a wartością średniej
arytmetycznej.
9. W komórce AA1 utworzyć nagłówek Granica stypendium.
10. W komórce AA2 wprowadzić wartość według klucza 2 wskazanego przez prowadzącego zajęcia
i zdefiniować nazwÄ™ (gStyp) dla tej komórki (FormuÅ‚y ö' Nazwy zdefiniowane ö' Definiuj nazwÄ™ ö'
Definiuj nazwÄ™& ).
Tabela 1. Numeru klucza 2. Wartości granicy stypendium
Numer Numer Numer
Granica Granica Granica
klucza klucza klucza
stypendium stypendium stypendium
2 2 2
1 4,88 11 4,03 21 3,98
2 4,61 12 4,28 22 4,38
3 4,76 13 4,85 23 4,17
4 4,89 14 4,27 24 4,64
5 4,19 15 4,57 25 3,91
6 3,94 16 4,29 26 4,08
7 4,13 17 4,85 27 4,78
8 4,00 18 4,36 28 4,06
9 4,50 19 4,73 29 4,41
10 4,41 20 4,56 30 4,30
11. W komórce X5 sprawdzić czy pierwsz(y)a Student(ka) na liście dla określonej granicy stypendium
otrzyma stypendium. Jeśli V5 ś* gStyp to powinna wypisać się w tej komórce wartość 1 w przeciwnym
wypadku wartość 0 (FormuÅ‚y ö' Biblioteka formuÅ‚ ö' Logiczne ö' JEÅ»ELI),
·ð W zapisie formuÅ‚y wykorzystać wczeÅ›niej zdefiniowanÄ… nazwÄ™ przechowujÄ…cÄ… granicÄ™ stypendium
gStyp.
12. Na przykładzie rysunku 3 wykonać formatowanie warunkowe komórek V5, W5, X5. Przykład tworzenia
formatowania warunkowego dla komórki V5:
·ð Wybrać komórkÄ™ V5,
·ð Przejść do NarzÄ™dzia główne ö' Style ö' Formatow. warunk. ö' ZarzÄ…dzaj reguÅ‚ami& ö' Nowa reguÅ‚a,
·ð Wybierz typ reguÅ‚y: Formatuj wszystkie komórki na podstawie ich wartoÅ›ci,
·ð Styl formatowania ö' Pasek danych,
·ð Minimum: Typ ö' Liczb, Wartość ö' 2, Maksimum: Typ ö' Liczb, Wartość ö' 5,
·ð Ustawić wyglÄ…d paska i zatwierdzić formatowanie przez klikniÄ™cie przycisku OK.
3
Analiza i Prezentacja Danych, Laboratorium 4
Rys. 3. Przykład formatowania warunkowego komórek w arkuszu kalkulacyjnym
13. Zastosować obliczenia i formatowanie warunkowe dla pozostałych studentów.
14. Utworzyć nowy arkusz o nazwie Statystyka.
15. W arkuszu Statystyka utworzyć nagłówki dla poniższych wyrażeń i obliczyć je w wierszu poniżej
z wykorzystaniem formuł:
·ð Najniższa Å›rednia ważona Studenta (FormuÅ‚y ö' Biblioteka formuÅ‚ ö' WiÄ™cej funkcji ö' Statystyczne ö'
MIN) ,
·ð Najwyższa Å›rednia ważona Studenta (formuÅ‚a MAX),
·ð Wartość Å›rednia z Å›redniej ważonej Studentów,
·ð Liczba przyznanych stypendiów (FormuÅ‚y ö' Biblioteka formuÅ‚ ö' Matem. i tryg. ö' SUMA.JEÅ»ELI),
·ð Liczba wszystkich studentów,
·ð Procent studentów otrzymujÄ…cych stypendium.
16. Zapoznać się z treścią dodatku.
17. W elektronicznej wersji instrukcji do laboratorium 4 zapoznać się z treścią zadania domowego.
Dodatek
Podczas pracy z arkuszem może się zdarzyć, że został popełniony błąd. Zamiast pisać od nowa całą
formułę, najczęściej wystarczy poprawić na przykład adres komórki lub szerokość kolumny. Poniżej
przedstawiono opis najczęściej spotykanych błędów:
·ð #### - wystÄ™puje, kiedy kolumna ma za maÅ‚Ä… szerokość albo zostaÅ‚a wpisana ujemna data lub godzina,
·ð #ARG!- wystÄ™puje, gdy zostaÅ‚ podany bÅ‚Ä™dny argument (na przykÅ‚ad zamiast komórki zawierajÄ…cej
liczbę został podany adres komórki zawierającej tekst) lub operator (na przykład złe odwołanie do
komórki),
·ð #DZIEL/0!- wystÄ™puje, kiedy nastÄ™puje próba dzielenia przez zero,
·ð #NAZWA?-wystÄ™puje tam, gdzie program nie rozpoznaje tekstu użytego w formule (na przykÅ‚ad gdy nie
użyto do zapisania tekstu cudzysłowu).
·ð #N/D!-wystÄ™puje, kiedy wartość jest niedostÄ™pna dla formuÅ‚y lub funkcji,
·ð #ADR!- wystÄ™puje, kiedy odwoÅ‚anie do komórki jest niepoprawne,
·ð #LICZBA!- wystÄ™puje kiedy formuÅ‚a lub funkcja zawiera niepoprawnÄ… wartość (na przykÅ‚ad gdy
następuje próba wyliczenia pierwiastka z liczby ujemnej).
4
Analiza i Prezentacja Danych, Laboratorium 4
Zadanie 2 (zadanie domowe)
Na podstawie zadania domowego z poprzednich zajęć laboratoryjnych i zadania 1 utworzyć nowy arkusz
o nazwie Moje stypendium i wypełnić go ocenami uzyskanymi w poprzednim semestrze. Wartości wag powinny
odpowiadać liczbie godzin przeznaczonych tygodniowo na dany przedmiot. Następnie obliczyć średnią
arytmetyczną, średnią ważoną oraz ich różnicę. Sprawdzić z wykorzystaniem zagnieżdżenia formuły JEŻELI czy
uzyskałoby się stypendium, jeżeli tak to w jakiej kwocie (tabela 2):
Tabela 2. Granice stypendium
Granica Kwota
stypendium stypendium
x ś* 4,85 1 000,00 zł
x< 4,85 i x ś* 4,65 800 zł
x< 4,65 i x ś* 4,45 600 zł
x< 4,45 i x ś* 4,25 400 zł
x< 4,25 i x ś* 4,05 200 zł
x< 4,05 i x ś* 3,85 100,00 zł
x<3,85 0,00 zł
5