Controlling operacyjny w przedsiębiorstwie handlowym - lab. z programu Excel 2007
Wydział Zarządzania, WZ, UTP, Bydgoszcz
=========================================================
=========================================================
=========================================================
Pierwsze zajęcia i prawdopodobnie kolejne z przedmiotu "Controlling operacyjny w p. h." będą prowadzone w oparciu o plik:
"Tp_cz1_puste.xlsx"
Jako, że nie posiadam praw autorskich do niego, nie będę go zamieszczał na końcu którejkolwiek lekcji, a jedynie swoje własne pliki. Każdy z Was jednak na pewno plik ten otrzyma na zajęciach.
Oto nazwy kolumn i przykładowe kilka wierszy:
Na zajęciach poznaliśmy funkcję "Sortuj" oraz "Filtruj", po to by do przygotowanych tabel wprowadzić wszystkie dane, które spełniają dany warunek, np. wartość sumy sprzedaży w roku 2006.
Sortowanie
Najpierw tworzymy nowy arkusz w otwartym już pliku
i w nim tworzymy następującą tabelę:
Aby zacząć sortowanie, klikamy na którąkolwiek komórkę, gdzie są dane, następnie w zakładce "Narzędzia główne" wybieramy "Sortuj i Filtruj", a następnie "Sortowanie niestandardowe".
W oknie, które się pojawi zrób następujące rzeczy:
- Zaznacz: "Moje dane mają nagłówki"
- Sortuj według: Rok
- Sortowanie: Wartości
- Kolejność: Od najmniejszych do największych
- Klikasz OK
Szczególnie opcja "Moje dane mają nagłówki" jest bardzo istotna z trzech względów:
- wiersz z nagłówkami nie jest uwzględniany przy sortowaniu
- Excel pamięta tą opcję i możemy skorzystać z szybkiego sortowania - Sortuj od A do Z i opcja odwrotna (ale o tym później)
- w sortowaniu niestandardowym dla opcji "Sortuj według" w menu rozwijanym pojawiają się nazwy kolumn, zamiast nazw standardowych, jak "Kolumna A, Kolumna B... "
Szybkie sortowanie to skorzystanie z opcji:
- Sortuj od A do Z
- Sortuj od Z do A
Teraz nie musimy za każdym razem wybierać opcji "Sortowanie niestandardowe" dla prostych sortowań. Teraz jedynie zaznaczamy nagłówek kolumny np. "Sprzedawca" i bierzemy którąś z tych dwóch opcji i automatycznie Excel sortuje wiersze po kryterium, jakim jest alfabetyczne posortowanie nazwisk sprzedawców.
Zatem, aby wypełnić pierwszą tabelkę musimy posortować wiersze według roku, dlatego klikamy na nagłówek "Rok" -> "Sortuj i Filtruj" -> "Sortuj od A do Z".
Teraz zjeżdżamy w dół do momentu, gdy wartość 2006 zmienia się w 2007. Klikamy Prawym PM na boczną listwę (z numerami wierszy) na pierwszy wiersz, który zawiera rok 2007 i wybieramy "Wstaw".
Pojawił się nowy pusty wiersz, a w nim klikamy komórkę, która znajduje się w kolumnie E, następnie klikamy na "Autosumowanie" (w zakładce Narzędzia główne).
Excel automatycznie wybierze obszar, który trzeba zsumować, od zaznaczonego wiersza wzwyż. Klikamy Enter.
Czynność tę powtarzamy dla lat 2007 i 2008. Autosumowanie dla np. 2007 roku sumuje wszystko wzwyż, aż natrafi na poprzednie zsumowanie, dotyczące 2006 roku. Analogicznie dla 2008 roku zsumowane zostało wszystko do wiersza z sumą dotyczącą 2007 roku.
Teraz te trzy obliczenia musimy przekopiować do tabeli utworzonej wcześniej w nowym arkuszu.
Jednak, tu, kopiujemy formułę, więc przy wklejaniu wyskoczy błąd, dlatego musimy użyć opcji wklejania specjalnego.
Klikamy na komórkę "B2" PPM, wybieramy "Wklej specjalnie..."
A następnie w opcji "Wklej" wybieramy "Wartość" i potem OK.
Tak samo wklejamy wartości dla lat 2007 i 2008.
Komórka B5 to suma tych trzech wartości. Klikamy na nią, wybieramy "Autosumowanie" i klikamy Enter.
Przechodzimy do komórki C2 i wpisujemy:
"=B2/$B$5"
i formułę przenosimy do komórek, które są niżej, do C5 włącznie. Zaznaczamy teraz komórki od C2 do C5, klikamy PPM, wybieramy "Formatuj komórki", wchodzimy w zakładkę "Liczby" i wybieramy "Procentowe".
Gotowa tabela wygląda tak:
Teraz tworzymy kolejny arkusz w tym dokumencie Excel'a i tworzymy taką tabelę:
Postępujemy tak samo, jak przy poprzedniej tabeli, tyle, że tu sortujemy dane ze względu na Region.
Uzupełniona tabela wygląda tak:
Filtrowanie
Tworzymy kolejny arkusz, a w nim tabelę:
Aby uzupełnić tę tabelę, użyjemy opcji "Filtruj". Klikamy gdzieś, gdzie są dane i wybieramy tę funkcję z menu "Sortuj i filtruj".
Po jej wybraniu, przy nagłówkach kolumn pojawiają się strzałeczki, których kliknięcie wysuwa listę z opcjami.
- klikamy na strzałeczkę w kolumnie: Sprzedawca
- klikamy na Zaznacz wszystkie, co spowoduje odznaczenie wszystkich nazwisk
- wybieramy nazwisko: Sylwiak
- klikamy OK
To samo dla kolumny Region
- klikamy na strzałeczkę w kolumnie: Region
- klikamy na Zaznacz wszystkie, co spowoduje odznaczenie wszystkich regionów
- wybieramy region: Wschód
- klikamy OK
Teraz na dole, zaraz pod ostatnim wierszem, klikamy na komórkę w kolumnie E i klikamy na "Autosumowanie" i Enter. Mamy sumę wartości sprzedaży, dla nazwiska Sylwiak i regionu Wschód.
Kopiujemy wartość i wklejamy do tabeli 3, w komórce Sylwiak/Wschód. Pamiętajmy, aby użyć opcji "Wklej specjalnie...", wybrać "Wartości" i OK.
Nie likwidujemy formuły sumowania częściowego, tylko przechodzimy do góry i zmieniamy filtr regionu, zostawiając tym razem zaznaczoną tylko opcję: "Zachód".
Teraz autosumowanie sumuje tylko dla nazwisko Sylwiak i regionu Zachód. Jest to duże ułatwienie, gdyż nie musimy wstawiać nowych wierszy i szukać miejsc, gdzie zmieniają się nazwiska lub regiony.
Trzeba pamiętać, aby w danym momencie był w filtrze zaznaczony tylko jeden region i tylko jeden sprzedawca.
Gotowa tabela 3 wygląda następująco:
Oczywiście nie muszę już tłumaczyć, że wiersz i kolumna "Razem" to suma tego co jest odpowiednio w danej kolumnie lub wierszu. Komórka F6 to suma kolumny "Razem" lub wiersza "Razem".
Na koniec w arkuszu od prowadzącego wybieramy opcję "Wyczyść".
Niektóre grupy zrobiły lub zrobią przedstawienie tabeli numer 3 w wartościach procentowych.
Pozostając w arkuszu, kopiujemy tabelę i umieszczamy kilka wierszy niżej, czyszcząc dane liczbowe oraz formatując wszystkie komórki różowe do formatu procentowego. (Zaznacz komórki różowe, PPM, Formatuj komórki, Zakładka - Liczby, Procentowe).
Potem kopiujemy tabelę jeszcze raz i wklejamy znowu kilka wierszy niżej. Podobnie jak poprzednio przerabiamy pola różowe na format procentowy.
Te dwie tabele procentowe musimy przerobić. W pierwszej usuwamy kolumnę "Razem", a w drugiej wiersz "Razem".
Wszystko wygląda tak:
W pierwszej tabeli procentowej w polu Sylwiak/Wschód wpisujemy formułę:
"=B2/B$6"
Jeden dolar został zastosowany, gdyż zależy nam, aby zmieniały się symbole/wartości kolumny, a wartości wierszy pozostały bez zmian.
Formułę przenosimy na pozostałe trzy komórki w tym wierszu, a następnie zjeżdżamy w dół z tą formułą także o trzy wiersze i mamy:
W drugiej tabeli procentowej w komórce Sylwiak/Wschód wpisujemy formułę:
"=B2/$F2"
Jeden dolar został zastosowany, gdyż zależy nam, aby zmieniały się symbole/wartości wierszy, a symbole/wartości kolumny pozostały bez zmian.
Podobnie jak poprzednio przenosimy formułę w bok, a następnie na dół w obrębie pól jasno różowych.
Gotowe tabele wyglądają tak:
Pierwsza z nich mówi jaki procent całego swojego zysku wypracował sobie dany sprzedawca w poszczególnych regionach.
Druga tabela mówi jaki procent rynku (jaką sprzedaż) w danym regionie osiągnął każdy ze sprzedawców.
Pliki do pobrania
Plik Excel - Uzupełniony
Plik Excel - Bez danych liczbowych