遇到Excel難以實(shí)現(xiàn)的復(fù)雜或特殊運(yùn)算時(shí)风宁,可先用剪貼板將數(shù)據(jù)復(fù)制到esProc,利用esProc強(qiáng)大的計(jì)算能力完成蛹疯,再用剪貼板返回Excel戒财。剪貼板方式比傳統(tǒng)的add-ins方式部署更簡(jiǎn)單,操作更順滑捺弦,開(kāi)發(fā)時(shí)不影響剪貼板正常使用饮寞,且支持多個(gè)源片區(qū)和結(jié)果片區(qū)。
基本用法
esProc提供了函數(shù)clipboard列吼,可實(shí)現(xiàn)剪貼板的基本用法幽崩。下面用“各科前3名的學(xué)生”為例進(jìn)行說(shuō)明。
Excel中處理前的數(shù)據(jù)如下寞钥,其中A列是學(xué)生姓名慌申,B-D列分別是數(shù)學(xué)、英語(yǔ)理郑、物理成績(jī)蹄溉。
ABCD
1namemathenglishphysics
2lily9710099
3Joshua10099100
4Sarah989996
5Bertram949585
6Paula918891
7Sophia928176
8Ben878076
9Ruth929187
10Pag958787
計(jì)算目標(biāo):求出每學(xué)科成績(jī)前3名的學(xué)生,并追加到本科目成績(jī)之后您炉。
這個(gè)計(jì)算目標(biāo)需要用到記錄集合TopN柒爵、按序號(hào)拼接等功能,Excel本身不好實(shí)現(xiàn)邻吭,但借助esProc就容易多了餐弱。先在Excel中選中源片區(qū)(A1:D10)宴霸,按下ctrl+C囱晴,復(fù)制到系統(tǒng)剪貼板,打開(kāi)集算器IDE瓢谢,編寫(xiě)并執(zhí)行如下腳本:
AB
1=clipboard().import@t()/從剪切板讀取數(shù)據(jù)
2=A1.top(-3;math).(name)/math前3名
3=A1.top(-3;english).(name)
4=A1.top(-3;physics).(name)
5=join@p(A2;A3;A4).export()/拼成二維表畸写,轉(zhuǎn)成字串
6=clipboard(A5)/向剪切板寫(xiě)數(shù)據(jù)
上面代碼中,clipboard函數(shù)有兩種形態(tài)氓扛,其中無(wú)參數(shù)調(diào)用該函數(shù)時(shí)枯芬,可返回剪貼中的字符串论笔,如A1格;以變量或格名為參數(shù)調(diào)用該函數(shù)時(shí)千所,可向剪貼板寫(xiě)入字符串狂魔,形如A6中的clipboard(var)。
執(zhí)行上述腳本后淫痰,在Excel的B11格用ctrl+V最楷,即可將剪切板中的數(shù)據(jù)復(fù)制到B11-D13,如下:
ABCD
…………
10Pag958787
11JoshuaLilyJoshua
12SarahSarahLily
13lilyJoshuaSarah
隨意編輯腳本
但是待错,我們?cè)诰庉嬚{(diào)試腳本時(shí)籽孙,保不齊會(huì)用一下復(fù)制粘貼理澎,這時(shí)候就把剪貼板的內(nèi)容沖掉了塌衰,再執(zhí)行clipboard()時(shí)萄喳,返回的內(nèi)容就是剛才編輯的代碼姓赤,這樣就無(wú)法完成計(jì)算了峦嗤,只能再回到Excel去重新復(fù)制戏溺,有點(diǎn)麻煩驯遇。
針對(duì)這個(gè)問(wèn)題焙畔,esProc提供了clipboard@e()函數(shù)谱仪,選項(xiàng)@e表示始終返回第一次從Excel復(fù)制到剪貼板中的數(shù)據(jù)犹菇。下面讓我們?cè)囈幌隆?/p>
在“各科前3名的學(xué)生”這個(gè)例子中,假設(shè)我們已經(jīng)從Excel復(fù)制了數(shù)據(jù)芽卿,并寫(xiě)完了腳本揭芍。此時(shí)編輯腳本,將A3卸例、A4移動(dòng)到B2称杨、B3,再執(zhí)行腳本時(shí)筷转,就會(huì)因?yàn)锳1中的clipbaord()取到錯(cuò)誤數(shù)據(jù)姑原,而導(dǎo)致A2報(bào)錯(cuò)。現(xiàn)在修改代碼呜舒,在A1使用剛才提到的clipboard@e()锭汛,則移動(dòng)代碼后可正確執(zhí)行。編輯后的代碼如下:
ABC
1=clipboard@e().import@t()/從剪切板讀取數(shù)據(jù)
2=A1.top(-3;math).(name)=A1.top(-3;english).(name)=A1.top(-3;physics).(name)
3=join@p(A2;B2;B3).export()/拼成二維表袭蝗,轉(zhuǎn)成字串
4=clipboard(A3)/向剪切板寫(xiě)數(shù)據(jù)
多個(gè)結(jié)果片區(qū)
clipboard(…)只能返回一個(gè)結(jié)果唤殴,但有些較復(fù)雜的運(yùn)算可能需要返回多個(gè)結(jié)果,這該怎么辦呢到腥?
其實(shí)朵逝,在esProc的結(jié)果顯示區(qū)可以直接把多個(gè)格值(或變量值)分別復(fù)制到剪貼板,并依次返回Excel乡范。
下面用“各科前3名和每個(gè)人的超越目標(biāo)”為例進(jìn)行說(shuō)明配名。
計(jì)算目標(biāo):在學(xué)生成績(jī)單的基礎(chǔ)上啤咽,不僅在各科目下面追加本科目前3名的學(xué)生,而且需要新加一列target渠脉,計(jì)算出比本人總分略高的3名學(xué)生的名單列表宇整,作為本人應(yīng)當(dāng)超越的目標(biāo)。注意芋膘,有些人的超越目標(biāo)不足3人没陡,每個(gè)超越目標(biāo)之間須用>號(hào)連接。
先在Excel中選中源片區(qū)(A1:D10)索赏,按下ctrl+C盼玄,復(fù)制到系統(tǒng)剪貼板,打開(kāi)esProc IDE潜腻,編寫(xiě)并執(zhí)行如下腳本:
ABC
1=clipboard@e().import@t()從剪切板獲取數(shù)據(jù)
2=A1.top(-3;math).(name)=A1.top(-3;english).(name)=A1.top(-3;physics).(name)
3=join@p(A2;B2;C2)片區(qū)1:各科前3名
4=A1.derive(sum(math,english,physics):subtotal)每個(gè)人的總分
5=A4.derive(t=subtotal,A4.select(subtotal>t):beforeMe)排在本人前面的學(xué)生
6=A5.new(beforeMe.top(3;subtotal).(name).concat(">"):target)片區(qū)2:最近3名學(xué)生
上述代碼中埃儿,A3存儲(chǔ)結(jié)果片區(qū)1,即本科目前3名的學(xué)生融涣;A4存儲(chǔ)結(jié)果片區(qū)2童番,即每個(gè)人的超越目標(biāo)。另外應(yīng)當(dāng)注意到威鹿,代碼中無(wú)須export()和clipboard(…)剃斧。
下面將計(jì)算結(jié)果返回Excel。先點(diǎn)擊片區(qū)1忽你,再點(diǎn)擊右側(cè)對(duì)應(yīng)的“copy data”按鈕幼东,如下圖:
need-to-insert-img
在Excel的B11格用ctrl+V,即可將片區(qū)1復(fù)制到B11-D13科雳,如下:
ABCD
1namemathenglishphysics
2Lily9710099
3Joshua10099100
4Sarah989996
5Bertram949585
6Paula918891
7Sophia928176
8Ben878076
9Ruth929187
10Pag958787
11JoshualilyJoshua
12SarahSarahlily
13lilyJoshuaSarah
再點(diǎn)擊腳本中的片區(qū)2根蟹,按住shift的同時(shí),點(diǎn)擊右側(cè)對(duì)應(yīng)的“copy data”按鈕糟秘,之后在Excel的的E1格按下ctrl+V简逮,即可將片區(qū)2連帶列名復(fù)制到E1:E10。如下所示:
ABCDE
1namemathenglishphysicsTarget
2Lily9710099Joshua
3Joshua10099100
4Sarah989996lily>Joshua
5Bertram949585Sarah>lily>Joshua
6Paula918891Bertram>Sarah>lily
7Sophia928176Pag>Ruth>Paula
8Ben878076Sophia>Pag>Ruth
9Ruth929187Bertram>Sarah>lily
10Pag958787Ruth>Paula>Bertram
11JoshualilyJoshua
12SarahSarahlily
13lilyJoshuaSarah
在上述操作中尿赚,我們可通過(guò)shift控制計(jì)算結(jié)果是否帶列名散庶,這是esProc的獨(dú)有方式,其他計(jì)算工具不具備此項(xiàng)功能凌净。
多個(gè)源片區(qū)
解決了多目標(biāo)的問(wèn)題后悲龟,我們很容易聯(lián)想到有多個(gè)源的問(wèn)題。計(jì)算可能需要使用Excel的多個(gè)源片區(qū)泻蚊,但clipboard只能保持最新復(fù)制的那一片躲舌,這又該怎么辦?
有辦法性雄,可以把剪貼板內(nèi)容直接復(fù)雜到格子里面去没卸。
下面用“查詢符合指定條件的訂單”為例進(jìn)行說(shuō)明。
Excel中有兩個(gè)sheet秒旋,訂單明細(xì)和員工名單约计,其中訂單明細(xì)如下:
ABCDE
1OrderIDClientSellerIdAmountOrderDate
21WVF ? Vip 14402014-11-03
32UFS ? Com118632015-01-01
43SWFR218132014-11-01
54JFS ? Pep26712015-01-01
65DSG137302015-01-01
76JFE114452015-01-01
87OLF36252015-01-01
98PAER324902015-01-01
員工名單如下:
ABCDEFG
1EIdStateDeptNameGenderSalaryBirthday
22New ? YorkMarketingAshleyF110011980-07-19
33New ? MexicoSalesRachelF90001970-12-17
44TexasHREmilyF70001985-03-07
55TexasR&DAshleyF160001975-05-13
66CaliforniaSalesMatthewM110001984-07-07
77IllinoisSalesAlexisF90001972-08-16
88CaliforniaMarketingMeganF110001979-04-19
91TexasHRVictoriaF30001983-12-07
計(jì)算目標(biāo):查詢出最近days天內(nèi)或訂單屬于部門列表depts的數(shù)據(jù),需要的列有訂單編號(hào)迁筛、日期煤蚌、金額、銷售員名字细卧、部門名稱尉桩。其中days是外部參數(shù),每次執(zhí)行時(shí)都可以輸入不同的值贪庙,比如輸入30表示查詢30天內(nèi)的訂單蜘犁;depts也是外部參數(shù),比如["Markeding","Finance"]止邮。這個(gè)計(jì)算目標(biāo)涉及動(dòng)態(tài)查詢和多鍵值查詢这橙,Excel本身不好實(shí)現(xiàn),但借助esProc就容易多了导披。
首先屈扎,在Excel中選定片區(qū)“訂單明細(xì)”,連帶列名一起復(fù)制粘貼到esProc腳本的A1單元格撩匕;同樣地鹰晨,將片區(qū)“員工名單”復(fù)制到esProc腳本的A1單元格。如下圖:
need-to-insert-img
注意止毕,一定要進(jìn)入A1格的編輯狀態(tài)才能進(jìn)行復(fù)制并村,不然會(huì)把剪貼板的內(nèi)容填進(jìn)一大片格子,結(jié)果可能變成這樣:
need-to-insert-img
這就不好了滓技,占有區(qū)域太大哩牍,會(huì)影響代碼布局。
如果操作正確令漂,A1/A2格外觀上只呈現(xiàn)很小一部分?jǐn)?shù)據(jù)膝昆,點(diǎn)擊后才會(huì)呈現(xiàn)完整數(shù)據(jù),這是網(wǎng)格式代碼的獨(dú)特之處叠必,適合在不影響閱讀和代碼布局的情況下復(fù)制大片數(shù)據(jù)荚孵。
復(fù)制完兩個(gè)源片區(qū)后,繼續(xù)編寫(xiě)腳本纬朝,期間可多次運(yùn)行腳本收叶,可手工操作剪貼板。最后代碼如下:
ABC
1OrderID Client…=A1.import@t()/order list
2EId 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
完成計(jì)算后共苛,可用“copy data”按鈕將A5中的計(jì)算結(jié)果拷貝回Excel判没。有一點(diǎn)需要注意蜓萄,保存代碼時(shí)應(yīng)當(dāng)把A1/A2格的數(shù)據(jù)清除掉,否則這些大片數(shù)據(jù)也會(huì)被一起保存起來(lái)了澄峰。