Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00411 008834 10444231 na godz. na dobę w sumie
Excel. Analiza danych biznesowych - książka
Excel. Analiza danych biznesowych - książka
Autor: Liczba stron: 256
Wydawca: Helion Język publikacji: polski
ISBN: 83-246-0506-1 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> aplikacje biurowe >> excel
Porównaj ceny (książka, ebook, audiobook).

Wykorzystaj Excel w swojej firmie

Excel jest jedną z najpopularniejszych aplikacji biurowych, znaną niemal wszystkim posiadaczom komputerów. Jednak większość użytkowników wykorzystuje jedynie część możliwości tej aplikacji, nie wiedząc, że -- oprócz obliczania sum i średnich w standardowych arkuszach -- może ona ułatwić pracę na wiele sposobów. Narzędzia oferowane przez Excel, m.in. te, które służą do analizy i wizualizacji, warto stosować w znacznie szerszym zakresie, gromadząc i przetwarzając dane z wielu innych systemów.

Książka 'Excel. Analiza danych biznesowych' przedstawia sposoby zastosowania tej aplikacji do realizacji zadań związanych z kierowaniem przedsiębiorstwem bądź zespołem produkcyjnym. Czytając ją, poznasz przykładowe aplikacje Excela i nauczysz się korzystać z wbudowanych w ten program narzędzi. Dowiesz się, jak przeprowadzać analizy za pomocą wykresów i tabel przestawnych, wyznaczać trendy na podstawie danych historycznych, korzystać z modułu Solver oraz tworzyć własne moduły obliczeniowe.

Przekonaj się, jak bardzo Excel może usprawnić działanie Twojej firmy.

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

IDZ DO IDZ DO PRZYK£ADOWY ROZDZIA£ PRZYK£ADOWY ROZDZIA£ SPIS TREœCI SPIS TREœCI KATALOG KSI¥¯EK KATALOG KSI¥¯EK KATALOG ONLINE KATALOG ONLINE ZAMÓW DRUKOWANY KATALOG ZAMÓW DRUKOWANY KATALOG TWÓJ KOSZYK TWÓJ KOSZYK DODAJ DO KOSZYKA DODAJ DO KOSZYKA CENNIK I INFORMACJE CENNIK I INFORMACJE ZAMÓW INFORMACJE ZAMÓW INFORMACJE O NOWOœCIACH O NOWOœCIACH ZAMÓW CENNIK ZAMÓW CENNIK CZYTELNIA CZYTELNIA FRAGMENTY KSI¥¯EK ONLINE FRAGMENTY KSI¥¯EK ONLINE Wydawnictwo Helion ul. Koœciuszki 1c 44-100 Gliwice tel. 032 230 98 63 e-mail: helion@helion.pl Excel. Analiza danych biznesowych Autor: Gerald Knight T³umaczenie: Marcin Karbowski ISBN: 83-246-0506-1 Tytu³ orygina³u: Analyzing Business Data with Excel Format: B5, stron: 256 Przyk³ady na ftp: 8001 kB Wykorzystaj Excel w swojej firmie (cid:129) Przeprowadzaj analizy statystyczne. (cid:129) Steruj procesami w firmie. (cid:129) Monitoruj obci¹¿enie systemów. Excel jest jedn¹ z najpopularniejszych aplikacji biurowych, znan¹ niemal wszystkim posiadaczom komputerów. Jednak wiêkszoœæ u¿ytkowników wykorzystuje jedynie czêœæ mo¿liwoœci tej aplikacji, nie wiedz¹c, ¿e — oprócz obliczania sum i œrednich w standardowych arkuszach — mo¿e ona u³atwiæ pracê na wiele sposobów. Narzêdzia oferowane przez Excel, m.in. te, które s³u¿¹ do analizy i wizualizacji, warto stosowaæ w znacznie szerszym zakresie, gromadz¹c i przetwarzaj¹c dane z wielu innych systemów. Ksi¹¿ka „Excel. Analiza danych biznesowych” przedstawia sposoby zastosowania tej aplikacji do realizacji zadañ zwi¹zanych z kierowaniem przedsiêbiorstwem b¹dŸ zespo³em produkcyjnym. Czytaj¹c j¹, poznasz przyk³adowe aplikacje Excela i nauczysz siê korzystaæ z wbudowanych w ten program narzêdzi. Dowiesz siê, jak przeprowadzaæ analizy za pomoc¹ wykresów i tabel przestawnych, wyznaczaæ trendy na podstawie danych historycznych, korzystaæ z modu³u Solver oraz tworzyæ w³asne modu³y obliczeniowe. (cid:129) Obliczenia i analizy statystyczne (cid:129) Korzystanie z tabel i wykresów przestawnych (cid:129) Tworzenie modeli matematycznych (cid:129) Prognozowanie obci¹¿enia (cid:129) Monitorowanie systemów i procesów (cid:129) Optymalizowanie funkcji (cid:129) Import danych ze Ÿróde³ zewnêtrznych (cid:129) Prezentacja danych Przekonaj siê, jak bardzo Excel mo¿e usprawniæ dzia³anie Twojej firmy Wstęp .............................................................................................................................. 7 1. Statystyka w Excelu .......................................................................................................11 11 13 16 Formuły tablicowe Pośrednie adresowanie komórek Funkcje statystyczne 2. Tabele przestawne i rozwiązywanie problemów ......................................................33 34 47 50 Podstawowe informacje o tabelach przestawnych Zmienianie danych Opcje tabeli przestawnej 3. Prognozowanie obciążenia pracą ...............................................................................53 54 61 Procedura Tworzenie aplikacji 4. Tworzenie modeli ......................................................................................................... 79 79 83 86 88 93 95 98 Regresja Definiowanie problemu Dopracowywanie metryk Analiza Budowanie modelu Analiza rezultatów Testowanie związków nieliniowych 5. Mierzenie jakości .........................................................................................................101 102 104 Statystyczne Sterowanie Procesami Uruchamianie aplikacji 3 Wygląd aplikacji Dokonywanie zmian w aplikacji 105 115 6. Monitorowanie złożonych systemów ........................................................................119 120 122 123 125 135 Aplikacja Dane Ustawienia Obszar roboczy Makra 7. Kontrola procesów ......................................................................................................141 142 143 146 Dane Aplikacja Obliczenia 8. Kontrola przepływu pracy ......................................................................................... 163 163 167 167 171 175 Aplikacja Dane Obliczenia VBA Rozbudowywanie aplikacji 9. Optymalizacja ..............................................................................................................177 177 186 Szukaj wyniku Solver 10. Importowanie danych ................................................................................................ 201 201 Pliki tekstowe 206 Bazy danych XML 213 11. Problemy z danymi ..................................................................................................... 219 219 222 223 228 Liczby Daty Raporty Równoważność 12. Techniki efektywnej prezentacji ............................................................................... 231 231 232 232 Szacunek dla informacji i jej odbiorcy Duże arkusze Wykresy 4 | Spis treści Obrazy i inne obiekty Złożoność Powtarzane elementy Zagęszczenie informacji Podkreślanie i wyróżnianie informacji 234 237 237 238 240 Skorowidz ...................................................................................................................245 Spis treści | 5 ROZDZIAŁ 3. Prognozowanie polega na wykorzystywaniu informacji dostępnych w danym momencie i prze- widywaniu na ich podstawie przyszłego rozwoju wypadków. Korzystamy z naszej wiedzy o przeszłości, decydując, o której godzinie wstać z łóżka, ile jedzenia kupić i gdzie pojechać na wakacje. Zwykle nie zastanawiamy się nad sposobem, w jaki podejmujemy te decyzje, i nie oceniamy ich skutków. Prognozowanie w rozumieniu biznesowym ma charakter bardziej strukturalny. Do dyspozycji mamy specjalistyczne techniki, pozwalające modelować związki między dostępnymi informacjami a przyszłymi wartościami badanych parametrów. Prognozy rzadko kiedy idealnie się sprawdzają. Należy się spodziewać pewnej różnicy mię- dzy prognozą a wartością, którą w rzeczywistości przybierze badany parametr. W większość przypadków nie wystarczy zatem opracowanie prognozy, trzeba również oszacować jej do- kładność. Oznacza to, iż przewidywanie polega na wyznaczeniu pewnego zakresu wartości i przypisaniu im określonego prawdopodobieństwa. W tym rozdziale zajmiemy się przewidywaniem obciążenia pracą w typowym procesie biz- nesowym. Opisana przykładowa aplikacja prognozuje ilość telefonów do działu informacji, jednak wykorzystane w niej narzędzia mogą być również zastosowane w analizie wielu in- nych problemów. Przewidywane wartości tworzone są z wykorzystaniem danych z przeszło- ści. W tym przypadku przyszła ilość telefonów szacowana jest na podstawie informacji z kil- ku ostatnich tygodni. Niniejszy rozdział opisuje również techniki tworzenia aplikacji w Excelu. Wykorzystuje ona połączenie organizacji, funkcji skoroszytu, formatowania oraz języka VBA. Na koniec otrzy- mamy kompletną aplikację prognozującą obciążenie pracą. Ponieważ jest to pierwsza aplikacja opisywana w tej książce, omówimy w szczegółach cały proces jej tworzenia, zaczynając od wykorzystywanych funkcji. Nazwy użytych funkcji widoczne są w tabeli 3.1. Większość z nich zostanie opisana w trakcie omawiania samej aplikacji. Funkcje INDEX, ADR.POŚR oraz ADRES będą wykorzystywane w aplikacjach zamieszczonych w dalszej części książki. Rola wymienionych funkcji jest spe- cyficzna, a opanowanie zasad ich działania niezbędne do zrozumienia omawianej aplikacji. Z tego względu przed przejściem do dalszej części rozdziału należy zapoznać się z zawarty- mi w rozdziale 1. informacjami na ich temat. W aplikacji wykorzystano również narzędzia opisane w tabeli 3.2. 53 Tabela 3.1. Funkcje programu Excel zastosowane w przykładowej aplikacji opisywanej w tym rozdziale INDEKS() ADR.POŚR() ADRES() DZIEŃ.TYG() WIERSZ() MAX() NR.KOLUMNY() SUMA() MIN() JEŻELI() ŚREDNIA() ODCH.STANDARDOWE() UFNOŚĆ() ZAOKR.DO.CAŁK() MODUŁ.LICZBY() NORMALIZUJ() ROZKŁAD.NORMALNY.S() Tabela 3.2. Narzędzia programu Excel wykorzystane w przykładowej aplikacji opisywanej w tym rozdziale Opis Formatowanie pozwala kontrolować wygląd aplikacji. Zwraca uwagę na ważne informacje, zaznaczając istotne elementy widoczne na ekranie. Nadawanie nazw komórkom i ich zakresom pozwala stworzyć jasny i przejrzysty interfejs między poszczególnymi częściami aplikacji. Formuły tablicowe umożliwiają skrócenie obliczeń, dzięki czemu nie ma konieczności tworzenia osobnych kolumn dla poszczególnych operacji matematycznych. Wykresy pozwalają przedstawić liczby w formie graficznej. W ten sposób użytkownik nie koncentruje się na samych liczbach, ale na ich znaczeniu i płynących z nich wnioskach. Język VBA pozwala zwiększyć potencjał programu Excel i dopasować go do własnych potrzeb. Narzędzie Formatowanie Nazwane komórki i nazwane zakresy komórek Formuły tablicowe Wykresy Język VBA Procedura Najpierw omówimy konieczne do wykonania obliczenia. Naszym zadaniem jest opracowanie jak najlepszej prognozy, zmierzenie jej dokładności i poradzenie sobie z ewentualnymi ano- maliami. Program Excel daje nam odpowiednie narzędzia, zanim jednak zaczniemy wpisywać formuły, musimy zrozumieć dane, którymi dysponujemy. Dane W tym rozdziale wykorzystujemy dane działu informacji telefonicznej. Pracuje on 5 dni w ty- godniu. Zgromadzone dane to po prostu informacje o ilości telefonów odebranych w poszcze- gólnych dniach. Wykorzystane tu techniki można zastosować w wielu innych sytuacjach wymagających ana- lizy obciążenia pracą. Celem jest wyznaczenie przewidywanych wartości badanego parametru w danych jednostkach czasu. Wartości te mogą wykazywać pewien ogólny trend, charakte- ryzujący się krótkoterminowym zwiększaniem lub zmniejszaniem ilości łączonych rozmów. Dokładność prognozy zostanie oszacowana celem wyznaczenia zakresu przewidywanych zmian i ich prawdopodobieństwa. Model obciążenia pracą powinien radzić sobie zarówno z przewidywalnymi, jak i nieprzewi- dywalnymi zmianami wartości. Łatwo przewidzieć, że zbliżają się święta, ale dane z grudnia nie stanowią dobrego materiału do tworzenia prognoz na styczeń. Na rzeczywiste wartości badanych parametrów wpływ ma bardzo wiele różnych czynników zewnętrznych. Konku- rencja może podnieść ceny, może nastąpić awaria serwera, a burza śnieżna może spowodo- wać brak dostaw prądu w znacznej części kraju. W rezultacie badane obciążenie pracą może się nagle zwiększyć lub zmniejszyć w sposób trudny do przewidzenia. 54 | Rozdział 3. Prognozowanie obciążenia pracą Przykładowe dane wykorzystane w tym rozdziale pochodzą z prawdziwego działu informa- cji telefonicznej i są opisem rzeczywistego procesu biznesowego. Prognozy Czas tworzy niepewność. Im dalszą przyszłość chcemy przewidzieć, tym mniejsza będzie dokładność naszej prognozy. W tym rozdziale zaczniemy od prognozy na najbliższy tydzień. W miarę uzyskiwania dodatkowych informacji oszacowanie będzie poprawiane i w efekcie otrzymamy prognozę dzienną. Stworzone też zostaną prognozy na poszczególne godziny, których wartości wykorzystywane będą do poprawienia dokładności oszacowań dziennych. Długość cyklu Długość cyklu to ilość obserwacji w cyklu. Wiele przedsiębiorstw działa w cyklu tygodniowym. Poniedziałki mają własną specyfikę, a piątki własną. Jeśli proces trwa 5 dni, długość cyklu wynosi 5. Oczywiście nie wszystko dzieje się w okresach 5-dniowych. Czasem mają one dłu- gość miesiąca lub godziny, a niekiedy jest to zupełnie inny, nietypowy przedział czasu. W każ- dym jednak przypadku niezmiernie ważne jest określenie ilości obserwacji w danym cyklu i zwykle wiedzę tę można zdobyć bez specjalnego analizowania danych. Jeśli jednak nie jeste- śmy pewni, lepiej to sprawdzić. W tym przykładzie wykorzystamy dane dotyczące ilości połączeń telefonicznych zrealizo- wanych w okresie 30 dni. Informacja działa od poniedziałku do piątku, w związku z czym prawdopodobnie długość cyklu wynosi 5 dni. Aby znaleźć jego rzeczywistą długość, musimy skorelować liczbę określającą ilość rozmów z nią samą, przesuniętą o różne ilości dni. Przesu- nięcie dające największą korelację to właśnie długość cyklu. W tym celu wykorzystamy funkcję WSP.KORELACJI. Bazuje ona na dwóch argumentach w po- staci zakresów o tej samej ilości komórek, a jako wynik zwraca korelację między wartościami w podanych zakresach. Formuła w kolumnie korelacja brzmi: =WSP.KORELACJI(B$2:B$21;B3: B22). Po jej wpisaniu przeciągnięto ją do kolejnych 10 komórek. Wysoki współczynnik kore- lacji uzyskano jedynie w 5. i 10. komórce. Potwierdza to tezę o 5-dniowym cyklu. Wzajemne korelowanie tych samych liczb nazywamy autokorelacją. Cała procedura pokazana jest na rysunku 3.1. Średnia Najprostsza prognoza to założenie, iż każdy dzień będzie się charakteryzował taką samą ilo- ścią połączeń telefonicznych jak odpowiadający mu dzień poprzedniego tygodnia. Może ono jednak spowodować problemy, jeżeli okaże się, iż tydzień wcześniej wystąpiły jakieś anoma- lie. Lepiej zatem opierać się na wartości średniej. Zwykła średnia może zostać zniekształcona przez bardzo nietypowe wartości, dlatego też lepiej wykorzystywać średnią filtrowaną. Eli- minuje ona najwyższe i najniższe wyniki pomiaru przed obliczeniem średniej, co umożliwia znacznie lepsze oszacowanie badanych wartości. Opisaną operację wykonuje się w dwóch etapach. Najpierw należy skonstruować tabelę za- wierającą wartości dla jednego dnia (na przykład poniedziałku). Następnie obliczamy sumę, odejmujemy maksymalne i minimalne wartości, a wynik dzielimy przez liczbę mniejszą o 2 od ilości zsumowanych liczb. Procedura | 55 Rysunek 3.1. Wykorzystywanie korelacji w celu wyznaczenia długości cyklu Do dyspozycji mamy dane z 5 tygodni. Pierwszy dzień to poniedziałek 02-mar-98, na liście znajduje się jeszcze 5 kolejnych poniedziałków. Z praktyki wynika, że ilość tygodni pozwa- lająca uzyskać najlepszy wynik bywa różna. Z jednej strony duża ilość danych daje większą precyzję oszacowania, z drugiej jednak zbyt duża liczba cykli może prowadzić do przekłamań z powodu występujących zmian sezonowych. W większości przypadków znakomicie spraw- dza się okres 8 tygodni. Funkcja INDEKS tworzy listę wartości dla wybranego dnia na podstawie analizy zdefiniowa- nego zakresu. Pierwszy z jej argumentów to zakres komórek zawierających listę wartości. Ko- lejny to numer wybranego elementu listy. W tym przypadku potrzebne nam są numery wier- szy zawierających dane dotyczące poniedziałków. Pierwszy z nich to wiersz drugi, a pierwsza wartość w kolumnie Wiersze (komórka C2) to 2. Formuła w komórce C3 brzmi: =C2+5. Doda- jemy 5, ponieważ tyle wynosi długość cyklu. Formułę przeciągamy do komórki C6. W ko- mórce D2 znajduje się następująca formuła: =INDEKS(B$1:B$26;C2). Wskazuje ona na drugi element listy B1:B26. Tę formułę również należy przeciągnąć w dół. Filtrowaną średnią obli- cza formuła: =LICZBA.CAŁK((SUMA(D2:D6)-(MAX(D2:D6)+MIN(D2:D6)))/3). Wykorzystuje- my 5 wartości, ale najwyższa i najniższa są eliminowane, sumę dzielimy więc przez 3. Funk- cja LICZBA.CAŁK zwraca wartość jako liczbę całkowitą. Ponieważ badamy ilość połączeń telefonicznych, nie ma sensu wykorzystywać wartości rzeczywistych — nie istnieje przecież coś takiego jak pół telefonu! W opracowywanej aplikacji filtrowana średnia stanowi prognozę tygodniową. Uzyskujemy dzięki niej oszacowanie ilości telefonów na tydzień naprzód. Wy- korzystaliśmy wartości z pięciu poniedziałków w okresie od 2. marca do 3. kwietnia i utwo- rzyliśmy prognozę na kolejny poniedziałek — 6. kwietnia. Efekt opisanych obliczeń przedsta- wiony jest na rysunku 3.2. 56 | Rozdział 3. Prognozowanie obciążenia pracą Rysunek 3.2. Obliczanie średniej filtrowanej Dopasowanie prognozy do trendu Trend to zmiana wartości średniej w czasie. Z roku na rok możemy oczekiwać wzrostu sprze- daży lub spadku ilości skarg. Jednak w krótkim czasie trendy zwykle niewiele znaczą. 5-pro- centowy wzrost w skali roku oznacza tygodniowy wzrost poniżej 0,1 . Systemy biznesowe nie są przewidywalne w tak małych przedziałach wartości. W rezultacie tego typu trend nie ma żadnego znaczenia dla tworzonej prognozy. Istnieją jednak inne czynniki. W przypadku każdej operacji wartości zwiększają się i zmniejszają z tygodnia na tydzień. Tego typu krót- koterminowe trendy mają znaczny wpływ na dokładność prognozy. Zrozumienie trendów jest niezbędne podczas budowania dokładnego modelu prognozującego. Obliczyć należy stosunek wartości prognozowanych (filtrowanej średniej) do wartości rze- czywistych. Na rysunku 3.3 widoczne błędy nie są losowe. Prognozowane wartości są niskie w okresie paru dni, a przez kolejnych kilka — wysokie. Nie mamy tu do czynienia z praw- dziwym trendem, a jedynie ze zmiennymi okresami wysokich i niskich wartości. Oznacza to, że dokładność można poprawić przez dopasowanie oparte na pomiarze błędu oszacowania z poprzedniego dnia. Błędy polegające na znacznych odchyleniach wartości w jednym kierunku oznaczać mogą występowanie długoterminowego trendu. Taka sytuacja wymagać może innego podejścia niż wykorzystywanie do pomiaru wartości tygodniowych. Jeśli błędy nie wykazują żadnych prawidłowości, najlepiej zapomnieć o trendzie, a do pro- gnozowania wykorzystywać średnią filtrowaną. W omawianym przypadku dopasujemy ją w oparciu o 1,5 wartości błędu z poprzedniego dnia. W większości przypadków nadaje to prognozie odpowiedni kierunek, nie powodując przy tym zbytniego zawyżania lub zaniża- nia szacowanych wartości. Procedura | 57 Rysunek 3.3. Analiza błędów Następnie obliczymy dopasowaną prognozę na dany dzień. Uwzględniać ona będzie aktual- ny trend, co zwiększy jej dokładność. Na rysunku 3.4 definiująca dopasowaną prognozę for- muła (komórka D17) brzmi: =C17*((1+(B16/C16))/2). Mnoży ona wartość w komórce C17 przez (1+(B16/C16))/2, gdzie B16/C16 to stosunek wartości rzeczywistej do prognozowanej. Chcemy jednak wykorzystać jedynie połowę tej liczby. Z tego względu formuła dzieli zwięk- szoną o 1 proporcję przez 2. Ogólnie dopasowana prognoza jest o 10 dokładniejsza niż fil- trowana średnia. Rysunek 3.4. Poprawianie dokładności prognozy 58 | Rozdział 3. Prognozowanie obciążenia pracą Formuła średniego błędu w kolumnie Dopasowana prognoza brzmi: {=ŚREDNIA(MODUŁ.LICZBY( B3:B16-D3:D16))}. Jest to formuła tablicowa. Tworzy ona wektor (listę) wartości bezwzględ- nych różnic między wartościami rzeczywistymi a prognozowanymi. Jako wynik podaje śred- nią tych wartości. Daje to ten sam efekt co utworzenie nowej kolumny zawierającej formułę =MODUŁ.LICZBY(B3-C3), ciągnącej się do wiersza 16, a następnie wyciągnięcie średniej z za- wartych w niej liczb. Wykorzystując formułę tablicową, wykonujemy to samo obliczenie bez dodawania 14 zbędnych formuł do arkusza. Określanie przedziału ufności Średnia dzienna rzeczywista ilość telefonów wynosi około 14 300, z kolei średni błąd dopaso- wanej prognozy wynosi 520, co jest równe mniej więcej 3,5 — oznacza to, iż prognoza okre- śla tak naprawdę pewien zakres wartości. W arkuszu Ustawienia użytkownik ustalić może przedział ufności. Aplikacja określi zakres do- pasowanej prognozy o takim prawdopodobieństwie. Jeśli przedział ufności ustawiony zosta- nie na 0,9, aplikacja wyświetli prognozę oraz modyfikator +/- równy wartości zakresu. War- tość rzeczywista znajdzie się w wybranym zakresie z prawdopodobieństwem 90 . Na rysunku 3.4 dopasowana prognoza na kolejny dzień wynosi 14 630. Wartość rzeczywista wynosić będzie 14 630 +/– 316 z prawdopodobieństwem 90 . Formuła tablicowa dla tego ob- liczenia to: {=UFNOŚĆ(0,1;ODCH.STANDARDOWE(D3:D16-B3:B16);14)}. Funkcja UFNOŚĆ jako wynik zwraca przedział ufności. Jej działanie opiera się na trzech argumentach. Pierwszy to pożądany poziom istotności. Jest to wartość błędu oczekiwanego; jeśli więc chcemy uzyskać poziom ufności równy 0,9, musimy jako parametr funkcji wpisać 1-0,9, czyli 0,1. Kolejna wartość to odchylenie standardowe. W tym przypadku obliczane jest ono na podstawie róż- nic między wartościami rzeczywistymi a dopasowanymi prognozami. Obliczenie to jest czę- ścią formuły tablicowej. Ostatni argument to ilość wykorzystywanych wartości — jest ich 14. Anomalie Problem stanowią święta, podobnie jak wszelkie duże, krótkoterminowe skoki wartości. Są one trudne do przewidzenia i zmniejszają dokładność późniejszych prognoz, ponieważ wy- korzystywane w tym rozdziale techniki bazują na zgromadzonych danych z przeszłości. Filtro- wanie średniej rozwiązuje problem zwykłych skoków wartości. Zdarzają się jednak anomalie o tak dużej skali, iż jedynym rozwiązaniem jest ich usunięcie z opracowywanych danych. Naj- lepszym przykładem są tutaj ostatnie 2 tygodnie grudnia każdego roku. Po wpisaniu wartości rzeczywistej aplikacja sprawdza błąd prognozy i oblicza prawdopodo- bieństwo przekroczenia wartości cechującej normalny rozkład błędów. Oczywiście zakłada- my tu, iż błędy mają rozkład normalny. Na rysunku 3.5 wprowadzona została wartość rzeczywista dla dnia 17-cze-98. Średni błąd dla ostatnich 14 dni wynosi 520,95. Błąd dla omawianego dnia wynosi 538. Aby stwierdzić, z ja- kim prawdopodobieństwem wartość ta stanowi anomalię, konieczne jest wyznaczenie od- chylenia standardowego dla ostatnio obliczonych błędów. Odpowiedzialna jest za to formuła tablicowa {=ODCH.STANDARDOWE(D3:D16-B3:B16)}, zawarta w komórce D23. Po raz kolejny wykorzystujemy tu formułę tablicową, aby uniknąć niepotrzebnego tworzenia dodatkowych Procedura | 59 kolumn z obliczeniami. W komórce D24 znajduje się formuła {=ŚREDNIA(D3:D16-B3:B16)}, obliczająca średni błąd. Wynik różni się od wartości w komórce D19, która określa średni błąd na podstawie wartości bezwzględnej. Rysunek 3.5. Wyszukiwanie anomalii Należy ustalić, jak bardzo (biorąc jako jednostkę odchylenie standardowe) obliczony błąd róż- ni się od średniej błędów. Wartość tę określa funkcja NORMALIZUJ. Badany błąd jest równy B17–D17. D24 to obliczona powyżej średnia błędów, a D23 to odchylenie standardowe. For- muła =MODUŁ.LICZBY(NORMALIZUJ(B17-D17;D24;D23)) w komórce D25 oblicza, jak bardzo błąd różni się od wartości średniej błędów (w odchyleniach standardowych). Chcemy również wiedzieć, jaka część rozkładu jest bardziej zbliżona do średniej niż wyzna- czony błąd. Dzięki temu będziemy mogli określić, z jakim prawdopodobieństwem jest on zbyt duży. Wykorzystując wynik działania funkcji NORMALIZUJ, formuła w komórce D26: =ROZKŁAD.NORMALNY.S(D25) wyznacza część rozkładu mieszczącą się pomiędzy średnią a war- tością w komórce D23. W omawianym tu przykładzie 86 błędów ma wartość mniejszą od średniej o 1,06 odchylenia standardowego. Prognoza może być wysoka lub niska. Z tego względu rozkład błędów ma dwa obszary krań- cowe. Ostateczną odpowiedź daje nam formuła w komórce D27, która brzmi =(D26-0,5)*2. Odejmujemy 0,5, ponieważ funkcja ROZKŁAD.NORMALNY.S bierze pod uwagę tylko jeden z krańcowych obszarów rozkładu, i mnożymy przez 2, gdyż błąd może wystąpić w dowol- nym z nich. Oszacowanie wskazuje, iż 71 błędów przyjmie wartość mniejszą niż 538. Dane z aktualnego dnia są zatem normalne. Opisane obliczenia ilustruje rysunek 3.5. Jeśli prawdopodobieństwo anomalii jest zbyt wysokie, wartość z danego dnia należy pominąć podczas obliczania prognozy. W przedstawionej w dalszej części rozdziału aplikacji użytkow- nik może ustalić stopień wykrywalności anomalii w arkuszu Ustawienia. Jeżeli jej prawdopo- dobieństwo jest wyższe od wyznaczonego stopnia, aplikacja zastępuje w obliczeniach daną wartość jej odpowiednikiem z poprzedniego tygodnia. 60 | Rozdział 3. Prognozowanie obciążenia pracą Tworzenie aplikacji Wiemy już, jak aplikacja powinna działać w teorii. Musimy jednak przełożyć nasze rozważa- nia na praktyczne rozwiązanie, które można zaproponować potencjalnemu użytkownikowi. W tej części rozdziału stworzymy za pomocą programu Excel kompletną aplikację, opartą na wcześniej opisanych procesach. Projekt W trakcie tworzenia aplikacji bardzo łatwo stracić kontrolę nad zawartymi w niej obliczenia- mi i procesami. Z tego względu ważne jest jej odpowiednie zaplanowanie i utworzenie ogól- nej struktury projektu. Przykładowa aplikacja składa się z kilku arkuszy, z których każdy ma osobną funkcję. Arkusz Dane przechowuje informacje i nie zawiera żadnych formuł. Nie jest również sforma- towany, ponieważ użytkownik nie będzie do niego zaglądał. Zawiera on jedynie dane, na podstawie których wykonywane będą wszystkie obliczenia. Do tego arkusza wprowadzane są także nowe informacje. Takie rozwiązanie ma swoje zalety: w przypadku zmiany źródła danych aktualizacji wymagać będzie jedynie interfejs między zawierającym je arkuszem a ich nowym źródłem. Wszystkie obliczenia zawarte są w arkuszu ObszarRoboczy. Tylko ten arkusz zawiera funkcje skoroszytu. Arkusz Prognoza służy do prezentacji danych. Jest połączony z arkuszem ObszarRoboczy za po- mocą nazwanych komórek i ich zakresów. Nie zawiera formuł, a jedynie odpowiednio sfor- matowane i zorganizowane informacje. W arkuszu Ustawienia dostępne są opcje aplikacji. Aplikacja zawiera również dwa specjali- styczne arkusze: Wykres, który przedstawia wykres dokładności prognozy, oraz Godziny, któ- ry zawiera tabelę prognoz tworzonych na kolejne godziny. Nie zawierają one formuł, a ich jedyną funkcją jest przechowywanie i prezentowanie informacji. Kluczową częścią aplikacji jest wyznaczanie prognoz w arkuszu ObszarRoboczy. Aby jednak stworzone rozwiązanie było kompletne i czytelne, należy wokół wspomnianego arkusza zbu- dować odpowiednią strukturę. W jaki sposób przedstawić wyniki obliczeń? Jak użytkownik może wpływać na działanie aplikacji? Odpowiedzi na tego typu pytania udziela się przez stworzenie projektu. Lista wymagań Na tym etapie konieczna jest współpraca z użytkownikiem. Twórca oprogramowania i użyt- kownik muszą ustalić, w jaki sposób aplikacja będzie wykorzystywana. W tym przypadku wymagania są następujące: 1. Tworzony system ma wyświetlać prognozy na cały tydzień. 2. Pokazywana będzie prognoza na kolejny dzień. 3. Obliczane i prezentowane będą prognozy na kolejne godziny. 4. Aplikacja obliczać będzie dokładność prognozy w oparciu o dane z ostatnich 20 dni. Tworzenie aplikacji | 61 5. Użytkownik ma mieć możliwość wprowadzania ilości telefonów dla poszczególnych godzin. 6. System powinien wykrywać anomalie i odpowiednio na nie reagować. 7. Użytkownik będzie mógł wprowadzać nowe dane oraz usuwać (poprawiać) informacje już zawarte w aplikacji. Źródło danych Dane pochodzić mogą z różnych źródeł. Można je pobrać z bazy danych SQL, strony inter- netowej utworzonej w języku XML lub po prostu wpisać. W omawianej aplikacji użytkownik wpisywać będzie dane dotyczące ilości połączeń własnoręcznie. Makra napisane w języku VBA kontrolować będą poprawność danych oraz miejsce ich zapisania. Wpisanie tych samych informacji bezpośrednio do arkusza Dane nie zakłóci jednak działania programu. Rysunek 3.6 pokazuje układ kolumn w arkuszu Dane. Użytkownik wprowadzać będzie wy- łącznie wartości w kolumnie Połączenia. Pozostałe dane obliczane będą przez samą aplikację. Arkusz ten nie zawiera żadnych formuł, a jego formatowanie nie ma znaczenia. Rysunek 3.6. Układ kolumn w arkuszu Dane Procedura usuwająca ostatni wprowadzony dzień usprawni edycję danych. W celu wyzna- czenia innego źródła danych wystarczy wprowadzić odpowiednie zmiany w kodzie VBA. Makrami zajmiemy się w dalszej części rozdziału. Prezentacja Kolejny krok to określenie sposobu prezentowania danych. Rozważyć należy trzy czynniki. Po pierwsze, najważniejsze na tym etapie są elementy informacji. Są one prezentowane jako bloki powiązanych danych. Obszar wyświetlający prognozę na poszczególne dni danego ty- godnia to jeden z takich elementów, innym przykładem może być wykres prognoz godzin- nych. Formuły i obliczenia w obszarze roboczym zależeć będą od zawartości i układu tych elementów. Istotne jest również rozmieszczenie wspomnianych elementów. Te, które są wzajemnie powią- zane, powinny się wzajemnie „wspierać”. Najważniejsze z nich należy umieścić w widocznych miejscach. Ostatni z omawianych czynników to formatowanie. Najlepszym rozwiązaniem jest 62 | Rozdział 3. Prognozowanie obciążenia pracą prosta kolorystyka, podkreślająca zawartość poszczególnych elementów. Rozmieszczenie i ko- lorystykę łatwo zmienić. Na rysunku 3.7 widoczny jest główny arkusz aplikacji, który spełnia funkcję podobną do strony głównej witryny internetowej, co pomaga użytkownikowi zrozumieć jej działanie. Dzięki te- mu wie on, czego może się po niej spodziewać i jak się nią posługiwać. Obszary wyświetlają- ce dane mają wygląd typowy dla programu Excel, co sprawia, że wyłącznie prezentowane informacje przedstawiane są czarno na białym. Nagłówki i etykiety mają znacznie mniejszy kontrast, co pozwala położyć większy nacisk na wyświetlane dane. Rysunek 3.7. Główne okno aplikacji Obszar po lewej stronie oddzielony jest za pomocą innego koloru tła. Zawiera przyciski do nawigacji i informacje podsumowujące. W głównej części okna widoczne są szczegółowe prognozy. Kolorystyka prezentacji opiera się na barwach czarnej, szarej oraz białej. Zbyt duża ilość ko- lorów mogłaby stanowić problem, ludzie bowiem lepiej rozróżniają jasne i ciemne odcienie niż poszczególne kolory; pewne połączenia barw powodują też zmęczenie oczu, a niektórzy spośród użytkowników mogą być daltonistami. Wiadomości wyróżnione za pomocą kolorów mogą również zostać utracone podczas druku. Prezentowane informacje pobierane są z obszaru roboczego za pomocą odwołań do nazwa- nych komórek i ich zakresów. Ma to kilka zalet: łatwo dzięki temu zmienić sposób wyświe- tlania danych, obszary arkusza można wycinać i wklejać w dowolnym miejscu, zachowuje to również organizację aplikacji. Omawiany arkusz nie zawiera obliczeń, a jedynie formatowanie. Aplikację uzupełniają dwa dodatkowe arkusze wyświetlające dane: wykres dokładności pro- gnoz z ostatnich dwudziestu dni oraz tabelę z prognozami godzinnymi na cały tydzień. Nie ma na nie miejsca w głównym oknie aplikacji, umieszczone więc zostały w osobnych arku- szach. Prezentowane w nich dane również pochodzą z obszaru roboczego. Tworzenie aplikacji | 63 Wykres dokładności prognoz widoczny jest na rysunku 3.8. Rysunek 3.8. Wykres dokładności Prognozy na poszczególne godziny przedstawiono na rysunku 3.9. Rysunek 3.9. Prognozy godzinne Konwencje i nazwy Omawiana aplikacja jest złożona. Nadanie nazw ważnym wartościom upraszcza formuły i uła- twia jej zrozumienie, a także rozwiązywanie problemów i poprawianie obliczeń. Innym spo- sobem kontrolowania zachodzących w programie procesów jest zastosowanie jasno zdefinio- 64 | Rozdział 3. Prognozowanie obciążenia pracą wanych konwencji w obszarze roboczym. Zastosowałem tu kilka prostych zasad: informacje pochodzące z arkusza Dane zapisane są niebieską czcionką; dane wyświetlane w arkuszach Prognoza, Wykres i Godziny umieszczane są na szarym tle; na niebieskim tle widoczne są in- formacje, które przechowywane będą w arkuszu Dane; komórki zawierające obliczenia mają pogrubione kontury; wartości wykorzystywane w innych obliczeniach i makrach umieszczo- ne są w kolumnie A i mają odpowiednie nazwy. Nazwy wartości i zakresów w arkuszu Ustawienia Przedstawiony na rysunku 3.10 arkusz Ustawienia zawiera opcje, które użytkownik może zmieniać. Rysunek 3.10. Arkusz Ustawienia W aplikacji zastosowano nazwane komórki i ich zakresy celem wyodrębnienia interfejsów między arkuszami. Nazwy wspomnianych komórek i zakresów zdefiniowane są w arkuszu Ustawienia. Nagłówek (B1) Wpisany w tym polu nagłówek wyświetlony zostanie u góry arkusza Prognoza. Długość cyklu (B2) Nazwa komórki wyjaśnia wszystko. W tym przypadku bazujemy na 5-dniowym tygodniu roboczym, więc długość cyklu wynosi 5. Aplikacja wykorzystywać może dowolną wartość między 2 a 7. Badana wartość (B3) Tu wpisać należy nazwę prognozowanej wartości. W naszym przypadku jest to ilość po- łączeń telefonicznych, ale w zależności od wykonywanej pracy może to być ilość wysta- wianych rachunków, zamówień, sprzedanych samochodów lub inny dowolny parametr. Wpisana nazwa wykorzystywana jest w nagłówkach i etykietach. Poziom ufności (B4) Poziom ufności określa prawdopodobieństwo wykorzystywane przy określaniu zakresu dopasowanej prognozy. Wykrywalność anomalii (B5) Aplikacja oblicza prawdopodobieństwo, z jakim rzeczywista wartość badanej zmiennej na dany dzień jest anomalią. Wpisana w tym polu wartość jest wykorzystywana podczas po- dejmowania decyzji o ewentualnym zignorowaniu danego pomiaru. Tworzenie aplikacji | 65 Rozkład na poszczególne godziny (E2:K12) Obszar ten zawiera oczekiwany rozkład pracy dla kolejnych dni. Wykorzystuje 11-godzinny dzień pracy, jednak podział ten można dowolnie zmieniać. Dla każdego dnia określono, jaki procent telefonów obsłużono w ciągu poszczególnych godzin. Obliczenia te oparte są na danych z ostatnich kilku tygodni. Rozkład opisany w tej tabeli wykorzystywany jest podczas tworzenia prognozy na kolejne godziny. Przedziały (D2:D12) Kolumna ta zawiera etykiety elementów tabeli Rozkład na poszczególne godziny. W przypad- ku naszych danych są to oznaczenia kolejnych godzin. Nazwy wartości w obszarze roboczym Pierwsza część obszaru roboczego widoczna jest na rysunku 3.11. Rysunek 3.11. Obszar roboczy W tym arkuszu zawarte są wszystkie obliczenia. Nazwy wykorzystywanych wartości opisa- no w tabeli 3.3. Zakresy komórek w obszarze roboczym W tej części rozdziału skoncentrujemy się na nazwanych zakresach komórek w obszarze ro- boczym. Każdy z nich odpowiada za dane i obliczenia związane z pojedynczym aspektem tworzonej prognozy. Organizują one również dane w sposób ułatwiający ich późniejszą pre- zentację w arkuszu Prognoza. Omawiana część obszaru roboczego pokazana jest na rysunku 3.12. 66 | Rozdział 3. Prognozowanie obciążenia pracą Tabela 3.3. Opisy wartości wykorzystywanych w obszarze roboczym Nazwa wartości Formuła Last_Row (A2) {=MAX((WIERSZ(Dane!B1: B2000)*(Dane!B1:B2000 )))} MyTop (A4) =Last_Row-56 DayofWeek (A6) =DZIEŃ.TYG(C56) Tomorrow (A8) =DZIEŃ.TYG(C57) StartofWeek (A11) =57-((Tomorrow- FirstWorkDay)) FirstWorkDay (A13) {=MIN(DZIEŃ.TYG( C49:C56))} Interval (A17) {=UFNOŚĆ(1-Confidence_ Level;ODCH.STANDARDOWE( G37:G56-D37:D56);20)} Opis Formuła ta oblicza numer ostatniego wiersza wykorzystywanego w arkuszu Dane. Jest to formuła tablicowa mnożąca numery wierszy przez wartość logiczną (1 albo 0). Wartość ta wynosi 0, jeśli komórka w danym wierszu jest pusta. Wynik obliczeń wykorzystywany jest do określenia numeru wiersza, do którego mają być wprowadzone nowe dane. Obliczenia wymagają danych z 56 dni. Ta wartość wyznacza numer wiersza w arkuszu Dane, znajdującego się tuż nad pierwszym z wierszy wykorzystywanych w obliczeniach. W ten sposób formuła wykorzystująca tę wartość może zostać wprowadzona do znajdujących się poniżej komórek przez przeciągnięcie. Opisywany parametr umożliwia kontrolowanie numerów wierszy zawierających dane przesyłane z arkusza Dane do obszaru roboczego. W arkuszu ObszarRoboczy daty skopiowane z arkusza Dane umieszczone są w kolumnie C. Ponieważ wykorzystujemy dane z 56 dni, ostatni wprowadzony dzień znajdował się będzie w komórce C56. Formuła zwraca dzień tygodnia odpowiadający ostatniemu wpisowi. Aplikacja wyświetla prognozy na bieżący i następny tydzień. W tym celu konieczne jest zlokalizowanie wiersza zawierającego dane z pierwszego dnia roboczego tygodnia. Wynik opisywanej formuły jest wykorzystywany w przeprowadzanych w tym celu obliczeniach oraz podczas prezentowania informacji. Ponieważ ostatni wprowadzony dzień znajduje się w komórce C56, następny będzie wprowadzony do komórki C57. Jej wynik posłuży do obliczenia pierwszego dnia bieżącego tygodnia. Formuła określa wiersz obszaru roboczego odpowiadający początkowi bieżącego tygodnia. Wynik jej działania wykorzystywany jest w obliczeniach bazujących na danych w ujęciu tygodniowym. W omawianym przykładzie tydzień roboczy rozpoczyna się w poniedziałek. Teoretycznie jednak może się on rozpoczynać dowolnego dnia. Aplikacja wykorzystuje minimalną wartość funkcji DZIEŃ.TYG z ośmiu dni celem określenia pierwszego dnia roboczego w tygodniu. Wynik jest również wykorzystywany podczas określania początku bieżącego tygodnia. Tu definiowany jest przedział ufności dla dopasowanej prognozy. Wykorzystywana jest formuła tablicowa. Kolumna G zawiera dopasowane prognozy, a kolumna D — wartości rzeczywiste. Rozmiar próby wynosi 20. Confidence_Level to zmienna określana w arkuszu Ustawienia (Poziom ufności). Interval to element wykorzystywany podczas wyświetlania danych. Tworzenie aplikacji | 67 Tabela 3.3. Opisy wartości wykorzystywanych w obszarze roboczym (ciąg dalszy) Nazwa wartości CurrentHour (A22) Formuła {=JEŻELI(SUMA(T48:T58) 1;0;MAX((T48:T58 0) *WIERSZ(T48:T58)))} CurrentRatio (A24) =JEŻELI(CurrentHour=0;1 ;ADR.POŚR( u A22)/ ADR.POŚR( v A22)) Prediction (I9) =ZAOKR.DO.CAŁK((SUMA( I1:I8)-(MAX(I1:I8) +MIN(I1:I8)))/6) Anomaly (L43) =JEŻELI(L42 =Anomaly_ Detection;1;0) AdjustedPrediction (G57) =JEŻELI(F56=0; ;F57 *((1+(E56/F56))/2)) Opis Po wpisaniu wartości rzeczywistych dla poszczególnych godzin są one łączone z kolumną T w obszarze roboczym. Aplikacja wykorzystuje je podczas dopasowywania prognozy w trakcie dnia. Opisywana zmienna pomaga śledzić kolejne wpisy, określając ostatnią godzinę, dla której wpisano wartość rzeczywistą. Ta formuła porównuje wartość oczekiwaną z wartością rzeczywistą danego dnia (zmierzoną do aktualnej godziny). Określa, czy badany parametr jest (jak dotąd) wysoki, czy niski i jak bardzo odchyla się od normy. Wynik tych obliczeń wykorzystywany jest podczas dopasowywania prognoz na kolejne godziny. W tej komórce obliczana jest prognoza tygodniowa — filtrowana średnia. Oparta jest na obliczeniach przeprowadzanych w znajdującym się powyżej obszarze (I1:I8), zawierającym wartości rzeczywiste dla aktualnego dnia tygodnia, zmierzone w ciągu ostatnich dwóch miesięcy. Obliczona wartość jest kopiowana do kolumny D arkusza Dane po wpisaniu nowych danych. Tu wyznaczany jest znacznik anomalii dla bieżącej wartości rzeczywistej. Jest on wynikiem obliczeń przeprowadzanych w znajdującym się powyżej obszarze (L38:L42). Zmienna Anomaly_Detection ustalana jest przez użytkownika w arkuszu Ustawienia (Wykrywalność anomalii). Wynik działania formuły kopiowany jest do kolumny A w arkuszu Dane. Formuła oblicza dopasowaną prognozę na dany dzień. Prognoza tygodniowa dla tego dnia (F57) mnożona jest przez połowę obliczonej dla poprzedniego dnia proporcji błędu. Wynik obliczeń kopiowany jest do kolumny E w arkuszu Dane. Rysunek 3.12. Ten tydzień i Następny tydzień 68 | Rozdział 3. Prognozowanie obciążenia pracą ThisWeek (M3:Q9) Ten obszar zawiera nazwy dni tygodnia, daty, prognozy tygodniowe, dopasowane progno- zy oraz wartości rzeczywiste dla każdego dnia bieżącego tygodnia. Obszar w kolumnie L zawiera oznaczenia dni roboczych. Podstawowa formuła wykorzystywana w tabeli to: =JEŻELI(L3=1;ADR.POŚR( ObszarRoboczy!c StartofWeek + WIERSZ(A1)-1); ). Odniesienie do komórki tworzone jest za pomocą funkcji ADR.POŚR. Rozpoczyna się od wskazania kolumny C w arkuszu obszaru roboczego. Wykorzystuje zmienną StartofWeek oraz numer wiersza w celu zlokalizowania odpowiedniego rekordu. Formułę przeciągnię- to do znajdujących się poniżej komórek. Dane pobierane są z kolumn C, F, G oraz D. For- muła w kolumnie M, =JEŻELI(L3=1;TEKST(DZIEŃ.TYG(N3); dddd ); ), określa nazwę dnia tygodnia. Funkcja DZIEŃ.TYG zwraca numer, a funkcja TEKST przekłada go na na- zwę przypisanego mu dnia. NextWeek (N15:Q21) Ten obszar zawiera daty oraz prognozy na kolejny tydzień. Wykorzystywane są w nim te same techniki co w tabeli ThisWeek. Podstawowa formuła to =JEŻELI(L3=1;ADR.POŚR ( ObszarRoboczy!c Lag + StartofWeek + WIERSZ(A1)-1); ). Różnica polega na tym, iż w tym przypadku do zmiennej StartofWeek dodawana jest zmienna Lag. HourlyNextWeek (Z20:AG31) Tu obliczane są prognozy na kolejne godziny dla całego tygodnia. Wynik obliczeń wy- świetlany jest w arkuszu Godziny. Obliczenia wykonywane w komórkach po lewej stronie wykorzystują wartości zapisane w obszarze HourlyDist w arkuszu Ustawienia. Liczby z za- kresu S20:Y20 to tygodniowe prognozy dla każdego z 7 dni aktualnego tygodnia. W celu obliczenia prognozy na określoną godzinę prognoza na dany dzień jest mnożona przez wartości rozkładu dla poszczególnych godzin. Rysunek 3.13 przedstawia tabelę Godzinne prognozy — następny tydzień, umieszczoną w obsza- rze roboczym. Komórki wypełnione szarym tłem to opisany wyżej zakres nazwany Hourly- NextWeek. Rysunek 3.13. Godzinne prognozy — następny tydzień Hours (R48:S58) W tym zakresie komórek podane są kolejne godziny i przypisane do nich prognozy. Całość działa na takiej samej zasadzie jak HourlyNextWeek, z tym że obliczenia ograniczone są do aktualnego dnia. Do przeprowadzenia tych obliczeń konieczne jest odszukanie właściwej kolumny w arkuszu Ustawienia. Wykorzystywana formuła brzmi =ADR.POŚR( Ustawienia! ADRES(WIERSZ(A2);DayofWeek+4)). Arkusz docelowy to Ustawienia, ale w obliczeniach uwzględniany jest zarówno wiersz, jak i kolumna. Zagnieżdżona w funkcji ADR.POŚR funkcja ADRES zapewnia elastyczność odczytywania danych z dowolnego wiersza lub Tworzenie aplikacji | 69 kolumny. Zmienna DayofWeek zwiększana jest o 4, ponieważ rozkład dla kolejnych go- dzin zapisywany jest od piątej kolumny arkusza Ustawienia. AdjustedHourly (W48:W58) Ten obszar zawiera dopasowane prognozy na kolejne godziny aktualnego dnia. Wyko- rzystuje dane z obszaru Hours w celu utworzenia tabeli prognoz godzinnych w arkuszu Prognoza. Podane przez użytkownika wartości rzeczywiste wprowadzane są do kolumny T. Ogólna liczba połączeń danego dnia przedstawiana jest narastająco w kolumnie U, na- tomiast w kolumnie V ta sama operacja wykonywana jest dla wartości prognozowanych z kolumny S. Zmienna CurrentRatio (A24) to suma wprowadzonych wartości podzielo- na przez wartość oczekiwaną dla tego samego okresu. Dopasowanie prognoz dla kolejnych godzin uzyskiwane jest w wyniku mnożenia ich przez tę zmienną. Rysunek 3.14 przedstawia część obszaru roboczego odpowiedzialną za obliczenia związane z prognozami na określone godziny. Rysunek 3.14. Godziny (zakres Hours) i Dopasowanie (zakres AdjustedHourly) Weekly (H60:H63) Formuły w komórkach tego zakresu obliczają średni błąd prognozy tygodniowej oraz pro- cent błędów w ciągu ostatnich 20 dni. Wyznaczona jest tu również średnia dzienna ilość telefonów za ten sam okres. NextDay (H67:H75) Obszar ten zawiera sumaryczne informacje na temat aktualnego dnia: dopasowaną pro- gnozę, przedział ufności, prawdopodobieństwo anomalii oraz ostatnią dopasowaną pro- gnozę opartą na wartościach rzeczywistych z poszczególnych godzin. Adjusted (J60:J61) Tu obliczany jest średni błąd dla ostatnich 20 dni, a także procentowa wartość błędów dla prognozy dopasowanej. Omawiane elementy obszaru roboczego przedstawione są na rysunku 3.15. Inne ważne łącza w obszarze roboczym (C37:D56) (F37:G56) Podane zakresy zawierają dane wykorzystywane przez wykres dokładności i pochodzą z arkusza Dane. 70 | Rozdział 3. Prognozowanie obciążenia pracą Rysunek 3.15. Zakresy komórek i łącza w obszarze roboczym (T48:T58) Obszar ten połączony jest z komórkami H7:H17 w arkuszu Prognoza. Tutaj użytkownik wpisuje wartości rzeczywiste dla określonych godzin. Na podstawie tych danych przepro- wadzane są obliczenia dopasowujące prognozy dla kolejnych godzin. Tworzenie łącz do danych Wiemy już, jaka jest funkcja obszaru roboczego. Teraz należy opracować odpowiednie for- muły do obsługi danych. W pierwszej kolejności musimy połączyć z obszarem roboczym in- formacje z arkusza Dane. Aby umożliwić obsługę wszystkich możliwych długości cyklu oraz dni tygodnia, wykorzystamy funkcję ADR.POŚR. W obliczeniach wykorzystywane są infor- macje z ostatnich 2 miesięcy, a w każdym tygodniu może być maksymalnie 7 dni roboczych, tak więc minimalna ilość danych obejmować musi 56 dni. Znajdująca się w obszarze roboczym komórka o nazwie Last_Row zawiera numer wiersza, w którym zawarte są ostatnie wykorzystywane dane z arkusza Dane. Aplikacja powinna po- bierać je, zaczynając od wpisu znajdującego się 55 wierszy powyżej ostatniego. Zmienna MyTop w obszarze roboczym przechowuje numer wiersza, poniżej którego zaczyna się zakres da- nych do pobrania. Początek zakresu pobranych danych w arkuszu ObszarRoboczy znajduje się w komórce B1. Wykorzystywana formuła to =ADR.POŚR( Dane!a MyTop + WIERSZ(A1)). Odwołuje się ona do kolumny A w arkuszu Dane. Zmienna MyTop wskazuje komórkę umiesz- czoną tuż nad pierwszą komórką zakresu wykorzystywanych danych, dzięki temu formułę można kopiować do komórek znajdujących się poniżej. Do wspomnianej zmiennej dodawany jest wynik działania funkcji WIERSZ(A1). Dla argumentu A1 wynosi on 1, jednak w miarę Tworzenie aplikacji | 71 kopiowania formuły do kolejnych komórek argument zmienia się na A2, A3 itd., co sprawia, że dla każdej wartości wyznaczany jest właściwy wiersz. Tak samo wykonywane są oblicze- nia w kolumnach C i D. Wszystkie opisywane kolumny wypełniane są do wiersza 56. Kolumna E odpowiada za anomalie. Jeśli w kolumnie B znajduje się znacznik anomalii, war- tość rzeczywista na dany dzień jest ignorowana, a zamiast niej w obliczeniach wykorzysty- wana jest wartość sprzed tygodnia. Formuła brzmi =JEŻELI(B1=0;D1;ADR.POŚR( Dane!c (MyTop + WIERSZ(A1)-Lag))). Jest ona również kopiowana aż do wiersza 56. Obliczona war- tość wykorzystywana jest podczas tworzenia prognozy. Prognozy z ostatnich 20 dni wykorzystywane są w kilku obliczeniach oraz podczas tworzenia wykresu, należy je zatem umieścić w obszarze roboczym. Prognozy tygodniowe znajdują się w zakresie F37:F63, a ich dopasowane odpowiedniki — w zakresie G37:G57. W komórkach tych zastosowane zostały te same formuły co w kolumnach B, C oraz D, odwołują się one jednak do innej kolumny w arkuszu Dane. Prognozy tygodniowe w kolumnie F ciągną się do wiersza 63, ponieważ przewidujemy wartości badanego parametru na tydzień naprzód. Opisane formuły łączą obszar roboczy z arkuszem Dane. Po wprowadzeniu nowych do tego arkusza nowych informacji obszar roboczy zostaje automatycznie uaktualniony i wszystkie obliczone wartości są gotowe do wykorzystania. Visual Basic Nasza aplikacja wykorzystuje język Visual Basic dla Aplikacji (VBA — ang. Visual Basic for Applications). To narzędzie o ogromnym potencjale, które często bywa nadużywane. Gene- ralnie rzecz biorąc, najlepiej wykonywać jak największą część pracy, używając skoroszytów, a VBA wykorzystywać wyłącznie w przypadkach wykraczających poza możliwości Excela. Kod zawarty w module Module1 (domyślna nazwa modułu przypisywanego arkuszowi) wy- świetlić można za pomocą edytora Visual Basic. Aby go otworzyć, należy wybrać polecenie Edytor Visual Basic z menu Narzędzia/Makro lub nacisnąć skrót klawiszowy Alt+F11. VBA nie jest niezbędny do działania aplikacji. Najbardziej złożona z wykonywanych przez niego operacji to dodanie danych dla kolejnego dnia, a to można zrobić także ręcznie, otwie- rając arkusz Dane i wpisując wartości w odpowiednim miejscu. W omawianym przykładzie Visual Basic wykorzystany został do realizacji trzech zadań. Po pierwsze do obsługi przycisków pozwalających na nawigację między arkuszami. Przeno- szą one użytkownika do wybranych arkuszy i ustawiają widok w ich lewym górnym rogu. Działają one na takiej samej zasadzie jak zakładki arkusza, ale dają większą elastyczność w za- kresie zabezpieczania aplikacji oraz zwiększają jej wewnętrzną spójność. Fragment kodu od- powiedzialny za nawigację wygląda następująco: Sub AccuracyChart() ******************* Nawigacja To makro przenosi użytkownika do arkusza Wykres i zaznacza komórkę A1 ******************* Sheets( Wykres ).Select Range( A1 ).Select End Sub 72 | Rozdział 3. Prognozowanie obciążenia pracą Powyższy kod wykonuje tylko dwie operacje: otwiera wskazany arkusz i zaznacza komórkę A1. Następną funkcją makra jest dodawanie danych dla kolejnego dnia do arkusza Dane. Łatwo można zmodyfikować kod tak, aby aplikacja pobierała je z bazy danych SQL. Zmiennej New- Actual można przypisać wartość za pomocą dowolnej metody. Całość wygląda, być może, bardzo zawile, ale w rzeczywistości wykonywane operacje sprowadzają się wyłącznie do prze- noszenia danych. Wszystkie obliczenia wykonywane są w Excelu. Sub AddDay() ***************************************** To makro pozwala użytkownikowi wpisać wartości rzeczywiste dla kolejnego dnia Wpis zostaje sprawdzony i jeśli jest poprawny nowe dane zostają wpisane do arkusza Dane ****************************************** Dim myItem As String Dim myDate As Date Dim NewActual As Variant Dim NextRow As Integer Dim Anomaly As Double Dim Prediction As Integer Dim AdjustedPrediction As Integer Dim TheLag As Integer myItem = Range( Item ).Value Odczytanie elementu z arkusza Ustawienia myDate = Range( ObszarRoboczy!c57 ).Value Pobranie następnej daty Użytkownik wprowadza kolejną wartość rzeczywistą za pomocą okna dialogowego NewActual = InputBox( Podaj LCase(myItem) _ w dniu myDate . , Wpisywanie wartości rzeczywistej , 0) If NewActual = 0 Or NewActual = Then Exit Sub Jeśli użytkownik zrezygnuje lub nie wprowadzi danych If Not IsNumeric(NewActual) Then Czy wprowadzona wartość to liczba? MsgBox ( Należy podać wartość liczbową. ) Jeśli nie, wyświetlony zostaje komunikat Exit Sub i operacja zostaje przerwana End If If Val(NewActual) 0 Then Czy wartość jest mniejsza od 0? MsgBox ( Wartość nie może być ujemna. ) Exit Sub End If If CDbl(NewActual) Int(CDbl(NewActual)) Then Czy wartość jest całkowita? MsgBox ( Wartość musi być całkowita. ) Exit Sub End If NextRow = Range( Last_row ).Value + 1 Określanie numeru kolejnego wiersza w arkuszu Dane Range( Dane!b NextRow).Value = myDate nowa data jest umieszczana w arkuszu Range( Dane!c NextRow).Value = NewActual nowa wartość rzeczywista jest umieszczana w arkuszu TheLag = Range( Lag ).Value z arkusza Ustawienia pobrana zostaje długość cyklu Tworzenie aplikacji | 73 Prediction = Range( Preditction ).Value Prognoza zostaje zaktualizowana po wprowadzeniu nowych danych do arkusza Dane Anomaly = Range( Anomaly ).Value Dane dotyczące anomalii również zostają zaktualizowane Dane dotyczące anomalii umieszczane są w arkuszu Dane, ponieważ wykorzystywane są podczas obliczania dopasowanej prognozy Range( Dane!a NextRow).Value = Anomaly AdjustedPrediction = Range( AdjustedPrediction ).Value pobranie wartości dopasowanej prognozy tygodniowa prognoza umieszczana jest o jedną długość cyklu poniżej aktualnego dnia Range( Dane!d NextRow + TheLag).Value = Prediction dopasowana prognoza umieszczana jest w kolejnym wierszu Range( Dane!e NextRow + 1).Value = AdjustedPrediction czyszczenie obszaru zawierającego godzinne wartości rzeczywiste Range( h7:h17 ).ClearContents Range( a1 ).Select Zaznaczenie komórki A1 End Sub Ostatnie makro pozwala użytkownikowi usunąć dane dotyczące ostatniego dnia. Umożliwia to edycję danych zawartych w arkuszu. Przedstawione poniżej makro odnajduje komórki za- pełnione podczas ostatniej operacji wprowadzania danych, zaznacza je i czyści ich zawartość. Sub DeleteDay() ******************************************* To makro usuwa dane o ostatnim dniu. Wystarczy w tym celu usunąć odpowiednie wpisy w arkuszu Dane ******************************************** Dim LastRow As Integer Dim TheLag As Integer LastRow = Range( Last_Row ).Value Najpierw należy zlokalizować ostatni wiersz. Jego numer zawarty jest w osobnej komórce w arkuszu ObszarRoboczy Ta deklaracja przechowuje numer ostatniego wiersza w zmiennej o nazwie LastRow Sheets( dane ).Select Wszystkie dane, które należy skasować, znajdują się w arkuszu Dane. Zaczniemy więc od zaznaczenia tego arkusza Dane znajdują się w kolumnach A-E. Musimy jedynie zaznaczyć odpowiednie komórki i skasować ich zawartość. W przypadku kolumn A, B i C dane, które mają zostać skasowane, zawarte są w wierszu o numerze przechowywanym w zmiennej LastRow. Możemy więc zaznaczyć wszystkie trzy komórki naraz i usunąć jednocześnie ich zawartość. Na przykład, jeśli zmienna LastRow ma wartość 75, to funkcja uruchomiona zostanie z parametrem Dane!a75:c75 74 | Rozdział 3. Prognozowanie obciążenia pracą Range( Dane!a LastRow :c LastRow).ClearContents TheLag = Range( Lag ) Aby określić wiersz zawierający ostatnią prognozę tygodniową, musimy znać długość cyklu. Wartość ta jest umieszczona w odpowiednio nazwanej komórce w arkuszu Ustawienia Ta sekcja usuwa ostatnią prognozę tygodniową. Znajduje się ona o jedną długość cyklu poniżej LastRow. Range( Dane!d LastRow + TheLag).ClearContents Ostatnia dopasowana prognoza znajduje się tuż pod wierszem o numerze zapisanym w zmiennej LastRow Range( Dane!e LastRow + 1).ClearContents Sheets( Prognoza ).Select Powrót do arkusza Prognoza Range( h7:h17 ).ClearContents Czyszczenie obszaru zawierającego wartości rzeczywiste dla poszczególnych godzin Range( a1 ).Select End Sub Obszar pracy i odpowiednie makra są gotowe. Czas przejść do tworzenia interfejsu użytkownika. Formatowanie Formatowanie w omawianej aplikacji opiera się głównie na wykorzystaniu teł i obramowania. Jedynie w arkuszu Prognoza zastosowano formatowanie warunkowe. W zakresach G23:J28 oraz M23:N28 ilość wierszy zawierających dane zależna jest od ilości dni roboczych w tygo- dniu — z tego względu konieczne jest wykorzystanie formatowania warunkowego celem wyświetlania lub dodawania szarego tła do odpowiednich komórek — w zależności od tego czy są one wykorzystywane, czy nie. Arkusz prezentujący prognozy na kolejne godziny przedstawiony jest na rysunku 3.16. Za- stosowano w nim różne obramowania i desenie. Być może formatowanie zastosowane w tym arkuszu jest nieco przesadzone, spełnia jednak swoją funkcję i pomaga użytkownikowi odnaleźć poszukiwane informacje. Uruchamianie aplikacji Aplikacja napisana została w Excelu 2002 i nie wykorzystuje dodatkowego oprogramowania. Po otwarciu jej w Excelu 95 mogą wystąpić pewne problemy w zakresie formatowania, jednak wszystkie obliczenia wykonywane są poprawnie. Na początek otwieramy aplikację i wybieramy zakładkę Prognoza. W znajdującym się po le- wej stronie obszarze Kolejny dzień wartość w polu Prognoza wynosi 11 072. Jest to dopasowana prognoza na aktualny dzień. Prognozy na pierwsze 3 godziny wynoszą 392, 839 oraz 1134. Tworzenie aplikacji | 75 Rysunek 3.16. Arkusz Godziny Załóżmy, że wartości rzeczywiste zanotowane w tych godzinach to 500, 1000 oraz 1300. Na rysunku 3.17 (element 1) wartości te zostały wprowadzone w odpowiednie pola. Znajdujący się obok wykres wskazuje teraz, iż wartości rzeczywiste są wyższe od prognozowanych (ele- ment 2). Jeśli w ciągu pierwszych 3 godzin zanotowano większą (w tym przypadku o mniej więcej 18 ) od spodziewanej ilość połączeń, to można oczekiwać, iż tendencja ta utrzyma się do końca dnia. Wartość Obecne dopasow. wzrosła do 13 103. Zwiększone zostały również pro- gnozy na kolejne godziny w obszarze Prognoza godzinna. Całość przedstawiona została na rysunku 3.17. Rysunek 3.17. Wykorzystywanie arkusza Prognoza 76 | Rozdział 3. Prognozowanie obciążenia pracą Aktualny dzień to piątek 1998-08-07. Data ta widnieje w obszarze Kolejny dzień, znajdującym się po lewej stronie. W tabeli Obecny tydzień wyświetlone są pełne dane dla aktualnego tygo- dnia. Element 3 wskazuje dane dla obecnego dnia. Komórki w kolumnach Prognoza i Dopa- sow. wypełnione są wartościami prognozowanymi. Komórka w kolumnie WR jest pusta, po- nieważ dzień jeszcze się nie zakończył. Prognoza na przyszły tydzień (element 4) nie została na razie obliczona, ponieważ nie mamy jeszcze niezbędnych do jej obliczenia danych z obec- nego dnia. Klikając przycisk Prognoza godzinna, obejrzeć możemy prognozy na kolejne godziny dla obec- nego tygodnia. Przycisk Wykres dokładności wyświetla wykres wartości przewidywanych na kolejny tydzień, ich dopasowanych odpowiedników oraz wartości rzeczywistych z ostatnich 20 dni. Przykładowe dane kończą się na dniu 1998-08-06. Wartości na kolejne dni następnego tygo- dnia wynoszą: 10 864 14 711 14 997 13 255 11 972 Klikamy przycisk Nowy dzień i wpisujemy wartość 10 864. Wyświetlane dane zostaną auto- matycznie zaktualizowane. Całość widoczna jest na rysunku 3.18. Rysunek 3.18. Kolejny dzień Aktualny dzień roboczy to poniedziałek 1998-08-10, w związku z czym wszystkie obszary ar- kusza ustawione zostały na początek nowego tygodnia. Przycisk Usuń dzień spowoduje usu- nięcie z aplikacji ostatnio wprowadzonych danych. Można go wykorzystać w celu skasowa- nia niepoprawnej wartości lub powrotu do poprzedniego dnia. Tworzenie aplikacji | 77 Należy pamiętać, że omawiana aplikacja to tylko skoroszyt Excela. Wprowadzone da- ne należy zatem zapisywać tak samo jak w każdym innym arkuszu tego programu. Dopasowywanie aplikacji do własnych potrzeb W celu wykorzystania aplikacji do obróbki własnych danych, należy wyczyścić arkusz Dane i wkleić 56 rekordów zawierających określone daty oraz przypisane im wartości do kolumn B i C, poczynając od wiersza 2. W wierszu 1 umieszczone są nagłówki. Następnie w arkuszu Ustawienia wpisujemy odpowiednie dla naszych potrzeb wartości w polach Nagłówek, Długość cyklu, Badana wartość, Poziom ufności oraz Wykrywalność anomalii. Aby uzyskać ilość informacji niezbędnych do działania wszystkich narzędzi, konieczne jest wprowadzenie danych z jesz- cze jednego pełnego cyklu. Można to zrobić, używając makra AddDay lub po prostu wpisać wartości bezpośrednio do arkusza Dane. Aplikacja jest samowystarczalna i nie potrzebuje żadnego dodatkowego oprogramowania poza Excelem. W dalszej części książki zajmiemy się bardziej zaawansowanymi technikami progno- zowania, które mogłyby zostać zastosowane w omawianym projekcie poprzez wprowadzenie odpowiednich zmian w obszarze roboczym. Ulepszeń dokonać można na wiele sposobów: • Tworząc nowy arkusz i dodając nieco obliczeń w obszarze roboczym, uwzględnić można dane dotyczące zatrudnianego personelu. W arkuszu Ustawienia można następnie wpro- wadzać ilość jednostek pracy na dzień. Wartość ta zostałaby z kolei wykorzystana do ob- liczania wymaganej ilości pracowników. • Makro AddDay można ulepszyć tak, aby aktualizowało zakres komórek Rozkład na po- szczególne godziny, umieszczony w arkuszu Ustawienia. W ten sposób oczekiwany rozkład pracy na kolejne godziny byłby zawsze aktualny. • Wykres dokładności mógłby zostać zastąpiony wykresem przestawnym, umożliwiającym bardziej precyzyjną analizę dokładności prognozowania. • Ulepszyć można formatowanie arkusza Prognoza, a umieszczone w nim elementy zawie- rające informacje mogłyby zostać przemieszczone za pomocą poleceń Wytnij i Wklej. Zmia- na samych elementów jest bardziej złożona i wymaga ingerencji w obliczenia przeprowa- dzane w obszarze roboczym. Kluczem do skutecznego ulepszania aplikacji jest przestrzeganie ustalonych konwencji. Obli- czenia powinny pozostać w obrębie obszaru roboczego, nowy rodzaj danych najlepiej umie- ścić w arkuszu Dane, a nowe parametry — w arkuszu Ustawienia. Wszystkie wykorzystywa- ne w aplikacji elementy należy też odpowiednio nazwać. 78 | Rozdział 3. Prognozowanie obciążenia pracą
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Excel. Analiza danych biznesowych
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: