察颜观色，今年的年终奖大揭秘
大年初七，开工第一天，关于年终奖也解密了，根据贡献大小，老板都是有标注的。心中有数、手下有色，简单明了，被人事和财务察言观色，搞得妥妥的，这究竟是怎么一回事呢？
问题提出
人事拿了员工花名册，每个部门列在一张工作表上，让老板定每个人应当发多少年终奖，财务部门一核算，资金总额不能超过37万元。老板体谅下面人，说了“我也不搞一言堂，我电脑水平有限，我就简单在人名上标个色，你们自己去定，颜色重的可以多发点。”
问题分析
花名册上的员工很多，为方便大家理解，这里仅列举局部如图1，要解决几个问题：标注了几种色彩？每种颜色有几人？。最浅色以系数1为基数，颜色每晋一级系数加0.1，各部门所有人员系数之和作为除数，资金总额作为分母，这样可计算得到系数为1折算年终奖是多少，以此乘以每个员工的系数即可得到其年终奖金额。
从EXCEL技术的角度，需要取得颜色代码（由于存在相同背景色的人员（重码），可使用UNIQUE函数，因为代码区域并非单一连续区域，所以需将他们拼接成单一区域作为UNIQUE函数的参数，单元格区域可视作区域数组，将多个一维数组拼成一个一维数组，这是一个难点。
图1 三部门奖金拟发（定色，局部）
问题解决
人事总监和财务总监闭门商量了一下，决定察言观色，深刻领会老板的意思。首先在老板的色表基础上增加了一个新工作表“汇总”，然后按下面的步骤“做”一下：
（一）准备取色函数
①点击【公式】【定义名称】，在弹出的对话框的“名称”中输入“getcolor”，在“引用位置”输入“=GET.CELL(63,INDIRECT("RC[-1]",FALSE))”，注意这里使用的是RC格式，表示取公式所在位置的左邻单元格颜色代码，此公式不能在单元格中直接使用；
②在图1分别在“工程部”“财务部”“生产部”工作表F8单元格输入公式“=getcolor”，并向下拖拉至F12，得到各个表格颜色单元格的代码值；
图2 年终奖发放系数计算表
（二）准备工作表清单函数
①点击【公式】【定义名称】，在弹出的对话框的“名称”中输入“getlist”，在“引用位置”输入“=GET.WORKBOOK(1)&T(NOW())”，此公式不能在单元格中直接使用；
②在图2单元格B4输入公式“=MID(INDEX(getlist,COLUMN(B1)),1+FIND("]",INDEX(getlist,COLUMN(B1))),99)”，并向拖拉n（所有部门的个数）个位置，得到各个部门的名字；这时的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(财务部!F8:F99))&"</b>"&
"<b>"&TEXTJOIN("</b><b>",1,TRANSPOSE(生产部!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（所有部门的个数）个位置，得到各个部门工作中各种色彩的唯一值；这时的99是工作表中员工所在单元格的行号的最大值，也可改为999；
②在图2单元格E5输入公式“="<a><b>"&TEXTJOIN("</b><b>",TRUE,TRANSPOSE(工程部!F8:F99))&"</b>"&"<b>"&TEXTJOIN("</b><b>",TRUE,TRANSPOSE(财务部!F8:F99))&"</b>"&"<b>"&TEXTJOIN("</b><b>",TRUE,TRANSPOSE(生产部!F8:F99))&"</b></a>"”(如表1)，这是为了构建XML格式所需要的字符，其中:
a)字母a和b可以修改为其他任意两个不同的字符（如m和n）；
b)字符串“</b><b>”是TEXTJOIN将各个部门工作中各种色彩代码值的连接标志符号；
c)公式中3处划线部分可依次改为“B5:B99”“C5:C99”“D5:D99”, 这时的99是色彩种类个数的最大值，也可改为999；
③在图2单元格F5输入公式“=SORT(UNIQUE(FILTERXML(E5,"a/b")))”，将拼接出来的字符串拆分后，取唯一值，再排序，得到“19;36;40;46;53”,也就是老板所有标色的种类的个数。
（四）计算发放系数
①在图2单元格G5和G6分别输入1.1和1.2，然后选中这两个单元格，向下拖拉到99行；
②在图2单元格H4输入公式“=B4”,并向右拖拉n（所有部门的个数）个位置，得到各个部门的名字；
③在图2单元格H5输入公式“=$G5*COUNTIF(INDIRECT(H$4&"!F8:F99"),$F5)”，,并向右拖拉n（所有部门的个数）个位置，向下拖拉到99行，计算得到每个部门每种色值的总系数。
图3 计算员工年终奖
（五）计算员工发放金额
①在图2单元格G2输入资金总额37万元，单元格J2输入公式“=G2/SUM(H5:J98)”,计算得到系数为1时的年终奖发放金额；
②在图3工作表“工程部”单元格G8和H8分别输入公式“=VLOOKUP(F8,汇总!$F$4:$G$9,2)”“=G8*汇总!$J$2”,根据员工色值从“汇总”工作表中取得该值对应的系数乘以单位奖金金额得到该员工应得的年终奖；
③在图3单元格G13和H13输入求和公式，即得到工程部的总系数和年终奖总金额；
④按照②和③的方法，完成财务部和生产部的总系数和年终奖总金额。
方案解读
①人事总监和财务总监的步骤概要如下：
a)先定义两个名称用于取色值和工作表清单，
b)再将位于不同工作表中的单元格区域拼接成一个数组，供FILTERXML拆分后用UNIQUE去重，得到色值类别总数
c)根据色值计算得到发放系数，再根据资金总额折算单位发放金额，最后得到每位员工的年终奖。
②图2 “汇总”工作表，容易在以下两处犯错：
a)B:E列仅为显示易读的目的，可以不列出，E5公式可直接嵌入F5公式中；b)G列取得的“发放系数”未使用COUNTIF乘以同色值员工数。
③将多个一维数组拼接成一个一维数组，是本文的知识难点，如果要规避它，可以有两种方法：
a)使用数据透视表，数据来源选取来自不同工作表多区域，方法可参照《财务精英都是Excel控：CFO手把手教你学管理会计》P73、P82；
图4 源自多表的数据透视
b)直接将多张工作表上的标色内容手动复制到一张工作表中，排序后使用分类汇总，这是最“笨”的方法。
