在PowerQuery中用分而治之的辦法處理復(fù)雜表格

在《用PowerBI自定義函數(shù)批量處理復(fù)雜表格》中我提到用自定義函數(shù)處理復(fù)雜表格葵蒂。
這是針對(duì)有N個(gè)復(fù)雜表格需要處理的情況——我們只需要把每個(gè)復(fù)雜表格處理的步驟用函數(shù)封裝起來(lái)蛾扇,就能在其他復(fù)雜表格上調(diào)用該函數(shù)了。

但是星持,在用自定義函數(shù)處理復(fù)雜一個(gè)表格時(shí),當(dāng)處理步驟很復(fù)雜的時(shí)候,還可以利用PowerQuery的特性藻茂,采用分而治之的辦法,把復(fù)雜表格分割成不同的處理步驟玫恳,最后再把分步處理結(jié)果合并起來(lái)即可辨赐,

需求

現(xiàn)在有很多個(gè)下面這樣的表格,每個(gè)表格是一個(gè)單獨(dú)的sheet京办,同一個(gè)部門(mén)所有人都在一個(gè)Excel文件的N個(gè)sheets中掀序。主管和員工的表格有差異(主管沒(méi)有部門(mén)主管評(píng)分,有財(cái)務(wù)部評(píng)分惭婿,各層級(jí)打分者在四個(gè)一級(jí)維度中所占的權(quán)重不同不恭,部門(mén)主管的考核項(xiàng)多了一個(gè)部門(mén)管理)。如下表所示:

被考核人: 考核周期:
評(píng)價(jià)項(xiàng)目 考評(píng)者
總經(jīng)辦評(píng)分 部門(mén)主管評(píng)分 行政人力評(píng)分 自評(píng)
考核權(quán)重評(píng)分 獎(jiǎng)勵(lì)性權(quán)重評(píng)分 考核權(quán)重評(píng)分 獎(jiǎng)勵(lì)性權(quán)重評(píng)分 考核權(quán)重評(píng)分 獎(jiǎng)勵(lì)性權(quán)重評(píng)分 考核權(quán)重評(píng)分
工作態(tài)度 積極主動(dòng)性
責(zé)任感
團(tuán)隊(duì)協(xié)作精神
考核得分: 獎(jiǎng)勵(lì)得分: 0.00
工作量 飽和度
完成度
考核得分: 獎(jiǎng)勵(lì)得分: 0.00
工作成果 工作目標(biāo)的達(dá)成
工作效率
工作質(zhì)量
考核得分: 獎(jiǎng)勵(lì)得分: 0.00
本月最終評(píng)分統(tǒng)計(jì) 0 0 0 0 0 0 0
最終考核得分: 最終獎(jiǎng)勵(lì)得分: 0.00
填表說(shuō)明:
xxx

計(jì)算公式為:
員工工作態(tài)度考核得分=SUM(總經(jīng)辦評(píng)分)15%+SUM(部門(mén)主管評(píng)分)15%+SUM(行政人力評(píng)分)60%+SUM(自評(píng))10%

員工工作態(tài)度獎(jiǎng)勵(lì)得分=SUM(總經(jīng)辦獎(jiǎng)勵(lì)評(píng)分)20%+SUM(部門(mén)主管獎(jiǎng)勵(lì)評(píng)分)20%+SUM(行政人力獎(jiǎng)勵(lì)評(píng)分)*60%

員工工作量考核得分=SUM(總經(jīng)辦評(píng)分)15%+SUM(部門(mén)主管評(píng)分)60%+SUM(行政人力評(píng)分)15%+SUM(自評(píng))10%

員工工作量獎(jiǎng)勵(lì)得分=SUM(總經(jīng)辦獎(jiǎng)勵(lì)評(píng)分)20%+SUM(部門(mén)主管獎(jiǎng)勵(lì)評(píng)分)60%+SUM(行政人力獎(jiǎng)勵(lì)評(píng)分)*20%

員工工作成果考核得分=SUM(總經(jīng)辦評(píng)分)60%+SUM(部門(mén)主管評(píng)分)15%+SUM(行政人力評(píng)分)15%+SUM(自評(píng))10%

