Udostępnij za pośrednictwem

Tworzenie miary w tabeli przestawnej lub na wykresie przestawnym

  • Artykuł

Miara to obliczenie tworzone w celu zmierzenia wyniku lub rezultatu względem innych czynników związanych z analizą, takich jak czas, region geograficzny, organizacja lub cechy produktu. Istnieje kilka metod tworzenia miar, ale ich efektywność jest różna w zależności od planowanego użycia miary. Podczas tworzenia skoroszytu, który ma pełnić rolę modelu danych w aplikacji do raportowania, należy pamiętać, aby tworzyć wyłącznie miary jawne, tak jak opisano w poniższych sekcjach.

Tworzenie miar przeznaczonych do użycia w modelach danych

Miar tworzonych w skoroszycie programu PowerPivot można używać w raportach tworzonych w programie Power View i innych aplikacjach do raportowania. Te miary mogą współistnieć z innymi miarami i obliczeniami tworzonymi bezpośrednio w raporcie. Aby miara mogła być widoczna w raporcie jako wstępnie zdefiniowane obliczenie, musi zostać utworzona w skoroszycie programu PowerPivot jako miara jawna. Miara jawna to miara tworzona ręcznie. Jej przeciwieństwem są miary niejawne generowane przez program Excel podczas dodawania pól do obszaru Wartości tabeli przestawnej.

Tworzenie miar przeznaczonych do użycia w programie Excel

Aby utworzyć miarę przeznaczoną do użycia w programie Excel, należy najpierw dodać do skoroszytu programu PowerPivot tabelę przestawną lub wykres przestawny. Następnie można tworzyć miary, używając jednej z następujących metod:

  • Tworzenie miary niejawnej przez przeciągnięcie pola z okna Lista pól programu PowerPivot do obszaru Wartości. Przeciągnięcie pola liczbowego spowoduje, że miara niejawna będzie obliczana z użyciem agregacji SUM. Przeciągnięcie pola tekstowego spowoduje, że miara będzie obliczana z użyciem agregacji COUNT. Można edytować miarę niejawną w celu zmiany obliczenia na inną agregację, taką jak MIN, MAX lub DISTINCTCOUNT.

    [!UWAGA]

    Tworzenie miar niejawnych jest łatwe, ale podlegają one większej liczbie ograniczeń niż miary jawne. Miar niejawnych nie można przenosić, używać w innych tabelach przestawnych i na innych wykresach w skoroszycie, a także nie można zmieniać ich nazw. Co więcej miary niejawne są oparte na istniejących polach, więc usunięcie takiego pola powoduje także usunięcie powiązanej z nim miary niejawnej. I wreszcie w miarach niejawnych można używać tylko formatu danych wbudowanego w agregację; miary niejawne nie obsługują szerokiego zakresu formatów danych dostępnych do użycia w miarach jawnych.

  • Tworzenie miary jawnej ręcznie przy użyciu polecenia Nowa miara na Wstążce programu PowerPivot.

  • Tworzenie miary jawnej ręcznie w oknie programu PowerPivot (w sekcji Obszar obliczeń) przez wpisanie nazwy miary i formuły w obszarze formuły komórki.

Po dodaniu miary formuła jest obliczana dla każdej komórki w obszarze Wartości tabeli przestawnej. Wynik jest tworzony dla poszczególnych kombinacji nagłówków wierszy i kolumn, więc wynik miary może być inny w każdej komórce.

Przykład: tworzenie miary jawnej, w której jest używana prosta agregacja

W tym przykładzie są używane dane dotyczące rowerów z bazy danych AdventureWorks. Aby uzyskać informacje dotyczące pobierania przykładowego skoroszytu, zobacz temat Pobieranie przykładowych danych dla programu PowerPivot. Aby uzyskać więcej informacji dotyczących formuł, zobacz temat Tworzenie formuł na potrzeby obliczeń.

