Budżety są realizowane na najwyższym poziomie - przychody według linii produktów według regionu według miesiąca. Dane rzeczywiste gromadzą się powoli w czasie - faktura po fakturze, pozycja po pozycji. Porównanie małego pliku budżetu z obszernymi danymi Rzeczywistymi było bólem od zawsze. Uwielbiam tę sztuczkę Roba Collie, aka PowerPivotPro.com.
Aby skonfigurować przykład, masz 54-wierszową tabelę budżetu: 1 wiersz na miesiąc na region na produkt.
Plik faktury jest na poziomie szczegółowości: do tej pory 422 wiersze w tym roku.
Nie ma na świecie funkcji WYSZUKAJ.PIONOWO, która kiedykolwiek pozwoliłaby Ci dopasować te dwa zestawy danych. Ale dzięki Power Pivot (inaczej modelowi danych w programie Excel 2013+) staje się to łatwe.
Musisz utworzyć małe tabele, które nazywam „łącznikami”, aby połączyć dwa większe zestawy danych.
W moim przypadku produkt, region i data są wspólne dla obu tabel. Stół Produkt to mały stół z czterema komórkami. Tak samo dla regionu. Utwórz każdy z nich, kopiując dane z jednej tabeli i używając opcji Usuń duplikaty.
Tabela kalendarza po prawej stronie była w rzeczywistości trudniejsza do utworzenia. Dane budżetowe mają jeden wiersz na miesiąc, zawsze przypadający na koniec miesiąca. Dane faktury pokazują daty dzienne, zwykle dni powszednie. Musiałem więc skopiować pole Data z obu zestawów danych do jednej kolumny, a następnie usunąć duplikaty, aby upewnić się, że wszystkie daty są reprezentowane. Następnie =TEXT(J4,"YYYY-MM")utworzyłem kolumnę Miesiąc na podstawie dat dziennych.
Jeśli nie masz pełnego dodatku Power Pivot, musisz utworzyć tabelę przestawną z tabeli Budżet i zaznaczyć pole wyboru Dodaj te dane do modelu danych.
Jak omówiono w poprzedniej wskazówce, podczas dodawania pól do tabeli przestawnej będziesz musiał zdefiniować sześć relacji. Chociaż można to zrobić za pomocą sześciu wizyt w oknie dialogowym Utwórz relację, uruchomiłem dodatek Power Pivot i użyłem widoku diagramu do zdefiniowania sześciu relacji.
Oto klucz do wykonania całej tej pracy: Możesz swobodnie korzystać z pól numerycznych z Budżetu i Rzeczywistego. Ale jeśli chcesz wyświetlić region, produkt lub miesiąc w tabeli przestawnej, muszą one pochodzić z tabel łączących!
Oto tabela przestawna z danymi pochodzącymi z pięciu tabel. Kolumna A pochodzi od stolarza Region. Wiersz 2 pochodzi od łącznika Kalendarza. Fragmentator produktu pochodzi z łącznika produktów. Numery budżetu pochodzą z tabeli Budżet, a liczby rzeczywiste z tabeli faktur.
Działa to, ponieważ tabele łączące stosują filtry do tabeli Budżet i Rzeczywisty. To piękna technika, która pokazuje, że dodatek Power Pivot nie jest przeznaczony tylko do dużych zbiorów danych.
