Tag: Średni

  • Dane z internetu za pomocą Excel Power Query

    Dane z internetu za pomocą Excel Power Query

    Jak pobrać dane z internetu za pomocą Excel Power Query

    Excel Power Query pozwoli Ci na automatyczne pobieranie danych z internetu wprost do arkusza. Jeżeli do tej pory kopiowałeś i wklejałeś te dane to jest lepszy, bo automatyczny sposób. Wystarczy raz ustawić połączenie i pozwolić Excelowi zrobić robotę. Zobacz jak.

    Excel Power Query: połącz się ze stroną www

    Wyobraź sobie, że prowadzisz sklep internetowy z produktami, które importujesz z zagranicy. Oprócz sprzedaży w Polsce, zdarza Ci się sprzedawać również za granicę. W takiej działalności ceny towarów wyrażone w różnych walutach są podstawą zarządzania sprzedażą.

    W jaki sposób codziennie pobierać aktualne kursy walut i zarządzać na tej podstawie sprzedażą? Ręczne kopiowanie i wklejanie wartości do arkuszy jest czasochłonne i nieefektywne. Wykorzystamy narzędzie Power Query Excela, aby połączyć się ze stroną NBP, z oficjalnymi kursami walut.

    Oto co musisz zrobić:

    Jak pobrać dane z internetu za pomocą Excel Power Query

    • Przejdź na wstążkę DANE i z sekcji POBIERANIE I PRZEKSZTAŁCANIE DANYCH, wybierz POBIERZ DANE i Z SIECI WEB.
    • W okienku Z SIECI WEB wklej adres url strony, która zawiera interesujące Cię dane i kliknij OK. W tym przypadku, będzie to strona z notowaniami kursów walut NBP.
    • Z NAWIGATORA wybierz tabelę, która zawiera interesujące Ciebie dane. W naszym przypadku, to „Table 0”
    • Jeżeli nie chcesz przekształcać w żaden sposób danych zawartych w tej tabeli, wybierz przycisk ZAŁADUJ. My potrzebujemy przekształcić nasze kursy walut, ponieważ czasami są one wyrażone w jedności (np. 1 USD), a czasami w większej jednostce (np. 100 JPY) Dlatego wybieramy opcję PRZEKSZTAŁĆ DANE.
    Nawigator Power Query

    Przekształcanie danych w Power Query

    Uruchomimy edytor Power Query, w którym będziemy mogli przekształcić nasze dane do odpowiadającej nam postaci.

    Musimy sprowadzić wszystkie kursy do tego samego mianownika, czyli do jedności. Aby to zrobić, należy oczywiście podzielić średni kurs przez podane jednostki. Problem w tym, że obok jednostki mam podany kod waluty, czyli tekst np. 1 EUR. Dlatego też, musimy rozdzielić jednostkę wyrażoną liczbowo od kodu wyrażonego tekstem. Użyjesz narzędzia podobnego do TEKST JAKO KOLUMNY, z Excela.

    • Zaznacz kolumnę „Kod waluty” i wybierz opcję PODZIEL KOLUMNY > WG OGRANICZNIKA. Znajdziesz ją na wstążce STRONA GŁÓWNA.
    • Excel domyślił się, że w tym przypadku ogranicznikiem jest SPACJA, więc pozostaje Ci zatwierdzić domyślny wybór.
    • Powstały dwie kolumny. Jedną z nich – z jednostkami – wykorzystasz do dalszych obliczeń.
    Power Query - podział kolumn wg ogranicznika

    • Ze wstążki DODAJ KOLUMNĘ, wybierz opcję KOLUMNA NIESTANDARDOWA.
    • W okienku dialogowym wprowadź swoją nazwę nowej kolumny, oraz ustawiając się w polu FORMUŁA KOLUMNY NIESTANDARDOWEJ kliknij dwukrotnie na kolumnę z kursem średnim, wprowadź znak dzielenia i ponownie dwuklik na kolumnie z jednostką waluty.
    =[Kurs średni]/[Kod waluty.1]
    • Możesz już usunąć dwie kolumny, które wykorzystałeś do obliczenia jednostkowego kursu. Kliknij na nie prawym przyciskiem myszy i wybierz USUŃ KOLUMNY.
    • Teraz załaduj obrobione dane do arkusza. Przejdź na STRONĘ GŁÓWNĄ i wybierz opcję ZAMKNIJ I ZAŁADUJ DO…
    Power Query - kolumna niestandardowa

    Jak zautomatyzować pracę na pobieranych danych

    Po wybraniu docelowego miejsca, gdzie mają się pojawić Twoje dane możesz ustawić opcję automatycznego odświeżania. Dzięki temu, nie będziesz musiał się martwić o aktualność danych.

    Zaznacz tabelę z danymi i po prawej stronie zobaczysz Twoje zapytanie Power Query. Kliknij na nie prawym przyciskiem myszy i wybierz WŁAŚCIWOŚCI. Teraz w polu ODŚWIEŻ CO – ustaw wybrany interwał w minutach. Od teraz dane będą pobierane automatycznie we wskazanych przez Ciebie odstępach czasu.

    Samo pobranie danych to nie wszystko. Możesz je wykorzystać, aby pokazywać ceny produktów w przeliczeniu na wartość w różnych walutach.

    Stwórz listę rozwijaną z oznaczeniem symboli poszczególnych walut. Teraz dla wybranej z listy rozwijanej waluty, pobierzemy odpowiedni kurs waluty z naszej tabeli z kursami walut. W tym celu wykorzystaj funkcję WYSZUKAJ PIONOWO.

    W kolejnym kroku wystarczy, że podzielisz cenę towaru w zł, przez komórkę w której znajduje się kurs wybranej aktualnie waluty.

    Możesz jeszcze stworzyć formułę, która obok przeliczonej wartości będzie wyświetlała także symbol waluty. Aby, to osiągnąć pobierz symbol waluty na podstawie komórki w której wybierana jest waluta danego kraju. Ponownie, wykorzystaj funkcję WYSZUKAJ PIONOWO.

    Następnie zmodyfikuj formułę przeliczającą wartość produktu w zł do wybranej waluty w taki sposób (tekst w cudzysłowie oczywiście musisz zastąpić odpowiednim adresem komórki):

    =ZAOKR("komórka z wartością w PLN"/"komórka z kursem";2)& " " &"komórka z symbolem waluty"

    Efekt końcowy, powinien wyglądać tak:

    Tabela z danymi - przeliczenia wg kursów walut

    .

    Podsumowanie

    Jak widzisz pobieranie danych z internetu, z wykorzystaniem Power Query może znacząco przyspieszyć Twoją pracę. Co więcej, zyskujesz możliwość analizowania danych – w zasadzie online.

    Mam do Ciebie prośbę – jeśli uważasz ten artykuł za wartościowy – udostępnij go proszę na swoim Facebooku.

  • Formatowanie warunkowe w zależności od komórki

    Formatowanie warunkowe w zależności od komórki

    Formatowanie warunkowe w zależności od zawartości innej komórki

    Formatowanie warunkowe to jedna z podstawowych dróg, aby uatrakcyjnić Twoje arkusze. Jeśli to dla Ciebie nowy temat zachęcam do lektury poprzedniego artykułu. Przeczytasz w nim o najważniejszych możliwościach jakie daje to narzędzie.

    W tym artykule przeczytasz o nieco bardziej zaawansowanych trickach, takich jak formatowanie warunkowe w zależności od komórki.

    Proste formatowanie warunkowe

    Formatowanie warunkowe umożliwia formatowanie komórek w zależności od zawartości danej komórki. Co to w praktyce oznacza? Wyobraź sobie, że chciałbyś żeby komórka zawierająca wartość sprzedaży zmieniała tło na zielone, w sytuacji kiedy wprowadzisz tam wartość powyżej 100 000 zł. Na takie operacje pozwala właśnie formatowanie warunkowe.

    Nie jest to formatowanie statyczne tylko dynamiczne. To znaczy, że jest wykonywane „w locie”. Excel na bieżąco sprawdza zawartość komórki i w zależności od ustawionych warunków – włącza, bądź wyłącza formatowanie.

    Co musisz zrobić aby skorzystać z tej funkcjonalności?

    Po pierwsze, wskaż obszar na którym ma działać formatowanie warunkowe. Załóżmy, że mamy  rejestr sprzedaży w firmie. Chcielibyśmy teraz w naszej bazie sprzedaży, w grupie towaru wyróżnić jakąś konkretną grupę np. AGD.

    W standardowy sposób musielibyśmy je najpierw posortować albo przefiltrować aby osiągnąć cel. Przy formatowaniu warunkowym nie musimy wykonywać żadnych dodatkowych czynności, aby wyróżnić komórki spełniające nasze warunki. Excel zrobi to za nas. 

    Przejdźmy do praktycznego przykładu. Zaznacz zakres komórek, na którym ma działać formatowanie. Przejdź na NARZĘDZIA GŁÓWNE i z sekcji STYLE wybierz FORMATOWANIE WARUNKOWE, a następnie REGUŁY WYRÓŻNIANIA KOMÓREK.

    Formatowanie warunkowe - reguły wyróżniania komórek

    Nasze formatowanie ma zadziałać wtedy, gdy w komórce pojawi się wyraz „AGD”. Zatem wybieramy opcję RÓWNE i wpisujemy warunek, czyli słowo AGD. Po prawej stronie mamy style formatowania. Możesz wybrać z kilku opcji stylów zdefiniowanych. Jeżeli żaden z nich nie spełnia Twoich oczekiwań, możesz wybrać FORMAT NIESTANDARDOWY i samodzielnie określić sposób formatowania.

    Formatowanie warunkowe w zależności od komórki

    Jeżeli chciałbyś teraz zmienić reguły powyższego formatowania i zamiast słowa „AGD” wybrać na przykład „Meble” musiałbyś przejść do menu formatowania warunkowego i edytować reguły.

    Oczywiście jest lepszy sposób. Załóżmy, że chcesz teraz wyróżniać „kraj odbiorcy”.  Chcemy uniknąć „sztywnego” określania konkretnego kraju. Dlatego zrobimy parametr w wybranej komórce. Parametr oznacza, że będziesz mógł zmieniać zawartość tej komórki, a formatowanie warunkowe będzie uzależnione od wartości z tej komórki.

    Jeśli np. wpiszesz „Polska” to „zapalą się” tylko te komórki, które zawierają to słowo. Zmieniając zawartość komórki na „Anglia” sformatujesz komórki które zawierają ten kraj.

    Jak widzisz to dużo ciekawsze rozwiązanie i łatwiejsze w obsłudze rozwiązanie. Jak to zrobić?

    1. Zaznacz obszar, który chcesz sformatować.
    2. Ze wstążki NARZĘDZIA GŁÓWNE, wybierz FORMATOWANIE WARUNKOWE, a następnie REGUŁY WYRÓŻNIANIA KOMÓREK.
    3. Wybierz opcję RÓWNE…
    4. Teraz nie wpisujesz statycznej nazwy kraju, tylko odwołujesz się do komórki, w której został umieszczony wspomniany wcześniej parametr.
    5. Pozostaw domyślny lub zmień styl formatowania.
    6. Zatwierdź wybór.

    Teraz, gdy zmienisz wartość komórki – parametru, automatycznie spowoduje to zmianę formatowania na właściwych komórkach. Prawda, że bardzo elastyczne rozwiązanie?

    Połącz formatowanie warunkowe z listą rozwijaną

    W poprzednim przykładzie, w komórce która była parametrem wartości zmieniałeś ręcznie. Byłoby dobrze to nieco zautomatyzować.

    Załóżmy, że masz tabelkę w której chciałbyś wyróżniać wybranych klientów. Nie wiesz ilu ich dokładnie jest, a tym bardziej nie pamiętasz ich wszystkich. Pomysł polega na tym, że utworzysz listę rozwijaną z nazwami klientów. Komórka, w której znajdzie się ta lista – będzie Twoim parametrem do formatowania warunkowego.

    W pierwszej kolejności – przygotuj listę unikatowych klientów. Skopiuj wszystkich klientów w oddzielne miejsce. Teraz, korzystając z narzędzia USUŃ DUPLIKATY na wstążce DANE, przygotujesz listę unikatowych wartości.

    Na podstawie tych wartości stwórz listę rozwijaną. O tym jak to zrobić przeczytasz w poprzednim artykule.

    Teraz, gdy powiążesz komórkę z listą rozwijaną z formatowaniem warunkowym, będziesz mógł wybierać klientów z listy, a formatowania będą automatycznie się zmieniały.

    Formatowanie warunkowe z warunkiem na liście rozwijanej

    Formatowanie warunkowe, które podświetla cały wiersz

    Byłoby jeszcze ciekawiej, gdyby po wybraniu parametru, w tabeli z danymi podświetlały się całe wiersze, które zawierają wybrany parametr. Zatem do dzieła.

    1. Zaznacz cały obszar z danymi. Robisz tak dlatego, że formatowanie ma dotyczyć całych wierszy.
    2. Przejdź do menu FORMATOWANIA WARUNKOWEGO i wybierz opcję NOWA REGUŁA. W standardowym menu nie ma opcji pozwalającej na podświetlenie całych wierszy. Musisz stworzyć własną regułę.
    3. W menu nowej reguły formatowania  wybierz opcję UŻYJ FORMUŁY DO OKREŚLANIA KOMÓREK, KTÓRE NALEŻY SFORMATOWAĆ.
    4. Teraz stwórz taką formułę, która gdy warunki formatowania zostaną spełnione to wynik formuły da PRAWDĘ, a w przeciwnym razie da FAŁSZ.

    Załóżmy, że chcesz aby podświetlił Ci się cały wiersz wtedy, kiedy wpis w kolumnie klient, będzie równy temu co jest wpisane w komórce F1. Wpisz:

    =$B7=$F$1

    Taka formuła oznacza, że porównujesz zawartość komórki B7 (tam masz nazwę klienta) z Twoim parametrem w komorce F1. Zauważ, że wybierając komórkę B7, Excel domyślnie wstawił wszędzie dolary (czyli zmienił adresowanie komórki na bezwzględne) W tym momencie, to nie jest działanie prawidłowe dla naszych potrzeb. 

    Chcemy stworzyć taką formułę, która dla kolejnych komórek w kolumnie B będzie sprawdzała czy wpis równa się zawartości komórki F1. Czyli dla całego obszaru zawsze będziemy odnosić się do komórki F1, ale porównywana komórka  będzie się zmieniać. To oznacza, że adres komórki w sprawdzanej kolumnie B musi mieć postać adresu mieszanego, czyli blokujemy kolumnę B znakiem $, ale numery wierszy pozostawiamy niezablokowane (bez znaku dolara)

    Excel będzie przynosił sprawdzanie warunku na kolejne kolumny i gdybyśmy nie zablokowali kolumny B w naszym warunku porównywanie nie miałoby sensu. Dlaczego? Ponieważ kazalibyśmy Excelowi porównywać np. nazwę klienta z grupą towaru.

    Dzięki temu, że zablokowaliśmy kolumnę B, w każdej kolejnej kolumnie sprawdzany jest właśnie ten warunek – właściwy dla tej kolumny.

    Kolejne kroki są już analogiczne do poprzedniego postępowania. Wybierz FORMATUJ i ustaw preferowany przez Ciebie styl formatowania. Teraz, gdy wybierzesz z listy rozwijanej kolejnych klientów, na Twojej liście danych będą podświetlały się całe wiersze, właściwe dla wybranego klienta.


    Podsumowanie

    Formatowanie warunkowe daje całą masę możliwości na ciekawą wizualizację Twoich danych. Natomiast formatowanie w zależności od zawartości innej komórki, daje również opcję na interaktywność w arkuszu.

    Jestem ciekawy jak Ty wykorzystujesz formatowanie warunkowe w swoich arkuszach. Podziel się Twoimi sposobami w komentarzu. A jeżeli artykuł pomógł Ci ulepszyć Twoje arkusze – podziel się nim proszę w Twoich mediach społecznościowych.

  • Dynamiczna lista rozwijana, czyli jak przyspieszyć pracę

    Dynamiczna lista rozwijana, czyli jak przyspieszyć pracę

    Dynamiczna lista rozwijana, czyli jak przyspieszyć pracę

    Listy rozwijane możesz zrobić na kilka sposobów. Dzięki lekturze tego artykułu dowiesz się, jak dzięki nim zautomatyzować pracę. Dynamiczna lista rozwijana pozwoli Ci ją przyspieszyć.

    Na koniec dowiesz się jak tworzyć hierarchiczne listy rozwijane. Hierarchiczne, to znaczy takie, gdzie lista wyboru z jednej listy zależy od wyborów z innej. Brzmi ciekawie? Zapraszam do lektury!

    Prosta lista rozwijana

    Załóżmy, że masz taką sytuację – dysponujesz bazą sprzedaży i chciałbyś zliczać łączną wartość sprzedaży netto dla poszczególnych grup towarów. Przygotujemy tabelę, w której będzie możliwe wybranie poszczególnych towarów z listy oraz automatyczne podsumowanie sprzedaży, wg tego wyboru.

    W kolejnych krokach pokażę Ci jak się za to zabrać.

    Przygotuj wszystkie unikatowe elementy listy, które mają pokazywać się na liście rozwijanej. Następnie ustaw się na komórce, gdzie ma się znaleźć lista rozwijana i wybierz POPRAWNOŚĆ DANYCH z menu górnego DANE w SEKCJI NARZĘDZIA DANYCH.

    W menu POPRAWNOŚĆ DANYCH, wybierz zakładkę KRYTERIA POPRAWNOŚCI. O co tutaj chodzi? Domyślnie do każdej komórki Excela możesz wpisać dowolną wartość. Natomiast my chcemy, aby do komórki z listą wyboru, mogły być wpisane tylko wartości zdefiniowane wcześniej przez nas.

    Dlatego, wybieramy polecenie LISTA i klikając na ikonę ŹRÓDŁO wskazujemy zakres komórek, gdzie umieściliśmy nasze unikalne kategorie towarów. Zatwierdzamy i nasza lista rozwijana jest gotowa.

    Prosta lista rozwijana

    Dynamiczna lista rozwijana

    Prosta lista rozwijana to nie koniec możliwości. Pewnie zauważyłeś, że w momencie dodania kolejnej wartości do listy unikalnych towarów, lista rozwijana nie zaktualizuje się o te dodatkowe wartości.

    Stworzymy zatem listy dynamiczne. Co to oznacza? Gdy dodamy kolejną grupę towarów – to nie wykonując żadnych dodatkowych czynności – nowy wpis automatycznie pokaże się na naszej liście rozwijanej. Jak się do tego zabrać?

    Pierwszą rzeczą jaką musisz zrobić to przygotować listę. Kolejnym elementem całej układanki jest przerobienie tej listy na tabelę excelową. Jednym z najprostszych sposobów jest użycie skrótu CTRL + T.

    Dzięki temu, że ten obszar jest teraz tabelą, dodanie kolejnych elementów będzie w niej automatycznie uwzględniane. Jeśli podłączysz teraz sprawdzanie poprawności do tabeli, automatycznie dodane rekordy będą pojawiać się na liście.

    Dynamiczna lista rozwijana

    Początek drogi jest analogiczny jak poprzednio. To znaczy:

    1. Ustaw się na komórce, gdzie ma się znaleźć lista rozwijana.
    2. Wybierz POPRAWNOŚĆ DANYCH z menu górnego DANE w SEKCJI NARZĘDZIA DANYCH.
    3. W menu POPRAWNOŚĆ DANYCH, wybierz zakładkę KRYTERIA POPRAWNOŚCI.
    4. Wybierz polecenie LISTA i klikając na ikonę ŹRÓDŁO wskaż tabelę, którą utworzyłeś ale tylko elementy listy bez nagłówka. Excel rozpozna, że chodzi tylko o same wpisy, a nie o nagłówek.
    5. Zatwierdź klikając OK.

    Teraz, gdy dodasz kolejny wpis na liście, Twoja lista rozwijana automatycznie uwzględni tę wartość!

    Hierarchiczne listy rozwijane

    Naprawdę ciekawym rozwiązaniem są hierarchiczne listy rozwijane. Co to takiego? Załóżmy że, mamy główne grupy towaru, a w każdej grupie towaru znajdują się poszczególne towary. Na przykład w grupie “AGD”, mamy: lodówka, mikrofalówka, okap. W grupie “Meble” mamy: biurko, fotel biurowy, itd.

    Cały trick polega na tym, aby stworzyć listy rozwijane w taki sposób, że jeśli wybierzesz główną grupę towaru (na przykład AGD), to pokażą się tylko towary przypisane do tej grupy.

    Wbrew pozorom, takie rozwiązanie nie jest trudne. Tak jak poprzednio, musisz przygotować dane, tyle że tym razem nagłówki tabel będą oznaczały grupę produktów, a poniżej będą poszczególne towary, czyli elementy listy rozwijanej.

    Kolejny krok to nazwanie obszarów dla poszczególnych grup towarów. Zaznacz wszystkie zakresy łącznie z nagłówkami. Musisz to zrobić używając przycisku CTRL na klawiaturze oraz lewego przycisku myszy. Następnie przejdź na wstążkę FORMUŁY i wybierz z sekcji NAZWY ZDEFINIOWANE polecenie UTWÓRZ Z ZAZNACZENIA. Sprawdź czy jest wybrana opcja GÓRNY WIERSZ i zatwierdź wybór.

    Utworzyłeś 3 nazwy grup towarów (AGD, meble i oświetlenie) Sprawdzisz to w MENEDŻERZE NAZW na wstążce FORMUŁY.

    Przejdź teraz do komórki, gdzie ma powstać lista rozwijana z możliwością wyboru grupy towarów. W tym miejscu tworzysz listę w standardowy, opisany wcześniej sposób. Oczywiście jako zakres danych dla tej listy wskażesz tylko nagłówki z nazwami grup towarów.

    Następnie przejdź do komórki, gdzie ma pojawić się lista rozwijana z towarami. Zawartość tej listy będzie zależna od wybranej w poprzedniej komórce grupy towarów.

    Podobnie jak wcześniej, przejdź do polecenia POPRAWNOŚĆ DANYCH i wybierz LISTĘ. W źródło danych nie możesz wpisać, ani wskazać zakresu z: “AGD, meble, oświetlenie” ponieważ Excel potraktowałby to jako elementy listy.

    Zamiast tego wykorzystaj dodatkową funkcję ADR.POŚR, która niejako “przetłumaczy” wcześniej nazwany zakres komórek (np. AGD) na konkretny zakres komórek, w której znajdują się towary z tej właśnie grupy. Jako argument tej funkcji musisz oczywiście wskazać komórkę, w której umieściłeś listę rozwijaną z grupami towarów. Zatwierdzasz i gotowe! Wybierając z listy rozwijanej grupa towarów “Oświetlenie”, zobaczysz tylko produkty, które należą do tej kategorii. 

    Hierarchiczna lista rozwijana

    Podsumowanie

    Ten artykuł pokazał Ci jak wiele ciekawych funkcjonalności kryje w sobie prosta lista rozwijana. Jak w większości przypadków, połączenie jej z innymi funkcjonalnościami Excela daje najlepsze efekty.

    Listy są często wykorzystywane, w tworzeniu interaktywnych raportów i dashboardów. Mam nadzieję, że uda Ci się wykorzystać opisywane tu możliwości w Twoich zestawieniach i raportach. Opisz proszę w komentarzu, do czego Ty wykorzystujesz listy rozwijane..

  • Jak zrobić wykres świecowy?

    Jak zrobić wykres świecowy?

    Jak zrobić wykres świecowy? Od danych giełdowych po siatki płac.

    Wykresy świecowe to stara, japońska technika analizy i prezentacji danych. Możesz ją wykorzystać zarówno do analizy danych giełdowych, jak i stworzenia wizualnej prezentacji siatki płac. Zastosowań jest wiele, bo wykres świecowy niesie ze sobą więcej informacji niż wykres liniowy czy kolumnowy. Pod tym względem „świece” przypominają wykresy pudełkowe [ang. box-plot]

    W artykule opisuje jak zrobić taki wykres, z czego się składa i jak wykorzystać go do wizualizacji danych na przykładzie siatki płac.

    Ekran laptopa z notowaniami giełdowymi w postaci wykresu świecowego

    Z czego składa się wykres świecowy

    Wykresy świecowe często wykorzystuje się do analizy danych giełdowych. Dlatego, zaczniemy od przygotowania takiego wykresu na podstawie notowań spółki NEUCA. Ściągnij plik z danymi początkowymi.

    Świeca reprezentuje 4 informacje. Cenę otwarcia i zamknięcia oraz minimum i maksimum z danego okresu. W sytuacji, gdy cena otwarcia jest mniejsza niż cena zamknięcia – mamy do czynienia ze wzrostem ceny i świeca jest biała. W odwrotnej sytuacji – cena otwarcia jest wyższa od ceny zamknięcia – mamy spadek i świeca jest czarna.

    Jak zrobić wykres świecowy - świece wzrostowe i spadkowe

    Istnieje wiele kombinacji (formacji) i możliwych wariantów świec, które mają dawać sygnały do kupna lub sprzedaży. Ale nie o inwestowaniu to post 🙂

    Jak zrobić wykres świecowy - tabela z danymi

    W pliku znajdziesz kursy spółki NEUCA z kilkunastu dni. Aby stworzyć wykres świecowy, dane muszą mieć układ: otwarcie, maksimum, minimum i zamknięcie. Mając tak przygotowaną tabelę, wystarczy, że:

    • Zaznaczysz tabelę z danymi (łącznie z datami).
    • Na karcie WSTAWIANIE, wybierzesz POLECANE WYKRESY.
    • Z zakładki WSZYSTKIE WYKRESY wybierzesz GIEŁDOWY i drugi typ wykresu od lewej.
    • Klikniesz OK.
    Jak zrobić wykres giełdowy


    Po utworzeniu wykresu zobaczysz jedną niedogodność. Notowania akcji nie odbywają się każdego dnia i w związku z tym na wykresie pojawią się źle wyglądające luki.

    Wykres świecowy z przerwami na osi czasu

    Możesz temu zaradzić, zamieniając typ osi z daty na tekst. Kliknij prawym przyciskiem myszy na oś czasu i wybierz opcję OŚ TEKSTU.

    Jak zrobić wykres świecowy - usuwanie przerw na osi czasu


    Jak stworzyć wykres pokazujący siatkę płac

    Opisywany wykres świecowy możesz także wykorzystać do zobrazowania siatek płac. Główna różnica polega na tym, że zamknięcie jest równe maksimum, a otwarcie – minimum. Można to łatwo uwzględnić w tabeli z danymi.

    Tabela z danymi do siatki płac

    Ponadto, ponieważ na osi poziomej nie będzie czasu ale kategorie zaszeregowania – nieco inaczej będzie wyglądało tworzenie wykresu. Zamiast zaznaczać całą tabelę, łącznie z kolumną „Kategoria zaszeregowania”, zaznacz tylko kolumny z danymi o wynagrodzeniach. Kategorie na oś poziomą wprowadzisz później. Poza tym wykres utworzysz dokładnie tak samo jak opisany powyżej wykres świecowy.

    Po stworzeniu wykresu, zauważysz że kategorie nie rosną od lewej do prawej. Możesz to zmienić klikając prawym przyciskiem na oś poziomą i wybierając opcję KATEGORIE W KOLEJNOŚCI ODWROTNEJ.

    .

    Podsumowanie

    Jestem pewny, że wykres świecowy możesz wykorzystać do innych zadań niż „rysowanie” siatek płac czy wykresów giełdowych. Mam nadzieję, że artykuł dał Ci potrzebną wiedzę i inspirację do działania. Będę wdzięczny, jeżeli podzielisz się swoimi pomysłami na wykorzystanie „świec” w komentarzu.

  • POWER QUERY: Zamień kolumny na wiersze

    POWER QUERY: Zamień kolumny na wiersze

    POWER QUERY: Zamień kolumny na wiersze

    Po co zamieniać kolumny na wiersze? Najczęściej po to, żeby przygotować dane do tabeli przestawnej. Z taką sytuacją masz do czynienia, kiedy masz tabelę z danymi w kolejnych kolumnach. Natomiast w tabeli przestawnej najlepiej mieć dane w postaci bazodanowej.

    Zanim pojawił się POWER QUERY, trzeba było kombinować z różnymi, najczęściej czasochłonnymi metodami (np. z wykorzystaniem funkcji TRANSPONUJ) Teraz jest na to znacznie prostszy sposób i co ważne – pozwalający na automatyczną aktualizację. Zainteresowany?

    Krajobraz ze słowem z chmur "Change"

    Przeczytaj również:

    Kolumny na wiersze: w czym problem?

    Wyobraź sobie, że masz stworzyć budżet wynagrodzeń dla kilku działów. Najwygodniej będzie działać, dodając wartości w kolejnych kolumnach reprezentujących miesiące. Szczególnie, jeżeli będziesz używał formuł obliczających potrzebne wartości.

    Zamiana kolumn na wiersze - dane podstawowe

    Zatem w czym problem?

    Po stworzeniu budżetu i niezbędnych akceptacjach, trzeba będzie przenieść dane do bazy i raportów. Dzięki temu będziesz w stanie porównywać plan z wykonaniem. Najczęściej i najłatwiej robi się to za pomocą tabeli przestawnej. A powyższy format nie jest najlepszy dla tabel. Dużo lepiej sprawdza się układ bazodanowy, tak jak poniżej.

    Kolumny na wiersze - układ bazodanowy

    Kolumny na wiersze, czyli zrób UNPIVOT

    Doprowadzenie do takiego układu danych, szczególnie gdy masz setki czy tysiące wierszy wymagało sporo zachodu. Wymagało, bo na szczęście mamy POWER QUERY, aka GET & TRANSFORM (od wersji 2016)

    Poniżej znajdziesz kolejne kroki jakie powinieneś zrealizować. W nawiasie zaznaczyłem, jeżeli krok jest opcjonalny i wynika z tego jak wyglądają moje dane. Oczywiście możesz pobrać plik startowy i przećwiczyć kolejne etapy.

    Potrzebny czas: 3 minuty

    Jak zamienić kolumny na wiersze w POWER PIVOT

    1. Zamień dane do postaci tabeli.

      Kliknij w dowolnym miejscu z danymi i przekształć je do postaci tabeli. NARZĘDZIA GŁÓWNE > FORMATUJ JAKO TABELĘ lub skrót CTRL + L

    2. Dodaj tabelę do POWER QUERY.

      Przejdź na kartę POWER QUERY i wybierz polecenie Z TABELI / ZAKRESU. W nowszych wersjach odpowiednie polecenie znajdziesz na karcie DANE, w sekcji POBIERANIE I PRZEKSZTAŁCANIE DANYCH.

    3. Usuń niepotrzebne kolumny (opcjonalnie).

      Usuń ostatnią kolumnę RAZEM. Podsumowanie nie będzie potrzebne. Kliknij prawym przyciskiem myszy i wybierz USUŃ.

    4. Zamień „null” na zera (opcjonalnie).

      Zamieniając wartości null na 0, spowodujesz że dane dla tych kategorii pojawią się w docelowym układzie danych. W przeciwnym razie zostaną pominięte.
      Zaznacz kolumny z miesiącami. Przejdź na wstążkę PRZEKSZTAŁĆ, wybierz ZAMIENIANIE WARTOŚCI i w pierwszym okienku wpisz null, a w drugim 0.

    5. Anuluj przestawianie innych kolumn.

      Teraz zasadnicza część całej operacji. Zaznacz kolumny „Dział” i „Grupa Kosztów”. Te kolumny pozostaną niezmienione. Kliknij prawym przyciskiem myszy i z menu kontekstowego wybierz polecenie ANULUJ PRZESTAWIANIE INNYCH KOLUMN.

    6. Zmień nazwę kolumn (opcjonalnie).

      Kliknij dwukrotnie w nagłówek kolumny „Atrybut” i zmień nazwę na „Miesiąc”

    7. Zapisz i załaduj do arkusza.

      Przejdź na wstążkę NARZĘDZIA GŁÓWNE i wybierz ZAMKNIJ I ZAŁADUJ. Wybierając miejsce w nowym arkuszu, Twoje dane zostaną załadowane w przekształconej formie do wskazanego miejsca.

    .

    Podsumowanie

    Zamiana kolumn na wiersze, dzięki POWER QUERY jest naprawdę prosta. Przekształcanie danych do takiej postaci jest wręcz koniecznością, jeżeli chcesz efektywnie pracować na tabelach przestawnych.

    Jest jeszcze jedna zaleta opisanego mechanizmu. Jeśli dodasz kolejne dane w tabeli źródłowej, wystarczy że później klikniesz ODŚWIEŻ, a przekształcona tabela docelowa zostanie automatycznie zaktualizowana.

  • Wykres kolumnowy. Jak wyróżnić MIN i MAX?

    Wykres kolumnowy. Jak wyróżnić MIN i MAX?

    Jak wyróżnić MIN i MAX na wykresie kolumnowym?

    Jednym z atrybutów wyróżniających dane może być kolor. Z artykułu dowiesz się jak utworzyć wykres kolumnowy, który automatycznie „pokoloruje” wartość minimalną i maksymalną.

    Dlaczego takie wyróżnienie może być ważne? No cóż, podobno masz 7 sekund na zrobienie pierwszego wrażenia, a w przypadku złożonych wizualizacji, pierwsze wrażenie formułuje się w mniej niż 250 milisekund!!! Powinieneś zatem ułatwiać odbiór Twoich danych tworząc wykresy czy dashboardy.

    Wykres kolumnowy

    Zacznij od pobrania pliku z danymi. Na ich podstawie utworzymy wykres kolumnowy. Tego typu wykres dobrze nadaje się do porównań, rozkładów czy szeregów czasowych. Więcej o różnych typach wykresów przeczytasz w tym artykule.

    Dane dotyczą liczby klientów w poszczególnych dniach tygodniach.

    Liczba klientów wg dni tygodnia

    Stwórz wykres kolumnowy. Jak widzisz, najwięcej klientów było w środę, a najmniej w sobotę. Mógłbyś zaznaczyć pojedynczą kolumnę danych i zmienić jej kolor. Problem polega na tym, że w kolejnym tygodniu, z nowymi danymi wartość maksymalna lub minimalna może być w zupełnie innym dniu. Musiałbyś ponownie, ręcznie zmieniać kolory. A przecież w Excelu, nie chodzi o ręczną robotę! 🙂

    Wykres kolumnowy z zaznaczoną wartością minimalną i maksymalną

    Wykres kolumnowy z dynamicznym MIN i MAX

    Jak zatem automatycznie wyróżniać wartości minimalne i maksymalne? Odpowiedzią są dodatkowe serie danych. Zadaniem jednej z nich będzie pokazywanie maksymalnej wartości. Natomiast druga ma wyświetlać tylko minimalne.

    Użyjemy funkcji MAX, MIN i JEŻELI, żeby pokazać na dodatkowych seriach właściwe wartości. Dodatkowo, trzeba użyć funkcji BRAK co spowoduje, że wartości zerowe / puste nie będą widoczne na wykresie.

    Wykres kolumnowy - formuły identyfikujące wartości: minimalną i maksymalną
    =JEŻELI(C$8=MIN($C$8:$I$8);C$8;BRAK())
    =JEŻELI(C$8=MAX($C$8:$I$8);C$8;BRAK())

    Mając przygotowane dane pod dwie dodatkowe serie, po prostu dodajemy je na wykres, na oś główną. Kolejnym krokiem jest zmiana w OPCJACH SERII wartości NAKŁADANIE SERII na 100%. To spowoduje, że zarówno podstawowa seria, jak i serie MIN i MAX „nałożą się” na siebie. Dodatkowo możesz zmienić SZEROKOŚĆ PRZERWY, aby dostosować szerokość kolumn.

    Opcji serii na wykresie

    Następnym krokiem jest zmiana koloru serii z minimalną i maksymalną wartością. Zaznacz dowolną kolumnę, a następnie kursorem (strzałka w górę lub w dół) możesz przeskakiwać pomiędzy kolejnymi elementami wykresu. W momencie kiedy będziesz miał aktywną właściwą serię – zmień kolor.

    Wykres kolumnowy z dynamicznym zaznaczeniem wartości minimalnej i maksymalnej

    Wykres kolumnowy – czy można jeszcze inaczej?

    Czy możemy jeszcze jakoś poprawić nasz wykres? Na pewno warto pozbyć się z wykresu elementów nieistotnych. Prościej znaczy lepiej. Ale czy można wzbogacić wizualną stronę wykresu, nie zaśmiecając go i nie zmieniając podstawowego przekazu?

    Pokażę Ci sposób, który jakiś czas temu zobaczyłem u Leily Gharani. Polecam Ci jej kanał na You Tube.

    Idea jest prosta, bo polega na przekształceniu zwykłych kolumn w kolumny z zaokrąglonymi rogami. Nie wiem dlaczego, ale kojarzą mi się ze sztachetami w płocie 🙂 Aby to zrobić, zwykły wykres kolumnowy musimy zmienić w skumulowany. Pomysł polega na tym, że w górną serię wykresu skumulowanego zostaje wklejony wcześniej przygotowany kształt. To pozwala na zaokrąglenie słupka wykresu.

    Nieco trudniejsze jest odpowiednie przygotowanie danych, ponieważ łącznie mamy ich 6 serii. Dla serii danych podstawowych, maksimum oraz minimum musimy mieć po 2 serie, które reprezentują dolną i górną część słupka. Całość oczywiście musi zmieniać się dynamicznie, dlatego konieczne jest użycie odpowiednich formuł.

    Na przykład, dane dla dolnej serii kolumny z wartością maksymalną powstaną w wyniku takiej formuły:

    =JEŻELI(C$8=MAX($C$8:$I$8);MIN($C$8:$I$8)/2;BRAK())

    Sprawdzamy każdą komórkę danych podstawowych i jeżeli jest ona równa wartości maksymalnej z tego zakresu, to bierzemy wartość minimalną z tego zakresu i dzielimy na pół.

    Dlaczego tak? Chodzi o to, aby dolna część kolumny nie była wyższa niż wartość minimalna w zakresie. W przeciwnym razie nie można byłoby uzyskać efektu zaokrąglenia. Spróbuj sam zobaczyć jak wygląda wykres, kiedy nie zrobisz dzielenia, jak również jak zmieniają się zaokrąglenia kolumn, jeżeli zrobisz dzielenie przez 3 lub 1,5.

    Jeśli chcesz ukryć tabelę z danymi, a wykres ma dalej je pokazywać, kliknij wykres prawym przyciskiem, wybierz ZAZNACZ DANE, potem UKRYTE I PUSTE KOMÓRKI i zaznacz POKAŻ DANE W UKRYTYCH WIERSZACH I KOLUMNACH.

    Podsumowanie

    Poznałeś jedną z dziesiątek metod na wyróżnienie danych. Wykres kolumnowy jest wyjątkowo efektywny jeżeli chodzi o wizualną prezentację, tego na co chcemy zwrócić uwagę odbiorcy. Potrafi być także wyjątkowo efektowny, jeżeli włożymy w niego nieco więcej pracy.

    A Ty, jakie znasz sposoby na wyróżnianie danych? A może masz ciekawe pomysły, których nie mogłeś do tej pory zrealizować? Podziel się z nami w komentarzu. Może wspólnie stworzymy coś wyjątkowego.

  • Funkcja WYSZUKAJ PIONOWO na 5 sposobów

    Funkcja WYSZUKAJ PIONOWO na 5 sposobów

    Funkcja WYSZUKAJ PIONOWO: 5 sposobów użycia, o których pewnie nie wiesz

    Funkcja WYSZUKAJ PIONOWO chyba powoli odchodzi do lamusa… W każdym razie, jeśli jesteś analitykiem, tudzież potrzebujesz bardziej elastycznych narzędzi – pewnie używasz relacji w Power Pivot. A jeżeli dysponujesz nową wersją Excela – funkcji X.WYSZUKAJ.

    Dla całej reszty z nas (większości?) funkcja WYSZUKAJ PIONOWO, nadal może być i będzie użytecznym narzędziem. Jeśli używasz jej na co dzień, to ten artykuł pomoże Ci poszerzyć zakres możliwości. A jeśli masz z nią jakieś trudności – polecam najpierw lekturę innych moich artykułów.

    Przeczytaj również:

    5 żarówek - 5 sposobów na użycie funkcji WYSZUKAJ PIONOWO
    Źródło: Canva.com

    Ściągnij plik początkowy, jeżeli chcesz przećwiczyć rozwiązania opisane w artykule.

    Policz prowizję, dzięki najlepszemu dopasowaniu

    Musisz policzyć odpowiednią wartość prowizji, na podstawie wartości sprzedaży. Na przykład sprzedawca, który zrealizuje sprzedaż do 10 000 zł – nie otrzyma prowizji. Za to ten, który zrealizuje sprzedaż od 10 000 zł włącznie do 20 000 zł – otrzyma prowizję w wysokości 1% sprzedaży.

    Zazwyczaj takie zadanie realizujemy budując zagnieżdżoną formułę z funkcją JEŻELI. I to jest ok, dopóki masz mało warunków. Im więcej warunków, tym bardziej złożona staje się formuła. A dla higieny naszych arkuszy (a raczej ograniczaniu ryzyka popełnienia błędów) powinniśmy upraszczać formuły.

    Niewiele osób wie, że w takich przypadkach można wykorzystać WYSZUKAJ PIONOWO, używając w czwartym argumencie: 1 (PRAWDA) – najlepsze dopasowanie, zamiast 0 (FAŁSZ) – dokładne dopasowanie.

    Funkcja WYSZUKAJ PIONOWO - najlepsze dopasowanie

    W powyższym przykładzie, dla wartości >= 10 000 zł i mniejszej niż 20 000 zł, funkcja zwróci prowizję 1%. Z kolej dla sprzedaży >=20 000 zł i mniejszej niż 30 000 zł, prowizja wyniesie 2%. I tak dalej. Tak właśnie działa WYSZUKAJ PIONOWO z najlepszym dopasowaniem.

    Jak pewnie zauważyłeś wartości wyszukiwane (pierwsza lewa kolumna w tabeli źródłowej) są posortowane rosnąco. To nie przypadek, tylko warunek konieczny, aby funkcja zwracała prawidłowe wartości.

    Wyszukaj niekompletną wartość

    Zdarza Ci się, że dane źródłowe w których wyszukujesz mają nieco inną postać niż byś sobie życzył? Mnie tak 🙂 Jest sposób i na to.

    W przykładzie poniżej, chcemy znaleźć ceny dla naszych produktów: krzesła, stołu, ławki i kanapy. Ale dane źródłowe z cenami, ściągnięte z systemu, zawierają jeszcze dodatkowe oznaczenia. Mamy zatem nazwę naszego produktu – Krzesło – wpisaną jako fragment większego ciągu znaków 1_Krzesło_WXX_2020.

    Mógłbyś podzielić dane źródłowe, za pomocą narzędzia TEKST JAKO KOLUMNY, ze wstążki DANE ale jak zwykle – jest lepszy sposób. W Excelu (i nie tylko) – „*” (gwiazdka) – zastępuje dowolny ciąg znaków. Okazuje się, że jeżeli użyjesz jej w WYSZUKAJ PIONOWO to spełni dokładnie taką rolę. Wpisując:

    "*"&"Krzesło"&"*"

    dajesz znać, że szukasz dowolnego ciągu znaków, który w środku zawiera słowo „krzesło”. Zobacz jak to zrobić na poniższym przykładzie.

    Funkcja WYSZUKAJ PIONOWO - wyszukiwanie niekompletnej wartości

    Wyszukiwanie częściowe z zamianą tekstu na liczbę

    W poprzednim akapicie pisałem, o problemie jaki mamy w użyciem WYSZUKAJ PIONOWO, gdy poszukiwane przez nas wartości mają zmienioną formę. Tak jest w sytuacji, gdy szukasz tylko fragmentu jakiegoś tekstu – powiedzmy identyfikatora produktu, a w danych źródłowych ten identyfikator jest „zaszyty” w identyfikatorze transakcji.

    I ponownie, dzięki połączeniu WYSZUKAJ PIONOWO z inną funkcją jesteś w stanie sobie doskonale poradzić. W naszym przykładzie id produktu występuje jako pierwsze cztery znaki w id transakcji. Możemy zatem użyć funkcji LEWY, aby wyciągnąć te pierwsze 4 znaki i użyć ich jako argumentu w WYSZUKAJ PIONOWO.

    Gdyby okazało się, że poszukiwany przez Ciebie fragment jest w innym miejscu (na przykład w środku) możesz analogicznie użyć funkcji FRAGMENT TEKSTU.

    Wyszukiwanie częściowe

    Oglądając przykład, zauważysz, że po fragmencie z funkcją LEWY dodaje zero. Dlaczego? Ponieważ wyciągnąłbym id produktu jako tekst, a w danych źródłowych ten sam identyfikator występuje jako liczba. Jak wiesz dla WYSZUKAJ PIONOWO to znacząca różnica. Użycie operacji matematycznej, w tym przypadku dodawania zamienia tekst na liczby, a żeby nie zmieniać wartości id – po prostu dodaję zero. Dla wnikliwych – odejmowanie zadziała podobnie, ale mnożenie już nie. Jeżeli wiesz dlaczego nie – pochwal się w komentarzu 😉

    Uniknij zera w wyniku wyszukiwania

    Jeżeli w danych źródłowych nie będzie poszukiwanej przez WYSZUKAJ PIONOWO wartości, to funkcja zwróci błąd: N/D! – błąd wartości niedostępnej. Jeśli natomiast poszukiwana wartość będzie, ale nie będzie do niej przypisanej „korespondującej” wartości, której poszukujemy (pusta komórka) – to w wyniku dostaniemy 0 (zero).

    Aby tego uniknąć (zera), do funkcji WYSZUKAJ PIONOWO wystarczy „dokleić” tzw. pusty znak, czyli dwa cudzysłowy „”. Będzie to wyglądało następująco:

    =WYSZUKAJ.PIONOWO(E11;$B$11:$C$18;2;0)&""
    Funkcja WYSZUKAJ PIONOWO - zamiana zera na pusty znak

    WYSZUKAJ PIONOWO z dwoma warunkami wyszukiwania

    Standardowo, funkcja WYSZUKAJ PIONOWO działa z jednym warunkiem wyszukiwania. Co jeśli masz dwa warunki? Przykładowo, masz zamiar zrobić podwyżki pracownikom na podstawie oceny ich wynagrodzenia (A – oznacza wynagrodzenie poniżej benchmarku, B – jesteśmy w benchmarku, a C – powyżej benchmarku) Jeśli mielibyśmy na tym poprzestać, to standardowe podejście nam wystarczy.

    Jednak, gdy chcielibyśmy różnicować podwyżki także w zależności od działu w jakim ktoś pracuje, to już musimy pokombinować. Jedną z metod jest wykorzystanie: nazw oraz funkcji ADR.POŚR.

    la każdego działu definiujemy inną tabelę podwyżkową, nazywamy jej zakres tak jak nazwane są nasze działy: Sprzedaż, Finanse, Logistyka. Za pomocą funkcji ADR.POŚR przekształcisz tekst „Sprzedaż” na zdefiniowaną wcześniej nazwę. Żeby lepiej zrozumieć działanie całego mechanizmu, pobierz plik w którym znajdziesz końcowe rozwiązania i więcej szczegółów dotyczących funkcji ADR.POŚR.

    Całość wygląda tak:

    {=WYSZUKAJ.PIONOWO(D11;ADR.POŚR(C11);2;0)}

    Nawiasy klamrowe oznaczają, że jest to formuła tablicowa (musisz ja zatwierdzić kombinacją klawiszy CTRL + SHIFT + ENTER) Jest to konieczne, ponieważ funkcja ADR.POŚR zwraca zakres komórek, a nie pojedynczą komórkę.

    Funkcja WYSZUKAJ PIONOWO - wyszukiwanie na podstawie dwóch warunków

    Podsumowanie

    Jestem ciekawy, czy znałeś takie zastosowania funkcji WYSZUKAJ PIONOWO. Nie zawsze są one popularne i powszechne, a jak widzisz ułatwiają życie.

    A może znasz jeszcze inne? Podziel się z nami w komentarzu.

  • Funkcja PMT czyli jak policzyć ratę kredytu

    Funkcja PMT czyli jak policzyć ratę kredytu

    Funkcja PMT, czyli jak policzyć ratę kredytu

    Masz kredyt? A może zastanawiasz się nad pożyczką? Byłoby dobrze, gdybyś potrafił sam policzyć jaka będzie rata do spłaty. Podstawą takich obliczeń jest funkcja PMT.

    Zakupy. Wymiana pieniądze za towar. Kredyt. Obliczanie raty za pomocą funkcji PMT w Excelu.

    Funkcja PMT: jak działa

    Funkcja wyliczy ratę kredytu lub pożyczki, czyli kapitał plus odsetki w danym okresie. Założenie jest takie, że zarówno raty, jak i stopa procentowa jest stała w całym okresie kredytowania. Na marginesie, szkoda że w Polsce nie ma kredytów hipotecznych ze stałą stopą oprocentowania…

    Funkcja PMT: składnia i argumenty

    Składnia funkcji PMT wygląda tak:

    PMT(Stopa;Liczba_rat;Wa;Wp;Typ)

    Poniżej znajdziesz wyjaśnienie każdego argumentu:

    • Stopa – oprocentowanie, czyli stopa procentowa w ujęciu rocznym. Jeśli okresy są na przykład miesięczne, to stopę procentową musisz podzielić przez liczbę okresów (jeżeli wydaje Ci się to skomplikowane – spokojnie, poniżej znajdziesz przykłady)
    • Liczba_rat – liczba wszystkich okresów, przez które spłacasz kredyt
    • Wa – wartość aktualna [ang. PV – Present Value] kredytu / pożyczki, czyli kwota, którą pożyczasz
    • Wp – wartość przyszła [ang. FV – Future Value] kredytu / pożyczki. Jeżeli pominiesz ten argument, to funkcja przyjmie 0. W końcu każdy kredyt trzeba spłacić do zera 🙂
    • Typ – argument, który mówi o tym czy raty są płacone: na końcu każdego okresu – wtedy 0; czy na początku – wtedy 1. Jeżeli pominiesz funkcja przyjmie z automatu 0.
    .

    Przykład użycia

    Przechodząc do przykładu, weźmy sytuację w której zastanawiasz się nad kredytem z banku na 5 000 zł. Kredyt chcesz wziąć na 4 lata, rata będzie spłacana co miesiąc na koniec okresu, a oprocentowanie wyniesie 18%.

    Funkcja PMT

    Formuła w komórce D9, wygląda następująco:

    =PMT(D7/12;D6;-D5;0;0)

    Kilka słów wyjaśnienia. Oprocentowanie z komórki D7, jest dzielone na 12, ponieważ mamy miesięczne okresy spłaty. Z kolei, kwota kredytu z komórki D5 poprzedza znak minus. To dlatego, że biorąc kredyt zaciągasz zobowiązanie, czyli jesteś coś winny.

    Wynik tej formuły daje ratę na poziomie 146,87 zł. Jeżeli przemnożysz tę ratę przez ilość miesięcy, przez jaką będziesz spłacał kredyt powinieneś otrzymać kwotę 7050 zł. A zatem finalnie oddasz do banku o 2050 zł więcej niż pożyczyłeś.

    W pliku znajdziesz opisany powyżej przykład oraz jeszcze jeden, z rocznymi okresami spłaty. Zachęcam Cię do samodzielnych ćwiczeń. Jeżeli nie będziesz pewny czy masz dobry wynik, możesz skorzystać z zewnętrznego kalkulatora w celu weryfikacji (tylko dla miesięcznych okresów spłaty). A w razie pytań korzystaj z możliwości komentowania lub grupy na Facebooku.

    Na koniec pamiętaj, aby zadłużać się z głową i z wykorzystaniem Excela! 🙂

  • VBA IF THEN czyli jak sterować kodem

    VBA IF THEN czyli jak sterować kodem

    VBA IF…THEN… – czyli jak kontrolować kod

    Jeśli czytałeś poprzednie artykuły z cyklu „Podstawy VBA”, to teraz nadeszła chwila prawdy! Instrukcja VBA IF…THEN w połączeniu z wcześniejszą wiedzą pozwoli stworzyć Ci pierwszy użyteczny kod.

    VBA IF…THEN (dla jasności JEŻELI…WÓWCZAS…) pozwoli Ci sterować, jaką drogą powinien „iść” Twój program. Jeśli znasz funkcję JEŻELI, to pójdzie Ci naprawdę szybko 🙂

    Haker. Sterowanie kodem.
    Źródło: Pixabay.com

    Przeczytaj również:

    VBA IF: podstawy i składnia

    Tak naprawdę, tego typu instrukcja warunkowa jest chyba podstawą każdego języka programowania. Piszę „chyba”, bo nie jestem programistą. Ale na tyle na ile rozumiem pojęcie algorytmu, to w każdym języku potrzebna jest instrukcja, która pozwoli programiście sterować „zachowaniem” programu.

    Podobnie jest w języku VBA, a służy do tego między innymi instrukcja IF…THEN. Idea jest taka: jeżeli w trakcie działania programu trafiasz na dwie lub więcej możliwości to instrukcja pozwala zdecydować co ma się wydarzyć dalej. Graficznie można to pokazać jak na poniższym obrazku.

    VBA If Then - algorytm decyzji

    Dla przykładu, weźmy taki fragment Twojego kodu, gdzie dalsze działanie programu uzależnione jest od wartości wpisanej do komórki A1. Jeżeli użytkownik wpisze coś do komórki (PRAWDA), to ma nastąpić dalsze działanie, sprawdzające czy wartość jest liczbą czy tekstem. Jeżeli komórka A1 pozostaje pusta – nic się nie dzieje, a w zasadzie program kończy działanie nie wykonując żadnej operacji.

    VBA IF: 1 czy 2 linie kodu

    Składnia kodu VBA w podstawowej postaci, wygląda następująco:

    IF Range("A1")<>"" Then Range("A2").Value = Range("A1").Value

    W powyższym przykładzie, program sprawdza zawartość komórki A1. Jeśli komórka nie jest pusta (<>dwa znaki cudzysłowu), to komórka A2 przyjmie wartość taką samą jak komórka A1. W przeciwnym razie – nic się stanie i program zakończy swoje działanie.

    Opisaną wyżej instrukcję możesz również umieścić w dwóch liniach kodu. W takim przypadku na końcu musisz dodać wyrażenie End If, które zamyka działanie całej instrukcji.

    IF Range("A1")<>"" 
 Then Range("A2").Value = Range("A1").Value
