Excel jako narzędzie BI?
Stosunkowo często pojawia się pytanie czy za pomocą Microsoft Excel można realizować proces Business Intelligence. Jest to przecież oprogramowanie określane jako „Arkusz kalkulacyjny” a nie typowy software BI. Biorąc pod uwagę, że BI to przede wszystkim proces, który zakłada przekształcanie danych w informacje i wiedzę, to – pół żartem pół serio – można przeprowadzić go również za pomocą ołówka, kartki papieru i kalkulatora, więc MS Excel tym bardziej na to pozwoli. W jakich sytuacjach jest to uzasadnione oraz jakich narzędzi i funkcji używać aby raporty w przejrzysty i elastyczny sposób pozwalały na skuteczne wnioskowanie i podejmowanie trafnych decyzji biznesowych? Poniżej kilka wskazówek.
Ograniczenie to wydajność
Jeżeli liczba źródeł danych jest niewielka, a same tabele nie są zbyt obszerne z powodzeniem można stworzyć całkiem rozbudowane i interaktywne dashboardy w MS Excel. Oczywiście nasuwa się zasadnicze pytanie gdzie jest granica obszerności tabel i liczby źródeł? Wymaga to wyczucia, jednak granicą może być sprawność działanie pliku, czyli w praktyce brak zawieszania się. Należy naturalnie zostawić pewien margines bezpieczeństwa, ponieważ tabele zwykle się rozrastają z biegiem czasu. Wydajność Excela ma swoje ograniczenia i będzie ona wystawiana wielokrotnie na próbę, jednak stosując pewne dobre praktyki można budować dashboardy integrujące kilka/kilkanaście źródeł w niewielkich przedsięwzięciach analitycznych.
Skoroszyt binarny *.XLSB
Wspomniany nieco wyżej problem wydajności może być częściowo niwelowany przez zapisanie pliku w formiacie *.XSLB (skoroszyt binarny programu Excel). Został on stworzony aby optymalizować szybsze uruchamianie się plików. Dodatkowo najczęściej zajmują dużo mniej miejsca niż w przypadku zapisu tego samego pliku w klasycznym formacie *.XLSX. Nie ma to wpływu na działanie funkcji czy narzędzi. Te działają jak do tej pory, a co więcej plik pozwala na przechowywanie makr. Różnice rozmiarów plików wynikają z innej struktury samych plików. A te bywają znaczne – co prawda nie ma reguły jednak bardzo często sięgają ponad 50%. Pliki dzięki temu nie tylko otwierają się szybciej, ale również szybciej działają podczas przeliczania formuł czy odświeżania narzędzi.
Ilość danych
Pamiętaj, że arkusz MS Excel ma nieco ponad 1 milion wierszy i ponad 16 tysięcy kolumn. O ile liczba kolumn dla większości użytkowników jest wystarczająca, to liczba wierszy może stać się zbyt mała, jeśli w arkuszach miałyby znajdować się dane źródłowe np. dotyczące sprzedaży. Załóżmy, że przypadek dotyczy firmy z branży e-commerce – sprzedaży internetowej. Źródło będzie zawierało szczegółowe dane dotyczące wszystkich pozycji w danym zamówieniu. Firma realizuje 500 zamówień dziennie, a w każdym zamówieniu średnio znajdują się 5 różnych produktów. Każdy dzień przyniesie więc przeciętnie 2500 rekordów (wierszy). Zatem wierszy w arkuszu wystarczy jedynie na nieco ponad rok. Warto od razu dodać, że dzielenie źródła na arkusze z uwagi na brak fizycznego miejsca nie jest dobrą praktyką. Z pomocą przychodzą tu narzędzia PowerPivot i PowerQuery z importem danych zewnętrznych, gdzie ograniczeniem pod względem liczby rekordów jest pamięć komputera a nie liczba wierszy.
Użycie PowerQuery, modeli danych, PowerPivot i miar często okaże się wystarczające a stosowane w tych narzędziach metody będą stosunkowo łatwe do migracji do „pełnoprawnego BI” w postaci PowerBI, ponieważ dane będą zintegrowane i przekształcane według tej samej koncepcji.
Projekt arkusza źródłowego
Jeśli Twój system będzie bazował jednak na innych arkuszach, zaprojektuj je tak, aby były odporne na błędy i otwarte na rozwój tabel. Jeżeli źródłami będą np. kolejne arkusze z kilku oddziałów firmy zadbaj, aby były stworzone według analogicznej struktury tj. z tą samą kolejnością kolumn, typami danych, jednolitymi formułami itd. Same arkusze nazwij wg określonego schematu np. za pomocą dokładnie takich nazw oddziałów jak występują w systemie. Zaowocuje to łatwiejszym stosowaniem formuł. Jeśli wiesz, że będziesz korzystać z określonych funkcji, a mają one swoje wymagania (jak np. to, że szukana_wartość w tabeli_tablicy funkcji WYSZUKAJ.PIONOWO musi znajdować się jako pierwsza kolumna w zaznaczeniu), spróbuj to przewidzieć i uwzględnij w projekcie.
Alternatywy dla WYSZUKAJ.PIONOWO()
Korzystanie z tej funkcji (i innych funkcji wyszukujących) wydaje się niezbędne, ponieważ najczęściej korzystamy z niej do przenoszenia danych z jednej tabeli do drugiej na podstawie pewnej części wspólnej (np. ID Transakcji, SKU itd.). Należy pamiętać jednak, że te funkcje są bardzo obciążające dla naszych komputerów. W przypadku integrowania danych z kilku źródeł lepszym pomysłem będzie skorzystanie z modelu danych, PowerPivot, PowerQuery ze scalaniem oraz dołączaniem zapytań. Daje to większe możliwości integracji, pozwala na budowanie relacji między tabelami, a przede wszystkim bardzo pozytywnie wpływa na wydajność. W wielu przypadkach wyeliminuje to również konieczność stosowania VBA.
Tabele przestawne
Jeśli tylko to możliwe podstawowym elementem dashboardu w Excelu powinna być Tabela przestawna, która sama w sobie jest bardzo elastycznym narzędziem. Determinuje to oczywiście jak mają wyglądać dane źródłowe – muszą mieć choćby nagłówki i raczej należy unikać scalania komórek. Dodanie do niej Fragmentatorów oraz Osi czasu spowoduje, że korzystanie z „pivotów” będzie przyjemniejsze i łatwiejsze. Nie zapominajmy o narzędziach tabeli przestawnych jak Zwijanie pól, Grupowanie czy Pola obliczeniowe. To wszystko w połączeniu z wykresem przestawnym da nam de facto interaktywny dashboard. Funkcja WEŹ.DANE.TABELI() nie jest powszechnie znana, ale pozwala na odwoływać się do pivota. Zdarza się czasami, że tabela przestawna nie pozwala na pewne operacje przez swoje ograniczenia i jesteśmy zmuszeni do potraktowania go jako „tabeli przejściowej”. Sam dashboard jest budowany na bazie klasycznych formuł z odwołaniami do pivota.
Elementy wizualne
Formatowanie warunkowe sprawia, że wyróżnianie komórek i liczb jest interaktywne, ponieważ aktywuje się gdy spełniony jest zdefiniowany przez nas warunek. Automatyczne wypełnianie komórek kolorem, paskami danych, ikonami ułatwiają zauważenie pewnych prawidłowości w tabelach. Wykresy przebiegu w czasie, czyli „mikrowykresy” znajdujące się w jednej komórce sprzyjają szybkiemu identyfikowaniu tendencji i prawidłowości.
Wykorzystanie Microsoft Excel, jak i innych arkuszy kalkulacyjnych (np. Google Sheets) do realizacji procesu BI jest jak najbardziej możliwe, aczkolwiek należy pamiętać o wielu ograniczeniach. Dużym problemem jest stosunkowo niska wydajność, która bywa przeszkodą w dłuższej perspektywie, gdy towarzysząca rozwojowi biznesu ilość danych rośnie. Skorzystanie z tworzenia zapytań w PowerQuery, korzystanie z modeli danych i Power Pivot znacznie przyspieszy działanie plików i przygotuje system raportowy na łatwe przeniesienie zapytań i miar w przyszłości do PowerBI.
business consulting and analytics
Excelling
Śląska 6/51
02-472 Warszawa
NIP PL7611533031
+48 696 030 760 | biuro@excelling.pl