Miesięczne archiwum: Czerwiec 2017

EXCEL funkcja ŚREDNIA()

EXCEL funkcja ŚREDNIA

Niniejszy wpis opisuje działanie funkcji ŚREDNIA() w programie Microsoft Excel.

Funkcja ŚREDNIA() zwraca wartość średniej arytmetycznej wskazanego zakresu danych. W tym przypadku od razu kojarzą nam się liczby ale funkcja ta może zwracać także średnią np. z nazw lub tablic.

Do zaprezentowania działania tej funkcji przygotowałem krótki przykłąd opierając się na liczbach od 0 do 10, patrz obrazek poniżej. Dane rozmieszczone są w komórkach od B2 do B12.

microsoft excel funkcja średnia function

Wstawiamy się w komórce B13 i klikamy na ikonę funkcji "fx".

microsoft excel funkcja średnia function

Po pojawieniu się okna "wstawianie funkcji" w części "wybierz funkcję" wyszukujemy funkcję "ŚREDNIA" i potwierdzamy przyciskiem OK. Jeżeli naszej funkcji tam nie ma wybierz pozycje "matematyczne" w rozwijalnym menu "wybierz kategorię" lub wpisz nazwę funkcji "ŚREDNIA" w polu "wyszukaj funkcję".

 microsoft excel funkcja średnia function

Teraz otworzyło nam się okno "argumenty funkcji" gdzie musimy podać zakres danych. Możemy to zrobić na dwa sposoby.
 
(1) w pole "Liczba1" wpisujemy zakres danych (komórek) które funkcja wziąć pod uwagę. Widzimy tutaj, że to pole jest wypełnione danymi B2:B12. Excel automatycznie zaproponował nam ten zakres bo komórka w której zastosowaliśmy funkcję ŚREDNIA() bezpośrednio styka się z komórkami z naszego zakresu. Jeżeli wybralibyśmy inną komórkę np. D14 musilibyśmy wpisać zakres danych ręcznie, czyli B2:B12 bo tam znajdują się nasze danej z któych chcemy obliczyć średnią.

(2) drugą opcją jest kliknięcie na ikonę tabelki z czerwonym kursorem. Wtedy okno "argumenty funkcji" zmniejszy się a my będziemy mogli zaznaczyć myszą recznie nasz zakres danych (komórki w któych znajdują się interesujące nas dane).

Wszystko potwierdzamy przyciskiem OK

(3) istnieje także trzeci sposób uruchomienia funkcji ŚREDNIA() bez korzystania z kreatora funkcji, czyli bez uzupełniania okna "argumenty funkcji". W pole po prawej stronie ikony "fx" wprowadzamy od razu formułę "=ŚREDNIA(B2:B12)".

microsoft excel funkcja średnia function

Po potwirdzeniu argumentów funkcji w komórce B13 widzimy wynik działania funkcji ŚREDNIA().

microsoft excel funkcja średnia function

 


EXCEL funkcja SUMA()

EXCEL funkcja SUMA

Ten wpis zapozna was z działaniem funkcji SUMA() w programie Microsoft Excel.

Funkcja SUMA() pomoże nam zliczyć wartości ze wskazanych pól. Aby pokazać Wam działanie tej funkcji przygotowałem dane do testów.

microsoft excel funkcja suma function

Na obrazku widzimy wypełnione komórki od B2 do B11 kolejnymi liczbami całkowitymi od 1 do 10. Przyjmijmy, że chcemy obliczyć ich sumę a wynik chcemy umieścić w komórce pod spodem czyli B12.  
W tym celu ustawiamy się w komórce B12 (1) i klikamy na ikonę funkcji (2).

microsoft excel funkcja suma function


Teraz powinno nam wyskoczyć okno "Wstawianie funkcji" i tam szukamy funkcji SUMA() (3) i potwierdzamy nasz wybór przyciskiem OK (4).

microsoft excel funkcja suma function

Klikając na przycisk OK powinniśmy przenieść się do okna "Argumenty funkcji". W polu "Liczba1" musimy wpisać zakres danych które chcemy zliczyć. Jak już wcześniej wspomniałem (zerknij takżę na obrazek) zakres komórek które chcemy zliczyć to komórki od B2 do B11 i właśnie takie wartości musimy wpisać w pole "Liczba1" oddzielając je dwókropkiem.

Jest jeszcze inna metoda wprowadzania zakresu komórek do zliczenia. Po prawej stronie pola "Liczba1" znajduje się ikona tabelki z czerwoną strzeałką. Kliknięcie tej ikonki powoduje zmniejszenie okna "Argumenty funkcji" i wtedy myszą możemy zaznaczyć obszar do zliczenia.

