Controlling operacyjny w przedsiębiorstwie handlowym - lab. z programu Excel 2007
Wydział Zarządzania, WZ, UTP, Bydgoszcz
=========================================================
=========================================================
=========================================================
Dzisiejsze laborki polegały na bardziej zaawansowanych działaniach na narzędziu: "Tabela przestawna".
Przypominam sposób uruchomienia:
Otwieramy plik "Tp_cz1_puste.xlsx" i klikamy w którąkolwiek komórkę z danymi, następnie wchodzimy w menu "Wstawianie" i wybieramy "Tabela przestawna".
Pojawi się okno:
Nic w nim nie zmieniamy, bo obszar danych zaznaczony jest automatycznie i zazwyczaj się nie myli (dlatego przed włączeniem tabel przestawnych trzeba było kliknąć gdzieś w dane).
Wybieramy zawsze nowy arkusz, aby nie robić sobie syfu w pliku źródłowym. Klikamy OK.
Na poprzednich zajęciach uczyliśmy się czym są tabele przestawne i do czego się mogą przydać, przykładowo do stworzenia zestawień, jakie robiliśmy przy pomocy opcji "Sortuj i filtruj".
Teraz te tabele (z końca lekcji) oraz kilka innych zbudujemy przy pomocy tabel przestawnych.
!!! Ważne !!!
Zanim przystąpimy do wykonania zadań, jedna ważna kwestia, dotycząca uzupełniania tabel przestawnych.
Klasyczny sposób to chwytanie danej kategorii (miar i wymiarów), czyli np. wartości, regionu, czy też sprzedawcy i przeciąganie tej kategorii nad któreś z 4 pól tabeli przestawnej, czyli:
- Upuść pola stron tutaj (wymiary)
- Upuść pola kolumn tutaj (wymiary)
- Upuść pola wierszy tutaj (wymiary)
- Upuść elementy danych tutaj (miary)
Ale te same kategorie możemy upuszczać w inne miejsce w ramach okna "Lista pól tabeli przestawnej":
Po pierwsze, przyda się to do zadania numer 3, ale to już przeczytacie poniżej.
Po drugie, jest o tyle łatwiejsze, iż gdy mamy więcej danych wrzuconych już w tabelę przestawną, późniejsze modyfikacje w ramach tabeli mogą być nieco utrudnione przez mniejszą czytelność. A tak możemy przestawiać wymiary i miary pomiędzy polami:
- Filtr raportu
- Etykiety kolumn
- Etykiety wierszy
- Wartości
oraz
- Wybierz pola, które chcesz dodać do raportu
Przestawiając kategorie między tymi pięcioma okienkami (w obrębie okna: "Lista pól tabeli przestawnej"), kursor także zmienia się, tak jak gdy operowaliśmy kategoriami bezpośrednio nad tabelą przestawną. Dla przypomnienia, kursor wygląda tak:
...i gdy znajdujemy się nad którymś z pól, podświetla się jedno z nich.
Lista pól, które sobie odpowiadają:
- Upuść pola stron tutaj - Filtr raportu
- Upuść pola kolumn tutaj - Etykiety kolumn
- Upuść pola wierszy tutaj - Etykiety wierszy
- Upuść elementy danych tutaj - Wartości
Zadanie 1
Zadanie będzie polegało na stworzeniu 4 tabel, z których każda będzie wykonywała inne działanie arytmetyczne na danych:
- Suma
- Średnia
- Maksimum
- Licznik
Najpierw stwórz taką tabelę przestawną:
Zwróć uwagę, gdzie znajdują się poszczególne miary i wymiary, czyli nasze kategorie (na rysunku oznaczone "!"). Kolory tła, pogrubianie zastosuj jakie chcesz.
Aby tabela była bardziej czytelna, robimy dwie rzeczy.
Zaznaczamy obszar wartości, klikamy prawym przyciskiem myszy i wybieramy: "Formatuj komórki...".
Przechodzimy do zakładki "Liczby", zaznaczamy "Liczbowe" oraz "Użyj separatora 1000 ()".
W tym samym oknie przechodzimy do zakładki "Wyrównanie", w poziomym wyrównaniu tekstu wybieramy "Prawa (Wcięcie)", a wartość wcięcia ustawiamy na "1". Klikamy OK.
Teraz zaznaczamy obszar od komórki F10 do A1, klikamy PPM i wybieramy "Kopiuj" i wklejamy poniżej tę tabelę, zostawiając 2-3 wiersze przerwy. Potem znowu przerwa, wklej, przerwa, wklej. W tym momencie powinniśmy mieć w tym arkuszu 4 tabele przestawne.
Najpierw w następnych tabelach zmieniamy tą komórkę:
Na odpowiednio:
- tabela druga: Funkcja - Średnia
- tabela trzecia: Funkcja - Max
- tabela czwarta: Funkcja - Licznik
Ale sama zmiana nazwy nie spowoduje zmiany rodzaju działania arytmetycznego w danej tabeli, dlatego też trzeba to zmienić.
Będąc w tabeli 2 (Średnia) klikamy gdzieś w polu wartości, potem klikamy PPM i wybieramy "Ustawienia pola wartości...".
W oknie, które się pojawiło wybieramy "Średnia" zamiast sumy, a w polu "Nazwa niestandardowa" możemy sobie wpisać własną nazwę dla tej tabeli, która wyświetli się w jej lewym, górnym rogu.
Tak samo robimy dla pozostałych tabel, zamieniając sumę odpowiednio na "Maksimum" i "Licznik".
Gotowe tabele wyglądają tak:
W tabeli "Licznik" warto zmienić liczbę miejsc po przecinku na "0". (PPM, Formatuj komórki, zakładka - Liczby, miejsca po przecinku na 0).
Zadanie 2
W tym zadaniu będziemy obliczać wartości procentowe dla działania arytmetycznego: Suma.
Najpierw tworzymy nową tabelę przestawną w nowym arkuszu, z tego samego pliku źródłowego co poprzednio. Tabela ma wyglądać tak:
Formatowanie, pogrubianie, kolory tła dobieracie takie, jakie chcecie.
Tabelę kopiujemy 2 razy, tak jak w poprzednim zadaniu. Ostatecznie mamy więc 3 tabele.
W drugiej zmieniamy napis w polu:
% wiersza na % kolumny
W tabeli trzeciej napis:
% wiersza na % sumy
Teraz klikamy w pole wartości w pierwszej tabeli przestawnej, potem klikamy PPM, wybieramy "Ustawienia pola wartości...".
W oknie, które się pojawi przechodzimy do zakładki
"Pokazywanie wartości jako", z listy rozsuwanej wybieramy "% wiersza" i klikamy OK.
Teraz pierwsza tabela przedstawia się tak:
Dla pozostałych tabel robimy tak samo tylko wybieramy odpowiednio:
- % kolumny (dla tabeli 2)
- % sumy (dla tabeli 3)
Gotowe tabele 2 i 3 wyglądają tak:
Zadanie 3
W tym zadaniu będziemy zestawiać sumę, średnią, maksimum, licznik i % sumy, tyle, że tym razem w jednej tabeli.
Tak jak poprzednio tworzymy nową tabelę przestawną w nowym arkuszu.
Najpierw stwórz taką tabelę:
Teraz trzeba przekopiować 5 razy "Wartość" na pole "Upuść elementy danych tutaj".
I tutaj właśnie się przyda drugi sposób uzupełniania tabeli przestawnej, o którym wspomniałem na początku.
Jeśli teraz 5 razy przeniesiesz "Wartość" nad tabelę przestawną i je tam upuścisz to powinieneś(aś) otrzymać coś takiego:
Jeśli nie wyszło tak jak to widzisz na obrazku powyżej, to coś zrobiłeś(aś) źle. Najprawdopodobniej "Wartość" powędrowała do innego pola, niż powinna lub Excel przyjął swój domyślny sposób formatowania i wyświetla dane w sposób jaki my nie chcemy.
Na początek sprawdź, czy masz uzupełnione te obszary tak samo jak ja:
Jeśli nie, to poprzestawiaj je tak, aby wszystkie miary i wymiary wyglądały tak jak powyżej.
Póki co nasza tabela wygląda tak:
Teraz trzeba zmienić typy obliczeń arytmetycznych, dla każdej z kolumn danych.
Klikamy na komórkę B7, klikamy PPM, wybieramy "Ustawienia pola wartości". Jako typ obliczeń pozostawiamy sumę, natomiast zmieniamy nazwę kolumny na "Wartość sprzedaży". Klikamy OK.
Klikamy na komórkę C7, klikamy PPM, wybieramy "Ustawienia pola wartości". Jako typ obliczeń wybieramy "Średnia", nazwę kolumny zmieniamy na "Średnia wartość sprzedaży". Klikamy OK.
Klikamy na komórkę D7, klikamy PPM, wybieramy "Ustawienia pola wartości". Jako typ obliczeń wybieramy "Maksimum", nazwę kolumny zmieniamy na "Najwyższa transakcja". Klikamy OK.
Klikamy na komórkę E7, klikamy PPM, wybieramy "Ustawienia pola wartości". Jako typ obliczeń wybieramy "Licznik", nazwę kolumny zmieniamy na "Liczba transakcji". Klikamy OK.
Klikamy na komórkę F7, klikamy PPM, wybieramy "Ustawienia pola wartości". Przechodzimy do zakładki "Pokazywanie wartości jako", z listy wysuwanej "Pokaż wartości jako" wybieramy "% sumy", a jako nazwę kolumny wpisujemy "Udział". Klikamy OK.
Tabelę odpowiednio formatujemy: wcięcia, miejsca po przecinku itd. Ostatecznie tabela wygląda tak:
Należy też pamiętać, że w powyższej tabeli jest tylko jedna miara, a nie pięć, gdyż:
- Wartość sprzedaży
- Średnia wartość sprzedaży
- Najwyższa transakcja
- Liczba transakcji
- Udział
są obliczane na podstawie tej samej miary, czyli dla nas jest to "Wartość".
!!! Ważne !!!
Nazwy kolumn są tutaj bardzo istotne, gdyż sugerują odpowiedź jaka może paść na kolokwium, np.:
- jaka była najwyższa transakcja na zakup batonów?
- ile było transakcji sprzedaży zapiekanek?
- Jaki udział w przychodzie lub zysku miała sprzedaż pizzy w 2006 roku?
Plik do pobrania: