Excel制表方法.docx
- 文档编号:9889847
- 上传时间:2023-05-21
- 格式:DOCX
- 页数:31
- 大小:923.09KB
Excel制表方法.docx
《Excel制表方法.docx》由会员分享,可在线阅读,更多相关《Excel制表方法.docx(31页珍藏版)》请在冰点文库上搜索。
Excel制表方法
返回绝对值的公式为=abs()
做管理费时,从序时账筛选出所有管理费用,单独开一个工作表标签,贴进去,然后有必要的话把这个标签移动或复制到“管理费用通用表格”中去。
内退人员的费用计入职工福利费,通过应付职工薪酬过账,离退休人员的费用直接计入福利费下的离退休人员统筹外费用,不通过应付职工薪酬过账,
Xlw不保存数据,只是一个快捷方式
按住shift拖拽某列可以与某列替换
ctrl加冒号键,会输入当天的日期
实心黑加号拖拽,按住ctrl键拖拽使拖拽出来的内容发生变化:
本来拖拽出来的是一样的内容(即复制了第一个单元格),按住ctrl键会拖拽出序列来;本来拖拽出来的是一个序列,按住ctrl键会变成复制第一个单元格。
实心黑加号拖拽,不是按住左键而是按住右键拖拽,会有很多种填充方法供你选择
在“文件”——“选项”——“高级”下头儿“编辑自定义列表”中,可以自定义经常拖拽的内容,比如张三李四王五赵六。
同样的位置下面还有lotus123兼容性,点选“转换lotus123”后,可不输入等号的情况下直接输入公式得到公式的结果
双击“格式刷”,可以按照选中的格式刷无数次
“设置单元格格式”——“数字"——"分类"——"自定义":
y表示显示年,yyyy表示年份用四位数表示;m表示显示月,mm表示用两位数显示月,mmm表示用英文简写显示月,mmmm表示用英文全称显示月;d表示显示日,dd表示用两位数显示日,ddd表示用英文简写显示日,dddd表示用英文全称显示日。
数字格式分四种情况:
正值;负值;零;文本,四种情况用分号“;”分开。
日期的本质也是数字,是从1900年1月1日开始计算的,比如3就表示1900年1月3日
数字格式自定义为aaa,是用一、二、三、四、五、六、日来显示,定义为aaaa是用星期一、星期二、星期三、星期四、星期五、星期六、星期天来显示。
如果想让他显示周几,输入周”aaa”即可。
Excel只能保证15位的精确度,如果超过15位,比如说身份证号码18位,超过的部分会忽略为0。
左上角有绿箭头的数字为文本格式,不可进行运算,而且通过修改单元格格式也不能把它转换为数字。
应当选中要修改的文本格式数字,左上角会出现一个黄色的感叹号,点击之,可以将其改成数字格
或者在旁边随便什么单元格里打上一个数字1,然后复制这个单元格,在要粘贴到选中的文本格式数字时,选择“选择性粘贴”,会打开一个对话框,选择“乘”,Excel就会强制把不能运算的文本格式数字转为数字格式的数字。
从文档中复制过来的数据,会自动分行,但是不会自动分列,即都挤到第一列里去了。
可选中复制过来的数据,点击“数据”选项卡,找到工具“分列”,可以按照固定的分隔符号(比如逗号或者分号)或者固定的宽度进行分列,方便数据处理。
但是Excel里默认的标点符号都是英文模式下的标点符号,所以如果想以中文的逗号为分隔符,就用“其他”,可自定义分隔符。
而且在点击“完成”前,如果点击“下一步”,会有机会对分隔后的两列单元格的格式进行修改。
“分列”工具是一个很强的工具。
正常情况下,文本格式是不能转换成其他格式的,就算把单元格格式改了,里面的值也该是文本形态,不方便分析的;但是“分列”这个工具却可以,因为如果把“下一步”点到最后,会发现它可以改变分出来的(或者根本没分,还是保持在一列里)单元格格式,连同里面值的格式,从而把文本形态的值变成数据格式的值。
当然也可以不点“下一步”,直接点“完成”,得到的就是默认格式,还是可以把文本形态的值变成数据形态。
Ctrl+f是查找,其中也可以替换。
但是如果遇到这种情况:
有一些单元格写的是管理费,另一些写的是管理费用,现在要统一成管理费用,怎么办?
这时可以点开选项,勾选“单元格匹配”,在将所有的管理费替换成管理费用就可以了。
因为写着管理费用的单元格是不符合搜索条件的。
在“查找和替换”单元格中,可以查找一定格式的单元格来替换成其他格式或内容的单元格,“格式”按钮后面有个下拉箭头,拉出来的第二个就是从现有的表格里提取格式。
在“替换”的时候,输入星号*用来表示通配符,比如替换“管理费用*”为“我了个擦”,那所以前面带“管理费用”的,不管“管理费用”后面还跟了什么字,统统都会变成“我了个擦”。
如果说要把“管理费用”后面只跟了三个字的找出来,比如管理费用—办公费,管理费用—差旅费,把这样的都找出来,应该怎么办呢?
替换“管理费用—?
?
?
”+勾选单元格匹配,替换为“我了个擦”,那么所有管理费用后面跟三个字的就都替换为“我了个擦”了。
也就是说*是表示一切东西的通配符,而?
则是表示一个字的通配符。
其中?
一定要是英文格式下的?
如果有需要查找所有带*的单元格怎么办?
需要在*前面加~,否则Excel会默认*为通配符,找到些乱七八糟的东西,而加上~后,*就失去了通配符的功能,?
也是同理。
选中某个区域的单元格,然后在名称框里输入一个随便起的名字,就可以为这个区域的单元格命名,下次再在名称框里面输入名字,就可以直接跳到这个区域。
添加备注时,office2010是可以改变备注框的形状的,但是要自己先设置一下,第一次设置好了,后面就不用管了。
先随便插入一个“形状”,点击这个图形,会发现多了“绘图工具”这个选项卡,在这个选项卡中找到“编辑形状”按钮,点击下拉箭头,看到第一个选项就是“更改形状”,在“更改形状”这个选项上点击右键,再点击“添加到快捷访问工具栏”,会发现Excel窗口的最上头多了一个“更改形状”按钮。
这时候再点击备注的框框就可以通过上面的“更改形状”来选择想要的批注框的形状了。
如何在批注中插入图片呢?
选中要插图片的批注,出现十字箭头的时候点击右键,选择“设置批注格式”,点选其中的颜色与线条,可以看到可以修改填充色,点击“颜色”下拉箭头,选中“填充效果”,就可以看到有“渐变”“纹理”“图案”“图片”几个选项卡了,选择“图片”,就可以插入图片了。
如果想将所有的批注同时显示出来,可以点击“审阅”选项卡,里面有“显示所有批注”这个按钮,按一下就可以了。
“定位”的意思就是选中符号条件的单元格。
按Ctrl+G,选择“定位条件”,点选“批注”,就可以同时选择所有含有批注的单元格,然后就任人宰割咯。
Ctrl+Z可以撤销刚刚的操作。
选中一个区域多个单元格,输入1,然后按Ctrl+回车,就可以在所有选中的单元格中输入1.
工作中获得的表格往往有合并的单元格,Excel只会认为第一个单元格里有东西,其他都是空的,所以不好编辑。
这时应当将所有合并的单元格拆开,然后Ctrl+G,点击"定位条件"选中“空值”,这时就选中了所有空着的空格,再输入=,再按键盘上的方向键“上”,再Ctrl+回车,可以看到所有单元格都被填充了,而且是它上面那个格子里的值。
“定位”为“对象”的话,会选中所有的图片。
如果有一张成绩表,有数学、语文、英语三门的成绩,其中数学是最重要的,语文次之,英语再次,按照这个原则给一个班的同学排名,也就是说先看数学成绩,高的排前面,数学成绩一样的话再看语文成绩,语文成绩再一样再看英语成绩,应该怎么办呢?
首先选中要排序的数据所在的区域,点击“开始”选项卡中的“排序和筛选“,点击”自定义排序“,出现一个对话框,里面有”主要关键字“下拉箭头,选择”数学“,按照“数值”排序,次序为“降序”,再点击“添加条件”,出现一行“次要关键字”,选择“语文”,按照“数值”排序,次序为“降序”,然后再点“添加条件”,又出现一行“次要关键字”,选择“英语”然后……就可以了。
还有一种操作也可以达到相同的效果:
先从最次要的一列开始,点选英语那一列的任意单元格,点击“排序和筛选”,点击“降序”,然后点选语文那一列的任意单元格,点击“排序和筛选”,点击“降序”,然后点选数学那一列的任意单元格,点击“排序和筛选”,点击“降序”,发现这样做的结果和自定义是一样的,因为最新的排序会刷新掉原来的排序,但是如果最新的排序和原来的排序不冲突,原来的排序就得以保留下来,比如三个人数学都是90分,那么点选数学那一列的排序并不会对他们三个本来的排序做改变,所以他们实际上还是按照语文的分值进行排序的。
如果想按照“红”“黄”“绿”的颜色对单元格进行排序,可以用“自定义排序”,很简单。
现在人力资源部要给大家发工资啦,大家的名单和所在部门都在表格中,在“部门”那一列输入为“审计一部”“审计二部”……“审计七部”,现在要按照1234567的顺序把人员排列起来,怎么办?
先选中要排序的大区域,点击“排序和筛选”——“自定义排序”——选择主要关键字“部门”——“排序依据”还是选择“数值”——“次序”选择“自定义序列”,然后依次输入“审计一部”回车“审计二部”回车……“审计七部”,点击“添加”再点“确定”——确定,就完成任务了。
制作工资条:
表头有“姓名”“部门”“学历”“出勤天数”“加班天数”等等等等,下面是一个个人以及他们的工资,工资单就是每个人的数据上面一行都有表头,到时候打印出来,然后把它撕开,然后他们过来签名后领走。
怎么做到每一行信息上面都有表头呢?
首先复制表头,看还缺多少表头,就在数据区域的下面复制几行,然后在数据最后一列后面的那一列输入编号,表头后面输入0,第一个人后面输入1,依次类推,然后把复制出来的表头后面输入1.5、2.5、3.5……,然后点选这一列的任意一个单元格,按升序排列就可以了。
比如说有一个非常多行的表格要打印,打印出来好几页,但是只有第一页有表头,其他的都没有,很不方便,怎么办?
点击页面设置选项卡,里面有个“打印标题”选项,点击之,可以看到“打印标题”——“顶端标题行”,就可以设置每一页都打印的表头了。
“筛选”是Excel中bug最多的工具了。
比如我要把筛选出来的数据贴到另外一张表里,结果复制粘贴之后,发现没有被筛选出来的数据也被贴过来了,怎么办?
方法是:
首先还是选中要复制的区域,然后点Ctrl+G——"定位条件"——“可见单元格”,复制、粘贴,就可以了
通过表格里的下拉箭头里的“数字筛选”,可以筛出>某值<某值的数来。
果这一列全是文字,还可以进行“文本筛选”,可选择“开头是”或者“结尾是”。
快捷键:
先点选某个数据区域内的某个单元格,按住Ctrl键,再按“左”,就会自动选中该单元格那一行的该数据区域最左边的单元格。
如果按住Shift键,再按“左”,就会选中该单元格以及它左边的单元格。
如果选中某数据区域最左上角的单元格,同时按住Ctrl和Shift,再按“右”再按“下”,即可选中整个数据区域。
除了“筛选”外,还有“高级筛选”,在“数据”选项卡里,“筛选”的旁边,有个“高级”。
例如,某单位销售产品,有个销售记录表,其中客户那一列里有客户A、客户B、客户C、等等,但是这一列是按照时间顺序排列的,客户A、B、C等都出现了多次,现在要获取客户的列表(不能重复),怎么办?
打开“高级筛选”,会看到有“在原有区域显示筛选结果”和“将筛选结果复制到其他位置”,前一个和筛选一样,是把不被筛选的单元格隐藏,而后一个是把筛选的结果另外贴到别的地方去。
现在我们选择“将筛选结果复制到其他位置”。
“列表区域”就选择客户那一列,“条件区域”空着,“复制到”随便选择一个单元格,最重要的是在“选择不重复的记录”前面打勾。
点确定就好了。
“条件区域”的应用可以非常炫目。
比如“部门”“工资”这两列,要求从这两列中挑选审计六部工资在8000块钱以上的人,那么在空白处中输入
部门
工资
审计六部
>8000
“列表区域”选择整个数据区域,“条件区域”选择刚刚填的四个格子,点击确定,可以得到结果。
“条件区域”中在一行里表示“并且”,而不在一行里表示“或者”。
例如,要挑选审计六部或者工资8000块钱以上的人,怎么办?
在空白处输入
部门
工资
审计六部
>8000
然后设置为“条件区域”就可以了。
上述的单元格里还可以使用*和?
作为通配符。
再上一个例子:
如果要找到所有审计五部的+审计六部工资8000块以上+不管什么部门工资在10000以上的,怎么办?
“条件区域”设置为
部门
工资
审计五部
审计六部
>8000
>10000
就可以达到目的。
所以可以看出一行就是一个选择的条件。
上一个更难得例子:
现有一个表格列出了本期所有销售的记录,其中有一列是“成本”,有一列是“售价”。
现在要求把所有成本>售价的销售记录找出来,怎么办?
可以通过在“条件区域”的单元格里编辑公式来完成。
假定“成本”是在G列,“售价”是在H列,第一行数据在“3”列。
“条件区域”设置为
=G3>H3
在“条件区域”中如果用公式进行筛选,第一个单元格可以是空的,也可以是错的,但就不能是对的,也不能不选上,否则一条记录都筛不出来。
另,=G3>H3这个公式写在Excel的表格里,如果G3确实大于H3,会显现TURE,否则显示FALSE。
分类汇总功能:
在“数据”选项卡中有“分类汇总”的功能,但是要应用分类汇总功能,必须先排序,在所要分类的列里按照升序或者降序排列都可以(不论是文字还是数字),要的并不是排序的结果,而是使同样的文字汇集到一起。
排序完了点选所属数据区域内的任意单元格,按“分类汇总”键,可以根据“所属地区”对“金额”进行分类汇总。
很简单。
如果不想要了,还是点选所属数据区域的任意单元格——“分类汇总”——“全部删除”。
如果要求在分地区分类汇总的基础上再按产品分类统计数量、金额、成本,怎么办?
其实没有区别,首先是要排序,两列同时排序可以用“数据”选项卡中的“排序”键进行,只要有个顺序就可以了,没有其他要求。
然后,再按“分类汇总”——将“替换当前分类汇总”前面的勾取消掉就可以了
定位到可见单元格的快捷键是Alt+;
有个“分类汇总”的变态的操作:
要把同一列中填有同样内容(所属地区)的单元格合并:
首先对所属地区排序——对“所属区域”进行分类汇总——会在左边出现一列,选中这一列(除了第一个单元格)定位到空值——合并单元格——取消分类汇总——把合并过的那一列的格式贴到所属区域那一列。
第五讲:
数据有效性
1、对单元格设置规则,不符合条件的数据不允许输入,例如只能在A列输入500~1000的整数:
选中A列,点开“数据”选项卡——选中“数据有效性”——“数据有效性”——“设置”——“整数”——最大值1000——最小值500——确定;
2、设置B列仅能输入字符长度为8位的产品编码:
选中B列,“数据有效性“——“设置”——允许“文本长度”——数据“等于”——长度“8”;
3、设置C列付款方式中仅能输入现金、转账、支票:
选中C列,“数据有效性”——允许“序列”——来源“现金,转账,支票”,其中的逗号必须是英文的。
第六讲:
数据透视表应用
1、一列是一个字段,一行是一个完整的记录
2、选中数据区域中的某一个单元格,点击“插入”选项卡,点击数据透视,就会自动选中整个数据区域,点击“确定”,插入了新的数据透视表。
在数据透视表上点击右键,“数据透视表选项”——“显示”选项卡——勾选“经典数据透视表布局”——确定。
想把什么往哪里放,拖过去就是了。
3、双击表格左上角的“计数项:
发生额”,可以更改计数的方法,比如从发生额变为计数,就可以知道每个月发生了多少笔交易。
还可以在“分类汇总”下面点选“无”,就可以不对本列进行汇总。
如果说对某月某部门的数据有疑问,可以双击那个单元格,就会出现一张新的表格,显示被双击单元格的数据来源。
4、如果要按照地区分大类,季度分小类,统计数据,应该怎么办?
在月份那一列按右键——点击“创建组”——在“步长”里选中“季度”就可以了。
5、比如现在想分析一下本年不同金额的订单的分布情况,比如1万以下的有多少单?
金额总共是多少?
1万到两万的单有多?
总共金额有多少?
怎么办?
把“金额”拖到行标签去,在把“金额”拖到数值里去,在右键点击“行标签”任意单元格——点击“创建组”——在“起始于”填0——在“终止于”填320000(因为金额的最大值是30万多一点,但是如果填31万的话,步长不好填,只能填一万,填其他的不容易除尽,而填32万就好很多)——“步长”可填40000.
6、对同一数据进行不同分析,只要将所要分析的数据往数据栏里多拖拽几次就可以实现了。
7、在数据透视表里插入公式:
点击数据透视表区域里的任何一个单元格,会出现一个“数据透视表工具”选项卡——点击里面的“选项”选项卡——点击里面的“域、项目和集”选——点击“计算字段”——在名称里写“利润率”——在“公式”里把0删掉,想输入那一列的数据从下面“字段”里选——“=(金额-利润)/金额”——点击确定,就完成了。
如果想把一些公式计算错误的格子美观掉,右键点击数据透视表中任意单元格,选中“数据透视表选项”,点击“布局和格式”,勾选“对于错误值,显示”,就完成了。
8、将不同的项目在不同的工作表里显示:
把要分不同工作表显示的项目拖到“报表筛选”里面去——把需要显示的东西拖进数据透视表里去——选中数据透视表里的任意单元格,点击“选项”选项卡——在最左侧找到“选项”下拉箭头——点击“显示报表筛选页”——选定要显示的报表筛选页字段——确定,完成——发现自动建了非常多的工作表,而且都是按你选的那一列里的文字命名的。
第七讲:
函数
1、&是连字符,在数字7上面。
比如单元格A1写的是“张”,单元格B1写的是“三”,在单元格C1里写“=A1&B1”,则C1单元格会显示为“张三”。
2、在中文模式下,Shift+6表示省略号……,在英文模式下Shift+6表示^,是乘方。
3、当鼠标移动到右下角变成黑实心十字时,双击,就起到了拖拽的作用,下面的单元格自动填充了。
4、绝对引用:
当公式中引用某个单元格比如A2单元格后,按F4,就会显示为$A$2,表示绝对引用,即无论公式怎么拖拽,始终引用A2这一个单元格。
其中$表示锁定,如果按两次F4会变成A$2,按三次会变成$A2,按四次就没有了。
$A$2表示永远引用这一个单元格,A$2表示上下拖动(沿1、2、3、4……方向)不会改变引用的单元格,但左右拖动会改变;$A2表示左右拖动(沿A、B、C、D、E……方向)不会改变引用的单元格,但上下拖拽会改变。
混合引用例:
九九乘法表(只计算得数)
1
2
3
4
5
6
7
8
9
1
1
2
2
4
3
3
6
9
4
4
8
12
16
5
5
10
15
20
25
6
6
12
18
24
30
36
7
7
14
21
28
35
42
49
8
8
16
24
32
40
48
56
64
9
9
18
27
36
45
54
63
72
81
5、求排名的函数rank:
这个函数的完整形式=rank(需要被排名的数字,在哪些数字中进行排名,0或者1)其中0表示降序排名,即最大的数是第一名,比较常用;1表示升序排名,即最小的数是第一名。
另注意,因为需要拖拽,而所选“在哪些数字中进行排名”往往不需要变,所以在选中“在哪些数字中进行排名”后按一下F4.
6、在“开始”选项卡左边有个自动“自动求和”按钮,下拉箭头中还有其他函数。
这个按钮的最大特点是可以自动选择数据区域,因此当不方便选择数据区域时好用。
例如有一列数据,中间有隔两个数需要求一次和的,有隔三个数求一次和的,有隔五个数求一次和的,各不相同,不方便拖拽。
这时只要定位到空值,然后使用“自动求和”按钮就可以自动选中要求和的数据,非常方便。
自动求和的快捷键为Alt+=.
第八讲:
IF函数逻辑判断
1、=if(条件,如果符合条件将显示的值,如果不符合条件将显示的值)
2、例如现在有一个学校的学生的分数表,其中600以上(含)的是第一批,大于等于500小于600的是第二批,大于等于400小于500的是第三批,小于400的是落榜。
公式应该怎样写呢?
注意,Excel是不认识如“400<=H4<500”这个公式的,它会先运算前面的“<=400”得到“TURE”或者“FALSE”,然后把“TURE”或者“FALSE”跟500进行比较。
上例的公式可以写为:
=if(H4>=600,”第一批”,if(H4>=500,”第二批”,if(H4>=400,”第三批”,”落榜”)))
3、if函数和iserror函数相连,比如某列用公式计算出来的值没有意义(比如说因为除了一个0),导致表不好看,或者后面的数据引用这个值的时候没有意义,这时就可以利用if函数和iserror函数结合使用:
=if(iserror(D3/F3),0,D3/F3)
本公式的意思是如果D3/F3是错误的话,那就显示为0,否则就显示其运算结果。
4、if函数和and函数、or函数相连,比如年龄60岁以上的男性员工给予1000元奖励:
=if(and(A3=”男”,B3>=60),1000,0)
比如给所有女性和大于60岁的男性1000元奖金:
=if(or(A3=”女”,and(A3=”男”,B3>=60)),1000,0)
在and()和or()中可以有多个条件,三个、四个都搞得定。
一定要记住:
if函数嵌套几层取决于结果有几种,而有几种可能性只影响and或者or函数怎么搭配。
第九讲:
COUNTIF函数
1、count函数只会数数字,而不会把文字包含在内。
2、=countif(需要计数的区域,条件)
3、例如要看看一个班里数学考及格的有几个人:
=countif(B2:
G2,”>=60”),完成。
因为>=60不算是数字也不算是完整的公式,所以只能认为他是一个字符串,所以要用””把它括起来。
4、使用countif函数数超过15位的数字的时候要注意,因为Excel会自动忽略15位以后的不同,所以后面要加点东西以保证数出来的数正确:
=countif(A2:
A200,A2&”*”)
5、Countif函数的应用1:
例如有一个班的人需要体检,其中昨天已经体检了10个了,而且有名单,现在要把没有体检的人找出来(全班人员名单在A列,已体检的人员名单在H列)。
=if(countif(H:
H,A2)=1,”已体检”,”未体检”)
6、Countif和条件格式搭配:
接上例,如果要把没有体检过的人都标记为红色,岂不是更加好找?
选中全班同学的名字——“开始”选项卡——“条件格式”——“新建规则”——“使用公式确定要设置格式的单元格”——在“为符合此公式的值设置格式”下面写公式:
=countif(H:
H,A2)=0——“格式”背景颜色改为红色,完成
7、Countif和数据有效性搭配:
例如,要把C列的数据有效性设置为不允许输入重复的值。
选中C列——“数据”选项卡——“数据有效性”——“设置”选项卡——允许“自定义”——“公式”:
=countif(C:
C,C1)<=1——确定,完成
8、注意,在跟条件格式、数据有效性搭配的时候,countif函数中的第二项数据,一般都选择选中区域中发白的那个单元格,且不固定(没有$符号),其他单元格就自动填充起来了。
9、2010和2007版还有个加强的功能:
countifs,可以数出符合多个条件的数据的个数。
=countifs(区域,某值,区域,某值……)其中区域和某值必须成对出现。
第十讲:
SUMIF函数
1、=sumif(查找区域,查找条件,加总区域),如果加总区域和查找区域是一个区域,那加总区域可以不写。
2、如果有往一些银行卡存钱的记录,其中有一张卡存多
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 制表 方法
![提示](https://static.bingdoc.com/images/bang_tan.gif)