Kolejne pola np. "Liczba2" a później i "Liczba3" itd służą do wprowadzania dodatkowych zakresów danych. Widzimy więc że zliczać możemy nie tylko dane z jednej kolumny ale dane z różnych części arkusza.

Po potwierdzeniu zakresu danych przyciskiem OK wracamy na arkusz i widzimy, że w komórkce B12 pojawiła się wartość 55. To wynik który zwróciła funkcja SUMA().

microsoft excel funkcja suma function

obok ikony fx widzimy funkcję z argumentami
SUMA(B2:B11)


db HOTELS 11. Wyświetl wszystkie rezerwacje (z informacją czy zrealizowane)

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 11

Treść: Wyświetl wszystkie rezerwacje (z informacją czy zrealizowane)

Polecenia/funkcje w zapytaniu: SELECT, CASE, FROM, JOIN, WHERE


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

SELECT
     Klient.client_last_name AS Nazwisko
    ,Klient.client_first_name AS [Imie]
    ,Kraj.country_name AS [Kraj (hotel)]
    ,Miasto.city_name AS [Miasto (hotel)]
    ,reservation_starting_date AS [Data rozpoczecia rezerwacji]
    ,reservation_final_date AS [Data zakończenia rezerwacji]
    ,CASE
        WHEN Rezerwacja.reservation_realized = 1 THEN 'Zrealizowana'
        ELSE 'NIE zrealizowana'
     END AS [Stan realizacji]
FROM
    reservations Rezerwacja
    JOIN clients Klient ON Rezerwacja.reservation_client_id = Klient.client_id
    JOIN rooms Pokoj ON Rezerwacja.reservation_room_id = Pokoj.room_id
    JOIN hotels Hotel ON Pokoj.room_hotel_id = Hotel.hotel_id
    JOIN countries Kraj ON Hotel.hotel_country_id = Kraj.country_id
    JOIN cities Miasto ON Hotel.hotel_city_id = Miasto.city_id
WHERE
    Klient.client_last_name = 'Banko'

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER


 

db HOTELS 10. Oblicz udział procentowy hoteli w danym kraju względem wszystkich hoteli w bazie

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 10

Treść: Oblicz udział procentowy hoteli w danym kraju względem wszystkich hoteli w bazie

Polecenia/funkcje w zapytaniu: DECLARE, SELECT, COUNT, FROM, WHERE, CAST(), JOIN, GROUP BY, ORDER BY


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

DECLARE @ile_hoteli numeric =
    (
        SELECT
            COUNT(*)
        FROM    
            hotels
    )   
SELECT
     C.country_name AS Kraj
    ,CAST(COUNT(*)*100/@ile_hoteli AS NUMERIC(6,2)) AS [%]
FROM
    hotels H
    JOIN countries C ON H.hotel_country_id = C.country_id    
GROUP BY
    C.country_name
ORDER BY
    2 DESC   

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


 

EXCEL funkcja WYSZUKAJ.PIONOWO

EXCEL funkcja WYSZUKAJ.PIONOWO

Dzisiaj zajmiemy się funkcją WYSZUKAJ.PIONOWO która pomaga nam wyszukać interesujące nas wartości, np. z jednej tabeli (tabela źródłowa), w innej tabeli (tabela docelowa). Wyszukiwanie to odbywa się na zasadzie szukania wskazanej wartości w tabeli docelowej ale w pierwszej od lewej kolumnie i zwrócenie wartości z kolumny (tabela docelowa) wskazanej przez użytkownika. Przejdźmy od razu do przedstawienia przykładu.

Na potrzeby tego wpisu stworzyłem dwie tabele.
Pierwsza "tabela 1 – daty" zawiera jedną kolumnę z trzema datami.

excel funkcja wyszukaj pionowo

Kolumna "data" jest sformatowana jako kategoria "data" i typ "2001-03-14"

Druga "tabela 2 – zamówienia" zawiera więcej danych : id, nr zamówienia, id klienta, data zamówienia

excel funkcja wyszukaj pionowo

Kolumna "data zamówienia" jest sformatowana jako kategoria "data" i typ "2001-03-14"

Naszym zadaniem będzie sprawdzenie czy daty znajdujące się w tabeli "tabela 1 – daty" znajdują się w tabeli "tabela 2 – zamówienia" w kolumnie "data zamówienia". Do tego celu wykorzystamy funkcję WYSZUKAJ.PIONOWO.

Krok 1. Ustawiamy się w komórce po lewej do pierwszej komórki z tabeli 1, czyli w komórce C5, i klikamy na ikonę funkcji.

excel funkcja wyszukaj pionowo

Krok 2. W części wyszukaj funkcję wyszukujemy funkcję WYSZUKAJ.PIONOWO, wskazujemy ją i potwierdzamy przyciskiem OK.

excel funkcja wyszukaj pionowo

Krok 3. Teraz najważniejsza sprawa, w oknie "Argumenty funkcji" musimy wprowadzić odpowiednie wartości.

excel funkcja wyszukaj pionowo

Krok 4. Pole "Szukana_wartość"
    Tutaj wskazujemy szukanych wartośći czyli musimy wprowadzić wartość pierwszej komórki z kolumny "data" z tabeli "tabela 1 – daty". W naszym przypadku będzie to komórka B5 (patrz obrazek). Narazie wskazujemy wartość pierwszej komórki a nie całą kolumną bo będziemy później kopiować wartość formuły w dół dla całej kolumny.

excel funkcja wyszukaj pionowo

Krok 5. Pole "Tabela_tablica"
    Tutaj musimy określić w jakiej tabeli chcemy wyszukiwać naszą wartość którą określiliśmy w kroku 4. Pamiętajmy o tym, że funkcja WYSZUKAJ.PIONOWO wyszuka wskazanej przez użytkownika wartości w pierwszej od lewej kolumnie. Więc jeżemy chcemy wyszukać daty z kolumny "data zamówienia" to musimy wskazać tą kolumnę (ta kolumna musi się znaleźć jako pierwsza od lewej jeżeli zaznaczamy większy obszar niż jedna kolumna).

excel funkcja wyszukaj pionowo

Krok 6. Pole "Nr_indeksu_kolumny"
    Tutaj wskazujemy jakie wartości, z której kolumny w przypadku zaznaczenia więcej niż jednej kolumny w kroku 5, maja być zwracane przez funkcję WYSZUKAJ.PIONOWO. W naszym przykładzie w kroku 5 zaznaczyliśmy tylko jedną kolumnę więc tutaj wpisujemy 1 wskazując tym samym że chcemy zwracać wartość właśnie z tej kolumny.

excel funkcja wyszukaj pionowo

Krok 7. Pole "Przeszukiwany_zakres"
    Zgodnie z opisem funkcji aby znaleźć dokładny odpowiednik szukanej wartości w to pole należy wpisać wartość "FAŁSZ"

excel funkcja wyszukaj pionowo

    Wszystkie wprowadzone argumenty potwierdzamy przyciskiem OK.

Widzimy że w komórce C5 czyli w komórce którą wskazaliśmy w kroku 1 pojawiła się wartość daty z komórki H5. Funkcja WYSZUKAJ.PIONOWO zadziałała następująco. Sprawdziła czy wartość z komórki B5 (wskazana w kroku 4) znajduje się w kolumnie "data zamówienia" w tabeli "tabela 2 – zamówienia". Z uwagi na to że taka sama wartość znajduje się w komórce H5, funkcja zwróciła wartość z tej komórki, zgodnie z konfiguracją w kroku 6.

excel funkcja wyszukaj pionowo

Teraz musimy tylko skopiować formułę z komórki C5 do komórek niżej. Wcześniej jednak musimy zablokować komórki wskazane w kroku 5 bo przy kopiwaniu formuły niżej zmieniałby nam się także zakres w którym będą wyszukiwane nasze wartości. W tym celu ustawiamy się w komórce C5 i w polu edycji formuły dodajemy znaki dolara do drugiego argumentu funkcji WYSZUKAJ.PIONOWO.

z
=WYSZUKAJ.PIONOWO(B5;H5:H14;1;FAŁSZ)

na
=WYSZUKAJ.PIONOWO(B5;$H$5:$H$14;1;FAŁSZ)

teraz możemy skopiwać tą formułę w dół. W tym celu ustawimy się w komórce C5 i "chwytamy" czarny kwadracik który pojawia się w prawym dolnym rogu tej komórki (wtedy kursor w postaci białego plusa zmienia się na czarny plus). Trzymając za ten kwadracić przeciągamy kursor myszy na komórki w dół.

excel funkcja wyszukaj pionowo

Po przeciągnięciu widzimy, że w komórkach C5 i C6 znajdują się daty a w komórce C7 jakieś znaczki #N/D!. Te symbole mówią nam o tym, że dla tej szukanej wartości nie znaleziono odpowiednika w tabeli docelowej (kolumnie "data zamówienia" tabela 2)

Żeby wszytko ładnie wyglądało i funkcja nie zwracała tych śmiesznych znaczków możemy dołożyć funkcję JEŻELI.BŁĄD() i w pierwszym argumencie wpisać całą formułę dotyczącą funkcji WYSZUKAJ.PIONOWO a jako drugi argument (czyli co w momencie kiedy wystąpi błąd) wprowadzić ciąg znaków "BRAK". Wtedy nasza formuła będzie wyglądała tak

z
=WYSZUKAJ.PIONOWO(B5;H5:H14;1;FAŁSZ)

na
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(B5;H5:H14;1;FAŁSZ);"BRAK")

Pamiętaj formułę zmieniamy w pierwszej komórce (C5) i później kopiujemy ją w dół na wszystkie komórki.

Teraz nasz wynik wygląda tak

excel funkcja wyszukaj pionowo

 


db HOTELS 9. Jakie było obłożenie wszystkich hoteli (%) w dniu 2016-10-10

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 9

Treść: Jakie było obłożenie wszystkich hoteli (%) w dniu 2016-10-10

Polecenia/funkcje w zapytaniu: DECLARE, SELECT, COUNT, FROM, WHERE, BETWEEN, CAST(), ROUND()


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

DECLARE @dataSprawdzenia date = '2016-10-10'
DECLARE @ilePokoi int =
    (
        SELECT
            COUNT(*)
        FROM
            rooms
    )
DECLARE @ilePokoiZajetych int =
    (    
        SELECT
            COUNT(*)    
        FROM    
            reservations R    
        WHERE
            @dataSprawdzenia BETWEEN R.reservation_starting_date AND R.reservation_final_date
    )    
DECLARE @oblozenie REAL = 100*@ilePokoiZajetych/@ilePokoi;
SELECT
     'Wszystkich pokoi: '+CAST(@ilePokoi AS NVARCHAR) AS Pokoi
    ,'Zajętych pokoi: '+CAST(@ilePokoiZajetych AS NVARCHAR) AS Zajętych
    ,'Obłożenie pokoi w dn. 2016-10-10 wynosi: '
        +CAST(CAST(ROUND((100*CAST(@ilePokoiZajetych AS NUMERIC)/CAST(@ilePokoi AS NUMERIC)),2) AS NUMERIC(6,2)) AS NVARCHAR)
        +'%' AS Obłożenie

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


 

db HOTELS 8. W jakim hotelu złożono najwięcej rezerwacji które zostały zrealizowane.

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 8

Treść:  W jakim hotelu złożono najwięcej rezerwacji które zostały zrealizowane.

Polecenia/funkcje w zapytaniu: SELECT TOP, Aliasy, COUNT(), FROM, JOIN, WHERE, GROUP BY, ORDER BY


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

SELECT TOP 1
     CO.country_name AS Kraj
    ,CI.city_name AS Miasto
    ,H.hotel_id AS [Nazwa hotelu]
    ,COUNT(*) AS [Ilość złożonych rezerwacji]
FROM
    reservations RE
    JOIN rooms RO ON RE.reservation_room_id = RO.room_id
    JOIN hotels H ON RO.room_hotel_id = H.hotel_id
    JOIN countries CO ON H.hotel_country_id = CO.country_id
    JOIN cities CI ON H.hotel_city_id = CI.city_id
WHERE
    RE.reservation_realized = 1
GROUP BY
    CO.country_name , CI.city_name , H.hotel_id        
ORDER BY
    COUNT(*) DESC

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


 

db HOTELS 7. W jakim hotelu złożono najwięcej rezerwacji.

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 7

Treść:  W jakim hotelu złożono najwięcej rezerwacji.

Polecenia/funkcje w zapytaniu: SELECT TOP, Aliasy, COUNT(), FROM, JOIN, GROUP BY, ORDER BY


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

SELECT TOP 1
     CO.country_name AS Kraj
    ,CI.city_name AS Miasto
    ,H.hotel_id AS [Nazwa hotelu]
    ,COUNT(*) AS [Ilość złożonych rezerwacji]
FROM
    reservations RE
    JOIN rooms RO ON RE.reservation_room_id = RO.room_id
    JOIN hotels H ON RO.room_hotel_id = H.hotel_id
    JOIN countries CO ON H.hotel_country_id = CO.country_id
    JOIN cities CI ON H.hotel_city_id = CI.city_id
GROUP BY
    H.hotel_id , CO.country_name , CI.city_name
ORDER BY
    COUNT(*) DESC

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


 

db HOTELS 6. Który hotel ma najwięcej miejsc parkingowych

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 6

Treść:  Który hotel ma najwięcej miejsc parkingowych

Polecenia/funkcje w zapytaniu: DECLARE, SELECT TOP, COUNT(), GROUP BY, FROM, ORDER BY, WHERE, HAVING


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

DECLARE @max_parking_places int = (
SELECT TOP 1
    COUNT(P.parking_hotel_id)
FROM
    parking P    
GROUP BY
    P.parking_hotel_id
ORDER BY
    1 DESC
);
SELECT
     H.hotel_name AS [Nazwa hotelu]
    ,COUNT(P.parking_hotel_id) AS [Ilość miejsc parkingowych]
FROM
    parking P    
    JOIN hotels H ON P.parking_hotel_id = H.hotel_id
GROUP BY
    P.parking_hotel_id, H.hotel_name
HAVING    
    COUNT(P.parking_hotel_id) = @max_parking_places   

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER