Moje doświadczenia z Excelem, Accesem i programowaniem w VBA

Wpisy z tagiem: kalendarz

niedziela, 04 stycznia 2015

Skoro tworzymy własny kalendarz w Excelu, to dobrze byłoby go spersonalizować, zaznaczając ważne daty i terminy. Ponieważ w tej wersji kalendarza nie ma makr, które pozwoliłyby na zdecydowanie większą automatyzację, konieczne jest skopiowanie arkusza tak, aby każdy miesiąc był w osobnym arkuszu.

Po skopiowaniu arkusza (wystarczy kliknąć prawym przyciskiem myszy na nazwę arkusza i z menu podręcznego wybrać kopiuj), zmieniamy też jego nazwę (też prawoklik i z menu podręcznego wybieramy zmień nazwę), tak, aby każdy miesiąc miał swój arkusz.

kopiowanie arkusza

Kolejny krok to zmiana wartości w komórce A1. Dla miesiąca lutego wstawiamy datę 2015-02-01, dla marca - 2015-03-01 itd.

zmiana daty

Wszystkie wartości wewnątrz kalendarza zauktualizują się automatycznie same, nie ma potrzeby wprowadzania żadnych innych zmian.

Mając kalendarz na cały rok, możemy dodatkowo wstawić komentarze przy poszczególnych datach. Samo wstawienie prostych komentarzy oczywiście jest proste i banalne. Jak jednak wstawić obrazek do komentarza?

obrazek w komentarzu

 

Pierwszy krok to wstawienie zwykłego komentarza. Następnie klikamy prawym przyciskiem myszy w krawędź komentarza (na czarną linię obramowania)

edycja komentarza

Z menu podręcznego wybieramy opcję Formatuj komentarz:

edycja komentarza

W formularzu wybieramy zakładkę Kolory i linie:

edycja komentarza

Po kliknięciu w Kolor wybieramy opcję Efekty wypełnienia:

edycja komentarza

Pojawi się nowy formularz, w którym wybieramy zakładkę Obraz, a następnie poprzez naciśnięcie przycisku Wybierz obraz -  wyszukujemy w komputerze obraz, który chcemy wstawić:

wstawianie obrazu do komentarza

Potem wystarczy zatwierdzić i już. Efekt końcowy wygląda tak:

wstawianie obrazu do komentarza

 

sobota, 03 stycznia 2015

Kolejna notka związana z tworzeniem własnego kalendarza to opis "kolorowania" dni świątecznych.

formatowanie warunkowe

Kolumny G i H są pokolorowane na niebiesko i czerwono, aby zaznaczyć soboty i niedziele. Co jednak ze świętami? W miesiącu styczniu 2015 jest to np. dzień 6 stycznia. Owszem, można też z góry zmienić kolor czcionki w tej komórce, ale jeżeli zmienimy kalendarz na inny miesiąc - to też zostałaby na czerwono i zrobi się problem, gdyż dzień np. 6 lutego już świętem nie jest. Na szczęście Excel jest dynamiczny i bez problemu sobie z tym poradzi.

W skoroszycie założyłam dodatkowy arkusz "święta".

frormatowanie warunkowe

Wypisałam tam daty wszystkich dni świątecznych w roku 2015. Dodatkowo, aby łatwiej odwoływać się do tych dat w funkcjach - zdefiniowałam zakres:

zakres

Dla przypomnienia - definiowanie zakresu opisałam tu:

Nazwa obszaru w Excelu

Nie jest to konieczne, ale ułatwia pracę.

Kolejny krok to ustawienie formatowania warunkowego dla tych dat, które są na liście świąt. Wykorzystuję tu formatowanie warunkowe za pomocą funkcji. Przy bardziej skomplikowanych formułach formatowania warunkowego robię to często tak, że formułę tworzę w dowolnej komórce arkusza i sprawdzam wynik (formatowanie zadziała  dla wyniku funkcji Prawda czyli 1), a następnie kopiuję formułę do formularza formatowania warunkowego, usuwając ją jednocześnie z komórki arkusza.

W tym przypadku zaczęłam od komórki B5

formatowanie warunkowe

 

Formuła tu zastosowana to:
=JEŻELI(B5<>"";JEŻELI(CZY.BRAK(PODAJ.POZYCJĘ(B5;Swieta;0));;1))
Na początek sprawdzam, czy komórka nie jest pusta. Jeżeli nie - to za pomocą funkcji Podaj.Pozycję szukam zawartej w niej daty na liście świąt czyli w obszarze zdefiniowanym jako święta. Jeżeli znajdzie - to wynik działania całej funkcji wyniesie 1. I tym samy włączy się formatowanie warunkowe czyli nastąpi zmiana koloru czcionki na czerwoną. Jeżeli daty nie ma na liście (a więc nie jest to święto), funkcja Podaj.Pozycję zwróci błąd #N/D! - dlatego "opakowałam" ją w funkcję Czy.Brak - w ten sposób otrzymuję wartość logiczną odpowiadającą na pytanie: jest czy nie jest na liście.
Na końcu jeszcze modyfikuję zakres działania tej formuły na obszar dat całego kalendarza:

formatowanie warunkowe

 

Dodatkowo, również formatowaniem warunkowym wyróżniam bieżącą datę. Zasada identyczna, tylko inna funkcja:
=JEŻELI(B5=DZIŚ();1)
Jeżeli data w komórce jest równa wartości zwracanej przez funkcję Dziś() - to funkcja zwraca wartość 1 (czyli prawda) i uruchamiania się mechanizm formatowania warunkowego. W tym przypadku - ustawiłam zielone tło i zmianę czcionki na kursywę.



Kontynuując wątek tworzenia własnego kalendarza pora przyporządkowanie dat do poszczególnych dni tygodnia.Tu akurat w każdym tygodniu będą inne formuły.

Pierwszy tydzień

daty w kalendarzu

W komórkach B5:H5 musimy wyszukać - w jakiej kolumnie (czyli w jakim dniu tygodnia) znajdą się pierwsze dni tygodnia. Punktem wyjścia jest data zapisana w komórce A1 czyli pierwszy dzień danego miesiąca.
Najprościej jest w komórce B5 - wystarczy po prostu sprawdzić, czy data w A1 to poniedziałek. Formuła to:
JEŻELI(DZIEŃ.TYG($A$1;2)=1;$A$1;"")
Działa w ten sposób, ze jeżeli dzień tygodnia daty zapisanej w A1 jest równy 1 (czyli poniedziałek) - to komórka B5=A1. Jeżeli nie - to wynikiem działania funkcji jest pusty string. 

Dla komórek C5 do H5 potrzebna jest bardziej rozbudowana funkcja. Przede wszystkim musimy sprawdzić, czy komórka z lewej strony jest pusta. Jeżeli tak - to znowu sprawdzamy numer dnia tygodnia. Jeżeli natomiast w komórce po lewej stronie jest data - to zwiększamy ją o 1. Formuła dla C5 wygląda tak:
=JEŻELI(B5="";JEŻELI(DZIEŃ.TYG($A$1;2)=2;$A$1;"");B5+1)

Drugi i trzeci tydzień

Drugi i trzeci tydzień w każdym miesiącu są akurat pełne, więc nie ma problemu - w kolejnych komórkach wystarczy tylko zwiększać datę o 1 w stosunku do komórek poprzednich.
Dla B6 formuła to:
=H5+1
Dla C6:
=B6+1
W pozostałych komórkach zakresu B6:H7 - analogicznie.

Czwarty, piąty i szósty tydzień

