在《用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了。