Jedną z rzeczy, które najbardziej wymieniam w PowerPivot dla programu Excel, jest możliwość dodawania tabel odnośników do zestawów danych. W większości przypadków dane, z którymi pracujesz, nie mają wszystkich pól, które potrzebujesz do analizy. Na przykład możesz mieć pole daty, ale musisz pogrupować dane według kwartału. Można napisać formułę, ale łatwiej jest utworzyć prostą tabelę odnośników w środowisku PowerPivot.
Możesz również użyć tej tabeli odnośników do innej grupy, takiej jak nazwa miesiąca i pierwsza / druga połowa roku. W terminach magazynowania danych faktycznie tworzysz tabelę wymiarów daty. W tym artykule przedstawię kilka przykładowych tabel wymiarów, aby ulepszyć twój projekt PowerPivot for Excel.
Nowa tabela wymiarów tekstu (wyszukiwania)
Rozważmy tabelę z danymi zamówienia. Załóżmy, że tabela zawiera pola dla klienta, daty zamówienia, sumy zamówień i rodzaju zamówienia. Skoncentrujemy się na polu typu zamówienia. Załóżmy, że pole typu zamówienia zawiera wartości takie jak:
- Netbooki
- Komputery stacjonarne
- Monitory
- Projektory
- Drukarki
- Skanery
- Aparaty cyfrowe
- Cyfrowe lustrzanki
- Kamery filmowe
- Kamkordery
- Telefony biurowe
- Inteligentne telefony
- PDA
- Akcesoria do telefonów komórkowych
W rzeczywistości będziesz miał kod dla tych, ale aby ten przykład był prosty, załóżmy, że są to rzeczywiste wartości w tabeli zamówień. Korzystając z programu PowerPivot dla programu Excel, łatwo będzie można pogrupować zamówienia według typu zamówienia.
A co jeśli chciałbyś innej grupy? Załóżmy na przykład, że potrzebujesz grupowania według kategorii, takich jak komputery, aparaty fotograficzne i telefony. Tabela zamówień nie ma pola kategorii, ale można ją łatwo utworzyć jako tabelę odnośników w programie PowerPivot for Excel. Pełna tabela wyszukiwania próbek znajduje się poniżej Tabela 1 .
Oto kroki:
- Krok 1: Potrzebujesz oddzielnej listy z pola typu dla tabeli odnośników. To będzie twoje pole wyszukiwania. Z zestawu danych utwórz osobną listę wartości z pola typu zamówienia. Wprowadź oddzielną listę typów do skoroszytu programu Excel. Oznacz etykietę kolumny Rodzaj .
- Krok 2: W kolumnie obok kolumny odnośnika (Typ) dodaj nowe pole, które chcesz zgrupować. W naszym przykładzie dodaj kolumnę z etykietą o nazwie Kategoria .
- Krok 3: Dla każdej wartości na odrębnej liście wartości (typy w tym przykładzie) dodaj odpowiednią wartość Kategoria wartości. W naszym prostym przykładzie wpisz albo Komputery , Aparaty fotograficzne lub Telefony do Kategoria kolumna.
- Krok 4: Skopiuj Rodzaj i Kategoria tabelę danych do schowka.
- Krok 5: Otwórz skoroszyt programu Excel z danymi zamówienia w programie PowerPivot dla programu Excel. Uruchom okno PowerPivot. Kliknij Wklej, co spowoduje pojawienie się Twojej nowej tabeli odnośników. Nadaj tabeli nazwę i upewnij się, że sprawdzisz Użyj pierwszego wiersza jako nagłówków kolumn. Kliknij dobrze. Utworzono tabelę odnośników w PowerPivot.
- Krok 6: Utwórz relację między Rodzaj pole w Zamówienie tabela i Kategoria pole w tabeli odnośników. Kliknij na Projekt wstążka i wybierz Utwórz relację. Dokonaj wyborów w Utwórz relację dialog i kliknij Stwórz.
Podczas tworzenia tabeli przestawnej w programie Excel na podstawie danych programu PowerPivot będzie można grupować według nowych Kategoria pole. Należy pamiętać, że PowerPivot dla programu Excel obsługuje tylko wewnętrzne połączenia. Jeśli masz typ zamówienia brakujące w tabeli odnośników, wszystkie odpowiednie rekordy dla tego typu nie będą dostępne w żadnej tabeli przestawnej na podstawie danych programu PowerPivot. Będziesz musiał to sprawdzić od czasu do czasu.
Tabela wymiaru daty (wyszukiwania)
Tabela odnośników daty najprawdopodobniej będzie potrzebna w większości projektów PowerPivot for Excel. Większość zestawów danych ma jakiś typ pól daty. Istnieją funkcje do obliczania roku i miesiąca.
Jednakże, jeśli potrzebujesz faktycznego tekstu miesiąca lub kwartału, musisz napisać złożoną formułę. Znacznie łatwiej jest dołączyć tabelę wymiarów wymiaru (Date) i porównać ją z numerem miesiąca w głównym zestawie danych. Do tabeli zamówień należy dodać kolumnę, która będzie zawierała numer miesiąca z pola daty zamówienia. Formuła DAX dla miesiąc w naszym przykładzie jest = MIESIĄC (Data zamówienia). Zwróci to liczbę od 1 do 12 dla każdego rekordu. Nasza tabela wymiarów zawiera alternatywne wartości, które zawierają link do numeru miesiąca. Zapewni to elastyczność w analizie. Pełna tabela wymiarów przykładowej daty znajduje się poniżej Tabela 2 .
Wymiar daty lub tabela wyszukiwania będzie zawierać 12 rekordów. Kolumna miesiąca będzie miała wartości od 1 do 12. Pozostałe kolumny będą zawierać skrócony tekst miesiąca, pełny tekst miesiąca, kwartał itp. Oto kroki:
- Krok 1: Skopiuj tabelę z tabeli 2 poniżej i wklej do PowerPivot. Możesz utworzyć tę tabelę w Excelu, ale oszczędzam Twój czas. Powinieneś być w stanie wkleić bezpośrednio z wybranych danych poniżej, jeśli korzystasz z przeglądarki Internet Explorer lub Edge. PowerPivot wybiera formatowanie tabeli w moich testach. Jeśli używasz innej przeglądarki, możesz najpierw wkleić do programu Excel i skopiować ją z programu Excel, aby pobrać formatowanie tabeli.
- Krok 2: Otwórz skoroszyt programu Excel z danymi zamówienia w programie PowerPivot dla programu Excel. Uruchom okno PowerPivot. Kliknij Pasta co spowoduje, że twoja tabela odnośników zostanie skopiowana z poniższej tabeli lub z Excela. Nadaj tabeli nazwę i upewnij się, że sprawdzisz Użyj pierwszego wiersza jako nagłówków kolumn. Kliknij dobrze. Utworzono tabelę wyszukiwania dat w PowerPivot.
- Krok 3: Utwórz relację między Miesiąc pole w Zamówienie tabela i Numer miesiąca pole w tabeli odnośników. Kliknij na Projekt wstążka i wybierz Utwórz relację. Dokonaj wyborów w Utwórz relację dialog i kliknij Stwórz.
Ponownie, po dodaniu wymiaru daty będzie można grupować dane w tabeli przestawnej za pomocą dowolnej z różnych wartości z tabeli wyszukiwania daty. Grupowanie według kwartału lub nazwy miesiąca będzie bardzo proste.
Przykładowe tabele wymiarów (odnośników)
Tabela 1
| Rodzaj | Kategoria |
| Netbooki | Komputer |
| Komputery stacjonarne | Komputer |
| Monitory | Komputer |
| Projektory i ekrany | Komputer |
| Drukarki, skanery i faksy | Komputer |
| Konfiguracja i serwis komputera | Komputer |
| Akcesoria komputerowe | Komputer |
| Aparaty cyfrowe | Aparat fotograficzny |
| Cyfrowe lustrzanki | Aparat fotograficzny |
| Kamery filmowe | Aparat fotograficzny |
| Kamkordery | Aparat fotograficzny |
| Akcesoria do aparatów i kamer | Aparat fotograficzny |
| Telefony domowe i biurowe | Telefon |
| Telefony z ekranem dotykowym | Telefon |
| Smartfony i urządzenia PDA | Telefon |
Tabela 2
| Numer miesiąca | MonthTextShort | MonthTextFull | Jedna czwarta | Semestr |
| 1 | Jan | styczeń | Q1 | H1 |
| 2 | Lut | luty | Q1 | H1 |
| 3 | Zniszczyć | Marsz | Q1 | H1 |
| 4 | Apr | kwiecień | Q2 | H1 |
| 5 | Może | Może | Q2 | H1 |
| 6 | Jun | czerwiec | Q2 | H1 |
| 7 | Jul | lipiec | Q3 | H2 |
| 8 | Aug | sierpień | Q3 | H2 |
| 9 | Sep | wrzesień | Q3 | H2 |
| 10 | Paź | październik | P4 | H2 |
| 11 | Nov | listopad | P4 | H2 |
| 12 | Dec | grudzień | P4 | H2 |
