Design performance notice with WPS form

Source: Internet
Author: User
Tags first row

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.