Patrząc na zawartość komórek arkusza w Excelu musimy pamiętać, że nie zawsze to, co widzimy jest tożsame z rzeczywistością wartością danej komórki. Wszystko zależy od formatowania danej komórki.
Na screenie widoczny jest banalny przykład liczby sformatowanej (wyświetlanej) z dokładnością do 1 miejsca po przecinku czyli 0,3. Faktycznie jednak, jak jest to widoczne na pasku formuły – jest tam wartość wynikająca z podzielenia liczby z komórki A1 przez B1, a więc 1/3 czyli 0,33333333… To, o czym musimy pamiętać to fakt, że do dalszych obliczeń zawsze brana jest rzeczywista wartość komórki, a nie to, co jest wyświetlane na ekranie. Czyli jeżeli w innej komórce wstawimy formułę np.10 * C1 – otrzymamy wynik 3,3333333… Choć oczywiście ta docelowa komórka też może być sformatowana. Na przykład jako ułamek zwykły:
Takich wbudowanych formatów liczbowych (a także związanych z nimi walutowych czy księgowych, procentowych itp.) jest więcej. Możemy je wykorzystywać, aby w najlepszy dla nas sposób prezentować dane. Można też stosować formatowanie niestandardowe i samemu określić sposób wyświetlania danych. No, ale to już temat na następną notkę.
W niektórych plikach Excela znajdują się łącza do innych plików. Najczęściej są to działania świadome i celowe, pozwalające np. na agregację danych z różnych arkuszy/skoroszytów. Czasem jednak zdarza się, że łączy teoretycznie nie powinno być, a jednak są widoczne. O ich istnieniu dowiadujemy się z komunikatów wyświetlanych przy otwarciu skoroszytu. W dodatku ich odnalezienie jest trudne, nie można ich także przerwać.
Wprawdzie można wyłączyć aktualizację łączy, ale to tylko maskuje, a nie rozwiązuje problem. Brak komunikatów nie załatwia sprawy. W tego typu przypadkach najczęściej okazuje się, że łącza do innego pliku wcale nie są wstawione bezpośrednio w komórkach, a nieco bardziej “zagnieżdżone”. Na początek warto więc sprawdzić:
ustawienia poprawności danych kto wie, czy na którejś z list nie ma odwołania do innego pliku?
formatowanie warunkowe tu też mogą być łącza
Tego typu sytuacje mogą się zdarzyć, gdy na przykład kopiujemy jakiś zakres komórek z innego skoroszytu, zapominając o tym, że jest tam zdefiniowana lista poprawności danych (przećwiczyłam to na sobie, zajęło mi to sporo czasu). Jeśli plik jest mało rozbudowany – można to znaleźć ręcznie, choć czasem trzeba się naszukać. Gorzej, gdy plik zawiera wiele arkuszy, w każdym jest sporo danych, tabel i wykresów. Wówczas znalezienie takich odwołań może być naprawdę bardzo pracochłonne. Na takie przypadki jest specjalny dodatek Billa Manville’a:
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:
