Model danych programu Excel (Power Pivot) umożliwia połączenie dużego, szczegółowego zestawu danych rzeczywistych z budżetem najwyższego poziomu przy użyciu tabel łączących.
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: jeden 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ą na podstawie tabeli budżetu 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.
Obejrzyj wideo
- Masz mały zbiór danych budżetowych z góry na dół
- Chcesz porównać ze zbiorem danych rzeczywistych od dołu do góry
- Dane rzeczywiste mogą pochodzić z rejestru faktur
- Model danych pozwoli ci porównać te zestawy danych o różnych rozmiarach
- Utwórz oba zestawy danych w tabeli Ctrl + T
- Dla każdego pola tekstowego, według którego chcesz raportować, utwórz tabelę łączącą
- Skopiuj wartości i usuń duplikaty
- W przypadku dat możesz uwzględnić daty z obu tabel i przekonwertować na koniec miesiąca
- Ustaw łączniki jako tabele Ctrl + T.
- Opcjonalne, ale przydatne do nazwania wszystkich pięciu tabel
- Utwórz tabelę przestawną z budżetu i wybierz model danych
- Utwórz tabelę przestawną, korzystając z opcji Budżet i Rzeczywiste na podstawie oryginalnych tabel
- Wszystkie inne pola muszą pochodzić z tabel łączących
- Dodaj fragmentatory według produktu
- Utwórz trzy relacje od Budżetu do Stolarzy
- Utwórz trzy relacje od rzeczywistego do stolarza
- Jutro: jak budowanie relacji jest łatwiejsze dzięki Power Pivot i formułom języka DAX
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2016 - Budżet odgórny a rzeczywiste oddolne!
Hej, podcastuję całą książkę, kliknij „i” w prawym górnym rogu i postępuj zgodnie z listą odtwarzania.
Hej, zamierzam to przerwać, tu Bill Jelen od 15 minut. Zdaję sobie teraz sprawę, że to niesamowicie długi podcast i kusi Cię, aby go po prostu kliknąć, ale pozwól, że opowiem Ci o tym krótko. Jeśli korzystasz z programu Excel 2013 i kiedykolwiek miałeś małą tabelę budżetową i ogromną tabelę wartości rzeczywistych i musisz je zmapować, jest to niesamowita nowa zdolność, którą mamy w programie Excel 2013, której niewiele osób wyjaśniło i prawdopodobnie o tym nie wiesz. Jeśli to ty, jesteś w 2013 roku i musisz zmapować te dwa zestawy danych, poświęć trochę czasu, może dziś, może jutro, może dodaj to do listy obserwowanych, warto, to niesamowita technika.
W porządku, oto, co mamy, po lewej stronie mamy budżet, ten budżet, jest wykonywany na najwyższym poziomie, z góry na dół, po prawej stronie dla każdej linii produktów, dla każdego regionu, dla każdego miesiąca jest budżet . Niewiele rekordów tutaj, liczba 55, po prawej stronie próbujemy to porównać z rzeczywistymi. Dane rzeczywiste pochodzą z rejestru faktur, więc mamy Region, Produkt i Przychód, ale są to indywidualne faktury, dużo więcej danych tutaj, jesteśmy już w połowie roku, a ja mam już 423 rekordy. W porządku, więc jak zmapować te 55 na te 423? Może to być trudne do zrobienia z WYSZUKAJ.PIONOWO, musisz najpierw podsumować, ale na szczęście w programie Excel 2013 model danych sprawia, że jest to naprawdę łatwe. To, czego potrzebujemy, aby ten wielki, masywny stół komunikował się z tym małym stolikiem, to pośrednicy, nazywam ich stolarzami.Małe tabelki, produkt, region i kalendarz, zamierzamy połączyć budżet z tymi trzema tabelami, zamierzamy połączyć rzeczywisty z tymi trzema tabelami i cudem zadziała tabela przestawna. W porządku, więc oto jak to robimy.
Najpierw muszę utworzyć łączniki, więc biorę to pole Produkt z kolumny A i kopiuję je do kolumny F, a następnie Dane, Usuń duplikaty, kliknij OK i zostaje nam mała mała tabela, 1 nagłówek 3 rzędy. To samo dla regionu, weź regiony, Ctrl + C, przejdź do kolumny G, Wklej, Usuń duplikaty, kliknij OK, 3 wiersze 1 nagłówek, w porządku. Teraz dla dat daty nie są takie same, są to daty końcowe miesiąca, w rzeczywistości są przechowywane jako daty końcowe miesiąca, a są to dni tygodnia. Mam zamiar wziąć obie listy, Ctrl + C drugą listę i wkleić ją tutaj, Ctrl + V, potem wezmę krótszą listę, skopiuję ją i wkleję poniżej, w porządku. I naprawdę denerwujące jest to, że mimo że są przechowywane jako daty, pojawiają się jako miesiące, a funkcja Usuń duplikaty nie będzie ich widzieć jako takich samych.Dlatego zanim skorzystam z opcji Usuń duplikaty, muszę zmienić to na krótką datę. Wybierz te dane, Dane, Usuń duplikaty, kliknij OK, a następnie trochę posortuj tutaj, aby to działało.
W porządku, teraz nie chcę raportować według daty dziennej, więc dodam tutaj kolumnę, kolumnę odnośnika z napisem Miesiąc, a to będzie równe EOMONTH tej dacie, 0, co doprowadzi nas do koniec miesiąca. Sformatuje to jako krótką datę i skopiuje to, w porządku. Teraz musimy przekształcić każdy z nich w tabelę Ctrl + T, więc stąd Ctrl + T, moja tabela ma nagłówki, pięknie. Te małe, nie zdaje sobie sprawy, że tam są nagłówki, więc musimy upewnić się, że zaznaczamy to i Ctrl + T, w porządku, i nazywają te tabele Tabela1, Tabela2, Tabela3, naprawdę nudne nazwy, prawda? Więc zamierzam zmienić ich nazwy i nazwać je BudTable, ProdTable, RegTable, my CalTable, a następnie ActTable, w porządku.
Zaczynamy od pierwszej tabeli, a przy okazji nie zamierzamy dzisiaj używać PowerPivot, zrobimy to wszystko z modelem danych. Tak więc, Excel 2013 lub nowszy, masz tę wstawkę, tabelę przestawną, zamierzamy zaznaczyć pole „Dodaj te dane do modelu danych”, kliknij OK i otrzymamy naszą listę pól z magicznym przyciskiem Wszystko, który pozwala wybieram spośród wszystkich pięciu tabel w skoroszycie: Rzeczywiste, Budżet, Kalendarz, Produkt, Region. W porządku, więc liczby będą pochodzić z tabeli Budżet, wstawię tam budżet, a z tabeli Rzeczywiste wstawię tam rzeczywisty, ale tutaj jest reszta tabeli przestawnej. Wszystkie inne pola tekstowe, które zamierzamy umieścić w obszarze wierszy lub kolumn, lub jako fragmentatory, muszą pochodzić z łączników, muszą pochodzić z tabel między tabelami.
W porządku, więc z tabeli kalendarza weźmiemy to pole Miesiąc i umieścimy je na górze, teraz zignorujemy inne relacje. Będę tworzyć relacje, ale chcę je wszystkie naraz. W tabeli Region odłóż regiony na bok. Mógłbym odłożyć produkty na bok, ale tak naprawdę zamierzam użyć tabeli produktów jako krajalnicy, więc Analizuj, Wstaw krajalnicę, ponownie musisz przejść do Wszystkie, jeśli jeszcze nie korzystałeś z tabeli produktów. Więc przejdź do Wszystkie, a zobaczysz, że Produkt jest dostępny do utworzenia jako fragmentator z produktów, w ten sposób. W porządku, w tym momencie nie stworzyliśmy relacji, więc wszystkie te liczby są błędne. A relacje, które musimy stworzyć, musimy stworzyć 3 tabele z tej małej tabeli budżetowej, jedną dla produktów, jedną dla regionów, jedną dla kalendarza,to 3 relacje. Następnie musimy utworzyć relacje z tabeli Rzeczywiste do regionu Produkt w Kalendarzu, czyli w sumie 6 tabel. I tak, byłoby to zdecydowanie łatwiejsze, gdybyśmy mieli PowerPivot, ale nie mamy lub załóżmy, że nie.
Użyję więc staromodnego sposobu, dialogu Utwórz tutaj, gdzie mamy tabelę Budżet po lewej stronie i zamierzamy użyć pola Region i powiązać to z tabelą Region, polem Region . W porządku, utworzono 1/6. Wybieram opcję Utwórz, ponownie z tabeli Budżet przechodzimy do produktu, a następnie łączymy go z tabelą produktów, do produktu, klikamy OK. Z tabeli Budżet pole Data, przechodzimy do tabeli Kalendarz, a pole Przeznaczenie, klikamy OK, jesteśmy w połowie drogi, w porządku. Z tabeli Rzeczywiste przechodzimy do tabeli Region, klikamy OK, z tabeli Rzeczywiste do Produktu iz tabeli Wartości rzeczywiste do Kalendarza. Właściwie to wezmę Wartości i sprawię, że pójdą w dół, w porządku. Zaprojektuj, układ raportu, pokaż w formie tabelarycznej, aby uzyskać preferowany widok, powtórz wszystkie etykiety elementów, w porządku,to jest absolutnie niesamowite! Teraz mamy tę małą tabelkę, około 50 rekordów w tej tabeli zawierającej setki rekordów, a dzięki modelowi danych utworzyliśmy pojedynczą tabelę przestawną. Dla każdego miejsca, w którym możemy zobaczyć budżet, możemy zobaczyć przychód, jest on podzielony według regionu, podzielony według miesiąca i można go podzielić według produktu.
Teraz pomysł ten przyszedł do mnie od Roba Collie, który zarządza Power Pivot Pro, a Rob stworzył wiele książek, jego najnowsza to „Power Pivot i Power BI”. Myślę, że to było w książce „Power Pivot Alchemy”, to było to, które widziałem i powiedziałem „Cóż, mimo że nie mam milionów wierszy do raportowania przez Power Pivot, to jest taki, który zrobiły OGROMNĄ zmianę w moim życiu, mając dwa zestawy danych o niedopasowanych rozmiarach i wymagając raportowania z obu z nich ”. Cóż, ten przykład i wiele innych znajduje się w tej książce, w końcu otrzymam podcast całej książki, który wygląda na to, że zajmie to dwa i pół miesiąca. Ale możesz dostać całą książkę dzisiaj, w tym samym czasie, iść tam, kupić książkę, 10 $ za e-book, 25 $ za drukowaną książkę i możesz mieć wszystkie te wskazówki naraz.
W porządku, naprawdę długi odcinek: mamy mały budżet odgórny i najniższy Rzeczywiste, mają różne rozmiary, ale używając modelu danych w programie Excel 2013… A tak przy okazji, jeśli jesteś w 2010, możesz Teoretycznie zrób to, pobierając dodatek Power Pivot i wykonując wszystkie te kroki w 2010 r. Utwórz oba zestawy danych w tabeli Ctrl + T, a następnie połącz swoje tabele ze wszystkim, co chcesz raportować, w etykieta wiersza, etykieta kolumny lub fragmentatory, więc skopiuj te wartości i Usuń duplikaty dat. Właściwie wziąłem wartości z obu tabel, ponieważ w każdej były jakieś unikalne wartości, a następnie użyłem EOMONTH, aby się tam dostać, aby te tabele łączące były tabelami kontrolowanymi. Jest to opcjonalne, ale nazwałem wszystkie 5 tabel, ponieważ łatwiej jest konfigurować te relacje, zamiast nazywać się Tabela1,Tabela 2, Tabela 3.
Dlatego zacznij od tabeli Budżet, Wstaw, Tabela przestawna, zaznacz pole wyboru Model danych, a następnie utwórz tabelę przestawną przy użyciu opcji Budżet i Rzeczywista. Cała reszta pochodzi z tabel łączących, więc region i miesiąc w obszarze wiersza i kolumny fragmentatory pochodzą z tabeli Produkt. Następnie musieliśmy utworzyć 3 relacje od budżetu do łączników, 3 relacje od rzeczywistego do łączących i mamy niesamowitą tabelę przestawną. Teraz jutro przyjrzymy się używaniu karty Power Pivot i tworzeniu dodatkowych obliczeń. Wszystko to jest więc możliwe, gdy chcemy wstawić pole obliczeniowe, wtedy musisz zapłacić dodatkowe 2 $ miesięcznie, aby uzyskać wersję Pro Plus Office 365.
Cóż, dzięki Robowi Collie z Power Pivot Pro za tę wskazówkę i dzięki za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem od!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2016.xlsx
