Samouczek: Analiza danych w tabeli przestawnej przy użyciu modelu danych w programie Excel 2013

W programie Excel w czasie krótszym niż godzina można utworzyć raport w formie tabeli przestawnej, w którym zostaną połączone dane z wielu tabel. W pierwszej części tego samouczka omówiono czynności importowania i eksplorowania danych. W drugiej części opisano sposób użycia dodatku Power Pivot do uściślenia modelu danych, na którym jest oparty raport, i wyjaśniono, jak dodawać obliczenia, hierarchie i optymalizacje na potrzeby raportów programu Power View.

Zacznijmy od zaimportowania danych.

  1. Pobierz przykładowe dane (ContosoV2) dla tego samouczka. Zobacz temat Pobieranie przykładowych danych dla języka DAX oraz samouczków dotyczących modelu danych, aby uzyskać szczegółowe informacje. Wyodrębnij i zapisz pliki danych w łatwo dostępnej lokalizacji, takiej jak folder Pobieranie lub Moje dokumenty.

  2. Otwórz pusty skoroszyt w programie Excel.

  3. Kliknij pozycję Dane > Pobieranie danych zewnętrznych > Z programu Access.

  4. Przejdź do folderu zawierającego przykładowe pliki danych i wybierz plik Sprzedaż Contoso.

  5. Kliknij przycisk Otwórz. Łączysz się z plikiem bazy danych, który zawiera wiele tabel, dlatego zostanie wyświetlone okno dialogowe Wybieranie tabeli, aby można było wybrać tabele do zaimportowania.

    Okno dialogowe wybierania tabeli

  6. W oknie Wybieranie tabeli zaznacz pole Włącz zaznaczanie wielu tabel.

  7. Wybierz wszystkie tabele i kliknij przycisk OK.

  8. W oknie Importowanie danych kliknij pozycję Raport w formie tabeli przestawnej i kliknij przycisk OK.

    Uwagi : 

    • Być może jeszcze o tym nie wiesz, ale właśnie został utworzony model danych. Model to warstwa integracji danych tworzona automatycznie podczas importowania wielu tabel jednocześnie lub pracy z wieloma tabelami jednocześnie w raporcie w formie tabeli przestawnej.

    • W programie Excel model jest w większości przezroczysty, ale można go wyświetlić i bezpośrednio modyfikować przy użyciu dodatku Power Pivot . W programie Excel obecność modelu danych można stwierdzić, gdy na liście pól tabeli przestawnej znajduje się kolekcja tabel. Model można utworzyć na kilka sposobów. Aby uzyskać szczegółowe informacje, zobacz Tworzenie modelu danych w programie Excel .

Eksplorowanie danych za pomocą tabeli przestawnej

Eksplorowanie danych jest proste i polega na przeciąganiu pól do obszarów Wartości, Kolumny i Wiersze na liście pól tabeli przestawnej.

  1. Przewiń listę pól w dół, tak aby wyświetlić tabelę SprzedażRzeczywista.

  2. Kliknij pozycję KwotaSprzedaży. Są to dane liczbowe, dlatego program Excel automatycznie umieści pole KwotaSprzedaży w obszarze Wartości.

  3. W tabeli WymiarData przeciągnij pole RokKalendarzowy do obszaru Kolumny.

  4. W tabeli WymiarPodkategoriaProduktu przeciągnij pole NazwaPodkategoriiProduktu do obszaru Wiersze.

  5. W tabeli WymiarProdukt przeciągnij pole NazwaMarki do obszaru Wiersze, umieszczając je pod polem podkategorii.

Utworzona tabela przestawna powinna wyglądać podobnie do tabeli na następującym ekranie.

Tabela przestawna z przykładowymi danymi

Przy niewielkim nakładzie pracy została utworzona podstawowa tabela przestawna, która zawiera pola z czterech różnych tabel. To zadanie było tak proste dzięki istniejącym wcześniej relacjom pomiędzy tabelami. Ponieważ w źródle istniały relacje pomiędzy tabelami i wszystkie tabele zostały zaimportowane za pomocą jednej operacji, program Excel mógł odtworzyć te relacje w modelu.

Ale co zrobić, jeśli dane pochodzą z różnych źródeł lub zostaną zaimportowane w późniejszym czasie? Zwykle nowe dane można dodać, tworząc relacje na podstawie zgodnych kolumn. W następnym kroku zaimportujesz dodatkowe tabele i dowiesz się, jakie są wymagania i czynności dotyczące tworzenia nowych relacji.

Dodawanie kolejnych tabel

Aby dowiedzieć się, jak skonfigurować relacje pomiędzy tabelami, potrzebujesz dodatkowych, niepodłączonych tabel, na których można pracować. W tym kroku uzyskasz pozostałe dane używane w tym samouczku, importując jeden dodatkowy plik bazy danych i wklejając dane z dwóch innych skoroszytów.

Dodawanie kategorii produktów

  1. Otwórz nowy arkusz w skoroszycie. Będzie on służył do przechowywania dodatkowych danych.

  2. Kliknij pozycję Dane > Pobieranie danych zewnętrznych > Z programu Access.

  3. Przejdź do folderu zawierającego przykładowe pliki danych i wybierz plik KategorieProduktów. Kliknij przycisk Otwórz.

  4. W oknie Importowanie danych wybierz opcję Tabela i kliknij przycisk OK.

Dodawanie danych geograficznych

  1. Wstaw kolejny arkusz.

  2. W folderze przykładowych plików danych otwórz plik Geografia.xlsx, umieść kursor w komórce A1, a następnie naciśnij klawisze Ctrl-Shift-End, aby zaznaczyć wszystkie dane.

  3. Skopiuj dane do Schowka.

  4. Wklej te dane do właśnie dodanego pustego arkusza.

  5. Kliknij pozycję Formatuj jako tabelę i wybierz dowolny styl. Sformatowanie danych jako tabeli umożliwi nadanie im nazwy, która będzie potrzebna podczas definiowania relacji w jednym z kolejnych kroków.

  6. W oknie Formatowanie jako tabeli sprawdź, czy została zaznaczona opcja Moja tabela zawiera nagłówki. Kliknij przycisk OK.

  7. Nadaj tabeli nazwę Geografia. Na karcie Narzędzia tabel > Projektowanie w polu Nazwa tabeli wpisz nazwę Geografia.

  8. Zamknij plik Geografia.xlsx, aby usunąć go z obszaru roboczego.

Dodawanie danych dotyczących sklepów

  • Powtórz poprzednie kroki w odniesieniu do pliku Sklepy.xlsx, wklejając jego zawartość do pustego arkusza. Nadaj tabeli nazwę Sklepy.

Teraz dostępne są cztery arkusze. Arkusz1 zawiera tabelę przestawną, Arkusz2 zawiera tabelę KategorieProduktów, Arkusz3 zawiera tabelę Geografia, a Arkusz4 zawiera tabelę Sklepy. Dzięki poświęceniu czasu na nadanie nazwy każdej tabeli wykonanie następnego kroku polegającego na utworzeniu relacji będzie o wiele prostsze.

Używanie pól z nowo zaimportowanych tabel

Możesz od razu zacząć używać pól z właśnie zaimportowanych tabel. Jeśli program Excel nie może ustalić, jak dodać pole do raportu w formie tabeli przestawnej, zostanie wyświetlony monit o utworzenie relacji pomiędzy tabelami, która skojarzy nową tabelę z tą, która już jest częścią modelu.

  1. U góry listy pól tabeli przestawnej kliknij pozycję Wszystkie, aby wyświetlić pełną listę dostępnych tabel.

  2. Przewiń do dołu listy. Tam znajdują się nowe tabele, które właśnie zostały dodane.

  3. Rozwiń węzeł Sklepy.

  4. Przeciągnij pole NazwaSklepu do obszaru Filtry.

  5. W programie Excel zostanie wyświetlony monit o utworzenie relacji. To powiadomienie jest wyświetlane, ponieważ używasz pól z tabeli nieskojarzonej z modelem.

  6. Kliknij przycisk Utwórz, aby otworzyć okno dialogowe Tworzenie relacji.

  7. W polu Tabela wybierz pozycję SprzedażRzeczywista. W używanych przykładowych danych tabela SprzedażRzeczywista zawiera szczegółowe informacje dotyczące sprzedaży i kosztów firmy Contoso, a także klucze do innych tabel, w tym kody sklepów znajdujące się również w pliku Sklepy.xlsx, który został zaimportowany w poprzednim kroku.

  8. W polu Kolumna (obca) wybierz pozycję KluczSklepu.

  9. W polu Powiązana tabela wybierz pozycję Sklepy.

  10. W polu Powiązana kolumna (podstawowa) wybierz pozycję KluczSklepu.

  11. Kliknij przycisk OK.

W tle program Excel tworzy model danych, którego można używać globalnie w skoroszycie w wielu tabelach przestawnych, na wykresach przestawnych i w raportach programu Power View. Podstawą tego modelu są relacje pomiędzy tabelami, które określają ścieżki nawigowania po danych oraz wykonywania obliczeń używane w raporcie w formie tabeli przestawnej. W następnym zadaniu ręcznie utworzysz relacje, aby połączyć właśnie zaimportowane dane.

Dodawanie relacji

Dla wszystkich nowych tabel, które importujesz, możesz systematycznie tworzyć relacje pomiędzy tabelami. Jeśli udostępniasz skoroszyt współpracownikom, wstępnie zdefiniowane relacje będą pomocne dla osób, które nie znają danych tak dobrze, jak Ty.

Podczas ręcznego tworzenia relacji będziesz pracować z dwoma tabelami jednocześnie. Dla każdej tabeli wybierzesz kolumny, które będą informować program Excel, jak ma wyszukiwać powiązane wiersze w innej tabeli.

Przeglądarka nie obsługuje klipu wideo. Instalowanie dodatku Microsoft Silverlight, Adobe Flash Player lub programu Internet Explorer 9.

