Minuty czytania: 5

Jak policzyć udział procentowy w tabeli przestawnej…tylko dla sumy końcowej

Na jednym z naszych szkoleń padło pytanie o udział procentowy w tabeli przestawnej. Uczestniczce szkolenia chodziło o dość nietypowe rozwiązanie. Chciała mianowicie, aby taki udział procentowy pojawił się na końcu każdego wiersza, ale TYLKO dla sumy końcowej.

Efekt końcowy miał wyglądać tak jak na screenie poniżej. Jak widzisz w ostatniej kolumnie mamy udział procentowy poszczególnych handlowców w całkowitej wartości sprzedaży. Jednak jednocześnie taki udział nie występuje po każdym kwartale. Jak osiągnęliśmy taki efekt? Dowiesz się z dalszej lektury artykułu lub po obejrzeniu filmu.

Udział procentowy w tabeli przestawnej - tylko dla sumy końcowej

Tworzymy tabelę przestawną

Oczywiście zachęcam Cię do jednoczesnych ćwiczeń, wraz z lekturą artykułu. Pobierz plik i działaj ze mną.

Wykonujemy standardowe kroki, aby stworzyć tabelę przestawną. Następnie ułożymy sobie dane, tak aby przeanalizować dane o sprzedaży po latach i kwartałach. Kolejne kroki mogą wyglądać więc następująco:

  • Sprzedaż, czyli wartość netto umieścimy w polu wartości.
  • Datę sprzedaży umieścimy w polu wierszy. Po przerzucenie daty sprzedaży do wierszy – w zależności od wersji Excela jaką posiadasz – możesz uzyskać automatyczne grupowanie. Jeżeli Excel nie pogrupował dat, bądź pogrupował je nie w taki sposób, jak oczekiwałeś – kliknij prawym przyciskiem myszy w polu wierszy i wybierz opcję Grupuj. W wierszach zostaw tylko lata.
  • Do pola kolumn przerzuć kwartały lub miesiące. Pozostałe elementy usuwamy. W ten sposób uzyskaliśmy bazę do analizy i dalszych prac.
Tabela przestawna - analiza wg lat i kwartałów

Udział procentowy w tabeli przestawnej – sposób standardowy

Teraz wstawimy standardowy udział procentowy w tabeli przestawnej.

  • Ponownie wstaw wartość netto do pola wartości.
  • Kliknij prawym przyciskiem myszy na tej zduplikowanej sumie z wartości netto i wybierz polecenie Pokaż wartości jako % sumy końcowej.
Udział procentowy w tabeli przestawnej - pokazywanie wartości jako % sumy końcowej

Efekt jaki uzyskaliśmy w wielu przypadkach będzie zgodny z oczekiwaniami. To znaczy, mamy udział procentowy dla każdego kwartału ale ponieważ w wierszach są również lata, to ten udział nie dotyczy danego roku ale sumy wszystkich lat. Excel działa zgodnie z poleceniem, czyli pokazuje % z sumy końcowej.

Udział procentowy w tabeli przestawnej - kwartały i lata

No dobrze, ale co gdybyśmy chcieli uzyskać udział procentowy tylko dla ostatniej kolumny, czyli dla danego roku? Nie możemy usunąć tylko wybranych kolumn. Standardowe podejście nie wystarczy.

Power Pivot nie taki straszny

Aby osiągnąć nasz cel wykorzystamy POWER PIVOT, dzięki któremu będziemy mogli tworzyć tabele przedstawione oparte o model danych. Spokojnie… nie będziemy robili niczego szczególnie skomplikowanego 🙂

Najpierw włączmy ten dodatek. Idziemy na wstążkę i wybieramy PLIK > OPCJE > DODATKI > DODATKI COM. Klikamy przycisk przejdź, a następnie zaznaczamy Microsoft Power Pivot. Na wstążce zobaczysz nową kartę.

OK, jesteśmy gotowi. Ponownie utwórz tabelę przestawną ale z jedną ważną różnicą. W menu tworzenia tabeli zaznacz opcję Dodaj te dane do modelu danych.

Dodawanie danych do modelu danych w tabeli przestawnej

Ponownie układamy dane w taki sposób, aby: w wierszach znalazły się lata, w kolumnach kwartały, a w polu wartości sprzedaż. Po wykonaniu tych czynności przejdź na kartę POWER PIVOT i wybierz polecenie ZARZĄDZAJ.

