@parasite85(Nie)dozwolone związki Excela i bazy danych - część 3

(Nie)dozwolone związki Excela i bazy danych - część 3

19 marca 2012, 22:38

Wstęp

W poprzednich dwóch wpisach przedstawiłem sposoby, aby tabelkę w Excelu przekształcić na bazę danych MS SQL (Część 1, Część 2 ). Coraz mniejsze jednostki gospodarcze gromadzą coraz więcej danych, tak więc migracja na profesjonalny system bazodanowy ma sens nawet w mikroprzedsiębiorstwach. Jednak po co gromadzimy dane? W jakim celu pieczołowicie zapisujemy, archiwizujemy informacje? Co sprawia, że są one tak cenne? Informacja sama w sobie nie jest dla nas istotna. Ważne dla nas są odpowiedzi których dzięki tym danym jesteśmy sobie udzielić na zadane pytania. Tym właśnie zajmuje się analiza danych. Można analizować dane ręcznie, ale to raczej przy Mega/Giga/Tera bajtach danych nie wchodzi w grę. Można kupić profesjonalne programy przeznaczone do tego celu, ale mało które małe przedsiębiorstwo jest w stanie sobie na to pozwolić. Można zamówić program napisany chociażby w PHP, który łącząc się z bazą danych będzie w stanie generować odpowiednie raporty, to jednak może być dosyć kosztowne i mało elastyczne. W tym może nam pomóc Excel i funkcje tabeli przestawnych.

Przygotowania 1 - stworzenie widoku

Potrzebna nam będzie baza danych, którą stworzyliśmy w pierwszej lub drugiej części: Database2SQL Załóżmy, że na podstawie naszych danych, chcemy stwierdzić, czy w przedsiębiorstwie występuje dyskryminacja płciowa pracowników. Do tego celu stworzymy widok. Widoki, są wygodnym sposobem na udostępnienie wycinka bazy danych. Służy to zarówno bezpieczeństwu (określony użytkownik, może mieć dostęp do konkretnych tabel, a nawet kolumn, a ukryć możemy inne) jak i wygodzie (tworząc taki widok nie musimy powtarzać pewnych zapytań). Widoki można tworzyć na różne sposoby, najłatwiej dla nowicjuszy jest to wyklikać. W oknie Microsoft SQL Server Management Studio po rozszerzeniu drzewka naszej bazy, klikamy prawym na "View" i wybieramy "New View..." Wybieramy wszystkie tabele:

Dodałem wszystkie kolumny ze wszystkich tabel (poza danymi z tabel słownikowych) i zapisałem widok jako przykladowa_lista.

Przygotowania 2 - dane wracają do Excela

Wskazujemy Exelowi połączenie

Najpierw musimy wejść w zakładkę dane, z innych źródeł, a następnie z programu SQL Server. W oknie które się pojawi wpisujemy nazwę serwera:

Następnie wybieramy bazę danych (u mnie Database2SQL) i odznaczamy pole "Połącz z określoną tabelą".

Po kliknięciu dalej, pojawi nam się okno w którym nic nie musimy robić. Możemy jednak wpisać tam nazwę połączenia, opis, ustawić uwierzytelnienia, itp. Po utworzeniu połączenia Excel zaproponuje nam z jakiej tabeli (lub widoku) chcemy czerpać dane. Wskazujemy na widok który stworzyliśmy:

Klikamy "OK" i wybieramy opcję: "Raport w formie tabeli przestawnej"

Chyba o czymś zapomniałem

Rzeczywiście. Zapomniałem o tym, że w tabeli nie mamy informacji o płci. Co prawda, mógłbym wrócić prawie do początku i poprawić co trzeba, ale spróbujmy rozwiązać sprawę na bieżąco. Otwieramy SQL Server Management Studio. Najpierw dodajemy kolumnę w tabeli (można to wyklikać - prawym na tabelę lista, i design w wierszu na samym dole wpisać "Plec" i w typie danych zmniejszyć ilość znaków z 10 (nvarchar(10)) na 1 nvarchar(1). Zapisujemy i zamykamy. Teraz pora na dane. Klikamy "New Query" i wpisujemy:


UPDATE Lista SET Plec='K' WHERE RIGHT(Imię,1)='a'
UPDATE Lista SET Plec='M' WHERE RIGHT(Imię,1)!='a'

Wracamy do naszego widoku w SQL Server Management Studio (jak zamknęliśmy już kartę, to prawym na nasz widok i design). Dodajemy do widoku nową kolumnę i go zapisujemy. Teraz możemy wrócić do Excela i odświeżyć dane (Zakładka Dane - Odśwież wszystko). Jeszcze możemy dodać do widoku jedną kolumnę, ale tylko w widoku (nie modyfikując pierwotnej tabeli). Do kodu widoku dopisujemy:


, CONVERT(INT, (GETDATE()-dbo.Lista.Pracuje_od))/365.25 AS 'Lata_pracy'

Całość wygląda tak:

Pora na analizę danych

Do tej pory były to typowo techniczne przygotowania. W tej chwili musielibyśmy określić kryteria, po których będziemy określali, czy dochodzi do dyskryminacji, czy też nie. Proponuję wziąć pod uwagę Lata pracy i stanowisko w "Etykietach wierszy", Płeć w "Etykietach kolumn" oraz w wartościach "Pensję". W odpowiednie rubryki po prawej stronie przeciągamy odpowiednie kolumny z tabeli. Zaznaczyłem całą kolumnę "Lata pracy" i dodałem grupowanie - zakres sam się wpisał, interwał=5. Po prawej stronie okna Excela jest "Suma z Pensja". Proponuję kliknąć lewym i "Ustawanie wartości pola". Zmieniamy z "Suma" na Średnia". Następnie format liczby i wybieramy walutowe oraz 0 miejsc po przecinku - nie jest to istotne przy naszej analizie. W tej chwili to wygląda tak:

Aby ułatwić sobie pogląd sytuacji, mając zaznaczoną jakąkolwiek komórkę w tabeli naciskamy F11 (tworzenie wykresu).

Zakończenie

Na podstawie tego wykresu ja nie widzę dyskryminacji. Nie mamy pełnych danych, ponieważ przy takich analizach jeszcze np. zasługi danego pracownika powinny być brane pod uwagę i pewnie 1000 innych czynników. Bardziej od kwestii analitycznej chodziło mi o przedstawienie możliwości jakie daje Excel w tym zakresie. Dzięki temu niedużym kosztem otrzymujemy potężne narzędzie. Można nawet próbować zestawić takie połączenie (przygotowując odpowiednie widoki) i odpowiednie osoby przeszkolić z posługiwania się tabelami przestawnymi. Dzięki temu sami sobie będą mogli w łatwy sposób wyciągać dane które potrzebują.