W tym przykładzie pokazano dwie metody tworzenia miary jawnej. W pierwszej metodzie miara zostanie utworzona w obszarze obliczeń w oknie programu PowerPivot, gdzie są widoczne wszystkie miary zdefiniowane w modelu. W drugiej metodzie po dodaniu tabeli przestawnej lub wykresu przestawnego do skoroszytu programu PowerPivot w celu dodania miary zostanie użyte okno dialogowe Ustawienia miary. Formuła miary definiuje sumę, średnią lub inne obliczenie używające kolumn i tabel w skoroszycie programu PowerPivot.

  1. W oknie programu PowerPivot kliknij kartę Narzędzia główne i w grupie Widoki kliknij przycisk Obszar obliczeń.

  2. W tabeli FactResellerSales kliknij dowolną komórkę w obszarze obliczeń.

  3. Na pasku formuły u góry skoroszytu wprowadź formułę w formacie <nazwa_miary>:<formuła>:

    Projected Sales:=SUM('FactResellerSales'[SalesAmount])*1.06

  4. Kliknij przycisk OK, aby zaakceptować formułę.

  5. W oknie programu PowerPivot kliknij kartę Narzędzia główne i w grupie Raporty kliknij przycisk Tabela przestawna.

    W oknie dialogowym Tworzenie tabeli przestawnej sprawdź, czy jest wybrana opcja Nowy arkusz i kliknij przycisk OK.

    Program PowerPivot utworzy pustą tabelę przestawną w nowym arkuszu programu Excel i wyświetli okno Lista pól programu PowerPivot z prawej strony skoroszytu.

  6. Rozwiń tabelę FactResellerSales, aby wyświetlić właśnie utworzoną miarę. Jeśli przed utworzeniem miary w skoroszycie znajdowała się już tabela przestawna, musisz kliknąć przycisk Odśwież u góry okna Lista pól programu PowerPivot, aby odświeżyć pola.

  7. W oknie programu Excel na karcie PowerPivot w grupie Miary kliknij przycisk Nowa miara.

  8. W oknie dialogowym Ustawienia miary w polu Nazwa tabeli kliknij strzałkę w dół i wybierz z listy rozwijanej pozycję FactResellerSales.

    Wybór tabeli określa, gdzie będzie przechowywana definicja miary. Nie jest wymagane, aby miara była przechowywana z tabelą, do której się odwołuje.

  9. W polu Nazwa miary (wszystkie tabele przestawne) wpisz ciąg Łączna ilość.

  10. Nazwa miary musi być unikatowa w obrębie skoroszytu i nie może być taka sama jak nazwa dowolnej kolumny w skoroszycie.

  11. W polu tekstowym Formuła umieść kursor za znakiem równości (=), a następnie wprowadź następującą formułę:

    SUM(FactResellerSales[OrderQuantity])

  12. Kliknij przycisk OK.

Obie utworzone miary zostały zapisane z tabelą danych źródłowych, ale można ich używać w dowolnej tabeli przestawnej lub na dowolnym wykresie przestawnym. Te miary są wyświetlane w oknie Lista pól programu PowerPivot i są dostępne dla wszystkich użytkowników skoroszytu.

Przykład: tworzenie miary jawnej, w której jest używana agregacja niestandardowa

W tym przykładzie zostanie utworzona agregacja niestandardowa, w której będzie używana jedna z nowych funkcji agregacji języka DAX (SUMX) oraz funkcja ALL, która w tym przypadku będzie zwracać wszystkie wartości z kolumny niezależnie od jej kontekstu. W tym przykładzie będą używane następujące kolumny z przykładowego skoroszytu języka DAX:

  • DateTime[CalendarYear]

  • ProductCategory[ProductCategoryName]

  • ResellerSales_USD[SalesAmount_USD]

W tym przykładzie będzie używana tabela przestawna, w której kolumna CalendarYear będzie pełnić rolę etykiety wierszy, a kolumna ProductCategoryName będzie pełnić rolę etykiety kolumn. Z kolei kolumna SalesAmount_USD będzie używana w formule miary. W tym przykładzie pokazano odpowiedź na pytanie: jaki procentowy udział w łącznej sprzedaży w latach 2005–2008 ma sprzedaż w każdym roku i sprzedaż w każdej kategorii produktów? Pozwoli to na przykład zobaczyć, jaki procent łącznej sprzedaży stanowi sprzedaż rowerów w 2007 roku. Aby udzielić odpowiedzi na to pytanie, należy użyć następującej formuły miary:

