Tworzenie raportów sprzedażowych za pomocą tabel przestawnych

Tabele przestawne
Automatyzacja pracy
Średniozaawansowany
0:00czas trwania

Tabela przestawna to niezbędne narzędzie dla każdej osoby pracującej z arkuszem kalkulacyjnym. Pozwala na tworzenie niezwykle funkcjonalnych raportów, podsumowań oraz obliczeń na dużych zbiorach danych. Jest dużym ułatwieniem w analizie danych umożliwiającym ich konsolidację w postaci przejrzystej tabeli. Chcesz wiedzieć jak - tworzyć raporty sprzedaży produktów, czy obliczać np. średnią wartość sprzedaży? Zapraszam do lektury!

Opublikowane: 20 maja 2019
Lekcję prowadzi: Kamil Skonecki
Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz szablon do dalszej pracy
Dostępne po opłaceniu

Zobacz krok po kroku jak wykonać lekcję

Z lekcji dowiesz się:

  • Jak konsolidować dane w postaci tabeli?
  • Jak tworzyć raporty sprzedaży produktów?
  • Jak modyfikować wartości w zależności od potrzeb?
  • Jak obliczać np. średnią wartość sprzedaży? Co jeszcze warto dodać do takiej tabeli?
Wykonaj zadanie samodzielnie
Dostępne po opłaceniu
Pobierz szablon do dalszej pracy
Dostępne po opłaceniu

