esProc 利用剪贴板强化 Excel 计算

遇到 Excel 难以实现的复杂或特殊运算时,可先用剪贴板将数据复制到 esProc,利用 esProc 强大的计算能力完成,这种方式比 add-ins 有更多优点,详细场景和代码解析请点击esProc 利用剪贴板强化 Excel 计算

遇到Excel难以实现的复杂或特殊运算时,可先用剪贴板将数据复制到esProc,利用esProc强大的计算能力完成,再用剪贴板返回Excel。剪贴板方式比传统的add-ins方式部署更简单,操作更顺滑,开发时不影响剪贴板正常使用,且支持多个源片区和结果片区。

基本用法

esProc提供了函数clipboard,可实现剪贴板的基本用法。下面用“各科前3名的学生”为例进行说明。

Excel中处理前的数据如下,其中A列是学生姓名,B-D列分别是数学、英语、物理成绩。


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

计算目标:求出每学科成绩前3名的学生,并追加到本科目成绩之后。

这个计算目标需要用到记录集合TopN、按序号拼接等功能,Excel本身不好实现,但借助esProc就容易多了。先在Excel中选中源片区(A1:D10),按下ctrl+C,复制到系统剪贴板,打开集算器IDE,编写并执行如下脚本:


A

B

1

=clipboard().import@t()

/从剪切板读取数据

2

=A1.top(-3;math).(name)

/math3

3

=A1.top(-3;english).(name)


4

=A1.top(-3;physics).(name)


5

=join@p(A2;A3;A4).export()

/拼成二维表,转成字串

6

=clipboard(A5)


/向剪切板写数据





上面代码中,clipboard函数有两种形态,其中无参数调用该函数时,可返回剪贴中的字符串,如A1格;以变量或格名为参数调用该函数时,可向剪贴板写入字符串,形如A6中的clipboard(var)

执行上述脚本后,在ExcelB11格用ctrl+V,即可将剪切板中的数据复制到B11-D13,如下:


A

B

C

D


10

Pag

95

87

87

11


Joshua

Lily

Joshua

12


Sarah

Sarah

Lily

13


lily

Joshua

Sarah

随意编辑脚本

但是,我们在编辑调试脚本时,保不齐会用一下复制粘贴,这时候就把剪贴板的内容冲掉了,再执行clipboard()时, 返回的内容就是刚才编辑的代码,这样就无法完成计算了,只能再回到Excel去重新复制,有点麻烦。

针对这个问题,esProc提供了clipboard@e()函数,选项@e表示始终返回第一次从Excel复制到剪贴板中的数据。下面让我们试一下。

在“各科前3名的学生”这个例子中,假设我们已经从Excel复制了数据,并写完了脚本。此时编辑脚本,将A3A4移动到B2B3,再执行脚本时,就会因为A1中的clipbaord()取到错误数据,而导致A2报错。现在修改代码,在A1使用刚才提到的clipboard@e(),则移动代码后可正确执行。编辑后的代码如下:


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;B3).export()

/拼成二维表,转成字串


4

=clipboard(A3)

/向剪切板写数据


多个结果片区

clipboard(…)只能返回一个结果,但有些较复杂的运算可能需要返回多个结果,这该怎么办呢?

其实 ,在esProc的结果显示区可以直接把多个格值(或变量值)分别复制到剪贴板,并依次返回Excel

下面用 “各科前3名和每个人的超越目标”为例进行说明。

计算目标:在学生成绩单的基础上,不仅在各科目下面追加本科目前3名的学生,而且需要新加一列target,计算出比本人总分略高的3名学生的名单列表,作为本人应当超越的目标。注意,有些人的超越目标不足3人,每个超越目标之间须用>号连接。

先在Excel中选中源片区(A1:D10),按下ctrl+C,复制到系统剪贴板,打开esProc IDE,编写并执行如下脚本:


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)

片区1:各科前3


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)

片区2:最近3名学生


上述代码中,A3存储结果片区1,即本科目前3名的学生;A4存储结果片区2,即每个人的超越目标。另外应当注意到,代码中无须export()clipboard(…)

下面将计算结果返回Excel。先点击片区1,再点击右侧对应的“copy data”按钮,如下图:

imagepng


Excel B11 格用ctrl+V ,即可将片区1 复制到B11-D13 ,如下:


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

再点击脚本中的片区2,按住shift的同时,点击右侧对应的“copy data”按钮,之后在Excel的的E1格按下ctrl+V,即可将片区2连带列名复制到E1:E10。如下所示:


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


在上述操作中,我们可通过shift控制计算结果是否带列名,这是esProc的独有方式,其他计算工具不具备此项功能。

多个源片区

解决了多目标的问题后,我们很容易联想到有多个源的问题。计算可能需要使用Excel的多个源片区,但clipboard只能保持最新复制的那一片,这又该怎么办?

有办法,可以把剪贴板内容直接复杂到格子里面去。

下面用 “查询符合指定条件的订单”为例进行说明。

Excel中有两个sheet,订单明细和员工名单,其中订单明细如下:


A

B

C

D

E

1

OrderID

Client

SellerId

Amount

OrderDate

2

1

WVF   Vip

1

440

2014-11-03

3

2

UFS   Com

1

1863

2015-01-01

4

3

SWFR

2

1813

2014-11-01

5

4

JFS   Pep

2

671

2015-01-01

6

5

DSG

1

3730

2015-01-01

7

6

JFE

1

1445

2015-01-01

8

7

OLF

3

625

2015-01-01

9

8

PAER

3

2490

2015-01-01

员工名单如下:


A

B

C

D

E

F

G

1

EId

State

Dept

Name

Gender

Salary

Birthday

2

2

New   York

Marketing

Ashley

F

11001

1980-07-19

3

3

New   Mexico

Sales

Rachel

F

9000

1970-12-17

4

4

Texas

HR

Emily

F

7000

1985-03-07

5

5

Texas

R&D

Ashley

F

16000

1975-05-13

6

6

California

Sales

Matthew

M

11000

1984-07-07

7

7

Illinois

Sales

Alexis

F

9000

1972-08-16

8

8

California

Marketing

Megan

F

11000

1979-04-19

9

1

Texas

HR

Victoria

F

3000

1983-12-07

计算目标:查询出最近days天内或订单属于部门列表depts的数据,需要的列有订单编号、日期、金额、销售员名字、部门名称。其中days是外部参数,每次执行时都可以输入不同的值,比如输入30表示查询30天内的订单;depts也是外部参数,比如["Markeding","Finance"]。这个计算目标涉及动态查询和多键值查询,Excel本身不好实现,但借助esProc就容易多了。

首先,在Excel中选定片区“订单明细”,连带列名一起复制粘贴到esProc脚本的A1单元格;同样地,将片区“员工名单”复制到esProc脚本的A1单元格。如下图:

imagepng

注意,一定要进入A1格的编辑状态才能进行复制,不然会把剪贴板的内容填进一大片格子,结果可能变成这样:

imagepng


这就不好了,占有区域太大,会影响代码布局。

如果操作正确,A1/A2格外观上只呈现很小一部分数据,点击后才会呈现完整数据,这是网格式代码的独特之处,适合在不影响阅读和代码布局的情况下复制大片数据。

复制完两个源片区后,继续编写脚本,期间可多次运行脚本,可手工操作剪贴板。最后代码如下:


A

B

C

1

OrderID Client…

=A1.import@t()

/order list

2

EId State…

=A2.import@t()

/employee list

3

=B1.switch(SellerId,B2:EId)


/Join with EId

4

=A3.select(OrderDate>=after(date(now()),days*-1)||   depts.pos(SellerId.Dept))


/Search data

5

=A4.new(OrderID,OrderDate,Amount,SellerId.Name:Name,SellerId.Dept:Dept)


/return fields   needed

完成计算后,可用“copy data”按钮将A5中的计算结果拷贝回Excel。有一点需要注意,保存代码时应当把A1/A2格的数据清除掉,否则这些大片数据也会被一起保存起来了。