Power Pivot - zarządzaj modelem danych

Udział procentowy w tabeli przestawnej – sposób z modelem danych

Po kliknięciu ikony otworzy się dodatek Power Pivot. Tutaj będziesz mógł utworzyć miarę, która będzie sumowała kolumnę wartość netto. Najprostszym sposobem dla początkującego użytkownika Power Pivot, będzie ustawienie się pod kolumną wartość netto i wybranie z menu górnego NARZĘDZIA GŁÓWNE, polecenia AUTOSUMOWANIE.

W ten sposób utworzysz miarę, która będzie sumowała tę kolumnę. Excel standardowo utworzył nazwę dla miary w stylu „Sum of Wartość netto”. Dla nas lepszą nazwą będzie „Udział %”. Możesz łatwo zmienić nazwę w tym miejscu:

Zmiana nazwy miary w modelu danych

W ten sposób zrobiliśmy wszystko co powinniśmy w Power Pivocie. Przechodzimy z powrotem do Excela. W tabeli przestawnej automatycznie pojawiła się nowa miara, którą przenosimy do pola wartości.

Udział procentowy w tabeli przestawnej - miara

Początkowo efekt mamy podobny jak w standardowym rozwiązaniu. Natomiast dzięki temu, że skorzystaliśmy z modelu danych to jesteśmy w stanie w nietypowy sposób filtrować nasze kolumny.

To znaczy, że możemy usunąć kolumny pośrednie (dotyczące kwartałów), pozostawiając tylko kolumnę końcową (która dotyczy sumy sprzedaży w roku).

Robimy to w następujący sposób.

  • Klikamy na tabelę przestawną w dowolnym miejscu.
  • Z karty ANALIZA TABELI PRZESTAWNEJ wybieramy POLA, ELEMENTY I ZESTAWY. Dzięki temu, że skorzystaliśmy z modelu danych mamy włączoną tę opcję. Utworzymy nietypowy zestaw, wybierając opcję UTWÓRZ ZESTAW NA PODSTAWIE ELEMENTÓW KOLUMNY…
  • W nowym menu zobaczysz wszystkie kolumny z tabeli przestawnej. Teraz wybierz te, które mają się pokazywać, a pozostałe usuń. Ponieważ nie chcemy udziałów % po każdym kwartale, to usuniemy właśnie te elementy.
Udział procentowy w tabeli przestawnej - nowy zestaw danych

W ten sposób pozbyliśmy się nieistotnych kolumn, natomiast ostatnią pokażemy jako udział procentowy sumy końcowej. Ponownie klikamy prawy przycisk myszy i wybieramy polecenie Pokaż wartości jako % sumy końcowej. Efekt końcowy wygląda tak:

Udział procentowy w tabeli przestawnej tylko dla sumy końcowej

Na marginesie…tabele przestawne i model danych są powszechnie wykorzystywane w interaktywnych dashboardach. Jeżeli chcesz takie tworzyć, a nie masz doświadczenia zacznij od naszego bezpłatnego ebooka i gotowego szablonu.

Podsumowanie

Tabele przestawne to potężne narzędzie które daje wiele możliwości. Pokazywanie udziałów procentowych w dynamiczny i elastyczny sposób jest jednym z doskonałych tego przykładów.

Jednak zazwyczaj użytkownicy Excela, tworzą dodatkowe kolumny pomocnicze, które poza tabela przestawną pokazują procenty z danych zawartych tabeli. To ma swoje istotne ograniczenia. Opisane powyżej metody rozwiązują problem.

Mam nadzieję, że film i artykuł okazał się pomocny. Jeżeli tak – podziel się proszę nim w swoich mediach społecznościowych. A może znasz inną, alternatywną metodę na pokazanie udziału procentowego w tabeli przestawnej tylko dla sumy końcowej? Koniecznie podziel się z nami w komentarzu.

Łukasz Pietrzak


Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.


2 Comments

Natalia · 18 czerwca 2021 at 08:32

Wydaje mi się, że plik do pobrania podpięty do artykułu dotyczy innego zadania?

    Łukasz Pietrzak · 21 czerwca 2021 at 07:43

    Dziękuję. Poprawione.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *