Excel

Niedoceniona funkcja excela – generuj funkcję weźdanetabeli

3 Mar , 2018  

generuj funkcję weźdanetabeli

Patrząc na osoby, które pracują w Excelu, rzadko w ich zestawieniu pojawia się funkcja generuj funkcję weźdanetabeli, a przecież możliwości jej są nieocenione. Warto ją zastosować przy tworzeniu interaktywnych raportów, czy po prostu wyselekcjonowania konkretnej informacji z tabeli przestawnej.

Jak generować funkcję wezdanetabeli?

Najpierw musimy stworzyć na podstawie danych tabele przestawną, upewnić się że mamy włączone generowania automatyczne tej funkcji. Warto zapoznać się również z funkcją przesuniecie.

Zaczynamy!

Zakładam że wiesz jak tworzyć tabele przestawne, wiec ten krok pominę, jeśli nie, możesz skorzystać z tego pliku, w którym jest już gotowa tabela przestawna.
Tabela przestawna pokazuje miesięczne wydatki podzielone na kategorie budżetu domowego.

Sprawdźmy czy mamy włączoną opcję Generuj funkcję WeźDaneTabeli, musimy stać kursorem na tabeli przestawnej, przechodzimy do zakładki Analiza, odnajdujemy ikonę z opisem Tabela przestawna, klikamy i z dostępnej listy wybieramy Opcje klikając na małą strzłke. Pojawia się na samym dole listy funkcja Generuj funkcję WeźDaneTabeli. Jeśli przy tej funkcji jest zaznaczony ptaszek, to oznacza że funkcja jest włączona. Wygląda to tak jak na poniższym screenie.

generuj funkcję weźdanetabeli

Skopiujmy nagłówek tabeli do wiersza 12-stego, a w komórce C13 wpisujemy = i zaznzaczamy komórkę C5

generuj funkcję weźdanetabeli
Pierwszym argumentem tej funkcji jest pole danych, nas interesuje kwota. Kolejnym argumentem jest wskazanie tabeli przestawnej, tu wystarczy wskazać adres w obrębie tabeli przestawnej, domyślnie jest to pierwsza komórka naszej tabeli przestawnej $A$3.
Kolejne pola w formule są to parametry, które możemy dynamicznie zmieniać wskazując komórkę którą będziemy modyfikować.
Zmodyfikujmy naszą formułę tak, aby reagowała na wartość w komórce A13 w której będziemy zmieniać datę. Wprowadźmy w A13 datę „2018-01-01”. Teraz zmodyfikujmy formułę, stajemy na komórce C13 i zaznaczamy obszar w pasku formuł.

wpisujemy w zaznaczony zakres adres naszej daty $A13


Formuła będzie dynamicznie wyświetlać wyniki dla wybranego miesiąca, zmieniając miesiąc w komórce A13.

Kolejny parametr w naszje formule to kat1 (kategoria), należy postąpić tak jak zrobliśmy to z datą.
Podmieniamy słowo „dzieci” na adres C12.
Pozostało nam skopiować formułę do pozostałych komórek i cieszyć się wynikiem.
Jeśli pojawił Ci się błąd sprawdź czy masz dobrze zadolarowane adresy komórek: $A13 i C$12 w komórce C13.

Modyfikując komórkę A13 na listę, mamy już ciekawy efekt zmiany wartości w poszczególnych kategoriach w zależności od wybranej daty.

 

Przykład poniżej do ściągnięcia

WezDaneTabeli.xls

 

, , , , , , ,

VBA

Excel VBA – makro łączenie plików excel

2 Mar , 2018  

makro łączenie plików excel

Makro łączenie plików excel , kilka linijek kodu VBA pozwala zaoszczędzić wiele cennego czasu. Inspiracją była koleżanka z pracy, która miała taki case. Otrzymywała pliki o tej samej strukturze w formie csv, każdego dnia od dostawcy, zawierające informacje np. o przesyłkach. Wciągu miesiąca plików uzbierało się ponad 20. Importowanie każdego oddzielnie pochłania mnóstwo czasu. Z pomocą przychodzi VBA, kilka linijek kodu poniżej, wystarczy przestudiować. W załączeniu jest gotowy plik do pobrania.

Jak scalić pliki csv makro łączenie plików excel?

Jak to działa?

  1. Umieszczamy plik scala.xlsm w katalogu z plikami csv.
  2. Otwieramy plik scala.xlsm, naciskamy baton „Scal CSV”
  3. I gotowe, powstaje nowy plik o nazwie „scalone.csv”, który zawiera dane ze wszystkich plików o rozszerzeniu CSV mieszczących się w folderze.

scala.xlsm

zamowienie_26_02_2018.csv

