Obliczenia i formuły w raportach tabeli przestawnej i wykresu przestawnego — informacje

Raporty wykresu przestawnego (raport w formie wykresu przestawnego: Wykres umożliwiający interakcyjną analizę danych, podobnie jak raport w formie tabeli przestawnej. Można zmieniać widoki danych, wyświetlać różne poziomy szczegółów lub zmieniać układ danych, przeciągając pola i pokazując lub ukrywając elementy w polach.) i tabeli przestawnej zapewniają wiele typów obliczeń. W polach danych (pole danych: Pole źródłowej listy, tabeli lub bazy danych zawierające dane, które są podsumowywane w raporcie w formie tabeli przestawnej lub wykresu przestawnego. Pole danych zwykle zawiera dane numeryczne, takie jak dane statystyczne czy dane dotyczące sprzedaży.) używane są funkcje podsumowania (funkcja podsumowania: Typ obliczenia, które podsumowuje dane źródłowe w raporcie w formie tabeli przestawnej lub tabeli konsolidacji albo pojawia się, gdy użytkownik wstawi automatyczne sumy częściowe do listy lub bazy danych. Funkcjami podsumowania są na przykład funkcje Suma, Policz i Średnia.) w celu łączenia wartości z bazowego źródła danych (dane źródłowe: Lista lub tabela, która jest używana do utworzenia raportu w formie tabeli przestawnej lub wykresu przestawnego. Dane źródłowe mogą pochodzić z listy lub zakresu programu Excel, zewnętrznej bazy danych lub modułu albo innego raportu w formie tabeli przestawnej.). Można też używać obliczeń niestandardowych (obliczenia niestandardowe: Metoda sumowania wartości w obszarze danych raportu w formie tabeli przestawnej przy użyciu wartości z innych komórek w obszarze danych. Aby utworzyć obliczenia niestandardowe, należy dla pola danych użyć listy Pokaż dane jako w oknie dialogowym Pole tabeli przestawnej.) w celu porównywania wartości danych lub dodawać własne formuły używające elementów raportu lub innych danych skoroszytu.

PokażJak raporty tabeli przestawnej i wykresu przestawnego sumują dane

Przykład danych źródłowych tabeli przestawnej

Dane źródłowe

Wartości w obszarze danych (obszar danych: Część raportu w formie tabeli przestawnej zawierająca dane podsumowania. Wartości w każdej komórce obszaru danych reprezentują podsumowanie danych z rekordów lub wierszy źródłowych.) to podsumowanie bazowych danych źródłowych w raporcie.

Przykład raportu tabeli przestawnej

Raport tabeli przestawnej utworzony na podstawie powyższych danych źródłowych

Pole kolumny (pole kolumny: Pole, które jest przypisane do orientacji kolumny w raporcie w formie tabeli przestawnej. Elementy skojarzone z polem kolumny są wyświetlane jako etykiety kolumn.), Miesiąc, dostarcza elementy (element: Podkategoria pola w raportach w formie tabeli przestawnej i wykresu przestawnego. Na przykład pole „Miesiąc” mogłoby mieć takie elementy, jak „Styczeń”, „Luty” itd.) Marzec i Kwiecień. Pole wiersza (pole wiersza: Pole, które jest przypisane do orientacji wiersza w raporcie w formie tabeli przestawnej. Elementy skojarzone z polem wiersza są wyświetlane jako etykiety wierszy.), Region, dostarcza elementów Północ, Południe, Wschód i Zachód. Wartość na przecięciu kolumny Kwiecień i wiersza Północ to zysk 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.

Przykład raportu wykresu przestawnego

Raport wykresu przestawnego utworzony na podstawie przykładowego raportu tabeli przestawnej