Teoretycznie czwarty tydzień każdego miesiąca też z reguły jest pełnym tygodniem, wiec pewnie też wystarczyłoby zastosować formuły analogiczne do tych, jakie występują w drugim i trzecim tygodniu. Zastosowałam tu jednak także, obowiązkowe dla piątego tygodnia sprawdzanie, czy dany miesiąc już się skończył.
Formuła np.dla komórki B8:
=JEŻELI(H7="";"";JEŻELI(H7=NR.SER.OST.DN.MIES(H7;0);"";H7+1))
dla C8:
=JEŻELI(B8="";"";JEŻELI(B8=NR.SER.OST.DN.MIES(B8;0);"";B8+1))
Sprawdzam najpierw, czy poprzednia komórka jest pusta. Jeżeli tak - to wartość komórki bieżącej również jest pusta. Jeżeli nie - to następuje sprawdzenie, czy zawarta tam data nie jest datą ostatniego dnia miesiąca i w przypadku prawdy - funkcja wstawia pusty string, a w przypadku fałszu - zwiększa datę o 1.


Przydatne opisy funkcji:

Jeżeli()

Dzień.Tyg()

Eomonth() , a od Excela 2010 - Nr.SER.OST.DN.MIES()

piątek, 02 stycznia 2015

Podstawą każdego kalendarza są daty w nim zawarte. To oczywiste. Podobnie jest w Excelu. Chcąc stworzyć własny kalendarz - na przykład taki jak opisany w notce:

Jak zrobić sobie własny kalendarz

też musimy zacząć od daty. Na wszelki wypadek przypominam, że data w Excelu to liczba odpowiadająca liczbie dni od 1 stycznia 1900r. Szczegóły są tu:

Data i czas to liczby

Chcąc wpisać prawidłową datę - musimy użyć standardu: RRRR-MM-DD. Choć nie oznacza to, że taki format musi być widoczny również na ekranie - sposób prezentacji danych to już kwestia odpowiedniego sformatowania komórki.
W moim kalendarzu widać to w komórce A1, gdzie wpisana jest data pierwszego dnia miesiąca, którego dotyczy dana karta kalendarza. 

formatowanie daty

Jak widać na pasku formuły - wpisana jest data 2015-01-01, natomiast w samej komórce A1 widnieje styczeń 2015. Wynika to z niestandardowego formatowania komórki:

formatowanie daty

W tym przypadku zastosowałam format mmmm rrrr - w ten sposób wyświetlana jest pełna nazwa miesiąca oraz rok w postaci 4 cyfr.

W części głównej kalendarza czyli komórkach B5:H9 też zastosowałam formatowanie niestandardowe, tym razem jednak wskazując sam dzień miesiąca:

 

formatowanie daty

W tym przypadku, ponieważ efekt końcowy ma być tylko liczbą oznaczającą kolejne dni miesiąca, formatowanie to: dd.

I tyle w temacie dat - kolejna notka będzie poświęcona formułom zawartym w części głównej kalendarza.


Początek Nowego Roku to także konieczność zmiany kalendarza. Jak zrobić sobie własny w Excelu? Nie jest to wcale trudne.

Może na przykład tak?

kalendarz

Link do skoroszytu Excel z projektem tego kalendarza jest tu:

Kalendarz

Kalendarz jest bez makr. Uwzględnia święta (z roku 2015),a aktualny dzień jest na jasnozielonym tle. Jest elastyczny - wystarczy zmienić datę początkową (i jeżeli miałby być na inny rok niż 2015 - zaktualizować także daty świąt ruchomych).
Arkusz można skopiować i przypisać do kolejnych miesięcy, dodatkowo wprowadzając także własne komentarze z istotnymi datami.

A jak został zrobiony? Wykorzystałam tu kilka funkcji Excela oraz formatowanie warunkowe.
W kolejnych notkach opiszę je wszystkie po kolei:

Własny kalendarz - najważniejsze są daty

Własny kalendarz - dni tygodnia

Własny kalendarz - zaznaczamy święta

Własny kalendarz - wstawianie obrazka do komentarza




 

Kurs Excel - zaawansowane funkcje i formuły

| < Lipiec 2017 > |
Pn Wt Śr Cz Pt So N
          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            


Książki warte polecenia
zobacz szczegóły...


A tu oferta na dziś:





ministat liczniki.org



Napisz do mnie!