Design performance notice with WPS form

Every achievement is presented with a difficult problem: The student performance chart is a one-person achievement for convenience, and each student's "performance notice" is designed to be more or more rows in order to be beautiful. How to easily and quickly format conversion? The WPS table function can easily solve the problem for you.

First, let's look at the format of the score sheet and the results notice (you can download the file to facilitate your study). See 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 A1, click Insert Name Definition, and then enter information as shown in Figure 3: Enter "name" (without quotes) in the name of the current workbook, and enter "=offset ($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 dropdown 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 effective validity of the cell dropdown menu

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 Notification form format

Step 4. Design Formula Call data

A. Select a single 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;

Summary of Experience:

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.