Kod VBA z internetu

Myślę, że czas najwyższy na dalsze wtajemniczenie w programowanie w Excelu w języku VBA. Znamy już podstawowe informacje o tym języku. Umiemy też zarejestrować makro w Excelu. Dziś zapiszemy i uruchomimy kod VBA, który zdobyliśmy "skądś" - w tym przypadku z internetu.

Nikt nie jest wszechwiedzący. Często więc w poszukiwaniu odpowiedzi przeszukujemy internet i zazwyczaj znajdujemy tam odpowiedzi. W końcu, jeśli czegoś nie ma w internecie, to zapewne nie istnieje.

Załóżmy, że mieliśmy przed sobą jakieś trudne zadanie w Excelu i znaleźliśmy odpowiedni kod VBA w internecie. Ktoś napisał taki kod i udostępnił go za darmo. Jak w takim przypadku wstawić ten kod VBA do swojego arkusza w Excelu?

Diagram Pareto

Nauczmy się tworzenia Diagramu Pareto w Excelu. Tak wygląda przykładowy:

Diagram Pareto jest zaawansowanym rodzajem wykresu w Excelu. Składa się zarówno z wykresu kolumnowego i wykresu liniowego. Przedstawia on graficzną częstotliwość występowania pewnych czynników (np. przyczyn reklamacji czy usterek). Stosuje się go głównie w kontroli jakości, aby pokazać, na czym głównie się skupić aby uzyskać najlepsze efekty (np. wyeliminowanie jednej usterki może zmniejszyć ilość reklamacji o połowę).

Pomysłodawcą Diagramu Pareto był włoski ekonomista Vilfredo Pareto. Podczas swoich badań doszedł on do wniosku, że 80 % majątku kraju jest w posiadaniu zaledwie 20 % ludzi. W ten sposób powstała tzw. Zasada Pareto, która opiera się na proporcji 20 / 80.

Zgodnie z Zasadą Pareto:

• 20 % klientów składa 80 % reklamacji
• 20 % wysiłków powoduje 80 % efektów
• 20 % klientów generuje 80 % dochodów firmy
• 20 % przyczyn powoduje złożenie 80 % reklamacji

Przykładów zastosowania Zasady Pareto można podać wiele.

Dzięki swojej prostocie Zasada Pareto stosowana jest m. in. w zarządzaniu jakością. To właśnie w tej dziedzinie bardzo przydatny jest Diagram Pareto (zwany też czasem jako Diagram Pareto - Lorenza).

Zobaczmy, jak stworzyć Diagram Pareto w Excelu.

Funkcja INDEKS

Omówię funkcję INDEKS. INDEKS jest jedną z najbardziej niewiarygodnie skutecznych funkcji w Excelu. Funkcja INDEKS to funkcja wyszukiwania i adresu podobnie jak omawiane już na blogu funkcje WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO. Funkcje te mają bardzo szerokie zastosowanie, ale dzięki funkcji INDEKS możemy o nich zapomnieć.

Funkcja indeks to podobnie jak formatowanie warunkowe i tabele przestawne najbardziej przydatne (i niedoceniane zarazem) możliwości Excela. Wg mnie to niezbędnik każdego użytkownika Excela.

Wróćmy jednak do funkcji INDEKS. Omówimy ją na przykładzie z poniższego obrazka, który przygotowałem. Jest to raport sprzedaży z podziałem na kwartały.

Dzięki funkcji INDEKS nauczymy się, jak wyszukiwać poszczególne dane z raportu w Excelu. Funkcja INDEKS ma 2 różne wersje - formę tablicową i formę odwołaniową. Omówmy każdą z nich.

Jak stworzyć harmonogram spłat kredytu w Excelu?

Witajcie! Dziś wpis dla bardziej zaawansowanych użytkowników Excela. Mimo wszystko myślę, że początkujący też dadzą sobie radę. Musicie tylko widzieć, co to są względne i bezwzględne adresy komórek i jak działa funkcja PMT.

W tym poście dowiemy się, jak stworzyć harmonogram spłat kredytu w Excelu. Będziemy korzystać z trzech różnych funkcji finansowych. W poprzednim poście dowiedzieliśmy się już, jak obliczyć wysokość raty przy użyciu funkcji PMT. Dziś nauczymy się jeszcze funkcji PPMT i IPMT.

Funkcja PPMT służy do obliczania części kapitałowej w racie dla określonego okresu, przy założeniu stałej wysokości raty i stałego oprocentowania. Funkcja PPMT wygląda następująco:

=PPMT(stopa;okres;liczba_rat,wa;[wp];[typ])

Poszczególne elementy można wyjaśnić jako:

• stopa to wysokość oprocentowania kredytu;
• okres to numer kolejnej raty;
• liczba_rat to ilość rat kredytu;
• wa to wartość bieżąca czyli wysokość kredytu (piszemy z minusem);
• [wp] to wartość przyszła czyli wartość, którą chcemy otrzymać po dokonaniu ostatniej płatności;
• [typ] to rodzaj płatności - 1 oznacza płatności na początku okresu (tzw. płatności z góry), a 0 lub pominięcie oznacza płatności na koniec okresu (tzw. płatności z dołu).

Funkcja IPMT natomiast pozwoli nam obliczyć część odsetkową raty kredytu przy założeniu stałej wysokości raty i stałego oprocentowania. Składnia funkcji IPMT wygląda w ten oto sposób:

=IPMT(stopa;okres;liczba_rat,wa;[wp];[typ])

Poszczególne elementy również można wyjaśnić jako:

• stopa to wysokość oprocentowania kredytu;
• okres to numer kolejnej raty;
• liczba_rat to ilość rat kredytu;
• wa to wartość bieżąca czyli wysokość kredytu (piszemy z minusem);
• [wp] to wartość przyszła czyli wartość, którą chcemy otrzymać po dokonaniu ostatniej płatności;
• [typ] to rodzaj płatności - 1 oznacza płatności na początku okresu (tzw. płatności z góry), a 0 lub pominięcie oznacza płatności na koniec okresu (tzw. płatności z dołu).

Tyle teorii. Przejdźmy teraz do praktyki. Zobaczmy, jak stworzyć harmonogram spłat kredytu.

Funkcja PMT - obliczamy ratę kredytu

Witam wszystkich użytkowników Excela!

W dzisiejszym wpisie zajmiemy się funkcją PMT, dzięki której nauczymy się jak obliczyć wysokość raty kredytu. Funkcja PMT służy do obliczania miesięcznych rat kredytu przy założeniu stałego oprocentowania i stałej wysokości raty.  Przy użyciu tej funkcji możemy również obliczyć kwotę zwrotu z inwestycji dla stałych wpłat i stałego oprocentowania.

Funkcja PMT ma następującą postać:

=PMT(stopa;liczba_rat;wa;[wp];[typ])

Poszczególne składniki funkcji PMT można wytłumaczyć jako:

• stopa to wysokość oprocentowania w skali roku;
• liczba_rat to ilość płatności, np. ilość rat kredytu;
• wa to wartość bieżąca, np. wysokość kredytu;
• [wp] to wartość przyszła czyli wartość, którą chcemy otrzymać po dokonaniu ostatniej płatności;
• [typ] to rodzaj płatności - 1 oznacza płatności na początku okresu (tzw. płatności z góry), a 0 lub pominięcie oznacza płatności na koniec okresu (tzw. płatności z dołu).

Zobaczmy, jak używać funkcji PMT na prostym przykładzie. Postarajmy się obliczyć wysokość miesięcznej raty kredytu w wysokości 10 000 zł udzielonego na 12 rat oprocentowanego na 10 % w skali roku. Przyjmujemy płatności na koniec okresu czyli płatności z dołu.

Funkcja WYSZUKAJ.POZIOMO

Witajcie! Dziś poznamy kolejną funkcję, którą zawiera Excel.

Funkcja WYSZUKAJ.POZIOMO działa podobnie, jak poznana przez nas wcześniej funkcja WYSZUKAJ.PIONOWO. WYSZUKAJ.POZIOMO to funkcja wyszukiwania i adresu. Funkcja wyszukuje dane z tabeli ułożonych poziomo. Większość tabel w programie Excel tworzymy pionowo, więc funkcja ta jest rzadko używana. Mimo wszystko myślę, że warto ją znać.

Funkcja WYSZUKUJ.POZIOMO ma następującą postać:

=WYSZUKAJ.POZIOMO(odniesienie; tablica; numer wiersza; wiersz)

W uproszczeniu można przyjąć, że poszczególne składniki funkcji to:

=WYSZUKAJ.PIONOWO(co; gdzie; w którym wierszu; prawda/fałsz)

Ostatnia część formuły jest bardzo ważna:

• prawda to przybliżone dopasowanie;
• fałsz to dokładna wartość.

Przygotowałem tabelę sprzedaży.

Nauczmy się na podstawie powyższej tabeli, jak działa funkcja WYSZUKAJ.POZIOMO. Dzięki funkcji po wybraniu imienia pracownika pojawią się wszystkie dane na jego temat, które zawiera tabela.

Funkcja WYSZUKAJ.PIONOWO

