|
你好，我是刘卓。欢迎来到我的公号，excel函数解析。今天来分享一个合并单元格求和的问题。用函数解决合并单元格的问题，思路就是把合并单元格补充满，主要用到lookup函数。 函数说明 下图左表的数据源记录的是各种电器的销量，其中电器这一列用的是合并单元格。现在的要求是计算下哪种电器的总销量最高，以及最高总销量是多少。 为方便大家理解，我用取消合并单元格的数据做了一个透视表，展示了各电器的总销量。如下图右表所示，可以看到"冰箱"的总销量最高。1）首先来看最高总销量的公式，在G3单元格输入下面的公式，按ctrl+shift+enter。=MAX(MMULT(N(TRANSPOSE(LOOKUP(ROW(3:20),ROW(3:20)/(A3:A20>0),A3:A20))=A3:A20),B3:B20))
下面来拆解下这个公式，lookup这部分把合并单元格补充完整，得到一个数组。公式和结果如下图所示。接下来，要计算一下各种电器的总销量，但这时候不能用sumif来求和，那就只能用mmult来求和了。 用transpose把lookup一列的数组转置成一行，如下图D2:U2所示。再用转置后的结果(条件区域)和A3:A20(条件)做相等的比较。形成一个二维数组，最后用n函数把比较的结果转换成0和1。用mmult函数对包含1和0的数组和B3:B20作矩阵乘积，得到了每种电器的总销量，公式和结果如下图所示。最后用max取出最大值就完成了。2）再来看电器的公式，在F3单元格输入下面的公式，按ctrl+shift+enter。=INDEX(A:A,MOD(MAX(MMULT(N(TRANSPOSE(LOOKUP(ROW(3:20),ROW(3:20)/(A3:A20>0),A3:A20))=A3:A20),B3:B20)/1%+ROW(3:20)),100))
在第1问mmult的基础上使用加权法，就是把mmult求出来的每种电器的总销量先乘以100，再加上对应的行号。如下图所示。接下来用max取出最大值242917，总销量最大的是2429，在第17行。我们要的是行号，用mod对242917除以100取余数，得到行号17。最后用index返回A列第17个单元格的电器。https://pan.baidu.com/s/1tehj_iNYFzymrhUJJl4Ryg
|
