Sorry, no posts matched your criteria.
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.
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.
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.
Skopiujmy nagłówek tabeli do wiersza 12-stego, a w komórce C13 wpisujemy = i zaznzaczamy komórkę C5
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
excel, excel pobieranie danych z tabeli, excel wezdanetabeli, funkcja dla analityków, funkcje, generuj funkcję weźdanetabeli, niedoceniona funkcja excela, weź dane tabeli
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 to działa?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
Sub scalaj_csv() Dim NazwaPlik As String ' zdefiniowanie zmiennej do przechowywania nazwy pliku Dim Lokalizacja As String ' zdefiniowanie zmiennej do przechowywania scieżki do katalogu Dim wbWyniki As Workbook ' skoroszyt gdzie będą wyniki Dim wbCSV As Workbook ' oryginalne skoroszyty z których będziemy kopiować Dim wiersz As Integer ' zdefiniowanie zmiennej typu liczbowego Lokalizacja = ThisWorkbook.Path & '\' 'Lokalizacja pliku NazwaPlik = Dir(Lokalizacja & "*.csv*") 'pobranie nazwy pliku csv Application.ScreenUpdating = False 'wyłączenie odświeżania Application.EnableEvents = False 'wyłaczenie obsługi zdarzeń Set wbWyniki = Workbooks.Add(xlWorksheet) 'storzenie nowego skoroszytu gdzie będziemy zapisywać i = 1 ' przypisanie zmiennej i wartości 1 ' uruchomienie pętli, która będzie się wykonywała aż warunek bedzie spełniony Do While NazwaPlik <> vbNullString Set wbCSV = Workbooks.Open(Filename:=Lokalizacja & NazwaPlik, ReadOnly:=True) 'otwieranie pliku csv NazwaPlik = Left((Left(NazwaPlik, Len(NazwaPlik) - 5)), 29) 'wyciagniecie nazwy pliku bez roszeżenia wbCSV.Activate ' aktywacja pliku CSV ' przy pierwszym kopiowaniu, kopiujemy nagłówek, przy kolejnych plikach omijamy nagłóweg If i = 1 Then Range("A1").Select ' wiersz z nagłówkiem Else Range("A2").Select ' omijamy nagłówek End If Range(Selection, Selection.End(xlDown)).Select ' zaznaczenie zakresu do skopiowania Selection.Copy 'kopiowanie wbWyniki.Activate 'aktywacja pliku z wynikami wiersz = Cells(Rows.Count, 1).End(xlUp).Row 'policzenie wypełnionych wierszy Cells(wiersz, 1).Select 'przejście do ostatniego wypełnionego wiersza ActiveCell.Offset(1, 0).Select ' przejście o 1 wiersz niżej od aktywnej komórki ActiveSheet.Paste 'wklejenie danych z pliku CSV wbCSV.Close SaveChanges:=False ' zamknięcie pliku CSV NazwaPlik = Dir 'pobranie nazwy pliku i = 2 ' zmiana wartości zmiennej i Loop Application.DisplayAlerts = False 'włączenie komunikatów wbWyniki.SaveAs Filename:=Lokalizacja & "scalone", FileFormat:=xlCSVMSDOS, CreateBackup:=False ' zapisanie pliku finalnego wbWyniki.Close SaveChanges:=False 'zamknięcie pliku finalnego Application.DisplayAlerts = True ' włączenie kominukatów Application.ScreenUpdating = True ' włączenie odświeżania Application.EnableEvents = True ' włączenie obsługi zdarzeń Set wbWyniki = Nothing ' zwolnienie pamieci z zmiennej obiektowej MsgBox "Pliki scalone" ThisWorkbook.Close SaveChanges:=False End Sub |
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.
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ł |
![\[ \mu=\frac{\Sigma x}{n} \]](https://www.strefaanalityka.pl/wp-content/ql-cache/quicklatex.com-c90ad7bd284da9f6fb19eaf12c66e2dd_l3.png "Rendered by QuickLaTeX.com")
![\[ \mu=\frac{suma ~zarobk\'ow}{liczba ~pracownik\'ow} \]](https://www.strefaanalityka.pl/wp-content/ql-cache/quicklatex.com-429ab5098c7136502f5c44f6ede6c4d7_l3.png "Rendered by QuickLaTeX.com")
Firma A
![\[ \mu=\frac{41300}{6}=6883 ~z\l{} \]](https://www.strefaanalityka.pl/wp-content/ql-cache/quicklatex.com-b67b8e7ccd6aaf5eb57b3e935df8ffa0_l3.png "Rendered by QuickLaTeX.com")
Firma B
![\[ \mu=\frac{39000}{6}=6500 ~z\l{} \]](https://www.strefaanalityka.pl/wp-content/ql-cache/quicklatex.com-ed67ae12a250d1ca71c4b77c6bee1373_l3.png "Rendered by QuickLaTeX.com")
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).
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
Firma B
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.
mediana, mediana statystyka, średnia arytmetyczna, średnia arytmetyczna statystyka
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:
Z rozwijanego menu na dole mamy dwa polecenia: „Chroń arkusz…” i „Zablokuj komórkę”
Drugie polecenie 
„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 
„Chroń arkusz...”.
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 
(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.
Zaznaczamy komórki D9:D20 z meny „Narzędzia główne” wybieramy polecenie „Format” i z listy klikamy na polecenie „Zablokuj komórkę”.
W kolejnym kroku znów z meny „Narzędzia główne” wybieramy polecenie „Format” i z listy klikamy na polecenie „Chroń arkusz„.
Pojawia się nam nowe okno, gdzie zatwierdzamy przyciskiem „OK„.
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:
Przykład poniżej:
Miesieczne_koszty.xlsx
excel, excel ochrona arkusza, excel ochrona komórek, jak odblokować komórki w excelu, warunkowe blokowanie komórek w excelu
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:
Funkcja PRZESUNIĘCIE potrzebuje 5 parametrów, gdzie 2 są opcjonalne.
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ć.
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
3. Stojąc kursorem na przycisku pokrętła, prawym klawiszem myszy wybieramy polecenie 
4. Pojawia się nam nowe okno gdzie w polu Łącze komórki wprowadzamy wartość: = $E$1, oraz ustawiamy wartość minimalną i maksymalną.
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.
Żeby obrazowo wyjaśnić o co chodzi z tym zakresem 
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.
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
Koniec wyjaśnień na temat formuły.
6. Stajemy na komórce E2 i przeciągamy formułę do komórki E9
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.
7. Do zmiany zdjęcia został wykorzystany prosty kod vba podpięty do pokrętła:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub zmien_zdjęcie() If Range("B13").Value = 1 Then ActiveSheet.Shapes.Range(Array("Picture 2")).Select Selection.ShapeRange.ZOrder msoBringToFront Range("K13").Select Else ActiveSheet.Shapes.Range(Array("Picture 3")).Select Selection.ShapeRange.ZOrder msoBringToFront Range("K13").Select End If End Sub |
Gotowy plik do pobrania: przesuniecie.xlsm
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 e-mail z Excela
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:
Zaczynamy 🙂
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.
Program jest wersją webową i uruchomi się w domyślnej przeglądarce pod adresem: https://127.0.0.1:8082/servlet/mstrWeb.
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.
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.
https://www.strefaanalityka.pl/download/malzenstwa_zawarte_rozwiazane_lata_1970_2013.xls
Przechodzimy teraz do kolejnego przedostatniego kroku – stworzenia dashboardu.
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
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 w MicroStrategy Analytics Desktop.
httpss://www.youtube.com/playlist?list=PLu4_Ge2aZ8ooSOcYH8otxT-ZaW_TRd4Vf
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:
|
1 2 3 4 |
select prac.imie || ' ' || prac.nazwisko nazwa_pracownika, stan.stanowisko, prac.place from pracownicy prac, stanowisko stan where prac.stan_id = stan.stan_id order by prac.stan_id, prac.place desc; |
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()
|
1 2 3 4 5 6 7 8 |
select prac.imie || ' ' || prac.nazwisko nazwa_pracownika, stan.stanowisko, prac.place, RANK() over (partition by stan.stanowisko order by prac.place desc) "RANK", DENSE_RANK() over (partition by stan.stanowisko order by prac.place desc) "DENSE_RANK" from pracownicy prac, stanowisko stan where prac.stan_id = stan.stan_id order by prac.stan_id, prac.place desc; |
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.
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:
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.
Całą zabawę podzielimy na 3 obszary:
Kolejność jest istotna wiec zabieramy się najpierw za konfiguracje karty sieciowej.
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
Przechodzimy dalej i zaznaczamy pole „Zainstaluj sprzęt, który wybiorę ręcznie z listy„.
W kolejnym oknie wybieramy Karta sieciowa
W kolejnym oknie oszukujemy Kartę Microsoft Loopback
Następnie naciskamy dwa razy Dalej i karta sieciowa została zainstalowana na komputerze, jedyne co nam zostało to ją skonfigurować.
Przechodzimy ponownie do Panelu Sterowania/Sieć i Internet/Połączenia sieciowe odnajdujemy w/w kartę.
Prawym myszki wybieramy właściwości. W oknie właściwości Karty Microsoft Loopback wybieramy Protokół internetowy (TCP/IP)
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.
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.
Zapisujemy wprowadzone zmiany w pliku hosts i uruchamiamy ponownie komputer.
Ściągnięty plik rozpakowujemy i uruchamiamy instalację.
pojawia się nam okno przygotowanie do instalacji
……
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:
Następnie pojawi się okno, gdzie klikamy Next
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
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
Kolejne okno pozwala nam na konfiguracje pliku tnsnames.ora, wypełniamy poszczególne pola. Pamiętaj ze zawsze może je edytować w pliku.
Kolejne okno pokazuje nam co będzie instalowane, pozostaje nam tylko nacisnąć Install
Czekamy cierpliwie aż instalacja dotrze do końca.
I czekamy na informacją, że dotarliśmy do końca instalacji.
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.
Data Access Components, instalacja bazy oracle, ODAC, oracele, sql
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:
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ę:
Efekt końcowy jest widoczny poniżej, liczby przechowywanych jako tekst są już liczbami i można wykonywać na nich operacje matematyczne.
Inna metodą jest wymnożenie wartości przez wartość 1 i liczby przechowywanych jako tekst zostaną przekonwertowane na liczby:
|
1 |
