INFORMATYKA W LOGISTYCE WICZENIA dr in Andrzej KIJ

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 Active. X 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 =$A 2*B$1

Dowolnej komórce lub zakresowi komórek możemy nadać nazwę: Wstaw/Nazwa/Definiuj. . . Co zostanie wyświetlone w komórce D 5 ? 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 • Słupkowy • Liniowy • Kołowy • Pierścieniowy • Giełdowy • XY (punktowy) • Bąbelkowy • Radarowy • Powierzchniowy • Stożkowy, Cylindryczny i Ostrosłupowy • Użytkownika (np. . 2 -osiowy)

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 5000 zł. 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*B 2*(B 8+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 B 8: E 8) do jego wartości maksymalnej, którą ogranicza całkowity budżet 40 000 (komórka F 8), 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 F 11, =SUMA(B 11: E 11).

A 1 Miesiąc 2 Sezonowość 3 Sprzedane jednostki 4 Przychód ze sprzedaży w zł 5 B C D E F 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 9000 34000 8 Reklama 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*B 2*(B 8+3000)^0, 5 =B 3*$B$13 =B 3*$B$14 =B 4 -B 5 8000 10000 =0, 15*B 4 =SUMA(B 7: B 9) =B 6 -B 10 =B 11/B 4 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 F 5 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

Wykres funkcji liniowej y=ax+b , t ak wykonać aby parametry a, b zmieniały się płynn ie Zadanie

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