Tworzenie formuł w celu wykonywania obliczeń
W tym temacie opisano podstaw konstruowania formuły PowerPivot for Excel, przedstawia przykład tworzenia kolumna obliczeniowa i informacje dotyczące 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 PowerPivot skoroszyt, można użyć formuły w kolumn obliczeniowych i środki:
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.Miara jest używany w wartości obszaru tabela przestawna.Aby umieścić obliczone wyniki w innym obszarze tabeli przestawnej, należy użyć kolumny obliczeniowej.
Podstawowe informacje dotyczące formuł
PowerPivot for Excel zawiera DAX nowy język formuły do tworzenia niestandardowych obliczeń.DAX umożliwia użytkownikom na definiowanie niestandardowych obliczeń w PowerPivot tabel w 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 Omówienie języka DAX (Data Analysis Expressions).
Formuły mogą być złożone, ale w poniższej tabela przedstawiono podstawowe formuły, które mogłyby zostać wykorzystane w PowerPivot kolumna obliczeniowa.
Formuła |
Opis |
=TODAY() |
Wstawia dzisiejszą data w każdym wierszu kolumna. |
=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. |
Można budować PowerPivot formuł obliczane kolumny tak, jak tworzyć formuły w programie Microsoft Excel.Tworzenia formuł dla miar przy użyciu jednej z następujących okien dialogowych: Okno dialogowe Ustawienia miary (agregacja standardowa)or 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 albo wpisz lub wybierz nazwę funkcja lub wpisz 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 Fx przycisk, 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 błędy składni: Upewnij się, że wszystkie nawiasy są zamknięte i poprawnie odwołania kolumn, tabel i wartości.
Naciśnij klawisz ENTER, aby zaakceptować formułę.
Ostrzeżenie
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:
Data sprzedaży |
Podkategoria |
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.
PowerPivot nie zamykający funkcji Dodaj lub automatyczne dopasowanie nawiasów.Należy się upewnić, każda funkcja jest poprawne syntaktycznie lub nie można zapisać lub użyć formuły. PowerPivot Podświetl nawiasów, który ułatwia sprawdzenie, jeżeli są właściwie zamknięty.
Aby uzyskać więcej informacji o korzystaniu z funkcji Autouzupełnianie, zobacz Tworzenie kolumny obliczeniowej i Tworzenie miary.
Praca z tabelami i kolumnami
PowerPivot tabele wyglądać podobnie do tabel programu Excel, ale różnią się w sposób pracy z dane i formuły:
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 Excel i PowerPivot tabele danych są nie jest wymienne, ale można połączyć się z tabel programu Excel z PowerPivot i wkleić dane programu Excel do PowerPivot.Aby uzyskać więcej informacji, zobacz 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 następująca formuła ilustruje sposób odwoływania się do kolumn z dwóch tabel za pomocą w pełni kwalifikowane nazwę:
=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])
Po obliczeniu formuły PowerPivot for Excel najpierw sprawdza, czy ogólna składnia, a następnie sprawdza nazwy kolumn i tabel, które zapewniają przeciwko możliwe kolumny 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 o wymaganiach nazewnictwa dla tabel, kolumn i innych obiektów zobacz "Wymagania nazewnictwa" in Specyfikacja składni języka DAX dla programu PowerPivot.
Ostrzeżenie
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 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 przypomina kwerenda parametryczna: można obliczyć sumę inny dla każdego wiersza w bieżącej tabela.
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 Praca z relacjami w tabelach przestawnych.
Usuwanie błędów występujących w formułach
Jeśli występuje błąd podczas definiowania kolumna obliczeniowa, formuła może zawierać albo Błąd składni lub błąd semantyczny.
Błędy syntaktyczne są najłatwiejsze do usunięcia.Zazwyczaj są spowodowane brakiem nawiasu lub przecinka.Uzyskać pomoc dotyczącą składni poszczególnych funkcji, zobacz Informacje dotyczące funkcji języka DAX używanych w programie PowerPivot.
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 wydaje się być poprawne, ale gdy dane programu PowerPivot silnika pobiera dane stwierdza niezgodność typów i zgłasza błąd.
Formuła przekazuje do funkcji niepoprawną liczbę parametrów lub parametry niewłaściwego typu.
Formuła odwołuje się do innej kolumna, która zawiera błąd, a zatem jego 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.