excel常用公式大全 ppt课件
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
用各种类型数据的参数(1～30个)，其中只 有数字类型的数据才能被统计。
• 实例：如果A1=90、A2=人数、A3=〞〞、
A4=54、A5=36，则公式 “=COUNT(A1:A5)”返回3
2021/3/26
excel常用公式大全 ppt课件
15
• COUNTA
• 用途：返回参数组中非空值的数目。
2021/3/26
excel常用公式大全 ppt课件
31
工作表的格式化
调整行高和列宽
– 鼠标拖拽操作 – 菜单操作： [格式]→[行]/[列] – 隐藏行或列：[格式]→[行]/列]→[隐藏]
2021/3/26
excel常用公式大全 ppt课件
32
工作表的格式化
工作表的整体显示
– 工作表的背景设置 菜单[格式]→[工作表]→[背景] – 工作表的整体设置 菜单[工具]→[选项]的[视图]，工作 表的网格线的设置
3
公式中的数据类型
输入公式要注意
公式中的运算对象可以包括：数值和 字符、单元格地址、区域、函数等,如： =A2+B2+C2
不要随意包含空格 公式中的字符要用半角引号括起来 公式中运算符两边的数据类型要相同 如：="ab"+25 出错 #VALUE
2021/3/26
excel常用公式大全 ppt课件
2021/3/26
excel常用公式大全 ppt课件
17
• COUNTIF
• 用途：计算区域中满足给定条件的单元
格的个数。
• 语法：COUNTIF(range，criteria)
• 参数：Range为需要计算其中满足条件
的单元格数目的单元格区域。Criteria为确 定哪些单元格将被计算在内的条件，其形式 可以为数字、表达式或文本
=$B$2+$B$3+$B$4
混合地址 在公式复制时绝对地址不变，相对
地址按规则调整。
2021/3/26
excel常用公式大全 ppt课件
10
公式的调整
公式移动 相对和绝对地址都不调整
➢ C3单元格的公式=B2+B3+B4移动到D5中， ➢ D3单元格的公式=B2+B3+B4
被公式引用的单元格移动，公式跟踪单元格，值不变
excel常用公式大全 ppt课件
21
IF(E2>=85,“优”,IF(E2>=75,“良”， IF(E2>=60,"及格"，"不及格")))
2021/3/26
excel常用公式大全 ppt课件
22
• 函数从左向右执行。首先计算E2>=85，
如果该表达式成立，则显示“优”，如果不 成立就继续计算E2>=75，如果该表达式 成立，则显示“良”，否则继续计算 E2>=60，如果该表达式成立，则显示 “及格”，否则显示“不及格”。
表中的数据，要引用的单元格地址前加表名 字和感叹号！
如：=sheet3!E3+sheet3!F3
一个公式中引用不同工作簿中的数据，要在
引用的单元格地址前加工作簿的名字，并用 方括号括起来
如：=[cjb.xls]sheet3!E3+[cjb.xls]sheet3!F3
2021/3/26
excel常用公式大全 ppt课件
100*B1/B2) （函数计算）
• ="ABC"&"XYZ" (字符计算，结果:ABCXYZ)
公式中常用单元格的引用来代替单元格的具体数据，当公式
中被引用单元格的数据变化时，公式的计算结果会随之变化
2021/3/26
excel常用公式大全 ppt课件
6
公式的复制和移动
公式的复制：
– 使用填充柄 – 菜单[编辑]→[复制]/[粘贴]
复制公式时，公式会作相对调整
2021/3/26
excel常用公式大全 ppt课件
7
相对引用和绝对引用
• 相对引用是针对当前单元格的引用，是随当
前单元格变化而变化的
• 绝对引用是针对起始单元格的引用，是不随
当前单元格的变化而变化的
2021/3/26
excel常用公式大全 ppt课件
8
公式的调整
相对地址引用: 在公式复制时将自动调整
录可以连续访问。
2021/3/26
excel常用公式大全 ppt课件
27
• 操作步骤：
• ●先对分类字段进行排序
• ●打开“数据”下拉菜单，再单击“分类汇
总”命令
• ●在“分类汇总”对话框中选择分类的字段
（与排序字段相同），汇总的方式，汇总的 项（统计的数值字段）
2021/3/26
excel常用公式大全 ppt课件
2021/3/26
excel常用公式大全 ppt课件
23
数据排序
• 使用工具栏上的排序按钮进行排序 • 单击数据清单需要排序的列中任一单元格； • 单击“常用工具栏”上的升序或降序按钮.
2021/3/26
excel常用公式大全 ppt课件
24
• 使用“数据”菜单中的“排序”命令进行排
序
• 1、排序 • 单击数据清单任一单元格； • 单击[数据]-[排序] • 选择主要关键字、次要关键字； • 选择升序还是降序，确定。
2
公式中的运算符
算术运算符：+ - * / ^ % 字符运算符：& 连接符 比较运算符：= < > <= >= <> 逻辑运算符：and or not 以函数形式出现 优先级顺序：算术运算符→字符运算符→
比较运算符→逻辑函数符
(使用括号可确定运算顺序)
2021/3/26
excel常用公式大全 ppt课件
19
• 3．条件表达式
• 把两个表达式用关系运算符(主要有=，<>，
>，<，>=，<=等6个关系运算符)连接起 来就构成条件表达式，例如，在 IF(a1+b1+50 <> b1+c1-50, 1,2)函 数式中，条件表达式是a1+b1+50 <> b1+c1-50。
• 4．执行过程
• 下面以IF(a1+b1+50 <> b1+c1-50,
语法：COUNTA(value1，value2，...)
• 说明:参数可以是任何类型，它们包括空格
但不包括空白单元格。如果不需要统计逻辑 值、文字或错误值，则应该使用COUNT函 数。
• 实例：如果A1=6.28、A2=3.74，A3=“我们”
其余单元格为空，则公式 “=COUNTA(A1:A7)”的计算结果等于3。
28
清除分类汇总
• “编辑“里的“撤消分类汇总”命令（若
已按了1、2或3分级显示，则需多次按 “撤消显示分级层次”至到出现“撤消分 类汇总”为止），
• 方法二：在“分类汇总”对话框中按“全
部删除”按钮。
2021/3/26
excel常用公式大全 ppt课件
29
多工作表或工作簿的操作
同一工作簿中的某一个公式中引用不同工作
SUMIF(A1:A4,">160,000",B1:B4)
最后的求和结果将是$63,000。
2021件
14
函数
• COUNT • 用途：它可以统计数组或单元格区域中
含有数字的单元格个数。
• 语法：COUNT(value1，value2，...)。 • 参数：value1，value2，...是包含或引
• Range 为用于条件判断的单元格区域。
Criteria 为确定哪些单元格将被相加求和 的条件，其形式可以为数字、表达式或文本。
例如，条件可以表示为 32、“32”、 “>32” 或 “apples”。
• Sum_range 为需要求和的实际单元格。
只有当 Range 中的相应单元格满足条件时，
格式行列隐藏3333工作表的格式化工作表的整体显示工作表的背景设置菜单格式工作表背景工作表的整体设置菜单工具选项的视图工作表的网格线的设置3434自动套用格式菜单格式自动套用格式样式格式的组合菜单格式样式添加修改删除应用样式工作表的格式化3535拆分和冻结工作表工作表拆分成多个窗格在拆分窗口中查看工作表的不同部分内容http
2021/3/26
excel常用公式大全 ppt课件
16
• COUNTBLANK
• 用途：计算某个单元格区域中空白单元
格的数目。
• 语法：COUNTBLANK(range)
• 参数：Range为需要计算其中空白单元
格数目的区域。
• 实例：如果A1=88、A2=55、A3=""、
A4=72、A5=""，则公式 “=COUNTBLANK(A1:A5)”返回2。
30
工作表的格式化
单元格格式的设置: 选定单元格或区域
选择菜单[格式]→[单元格]
设置数字格式：两位小数、千位分隔
字体、对齐方式、边框底纹等，EXCEL表格要
加边框线，打印的时候才有边线
合并及居中
选择菜单[格式]→[条件格式]设置带条件的格式， 如：小于60分的成绩显示红色粗体，背景为兰色
使用[格式刷]或[选择性粘贴]复制格式
2021/3/26
excel常用公式大全 ppt课件
25
数据筛选
• 任选一个单元格 •数据-----筛选------自动筛选
2021/3/26
excel常用公式大全 ppt课件
26
分类汇总
• 分类汇总:就是把某一个关键字段的相同数
据汇总在一起。
•
使用分类汇总命令之前，首先要对关
键字段进行排序，使关键字段相同的数据记
2021/3/26
excel常用公式大全 ppt课件
11
选择性粘贴
菜单[编辑]→[复制] 菜单[编辑]→[选择性粘贴]
公式、数值、格式的粘贴 运算、转置的使用
2021/3/26
excel常用公式大全 ppt课件
12
Sumif函数
• 根据指定条件对若干单元格求和。
• SUMIF(range,criteria,sum_range)
2021/3/26
excel常用公式大全 ppt课件
33
工作表的格式化
自动套用格式
菜单[格式]→[自动套用格式]
样式（格式的组合）
菜单[格式]→[样式]
添加、修改、删除、应用样式
2021/3/26
excel常用公式大全 ppt课件
34
才对 sum_range 中的单元格求和。如果
省略 sum_range。则直接对 Range 中的
单元格求和 2021/3/26
excel常用公式大全 ppt课件
13
• 例如，假设A1:A4单元格中的内容分别为四
种产品的属性值：$100,000，$200,000， $300,000，$400,000。B1:B4 的内容为每 个属性值所对应的销售佣金；$7,000， $14,000，$21,000，$28,000，那么就可以 运行公式：
4
公式的输入
公式的基本特性：
• 公式的输入是以“ = ”开始 • 公式计算结果显示在单元格中 • 公式本身显示在编辑栏中
2021/3/26
excel常用公式大全 ppt课件
5
公式示例
• =1+2+6 （数值计算） • =A1+B2 （引用单元格地址） • =SUM(A1:C4) （公式计算） • =IF( and(A1>A2,B1>B2),100*A1/A2,
调整原则是：新行地址=原来行地址+行偏移量
•
新列地址=原来列地址+列偏移量
例如：将D2单元中的公式 =A2+B2+C2
复制到D4后，D4中的公式 =A4+B4+C4
2021/3/26
excel常用公式大全 ppt课件
9
公式的调整
绝对地址引用 在公式复制时不变。
➢例如：C3单元的公式=$B$2+$B$3+$B$4 ➢复制到D5中，D5单元的公式
2021/3/26
excel常用公式大全 ppt课件
18
If函数
• 1．IF函数的语法结构
IF函数的语法结构：IF(条件,结果1,结果2)。
• 2．IF函数的功能 • 对满足条件的数据进行处理，条件满足则输
出结果1，不满足则输出结果2。可以省略 结果1或结果2，但不能同时省略。
2021/3/26
excel常用公式大全 ppt课件
1,1)函数式为例来说明IF函数的执行过程。
2021/3/26
excel常用公式大全 ppt课件
20
先计算条件表达式a1+b1+50 <> b1+c1 -50，如果表达式成立，值为TRUE，并在 函数所在单元格中显示“ 1”；如果表达 式不成立，值为FALSE，并在函数所在单 元格中显示“2”。
2021/3/26
EXCEL公式和函数
公式的使用和公式的调整 工作表的格式化
2021/3/26
excel常用公式大全 ppt课件
1
公式
• Excel表格中不仅可以输入数据，
也可以使用公式对工作表中的数据 进行各种数值计算和统计分析等
• Excel的公式是由运算符号，和参
与计算的运算数组成的表达式
2021/3/26
excel常用公式大全 ppt课件
• 实例：如果A1=90、A2=人数、A3=〞〞、
A4=54、A5=36，则公式 “=COUNT(A1:A5)”返回3
2021/3/26
excel常用公式大全 ppt课件
15
• COUNTA
• 用途：返回参数组中非空值的数目。
2021/3/26
excel常用公式大全 ppt课件
31
工作表的格式化
调整行高和列宽
– 鼠标拖拽操作 – 菜单操作： [格式]→[行]/[列] – 隐藏行或列：[格式]→[行]/列]→[隐藏]
2021/3/26
excel常用公式大全 ppt课件
32
工作表的格式化
工作表的整体显示
– 工作表的背景设置 菜单[格式]→[工作表]→[背景] – 工作表的整体设置 菜单[工具]→[选项]的[视图]，工作 表的网格线的设置
3
公式中的数据类型
输入公式要注意
公式中的运算对象可以包括：数值和 字符、单元格地址、区域、函数等,如： =A2+B2+C2
不要随意包含空格 公式中的字符要用半角引号括起来 公式中运算符两边的数据类型要相同 如：="ab"+25 出错 #VALUE
2021/3/26
excel常用公式大全 ppt课件
2021/3/26
excel常用公式大全 ppt课件
17
• COUNTIF
• 用途：计算区域中满足给定条件的单元
格的个数。
• 语法：COUNTIF(range，criteria)
• 参数：Range为需要计算其中满足条件
的单元格数目的单元格区域。Criteria为确 定哪些单元格将被计算在内的条件，其形式 可以为数字、表达式或文本
=$B$2+$B$3+$B$4
混合地址 在公式复制时绝对地址不变，相对
地址按规则调整。
2021/3/26
excel常用公式大全 ppt课件
10
公式的调整
公式移动 相对和绝对地址都不调整
➢ C3单元格的公式=B2+B3+B4移动到D5中， ➢ D3单元格的公式=B2+B3+B4
被公式引用的单元格移动，公式跟踪单元格，值不变
excel常用公式大全 ppt课件
21
IF(E2>=85,“优”,IF(E2>=75,“良”， IF(E2>=60,"及格"，"不及格")))
2021/3/26
excel常用公式大全 ppt课件
22
• 函数从左向右执行。首先计算E2>=85，
如果该表达式成立，则显示“优”，如果不 成立就继续计算E2>=75，如果该表达式 成立，则显示“良”，否则继续计算 E2>=60，如果该表达式成立，则显示 “及格”，否则显示“不及格”。
表中的数据，要引用的单元格地址前加表名 字和感叹号！
如：=sheet3!E3+sheet3!F3
一个公式中引用不同工作簿中的数据，要在
引用的单元格地址前加工作簿的名字，并用 方括号括起来
如：=[cjb.xls]sheet3!E3+[cjb.xls]sheet3!F3
2021/3/26
excel常用公式大全 ppt课件
100*B1/B2) （函数计算）
• ="ABC"&"XYZ" (字符计算，结果:ABCXYZ)
公式中常用单元格的引用来代替单元格的具体数据，当公式
中被引用单元格的数据变化时，公式的计算结果会随之变化
2021/3/26
excel常用公式大全 ppt课件
6
公式的复制和移动
公式的复制：
– 使用填充柄 – 菜单[编辑]→[复制]/[粘贴]
复制公式时，公式会作相对调整
2021/3/26
excel常用公式大全 ppt课件
7
相对引用和绝对引用
• 相对引用是针对当前单元格的引用，是随当
前单元格变化而变化的
• 绝对引用是针对起始单元格的引用，是不随
当前单元格的变化而变化的
2021/3/26
excel常用公式大全 ppt课件
8
公式的调整
相对地址引用: 在公式复制时将自动调整
录可以连续访问。
2021/3/26
excel常用公式大全 ppt课件
27
• 操作步骤：
• ●先对分类字段进行排序
• ●打开“数据”下拉菜单，再单击“分类汇
总”命令
• ●在“分类汇总”对话框中选择分类的字段
（与排序字段相同），汇总的方式，汇总的 项（统计的数值字段）
2021/3/26
excel常用公式大全 ppt课件
2021/3/26
excel常用公式大全 ppt课件
23
数据排序
• 使用工具栏上的排序按钮进行排序 • 单击数据清单需要排序的列中任一单元格； • 单击“常用工具栏”上的升序或降序按钮.
2021/3/26
excel常用公式大全 ppt课件
24
• 使用“数据”菜单中的“排序”命令进行排
序
• 1、排序 • 单击数据清单任一单元格； • 单击[数据]-[排序] • 选择主要关键字、次要关键字； • 选择升序还是降序，确定。
2
公式中的运算符
算术运算符：+ - * / ^ % 字符运算符：& 连接符 比较运算符：= < > <= >= <> 逻辑运算符：and or not 以函数形式出现 优先级顺序：算术运算符→字符运算符→
比较运算符→逻辑函数符
(使用括号可确定运算顺序)
2021/3/26
excel常用公式大全 ppt课件
19
• 3．条件表达式
• 把两个表达式用关系运算符(主要有=，<>，
>，<，>=，<=等6个关系运算符)连接起 来就构成条件表达式，例如，在 IF(a1+b1+50 <> b1+c1-50, 1,2)函 数式中，条件表达式是a1+b1+50 <> b1+c1-50。
• 4．执行过程
• 下面以IF(a1+b1+50 <> b1+c1-50,
语法：COUNTA(value1，value2，...)
• 说明:参数可以是任何类型，它们包括空格
但不包括空白单元格。如果不需要统计逻辑 值、文字或错误值，则应该使用COUNT函 数。
• 实例：如果A1=6.28、A2=3.74，A3=“我们”
其余单元格为空，则公式 “=COUNTA(A1:A7)”的计算结果等于3。
28
清除分类汇总
• “编辑“里的“撤消分类汇总”命令（若
已按了1、2或3分级显示，则需多次按 “撤消显示分级层次”至到出现“撤消分 类汇总”为止），
• 方法二：在“分类汇总”对话框中按“全
部删除”按钮。
2021/3/26
excel常用公式大全 ppt课件
29
多工作表或工作簿的操作
同一工作簿中的某一个公式中引用不同工作
SUMIF(A1:A4,">160,000",B1:B4)
最后的求和结果将是$63,000。
2021件
14
函数
• COUNT • 用途：它可以统计数组或单元格区域中
含有数字的单元格个数。
• 语法：COUNT(value1，value2，...)。 • 参数：value1，value2，...是包含或引
• Range 为用于条件判断的单元格区域。
Criteria 为确定哪些单元格将被相加求和 的条件，其形式可以为数字、表达式或文本。
例如，条件可以表示为 32、“32”、 “>32” 或 “apples”。
• Sum_range 为需要求和的实际单元格。
只有当 Range 中的相应单元格满足条件时，
格式行列隐藏3333工作表的格式化工作表的整体显示工作表的背景设置菜单格式工作表背景工作表的整体设置菜单工具选项的视图工作表的网格线的设置3434自动套用格式菜单格式自动套用格式样式格式的组合菜单格式样式添加修改删除应用样式工作表的格式化3535拆分和冻结工作表工作表拆分成多个窗格在拆分窗口中查看工作表的不同部分内容http
2021/3/26
excel常用公式大全 ppt课件
16
• COUNTBLANK
• 用途：计算某个单元格区域中空白单元
格的数目。
• 语法：COUNTBLANK(range)
• 参数：Range为需要计算其中空白单元
格数目的区域。
• 实例：如果A1=88、A2=55、A3=""、
A4=72、A5=""，则公式 “=COUNTBLANK(A1:A5)”返回2。
30
工作表的格式化
单元格格式的设置: 选定单元格或区域
选择菜单[格式]→[单元格]
设置数字格式：两位小数、千位分隔
字体、对齐方式、边框底纹等，EXCEL表格要
加边框线，打印的时候才有边线
合并及居中
选择菜单[格式]→[条件格式]设置带条件的格式， 如：小于60分的成绩显示红色粗体，背景为兰色
使用[格式刷]或[选择性粘贴]复制格式
2021/3/26
excel常用公式大全 ppt课件
25
数据筛选
• 任选一个单元格 •数据-----筛选------自动筛选
2021/3/26
excel常用公式大全 ppt课件
26
分类汇总
• 分类汇总:就是把某一个关键字段的相同数
据汇总在一起。
•
使用分类汇总命令之前，首先要对关
键字段进行排序，使关键字段相同的数据记
2021/3/26
excel常用公式大全 ppt课件
11
选择性粘贴
菜单[编辑]→[复制] 菜单[编辑]→[选择性粘贴]
公式、数值、格式的粘贴 运算、转置的使用
2021/3/26
excel常用公式大全 ppt课件
12
Sumif函数
• 根据指定条件对若干单元格求和。
• SUMIF(range,criteria,sum_range)
2021/3/26
excel常用公式大全 ppt课件
33
工作表的格式化
自动套用格式
菜单[格式]→[自动套用格式]
样式（格式的组合）
菜单[格式]→[样式]
添加、修改、删除、应用样式
2021/3/26
excel常用公式大全 ppt课件
34
才对 sum_range 中的单元格求和。如果
省略 sum_range。则直接对 Range 中的
单元格求和 2021/3/26
excel常用公式大全 ppt课件
13
• 例如，假设A1:A4单元格中的内容分别为四
种产品的属性值：$100,000，$200,000， $300,000，$400,000。B1:B4 的内容为每 个属性值所对应的销售佣金；$7,000， $14,000，$21,000，$28,000，那么就可以 运行公式：
4
公式的输入
公式的基本特性：
• 公式的输入是以“ = ”开始 • 公式计算结果显示在单元格中 • 公式本身显示在编辑栏中
2021/3/26
excel常用公式大全 ppt课件
5
公式示例
• =1+2+6 （数值计算） • =A1+B2 （引用单元格地址） • =SUM(A1:C4) （公式计算） • =IF( and(A1>A2,B1>B2),100*A1/A2,
调整原则是：新行地址=原来行地址+行偏移量
•
新列地址=原来列地址+列偏移量
例如：将D2单元中的公式 =A2+B2+C2
复制到D4后，D4中的公式 =A4+B4+C4
2021/3/26
excel常用公式大全 ppt课件
9
公式的调整
绝对地址引用 在公式复制时不变。
➢例如：C3单元的公式=$B$2+$B$3+$B$4 ➢复制到D5中，D5单元的公式
2021/3/26
excel常用公式大全 ppt课件
18
If函数
• 1．IF函数的语法结构
IF函数的语法结构：IF(条件,结果1,结果2)。
• 2．IF函数的功能 • 对满足条件的数据进行处理，条件满足则输
出结果1，不满足则输出结果2。可以省略 结果1或结果2，但不能同时省略。
2021/3/26
excel常用公式大全 ppt课件
1,1)函数式为例来说明IF函数的执行过程。
2021/3/26
excel常用公式大全 ppt课件
20
先计算条件表达式a1+b1+50 <> b1+c1 -50，如果表达式成立，值为TRUE，并在 函数所在单元格中显示“ 1”；如果表达 式不成立，值为FALSE，并在函数所在单 元格中显示“2”。
2021/3/26
EXCEL公式和函数
公式的使用和公式的调整 工作表的格式化
2021/3/26
excel常用公式大全 ppt课件
1
公式
• Excel表格中不仅可以输入数据，
也可以使用公式对工作表中的数据 进行各种数值计算和统计分析等
• Excel的公式是由运算符号，和参
与计算的运算数组成的表达式
2021/3/26
excel常用公式大全 ppt课件