W raporcie wykresu przestawnego pole Region może być polem kategorii (pole kategorii wykresu przestawnego: Pole, które jest przypisane do osi kategorii w raporcie w formie wykresu przestawnego. Na wykresie kategorie zazwyczaj pojawiają się na osi x (poziomej).) pokazującym Północ, Południe, Wschód i Zachód jako kategorie. Pole Miesiąc może być polem serii (pole serii wykresu przestawnego: Pole, które jest przypisane do osi serii w raporcie w formie wykresu przestawnego. Na wykresie serie są reprezentowane w legendzie.) pokazującym elementy Marzec, Kwiecień i Maj jako serie reprezentowane w legendzie. Pole danych o nazwie Suma sprzedaży może zawierać znaczniki danych (znacznik danych: Słupek, obszar, punkt, wycinek koła lub inny symbol na wykresie, który reprezentuje pojedynczy punkt danych lub wartość pochodzącą z komórki arkusza kalkulacyjnego. Powiązane znaczniki danych na wykresie tworzą serię danych.) reprezentujące całkowity zysk w każdym z regionów w każdym z miesięcy. Na przykład jeden znacznik danych może reprezentować swoją pozycją na osi wartości (oś: Linia ograniczająca obszar kreślenia wykresu, na której zaznaczone są jednostki miary. Oś y jest zazwyczaj osią pionową i zawiera dane. Oś x jest zazwyczaj osią poziomą i zawiera kategorie.) całkowitą sprzedaż w kwietniu w regionie Północ.

Wartości i obliczenia w raporcie wykresu przestawnego odzwierciedlają obliczenia i wartości skojarzonego raportu tabeli przestawnej (skojarzony raport w formie tabeli przestawnej: Raport w formie tabeli przestawnej, który jest źródłem danych dla raportu w formie wykresu przestawnego. Jest on tworzony automatycznie, gdy użytkownik tworzy nowy raport w formie wykresu przestawnego. Jeśli układ któregokolwiek raportu zostaje zmieniony, układ drugiego również ulega zmianie.) i vice versa.

Obliczenia niestandardowe       Obliczenie niestandardowe pokazuje wartości na podstawie pozostałych elementów lub komórek w obszarze danych. Na przykład można wyświetlić wartości w polu Suma sprzedaży jako procent sprzedaży w marcu albo jako sumę bieżącą elementów w polu Miesiąc.

Formuły       Jeśli funkcje podsumowania i obliczenia niestandardowe nie zapewniają żądanych wyników, to można utworzyć własne formuły w polach obliczeniowych (pole obliczeniowe: Pole w raporcie w formie tabeli przestawnej lub wykresu przestawnego, które używa formuły utworzonej przez użytkownika. Pola obliczeniowe mogą przeprowadzać obliczenia przy użyciu zawartości innych pól w raporcie w formie tabeli przestawnej lub wykresu przestawnego.) i elementach obliczeniowych (element obliczeniowy: Element w polu tabeli przestawnej lub wykresu przestawnego, który używa formuły utworzonej przez użytkownika. Elementy obliczeniowe mogą przeprowadzać obliczenia przy użyciu zawartości innych elementów w obrębie tego samego pola raportu w formie tabeli przestawnej lub wykresu przestawnego.). Na przykład można dodać element obliczeniowy za pomocą formuły do obliczania prowizji od sprzedaży, która będzie odmienna dla każdego z regionów. Wówczas w raporcie będzie automatycznie dołączana prowizja w sumach częściowych i w sumach końcowych.

PokażJak typ źródła danych wpływa na na obliczenia

Obliczenia i opcje dostępne w raporcie są zależne od tego, czy źródło danych pochodzi z bazy danych OLAP (OLAP: Technologia bazy danych, która została zoptymalizowana ze względu na tworzenie kwerend i raportów, a nie na przetwarzanie transakcji. Dane OLAP są zorganizowane hierarchicznie i przechowywane w modułach, a nie w tabelach.) czy też z bazy danych innego typu.

