The application of WPS table conditional format in two examples

Referring to the "conditional formatting" of the WPS table, if the user uses the "Mixed reference cell" in conditional formatting, it will have an unexpected effect.

Here are a few classic examples to lead us all into this magical world.

First give the user a simple introduction to the concept of cell reference, in the WPS table, the cell reference is divided into the following 3 categories (4 references):

• Relative references: A1, D10, etc., when the formula is copied, automatically by rows, columns produce relative references.

• Absolute reference: $B $, $F $, before the column number "$" to ensure that the formula will not change after copying.

• Mixed reference: $A 2, b$1, row relative, column absolute reference (such as $A2), when the formula is replicated in the left and right direction, a column fixed to remain unchanged, row absolute, column relative reference (such as: b$1), when the formula is copied in the upper and lower direction, the 1th row fixed remains unchanged.

Fixed interlaced shading

The article begins by referring to the use of conditional formatting to set interlacing shading, but the general method of interlacing settings often fails after the user makes conditional filtering, as shown in Figure 1, how can you make spacing shading unaffected by filtering?

Figure 1 Affected interval shading effects after filtering

The specific settings are as follows:

Step 1 Click A2 Cell, and drag the mouse to select the A2:g11 area;

Step 2 Select the Format | conditional Format menu item, and in the pop-up dialog box, set the conditional formatting formula as follows:

The conditional format formula is: =mod (3, $A $: $A 2), 2)

Formula Analysis: This technique mainly uses the SUBTOTAL function to support the data counting function in the filtering condition to realize the data number dynamically, then uses the MoD function to calculate the remainder, so that satisfies the condition the data to give the format. Where: The subtotal function parameter "$A $ $A 2" uses A1 's absolute reference and A2 's mixed reference.

Step 3 Click the Format button and set the corresponding cell format as shown in Figure 2:

Figure 2 Conditional formatting for interlaced shading

Step 4 Click OK to save the settings and make the filter settings again, such as filtering "students with a language score of 60", and the final format will look as shown in Figure 3.

Fig. 3 Fixed interlaced shading display effect

Tip: Because the SUBTOTAL function is used in conditional formatting formulas, you can display interlaced shading in a filtered state regardless of the data.

Automatically mark duplicate data

Many times, the user because of misoperation and other reasons, resulting in the data table entered the duplicate data, such as the duplicate employee name entered, the same product number, if you can input the user to "explicit" reminders, will be as far as possible to avoid the user's error input. The data table shown has already entered some of the students ' scores, and the following method describes using conditional formatting to remind users of repeated student names.

Figure 4 Student Final Exam results form

The specific setup methods are as follows:

Step 1 First position the cursor A2 cell, select the A2:g9 cell range;

Step 2 Select the Format | conditional Format menu item, and in the pop-up dialog box, set the conditional formatting formula as follows:

Conditional Formatting formula: =countif ($A $: $A 2, $A 2) >1

IDEA Analysis: The formula mainly utilizes the mixed reference of the COUNTIF function combined with the cell to realize the dynamic counting, when the user enters the duplicate student, the formula calculation value must be greater than 1, therefore satisfies the condition to give the specified format.

Step 3 Click the Format button and set the corresponding cell format as shown in Figure 5:

Figure 5 Conditional formatting for duplicate data markers

Step 4 Click OK to save the settings and the system will display in a special format when the user enters a duplicate student, as shown in Figure 6.

Figure 6 Duplicate data marker results

Summary:

1, understanding and mastering the "mixed reference" of the cell, it is advantageous to copy and use the formula of WPS table.

2, the use of subtotal function combined with mixed reference, you can achieve "fixed interlaced shading" display.

3, using the COUNTIF function combined with mixed references, users can purposefully prevent the input of duplicate data.

The user understands and grasps the conditional format, the actual work carries on the reasonable application. For example, "find out if the current data exists in the specified datasheet?" Such applications can be implemented using the "conditional formatting" of the WPS table.

Scope of application:

Applies to WPS Office 2005 and above.