3.3 Multiple result data areas

 

The clipboard(…) can only return one result, but some complex operations may need to return multiple results, what should we do?

We can directly copy multiple cell values (or variable values) in the result display area of esProc to the clipboard separately, and return them to Excel in turn.

Let’s take the example of "finding the top 3 students in each subject and the goal of each person to surpass”.

Calculation objective: 1)append the names of the top 3 students in each subject to the end of corresponding subject based on the student score table; 2) add a new column “target” to calculate out three students for everyone, and each of the three students has a total score higher than and is close to the one to be calculated, as his/her target to surpass. Note that not all students have 3 targets, and the target students should be concatenated by the greater than sign (>).

Operation steps: i)select the source data area (A1:D10) in Excel; ii)press Ctrl+C to copy to system’s clipboard; iii) open the esProc to write and execute the following script:

A B C
1 =clipboard@e().import@t()
2 =A1.top(-3;math).(name) =A1.top(-3;english).(name) =A1.top(-3;physics).(name)
3 =join@p(A2;B2;C2)
4 =A1.derive(sum(math,english,physics):subtotal)
5 =A4.derive(t=subtotal,A4.select(subtotal>t):beforeMe)
6 =A5.new(beforeMe.top(3;subtotal).(name).concat(“>”):target)

A1: Get the data from clipboard
A3: Data area 1: top 3 students in each subject
A4: Total score of each student
A5: 3 students whose total score is higher than the one to be calculated
A6: Data area 2: 3 students whose total score is close to the one to be calculated

A3 stores the result data area 1, that is, the top 3 students in score in each subject; A4 stores the result data area 2, i.e., the targets of each student to surpass. It should also be noted that export ()and clipboard (…) are not needed in the code.

Now we return the results to Excel.

Click on the data area 1, and click the corresponding “copy data” button on the right, as shown below:

imagepng

Then, select the cell B11 in Excel, and press Ctrl+V to copy the data area 1 to B11-D13, as shown as below:

A B C D
1 name math english physics
2 Lily 97 100 99
3 Joshua 100 99 100
4 Sarah 98 99 96
5 Bertram 94 95 85
6 Paula 91 88 91
7 Sophia 92 81 76
8 Ben 87 80 76
9 Ruth 92 91 87
10 Pag 95 87 87
11 Joshua lily Joshua
12 Sarah Sarah lily
13 lily Joshua Sarah

Next, click on the data area 2 in the script, and hold down the Shift key and click the corresponding “copy data” button on the right. After that, select the cell E1 in Excel, and press Ctrl+V to paste the data area 2 together with the column name to E1-E10. The result is as follows:

A B C D E
1 name math english physics Target
2 Lily 97 100 99 Joshua
3 Joshua 100 99 100
4 Sarah 98 99 96 lily>Joshua
5 Bertram 94 95 85 Sarah>lily>Joshua
6 Paula 91 88 91 Bertram>Sarah>lily
7 Sophia 92 81 76 Pag>Ruth>Paula
8 Ben 87 80 76 Sophia>Pag>Ruth
9 Ruth 92 91 87 Bertram>Sarah>lily
10 Pag 95 87 87 Ruth>Paula>Bertram
11 Joshua lily Joshua
12 Sarah Sarah lily
13 lily Joshua Sarah

We can use Shift key to control whether the calculation result has a column name.