How to form a summary table by spelling the statistical values of related detailed items in Excel?

A company's recruitment record exists in EXCEL, and the data format is as follows:

Recruitment plan:

Now, based on the above data, according to the KOA application number in the recruitment record table, we want to count the number of initial test results, re-examination results, final test results, offer release results and employment time respectively, and finally generate a light green part of the recruitment ledger, which also includes the automatic summary of recruitment costs and recruitment channels.

The following is the final recruitment ledger effect (target report):

Under Run Qian Report Designer, create a new blank report, and select the script data set in Report menu-Dataset-Add.

a1: = file ("recruitment plan/recruitment statistics. xlsm"), which defines a file object, and the path is a relative path.

A2：=A1.xlsimport@t(; "Recruitment Record Table", 2:14), use xlsimport function to read the contents of A1 file object, @t means that the first line is the title, the recruitment record table is the specified sheet,2 is the starting data line, and 14 is the ending line.

A3：=A1.xlsimport@t(; "Recruitment Plan", 2:4), read the second to third lines from the sheet page of the recruitment plan, with the first line of data as the title.

a4: = a2.groups (KOA application number: KOA application number; Count (initial test result): initial number of people, count (retest result): multiple number of people, count (final test result): final number of people, count(offer distribution result): offer number, count (entry time): actual number of people on duty, recruitment channel, sum (recruitment cost)), and group according to KOA application form number to make statistics on initial numbers.

a5: return A3, A4, and return two cell sets of a3 and a4 as report data set results.

since two result sets are returned in the script dataset, there should also be two dataset names, and multiple dataset names should be separated by commas.

after the data is created, we can define cell expressions by dragging fields to cells in the data set panel at the lower right of the report designer.

the following is the setting and description of report cell expressions:

a3: = ds1. Select (serial number)

B3: =ds1. Plate, C3~I3 are all from ds1, and the expressions are all DS1. Field names.

J3: = ds2. Select (initial headcount, KOA application form number ==ds1.KOA application form number), realize the association between two result sets according to KOA application form number, and get the value of initial headcount field correspondingly.

k3: = ds2. The number of people with multiple faces, the blue area comes from the field of ds2, and the expressions are all ds2. Field names.

preview the report, and then export it to ——EXCEL file through File menu.