Istnieje kilka sposobów automatyzacji raportowania w Excelu. Jeżeli bazujemy na tabelach przestawnych, po prostu odświeżamy je i wykresy przestawne automatycznie się aktualizują. Jeżeli bazujemy na tabelach zdefiniowanych, Excel automatycznie rozszerza zakres danych o nowe rekordy i kolumny. Czy da się tak zautomatyzować także zwykłe wykresy Excel, aby po wklejeniu nowych danych wszystkie wykresy się odświeżyły? Tak i to bez VBA!

Przygotowanie danych w programie Excel

Zacznijmy od pojedynczego wykresu. Jego uzupełnienie o dane ze stycznia kolejnego roku wymaga wstawienia danych w nowej kolumnie oraz przesunięcia zakresu danych (ewentualnie można nadpisać dane, wklejając dane o jedną kolumnę w lewo).

A co gdyby aktualizacja wymagała tylko wstawienia nowych danych…

Przygotowanie nazw zdefiniowanych w programie Excel

Kluczem do sukcesu jest oparcie źródła danych do wykresu nie o zakres komórek, a o nazwę zdefiniowaną. Nazwa zdefiniowana to nic innego jak obszar komórek nazwany globalnie dla całego arkusza lub skoroszytu. Najczęściej wykorzystuje się nazwy zdefiniowane do nazywania komórek z kursami walut, np. euro, co pozwala później tworzyć formuły z użyciem nazwy zdefiniowanej. Dla przykładu w komórce D1 posłużyłem się formułą =100*euro. Wszystkie formuły znaleźć można w Menedżerze nazw na karcie FORMUŁY (Ctrl+F3).

Przepis na automatyczny zakres jest dość skomplikowany i składa się kombinacji 3 formuł:

• ILE.NIEPUSTYCH – funkcja odpowiada za policzenie, ile komórek w naszym zakresie ma dane (czyli są niepuste). Wynikiem tej formuły jest liczba komórek, w naszym przypadku dla wiersza 2 jest to 13 (tekst też się liczy).
• INDEKS – funkcja ta zwraca adres komórki, który jest interpretowany przez inne formuły
• PRZESUNIĘCIE – funkcja przesuwa zakres danych o liczbę wierszy i pól, przy okazji rozszerzając go o zadaną szerokość i wysokość.

Zacznijmy od utworzenia wartości dla osi czasu – tworzymy nową nazwę zdefiniowaną, a następnie wklejamy tam formułę:

=PRZESUNIĘCIE(INDEKS(Dane!$1:$1;;ILE.NIEPUSTYCH(Dane!$1:$1)-11;1);0;0;1;12)

Formuła ta zaznacza 12 ostatnich elementów pierwszego wiersza, co można przetestować, klikając F5 (Przejdź do) i wpisują nazwę „czas”:

Następnie tworzymy taką samą nazwę zdefiniowaną, tylko dla 2 wiersza. Nazwijmy ją „wynik”:

=PRZESUNIĘCIE(INDEKS(Dane!$1:$1;;ILE.NIEPUSTYCH(Dane!$2:$2)-11;1);0;0;1;12)

Podłączenie wykresu do nazwy zdefiniowanej

Teraz pozostaje nam jedynie odnieść serię danych wykresu (Zaznacz dane > Edytuj) do naszej nazwy zdefiniowanej poprzez wprowadzenie jako Wartość serii:

=nazwa_pliku.xlsx!nazwa_zdefiniowana

Inaczej niż przy innych elementach arkusza posługujemy się nazwą pliku z rozszerzeniem i wykrzyknikiem na końcu, a nie nazwą arkusza.

W ten sposób dodawanie kolejnych kolumn z danymi będzie proste:

Jak automatyzować w ten sposób cały raport?

Wystarczy utworzyć prostą strukturę danych, złożoną z czasu w pierwszym wierszu i wyników dla różnych wskaźników w kolejnych wierszach. Następnie utworzyć wiele nazw zdefiniowanych (1 wiersz = 1 nazwa) i wiele wykresów. Aktualizacja raportu dla kolejnego miesiąca będzie sprowadzać się do ręcznego uzupełnienia jednej kolumny danych lub wklejenia jej z kwerendy. Tabela źródłowa może mieć układ wierszy lub kolumn (co wymaga zmiany formuły). Wykresy mogą być w oddzielnym arkuszu, bo nazwy zdefiniowane mają zasięg na wszystkie arkusze. Warto pamiętać, aby w arkuszu z danymi nie było niepotrzebnych wpisów w komórkach (co spowoduje błędne działanie funkcji ILE.NIEPUSTYCH).

Plik do pobrania

Tu pobierzesz plik z wykresem Excel, który sam się aktualizuje.