員工工作成果獎(jiǎng)勵(lì)得分=SUM(經(jīng)辦獎(jiǎng)勵(lì)評(píng)分)60%+SUM(部門(mén)主管獎(jiǎng)勵(lì)評(píng)分)20%+SUM(行政人力獎(jiǎng)勵(lì)評(píng)分)*20%

員工最終考核得分=員工工作態(tài)度考核得分+員工工作量考核得分+員工工作成果考核得分

員工最終獎(jiǎng)勵(lì)得分=員工工作態(tài)度獎(jiǎng)勵(lì)得分+員工工作量獎(jiǎng)勵(lì)得分+員工工作成果獎(jiǎng)勵(lì)得分

管理員的得分計(jì)算公式與此類(lèi)似财饥,只是具體權(quán)重不同换吧,而且部門(mén)主管變成了財(cái)務(wù)部,就不一一列舉了佑力。

要求是把所有人部門(mén)所有人(主管和員工)的打分結(jié)果匯總統(tǒng)計(jì)出來(lái)式散,便于財(cái)務(wù)發(fā)工資。

分析

這個(gè)表格變態(tài)復(fù)雜打颤,填表的人頭大如豆暴拄,最后統(tǒng)計(jì)匯總打分結(jié)果的人更是頭大如豆。在既成事實(shí)的前提下编饺,再提把表格變得更簡(jiǎn)單已經(jīng)無(wú)意義乖篷。
現(xiàn)在最緊迫的是如何完成快速統(tǒng)計(jì)工作。

我想到三種方法:vba透且、python和powerquery撕蔼。
vba我不會(huì)。
python嘗試了下xlwings秽誊,只會(huì)絕對(duì)引用鲸沮,那樣的話如果有一個(gè)表格的某個(gè)單元格和別的不一樣,就不行了锅论。
最后還是回到我最熟悉的powerquery讼溺。

引入單一數(shù)據(jù)源

有N個(gè)Excel文件和M個(gè)sheets需要合并,因此首先引入來(lái)自文件夾的源最易,這樣就把所有的Excel文件和sheets一次性導(dǎo)入powerquery了怒坯。

