Jak podsumować dane z produkcji?
Z poprzednich artykułów wiecie już, jak zbudować dobrą bazę danych, a także orientujecie się, w jaki sposób dane zbierać i które z nich uznać za kluczowe. Czas teraz, zastanowić się nad kolejnym istotnym aspektem – jak można uzyskane informacje wykorzystać.
Na wstępie pozwolę sobie przypisać męskiej części czytelników, rolę w pewnej scence rodzajowej. Wyobraźcie sobie, że pewnego popołudnia wasza druga połowa, patrząc na salon w domu, stwierdza „przydałoby się lekkie przemeblowanie”. Wiecie już, że użyty w tym zdaniu epitet nijak ma się do faktycznych planów, a argumenty, straż pożarna, policja i czarna magia nie są w stanie odwieść partnerki od jej pomysłu. Generalnie rzecz biorąc czeka was dużo roboty. Przewińmy całą procedurę do momentu, kiedy wszystko jest gotowe. Kanapa jako ostatni element staje na swoim miejscu, a Ty możesz wreszcie otrzeć pot z czoła. Spoglądasz bohatersko na inicjatorkę przedsięwzięcia i widzisz na jej twarzy wyraz głębokiego zamyślenia połączony z grymasem niezadowolenia. Po chwili słyszysz „Wiesz co, regały powinny jednak stać po drugiej stronie”.
Co ta anegdotka ma wspólnego z tematem dzisiejszego artykułu? Jak zwykle – sporo. Zbliża się początek miesiąca, a Ty musisz przygotować raport wyników twojego wydziału na spotkanie podsumowujące. Bierzesz, więc na warsztat dane, które zgromadziłeś w ciągu poprzedniego miesiąca i tworzysz zestawienie. Załóżmy, że będzie to raport dotyczący sprzedaży produktów. Tworzysz więc spis, w którym prezentujesz sumaryczną ilość i wartość sprzedanych produktów. Wyliczasz procentowe udziały każdego produktu, grupujesz je w kategorie i wizualizujesz wyniki na wykresach. Przedstawiasz przygotowane zestawienia na podsumowaniu i widzisz na twarzy prezesa ten sam wyraz głębokiego zamyślenia co u twojej partnerki po zakończony przemeblowaniu. Po chwili z ust Twojego kierownika pada pytanie „A co gdyby…”
No właśnie, gdyby co?
Gdyby prezes poprosił o pokazanie danych sprzed dwóch miesięcy, a nie tylko z poprzedniego? Nie posiadasz takich informacji, bo wyciągnąłeś dane tylko za ubiegły miesiąc.
Gdyby prezes poprosił o zestawienie TOP 3 produktów? Musiałbyś przerabiać wykresy ręcznie.
Gdyby prezes poprosił o odseparowanie konkretnych kategorii produktów? Kolejny raz musisz modyfikować raport, który i tak pochłonął już sporo Twojego czasu.
Widzisz analogię? Zupełnie jak w anegdotce z przemeblowaniem. Jedna wątpliwość, jedno pytanie i musisz powtarzać lwią część pracy. Chyba, że znasz dobrze Excela, a w szczególności tabele przestawne.
Dzięki tabelom przestawnym możesz kilkoma kliknięciami tworzyć elastyczne tabele danych, zliczające wystąpienia, sumujące dane i wyliczające średnie w zadanym przez ciebie zakresie kryteriów.
Zanim pokażę, jak tworzyć tabele przestawne, chciałbym, abyś uświadomił sobie, jak wiele tracimy nie stosując tej funkcjonalności arkusza kalkulacyjnego. Do tego celu posłużymy się przykładem bazy z poprzedniego tygodnia.
Załóżmy, że potrzebujemy stworzyć zestawienie prezentujące ilość braków powstałych na gięciu i spawaniu, ale tylko w styczniu.
Teoretycznie można to zrobić wertując po kolei każdy wiersz i sumować bądź przepisywać do innej tabeli pozycje spełniające warunki (patrz: Baza braków – 2).
Zwróć uwagę na kolejność występowania dat – nie są ułożone w logicznym porządku. Eksportując dane z innego systemu np.: ERP do Excela, warto je najpierw odpowiednio przygotować.
Informacje zawarte w tym przykładzie zostały wyodrębnione z osobnego systemu i nie pomyślano o sprawdzeniu użytych filtrów danych. W konsekwencji stworzona została tabela z nieposortowanymi rekordami. Gdybyś chciał przygotować zestawienie według powyższej instrukcji, konieczne byłoby wybieranie z danych nie tylko nazwy stanowisk, ale i pojedynczej daty.
Na szczęście dla Excela nieposortowane dane nie stanowią problemu i można je opracować w zaledwie kilka sekund. Sprawa ma się jednak inaczej, jeżeli w wyniku niedopatrzenia wyeksportowałbyś niekompletne dane. Pamiętaj, że nawet najlepszy program nie zastąpi trzeźwego myślenia w pracy.
Wszyscy chyba zgodzimy się co do tego, że nie jest to najlepsze rozwiązanie. Po pierwsze – bardzo czasochłonne, po drugie nieciekawe, a po trzecie obarczone wysokim ryzykiem pomyłki. Pozwól, że będę szczery – to rozwiązanie jest po prostu bez sensu.
Zdecydowanie lepszym pomysłem jest użycie filtrowania, aby wyciągnąć odpowiednie dane, które następnie można przygotować w osobnej tabeli.
xxxx
Jeżeli korzystasz z Excela 2016, masz dodatkowe ułatwienie, ponieważ filtr na kolumnie z datą automatycznie będzie grupował dane w następującym porządku: rok, miesiąc, dzień. W przypadku starszych wersji Excela, będziesz potrzebował dorzucić do swojej bazy kolumnę pomocniczą z funkcją zwracającą miesiąc na podstawie podanej daty. Mogłeś zapoznać się z tą funkcją w arkuszach z poprzedniego tygodnia.
W tym momencie kończy się ułatwienie, ponieważ wyfiltrowane dane i tak będziesz musiał obrobić w osobnej tabeli.
Tabele przestawne to fantastyczne narzędzie, które pozwala nam zamienić wszystkie powyższe mozolne czynności na kilka kliknięć.
Myślę, że otrzymaliśmy wystarczający obraz funkcjonowania bez tabel przestawnych. Teraz pokażę, jak taką stworzyć. A sami ocenicie, czy jest to przydatne narzędzie w pracy z Excelem.
Tradycyjnie na końcu artykułu znajdziecie pliki z przykładowymi raportami w formie tabel przestawnych.
WSTAWIANIE TABELI PRZESTAWNEJ
1. Zaznaczy dowolną komórkę w bazie danych
2. Z karty WSTAWIANIE wybierz opcję tabela przestawna
Możesz również zamienić kolejnością krok 1 z krokiem 2. Jedyna różnica to konieczność ręcznego zaznaczenia zakresu danych wejściowych
3. W okienku tworzenia tabeli upewnij się czy zakres danych się zgadza. Jeżeli Excel poprawnie wybrał zakres, będzie w nim widniała nazwa tabeli, jaką nadałeś. Jeżeli nie pamiętasz, jak nadać nazwę tabeli przejdź do poprzedniego artykułu. Jeżeli zakres się nie zgadza, wystarczy zaznaczyć poprawny obszar (całą tabelę z danymi).
4. Wybierz lokalizację tabeli. W naszym pliku będzie to zakładka 
Po potwierdzeniu przyciskiem OK, zostaniesz automatycznie przekierowany do arkusza z nowo powstałą tabelą przestawną.
Zanim jednak stanie się ona użyteczna, należy wybrać, jakie pola tabeli wyjściowej chcesz przedstawić na tabeli przestawnej.
W panelu po prawej stronie znajduje się lista pól tabeli. Można je sytuować w 4 kategoriach:
Filtry – dane, po których tabela będzie filtrowała wartości np.: miesiąc, rok.
Kolumny – kolumny tabeli – w naszym przypadku będzie to stanowisko.
Wiersze – wiersze tabeli – w naszym przypadku będzie to miesiąc.
Wartości – pola, w których pojawią się wartości danych – w naszym przypadku będzie to ilość braków.
Pola umieszcza się w tabeli przestawnej zaznaczając „checkbox” przy odpowiednim polu lub przeciągając je w jeden z czterech obszarów. Zaznaczając checkboxy, nie mamy kontroli nad tym w jakim polu zostanie usytuowana dana grupa danych.
TWORZENIE TABELI PRZESTAWNEJ
Aby nadać tabeli właściwą formę musisz wybrać odpowiednie grupy danych i wstawić je w odpowiednie pola tabeli. Przypomnę, że twoim zadaniem jest pokazać sumaryczną ilość braków na gięciu i spawaniu w styczniu.
Przeciągnij i upuść dane wyjściowe w porządku pokazanym poniżej
Po zaznaczeniu dowolnej komórki w obszarze tabeli przestawnej, dostępne będą dwie dodatkowe karty narzędzi. 
Na karcie Projektowanie możemy formatować obszar tabeli przy użyciu predefiniowanych stylów. Na karcie Analiza możemy dodawać pola do istniejącej tabeli, zmienić jej nazwę, zakres danych wejściowych lub odświeżyć dane.
podniesienie swojej osobistej produktywności
delegowanie zadań dla pozostałych członków zespołu
rozpoznawanie 5 kluczowych dysfunkcji w pracy zespołowej
Kolejnym krokiem w uzyskaniu odpowiedniego raportu jest selekcja konkretnych pozycji z grup danych oraz formatowanie tabeli.
Korzystanie z filtrów danych – filtry tabeli pojawiają się nad właściwą częścią z danymi 
Wybierz z listy dla pola miesiąc, pozycję „1” – odpowiadającą styczniowi. W polu rok nie musisz nic zmieniać, ponieważ wszystkie dane w tabeli wyjściowej są z roku 2017.
Dostęp do listy pól wierszy możesz uzyskać również z menu pola tabeli przestawnej
Aby uczynić raport jeszcze bardziej przejrzystym, dobrze nadać jasne nazwy kolumnom z danymi. Można to zrobić na kilka sposobów:
1. Edytuj bezpośrednio komórki z nagłówkami.
W przypadku pól z nagłówkami wierszy i kolumn – Etykiety wierszy, Etykiety kolumn
2. Zmień nazwę z poziomu karty Analiza lub opcji Ustawienia pól.
Podobnie jak w przypadku zwykłych tabel sformatowanych, tabelom przestawnym również możesz nadawać indywidualne nazwy. Zmiana nazwy dostępna jest na początku karty Analiza.
Możesz nazwać swoje pola jak tylko chcesz. Ja nazwałem je tak:
Powyższa tabela jest dokładnie tym, co chcieliśmy uzyskać. Jeżeli wydaje Ci się, że nie zaoszczędziłeś czasu wybierając tę metodę, spróbuj trochę poćwiczyć. Gwarantuję Ci, że szybko przekonasz się o wyższości tabel przestawnych nad ręcznymi zestawieniami danych.
Zadanie zostało wykonane, ale co zrobić, gdy przyjdzie kolejny miesiąc? Co zrobić, gdy na podsumowaniu przełożony zapyta o inne dane niż te, które przedstawiliśmy?
Wystarczy, że z listy z pozycji w filtrze wybierzesz pożądany miesiąc lub odznaczysz/zaznaczysz pozycje w wierszach/kolumnach. W klika sekund możesz przełączać się dowolnie pomiędzy różnymi wariantami raportu.
Od Excela 2013 masz możliwość jeszcze wygodniejszego filtrowania kryteriów w postaci tzw. Fragmentatora.
Zaznacz dowolną komórkę w tabeli przestawnej i z karty Analiza wybierz pozycję Wstaw fragmentator. Następnie zaznacz pola z danymi, po których chcesz filtrować tabelę.
W efekcie otrzymasz pola filtru. Kliknięcie pozycji na którymkolwiek z pól, automatycznie zawęzi dane do tej kategorii. Możesz zaznaczać wiele pozycji przytrzymując klawisz shift lub ctrl. Różnice pozastawiam Ci do zbadania.
Wiesz już, jak korzystać z tabel przestawnych, ale co w momencie, gdy zajdzie potrzeba dołożenia danych, których nie przewidziałeś na początku? Załóżmy, że chciałbyś dodać do tabeli filtrowanie po numerze tygodnia. Aby to zrobić musisz dodać kolumnę, z funkcją zwracającą numer tygodnia, w tabeli wyjściowej.
Nie ma znaczenia, w którym miejscu wstawisz kolumnę, o ile znajdzie się ona w zakresie tabeli. Aby mieć pewność, że nowo wstawiona kolumna znajduje się w zakresie wyjściowym, wstaw ją w bezpośrednim sąsiedztwie innej kolumny z danymi. Zachęcam Cię jednak, aby korzystać z dobrych praktyk tworzenia baz danych i umieścić kolumnę z numerem tygodnia obok kolumn z funkcjami zwracającymi miesiąc i rok.
Kliknij prawym przyciskiem myszy na kolumnę S i użyj polecenia wstaw.
Nowo powstałą kolumnę nazwij Nr tygodnia.
W pierwszej komórce kolumn wprowadź następującą funkcję: =NUM.TYG([@Data];2)
Pierwszym argumentem tej funkcji jest data na podstawie, której zwrócony zostanie nr tygodnia. Kolejny argument to typ zwracanych danych. Liczba dwa oznacza, że jako początek tygodnia przyjęty zostanie poniedziałek.
Po wprowadzeniu funkcji powinna się ona automatycznie powielić w pozostałych kolumnach, ponieważ znajdujemy się w zakresie sformatowanym jako tabela.
ODŚWIEŻANIE TABELI DANYCH
Za każdym razem, gdy zmieniamy dane w wyjściowej tabeli musimy ją odświeżyć. Jest to nic innego jak aktualizacja zmian. Tabelę odświeża się wybierając polecenie Odśwież z karty Analiza.
Uwaga – Często zdarza się tak, że jeżeli zmienisz nazwę pliku, źródło danych tabeli przestawnej nie zaktualizuje się. W tym wypadku musisz użyć opcji Zmień źródło danych.
Excel po stworzeniu tabeli przestawnej, automatycznie dopisuje do adresu źródła nazwę pliku i nazwę arkusza. Nie zawsze jednak aktualizuje je po zmianie nazwy pliku. Trzeba to zrobić ręcznie.
W polu Tabela/zakres możesz wprowadzić zarówno nazwę jaką nadałeś tabeli wyjściowej lub zakres w komórkach.
Wracając do naszego zadania – po odświeżeniu tabeli w polu wyboru danych tabeli przestawnej pojawi się opcja „Numer tygodnia”.
Przeciągając je do kategorii Filtry będziesz mógł pokazywać dane dla konkretnego tygodnia w roku.
POLA OBLICZENIOWE
Kolejnym ciekawym elementem tabel przestawnych są pola obliczeniowe. Pozwalają one uniknąć niepotrzebnych ingerencji w strukturę tabeli wyjściowej i tym samym odciążają arkusz wizualnie i wydajnościowo.
Do czego mogą nam się przydać pola obliczeniowe?
Dla naszego przypadku możesz policzyć tzw. PPM – Parts Per Milion. Jest to wskaźnik poziomu jakości obrazujący skalę poziomu braków w firmie. Sposób obliczania PPM wyjaśnię na prostym przykładzie:
Wracając do naszego zadania – po odświeżeniu tabeli w polu wyboru danych tabeli przestawnej pojawi się opcja Numer tygodnia.
Przeciągając je do kategorii Filtry będziesz mógł pokazywać dane dla konkretnego tygodnia w roku.
W kolejnym oknie w polu Nazwa: wprowadź nazwę dla tworzonego pola. Następnie wyczyść pole Formuła. Przewiń listę wyboru pola i dwukrotnie kliknij na pozycję Ilość braki. Zostanie ona przeniesiona do pola z formułą. Teraz musisz wstawić znak dzielenia „/” i dwukrotnie kliknąć pozycję Zlecenie ilość. Pozostało Ci już tylko przemnożyć, używając znaku „*”, podane pozycje przez 1 000 000.
Potwierdź operację klikając Dodaj.
Jeżeli chciałbyś dodać elementy obliczeniowe do istniejącego pola, wybierz je z listy rozwijanej w polu Nazwa:, następnie wprowadź zmiany w formule i potwierdź wybór przyciskiem Modyfikuj.
Jeżeli wszystko poszło zgodnie z planem, nowe pole powinno samo dodać się w kategorii wartości.
FORMATOWANIE DANYCH
Zgodzisz się ze mną, że nie potrzebujemy sześciu miejsc po przecinku? Jeżeli tak to możesz zrobić dwie rzeczy: zaznaczyć dane w kolumnie Suma z PPM i klasycznie jak w przypadku zwykłej tabeli usunąć zbędne miejsca wybierając polecenie Zmniejsz dziesiętne z karty Narzędzia główne.
Wybierając przycisk Format liczby z lewego dolnego rogu dostajemy znane już okno formatowania.
Aby pozbyć się niepotrzebnych miejsc po przecinku, wybierz z listy pozycję Liczbowe i w polu Miejsca dziesiętne wprowadź liczbę 2.
Dużo ciekawszym miejscem do formatowania danych jest polecenie Ustawienia pola. Aby się do niego dobrać użyj menu rozwijanego przy pozycji Suma PPM w kategorii wartości. W oknie ustawień możesz zmienić nazwę pola, a także wybrać czy dane dla wybranej grupy wartości mają być liczone jako suma, średnia czy może jako liczba wystąpień.
Wybierając przycisk Format liczby z lewego dolnego rogu dostajemy znane już wszystkim okno formatowania. Aby pozbyć się niepotrzebnych miejsc po przecinku, wybierz z listy pozycję Liczbowe i w polu Miejsca dziesiętne wprowadź liczbę 2.
To koniec dzisiejszego zadania, ale temat tabel przestawnych pozostaje otwarty, ponieważ za tydzień pokażę , jak skutecznie wizualizować dane w oparciu o wykresy przestawne. Znajdzie się tam również kilka trików, które będą uzupełnieniem tego wpisu.
Poniżej przygotowane przez nas pliki – a w nich przykłady raportów w formie tabel przestawnych – tym razem bez dodatkowych instrukcji, żeby zachęcić was do samodzielnej analizy.
Pamiętaj jednak, że jeśli nie podoba Ci się prowadzenie firmy w Excelu, możesz zdecydować się na specjalny system, czyli rozwiązanie dedykowane firmom produkcyjnym. Przykładem takiego rozwiązania jest aplikacja ZaPro, która pozwala na zarządzanie produkcją od a do z.
WYKORZYSTAJ GOTOWE SZABLONY
wizualizacja danych jakościowych
- Procentowa ilość braków
- Analiza Pareto – Lorenza
- Diagram Iskihawa (rybich ości) – szablon
- Raport A3
Aby otrzymać plik, potwierdź swój adres mailowy. Sprawdź folder oferty oraz spam.
Absolwent Zarządzania i Inżynierii Produkcji Krakowskiej AGH.
Projektant i wykonawca narzędzi Excel z ponad 3 letnim doświadczeniem.
Wdrożył ponad 100 projektów opartych na arkuszach kalkulacyjnych w przedsiębiorstwach produkcyjnych i usługowych. Autor kursów online „Excel na Produkcji”, „Excel w Usługach” oraz Aplikacji „Analiza Nagrań”. Przeprowadził liczne szkolenia z zakresu metodologii Lean oraz pracy w Excelu w branży na produkcji.
