Zacznijmy od pytania: Dlaczego warto gromadzić dane? Akurat odpowiedź na to pytanie, która najmocniej na mnie działa, nie pochodzi z żadnej książki o finansach czy też technicznej, ale z książki fantasy – „Achaja” Andrzeja Ziemiańskiego. Jeśli dobrze pamiętam, to skryba Zaan na podstawie zebranych danych odnajduje źródło zarazy oraz zmienia całkiem sposób prowadzenia wojny, co prowadzi królestwo Troy od zwycięstwa do zwycięstwa.

Zatem możemy dojść do wniosku, że same dane nie wystarczą – musimy z nich wyciągnąć odpowiednie wnioski albo znaleźć osobę, która potrafi odpowiednio przeanalizować dane.
W dzisiejszych czasach odpowiednia analiza danych sprowadza się najczęściej do oszczędzania lub zarabiania pieniędzy, ale zanim będziemy mogli analizować dane, musimy je odpowiednio zapisać. W taki sposób, żeby ich analiza była jak najprostsza. Żeby to zrobić, w pierwszej kolejności musimy sobie odpowiedzieć na pytanie:

Czym jest baza danych?

Korzystając z definicji z Wikipedii:
Baza danych to zbiór danych zapisanych zgodnie z określonymi regułami.
Czyli po kolei – musimy nasze dane zapisać. W dzisiejszych czasach najczęściej zapisujemy dane na nośnikach cyfrowych, a Excel (lub ewentualnie inny arkusz kalkulacyjny) jest jednym z bardziej czytelnych i popularnych sposobów zapisu danych.
Teraz wypadałoby ustalić reguły zapisywania danych, gdyż mocno wpływają one na to, jak łatwo będzie je później można analizować, a tym samym wyciągnąć z nich wnioski, które prowadzą do konkretnych korzyści:
  • wyeliminowania zbędnych kosztów,
  • promocji wartościowych ludzi,
  • odnajdywania tzw. wąskich gardeł lub usterek w produkcie,
  • minimalizacji czasu przejazdu/reakcji/procesu.

Jakie są reguły gry zwanej bazą danych w Excelu?

Pierwszą i najważniejszą regułą jest to, żeby każdy odrębny typ danych (informacja) trafił do swojej kolumny w Excelu.
Co to dokładnie znaczy?
Rozważmy taki przykład (Rysunek 1), że nasze dane są wyeksportowane z naszego systemu do pliku .txt lub .csv, gdzie dane najczęściej rozdzielane są przecinkiem lub średnikiem. Jeśli je zaimportujemy/skopiujemy bezpośrednio do Excela, to trafią do pojedynczej kolumny.

Przy takim zapisie bardzo trudno się zorientować, jakie dokładnie dane mamy, co oznaczają i jak są szczegółowe.
Dlatego potrzebujemy je rozdzielić i dodatkowo nazwać każdy typ danych – każda kolumna musi mieć swój nagłówek (jest to druga reguła związana z przechowywaniem danych w Excelu, ułatwiająca przede wszystkim takie operacje, jak sortowanie i filtrowanie, ale praktycznie jest nieodzowna przy każdej operacji związanej z analizą, przechowywaniem lub przekształcaniem danych).
Ponieważ dane często trafiają do Excela w postaci pojedynczej kolumny, programiści Microsoftu stworzyli dwie funkcjonalności, które ułatwiają ich rozdzielanie – jest to polecenie Tekst jako kolumny z karty Dane oraz od Excela 2013 również Flash Fill (Wypełnianie błyskawiczne), które działa bardziej w tle i uruchamia się, gdy wpisujemy dane w komórki i Excel odnajdzie wzorzec, na podstawie którego chcemy wyciągać lub zmieniać dane z sąsiadujących kolumn (Rysunek 2).