Czołem! Przedstawiam kolejny wpis o możliwościach programu Excel.

Dziś nauczymy się kolejnej funkcji, którą zawiera Excel. Jest to funkcja WYSZUKAJ.PIONOWO. Funkcja WYSZUKAJ.PIONOWO jest funkcją wyszukiwania i odwołań. Za jej pomocą Excel w bardzo prosty sposób wyszuka i dopasuje do siebie dane, które znajdują się w dwóch osobnych tabelach.

Struktura funkcji wygląda następująco:

=WYSZUKAJ.PIONOWO(szukana wartość; tabela tablica; numer indeksu kolumny; przeszukiwany zakres)

W uproszczeniu można przyjąć, że poszczególne składniki funkcji to:

=WYSZUKAJ.PIONOWO(co; gdzie; w której kolumnie; prawda/fałsz)

Ostatnia część formuły jest bardzo ważna:

• prawda to przybliżone dopasowanie;
• fałsz to dokładna wartość.

Zobaczmy, w jaki sposób używać tych dwóch wariantów funkcji WYSZUKAJ.PIONOWO.

Obliczamy różnicę pomiędzy datami

Witam wszystkich miłośników Excela!

Dziś zajmiemy się datami w Excelu. Nauczymy się, jak obliczyć różnicę pomiędzy datami. Poznamy funkcje, które obliczą, ile pomiędzy dwiema datami upłynęło: lat, miesięcy, dni i dni roboczych.

Pobawimy się trochę datami. Może się to przydać też w biznesie. Za pomocą tych funkcji łatwo obliczymy dzień zapłacenia faktury czy dostarczenia towaru.

Przygotowałem tabelkę, w której obliczymy, ile czasu upłynęło od dnia moich narodzin (3. marca 1986 r.) do dzisiaj.

No to do dzieła!

Względne i bezwzględne adresy komórek

Komórki w Excelu mają swoje adresy. Adres komórki, która leży w kolumnie B i wierszu 10 to B10 - jest to adres względny. Adres bezwzględny to $B$10. Pozostałe możliwości to: $B10 i B$10. Znak dolara oznacza blokowanie. Zobaczmy to na przykładzie.

Stwórzmy tabliczkę mnożenia w programie Excel.

Gdybyśmy wpisali formuły używając tylko adresów względnych, to tabliczka mnożenia będzie wyglądać w taki sposób:

Musimy użyć adresów bezwzględnych. Zobaczmy jak to zrobić.

Jak zrobić listę rozwijaną?

Witam wszystkich czytelników bloga Abc Excel. Dziś nauczymy się, jak zrobić listę rozwijaną w Excelu.

Co to właściwie jest lista rozwijana? Lista rozwijana (lub lista wybierana albo lista wyboru) to bardzo pożyteczna sztuczka. Pozwala ona nam na łatwiejsze wpisanie do komórki wybranych wcześniej słów lub liczb. Lista rozwijana jest też przydatna, jeśli chcemy narzucić wpisywanie określonych treści do komórki.

Czasem chcemy, żeby wszystkie komórki w danej kolumnie były treści TAK albo NIE. My stworzymy listę rozwijaną, w której będziemy mieli możliwość wyboru marki samochodu.

W poprzednich częściach bloga zajmowaliśmy się analizowaniem sprzedaży konkretnych marek samochodów. Tabelka wyglądała w ten sposób:

Lista rozwijana, którą stworzymy będzie wyglądać tak:

Jak widać mamy możliwość wyboru spośród wszystkich marek samochodów z tabelki. Jak to zrobić?

Tabele przestawne

Witam w kolejnej części naszej przygody z Excelem!

Dziś na Wasze życzenie zajmiemy się tabelami przestawnymi. Excel może być używany nie tylko do obliczeń czy rysowania wykresów. Excel jest też stosowany do analizy danych.

Mamy tabelę z danymi o sprzedaży w przykładowej firmie.

Kolumny tabeli to:

• data
• kraj
• imię sprzedawcy
• klient
• nazwa sprzedanego produktu
• koszt sprzedaży
• wielkość sprzedaży

Tabela zawiera 2000 wierszy z danymi. Jak wyczytać coś z tak dużej ilości danych? Skorzystamy z tabeli przestawnych.

Diagram Gantta

Dzisiejszy wpis pokaże możliwości Excela. Jest on dedykowany wyłącznie doświadczonym użytkownikom Excela. Zajmiemy się stworzeniem diagramu Gantta zwanego też wykresem Gantta. Stosuje się go w zarządzaniu projektami. Jako że projekt jest podzielony na poszczególne zadania, to wykres Gantta pokazuje rozmieszczenie zadań (mini-projektów) w czasie. Podzieliliśmy więc główny projekt na mini-projekty i tworzymy wykres Gantta, żeby zobaczyć, gdzie w czasie się znajdują.

Przygotowaliśmy już sobie tabelkę z danymi.

Nauczymy się, jak stworzyć diagram Gantta w formie tabeli.

Narysujemy też wykres Gantta w formie wykresu słupkowego.

Jak to zrobić? Nauczmy się.

Wykres liniowy

Umiemy już tworzy wykres kolumnowy i wykres kołowy w Excelu. Czas nauczyć się równie popularnego wykresu liniowego. Służy on głównie do pokazania zmiany trendów w czasie. W naszym przypadku skupiamy się na sprzedaży samochodów w Polsce. Zobaczmy, jak pokazać zmianę sprzedaży Fiata i Skody w latach 2003 - 2009.

Przygotowaliśmy sobie wcześniej potrzebne dane.

Tak będzie wyglądał nasz wykres:

Zobaczmy krok po kroku, jak to zrobić.

Wykres kołowy

Wykres kołowy jest bardzo często używany zarówno dla potrzeb domowych, biurowych, jak i w biznesie. Jego popularność bierze się głównie z przejrzystości zaprezentowanych na nim danych. Wykres kołowy najlepiej nadaje się do zaprezentowania danych jako części całości. Na przykładzie naszych danych o sprzedaży samochodów możemy stworzyć taki oto wykres:

Zobaczmy, jak stworzyć taki wykres.

Zmiana położenia wykresu

Nasz wykres po wstawieniu zakrywa tabelę z danymi. Wygląda to w ten sposób:

Wykres możemy przesunąć. Wystarczy tylko umieścić kursor nad wykresem w ten sposób, aby miał kształt 4 strzałek skierowanych od środka na zewnątrz.

Tworzenie wykresu

No to zaczynamy tworzenie naszego pierwszego wykresu w Excelu 2007.

Najpierw zaznaczamy dane, które mają znaleźć się na wykresie. U nas jest to zakres A2:B11.

Wykresy tworzy się poprzez wybranie odpowiedniego rodzaju wykresu na wstążce w zakładce Wstawianie w sekcji Wykresy.

Sortowanie danych

Nauczmy się sortować dane. Jest to pierwszy etap do stworzenia wykresu w Excelu 2007.

Do nauki potrzebne są nam dane. Na poniższym arkuszu mamy przykładowo sprzedaż samochodów osobowych w Polsce w 2009 roku.

Dane nie są posortowane i czyta się je bardzo ciężko. Ułóżmy je więc w kolejności malejącej czyli od największej do najmniejszej.

Mnożenie

W poprzednich postach nauczyliśmy się dodawać. Kolejnym działaniem, które poznamy niech będzie mnożenie.

Stworzyliśmy sobie taki oto rachunek:

Tym razem, żeby obliczyć wysokość rachunku, musimy najpierw pomnożyć cenę towaru za kilogram przez jego ilość.

Zacznijmy od obliczenia ceny szynki. Zaznaczamy więc komórkę D3 i uruchamiamy przycisk Wstaw funkcję analogicznie, jak robiliśmy to przy sumowaniu. Znów wybieramy kategorię Matematyczne i wybieramy funkcję Iloczyn.

Funkcja SUMA

Ostatnim razem dodawaliśmy poszczególne komórki korzystając z zapisu =B3+B4+B5+B6+B7. Jest to skuteczny, ale uciążliwy sposób. Bo co by się stało, gdyby komórek było 50 albo 5000? W Excelu jest więc łatwiejszy sposób dodawania. Nauczymy się dziś dodawania przy pomocy funkcji SUMA.

Funkcja ta w Excelu ma postać =SUMA()

W nawias wpisujemy zakres komórek, które chcemy dodać.

Spójrzmy jeszcze raz na nasz rachunek:

Chcemy dodać ceny poszczególnych artykułów. Zaznaczamy więc komórkę, w której ma się znajdować suma.

Następnie klikamy na przycisku WSTAW FUNKCJĘ.

Dodawanie

Pamiętacie rachunek sklepowy z posta o wpisywaniu symboli walut?



Jest tam suma cen wszystkich artykułów. Nauczmy się dodawania w Excelu.

Suma za zakupy składa się z cen poszczególnych produktów. Excel tego nie zrozumie. Dla Excela łączna cena, to suma wyniku dodawania komórek B3 + B4 + B5 + B6 + B7.

Żeby dodać zawartość tych komórek do siebie zaznaczmy najpierw komórkę, w której znajdować się ma formuła.