Quickly arrange complex duty tables with Excel

The school decided to break the original leadership of a person on duty, so that all teachers to participate in the work on duty, every day to arrange a shift leadership and a male teacher on the night shift, female teachers only value Saturday, Sundays and other statutory holidays daytime. Such a request, to the scheduling of the schedule brought trouble, both do not have to leave no one, do not miss every day, but also to distinguish which day is Saturday Sunday, not the female teachers to the Monday to Friday normal working hours. In order to make no mistake, we used Excel to do the work.

1, the school more than 100 teachers according to leadership, female teachers and male teachers into three teams.

2, in Excel input table head on duty leader, day shift teacher, night teacher, date, week, then enter 2007-3-1 in D2 cell (or enter "March 1, 2007" and other formats), then point the mouse pointer to the lower right corner of D2 cell, When the mouse pointer changes to a black solid plus sign, drag down to select the column, and drag to the D150 cell as prompted by the left line number (this number is determined by the number of days to prepare the row). (Figure 1)

Figure 1 Enter Date

3, in the E2 cell input =text (D2, "AAAA"), then point the mouse pointer to the lower-right corner of the cell, and when the mouse pointer changes to a black solid plus sign, drag down the column, and the week column will automatically calculate and populate the E2 on the date of the previous column. If the argument changes to dddd, the day of the week is displayed in English words. (Figure 2)

Figure 2 automatically populate Chinese characters

4, from the A2 cell start to enter all the duty leader list, then drag the list of all the leaders, click edit → copy, click the next cell after all the leaders list, click edit → Paste, click the next cell after all the leaders list, and then click edit → Paste ... Until the leaders of all dates are filled. (Figure 3)

Figure 3 Input leader list

5, imitate the input on duty leader's method, fills good night teacher (male teacher) this column.

6. In accordance with the date and the week, enter day shift teachers (female teachers) in Saturday, Sunday and statutory holidays.

Note : More wonderful tutorials Please pay attention to the triple computer tutorial section, triple Computer office group: 189034526 welcome you to join