Załóżmy, że już udało nam się podzielić dane po średniku i dodaliśmy nagłówki.
Są dużo czytelniejsze dla człowieka, ale również dla Excela. Widać, że są to jakieś dane adresowe. Przyczepmy się tutaj do jednej kolumny(pola)– Nazwisko i Imię. Czy faktycznie jest to pojedyncza informacja?
To zależy od celów i operacji, jakie planujemy wykonywać na bazie danych w Excelu. Przykładowo, jeśli będziemy zawsze sortować dane najpierw po nazwisku, a później po imieniu i nie będziemy wykonywać innych operacji na Nazwisku i Imieniu, to dane mogą zostać w obecnej postaci, ale wystarczyłaby prosta operacja jak adresowanie listów czy zwroty grzecznościowe (np.: Panie Edwardzie), gdzie raczej zaczynamy od imienia albo potrzebujemy tylko imienia. Wtedy zdecydowanie przydałoby się mieć imię i nazwisko w osobnych kolumnach (polach).
Nasz przykład nie porusza aspektów drugiego imienia lub nazwisk zagranicznych, w przypadku których czasem trudno rozróżnić, co jest imieniem, drugim imieniem, a co nazwiskiem, tylko na podstawie kolejności w zapisie (np.: Johann Ludwig Yorck von Wartenburg).
Dlatego sposób, w jaki powinniśmy podzielić informacje na kolumny (pola), powinien być uzależniony od tego, jak będziemy wykorzystywać dane. Wracając do naszego przykładu, możemy sobie zadać pytanie, czy faktycznie warto dzielić adres na ulicę i numer domu, a być może jeszcze numer mieszkania, na osobne kolumny (pola)?
Raczej nie będzie takiej potrzeby, a dodatkowo w przykładowym tekście dla 100 000 wierszy (rekordów) (patrz załączone pliki) wynika, że podział adresu na trzy kolumny (ulica, numer domu i mieszkania) zwiększa objętość pliku (1781 KB) w porównaniu do przechowywania adresu w pojedynczej kolumnie (1249 KB).
Możemy powiedzieć, że to przecież zaledwie 0,5 MB, przy dzisiejszych pojemnościach dysków twardych to nieistotna wartość. Zgadza się. Nie przechowujemy już informacji na pergaminach i bez problemu możemy przechowywać w Excelu tysiące, a nawet setki tysięcy wierszy, ale jeśli chcemy mieć bazę danych, która jest wydajna, musimy brać pod uwagę również jej rozmiar. W powyższym przykładzie rozważano tylko zmianę zapisu pojedynczej informacji, dla 100 000 wierszy, a istnieją bazy danych mające miliony wierszy (rekordów), które niestety trudno jest przechowywać w Excelu (ograniczenie arkusza do 1 048 576 wierszy) i przestaje to być wydajne.
Na koniec opisywania reguł baz danych w Excelu warto wspomnieć, że:
1. Staramy się unikać pustych/niewypełnionych komórek.
2. Unikamy całych pustych wierszy i kolumn wewnątrz naszego zbioru danych (takie wiersze i kolumny uniemożliwiają Excelowi poprawne rozpoznanie całego zakresu zbioru danych).
3. Nie dodajemy komentarzy (tekstu) w komórkach tuż obok zbioru danych (znów niepoprawne rozpoznanie zakresu zbioru danych przez Excela).

Czym są relacje? – oczywiście w bazach danych

Rozmawiając o bazach danych, wręcz obowiązkowo trzeba poruszyć temat relacji.
Do tej pory rozmawialiśmy tylko o bazach danych, które mieszczą się w pojedynczej tabeli (dane adresowe) i nie było potrzeby dodawania informacji w innych tabelach. Czy aby na pewno?
Załóżmy, że dane, które mieliśmy, nie były danymi do korespondencji pojedynczych osób, tylko do portfolio nieruchomości, którymi zarządza nasza firma (Rysunek 3). W takiej sytuacji nie dziwi już, że niektóre osoby się powtarzały, ale pojawia się problem związany z nadmiarowością danych. W przykładzie mamy tylko nazwisko i imię

Ale co, gdybyśmy przechowywali jeszcze numer telefonu klienta, jego mail, od kiedy jest naszym klientem itp.? Bez sensu byłoby powtarzanie tych informacji przy każdej jego nieruchomości. Dlatego informacje w bazach danych dzieli się często na wiele różnych tabel, które są powiązane ze sobą – mają relację między sobą.
W takim razie jak stworzyć relację pomiędzy tabelami w Excelu?
Musimy tutaj na chwilę przystopować, bo tabela w typowej bazie danych różni się odrobinę od tabeli w Excelu. Przede wszystkim wszystkie dane, które do tej pory przedstawiłem w tym artykule, nie są tabelami w rozumieniu Excela. Oczywiście według człowieka to jak najbardziej są tabele (mają nagłówki i dane poniżej), ale Excel nie traktuje ich w żaden specjalny sposób.
To byłoby OK, jeśli tworzylibyśmy relację pomiędzy zbiorami danych w Excelu za pomocą funkcji WYSZUKAJ.PIONOWO, która odnajdywałaby wspólny element w zbiorach i na tej podstawie wyszukiwałaby informację/dane, które w danym momencie są nam potrzebne, ale to przestarzałe rozwiązanie i już przy dziesiątkach tysięcy wierszy zaczyna widocznie obciążać procesor.
Żeby faktycznie stworzyć relację bazodanową pomiędzy zbiorami danych w Excelu, przede wszystkim musimy zamienić zbiory danych na tabele Excela (karta Wstawianie – polecenie Tabela). Tabelę Excela obowiązują reguły baz danych, o których wspominaliśmy wcześniej.

Dzielenie tabeli

Ale chwila moment, my jeszcze nie ustaliliśmy, jak mamy podzielić naszą tabelę na dwie osobne tabele, by zajmowała mniej miejsca. Żeby to zrobić, musimy powiedzieć o kluczu głównym w tabeli – jest to pole, które jednoznacznie identyfikuje dany wiersz (rekord), czyli oznacza to przede wszystkim, że wartości w kluczu głównym (jego kolumnie/polu) są unikatowe i nie mogą być puste. Mówiąc inaczej – znając wartość klucza głównego, odnajdziemy bez problemu wszystkie pozostałe informacje z nim powiązane (np.: za pomocą funkcji WYSZUKAJ.PIONOWO).
Tworząc tabele w Excelu, nie musimy określać, które pole jest kluczem głównym, ale dobrze wiedzieć, które pole może być kluczem głównym. W przykładzie z Rysunku 3 pole Imię, Nazwisko ani Województwo nie mogą być kluczem głównym, ponieważ powtarzają się w nich wartości. Z kolei adres mógłby posłużyć za klucz główny, bo wygląda na to, że jest unikatowy (ale tylko przy założeniu, że jedna nieruchomość może mieć tylko jednego właściciela, co oczywiście nie jest zgodne z rzeczywistością). Dlatego często w bazach danych możemy spotkać osobne pole automatycznie numerowane, które służy jako klucz główny, żeby uniknąć trudności z identyfikacją, które pole (lub kombinacja pól) jest kluczem gównym.
Przejdźmy do konkretów – podzielmy naszą tabelę na dwie osobne. W jednej tabeli będziemy mieli nazwisko i imię klienta (unikatowe), któremu przypiszemy numer porządkowy (klucz główny). W drugiej tabeli pozostanie adres i województwo, w którym znajduje się nieruchomość, i musimy dodać jeszcze jedną kolumnę, która pozwoli nam zidentyfikować powiązane dane z pierwszej tabeli, czyli tzw. klucz obcy, który jest po prostu wartością klucza głównego z pierwszej tabeli prowadzącą nas do wiersza z właścicielem danej nieruchomości (Rysunek 4).

W tym przykładzie mamy mało kolumn i nie widać dobrze oszczędności miejsca, jaką uzyskujemy dzięki takiemu podziałowi, bo musimy dodawać (zostawiać) kolumny, które pozwolą nam łączyć ze sobą tabele (tworzyć między nimi relację).
Wystarczy jednak sobie wyobrazić to, o czym już było wspomniane w tym artykule, że przy kliencie mamy jeszcze jego numer telefonu, mail, od kiedy jest naszym klientem, jaki jest jego adres do korespondencji itd., i powód – dlaczego dzielimy dane na osobne tabele, pomiędzy którymi tworzymy relacje – zaczyna być jasny (oszczędzamy miejsce, a tym samym nasza baza danych jest bardziej wydajna).

Model danych

Tabele z Rysunku 4 możemy łączyć za pomocą funkcji WYSZUKAJ.PIONOWO, ale od Excela 2013 możemy tworzyć relacje pomiędzy tabelami Excela i dodawać je do modelu danych.
Rozważmy kolejny przykład (Rysunek 5).

