W tym poradniku pokażę Ci jak zbudować interaktywny wykres Excel. Co to właściwie znaczy „wykres interaktywny”? Możemy przyjąć, że wykres jest interaktywny, gdy możemy nim w pewien sposób sterować. Na przykład za pomocą dodanych kontrolek formularza takich jak przyciski pokrętła:

lub pola wyboru:

Oczywiście można zbudować wykres, który będzie się zmieniał w zależności od innych kontrolek takich jak paski przewijania czy pola opcji. Możliwości jest bardzo dużo. Teraz zobacz jak po kolei taki wykres przygotować.

Rozwiązanie tego problemu było zadaniem jednego z turbo-wyzwań Excel. Dołącz do kolejnych wyzwań i zgarnij atrakcyjne nagrody. Wystarczy, że dołączysz do naszej turbo-grupy na facebooku.

 

 

Stan wyjściowy – wykres typu kombi

Zacznijmy od tabelki z danymi i wykresu typu kombi wstawionego na ich podstawie.

Jest to standardowy wykres, który wyświetla wszystkie wybrane serie bez możliwości sterowania przez użytkownika.

Pobierz plik Excel z powyższymi danymi i spróbuj wykonać ćwiczenie samodzielnie.

Kopia tabeli danych

Pierwszym krokiem jest zrobienie kopii tabeli danych.

Nowy wykres interaktywny wstawimy na tej właśnie kopii. Dzięki temu nie będziemy modyfikować oryginalnych danych.

Wstawianie kontrolki formularza

Wybraną kontrolkę formularza wstawisz z zakładki Deweloper na wstążce. Ja wybrałem przycisk pokrętła.

To dopiero pierwszy krok. Teraz musimy połączyć kontrolkę z odpowiednimi wartościami i na koniec z wykresem.

Atrybuty kontrolki formularza

Każda kontrolka formularza ma swoje atrybuty. Aby je wyświetlić kliknij prawym przyciskiem myszy i wybierz polecenie formatuj formant.

W przypadku przycisku pokrętła będziemy modyfikować Wartość minimalną, maksymalną, zmianę przyrostową oraz łącze komórki. Dzięki temu będziemy mogli zarządzać formantem i ostatecznie podłączyć go do naszego wykresu.

Łącze komórki kontrolki formularza

Na naszym wykresie chcemy wyświetlać jedną z trzech serii (lub grup serii) w jednym czasie. Dlatego stwórzmy prostą listę trzech wartości. Następnie ustawmy atrybuty kontrolki jak poniżej:

Dzięki temu nasz przycisk pokrętła będzie przyjmował wartości 1-3, które odpowiadają kolejnym pozycjom listy. Aktualny wybór widzisz w komórce, którą wybrałeś jako łącze komórki.

Podłączanie danych do przycisku pokrętła

Jesteśmy już gotowi, aby podłączyć nasze dane do przycisku pokrętła. Zrobimy to w skopiowanej tabeli danych. Nowa tabela będzie wyświetlała wartość z oryginalnej tabeli tylko wtedy, gdy przycisk pokrętła będzie ustawiony w odpowiedniej pozycji.

Do tego użyjemy formuły:

=JEŻELI($N$3=1; C3; BRAK())

Użycie funkcji BRAK() gwarantuje nam wyświetlenie (konkretnego) błędu dla serii, których w danej chwili nie chcemy wyświetlać.

Wstawianie interaktywnego wykresu

Jesteśmy już gotowi do wstawienia wykresu na podstawie naszej zmodyfikowanej tabeli danych, która zmienia się w zależności od ustawienia przycisku pokrętła.

Ukryj błędne serie

Na koniec pokażę Ci jak ustawić wykres, aby nie wyświetlał błędnych serii. Chcemy, aby takie serie w ogóle nie były widoczne na wykresie. Standardowo, mogą one być dalej wyświetlane jako zera. Wtedy widzimy niepotrzebne linie na dole wykresu.

Można to łatwo zmienić. Wystarczy przejść do Zaznacz dane –> Ukryte i puste komórki. Następnie zaznaczyć opcję Pokaż błąd #N/A jako pustą komórkę oraz pokaż puste komórki jako odstępy.

Gratulacje! Twój interaktywny wykres Excel jest już gotowy 😊

Pobierz plik z kompletnym rozwiązaniem i przeanalizuj je jeszcze raz.

 

Jeśli szukasz więcej przykładów, to zapraszam na kursy online. Szczególnie przydatne będą kurs Excel Średnio Zaawansowany oraz Excel Zaawansowany. To kompleksowe kursy wideo naszpikowane praktycznymi przykładami. Wynieś swoją wiedzę z Excela na zupełnie inny poziom!

Użyteczność standardowej funkcji SUMA kończy się wtedy, gdy potrzebujemy sumować jedynie wybrane wartości spełniające pewne kryteria. Dokładnie tak jest w naszym przykładzie.

Mamy za zadanie obliczyć łączną liczbę pasażerów podróżujących londyńskim metrem w godzinach szczytu. Ustaliliśmy, że za godziny szczytu przyjmiemy siedem godzin z największym ruchem. Musimy zatem wyznaczyć sumę jedynie siedmiu największych wartości z każdej kolumny dla dni tygodnia poniedziałek – piątek. Wartości te zostały zaznaczone na zielono za pomocą formatowania warunkowego.

Zastosowanie funkcji SUMA oraz ręczne wybranie komórek nie wchodzi w grę. Takie rozwiązanie jest po prostu za mało elastyczne. Musi więc znaleźć lepsze rozwiązanie. Okazuje się, że takich rozwiązań jest kilka. Sprawdź szczegóły poniżej.

Pobierz plik Excel z rozwiązaniem tego przykładu.

1. JEŻELI oraz MAX.K

W pierwszym rozwiązaniu zbudujemy formułę złożoną z dwóch funkcji: SUMA.JEŻELI oraz MAX.K.

=SUMA.JEŻELI(pon;”>=” & MAX.K(pon; 7))

gdzie „pon” jest nazwą obszaru „B5:B:28”.

Wewnętrzna funkcja MAX.K(pon; 7) zwraca siódmą największą wartość we wskazanym zakresie. Natomiast funkcja SUMA.JEŻELI sumuje jedynie te wartości, które są od niej większe lub równe. Czyli dokładnie o to nam chodziło.

Możemy jeszcze bardziej uelastycznić tą formułę ustawiając 7 jako dodatkowy parametr

2. SUMA.ILOCZYNÓW oraz POZYCJA.NAJW

Znów użyjemy kombinacji dwóch funkcji, ale tym razem będą to: SUMA.ILOCZYNÓW oraz POZYCJA.NAJW.

=SUMA.ILOCZYNÓW(pon*(POZYCJA.NAJW(pon; pon)<=7))

Funkcja POZYCJA.NAJW zwraca pozycję danej liczby na liście liczb. Jeśli zastosujemy tą funkcję do całego wektora „pon”, to każdej liczbie z tego zakresu będzie przypisana jej pozycja. Wystarczy wybrać jedynie te, które są na co najmniej siódmej pozycji. Postępując w ten sposób otrzymamy wektor logiczny: {FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;…

Na koniec użyjemy funkcji SUMA.ILOCZYNÓW, aby zsumować wektor powstały z przemnożenia naszego wektora danych „pon” oraz wektora logicznego. Dzięki temu dostaniemy szukaną wartość.

3. Inne kombinacje funkcji

Możesz też użyć innych kombinacji funkcji z dwóch opisanych rozwiązań np. SUMA.ILOCZYNÓW oraz MAX.K. Spróbuj pokombinować. Sprawdź, które rozwiązanie jest dla Ciebie najbardziej intuicyjne.

4. Własna funkcja napisana w VBA

Jeśli często używasz kombinacji tych samych funkcji, np. takich jak opisane wcześniej, możesz napisać własną funkcję w VBA. Dzięki temu będziesz mógł łatwiej i szybciej znaleźć lub obliczyć szukaną wartość.

Sprawdź przykład takiej funkcji poniżej

Function GetXLargest_Vec(Ref_Tab As Range, Ref_Num As Integer) As Long
   'Ta funkcja działa poprawnie jeśli wejściowa tablica Ref_Tab jest wektorem danych/kolumną

    Dim X_Large As Long
    Dim Temp_Cell As Range
    Dim Result As Long

    'Określ wartość numer X (Ref_Num) w zbiorze danych Ref_Tab
    X_Large = Application.WorksheetFunction.Large(Ref_Tab, Ref_Num)
    'funkcja LARGE jest angielską wersją funkcji MAX.K

    Result = 0
    For Each Temp_Cell In Ref_Tab               'Pętla przechodzi przez wszystkie komórki w zakresie Ref_Tab
        If Temp_Cell >= X_Large Then            
            Result = Result + Temp_Cell         'i sumuje te komórki, które są co najmniej takie jak X_Large
        End If
    Next Temp_Cell
        
    GetXLargest_Vec = Result

End Function

Oczywiście taką funkcję możesz poprawić, aby była jeszcze bardziej elastyczna. Dobrze by było, gdyby potrafiła zwrócić od razu sumę N największych wartości dla każdej kolumny z zakresu. Potrafisz to zrobić?

Pobierz plik Excel z rozwiązaniem tego przykładu.

Jeśli szukasz więcej przykładów, to zapraszam na kurs online. Podczas kursu Excel Średnio Zaawansowany lub Excel Zaawansowany dowiesz się jak rozwiązywać podobne problemy za pomocą skomplikowanych i zagnieżdżonych formuł..