察顏觀色，今年的年終獎大揭秘
大年初七，開工第一天，關(guān)于年終獎也解密了，根據(jù)貢獻大小，老板都是有標(biāo)注的。心中有數(shù)、手下有色，簡單明了，被人事和財務(wù)察言觀色，搞得妥妥的，這究竟是怎么一回事呢？
問題提出
人事拿了員工花名冊，每個部門列在一張工作表上，讓老板定每個人應(yīng)當(dāng)發(fā)多少年終獎，財務(wù)部門一核算，資金總額不能超過37萬元。老板體諒下面人，說了“我也不搞一言堂，我電腦水平有限，我就簡單在人名上標(biāo)個色，你們自己去定，顏色重的可以多發(fā)點?！?/span>
問題分析
花名冊上的員工很多，為方便大家理解，這里僅列舉局部如圖1，要解決幾個問題：標(biāo)注了幾種色彩？每種顏色有幾人？。最淺色以系數(shù)1為基數(shù)，顏色每晉一級系數(shù)加0.1，各部門所有人員系數(shù)之和作為除數(shù)，資金總額作為分母，這樣可計算得到系數(shù)為1折算年終獎是多少，以此乘以每個員工的系數(shù)即可得到其年終獎金額。
從EXCEL技術(shù)的角度，需要取得顏色代碼（由于存在相同背景色的人員（重碼），可使用UNIQUE函數(shù)，因為代碼區(qū)域并非單一連續(xù)區(qū)域，所以需將他們拼接成單一區(qū)域作為UNIQUE函數(shù)的參數(shù)，單元格區(qū)域可視作區(qū)域數(shù)組，將多個一維數(shù)組拼成一個一維數(shù)組，這是一個難點。
圖1 三部門獎金擬發(fā)（定色，局部）
問題解決
人事總監(jiān)和財務(wù)總監(jiān)閉門商量了一下，決定察言觀色，深刻領(lǐng)會老板的意思。首先在老板的色表基礎(chǔ)上增加了一個新工作表“匯總”，然后按下面的步驟“做”一下：
（一）準備取色函數(shù)
①點擊【公式】【定義名稱】，在彈出的對話框的“名稱”中輸入“getcolor”，在“引用位置”輸入“=GET.CELL(63,INDIRECT("RC[-1]",FALSE))”，注意這里使用的是RC格式，表示取公式所在位置的左鄰單元格顏色代碼，此公式不能在單元格中直接使用；
②在圖1分別在“工程部”“財務(wù)部”“生產(chǎn)部”工作表F8單元格輸入公式“=getcolor”，并向下拖拉至F12，得到各個表格顏色單元格的代碼值；
圖2 年終獎發(fā)放系數(shù)計算表
（二）準備工作表清單函數(shù)
①點擊【公式】【定義名稱】，在彈出的對話框的“名稱”中輸入“getlist”，在“引用位置”輸入“=GET.WORKBOOK(1)&T(NOW())”，此公式不能在單元格中直接使用；
②在圖2單元格B4輸入公式“=MID(INDEX(getlist,COLUMN(B1)),1+FIND("]",INDEX(getlist,COLUMN(B1))),99)”，并向拖拉n（所有部門的個數(shù)）個位置，得到各個部門的名字；這時的99是工作表名字字符的最大長度，也可改為999；
表1 工作表“匯總”主要公式
單元格
公式
A5
=IF(F5="","","C"&ROW()-5)
B4
=MID(INDEX(getlist,COLUMN(B1)),1+FIND("]",INDEX(getlist,COLUMN(B1))),99)
B5
=UNIQUE(INDIRECT(B4&"!F8:F99"))
C4
=MID(INDEX(getlist,COLUMN(C1)),1+FIND("]",INDEX(getlist,COLUMN(C1))),99)
C5
=UNIQUE(INDIRECT(C4&"!F8:F99"))
D4
=MID(INDEX(getlist,COLUMN(D1)),1+FIND("]",INDEX(getlist,COLUMN(D1))),99)
D5
=UNIQUE(INDIRECT(D4&"!F8:F99"))
E5
="<a>"&
"<b>"&TEXTJOIN("</b><b>",1,TRANSPOSE(工程部!F8:F99))&"</b>"&
"<b>"&TEXTJOIN("</b><b>",1,TRANSPOSE(財務(wù)部!F8:F99))&"</b>"&
"<b>"&TEXTJOIN("</b><b>",1,TRANSPOSE(生產(chǎn)部!F8:F99))&"</b>"&
"</a>"
F5
=SORT(UNIQUE(FILTERXML(E5,"a/b")))
H4
=B4
H5
=$G5*COUNTIF(INDIRECT(H$4&"!F8:F99"),$F5)
I4
=C4
I5
=$G5*COUNTIF(INDIRECT(I$4&"!F8:F99"),$F5)
J2
=G2/SUM(H5:J98)
J4
=D4
J5
=$G5*COUNTIF(INDIRECT(J$4&"!F8:F99"),$F5)
（三）取各工作表的唯一值
①在圖2單元格B5輸入公式“=UNIQUE(INDIRECT(B4&"!F8:F99"))”，并向拖拉n（所有部門的個數(shù)）個位置，得到各個部門工作中各種色彩的唯一值；這時的99是工作表中員工所在單元格的行號的最大值，也可改為999；
②在圖2單元格E5輸入公式“="<a><b>"&TEXTJOIN("</b><b>",TRUE,TRANSPOSE(工程部!F8:F99))&"</b>"&"<b>"&TEXTJOIN("</b><b>",TRUE,TRANSPOSE(財務(wù)部!F8:F99))&"</b>"&"<b>"&TEXTJOIN("</b><b>",TRUE,TRANSPOSE(生產(chǎn)部!F8:F99))&"</b></a>"”(如表1)，這是為了構(gòu)建XML格式所需要的字符，其中:
a)字母a和b可以修改為其他任意兩個不同的字符（如m和n）；
b)字符串“</b><b>”是TEXTJOIN將各個部門工作中各種色彩代碼值的連接標(biāo)志符號；
c)公式中3處劃線部分可依次改為“B5:B99”“C5:C99”“D5:D99”, 這時的99是色彩種類個數(shù)的最大值，也可改為999；
③在圖2單元格F5輸入公式“=SORT(UNIQUE(FILTERXML(E5,"a/b")))”，將拼接出來的字符串拆分后，取唯一值，再排序，得到“19;36;40;46;53”,也就是老板所有標(biāo)色的種類的個數(shù)。
（四）計算發(fā)放系數(shù)
①在圖2單元格G5和G6分別輸入1.1和1.2，然后選中這兩個單元格，向下拖拉到99行；
②在圖2單元格H4輸入公式“=B4”,并向右拖拉n（所有部門的個數(shù)）個位置，得到各個部門的名字；
③在圖2單元格H5輸入公式“=$G5*COUNTIF(INDIRECT(H$4&"!F8:F99"),$F5)”，,并向右拖拉n（所有部門的個數(shù)）個位置，向下拖拉到99行，計算得到每個部門每種色值的總系數(shù)。
圖3 計算員工年終獎
（五）計算員工發(fā)放金額
①在圖2單元格G2輸入資金總額37萬元，單元格J2輸入公式“=G2/SUM(H5:J98)”,計算得到系數(shù)為1時的年終獎發(fā)放金額；
②在圖3工作表“工程部”單元格G8和H8分別輸入公式“=VLOOKUP(F8,匯總!$F$4:$G$9,2)”“=G8*匯總!$J$2”,根據(jù)員工色值從“匯總”工作表中取得該值對應(yīng)的系數(shù)乘以單位獎金金額得到該員工應(yīng)得的年終獎；
③在圖3單元格G13和H13輸入求和公式，即得到工程部的總系數(shù)和年終獎總金額；
④按照②和③的方法，完成財務(wù)部和生產(chǎn)部的總系數(shù)和年終獎總金額。
方案解讀
①人事總監(jiān)和財務(wù)總監(jiān)的步驟概要如下：
a)先定義兩個名稱用于取色值和工作表清單，
b)再將位于不同工作表中的單元格區(qū)域拼接成一個數(shù)組，供FILTERXML拆分后用UNIQUE去重，得到色值類別總數(shù)
c)根據(jù)色值計算得到發(fā)放系數(shù)，再根據(jù)資金總額折算單位發(fā)放金額，最后得到每位員工的年終獎。
②圖2 “匯總”工作表，容易在以下兩處犯錯：
a)B:E列僅為顯示易讀的目的，可以不列出，E5公式可直接嵌入F5公式中；b)G列取得的“發(fā)放系數(shù)”未使用COUNTIF乘以同色值員工數(shù)。
③將多個一維數(shù)組拼接成一個一維數(shù)組，是本文的知識難點，如果要規(guī)避它，可以有兩種方法：
a)使用數(shù)據(jù)透視表，數(shù)據(jù)來源選取來自不同工作表多區(qū)域，方法可參照《財務(wù)精英都是Excel控：CFO手把手教你學(xué)管理會計》P73、P82；
圖4 源自多表的數(shù)據(jù)透視
b)直接將多張工作表上的標(biāo)色內(nèi)容手動復(fù)制到一張工作表中，排序后使用分類匯總，這是最“笨”的方法。
