《你早该这么玩Excel》读书笔记

想要用Excel表制作家庭记账表，就把这本书复习了一遍。表格建的很粗陋而且还没用起来，不好意思分享出来。这本书挺有意思的，角度很新颖，提出“三表”的概念，把Excel当作一个系统来看待，源数据表的好多概念和数据库的使用暗合；里边提到的小技巧都很实用，都是经常用得到的。
这篇文章只有干巴巴的文字叙述，一点都不生动可爱，感兴趣的可以看书，书里边图文并茂，而且举例都很有意思。

1. 源数据表

• 一维表格、首行首列默认是标题行，及数据的字段名称，标题可写在工作簿或工作表。

• 字段顺序（及数据录入顺序）要与工作顺序保持一致。
  tips： 插入列，选中一列数据，按住shift键，将鼠标放在数据列一侧，拖动到插入位置，完成。

• 数据要有连续性，不能人为割裂开，因为Excel根据行列的连续性判断数据的关联性
  tips： 删除空白列，Excel表格只有筛选空白行的功能，所以将表格内容全选，选择性复制，勾选转置，将列变为行，任意列筛选空行，删除，在转置回去就可以了

• 只对数据进行录入，不进行统计
  Excel工作步骤：1.数据录入；2.数据处理；3.数据分析。
  对应的操作步骤：1.输入数据；2.整理数据（函数等技巧）；3.数据分类汇总
  对应的工作表：1.源数据表；2.源数据表或其他新建工作表；3.分类汇总表

• 同一种属性数据记录一列，如果明细数据有一个属性不同，就应该录入为两条数据
  tips： 批量录入数据，所用快捷键“Ctrl + Enter”，首先按住“Ctrl”键选中多个单元格，在最后一个单元格录入数据，录入完毕，按组合键，完成

• 不要用“合并居中”，会打乱Excel的内在逻辑
  修正方法很有意思，技巧性很强
  首先全部选中，点击“合并居中”按钮，拆分合并的单元格，在按“F5”键，打开定位功能，空位条件为空值，在单元格输入公式“=上一个单元格”（如光标所在单元格为B4，就在这个单元格录入=B3），按下“Ctrl + Enter”键，这样录入的数据为公式，复制选择性粘贴为数值，完成。

• 数据不能有缺失，不能有空格，没有数据可以用“0”表示；而且设计表格的时候要仔细分析所需的字段，属性，不能有缺失。
  tips： 数字变换为中文大写形式，选中数字单元格，设置单元格格式（Ctrl + 1），数字标签下的“特殊”，“中午大写数字”，完成，但是单元格的实质还是阿拉伯数字。

• 属性要单一，不要记录复合属性
  tips：　＂数据－分列＂，可以将一列数据分成几列；合并数据用　“＆”　运算符，对多个单元格进行合并。

源数据表其实就是记录最原始的数据，它的设计特别像数据库表的设计，要有主键，属性字段，属性字段可以进行逻辑运算等等

2. 三表概念

从系统的角度对Excel进行解读

• 参数表
  表示数据匹配关系或某属性明细等不会经常变更的数据。

• 源数据表
  数据录入的表格，设计表格时，物品最好用代码来表示，每一个代码唯一标识该物品，相当于数据库里的主键。
  正确源数据表条件：1.一维数据；2.一个标题行；3.字类分类清晰；4.数据属性完整；5.数据连续；6.无合并单元格；7.无合计行；8.无分隔行/列；9.数据区域中空白单元格；10.单元格内容禁用短语或句子。

• 分类汇总表
  根据函数关联或其他方法，从源数据表和参数表自动生成的分类汇总结果，满足Excel表的使用目的
  tips：　条件格式，选中数据区域，开始选项卡，样式区域，条件格式，设置条件，设置格式，可以试一下将数值小于３０的数据筛选出来。
  tips：　写有公式的单元格过多，导致没修改一次数据，都要等待计算完毕；可以设置Ｅｘｃｅｌ的计算方式为手动，而非自动（选项－公式－手动计算），这样等我们把所有需要修改的数据修改完毕后，按“Ｆ９”键，让表格进行计算

3. 数据透视表

在源数据准备完成的基础上，对其进行各种分类汇总，数据透视表可轻松实现该功能。

tips： 快捷键 Alt+D-->P-->F

• 隐藏汇总项
  选中一个汇总项，右击，字段设置，分类汇总，无，即可
  若取消隐藏，可将分类汇总设置成自动。

• 拆分汇总表，分页显示
  将需要分页显示的字段拖入页字段，在“选项”选项卡中，“数据表名称”栏，点击“选项”后边的小三角，点击“显示报表筛选页”就行啦。

  
  image.png

如果分页打印的话，可在字段设置中勾选“每项后面插入分页符”就行了。

image.png

• 汇总报表设计
  分类多的字段一般作为行字段，分类小的作为列字段，即Excel表“站起来”而不是“躺着”。

• 汇总时可按月汇总，按天汇总意义不大
  可以用“month”函数提取日期的月份，也可在源数据中添加字段，但是都比较麻烦，后边有“组合”的介绍，这个方法最好用了。

• 不要超过两个列字段

• 汇总跟着文字走
  例子，不同省份，不同会计制度类别，不同行业在不同营业状态下的全年营业收入总和。
  “在”后边字段为列字段
  “的”后边是汇总项
  一级行字段：省份
  二级行字段：会计制度类别
  三级行字段：行业
  列字段:营业状态
  汇总方式：求和

• 字段设计主次要分明
  一级行字段不同关注重点会不同，如果一级行字段是省份，则关注的可能是不同省份的差异，而如果一级行字段是会计制度，则关注重点可能就变为了制度差异。

• 日期组合
  源数据表只要时间格式正确就可以变出各种事件组合如年、季度、月、日、年/月、月/日（选择是可多选）等。
  方法：在任意一个时间单元格，右击，组合，选择需要的组合即可。
  常见错误录入方式：
  “点”型--2011.1.1
  空格型--2011 1 1
  常见正确录入方式：
  “减号”型--2011-1-1
  “反斜杠”型--2011/1/1
  检验日期正确的方法：将单元格设置成常规，如果变为数字就是正确的，如果不变就是错误的。
  输入当前日期“Ctrl +”，输入当前时间“Ctrl + Shift + ”

• 汇总表数据明细
  如需查看汇总数的明细，只需双击汇总数就可以得到想要的结果，Excel会新建一个工作表，逐项罗列相关数据。
  因此，数据透视表得到的汇总表，传播时需谨慎，可以复制汇总表，选择性粘贴为数值，再发送。
  明细数据不是源数据，如需对数据更新，一定要在源数据中进行，否则更新后，汇总表无法同步。

• 数据更新
  源数据更新后，只需选中任意单元格，点击更新，即可，如果有多张数据透视表，需逐张更新，若要实现一次全部更新，需要VBA二次开发。

• 预约源数据
  添加数据后，源数据区域扩大，但是透视表选定区域并不会自动扩大，可用用预约源数据的方法解决，在建立数据透视表的第二步，设置即可，扩大选定范围。

• 源数据表需要备份
  备份频率视更新情况，可一个月一次，可一年一次。

• 重复数据删除
  方法有三：函数法（countif），版本法（2007及以上版本自带此功能），透视法。
  透视法：将要筛选的字段，拖入行字段和数据区域，对数据区域进行计数统计。

• 并列显示汇总数
  对数据进行多角度分析，需要汇总不同方面的数据一同显示，这需要汇总项的并列显示。
  将汇总项拖入数据区域，重复一次这个操作，可以看到，汇总项是行显示的模式展现的，并不够直观；选中“数据”单元格，拖动到“汇总”单元格即可，汇总数并列显示了，然后继续将统计数据拖动到数据区域，可以看到，以后拖进去的数据自动并列显示了可以。

• 复制汇总表
  对于源数据可能需要不同方面分析，因此可能一张汇总表不能够完全满足我们的需求，可以复制汇总表，复制的汇总表保留原汇总表全部的功能，这样可以在不同表格中统计不同的东西，而不用重新建立数据透视表了。

4.“天下第一表”

tips： 选中连续区域的快捷键 “Ctrl + Shift + 向下方向键”，

• 保证表格的安全
  单元格数据录入安全：单元格数据有效性设置，快捷键“Alt+D”再按“L”，可保证单元格数据录入的正确性。
  工作表数据安全：数据表分允许操作部分，和不允许操作部分，设置步骤如下
  选中允许录入的单元格区域，设置单元格格式，取消勾选保护标签里的“锁定”。
  调用保护工作表功能，取消勾选“选定锁定单元格”。完成！！

