Strona używa cookies (ciasteczek). Dowiedz się więcej o celu ich używania i zmianach ustawień. Korzystając ze strony wyrażasz zgodę na używanie cookies, zgodnie z aktualnymi ustawieniami przeglądarki.    X

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

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:Database2SQLZałóż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ą. 

porady

Komentarze

0 nowych
  #1 20.03.2012 13:38

Kocham ten "duży kalkulator" jakim jest Excel. Sam czasem też coś w nim dłubię, a takie artykuły jak ten mocno poszerzają horyzonty i inspirują do stosowania nowych rozwiązań. Dzięki :)

revcorey   6 #2 20.03.2012 22:55

Arkusze kalkulacyjne fajna sprawa. Ale jak bym miał coś liczyć na poważnie to bym jak by min ie dali matlaba/mathematicy to poleciałbym octave/scilab. Na dłuższą metę to jest dużo lepsze niż klikanie w arkuszu nawet + skrypt.

parasite85   7 #3 20.03.2012 23:13

@recovery:
Albo mi się wydaje, albo trochę pomyliłeś programy. Przytaczasz programy matematyczne, gdzie Excel jest wykorzystywany do analizy danych - Mathematica policzyć potrafi, ale czy wyciągnie dane z bazy i zaprezentuje je tak, aby na tej podstawie można było podejmować decyzję??

revcorey   6 #4 21.03.2012 14:35

http://www.mathworks.com/help/toolbox/database/ug/database.html
Przy funkcjach statystycznych itp.(gdzie w excelu kiedyś były błędy) excel to popierdółka. Reprezentacja danych w postaci wykresów jest potężna. Toolboxów masę jest, z excela dane też można ściągać.

parasite85   7 #5 21.03.2012 15:39

Nie twierdzę, że Excel jest lepszy. Zresztą są to różne programy do różnych celów przeznaczone, na korzyść Excela na pewno przemawia cena, która kształtuje się na poziomie około 1100 złotych brutto w pakiecie dla użytkowników domowych i małych firm za cały pakiet. MatLab kosztuje 1170.....dolarów, za aplikację bazową (http://tomopt.com/tomlab/products/prices/commercial.php). Wytłumacz mi proszę, po co wydawać mnóstwo pieniędzy, jeśli w większości Excel świetnie poradzi sobie z zadaniem??

revcorey   6 #6 21.03.2012 18:16

Po pierwsze przeczytaj mój pierwszy post Napisałem na dłuższą metę. Czyli analiza wielu tysięcy rekordów itd.
Po drugie koszta. Scilab(a nawet octave) w przypadku statystyki pokrywa się możliwościami z matlab i ma możliwość podłączenia do bazy się. A jego koszt to 0 zł. I lepiej radzi sobie z obliczeniami, mogą się pojawić algorytmy i liczba danych która zabije excela.
JA sam używam arkuszy głównie Calca i piszę w nim sprawka, czy ewentualnie parę set danych z jakiś czujników. I jest to wygodne narzędzie ale jak napisałem jak się człowiek zajmuje jakimiś takimi większymi obliczeniami to na dłuższą metę bardziej opłaca się liczyć w czym innym chociaż wymaga to nauczenia się programowcze ale w tym przypadku jest to proste.

parasite85   7 #7 22.03.2012 07:50

Jeszcze nie spotkałem się z sytuacją w której Excel nie dał sobie rady, ale jeśli tak się stanie, to będę wiedział czego użyć

  #8 26.03.2012 00:32

Nie jestem informatykiem, ale znam setki różnych programów, kilka języków programowania, które zmieniałem przez lata, a samego Excel'a używam od ponad 20 lat. Od ponad ponad 10 lat używam go wraz z wykorzystaniem VBA, natomiast o ponad 8 lat wykorzystuję również różne inne API wywoływane z Excel'a. Dlaczego? Dlatego, że moim zdaniem nie ma prostszego narzędzia do analiz danych, wizualizacji danych i automatyzacji obróbki danych w jednym. Excel ściąga mi dane z FTP, generuje różne pliki wynikowe txt, xls, csv, dsv, xml. Excel czyta mi dane obrabia i wprowadza wyniki do systemów z dostępem terminalowym, gdzie "klepanie ręczne" zajełoby mi setki godzin a moi informatycy chcieli za zmiany oprogramowania setki tysięcy złotych.
No a już bajecznie działa Excel 2010 gdzie do wcześniejszych zalet należy dorzucić możliwość załadaowania do jednego arkusza ponad miliona rekordów. To wszystko dostępne dla przeciętnego użytkownika biznesowego jak ja za niewielkie pieniądze.