esProc 利用剪貼板強(qiáng)化 Excel 計(jì)算

遇到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)了澄峰。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末嫉沽,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子俏竞,更是在濱河造成了極大的恐慌绸硕,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,681評(píng)論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件魂毁,死亡現(xiàn)場(chǎng)離奇詭異玻佩,居然都是意外死亡,警方通過(guò)查閱死者的電腦和手機(jī)席楚,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,205評(píng)論 3 399
  • 文/潘曉璐 我一進(jìn)店門咬崔,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái),“玉大人酣胀,你說(shuō)我怎么就攤上這事刁赦。” “怎么了闻镶?”我有些...
    開(kāi)封第一講書(shū)人閱讀 169,421評(píng)論 0 362
  • 文/不壞的土叔 我叫張陵甚脉,是天一觀的道長(zhǎng)。 經(jīng)常有香客問(wèn)我铆农,道長(zhǎng)牺氨,這世上最難降的妖魔是什么? 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 60,114評(píng)論 1 300
  • 正文 為了忘掉前任墩剖,我火速辦了婚禮猴凹,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘岭皂。我一直安慰自己郊霎,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,116評(píng)論 6 398
  • 文/花漫 我一把揭開(kāi)白布爷绘。 她就那樣靜靜地躺著书劝,像睡著了一般。 火紅的嫁衣襯著肌膚如雪土至。 梳的紋絲不亂的頭發(fā)上购对,一...
    開(kāi)封第一講書(shū)人閱讀 52,713評(píng)論 1 312
  • 那天,我揣著相機(jī)與錄音陶因,去河邊找鬼骡苞。 笑死,一個(gè)胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的解幽。 我是一名探鬼主播贴见,決...
    沈念sama閱讀 41,170評(píng)論 3 422
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼亚铁!你這毒婦竟也來(lái)了蝇刀?” 一聲冷哼從身側(cè)響起螟加,我...
    開(kāi)封第一講書(shū)人閱讀 40,116評(píng)論 0 277
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤徘溢,失蹤者是張志新(化名)和其女友劉穎,沒(méi)想到半個(gè)月后捆探,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體然爆,經(jīng)...
    沈念sama閱讀 46,651評(píng)論 1 320
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,714評(píng)論 3 342
  • 正文 我和宋清朗相戀三年黍图,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了曾雕。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 40,865評(píng)論 1 353
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡助被,死狀恐怖剖张,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情揩环,我是刑警寧澤搔弄,帶...
    沈念sama閱讀 36,527評(píng)論 5 351
  • 正文 年R本政府宣布,位于F島的核電站丰滑,受9級(jí)特大地震影響顾犹,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜褒墨,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,211評(píng)論 3 336
  • 文/蒙蒙 一炫刷、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧郁妈,春花似錦浑玛、人聲如沸。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 32,699評(píng)論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至剧腻,卻和暖如春拘央,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背书在。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 33,814評(píng)論 1 274
  • 我被黑心中介騙來(lái)泰國(guó)打工灰伟, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 49,299評(píng)論 3 379
  • 正文 我出身青樓栏账,卻偏偏與公主長(zhǎng)得像帖族,于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子挡爵,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,870評(píng)論 2 361

推薦閱讀更多精彩內(nèi)容