zamowienie_27_02_2018.csv

zamowienie_28_02_2018.csv

zamowienie_25_02_2018.csv

 

 

Zachęcam to pisania komentarzy. Jeśli ktoś uzna, że warto by rozszerzyć działanie makra do zachęcam do kontaktu najlepiej drogą email.

 

Jeśli podobał Ci się artykuł to daj Lajka, w ten sposób pomożesz dotrzeć innym do tego artykuły. Zachęcam Cie również do zapisania się na newsletter, znajdziesz go po prawej stronie strony.

 

Statystyka

Cała prawda o średniej – średnia arytmetyczna

22 Sie , 2017  

Często mamy do czynienia ze średnią arytmetyczną, ale pod tym pojęciem kryje się kilka statystyk: średnia arytmetyczna,  mediana. Którą kiedy stosować statystykę, zapraszam do lektury 🙂

Wyobraźmy sobie że są dwie firmy z tej samej branży i staramy się wybrać firmę która lepiej wynagradza pracowników.

Z każdej firmy znamy zarobki na wszystkich stanowiskach, zakładamy że obie firmy zatrudniają 6 pracowników.

Firma A

Lp Stanowisko Zarobki
1 stanowisko1 4100zł
2 stanowisko2 4200zł
3 stanowisko3 4400zł
4 stanowisko4 4300zł
5 stanowisko5 4300zł
6 stanowisko6 20000zł

Firma B

Lp Stanowisko Zarobki
1 stanowisko1 5000zł
2 stanowisko2 5100zł
3 stanowisko3 5500zł
4 stanowisko4 5200zł
5 stanowisko5 5200zł
6 stanowisko6 13000zł

Średnia arytmetyczna

