Miesięczne archiwum: Czerwiec 2017

EXCEL funkcja ŚREDNIA()

28 czerwca 2017EXCELexcel, funkcja, średniaibs_sel_nanonco

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()

28 czerwca 2017EXCELexcel, funkcja, sumaibs_sel_nanonco

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)

27 czerwca 2017HOTELS, SQLbaza, database, hotele, hotels, kurs, sql, tutorialibs_sel_nanonco

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

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

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

25 czerwca 2017HOTELS, SQLbaza, database, hotele, hotels, kurs, sql, tutorialibs_sel_nanonco

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

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

EXCEL funkcja WYSZUKAJ.PIONOWO

22 czerwca 2017EXCELexcel, funkcja, wyszukaj.pionowoibs_sel_nanonco

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

22 czerwca 2017HOTELS, SQLbaza, database, hotele, hotels, kurs, sql, tutorialibs_sel_nanonco

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()

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

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

22 czerwca 2017HOTELS, SQLbaza, database, hotele, hotels, kurs, sql, tutorialibs_sel_nanonco

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

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

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

18 czerwca 2017HOTELS, SQLbaza, database, hotele, hotels, kurs, sql, tutorialibs_sel_nanonco

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

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

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

17 czerwca 2017HOTELS, SQLbaza, database, hotele, hotels, kurs, sql, tutorialibs_sel_nanonco

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

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

Ikona wózek z paczką. Icon trolley with pack.

16 czerwca 2017darmowe ikony free icons, zakupy shoppingbox, icon, ikona, pudełkoibs_sel_nanonco

Ikona wózek z paczką. Icon trolley with parcel.

PNG 512x512px

SQL C# CSharp ASP.NET MVC Android kurs tutorial darmowe ikony

blog o: programowaniu w: SQL, T-SQL, PostgreSQL, C#, CSharp, ASP.NET MVC. Inne: darmowe ikony, dla dzieci, zdrowie i uroda, świadomy konsument. Wejdź.

Miesięczne archiwum: Czerwiec 2017

EXCEL funkcja ŚREDNIA()

EXCEL funkcja ŚREDNIA

EXCEL funkcja SUMA()

EXCEL funkcja SUMA

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

SQL tutorial. db Hotels 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.

EXCEL funkcja WYSZUKAJ.PIONOWO

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.

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

SQL tutorial. db Hotels exercises. SQL SERVER.

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

SQL tutorial. db Hotels exercises. SQL SERVER.

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

SQL tutorial. db Hotels exercises. SQL SERVER.

Ikona wózek z paczką. Icon trolley with pack.

Ikona wózek z paczką. Icon trolley with parcel.