Dane źródłowe OLAP       W przypadku raportów utworzonych na podstawie modułów (moduł: Struktura danych OLAP. Moduł zawiera wymiary, na przykład Kraj/Region/Miasto, i pola danych, na przykład Wielkość sprzedaży. Wymiary organizują typy danych w hierarchie z poziomami szczegółów, a pola danych mierzą ilości.) OLAP sumowane wartości są obliczane wstępnie na serwerze OLAP, zanim program Microsoft Excel wyświetli wyniki. Dlatego też nie można zmienić sposobu obliczania tych wartości z poziomu raportu. Nie można zmienić funkcji podsumowania użytej do obliczenia pól danych lub sum częściowych ani dodać pól obliczeniowych (pole obliczeniowe: Pole w raporcie w formie tabeli przestawnej lub wykresu przestawnego, które używa formuły utworzonej przez użytkownika. Pola obliczeniowe mogą przeprowadzać obliczenia przy użyciu zawartości innych pól w raporcie w formie tabeli przestawnej lub wykresu przestawnego.) lub elementów obliczeniowych (element obliczeniowy: Element w polu tabeli przestawnej lub wykresu przestawnego, który używa formuły utworzonej przez użytkownika. Elementy obliczeniowe mogą przeprowadzać obliczenia przy użyciu zawartości innych elementów w obrębie tego samego pola raportu w formie tabeli przestawnej lub wykresu przestawnego.). Jeśli serwer OLAP zapewnia pola obliczeniowe, znane jako człony obliczeniowe, to pola te będą widoczne na liście pól tabeli przestawnej. Widoczne będą także wszelkie pola obliczeniowe i elementy obliczeniowe utworzone za pomocą makr (makro: Akcja lub zestaw akcji, przy użyciu których można automatyzować zadania. Makra są rejestrowane w języku programowania Microsoft Visual Basic for Applications.) napisanych w języku Visual Basic for Applications (VBA (Visual Basic for Applications): Język makr będący odmianą języka Microsoft Visual Basic. Służy do programowania aplikacji systemu Microsoft Windows i jest dołączony do kilku programów firmy Microsoft.) i przechowywanych w danym skoroszycie, ale niemożliwe będzie zmienianie tych pól lub elementów. Jeśli potrzebne będą dodatkowe typy obliczeń, należy skontaktować się z administratorem bazy danych OLAP.

Inne typy źródeł danych       W raportach opartych na innych typach danych zewnętrznych lub na danych arkuszy, w programie Microsoft Excel używana jest funkcja podsumowania Suma do obliczania pól danych zawierających dane liczbowe, a funkcja podsumowania Licznik do obliczania pól danych zawierających tekst. Można wybrać inną funkcję podsumowania — na przykład Średnia, Maksimum albo Minimum — w celu dalszego analizowania i dostosowywania danych. 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.

Ukryte elementy w sumach      W przypadku źródła danych OLAP można podczas obliczania sum częściowych i sum końcowych uwzględnić lub wykluczyć wartości ukrytych elementów. W przypadku innych typów źródeł danych wartości elementów ukrytych są wykluczane domyślnie, ale można opcjonalnie uwzględnić ukryte elementy pól strony (pole strony: Pole, które jest przypisane do orientacji strony w raporcie w formie tabeli przestawnej lub wykresu przestawnego. W polu strony można wyświetlać albo podsumowanie wszystkich elementów w polu strony, albo jeden element naraz (dane dla wszystkich innych elementów są wtedy odfiltrowywane).).

PokażSkładnia formuły

Formuły można utworzyć tylko w raportach, które nie są oparte na danych źródłowych OLAP (OLAP: Technologia bazy danych, która została zoptymalizowana ze względu na tworzenie kwerend i raportów, a nie na przetwarzanie transakcji. Dane OLAP są zorganizowane hierarchicznie i przechowywane w modułach, a nie w tabelach.).

Formuły są dostępne w raportach wykresów przestawnych i używa się w nich takiej samej składni, co w raportach tabel przestawnych. Najlepsze wyniki w pracy z raportem wykresu przestawnego daje utworzenie i edycja formuł w skojarzonym raporcie tabeli przestawnej (skojarzony raport w formie tabeli przestawnej: Raport w formie tabeli przestawnej, który jest źródłem danych dla raportu w formie wykresu przestawnego. Jest on tworzony automatycznie, gdy użytkownik tworzy nowy raport w formie wykresu przestawnego. Jeśli układ któregokolwiek raportu zostaje zmieniony, układ drugiego również ulega zmianie.), gdzie można zobaczyć poszczególne wartości danych, a następnie wyświetlanie wyników w raporcie wykresu przestawnego.

