【辅助列解决实际问题】

cleverzhzhf

原帖地址：http://club.excelhome.net/thread-1158998-1-1.html

合并单元格，是常见的一个功能，能够对版面进行组合、美化，看上去很美。
可是在数据分析的过程中，合并单元格是一个令人抓狂、叫苦不迭的功能：
        1、单元格里面不支持数组公式（如果一定要用，先把相应的单元格取消合并，写好数组公式并运算后，再合并回来）
        2、不能够灵活的移动（常会遇到警告：不能对合并单元格作部分更改）
        3、看上去有值，实际上没值，它的值仅存在于合并区域的第一个格，其他格为空（23楼有曲线办法做到，合并单元格内每格都有值）

三国集团销售大会，漂亮的貂蝉MM做出了漂亮的表格A:D列        
老板汉献帝看了一眼，交给了做数据分析的罗贯中，说：帮我查几个数据        
        1、群雄销量总计为多少
        2、魏国最后一周销量各为多少
        3、蜀国共有多少个员工
        4、吴国小乔的销量有多少
罗贯中拿到表格暗暗叫苦：        
        天杀的貂蝉，合并单元格是做表的大忌，你只管漂亮，不管他人方便不方便！
突然灵机一动，辅助列神器不用白不用！

对合并单元格项目求和.zip (35.98 KB)

---

如果考虑使用一个公式，无辅助列解决合并单元格问题，请移步以下帖子：
对于合并单元格对应值的提取
http://club.excelhome.net/thread-969518-1-1.html

cleverzhzhf

这里的公式都极其普通：
1、=IF(A2="",E1,A2)；=IF(B2="",F1,B2)
因为合并单元格，只有第一个位置有值，其他地方均为空，所以先判断本行A列的格是否为空：
    1）如果为空，便等于自身上一单元格的值
    2）如果不为空，说明是又一个新的合并单元格的起始，便等于此合并单元格的值

2、=SUMIF(E:E,"群雄",D:D)；=SUMIF(F:F,"小乔",D:D)；=SUMIFS(D:D,E:E,"魏国",C:C,"第四周")
这三个是基础的Sumif和Sumifs函数的使用，可参考知识树相关板块：
    http://club.excelhome.net/tree/index-77.html
    http://club.excelhome.net/tree/index-227.html

3、=COUNTIFS(E:E,"蜀国",B:B,">a")
对于Countifs的基础使用，可参考知识树相关板块：
http://club.excelhome.net/tree/index-95.html
其中，着重说明">a"的用法。
    文字是可以比较大小的，按照“汉字>英文字母”，“拼音在后>拼音在前”，例如：
    1）b>a
    2）啊>z
    3）早>晚
    4）你好>你吃了吗
    这里面由于都是汉字人名，所以每个都会大于a，而空单元格是小于a的。依此来统计B列人名数。

cleverzhzhf

虽然有间接方法可以做到既美观，又能够计算方便，但在实际应用中，对于基础数据，还是倾向于像数据库那样的数据存储。
数据整齐，才方便操作，包括使用透视表等。

这个操作过程的最大区别：
    在操作前，选中罗贯中和吕布两个单元格，右下角状态栏的计数为2；
    在操作前，选中罗贯中和吕布两个单元格，右下角状态栏的计数为8；并且任意选一个单元格，取消合并，不会出现空值。（不过此操作不可逆，需要用Ctrl+Z撤销恢复）

cleverzhzhf

原帖地址：http://club.excelhome.net/thread-1161188-1-1.html

透视表可以说是Excel中的最大神器！处理数据量大，速度快，操作简便。
如果用函数来做，需要多重嵌套，多重计算，甚至可能直接电脑搞死机。

可是也有它的缺点：
1、对数据的规范性有一定的苛求：
    1）第一行上，需要每一个字段都有名称，不能有空值；（是缺点也是优点，正所谓无规矩不成方圆）
    2）每一列的数据要尽量做到格式统一，否则后续操作会有麻烦；（这就需要养成一个良好的表格习惯，不能太随心所欲）
    3）常规引用数据源方式，在增加新数据的时候不能自动扩展；（使用OFFSET+COUNTA这种最常用的动态数据引用组合）
2、出来的数据，可以说是一种制式，不能达到想要的最终效果
（没关系，让透视表完成量大恼人的计算即可，之后我可以Vlookup引用出来需要的数据，就达到效果啦。）

又一年的三国销售大会，这次卖的是电器，罗贯中给老板汉献帝做了个分析报告。
这次他不再只局限于使用函数公式的辅助列，而是神奇的用上了辅助透视表！
辅助透视表.zip (114.33 KB)

数据区域：

辅助透视表：

定义动态名称的公式：

每次更新，“全部刷新”的神奇功效：

cleverzhzhf

一、定义名称data：
    =OFFSET(销售清单!$A$1,,,COUNTA(销售清单!$A:$A),COUNTA(销售清单!$A$1:$F$1))
    COUNTA：计算区域内用多少个非空单元格。
    用COUNTA数出来数据区域有多少行多少列，然后通过OFFSET的第4、5参数扩展到相应的数据区域，这样就把全部数据都包含进来了。
    有新的数据增加，COUNTA计算的数据就会相应的变化，然后OFFSET引用的数据区域也跟着变，达到动态的目的。

二、插入透视表：
    在数据区域那里，输入data。
   

三、对透视表进行操作，添加相应的字段，以及进行“排序”：
    因为我们在做分析报告，引用数据的时候，常常需要查看诸如“前三名”，“销量冠军”，“后三名”之类的；
    在一次对透视表排序后，以后每次刷新透视表，都会按照此规则进行排序计算。所以每次做，“前三名”等都会在“固定的单元格”。

四、=INDEX(辅助透视表!A:A,ROW(4:4))：
    用公式从透视表中引用相应的数据，也可以把公式更复杂一些，以增加容错性。
    根据个人喜好和水平而定，也可以直接简单的单元格引用：=辅助透视表!A4

五、美化我们引用的数据：
    这一步纯为了给老板看，大多老板还是喜欢看一些五彩斑斓的东西；
    同时可以在引用出来的数据旁增加简单的评论，使你的分析报告更加的专业，高大上。

cleverzhzhf

汇总完后，发现自己曾经还真是能写，呵呵

只希望能帮助需要之人，找到问题的解决方式。给大家提供一些新的思路。

如有不妥或遗错之处，还请大家斧正。

sw=andy

第一朵花居然是我送的.......

jyhxr

谢谢分享，辛苦!!!

xulubang

cleverzhzhf 发表于 2014-10-27 16:46
汇总完后，发现自己曾经还真是能写，呵呵

只希望能帮助需要之人，找到问题的解决方式。给大家提供一些新 ...

这是楼主原创，可以出书当教材了！！

kakalv10

谢谢分享，翟班辛苦!!!