=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

Formuła jest tworzona w następujący sposób:

  1. Licznik (SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])) to suma wartości w kolumnie ResellerSales_USD[SalesAmount_USD] dla bieżącej komórki w tabeli przestawnej. Ze względu na kontekst kolumn CalendarYeari ProductCategoryName ta wartość będzie inna dla każdej kombinacji roku i kategorii produktów. Na przykład łączna liczba rowerów sprzedanych w 2003 roku będzie inna niż łączna liczba akcesoriów sprzedanych w 2008 roku.

  2. Aby obliczyć mianownik, należy najpierw określić tabelę (ResellerSales_USD) i użyć funkcji ALL w celu usunięcia całego kontekstu z tabeli. Dzięki temu wartość będzie taka sama dla każdej kombinacji roku i kategorii produktów — mianownik zawsze będzie równy łącznej sprzedaży w latach 2005–2008.

  3. Następnie zostanie użyta funkcja SUMX w celu zsumowania wartości w kolumnie ResellerSales_USD[SalesAmount_USD]. Innymi słowy, zostanie obliczona suma wartości sprzedaży z kolumny ResellerSales_USD[SalesAmount_USD] dla wszystkich odsprzedawców.

[!UWAGA]

W systemach Windows Vista i Windows 7 funkcje okna programu PowerPivot są dostępne na Wstążce, którą omówiono w tym temacie. W systemie Windows XP te funkcje są dostępne za pośrednictwem zestawu menu. Użytkownicy systemu Windows XP, którzy chcą zobaczyć, jak polecenia menu są powiązane z poleceniami Wstążki, powinni zapoznać się z tematem Interfejs użytkownika programu PowerPivot w systemie Windows XP.

