Czyli dynamiczny wykres prostą metodą

Chcemy porównać ze sobą sprzedaż i plan z 2 lat. Przedstawienie tego na jednym wykresie będzie nieczytelne, a na 2 oddzielne wykresy nie mamy miejsca i ochoty. Chcemy więc stworzyć jeden wykres, ale taki, aby użytkownik mógł decydować, czy chce zobaczyć na nim porównanie sprzedaży czy planu. O tak:

Wygląda groźnie i skomplikowanie, ale zobaczysz, że do zrobienia tego zadania potrzebujemy sprytnego ułożenia danych i jednej funkcji… :). Let’s go!

1. Dane początkowe

Dane sprzedażowe za 2 lata mamy w następującym układzie: najpierw dane za 2018 rok, a potem za 2019. O tak:

Dane początkowe

Wykres potrzebuje jednak od nas tylko 2 informacji, z których powstaną 2 serie danych: jedna dotycząca roku 2018, a druga 2019. Czyli jeśli użytkownik zdecyduje, że chce oglądać plan – wykres powinien pokazać mu takie dane (C6:C17 i E6:E17):

Dane dla PLANU

A jeśli będzie chciał zobaczyć sprzedaż, to takie (D6:D17 i F6:F17):

Dane dla SPRZEDAŻY

Takie dane mogłyby nam wystarczyć, gdybyśmy zdecydowali się tworzyć wykres oparty na formule (nazwanej). Ja jednak tego rozwiązania nie lubię (choć jest widowiskowe). Ja lubię stworzyć oddzielną tabelę źródłową dla wykresu – w końcu potrzebuje on tylko 2 kolumn z seriami danych i opisy miesięcy (kategorie) – i tabelę tą zasilać danymi pobranymi przez odpowiednie formuły. Takie rozwiązanie jest dla mnie zdecydowanie bardziej czytelne i łatwiejsze do ewentualnych modyfikacji.

Stwórzmy zatem tabelę, bezpośrednio z której wykres będzie pobierał dane.

2. Dane źródłowe dla wykresu – formatka

Będzie to prosta tabelka gdzieś w innym miejscu arkusza. U mnie jest tak (H5:J17):

Tabela źródłowa dla wykresu – formatka

Uzupełnienie tej tabeli będzie dla nas największym wyzwaniem. Pomogą nam w tym odpowiednie formuły, ale to za chwilę. Pamiętajmy bowiem, że wykres ma być połączony z listą rozwijaną, dzięki której użytkownik będzie wybierał co chce zobaczyć na wykresie: plan czy sprzedaż.

Przejdźmy więc do tworzenia listy rozwijanej.

3. Lista rozwijana

Mamy tutaj 2 opcje: sprawdzanie poprawności albo formant formularza. Ja skorzystam z tej drugiej opcji. Wstawię do arkusza formant listy rozwijanej, czyli pole kombi. Zanim jednak to zrobię – przygotuję dane dla tego formantu.

Nasza lista rozwijana potrzebuje zakresu wejściowego, zawierającego wartości, które mają zostać wyświetlone na liście. Zakres ten musi być pionowy. Ponieważ nie mam takich danych (pionowych), stworzę osobne źródło do tej listy (wolę tak, niż zmieniać układ tabeli, który mi się podoba:)). Wpiszę wartości do komórek I1 i I2 (obrazek poniżej).

Dodatkowo potrzebuję też łącza komórki, czyli komórki, w której ma wylądować numer wiersza listy rozwijanej, który wybierze użytkownik. U mnie będzie to J1, którą dla ułatwienia jeszcze sobie nazwę jako Nr. Aby nazwać komórkę, zaznacz ją i w polu nazwy wprowadź nazwę, którą chcesz nadać, po czym zatwierdź Enterem (więcej o nazywaniu komórek możesz się dowiedzieć tutaj):

Zaplecze listy rozwijanej

Jak już to mamy, można wstawiać listę rozwijaną: Deweloper/ Wstaw/ Pole kombi (wstawianie formantu opisywałam dokładnie tutaj). W ustawieniach formantu odwołujemy się do komórek, które przed chwilą wstawiliśmy:

Ustawienia formantu: lista rozwijana

Po naciśnięciu OK mamy ślicznie działającą listę rozwijaną. Wybór wpisu na niej umieszczany jest w formie liczbowej (numer wiersza) w komórce Nr. I ten właśnie wpis wykorzystają nasze formuły uzupełniające dane do wykresu.

4. Dane do wykresu – formuła

Dla tak ułożonych danych, jak w naszej formatce, napiszemy dwie formuły różniące się jedynie przeszukiwanym zakresem.

Pamiętajmy, że w danych do wykresu powinien pojawić się plan lub sprzedaż zawsze z 2018 i 2019 roku. Czyli rok mamy stały – kolumna I zawsze pokaże dane dla 2018, a kolumna J – dla 2019. Zmieniać się będzie tylko plan/sprzedaż.

Zajmijmy się najpierw rokiem 2018.

Do wybrania danych użyję funkcji INDEKS. Dla 2018 roku wybierać będę z zakresu C6:D17. Jeśli user wybierze z listy, że chce zobaczyć Plan – wyświetlę pierwszą kolumnę tego zakresu (zobacz, że na liście plan jest w pierwszym wierszu, czyli do komórki Nr trafi liczba 1 – idealnie), a raczej odpowiednią komórkę z tej kolumny, w zależności od miesiąca. Miesiąc mam napisany numerycznie, więc zadanie będzie proste.

Formuła w I6 wygląda następująco:

=INDEKS($C$6:$D$17;H6;Nr)

Z zakresu $C$6:$D$17 każemy funkcji INDEKS wybrać wartość z wiersza takiego jak miesiąc (jest numeryczny) i z kolumny o numerze wybranym z listy przez użytkownika. Dla planu: 1, dla sprzedaży: 2.

Analogicznie zrobimy dla 2019. Formuła będzie następująca:

=INDEKS($E$6:$F$17;H6;Nr)

Po skopiowaniu obu formuł w dół otrzymamy gotowe dane do wykresu:

Dane do wykresu

Teraz już tylko stworzyć na nich wykres.

5. Wykres

Zaznaczmy więc dane, które mają być źródłem wykresu: I6:J17, kliknijmy menu Wstawianie/Wykres liniowy. Ponieważ zarówno miesiące jak i nazwy serii są liczbami – warto dokonać modyfikacji wykresu tak, aby uzyskać odpowiednie opisy serii na wykresie. Modyfikacji tych możesz dokonać klikając menu Narzędzia Wykresów Projektowanie/ Zaznacz dane i tam przycisk Edytuj dane (po wybraniu odpowiedniej serii), np.:

Edytowanie serii danych 2018

Tytułem wykresu może być lista rozwijana – ciekawie to będzie wyglądało i jednocześnie będzie użyteczne.

Jeśli jednak chcesz mieć tradycyjny tytuł wykresu w polu tekstowym – polecam, aby był on dynamiczny. W końcu prezentowane na wykresie dane zależą od wyboru użytkownika, więc tytuł wykresu też powinien się zmieniać. W tym celu zastosuję patent pokazany w tym wpisie.

W komórce I4 umieszczę następującą formułę (żeby się nie powtarzać – funkcja WYBIERZ ;)):

=WYBIERZ(Nr;I1;I2)

I to będzie moje źródło tytułu wykresu. Efekt tego będzie taki:

Na koniec można jeszcze popracować nad kosmetyką. Wykres z listą rozwijaną można przenieść do oddzielnego arkusza (uważaj wtedy na odwołania w formancie!), a arkusz ze wszystkimi roboczymi danymi ukryć. Będzie pięknie, a user nie będzie widział całej kuchni tematu, a jedynie to, co go interesuje.

PS. Można oczywiście porównywać lata między sobą – wystarczy odpowiednio ułożyć dane. To jednak łatwiej zrobić za pomocą wykresu przestawnego i fragmentatorów i to zdecydowanie mniejszym nakładem pracy :).

Plik z gotowym rozwiązaniem do pobrania:

MalinowyExcel Dane na wykresie wybierane z listy rozwijanej dw.xlsx

I instrukcja krok po kroku na wideo:

(pojawi się we wtorek, 11 czerwca 2019)

Powiązane produkty:

• WEBINAR: Listy rozwijane. Tutaj zobaczysz jeszcze inne opcje wykorzystania list rozwijanych na potrzeby wykresów i nie tylko.
• WEBINAR: Nazwy w Excelu – wstęp. Podczas tego webinaru dowiesz się więcej o tworzeniu i wykorzystywaniu nazw.