1、参数表
参数可以等同于系统的配置参数,供源数据表和分类汇总表调用
2、源数据表应该满足以下条件
* 一维数据
* 一个标题行
* 字段分类清晰
同一属性的数据记录在一列中,不需要摊派的多列
如事假、年假、病假都属于请假类别,拥有相同的属性,所以应该记录在请假类别一列中,而不是使用对勾分三列做记录
如果一个员工请两种假,对于源数据表的明细数据,只要有任何一个属性不同,都应该分别记录。
* 数据属性完整
* 数据连续
* 无合并单元格
* 无合计行
* 无分隔列/行
* 数据区域中无空白单元格
* 单元格内容禁用短语或句子
源数据表的元素
可以:日期、数值、单词、公式、文字描述(仅限备注列)
不可以:符号、短语、句子、中文数值
不推荐:图形、批注
3、数据透视表
“数据”-“数据透视表”
隐藏汇总项
如果不喜欢汇总项,只要选中其中一个汇总项,右键-“隐藏”。对于同一字段,一次操作可以隐藏该字段所有汇总项
如果要取消行字段隐藏的汇总项,只要选中行字段所在区域的任意单元格,右键-“字段设置”,将分类汇总选项从“无”-“自动”
拆分源数据
将需要分页的“月份”拖入页字段,按照源数据表的顺序,将各个字段一次拖入行字段,并隐藏汇总项
调出数据透视表工具栏,点击“分页显示”,选中“月份”并确定,表格被自动拆分了
按照不同时间范围进行统计
选中汇总表的日期字段数据区域,右键-“组及显示明细数据”-“组合”
设置期望的汇总时间范围(可以多选)
查看数据透视表的明细
想看什么数据明细,双击它,会新建一张表,逐项罗列出所有相关数据
去除重复数据
将需要去重的字段拖入行字段
再将该字段拖入数据项区域
生成的汇总表不仅完成了去重,还显示每个项目被重复的次数
复制透视表
数据透视表是允许复制的,粘贴后的表格具备透视表的所有功能,可以单独进行设置,为一源多表提供最大的便捷
关联数据
由于汇总表是根据源数据表自动时的,当源数据发生变化,只要点击数据透视表工具栏的“刷新数据”按钮,数据就会自动刷新
预约数据源
当添加新数据时,意味着数据源扩大了,所以在设置数据透视表时,可以将选定区域放大。
4、单元格操作
行列移动
选中待调整列,将光标移至该列左右任意一侧边缘,呈四向箭头形状
按住shift不放,拖动鼠标至待插入位置(B:B表示插入B列)
选中列非空单元格
选中B1:D1
同时按住ctrl+shift,再按方向键下,就可以选中B:D列所有非空单元格
拆分已合并的单元格
全选数据
点击“合并及居中”按钮,拆分合并单元格
使用“定位”功能,选择“定位条件”,选中“空值”为定位条件
在空单元格里输入同列上一个单元格的坐标
Ctrl+Enter
多个单元格录入相同的数据
选定多个单元格
直接敲键盘输入内容
Ctrl+Enter
条件格式
当单元格满足某种或某几种条件时,显示为设定的单元格格式。条件可以是公式、文本、数值。
选中数据,调用“格式”-“条件格式”
设置条件
设定显示格式
注意:条件格式是一种格式,要用格式刷复制,不能ctrl+c;条件格式的优先级大于普通格式,当单元格满足条件时,设定的格式将覆盖原有的普通格式
5、数据操作
分开单元格内容
选择待分开内容的单元格(只能是同列)
使用“数据”-“分列”功能
两种分开方法:按分割符号,需要单元格内容有相同的分割符号;按固定宽度,对单元格中的文本长度有要求
如果是按照固定宽度,可以单击鼠标左键设置“分隔”点,双击鼠标可删除分隔箭头
使用“&”可以拼接多个单元格内容
日期的正确录入方式
“减号”型,以“-”为分隔符号的日期,如2013-1-1
“正斜杠”型,以“/”为分隔符的日期,如2013/1/1
按住ctrl和;可以输入当前日期
按住ctrl和shift和;可以输入当前时间
数据有效性
“数据”-“有效性”
控制日期录入时,“允许”中选择“日期”
控制特定内容的录入并需要制作下拉列表时,允许”中选择“序列”
如果列表短,可以在“来源”直接输入内容,文本与文本使用英文半角逗号分隔,如果列表很长,可直接引用数据区域
录入长文本
“工具”-“自动更正玄虚昂,在"替换"栏输入替换的值和替换为的值,以后只需输入替换的值,就会自动变成替换为的值
6、函数
调用函数
在单元格内直接输入=Vlookup,然后按Ctrl+A,就能打开函数参数面板,如果后面多打了括号,Ctrl+A就失效了
Vlookup
作用是:查找某单元格数据在源数据库中是否存在,如存在,则返回源数据库中同行指定列的单元格内容;如不存在,则返回#N/A。
首先要有一个待查找数据库,该数据库的首列必须为待匹配字段,这是这个函数的规定
用什么找、去哪里找、返回第几个值,精确还是模糊找
例如:=Vlookup($E2,'参数表'!$A:$D,Column(B1),0)
因为公式要向右复制,所以E2变成$E2
A:D变成$A:$D,也是为了保证向右复制依然引用的是A:D列
第三个参数应该从2依次递增,所以B1的列返回2,D1的列返回4
7、好习惯
备份文档
数据源按照日期命名,方便以后查找。根据报表性质,一个月一次或者半年、一年一次备份。
保护工作表
允许录入的单元格区域,设置单元格格式,取消够勾选保护标签中的“锁定”
“工具”-“保护”-“保护工作表”,在“允许此工作表的所有用户进行”里,勾选“选定未锁定的单元格”
《你早该这么玩Excel》读书笔记
《你早该这么玩Excel》热门书评
-
不用挥鞭,自奋蹄
181有用 6无用 刘小萌 2012-03-16
这段时间读了一系列的职场畅销书:吴昊的《你早该这么玩EXCEL》、张鄂勇的《思维导图三招十八式》、刘万祥的excelpro的《EXCEL图表之道》、Liz的《别告诉我你懂ppt》&《不懂项目管理还敢拼职场》和秋叶等一帮网上集合体的《说服力-让你的ppt会说话》。 如果按照我的个人喜...
-
一些小技巧备忘
27有用 1无用 闷骚的刘大哥 2011-12-06
2011.11.281、Excel提供了两种记录标题的方式:命名工作簿或者命名工作表。身为一张源数据表,不要用标题占用工作表首行。2、快速移动行/列/单元格:选中,鼠标移动到边框,shift+拖动,释放左键,释放shift。若不按shift,则为替换。3、用空白行/列分隔,Ctrl+A,可局部全选(...
-
道术结合,重道轻术
27有用 0无用 raven1988 2012-02-02
我看励志书喜欢对比着看,国内的和国外的。那是因为我发现国内的励志书主要讲的是道理,各种各样的道理,有的没的,对的错的,总之用一大堆道理让你在书店蹭书时翻的不忍离手,然后痛痛快快地在当当卓越上下单。国外的励志书因为不是用我们的母语写作,再加上有个麻烦的有关部门,在国内的励志图书市场上路子走得有些崎岖了...
-
侧重实际工作(非分析工作)应用,而非excel技巧的书
19有用 1无用 fitree 2011-11-14
一本对我无用的书,作者只着重讲了数据透视表而以,是给非分析专业人士看的书。当然作者设计表的思路值得借鉴。1. 三表概念: a. 源数据表----期间比较固定的数据,可通过vlookup从参数表中关联,对于需...
-
《你早该这么玩Excel》笔记
16有用 1无用 waiting 2011-11-15
源数据表只应该有一张,而且应该是一维数据格式。坚持一项工作一张表格的原则。不要用标题占用工作表首行。我们在Excel中的动作,尤其是数据录入的动作,都必须与工作顺序保持一致。切忌在源数据表(尤其是别人的)中做试验,应首先另存一份临时文件。Excel默认首行为标题行。凡是同一种属性的数据,都应该记录在...
书名: 你早该这么玩Excel
作者: 伍昊
出版社: 北京大学出版社
出版年: 2011-7-15
页数: 252
定价: 39.80元
装帧: 平装
丛书: 你早该这么玩Excel
ISBN: 9787301191309