W tym poście omówimy sumę ze zmieniającej się ilości kolumn w danych. Będzie to rozwiązanie problemu z Power Query w Excelu.
Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Mamy tutaj wynik zapytania Power Query, który tworzy podsumowanie z danych z pierwszej tabeli. Mamy na razie przedstawione tylko 3 miesiące, ale pod spodem dojdą kolejne dane z dodatkowymi miesiącami (rys. nr 2).
W podsumowaniu z Power Query chcemy mieć kolumnę "Suma". Można dodawać kolumny do zapytania Power Query, tylko trzeba mieć na uwadze, że ilość kolumn w Power Query może się zmienić. Tutaj standardowo chcielibyśmy otrzymać sumę. Użyjemy skrótu klawiszowego Alt + =. Excel wstawi nam automatycznie funkcję SUMA, jak widać na rys. nr 3.
Możemy sobie uprościć zapis tej funkcji i wstawić w niej ręcznie odpowiedni zakres zamiast nazw tekstowych. Zapis będzie wtedy wyglądał następująco:
=SUMA(I2:K2)
Otrzymamy zsumowane dane w kolumnie L przedstawione na rys. nr 4.
Nasza suma przedstawia wynik z aktualnej sytuacji. Problem polega na tym, że jeśli wstawimy w dane kolejną kolumnę (Kwiecień) i dopiszemy jakieś wartości, to nie zostaną one uwzględnione w kolumnie Suma (rys. nr 5). Nasza Suma się nie rozszerzy, jej zakres się nie zmieni.
Wróćmy do stanu sprzed dodania dodatkowej kolumny za pomocą skrótu klawiszowego Ctrl+Z. Jeśli natomiast wstawimy kolumnę między np. Lutym a Marcem i wpiszemy jakieś wartości liczbowe, to zostaną one uwzględnione w sumie (rys. nr 6). Zakres Sumy się rozszerzy.
Problem pojawia się, gdy nasze dane zostaną rozszerzone o kolumnę obok zakresu uwzględnionego w formule funkcji SUMA. W takiej sytuacji funkcja się nie rozszerza automatycznie, nie dopasowuje do nowego zakresu. Najprostszym rozwiązaniem jest użycie funkcji Excela. Jest możliwe rozwiązanie tego problemu w Power Query, ale jest bardziej skomplikowane i wymaga użycia kilku funkcji. Rozwiązanie to pokażemy w kolejnym poście.
Naszym zadaniem jest dynamiczne ustalenie zakresu funkcji SUMA. Możemy użyć funkcji PRZESUNIĘCIE. Pierwszym argumentem funkcji jest odwołanie, czyli komórki z zakresu I2:L2 (obejmujemy zakresem kolumnę Suma). Musimy ograniczyć to przesunięcie do ilości kolumn minus jedna kolumna. Czyli nie przesuwamy o konkretną ilość wierszy (argument wiersze), czy kolumn (argument kolumny), ale musimy zmienić argument szerokość (czwarty argument). Trzeci argument, czyli szerokość wstawiamy wartość 1. Musimy policzyć liczbę kolumn. Możemy to zrobić za pomocą funkcji LICZBA.KOLUMN. Argumentem funkcji jest tablica, czyli zakres obejmujący nasze 4 kolumny minus jedna kolumna (I2:L2‑1). Zapis formuły powinien wyglądać następująco:
=SUMA(PRZESUNIĘCIE(I2:L2;0;0;1;LICZBA.KOLUMN(I2:L2)-1))
Powyższą formułę zatwierdzamy. Sprawdzimy teraz, czy działa prawidłowo. Zaznaczamy kolumnę L i za pomocą skrótu klawiszowego Ctrl + Shift + Add wstawiamy nową kolumnę przed tą zaznaczoną. Następnie ręcznie wpisujemy dowolną wartość w tej kolumnie. Wynik w kolumnie Suma automatycznie ulegnie zmianie co widać na rys. nr 7.
Teraz musimy usunąć dodaną kolumnę ręcznie za pomocą skrótu klawiszowego Ctrl+Shift+Subtract. Teraz sprawdzimy, czy jeśli dodamy dużą ilość danych, zakres kolumn i sumy rozszerzy i przeliczy się prawidłowo. Dodatkowe dane z rysunku nr 2 zaznaczamy i przesuwamy w górę, czyli doklejamy do naszych danych źródłowych. Następnie odświeżamy dane z zapytania, czyli klikamy prawym przyciskiem myszy w dowolnym punkcie tabeli i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 8).
Otrzymamy odświeżone (zaktualizowane) dane przedstawione na rys. nr 9.
Jak widać na rysunku powyżej nasze dane się przeliczyły, zakres Sumy odpowiednio się rozszerzył i otrzymaliśmy prawidłowe wyniki.