Tworzenie relacji między tabelami PodkategoriaProduktu i KategoriaProduktu

  1. W programie Excel kliknij pozycję Dane > Relacje > Nowe.

  2. W polu Tabela wybierz pozycję WymiarPodkategoriaProduktu.

  3. W polu Kolumna (obca) wybierz pozycję KluczKategoriiProduktu.

  4. W polu Powiązana tabela wybierz pozycję Tabela_KategoriaProduktu.accdb.

  5. W polu Powiązana kolumna (podstawowa) wybierz pozycję KluczKategoriiProduktu.

  6. Kliknij przycisk OK.

  7. Zamknij okno dialogowe Zarządzanie relacjami.

Dodawanie kategorii do tabeli przestawnej

Mimo że model danych został już zaktualizowany, dzięki czemu zawiera dodatkowe tabele i relacje, nie są one jeszcze używane w tabeli przestawnej. W tym zadaniu dodasz tabelę KategoriaProduktu do listy pól tabeli przestawnej.

  1. Na liście pól tabeli przestawnej kliknij pozycję Wszystkie, aby wyświetlić tabele istniejące w modelu danych.

  2. Przewiń do dołu listy.

  3. Usuń pole NazwaMarki z obszaru Wiersze.

  4. Rozwiń węzeł Tabela_WymiarKategorieProduktów.accdb.

  5. Przeciągnij pole NazwaKategoriiProduktu do obszaru Wiersze i umieść je nad polem PodkategoriaProduktu.

  6. Na liście pól tabeli przestawnej kliknij pozycję Aktywne, aby sprawdzić, czy właśnie użyte tabele są aktywne w tabeli przestawnej.

Punkt kontrolny: sprawdzanie zakresu opanowanego materiału

Masz teraz tabelę przestawną zawierającą dane z wielu tabel, z których kilka zostało zaimportowanych w kolejnym kroku. Aby połączyć te dane, trzeba było utworzyć relacje pomiędzy tabelami, za pomocą których program Excel koreluje wiersze. Wiesz, że posiadanie kolumn dostarczających zgodne dane jest niezbędne do wyszukiwania powiązanych wierszy. Każda tabela w przykładowych plikach danych zawiera kolumnę, której można użyć w tym celu.

Co prawda tabela przestawna jest funkcjonalna, ale widać, że kilka rzeczy można poprawić. Wygląda na to, że lista pól tabeli przestawnej zawiera dodatkowe tabele (WymiarJednostka) i kolumny (IdentyfikatorŁadowaniaETL), które nie są związane z działalnością firmy Contoso. Ponadto wciąż nie zostały zintegrowane dane z tabeli Geografia.

Następne czynności: wyświetlanie i rozszerzanie modelu przy użyciu dodatku Power Pivot

W następnej serii zadań rozszerzysz model przy użyciu dodatku Microsoft Office Power Pivot w programie Microsoft Excel 2013. Przekonasz się, że relacje można tworzyć o wiele prościej, korzystając z dostępnego w tym dodatku widoku diagramu. Ponadto przy użyciu dodatku utworzysz obliczenia i hierarchie, ukryjesz elementy, które nie powinny być widoczne na liście pól, oraz zoptymalizujesz dane pod kątem dodatkowych zadań raportowania.

Uwaga :  Dodatek Power Pivot w programie Microsoft Excel 2013 jest dostępny w pakiecie Office Professional Plus. Aby uzyskać więcej informacji, zobacz Dodatek Power Pivot w programie Microsoft Excel 2013.

Dodaj kartę Power Pivot do wstążki programu Excel, włączając dodatek Power Pivot.

  1. Przejdź do pozycji Plik > Opcje > Dodatki.

  2. W polu Zarządzaj kliknij pozycję Dodatki COM > Przejdź.

  1. Zaznacz pole Microsoft Office Power Pivot w programie Microsoft Excel 2013, a następnie kliknij przycisk OK.

Wstążka ma teraz kartę Power Pivot.