Elementy formuły       W formułach utworzonych dla pól obliczeniowych (pole obliczeniowe: Pole w raporcie w formie tabeli przestawnej lub wykresu przestawnego, które używa formuły utworzonej przez użytkownika. Pola obliczeniowe mogą przeprowadzać obliczenia przy użyciu zawartości innych pól w raporcie w formie tabeli przestawnej lub wykresu przestawnego.) i elementów obliczeniowych (element obliczeniowy: Element w polu tabeli przestawnej lub wykresu przestawnego, który używa formuły utworzonej przez użytkownika. Elementy obliczeniowe mogą przeprowadzać obliczenia przy użyciu zawartości innych elementów w obrębie tego samego pola raportu w formie tabeli przestawnej lub wykresu przestawnego.) można użyć operatorów (operator: Znak lub symbol, który określa typ obliczenia, jakie ma być przeprowadzane w wyrażeniu. Wyróżnia się operatory matematyczne, porównania, logiczne i odwołania.) i wyrażeń, tak jak w innych formułach skoroszytu. Możliwe jest używanie stałych (stała: Wartość, która nie jest obliczana i dlatego nie zmienia się. Na przykład liczba 210 i tekst „Zarobki kwartalne” są stałymi. Wyrażenie i wartość będąca wynikiem wyrażenia nie są stałymi.) i odwoływanie się do danych z raportu, lecz nie można używać odwołań do komórek ani zdefiniowanych nazw (nazwa: Wyraz lub ciąg, który reprezentuje komórkę, zakres komórek, formułę lub wartość stałą. Używając nazw, łatwiej jest zrozumieć odwołania; na przykład odwołanie Produkty jest bardziej zrozumiałe niż Sprzedaż!C20:C30.). Nie można użyć funkcji (funkcja: Uprzednio napisana formuła, która pobiera wartość lub wartości, przeprowadza operację i zwraca wartość lub wartości. Funkcje upraszczają i skracają formuły używane w arkuszu, szczególnie te, które przeprowadzają długie lub złożone obliczenia.) arkusza wymagających odwołań do komórek lub zdefiniowanych nazw jako argumentów ani funkcji tablicowych (tablica: Służy do konstruowania pojedynczych formuł, które dają wiele wyników lub operują na grupie argumentów uporządkowanych w wiersze i kolumny. Zakres tablicy współużytkuje wspólną formułę; stała tablicowa to grupa stałych używana jako argument.).

Nazwy w raportach       Program Microsoft Excel oferuje nazwy służące identyfikowaniu w formułach elementów raportów. Nazwy składają się z nazw pól (pole: W raporcie w formie tabeli przestawnej lub wykresu przestawnego jest to kategoria danych, które są pobierane z pola w danych źródłowych. Raporty w formie tabeli przestawnej mają pola wiersza, kolumny, strony i danych. Raporty w formie wykresu przestawnego mają pola serii, kategorii, strony i danych.) i nazw elementów (element: Podkategoria pola w raportach w formie tabeli przestawnej i wykresu przestawnego. Na przykład pole „Miesiąc” mogłoby mieć takie elementy, jak „Styczeń”, „Luty” itd.). W poniższym przykładzie dane w zakresie C3:C9 mają nazwę Nabiał.

Przykładowy raport tabeli przestawnej

W raporcie wykresu przestawnego nazwy pól są wyświetlane na przyciskach 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.

Przykłady       Pole obliczeniowe o nazwie Prognoza umożliwia prognozowanie przyszłych zamówień za pomocą formuły takiej jak np.:

=Sprzedaż * 1,2

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%

Formuły operują sumami, a nie poszczególnymi rekordami       Formuły pól obliczeniowych operują na sumie danych bazowych wszelkich pól w formule. Na przykład formuła =Sprzedaż * 1,2 powoduje pomnożenie sumy sprzedaży 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ą natomiast na poszczególnych rekordach; obliczana formuła elementu Nabiał * 115% powoduje pomnożenie każdej sprzedaży z osobna przez 115%, po czym pomnożone kwoty są sumowane łącznie w obszarze danych (obszar danych: Część raportu w formie tabeli przestawnej zawierająca dane podsumowania. Wartości w każdej komórce obszaru danych reprezentują podsumowanie danych z rekordów lub wierszy źródłowych.).

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 słowa lub zawierające liczby lub symbole należy ujmować w pojedynczy cudzysłów.

