Controlling operacyjny w przedsiębiorstwie handlowym - lab. z programu Excel 2007
Wydział Zarządzania, WZ, UTP, Bydgoszcz
=========================================================
=========================================================
=========================================================
Ostatnie laborki z tabel przestawnych.
Zadanie 1
Zadanie numer 1 polegało na stworzeniu takiej tabeli przestawnej na podstawie pliku z danymi: "Tp_cz1_puste.xlsx".
Na początek w obszar "Upuść pola stron tutaj" lub "Filtr raportu" wrzucamy kategorie (wymiar) "Rok" i "Miesiąc".
Teraz w obszar "Upuść pola kolumn tutaj" lub "Etykiety kolumn" wrzucamy kategorię (wymiar) "Rodzaj", natomiast w obszar "Upuść pola wierszy tutaj" lub "Etykiety wierszy" wrzucamy kategorię "Region".
Teraz w obszar "Upuść elementy danych tutaj" lub "Wartości" wrzucamy 3 razy kategorię (miarę) "Wartość".
Przypominam, że wrzucając kategorię (miarę) "Wartość" bezpośrednio do tabeli przestawnej kursor powinien mieć zapalony ten obszar:
Powstanie taka tabela:
Jak widać ta tabela przestawna jest bardzo nieczytelna. Po pomniejszeniu mamy coś takiego:
Rozwiązaniem jest przeniesienie "Wartości" do obszaru wierszy.
!!! Ważne !!!
To przenoszenie jest bardzo ważne i wiele osób miało z tym problem. Dlatego zapamiętajcie to bardzo dokładnie.
Klikamy i trzymamy nazwę miary w tabeli przestawnej, czyli "Wartości" i przenosimy ją w obszar "Upuść pola wierszy tutaj". Pamiętaj, że miary mogą mieć różne nazwy w różnych tabelach.
Kursor powinien zmienić podświetlanie, z pola 3 na pole 2.
Powstała nam zatem taka tabela, która jest dużo bardziej czytelna:
Teraz klikamy 2 razy LPM na pole "Suma z Wartość" (komórka B6).
Jako "Nazwa niestandardowa" wpisujemy: Wartość sprzedaży.W zakładce "Podsumowanie według" pozostawiamy sumę. Klikamy OK.
Klikamy 2 razy LPM na pole "Suma z Wartość 2" (komórka B7).
Jako "Nazwa niestandardowa" wpisujemy: Procent obrotu regionu.W zakładce "Pokazywanie wartości jako" wybieramy "% wiersza". Klikamy OK.
Klikamy 2 razy LPM na pole "Suma z Wartość 3" (komórka B8).
Jako "Nazwa niestandardowa" wpisujemy: Procent obrotu towaru.W zakładce "Pokazywanie wartości jako" wybieramy "% kolumny". Klikamy OK.
Gotowa tabela wygląda tak:
Zadanie 2
Ściągnij ten plik. Zawiera on przygotowaną w czasie zajęć tabelę z danymi.
Uwaga!!! Każda grupa może mieć na zajęciach nieco inną tabelę, niż pozostałe.
Komórki od A1 do H1 są rozszerzoną informacją o tym, czym konkretnie są dane kolumny, ale na zajęciach nie było za dużo czasu na spisanie pełnego opisu. Ale mi się udało :)
Na podstawie tych danych tworzymy nową tabelę przestawną (musi być kliknięta jakaś kolorowa komórka tabeli).
Pamiętaj, że komórki A1 do H1 nie mogą być brane pod uwagę przy tworzeniu tabeli przestawnej. Stąd też wstawiłem jeden pusty wiersz, aby narzędzie "tabela przestawna" automatycznie je ominęła.
Nowa tabela wygląda tak:
Dla tej tabeli wymiary to:
- Sprzedawca
- Towar
Miary to:
- Przychody
- Zmniejszenia
- Koszty I
- Marża I
- Koszty II
- Marża II
Musimy stworzyć taką tabelę przestawną:
Na początek w obszar "Upuść pola wierszy tutaj" lub "Etykiety wierszy" wrzucamy kategorie (wymiary) "Sprzedawca" i "Towar".
Teraz w obszar "Upuść elementy danych tutaj" lub "Wartości" wrzucamy razy kategorie (miary):
- Przychody
- Marża I
- Marża II
Formatujemy według własnego uznania i mamy:
Zadanie 3
Tworzymy kopię poprzedniej tabeli i przedstawiamy w niej zestawienia procentowe.
Kopiujemy poprzedni arkusz z tabelą przestawną do nowego arkusza.
(Kliknij na nazwę arkusza PPM, wybierz "Przenieś lub kopiuj...", zaznacz "(przenieś na koniec)", zaznacz "Utwórz kopię", kliknij OK)
Kliknij 2 razy LPM w tabeli przestawnej na nazwę pierwszej miary, czyli na "Przychody", (komórka C4).
Klikamy zakładkę "Pokazywanie wartości jako" oraz wybieramy "% kolumny".
Tak samo czynimy dla "Suma z Marża I" oraz "Suma z marża II".
Gotowa tabela wygląda tak:
Zadanie 4
W tym zadaniu poznamy, jak tworzyć bardziej zaawansowane przedstawianie danych za pomocą narzędzia "Formuły". Będziemy tu obliczać wskaźniki z marży, ale nie zapisałem sobie objaśnienia co one oznaczają.
Kopiujemy poprzedni arkusz do nowego arkusza i z tabeli przestawnej usuwamy wszystkie miary, czyli przychody, marżę I oraz marżę II.
Teraz klikamy w tabeli przestawnej gdzieś w polu danych, tak, aby w zakładce "Opcje" była aktywna funkcja "Formuły".
Klikamy na "Formuły" i wybieramy opcję "Pole obliczeniowe".
Pojawia się takie okno:
Jako nazwę wpisujemy "Wskaźnik z MB 1".
Teraz przechodzimy do okna "Formuła" i zostawiamy sam znak równości "=".
W oknie "Pola" klikamy na "Marża I", przycisk "Wstaw pole", z klawiatury wpisujemy znak dzielenia "/", powracamy do okna "Pola", klikamy na "Przychody", przycisk "Wstaw pole".
Klikamy przycisk "Dodaj".
Zmieniamy nazwę na: "Wskaźnik z MB 2".
W oknie "Pola" klikamy na "Marża II", przycisk "Wstaw pole", z klawiatury wpisujemy znak dzielenia "/", powracamy do okna "Pola", klikamy na "Przychody", przycisk "Wstaw pole". Klikamy OK.
Po dodaniu wskaźników pojawiają się one w oknie "Lista pól tabeli przestawnej", a sama tabela wygląda tak:
Wygląda to tak, jakby coś poszło nie tak, ale wystarczy zaznaczyć komórki liczbowe i zmienić ich formatowanie z liczbowego na procentowe.
(Zaznacz komórki, PPM, Formatuj komórki..., zakładka Liczby, opcja Procentowe, OK).
Gotowa tabela przestawna wygląda tak:
Pliki do pobrania:
CO-PJ-4.xlsx
Wzór 1