Obliczanie wartości w tabeli przestawnej

Aby obliczyć wartości w tabeli przestawnej, można używać dowolnych lub wszystkich z następujących metod obliczeniowych:

• Funkcje podsumowania w polach wartości    Dane w obszarach wartości to podsumowanie bazowych danych źródłowych w tabeli przestawnej. Na przykład następujące dane źródłowe:

• Umożliwiają utworzenie następujących tabel przestawnych i wykresów przestawnych. Po utworzeniu wykresu przestawnego na podstawie danych z tabeli przestawnej wartości na tym wykresie przestawnym odzwierciedlają obliczenia, które zawiera skojarzony raport w formie tabeli przestawnej.

• W tabeli przestawnej pole kolumny Miesiąc dostarcza elementy Marzec i Kwiecień. Pole wiersza Region zawiera elementy Północ, Południe, Wschód i Zachód. Wartość w miejscu przecięcia kolumny Kwiecień i wiersza Północ to przychód ze sprzedaży całkowitej, zsumowany na podstawie rekordów danych źródłowych mających wartości Miesiąc równe Kwiecień i wartości Region równe Północ.

• Na wykresie przestawnym pole Region może być polem kategorii z kategoriami Północ, Południe, Wschód i Zachód. Pole Miesiąc może być polem serii z seriami Marzec, Kwiecień i Maj w legendzie. Pole Wartości o nazwie Suma sprzedaży może zawierać znaczniki danych reprezentujące całkowity przychód w każdym z regionów dla każdego miesiąca. Jeden znacznik danych może na przykład reprezentować swoją pozycją na osi pionowej (wartości) całkowitą sprzedaż dla miesiąca Kwiecień w regionie Północ.

• Dla wszystkich typów danych źródłowych z wyjątkiem danych źródłowych Online Analytical Processing (OLAP) są dostępne następujące funkcje podsumowujące, umożliwiające obliczanie pól wartości.

  Funkcja	Podsumowanie
  Suma	Suma wartości. Jest to domyślna funkcja dla danych liczbowych.
  Licznik	Liczba wartości danych. Funkcja podsumowująca Licznik działa tak samo jak funkcja ILE.NIEPUSTYCH. Funkcja Licznik jest domyślnie używana w przypadku danych nieliczbowych.
  Średnia	Średnia z wartości.
  Maksimum	Największa wartość.
  Minimum	Najmniejsza wartość.
  Produkt	Iloczyn wartości.
  Licznik num.	Liczba wartości danych będących liczbami. Funkcja podsumowująca Licznik num. działa tak samo jak funkcja ILE.LICZB.
  OdchStd	Szacowana wartość odchylenia standardowego populacji, gdzie próbka to podzbiór całej populacji.
  OdchStdc	Odchylenie standardowe dla populacji, którą stanowią wszystkie podsumowywane dane.
  Wariancja	Szacowana wariancja populacji, gdzie próbka to podzbiór całej populacji.
  Wariancja populacji	Wariancja dla populacji, którą stanowią wszystkie podsumowywane dane.

• Obliczenia niestandardowe    Metoda uwzględniająca obliczanie niestandardowe pokazuje wartości na podstawie pozostałych elementów lub komórek w obszarze danych. Można na przykład wyświetlić wartości w polu danych Suma sprzedaży jako procent sprzedaży w miesiącu Marzec albo jako sumę bieżącą elementów w polu Miesiąc.

  W obliczeniach niestandardowych wykonywanych w polach wartości można używać następujących funkcji.

  Funkcja	Wynik
  Bez obliczeń	Wyświetla wartość wprowadzoną w polu.
  % sumy końcowej	Wyświetla wartości jako procent sumy końcowej wszystkich wartości lub punktów danych w raporcie.
  % sumy kolumny	Wyświetla wszystkie wartości w poszczególnych kolumnach lub seriach jako procent sumy danej kolumny lub serii.
  % sumy wiersza	Wyświetla wartości w poszczególnych wierszach lub kategoriach jako procent sumy danego wiersza lub kategorii.
  % z	Wyświetla wartości jako procent wartości Element podstawowy w polu Pole podstawowe.
  % sumy wiersza nadrzędnego	Oblicza wartości w następujący sposób: (wartość dla elementu) / (wartość dla elementu nadrzędnego w wierszach)
  % sumy kolumny nadrzędnej	Oblicza wartości w następujący sposób: (wartość dla elementu) / (wartość dla elementu nadrzędnego w kolumnach)
  % sumy elementu nadrzędnego	Oblicza wartości w następujący sposób: (wartość dla elementu) / (wartość dla elementu nadrzędnego w wybranym polu Pole podstawowe)
  Różnica	Wyświetla wynik odejmowania wartości od wartości Element podstawowy w polu Pole podstawowe.
  % różnicy	Wyświetla procentowo wyrażony wynik odejmowania wartości od wartości Element podstawowy w polu Pole podstawowe.
  Suma bieżąca w	Wyświetla wartość dla kolejnych elementów w polu Pole podstawowe jako sumę bieżącą.
  % sumy bieżącej w	Oblicza wartość procentową dla kolejnych elementów w polu Pole podstawowe wyświetlonych jako suma bieżąca.
  Porządkuj od najmniejszych do największych	Wyświetla klasyfikację wybranych wartości w określonym polu, umieszczając najmniejszy element na pozycji 1, a kolejne większe wartości na dalszych pozycjach.
  Porządkuj od największych do najmniejszych	Wyświetla klasyfikację wybranych wartości w określonym polu, umieszczając największy element na pozycji 1, a kolejne mniejsze wartości na dalszych pozycjach.
  Indeks	Oblicza wartości w następujący sposób: ((wartość w komórce) x (suma końcowa sum końcowych)) / ((suma końcowa wiersza) x (suma końcowa kolumny))

• Formuły    Jeśli funkcje podsumowania i obliczenia niestandardowe nie pozwalają na uzyskanie odpowiednich wyników, możesz utworzyć własne formuły w polach obliczeniowych i elementach obliczeniowych. Możesz na przykład dodać element obliczeniowy zawierający formułę do obliczania prowizji od sprzedaży, która może być inna dla każdego regionu. Spowoduje to automatyczne uwzględnienie prowizji w sumach częściowych i sumach końcowych raportu.

Obliczenia i opcje dostępne w raporcie są zależne od tego, czy dane źródłowe pochodzą z bazy danych OLAP, czy z innego źródła.

• Obliczenia oparte na danych źródłowych OLAP    W przypadku tabel przestawnych utworzonych na podstawie modułów OLAP wartości podsumowań są obliczane wstępnie na serwerze OLAP, zanim program Excel wyświetli wyniki. Nie można zmienić sposobu obliczania tych wstępnie obliczanych wartości w tabeli przestawnej. Nie można na przykład zmienić funkcji podsumowania służącej do obliczenia pól danych lub sum częściowych ani dodać pól obliczeniowych czy elementów obliczeniowych.

  Ponadto jeśli serwer OLAP zapewnia pola obliczeniowe, znane jako elementy obliczeniowe, te pola będą widoczne na liście pól tabeli przestawnej. Widoczne będą także wszystkie pola obliczeniowe i elementy obliczeniowe utworzone za pomocą makr napisanych w języku Visual Basic for Applications (VBA) i przechowywanych w danym skoroszycie, ale nie będzie można zmienić tych pól ani elementów. Jeśli będziesz potrzebować dodatkowych typów obliczeń, skontaktuj się z administratorem bazy danych OLAP.

  W przypadku źródła danych OLAP podczas obliczania sum częściowych i sum końcowych można uwzględnić lub wykluczyć wartości ukrytych elementów.

• Obliczenia oparte na danych źródłowych innych niż OLAP    W tabelach przestawnych opartych na innych typach danych zewnętrznych lub na danych arkuszy program Excel używa funkcji podsumowania: funkcji Suma do obliczania pól wartości zawierających dane liczbowe i funkcji Licznik do obliczania pól danych zawierających tekst. W celu dokładniejszego analizowania i dostosowywania danych można wybrać inną funkcję podsumowania (na przykład Średnia, Maksimum albo Minimum). Można też utworzyć własne formuły używające elementów raportu lub innych danych arkusza, tworząc pole obliczeniowe lub element obliczeniowy w obrębie pola.

Formuły można tworzyć tylko w raportach, które są oparte na danych źródłowych innych niż OLAP. Formuł nie można używać w raportach opartych na bazie danych OLAP. Korzystając z formuł w tabelach przestawnych, należy pamiętać o następujących zasadach dotyczących składni formuły i mechanizmach działania formuły:

• Elementy formuły tabeli przestawnej    W formułach utworzonych dla pól obliczeniowych i elementów obliczeniowych można używać operatorów i wyrażeń, tak jak w innych formułach arkusza. Możliwe jest używanie stałych i odwoływanie się do danych z raportu, ale nie można używać odwołań do komórek ani zdefiniowanych nazw. Nie można używać funkcji arkusza wymagających jako argumentów odwołań do komórek lub zdefiniowanych nazw ani funkcji tablicowych.

• Nazwy pól i elementów    W programie Excel nazwy pól i elementów służą do identyfikowania tych elementów raportów w formułach. W poniższym przykładzie dane w zakresie C3:C9 mają nazwę pola Nabiał. Element obliczeniowy w polu Typ, szacujący sprzedaż nowego produktu na podstawie sprzedaży nabiału, mógłby korzystać z formuły: =Nabiał*115%.

  

  Uwaga: Na wykresie przestawnym nazwy pól są wyświetlane na liście pól, a nazwy elementów można zobaczyć na każdej liście rozwijanej pola. Nie należy mylić tych nazw z nazwami widocznymi na etykietkach wykresu, odzwierciedlającymi nazwy serii i punktów danych.

• Formuły operują na sumach, a nie na poszczególnych rekordach    Formuły pól obliczeniowych operują na sumie danych bazowych dowolnych pól w formule. Na przykład formuła pola obliczeniowego =Sprzedaż*1,2 powoduje pomnożenie sumy sprzedaży dla każdego z typów i regionów przez liczbę 1,2 — a nie pomnożenie poszczególnych wartości sprzedaży przez liczbę 1,2 i następnie zsumowanie pomnożonych wielkości.

  Formuły elementów obliczeniowych operują na poszczególnych rekordach. Na przykład formuła elementu obliczeniowego =Nabiał*115% powoduje pomnożenie każdej sprzedaży z osobna przez 115%, po czym pomnożone kwoty są łącznie podsumowywane w obszarze Wartości.

• Spacje, liczby i symbole w nazwach    W nazwie zawierającej więcej niż jedno pole pola mogą mieć dowolną kolejność. W powyższym przykładzie komórki C6:D6 mogą mieć kolejność 'Kwiecień Północ' albo 'Północ Kwiecień'. Nazwy składające się z więcej niż jednego wyrazu lub zawierające liczby albo symbole należy ujmować w cudzysłów pojedynczy.

• Sumy    Formuły nie mogą odwoływać się do sum (takich jak Marzec Suma, Kwiecień Suma czy Suma końcowa w przykładzie).

• Nazwy pól w odwołaniach elementów    W odwołaniu do elementu możesz dodać nazwę pola. Nazwę elementu trzeba ująć w nawiasy kwadratowe— na przykład Region[Północ]. Tego formatu należy używać, aby uniknąć błędów #NAZWA? w przypadkach, gdy dwa elementy w dwóch różnych polach w raporcie mają tę samą nazwę. Jeśli na przykład w raporcie w polu Typ jest element o nazwie Mięso, a inny element o nazwie Mięso jest w polu Kategoria, to można uniknąć błędów #NAZWA?, odwołując się do elementów w następujący sposób: Typ[Mięso] i Kategoria[Mięso].

• Odwoływanie się do elementów za pomocą pozycji    Można odwoływać się do elementu za pomocą jego pozycji w raporcie, określonej stosownie do bieżącego wyświetlania i sortowania. Typ[1] to Nabiał, a Typ[2] to Owoce morza. W przypadku takiego sposobu odwoływania się do elementu może on się zmienić po każdej zmianie pozycji elementów, a także po wyświetleniu lub ukryciu elementów. Ukryte elementy nie są zliczane w tym indeksie.

  W celu odwoływania się do elementów można używać pozycji względnych. Pozycje są określane względem elementu obliczeniowego zawierającego formułę. Gdy bieżącym regionem jest Południe, wówczas Region[-1] to Północ; gdy regionem bieżącym jest Północ, wówczas Region[+1] to Południe. Na przykład element obliczeniowy może używać formuły =Region[-1]*3%. Jeśli podana pozycja znajduje się przed pierwszym lub za ostatnim elementem w polu, formuła zwróci wartość błędu #ADR! .

Aby używać formuł na wykresie przestawnym, należy utworzyć formuły w skojarzonej tabeli przestawnej, gdzie można zobaczyć poszczególne wartości składające się na dane, a następnie wyświetlić graficznie wyniki w formie wykresu przestawnego.

Na przykład poniższy wykres przestawny obrazuje sprzedaż każdego ze sprzedawców w rozbiciu na regiony:

Aby zobaczyć, jak będzie wyglądać sprzedaż przy wzroście o 10 procent, można utworzyć w skojarzonej tabeli przestawnej pole obliczeniowe, które używa formuły, takiej jak =Sprzedaż*110%.

Wynik zostanie natychmiast przedstawiony na wykresie przestawnym, jak pokazano na poniższym wykresie:

Aby zobaczyć osobny znacznik danych sprzedaży w regionie Północ minus ośmioprocentowe koszty transportu, można utworzyć element obliczeniowy w polu Region za pomocą formuły, takiej jak =Północ-(Północ*8%).

Wykres będzie wówczas wyglądać następująco:

Jednakże element obliczeniowy utworzony w polu Sprzedawca zostanie wyświetlony jako seria reprezentowana w legendzie, a także na wykresie jako punkt danych w każdej kategorii.