let
    源 = Folder.Files(path),
    刪除的其他列 = Table.SelectColumns(源,{"Content", "Name"}),
    已添加自定義 = Table.AddColumn(刪除的其他列, "自定義", each Excel.Workbook([Content])),
    #"展開(kāi)的“自定義”" = Table.ExpandTableColumn(已添加自定義, "自定義", {"Data", "Item"}, {"自定義.Data", "自定義.Item"}),
    刪除的列 = Table.RemoveColumns(#"展開(kāi)的“自定義”",{"Content"})
in
    刪除的列

Folder.Files()函數(shù)把文件夾下的所有文件引入powerquery炫狱;Excel.Workbook()函數(shù)解析Excel文件中的sheets,把每一個(gè)Excel文件中的每個(gè)sheets變成一個(gè)table剔猿,然后所有這些tables嵌套在表示單一Excel文件的table中视译。
待會(huì)兒的工作就要在這些嵌套tables上進(jìn)行。

由于主管和員工的表格不一樣归敬,因此我決定先篩選出主管和員工表格酷含,分開(kāi)處理。
觀察主管和員工表格弄慰,發(fā)現(xiàn)第八列【Column8】不一樣:主管表格是財(cái)務(wù)評(píng)分第美,員工表格是部門(mén)主管評(píng)分蝶锋。因此直接添加一列來(lái)判斷是否主管陆爽。

= Table.AddColumn(源, "是否主管", each if  List.Contains([自定義.Data][Column8],"部門(mén)主管評(píng)分")  then "N" else "Y")

然后根據(jù)【是否主管】這個(gè)字段的值,將數(shù)據(jù)源分為兩個(gè)查詢(xún):主管得分和員工得分扳缕。
當(dāng)然慌闭,這一步不是必須,建立函數(shù)后躯舔,可以利用if判斷語(yǔ)句來(lái)一次同時(shí)處理主管和員工的表格驴剔。

利用powerquery【let…in】語(yǔ)句特性分步處理同一個(gè)表格

在Powerquery中,【let…in】有一個(gè)特性粥庄,即可以在任意步驟中隨時(shí)跳轉(zhuǎn)丧失,利用這個(gè)特性,就可以在同一個(gè)查詢(xún)中惜互,將復(fù)雜處理分為不同步驟布讹,最后再把各步處理的結(jié)果合并起來(lái)。
具體而言训堆,我將主管考核表中的四個(gè)考核項(xiàng)分別處理(以自定義函數(shù)的方式):

(tbl as table)=>let
    源 = tbl,
    替換的值= Table.ReplaceValue(源,null,0,Replacer.ReplaceValue,{"姓名", "評(píng)價(jià)維度", "總經(jīng)辦評(píng)分", "總經(jīng)辦加減分", "財(cái)務(wù)評(píng)分", "財(cái)務(wù)加減分", "行政人力評(píng)分", "行政人力加減分", "自評(píng)"}),
    start = 替換的值,
    篩選的行10 = Table.SelectRows(start, each ([評(píng)價(jià)維度] = "部門(mén)管理")),
    已添加自定義 = Table.AddColumn(篩選的行10, "考核得分", each [總經(jīng)辦評(píng)分]*0.5+[財(cái)務(wù)評(píng)分]*0.25+[行政人力評(píng)分]*0.15+[自評(píng)]*0.10),
    rename = Table.AddColumn(已添加自定義, "獎(jiǎng)勵(lì)得分", each [總經(jīng)辦加減分]*0.50+[行政人力加減分]*0.15+[財(cái)務(wù)加減分]*0.35),
    重命名的列3 = Table.RenameColumns(rename,{{"總經(jīng)辦加減分", "總經(jīng)辦獎(jiǎng)勵(lì)得分"}, {"財(cái)務(wù)加減分", "財(cái)務(wù)獎(jiǎng)勵(lì)得分"}, {"行政人力加減分", "行政人力獎(jiǎng)勵(lì)得分"}}),
    部門(mén)管理 = Table.RenameColumns(重命名的列3,{{"總經(jīng)辦評(píng)分", "總經(jīng)辦考核評(píng)分"}, {"財(cái)務(wù)評(píng)分", "財(cái)務(wù)考核評(píng)分"}, {"行政人力評(píng)分", "行政人力考核評(píng)分"}}),
    自定義1 =start,
    篩選的行2 = Table.SelectRows(start, each ([評(píng)價(jià)維度] = "工作態(tài)度")),
    自定義2 = Table.Group(篩選的行2, {"姓名", "評(píng)價(jià)維度"}, {
        {"總經(jīng)辦考核評(píng)分", each 0.35*List.Sum([總經(jīng)辦評(píng)分]), type number},
         {"財(cái)務(wù)考核評(píng)分", each 0.35*List.Sum([財(cái)務(wù)評(píng)分]), type number}, 
         {"行政人力考核評(píng)分", each 0.20*List.Sum([行政人力評(píng)分]), type number}, 
         {"自評(píng)", each 0.1*List.Sum([自評(píng)]), type number},
         {"總經(jīng)辦獎(jiǎng)勵(lì)得分", each 0.35*List.Sum([總經(jīng)辦加減分]), type number}, 
         {"財(cái)務(wù)獎(jiǎng)勵(lì)得分", each 0.30*List.Sum([財(cái)務(wù)加減分]), type number}, 
         {"行政人力獎(jiǎng)勵(lì)得分", each 0.35*List.Sum([行政人力加減分]), type number}}),
    已添加自定義20 = Table.AddColumn(自定義2, "考核得分", each [總經(jīng)辦考核評(píng)分]+[財(cái)務(wù)考核評(píng)分]+[行政人力考核評(píng)分]+[自評(píng)]),
    工作態(tài)度 = Table.AddColumn(已添加自定義20, "獎(jiǎng)勵(lì)得分", each [總經(jīng)辦獎(jiǎng)勵(lì)得分]+[財(cái)務(wù)獎(jiǎng)勵(lì)得分]+[行政人力獎(jiǎng)勵(lì)得分]),
    自定義3 =  start,
    篩選的行3 = Table.SelectRows(自定義3, each ([評(píng)價(jià)維度] = "工作成果")),
    自定義4 = Table.Group(篩選的行3, {"姓名", "評(píng)價(jià)維度"}, {
        {"總經(jīng)辦考核評(píng)分", each 0.35*List.Sum([總經(jīng)辦評(píng)分]), type number},
         {"財(cái)務(wù)考核評(píng)分", each 0.35*List.Sum([財(cái)務(wù)評(píng)分]), type number}, 
         {"行政人力考核評(píng)分", each 0.20*List.Sum([行政人力評(píng)分]), type number}, 
         {"自評(píng)", each 0.1*List.Sum([自評(píng)]), type number},
         {"總經(jīng)辦獎(jiǎng)勵(lì)得分", each 0.40*List.Sum([總經(jīng)辦加減分]), type number}, 
         {"財(cái)務(wù)獎(jiǎng)勵(lì)得分", each 0.20*List.Sum([財(cái)務(wù)加減分]), type number}, 
         {"行政人力獎(jiǎng)勵(lì)得分", each 0.40*List.Sum([行政人力加減分]), type number}}),
    已添加自定義3 = Table.AddColumn(自定義4, "考核得分", each [總經(jīng)辦考核評(píng)分]+[財(cái)務(wù)考核評(píng)分]+[行政人力考核評(píng)分]+[自評(píng)]),
    工作成果 = Table.AddColumn(已添加自定義3, "獎(jiǎng)勵(lì)得分", each [總經(jīng)辦獎(jiǎng)勵(lì)得分]+[財(cái)務(wù)獎(jiǎng)勵(lì)得分]+[行政人力獎(jiǎng)勵(lì)得分]),
    自定義5 = start,
    篩選的行4 = Table.SelectRows(自定義5, each ([評(píng)價(jià)維度] = "工作量")),
    自定義6 = Table.Group(篩選的行4, {"姓名", "評(píng)價(jià)維度"}, {
        {"總經(jīng)辦考核評(píng)分", each 0.35*List.Sum([總經(jīng)辦評(píng)分]), type number}, 
        {"財(cái)務(wù)考核評(píng)分", each 0.35*List.Sum([財(cái)務(wù)評(píng)分]), type number}, 
        {"行政人力考核評(píng)分", each 0.20*List.Sum([行政人力評(píng)分]), type number}, 
        {"自評(píng)", each 0.1*List.Sum([自評(píng)]), type number},
        {"總經(jīng)辦獎(jiǎng)勵(lì)得分", each 0.35*List.Sum([總經(jīng)辦加減分]), type number}, 
        {"財(cái)務(wù)獎(jiǎng)勵(lì)得分", each 0.30*List.Sum([財(cái)務(wù)加減分]), type number}, 
        {"行政人力獎(jiǎng)勵(lì)得分", each 0.35*List.Sum([行政人力加減分]), type number}}),
    已添加自定義4 = Table.AddColumn(自定義6, "考核得分", each [總經(jīng)辦考核評(píng)分]+[財(cái)務(wù)考核評(píng)分]+[行政人力考核評(píng)分]+[自評(píng)]),
    工作量 = Table.AddColumn(已添加自定義4, "獎(jiǎng)勵(lì)得分", each [總經(jīng)辦獎(jiǎng)勵(lì)得分]+[財(cái)務(wù)獎(jiǎng)勵(lì)得分]+[行政人力獎(jiǎng)勵(lì)得分]),
    自定義7 = Table.Combine({部門(mén)管理,工作態(tài)度,工作成果,工作量}),
    分組的行1 = Table.Group(自定義7, {"姓名"}, {{"考核得分", each List.Sum([考核得分]), type number}, {"獎(jiǎng)勵(lì)得分", each List.Sum([獎(jiǎng)勵(lì)得分]), type none}})
in
    分組的行1

從第10行開(kāi)始描验,我分別用了【自定義1】、【自定義3】和【自定義5】引用了同一個(gè)步驟【start】坑鱼,【start】步驟就是包含【部門(mén)管理】膘流、【工作態(tài)度】、【工作量】和【工作成果】四個(gè)考核指標(biāo)的一個(gè)大表格鲁沥,我每引用一次【start】呼股,就對(duì)其篩選一次,處理四個(gè)考核指標(biāo)中的一個(gè)画恰,處理完了再回到【start】彭谁,篩選另一個(gè)指標(biāo)進(jìn)行處理,以此類(lèi)推阐枣。四個(gè)指標(biāo)處理完畢之后马靠,得到四個(gè)表格奄抽,用Table.Combine()函數(shù)合并四個(gè)表格。

上面那段很長(zhǎng)的代碼甩鳄,我是完全復(fù)現(xiàn)了每個(gè)考核指標(biāo)的計(jì)算公式逞度,如果只想要結(jié)果的話,可以略過(guò)公式計(jì)算妙啃,直接對(duì)表格中的【考核得分】列進(jìn)行處理档泽,這樣步驟會(huì)簡(jiǎn)潔很多,甚至都不需要分而治之的策略了揖赴。

用同樣的思路處理員工表格馆匿。最后將兩個(gè)表格合并起來(lái)就ok了。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末燥滑,一起剝皮案震驚了整個(gè)濱河市渐北,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌铭拧,老刑警劉巖赃蛛,帶你破解...
    沈念sama閱讀 216,324評(píng)論 6 498
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場(chǎng)離奇詭異搀菩,居然都是意外死亡呕臂,警方通過(guò)查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,356評(píng)論 3 392
  • 文/潘曉璐 我一進(jìn)店門(mén)肪跋,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)歧蒋,“玉大人,你說(shuō)我怎么就攤上這事州既∶涨ⅲ” “怎么了?”我有些...
    開(kāi)封第一講書(shū)人閱讀 162,328評(píng)論 0 353
  • 文/不壞的土叔 我叫張陵易桃,是天一觀的道長(zhǎng)褥琐。 經(jīng)常有香客問(wèn)我,道長(zhǎng)晤郑,這世上最難降的妖魔是什么敌呈? 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 58,147評(píng)論 1 292
  • 正文 為了忘掉前任,我火速辦了婚禮造寝,結(jié)果婚禮上磕洪,老公的妹妹穿的比我還像新娘。我一直安慰自己诫龙,他們只是感情好析显,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,160評(píng)論 6 388
  • 文/花漫 我一把揭開(kāi)白布。 她就那樣靜靜地躺著签赃,像睡著了一般谷异。 火紅的嫁衣襯著肌膚如雪分尸。 梳的紋絲不亂的頭發(fā)上,一...
    開(kāi)封第一講書(shū)人閱讀 51,115評(píng)論 1 296
  • 那天歹嘹,我揣著相機(jī)與錄音箩绍,去河邊找鬼。 笑死尺上,一個(gè)胖子當(dāng)著我的面吹牛材蛛,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播怎抛,決...
    沈念sama閱讀 40,025評(píng)論 3 417
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼卑吭,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼!你這毒婦竟也來(lái)了马绝?” 一聲冷哼從身側(cè)響起豆赏,我...
    開(kāi)封第一講書(shū)人閱讀 38,867評(píng)論 0 274
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤,失蹤者是張志新(化名)和其女友劉穎迹淌,沒(méi)想到半個(gè)月后河绽,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 45,307評(píng)論 1 310
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡唉窃,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,528評(píng)論 2 332
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了纹笼。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片纹份。...
    茶點(diǎn)故事閱讀 39,688評(píng)論 1 348
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖廷痘,靈堂內(nèi)的尸體忽然破棺而出蔓涧,到底是詐尸還是另有隱情,我是刑警寧澤笋额,帶...
    沈念sama閱讀 35,409評(píng)論 5 343
  • 正文 年R本政府宣布元暴,位于F島的核電站,受9級(jí)特大地震影響兄猩,放射性物質(zhì)發(fā)生泄漏茉盏。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,001評(píng)論 3 325
  • 文/蒙蒙 一枢冤、第九天 我趴在偏房一處隱蔽的房頂上張望鸠姨。 院中可真熱鬧,春花似錦淹真、人聲如沸讶迁。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 31,657評(píng)論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)巍糯。三九已至啸驯,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間祟峦,已是汗流浹背坯汤。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 32,811評(píng)論 1 268
  • 我被黑心中介騙來(lái)泰國(guó)打工, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留搀愧,地道東北人惰聂。 一個(gè)月前我還...
    沈念sama閱讀 47,685評(píng)論 2 368
  • 正文 我出身青樓,卻偏偏與公主長(zhǎng)得像咱筛,于是被迫代替她去往敵國(guó)和親搓幌。 傳聞我的和親對(duì)象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,573評(píng)論 2 353

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