• 利用参数对数据进行匹配
  主要是VLOOKUP的使用，该函数规定，匹配信息的主键必须位于第一列。
  该函数有四个参数：
  用什么找————设置匹配信息的主键，相对引用
  去哪里找————‘工作表123’！$A：$D,表示去工作表123中A到D列中去匹配。$表示相对引用。
  返回第几个值————2表示返回第二个值
  精确还是模糊找————False或0为精确查找，True或1为模糊查找
  书中例子：
  =IF（LEN（$E2）=0，“”，VLOOKUP（$E2，'参数表（供应商信息）'！$A：$D，COLUMN（B1），0））
  翻译：当E2单元格无空时，返回空，否则返回VLOOKUP函数结果。
  注释：$为相对引用，COLUMN为单元格所在列号

• 表格美化
  文本对齐——垂直方向居中对齐，同列数据水平方向对齐方式一致。
  文本大小——对于数据明细，10号字比12号字更精致。
  字体——中文宋体，英文Arial或Times New Roman。
  单元格边框——同类数据采用相同边框，禁用大面积粗边框或虚线边框。
  色彩——不宜超过三种。
  简化数据区域——待录入数据区域最好不填充颜色。
  数据区域——手工录入、复制粘贴、公式不同区域用不同颜色区分。
  字体大小——录入和经常查看的单元格字体稍大，公式生产的明细数据字体稍小区分关注重点。
  边框——用虚线边框弱化明细数据和非重点数据，用粗线边框分隔录入方式不同的数据区域。
  工作表——以不同的工作表标签颜色区分汇总表、源数据表、参数表。

• 输入长文本技巧
  利用“自动更正选项”，将长文本的代号更正成文本本身。代号最好用三个英文字母组合，可以将常用长文本设置好的替换列表打印出来，随时查看。
  设置好以后，word和ppt也可以使用。
  tips： 定位功能（F5），可以定位表格中设置定位条件的所有单元格，如选中所有空格。

5.图表

• 观点：Excel中的图表用来说明数据的意义，不用过多的美化，只要最基本的就行，直观、准确。PPT中说明观点的图表才需要考虑过多的美化问题。

• 分析数据是用来指导行动的，如果只是单纯的整理数据，那么数据是没有用处的，以指导行动为目标来对数据进行必要的分析。

tips： 当图表数据区域扩大时，可以将扩大的数据选中，拖放到图表中即可；也可以将数据区域用鼠标按住锚点扩大即可：）

tips： 对于图形，我们可以通过鼠标 拖拉来改变图形的形状，数据跟着图形而变动，这样比修改数据要来的直观

• 图表类型
  作者推荐三种图表：
  饼状图——对比份额，分布
  柱状图——比较数值
  带点折线图——表示趋势
  图形都推荐使用二维最简单直观的，慎用3D或其他复杂图形。

• 图表美化
  参考标准：干净、原配（默认图表类型）、大小（图大字小）、繁简（简洁为王）
  如需美化只需，双击需要美化的地方，设置选项就会出现。

tips： Excel图表粘贴到Word和PPT中，粘贴时可选择“选择性粘贴，如果勾选“粘贴链接”，Excel表格更新时，Word的图表也会更新。但是需要注意，直接“Ctrl+V”粘贴到word中时很安全，只是张图片，但是粘贴到PPT中时将会把所有的源数据粘贴到PPT中，无意中泄露了很多商业秘密，（粘贴为链接时，不在本机，无法打开源数据；粘贴为图表对象，就会附带源数据，即便不在本机也可以打开）

• 数据透视图
  数据透视图实现和数据透视表，源数据同步更新，并且和数据透视表一样想怎么拖拽设计就怎么拖拽设计。

6. 职场经验

• 赢得同事认可
  人的形象时逐渐积累的过程，要认真对待每一份工作，珍惜每一次沟通机会。
  善于总结，并乐于分享，这也是展现自己工作的机会。
  保存好报表，分类，备份。
  真诚

• 赢得老板的认可
  比吩咐的多做一点；
  汇报进度并按时完成；
  要主动汇报情况，明确完成时间时，如有异议，要当场提出。未提出，就要克服困难，尽力按时完成。
  邮件的重要性：
  不要有错别字，
  如果有附件，可以先添加附件，以免最后忘了添加。
  如果忘了发附件，要补发邮件，内容或标题区别于第一封，并电话确认。
  如果附件比较重要，可以在邮件正文中对邮件进行总结描述。
  汇报时，让老板做多选题，提出多个解决方案，并给出建议。

完