Informatyczne Podstawy Projektowania

Zastosowanie programu Excell

G……….. L………….

Grupa 3 (czwartek 9.15-11.00)

Wrocław 2010

1. Funkcje w Excelu

MS Excel posiada sporą ilość funkcji. Są one podzielone na kategorie, np.: matematyczne, tekstowe, finansowe, statystyczne, bazy danych itp. Kreator funkcji uruchamiamy przyciskiem wstaw funkcję znajdującym się w zakładce formuły, oferuje on nam kilkaset różnego rodzaju gotowych formuł, które znacznie przyspieszają i ułatwiają pracę w arkuszu kalkulacyjnym.

Najpopularniejsze z nich to: SUMA, ILOCZYN, JEŻELI, ŚREDNIA, a także funkcje złożeniowe np.: ŚREDNIA.JEŻELI, SUMA.JEŻELI. Funkcję możemy wywołać również bezpośrednio w komórce wpisując znak „=” i po nim nazwę funkcji. Istnieją również funkcje tablicowe, które pozwalają na operacje na kilku zbiorach wartości. Z pomocą kreatora funkcji następujące przykłady nie stanowią już dla nas żadnego problemu:

1. 

2. 

3. 

4. 

5. 

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

1. Metoda Cramera

Spróbujmy rozwiązać podany obok układ równań z trzema niewiadomymi metodą Cramera.

1. Zapisujemy lewą stronę równania w postaci macierzy i liczymy jej wyznacznik (musi być różny od 0). Korzystamy z funkcji WYZNACZNIK.MACIERZY

Det = 180

2. Zapisujemy lewą stronę równania w postaci 3 macierzy, zastępując stałe przy kolejnych współczynnikach prawą stroną równania i również liczymy wyznaczniki.

Det_x = 180

Det_y = 60

Det_z = 60

3. Ze wzorów Cramera liczymy niewiadome:

W Excelu do obliczenia niewiadomych wykorzystujemy formułę dzielenia podając współrzędne komórek, w których znajdują się obliczone wcześniej wartości wyznaczników. Np. =A1/B5

x = Det_x/Det

y = Det_y/Det

z = Det_z/Det

2. Metoda odwracania macierzy

Dany układ rozwiążmy metodą macierzy odwrotnej.

1. Zapisujemy lewą stronę równania w postaci macierzy i liczymy jej wyznacznik (musi być różny od 0). Korzystamy z funkcji WYZNACZNIK.MACIERZY

Det = 11

2. Zaznaczamy obszar o identycznych wymiarach jak macierz „a” (w tym wypadku 2x2) i za pomocą funkcji MACIERZ.ODW tworzymy macierz „a(-1)”, pamiętając o tym, że wpisaną formułę zatwierdzamy sekwencja klawiszy Ctrl + Shift + Enter. Tworzymy również macierz „b”, zawierającą liczby z prawej strony równania.

3. Kolejnym krokiem jest wymnożenie macierzy „a(-1)” przez macierz „b” (tzw. macierz wyników) za pomocą funkcji MACIERZ.ILOCZYN. Jak łatwo się domyślić wynikiem tego mnożenia będzie macierz o wymiarach 2x1, więc przed wpisaniem formuły musimy pamiętać o zaznaczeniu obszaru tej właśnie wielkości, a także (jw.) o klawiszach Ctrl + Shift + Enter. Otrzymane liczby są szukanymi niewiadomymi naszego układu równań.

3. Wykorzystanie Solvera

Solver to funkcja umożliwiająca wyznaczenie wartości szukanej zmiennej. Jeżeli zakładka Solver nie jest dostępna w menu Narzędzia kliknij w lewym górnym rogu Przycisk pakietu Office / opcje programu Excel / Dodatki / Dodatek Solver / OK.

Zapisujemy każdy wers równania w formie: a1*x1+a2*x2+a3*x3 = b , a następnie odpowiednie współczynniki wpisujemy do tabeli

W szóstej kolumnie wpisujemy w postaci formuły działanie z jej nagłówka: a1*x1+a2*x2+a3*x3, używając przy tym adresowania bezwzględnego. Jak widać w tabeli poniżej pojawiły się zera (ponieważ brakuje nam zmiennych).

Zaznaczamy komórki w kolumnie zmienne (te zielone), a następnie otwieramy narzędzie Solver. W polu Ustaw komórkę docelową podajemy zakres komórek do których wpisaliśmy wcześniej formułę (czyli 6 kolumna), zaznaczamy opcję Min, a w polu Komórki zmieniane podajemy obszar danych z pierwszej, drugiej, trzeciej i piątej kolumny. Klikamy Enter i możemy się cieszyć z rozwiązanego układu równań (wyniki w tabeli poniżej). Uwaga: jeśli Solver nie chce wyliczyć nam zmiennych, zamiast Entera używamy sekwencji klawiszy Ctrl + Shift + Enter.

4. Wykorzystanie Polecenia „szukaj wyniku”

Polecenie „szukaj wyniku” pomocne jest w przypadku gdy znamy wynik danej formuły, a nie znamy jednej z wartości wejściowej. Przeanalizujmy przykład:

Tworzymy tabelę:

W 2 kolumnie podajemy wynik naszego równania natomiast w 3 wpisujemy formułę: =(2*x+3)*x^2-10*x gdzie x to adres komórki w kolumnie „zmienna”. Otwieramy Dane / Analiza symulacji / Szukaj wyniku.

W polu „Ustaw komórkę” podajemy adres komórki z formułą, w „Wartość” wpisujemy 51, natomiast w „Zmieniając komórkę” adres naszej zmiennej, a następnie OK.

Po kilku sekundach program zwróci nam szukany wynik:

3. Wykresy w Excelu

Program Microsoft Excel obsługuje wiele typów wykresów, co ułatwia przedstawianie danych w sposób czytelny dla odbiorców. Dostępne są m.in. wykresy: kolumnowe, liniowe, kołowe, słupkowe, warstwowe, punktowe i inne.

Częstym problemem jest stosowanie wykresu liniowego zamiast punkowego. Główna różnica między nimi polega na sposobie przedstawiania danych na osi poziomej. Wykres punktowy zawsze zawiera dwie osie wartości. Jeden zestaw danych liczbowych jest wyświetlany na osi poziomej (osi wartości), a drugi — na osi pionowej (osi wartości). Na przecięciu się wartości liczbowych X i Y są wyświetlane punkty łączące te wartości w pojedyncze punkty danych. Wykres liniowy zawiera tylko jedną oś wartości (oś pionową). Na osi poziomej wykresu liniowego są przedstawiane tylko rozłożone równomiernie grupy (kategorie) danych automatycznie wygenerowane, na przykład 1, 2, 3 itd.

Przykładowe wykresy:

Rysunek 1 - wykres liniowy

Rysunek 2 - wykres punktowy

R ysunek 3 - wykres kolumnowy

Rysunek 4 - wykres kołowy

1. Wykres funkcji jednej zmiennej

Tabela 1 - funkcja jednej zmiennej

Dla funkcji: f(x)=exp(-x)+2x tworzymy tabelę z argumentami i wartościami (tak jak obok), następnie zaznaczamy obie kolumny, przechodzimy do zakładki: Wstawianie / Wykresy – wykres punktowy. Utworzony wykres możemy dowolnie modyfikować dodając tytuł, zmieniając kolory, wielkość i położenie poszczególnych elementów wykresu (jak na przykładzie niżej).

Rysunek 5 - wykres funkcji jednej zmiennej

3.2 Regresja liniowa

Regresja liniowa polega wyznaczeniu prostej (tzw. linii trendu) która jest uśrednieniem danych zaznaczonych na wykresie (najlepiej do nich pasuje). Do tego celu wykorzystywana jest metoda „najmniejszych kwadratów”, która polega na narysowaniu wokół każdego punktu takiego samego kwadratu o możliwie najmniejszym polu, takim by było możliwe poprowadzenie prostej przecinającej każdy z nich przynajmniej w jednym punkcie. Na podobnej zasadzie działa regresja nieliniowa, z tym wyjątkiem, że prostą zastępuje inna krzywa np. trend wykładniczy.

Aby wstawić linię trendu w Excelu przechodzimy (przy aktywnym obszarze wykresu) do zakładki: Układ / Linia trendu i wybieramy jeden z dostępnych rodzajów. Możemy oczywiście edytować wygląd linii wg własnego uznania (Układ / Linia trendu / Więcej opcji linii trendu.. )

P rzykład trendu liniowego (linia czerwona):

Rysunek 6 - trend liniowy

P rzykład trendu nieliniowego (linia czerwona, przerywana):

Rysunek 7 - trend nieliniowy

4. Tabele w Excelu

1. Tabele z jedną zmienną

Tabelą w Excelu możemy nazwać pewien zakres komórek (o dowolnym wymiarze), który pokazuje jak pod wpływem pewnej zmiennej zachowują się wartości w niej zawarte. Rozpatrzmy to na poniższym przykładzie. Zapisujemy pewną funkcję, a następnie tworzymy dwu kolumnową tabelę z dowolnymi danymi i skopiowaną formułą nad pustą kolumną:

Zaznaczamy całą utworzoną tabelę a następnie przechodzimy: Dane / Analiza symulacji / Tabela danych w oknie Kolumnowa komórka wejściowa (bo wynik ma być wyświetlony w wolnej kolumnie obok naszych liczb) podajemy współrzędne zmiennej (tu: x) i OK. Program wyświetli nam wynik.

Tabela 2 - tabela z jedną zmienną

2. Tabele z dwiema zmiennymi

Na podobnej zasadzie możemy skonstruować tabelę z 2 zmiennymi z tym wyjątkiem że adres jednej zmiennej podajemy w oknie Kolumnowa, a drugiej Wierszowa komórka wejściowa (tu zmienne: P, E). Przykład:

Tabela 3 - tabela z dwiema zmiennymi

5. Tworzenie NAZW w Excelu

Formuły w Excelu mają za zadanie ułatwiać pracę użytkownikowi, jednak gdy musimy napisać długą i skomplikowaną łatwo jest popełnić błąd, w celu zminimalizowania pomyłki stworzono NAZWY dzięki którym formuły staja się czytelniejsze i bardziej zrozumiałe. NAZWĘ możemy zdefiniować dla: wartości podanej w komórce; funkcji; stałej; czy tabeli. Spróbujmy utworzyć przykładową NAZWĘ. Tworzymy przykładową tabelę:

Z zakładki Formuły otwieramy Menadżer nazw i wybieramy Nowy. W polu Nazwa wpisujemy „budżet” (uwaga: nie możemy używać spacji, ani nazw zdefiniowanych w programie Excel, np. nazw funkcji), natomiast w polu Odwołuje się do: podajemy adres komórki w której zapisaliśmy kwotę naszego budżetu i klikamy OK. Następnie zdefiniujmy Nazwę: „suma_wydatków” podając w polu Odwołuje się do: formułę obliczającą sumę wydatków z naszej tabeli.

Do oceny budżetu wykorzystajmy funkcję JEŻELI, jednak zamiast podawać adresy komórek czy pisać formuły funkcji wewnętrznej użyjmy zdefiniowanych wcześniej nazw: =JEŻELI(suma_wydatków > budżet ; "przekroczyłeś budżet " ; "masz nadwyżkę! "). Po jej zastosowaniu otrzymamy komunikat:

A gdy nieco zwiększymy nasze wydatki:

SPIS TREŚCI

1. Funkcje w excelu 2

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

2.1 metoda cramera 3

2.2 metoda odwracania macierzy 4

2.3 wykorzystanie solvera 5

2.4 wykorzystanie polecenia „szukaj wyniku” 6

3. Wykresy w excelu 7

3.1 wykres funkcji jednej zmiennej 9

3.2 regresja liniowa 10

4. Tabele w excelu 11

4.1 tabele z jedną zmienną 11

4.2 tabele z dwiema zmiennymi 11

5. Tworzenie nazw w excelu 12

Spis ilUSTRACJI:

Rysunek 1 - wykres liniowy 7

Rysunek 2 - wykres punktowy 7

Rysunek 3 - wykres kolumnowy 8

Rysunek 4 - wykres kołowy 8

Rysunek 5 - wykres funkcji jednej zmiennej 9

Rysunek 6 - trend liniowy 10

Rysunek 7 - trend nieliniowy 10

sPIS TABEL

Tabela 1 - funkcja jednej zmiennej 9

Tabela 2 - tabela z jedną zmienną 11

Tabela 3 - tabela z dwiema zmiennymi 11