arkusz kalkulacyjny microsoft excel

Wydział Elektryczny
Katedra Elektrotechniki Teoretycznej i Metrologii
Instrukcja do pracowni z przedmiotu Podstawy Informatyki
Kod przedmiotu: TS1C 100 003
Ćwiczenie pt.
ARKUSZ KALKULACYJNY MICROSOFT EXCEL
Numer ćwiczenia
PI EiT 01
Autor
mgr inż. Agnieszka Choroszucho
Białystok 2012
1. OPIS STANOWISKA
Ćwiczenie jest wykonywane na stanowiskach komputerowych z zainstalowanym oprogramowanie
Microsoft Office 2010. Studenci poznają zasady wprowadzania danych do arkusza kalkulacyjnego. Stosują
formuły i funkcje z uwzględnieniem typów adresowania. Wykonują podstawowe obliczenia na liczbach
zespolonych. Wykonują i formatują wykresy. Sporządzone zadania stanowią sprawozdanie z ćwiczenia.
2. WSTĘP TEORETYCZNY
2.1. Okno programu
Rys. 1. Opis okna programu MS Excel
2.2. Budowanie formuł
Formuła – instrukcja, która oblicza i zwraca wynik do komórki, w której jest wpisana.
Tabela 1. Operatory arytmetyczne
Symbol
+
*
/
^
Priorytety operatorów:
Nazwa
dodawanie
odejmowanie
mnożenie
dzielenie
potęgowanie
Przykład
a+b
a-b
a*b
a/b
a^b
^ ---> *, / ---> +, -Przykład formuły
= (3+5) / (12-9) * 2^6
--->
170,67
Wynik, który zostanie wypisany w
komórce z formułą.Znak ‘=’ oznacza, że
nie są to dane, tylko formuła.
W celu zagnieżdżenia bardziej złożonych wyrażeń używamy nawiasów zwykłych.
=( (3,4-2,1) / (5,7-1,43) )^2
2
2.3. Formatowanie danych
Rys. 2. Przykład wyboru formatowania wybranych komórek
Rys. 3. Dostępne opcje przy formatowaniu danych
3
Rys. 4. Opcje w zakładce Wyrównanie
2.4. Wzory obliczeniowe – funkcje
SUMA(zakres) – funkcja oblicza sumę arytmetyczna liczb znajdujących się w komórkach danego zakresu.
Rys. 5. Przykład zastosowania funkcji SUMA(zakres)
ŚREDNIA(zakres) – funkcja oblicza średnią arytmetyczna z liczb znajdujących się w komórkach danego
zakresu.MAX(zakres), MIN(zakres) – funkcje znajdują odpowiednio największą lub najmniejszą wartość z
danego zakresu.JEŻELI(tekst_logiczny;Wartość_jeżeli_prawda;Wartość_jeżeli_fałsz) – funkcja zwraca
jedną wartość, gdy warunek ma wartość PRAWDA lub inną wartość, gdy warunek ma wartość FAŁSZ
(rys. 6).
4
Rys. 6. Zastosowanie funkcji JEŻELI(tekst_logiczny;Wartość_jeżeli_prawda;Wartość_jeżeli_fałsz)
LICZ.JEŻELI(zakres;kryteria) – oblicza liczbę komórek we wskazanym zakresie spełniających podane
kryteria (rys. 7).
Rys. 7. Przykład zastosowania funkcji LICZ.JEŻELI(zakres;kryteria)
ILE.NIEPUSTYCH(zakres) – bada, w ilu komórkach z zakresu są wartości i zwraca ich ilość jako wynik.
Funkcje w formułach można zagnieżdżać (czyli jedna funkcja może być parametrem innej funkcji), co
szczególnie przydaje się w instrukcji JEŻELI(), ORAZ(), LUB().
5
2.5. Serie danych
Rys. 8. Przykładowe wykonywanie serii z krokiem co zero
Rys. 9. Przykładowe tworzenie serii rosnącej z krokiem co jeden
2.6. Odwołanie do komórek
Do komórek odwołujemy się w formułach, a sposób odwołania ma szczególne znaczenie, jeśli formułą
wypełniamy pewien zakres komórek.
Rodzaje odwołań do komórek:
- względne,
- bezwzględne,
- mieszane.
6
a) Odwołanie względne – określa sposób odwołania WZGLĘDEM miejsca, skąd się odwołujemy (rys. 10).
np. wpisanie formuły =A1/2 w komórce B1
Rys. 10. Przykład odwołania względnego
b) Odwołanie bezwzględne – odwołanie do konkretnego miejsca, niezależnie od tego, skąd się
odwołujemy (rys. 11).np. =$A$1/2 - wpisane w komórce B1
Rys. 11. Przykład odwołania bezwzględnego
Na rysunku 12 przedstawiono różnicę pomiędzy prawidłowym i błędnym wykorzystaniem rodzaju
odwołania.
7
źle obliczone wartości U
poprawnie obliczone wartości U
Rys. 12. Porównanie wyników odwołania względnego i bezwzględnego
b) Odwołanie mieszane – jest to rozwiązanie pośrednie miedzy odwołaniem względnym a
bezwzględnym (rys. 13).np. =$A1 lub =A$1Znak „$” blokuje zmianę współrzędnej, przed która jest
umieszczony (np. kolumny, czy wiersza).
Rys. 13. Zastosowanie odwołania mieszanego
2.7. Liczby zespolone - funkcje
Każdą liczbę zespoloną z można zapisać w postaci z = a + bi, gdzie a i b są liczbami rzeczywistymi.
Natomiast i jest tzw. jednostką urojoną, tj. i jest jednym z dwóch elementów zbioru liczb zespolonych,
spełniającym warunek i2 = -1.
Liczba zespolona w postaci z = a + bi zawiera:
część rzeczywistą (re z = a),
część urojoną (im z = b).
8
Tabela 2. Podstawowe funkcje stosowane przy liczbach zespolonych
Funkcja
Opis
IMREAL(liczba_zespolona)
Zwraca część rzeczywistą liczby zespolonej.
IMAGINARY(liczba_zespolona)
Zwraca część urojoną liczby zespolonej.
IMABS(liczba_zespolona)
Zwraca wartość bezwzględną (moduł) liczby zespolonej.
IMARGUMENT(liczba_zesp)
Zwraca wartość argumentu liczby zespolonej (kąta w radianach).
IMCONJUGATE(liczba_zesp)
Zwraca wartość sprzężoną liczby zespolonej.
IMSUM(liczba_zesp1;liczba_zesp2)
Zwraca sumę liczb zespolonych.
IMSUB(liczba_zesp1;liczba_zesp2)
Zwraca różnicę dwóch liczb zespolonych.
IMPOWER(liczba_zesp;liczba)
Zwraca wartość liczby zespolonej podniesionej do potęgi całkowitej.
IMPRODUCT(liczba_zesp1;liczba_zesp2)
Zwraca iloczyn od 1 do 255 liczb zespolonych.
IMDIV(liczba_zesp1;liczba_zesp2)
Zwraca iloraz dwóch liczb zespolonych.
Rys. 14. Przykładowe wyniki uzyskane przy operacjach na jednej liczbie zespolonej
Rys. 15. Przykładowe wyniki otrzymane przy operacjach na dwóch liczbach zespolonych
2.8. Wykresy
Kreator wykresów zawiera standardowe i niestandardowe typy wykresów.
Przykładowe standardowe typy wykresów:
Kolumnowy,
Słupkowy,
Liniowy,
Kołowy,
XY (Punktowy),
Warstwowy,
Pierścieniowy.
Rys. 16. Wstawianie wykresu
Każdy z typów wykresu umożliwia dodatkowo wybór podtypu wykresu. Umożliwia to wybranie przycisku
zaznaczonego na rys. 17.
9
Rys. 17. Wybór podtypu wykresu
Rys. 18. Okno z podtypami wykresów
Po wykonaniu wykresu MS Excel umożliwia formatowanie:
Obszaru wykresu,
Głównych i pomocniczych linii siatki,
Obszaru kreślenia,
Legendy,
Osi kategorii (rys. 19),
Osi wartości,
Serii danych.
10
Rys. 19. Okno umożliwiające formatowanie osi wykresu
W zakładce Układ dostępne są opcje związane z formatowaniem elementów obszaru wykresu (rys. 20).
Rys. 20. Podpisywanie osi wykresu
Rys. 21. Dodawanie, usuwanie i edytowanie serii
11
2.9. Dodawanie linii trendu
Linia trendu jest najpopularniejszym narzędziem analizy technicznej. Jest to wykres ruchomej średniej
danych z serii, rodzaj prognozy, którą można nanieść na wykres do wybranej serii danych (rys. 22).
Rys. 22. Opcje linii trendu w MS Excel
12
3. PRZEBIEG ĆWICZENIA
3.1. Zaprojektuj tabele wg poniższego wzorca. Tabelę z danymi studentów należy samodzielnie
wypełnić tak, aby lista osób wynosiła 30. Używając funkcji warunkowej JEŻELI() należy sprawdzić,
czy dany student zaliczył przedmiot (uwaga: obie oceny muszą być równe lub większe od 3,0 oraz
jednocześnie mniejsze lub równe 5,0). Należy również obliczyć średnie: z ćwiczeń, wykładu oraz z
całego przedmiotu. Przy wykorzystaniu funkcji wbudowanych obliczyć ilość pustych miejsc z
ocenami oraz zrobić statystykę ocen (tzn. np. ile było wartości 5,0 wśród wszystkich ocen).
3.2. Oblicz wartość poszczególnych wyrażeń.
a)
(3.54 − 1.21) 2
(2.33 − 1) ⋅ (3.14 + 0.97)
b)
((((2 + 3) ⋅ 2 + 3) ⋅ 2 + 3) ⋅ 2 + 3) ⋅ 2 + 3
c)
1 .5
(1.2 + 1) 0.5
12.3
+
⋅
2.3 / 4.1
2
((4.5 + 2) 2 − 1
3.3. Oblicz wynik równania (5+4i)3 + (3-i)*(2+2i). Podaj moduł oraz argument otrzymanego wyniku.
Wykorzystaj dostępne w MS Excel funkcje.
3.4. Wyobraź sobie, że masz 5000 zł i chcesz je przechować przez rok.
Porównaj, co będzie korzystniejsze:
a) lokata tygodniowa o oproc. 2,55%;
b) lokata miesięczna o oproc. 3%;
c) lokata kwartalna o oproc. 4,5%;
13
d) lokata roczna o oproc. 6%.
Przedstaw wyniki na wykresach:
na jednym wykresie porównanie sald końcowych;
na osobnych wykresach wzrost kapitału w ciągu roku.
3.5. Wyobraź sobie, że masz 5000 zł i chcesz je przechować przez rok.
Porównaj, co będzie korzystniejsze:
a)
b)
c)
d)
lokata tygodniowa o oproc. 2,55%;
lokata miesięczna o oproc. 3%;
lokata kwartalna o oproc. 4,5%;
lokata roczna o oproc. 6%.
Przedstaw wyniki na wykresach:
na jednym wykresie porównanie sald końcowych;
na osobnych wykresach wzrost kapitału w ciągu roku.
3.6. Wykreśl charakterystyki I = f(U) na podstawie wyników pomiaru zamieszczonych w tabeli.
U
I
V
mA
0
0
1,5
2,3
3,0
3,6
4,5
5,9
6,0
6,6
7,5
8,4
9,0
8,3
10,5
9,3
Wykonaj poniższe polecenia.
Wybierz typ wykresu XY punktowy.
Nadaj tytuł wykresowi oraz nazwij odpowiednio osie.
Ustaw linie pomocnicze niewidoczne.
Ustaw podziałkę osi U co 1,5, natomiast osi I co 2,0.
Znaki na osiach U oraz I należy sformatować tak, aby czcionka była 10 pkt., Arial.
3.7. Wykreśl funkcję y=x2 dla następujących wartości x zawartych w tabeli.
x
1
2
5
10
20
50
100
200
500
1000
Wykonaj poniższe polecenia.
Wybierz typ wykresu XY punktowy.
Nadaj tytuł wykresowi oraz nazwij i sformatuj odpowiednio osie.
Usuń szare tło i zrób linie pomocnicze.
Na osi y zrób skalę logarytmiczną.
3.8. Zmień nazwę Arkusz 1 na ZADANIE 1. W kolumnie A wypełnić 47 komórek serią danych od (-68,5).
Każda kolejna wartość będzie większa od poprzedniej o 3,0. Liczby w kolumnie A stanowią
współrzędne x, natomiast wartości y należy obliczyć w kolumnie C na podstawie funkcji:
y = 0,2(x) + sin2(x) + cos(x)
14
Następnie wykonaj poniższe polecenia.
Wybierz właściwy typ wykresu.
Nazwij i sformatuj odpowiednio osie (rozmiar czcionki 8 pkt., Arial Narrow).
Skala x od -80 do 80 z podziałką co 10.
Skala y od -25 do 25 z podziałką co 5.
Rozmiar znacznika 3 pkt.
Linie siatki kreskowane, kolorem szarym.
Legenda na dole z odpowiednio nazwaną serią danych.
4. LITERATURA
[1]
[2]
[3]
[4]
[5]
[6]
Kopertkowska M.: Arkusze kalkulacyjne. Mikom, Warszawa, 2006.
Masłowski K.: Funkcje w przykładach. Helion, 2007.
Moran J., Hull V.: Brilliant ECDL. Pearson Education, 2005.
Walkenbach J.: Excel 2010 PL. Biblia. Helion, 2010.
Masłowski K.: Excel 2007/2010 PL. Ćwiczenia zaawansowane. Helion, 2011.
Wrotek W.: Elektronika z Excelem. Helion, 2012.
5. ZAGADNIENIA NA ZALICZENIE
1. Co to jest formuła i jakie są priorytety operatorów?
2. Wymień rodzaje odwołań do komórek. Na dowolnym przykładzie zaprezentuj działanie wymienionych
rodzajów odwołań.
3. Na dowolnym przykładzie zaprezentuj działanie funkcji LICZ.JEŻELI(zakres;kryteria).
4. Napisz formułę, za pomocą której będzie można wypełnić zadany obszar w celu sporządzenia tabliczki
mnożenia od 100 do 1000.
5. Opisz postać algebraiczną liczby zespolonej. Wymień funkcje MS Excel wspomagające obliczenia na
liczbach zespolonych.
6. Co to jest linia trendu i jakie są jej rodzaje w MS Excel? Na dowolnym przykładzie wykonaj liniową linię
trendu.
7. Jakie są podstawowe rodzaje wykresów w MS Excel? Jaki typ wykresu należy wybrać w przypadku
zależnych od siebie danych np. przy wykonywaniu charakterystyki I = f(U)?
15