Tworzenie formuł na potrzeby obliczeń
W tym temacie opisano podstawy tworzenia formuł w programie PowerPivot for Excel, pokazano przykład tworzenia kolumny obliczeniowej oraz opisano sposób pracy z tabelami. Ten temat zawiera następujące sekcje:
Miary i kolumny obliczeniowe
Podstawowe informacje dotyczące formuł
Praca z tabelami i kolumnami
Usuwanie błędów występujących w formułach
Po przeczytaniu tego tematu warto zapoznać się z następującymi tematami, aby uzyskać więcej informacji:
Miary i kolumny obliczeniowe
W skoroszycie programu PowerPivot można używać formuł umieszczonych w kolumnach obliczeniowych i miarach:
Kolumna obliczeniowa to kolumna dodawana do istniejącej tabeli programu PowerPivot. Zamiast wklejać lub importować wartości kolumny, użytkownik tworzy formułę języka DAX (Data Analysis Expressions), która definiuje wartości w kolumnie. Umieszczenie tabeli programu PowerPivot w tabeli przestawnej (lub na wykresie przestawnym) spowoduje, że kolumny obliczeniowej będzie można używać jak dowolnej innej kolumny danych.
Miara to formuła utworzona specjalnie do użycia w tabeli przestawnej (lub na wykresie przestawnym), w której są używane dane programu PowerPivot. Miary mogą być oparte na standardowych funkcjach agregujących, takich jak COUNT lub SUM, ale można też zdefiniować własną formułę, używając języka DAX. Miary są używane w obszarze Wartości tabeli przestawnej. Aby umieścić obliczone wyniki w innym obszarze tabeli przestawnej, należy użyć kolumny obliczeniowej.
Aby uzyskać więcej informacji, zobacz sekcję „Kolumny obliczeniowe i miary” w temacie Omówienie języka DAX (Data Analysis Expressions).
Podstawowe informacje dotyczące formuł
Program PowerPivot for Excel oferuje nowy język formuł DAX umożliwiający tworzenie obliczeń niestandardowych. Język DAX umożliwia użytkownikom definiowanie obliczeń niestandardowych w tabelach programu PowerPivot i tabelach przestawnych programu Excel. Język DAX zawiera niektóre funkcje używane w formułach programu Excel oraz funkcje dodatkowe, zaprojektowane na potrzeby pracy z danymi relacyjnymi i wykonywania dynamicznych agregacji. Aby uzyskać więcej informacji, zobacz temat Omówienie języka DAX (Data Analysis Expressions).
Formuły mogą być złożone, ale w poniższej tabeli pokazano podstawowe formuły, których można używać w kolumnie obliczeniowej programu PowerPivot.
Formuła |
Opis |
=TODAY() |
Wstawia dzisiejszą datę w każdym wierszu kolumny. |
=3 |
Wstawia wartość 3 w każdym wierszu kolumny. |
=[Column1] + [Column2] |
Dodaje wartości z tego samego wiersza kolumn [Column1] i [Column2] i umieszcza wyniki w tym samym wierszu kolumny obliczeniowej. |
Formuły programu PowerPivot przeznaczone do użycia w kolumnach obliczeniowych można tworzyć podobnie jak formuły programu Microsoft Excel. Do tworzenia formuł przeznaczonych do użycia w miarach służą następujące okna dialogowe: Okno dialogowe Ustawienia miary (agregacja standardowa) i Okno dialogowe Ustawienia miary (agregacja niestandardowa).
Wykonanie poniższych kroków umożliwia utworzenie formuły:
Każda formuła musi rozpoczynać się od znaku równości.
Można wpisać lub wybrać nazwę funkcji albo wpisać wyrażenie.
Po wpisaniu kilku pierwszych liter funkcji lub nazwy funkcja autouzupełniania wyświetli listę dostępnych funkcji, tabel i kolumn. Naciśnij klawisz TAB, aby dodać do formuły element z listy funkcji autouzupełniania.
Kliknij przycisk Fx, aby wyświetlić listę dostępnych funkcji. Aby wybrać funkcję z listy rozwijanej, użyj klawiszy strzałek w celu wyróżnienia odpowiedniej pozycji, a następnie kliknij przycisk OK w celu dodania funkcji do formuły.
Określ argumenty funkcji, wybierając je z listy rozwijanej możliwych tabel i kolumn lub wpisując ich wartości.
Sprawdź, czy nie występują błędy składniowe: upewnij się, że wszystkie nawiasy są zamknięte i że odwołania do kolumn, tabel i wartości są poprawne.
Naciśnij klawisz ENTER, aby zaakceptować formułę.
[!UWAGA]
Zaakceptowanie formuły w kolumnie obliczeniowej powoduje wypełnienie kolumny wartościami. W przypadku miary naciśnięcie klawisza ENTER powoduje zapisanie definicji miary, a w przypadku nowej miary programu PowerPivot automatycznie dodaje miarę do obszaru Wartości tabeli przestawnej.
Tworzenie prostej formuły
W poniższym przykładzie pokazano, jak utworzyć kolumnę obliczeniową z prostą formułą, na podstawie następujących danych:
SalesDate |
Subcategory |
Produkt |
Sprzedaż |
Ilość |
|---|---|---|---|---|
1/5/2009 |
Accessories |
Torba |
254995 |
68 |
1/5/2009 |
Accessories |
Miniładowarka |
1099.56 |
44 |
1/5/2009 |
Cyfrowe |
Płaskie cyfrowe |
6512 |
44 |
1/6/2009 |
Accessories |
Obiektyw konwersyjny |
1662.5 |
18 |
1/6/2009 |
Accessories |
Statyw |
938.34 |
18 |
1/6/2009 |
Accessories |
Kabel USB |
1230.25 |
26 |
Aby utworzyć kolumnę obliczeniową z prostą formułą |
|
Porady dotyczące korzystania z funkcji autouzupełniania
Funkcji autouzupełniania formuł można używać w środku istniejącej formuły z funkcjami zagnieżdżonymi. Tekst bezpośrednio poprzedzający punkt wstawiania jest używany do wyświetlania wartości na liście rozwijanej, a cały tekst następujący po punkcie wstawiania pozostaje niezmieniony.
Program PowerPivot nie dodaje nawiasu zamykającego funkcji ani nie dopasowuje nawiasów w sposób automatyczny. Użytkownik musi się upewnić, że każda funkcja jest poprawna pod względem składni. W przeciwnym wypadku nie można zapisać ani używać formuły. Program PowerPivot wyróżnia nawiasy, co ułatwia sprawdzenie, czy są one poprawnie zamknięte.
Aby uzyskać więcej informacji dotyczących używania funkcji autouzupełniania, zobacz tematy Kolumny obliczeniowe i Miary w programie PowerPivot.
Praca z tabelami i kolumnami
Tabele programu PowerPivot przypominają tabele programu Excel, ale różnią się od nich pod względem obsługi danych i formuł:
Formuły działają tylko z tabelami i kolumnami, a nie z pojedynczymi komórkami, odwołaniami do zakresów lub tablicami.
W formułach można używać relacji w celu pobierania danych z powiązanych tabel. Pobierane wartości są zawsze powiązane z bieżącą wartością wiersza.
Formuł języka DAX (Data Analysis Expressions) nie można wklejać w skoroszycie programu Excel i odwrotnie.
Nie można używać nieregularnych (niewyrównanych) danych, tak jak w arkuszu programu Excel. Każdy wiersz w tabeli musi zawierać taką samą liczbę kolumn. Niektóre kolumny mogą jednak zawierać wartości puste. Tabele danych programu Excel i tabele danych programu PowerPivot nie są zamienne, ale można utworzyć połączenie z tabelami programu Excel z programu PowerPivot i wkleić dane programu Excel do programu PowerPivot. Aby uzyskać więcej informacji, zobacz tematy Dodawanie danych przy użyciu połączonych tabel programu Excel i Kopiowanie i wklejanie danych do programu PowerPivot.
Odwoływanie się do tabel i kolumn w formułach i wyrażeniach
Do tabeli i kolumny można odwołać się, używając jej nazwy. Na przykład poniższa formuła pokazuje sposób odwoływania się do kolumn z dwóch tabel przy użyciu w pełni kwalifikowanej nazwy:
=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])
Podczas obliczania formuły program PowerPivot for Excel najpierw sprawdza składnię ogólną, a następnie sprawdza podane nazwy kolumn i tabel w odniesieniu do możliwych kolumn i tabel w bieżącym kontekście. Jeśli nazwa jest niejednoznaczna albo nie można odnaleźć kolumny lub tabeli, zostanie zwrócony błąd formuły (ciąg #BŁĄD zamiast wartości danych w komórkach, w których wystąpił błąd). Aby uzyskać więcej informacji na temat wymagań dotyczących nazewnictwa tabel, kolumn i innych obiektów, zobacz sekcję „Wymagania dotyczące nazewnictwa” w temacie Specyfikacja składni języka DAX dla programu PowerPivot.
[!UWAGA]
Kontekst jest ważną funkcją skoroszytów programu PowerPivot i umożliwia tworzenie formuł dynamicznych. Kontekst jest ustalany na podstawie tabel w skoroszycie, relacji między tabelami i zastosowanych filtrów. Aby uzyskać więcej informacji, zobacz temat Kontekst w formułach języka DAX.
Relacje tabel
Tabele mogą być powiązane z innymi tabelami. Utworzenie relacji umożliwia wyszukiwanie danych w innej tabeli i używanie powiązanych wartości w celu wykonywania złożonych obliczeń. Na przykład kolumny obliczeniowej można użyć w celu wyszukania wszystkich rekordów wysyłek powiązanych z bieżącym odsprzedawcą, a następnie zsumować koszty wysyłek dla każdego odsprzedawcy. Efekt będzie podobny do efektu użycia zapytania parametrycznego: można obliczyć inną sumę dla każdego wiersza w bieżącej tabeli.
Wiele funkcji języka DAX wymaga, aby istniała relacja między tabelami albo między wieloma tabelami, co umożliwia lokalizowanie kolumn, do których utworzono odwołania, i zwracanie sensownych wyników. Inne funkcje podejmują próby zidentyfikowania relacji, jednak najlepsze wyniki można osiągnąć, zawsze tworząc relację tam, gdzie jest to możliwe. Aby uzyskać więcej informacji, zobacz następujące tematy:
Podczas pracy z tabelami przestawnymi szczególnie ważne jest, aby połączyć wszystkie tabele używane w tabeli przestawnej, co umożliwi poprawne obliczenie danych podsumowania. Aby uzyskać więcej informacji, zobacz temat Praca z relacjami w tabelach przestawnych.
Usuwanie błędów występujących w formułach
Jeśli podczas definiowania kolumny obliczeniowej wystąpi błąd, może to oznaczać, że formuła zawiera błąd syntaktyczny lub błąd semantyczny.
Błędy syntaktyczne są najłatwiejsze do usunięcia. Zazwyczaj są spowodowane brakiem nawiasu lub przecinka. Aby uzyskać pomoc dotyczącą składni poszczególnych funkcji, zobacz temat Funkcje języka DAX — kompendium.
Błędy drugiego typu występują, gdy składnia jest poprawna, ale wartość lub kolumna, której dotyczy odwołanie, jest nieprawidłowa w kontekście formuły. Takie błędy semantyczne mogą być powodowane przez dowolny w następujących problemów:
Formuła odwołuje się do nieistniejącej kolumny, tabeli lub funkcji.
Formuła wygląda na poprawną, ale gdy aparat danych programu PowerPivot pobiera dane, okazuje się, że w danych występuje niezgodność typów, co powoduje błąd.
Formuła przekazuje do funkcji niepoprawną liczbę parametrów lub parametry niewłaściwego typu.
Formuła odwołuje się do innej kolumny, która zawiera błąd, i dlatego jej wartości są nieprawidłowe
Formuła odwołuje się do kolumny, która nie została przetworzona. Może się tak zdarzyć po zmianie trybu korzystania ze skoroszytu na tryb ręczny, wprowadzeniu zmian i nieodświeżeniu danych lub niezaktualizowaniu obliczeń
W pierwszych czterech przypadkach język DAX flaguje całą kolumnę zawierającą nieprawidłową formułę. W ostatnim przypadku język DAX wyszarza kolumnę w celu wskazania, że jest ona w stanie nieprzetworzenia.
Zobacz także
Koncepcje
Dodawanie obliczeń do raportów, wykresów i tabel przestawnych