| 楼主 gouweicao78 |
本题为2006年3月我在EH出的开放式考题，已收录在《Excel 函数与公式实战技巧精粹》一书。 【题目】A、B、C三个部门在年终考评得分如图中左边表格： 1、考核项目分服务态度、办事效率、创新活动3个，评分所占系数分别为0.5、0.3、0.2（存放在单元格，可变） 2、评分用优、良、中、差4个等级表示，右侧有这4个等级的成绩换算表，分别为10、8、6、4分（存放在单元格，可变） 求：在H21:H23单元格用公式请根据规则算出各个部门的综合评分。  【附件】  考评综合评分（总结）.rar |
| 2楼 gouweicao78 |
解法1（作者：onkey） =MMULT(MMULT(N(G21:G23=TRANSPOSE(B7:B15)),VLOOKUP(C7:E15,G10:H13,2,)),TRANSPOSE(C6:E6)) 1、用MMULT将部门=G21:G23的（得到的是3行9列的True、False数组）与Vlookup对C7:E15的优良中差对应分数（3行9列分数数组）进行矩阵相乘。得到3行9列分数 2、用MMULT将上面得到的分数表与1行3列的系数矩阵相乘，得到最后得分。 高数学的真好！ 3、还有一点也很不错，这个解法用了多单元格数组公式！ 解法2 （作者：rongjun） =SUM(MMULT(TRANSPOSE(($B$7:$B$15=G21)*N(OFFSET($H$10,MATCH($C$7:$E$15,$G$10:$G$13,0)-1,))),ROW($B$7:$B$15)^0)*TRANSPOSE($C$6:$E$6)) 1、用Transpose将“部门=G21（下拖变化）与offset+match对C7:E15的优良中差对应分数相乘得到的数组——9行3列”转置为3列9行的分数； 2、用row(B7:B15)^0——任何非0的数的0次方都为1——得到1列9行的都是1的数组； 3、用MMULT将上述2个数组矩阵相乘得到（以部门A为例{22;20;22}）3列1行的数组 3、用SUM将此数组与转置的系数（1列3行）相乘——得到最后分数21.4 解法3（作者：qlz110、yeatol简化） =SUM((B$7:B$15=G21)*VLOOKUP(C$7:E$15,G$10:H$13,2,)*C$6:E$6) "1、此解法是出题时的一个主要解题思路，主要是利用vlookup第一参数为数组进行多个查询。 2、公式简单，用*分段将公式分成3段再套SUM，就是3个数组相乘再加总而已 3、此法特色——vlookup、hlookup类的函数第一参数为“数组或多于1个单元格的矩形连续区域时，……均可以真实返回一个可以继续参与运算的数组，关键是最后的公式必须是一个多单元格数组公式。 解法4 （作者：BIN_YANG168） =SUM(IF($B$7:$B$15=G21,INDEX($H$10:$H$13,MATCH($C$7:$E$15,$G$10:$G$13,0))*$C$6:$E$6)) 1、此解法将上面公式中的vlookup改为index+match 2、仍然是要求2个或以上单元格组成多单元格数组公式来完成 3、类似的函数——即这种第一参数为数组要用2个或以上单元格的多单元格数组公式来完成的函数有：hlookup、lookup、match。 解法5（作者：qlz110） =SUM(IF($B$7:$B$15=G21,SUMIF($G$10:$G$13,$C$7:$E$15,$H$10:$H$13)*$C$6:$E$6)) 1、核心——sumif函数的第二参数，即条件。本解法是利用多条件数组来解。 即：如果部门=G21，则以评价区域C7：E15为（多单元格组成的）多条件数组，在评分规则进行条件求和，并用此求和（得到的是3列1行的“服务态度总分、办事效率总分、创新活动总分”）*系数{0.5,0.3,0.2} 2、关于sumif多条件数组应用详见以下链接： 多条件数组在sumif、countif中的用法简介 解法6（作者：gdliyy简化） =SUM((B$7:B$15=G21)*SUMIF(G:G,C$7:E$15,H:H)*C$6:E$6) 直接将if条件用数组相乘的方法替代，使公式精简得比vlookup公式本身还短，且不用辅助单元格。——有了解法5的理解，这个就不难了理解了。 解法7（作者：fy5s） =SUM(($B$7:$B$15=$G21)*(12-2*SEARCH($C$7:$E$15,"优良中差"))*C$6:E$6) 1、新思路非通用解法，用search函数找出评分区域C7:E15在优良中差中的位置，用12-2*这个位置来取得得分。 2、用sum(条件*得分*系数)加总。 |
| 3楼 monvzhilei |
我找错， 用MMULT将部门=G21:G23的（得到的是3列9行的True、False数组） 应该是:用MMULT将部门=G21:G23的（得到的是3行9列的True、False数组） |
| 4楼 wangqilong1980 |
各解法点评 解法1（作者：onkey） =MMULT(MMULT(N(G21:G23=TRANSPOSE(B7:B15)),VLOOKUP(C7:E15,G10:H13,2,)),TRANSPOSE(C6:E6)) 1、用MMULT将部门=G21:G23的（得到的是3行9列的True、False数组）与Vlookup对C7:E15的优良中差对应分数（3行9列分数数组）进行矩阵相乘。得到3行9列分数 2、用MMULT将上面得到的分数表与1行3列的系数矩阵相乘，得到最后得分。 高数学的真好！ 3、还有一点也很不错，这个解法用了多单元格数组公式！ 解法2 （作者：rongjun） =SUM(MMULT(TRANSPOSE(($B$7:$B$15=G21)*N(OFFSET($H$10,MATCH($C$7:$E$15,$G$10:$G$13,0)-1,))),ROW($B$7:$B$15)^0)*TRANSPOSE($C$6:$E$6)) 1、用Transpose将“部门=G21（下拖变化）与offset+match对C7:E15的优良中差对应分数相乘得到的数组——9行3列”转置为3列9行的分数； 2、用row(B7:B15)^0——任何非0的数的0次方都为1——得到1列9行的都是1的数组； 3、用MMULT将上述2个数组矩阵相乘得到（以部门A为例{22;20;22}）3列1行的数组 3、用SUM将此数组与转置的系数（1列3行）相乘——得到最后分数21.4 解法3（作者：qlz110、yeatol简化） =SUM((B$7:B$15=G21)*VLOOKUP(C$7:E$15,G$10:H$13,2,)*C$6:E$6) "1、此解法是出题时的一个主要解题思路，主要是利用vlookup第一参数为数组进行多个查询。 2、公式简单，用*分段将公式分成3段再套SUM，就是3个数组相乘再加总而已 3、此法特色——vlookup、hlookup类的函数第一参数为“数组或多于1个单元格的矩形连续区域时，……均可以真实返回一个可以继续参与运算的数组，关键是最后的公式必须是一个多单元格数组公式。 解法4 （作者：BIN_YANG168） =SUM(IF($B$7:$B$15=G21,INDEX($H$10:$H$13,MATCH($C$7:$E$15,$G$10:$G$13,0))*$C$6:$E$6)) 1、此解法将上面公式中的vlookup改为index+match 2、仍然是要求2个或以上单元格组成多单元格数组公式来完成 3、类似的函数——即这种第一参数为数组要用2个或以上单元格的多单元格数组公式来完成的函数有：hlookup、lookup、match。 解法5（作者：qlz110） =SUM(IF($B$7:$B$15=G21,SUMIF($G$10:$G$13,$C$7:$E$15,$H$10:$H$13)*$C$6:$E$6)) 1、核心——sumif函数的第二参数，即条件。本解法是利用多条件数组来解。 即：如果部门=G21，则以评价区域C7：E15为（多单元格组成的）多条件数组，在评分规则进行条件求和，并用此求和（得到的是3列1行的“服务态度总分、办事效率总分、创新活动总分”）*系数{0.5,0.3,0.2} 2、关于sumif多条件数组应用详见以下链接：多条件数组在sumif、countif中的用法简介 解法6（作者：gdliyy简化） =SUM((B$7:B$15=G21)*SUMIF(G:G,C$7:E$15,H:H)*C$6:E$6) 直接将if条件用数组相乘的方法替代，使公式精简得比vlookup公式本身还短，且不用辅助单元格。——有了解法5的理解，这个就不难了理解了。 解法7（作者：fy5s） =SUM(($B$7:$B$15=$G21)*(12-2*SEARCH($C$7:$E$15,"优良中差"))*C$6:E$6) 1、新思路非通用解法，用search函数找出评分区域C7:E15在优良中差中的位置，用12-2*这个位置来取得得分。 2、用sum(条件*得分*系数)加总。 |
| 5楼 7786910 |
学习了 |
| 6楼 LOGO |
学习，要是没记错的话《EXCEL应用大全》一书中也收录了，书中的是解法6. |
| 7楼 那头懒驴 |
学习了 |