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:
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:
Then, select the cell B11 in Excel, and press Ctrl+V to copy the data area 1 to B11-D13, as shown as below:
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:
We can use Shift key to control whether the calculation result has a column name.