Z lekcji dowiesz się:

  • Jak konsolidować dane w postaci tabeli?
  • Jak tworzyć raporty sprzedaży produktów?
  • Jak modyfikować wartości w zależności od potrzeb?
  • Jak obliczać np. średnią wartość sprzedaży? Co jeszcze warto dodać do takiej tabeli?
  • Jak konsolidować dane w postaci tabeli?
  • Jak tworzyć raporty sprzedaży produktów?
  • Jak modyfikować wartości w zależności od potrzeb?
  • Jak obliczać np. średnią wartość sprzedaży? Co jeszcze warto dodać do takiej tabeli?
  • Samo tworzenie tabel przestawnych jest bardzo proste. Z karty wstawianie wybieramy opcję tabela przestawna, a następnie Excel automatycznie zaznacza obszar tabeli, który mamy w arkuszu. Naszą tabelę możemy umieścić we wskazanej przez nas lokalizacji w pliku albo automatycznie utworzyć nowy arkusz z tabelą przestawną.

    Ważna uwaga: pamiętaj aby w Twoim zbiorze danych każda kolumna miała tytuł, w przeciwnym wypadku tabela przestawna nie będzie mogła się utworzyć.

    Dodatkowo, możemy wybrać dane, które nie znajdują się w arkuszu Excel tylko np. w bazie danych Acces lub Sql - w tym celu wystarczy, że wybierzemy opcje „użyj zewnętrznego źródła danych” i interesujące nas połączenie.

    Kiedy już utworzyliśmy naszą tabelę w nowym arkuszu, możemy już swobodnie wykonywać operacje na naszej tabeli przestawnej.

    Załóżmy że na naszych danych chcielibyśmy utworzyć raport dotyczący sprzedaży produktów przez naszych pracowników. Przeciągamy kolumnę sprzedawca do wierszy, a wartość i ilość do wartości i gotowe. Dodatkowo chcielibyśmy zweryfikować, jak taka sprzedaż prezentowała się w maju 2019 roku. Należy teraz przenieść kolumny rok i miesiąc do pola filtru, a następnie wybrać odpowiednie wartości i gotowe.

       Zobacz jak szybko i łatwo udało się przygotować takie zestawienie, nie używając nawet żadnych skomplikowanych formuł. Zatrzymajmy się na chwilę w tym miejscu. Chciałbym pokazać jak można modyfikować wartość w tabeli przestawnej. Na naszej liście pól klikamy prawym przyciskiem na np. ilość - widzimy ze można wybrać jak chcemy przedstawić daną zmienną np. suma, liczba itp. W ten sposób możemy modyfikować sposób przedstawiania naszej wartości w zależności od wymaganych przez nas potrzeb. Tabela może wskazać nam np. wartość maksymalną lub minimalną, czy po prostu zliczyć rekordy i przedstawić wartość jako liczba.

    Kiedy jesteśmy już przy temacie liczb i ich formatowania, dodamy teraz pole obliczeniowe do naszej tabeli. Z zagadki analizy wybieramy pole obliczeniowe. Tutaj wykonujemy dwie czynności, nazywamy nasze pole obliczeniowe, a także wprowadzamy działanie, które ma ono wykonywać. W naszym przypadku chcemy obliczyć średnią wartość sprzedaży dla każdego z naszych sprzedawców. Otóż, wartość dzielimy przez ilość i otrzymujemy spodziewany przez nas rezultat. Obliczeń jakie możemy tu wykonać jest nieskończenie wiele i tylko od Ciebie i Twoich potrzeb zależy co konkretnie należy tutaj policzyć.

    Jeżeli chcemy aby dane które pobraliśmy z tabeli przestawnej, nie były w niej zagnieżdżone, należy wyłączyć funkcje „weź dane z tabeli”, dzięki temu będziemy mogli kopiować i przeciągać wartości z tabel przestawnych w inne komórki.

    Ważnym elementem w pracy z tabelami przestawnymi jest umiejętność grupowania.
    W naszym przykładzie pogrupujemy tabele przestawną po wartościach naszej sprzedaży. W tym celu najeżdżamy myszką na tabelę przestawną, klikamy prawy przycisk myszy i z listy która nam się pokazała wybieramy grupuj. Możemy wybrać ustawienie, które poda nam Excel albo ustawić je ręcznie samemu. Wybieramy wartość początkową, końcową i rozpiętość naszych przedziałów.

    Musimy pamiętać, że nie ma możliwości grupowania po nieregularnych przedziałach. W takim przypadku, musimy robić to ręcznie i grupować za każdym razem interesujący nas przedział.

    Dużym ułatwieniem w pracy z tabelami przestawnymi jest umiejętność ustawienia dynamicznego zakresu danych, dzięki któremu możemy swobodnie dopisywać kolejne rekordy do naszych danych, a tabela przestawna automatycznie - przy jej odświeżaniu - je pobierze.

    Formuła, która pozwala nam na taki zabieg ma następującą postać:

    Dużą zaletą tej funkcji jest uniwersalność, dla każdego zbioru danych jest ona taka sama. Jedyne o czym musimy pamiętać, to zmieniająca się liczba kolumn oraz inna nazwa arkusza w którym znajdują się nasze dane. Jako, że nasza tabela posiada 6 kolumn, to w funkcji na ostatniej pozycji widzimy 6. Analogicznie jeśli np. mieliśmy zbiór składający się z 15 kolumn, a nasz arkusz nazywałby się Arkusz2 to funkcja wyglądałaby następująco:

    PRZESUNIĘCIE(Arksuz2$A$1;0;0;ILE.NIEPUSTYCH(Arkusz2$A:$A);15)

    Na tym przykładzie jeszcze dokładniej można zrozumieć, jak należy zmieniać wartości funkcji w odniesieniu do zbioru danych który posiadamy.

    Następnie z zakładki formuły wybieramy menedżer nazw, wybieramy nowy, dowolnie ją nazywamy i klikamy ok. Następnie kopiujemy wcześniej przygotowaną przez nas funkcję i wklejamy ją w miejsce odwołania naszego menedżera nazw. Zatwierdzamy i przechodzimy do naszej tabeli przestawnej, gdzie zmieniamy obszar danych. Z zakładki analiza wybieramy zmień źródło danych i wpisujemy nazwę stworzonej przez nas formuły, w tym przypadku „Tabela”. Zatwierdzamy i gotowe. W ten sposób stworzyliśmy dynamiczny zakres danych.

     

    Wykonaj zadanie samodzielnie
    Dostępne po opłaceniu
    Pobierz gotowy arkusz z lekcji
    Dostępne po opłaceniu
    Kolejne kroki dostępne dla użytkowników którzy wykupili pełny dostęp

    Jeszcze się wahasz?

    Zobacz, co zyskasz, mając pełny dostęp:

    • Dostęp do wszystkich lekcji video
    • Materiały do ćwiczeń
    • Dodatkowe artykuły z trikami
    • Formularze gotowe od razu do użycia
    • E-booki pogłębiające Twoją wiedzę
    • Certyfikat potwierdzający Twoje umiejętności
    Sprawdź co oferuje serwis Sprytny Excel »

    Masz już konto w serwisie?

    Zaloguj się

    Adres e-mail lub login:

    Hasło

    Nie pamiętam hasła
    - Twój pierwszy krok do lepszych wyników
    Oglądaj pełne lekcje i wiedz więcej!
    Masz już konto w serwisie?
    Zaloguj się »

    wiper-pixel