Excel to do automatic statistics course number of colorful class schedule

With Excel, we can automatically use different fill colors for different subjects in the timetable and automatically count the total number of hours in each subject. So, it looks at a glance.

One, different disciplines, different colors

This will use conditional formatting. Select the range of cells in which the schedule is located. Click the menu command "format → conditional formatting" to open the Conditional Formatting dialog box. Under condition 1, in the Drop-down list, select cell value. Then, in the Drop-down list on the right, select Equals, and in the rightmost input box, enter "= Math" (excluding the outer double quotes, and the rest in the English half-width state except the kanji), as shown in Figure 1.

Click the "Format" button at the bottom right, and in the Open Cell Format dialog box, click the Pattern tab and choose a fill color. As shown in Figure 2. When you are sure, close this dialog box.

Click on the "Add" button in conditional format to add "Condition 2", set "cell value" "equal to" = "Language", and click the Format button and specify another fill color. Continue to click the "Add" button and add "Condition 3" to set the fill format for another course. When you are sure, you will see that the cell in the specified three courses is already populated with the specified color, as shown in Figure 3.

Computer Tutorials

Ii. select each class area at any time

If you want to row a number of classes in the total table, it is unavoidable to often select a class of the course in the range of cells. If you click the CTRL key each time you choose the mouse in turn, it would be too much trouble. Therefore, we should use the "named" Method with the optional.

For example, class one's schedule is distributed in the C2:C12, G2:g12, k2:k12, o2:o12, s2:12 cell ranges in the SHEET2 worksheet. Then press the CTRL key to select the areas in turn, and then click the menu command "Insert → name → definition" and enter "Class one" in the Enter box under name in the current workbook in the Open Define Name dialog box. As shown in Figure 4. Once we're sure, we'll just enter "first Class" in the Name box to the left of the Excel formula bar, and then the range of cells will be selected immediately.

Would it be more convenient to put the other classes in the same way and just type in the names we define?

Number of weekly hours in automatic statistical subjects

Manual scheduling also has to continuously count the number of weeks in each subject, so as not to reach or exceed the specified number of hours. If you count it over and over again, it's obviously not a good idea. So, we can use the Excel function to live statistics.

For example, we want to count the number of classes in each subject in the schedule shown in Figure 2, so we just enter "math" in cell C20, then enter the other four subject names in C21, C22, C23, C24, and then enter the formula in D20 cell =countif ($C $: $G $16, C20) ", and then enter, you can count the number of math occurrences in cell c5:g16, as shown in Figure 5. Drag the fill handle down to get a count of the number of hours in the other subjects.

In this way, when we adjust the disciplines in the cell range above, we can look at the number of sessions in each subject at any time.