Sumy końcowe       Formuły nie mogą odwoływać się do sum (takich jak Suma marca, Suma kwietnia czy Suma końcowe w tym przykładzie).

Nazwy pól w odwołaniach do elementów       W odwołaniu do elementu można zawrzeć 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ę. Na przykład, jeśli 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 Ryby. Przy takim sposobie odwoływania się do elementu, element może się zmienić przy każdej zmianie pozycji elementów, albo gdy różne elementy zostaną wyświetlone lub ukryte. Ukryte elementy nie są obliczane 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, to formuła zwróci wartość błędu #ADR!.

W formułach elementów obliczeniowych, jeśli odwołania do elementów to odwołania za pomocą pozycji albo pozycji względnych, wszelkie opcje ustawione w obszarach Autopokazywanie 10 pierwszych i Opcje Autosortowania w oknach dialogowych Sortowanie i 10 pierwszych tabeli przestawnej lub Zaawansowane opcje pola tabeli przestawnej zostaną resetowane do ustawień Wyłączone lub Ręcznie, a opcje staną się niedostępne.

PokażUżywanie formuł w raportach wykresu przestawnego

Metody i zasady tworzenia formuł w raportach wykresów przestawnych są te same, co zasady dla raportów tabeli przestawnej. Gdy tworzone jest pole obliczeniowe (pole obliczeniowe: Pole w raporcie w formie tabeli przestawnej lub wykresu przestawnego, które używa formuły utworzonej przez użytkownika. Pola obliczeniowe mogą przeprowadzać obliczenia przy użyciu zawartości innych pól w raporcie w formie tabeli przestawnej lub wykresu przestawnego.) lub element obliczeniowy (element obliczeniowy: Element w polu tabeli przestawnej lub wykresu przestawnego, który używa formuły utworzonej przez użytkownika. Elementy obliczeniowe mogą przeprowadzać obliczenia przy użyciu zawartości innych elementów w obrębie tego samego pola raportu w formie tabeli przestawnej lub wykresu przestawnego.) w raporcie wykresu przestawnego, obliczenia są odzwierciedlane w skojarzonym raporcie tabeli przestawnej (skojarzony raport w formie tabeli przestawnej: Raport w formie tabeli przestawnej, który jest źródłem danych dla raportu w formie wykresu przestawnego. Jest on tworzony automatycznie, gdy użytkownik tworzy nowy raport w formie wykresu przestawnego. Jeśli układ któregokolwiek raportu zostaje zmieniony, układ drugiego również ulega zmianie.) i vice versa. Najlepsze wyniki daje tworzenie formuł raportów wykresu przestawnego w skojarzonym raporcie tabeli przestawnej, gdzie można zobaczyć poszczególne wartości danych, a następnie wyświetlanie wyników w postaci graficznej w raporcie wykresu przestawnego.

Następujący raport wykresu przestawnego pokazuje sprzedaż każdego ze sprzedawców w rozbiciu na regiony:

Raport wykresu przestawnego pokazujący 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ć pole obliczeniowe, które używa następującej formuły:

=Sprzedaż * 110%

Wynik zostanie odzwierciedlony na takim wykresie:

Raport wykresu przestawnego pokazujący sprzedaż przy wzroście o 10 procent w rozbiciu na regiony

Aby zobaczyć osobny znacznik danych (znacznik danych: Słupek, obszar, punkt, wycinek koła lub inny symbol na wykresie, który reprezentuje pojedynczy punkt danych lub wartość pochodzącą z komórki arkusza kalkulacyjnego. Powiązane znaczniki danych na wykresie tworzą serię danych.) sprzedaży w regionie Północ minus ośmioprocentowe koszty transportu, można utworzyć element obliczeniowy w polu Region za pomocą następującej formuły:

=Północ— (Północ * 8%)

Wynik będzie wyglądać następująco:

Wykres tabeli przestawnej z elementem obliczeniowym.

Element obliczeniowy utworzony w polu Sprzedawca zostanie wyświetlony jako seria reprezentowana w legendzie i w wykresie jako punkt danych w każdej z kategorii.

 
 
Dotyczy:
Excel 2003