Informatyczne Podstawy Projektowania

Zastosowanie programu Excell

Grupa czwartek godz: 11.15-13.00

Wrocław 2010

Plan sprawozdania:

1. Funkcje w Excellu

W Excellu mamy wiele rodzajów funkcji, każda z nich składa się ze znaku „=” przed formułą, nazwy funkcji np. suma oraz listy argumentów ujętych w nawiasy. Aby wprowadzić daną funkcje do arkusza zaznaczamy komórkę, do której chcemy ją wstawić a następnie używamy kreatora funkcji uruchamianego za pomocą Wstaw/Funkcja lub za pomocą ikony . Pojawi się okno wklej funkcję, gdzie należy wybrać kategorię oraz nazwę funkcji a następnie zakres argumentów. Wyróżniamy funkcje tablicowe oraz „zwykłe”. Funkcje tablicowe potwierdza się poprzez jednoczesne naciśnięcie „CTRL+SHIFT+ENTER” natomiast funkcje zwykłe tylko przez naciśnięcie klawisza ENTER.

Możemy wyróżnić następujące rodzaje funkcji wbudowanych:

Finansowe
Data i czas
Matematyczne
Statystyczne
Daty i adresu
Bazy danych
Modułowe
Logiczne
Tekstowe
Informacyjne
Użytkownika
Inżynierskie

2. Rozwiązywanie równań i układów równań

2.1 Metoda Cramera

Aby znaleźć rozwiązania równania:

5x-3y=7
2x+y=3

należy najpierw stworzyć dwie tabelki. W jedną wpisać wartości x i y a w drugą wyrazy wolne

A			B
5	-3		7
2	1		3

następnie trzeba policzyć wyznacznik z macierzy A. Zaznaczając wyrazy w tabelce A po czym z zakładki Formuły wybrać Matem. i tryg. a następnie WYZNACZNIK.MACIERZY.

det A
11

Tworzymy tabelki Ax oraz Ay i liczymy ich wyznaczniki.

Ax			Ay
7	-3		5	7
3	1		2	3

det Ax			det Bx
16			1

Ostatecznie korzystając ze wzorów: oraz wyznaczamy niewiadome

x				y
3				1

2.2 Metoda odwracania macierzy

Wyznaczamy macierz odwrotną A(-1) do danej macierzy A korzystając z układu równań z poprzedniego przykładu. Macierz odwrotną tworzy się poprzez zaznaczenie pola, w którym ta macierz ma być wyliczona w tym przypadku pole 3x3 a następnie z zakładki Formuły wybrać Matem. i tryg. MACIERZ.ODW. W tablice należy zaznaczyć tabelkę z wyrazami macierzy A po czym nacisnąć jednocześnie „CTRL+SHIFT+ENTER”.

A(-1)
0,090909	0,272727
-0,181818	0,454545

Aby wyliczyć wyrazy wolne zaznaczamy pole w tym przypadku 2x1 i z zakładki Formuły wybieramy Matem. i tryg. MACIERZ.ILOCZYN. W tablicę1 należy zaznaczyć wyrazy macierzy A natomiast w tablicę2 wyrazy wolne B po czym nacisnąć jednocześnie „CTRL+SHIFT+ENTER”.

X
1,454545
0,090909

2.3 Wykorzystanie Solvera

Dodatek Solver znajdziemy w zakładce Dane, umożliwia on, po uprzednim wprowadzeniu równania, wyznaczenie wartości dowolnej zmiennej szukanej, gdy są dane wartości pozostałych zmiennych; bądź też wyznaczenie wartości zmiennej, przy której całe wyrażenie jest równe zeru.

Rozwiązując podany układ równań:

3x+2y+z=5
y+6z-3v=1
2x-y+4z=4
x+y-z+v=6

Tworzymy tabelkę gdzie w pola zmiennych x wpisujemy dowolne wartości, a1 są to liczby stojące przy x, a2 przy y, a3 przy z.

	a1	a2	a3	a4	zmienne x	b	a1x1+a2x2+a3x3+a4x4
1)	3	2	1	0	-2	5	5
2)	0	1	6	-3	4	1	1
3)	2	-1	4	0	3	4	4
4)	1	1	-1	1	7	6	6

W zamieszczonej tabeli mamy już obliczone niewiadome .W oknie dialogowym Solvera „komórkę celu” pozostawiamy pustą w miejsce „równa” wartość zostawiamy 0 do :komórek zmienianych” wklejamy adres zmiennych x . Musimy dodać także warunki ograniczające są to nasze ograniczenia spełniające warunki układu równań. Wpisujemy 4 warunki w „adres komórki” naszą wyliczoną wartość funkcji = „warunek ograniczający” czyli nasze b.

2.4 Wykorzystanie polecenia „szukaj wyniku”

Za pomocą „szukaj wyniku” znaleźć przynajmniej jedno rozwiązanie równania:

Tworzymy tabelkę z dowolną zmienną x a w drugą kolumnę wpisujemy działanie z użyciem tej liczby i otrzymujemy pewien wynik:

x	działanie
1	-5

Następnie z zakładki Dane wybieramy Analizę symulacji/Szukaj wyniku. Pojawi się tabelka gdzie w okno „ustaw komórkę” należy zaznaczyć pole pod działaniem, w wartość należy wpisać szukany wynik czyli 51 a w okno „zmieniając komórkę” należy zaznaczyć pole ze zmienną x. Jeżeli przykładowa zmienna x będzie dodatnia to program znajdzie dodatnie rozwiązanie równania jeśli natomiast wpiszemy liczbę ujemną to będzie szukał ujemnego rozwiązania.

