Formuły obok tabeli zapytania rozjeżdżają się po odświeżeniu

Power Query w Excelu

Formuły obok tabeli zapytania rozjeżdżają się po odświeżeniu

W skrócie

Formuły obok tabeli zapytania to wygodny sposób, żeby dopisać do wyniku Power Query odchylenie od budżetu, prowizję albo komentarz. Działa to do pierwszego odświeżenia, w którym zmieni się liczba albo kolejność wierszy. Pokazujemy na teście w Excelu, co dokładnie dzieje się z formułami i wpisami w kolumnach obok, i jak ułożyć arkusz tak, żeby odświeżanie niczego nie rozjeżdżało.

Jak to wygląda w praktyce

Masz w arkuszu tabelę załadowaną przez Power Query, a obok niej, po pustej kolumnie, formuły liczące coś dla każdego wiersza albo ręcznie wpisane uwagi. Po Odśwież wszystko dzieją się trzy rzeczy:

• formuła stojąca obok sklepu A liczy dane sklepu B, bo w tym wierszu arkusza jest już inny rekord,
• nowe wiersze tabeli nie mają formuł, a pod ostatnią formułą zostaje luka,
• uwagi wpisane obok trafiają do innych rekordów.

W naszym teście w Excelu wynik zapytania był posortowany malejąco po wartości, a obok stały formuły =B2*2, =B3*2 i =B4*2. Po odświeżeniu z dwoma nowymi rekordami, z których jeden trafił na górę tabeli, a drugi na dół, formuła z czwartego wiersza zmieniła się na =B6*2, czyli wskazywała zupełnie inny rekord, a dwa dodatkowe wiersze zostały bez formuły. Excel nie pokazał przy tym żadnego komunikatu.

Dlaczego tak się dzieje

Przy odświeżaniu Power Query zwraca nowy zestaw wierszy w kolejności wynikającej z kroków zapytania, a Excel wpisuje go w obszar tabeli. Gdy wierszy przybywa, Excel wstawia komórki wewnątrz tabeli, ale nie przesuwa komórek w kolumnach obok niej. Formuła poza tabelą jest przypisana do wiersza arkusza, a nie do rekordu, więc po odświeżeniu liczy to, co akurat stoi w jej wierszu. Część odwołań Excel przy tym dopasowuje do wstawionych komórek, dlatego adres w formule potrafi przeskoczyć na inny wiersz.

Kolumna obliczeniowa wewnątrz tabeli działa inaczej. Jest częścią tabeli, a jej formuła odwołuje się do bieżącego wiersza zapisem [@Kolumna]. W naszym teście po odświeżeniu taka kolumna miała formułę we wszystkich wierszach, także w nowych, i każda liczyła dane swojego rekordu.

Z wartościami wpisanymi ręcznie w dodatkowej kolumnie tabeli jest gorzej. Nie ma w nich formuły, którą dałoby się powtórzyć, więc zostają na swoich pozycjach i po odświeżeniu stoją przy innych rekordach. Ręczne sortowanie wyniku w arkuszu tego nie naprawia. W drugim teście Excel po odświeżeniu przywrócił kolejność ustawioną w arkuszu, a mimo to wszystkie trzy komentarze stały przy innych rekordach, a wiersz jednego ze starych rekordów został pusty.

Jak to rozwiązać krok po kroku

1. Znajdź obliczenia poza tabelą. Sprawdź, które kolumny obok tabeli zapytania mają formuły albo wpisy dotyczące pojedynczych wierszy. Kolumna oddzielona od tabeli choćby jedną pustą kolumną do tabeli nie należy i nie przesuwa się razem z jej wierszami.
2. Zrób z formuły kolumnę obliczeniową. Kliknij pierwszą pustą komórkę w wierszu nagłówków tuż na prawo od tabeli i wpisz nazwę kolumny, na przykład Odchylenie. Excel rozszerzy tabelę o tę kolumnę. W komórce pod nagłówkiem wpisz formułę z odwołaniem do bieżącego wiersza, na przykład =[@[Sprzedaż netto]]-[@Budżet], i naciśnij Enter. Excel wypełni nią całą kolumnę.
3. Albo policz to w Power Query. W edytorze na karcie Dodaj kolumnę kliknij Kolumna niestandardowa, wpisz nazwę Odchylenie i formułę [Sprzedaż netto] - [Budżet]. Wynik stanie się częścią zapytania, więc zawsze będzie w tym samym wierszu co jego dane. Dla 750 zł sprzedaży i 800 zł budżetu taka kolumna zwróciła -50.
4. Usuń stare formuły spoza tabeli. Po przeniesieniu obliczeń skasuj formuły w kolumnach obok. Formuły zbiorcze, na przykład sumę całej kolumny, pisz z odwołaniem strukturalnym, czyli nazwą tabeli i kolumny zamiast zakresu komórek. Microsoft opisuje, że takie odwołania dopasowują się, gdy w tabeli przybywa wierszy.
5. Ręczne uwagi trzymaj w osobnej tabeli. Wpisz je w zwykłej tabeli Excela z kolumnami klucza, na przykład Sklep i Początek miesiąca, oraz kolumną Komentarz. Wczytaj ją poleceniem Z tabeli/zakresu, a w zapytaniu głównym użyj Scal po obu kolumnach klucza i rozwiń kolumnę Komentarz. W naszym teście komentarz został przy właściwym sklepie i miesiącu niezależnie od kolejności wierszy.
6. Sortuj w zapytaniu, nie w arkuszu. W edytorze zaznacz kolumnę i kliknij Sortuj rosnąco albo Sortuj malejąco jako jeden z ostatnich kroków, już po scaleniach. Nie sortuj tabeli wynikowej w arkuszu: w naszym teście Excel zapamiętał takie sortowanie i zastosował je ponownie po odświeżeniu, zamiast kolejności z zapytania.

Jak sprawdzić, że zadziałało

Zrób test z lekcji o ładowaniu danych: dodaj do folderu źródłowego plik za kolejny miesiąc i kliknij Odśwież wszystko. W kursie zestawienie urosło wtedy z 60 do 75 wierszy. Sprawdź, czy kolumna obliczeniowa ma formułę w każdym nowym wierszu, i przelicz ręcznie dwa, trzy wiersze, w tym jeden z nowych. Komentarze dołączone scalaniem powinny stać przy tych samych sklepach i miesiącach co przed odświeżeniem.

Na przyszłość trzymaj się prostej zasady: w kolumnach obok tabeli zapytania nie zostawiaj niczego, co dotyczy pojedynczych wierszy. Obliczenia wierszowe należą do tabeli albo do zapytania, a dane wpisywane ręcznie do osobnej tabeli z kluczem.

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5