End If

    VBA IF…ELSE…ELSEIF…

    No dobrze, ale do tej pory nie mieliśmy okazji do rzeczywistego sterowania programem. Czyli nie pisaliśmy co ma się wydarzyć, jeżeli pierwszy warunek jest niespełniony. Program po prostu kończył działanie. Teraz to nadrobimy.

    Weźmy taki przykład. Program ma sprawdzić zawartość komórki A1, do której powinien być wprowadzana litera oznaczająca region (N oznacza Północ, S – Południe, W – Zachód, E – Wschód) Program ma zamienić literę na jeden z czterech regionów. Jeśli komórka nie będzie miała oznaczenia: N, S, W, E to powinien pojawić się napis „Brak”.

    Jak widzisz mamy tutaj do czynienia z więcej niż jednym warunkiem. Musimy bowiem sprawdzić, czy A1 = „N”, a jeżeli nie, to czy równa się „S”, a jeżeli nie…itd. Aby wprowadzić takie wielowarunkowe sprawdzenie, w wyrażeniu trzeba użyć instrukcji ELSEIF oraz ELSE. ELSEIF pozwala wprowadzić kolejny warunek, a po ELSE wskazujesz co ma się wydarzyć w każdym innym przypadku (poza opisanymi warunkami)

    Kod będzie wyglądał tak:

    Sub Regiony()

