...

Use a two-dimensional association table

 

Example

There are children's height scoring data in the Excel file book1.xlsx, and some of the data are shown below. A1, B1, and C1 are age ranges, below which is height (cm), and column D is the score.

undefined

Another file, book2.xlsx, has children's information. Part of the data is shown in the figure below:

undefined

Find out the score data of children in book1.xlsx according to Age and Height.

Write SPL script:

A

1

=T("e:/work/book1.xlsx").rvs()

2

=T("e:/work/book2.xlsx")

3

=(A1.fno()-1).(int(A1.fname(~).split("-")(1)))

4

=A2.run(a=A3.pseg(Age),Score=A1.segp(~.field(a),Height).Score)

5

=T("e:/work/book2.xlsx",A2)

A1 Read book1.xlsx and use the rvs function to reverse the order to form an ascending order by height

 

A2 Read book2.xlsx

 

A3 Take the column name of A1, remove the last column name, split each column name with a minus sign and take the first one and convert it into an integer to form a sequence, [6,8,10]

 

A4 Loop through each row of A2, and use Age to find out the segment number a in the sequence of A3. Then use Height to find its corresponding record in the sequence interval formed by the value of the a-th field of A1, and take the Score of this record and assign it to the Score of A2

 

A5 Save A2 to the file book2.xlsx


 
 •  215 View  •