Dodawanie relacji przy użyciu widoku diagramu w dodatku Power Pivot

  1. W programie Excel kliknij pozycję Arkusz3, aby ustawić ten arkusz jako aktywny. Arkusz Arkusz3 zawiera zaimportowaną wcześniej tabelę Geografia.

  2. Na wstążce kliknij pozycję Power Pivot > Dodaj do modelu danych. Ta czynność spowoduje dodanie do modelu tabeli Geografia oraz otwarcie dodatku Power Pivot, przy użyciu którego wykonasz pozostałe czynności w tym zadaniu.

  3. Zauważ, że w oknie dodatku Power Pivot są wyświetlane wszystkie tabele w modelu, w tym tabela Geografia. Kliknij kilka tabel. W tym dodatku można wyświetlić wszystkie dane zawarte w modelu.

  4. W oknie dodatku Power Pivot w sekcji Widok kliknij pozycję Widok diagramu

  5. Użyj paska suwaka, aby zmienić rozmiar diagramu, tak aby były widoczne wszystkie obiekty na diagramie. Zauważ, że istnieją dwie tabele niepowiązane z resztą diagramu: WymiarJednostka i Geografia.

  6. Kliknij prawym przyciskiem myszy pozycję WymiarJednostka i kliknij polecenie Usuń. Ta tabela jest artefaktem z oryginalnej bazy danych i nie jest potrzebna w tym modelu.

  7. Powiększ tabelę Geografia, tak aby wyświetlić wszystkie jej pola. Diagram można powiększyć za pomocą suwaka.

  8. Zauważ, że tabela Geografia zawiera kolumnę KluczGeografii. W tej kolumnie znajdują się wartości, które jednoznacznie identyfikują każdy wiersz w tabeli Geografia. Sprawdźmy, czy inne tabele w modelu także używają tego klucza. Jeśli tak, możemy utworzyć relację łączącą tabelę Geografia z resztą modelu.

  9. Kliknij przycisk Znajdź.

  10. W oknie Znajdowanie metadanych wpisz ciąg KluczGeografii.

  11. Kliknij kilka razy przycisk Znajdź następny element. Zauważysz, że kolumna KluczGeografii znajduje się w tabelach Geografia i Sklepy.

  12. Zmień położenie tabeli Geografia tak, aby znajdowała się obok tabeli Sklepy.

  13. Przeciągnij kolumnę KluczGeografii w tabeli Sklepy do kolumny KluczGeografii w tabeli Geografia. W dodatku Power Pivot zostanie narysowana linia między tymi dwiema kolumnami, która będzie wskazywać relację.

Podczas wykonywania tego zadania użytkownik poznał nową technikę dodawania tabel i tworzenia relacji. Teraz istnieje w pełni zintegrowany model, w którym wszystkie tabele są połączone i dostępne dla tabeli przestawnej znajdującej się w Arkuszu1.

Porada :  W widoku diagramu kilka diagramów tabel jest w pełni rozwiniętych, wyświetlając kolumny takie jak IdentyfikatorŁadowaniaETL, DataŁadowania i DataAktualizacji. Te pola są artefaktami z oryginalnej hurtowni danych firmy Contoso, dodanymi w celu obsługi operacji ładowania i wyodrębniania danych. Nie potrzebujesz ich w swoim modelu. Aby się ich pozbyć, wyróżnij i kliknij prawym przyciskiem myszy odpowiednie pole, a następnie kliknij polecenie Usuń .

Tworzenie kolumny obliczeniowej

W dodatku Power Pivot możesz dodawać obliczenia przy użyciu języka DAX (Data Analysis Expressions). W tym zadaniu obliczysz całkowity zysk i dodasz kolumnę obliczeniową, która odwołuje się do wartości danych z innych tabel. Później zobaczysz, jak można uprościć model przy użyciu kolumn z odwołaniami.

  1. W oknie dodatku Power Pivot przejdź z powrotem do widoku danych.

  2. Zmień nazwę tabeli Tabela_KategorieProduktów.accdb na prostszą. W kolejnych krokach będziesz tworzyć odwołania do tej tabeli i dzięki krótszej nazwie obliczenia będą łatwiejsze do odczytania. Kliknij prawym przyciskiem myszy nazwę tabeli, kliknij polecenie Zmień nazwę, wpisz nazwę KategorieProduktów, a następnie naciśnij klawisz Enter.

  3. Zaznacz tabelę SprzedażRzeczywista.

  4. Kliknij pozycję Projekt > Kolumny > Dodaj.

  5. Na pasku formuły nad tabelą wpisz poniższą formułę. Funkcja autouzupełniania pomaga wprowadzić w pełni kwalifikowane nazwy kolumn i tabel oraz wyświetla dostępne funkcje. Możesz także kliknąć kolumnę, a dodatek Power Pivot wstawi nazwę tej kolumny do formuły.

    = [KwotaSprzedaży] - [KosztCałkowity] - [KwotaZwrotu]

  6. Gdy skończysz konstruować formułę, naciśnij klawisz Enter, aby ją zaakceptować.

    Wszystkie wiersze kolumny obliczeniowej zostaną wypełnione wartościami. Przewijając tabelę w dół, można zobaczyć, że te wiersze mają różne wartości dla tej kolumny, co jest zależne od danych w każdym z tych wierszy.

  7. Zmień nazwę kolumny, klikając prawym przyciskiem myszy pozycję CalculatedColumn1 i wybierając polecenie Zmień nazwę kolumny. Wpisz nazwę Zysk i naciśnij klawisz Enter.

  8. Tera zaznacz tabelę WymiarProdukt.

  9. Kliknij pozycję Projekt > Kolumny > Dodaj.

  10. Na pasku formuły nad tabelą wpisz następującą formułę.

    = RELATED(KategorieProduktów[NazwaKategoriiProduktu])

    Funkcja RELATED zwraca wartość z powiązanej tabeli. W tym przypadku tabela KategorieProduktów zawiera nazwy kategorii produktów, które będą przydatne w tabeli WymiarProdukt podczas tworzenia hierarchii zawierającej informacje o kategoriach. Aby uzyskać więcej informacji o tej funkcji, zobacz RELATED, funkcja (DAX).

  11. Gdy skończysz konstruować formułę, naciśnij klawisz Enter, aby ją zaakceptować.

    Wszystkie wiersze kolumny obliczeniowej zostaną wypełnione wartościami. Przewijając tabelę w dół, można zobaczyć, że każdy wiersz zawiera nazwę kategorii produktu.

  12. Zmień nazwę kolumny, klikając prawym przyciskiem myszy pozycję CalculatedColumn1 i wybierając polecenie Zmień nazwę kolumny. Wpisz nazwę KategoriaProduktu i naciśnij klawisz Enter.

  13. Kliknij pozycję Projekt > Kolumny > Dodaj.

  14. Na pasku formuły nad tabelą wpisz następującą formułę, a następnie naciśnij klawisz Enter, aby zaakceptować tę formułę.

    = RELATED(WymiarPodkategoriaProduktu[NazwaPodkategoriiProduktu])

  15. Zmień nazwę kolumny, klikając prawym przyciskiem myszy pozycję CalculatedColumn1 i wybierając polecenie Zmień nazwę kolumny. Wpisz nazwę PodkategoriaProduktu i naciśnij klawisz Enter.