Aby utworzyć miarę, w której jest używana agregacja niestandardowa

  1. W oknie programu PowerPivot kliknij kartę Narzędzia główne i w grupie Raporty kliknij przycisk Tabela przestawna.

  2. W oknie dialogowym Tworzenie tabeli przestawnej sprawdź, czy jest wybrana opcja Nowy arkusz i kliknij przycisk OK.

    Program PowerPivot utworzy pustą tabelę przestawną w nowym arkuszu programu Excel i wyświetli okno Lista pól programu PowerPivot z prawej strony skoroszytu.

  3. W oknie programu Excel użyj okna Lista pól programu PowerPivot, aby dodać kolumny do tabeli przestawnej:

    1. Znajdź tabelę DateTime i przeciągnij kolumnę CalendarYear do obszaru Etykiety wierszy tabeli przestawnej.

    2. Znajdź tabelę ProductCategory i przeciągnij kolumnę ProductCategory do obszaru Etykiety wierszy tabeli przestawnej.

  4. W oknie programu Excel na karcie PowerPivot w grupie Miary kliknij przycisk Nowa miara.

  5. W oknie dialogowym Ustawienia miary w polu Nazwa tabeli kliknij strzałkę w dół i wybierz z listy rozwijanej pozycję ResellerSales_USD.

    Wybór tabeli określa, gdzie będzie przechowywana definicja miary. Nie jest wymagane, aby miara była przechowywana z tabelą, do której się odwołuje.

  6. W polu Nazwa miary (wszystkie tabele przestawne) wpisz ciąg StosunekDoSprzedWszOdsprz.

    Ta nazwa jest używana jako identyfikator miary, przez co musi być unikatowa w obrębie skoroszytu i nie można jej zmienić.

  7. W polu Nazwa niestandardowa (ta tabela przestawna) wpisz ciąg Stosunek do sprzedaży wszystkich odsprzedawców.

    Ta nazwa jest używana tylko w bieżącej tabeli przestawnej na potrzeby wyświetlania. Na przykład można ponownie użyć miary StosunekDoSprzedWszOdsprz w innych tabelach przestawnych, ale należy nadać jej inną nazwę lub użyć innego języka.

  8. W polu tekstowym Formuła umieść kursor za znakiem równości (=).

  9. Wpisz funkcję SUMX, a następnie nawias.

    =SUMX(

    Podczas wpisywania etykietka narzędzia poniżej pola tekstowego Formuła wskazuje, że funkcja SUMX wymaga dwóch argumentów: pierwszym argumentem jest tabela lub wyrażenie zwracające tabelę, a drugim jest wyrażenie określające liczby, które mogą zostać zsumowane.

    Wpisz ciąg Res, a następnie wybierz z listy pozycję ResellerSales_USD i naciśnij klawisz TAB.

    Nazwa kolumny zostanie wstawiona do formuły w następujący sposób:

    =SUMX(ResellerSales_USD

  10. Wpisz przecinek.

    Etykietka narzędzia wskaże, że następny wymagany argument to wyrażenie. Wyrażenie może być wartością, odwołaniem do kolumny lub pewną kombinacją tych elementów. Można na przykład utworzyć wyrażenie sumujące dwie pozostałe kolumny. W tym przykładzie należy podać nazwę kolumny zawierającej kwotę sprzedaży dla każdego odsprzedawcy.

  11. Wpisz kilka pierwszych liter nazwy tabeli zawierającej kolumnę, którą chcesz uwzględnić. W tym przykładzie wpisz litery Res, a następnie wybierz z listy kolumnę ResellerSales_USD[SalesAmount_USD].

  12. Naciśnij klawisz TAB, aby wstawić nazwę kolumny do formuły, i dodaj nawias zamykający, tak jak pokazano poniżej:

    =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])

  13. Wpisz ukośnik, a następnie wpisz lub skopiuj i wklej następujący kod do okna dialogowego Ustawienia miary:

    SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

    Należy zwrócić uwagę na zagnieżdżenie funkcji ALL w funkcji SUMX. Cała formuła powinna wyglądać następująco:

    =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

  14. Kliknij przycisk Sprawdź formułę.

    Formuła zostanie sprawdzona pod kątem błędów składni i odwołań. Usuń błędy, które mogły wystąpić, takie jak brak nawiasu czy przecinka.

  15. Kliknij przycisk OK.

    Miara wypełni teraz tabelę przestawną wartościami dla wszystkich kombinacji roku kalendarzowego i kategorii produktów.

  16. Sformatuj tabelę:

    1. Zaznacz dane w tabeli przestawnej, w tym wiersz Suma końcowa.

    2. Na karcie Narzędzia główne w grupie Liczba kliknij przycisk procentu (%), a następnie kliknij dwa razy przycisk zwiększenia liczby miejsc dziesiętnych (<- .0 .00).

    Gotowa tabela powinna wyglądać tak jak poniżej. Teraz można zobaczyć procent łącznej sprzedaży dla każdej kombinacji produktu i roku. Na przykład sprzedaż rowerów w 2007 roku stanowiła 31,71% łącznej sprzedaży w latach 2005–2008.

Sprzedaż wszystkich odsprzedawców

Etykiety kolumn

 

 

 

 

Etykiety wierszy

Accessories

Bikes

Clothing

Components

Suma końcowa

2005

0.02%

9.10%

0.04%

0.75%

9.91%

2006

0.11%

24.71%

0.60%

4.48%

29.90%

2007

0.36%

31.71%

1.07%

6.79%

39.93%

2008

0.20%

16.95%

0.48%

2.63%

20.26%

Suma końcowa

0.70%

82.47%

2.18%

14.65%

100.00%

Zobacz także

Odwołanie

Okno dialogowe Kluczowy wskaźnik wydajności

Koncepcje

Miary w programie PowerPivot

Okno programu PowerPivot: obszar obliczeń

Inne zasoby

Omówienie raportów, wykresów i tabel przestawnych