你早该这么玩Excel 读书笔记
- 1. Excel用来分析数据,至少要有一份原始数据和对于的分类汇总数据,这两种数据在一项任务中,应该是存放在同一个Excel文档中的,在书籍中,把他们叫做源数据表和分类汇总表。用户输入源数据表,根据相关的业务需求,利用Excel自动生成分类汇总表。本书中,时刻强调,只需要做一张源数据表,汇总表是自动生成的。把数据录入和数据处理结果分开,在软件开发中,这叫做隔离变化,反正是求和嘛,我只需要给出原始数据和求和数据之间的关系,新添数据后,新的求和数据自动产生。
- 源数据表的数据完整性和连贯性,会直接影响到数据分析的过程和结果,对一张 源数据表, 进行筛选、排序、函数引用和自动汇总。
- 2. 在Excel中,连续数据区域的首行为标题行,标题行代表每列数据的属性,是筛选和排序字段的依据。
- 标题是为了让阅读该表的人知道,这是一张什么表,除此之外不具备任何功能,不要用标题占据工作表首行,要把标题写在工作表中,一个Excel默认打开一个工作簿,一个工作簿中有三个工作表,分为Sheet1,Sheet2和Sheet3.
- 3. 当在数据区域中进行行列移动时,采用如下的方法:
-
将J列和L列的数据互换
- 先选中某一行,让鼠标放在选中框边沿,呈现十字架样式,然后按住Shift键,拖动鼠标将其放在应该放置的位置,拖动完毕后,再松开Shift键。这样,就快捷的在众多行中移动数据。
4. 在接收到别人提供的关键数据时,又需要自己做进一步的处理,在处理之前,一定要实现另存一份原始表格,在副本上进行分析处理,这既能保护数据安全,也体现出对别人劳动成果的尊重。
5. 同一种属性的数据,都应该记录在同一列。明细数据必须有一条,记录一条。
6. Ctrl键有复制的功能,Enter代表单元格录入完毕,他们两个组合起来,就是将录入完毕的数据同时复制到所选定的单元格。Ctrl+Enter,可以同时往多个分散的单元格输入相同的数据。
第一步:先用Ctrl选中多个单元格,可以在一起,也可以分散
第二步:松开Ctrl,直接输入想要的内容
第三部:先按住Ctrl,在按下Enter,就可以得到想要的效果。
7. 合并单元格严重破坏了数据结构,与其合并单元格,还不如扩大单元格的大小。我之前在做Excel中,经常看着单元格太小了,就合并单元格,虽说没有数据统计的需求,但是为了专业,以后要改掉这个坏习惯。
合并及居中,在制作用于打印的表格(如招聘表、调岗申请表、签到表等),很有用处。在制作源数据表时,要禁止使用,它会破坏源数据表中的格式,不便于函数处理。
8. 数据填充,数值部分如果没有数据的话,要统一填写0值,文本部分的空白单元,需要天上统一的标识空白的文本数据,这样方便后期使用公式来统计。使用第六步的 一键批量录入功能。
9. 将中文数字转变为大写的数字,
此方法只适用于转换整数数值,小数部分无法按照中文逻辑正确转换。
10. Excel作为专业的数据处理软件,看中是数据属性,而非文字描述。属性这个特征,1就是1,2就是2,是构成信息的最小单位,例如填写物流地址,需要剥离出不同的属性,分列记录,如四川省成都市青羊区XX路XX楼XXX号,在Excel中,应该划分为更细粒度的属性,变成:四川省、成都市、青羊区、XX路XX楼,根据这四个属性,才能掌握省、市、区的配送情况。
11. 单元格切分,Excel提供两种切分方法:按分隔符号或者按照固定宽度。功能入口是 数据-->分列 功能。
按分隔符号来“切”,需要单元格内容有相同的分隔符号
按固定宽度来“切”,对单元格的文本长度有要求。
例如: ,分析此内容,符合414N,即4个中文汉字+1个数字+4个中文汉字+N个数字,可以按照固定宽度进行切分,切分的操作很简单,就不贴出来了。
既然可以切分,那也可以合并,合并用到的符号为 & , 在需要合并的单元格里面,输入 = B2&C2&D2&E2 就可以完成对应单元格的合并。
12. 做好分类汇总表
不要使用Excel自带的分类汇总表,它不能满足用户自定义的需求。在源数据表中,不需要做任何汇总动作,
Excel默认新建生产的空白工作铺,默认有三张工作表,这三张表各有各的作用的,第一张是源数据表,其他两张是配置参数和分类汇总报表。配置参数指的是后台数据,不需要经常操作,源数据时通过手动录入的业务明细数据,汇总表报是系统自动生成的。
以银行的信用卡系统为例子,客户申请信用卡时填写的资料是配置参数,一旦填写,后期改动的可能性很小。客户每次刷卡消费的明细是原始数据,月底收到的信用卡账单是汇总报表。分类三分,清楚明白。
再以电信话费系统为例子,客户申请电话卡时填写的身份信息是配置参数,客户的通话明细是源数据,话费账单是汇总报表。
做Excel,就是做一张源数据表,和”变“N张分类汇总表。
12.1 参数表里面的数据,可以供源数据表和分类汇总表调用,属于基础数据,通常为表示数据匹配关系或者某属性明细。以办公用品为例子,可以作为参数的有:各部门名称,办公用品种类和对于的单月,月份列表等。
12.2 源数据表
为了限定单元格的录入内容,结合数据有效性和定义名称进行设置,在源数据表里面用选择的方式录入指定内容。Excel中一切与数据录入相关的工作,都要在源数据表中进行。
对任何数据表格的处理,要明确三张表,首先,确定数据身份,据此判断他们分别属于哪张工作表,然后设计分类汇总表样式,最后设计数据关联,根据参数表和源数据表自动获得分类汇总结果。
13. 条件格式
条件格式是指,当单元格满足某种或者某几种条件时,显示为设定的单元格格式,其天剑可以是公式、文本、数值、
条件格式是一种格式,不能用Ctrl+C来复制,而是用格式刷进行复制
条件格式的优先级大于普通格式,当单元格满足条件时,设定的格式将覆盖原有的普通格式。
14. 为了统一管理产品分类,描述一个产品最好用代码而非文字。例如:“无缝钢管A型优质”这样的文字描述,可以转化为WFGG-A-A:WFGG代表无缝钢管,A代表A型,另一个A代表优质。这种代号叫做物料代号,是物料的唯一识别码。对于电子产品来说,它叫S/N码,
15. 利用Excel的三张表来解决问题的思路
首先,明确做这件事只需要三张工作表,其次,确定数据身份,据此判定它们分别属于哪张工作表,然后,设计分类汇总表样式,最后,设置数据关联,根据参数和源数据自动获得分类汇总结果。
以电器卖场为例子,采购部每个月需要从各地供应商处进货,需要详细记录进货明细,并定期进行分析。分析的角度有很多,比如按照进货周期分析从各地进货的数量,按供应商级别分类同类家电进货数量的比例等。先以最简单的进货总量分析,来看这张采购表该如何设计:
确定数据身份:采购资料库里面所有供应商以及所供应商品的详细信息,一般不会轻易变动,所以要依此作为参数。
源数据表中的数据,来源于每一次的采购动作,字段和记录顺序应该为:
在制作汇总表时,分类多的字段,应该尽量作为行字段,分类少的字段,才可以作为列字段,这样比较有利于在一个页面上显示更多的内容。
管理知识再多又有何用?没有数据,就什么也干不了。
16. 数据透视表
数据透视表,就是帮助我们看透数据背后的意义,洞悉管理的真相。
具体的详细操作需要结合具体实例来理解。
已经做好的数据透视表集成了原始数据,通过数据透视表得到的汇总表走到哪里,这些源数据就跟在哪里,要注意保密措施。
未经处理的通过透视表生产的汇总表,切不可以随意传播!!!
17. 日期的输入
常见的有 两种, 减号型 以“-”为分隔符号录入的日期,如2014-12-11
正斜杠型,以“/”为分隔符号录入的日期, 如2014/12/11
Ctrl+”;”: 快速输入当前日期
Ctrl+Shift+“;”:快速输入当前时间
18. 书目作者这么多年的Excel经验上,Excel中最重要的是制表规范和思路,具体技巧还在其次。这和软件开发类似,同样的问题,可以用不同的代码去编写解决方案,团队作战,要的是编码规范和注释风格,具体的高深的技巧是其次,重点写出清晰易懂、便于后期维护的代码。
设计一张Excel表的字段,其实最全面的反映了设计者的工作经验和智慧,对相关业务流程的熟悉程度决定着设计表格的匹配度。
在数据管理中,中文识别是有缺陷的,为特定数据发放身份号码是非常有必要的。
19. 快速选中含有数据的列单元格
Ctrl+Shift+上下箭头 即可
20. 表格使用者的主要任务是录入数据,他们的工作是重复、繁琐、细致的,面对大量的数据录入,再认证的人也难免会出错,长时间盯着一模一样的单元格,很容易造成用眼疲劳,影响工作品质和效率,使用者的心情。要保证录入的安全,有两层意思,一是单元格的数据录入安全,二是工作表的数据录入安全。
Excel中有一个功能,专治单元格录入不规范,这叫做数据有效性。只有当单元格内容满足预设条件时,才能录入成功,否则报错。
21. Excel快捷键使用, 调用一级菜单使用Alt,调用二级功能,选择对于字母,依次选择对于的字母就可开启对于的功能。
22. 从庞大的源数据中挑出数据,复制,粘贴,然后组成新的数据表,这一切,只要拥有vlookup就能够彻底解决。
vlookup属于查找与引用函数,它的作用是: 查找某单元格数据在源数据库中是否存在,如果存在,则返回源数据库中同行指定列的单元格内容;如果不存在,则返回N/A。
Vlookup有四个参数,分别是 用什么找?去哪里找?找到了返回第几个值?精确找还是模糊找?
使用vlookup函数来进行匹配,待匹配的字段必须在数据区域的首列,这是vlookup函数的规定。
使用方法: 在空的单元格中,输入“=vlookup”,然后按下Ctrl+A,就可以打开Vlookup的参数面板。
23. 文本大小,对于数据明细来说,10号字体比默认的12号字体更精致,文本在垂直方向居中对齐
字体,中文用宋体,英文用Arial或者Times New Roman,
突出标题行-设置标题行的单元格填充色
24 自动更正选项,Excel中的此项功能原本是用来更正常见的录入错误,比如输入soudn,Excel会自动将其更正为sound,为什么呢?因为Excel的数据库里面存放着对比文本,按照这个思路,如果我们自定义对比文本,岂不是可以用短文本来代替长文本的呢?
在里面添加自己需要的缩写,
在单元格中,输入abc按回车,就显示为替换的文本了。
建议: 替换文本最好使用三个英文字母的组合,这样,既可以有多重变化,又容易记忆,不会和正常的文本冲突。建议将替换表打印出来,便于快速输入。
在Excel中配置好后,在Word和PPT里面同样可以使用,输入abc,按下回车,就将abc替换为自己需要的字符了。
25.
工作中最常用的有三种图表。
饼图,对比份额、分布等信息,能够直观的通过扇形大小来说明比例关系,而不是强调数值本身,二维正面紧凑图就够。
柱形图,比较数值时使用,它既关注多个数值间的大小关系,也关注单个数值本身,需要注意的一点是,参加对比的数值,需要有可比性才行。简单直观就好,二维效果就足够。
折线图,关注数值的变化趋势,用来翻译多个时期的数据变化,在数据小于6个的时候,用柱形图的表现力会更好,数值越多,折线图越能体现出变化的规律。
26. 如果需要将Excel中的图标粘贴到Word中去,同时保持两个图表的同步改变,如果执行选择性粘贴中的
这样,在Excel中修改数据后,改变会立刻同步到Word中去,粘贴到PPT中的也一样,这里的话,存在安全隐患。
关闭掉源Excel文档,右键点击图标,选中编辑数据,会立刻打开Excel源文件(如果源文件没有移动的话)
这里面,就要选择粘贴为链接。千万不要选择粘贴为图表对象。
27. 我们在工作中难免要和其他部门、其他公司的人打交道,也许是报表传递,也许是数据分享,珍惜每一次与他人沟通的机会,通过它来展现自己良好的职业素养和审美品位。人与人之间的印象是一点一滴积累起来的,尤其是好的印象,需要长时间的积累才能形成,但得来不易的好形象,却可以在一瞬间崩塌。
让优秀成为一种习惯。在工作中,用心的人,都有对工作深刻理解和堵到感悟,这些经验,与其口口相传,不如记录在案。在整理、记录的同时,更好的认识自己。
分享是一种美德,也是人与人之间最好的沟通方式。如果失去了分享的精神,连龙门阵都没的摆,与人聊天的过程本身,就是分享信息的过程。
职场感悟:以诚待人,待人真诚勿算计,真诚的人往往能散发出很强的亲和力,带给他人信任感,并由此获得更多的机会,一个人是否真诚和智力水平无关,也就不所谓你必须足够聪明,才能让别人感觉到你的真诚。
做任何事情,都要有反馈,其中,工作进度的反馈尤为重要。反馈是指,被安排工作者向工作安排者主动汇报情况,在职场中,主动是很好的个人素质,主动的人,永远占尽先机,而被动的人永远错事良机。
一件事情吩咐下来后,我们应该主动向老板汇报进度,汇报的内容无非两点,一是能够按原计划完成,如果不能,是由什么原因造成的,预计推迟多久,二是,遇到了什么困难,需要什么帮助。至于已经解决的问题,或者取得的成就,在汇报进度的时候,不用交代。主动汇报进度,给老板方便,就是给自己方便。
除了及时汇报工作进度,还要注意按时完成工作任务,虽说工作有不可控因素,但还是要尽量按时完成,这能体现出你的工作能力和计划性。
企业管理知识懂的再多又有何用,没有数据,拿什么来决策?