Tworzenie hierarchii

Większość modeli zawiera dane, które dziedzicznie są hierarchiczne. Typowymi przykładami są dane kalendarzowe, dane geograficzne i kategorie produktów. Tworzenie hierarchii jest użyteczne, ponieważ umożliwia przeciągnięcie jednego elementu (hierarchii) do raportu, bez konieczności ciągłego kompletowania i porządkowania tych samych pól.

  1. W programie Power Pivot przełącz się do widoku diagramu. Rozwiń tabelę WymiarData, co ułatwi przeglądanie wszystkich jej pól.

  2. Naciśnij i przytrzymaj klawisz Ctrl, a następnie kliknij kolumny RokKalendarzowy, KwartałKalendarzowy i MiesiącKalendarzowy (będzie konieczne przewinięcie w dół tabeli).

  3. Mając zaznaczone te trzy kolumny, kliknij prawym przyciskiem myszy jedną z nich i kliknij polecenie Utwórz hierarchię. U dołu tabeli zostanie utworzony węzeł nadrzędny hierarchii, Hierarchia 1, a wybrane kolumny zostaną skopiowane do hierarchii jako węzły podrzędne.

  4. Wpisz nazwę Daty jako nazwę nowej hierarchii.

  5. Dodaj do hierarchii kolumnę EtykietaPełnejDaty. Kliknij prawym przyciskiem myszy kolumnę EtykietaPełnejDaty i wybierz polecenie Dodaj do hierarchii. Wybierz pozycję Data. Kolumna EtykietaPełnejDaty zawiera pełną datę, czyli rok, miesiąc i dzień. Sprawdź, czy kolumna EtykietaPełnejDaty jest ostatnia w hierarchii. Teraz masz wielopoziomową hierarchię, która obejmuje rok, kwartał, miesiąc i poszczególne dni kalendarzowe.

  6. Przy wciąż aktywnym widoku diagramu wskaż tabelę WymiarProdukt, a następnie kliknij przycisk Utwórz hierarchię w nagłówku tabeli. U dołu tabeli pojawi się pusty węzeł nadrzędny hierarchii.

  7. Wpisz nazwę Kategorie produktów jako nazwę nowej hierarchii.

  8. Aby utworzyć węzły podrzędne hierarchii, przeciągnij na nią kolumny KategoriaProduktu i PodkategoriaProduktu.

  9. Kliknij prawym przyciskiem myszy pozycję NazwaProduktu i wybierz polecenie Dodaj do hierarchii. Wybierz pozycję Kategorie produktów.

Teraz, gdy użytkownik poznał już kilka różnych metod tworzenia hierarchii, można użyć ich w tabeli przestawnej.

  1. Wróć do programu Excel.

  2. W arkuszu Arkusz1 (zawierającym tabelę przestawną) usuń pola z obszaru Wiersze.

  3. Zamień je na nową hierarchię Kategorie produktów z tabeli WymiarProdukt.

  4. Podobnie zamień pole RokKalendarzowy w obszarze Kolumny na hierarchię Daty z tabeli WymiarData.

Teraz podczas eksplorowania danych łatwo można zauważyć korzyści wynikające z użycia hierarchii. Możesz niezależnie rozwijać i zamykać różne obszary tabeli przestawnej, co umożliwia lepszą kontrolę nad wykorzystaniem dostępnego miejsca. Co więcej, dodanie pojedynczych hierarchii do obszarów Wiersze i Kolumny umożliwia rozbudowane i natychmiastowe rozwijanie szczegółów, bez konieczności zestawiania wielu pól w celu uzyskania podobnego efektu.

Ukrywanie kolumn

Po utworzeniu hierarchii Kategorie produktów i umieszczeniu jej w tabeli WymiarProdukt nie ma już potrzeby przechowywania kolumn WymiarKategoriaProduktu i WymiarPodkategoriaProduktu na liście pól tabeli przestawnej. Podczas wykonywania tego zadania użytkownik nauczy się usuwać nadmiarowe tabele i kolumny, które zajmują miejsce na liście pól tabeli przestawnej. Ukrywając tabele i kolumny, można zwiększyć możliwości w zakresie raportowania, bez wywierania wpływu na model dostarczający relacje między danymi i wykonywane na nich obliczenia.

Przeglądarka nie obsługuje klipu wideo. Instalowanie dodatku Microsoft Silverlight, Adobe Flash Player lub programu Internet Explorer 9.

Możesz ukryć pojedyncze kolumny, zakres kolumn lub całą tabelę. Nazwy tabel i kolumn są wyszarzane, aby wskazać, że są ukryte przed klientami raportowania używającymi danego modelu. Ukryte kolumny są wyszarzane w modelu w celu wskazania ich stanu, ale pozostają widoczne w widoku danych, dzięki czemu nadal można z nimi pracować.

  1. W programie Power Pivot upewnij się, że jest wybrany widok danych.

  2. W obszarze kart u dołu kliknij prawym przyciskiem myszy kartę WymiarPodkategoriaProduktu i wybierz polecenie Ukryj przed narzędziami klienta.

  3. Powtórz ten krok w odniesieniu do tabeli KategorieProduktów.

  4. Otwórz tabelę WymiarProdukt.

  5. Kliknij prawym przyciskiem myszy następujące kolumny, a następnie kliknij polecenie Ukryj przed narzędziami klienta:

    • KluczProduktu

    • EtykietaProduktu

    • PodkategoriaProduktu

  6. Zaznacz wiele sąsiadujących kolumn. Zacznij od kolumny IdentyfikatorKlasy i kontynuuj do kolumny PodkategoriaProduktu znajdującej się na końcu. Kliknij prawym przyciskiem myszy, aby je ukryć.

  7. Powtórz te kroki w odniesieniu do innych tabel, usuwając identyfikatory, klucze i inne szczegóły, które nie są potrzebne w tym raporcie.

Przełącz się z powrotem do programu Excel i arkusza Arkusz1, który zawiera listę pól tabeli przestawnej, aby zobaczyć różnicę. Liczba tabel została zmniejszona, a tabela WymiarProdukt zawiera tylko te elementy, których najprawdopodobniej będziesz używać podczas analizowania sprzedaży.

Tworzenie raportu programu Power View

Raporty w formie tabeli przestawnej nie są jedynymi raportami, w których są wykorzystywane zalety modelu danych. Używając właśnie utworzonego modelu, możesz dodać arkusz programu Power View i wypróbować kilka oferowanych przez niego układów.

  1. W programie Excel kliknij pozycję Wstawianie > Power View.

    Uwaga :  Jeśli po raz pierwszy używasz programu Power View na tym komputerze, zostanie wyświetlony monit o włączenie dodatku i zainstalowanie programu Silverlight.

  2. W polach programu Power View kliknij strzałkę obok tabeli SprzedażRzeczywista i kliknij pozycję KwotaSprzedaży.

  3. Rozwiń tabelę Geografia i kliknij kolumnę NazwaRegionuKraju.

  4. Na wstążce kliknij przycisk Mapa.

  5. Zostanie wyświetlony raport w formie mapy. Przeciągnij narożnik, aby zmienić jego rozmiar. Widoczne na mapie niebieskie kółka o różnych rozmiarach wskazują wyniki sprzedaży w różnych krajach lub regionach.

Optymalizowanie pod kątem raportów programu Power View

Wprowadzenie kilku małych zmian do modelu spowoduje, że podczas projektowania raportu programu Power View odpowiedzi będą bardziej intuicyjne. W tym zadaniu dodasz adresy URL witryn sieci Web dla kilku producentów, a następnie skategoryzujesz te dane jako adres URL sieci Web, aby adres URL był wyświetlany jako łącze.

Najpierw dodaj adresy URL do skoroszytu.

  1. W programie Excel otwórz nowy arkusz i skopiuj następujące wartości:

AdresURLProducenta

IdentyfikatorProducenta

http://www.contoso.com

Contoso

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam

  1. Sformatuj komórki jako tabelę i nadaj jej nazwę AdresURL.

  2. Utwórz relację pomiędzy tabelą AdresURL i tabelą, która zawiera nazwy producentów, WymiarProdukt:

    1. Kliknij pozycję Dane > Relacje. Zostanie wyświetlone okno dialogowe Tworzenie relacji.

    2. Kliknij pozycję Nowe.

    3. W polu Tabela wybierz pozycję WymiarProdukt.

    4. W polu Kolumna wybierz pozycję Producent.

    5. W polu Powiązana tabela wybierz pozycję AdresURL.

    6. W polu Powiązana kolumna (podstawowa) wybierz pozycję IdentyfikatorProducenta.

