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

2

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 Nazwa Przykład

+ dodawanie a+b

- odejmowanie a-b

* mnożenie a*b

/ dzielenie a/b

^ potęgowanie a^b

Priorytety operatorów: ^ ---> *, / ---> +, -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

3

2.3. Formatowanie danych

Rys. 2. Przykład wyboru formatowania wybranych komórek

Rys. 3. Dostępne opcje przy formatowaniu danych

4

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).

5

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().

6

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.

7

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.

8

ź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).

9

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.

10

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.

11

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

12

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

13

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) )97.014.3()13.2(

)21.154.3(3

2

+⋅−

−

b) 323)23)23)23)((((2 +⋅+⋅+⋅+⋅+

c) 1)25.4((

3.12

2

)12.1(

1.4/3.2

5.12

5.0

−+

⋅+

+

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%;

14

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) lokata tygodniowa o oproc. 2,55%;

b) lokata miesięczna o oproc. 3%;

c) lokata kwartalna o oproc. 4,5%;

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.6. Wykreśl charakterystyki I = f(U) na podstawie wyników pomiaru zamieszczonych w tabeli.

U V 0 1,5 3,0 4,5 6,0 7,5 9,0 10,5

I mA 0 2,3 3,6 5,9 6,6 8,4 8,3 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)

15

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] Kopertkowska M.: Arkusze kalkulacyjne. Mikom, Warszawa, 2006.

[2] Masłowski K.: Funkcje w przykładach. Helion, 2007.

[3] Moran J., Hull V.: Brilliant ECDL. Pearson Education, 2005.

[4] Walkenbach J.: Excel 2010 PL. Biblia. Helion, 2010.

[5] Masłowski K.: Excel 2007/2010 PL. Ćwiczenia zaawansowane. Helion, 2011.

[6] 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)?