If Range("A1") = "N" Then
    Range("A1") = "Północ"
ElseIf Range("A1") = "S" Then
    Range("A1") = "Południe"
ElseIf Range("A1") = "W" Then
    Range("A1") = "Zachód"
ElseIf Range("A1") = "E" Then
    Range("A1") = "Wschód"
Else
    Range("A1") = "Brak"
End If

End Sub

    Program zadziała jak na poniższym filmie.

    Chcesz lepiej poznać Excela? Zapisz się na Excelness News

    .

    Podsumowanie

    Znajomość instrukcji IF-THEN to kolejny krok, aby opanować podstawy VBA. W większości przypadków, będziesz mógł sterować działaniem swoich programów wykorzystując to rozwiązanie. Dla bardziej złożonych przypadków (większej ilości warunków) użyjesz wyrażenia SELECT CASE. Ale o tym w jednym z kolejnych artykułów. Nie przegap i zapisz się na newsletter, bądź dołącz do grupy na Facebooku.

  • Zmienne VBA – poznaj i wykorzystaj

    Zmienne VBA – poznaj i wykorzystaj

    Zmienne VBA. Poznaj i używaj ich stale.

    Jestem pewny, że już używasz zmiennych w Excelu. Nie muszą to być zmienne VBA. Wystarczy, że przynajmniej raz, napisałeś najprostszą formułę. Użyłeś w niej komórki z jakąś wartością. A wartości w komórkach mogą się zmieniać.

    A może używasz nazw? Nazwa reprezentuje wartość, zakres albo pojedynczą komórkę. Nazwa pozostaje stała, ale wartości jakie reprezentuje zmieniają się. Widzisz, że to nic skomplikowanego. Zatem do dzieła!

    Kobiety programujące na laptopie. Zmienne VBA.

    Czym są zmienne VBA i po co ich używać?

    Zmienna w VBA to nic innego jak nazwa, która służy do „przechowywania” wartości. Nazwy są przypisywane z wykorzystaniem znaku równości. Na przykład:

    mojaZmienna = Range("A1").Value

    Dlaczego używamy zmiennych? Po pierwsze, zmienne ułatwiają czytanie kodu VBA. Po drugie ułatwiają utrzymanie bardziej złożonych programów oraz pozwalają na bardziej zaawansowane kodowanie. I w końcu – oszczędzają czas!

    Wyobraź sobie sytuację, że w swoim kodzie użyłbyś 10 razy odwołania do komórki B1 w taki sposób:

    ....Range("B1").Value

    Jeżeli po jakimś czasie będziesz zmuszony zmienić to odwołanie, to czeka Cię aktualizacja wszystkich przypadków. Gdybyś użył zmiennej, na przykład tak:

     mojaDrugaZmienna = Range("B1").Value

    to w opisanej sytuacji – zmienisz tylko tę zmienną.

    Na koniec być może najważniejszy powód, aby używać zmiennych VBA. Niektórych zadań w VBA, po prostu nie zrobisz efektywnie bez ich użycia. Mam na myśli na przykład wszelkiego rodzaju pętle. Są to fragmenty kodu gdzie przetwarzasz, jeden po drugim jakieś obiekty (np. komórki w zakresie)

    Nadając nazwy swoim zmiennym, pamiętaj o tych zasadach:

    • Pierwszy znak nazwy zmiennej musi być literą
    • Nie używaj słów zastrzeżonych, takich jak: if, for, with, select etc.
    • Używaj podkreślenia i/lub wielkich liter w środku nazwy w celu zwiększenia czytelności
    • Nie używaj znaków: #$&!
    • Nazwa zmiennej nie może mieć więcej niż 255 znaków

    Rodzaje zmiennych VBA

    Wiesz już czym są zmienne VBA i dlaczego warto ich używać. Porozmawiajmy zatem o ich rodzajach oraz kiedy ich używać.

    Generalnie rzecz biorąc, wskazując typ zmiennej mówisz ile pamięci ma zarezerwować na dane jakie przypiszesz do tej zmiennej. To przyspiesza kod, zmniejsza ryzyko popełnienia błędów i jest dobrą praktyką kodowania. Ale to jest cykl dla początkujących nie-programistów, więc na tym poprzestańmy. Nie będziemy liczyć ile to bajtów i ile milisekund szybciej będzie działał Twój kod 🙂

    Najważniejsze typy zmiennych znajdziesz w poniższej tabeli.

    TYP ZMIENNEJZAKRES
    ByteLiczby całkowite w zakresie od 0 do 255
    IntegerLiczby w zakresie od -32 768 do 32 768
    LongLiczby w zakresie od -2 147 483 648  do 2 147 483 648
    BooleanPrawda / Fałsz
    DoubleBardzo duże liczby (ujemne do dodatnich) z wysoką precyzją
    miejsc po przecinku. Używamy również w przypadku %.
    StringTekst. Może być bardzo długi.
    ObjectWszelkiego rodzaju obiekty, np. wykresy.
    DateData w zakresie od 1 stycznia 0001 do 31 grudnia 9999
    VariantDowolna wartość. Może przechowywać również takie wartości
    jak: „Empty” czy „Null”. Jeśli nie przypiszesz innego typu, to zmienna przyjmie właśnie typ Variant.

    Jak deklarować zmienne VBA?

    Aby zadeklarować jakąś zmienną musisz użyć słowa kluczowego Dim. Po słowie Dim wpisujesz wybraną przez siebie nazwę (zgodną z zasadami opisanymi na początku artykułu) Następnie używasz słowa „as” oraz wskazujesz na typ zmiennej.

    Pojedynczą zmienną deklarujesz w pojedynczym wierszu, tak:

    Dim MojTekst as String

    lub kilka zmiennych w ramach jednego wiersza kodu, w taki sposób:

    Dim PierwszyWiersz as Long, OstatniWiersz as Long

    Jeżeli potrzebujesz użyć w kodzie stałej wartości, która nie będzie się zmieniała użyj słowa kluczowego Const.

    Const StawkaVat as Double = 0,23

    Deklarując jednocześnie wiele zmiennych tego samego typu, w ramach tej samej nazwy użyjesz tablicy Array, w taki sposób:

    Dim Miesiace(1 to 12) as String

    O deklarowaniu zmiennych dość łatwo zapomnieć. Ale możesz na samym sobie wymóc taki obowiązek. Wystarczy jeżeli użyjesz Option Explicit, które pojawiając się na początku każdego kodu wymusi definiowanie każdej zmiennej. Zachęcam Cię gorąco do włączenia tej opcji. W edytorze VBA, przejdź do TOOLS > OPTIONS i zaznacz REQUIRE VARIABLE DECLARATION.

    Włączanie opcji Option Explicit.

    Jedną z typów zmiennych jest Object. Dlatego, że zmienne mogą przechowywać różne obiekty Excela. Mogą nimi być na przykład: arkusz, wykres, kształt czy zakres komórek.

    Nie wystarczy, że zadeklarujesz zmienną w ten sposób:

    Dim MojZakres as Object

    Musisz następnie przypisać dany obiekt do tej zmiennej używając słowa Set.

    Set MojZakres = Range("A1:C1000")

    W przypadku innych typów zmiennych, używasz znaku równości aby przypisać wartość do zmiennej. Co prawda w składni VBA występuje w takich przypadkach słowo Let, ale jego użycie jest opcjonalne. Taki zapis:

    OstatniWiersz = Rows.Count

    będzie równoznaczny z tym:

    Let OstatniWiersz = Rows.Count

    Zakres obowiązywania zmiennych

    Zmienne VBA możesz wykorzystywać na kilku poziomach. Oznacza to, że zmienna może istnieć w ramach danej: procedury, pojedynczego modułu, czy też wszystkich procedur i modułów. Jeśli wydaje Ci się to skomplikowane, to spójrz na poniższe przykłady.

    Option Explicit

Sub MojaProcedura()
 Dim MojaZmienna as String
 ---jakiś kod---
End Sub

    W powyższym przykładzie, zmienna MojaZmienna, będzie działała tylko w ramach procedury MojaProcedura. Oznacza to że zmienna istnieje tak długo, jak długo trwa procedura. Następnie pamięć jest zwalniana. Wartości przechowywanej przez zmienną, nie będziesz w stanie wykorzystywać w innych procedurach.

    Option Explicit

Dim MojaZmienna as String 

Sub MojaProcedura()
 ---jakiś kod---
End Sub

    Umieszczając deklarację zmiennej przed procedurą sprawisz, że będzie ona dostępna również dla innych procedur (w ramach danego modułu). Co więcej, po zakończeniu działania danej procedury, wartość przechowywana przez zmienną nie znika z pamięci – możesz jej użyć w innym kodzie.

    Option Explicit

Public MojaZmienna as String 

Sub MojaProcedura()
 ---jakiś kod---
End Sub

    Jeśli użyjesz słowa Public, to zdefiniowana zmienna będzie dostępna dla wszystkich modułów i procedur w ramach danego pliku Excel. Taką zmienną możesz zadeklarować w dowolnym module. Oczywiście, nie będzie też znikała z pamięci po zakończeniu działania danego kodu.

    Zakres obowiązywania zmiennych VBA. Moduły i procedury.
    .

    Podsumowanie

    Wiesz już wszystko co powinieneś wiedzieć (na dobry początek) o zmiennych w VBA. Używaj ich, bo to nie tylko dobra praktyka i ułatwienie w czytaniu kodu. Przede wszystkim, dzięki zmiennym, możesz robić naprawdę ciekawe rzeczy w VBA. Automatyzacja pracy, przetwarzanie wielu plików, arkuszy i wierszy – to wszystko jest możliwe między innymi, dzięki zmiennym.

    Nadal masz wątpliwości lub pytania? Zadaj je w komentarzu, lub dołącz do naszej grupy na Facebooku.