Darmowy fragment publikacji:
Excel 2007 w firmie.
Controlling, finanse
i nie tylko
Autor: Sebastian Wilczewski, Maciej Wrz(cid:243)d
ISBN: 978-83-246-1292-5
Format: B5, stron: 392
Wykorzystaj mo¿liwo(cid:156)ci Excela, aby u‡atwi(cid:230) sobie pracŒ
i zarz„dzanie domowymi finansami
(cid:149) Jak wykorzystywa(cid:230) funkcje zagnie¿d¿one do obliczania premii?
(cid:149) Jak importowa(cid:230) dane finansowe z wielu (cid:159)r(cid:243)de‡?
(cid:149) Jak tworzy(cid:230) zestawienia zbiorcze z zastosowaniem sum czŒ(cid:156)ciowych?
Microsoft Office Excel 2007 to nowoczesne narzŒdzie, kt(cid:243)re mo¿na wykorzysta(cid:230) nie
tylko w biurze, ale tak¿e do sprawnego zarz„dzania finansami domowymi. Program ten
pozwala na dokonywanie zaawansowanych i skomplikowanych wyliczeæ, analiz
i zestawieæ, a poza tym posiada ogromne mo¿liwo(cid:156)ci prezentacji danych. DziŒki
zagnie¿d¿aniu funkcji mo¿e by(cid:230) stosowany do wyliczania premii lub wskazywania
pracownik(cid:243)w, kt(cid:243)rzy maj„ dosta(cid:230) nagrodŒ. W warunkach domowych przy u¿yciu Excela
mo¿esz dokona(cid:230) wyboru najlepszej oferty kredytowej lub funduszu inwestycyjnego.
(cid:132)Excel 2007 w firmie. Controling, finanse i nie tylko(cid:148) pokazuje, w jaki spos(cid:243)b korzysta(cid:230)
z programu w charakterze narzŒdzia analiz. Nie ma znaczenia, czy dopiero zaczynasz
pracŒ z Excelem, czy te¿ u¿ywa‡e(cid:156) go ju¿ wcze(cid:156)niej. Ksi„¿ka zawiera wiele
praktycznych (cid:230)wiczeæ oraz konkretnych przyk‡ad(cid:243)w wraz z obja(cid:156)nieniami, kt(cid:243)re
wskazuj„ na r(cid:243)¿ne zastosowania tego programu. Dowiesz siŒ miŒdzy innymi, w jaki
spos(cid:243)b u¿ywa(cid:230) Excela do planowania p‡ynno(cid:156)ci finansowej albo jak obliczy(cid:230) okres
zwrotu nak‡ad(cid:243)w. Nauczysz siŒ tworzy(cid:230) prezentacje danych oraz w‡asne funkcje.
Co wa¿ne, ten podrŒcznik zosta‡ skonstruowany tak, ¿eby(cid:156) m(cid:243)g‡ nie tylko
wykorzystywa(cid:230), ale i ‡„czy(cid:230) poszczeg(cid:243)lne funkcjonalno(cid:156)ci (cid:151) i w efekcie biegle porusza(cid:230)
siŒ w(cid:156)r(cid:243)d ogromnej liczby mo¿liwo(cid:156)ci tej niesamowitej aplikacji.
(cid:149) Funkcje statystyczne, logiczne i tekstowe
(cid:149) Funkcje matematyczne i trygonometryczne
(cid:149) Adresy wzglŒdne i bezwzglŒdne
(cid:149) Operacje finansowe z wykorzystaniem daty i czasu
(cid:149) Funkcje zagnie¿d¿one
(cid:149) Analiza finansowa
(cid:149) Analiza wielokryterialna
(cid:149) UdostŒpnianie skoroszytu
(cid:149) Ochrona danych przed kopiowaniem
(cid:149) Automatyzacja pracy z danymi
Excel 2007 (cid:151) praktyczny zestaw nowoczesnych rozwi„zaæ
do zarz„dzania finansami firmowymi i domowymi
Wydawnictwo Helion
ul. Ko(cid:156)ciuszki 1c
44-100 Gliwice
tel. 032 230 98 63
e-mail: helion@helion.pl
SPIS TREŚCI
Wstęp
7
1. Zastosowanie wybranych funkcji
do analizy finansowej i nie tylko
11
Funkcje statystyczne .............................................................................. 13
Wyszukiwanie wartości maksymalnej (zastosowanie
funkcji Max()) na przykładzie analizy sprzedaży ................... 13
Wyszukiwanie wartości minimalnej (zastosowanie
funkcji Min()) na przykładzie analizy sprzedaży .................... 16
Wyszukiwanie średniej (zastosowanie funkcji Średnia())
na przykładzie analizy sprzedaży ........................................... 19
Zliczanie osób i innych elementów (funkcja Licz.warunki())
spełniających dane warunki .................................................... 21
Funkcje logiczne ..................................................................................... 23
Wykonywanie obliczeń zależnych od spełnienia
innych warunków (funkcja Jeżeli()) ........................................ 23
Sprawdzanie czy dane spełniają jeden z wielu warunków
(funkcja LUB()) ......................................................................... 27
Funkcje tekstowe ................................................................................... 29
Pobieranie wybranych fragmentów tekstu
do dalszego wykorzystania (funkcja Fragment.Tekstu()) ....... 29
Łączenie kilku ciągów znaków w jeden
(funkcja Złącz.Teksty()) ............................................................ 32
Excel 2007 PL. Controlling, finanse i nie tylko
Zamiana ciągu znaków na ciąg pisany tylko małymi
literami (funkcja Litery.Małe()) ...............................................34
Zamiana ciągu znaków na ciąg pisany tylko wielkimi
literami (funkcja Litery.Wielkie()) ...........................................34
Zamiana ciągu znaków na ciąg pisany od wielkiej litery
(funkcja Z.Wielkiej.Litery()) .....................................................34
Funkcje wyszukiwania i adresu .............................................................35
Wyszukiwanie informacji o osobach, towarach
(zastosowanie funkcji Wyszukaj.pionowo())
na przykładzie przygotowania formatki faktury ....................35
Funkcje matematyczne i trygonometryczne ..........................................40
Obliczanie sumy (zastosowanie funkcji Suma())
na przykładzie analizy sprzedaży ...........................................40
Sumowanie tylko tych elementów, które spełniają
zadane kryteria (zastosowanie funkcji Suma.jeżeli()),
na przykładzie analizy sprzedaży ...........................................42
Zaokrąglanie danych finansowych
(zastosowanie funkcji Zaokr()) .................................................45
2. Adresy względne i bezwzględne
49
Adresy względne .....................................................................................50
Adresy bezwzględne ...............................................................................56
3. Operacje finansowe z wykorzystaniem
daty i czasu
65
Data i godzina jako część zestawień finansowych ................................66
Właściwa prezentacja daty i czasu w arkuszach
programu Microsoft Office Excel 2007 ...................................71
Obliczenia prowadzone na dacie i czasie ...................................79
Funkcje kategorii Data i godzina ................................................81
4. Funkcje zagnieżdżone
101
Kilka słów o zagnieżdżaniu funkcji .....................................................102
Wykorzystywanie zagnieżdżeń funkcji do wyliczania premii ............103
Wykorzystanie funkcji zagnieżdżonych do wskazania
pracowników, którzy mają otrzymać nagrodę jubileuszową ..........108
Kilka dodatkowych informacji .............................................................112
5. Pobieranie danych finansowych
z różnych źródeł
113
Import danych finansowych z wielu źródeł ........................................114
Dostęp do danych opublikowanych na stronach internetowych ........124
Pobieranie informacji z programu Microsoft Outlook .........................133
4
Spis treści
6. Analiza finansowa
141
Wykonywanie działań analitycznych na danych z wielu arkuszy
na przykładzie sumowania sprzedaży z różnych okresów ............. 142
Konsolidacja danych na przykładzie analizy czasu pracy
poszczególnych pracowników w poszczególnych miesiącach
i przy poszczególnych projektach ..................................................... 145
Filtrowanie danych .............................................................................. 150
Stosowanie autofiltru na przykładzie analizy informacji
o sprzedaży ............................................................................ 150
Stosowanie filtru zaawansowanego na przykładzie
analizy informacji o pracownikach ....................................... 157
Zliczanie obiektów występujących w bazie za pomocą funkcji
Bd.ile.rekordów.A() na przykładzie analizy sprzedaży .................... 162
Tworzenie zestawień zbiorczych — zastosowanie
sum częściowych .............................................................................. 165
Tworzenie elastycznych analiz za pomocą tabel
i wykresów przestawnych ................................................................ 172
Wykorzystanie tabel przestawnych ......................................... 172
Wykorzystanie wykresów przestawnych ................................ 179
7. Właściwa prezentacja
danych finansowych
183
Formatowanie danych finansowych ................................................... 184
Formatowanie niestandardowe wykorzystywane w finansach
i controllingu ..................................................................................... 201
Formatowanie warunkowe .................................................................. 206
Budowa wykresów w oparciu o dane finansowe ................................ 217
Przebudowa wykresów na potrzeby analizy finansowej .................... 223
8. Funkcje finansowe
239
Przyszła wartość inwestycji — zastosowanie funkcji FV() ................. 240
Dyskontowanie wartości — zastosowanie funkcji PV() ...................... 245
Stopa zwrotu ........................................................................................ 248
Stopa zwrotu z kapitału (ROI) .................................................. 248
Stopa zwrotu z kapitału własnego (ROE) ................................. 248
Wewnętrzna stopa zwrotu — zastosowanie funkcji IRR() ...... 248
Wyliczanie raty kredytowej i analiza oprocentowania ...................... 251
Obliczanie raty kredytu przy ratach równych
— zastosowanie funkcji PMT() .............................................. 251
Obliczanie raty kredytu przy ratach malejących ..................... 253
Efektywna i nominalna stopa procentowa .............................. 257
Sposoby wyliczania amortyzacji .............................................. 260
5
Excel 2007 PL. Controlling, finanse i nie tylko
9. Zagadnienia optymalizacji danych
267
Analiza „co jeżeli” w przypadku danych finansowych ........................269
Analiza wrażliwości poprzez tabele danych ........................................274
Analiza wielowariantowa z wykorzystaniem scenariuszy .................281
Optymalizacja danych przy wielu niewiadomych ...............................288
Analiza danych .....................................................................................301
Histogram ..................................................................................301
Korelacja ....................................................................................303
10. Zarządzanie płynnością finansową
oraz segmentami rynku
309
Wydzielanie rejonów rynku .................................................................310
Analiza wielokryterialna ......................................................................319
Planowanie płynności finansowej ........................................................326
11. Analiza inwestycji
333
Obliczanie okresu zwrotu nakładów ...................................................334
Określenie bieżącej i przyszłej wartości inwestycji
— funkcja NPV() ................................................................................336
Wybór najkorzystniejszej inwestycji — indeks zyskowności .............338
12. Praca wielu osób na jednym skoroszycie 341
Udostępnianie skoroszytu ....................................................................342
Śledzenie zmian w udostępnionym skoroszycie ..................................346
Akceptacja i odrzucanie zmian ............................................................350
Ochrona skoroszytu ..............................................................................351
Ochrona arkusza ...................................................................................353
Nadawanie uprawnień do komórek .....................................................356
Zabezpieczenie pliku hasłem ................................................................358
Ochrona danych przed drukowaniem, kopiowaniem
i dystrybucją ......................................................................................359
13. Wykorzystanie makra i własnych funkcji
do usprawnienia analiz finansowych
363
Tworzenie funkcji finansowych ...........................................................364
Automatyzacja pracy z danymi — wykorzystanie makr ....................372
Rejestracja makra ......................................................................372
Skorowidz
383
6
9
ZAGADNIENIA
OPTYMALIZACJI
DANYCH
Ten rozdziaï ma na celu prezentacjÚ moĝliwoĂci analitycznych
programu Microsoft Office Excel 2007. Program nie tylko umoĝ-
liwia obliczanie i wïaĂciwÈ prezentacje danych, pozwala równieĝ
przy wykorzystaniu odpowiednich narzÚdzi obliczyÊ równania
z jednÈ oraz z wieloma niewiadomymi. Moĝliwe jest takĝe wielo-
wariantowe prezentowanie danych finansowych.
Z tego rozdziaïu dowiesz siÚ:
Q w jaki sposób przeprowadzaÊ analizÚ „co jeĝeli”
i wyszukiwaÊ rozwiÈzañ równañ z jednÈ niewiadomÈ,
Q jak moĝna wykorzystaÊ program Microsoft Office Excel
2007 do analizy wraĝliwoĂci,
Q jak ïatwo prowadziÊ analizÚ wielowariantowÈ,
Q w jaki sposób obliczaÊ równania z wieloma niewiadomymi,
Excel 2007 PL. Controlling, finanse i nie tylko
Q jakie narzÚdzia analizy statystycznej dostÚpne
sÈ w programie.
Program Microsoft Office Excel 2007 poza szerokimi moĝliwo-
Ăciami obliczania i prezentowania danych finansowych pozwala
na bardzo zaawansowanÈ ich analizÚ. WykorzystujÈc znajdujÈcÈ
siÚ na wstÈĝce zakïadkÚ Dane, uzyskuje siÚ dostÚp do narzÚdzi sïu-
ĝÈcych do przeprowadzania optymalizacji. NarzÚdzia te znajdujÈ
siÚ w dwóch sekcjach: NarzÚdzia danych oraz Analiza. Ta druga
sekcja domyĂlnie nie jest wïÈczona. Aby uzyskaÊ dostÚp do narzÚ-
dzi Analiza danych oraz Solver, naleĝy zainstalowaÊ odpowiednie
dodatki. Moĝna to wykonaÊ poprzez przejĂcie do polecenia Opcje
programu Excel znajdujÈcego siÚ w rozwijanym menu otwieranym
przyciskiem pakietu Office.
Aby zainstalowaÊ dodatki umoĝliwiajÈce optymalizacjÚ danych,
naleĝy:
1. WybraÊ przycisk pakietu Office (jedyny przycisk menu
w nowym interfejsie Excela 2007), a nastÚpnie wskazaÊ
polecenie Opcje programu Excel, co prezentuje rysunek 9.1.
Rysunek 9.1.
Wybór
przycisku
programu
Excel
268
Rozdziaï 9. Zagadnienia optymalizacji danych
2. W oknie Opcje programu Excel przejĂÊ do sekcji Dodatki
w lewej czÚĂci okna — zostanÈ zaprezentowane aktywne
i nieaktywne dodatki programu.
3. W sekcji ZarzÈdzaj wybraÊ z listy rozwijanej Dodatki
programu Excel, a nastÚpnie wcisnÈÊ przycisk Przejdě….
4. Po otwarciu okna Dodatki zaznaczyÊ przycisk opcji
przy dodatkach: Analysis ToolPak oraz Dodatek Solver,
po czym wybraÊ przycisk OK (rysunek 9.2).
Rysunek 9.2.
Okno Dodatki
5. Dodatki zostanÈ zainstalowane i umieszczone na zakïadce
Dane w sekcji Analiza.
Analiza „co jeĝeli”
w przypadku danych
finansowych
JednÈ z podstawowych funkcjonalnoĂci zwiÈzanych z optymali-
zacjÈ danych jest moĝliwoĂÊ symulowania sytuacji „co jeĝeli”,
a wiÚc rozwiÈzywania równañ z jednÈ niewiadomÈ. Co stanie
siÚ z zyskiem firmy, jeĂli zmniejszona zostanie marĝa, bÈdě jak
bÚdÈ wyglÈdaïy koszty przedsiÚwziÚcia przy uĝyciu droĝszych
269
Excel 2007 PL. Controlling, finanse i nie tylko
materiaïów wykoñczeniowych — to podstawowe pytania, na które
firmy muszÈ odpowiadaÊ codziennie.
Doskonaïym narzÚdziem uïatwiajÈcym takÈ analizÚ danych jest
polecenie Szukaj wyniku, które w ïatwy sposób umoĝliwia odnale-
zienie wskazanej wartoĂci niewiadomej przy okreĂlonych wszyst-
kich pozostaïych wartoĂciach.
Polecenie umieszczone jest na zakïadce Dane w sekcji NarzÚdzia
danych pod listÈ rozwijanÈ Analiza symulacji. Po wyborze pole-
cenia Szukaj wyniku otwiera siÚ okno z takÈ samÈ nazwÈ zawie-
rajÈce trzy argumenty do wypeïnienia (rysunek 9.3).
Rysunek 9.3.
Okno Szukanie
wyniku
Argumenty oznaczajÈ kolejno:
Q Ustaw komórkÚ — w tym polu moĝliwe jest wyïÈcznie
wstawiania adresu komórki, która musi byÊ wypeïniona
formuïÈ; jest to komórka, której wynik znamy.
Q WartoĂÊ — miejsce, gdzie naleĝy wpisaÊ, jakÈ wartoĂÊ ma
przyjÈÊ komórka okreĂlona w polu Ustaw komórkÚ, moĝe
to byÊ zarówno liczba ujemna, jak i dodatnia.
Q ZmieniajÈc komórkÚ — pole, w którym naleĝy wstawiÊ
komórkÚ, która jest niewiadomÈ rozwiÈzywanego
równania, np. komórka zawierajÈca zmienianÈ marĝÚ.
Aby wykorzystaÊ polecenie Szukaj wyniku, naleĝy:
1. WstawiÊ formuïÚ obliczajÈcÈ równanie, np. wartoĂÊ
zamówienia na okreĂlonÈ liczbÚ produktów przy zaïoĝonej
marĝy, jak na rysunku 9.4.
2. Formuïa powinna wyglÈdaÊ nastÚpujÈco: =C3*(1+C4)*C5.
270
Rozdziaï 9. Zagadnienia optymalizacji danych
Rysunek 9.4.
Przykïadowe
dane finansowe
3. PrzyjmujÈc, ĝe WartoĂÊ zamówienia powinna osiÈgnÈÊ
3000 zï, obliczyÊ, jaka powinna byÊ narzucona Marĝa.
4. PrzejĂÊ w zakïadce Dane do listy rozwijanej Analiza
symulacji i wybraÊ polecenie Szukaj wyniku.
5. W oknie Szukanie wyniku wpisaÊ wartoĂci jak
na rysunku 9.5, co pozwoli osiÈgnÈÊ zaïoĝonÈ WartoĂÊ
zamówienia.
Rysunek 9.5.
WartoĂci
w oknie
Szukanie
wyniku
6. Po wyborze przycisku OK wartoĂÊ komórki C7 zostanie
ustawiona na 3000 zï, a wysokoĂÊ Marĝy powinna
osiÈgnÈÊ 19 .
7. Dodatkowo dostÚpne bÚdzie jeszcze okno Stan szukania
wyniku, dziÚki któremu moĝna zaakceptowaÊ bÈdě
odwoïaÊ wyliczone wartoĂci.
8. Po naciĂniÚciu przycisku OK wyliczone wartoĂci zostanÈ
zachowane w poszczególnych komórkach.
mwiczenie 9.1.
WykorzystujÈc plik 9_1.xlsx, obliczyÊ wartoĂÊ raty kredytu inwe-
stycyjnego, a nastÚpnie okreĂliÊ, jak dïugi powinien byÊ okres spïa-
cania kredytu, biorÈc pod uwagÚ, ĝe wysokoĂÊ spïacanej raty nie
moĝe przekroczyÊ 1200 zï miesiÚcznie.
271
Excel 2007 PL. Controlling, finanse i nie tylko
Plik 9_1.xlsx zawierajÈcy podstawÚ do tego Êwiczenia jest
udostÚpniony wraz z innymi materiaïami dotyczÈcymi
ksiÈĝki pod adresem: ftp://ftp.helion.pl/przyklady/
´ex27wf.zip.
Aby obliczyÊ ratÚ kredytu, a nastÚpnie wskazaÊ, jak dïugo bÚdzie
on spïacany przy racie kapitaïowej w wysokoĂci 1200 zï, naleĝy:
1. W programie Microsoft Office Excel 2007 otworzyÊ plik
9_1.xlsx.
2. W arkuszu Kredyt przejĂÊ do komórki F13.
3. WybraÊ z wstÈĝki zakïadkÚ Formuïy, a nastÚpnie z listy
rozwijanej Finansowe wstawiÊ funkcjÚ PMT.
4. OkreĂliÊ w oknie funkcji poszczególne argumenty,
jak na rysunku 9.6, wprowadzajÈc znak minus
w argumencie Wa, aby wartoĂÊ raty byïa liczbÈ dodatniÈ,
i wcisnÈÊ przycisk OK.
Rysunek 9.6.
Argumenty funkcji PMT
5. Wyliczona rata jest zbyt wysoka, wiÚc z zakïadki Dane
w sekcji NarzÚdzia danych wybraÊ listÚ Analiza symulacji,
a nastÚpnie polecenie Szukaj wyniku.
272
Rozdziaï 9. Zagadnienia optymalizacji danych
6. W oknie Szukanie wyniku w polu Ustaw komórkÚ
wpisaÊ F13.
7. W polu WartoĂÊ zdefiniowaÊ wysokoĂÊ raty na 1200,
a w polu ZmieniajÈc komórkÚ wpisaÊ adres F10 — okno
polecenia prezentuje rysunek 9.7 — i wcisnÈÊ OK.
Rysunek 9.7.
Wypeïnione
okno Szukanie
wyniku
8. PotwierdziÊ wprowadzone obliczenia kolejnym
przyciskiem OK.
9. Wynik Êwiczenia wskazuje na 113,5 rat, a wiÚc po 114
ratach (czyli po przeszïo 9 latach) nastÈpi spïata caïoĂci
zaciÈgniÚtego kredytu inwestycyjnego.
Efekt Êwiczenia prezentuje rysunek 9.8. Wynik Êwiczenia zostaï
zapisany w pliku o nazwie 9_1_wynik.xlsx.
Rysunek 9.8.
Wynik
Êwiczenia
273
Excel 2007 PL. Controlling, finanse i nie tylko
Analiza wraĝliwoĂci
poprzez tabele danych
Analiza wraĝliwoĂci jest jednÈ z podstawowych funkcjonalnoĂci
programu Microsoft Office Excel 2007, jeĂli zacznie siÚ rozpa-
trywaÊ jego moĝliwoĂci analityczne, szczególnie w perspektywie
finansowej. Otóĝ analiza wraĝliwoĂci pozwala ustaliÊ próg ren-
townoĂci inwestycji przy zmieniajÈcych siÚ czynnikach. Po pro-
stu moĝliwe jest zaobserwowanie, w jaki sposób dane wyjĂciowe
zawarte w arkuszu zostanÈ zmienione w przypadku zmiany wska-
zanych danych wejĂciowych umieszczonych w tym arkuszu.
Analiza wraĝliwoĂci moĝe byÊ wykorzystywana we wszystkich
tych wyliczeniach, które majÈ wskazaÊ, przy jakim poziomie
kosztu jednostkowego i ceny opïacalne jest wejĂcie na rynek lub
przy jakim koszcie zmiennym warto prowadziÊ dziaïalnoĂÊ. Ana-
liza wraĝliwoĂci pozwala ustaliÊ jeden bÈdě wiele czynników
zmiennych (o których szerzej w rozdziale 10.), np. przy jakim
oprocentowaniu i okresie spïaty rat najlepiej wziÈÊ kredyt in-
westycyjny.
W przypadku pracy z analizÈ wraĝliwoĂci w programie Microsoft
Office Excel 2007 idealnym narzÚdziem jest polecenie Tabela
danych. Umoĝliwia ono tablicowanie wzorów matematycznych
wedïug wskazanego szablonu. Polecenie Tabela danych umiesz-
czone jest na wstÈĝce w zakïadce Dane i, podobnie jak w przy-
padku Szukaj wyniku, równieĝ naleĝy je wybraÊ z listy Analiza
symulacji.
Tabela danych umoĝliwia stablicowanie jednego wzoru do zazna-
czanego zakresu, moĝe to byÊ jedna lub wiele kolumn, waĝne jest
jednak, aby w lewej górnej komórce zaznaczonego zakresu poja-
wiïa siÚ formuïa, która ma zostaÊ stablicowana. Dodatkowo
w przypadku wpisywania formuïy dla argumentów, które bÚdÈ
zmienne w tabeli, naleĝy wykorzystaÊ komórki zewnÚtrzne w sto-
sunku do caïej póěniej zaznaczonej tabeli. W momencie urucho-
mienia polecenia Tabela danych program wstawi po prostu do
274
Rozdziaï 9. Zagadnienia optymalizacji danych
zaznaczonych komórek odpowiedni wynik formuïy ze wskaza-
nymi argumentami, a ĝeby mógï on obliczyÊ wynik, musi podsta-
wiÊ zmienne argumenty do wolnych komórek.
Okno Tabela danych zawiera dwa pola:
Q Wierszowa komórka wejĂciowa — odpowiada za wartoĂci
w tabeli danych umieszczone w pierwszym wierszu.
Q Kolumnowa komórka wejĂciowa — odpowiada za wartoĂci
tabeli umieszczone w pierwszej kolumnie.
Aby wykorzystaÊ polecenie Tabela danych dla prostego wyliczenia
tabliczki mnoĝenia, naleĝy:
1. W programie Microsoft Office Excel 2007 zbudowaÊ
kolumnÚ oraz wiersz zawierajÈce komórki wypeïnione
kolejnymi liczbami od 1 do 10, co prezentuje rysunek 9.9.
Rysunek 9.9.
Podstawa
do wyliczenia
tabliczki
mnoĝenia
2. NastÚpnie na przeciÚciu siÚ kolumn i wierszy tabeli,
czyli w komórce A1, wstawiÊ formuïÚ pozwalajÈcÈ
na wyliczenie poszczególnych wyników mnoĝenia,
wykorzystujÈc w tej formule komórki zewnÚtrzne
w stosunku do tabeli danych.
3. Przykïadowa formuïa moĝe wyglÈdaÊ nastÚpujÈco: =A15*B15.
4. ZaznaczyÊ caïy zakres, w którym ma zostaÊ stablicowany
wzór mnoĝenia, a wiÚc od A1 do K10.
5. PrzejĂÊ na wstÈĝce do zakïadki Dane i wybraÊ z listy
rozwijanej Analiza symulacji polecenie Tabela danych.
275
Excel 2007 PL. Controlling, finanse i nie tylko
6. WypeïniÊ okno Tabela danych wartoĂciami
jak na rysunku 9.10 i wcisnÈÊ przycisk OK.
Rysunek 9.10.
Okno Tabela
danych
7. Program wyliczy wynik, który powinien wyglÈdaÊ
jak na rysunku 9.11.
Rysunek 9.11.
Wynik Tablicy
mnoĝenia
Aby skorzystaÊ z tablicowania wzorów w przypadku analizy wraĝ-
liwoĂci, czÚsto wykorzystuje siÚ wyïÈcznie jednÈ zmiennÈ (wiele
zmiennych to analiza wielowariantowa, o czym szerzej w roz-
dziale 10.). Analiza wraĝliwoĂci moĝe zostaÊ zaprezentowana na
podstawie nastÚpujÈcego prostego przykïadu: firma sprzedaje
lizaki po 1 zï za sztukÚ, ich koszt jednostkowy wynosi 50 gr. Popyt
ksztaïtuje siÚ na poziomie 3000 sztuk, jednak cena jest równieĝ
czynnikiem wpïywajÈcym na jego wielkoĂÊ: otóĝ popyt obniĝa siÚ
o wartoĂÊ iloczynu 1000 sztuk i aktualnej ceny. Firma chce zoba-
czyÊ, jaka cena dla takiego towaru bÚdzie najbardziej adekwatna
na rynku, zaczynajÈc badanie od 50 gr, a koñczÈc na 3 zï (ze sko-
kiem o 25 gr). Aby wykorzystaÊ tablicowanie wzorów w takiej
analizie wraĝliwoĂci, naleĝy:
1. ZbudowaÊ wïaĂciwy model dla wskazanych danych,
w nowym pliku programu Microsoft Office Excel 2007
wstawiÊ w komórkÚ B1 sïowo Cena, w komórkÚ B2 sïowo
Koszt zmienny, w komórce B3 umieĂciÊ sïowo Popyt,
a w komórce B5 Zysk.
276
Rozdziaï 9. Zagadnienia optymalizacji danych
2. W komórce C1 umieĂciÊ cenÚ 1 zï, w komórce C2 koszt
jednostkowy 0,5 zï.
3. W komórce C3 wprowadziÊ formuïÚ wyliczajÈcÈ wielkoĂÊ
popytu, tj. =3000–1000*C1.
4. W komórce C4 wyliczyÊ wskaěnik zysku =C1*C3–C2*C3.
5. PrzejĂÊ do komórki B10 i wprowadziÊ liczbÚ 0,5 zï.
6. W nastÚpnej komórce wstawiÊ liczbÚ 0,75 zï.
7. ZaznaczyÊ obie liczby i przeciÈgnÈÊ do doïu
aĝ do otrzymania wyniku 3 zï.
8. W komórce C9 wprowadziÊ formuïÚ obliczajÈcÈ Zysk,
czyli ponowiÊ formuïÚ z komórki C4, a wiÚc =C1*C3–C2*C3.
9. Arkusz programu powinien wyglÈdaÊ jak na rysunku 9.12.
Rysunek 9.12.
Analiza
wraĝliwoĂci
ceny lizaka
10. ZaznaczyÊ zakres komórek od B9 do C20.
11. Na wstÈĝce wybraÊ zakïadkÚ Dane, a nastÚpnie z listy
rozwijanej Analiza symulacji polecenie Tabela danych.
12. PodaÊ wyïÈcznie jednÈ zmiennÈ dla kolumnowej komórki
wejĂciowej — bÚdzie to komórka zawierajÈca w formule
cenÚ, a wiÚc C1.
277
Excel 2007 PL. Controlling, finanse i nie tylko
13. Po klikniÚciu przycisku OK program stablicuje wzór
zawarty w komórce C7, wstawiajÈc do poszczególnych
komórek jako zmiennÈ cenÚ za sztukÚ lizaka.
14. Wyniki prezentuje rysunek 9.13.
Rysunek 9.13.
Analiza
wraĝliwoĂci
ceny lizaka
15. Dokïadnie wiÚc moĝna okreĂliÊ, iĝ przy powyĝszych
zaïoĝeniach najbardziej poĝÈdanÈ cenÈ bÚdzie 1,75 zï
za sztukÚ.
mwiczenie 9.2.
WykorzystujÈc plik 9_2.xlsx wyliczyÊ akceptowalny poziom kosztu
zmiennego umoĝliwiajÈcy maksymalizacjÚ miesiÚcznego zysku
z produkcji woreczków do lodów przy niezmiennych pozostaïych
wartoĂciach.
Plik 9_2.xlsx zawierajÈcy podstawÚ do tego Êwiczenia jest
udostÚpniony wraz z innymi materiaïami dotyczÈcymi ksiÈĝki
pod adresem: ftp://ftp.helion.pl/przyklady/ex27wf.zip.
Aby dokonaÊ wïaĂciwego obliczenia poziomu kosztu, naleĝy:
1. OtworzyÊ plik o nazwie 9_2.xlsx i przejĂÊ do komórki C6.
2. WstawiÊ formuïÚ obliczajÈcÈ przychód ze sprzedaĝy, czyli
iloczyn ceny i popytu: =C4*C1.
278
Rozdziaï 9. Zagadnienia optymalizacji danych
3. W komórce C8 umieĂciÊ formuïÚ wyliczajÈcÈ wartoĂÊ
zysku brutto, czyli przychodu pomniejszonego o koszty
zmienne, a wiÚc =C6–C4*C2.
4. W komórce C12 powinna siÚ znaleěÊ formuïa wskazujÈca
na zysk operacyjny, czyli pomniejszenie zysku brutto
o koszty staïe, a wiÚc =C8–C10.
5. Natomiast w komórce C16 wpisaÊ wyliczenie
uwzglÚdniajÈce podatek do zapïacenia, czyli formuïÚ
=C12–C12*C14.
6. Dla wyliczenia zysku wykorzystanego w tablicy wstawiÊ
w komórce F3 formuïÚ wskazujÈcÈ na wartoĂÊ przychodu =C6.
7. W komórce G3 podobnie wskazaÊ na zysk operacyjny
z komórki C12, a w komórce H3 na zysk netto umieszczony
w komórce C16.
8. W komórce I3 wstawiÊ formuïÚ obliczajÈcÈ wartoĂÊ
caïkowitÈ kosztów, a wiÚc =C10+C4*C2.
9. Wypeïniony arkusz powinien wyglÈdaÊ jak na rysunku 9.14.
Rysunek 9.14. Analiza wraĝliwoĂci kosztów
279
Excel 2007 PL. Controlling, finanse i nie tylko
10. ZaznaczyÊ zakres komórek od E4 do I24.
11. Na wstÈĝce wybraÊ zakïadkÚ Dane, a nastÚpnie z listy
rozwijanej Analiza symulacji polecenie Tabela danych.
12. PodaÊ wyïÈcznie jednÈ zmiennÈ dla kolumnowej komórki
wejĂciowej — bÚdzie to komórka zawierajÈca w formule
cenÚ, a wiÚc C2.
13. Po klikniÚciu przycisku OK program stablicuje wzór
zawarty w komórkach F3, G3, H3 oraz I3, wstawiajÈc
do nich jako zmiennÈ koszt jednostkowy.
14. W komórce H30 wstawiÊ funkcjÚ wyliczajÈcÈ maksymalnÈ
wartoĂÊ z zakresu H4:H24 — formuïa wyglÈda nastÚpujÈco:
=max(H4:H24).
15. Wyniki Êwiczenia prezentuje rysunek 9.15.
Rysunek 9.15. Analiza wraĝliwoĂci ceny lizaka
16. Maksymalny zysk przy okreĂlonych warunkach wynosi
wiÚc 11340 zï, w zwiÈzku z tym optymalnym poziomem
kosztu zmiennego jest 1 zï.
Wynik Êwiczenia zostaï zapisany w pliku 9_2_wynik.xslx.
280
Rozdziaï 9. Zagadnienia optymalizacji danych
Analiza wielowariantowa
z wykorzystaniem
scenariuszy
Kolejnym bardzo przydatnym elementem w programie Microsoft
Office Excel 2007 jest Menedĝer scenariuszy, który umoĝliwia
przeprowadzenie wielowariantowej analizy danych. Za pomocÈ
scenariuszy bardzo ïatwo moĝna zbudowaÊ kilka wariantów dzia-
ïalnoĂci firmy, np. optymistyczne przychody, zakïadane przychody,
oraz sprawdzaÊ, jak przy zdefiniowanych argumentach bÚdÈ nastÚ-
powaïy zmiany. DziÚki scenariuszom w ïatwy sposób moĝna za-
prezentowaÊ kilka wariantów tej samej sytuacji, wybierajÈc tÚ
najbardziej realnÈ i odpowiedniÈ. Dla przykïadu moĝna zbudowaÊ
model prezentujÈcy zarobki firmy ubezpieczeniowej w zaleĝnoĂci
od liczby zatrudnionych agentów ubezpieczeniowych, zmieniajÈc
w scenariuszach liczbÚ agentów i sprawdzajÈc, na jakim poziome
ksztaïtowaÊ siÚ bÚdÈ koszty i przychody.
OczywiĂcie aby dobrze wykorzystaÊ funkcjonalnoĂÊ scenariuszy,
najwaĝniejsze jest zbudowanie wïaĂciwego modelu do analizy.
Dopiero po tym podaje siÚ, które komórki wyliczajÈce wartoĂci
w modelu bÚdÈ zmiennymi dla poszczególnych scenariuszy. Roz-
wiÈzanie to jest rozwiÈzaniem odwrotnym do polecenia Szukaj
wyniku — tam wiadomo byïo, jaki jest ostateczny wynik, nale-
ĝaïo wyliczyÊ wyïÈcznie wartoĂÊ argumentu, który na ten wynik
wpïywa, w przypadku scenariuszy znane sÈ argumenty umoĝli-
wiajÈce obliczenie wyniku.
Po zbudowaniu modelu moĝna przystÈpiÊ do tworzenia scenariu-
szy. Polecenie Menedĝer scenariuszy umieszczone jest na wstÈĝce
w zakïadce Dane jako ostatnie na liĂcie Analiza symulacji. Po
wyborze polecenia otwarte zostanie okno o nazwie Menedĝer sce-
nariuszy, w którym moĝliwe jest zarzÈdzanie wszystkimi dostÚp-
nymi scenariuszami. Wybranie przycisku Dodaj… spowoduje
otwarcie nowego okna o nazwie Dodawanie scenariusza (rysunek
281
Excel 2007 PL. Controlling, finanse i nie tylko
9.16), w którym wpisuje siÚ nazwÚ scenariusza oraz zaznacza ko-
mórki zmieniane, po czym okreĂla wartoĂci zaznaczonych komórek
dla dodawanego scenariusza.
Rysunek 9.16. Okna Dodawanie scenariusza
W celu zaznaczenia kilku komórek leĝÈcych w róĝnych
miejscach arkusza moĝna wykorzystaÊ umieszczony
na klawiaturze przycisk Ctrl w poïÈczeniu z lewym
przyciskiem myszy. Po prostu po wyborze pierwszej
komórki wcisnÈÊ klawisz Ctrl i kolejno lewym przyciskiem
myszy dodawaÊ zaznaczenie.
Kiedy zostanÈ dodane wszystkie scenariusze, moĝna je pojedyn-
czo wyĂwietlaÊ, wykorzystujÈc do tego celu przycisk Pokaĝ, jak
i dodaÊ za pomocÈ przycisku Podsumowanie… nowy arkusz z pod-
sumowaniem wszystkich dostÚpnych scenariuszy, wartoĂciami
poszczególnych komórek oraz wynikami komórek docelowych.
Podsumowanie moĝe mieÊ format tabeli przestawnej bÈdě zwy-
kïego podsumowania danych.
Aby dodaÊ scenariusze pozwalajÈce wyliczyÊ zwrot z inwestycji
w lokatÚ terminowÈ (z kapitaïem poczÈtkowym 1000 zï, na 6,5
rocznie) przy okresie oszczÚdzania piÚÊ lat i przy róĝnych warto-
Ăciach wpïacanych rat (50, 100, 500, 1000 zï), naleĝy:
282
Rozdziaï 9. Zagadnienia optymalizacji danych
1. W programie Microsoft Office Excel 2007 zbudowaÊ
wïaĂciwy model, np. w komórce B1 wstawiÊ 1000 zï,
w komórce B3 wpisaÊ 6,5 , w B5 wprowadziÊ formuïÚ
=5*12, a w B7 umieĂciÊ wartoĂÊ podstawowÈ raty,
czyli 100 zï.
2. W komórce B9 wstawiÊ funkcjÚ FV, wykorzystujÈc
zakïadkÚ Formuïy i listÚ rozwijanÈ Finansowe.
3. OkreĂliÊ poszczególne argumenty funkcji (dziÚki znakom
minus dla Raty i Wa wyĂwietlony zostanie dodatni wynik
funkcji) jak na rysunku 9.17 i wcisnÈÊ OK.
Rysunek 9.17.
Argumenty
funkcji FV
4. PrzejĂÊ do zakïadki Dane i z listy rozwijanej Analiza
symulacji wskazaÊ polecenie Menedĝer scenariuszy,
a nastÚpnie wybraÊ przycisk Dodaj….
5. W oknie Dodawanie scenariusza w polu Nazwa
scenariusza wpisaÊ Podstawowy, a w komórki zmieniane
wstawiÊ B7 i kliknÈÊ przycisk OK.
6. W oknie WartoĂci scenariusza nie zmieniaÊ wartoĂci,
pierwszy scenariusz jest podstawÈ.
7. DodaÊ kolejny scenariusz za pomocÈ przycisku Dodaj.
8. NadaÊ scenariuszowi nazwÚ Pesymistyczny i nie zmieniajÈc
adresu komórki, potwierdziÊ przyciskiem OK.
9. W oknie WartoĂci scenariusza wprowadziÊ wartoĂÊ 50.
283
Excel 2007 PL. Controlling, finanse i nie tylko
10. DodaÊ dwa kolejne scenariusze o nazwie Ostroĝny
i Optymistyczny oraz odpowiadajÈce im wartoĂci
— 500 i 1000.
11. Po wprowadzeniu ostatniego scenariusza potwierdziÊ
przyciskiem OK.
12. Okno Menedĝer scenariuszy powinno wyglÈdaÊ jak
na rysunku 9.18.
Rysunek 9.18.
Okno Menedĝer
scenariuszy
13. Moĝliwe jest teraz przeglÈdanie wyników obliczeñ funkcji
FV dla poszczególnych scenariuszy, wystarczy zaznaczyÊ
odpowiedni scenariusz i wybraÊ przycisk Pokaĝ.
14. Ostatnim krokiem bÚdzie zbudowanie podsumowania.
15. Po wybraniu przycisku Podsumowanie… otwarte zostanie
okno Podsumowanie scenariuszy, w którym naleĝy
zaznaczyÊ opcje jak na rysunku 9.19 i wcisnÈÊ przycisk OK.
Rysunek 9.19.
Wybór sposobu
wstawienia
podsumowania
284
Rozdziaï 9. Zagadnienia optymalizacji danych
16. Zostanie dodany nowy arkusz zawierajÈcy poszczególne
wartoĂci komórek zmienianych oraz wynik funkcji FV,
co prezentuje rysunek 9.20.
Rysunek 9.20. Arkusz Podsumowanie scenariuszy
Warto zauwaĝyÊ, iĝ w podsumowaniu scenariuszy zarówno
w kolumnach, jak i w wierszach dostÚpny jest przycisk rozwija-
jÈcy i zwijajÈcy zgrupowane informacje.
mwiczenie 9.3.
WykorzystujÈc plik 9_3.xlsx, oblicz zysk netto, dodaj cztery scena-
riusze zawierajÈce wskazane w arkuszu wartoĂci kosztów oraz cen
jednostkowych, a nastÚpnie dodaj podsumowanie scenariuszy.
Plik 9_3.xlsx zawierajÈcy podstawÚ do tego Êwiczenia jest
udostÚpniony wraz z innymi materiaïami dotyczÈcymi ksiÈĝki
pod adresem: ftp://ftp.helion.pl/przyklady/ex27wf.zip.
Aby obliczyÊ zysk i dodaÊ scenariusze, naleĝy:
1. W pliku 9_3.xlsx w arkuszu Zysk netto umieĂciÊ aktywnÈ
komórkÚ w C10.
2. ZbudowaÊ formuïÚ wyliczajÈcÈ Przychody ze sprzedaĝy,
a wiÚc iloczyn ceny i iloĂci sprzedanych towarów. Formuïa
wyglÈda nastÚpujÈco: =C5*C8.
285
Excel 2007 PL. Controlling, finanse i nie tylko
3. PrzejĂÊ do komórki C12 i obliczyÊ Zysk bÈdě stratÚ brutto,
które sÈ róĝnicÈ przychodów ze sprzedaĝy i kosztów
zmiennych, a wiÚc =C10–C6*C8.
4. W komórce C16 wstawiÊ formuïÚ wyliczajÈcÈ Zysk
operacyjny, a wiÚc róĝnicÚ pomiÚdzy zyskiem (stratÈ)
brutto a kosztami operacyjnymi. Formuïa wyglÈda
nastÚpujÈco: =C12–C14.
5. PrzejĂÊ do komórki C20 i zbudowaÊ formuïÚ obliczajÈcÈ
Zysk netto przed opodatkowaniem, czyli róĝnicÚ zysku
operacyjnego i odsetek kredytu =C16–C18.
6. W komórce C24 wstawiÊ ostatniÈ formuïÚ wyliczajÈcÈ
Zysk netto, a wiÚc róĝnicÚ pomiÚdzy zyskiem netto przed
opodatkowaniem a wyliczonÈ wartoĂciÈ podatku — naleĝy
jednak pamiÚtaÊ, iĝ podatek pïacony jest wyïÈcznie wtedy,
kiedy firma osiÈga zysk. Formuïa powinna zatem braÊ
pod uwagÚ wartoĂci ujemne, wyglÈda wiÚc nastÚpujÈco:
=JE¿ELI(C20 0;C20;C20–C20*C22).
7. Na wstÈĝce z zakïadki Dane wybraÊ z listy Analiza
symulacji polecenie Menedĝer scenariuszy.
8. W oknie Menedĝera scenariuszy wcisnÈÊ przycisk Dodaj…
i wpisaÊ nazwÚ dla pierwszego scenariusza: Podstawowy oraz
okreĂliÊ komórki zmieniane: C5 i C6. Okno Edytowanie
scenariusza prezentuje rysunek 9.21.
Rysunek 9.21.
Okno
Edytowanie
scenariusza
286
Rozdziaï 9. Zagadnienia optymalizacji danych
9. W oknie WartoĂci scenariusza pozostawiÊ niezmienione
dane, a nastÚpnie za pomocÈ przycisku Dodaj przejĂÊ
do wstawiania kolejnego scenariusza.
10. WstawiÊ trzy kolejne scenariusze zaprezentowane
w arkuszu Scenariusze, a wiÚc:
Optymistyczny C5=22,5; C6=15,2,
Pesymistyczny C5=17,2; C6=16,
Oczekiwany C5=21,2; C6=18,2.
11. Po wpisaniu ostatniego scenariusza wcisnÈÊ przycisk OK.
Okno Menedĝera scenariuszy powinno wyglÈdaÊ jak
na rysunku 9.22.
Rysunek 9.22.
Wstawione
wszystkie
scenariusze
12. Z okna Menedĝer scenariuszy wybraÊ przycisk
Podsumowanie….
13. ZaznaczyÊ jako Typ raportu — Podsumowanie scenariuszy
oraz wskazaÊ jako komórkÚ wynikowÈ C24 i wcisnÈÊ
przycisk OK.
14. W nowym arkuszu zostanie wstawione podsumowanie,
co prezentuje rysunek 9.23.
Wynik Êwiczenia zapisany zostaï w pliku 9_3_wynik.xlsx.
287
Excel 2007 PL. Controlling, finanse i nie tylko
Rysunek 9.23. Podsumowanie scenariuszy
Optymalizacja danych
przy wielu niewiadomych
Na poczÈtku rozdziaïu zostaïa zaprezentowana moĝliwoĂÊ oblicza-
nia równañ finansowych z jednÈ niewiadomÈ. Bardzo czÚsto jed-
nak w tak dynamicznie zmieniajÈcym siÚ Ărodowisku jak finanse
firmy nieznanych jest duĝo wiÚcej elementów. Pomocnym narzÚ-
dziem w analizie finansowej, umoĝliwiajÈcym maksymalizacjÚ
wyniku finansowego lub minimalizacjÚ kosztów prowadzenia
dziaïalnoĂci, jest Solver. Dodatek Solver pozwala na znalezienie
najlepszego rozwiÈzania dla wskazanych komórek — optymali-
zuje cel przy okreĂlonych warunkach ograniczajÈcych. NarzÚdzie
to naleĝy dodaÊ do programu Microsoft Office Excel 2007 w spo-
sób, jaki zostaï zaprezentowany na poczÈtku rozdziaïu. Po wïÈ-
czeniu dodatku Solver jest on dostÚpny na wstÈĝce w zakïadce
Dane w sekcji Analiza.
Podczas pracy z poleceniem Solver najwaĝniejsze jest zbudowa-
nie z posiadanych informacji wïaĂciwego modelu, dopiero potem
moĝna wywoïaÊ okno Solver (rysunek 9.24).
W oknie najwaĝniejsze sÈ trzy elementy:
Q komórka docelowa — komórka, której wartoĂci majÈ zostaÊ
zoptymalizowane do okreĂlonego celu (maksymalizacji,
minimalizacji lub wskazanej wartoĂci),
288
Rozdziaï 9. Zagadnienia optymalizacji danych
Rysunek 9.24.
Okno polecenia
Solver
Q komórki zmieniane — komórki, które w modelu
sÈ niewiadomymi, jakie program musi rozwiÈzaÊ,
Q ograniczenia — naïoĝone w modelu ograniczenia.
Dodatkowe ustawienia moĝna wywoïaÊ, wybierajÈc przycisk Opcje
otwierajÈcy kolejne okno (widoczne na rysunku 9.25).
Rysunek 9.25.
Okno Opcje
Najwaĝniejsze ustawienia w tym oknie pozwalajÈ na dokïad-
ne okreĂlenie metod wyliczania wyniku. Poszczególne opcje
oznaczajÈ:
Q Maksymalny czas — liczba sekund, w czasie których
Solver musi odnaleěÊ rozwiÈzanie; maksymalna wartoĂÊ,
którÈ moĝna wprowadziÊ, to 32767.
Q Liczba iteracji — liczba poĂrednich obliczeñ; mniejsza
wartoĂÊ skraca czas obliczania; maksymalna wartoĂÊ
to równieĝ 32767.
289
Excel 2007 PL. Controlling, finanse i nie tylko
Q DokïadnoĂÊ — wartoĂÊ wskazujÈca na dokïadnoĂÊ
tolerancji dla komórki ograniczenia; wartoĂci z zakresu
od 0 do 1; dokïadnoĂÊ tym wiÚksza, im wiÚcej miejsc
dziesiÚtnych.
Q Tolerancja — wartoĂÊ wskazujÈca w procentach
akceptowalne odchylenie od wartoĂci optymalnej dla
komórki docelowej speïniajÈcej ograniczenia caïkowite;
zastosowanie wyïÈcznie dla zadañ z ograniczeniami
caïkowitymi.
Q Przyjmij model liniowy — opcja przyspieszajÈca
wyszukiwanie rozwiÈzania w przypadku optymalizacji
liniowej.
Q Przyjmij nieujemne — komórki zmieniane bez okreĂlonej
dolnej granicy w ograniczeniach przyjmujÈ minimalnÈ
wartoĂÊ równÈ zero.
Q Automatyczne skalowanie — opcja umoĝliwiajÈca
skalowanie, jeĂli miÚdzy danymi i wynikami jest duĝa
róĝnica wartoĂci.
Q Pokaĝ wynik iteracji — zaznaczenie tej opcji pozwala
na zatrzymanie poszukiwania rozwiÈzania i wyĂwietlenie
kaĝdej iteracji (kaĝdego moĝliwego rozwiÈzania).
Po rozwiÈzaniu zadania pojawia siÚ okno Solver – Wyniki (rysunek
9.26), w którym moĝliwe jest pozostawienie wyliczonego rozwiÈ-
zania lub przywrócenie wartoĂci poczÈtkowych. Dodatkowo wy-
liczony wynik po klikniÚciu przycisku Zapisz scenariusz… moĝe
zostaÊ zapisany w Menedĝerze scenariuszy do póěniejszego wy-
korzystania.
Rysunek 9.26.
Okno wyników
290
Rozdziaï 9. Zagadnienia optymalizacji danych
Ostatnim elementem dostÚpnym w tym oknie sÈ raporty. ZostanÈ
one dodane w nowych arkuszach, jeĂli ich nazwy bÚdÈ podĂwie-
tlone. DostÚpne sÈ trzy typy raportów:
Q Wyników — prezentuje informacje o wartoĂciach
oryginalnych i koñcowych dla komórek zmienianych,
wskazuje, jakie zostaïy naïoĝone ograniczenia oraz jakÈ
wartoĂÊ przyjmuje komórka celu.
Q WraĝliwoĂci — informacje wskazujÈce na czuïoĂÊ
rozwiÈzania zawartego w komórce celu oraz na niewielkie
zmiany w ograniczeniach.
Q Granic — zawiera informacje o komórkach docelowych
oraz zmienianych wraz z wartoĂciami koñcowymi, a takĝe
prezentuje dolnÈ i górnÈ granicÚ, którÈ mogÈ przyjÈÊ
poszczególne komórki zmieniane.
W przypadku naïoĝenia warunku okreĂlajÈcego wynik jako
liczby caïkowite Solver wyĂwietli wyïÈcznie raport wyników.
Przykïadowe zastosowanie polecenia Solver moĝna pokazaÊ na
nastÚpujÈcym zadaniu.
Firma produkuje cztery rodzaje zabawek: klocki, samochody,
pistolety na wodÚ i telefony. W fabryce zatrudnionych jest dwu-
dziestu pracowników, którzy do wykorzystania majÈ cztery ma-
szyny. Zabawki skïadajÈ siÚ z okreĂlonej liczby takich samych
czÚĂci. Cena, jednostkowy czas pracy osób, jednostkowy czas pracy
maszyn oraz wykorzystywane czÚĂci poszczególnych zabawek
ksztaïtujÈ siÚ na poziomie:
Cena
Jednostkowy
czas pracy osób
Jednostkowy czas
pracy maszyn
Wykorzystywane
czÚĂci
samochód 13 zï
0,5
telefon
20 zï
1
klocki
pistolet
17 zï
10 zï
0,7
0,2
0,1
0,24
0,055
0,14
5
15
10
7
291
Pobierz darmowy fragment (pdf)
