How to design a performance notice using the WPS table

First, let's look at the format of the score sheet and the results note, as shown in Figure 1 and Figure 2.

Figure 1 Score sheet

Figure 2 Notice of achievement

Solution Idea:

1. The score sheet and the notice form are based on two worksheets.

2. The notice can be used to invoke the information of each student in the "score sheet" through a function.

3. Create a cell Drop-down menu to select all student names, and when the change is changed, the notice follows the change. And the number of students on the existing data will not be affected, so that the design of the notice to achieve universal functionality.

Clear the idea, now with an example to explain the implementation of specific methods.

Step 1. Define Name

Select the "Score sheet" A1, tap Insert name, define, and then enter information as shown in Figure 3: Enter "name" (without quotes) in the name of the current workbook, and enter "=offset ($A $1,1,0,counta (A:A)-1,)" at the reference location. Formula meaning: Returns the number of students by CountA (A:A)-1, minus 1 is because the number of nonblank cells in column A contains the first row of "name"; then use the offset function to extract all the student names in column A.

Figure 3 Definition Name

Step 2. Set data validation

To enter the notice cell A4, click on "Data" "effectivity" "Settings", select the condition as a sequence at the allow of the validity condition, enter "= name" at the source, and make sure that the "provide Drop-down arrow" is checked before the right (see Figure 4), and then click on the cell I4 to try the effect (see Figure 5).

Figure 4 Setting data validation

Fig. 5 The effectiveness of the established cell dropdown dish

Step 3: Create a notification Form basic format

A blank format is established in Figure VI below to invoke the data of the performance table.

Figure 6

Step 4. Design Formula Call data

A. Select cell C2 and enter the following formula:

=vlookup (I4, score sheet!) a2:d1000,4,0) & "&I4&" Students ' results notice list "

B. Select cell D4, enter formula: =I4

C. Select cell F4 and enter the following formula:

=vlookup (I4, score sheet!) a2:c1000,3,0)

D. Select cell H4 and enter the following formula:

=vlookup (I4, score sheet!) a2:b1000,2,0)

E. Select cell F5 and enter the following formula:

=vlookup (i$4, score sheet!) A$2:k$1000,row (A5), 0)

F. Select cell F5 to pull the formula down to F11

At this point the formula is finished, you can select the I4 under the arrow to select different student names, you can see the "notice" of the data has followed changes. Subsequent printing will be printed by the Drop-down box to print any student's results at any time, without destroying the format of the original results table.

Formula resolution: VLOOKUP is a lookup formula that has four parameters, namely lookup value, datasheet, sequence number, match criteria.

The lookup value is the target data that needs to be looked up to the target area, can enter the target value, can also be a cell reference;

The data table is the area to be looked up, in this case the data is only 10, and the reference area input a2:d1000 is to allow the notification formula to be used when adding data to the score table, without modifying the formula. If you have more than 1000 students, you can increase the reference field accordingly, so that the formula adapts to all changes.

A sequence number is the number of columns in which the target value is located in the lookup area, and the value of the second column in the target range is returned if 2 is entered.

The matching criteria generally have two options, 0 and 1. 0 indicates an exact match, and 1 indicates an approximate match. The matching criteria can be selected according to different conditions.

Conclusion: WPS Table Lookup function is also known as reference function, powerful, flexible and changeable. For search data and format conversion has a better function. Lookup function VLOOKUP or HLOOKUP, lookup, MATCH, INDIRECT, INDEX, offset and so on, only the flexibility to master these functions to make the WPS table real potential, let a lot of manual query into the function of automatic search, thereby enhancing efficiency.