Dziś nietypowo rozpocznę posta nie od VBA czy Excela ale od języka SQL. Oczywiście Ci co mnie znają wiedzą, że o Excela też na 100% zahaczymy ;) Ale zamysł jest taki, że póki co skupiam się na tym jak pisać zapytania SQL. Po przeczytaniu tego artykułu każdy będzie umiał za pomocą poleceń SQLa uzyskać to co w Excelu robi tabela przestawna (czy też z angielska pivot table ;) )

 

Czego dowiesz się z poniższego artykułu:

1. Co to jest SQL i zapytania SQLowe?
2. Zapytania SQL w Excelu
3. Zapytanie SELECT
4. DISTINCT
5. WHERE – filtrowanie wyniku zapytania
6. Operatory AND i OR – doprecyzowanie wyników zapytań
7. Podstawowe funkcje języka SQL
8. ORDER BY – sortowanie wyniku zapytania
9. GROUP BY

 

1. Co to jest SQL i zapytania SQLowe?

Najprościej jak tylko się da: SQL to język, który służy do „rozmawiania” z bazą danych :)
Zapytania SQLowe to zdania/polecenia, które „mówimy” do bazy danych, żeby baza coś dla nas zrobiła.

 

Zapytania SQL mają zastosowanie w ogromnej ilości otaczających nas programów. Znajomość tworzenia tych zapytań i komunikowania się z bazami danych oraz innymi aplikacjami takimi jak Excel, jest istotną umiejętnością w branży IT, poszukiwaną i cenioną przez pracodawców.

 

Opanowanie języka SQL na podstawowym poziomie jest naprawdę łatwe, a korzyści jakie ta wiedza daje, ogromne. Jeżeli dodamy do tego szczyptę VBA czy nawet samego Excela, spektrum możliwości jeszcze się poszerzy. Najśmieszniejsze jest to, że jeżeli choć trochę pracujesz w Excelu, to część rzeczy o których piszę, będzie brzmiała znajomo. Niektóre polecenia nazywają się wręcz dokładnie tak samo :)

 

Jeszcze zanim przystąpisz do nauki, chciałem Ci wyjaśnić, że nie ma jednego języka SQL. Tak jak nie ma jednego języka angielskiego :) Nieco inny angielski jest np. w Australii a inny w Szkocji, niby to samo a jednak są „drobne” ;) różnice. Podobnie jest z SQLami. Jest tyle różnych wersji SQLa ile różnych baz danych z którymi chcesz rozmawiać. Najpopularniejsze bazy danych to ORACLE, SQL Server, MySQL i PostgreSQL.

 

Oczywiście najprostsze zapytania będą wyglądały praktycznie tak samo bez względu na bazę ale… jak to mówią, diabeł tkwi w szczegółach a im dalej w las, tym więcej drzew itd. :)

 

2. Zapytania SQL w Excelu

Pierwszym problemem jaki większość osób napotyka przystępując do nauki zapytań SQL to baza danych. Trzeba coś zainstalować, konfigurować, zakładać tabele, użytkowników itd. To zniechęca. Dlatego pomyślałem, że pokażę Ci jak pisać zapytania SQL w Excelu :)

 

Tak tak, stary dobry Excel może być bazą danych :) Dzięki Excelowi niczego nie trzeba będzie instalować, ustawiać, dodawać użytkowników, tabel itd. wystarczy, że pobierzesz plik „baza_danych.xlsm”, który załączam w poniższym linku i… gotowe :)

 

link do pliku baza danych Excel na dysku Google

 

Czyli Pobieramy plik „baza_danych.xlsm” z linku powyżej. Następnie otwieramy go i włączamy makra. Bo musiałem dorzucić do tego pliku parę linijek kodu, żeby nam SQL zadziałał :)

 

Spis tabel, arkuszy wygląda następująco.
Arkusz Wynik – zawiera:

• szare pole do wpisywania poleceń SQL (kolumny od J do P)
• przycisk wykonania zapytania
• sekcję do zwracania wyników (kolumny od A do H)

Arkusz pracownicy – tabela z danymi o pracownikach

Arkusz oddzialy – tabela z danymi o oddziałach
(zwróć uwagę że w nazwie arkusza nie używam polskich znaków, nie jest to konieczne ale… lepiej dmuchać na zimne ;)
Obsługa:
Wpisujemy zapytanie SQL w szarym polu, klikamy przycisk „wykonaj zapytanie” i obserwujemy wynik w arkuszu :)
No to zaczynamy!

 

3. SELECT

Pierwsze polecenie SQL jakie zastosujemy w Excelowej bazie danych to SELECT. Z jego pomocą możesz pobierać dane z tabel czyli w naszym przypadku z arkuszy Excela.
Struktura polecenia (w najprostszej formie) wygląda następująco:

Wielkość znaków w nazwach kolumn, czy tabel nie ma znaczenia. Zapytanie zwróci nam wszystkie dane z kolumn, których nazwy (nagłówki) podamy.
Czyli zapytanie, które pobierze z arkusza pracownicy, dane na temat nazwisk, stanowisk oraz pensji poszczególnych pracowników będzie wyglądało tak:

wynik:

 
Jak widzisz domyślnie zostały pobrane wszystkie dane z kolumn które wskazałem.
Jeżeli kolumn jest dużo i nie chce Ci się podawać ich wszystkich, możesz użyć * do zwrócenia zawartości całej tabeli. Sprawdź to, wykonując poniższe zapytanie.

wynik:

 

Fajne jest to, że kolejność kolumn w jakiej dane występują w tabeli nie ma znaczenia. To treść naszego zapytania „mówi” jak Excelowa baza danych ma ustawić kolejność prezentowanych wyników.
Sprawdź jaki wynik uzyskasz podając inną kolejność kolumn czyli np. tak:

wynik:

 

4. DISTINCT

Drugim, przydatnym poleceniem języka SQL jest DISTINCT. Przypomina on trochę Excelowe narzędzie Usuń duplikaty/Remove Duplicates.
Po dodaniu klauzuli DISTINCT do zapytania, w wyniku zobaczymy tylko unikatowe wiersze / rekordy.
Zobacz jaki wynik da takie zapytanie:

wynik:

 
A co otrzymasz gdy zapytanie napiszesz tak?

wynik:

 
OK, czyli wiemy już jak pobrać wszystkie dane znajdujące się w podanych kolumnach tabeli.
Kolejnym krokiem będzie nauczenie się jak filtrować wyniki zapytań, dodawać do nich warunki, sortować je itd.
Tak jak wspominałem są to totalne podstawy więc nie musisz się obawiać że będzie trudniej niż przed chwilą :)

 

5. WHERE – filtrowanie wyniku zapytania

Trzecie polecenie jakie często dodaje się do zapytania SQL to WHERE

 

Jeżeli używasz Excela, to tak naprawdę pewnie już znasz to polecenie, tyle tylko że pod inną nazwą =JEŻELI / =IF. Tak tak, WHERE to właśnie taki Excelowy =IF. Z jego pomocą możemy przefiltrować wynik zapytania SQLowego czyli „ukryć” to co nas nie interesuje.

 

Zacznijmy od zapytania, które z naszej Excelowej bazy danych wypisze nazwiska, pensje i stanowisko wszystkich pracowników, zarabiających mniej niż 2000 (oczywiście w euro ;).

wynik:

 

To teraz coś innego, wybierzemy nazwiska, pensje oraz stanowiska wszystkich osób pracujących na stanowisku ‘sprzedawca’. Zwróć uwagę na znak apostrofu którym otoczyłem słowo sprzedawca. Dzięki tym znakom słowo sprzedawca będzie traktowane jako tekst :)

 

wynik:

 

Pamiętaj, że cały czas zajmujemy się tutaj zapytaniami SQL na poziomie podstawowym ale gdyby temat bardziej Cię zainteresował, sprawdź jak działa operator LIKE zastosowany w WHERE zamiast znaku =.

 

Jeżeli chcesz filtrować wynik zapytania na podstawie tekstu, dzięki LIKE możesz używać znaków specjalnych (tzw. „wildcards”). I tutaj dochodzimy do różnic w rodzajach języków SQL o których wspominałem na początku. Jeżeli korzystasz z mojego pliku czyli bazy danych Excel, to znaki specjalne będą takie jak w bazie danych Microsoftu (SQL Server) czyli:
% który oznacza zero lub więcej dowolnych znaków
_ oznacza jeden dowolny znak itd.
Przekonaj się jak działa like wykonując poniższe zapytanie:

 

wynik:

 
Czyli wszyscy pracownicy, których nazwisko zaczyna się na literę „B”.
A teraz zobacz jak działa takie zaklęcie:

 

wynik:

 
Czyli mamy wszystkie stanowiska, które mają w sobie literę „e” (na dowolnym miejscu). Zachęcam do pobawienia się operatorem LIKE, bo zrozumienie zasady jego działania przyda Ci się potem w innych językach programowania (np. w VBA czy Pythonie).

 

6. Operatory AND i OR – doprecyzowanie wyników zapytań

Kolejny ważny element zapytań SQL to operatory AND i OR. Jeżeli używasz Excela w języku angielskim to… te słowa na pewno brzmią dla Ciebie znajomo. Pewnie nie raz w Twoich formułach pojawiały się funkcje =AND() czy =OR() :) W przypadku osób korzystających z polskiej wersji językowej mamy odpowiednio formuły =ORAZ() i =LUB(). Po co o tym wspominam? Ponieważ zasada działania tych operatorów jest taka sama w praktycznie każdym języku programowania. Znasz je z Excela, stosujesz wszędzie indziej :)

 

Ale dla tych, którzy nie używali tych formuł w Excelu, przypomnę tylko że:
OR – oznacza, że wystarczy że jeden z elementów wyrażenia będzie prawdziwy i wtedy całe wyrażenie jest prawdziwe
np.: liczba =2 OR liczba=3 OR liczba=4 OR liczba=5 będzie prawdziwe dla: liczb 2,3,4,5

 

AND – oznacza, że WSZYSTKIE elementy muszą być prawdziwe żeby całe wyrażenie było prawdziwe
np.: Imię=’ALA’ AND nazwisko=’Kowalska’ AND miasto=’Krakow’ będzie prawdziwe tylko dla pracownika: ALA Kowalska z miasta Krakow.

 

Spróbujmy w takim razie przećwiczyć działanie tych operatorów zadając naszej Excelowej bazie danych zapytanie o nazwiska, pensje i stanowiska wszystkich pracowników, pracujących na stanowisku ‘ksiegowy’ lub ‘analityk’:

 

wynik:

Takie powtórzenie nazwy kolumny dla której warunek ma być zastosowany czyli: WHERE stanowisko =’ksiegowy’ or stanowisko= ‚analityk’ występuje też np. w języku VBA (ha udało mi się zahaczyć o makra ;)
Jeżeli chodzi o AND to możemy sprawdzić jak on działa pytając bazę o nazwiska, pensje i stanowiska pracowników na stanowisku ‘sprzedawca’, którzy zarabiają więcej niż 1400:

 

wynik:

 

7. ORDER BY – sortowanie wyniku zapytania

W poprzednim dwóch poprzednich punktach (WHERE oraz tym o operatorach) pokazałem w jaki sposób używać filtrowania w SQLu. W tym punkcie zobaczysz w jak sortować dane za pomocą SQL.
Jak się pewnie domyślasz służy do tego polecenie ORDER BY :) Współpracuje ono, z dwoma dodatkowymi klauzulami. Ich nazwa zależy od bazy danych w której pracujemy w jednych będzie to słowo DESC a w innych DESCENDING ale zasada działania jest taka sama czyli:

• ASC to sortowanie rosnące (w Excelu odpowiada mu przycisk A -> Z)
• DESC to sortowanie malejące (w Excelu odpowiada mu przycisk Z -> A)

No to do roboty, chcemy wyświetlić nazwiska pracowników oraz ich pensje, posortowane od osoby zarabiającej najlepiej. Już wiesz, że musimy odpytać bazę o kolumny nazwisko i pensja ale dodatkowo na końcu zapytania dorzucimy nasze ORDER BY

 

wynik:

 

8. Podstawowe funkcje języka SQL.

Co to jest funkcja SQL? W uproszczeniu jest tym samym co funkcja w Excelu :) Jest to takie magiczne słowo/zaklęcie, które na podstawie dostarczonej wartości/argumentu zwraca określony wynik.
Żeby było śmieszniej, nazwy funkcji pomiędzy językami programowania czy programami powtarzają się. Dlaczego? Bo jeżeli coś działa to… po co to zmieniać ;)

 
Przykładowo w Excelu (anglojęzycznym) masz funkcję =MONTH() której wynikiem jest numer miesiąca z podanej daty. W SQLu też jest funkcja, która zwraca numer miesiąca na podstawie daty. Jak myślisz, jak się nazywa? :) DOKŁADNIE !!! month ;)
Czyli gdybym chciał zobaczyć w którym miesiącu został zatrudniony jaki pracownik mogę napisać takie zapytanie:

