First, let’s take a look at the formats of the “Results Table” and “Result Notification”. As shown in Figure 1 and Figure 2.

                                                            Figure 1 Score Table

​​​​​Picture 2 Score Notification

Step 1. Define the name

Select "Grade Sheet" A1, click [Insert] [Name] [Definition], and then enter information as shown in Figure 3: enter "Name" (without quotation marks) as the name of the current workbook, and enter "=OFFSET" at the reference position. ($A$1,1,0,COUNTA(A:A)-1,)". Meaning of the formula: Return the number of students in column A through COUNTA(A:A)-1, minus 1 because the number of non-empty cells in column A includes the word "name" in the first row; then use the OFFSET function to extract the names of all students in column A.

Figure 3 Define the name

Step 2. Set data validity

Enter cell A4 of "Notice", click [Data] [Validity] [Settings], select the condition as sequence where the validity condition is allowed, enter "=name" as the source, and make sure that there is a drop-down arrow before "provide a drop-down arrow" on the right It has been checked (see Figure 4). After confirmation, click cell I4 to try the effect (see Figure 5)

Figure 4 Set data validity

Figure 5 Cell drop-down menu established by data validity

Step 3. Create the basic format of the notice

                                                                                                          creating a blank format as shown in Figure 6 below to wait for the data of the score table to be called.

Figure 6 Notice format

Step 4. Design formula to call data

A. Select cell C2 and enter the following formula:

=VLOOKUP(I4,score list!A2:D1000,4,0)&" "&I4&" Student Score Notification"

B. Select cell D4 and enter the formula: =I4

C. Select cell F4 and enter the following formula:

=VLOOKUP(I4,score list!A2:C1000,3,0)

D. Select cell H4 and enter the following formula:

=VLOOKUP(I4,score list!A2:B1000,2,0)

E. Select cell F5 and enter the following formula:

=VLOOKUP(I$4,score list!A$2:K$1000,ROW(A5),0)

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

At this point, the formula design is completed. You can select the drop-down arrow in cell I4 to select the names of different students. You can see that the data in the "Notice" has changed accordingly. During subsequent printing, the results of any student can be printed at any time through the drop-down box printing without destroying the format of the original score sheet.

Formula analysis: VLOOKUP is a search formula with four parameters, namely [search value], [data table], [sequence number], and [matching condition].

The search value is the target data that needs to be searched in the target area. You can enter the target value or a cell reference;

The data table is the area to be searched. In this example, there are only 10 data. The reference area is entered as A2:D1000 so that the notification formula can be used universally when data is added to the score table, so there is no need to modify the formula. If your number of students exceeds 1,000, you can increase this reference field accordingly so that the formula can adapt to all changes.

The sequence number refers to the number of columns in which the target value is located in the searched area. If you enter 2, the value in the second column of the target area will be returned.

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

Summary of ideas:

​​ 1. "Results List" and "Notice" are established in two worksheets.

​​ 2. "Notice" can call the information of each student in "Grade Table" through functions.

​ ​ 3. Create a cell drop-down menu for selecting all student names. When the options change, the notification will change accordingly. Moreover, increasing the number of students on the existing data will not be affected, thus making the notification design achieve universal functionality.

4. In addition to VLOOKUP, search functions include HLOOKUP, LOOKUP, MATCH, INDIRECT, INDEX, OFFSET, etc. Only by flexibly mastering these functions can WPS tables truly realize their potential, and many manual query methods can be transformed into automatic search by functions, thus improving efficiency. .
The above is what the editor said aboutHow to use the WPS form to create a simple and clear score analysis table. I hope it will be helpful to everyone. For more exciting tutorials, please pay attention to Huajun Software Park.