《你早该这么玩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: 条件格式,选中数据区域,开始选项卡,样式区域,条件格式,设置条件,设置格式,可以试一下将数值小于30的数据筛选出来。
    tips: 写有公式的单元格过多,导致没修改一次数据,都要等待计算完毕;可以设置Excel的计算方式为手动,而非自动(选项-公式-手动计算),这样等我们把所有需要修改的数据修改完毕后,按“F9”键,让表格进行计算

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. 职场经验

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

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

推荐阅读更多精彩内容