Liczymy średnia zgodnie ze wzorem:

    \[ \mu=\frac{\Sigma x}{n} \]

    \[ \mu=\frac{suma ~zarobk\'ow}{liczba ~pracownik\'ow} \]

 

Firma A

    \[ \mu=\frac{41300}{6}=6883 ~z\l{} \]

Firma B

    \[ \mu=\frac{39000}{6}=6500 ~z\l{} \]

Po wyliczeniu średniej stwierdzamy że Firma A płaci więcej od Firmy B swoim pracownikom. I tu średnia arytmetyczna wprowadziła nas w błąd, bo jak widać w danych detalicznych (tabele) nie jest to prawdą. Dlaczego tak się stało? Średnia arytmetyczna jest wrażliwa na dane odstające, w naszym przypadku tą daną jest wartość przy stanowisku 6 (zarobki prezesa firmy) wielokrotnie wyższe od pozostałych stanowisk.

Jak poradzić sobie z taką sytuacja? Przed użyciem średniej arytmetycznej warto poznać rozkład danych za pomocą histogramu, a dlaczego bo średnią arytmetyczną warto stosować, gdy dane są względnie symetryczne (brak danych odstających).

srednia arytmetyczna, mediana

Mediana

Na ratunek przychodzi nam mediana, która jest niewrażliwa na odstające wartości.  Mediana znajduje się zawsze po środku danych. Jest to wartość środkowa.

Jak liczymy medianę?

Aby znaleźć medianę należy uporządkować dane w kolejności rosnącej, a następnie wybrać wartość, która znajduje się dokładnie po środku. Przy parzystej liczbie danych tak jak w naszym przypadku (6 wartości), mediana jest średnia arytmetyczna dwóch środkowych wartości (a więc suma podzielonej przez 2).

Firma A

srednia arytmetyczna

Firma B

srednia arytmetyczna

Teraz widać że Firma B wynagradza lepiej swoich pracowników od Firmy A.

W tym przypadku rozkład danych sprawia że mediana okazał się lepszą statystyką, ale nie należy traktować mediany jako lekarstwo na całe zło, bo są również takie zbiory danych w których i mediana sobie nie poradzi, ale o tym w kolejnym artykule.

 

, , ,

Excel

Warunkowe blokowanie komórek w excelu – chroń komórki Excela!

2 Wrz , 2016  

warunkowe blokowanie komórek w excelu

Warunkowe blokowanie komórek w excelu – chroń komórki Excela!

Chcemy chronić wszystkie komórki arkusz  lub warunkowe blokowanie komórek (wybrane komórki). Jak się za to zabrać?

W Excelu mamy taką funkcjonalność możemy chronić komórki w arkuszu,  na początek trzeba zrozumieć, że wszystkie komórki domyślnie są zablokowane w momencie, gdy chronimy arkusz lub cały skoroszyt. Najpierw musimy odblokować wszystkie komórki, następnie zablokować wybrane komórki i włączyć ochronę.

Do dzieła!

Na zakładce „Narzędzia główne” klikamy na polecenie Format:

warunkowe blokowanie komórek w excelu

Z rozwijanego menu na dole mamy dwa polecenia: „Chroń arkusz…” i „Zablokuj komórkę”

Drugie polecenie blokuj_komorke „Zablokuj komórkę” domyślnie jest włączone, tzn. wszystkie komórki są zablokowane.  Ktoś powie przecież ja mogę w każdej komórce zmieniać dane, owszem ma rację. To polecenie działa dopiero, gdy włączymy polecenie chron_arkusz „Chroń arkusz...”.

Warunkowe blokowanie komórek w excelu

Jeśli chcemy zablokować tylko wybrane komórki (warunkowe blokowanie) to wpierw należy wyłączyć domyślnie zablokowane wszystkie komórki naciskając kombinację CTRL+A  (zaznaczymy wszystkie komórki w arkuszu), następnie wyłączamy odblokuk komorki(widać znikło obramowanie wokół ikony).

Kolejny krok to wybieramy komórki do zablokowania, w naszym przykładzie zablokujemy komórki od D8:D20. Chcemy zablokować, są tam wprowadzone formuły i nie chcemy aby była możliwość ich modyfikacji.

warunkowe blokowanie komórek w excelu

Zaznaczamy komórki D9:D20 z meny „Narzędzia główne” wybieramy polecenie „Format” i z listy klikamy na polecenie „Zablokuj komórkę”.

warunkowe blokowanie komórek w excelu

W kolejnym kroku znów z  meny „Narzędzia główne” wybieramy polecenie „Format” i z listy klikamy na polecenie „Chroń arkusz„.

warunkowe blokowanie komórek w excelu

Pojawia się nam nowe okno, gdzie zatwierdzamy przyciskiem „OK„.

warunkowe blokowanie komórek w excelu

 

Możemy również w polu „Hasło do usunięcia ochrony arkusza:”  wprowadzić hasło, które zabezpieczy nas przed wyłączeniem ochrony wybranych komórek.

Efekt końcowy: w przypadku próby modyfikacji zablokowanych komórek pojawia nam się komunikat:

blokowanie_8

Przykład poniżej:

excel ochrona komórek

Miesieczne_koszty.xlsx

Miesieczne_koszty.xlsx

, , , ,

Excel,Triki

Funkcja przesuniecie

9 Sie , 2016  

Jedną z ulubionych moich funkcji w MS Excel jest funkcja PRZESUNIĘCIE. Można ją wykorzystać na wiele sposobów, najpopularniejszy sposób to dynamicznie zmieniający się wykres.

Efekt końcowy widać na poniższym rysunku:
przesuniecie_4

 

Co trzeba wiedzieć o funkcji aby móc z niej skorzystać?

Funkcja PRZESUNIĘCIE potrzebuje 5 parametrów, gdzie 2 są opcjonalne.

Składnia
PRZESUNIĘCIE(odwołanie;wiersze;kolumny;[wysokość];[szerokość])

Odwołanie – Argument wymagany. Konkretna komórka w Excelu, od której będzie wskazywany zakres komórek lub komórka.

Wiersze – Argument wymagany. O ile wierszy należy się przesunąć od komórki, aby dostać się do żądanego zakresu lub komórki.

Kolumny – Argument wymagany. O ile kolumn należy się przesunąć od komórki, aby dostać się do żądanego zakresu lub komórki.

Wysokość – Argument opcjonalny. Liczba wierszy, z których składa się zakres, który chcemy pobrać.

Szerokość – Argument opcjonalny. Liczba kolumn, z których składa się zakres, który chcemy pobrać.

Tyle teorii zaczynamy część praktyczną!

1. Najlepiej poniższą tabele skopiować do excel’a lub pobrać plik dane_przesuniecie.xlsm

Rok Messi Ronaldo
 2015/2106  41 51
 2014/2015  58  61
 2013/2014  41  51
 2012/2013  60  55
 2011/2012  73  60
 2010/2011  53  53
 2009/2010  47  33

 

2. Przechodzimy do menu Developer rozwijamy Wstaw i wybieramy narzędzie Przycisk pokrętła

przesuniecie_1

3. Stojąc kursorem na przycisku pokrętła, prawym klawiszem myszy wybieramy polecenie przesuniecie_2

przesuniecie_1

4. Pojawia się nam nowe okno gdzie w polu Łącze komórki wprowadzamy wartość: = $E$1, oraz ustawiamy wartość minimalną i maksymalną.

przesuniecie_3

Efekt końcowy – klikając na pokrętło zmienia nam się dynamicznie wartość w komórce E1.  Przycisk pokrętła jest już gotowy do wykorzystania go w funkcji przesunięcie.

5. Przechodzimy do funkcji przesunięcie, ustawiamy aktywną komórkę E2, i wprowadzamy w pasku formuł polecenie „=PRZESUNIĘCIE(A2;0;$E$1;1;1)” i naciskamy enter.

przesuniecie_4

 

Wyjaśnienie formuły:

przesuniecie_5

Żeby obrazowo wyjaśnić o co chodzi z tym zakresem przesuniecie_6 skopiujmy formułę z komórki E2 do E3.

 

 

W komórce E1 mamy wartość 1 wiec wyświetlają nam się dane z pierwszej kolumny od kolumny A.

przesuniecie_7

Jeśli zmodyfikujemy formulę w komórce E3 na: =SUMA(PRZESUNIĘCIE(A2;0;$E$1;1;2)), pamiętamy że ostatni parametr steruje szerokością. W tym przypadku podaliśmy 2 czyli 2 kolumny, wówczas efekcie końcowym będziemy mieli zsumowane dwie wartości 41 + 51 = 92

przesuniecie_8

Koniec wyjaśnień na temat formuły.

 

6. Stajemy na komórce E2 i przeciągamy formułę do komórki E9

przesuniecie_2

 

Kręcąc pokrętłem mamy efekt końcowy czyli zmieniają nam się wartości w kolumnie E. Wystarczy teraz zaznaczyć ten zakres plus zakres z kolumny A i wstawić wykres, który będzie zmieniał się dynamicznie.

przesuniecie_3

 

7. Do zmiany zdjęcia został wykorzystany prosty kod vba podpięty do pokrętła:

Gotowy plik do pobrania: przesuniecie.xlsm

 

 

, ,

VBA

Wysłanie e-maila z Excela (LotusNotes)

17 Mar , 2015  

Wysłanie e-maila z Excela obsługa poczty Lotus Notes.

W dzisiejszym krótkim artykule przedstawię jak można wysłać e-maila bezpośrednio z poziomu Excela.
Napisane makro obsługuję pocztę Lotus Notes. Można podejrzeć kod vba kilkanaście linijek.
Jak to działa:
Na front zostały wyniesione parametry typu:
– temat e-maila (komórka E4)
– treść e-maila (komórka E5)
– ścieżka załącznika (komórka E3)
– lista adresatów (komórki: =$A$3:$A$5)

gdzie łatwo można bez zaglądania do kodu przystosować wysyłkę do własnych potrzeb. Aby wysyłka odbyła się musimy mieć otwartego LotusNotes.

Wysyłka emaila z poziomu Excela

Wysyłka e-mail z Excela

,

Busienss Inteligence

BI – MicroStrategy Analytics Desktop – za darmo

31 Gru , 2014   Video

Kolejnym krokiem po rozwiązaniach Busieness Inteligence jest Business Analytics. Firma MisroStrategy wypuściła na rynek bezpłatne zaawansowane narzędzie MicroStrategy Analytics Desktop, które pozwala zamienić dane w użyteczną wiedzę.

Jeśli chcesz przedstawić np. wyniki finansowe firmy w wizualny sposób wykorzystując wszelakie typy wykresów niedostępnych nawet w MS Excel takie jak mapa temperatur, mapy geograficznej czy  sieci to narzędzie MicroStrategy Analytics Desktop jest warte uwagi.

Na wstępie warto zapoznać się z filmem pokazującym możliwości narzędzia, którego umieściłem w nagłówku tego artykułu.

Krok po korku przygotujemy analizę z warstwą wizualną, prace podzielimy na kilka etapów:

  1. instalacja MicroStrategy Analytics Desktop
  2. połączenie danych z MicroStrategy Analytics Desktop
  3. stworzenie Dashboard’u
  4. publikacja raportu

Zaczynamy 🙂

  1.  Instalacja MicroStrategy Analytics Desktop

  2. Pobieramy instalkę ze strony www.Microstrategy.pl gdzie należy podać adres email. Adres email musi być firmowy nie przejdzie z sufiksem: o2.pl, gmail.com, wp.pl etc. Plik waży 320 MB. Sama instalacja jest intuicyjna „Next, Next, Next…” Po zainstalowaniu na naszym pulpicie pojawi się skrót uruchamiamy program klikając na skrót.
    ikona_microstrategy

     

     

     

    Program jest wersją webową i uruchomi się w domyślnej przeglądarce pod adresem: https://127.0.0.1:8082/servlet/mstrWeb.

     

    web_microstrategy
     
     

  3.  Połączenie danych z MicroStrategy Analytics Desktop

    Możemy zaimportować dane w formacje: excela, csv, txt, oraz połączyć się z bazą za pomocą ODBC lub poprzez DSN z każdym typem bazy typu oracle, Postgre SQL, SQL Serwer, Web Serwices, Microsoft Access itp.. Mało tego możemy wprowadzić zapytanie SQL, które pozwoli nam ograniczyć się do wybranych danych.

    • Klikamy na Utwórz dashboard
    • utworz_dashboard
       
       
       
       
       

    • Klikamy na Importuj dane
    • dane_zrodlowe3

    • Wybieramy źródło danych
    • zrodlo_danych

      W przykładzie użyjemy danych z Głównego Urzędu Statystycznego dotyczących zawartych i rozwiązanych małżeństw w Polsce w latach 1970 do 2013. Plik można pobrać bezpośrednio ze strony (https://stat.gov.pl/obszary-tematyczne/ludnosc/ludnosc/struktura-ludnosci-do-2013-r-,16,1.html) lub klikając na ikonę MS Excela poniżej. Dane zostały ułożone w formie tabeli, co pozwoli nam łatwo zaimportować plik.

      xl

    • Naciskamy Przeglądaj i wybieramy plik z katalogu gdzie został zapisany po pobraniu, lub podajemy bezpośredni adres strony:
    •  https://www.strefaanalityka.pl/download/malzenstwa_zawarte_rozwiazane_lata_1970_2013.xls

      dane_zrodlowe

       

    • Wciskamy baton Kontynuuj mieszczący się w prawym dolnym rogu. Pojawia się nam nowe okno, gdzie najistotniejszą rzeczą jest typ danych jaki został automatycznie zdefiniowany przez aplikację. Należy szczególną uwagę zwrócić na typ danych typu Liczba. Może się zdarzyć ze automatycznie kolumnę reprezentującą lata w formie samej liczby roku narzędzie MicroStrategy Analytics Desktop zdefiniuje jako typ Liczba. Typ Liczba w nomenklaturze MicroStrategy nazywamy metrykami, a opisujące te liczby dane nazywamy atrybutami. Klikając na poszczególny nagłówek możemy zmieniać typ danych, zmienić nazwę pola lub wskazać aby dana kolumna nie była importowana.
    • dane_zrodlowe2

      typ

    • Wciskamy baton Kontynuuj i pojawią się okno z możliwością zapisania zestawu danych. Podajemy nazwę i wsiskamy OK.
    • dane_zrodlowe3

       

       

    • Po zapisaniu zestawu danych naszym oczom pojawia się komunikat tego typu, co zwiastuje o zakończeniu procesu importu danych.
  4. dane_zrodlowe4

     

    Przechodzimy teraz do kolejnego przedostatniego kroku – stworzenia dashboardu.

  5.  Stworzenie Dashboard’u

    •  Klikamy na ikonę Utwórz dashboard
    • create_dashboard

       

       

       

       

       

    • Pojawia się kolejne okno, gdzie będziemy pracować nad wizualizacją danych.
    • widok_ms

       

       

       

       

       

       

       

       

       

       

    • Dodajemy wizualizację, klikając na Wybierz Wizualizacje
    • wizualizacja

    • Do dyspozycji mamy gamę różnych typów wizualizacji, wybieramy wizualizację Wykres bąbelkowy
    • wyk_bombelkowy

    • Automatycznie program wstawi metryki i atrybuty w odpowiednie pola i pojawi nam się tego typu wykres, z którego już możemy wywnioskować, że najwięcej rozwodów jest w miastach w stosunku do zawieranych związków.
    • wyk_bombelkowy1

       

       

    • Metodą drag and drop przeciągamy atrybuty i metryki w odpowiednie pola. Możemy również dodać kolejną wizualizację i w efekcie końcowym możemy uzyskać taki dashboard.
    • dashboard1
       

  6.  Dystrybucja dashboardu

    Możemy eksportować dashboard do postaci PDF, formatu MicroStrategy lub obrazu PNG.

    Dashboard jest interaktywny, eksportując do PDF nie tracimy tej możliwości.

     

    Małżeństwa zawarte i rozwiązane w latach 1970 do 2013

    Małżeństwa zawarte i rozwiązane w latach 1970 do 2013

MicroStrategy Analizy Desktop to szybki, wygodny i piękny sposób aby analizować dane i dzielić się swoimi spostrzeżeniami. To samoobsługowe rozwiązanie analiz biznesowych ma na celu pomóc w intuicyjny sposób odkrywanie danych bez pomocy ekspertów. MicroStrategy Analizy Desktop pozwala na dostęp do danych, do wiedzy za pomocą atrakcyjnych wizualizacji danych, organizowanie informacji w interaktywnych kokpitów i dostarczania wyników poprzez e-mail, bez złożonych skryptów.
 
Nie wymaga specjalnego szkolenia lub doświadczenia, jest przeznaczony dla ludzi biznesu, którzy muszą wizualizować, analizować i dostarczać danych na własną rękę, ale które nie posiadają niezbędne narzędzia, doświadczenie techniczne lub zasobów informatycznych.
 
Spróbuj sam i przekonaj się, jak łatwo można analizować i prezentować dane.

 
Zachęcam do obejrzenia filmu prezentującego bardziej obrazowo przygotowanie dashboardu MicroStrategy Analytics Desktop.

httpss://www.youtube.com/playlist?list=PLu4_Ge2aZ8ooSOcYH8otxT-ZaW_TRd4Vf

, ,

SQL

SQL – funkcja rankingowa RANK()

26 Gru , 2014  

W systemach relacyjnych baz danych od dawna już zawitały mechanizmy analityczne, które pozwalają z poziomu bazy za pomocą funkcji analitycznych takich ja np. rank(), dense_rank() przetwarzać dane bez konieczności stosowania dodatkowego oprogramowania.  W tym artykule zajmiemy się jedną z funkcji rankingowych bez której nie wyobrażam sobie codziennej pracy 🙂

Do funkcji rankingowych zaliczamy takie funkcje jak: RANK(), DENSE_RANK(), CUME_DIST(); PERCENT_RANK(), NTILE(), ROW_NUMBER().

Ogólna składnia użycia funkcji rankingowych jest następująca:

funkcja_rankingowa() over ([partition by kolumna_1] order by kolumna_2 [desc] [nulls first | last])

gdzie:

zapis w nawiasach kwadratowych [] jest opcjonalny, nie musi występować a funkcja zadziała poprawnie.

funkcja_rankingowa() – nazwa funkcji rankingowej, np RANK()

partition by kolumna_1 – podajemy nazwę kolumny/kolumn po której dzielimy tabele na obszary.

Obrazowo można powiedzieć ze dzielimy jedną tabele na „x” małych tabel i funkcja działa niezależnie na każdą z nich. Jeśli nie użyjemy wyrażenia partition by to rankingowi podlegają wszystkie rekordy tabeli.

kolumna_2 – kolumna po której sortujemy kolejność rekordów

desc – sortuje rekordy od największej do najmniejszej, domyślnie użyte jest wyrażenie asc która działa odwrotnie.

nulls first – powoduje że wartości puste trafiają na początek rankingu

nulls last –  powoduje że wartości puste trafiają na koniec rankingu Funkcje rankingu wyznaczają pozycję danego rekordu porównując go z wartościami innych rekordów w określonym zbiorze danych. Podział zbioru danych pozwala na stworzenie oddzielnych rankingów dla każdego zbioru. To tyle teorii a teraz przejdźmy do przykładu.

Przykład Wyobraźmy sobie taką sytuacje, przychodzi do nas Boss i prosi nas o listę dwóch pracowników, którzy w ramach danego stanowiska zarabiają najwięcej. Najlepszą metodą będzie użycie funkcji rank(). Owszem ktoś powie, że można to zrealizować za pomocą tabeli przestawnej w MS Excelu, ale po co sięgać do dodatkowych narzędzi skoro z poziomu bazy może również to zrealizować, ba mało tego funkcja rank() bardzo często przydaje się w innych zastosowaniach np. z użyciem funkcji merge ale o tym w innym artykule. Lista pracowników wygląda tak jak poniżej. Wszystkie zapytania działają na przykładowej bazie, którą przygotowałem i jest zbliżona do przykładowej bazy HR dostępnej podczas instalacji Oracl’a. Moją bazę po liftingu możesz pobrać tu. zapytanie:

SQL - funkcja rankingowa RANK()

4 i 5 rekord pokazuje, że na tym samym stanowisku „Kierownik Produktu” jest dwóch pracowników którzy zarabiają tyle samo. Dodajmy teraz do zapytania funkcję RANK() i DENSE_RANK()

 

 

SQL - funkcja rankingowa RANK()

 Czym się różni funkcja RANK() od DENSE_RANK()?

Różnica pomiędzy tymi funkcjami jest taka, że w sytuacji gdy rekordy znajdują się na pozycji ex aequo powstaje przerwa w numeracji, gdy używamy funkcji RANK(). Nie dzieje się tak dla funkcji DENSE_RANK() co bardzo dobrze widać na powyższym rysunku. Nasz Boss poprosił nas o listę 2 pracowników najlepiej zarabiających na każdym stanowisku. Musimy ograniczyć się do rekordów gdzie w kolumnie RANK jest wartość nie większa od liczby 2. Aby tego dokonać musimy obrać nasze zapytanie w dodatkowego select’a z warunkiem „RANK” < =2, dlaczego? A no dlatego bo funkcje analityczne działają na rekordach będących wynikiem zapytania. Działanie funkcji analitycznych odbywa się na samym końcu po sortowaniu i przed takimi działaniami jak: grupowanie, łączenie tabel, warunku where , warunku having.

, ,

SQL

SQL – instalacja Oracle 11g XE

14 Gru , 2014  

W dzisiejszym artykule przedstawie krok po kroku jak zainstalować darmową bazę danych Oracle 11g XE z możliwością wykorzystania komercyjnie. Wersja darmowa ma pewnie ograniczenia:

  • 11GB danych użytkownika,
  • 1GB RAM,
  • 1 CPU

Jest to dobre rozwiązanie dla małych i średnich firm, oraz dobra baza do testowania różnych rozwiązań w celach edukacyjnych. Warto ją znać bo większe firmy stosują głównie rozwiązania Oracle. Ranking popularności możecie znaleźć na stronie: https://db-engines.com/en/ranking

Dla potrzeb dydaktycznych warto zainstalować bazę danych na wirtualnej maszynie. Jeśli nigdy nie słyszałeś o wirtualnej maszynie zajrzyj tu. Jednym słowem jest to komputer w komputerze. Pozwala nam to uruchamiać bazę tylko wtedy kiedy jej potrzebujemy i nie obciąża na co dzień komputera. Druga sprawa możemy przetestować realne środowisko klient-serwer. Pomijam krok instalacji wirtualnej maszyny bo nie jest to przedmiotem tego artykułu, zabieramy się za postawienie bazy lokalnie ma komputerze z system operacyjnym Windows. Wersja systemu operacyjnego Windows nie ma większego znaczenia logika działania jest taka sama. A więc zaczynamy.

Co będzie nam potrzebne?

Całą zabawę podzielimy na 3 obszary:

  • Instalacja i konfiguracja karty sieciowej Microsoft Loopback
  • Instalacja bazy danych Oracle 11g XE
  • Instalacja Oracle Data Access Components (ODAC)

Kolejność jest istotna wiec zabieramy się najpierw za konfiguracje karty sieciowej.

 

Instalacja i konfiguracja karty sieciowej Microsoft Loopback

Zanim rozpoczniemy instalacje bazy należy zainstalować kartę sieciową Loopback. Wirtualna karta sieciowa pozwala na skonfigurowanie procesu nasłuchu systemu zarządzania bazą danych Oracle tak, aby możliwa była praca z bazą w przypadku odłączenia komputera od serwera DHCP przydzielającego adresy IP w sieci.

W zależności od wersji systemu Windows polecenia mogą się różnic, dla Windows 7 przechodzimy do Panelu Sterowania i wybieramy Menadżer urządzeń. W oknie zaznaczamy Karty sieciowe i w górnym menu wybieramy polecenie Akcja następnie Dodaj starszy sprzęt. Pojawia się okno Kreatora

SQL - instalacja Oracle 11g XE

Przechodzimy dalej i zaznaczamy pole „Zainstaluj sprzęt, który wybiorę ręcznie z listy„.

SQL - instalacja Oracle 11g XE

W kolejnym oknie wybieramy Karta sieciowa

SQL - instalacja Oracle 11g XE

W kolejnym oknie oszukujemy Kartę Microsoft Loopback

SQL - instalacja Oracle 11g XE

Następnie naciskamy dwa razy Dalej i karta sieciowa została zainstalowana na komputerze, jedyne co nam zostało to ją skonfigurować.

 

Konfiguracja protokołu internetowego (TCP/IP) karty Microsoft Loopback

Przechodzimy ponownie do Panelu Sterowania/Sieć i Internet/Połączenia sieciowe odnajdujemy w/w kartę.

SQL - instalacja Oracle 11g XE

Prawym myszki wybieramy właściwości. W oknie właściwości Karty Microsoft Loopback wybieramy Protokół internetowy (TCP/IP)

SQL - instalacja Oracle 11g XE

W zakładce ogólne wybieramy opcję Użyj następującego adresu IP i wprowadzamy dowolny adres lokalny np: 10.10.10.10 z maską podsieci 255.255.255.0. Przyciskamy OK w oknie Właściwości.

SQL - instalacja Oracle 11g XE

Jedyne co trzeba jeszcze zrobić to aktualizacja wpisu w pliku hosts. Przy pomocy notatnika otwieramy plik hosts z folderu C:\Windows\system32\drivers\etc. W nowym wierszu wprowadzamy adres IP zdefiniowany dla karty Microsoft Loopback oraz pełną nazwę komputera odczytaną z Właściwości systemu dla skrótu Mój komputer.

SQL - instalacja Oracle 11g XE

Zapisujemy wprowadzone zmiany w pliku hosts i uruchamiamy ponownie komputer.

 

 Instalacja bazy danych Oracle 11g XE

Ściągnięty plik rozpakowujemy i uruchamiamy instalację.

pliki

pojawia się nam okno  przygotowanie do instalacji

SQL - instalacja Oracle 11g XE

……

 

Instalacja Oracle Data Access Components (ODAC)

 

Po pobraniu i rozpakowaniu plików instalacyjnych ODAC należy uruchomić instalację. Link poniżej:

https://www.oracle.com/technetwork/topics/dotnet/downloads/index.html

Pierwsze okno które na się pojawi:

 

SQL - instalacja Oracle 11g XE odac

Następnie pojawi się okno, gdzie klikamy Next

SQL - instalacja Oracle 11g XE

Pojawia się okno w którym wybieramy katalog docelowy instalacji klienta Oracle, najlepiej aby była to inna partycja dysku od instalacji serwera, np dysk D

SQL - instalacja Oracle 11g XE ODAC

Z listy poniżej w oknie wybieramy pakiety które chcemy aby były zainstalowane dla naszych potrzeb wystarczy Oracle Instant Client 12.1.0.1.0 i wybieramy Next

SQL - instalacja Oracle 11g XE

Kolejne okno pozwala nam na konfiguracje pliku tnsnames.ora, wypełniamy poszczególne pola. Pamiętaj ze zawsze może je edytować w pliku.

SQL - instalacja Oracle 11g XE

 

Kolejne okno pokazuje nam co będzie instalowane, pozostaje nam tylko nacisnąć Install

SQL - instalacja Oracle 11g XE

Czekamy cierpliwie aż instalacja dotrze do końca.

SQL - instalacja Oracle 11g XE

I czekamy na informacją, że dotarliśmy do końca instalacji.

 

Konfiguracja nasłuchu (Oracle Listener)

Biblioteki ODAC instalowane są wraz z klientem bazy danych, obsługującym komunikację pomiędzy serwerem oraz aplikacją kliencką. Po zainstalowaniu ODAC należy skonfigurować proces nasłuchu klienta Oracle tak, umożliwić komunikację pomiędzy klientem a serwerem bazy danych. Najlepiej jest skopiować pliki konfiguracyjne procesu nasłuchu serwera i zapisaniu ich jako pliki konfiguracyjne procesu nasłuchu klienta Oracle.

W tym celu należy przejść do katalogu głównego  instalacji serwera Oracle (np. C:\oraclexe) i przejść do do katalogu z plikami konfiguracyjnymi procesu nasłuchu serwera: C:\oraclexe\app\oracle\product\11.2.0\serwer\network\admin. Zaznaczamy i kopiujemy pliki konfiguracyjne: listener.ora oraz tsnames.ora.

Następnie wybieramy katalog główny instalacji klienta ODAC (np. D:\oracle) i przechodzimy do katalogu z plikami konfiguracyjnymi procesu nasłuch klienta: D:\oracle\product\11.0.2\client_1\network\admin. Dla bezpieczeństwa stwórzmy kopie zapasowe plików konfiguracyjnych zmieniając rozszerzenie plików listener.ora oraz tnsnames.ora na listener.ora.bak oraz tnsnames.ora.bak. Zapisujemy skopiowane pliki konfiguracyjne serwera w katalogu klienta.

 

, , , ,

Excel,Triki

Excel – konwertowanie liczb przechowywanych jako tekst

7 Gru , 2014  

Często zdarza się, że otrzymujemy dane z innego źródła, lub wklejamy dane do Excela i liczby nie są traktowane jak prawdziwe liczby. Próbując wykonać działania matematyczne na tych liczbach nie mamy zamierzonego efektu. Widok tych liczba, które są traktowane jako tekst widać poniżej:

Excel - konwertowanie liczb przechowywanych jako tekst

Jak wiadomo tekst domyślnie wyrównany jest to lewej strony komórki, zaś liczby do prawej. W lewym górnym roku pojawia się w Excelu pomocna funkcja, która umożliwia konwertowanie liczb przechowywanych jako tekst na liczbę:

Excel - konwertowanie liczb przechowywanych jako tekst

 

Excel - konwertowanie liczb przechowywanych jako tekst

Efekt końcowy jest widoczny poniżej, liczby przechowywanych jako tekst są już liczbami i można wykonywać na nich operacje matematyczne.

Excel - konwertowanie liczb przechowywanych jako tekst

Inna metodą jest wymnożenie wartości przez wartość 1 i liczby przechowywanych jako tekst zostaną przekonwertowane na liczby:

Excel - konwertowanie liczb przechowywanych jako tekst Excel - konwertowanie liczb przechowywanych jako tekst

 

,