Chwila, chwila, przecież w drugiej tabeli (mniejszej) mamy tylko numer miesiąca i jego nazwę. Jaki jest sens tworzenia takich tabel? Czy nie łatwiej wpisywać nazwę miesiąca w tabeli głównej?
Na pewno łatwiej, ale czasami chcemy przedstawiać/identyfikować miesiące po nazwach, a czasem po numerach. Poza tym nawet w Excelu, gdzie mamy dużo mniejszy wpływ na typ danych dla danej kolumny (pola), powoduje to oszczędność miejsca (rozmiaru pliku). Po numerach miesięcy jest też łatwiej określić, który to kwartał. Poza tym na takim prostym przykładzie możemy w klarowny sposób pokazać, jak działa model danych na przykładzie tabel przestawnych.
Zaznaczamy komórkę w głównej tabeli i wybieramy polecenie Tabele przestawne z karty Wstawianie. Otworzy się okno, w którym od Excela 2013 będzie znajdował się checkbox (pole wyboru) – Dodaj te dane do modelu danych, który musimy zaznaczyć.

Następnie przechodzimy do tabeli przestawnej i będziemy chcieli w niej uzyskać podsumowanie sprzedaży po miesiącach (ich nazwach, nie numerach) (Rysunek 6). Pojawia się problem, ponieważ z tabelą przestawną, jest powiązana tylko tabela główna i gdy przeciągniemy z niej pole Miesiąc do obszaru etykiet wierszy, a pole Sprzedaż do obszaru podsumowania Wartości, to będziemy mieć podsumowanie po numerach miesięcy, a nie tak jak chcemy – po nazwach miesięcy. Co w takim razie możemy zrobić, żeby w tabeli przestawnej pojawiły się nazwy miesięcy, a nie ich numery (Rysunek 7)?

Zwróćmy przede wszystkim uwagę, że tuż pod nagłówkiem pól tabeli przestawnej widzimy zakładkę Aktywne, a tuż obok niej jest zakładka Wszystkie. Przechodząc na zakładkę Wszystkie, zobaczymy wszystkie tabele Excel w danym pliku (Rysunek 8).

Dodatkowo tabele Excela powiązane z tabelą przestawną będą miały w ikonie czarną kreskę nagłówków.
Potrzebujemy teraz z tabeli Excela nazwanej tMiesiące przeciągnąć pole Nazwa do obszaru Etykiet zamiast wcześniejszego pola z tabeli Excela tSprzedaż. Niestety, wyniki, które się pojawią, nie będą prawidłowe (dla każdego miesiąca będzie widać sumę z całego roku, a właściwie lat), ponieważ jeszcze nie stworzyliśmy relacji pomiędzy tymi tabelami, ale Excel już się tego domyśla, dlatego w oknie pól tabeli przestawnej pojawia się przycisk Utwórz, za pomocą którego stworzymy relacje (połączenie) pomiędzy tabelami (Rysunek 9).

W oknie, które się otworzy, musimy wybrać tabele, które chcemy połączyć, oraz kolumny (pole), za pomocą których chcemy stworzyć połączenie (relacje). Ważne, że jedna z tych kolumn (pokrewna kolumna – obiekt/klucz podstawowy) musi zawierać unikatowe wartości, które pojawiają się w drugiej kolumnie (obiekt/klucz obcy) (Rysunek 10).

Po wybraniu odpowiednich tabel i kolumn (obie łączące kolumny zawierają liczby – numery miesięcy) tabela przestawna będzie zawierać prawidłowe wyniki (co najwyżej będzie trzeba posortować ponownie dane, żeby Excel prawidłowo wykrył, że korzystamy z niestandardowej listy przy sortowaniu – listy miesięcy) (Rysunek 11).

Teraz już w stworzonej tabeli przestawnej nie będziemy mogli przejść do okna tworzenia relacji. Będziemy musieli to robić z karty Dane za pomocą polecenia Relacje. Otworzy się okno, w którym będziemy mogli zarządzać wszystkimi relacjami w danym pliku Excela (stworzyliśmy zaledwie jedną) (Rysunek 12).

Dotarliśmy do końca artykułu, ale nie do końca tematu baz danych w Excelu. Już teraz powinniśmy zacząć zauważać korzyści, jakie da odpowiednie zapisanie danych w Excelu (podpowiedź – szybszą i łatwiejszą analizę danych oraz raportowanie informacji w taki sposób, w jaki chcesz).
Jednak Microsoft wciąż rozwija Excela i stworzył dwa dodatki, które ułatwiają scalanie i łączenie danych z różnych źródeł (Power Query) oraz zarządzanie relacjami pomiędzy tabelami (PowerPivot).