Aby porównać wyniki przed i po, uruchom nowy raport programu Power View i dodaj do niego tabele SprzedażRzeczywista | KwotaSprzedaży, WymiarProdukt | Producent i AdresURL | AdresURLProducenta. Zwróć uwagę, że adresy URL są wyświetlane jako tekst statyczny.

Renderowanie adresu URL jako aktywnego hiperłącza wymaga kategoryzacji. Aby skategoryzować kolumnę, użyjesz dodatku Power Pivot.

  1. W dodatku Power Pivot otwórz pozycję URL.

  2. Zaznacz pozycję AdresURLProducenta.

  3. Kliknij pozycję Zaawansowane > Właściwości raportów > Kategoria danych: Bez kategorii.

  4. Kliknij strzałkę w dół.

  5. Zaznacz pozycję Adres URL sieci Web.

  6. W programie Excel kliknij pozycję Wstawianie > Power View.

  7. W polach programu Power View zaznacz tabele SprzedażRzeczywista | KwotaSprzedaży, WymiarProdukt | Producent i AdresURL | AdresURLProducenta. Tym razem adresy URL będą wyświetlane jako rzeczywiste hiperłącza.

Inne optymalizacje dla programu Power View to m.in. definiowanie domyślnego zestawu pól dla każdej tabeli i ustawianie właściwości określających, które wiersze zawierające powtarzające się dane są agregowane lub wyświetlane niezależnie. Zobacz tematy Konfigurowanie domyślnego zestawu pól dla raportów programu Power View i Konfigurowanie właściwości zachowania tabeli dla raportów programu Power View, aby uzyskać więcej informacji.

Tworzenie pól obliczeniowych

W drugim zadaniu (Eksplorowanie danych za pomocą tabeli przestawnej) na liście pól tabeli przestawnej zostało kliknięte pole KwotaSprzedaży. Kolumna KwotaSprzedaży to kolumna liczbowa, dlatego została automatycznie umieszczona w obszarze Wartości tabeli przestawnej. W ten sposób sumy kolumny KwotaSprzedaży były gotowe do obliczania kwot sprzedaży przy dowolnym zastosowanym filtrze. W tym przypadku najpierw bez filtrów, a następnie z filtrami RokKalendarzowy, NazwaPodkategoriiProduktu i NazwaMarki.

W rzeczywistości zostało utworzone niejawne pole obliczeniowe, ułatwiające analizowanie kwot sprzedaży z tabeli SprzedażRzeczywista pod kątem innych pól, takich jak kategoria produktu, region i daty. W programie Excel niejawne pola obliczeniowe są tworzone po przeciągnięciu pola do obszaru Wartości lub po kliknięciu pola liczbowego, jak to miało miejsce w przypadku pola KwotaSprzedaży. Niejawne pola obliczeniowe to formuły, w których są używane standardowe funkcje agregacji, takie jak SUMA, ILE.LICZB oraz ŚREDNIA, tworzone automatycznie.

Istnieją także inne typy pól obliczeniowych. W dodatku Power Pivot można tworzyć jawne pola obliczeniowe. W przeciwieństwie do niejawnych pól obliczeniowych, które mogą być używane tylko w tabeli przestawnej, w której zostały utworzone, jawne pola obliczeniowe mogą być używane w dowolnej tabeli przestawnej w skoroszycie i w dowolnym raporcie korzystającym z modelu danych jako źródła danych. W przypadku jawnych pól obliczeniowych utworzonych w dodatku Power Pivot można używać funkcji autosumowania, aby automatycznie tworzyć pola obliczeniowe przy użyciu standardowych funkcji agregacji, lub można tworzyć własne za pomocą formuły utworzonej przy użyciu języka DAX (Data Analysis Expressions).

Jak możesz sobie wyobrazić, tworzenie pól obliczeniowych jest ogromną i zaawansowaną pomocą w analizowaniu danych, zatem dowiedzmy się, jak je tworzyć.

Tworzenie pól obliczeniowych w dodatku Power Pivot jest łatwe, kiedy używa się funkcji Autosumowanie.

  1. W tabeli SprzedażRzeczywista kliknij kolumnę Zysk.

  2. Kliknij pozycję Obliczenia > Autosumowanie. Zauważ, że nowe pole obliczeniowe o nazwie Suma Zysk zostało automatycznie utworzone w komórce w obszarze obliczeń bezpośrednio poniżej kolumny Zysk.

  3. W programie Excel w arkuszu Arkusz1 na liście pól w tabeli SprzedażRzeczywista kliknij pozycję Suma Zysk.

To wszystko! Tyle wystarczy, aby utworzyć pole obliczeniowe przy użyciu standardowych funkcji agregacji w dodatku Power Pivot. Jak widzisz, w ciągu kilku minut zostało utworzone pole obliczeniowe Suma Zysk, które następnie zostało dodane do tabeli przestawnej, co ułatwia analizowanie zysków w zależności od zastosowanych filtrów. W tym przypadku do sumy zysków zastosowano filtry hierarchii Kategoria produktu i Daty.

A co zrobić, jeśli trzeba wykonać bardziej szczegółową analizę, taką jak liczba sprzedaży dla określonego kanału, produktu lub określonej kategorii? W tym celu należy utworzyć kolejne pole obliczeniowe, które zlicza liczbę wierszy, jeden dla każdej sprzedaży w tabeli SprzedażRzeczywista, w zależności od zastosowanych filtrów.

  1. W tabeli SprzedażRzeczywista kliknij kolumnę KluczSprzedaży.

  2. W grupie Obliczenia kliknij strzałkę w dół na pozycji Autosumowanie > Liczba.

  3. Zmień nazwę nowego pola obliczeniowego, klikając prawym przyciskiem myszy pozycję Liczba KluczSprzedaży w obszarze obliczeń, a następnie wybierając polecenie Zmień nazwę. Wpisz nazwę Liczba, a następnie naciśnij klawisz Enter.

  4. W programie Excel w arkuszu Arkusz1 na liście pól w tabeli SprzedażRzeczywista kliknij pozycję Liczba.

Zauważ, że do tabeli przestawnej została dodana nowa kolumna o nazwie Liczba, w której jest wyświetlana liczba sprzedaży w zależności od zastosowanych filtrów. Tak samo, jak w przypadku pola obliczeniowego Suma Zysk, do kolumny Liczba zastosowano filtry hierarchii Kategoria produktu i Daty.

Utwórzmy kolejne pole. Tym razem utworzysz pole obliczeniowe, które oblicza procent sprzedaży całkowitej dla określonego kontekstu lub filtru. Jednak w przeciwieństwie do poprzednich pól obliczeniowych utworzonych przy użyciu funkcji Autosumowanie, tym razem wprowadzisz formułę ręcznie.

  1. W tabeli SprzedażRzeczywista w obszarze obliczeń kliknij pustą komórkę. Wskazówka: warto zacząć umieszczać pola obliczeniowe w lewej górnej komórce. Dzięki temu łatwiej je znaleźć. Możesz przemieszczać każde pole obliczeniowe w obszarze obliczeń.

  2. Na pasku formuły wpisz następującą formułę (użyj funkcji IntelliSense, aby ją utworzyć): Procent wszystkich produktów:=[Liczba]/CALCULATE([Liczba]; ALL(WymiarProdukt))

  3. Naciśnij klawisz ENTER, aby zaakceptować formułę.

  4. W programie Excel w arkuszu Arkusz1 na liście pól w tabeli SprzedażRzeczywista kliknij pozycję Procent Wszystkie produkty.

  5. W tabeli przestawnej zaznacz wiele kolumn Procent Wszystkie produkty.

  6. Na karcie Narzędzia główne kliknij pozycję Liczba > Procent. Sformatuj każdą nową kolumnę przy użyciu dwóch miejsc dziesiętnych.

To nowe pole obliczeniowe oblicza procent sprzedaży całkowitej dla danego kontekstu filtru. W tym przypadku nasz kontekst filtru to wciąż hierarchie Kategoria produktu i Daty. Możesz na przykład zauważyć, że udział procentowy komputerów w całkowitej sprzedaży produktów wzrósł na przestrzeni lat.

Jeśli potrafisz tworzyć formuły programu Excel, tworzenie formuł na potrzeby kolumn obliczeniowych i pól obliczeniowych będzie dla Ciebie dość proste. Niezależnie od tego, czy znasz formuły programu Excel, czy nie, świetnym sposobem na naukę podstaw formuł języka DAX jest zapoznanie się z lekcjami w samouczku Szybki start: nauka podstaw języka DAX w 30 minut.

Zapisywanie wyników pracy

Zapisz skoroszyt, aby można go było używać na potrzeby innych samouczków lub dalszego zgłębiania tego tematu.

Następne kroki

Mimo że można łatwo importować dane z programu Excel, często można to zrobić szybciej i wydajniej za pomocą dodatku Power Pivot. Importowane dane można filtrować, wykluczając niepotrzebne kolumny. Można także określić, czy do pobierania danych ma być używany konstruktor zapytań, czy polecenie zapytania. W następnym kroku zostaną przedstawione następujące alternatywne metody: Pobieranie danych ze strumieniowego źródła danych w dodatku Power Pivot oraz Importowanie danych z usług Analysis Services lub dodatku Power Pivot.

Funkcja raportowania programu Power View została zaprojektowana do działania z modelami danych podobnymi do utworzonego podczas przerabiania tego samouczka. Więcej informacji dotyczących zaawansowanych wizualizacji danych dostępnych w programie Excel za pośrednictwem programu Power View znajduje się w następujących tematach: Uruchamianie programu Power View w programie Excel 2013 i Power View: eksplorowanie, wizualizowanie i prezentowanie danych.

Spróbuj ulepszyć swój model danych, aby tworzyć lepsze raporty programu Power View. W tym celu zobacz Samouczek: optymalizowanie modelu danych pod kątem raportów programu Power View

Rozwijaj swoje umiejętności
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×