x	działanie
2,9999996	50,99998

3. Wykresy w Excellu

Wykresy w programie Excell znajdują się w zakładce Wstawianie.

Wyróżniamy następujące wykresy:

Kolumnowy
Liniowy
Kołowy
Słupkowy
Warstwowy
XY(punktowy)
Giełdowy
Powierzchniowy
Pierścieniowy
Bąbelkowy
Radarowy

Wykresy punktowe i liniowe wyglądają bardzo podobnie, jednak w przypadku obu typów tych wykresów istnieje jednak duża różnica w sposobie przedstawiania danych na osiach. Wykres punktowy zawsze zawiera dwie osie wartości natomiast wykres liniowy zawiera tylko jedną oś wartości (oś pionową). Na osi poziomej wykresu liniowego są przedstawione tylko rozłożone równomiernie grupy(kategorie) danych.

Np.

Rysunek 1. Wykres liniowy

Rysunek 2. Wykres punktowy (XY)

Rysunek 3. Wykres kolumnowy

Rysunek 4. Wykres kołowy

3.1. Wykres funkcji jednej zmiennej

Tabela 1

x	y(x)
1	5
1,5	14,625
2	32
2,5	59,375
3	99
3,5	153,125
4	224
4,5	313,875
5	425

Rysunek 5. Wykres funkcji jednej zmiennej

3.2. Regresja liniowa i nieliniowa

Regresja jest to sprowadzenie zagadnienia współzależności zmiennych losowych do zależności funkcji. Linia trendu to wykres ruchomej średniej danych z serii, jest to rodzaj prognozy, którą można nanieść na wykres. Tworzymy wykres punktowy następnie dodajemy linię trendu poprzez kliknięcie prawym przyciskiem myszy na wybranej serii danych na wykresie. Z menu podręcznego wybieramy polecenie Dodaj linię trendu.

nr pomiaru	pomiar
1	0,8
2	1,4
3	2,2
4	3,5
5	5,2
6	6,1
7	7,3
8	10,2
9	11,5
10	12

Rysunek 6. Regresja liniowa

f(x)=exp(-x)+2x

x
-2
-1,5
-1
-0,5
0
0,5
1
1,5
2
2,5
3
3,5
4

Rysunek 7. Regresja nieliniowa

4. Tabele w Excellu

4.1. Tabele z jedną zmienną.

cena akcji (P)	20
zysk E=	7
P/E=	2,857143

Tworzymy tabelę gdzie wpisujemy w cenę akcji dowolne wartości następnie zaznaczamy całą tabelę i z zakładki Dane wybieramy Analiza symulacji/Tabela danych. Do komórki kolumnowej wklejamy adres ceny akcji(P).

Tabela 2

		2,857143
ceny akcji:	100	14,28571
	200	28,57143
	300	42,85714
	220	31,42857
	180	25,71429
	150	21,42857

4.2. Tabele z dwiema zmiennymi.

ceny akcji (P)	20
zysk (E)=	7
P/E=	2,857143

Tworzymy tabelę gdzie wpisujemy w cenę akcji oraz zysk dowolne wartości następnie zaznaczamy całą tabelę i z zakładki Dane wybieramy Analiza symulacji/Tabela danych. Do komórki wierszowej wklejamy adres komórki zysk(E) natomiast do kolumnowej wklejamy adres ceny akcji(P).

Tabela 3

		zysk
	2,8571429	5	7	10	20
ceny akcji	10	2	1,428571	1	0,5
	20	4	2,857143	2	1
	540	108	77,14286	54	27
	60	12	8,571429	6	3
	40	8	5,714286	4	2
	100	20	14,28571	10	5

5. Tworzenie NAZW w Excellu

Nazwy służą do nazywania funkcji lub stałych wprowadzonych do arkusza kalkulacyjnego. W efekcie formuły stają się bardziej zrozumiałe i łatwiejsze w obsłudze. Nazwę można zdefiniować dla zakresu komórek, funkcji, stałej lub tabeli. Rodzaje nazw: bezwzględna, względna, formuł, stałych.

Aby dodać nazwę wchodzimy w zakładkę Formuły/Definiuj nazwę.

Aby zobaczyć jakie nazwy zostały utworzone wybieramy Formuły/Menadżer nazw.

Np.:

	telefon	czynsz
styczeń	130	450
luty	100	500
marzec	156	550
razem	386	1500

Tworzymy nazwę „razem” wpisując w nazwę „suma_3_mies.” a w komórkę „odwołuje się do” =SUMA(adresy trzech komórek z odwołaniem względnym)

Nazwę możemy też zastosować w bardziej skomplikowanej funkcji JEŻELI

dochody:		wydatki:
		czynsz	600
pensja	2000	energia	300
prowizja	500	telefon	150
zlecone	300	paliwo	300
zasiłki	120	jedzenie	600
		ubrania	300
dochody=	2920	inne	230

		wydatki=	2480

W tym przypadku wykorzystaliśmy nazwę do utworzenia funkcji „wydatki” oraz zmiennej liczby „dochody”, która użyjemy w funkcji JEŻELI w następujący sposób:

=JEŻELI(dochody>wydatki; "nadwyżka="; "debet="). W ten sposób otrzymujemy: