Stworzenie skutecznego narzędzia do analizowania i prezentowania danych jest niezwykle pomocne w pracy analityka. Przygotowanie modelu jest zazwyczaj praco- i czasochłonne, jednakże dobrze przygotowane narzędzie ułatwi pracę i może być wykorzystywane do różnego rodzaju danych. W niniejszym artykule stworzymy efektywne narzędzie, które ułatwi analizę danych i sprawi, że praca z danymi stanie się przyjemnością. Prezentujemy interaktywną tabelę z przyciskami przewijania i opcji z karty Deweloper.

Zacznijmy od końca – Jak działa interaktywna tabela Excel z KPI, przyciskiem sortowania i przewijania?

Przygotowanie modelu zajmie trochę czasu, jednakże uzyskany efekt może posłużyć do analizy wielu typów danych. Tabela będzie zawierała pasek przewijania oraz umożliwi sortowanie danych według wybranych kryteriów za pomocą jednego kliknięcia. Plik zostanie stworzony na podstawie danych dotyczących wybranych modeli telefonów komórkowych poszczególnych producentów – cena, sprzedana ilość, wartość sprzedaży, udział w sprzedaży oraz marża jednego z dużych polskich sklepów we wrześniu 2014 roku.

Przygotowanie danych do modelu

W arkuszu o nazwie Data przygotujmy dane źródłowe. Skopiujmy poniższą tabelę i wklejmy od komórki B2.

W arkuszu o nazwie Calculation przygotowujemy tabelę, w której przeliczymy dane do modelu.

Dane przeliczymy w czterech kolumnach, których nagłówki oznaczają: Nad tabelą z przeliczonymi danymi przygotowujemy małą tabelkę, która będzie zawierać parametry finalnego modelu (tabelkę wklejmy od komórki C5 w arkuszu Calculation). Komórka D7 będzie potrzebna do wielu obliczeń. Dlatego też nadamy jej nazwę ‘KryteriaSortowania’. Pisząc potem formułę nie będziemy musieli pamiętać jej adresu, wystarczy że wpiszemy jej nazwę. Wpiszmy w KryteriaSortowania pierwszą z opcji, czyli 1. Następnie skorzystamy z formuły ‘Przesunięcie’. Formuła ta zwraca odwołanie do zakresu, który jest podaną liczbą wierszy lub kolumn począwszy od komórki lub zakresu komórek. Zwrócone odwołanie może być pojedynczą komórką lub zakresem komórek. Można określić liczbę zwracanych wierszy i kolumn. Warto zapamiętać wymienioną formułę, ponieważ będziemy często z niej korzystać. Bazą formuły ‘Przesunięcie’ w kolumnie Wybrany KPI będzie komórka C3 w arkuszu Data. Komórka odpowiada pierwszej pozycji z listy aparatów telefonicznych. Za komórką C3 wpisujemy 0 (nie chcemy przesuwać o wiersze), następnie wpisujemy ‘KryteriaSortowania’ – formuła zwróci wartość przesuniętą o ilość kolumn wskazaną w tej właśnie komórce. W przypadku wartości 1, formuła zwróci wartości dla KPI nr 1. Formułę przeciągamy przez wskazane komórki w wybranej kolumnie. Kolejnym krokiem będzie stworzenie z przeliczonych wartości unikatów. Prostym sposobem będzie dodanie do wartości bardzo małego ułamka według poniższego wzoru. W kolumnie ‘Sortowanie’ wykorzystamy formułę MAX.K, która zwraca k-tą największą wartość w zbiorze danych. Funkcji tej można użyć do wybrania wartości na podstawie jej względnej pozycji. Przykładowo można użyć funkcji MAX.K w celu określenia pierwszego, drugiego lub trzeciego miejsca. Będziemy szukać kolejnych pozycji w tablicy unikatów według poniższego przykładu: W ostatniej kolumnie wpiszemy formułę PODAJ.POZYCJĘ. Funkcja PODAJ.POZYCJĘ wyszukuje określony element w zakresie komórek, a następnie zwraca względną pozycję tego elementu w zakresie. Funkcji PODAJ.POZYCJĘ należy użyć zamiast jednej z funkcji WYSZUKAJ, kiedy jest konieczna znajomość położenia elementu w zakresie, a nie tylko znajomość samej pozycji elementu. Funkcja PODAJ.POZYCJĘ będzie wyglądała jak poniżej: Uzupełniona tabela będzie prezentowała się jak poniżej:

Następnie obok przeliczonych danych stworzymy kolejną tabelę o takiej samej ilości wierszy i sześciu kolumnach (nazwa produktu i 5 KPI). W kolumnie Produkt wykorzystamy kolejny raz funkcję PRZESUNIĘCIE. Bazą będą dane z arkusza ‘Data’, a dokładnie nagłówki. W kolumnie ‘Produkt’ formuła będzie składała się z komórki C2 (nagłówek listy produktów), która będzie przesuwana pionowo o numer pozycji (komórka G10 w arkuszu ‘Calculation’). Formuła PRZESUNIĘCIE będzie wykorzystana także w pozostałych kolumnach. Jednakże w kolumnie ‘Cena’ bazą będzie nagłówek listy cen poszczególnych produktów,… … w kolumnie ‘Ilość’ będzie to nagłówek ilości, itd. Formuły przeciągamy do pozostałych komórek. Poniższa tabela to wynik sortowania według pierwszego KPI, czyli według ceny. Gdybyśmy zmienili wartość w komórce ‘KryteriaSortowania’ na przykład na 2, dane przeliczyłyby się według KPI nr 2 – ilość.

Budowa finalnej tabeli z przyciskami z karty Deweloper

Czas na stworzenie arkusza z finalnym modelem. Arkusz z danymi nazwijmy ‘Dashboard’. Wcześniej jednak uzupełnijmy tabelę z parametrami (arkusz Calculation) o aktualną pozycję. Na razie wpiszmy 1. Chcemy stworzyć narzędzie z suwakiem, dlatego nie wstawiajmy 25 wierszy z danymi źródłowymi, bo suwak nie będzie miał zastosowania. Przyjmijmy zatem, że nasza tabela będzie prezentowała dane dla 10 rekordów, a żeby zobaczyć pozostałe będziemy korzystać właśnie z suwaka. Tabela będzie więc z 10 wierszami. Natomiast kolumn będzie 8 (lp, produkt, suwak, cena, ilość, wartość, udział oraz marża). Pierwsza kolumna będzie odpowiadała liczbie pojedynczej. Ponownie wykorzystamy PRZESUNIĘCIE, bazą będzie kolumna B z arkusza ‘Calculation’, a będziemy przesuwać pionowo o wartość z ‘Aktualnej pozycji’ (komórka C5). Druga kolumna będzie zawierać nazwy produktów. Bazą będzie kolumna I z arkusza ‘Calculation’, a przesuwać będziemy o ‘Aktualną pozycję’. Następnie wstawiamy interaktywny suwak. Wykorzystamy do tego kontrolkę formularza z Dewelopera, który wykorzystywany jest w VBA: Deweloper >Wstaw > Kontrolki formularza. Kontrolkę dopasowujemy rozmiarowo do kolumny E w arkuszu ‘Dashboard’. Wracamy na chwilę do arkusza ‘Calculation’ i uzupełniamy ostatni parametr w tabeli – maksymalna pozycja. Maksymalna pozycja dla suwaka zostanie obliczona na podstawie różnicy między ostatnią pozycją w danych (25) a ilością wierszy w modelu (10). Dodajemy jeszcze 1 na końcu. Funkcja ILE.NIEPUSTYCH zlicza niepuste komórki w zakresie (zakres: Dwie lub większa liczba komórek w arkuszu. Komórki w zakresie mogą być przylegające lub nieprzylegające.). Klikamy prawym przyciskiem myszy na kontrolkę,… …przechodzimy do zakładki Formant. Wartość minimalną ustawiamy na 1, maksymalną – 16 (maksymalna pozycja z arkusza Calculation), zmianę przyrostową – 1, a zmiana strony nastąpi po 5 rekordach. Łączymy kontrolkę z komórką D5 (Calculation), czyli ‘Aktualną pozycją’- komórka ta będzie sprzężona z kontrolką. Oznacza to, że po zmianie wartości w komórce, suwak będzie się przesuwał i na odwrót. Kolejnym krokiem będzie nadanie pozostałym kolumnom nazw zgodnych z nazwami poszczególnych KPI. Następnie wstawiamy kolejne kontrolki z Dewelopera. Tym razem będzie to przycisk opcji. Przyciski wstawiamy po nazwą każdego KPI. Po wstawieniu wszystkich kontrolek i sformatowaniu obszaru, na którym pracujemy, model będzie wyglądał jak poniżej:

Wstawione przyciski opcji łączymy z komórką KryteriumSortowania (arkusz Calculation). Kolejny raz wykorzystamy PRZESUNIĘCIE. Bazą formuły będą dane przeliczone w tabeli od kolumny J do kolumny N. Będziemy przesuwać pionowo zgodnie z wartością ‘Aktualnej pozycji’ (komórka D5 w arkuszu Calculation). Formułę przeciągamy przez całą tabelę.

W modelu użyjemy formatowania warunkowego. Warunek będzie spełniony jeżeli dla KPI 1 wartość komórki KryteriaSortowania będzie równa 1, dla KPI 2=2, dla KPI 3=3, itd. Spełniony warunek umożliwi wypełnienie wybranej kolumny na kolor jasnoszary. Formatowanie warunkowe wstawiamy poprzez wybór: Narzędzia główne>Formatowanie warunkowe>Nowa reguła>Użyj formuły do określenia komórek, które należy sformatować. Dodatkowo możemy wstawić nad i pod paskiem przewijania poniższe formuły, które wskażą w którą stronę możemy przesuwać suwakiem. Gotowe narzędzie, które pozwoli sortować za pomocą jednego kliknięcie, prezentuje się jak poniżej: