The method of making the achievement notice in Jinshan WPS form

Soon winter vacation, I believe that a lot of users engaged in the work of teachers to start the final exam preparation, as well as the results of the statistics and results of the production of notice. So, how to make a notice of achievement? Today, small knitting to bring us a simple way to produce transcripts, we need to use the software is Jinshan WPS form, the following, together to see the Jinshan WPS form to produce results notice method bar!

Performance Notice

The solution to the production of performance notice:

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.

Definition name

Step 2. Set data validation

Enter the notice cell A4, click on "Data" "effectivity" "Settings", where the validity conditions allow the selection of the conditions for the sequence, the source input "= name", while ensuring that the right "provide a drop-down arrow" before the tick (see Figure 4), determine after the Click cell I4 test Results.

Set data validation

Effectiveness of the establishment of the cell dropdown dish

Step 3: Create a notification Form basic format

Create a blank format to call the data in the score sheet.

Establish a basic format for a notification form

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.

Form making