Ponowne obliczanie formuł w dodatku Power Pivot

Podczas pracy z danymi w Power Pivot od czasu do czasu może być konieczne odświeżenie danych ze źródła, ponowne obliczenie formuł utworzonych w kolumnach obliczeniowych lub upewnienie się, że dane przedstawione w tabeli przestawnej są aktualne.

W tym temacie wyjaśniono różnice między odświeżaniem danych a ponownym obliczaniem danych, przedstawiono sposób wyzwalania ponownego obliczania oraz opisano opcje sterowania ponownym obliczaniem.

Opis odświeżania danych i ponownego obliczania

Power Pivot korzysta zarówno z odświeżania danych, jak i ponownego obliczania:

Odświeżanie danych oznacza uzyskiwanie aktualnych danych z zewnętrznych źródeł danych. Power Pivot nie wykrywa automatycznie zmian w zewnętrznych źródłach danych, ale dane można odświeżać ręcznie w oknie Power Pivot lub automatycznie, jeśli skoroszyt jest udostępniany w programie SharePoint.

Ponowne obliczanie oznacza zaktualizowanie wszystkich kolumn, tabel, wykresów i tabel przestawnych w skoroszycie zawierających formuły. Ponieważ ponowne obliczanie formuły wiąże się z kosztem wydajności, ważne jest zrozumienie zależności skojarzonych z poszczególnymi obliczeniami.

Ważne: Nie należy zapisywać ani publikować skoroszytu, dopóki formuły w nim nie zostaną obliczone ponownie.

Ręczne a automatyczne ponowne obliczanie

Domyślnie Power Pivot automatycznie ponownie obliczane zgodnie z wymaganiami podczas optymalizowania czasu wymaganego do przetwarzania. Chociaż ponowne obliczanie może zająć trochę czasu, jest to ważne zadanie, ponieważ podczas ponownego obliczania są sprawdzane zależności kolumn i zostanie wyświetlone powiadomienie, jeśli kolumna uległa zmianie, czy dane są nieprawidłowe, lub jeśli w formule, która działała, pojawił się błąd. Można jednak zrezygnować z sprawdzania poprawności i aktualizować obliczenia tylko ręcznie, zwłaszcza jeśli pracujesz z złożonymi formułami lub bardzo dużymi zestawami danych i chcesz kontrolować chronometraż aktualizacji.

Zarówno tryby ręczne, jak i automatyczne mają zalety; jednak zdecydowanie zalecamy używanie trybu automatycznego ponownego obliczania. W tym trybie metadane Power Pivot są przechowywane w synchronizacji i zapobiega problemom spowodowanym usunięciem danych, zmianami nazw lub typów danych lub brakującymi zależnościami.

Używanie automatycznego ponownego obliczania

W przypadku użycia trybu automatycznego ponownego obliczania wszelkie zmiany danych, które spowodują zmianę wyniku dowolnej formuły, spowodują ponowne obliczenie całej kolumny zawierającej formułę. Następujące zmiany zawsze wymagają ponownego obliczenia formuł:

Wartości z zewnętrznego źródła danych zostały odświeżone.
Definicja formuły uległa zmianie.
Nazwy tabel lub kolumn, do których odwołuje się formuła, zostały zmienione.
Dodano, zmodyfikowano lub usunięto relacje między tabelami.
Dodano nowe miary lub kolumny obliczeniowe.
Wprowadzono zmiany w innych formułach w skoroszycie, dlatego kolumny lub obliczenia zależne od tego obliczenia powinny zostać odświeżone.
Wiersze zostały wstawione lub usunięte.
Zastosowano filtr, który wymaga wykonania zapytania w celu zaktualizowania zestawu danych. Filtr mógł zostać zastosowany w formule albo jako część tabeli przestawnej lub wykresu przestawnego.

Używanie ręcznego ponownego obliczania

Możesz użyć ręcznego ponownego obliczania, aby uniknąć ponoszenia kosztów obliczania wyników formuły do momentu, gdy wszystko będzie gotowe. Tryb ręczny jest szczególnie przydatny w następujących sytuacjach:

Projektujesz formułę przy użyciu szablonu i chcesz zmienić nazwy kolumn i tabel użytych w formule przed jej zweryfikowaniem.
Wiesz, że niektóre dane w skoroszycie uległy zmianie, ale pracujesz z inną kolumną, która się nie zmieniła, więc chcesz odroczyć ponowne obliczenie.
Pracujesz w skoroszycie, który ma wiele zależności i chcesz odraczać ponowne obliczanie, dopóki nie będziesz mieć pewności, że wprowadzono wszystkie niezbędne zmiany.

Pamiętaj, że o ile skoroszyt jest ustawiony jako tryb obliczania ręcznego, dodatek Power Pivot w programie Excel nie sprawdza poprawności ani sprawdzania formuł z następującymi wynikami:

Wszystkie nowe formuły dodane do skoroszytu zostaną oflagowane jako zawierające błąd.
W nowych kolumnach obliczeniowych nie będą wyświetlane żadne wyniki.

Aby skonfigurować skoroszyt do ponownego obliczania ręcznego

W Power Pivotkliknij pozycję Obliczenia> projektowania> opcje obliczania> trybie obliczania ręcznego.
Aby ponownie obliczyć wszystkie tabele, kliknij pozycję Opcje obliczania> Oblicz teraz.

Formuły w skoroszycie są sprawdzane pod kątem błędów, a tabele są aktualizowane o wyniki (jeśli istnieją). W zależności od ilości danych i liczby obliczeń skoroszyt może przez jakiś czas przestać odpowiadać.

Ważne: Przed opublikowaniem skoroszytu należy zawsze zmienić tryb obliczania z powrotem na automatyczny. Pomoże to uniknąć problemów podczas projektowania formuł.

Rozwiązywanie problemów z ponownym obliczaniem

Zależności

Jeśli kolumna zależy od innej kolumny, a zawartość tej drugiej kolumny zmienia się w jakikolwiek sposób, może być konieczne ponowne obliczenie wszystkich powiązanych kolumn. Za każdym razem, gdy wprowadzane są zmiany w skoroszycie Power Pivot, dodatek Power Pivot w programie Excel przeprowadza analizę istniejących danych Power Pivot w celu ustalenia, czy jest wymagane ponowne obliczanie, i wykonuje aktualizację w najbardziej efektywny sposób.

Załóżmy na przykład, że istnieje tabela Sprzedaż powiązana z tabelami Product i ProductCategory. i formuły w tabeli Sprzedaż zależą od obu pozostałych tabel. Każda zmiana w tabelach Product (Produkt ) lub ProductCategory (KategoriaProduktu ) spowoduje ponowne obliczenie wszystkich kolumn obliczeniowych w tabeli Sprzedaż . Ma to sens, jeśli wziąć pod uwagę, że mogą występować formuły zwiększające sprzedaż według kategorii lub produktu. Dlatego, aby mieć pewność, że wyniki są poprawne; formuły oparte na danych muszą zostać ponownie obliczone.

Power Pivot zawsze wykonuje pełne ponowne obliczenie tabeli, ponieważ pełne ponowne obliczenie jest bardziej efektywne niż sprawdzanie zmian wartości. Zmiany wyzwalające ponowne obliczanie mogą obejmować takie poważne zmiany, jak usunięcie kolumny, zmiana typu danych liczbowych kolumny lub dodanie nowej kolumny. Jednak pozornie trywialne zmiany, takie jak zmiana nazwy kolumny, mogą również spowodować ponowne obliczenie. Jest tak dlatego, że nazwy kolumn są używane jako identyfikatory w formułach.

W niektórych przypadkach Power Pivot może stwierdzić, że kolumny można wykluczyć z ponownego obliczania. Jeśli na przykład istnieje formuła wyszukuująca wartość, taką jak [Kolor produktu] z tabeli Produkty, a kolumna, która zostanie zmieniona, to [Ilość] w tabeli Sprzedaż , nie trzeba ponownie obliczać formuły, nawet jeśli tabele Sprzedaż i Produkty są ze sobą powiązane. Jeśli jednak istnieją formuły oparte na wartości Sales[Quantity], wymagane jest ponowne obliczenie.

Sekwencja ponownego obliczania dla kolumn zależnych

Zależności są obliczane przed każdym ponownym obliczaniem. Jeśli istnieje wiele kolumn, które są zależne od siebie, Power Pivot jest zgodna z sekwencją zależności. Dzięki temu kolumny są przetwarzane w odpowiedniej kolejności z maksymalną prędkością.

Transakcje

Operacje, które ponownie obliczają lub odświeżają dane, mają miejsce jako transakcja. Oznacza to, że jeśli jakaś część operacji odświeżania zakończy się niepowodzeniem, pozostałe operacje zostaną wycofane. Ma to na celu zapewnienie, że dane nie zostać pozostawione w stanie częściowo przetworzonym. Nie można zarządzać transakcjami tak jak w relacyjnej bazie danych ani tworzyć punktów kontrolnych.

Ponowne obliczanie funkcji nietrwałych

Niektóre funkcje, takie jak TERAZ, LOS lub DZIŚ, nie mają stałych wartości. W celu uniknięcia problemów z wydajnością wykonanie zapytania lub filtrowania zwykle nie powoduje ponownego oceny takich funkcji, jeśli są używane w kolumnie obliczeniowej. Wyniki tych funkcji są obliczane ponownie tylko po ponownym obliczeniu całej kolumny. Te sytuacje obejmują odświeżanie z poziomu zewnętrznego źródła danych lub ręczne edytowanie danych, które powoduje ponowne obliczenie formuł zawierających te funkcje. Jednak funkcje nietrwałe, takie jak TERAZ, LOS lub DZIŚ, będą zawsze obliczane ponownie, jeśli ta funkcja jest używana w definicji pola obliczeniowego.