EXCEL操作与技巧PPT课件

报表 数据筛选 自定义筛选，筛选的匹配符：？--- 单个字符。 * --- 多个字符 状态栏上会显示符合筛选条件项目的个数。
28
第28页/共46页
报表
数据透视
数据透视是依据用户的需要，从不同的角度在列表中提取数据，重 新拆装组成新的表，它不是简单的数据提取，而是伴随着数据的统计处 理。
1、数据透视表的建立 使用【数据】菜单中的【数据透视表和数据透视图】命令，执行该 命令向导，一步一步完成透视表。
36
第36页/共46页
条件格式（EXCEL） 用于将符合条件的单元格按要求的格式进行标注。 有两种条件： 1、按单元格数值（绝对值），可批量设置 2、按公式（相对值），只能单格设置
37
第37页/共46页
设置数据有效性（EXCEL） 输入数据时，某些单元格只能输入数值，某些单元格只能输入字符，为 了保证正确输入数据，要使用到数据有效性。
38
第38页/共46页
格式刷（OFFICE） 用于复制格式 点击格式源，单击“格式刷”，再单击目标单元格 可一次单击格式源，连续使用在多个目标单元格上，且目标格 分散多处。使用方法：点击格式源，双击“格式刷”，依次单 击目标单元格即可。
39
第39页/共46页
页面设置标题行/列（EXCEL） 用于一个页面无法显示全部内容时，在各页面加上统一标题，以便在后续 页面显示出表头。
无需加""符号 （4）函数中设置的计算范围需一致 （5）需在函数公式打开状态下,同时按下Ctrl+Shift+Enter,
当公式头尾出现花括号｛｝时才算计算完成
第19页/共46页
2 自动化注意事项 2、统计数据来源（明细）
（1）源数据较大时，应将“菜单栏/工具”的重新计算调整为“手动计算” （2）格式：不得出现空格
第24页/共46页
3 常用函数略析 5、返回同类的个数 --COUNTIF
第25页/共46页
回顾
（1）求和的函数是什么？
（2）vlookup如何使用？
26
第26页/共46页
报表 数据排序 一次排序最多可以选择三个关键字：主关键字、次关键字、第三关 键字。 或者利用工具栏上的两个排序按钮。
27
第27页/共46页
18
第18页/共46页
2 自动化注意事项
1、函数组成元素 （1）括号（）要对应显示，不可出现单独的括号，或少括号 （2）输入法的半角全角、大小写需注意
如："" 不可输入成“” * 不可输入成×
（3）如条件是寻找某个文字：南京， 在函数中应输入为: "南京"
如条件是寻找某个单元格或数字：A5(A列第5行) 或5 在函数中应输入为：A5 或5
值进行修改时，数据透视表并不会自动更新，必须执行【刷新数据】才 行。
第30页/共46页
报表
报表排版
报表应当含有： 1、报表名称（在表格抬头，通常字号略大些） 2、表头（在表格主体的顶端与侧端，通常可用颜色着重标注） 3、各字段数据值（如有文字内容应相对简略，避免视觉疲劳；另如 有大量零值，可使用“工具”菜单下的“选项”按钮，将“视图”页 面下的“零值”一栏勾掉不显示） 报表需要： 1、合适的列宽和行高（鼠标放置需求列或行线的最末端，双击即可） 2、当表格过长或过宽时，应使用“冻结窗口”功能。
状态栏
工作表
2
第2页/共46页
回顾 （1）EXCEL中工作簿、工作表和单元格的关系是什么？ （2）EXCEL的窗口由哪几个部分组成？
3
第3页/共46页
EXCEL基础常识 （1）公式 （2）函数
4
第4页/共46页
公式与函数 公式 在工作表中对数据进行计算的式子，它可以对工作表数值进行加、减、 乘、除等运算。
如：“黑电”和“黑 电” （3）某一字段暂不需/无法统计，如是需量化的单元格，则需以“0”填充，
不可写“无” （4）不可将明细全部删除后，在原有模板的基础上重写贴入明细再次使用，
将会改变计算范围，应重新使用新模板 （5）注意统计周期的输入 （6）注意EXCEL函数设置中某条件的计算列是否是需要的计算列 （7）如需从一个EXCEL表格的数据复制到另一表格中，应点击“菜单栏/
第33页/共46页
图表制作 图表类型 1、柱形图：比较分析 2、折线图：趋势分析 3、饼图：占比分析 着重标色
34
第34页/共46页
回顾 （1）饼图适用于什么场景？ （2）柱状图如何制作？
35
第35页/共46页
快捷键
复制CTRL+C 粘贴CTRL+V 查找CTRL+F 替换CTRL+H 撤销上一次的操作CTRL+Z 重做上一次的操作CTRL+Y 单元格内换行 ALT+ENTER 选择不连续单元格区域 CTRL+鼠标左击逐个单元格 选择连锁单元格区域SHIFT+鼠标左击第一个和最后一个单元格
创建公式
运算符
（1）算术运算：+（加）、-（减）、*（乘）、/（除）、%（百分比）、 ^（乘方) （2）比较运算：=（等于）、>（大于）、<（小于）、>=（大于等 于）、 <=（小于等于）、<>（不等于） （3）字符运算：&（连接） （4）引用运算：（：冒号）、（，逗号）、（ 空格）
要创建一个公式，首先需要选定一个单元格，输入一个等于号“=”， 然后在其后输入公式的内容，按回车键就可以按公式计算得出结果。
第17页/共46页
1 自动化公式详解 函数统计规则：
需要查找数据的数 据表区域
计算区域中满足给定条件的单元格的个数。
COUNTIF
条件
COUNTIF(range,criteria)
RANK
返回一个数字在数字列表中的排名。
RANK(number,ref,order)
值
数据范围
0：从大到小 1：从小到大 默认为0
（1）合并单元格 （2）复制/粘贴单元格 （3）填充单元格 （4）插入/删除/清除单元格 （5）添加/编辑/清除批注 （6）数据转置
14
第14页/共46页
EXCEL基础操作
（1）自动化公式 （2）常用函数
第15页/共46页
15
1
自动化公式详解
函数统计规则：
函数名（参数1，参数2，参数3…）
参数有必选参数和可选参数两种。如若必选参数不完整，系统会提示 参数太少。
10
第10页/共46页
创建公式
函数功能
➢数值统计（sum、count、max、min、large、small、sumif、countif、 dsum、dcount、sumcolor等） ➢逻辑判断（if、and、not、or、isnumber、iserror等） ➢文本处理（mid、left、right等） ➢日期时间（now、date、day、today等） ➢财务计算 ➢工程分析
第31页/共46页
回顾 （1）如何筛选数据？ （2）如何做透视表？
32
第32页/共46页
图表制作 在处理电子表格时，需要对大量繁琐的数据进行分析和研究，我们需
要利用图形方式再现数据变动和发展趋势。 EXCEL提供了强有力的图表处理功能，使得用户很快就可得到所要的
图表。
创建图表
1、在工作表上选取图表的数据区 2、启动图表向导 3、选择图表类型 4、确认数据源 5、设置图表选项 6、选择工作表类型 7、对图表进行修饰
EXCEL基础常识 （1）工作簿 （2）工作表 （3）单元格
1
第1页/共46页
工作簿
菜单栏
单元格，通过位置（列标行标）进行标识。
工具栏
1张工作表（sheet）由65536行*256列，即65536*256=1677万个单元格组成； 1个工作簿（book）最多可含有255张工作表。 注：EXCEL2007以下版本。
29
第29页/共46页
报表
数据透视
2、数据透视表的编辑 数据透视表建立后，屏幕自动显示【数据透视】工具栏，利用工具栏，
可方便地修改数据透视表。 调整字段：行字段区、列字段区、数据字段区和页字段区 数据区字段汇总方式的修改：数据透视不仅可以对字段进行求和，而且
可以对其进行计数，求平均值、最大值等。 更新数据透视表：数据透视表的数据值依赖于数据源的值，在对数据源
第11页/共46页
11
创建公式 函数的使用
➢手工录入 ➢粘贴函数 首先选定要生成函数的单元格， 然后单击常用工具上的【插入函数】 按钮 ，打开【插入函数】对话框。
12
第12页/共46页
回顾 （1）什么叫公式？什么叫函数？ （2）什么是相对引用和绝对引用？
13
第13页/共46页
EXCEL基础操作
9
第9页/共46页
创建公式 绝对地址与相对地址
相对地址：=B3+B4+B5+B6 绝对地址：=$B$3+$B$4+$B$5+$B$6
混合运用，F4切换。 两者的差异是： 相对地址：如果在A7单元格内输入公式 =B3+B4+B5+B6，那么将A7 中的公式复制至A8单元格时，A8的结果变成=B4+B5+B6+B7， 再将 A8中的公式复制至B8时，B8的结果变成=C4+C5+C6+C7，也就是绝对 地址在公式复制时，会随单元格的不同而发生变化。 绝对地址：在公式复制时，不会随单元格的不同而发生变化。不管复制 到哪个单元格，结果都是同一个公式，即=$B$3+$B$4+$B$5+$B$6。
7
第7页/共46页
创建公式 运算符的优先级
运算符 引用运算符： ，
% 乘方 乘或除 加或减
& 比较运算符（> < >= <= <>） 使用圆括号改变运算符的优先级顺序。
第8页/共46页
优先级 1 2 3 4 5 6 7
创建公式
单元格的引用
引用运算符：（：冒号）、（，逗号）、（ 空格） （1）：（冒号）——区域运算符，如B2:F6表示B2单元格到F6单元格矩 形区域内的所有单元格。 （2），（逗号）——联合运算符，将多个引用合并为一个引用，如 SUM(B5:B26,D4:D22)，表示B5至B26以及D4至D22所有单元格求和 （SUM是求和函数） （3） （空格） ——交叉运算符，如SUM(B5:B26 A4:D22)表示求B5 至B26与A4至D22两区域交叉单元格之和，即B5至B22之和。
16
第16页/共46页
1 自动化公式详解 函数统计规则：
返回某一单元格区域中所有数字之和
数字、逻辑值、文 本ห้องสมุดไป่ตู้达式
SUM
SUM(number1,number2, ...)
IF
判断某一条件是否满表足示，tru如e或满f足als返e的回任一个值为t，ru如e的时不值返满回足返回另一个值
意值或表达式
为false时返回
的值
IF(logical_test,value_if_true,value_if_false)
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中
指定列处的数值
VLOOKUP为需要数在值数或组文第本一字列符中串查询的
为一逻辑值找不到匹配值时 返回#N/A错误值
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
角标志）。 进入“格式”菜单下的“单元格”页面，设置成“数值”型，将文本型
值重新手动输入即可。
41
第41页/共46页
常见问题解决
3、问题：设置了公式后，全表复制使用，但结果值不对。 解决思路：检查确认公式是绝对引用还是相对引用，会引发错误。 方法：绝对引用与相对引用的快捷切换键是F4。
4、问题：页面固定，使用滚动条无法翻页。 解决思路：取消窗口冻结。 方法：在“窗口”菜单下面选择“取消窗口冻结”。
实例
40
第40页/共46页
常见问题解决
1、问题：设置了公式后，结果不能实时更新。 解决思路：检查系统设置的重算方法是否为“自动重算”。 方法：进入“工具”菜单下的“选项”页面，找到里面的“重新计算”，
设置“自动重算”即可。
2、问题：用SUM求和后，发现总值不对。 解决思路：检查数据是否全为数值型。 方法：数值型通常自动靠右，文本型通常自动靠左（左上角会出现绿三
编辑”的链接中“断开链接”
第20页/共46页
3
常用函数略析
1、条件判断--IF
21
第21页/共46页
3 常用函数略析 2、搜索表区域首列满足条件的元素--VLOOKUP
第22页/共46页
3 常用函数略析 3、返回一个数字在数字列表中的排名 --RANK
第23页/共46页
3 常用函数略析 4、返回同类的合计数 --SUMIF
函数 函数是EXCEL预定义的内置程序，可以使用在公式中。
5
第5页/共46页
创建公式 数据类型 （1）数值型：一般的数字格式。 （2）货币型：货币格式，对一列数值进行小数点对齐。 （3）日期时间型：可以对两个时间段的间隔时长进行计算。 （4）文本型：在文本单元格中，数字、公式作为文本处理。
6
第6页/共46页