W tym artykule przedstawiono podstawy tworzenia formuł obliczeniowych zarówno dla kolumn obliczeniowych , jak i miar w dodatku Power Pivot. Jeśli nie znasz języka DAX, zapoznaj się z artykułem Szybki start: poznaj podstawy języka DAX w ciągu 30 minut.
Podstawowe informacje o formułach
Dodatek Power Pivot udostępnia język DAX (Data Analysis Expressions) do tworzenia niestandardowych obliczeń w tabelach dodatku Power Pivot i tabelach przestawnych programu Excel. Język DAX zawiera niektóre funkcje używane w formułach programu Excel oraz dodatkowe funkcje zaprojektowane do pracy z danymi relacyjnymi i wykonywania agregacji dynamicznej.
Oto kilka podstawowych formuł, których można użyć w kolumnie obliczeniowej:
| Formuła | Opis |
|---|---|
| =DZIŚ() | Wstawia dzisiejszą datę w każdym wierszu kolumny. |
| =3 | Wstawia wartość 3 w każdym wierszu kolumny. |
| =[Kolumna1] + [Kolumna2] | Dodaje wartości w tym samym wierszu kolumn [Kolumna1] i [Kolumna2] i umieszcza wyniki w tym samym wierszu kolumny obliczeniowej. |
Podczas tworzenia formuł w programie Microsoft Excel można tworzyć formuły dodatku Power Pivot dla kolumn obliczeniowych.
Podczas tworzenia formuły wykonaj następujące czynności:
- Każda formuła musi zaczynać się od znaku równości.
- Możesz wpisać lub wybrać nazwę funkcji albo wpisać wyrażenie.
- Zacznij wpisywać kilka pierwszych liter odpowiedniej funkcji lub nazwy, a funkcja Autouzupełnianie wyświetli listę dostępnych funkcji, tabel i kolumn. Naciśnij klawisz TAB, aby dodać element z listy Autouzupełnianie do formuły.
- Kliknij przycisk Fx , aby wyświetlić listę dostępnych funkcji. Aby wybrać funkcję z listy rozwijanej, wyróżnij element za pomocą klawiszy strzałek, a następnie kliknij przycisk OK , aby dodać funkcję do formuły.
- Podaj argumenty do funkcji, wybierając je z listy rozwijanej możliwych tabel i kolumn albo wpisując wartości lub inną funkcję.
- Sprawdź, czy występują błędy składni: upewnij się, że wszystkie nawiasy są zamknięte, a kolumny, tabele i wartości są prawidłowo odwoływane.
- Naciśnij klawisz ENTER, aby zaakceptować formułę.
Uwaga
W kolumnie obliczeniowej, gdy tylko zaakceptujesz formułę, kolumna zostanie wypełniona wartościami. Naciśnięcie klawisza ENTER powoduje zapisanie definicji miary.
Tworzenie prostej formuły
| Aby utworzyć kolumnę obliczeniową za pomocą prostej formuły SalesdateSubcategoryProductSalesQuantity1/5/2009AccessoriesCarrying Case254995681/5/2009AccessoriesMini Battery Charger1099.56441/5/2009DigitalSlim Digital6512441/6/2009AccessoriesTelephoto Conversion Lens1662.5181/6/2009AccessoriesTripod938.34181/6/2009AccessoriesUSB Cable1230.2526
|
|---|
Porady dotyczące korzystania z funkcji Autouzupełnianie
- Funkcji Autouzupełnianie formuł można używać w środku istniejącej formuły z funkcjami zagnieżdżonymi. Tekst znajdujący się bezpośrednio przed punktem wstawiania jest używany do wyświetlania wartości z listy rozwijanej, a cały tekst po punkcie wstawiania pozostaje niezmieniony.
- Dodatek Power Pivot nie dodaje nawiasu zamykającego funkcji ani nie jest automatycznie dopasowywany do nawiasów. Należy upewnić się, że każda funkcja jest poprawna syntaktycznie lub że nie można jej zapisać ani użyć. Dodatek Power Pivot wyróżnia nawiasy, co ułatwia sprawdzanie, czy są one poprawnie zamknięte.
Praca z tabelami i kolumnami
Tabele dodatku Power Pivot wyglądają podobnie do tabel programu Excel, ale różnią się pod względem sposobu pracy z danymi i formułami:
- Formuły w dodatku Power Pivot działają tylko z tabelami i kolumnami, a nie z poszczególnymi komórkami, odwołaniami do zakresów lub tablicami.
- Formuły mogą używać relacji do uzyskiwania wartości z tabel pokrewnych. Pobierane wartości są zawsze powiązane z bieżącą wartością wiersza.
- Nie można wklejać formuł dodatku Power Pivot do arkusza programu Excel i na odwrót.
- Nie można tworzyć danych o nieregularnych ani niewyrównanych danych, tak jak w arkuszu programu Excel. Każdy wiersz w tabeli musi zawierać taką samą liczbę kolumn. Jednak w niektórych kolumnach mogą być puste wartości. Tabele danych programu Excel i tabele danych dodatku Power Pivot nie są wymienne, ale można łączyć się z tabelami programu Excel z dodatku Power Pivot i wklejać dane programu Excel do dodatku Power Pivot. Aby uzyskać więcej informacji, zobacz Dodawanie danych arkusza do modelu danych przy użyciu tabeli połączonej oraz Kopiowanie i wklejanie wierszy do modelu danych w dodatku Power Pivot.
Odwoływanie się do tabel i kolumn w formułach i wyrażeniach
Przy użyciu jej nazwy możesz odwołać się do dowolnej tabeli i kolumny. Na przykład poniższa formuła ilustruje sposób odwoływania się do kolumn z dwóch tabel przy użyciu w pełni kwalifikowanej nazwy:
=SUMA('Nowa sprzedaż'[Kwota]) + SUMA('Przeszła sprzedaż'[Kwota])
Po obliczeniu formuły dodatek Power Pivot najpierw sprawdza ogólną składnię, a następnie sprawdza nazwy podanych kolumn i tabel pod kątem możliwych kolumn i tabel w bieżącym kontekście. Jeśli nazwa jest niejednoznaczna lub nie można odnaleźć kolumny lub tabeli, w formule zostanie wyświetlony błąd (ciąg #ERROR zamiast wartości danych w komórkach, w których występuje błąd). Aby uzyskać więcej informacji o wymaganiach dotyczących nazewnictwa tabel, kolumn i innych obiektów, zobacz "Wymagania dotyczące nazewnictwa w specyfikacji składni języka DAX dla dodatku Power Pivot.
Uwaga
Kontekst to ważna funkcja modeli danych dodatku Power Pivot, która umożliwia tworzenie formuł dynamicznych. Kontekst jest określany na podstawie tabel w modelu danych, relacji między tabelami i wszelkich zastosowanych filtrów. Aby uzyskać więcej informacji, zobacz Kontekst w formułach języka DAX.
Relacje pomiędzy tabelami
Tabele mogą być powiązane z innymi tabelami. Tworząc relacje, uzyskujesz możliwość wyszukiwania danych w innej tabeli i używania powiązanych wartości do wykonywania złożonych obliczeń. Za pomocą kolumny obliczeniowej można na przykład wyszukać wszystkie rekordy wysyłki związane z bieżącym odsprzedawcą, a następnie zsumować koszty wysyłki każdego z nich. Efekt przypomina zapytanie parametryczne: można obliczyć inną sumę dla każdego wiersza w bieżącej tabeli.
Wiele funkcji języka DAX wymaga istnienia relacji między tabelami lub między wieloma tabelami w celu zlokalizowania kolumn, do których się odwołujesz, i zwrócenia odpowiednich wyników. Inne funkcje będą próbować zidentyfikować relację; jednak w celu uzyskania najlepszych wyników zawsze należy utworzyć relację tam, gdzie to możliwe.
Podczas pracy z tabelami przestawowymi szczególnie ważne jest połączenie wszystkich tabel używanych w tabeli przestawnej, aby można było poprawnie obliczać dane podsumowania. Aby uzyskać więcej informacji, zobacz Praca z relacjami w tabelach przestawnych.
Rozwiązywanie problemów z błędami w formułach
Jeśli podczas definiowania kolumny obliczeniowej zostanie wyświetlony błąd, formuła może zawierać błąd składniowy lub błąd semantyczny.
Najłatwiejszym rozwiązaniem są błędy składniowe. Zazwyczaj zawierają brakujący nawias lub przecinek. Aby uzyskać pomoc dotyczącą składni poszczególnych funkcji, zobacz Dokumentacja funkcji języka DAX.
Inny typ błędu występuje, gdy składnia jest poprawna, ale wartość lub kolumna, do których odwołuje się odwołanie, nie ma sensu w kontekście formuły. Takie błędy semantyczne mogą być spowodowane przez dowolny z następujących problemów:
- Formuła odwołuje się do nieistniejącej kolumny, tabeli lub funkcji.
- Formuła wydaje się poprawna, ale gdy dodatek Power Pivot pobiera dane, znajduje niezgodność typu i zgłasza błąd.
- Formuła przekazuje do funkcji nieprawidłową liczbę lub typ parametrów.
- Formuła odwołuje się do innej kolumny, która zawiera błąd, 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ć, jeśli skoroszyt zostanie zmieniony w tryb ręczny, wprowadzisz zmiany, a następnie nigdy nie odświeżysz danych ani nie zaktualizujesz obliczeń.
W pierwszych czterech przypadkach język DAX oflagowuje całą kolumnę zawierającą nieprawidłową formułę. W ostatnim przypadku język DAX wyszarza kolumnę, wskazując, że kolumna jest w stanie nieprzetworzonym.