Kontynuujemy temat budżetowania. W poprzednim artykule zajmowaliśmy się łączeniem wielu plików Excela w jedne plik. Pliki te pochodziły od handlowców i zawierały prognozowaną roczną sprzedaż produktów do klientów. 

Teraz przyszedł czas na rozłożenie całościowej sprzedaży po miesiącach. 

Znowu będzie to zadanie dla Power Query. 

Załóżmy, że mamy tabelkę z miesięcznym rozkładem sprzedaży. Tabelka ta znajduje się w obiekcie tabela o nazwie tbSalesRatio i wygląda następująco: 

Miesięczny udział sprzedaży

I całe zadanie polega na tym, aby dla każdego wpisu w tabeli ze zbiorczymi danymi od handlowców, o tej: 

Gotowe zapytanie

stworzyć 12 rekordów miesięcznych. Każdy taki rekord (dla poszczególnych miesięcy) będzie zawierał wartość roczną pomnożoną przez procent (Ratio) dla odpowiedniego miesiąca. 

Zaczynamy! 

1. Ładowanie tabeli do Power Query 

Zacznijmy od załadowania tabeli ze współczynnikami (tbSalesRatio) do Power Query. 

W tym celu po prostu zaznacz dowolną komórkę w obrębie tej tabeli i wybierz Dane → Z tabeli/zakresu. 

Powstanie zapytanie o nazwie tbSalesRatio, które załaduj do Excela jako połączenie. 

2. Wyliczanie danych miesięcznych 

Teraz wróćmy do zapytania REP w Power Query (dokładny opis, jak stworzyć to zapytanie, opisywałam w artykule Budżetowanie cz.1.: łączenie plików budżetowych). 

Wstawimy do tego zapytania kolumnę niestandardową, a zatem: Dodaj kolumnę → Kolumna niestandardowa. 

Jej nazwa nie ma dla nas znaczenia, natomiast w każdej komórce tej kolumny będziemy chcieli umieścić całą tabelę tbSalesRatio. A zatem w formule kolumny niestandardowej należy wpisać właśnie to: 

Kolumna niestandardowa

A teraz po prostu należy rozwinąć/ekspandować tę kolumnę, a zatem skorzystać ze strzałek lewo-prawo w jej nagłówku, wybrać wszystkie kolumny, pamiętając tylko o odznaczeniu opcji Użyj oryginalnej nazwy kolumny jako prefiksu: 

Rozwijanie kolumny

Efekt jest dokładnie taki, jakiego oczekiwaliśmy: 

Wynik rozwinięcia

Teraz to już prosta matematyka: wystarczy wymnożyć wartości z kolumny Wartość i Ratio, aby uzyskać wartość miesięczną. 

A zatem proste mnożenie: 

1. zaznacz kolumny Wartość i Ratio 
2. menu Dodaj kolumnę → Standardowy → Mnożenie 

Powstała kolumna o nazwie Mnożenie (sugeruję zmienić jej nazwę w pasku formuły na np. WartośćM). 

Dodatkowo wartości w tej kolumnie chciałabym zaokrąglić w górę (posiadają miejsca dziesiętne, a ja chciałabym tutaj widzieć liczby całkowite). 

W tym celu dokonam prostej modyfikacji w pasku formuły: wrzucę wyrażenie mnożenia w funkcję języka M Number.RoundUp: 

Number.RoundUp( [Wartość] * [Ratio] , 0) 

Zaokrąglanie i zmiana nazwy kolumny

Potem wystarczy już tylko usunąć niepotrzebne kolumny (u mnie będą to Wartość i Ratio) i z powrotem załadować wszystko to Excela do tabeli przestawnej. 

Oto wynik przykładowego raportu, uwzględniającego miesiące: 

Tabela przestawna

I voila! Gotowe! 

Film na Youtubie, gdzie pokazuję powyższe rozwiązanie: