Utwórz kalendarz w programie Excel z jedną formułą, używając formuły wprowadzonej w tablicy.
Spójrz na ten rysunek:
Ta formuła =Cooljest tą samą formułą w każdej komórce z B5: H10! Popatrz:
Został wprowadzony do tablicy po pierwszym wybraniu B5: H10. W tym artykule zobaczysz, co kryje się za formułą.
Nawiasem mówiąc, jest komórka, która nie jest jeszcze wyświetlana, a która jest miesiącem do wyświetlenia. Oznacza to, że komórka J1 zawiera =TODAY()(i piszę to w grudniu), ale jeśli zmienisz ją na 08.05.2012, zobaczysz:
To maj 2012. OK, zdecydowanie super! Zacznij od początku i przejdź do tej formuły w kalendarzu i zobacz, jak to działa.
Załóżmy też, że dzisiaj jest 8 maja 2012.
Najpierw spójrz na ten rysunek:
Formuła nie ma sensu. Byłoby tak, gdyby był otoczony =SUM, ale chcesz zobaczyć, co kryje się za formułą, więc rozszerzysz ją, zaznaczając ją i naciskając klawisz F9.
Powyższy rysunek staje się rysunkiem poniżej po naciśnięciu klawisza F9.
Zauważ, że po 3 znajduje się średnik - oznacza to nowy wiersz. Nowe kolumny są oznaczone przecinkiem. Więc zamierzasz to wykorzystać.
Liczba tygodni w miesiącu jest różna, ale żaden kalendarz nie potrzebuje więcej niż sześciu wierszy do reprezentowania dowolnego miesiąca, i oczywiście wszystkie mają siedem dni. Spójrz na ten rysunek:
Wprowadź ręcznie wartości od 1 do 42 w B5: H10, a jeśli wpiszesz =B5:H10komórkę, a następnie rozwiniesz pasek formuły, zobaczysz, co pokazano tutaj:
Zwróć uwagę na umieszczenie średników - po każdej wielokrotności 7 - wskazujących na nowy wiersz. To jest początek formuły, ale zamiast tak długiej, możesz użyć tej krótszej formuły. Wybierz B5: H10. Rodzaj
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
jako formułę, ale nie naciskaj klawisza Enter.
Aby powiedzieć Excelowi, że to formuła tablicowa, musisz przytrzymać Ctrl + Shift lewą ręką. Trzymając Ctrl + Shift, naciśnij Enter prawą ręką. Następnie zwolnij Ctrl + Shift. W pozostałej części tego artykułu ten zestaw naciśnięć klawiszy będzie nosił nazwę Ctrl + Shift + Enter.
Jeśli poprawnie wykonałeś kombinację klawiszy Ctrl + Shift + Enter, nawiasy klamrowe pojawią się wokół formuły na pasku formuły, a liczby od 1 do 42 pojawią się w B5: H10, jak pokazano tutaj:
Zwróć uwagę, że bierzesz liczby od 0 do 5 oddzielone średnikami (nowy wiersz dla każdego) i mnożymy je przez 7, co w efekcie daje:
Pionowa orientacja tych wartości dodana do poziomej orientacji wartości od 1 do 7 daje takie same wartości, jak pokazano. Rozszerzenie tego jest identyczne z tym, co miałeś wcześniej. Załóżmy, że teraz dodasz DZISIAJ do tych liczb?
Uwaga: Edytowanie istniejącej formuły tablicowej jest bardzo trudne. Ostrożnie wykonaj następujące kroki: Wybierz B5: H10. Kliknij pasek formuły, aby edytować istniejącą formułę. Wpisz + J1, ale nie naciskaj klawisza Enter. Aby zaakceptować edytowaną formułę, naciśnij Ctrl + Shift + Enter.
Wynik za 8 maja 2012 r. To:
Te liczby to numery seryjne (liczba dni od 01.01.1900). Jeśli sformatujesz je jako krótkie daty:
Najwyraźniej nie w porządku, ale tam dotrzesz. Co się stanie, jeśli sformatujesz je jako po prostu „d” dla dnia miesiąca:
Wygląda prawie jak miesiąc, ale żaden miesiąc nie zaczyna się od dziewiątego miesiąca. Ach, tu jest jeden problem. Użyłeś J1, który zawiera 5/8/2012, i naprawdę musisz użyć daty pierwszego dnia miesiąca. Więc załóżmy, że wstawisz =DATE(YEAR(J1),MONTH(J1),1)J2:
Komórka J1 zawiera 2012-05-08, a komórka J2 zmienia to na pierwszy dzień miesiąca tego, co zostało wprowadzone w J1. Jeśli więc zmienisz J1 w formule kalendarza na J2:
Bliżej, ale nadal nie w porządku. Potrzebna jest jeszcze jedna korekta, a mianowicie musisz odjąć dzień tygodnia pierwszego dnia. Oznacza to, że komórka J3 zawiera =WEEKDAY(J2). 3 oznacza wtorek. Więc teraz, jeśli odejmiemy J3 od tego wzoru, otrzymamy:
I tak jest właściwie na maj 2012!
Ok, jesteś naprawdę blisko. Wciąż nie tak jest to, że 29 i 30 kwietnia pojawia się w kalendarzu majowym, a także od 1 do 9 czerwca. Musisz je wyczyścić.
Możesz nadać formule nazwę, aby ułatwić odniesienie. Nazwij to „Cal” (jeszcze nie „cool”). Zobacz ten rysunek:
Następnie możesz zmienić formułę na po prostu =Cal(nadal Ctrl + Shift + Enter):
Teraz możesz zmienić formułę, aby przeczytać, że jeśli wynik jest w wierszu 5, a wynik przekracza 20, powiedzmy, ten wynik powinien być pusty. Wiersz 5 będzie zawierał pierwszy tydzień dowolnego miesiąca, więc nigdy nie powinieneś widzieć żadnych wartości powyżej 20 (lub jakakolwiek liczba powyżej siedmiu byłaby błędna - liczba taka jak 29, którą widzisz w komórce B5 na powyższym rysunku, pochodzi z poprzedniego miesiąca). Możesz więc użyć =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Po pierwsze, zauważ, że komórki B5: D5 są puste. Formuła brzmi teraz „jeśli to jest wiersz 5, to jeśli DZIEŃ wyniku przekracza 20, pokaż puste”.
Możesz kontynuować usuwanie niskich liczb na koniec - wartości z przyszłego miesiąca. Oto jak to łatwo zrobić.
Edytuj formułę i wybierz ostateczne odniesienie do „Cal”
Rozpocznij wpisywanie IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal), aby zastąpić ostatnią Cal.
Ostateczna formuła powinna być
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Naciśnij Ctrl + Shift + Enter. Wynik powinien być:
Zostały dwie rzeczy do zrobienia. Możesz wziąć tę formułę i nadać jej nazwę „Cool”:
Następnie użyj tego we wzorze pokazanym tutaj:
Nawiasem mówiąc, zdefiniowane nazwy są traktowane tak, jakby zostały wprowadzone do tablicy.
Pozostaje tylko sformatować komórki i wpisać Dni tygodnia i nazwę miesiąca. Więc poszerzasz kolumny, zwiększasz wysokość wiersza, zwiększasz rozmiar czcionki i wyrównujesz tekst:
Następnie umieść obramowania wokół komórek:
Scal i wyśrodkuj miesiąc i rok oraz sformatuj je:
Następnie wyłącz linie siatki i voila:
Ten artykuł gościnny pochodzi od MVP programu Excel, Boba Umlasa. Pochodzi z książki Excel Outside the Box. Aby zobaczyć inne tematy w książce, kliknij tutaj.
