Making twelve constellations perpetual calendar with excel

Take off the last page of the calendar of 20 15, and 200 15 walks towards us with brisk steps. Every New Year, one thing I want to do is to replace the old desk calendar on my desk. Is it getting more troublesome every year? Today, we create a perpetual calendar through Excel, which can not only browse 1900 at any time? The date of any year between 2 100 is also equipped with twelve constellations, which is very beautiful.

First, customize the frame of the calendar.

Step 1: Run Excel 2003 and put? Sheet 1？ The worksheet was renamed to? Perpetual calendar Press Ctrl+A to select the entire worksheet, and then execute? Format? Column? Column width? Command, enter it in the pop-up dialog box? 2.4? , click? Are you sure? Button to adjust the column width of the entire worksheet to 2.4.

Step 2: Select R5? X6 cell, right-click the selected area, and select? Format cells? Command, and then in the Cell Format dialog box? Alignment? Select in the tab? Merge cells? Item to merge the selected cells. After the perpetual calendar is finally made, just enter the year in which the calendar was generated here and press Enter to automatically generate the calendar for twelve months of that year.

skill

Select the year from the drop-down list.

In cell R5, in addition to manual input, we can also set a drop-down list for users to choose: select cell BD 1 (BD column is mainly selected to prevent the column from being displayed in the main interface and affecting the appearance) and enter? 1900? , select BD2 cell and enter? 190 1? , select two cells at the same time and drag? Fill handle? Fill the following cells in sequence to the number 2100; Click to select the just merged R5 cell, and execute? Data? Effectiveness? Order, in? Data validity? In the dialog box? Is it allowed? Select from the drop-down menu? Sequence? , in? Source? Enter a formula in the column? =$BD$ 1:$BD$20 1？ (see figure 1), click? Are you sure? Button, at this time, as long as the R5 cell is selected, a small button will appear on its right. Click it to select the year from the drop-down list (1900? 2 100).

Figure 1

Step 3: Select F 1 1? L 1 1 cell, merge, and press enter? January? (January), centered, set the appropriate font and size; Then in turn in F 12? Enter the English abbreviation of the week in cell L 12 (for example, Sunday, Monday, Tuesday, etc.). You can also use Chinese? Day? 、? One? 、? Two? To replace); Click Next? Insert? Pictures? From the file? Command, insert a picture of twelve constellations prepared in advance (available from the download package provided in this article), and adjust the line height of 10 to make it consistent with the height of the picture. The final perpetual calendar will automatically generate the date of the end of the week, so here we reserve 6 lines of spaces for the date. The remaining 1 1 calendar can be made in the same way, as shown in Figure 2.

Figure 2

Second, put on a beautiful coat for the calendar.

Let's further beautify the calendar we just made.

Step one: execute? Format? Worksheet? Background? Command, select the prepared background picture in the dialog box (provided in the material package of this article) to add the background to the worksheet.

Step 2: Choose E9? AK37 cell area, right-click the selected area and select? Format cells? Orders. In the open cell format dialog box? Pattern? Labels, settings? Cell shading? White; Are you online? Border? Tabs for this cell range? Light blue? Thick solid border of color.

Step 3: Select column A, column B and an? AZ column, line 1, line 2 and line 4 1? 50 lines (press Ctrl to check), set? Cell shading? Set to white. The purpose of this setting is to highlight the main part of the calendar. For the sake of beauty, we can change it to C3? Add a thin black solid border to the AM40 cell area.

Step 4: Select 6 rows of cells corresponding to Saturday and Sunday of each month, right-click to pop up the cell format dialog box, and click? Font? Tab, set the font color to red. Then, set the font color, font style and font size of the year and month names respectively.

Step 5: The year (R5) cell is a bit monotonous, so let's add a border to decorate it. Choose O4 respectively? AA7 and L5? AD6 cell area, add a color double-line border for each of these two areas, and adjust the height of 4 rows and 7 rows after adding to make the border look more beautiful.

Step 6: Click? Tools? Options? Commands in the options dialog box? Opinion? Tab, click Cancel? Grid line? Item so that the grid lines in the worksheet can be hidden. At this point, the interface of this example calendar is designed (see Figure 3).

Figure 3

Third, make a perpetual calendar

We have put a beautiful coat on the calendar in front of us, and then we need to fill it with actual content, so that it can know the past and the future and become a real perpetual calendar.

If we know what day of the week is 1, it is very easy to automatically arrange the date of the month in Excel: just add 1 in the previous cell to arrange the date series in order, that is, as long as the problem of automatically identifying the day of the week of each month 1 is solved, combined with the identification of big month, small month and leap year, it can be automatically arranged in any year. Here is a brief introduction to the idea of this method.

Step one:

Select cell F 13 and enter the formula? = IF(WEEKDAY(DATE $ 5， 1， 1)，2)=7， 1，" ")？ (except the outermost quotation marks, the same below). The above formula means: What if? R5？ If the year 65438+ 10 month 1 entered in the cell is Sunday, it will be displayed in the cell F 13. 1? , otherwise nothing will be displayed.

Step two:

Select cell G 13 and enter the formula? = IF(f 13 & lt； & gt""，F 13+ 1，IF(WEEKDAY(DATE($ 5， 1， 1)，2)= 1， 1，" ")？ The meaning of this formula is: If cell F 13 is not empty, the number in cell F 13 is added to 1, otherwise? R5？ Enter the year 65438+ 10 month 1 in the cell to determine whether it is Monday.

Step 3:

Drag the G 13 cell? Fill handle? Copy the formula to H 13? In unit L 13, select H 13 respectively. L 13 cells, manually adjust each cell? Working days? Change the discriminant value of the function, that is, the number to the right of the equal sign, to 2, 3, 4, 5 and 6 in turn, so that it corresponds to the week.

Step 4:

Select cell F 14 (the corresponding cell in the second week of January) and enter the formula? =L 13+ 1？ (L 13 is the last cell in the previous row), and enter the formula in the cell G 14? =F 14+ 1？ , and then, drag? Fill handle? Fill in H 14? L 14 battery is enough. Similarly, enter the corresponding formula in the following four lines, but be sure to pay attention to the cell reference when wrapping.

Step five:

Repeat the above operations and set the date series of the next 1 1 month respectively. It should be noted that in the formula for identifying the day of the week of 1 every month, Dating? The monthly value in the function corresponds to the actual monthly value, for example, when setting the February calendar,? Dating? The expression of the function should be? = date ($5,2, 1 real)? .

Limited by space, the above is only a brief introduction, and it does not solve the problem that the date of big, small, leap month and date is greater than? 3 1? Phenomenon and so on. Please refer to the specific settings? Calendar. xls？ Documents. In addition, copying the formula line by line is also very cumbersome. You can also refer to it? yearly_calendar.xls？ File, learn how to completely realize the automatic updating of calendar through the advanced functions of Excel, but this requires a deeper understanding of Excel.