Transcript T.5_Solver

INFORMATYKA
W LOGISTYCE
ĆWICZENIA
dr inż. Andrzej KIJ
Adresowanie komórek
Plan prezentacji:
Adresy względne i bezwzględne
Definiowanie nazw komórek
Wykresy
Jak utworzyć wykres ?
Typy wykresów w Excelu
Sortowanie, filtry, sumy pośrednie
Tabele przestawne
Dodatki
Solver
Makra
Formanty ActiveX
Skróty klawiaturowe
Zadanie
W „zielonych” komórkach wpisać odpowiednie formuły
Następnie „przeciągnąć” je na komórki poniżej
Wpisać odpowiednie
formuły,
Wskazówka. odpowiednie
adresowanie
Ciekawe Zadanko 
Należy zbudować tabliczkę mnożenia liczb w zakresie
od 1 do 10 przy użyciu tylko 1 formuły.
Wskazówka. Adresowanie mieszane
=$A2*B$1
Dowolnej komórce lub zakresowi komórek możemy
nadać nazwę: Wstaw/Nazwa/Definiuj ...
Co zostanie
wyświetlone w
komórce D5 ?
Pamiętaj !
Adres bezwzględny komórki
= nazwie zdefiniowanej komórki
Po co są Wykresy ?
Wykres - graficzna reprezentacja
danych
liczbowych,
jako
ich
poglądowa wizualizacja, pozwalająca
ocenić
je
"wzrokowo"
bez
dokładniejszej analizy konkretnych
liczb, np. wzrost sprzedaży firmy w
kolejnych
latach
czy
udział
poszczególnych firm w rynku jakiegoś
produktu.
Tworzenie wykresów:
1. Wprowadzamy dane do Arkusza
2. Zaznaczamy dane
3. Uruchamiamy Kreator wykresów
4. Określamy źródło danych
5. Podajemy parametry wykresu (tytuły, osie,
legendy, siatki, legenda, etykiety, . . . )
6. Określamy położenie wykresu
7. ...możemy edytować wykres i go modyfikować
Typy głównych wykresów w Excelu:
Wykres 1.3.1. Czasowy rozkład zmiany
intensywności cząstek GPK rejestrowanej przez
stację Kilonia (Kiel) oraz liczba Wolfa określająca
aktywność Słońca w latach 1957–2004
• Warstwowy
• Kolumnowy
6500
• Słupkowy
• Liniowy
5500
• Kołowy
• Pierścieniowy
4500
• Giełdowy
• XY (punktowy)
• Bąbelkowy
3500
1956 1961 1967 1972
• Radarowy
• Powierzchniowy
• Stożkowy, Cylindryczny i Ostrosłupowy
• Użytkownika (np.. 2-osiowy)
Intensywność GPK (Kiel)
2
Liczba Wolfa
[ilość cząstek / (s m sr ) ]
500
400
300
200
100
Lata
0
1978 1983 1988 1994 1999 2005
Sortowanie
Możemy
sortować
informacje czyli
ustawiać je w
odpowiadającej
nam kolejności
np. rosnąco,
malejąco
Dane / Sortuj ...
Filtrowanie
Dane / Filtr /Autofiltr
W Excelu w
prosty sposób
można
filtrować
informacje czyli
wybierać tylko
te które
spełniają
odpowiednie
warunki
Sumy pośrednie – zliczanie w kolumnach względem
odpowiednich kategorii
Tabele przestawne
Jednym z najwspanialszych
mechanizmów,
jakie
oferuje nam Excel, są tzw.
tabele przestawne.
W największym skrócie
można powiedzieć, że są to
tabele
ułatwiające
analizowanie
dużych
ilości danych czerpanych
z arkusza kalkulacyjnego.
Wyobraźmy
sobie,
że
zbudowaliśmy w Excelu
następującą
tabelę
szkolnych wydatków
Jeśli taka tabela ma kilkaset wierszy, to niezwykle
użyteczne staje się grupowanie tych jej wierszy,
które
w
poszczególnych
kolumnach
mają
identyczne wartości. To właśnie znakomicie potrafią
robić tabele przestawne. Wystarczy wybrać z menu
odpowiednią pozycję i wskazać interesujące nas
kolumny. Może to wyglądać np. tak
Okno pozwalające ustalić
zawartość tabeli
przestawnej przy użyciu
danych
Oznacza to, że interesuje nas, jakie kwoty przeznaczyliśmy
w poszczególnych miesiącach na określone kategorie
wydatków. W ciągu sekundy Excel przygotuje nam
odpowiednią tabelkę
Gotowa tabela przestawna, pozwalająca wygodnie analizować
dane z tabeli
Zadanie
Musimy
wydać
dokładnie
5000zł. Możemy kupić 3 różne
towary w różnej cenie. Dane
przedstawia tabelka. Ile czego
kupić aby wydać całą kwotę ?
Szukaj wyniku
Nie wszystkie dodatki Excela są
instalowane standardowo wraz z
programem
głównym.
Listę
dostępnych dodatków rozwijamy
klikając na Narzędzia /Dodatki ...
Solver, podstawowe
informacje
Dodatek Solver jest częścią zestawu poleceń
czasami zwaną narzędziami analizy typu co-jeśli.
Korzystając z dodatku Solver, można znaleźć
optymalną wartość dla formuły w pojedynczej
komórce — zwanej komórką docelową — w
arkuszu.
Dodatek Solver pracuje z grupą komórek
powiązanych, bezpośrednio lub pośrednio, z
formułą w komórce docelowej.
Dodatek Solver dostosowuje wartości w
zmieniających się komórkach określonych przez
użytkownika — zwanych komórkami
zmienianymi — w celu uzyskania wyniku
określonego przez użytkownika na podstawie
formuły w komórce docelowej.
Można zastosować ograniczenia które
zmniejszają zakres wartości używanych przez
dodatek Solver w modelu i mogą odwoływać
się do innych komórek wpływających na
formułę w komórce docelowej.
Dodatku Solver można używać do ustalenia
maksymalnej lub minimalnej wartości
określonej komórki przez zmianę innych
komórek, na przykład można zmienić
przewidywany budżet reklamowy i zobaczyć
wpływ tej zmiany na przewidywany zysk.
Zasady projektowania modeli poszukiwania
rozwiązań za pomocą dodatku Solver
Jak działa dodatek Solver? Dodatek Solver pozwala
zoptymalizować wartość formuły w jednej z komórek
arkusza – nazywanej komórką celu. Zakresem
działania jest grupa komórek związanych bezpośrednio
lub pośrednio z formułą w komórce celu. Wartości w
komórkach
określonych
przez
użytkownika
–
nazywanych komórkami zmienianymi – są zmieniane
tak, aby osiągnąć żądany wynik w komórce celu.
Zakres zmian wartości występujących w modelu
można ograniczyć, wprowadzając ograniczenia. Mogą
one także dotyczyć innych komórek, które mają wpływ
na formułę w komórce celu.
Z menu Narzędzia wybierz polecenie Solver.
Jeżeli polecenie Solver nie jest dostępne w menu
Narzędzia, należy zainstalować dodatek Solver.
W polu Komórka celu podaj adres lub nazwę komórki docelowej. Komórka
celu musi zawierać formułę. Jeżeli chcesz, aby wartość w komórce była jak
największa, kliknij opcję Maks. Jeżeli chcesz, aby wartość w komórce była
jak najmniejsza, kliknij opcję Min. by określić wartość w komórce docelowej,
kliknij opcję Wartość i wpisz wartość w polu obok.
W polu Komórki zmieniane podaj nazwę lub adres każdej komórki
zmiennej, oddzielając przecinkami adresy nie przylegających komórek.
Komórki zmiennej muszą być bezpośrednio lub pośrednio związane z
komórką docelową. Można określić maksymalnie 200 komórek zmienianych.
Aby w dodatku Solver automatycznie zaproponować komórki zmieniane dla
komórki celu, kliknij przycisk Odgadnij.
W polu Warunki ograniczające [na dole strony] podaj wszystkie
ograniczenia, które chcesz zastosować. Kliknij przycisk Rozwiąż.
Aby zanotować wyniki w arkuszu, kliknij przycisk Przechowaj
rozwiązanie w oknie dialogowym Solver - Wyniki. Aby przywrócić
pierwotne wartości, kliknij przycisk Przywróć wartości początkowe.
Przykład obliczeń z użyciem dodatku Solver.
W podanym dalej przykładzie, wydatki na "Reklamę" w
poszczególnych kwartałach mają wpływ na liczbę "Sprzedanych
jednostek", określając pośrednio "Przychód ze sprzedaży" [według
równania
=35*B2*(B8+3000)^0,5
czyli:
35*wskaźnik
sezonowości*(reklama+3000)^0,5], wydatki ("Koszty zakupu",
"Reklama" i "Koszt ogólnozakładowy") oraz "Zysk". Optymalizacja
polega na zmienianiu kwartalnego budżetu na "Reklamę" (komórki
B8:E8) do jego wartości maksymalnej, którą ogranicza całkowity
budżet 40 000 (komórka F8), aż do osiągnięcia największego
możliwego "Zysku". Wartości w komórkach zmienianych są używane
do obliczenia "Zysku" w poszczególnych kwartałach i są związane z
formułą w komórce celu F11, =SUMA(B11:E11).
A
1
Miesiąc
2
Sezonowość
3
Sprzedane jednostki
4
Przychód ze sprzedaży w zł
5
B
C
D
E
F
Kw. I
Kw. II
Kw. III
Kw. IV
Razem
0,9
1,1
0,8
1,2
3592
4390
3192
4789
15962
143662
175587
127700
191549
638498
Koszt zakupu
89789
109742
79812
119718
399061
6
Marża brutto
53873
65845
47887
71831
239437
7
Wydatki służbowe
8000
8000
9000
9000
34000
8
Reklama
10000
10000
10000
10000
40000
9
Koszt ogólnozakładowy
21549
26338
19155
28732
95775
10
Koszt całkowity
39549
44338
38155
47732
169775
11
Zysk z produktów w zł
14324
21507
9732
24099
69662
12
Rentowność sprzedaży
10%
12%
8%
13%
11%
13
Cena produktu
40
14
Koszt produktu
25
Formuły w komórkach I kwartału.
B
1
Kw. I
2
3
4
5
6
7
8
9
10
11
12
13
14
0,9
=35*B2*(B8+3000)^0,5
=B3*$B$13
=B3*$B$14
=B4-B5
8000
10000
=0,15*B4
=SUMA(B7:B9)
=B6-B10
=B11/B4
40
25
Wynik optymalizacji.
Reklama
7273
12346
5117
15263
40000
Koszt ogólnozakładowy
19156
28616
15136
34056
96965
Koszt całkowity
34430
48963
29253
58319
170965
Zysk z produktów w zł
13461
22578
8587
26820
71447
Dodawanie ograniczeń w dodatku Solver
W menu Narzędzia kliknij polecenie Solver.Kliknij przycisk Dodaj.
W polu Odwołanie do komórki podaj nazwę lub adres zakresu
komórek, których wartości chcesz ograniczyć.
Kliknij symbol relacji ( <=, =, >=, int lub bin ), która ma zachodzić
pomiędzy wskazaną komórką, a wartością ograniczającą. Jeżeli
klikniesz symbol int, w polu Warunki ograniczające pojawi się
informacja "Liczba całkowita". Jeżeli klikniesz bin, w polu Warunki
ograniczające pojawi się informacja "binary".
W polu Warunki ograniczające wpisz liczbę, nazwę lub adres
komórki, albo formułę.
Aby potwierdzić warunek ograniczający i dodać następny, kliknij
przycisk Dodaj.
Aby zaakceptować warunek ograniczający i powrócić do okna
dialogowego Solver - Parametry, kliknij przycisk OK.
Uwagi
Relacje int i bin mogą występować tylko w więzach nałożonych na
komórki zmieniane.
Jeśli w oknie dialogowym Opcje dodatku Solver jest zaznaczone pole
wyboru Model liniowy, nie obowiązuje żaden limit liczby ograniczeń.
W przypadku problemów nieliniowych każda komórka może
zawierać, oprócz ograniczeń dla zmiennych, do 100 innych
ograniczeń. Informacje o opcjach w oknie dialogowym Dodawanie
warunku ograniczającego.
MAKRA
Jeśli jakieś zadania są często powtarzane w programie Microsoft
Excel, to można zautomatyzować ich wykonywanie za pomocą
makra. Makro jest serią poleceń i funkcji (funkcja: Uprzednio
napisana formuła, która pobiera wartość lub wartości,
przeprowadza operację i zwraca wartość lub wartości. Funkcje
upraszczają i skracają formuły używane w arkuszu, szczególnie
te, które przeprowadzają długie lub złożone obliczenia.)
przechowywanych w module (moduł: Kolekcja deklaracji,
instrukcji i procedur, które są przechowywane razem jako
nazwana jednostka. Istnieją dwa typy modułów: moduły
standardowe i moduły klas.) języka Microsoft Visual Basic i
może być uruchomione zawsze, gdy trzeba wykonać dane
zadania.
Na przykład jeżeli do komórek często są wprowadzane długie
ciągi tekstowe, to można utworzyć makro tak formatujące
komórki, aby znajdujący się w nich tekst był zawijany.
Rejestrowanie makr
Podczas rejestrowania makra program Excel przechowuje
informacje o każdym kroku wykonanym przez użytkownika używającego serii poleceń.
Następnie można uruchomić makro, aby powtórzyć lub „odtworzyć” polecenia. Po
popełnieniu błędu podczas rejestrowania makra korekty wprowadzane przez
użytkownika są również rejestrowane. Język Visual Basic został opracowany przez
firmę Microsoft i służy do budowania aplikacji systemu Windows, przechowuje każde
makro w nowym module dołączonym do skoroszytu.
Konfigurowanie makra, aby można je było łatwo uruchamiać
Można uruchomić
makro, wybierając je z listy wyświetlanej w oknie dialogowym Makro. Aby makro
uruchamiało się zawsze po kliknięciu określonego przycisku lub naciśnięciu kombinacji
klawiszy, można przypisać je do przycisku paska narzędzi, klawisza skrótu (klawisz
skrótu: Klawisz funkcji lub kombinacja klawiszy, na przykład F5 lub CTRL+A, której
można użyć, aby wykonać polecenie menu. W odróżnieniu od niego klawisz dostępu
jest kombinacją klawiszy, na przykład ALT+F, która przenosi fokus na menu, polecenie
lub formant.) lub obiektu graficznego umieszczonego w arkuszu.
Zarządzanie makrami Po zarejestrowaniu makra można przeglądać jego kod,
używając Edytora Visual Basic (Edytor Microsoft Visual Basic: Środowisko, w którym
można edytować zarejestrowane makra oraz pisać nowe makra i programy w języku
Visual Basic for Applications.) w celu korygowania błędów lub zmieniania czynności
wykonywanych przez makro. Na przykład jeżeli makro zawijające tekst w komórce ma
go także pogrubiać, to można zarejestrować makro służące do pogrubiania tekstu, a
następnie skopiować jego instrukcje do makra zawijającego tekst w komórce.
Makra
1. Rejestracja makra:
Nadanie nazwy + skrót
1. Nagranie „akcji”
2. Uruchomieniu makra
Nie ma myszki i co wtedy ?
Czy można pracować w Excelu za pomocą samej
klawiatury ?
Poruszanie się w obrębie arkusza roboczego
Aby zmienić komórkę aktywną za pomocą myszy,
należy w nią kliknąć. Aby zmienić aktywną komórkę
za pomocą klawiatury, można użyć klawiszy
kursorów lub ich kombinacji z innymi klawiszami.
Poniższe tabelki przedstawiają przykładowa listę
klawiszy których możesz używać – sprawdź sam.
Co jeszcze można robić w Excelu ?
Praca z wieloma skoroszytami
Ochrona danych
Dodatki
Weryfikowanie i sprawdzanie arkuszy
Scenariusze
Raporty
Excel a inne aplikacje pakietu Office
Bazy danych w Excelu
Internet a Arkusz kalkulacyjny
Poczta elektroniczna
Zagadnienia sieciowe
VBA
...
Koniec Excela
Dziękuję za Uwagę
Zapraszam na następny Wykład