wynik:

 
Widzisz jakie to proste? :) I takich podobieństw jest mnóstwo.
Kolejny przykład =SUM(), która w Excelu zwraca sumę z zadanego zakresu. Jak w takim razie może wyglądać zapytanie w SQLu, które poda nam sumę zarobków wszystkich pracowników w firmie:

 

wynik:

 
Tak samo będzie działała funkcja Average itd. pamiętajcie tylko o wspomnianej przeze mnie różnicy w językach SQL. Akurat w naszym przypadku musimy napisać AVG a nie Average ale mam nadzieję, że czujesz o co chodzi :)

 

wynik:

 
Tych funkcji jest w języku SQL mnóstwo i nie będę ich tu wszystkich wymieniał ale pamiętaj, jeżeli znasz jakąś funkcję z Excela to bardzo prawdopodobne że taka sama (lub podobna) funkcja znajduje się też w SQLu, Pythonie, PHP czy innym języku.

 
Użytkownicy Excela, wykonując podane powyżej zapytania, zauważyli pewnie, że dają one podobny efekt jak stworzenie tabeli przestawnej i „wrzucenie” kolumny pensja w prawy dolny róg (czyli do wartości).

 

A co robimy w Excelu jeżeli chcemy zobaczyć sumę zarobków wszystkich pracowników ale podzielonych na poszczególne stanowiska?

 
Wystarczy przeciągnąć kolumnę stanowisko w lewy dolny róg czyli do sekcji „wiersze” i już :) Tabela przestawna zgrupuje nam wiersze / pensje tych pracowników, którzy pracują na tych samych stanowiskach w taki sposób.

 
Właśnie to słowo GRUPUJE jest tutaj kluczowe. Bo jest to nasz kolejny, bardzo przydatny operator używany w zapytaniach SQL :)

 

9. GROUP BY

Jeżeli chcesz uzyskać za pomocą zapytania SQL taki efekt jak w tabeli przestawnej użyj operatora grupowania, i powiedz SQLowi w ramach jakiej kolumny ma sobie zgrupować powtarzające się wiersze. Nasza baza danych w Excelu ma w tabeli pracownicy kolumnę STANOWISKO. Wiersze w tej kolumnie powtarzają się.

 

Jeżeli będę chciał zobaczyć jaka jest średnia pensja na danym stanowisku napiszę więc takie zapytanie:

 

wynik:

 
W drugiej kolumnie naszego wyniku widzę, że średnia pensja na stanowisku MENADZER ma zbyt wiele cyfr po przecinku. Spokojnie wystarczyłyby 2 :)

 
Zanim zaczniesz szperać w dokumentacji jaka funkcja SQLa potrafi zaokrąglić wynik, zastanów się jak by to było w Excelu. Dokładnie! =ROUND(liczba, ile_miejsc_po_przeć)

 
Czyli w SQLu będzie to wyglądało bardzo podobnie :)

wynik:

 
Pamiętaj że nasze zapytania wykonujemy nie na prawdziwej bazie danych tylko na Excelu a Excel ma swoje pomysły na wyświetlanie danych. Dlatego jeżeli dorzucę do naszej kolumny symbol waluty (np. ‘PLN’) to Excel wyświetli kolumnę z pensją jako tekst (czyli wyrówna wynik do lewej krawędzi komórki).

 
Sprawdź takie zapytanie:

wynik:

 
I tak dalej i tak dalej :)
 
Tak jak wspomniałem na początku, to co sobie tutaj przećwiczyliśmy to jedynie wstęp do tematu SQLa. Jeżeli zastanawiasz się dlaczego w nagłówku drugiej kolumny naszego wyniku jest jakiś dziwny tekst (Expr1001), jak go zmienić na coś bardziej ludzkiego? Albo chcesz zapytać bazę danych w jakim mieście pracuje który pracownik, czy w którym mieście najlepiej się zarabia (a więc połączyć w wyniku zapytania dane z kilku tabel) polecam skorzystać z ogromnej wiedzy i praktycznego doświadczenia mojego kolegi, który prowadzi w HintMine szkolenia SQL w różnych bazach danych :)
 
Na przykład takie kursy:

• szkolenie SQL Server, Ms SQL
• kurs Oracle
• szkolenia MySQL

 
A jeżeli chcesz się dowiedzieć jak używać języka SQL w Excelu, połączyć dane z plików Excelowych za pomocą zapytań SQLa zapraszam do mnie na:

• Szkolenie VBA dla analityków
• Kurs VBA dla zaawansowanych