W poprzedniej notce opisywałam w jaki sposób można uruchomić wbudowane do Excela dodatki Solver i Analysis ToolPack. W Excelu możemy jednak zainstalować również własne dodatki – stworzone przez nas lub pobrane gdzieś z sieci (tu zdecydowanie zalecam rozwagę, choć jest wiele sprawdzonych stron i dodatków).
Jak i po co to zrobić? Tworzenie własnych dodatków będzie tematem kolejnych notek. Generalnie jednak jest to wykorzystanie funkcjonalności (formuł czy procedur) skoroszytu zapisanych w formacie *.xlam (lub w wersjach starszych *.xla):
Po zainstalowaniu takiego dodatku zapisane w nim formuły będą działać w naszym Excelu w każdym pliku.
Instalacja takiego dodatku jest przedstawiona tu:
Przykłady ciekawych dodatków to temat na kolejne notki.
Dodatki programu Excel – czym są i po co są stosowane? Najogólniej mówiąc są to części oprogramowania dołączane do Excela (i nie tylko) po to, aby zwiększyć jego funkcjonalność. Standardowymi dodatkami w własnymi Excela są:
Solver
Analysis ToolPak
Po zainstalowaniu Excela dodatki te nie są automatycznie włączone, trzeba je dodać:
Karta Plik –> Opcje –> Dodatki
Otwiera się formularz, w którym wybieramy dodatki dostępne w domyślnym katalogu (Microsoft –> Dodatki) lub wyszukujemy dodatki znajdujące się w innym miejscu.
Dodatek włączamy poprzez zaznaczenie go na liście i zatwierdzenie przyciskiem OK.
Dodatki można też włączać bezpośrednio na karcie Developer, w grupie opcji Dodatki.
Tu też znajdują się wszystkie własne i niestandardowe dodatki. Standardowe(czyli wbudowane) dodatki Excela są widoczne na karcie Dane, w grupie Analiza.
W wielu różnych rozwiązaniach Excela np.grafiki, harmonogramy itp. istotnym elementem są daty świąt. Pewne dni są przewidywalne – Nowy Rok zawsze wypada 1 stycznia, Święto Niepodległości – 11 listopada, ale co z datami świąt ruchomych? Owszem, można zajrzeć do kalendarza i sprawdzić datę Wielkanocy czy Bożego Ciała, ale w kolejnym roku trzeba to zrobić ponownie. Na szczęście można to zrobić raz, a dobrze – wykorzystując po prostu formuły Excela.
Na rok 2019 wygląda to tak:
Po zmianie w komórce A1 roku na 2020 wygląda to tak:
Jak zrobić taką listę? W komórce A3 wyznaczającej datę Nowego Roku jest formuła: =DATA(A1;1;1) Podobnie dla A4: =DATA(A1;1;6) W analogiczny sposób ustawiamy daty świąt mających swoje stałe daty w kalendarzu. Święta ruchome powiązane są natomiast z Wielkanocą. Przypada zawsze w niedzielę po pierwszej wiosennej pełni księżyca. W Excelu wyznaczamy ją formułą w komórce A5: =ZAOKR.W.DÓŁ(DATA(A1;5;DZIEŃ(MINUTA(A1/38)/2+56));7)-34 Drugi dzień Wielkanocy (komórka A6) jest już prosty: =A5+1 Zielone Świątki to formuła: =A5+49 Natomiast Boże Ciało (komórka A10) to: =A5+60
W ten sposób można ustawić listę świąt na dany rok. Wystarczy zmienić wartość w oznaczającej rok komórce A1 i nastąpi automatyczne przeliczenie.
W poprzedniej notce pisałam, że data w Excelu to liczba Data to liczba A jak jest z czasem? Oczywiście, to też liczba choć nie całkowita, a ułamek. Dokładnie – każda godzina to 1/24 czyli wartość ok.0,0416666666666667. Analogicznie – każda minuta to 1/1440, a każda sekunda to 1/86400.
To, co widać na ekranie – to też kwestia ustawienia odpowiedniego formatu.
data
Kolumna A to liczby, kolumna B odwołuje się do sąsiednich komórek, ale jest sformatowana.
A chcąc prawidłowo wpisać oznaczenie godziny do komórki robimy to w formacie gg:mm.
Na każdym z przeprowadzanych przeze mnie szkoleń Excela, niezależnie od poziomu, zawsze przypominam: data w Excelu to liczba. I ma to duże znaczenie, szczególnie przy analizie danych. Z własnej praktyki wiem, że kreatywność użytkowników w różnych sposobach wpisywania daty do komórek Excela jest niczym nieograniczona, a potem generuje to różne problemy.
Zapamiętajmy więc – data to liczba. To, co widzimy na ekranie – ładna data, gdzie nawet miesiąc jest wpisana słownie to tylko kwestia odpowiednio sformatowanego wyświetlania na ekranie, nadal jednak jest to liczba. Jej wielkość jest wyznaczona liczbą dni jakie upłynęły od “początku świata”, a konkretnie – od 1 stycznia 1900 roku. Można się o tym przekonać samemu – w kolumnie A wstawiamy liczby, w kolumnie B – odniesienia do komórek z tego samego wiersza kolumny A (np. B1=A1). Kolumnę B formatujemy jako datę – efekt jak na obrazku:
W kolumnie A jest wszędzie wpisana liczba 43563. W kolumnie B – odwołania do do sąsiednich komórek czyli np. B2=A2, B3=A3 itd. Oznacza to, że w kolumnie B jest także ta sama liczba, tylko inaczej sformatowana czyli wyświetlana na ekranie czy na wydruku.
Jak więc wpisywać do komórek Excela wartości daty? Przecież trudno za każdym razem przekształcać wartość daty na liczbę – nie miałoby to sensu. Chcąc mieć pewność, że do komórki wpisujemy na pewno datę, robimy to w formacie: rrrr-mm-dd np.2019-04-08 Wówczas nie będzie już niespodzianek.
A przy okazji – skrót klawiaturowy Ctrl+; – wstawia do aktywnej komórki wartość bieżącej daty.
Kalkulator wyborczy czyli plik Excela przeliczający głosy na mandaty. Uwzględnia przekroczenie progu wyborczego (5% dla partii i 8% dla koalicji). Oczywiście jest oparty o metodę D’Hondta. W roku podwójnych wyborów może się przydać.
Do wypełnienia są żółte pola. W komórce D6 wpisujemy liczbę mandatów do podziału, w komórkach C4:M4 – oznaczenie T/N dla koalicji, a w komórkach C5:M5 – liczbę głosów uzyskanych przez poszczególne partie. I już całą reszta jest obliczana automatycznie.
Komórka B10 to suma wszystkich głosów czyli suma zakresu C5:M5. W wierszu 6 mamy formułę np. w C6: =C5/$B$10 – w pozostałych kolumnach wiersza 6 – analogicznie. W ten sposób obliczamy procent uzyskanych głosów dla poszczególnych partii. W komórce C7 (i analogicznie w kolumnach od D do M) jest formuła: =JEŻELI(C4=”T”;JEŻELI(C6>=8%;”T”;”N”);JEŻELI(C6>=5%;”T”;”N”)) – następuje tu sprawdzenie, w zależności od tego czy jest to koalicja czy nie, czy został przekroczony próg wyborczy.
Metoda H’ondta polega na tym, że w pierwszym kroku wstawiamy do tabeli wszystkie wyniki, w kolejnym wierszu – dzielimy te wartości przez 2, w następnym – przez 3 itd. Wyniki tych obliczeń są umieszczone od wiersza 14 w dół.
Formuły zawarte w wierszu 14 – to: np. komórka C14: =JEŻELI(LUB(C5=””;C7=”N”);0;C5) – czyli jeżeli w tabeli z danymi w kolumnie nie ma wartości lub wynik działania formuły w wierszu 7 jest “N” – jest ona pobierana do wiersza 14, jeżeli nie – ZERO.
Od wiersza 15 – formuła wygląda tak: np. w komórce C15: =JEŻELI(LUB(C14=””;C14jeżeli w komórce powyżej jest wartość co najmniej 1 – jest ona dzielona przez wartość w komórce B15 czyli 2. Analogicznie – w kolejnych wierszach.
Formuła B13 jest natomiast formuła:
=MAX.K(C14:M313;D1)
Wyznacza ona k-tą wartość maksymalną z zakresu obliczeń. Wartość k jest wyznaczona przez liczbę mandatów do podziału – zapisana jest w komórce D1. Wartości większe i równe tej wartości minimalnej są oznaczone w obliczeniach na jasnoniebiesko. Tu wykorzystałam formatowanie warunkowe:
W wierszu 8 jest wyznaczona liczba mandatów dla danej partii. Zastosowałam tu formułę: =JEŻELI(C7=”T”;LICZ.JEŻELI(C14:C313;”>=” &$B13);0)
Następuje tu sprawdzenie – ile w zakresie obliczeń dla danej kolumny występuje liczb niemniejszych od B7.
I już, wszystko jasne. – można wspomóc PKW kalkulatorem wyborczym.
Korzystając z automatycznego mailingu czyli wysyłania maili w Outlooku poprzez aplikację w Excelu czy Accessie, możemy natknąć się na blokadę Outlooka. Na ekranie pojawia się komunikat, że pewien program próbuje wysłać wiadomość. Po dłuższej chwili odblokowują się przyciski i można wyrazić zgodę, jednak przy kolejnym mailu sytuacja się powtarza. W efekcie okazuje się, że cały automatyzm przestaje być skuteczny i nie ma sensu.
Można sobie z tym poradzić za pomocą programu Outlook WarningDoctor firmy CodeTwo. Jest darmowy, bezpieczny i często polecany. Do pobrania na stronie:
Do tej pory przez 9 lat prowadziłam blog Excel, Access, VBA
na platformie blogowej Blox. Niestety, pod koniec kwietnia ’19 Agora zamyka Bloxa i trzeba znaleźć nowe miejsce w sieci. Owszem, przeniosłam stary blog na WordPress.com, ale nie wszystko wyszło tak, jak bym chciała. Przede wszystkim z większości notek zniknęły zdjęcia (a dokładnie – screeny ilustrujące opisywaną treść), wstawione linki odwołujące się do innych notek też nie działają. Blog ten jest tu: Excel, Access, VBA – archiwum na WordPress
Wszystko to można poprawić, ale każdą z prawie 600 notek trzeba przejrzeć, poustawiać wszystko na nowo i trochę traci to sens. Archiwum zostaje, ale postanowiłam zacząć od nowa. Wykorzystam swoje doświadczenia, przy okazji reorganizując swoje blogowisko. W przypadku tego blogu postanowiłam rozdzielić Excel od Accessa. Ten blog pozostanie blogiem “excelowym”, o Accessie założę nowy blog.
