RaqForum 19 No.
1 Reply  •  310 View  • 
     ...

Find the location of a value

 

Example

There is an Excel file book1.xlsx with the 3 area data shown in the figure below (indicated by different colors).

undefined 

Now we need to calculate a column of data corresponding to 1-12. The calculation rule: look for 1-12 in the three regions in turn, and take the number at the corresponding position of region one after finding it. If it is 0, take the number in the same position of region 2; if it is 1, then Take F + current search number. The results are shown in columns G and H in the following figure:

This question involves locating sequence members, fetching members at specified positions in sequence, fetching column values in a table sequence, and using the clipboard.

Implementation steps

1. Write SPL script:

A

1

=clipboard().import()

2

=A1.(_1)|A1.(_2)

3

=A1.(_3)|A1.(_4)

4

=A1.(_5)|A1.(_6)

5

=12.((k=A4.pos(~),~/"\t"/if(A2(k)==0,A3(k),"F"/~)))

6

=clipboard(A5.concat("\n"))

 

A1 Read data from the clipboard.

 

A2 Take the value of the first column in A1 and the value of the second column and combine them into a sequence, and then the area one sequence is obtained.

 

A5 Loop through from 1 to12, first find the position number k of the current number in A4 and return the current number + Tab(\t)+ (if the number at position k in A2 is 0, then take the number at position k in A3, otherwise take F+current number)

 

A6 Consolidate the sequence members in A5 into a text with \n and put it into the clipboard.

 

2. Using the clipboard

Select data A1:F6 in the Excel file, and then press Ctrl+C to copy it to the clipboard. Go back to esProc, run the program, go back to the Excel file, click G1, and press Ctrl+V to paste the calculation results.


 
 •  310 View  •