Excel在日常工作中的高效应用幻灯片
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
函数与公式既有区别又互相联系。如果说前者是Excel 预先定义好的特殊公式，后者就是由用户自行设 计对工作表进行计算和处理的公式。以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内 部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1 的引 用(使用其中存储的数据)，“26”则是常量，“*” 和“+”则是算术运算符(另外还有比较运算符、文本运 算符和引用运算符)。
Excel中的照相功能，可以将被选择的数据区域“拍照”成为图片。 换句话说，使用Excel中的照相功能，可以把选中的数据区域，转换 成为图片。
Office中高级培训
Page 10
Excel 2.函数公式在日常工作中的运用
函数和公式
1 .什么是函数 Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM 为例，
Office中高级培训
Page 5
Excel 1.高效管理数据技巧 根据单元格数据内容有条件设置单元格格式 使用条件格式按单元格数值大小改变单元格格式
Office中高级培训
Page 6
Excel 1.高效管理数据技巧 根据单元格数据内容有条件设置单元格格式 使用公式做为条件设置复杂的单元格格式：到期提醒
=IF(A4>89,"A",IF(A4>79,"B", IF(A4>69,"C",IF(A4>59,"D","F")))) 为第三个成绩指定一个字母等级 (C)
Office中高级培训
Page 15
Excel 2.函数公式在日常工作中的运用
Sumif
SUMIF(range, criteria, [sum_range]) SUMIF 函数语法具有以下参数： •range 必需。用于条件计算的单元格区域。每个区域中的单元格都必须是数字 或名称、数组或包含数字的引用。空值和文本值将被忽略。 •criteria 必需。用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、 单元格引用、文本或函数。例如，条件可以表示为 32、">32"、B5、"32"、"苹 果" 或 TODAY()。 •重要 任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号 (") 括起 来。 如果条件为数字，则无需使用双引号。 •sum_range 可选。要求和的实际单元格（如果要对未在 range 参数中指定的 单元格求和）。如果省略 sum_range 参数，Excel 会对在 range 参数中指定的 单元格（即应用条件的单元格）求和。 •可以在 criteria 参数中使用通配符 （包括问号 (?) 和星号 (*)）。问号匹配任意 单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符 前键入波形符 (~)。
Day Date today Datedif Networkdays Weekday Weeknum Hour Minute Second Now
Office中高级培训
文本函数
Left Right Mid Find Text Len
Page 14
Excel 2.函数公式在日常工作中的运用
if
用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务 ；
Office中高级培训
Page 13
Excel 2.函数公式在日常工作中的运用
统计类函数
Sum Average
Count Max Min Sumif Sumifs Countif Countifs Counta Averageif Averageifs Sumproduct
时间日期函数
Year Month
Page 18
Excel 2.函数公式在日常工作中的运用
Vlookup
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) 例如：
自定义快捷键 可使用自定义宏加设置快捷键方式
Office中高级培训
Page 3
Excel 1.高效管理数据技巧 创建下拉菜单
路径：数据->数据有效性
Office中高级培训
Page 4
Excel 1.高效管理数据技巧 实现表格某些部分别人不能编辑，但是某些数据区需要别人填数据
关键步骤:1.全部单元格变成未锁定 2.需要保护的单元格变成锁定 3.审阅保护工作表，只勾选：选定未锁定的单元格
实例：
A1 序号 成绩
A2 2
45
A3 3
90
A4 4
78
公式 说明（结果）
=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F")))) 为第一个成绩指定一个字母等级 (F)
=IF(A3>89,"A",IF(A3>79,"B", IF(A3>69,"C",IF(A3>59,"D","F")))) 为第二个成绩指定一个字母等级 (A)
如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数 本身。
Office中高级培训
Page 11
Excel 2.函数公式在日常工作中的运用
函数的参数
参数可以是常量(数字和文本)、逻辑值(例如TRUE 或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如 E1:H1)， 甚至可以是另一个或几个函数等 。 常量这里就不多叙述，大家应该见得很多，比如数字“012”、文本“星愿宝”等，都是常量，可以作为参数； 1. 逻辑值 ：
Office中高级培训
Page 16
Excel 2.函数公式在日常工作中的运用
Sumifs SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) =SUMIFS(A2:A9,B2:B9,"=A*",C2:C9,"卢宁") =SUMIFS(A2:A9,B2:B9,"<>香蕉",C2:C9,"卢宁")
逻辑值是比较特殊的一类参数，它只有TRUE(真)或FALSE(假)两种类型。 例如在公式“=IF(A3=0,"",A2/A3)”中，“A3=0”就是一个可以返回TRUE(真)或FALSE(假)两种结果的参 数。当“A3=0”为TRUE(真)时在公式所在单元格中填入“0”，否则在单元格中填入“A2/A3”的计算结果。 2. 数组： 数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。Excel 中有常量和区域两 类数组。前者放在“{}”(按下Ctrl+Shift+Enter 组合键自动生成)内部，而且内部各列的数值要用逗号“,”隔开， 各行的数值要用分号“;”隔开。假如你要表示第1 行中的56、78、89 和第2 行中的90、76、80，就应该建立 一个2 行3 列的常量数组“{56,78,89;90,76,80}。 区域数组是一个矩形的单元格区域，该区域中的单元格共用 一个公式。例如公式“=TREND(B1:B3,A1:A3)”作为数组公式使用时，它所引用的矩形单元格区域 “B1:B3,A1:A3”就是一个区域数组。 3 .错误值： 使用错误值作为参数的主要是信息函数，例如“ERROR.TYPE”函数就是以错误值作为参数。 它的语法为“ERROR.TYPE(error_val)”， 如果其中的参数是#NUM!，则返回数值“6”。
Office中高级培训
Page 12
Excel 2.函数公式在日常工作中的运用
函数的参数
5. 单元格引用 单元格引用是函数中最常见的参数，我们可以引用分为相对引用、绝对引用和混合引用三种类型。以存
放在F2 单元格中的公式“=SUM(A2:E2)”为例，当公式由F2 单元格复制到F3 单元格以后，公式中的引用 也会变化为“=SUM(A3:E3)”。如果上述公式改为“=SUM($A $3:$E ＄3)”， 则无论公式复制到何处， 其引用的位置始终是“A3:E3”区域。混合引用有“绝对列和相对行”，或是“绝对行和相对列”两种形式。 前者如“=SUM($A3:$E3)”，后者如“=SUM(A$3:E$3)”。 提示：上面介绍的是Excel 默认的引用方式， 称为“A1引用样式”。如果你要计算处在“宏”内的行和列，必须使用“R1C1 引用样式”。在这种引用 样式中，Excel使用“R”加“行标”和“C”加“列标”的方法指示单元格位置。 6. 嵌套函数
特有快捷键 口 设置单元格格式 ctrl+1 口 转换单元格引用方式 f4 口 数组公式 ctrl+shift+enter 口 扩展选定区域 ctrl+shift+方向 口 单元格内换行 alt+enter 口 添加批注 shift+f2 口 移至表格开始位置 ctrl+home 口 多单元格填入相同内容：ctrl+enter
Office软件中高级培训之——
Excel在日常工作中的高效应用
1
Excel
目录
12
ຫໍສະໝຸດ Baidu
34
操作技巧 公式函数
数据透视表 图表与VBA
Office中高级培训
Page 2
Excel 1.高效管理数据技巧 快捷键运用技巧
通用快捷键 口 复制 ctrl+c 口 剪切 ctrl+x 口 全选 ctrl+a 口 粘贴 ctrl+v 口 撤销 ctrl+z 口 重做 ctrl+y 口 查找 ctrl+f 口 打印 ctrl+p 口 替换 ctrl+H 口 返回桌面 Windows+P
它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称，一个函数只有唯一的一个 名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后 用一个右括号表示函数结束。
参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元 格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。 按照函数的来源，Excel 函数 可以分为内置函数和扩展函数两大类。前者只要启动了Excel， 用户就可以使用它们；而后者必须通过单击 “工具→加载宏”菜单命令加载，然后才能像内置函数那样使用。 2 .什么是公式
语法：IF(logical_test，value_if_true，value_if_false)；
参数：Logical_test 计算结果为TRUE 或FALSE 的任何数值或表达式；而且Value_if_true 和 Value_if_false都可以是一个表
达式；如果logical_test 为FALSE 并且省略value_if_false， 则返回FALSE。
条件格式的拓展应用：1. 表格样式自动生成
Office中高级培训
Page 7
Excel 1.高效管理数据技巧 重复数据与缺失数据的处理
Office中高级培训
Page 8
Excel 1.高效管理数据技巧 数据维护：共享工作簿
Office中高级培训
Page 9
Excel 1.高效管理数据技巧
照相机的功能
除了上面介绍的情况外，函数也可以是嵌套的，即一个函数是另一个函数的参数，例如 “=IF(RIGHT(E2,1)=”1“，男”,“女”)”。 7. 名称和标志
为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名称，从而在公式或函数中直接引 用。由于Excel 工作表多数带有“列标志”。例如一张成绩统计表的首行通常带有“序号”、“姓名”、 “数学”、“物理”等“列标志”(也可以称为字段)，如果单击“工具→选项”菜单命令，在打开的对话框 中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引 用“列标志”了。例如“B2:B46”区域存放着学生的物理成绩，而B1 单元格已经输入了“物理”字样，则 求物理平均分的公式可以写成“=AVERAGE(物理)”。
Office中高级培训
Page 17
Excel 2.函数公式在日常工作中的运用
逻辑类函数
查找引用函数
If And Or Iserror
Vlookup Index Match Offset
Indirect
其他函数
Colum Row Rank Mod
Round Rounddown
Roundup
Office中高级培训
Excel中的照相功能，可以将被选择的数据区域“拍照”成为图片。 换句话说，使用Excel中的照相功能，可以把选中的数据区域，转换 成为图片。
Office中高级培训
Page 10
Excel 2.函数公式在日常工作中的运用
函数和公式
1 .什么是函数 Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM 为例，
Office中高级培训
Page 5
Excel 1.高效管理数据技巧 根据单元格数据内容有条件设置单元格格式 使用条件格式按单元格数值大小改变单元格格式
Office中高级培训
Page 6
Excel 1.高效管理数据技巧 根据单元格数据内容有条件设置单元格格式 使用公式做为条件设置复杂的单元格格式：到期提醒
=IF(A4>89,"A",IF(A4>79,"B", IF(A4>69,"C",IF(A4>59,"D","F")))) 为第三个成绩指定一个字母等级 (C)
Office中高级培训
Page 15
Excel 2.函数公式在日常工作中的运用
Sumif
SUMIF(range, criteria, [sum_range]) SUMIF 函数语法具有以下参数： •range 必需。用于条件计算的单元格区域。每个区域中的单元格都必须是数字 或名称、数组或包含数字的引用。空值和文本值将被忽略。 •criteria 必需。用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、 单元格引用、文本或函数。例如，条件可以表示为 32、">32"、B5、"32"、"苹 果" 或 TODAY()。 •重要 任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号 (") 括起 来。 如果条件为数字，则无需使用双引号。 •sum_range 可选。要求和的实际单元格（如果要对未在 range 参数中指定的 单元格求和）。如果省略 sum_range 参数，Excel 会对在 range 参数中指定的 单元格（即应用条件的单元格）求和。 •可以在 criteria 参数中使用通配符 （包括问号 (?) 和星号 (*)）。问号匹配任意 单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符 前键入波形符 (~)。
Day Date today Datedif Networkdays Weekday Weeknum Hour Minute Second Now
Office中高级培训
文本函数
Left Right Mid Find Text Len
Page 14
Excel 2.函数公式在日常工作中的运用
if
用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务 ；
Office中高级培训
Page 13
Excel 2.函数公式在日常工作中的运用
统计类函数
Sum Average
Count Max Min Sumif Sumifs Countif Countifs Counta Averageif Averageifs Sumproduct
时间日期函数
Year Month
Page 18
Excel 2.函数公式在日常工作中的运用
Vlookup
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) 例如：
自定义快捷键 可使用自定义宏加设置快捷键方式
Office中高级培训
Page 3
Excel 1.高效管理数据技巧 创建下拉菜单
路径：数据->数据有效性
Office中高级培训
Page 4
Excel 1.高效管理数据技巧 实现表格某些部分别人不能编辑，但是某些数据区需要别人填数据
关键步骤:1.全部单元格变成未锁定 2.需要保护的单元格变成锁定 3.审阅保护工作表，只勾选：选定未锁定的单元格
实例：
A1 序号 成绩
A2 2
45
A3 3
90
A4 4
78
公式 说明（结果）
=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F")))) 为第一个成绩指定一个字母等级 (F)
=IF(A3>89,"A",IF(A3>79,"B", IF(A3>69,"C",IF(A3>59,"D","F")))) 为第二个成绩指定一个字母等级 (A)
如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数 本身。
Office中高级培训
Page 11
Excel 2.函数公式在日常工作中的运用
函数的参数
参数可以是常量(数字和文本)、逻辑值(例如TRUE 或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如 E1:H1)， 甚至可以是另一个或几个函数等 。 常量这里就不多叙述，大家应该见得很多，比如数字“012”、文本“星愿宝”等，都是常量，可以作为参数； 1. 逻辑值 ：
Office中高级培训
Page 16
Excel 2.函数公式在日常工作中的运用
Sumifs SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) =SUMIFS(A2:A9,B2:B9,"=A*",C2:C9,"卢宁") =SUMIFS(A2:A9,B2:B9,"<>香蕉",C2:C9,"卢宁")
逻辑值是比较特殊的一类参数，它只有TRUE(真)或FALSE(假)两种类型。 例如在公式“=IF(A3=0,"",A2/A3)”中，“A3=0”就是一个可以返回TRUE(真)或FALSE(假)两种结果的参 数。当“A3=0”为TRUE(真)时在公式所在单元格中填入“0”，否则在单元格中填入“A2/A3”的计算结果。 2. 数组： 数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。Excel 中有常量和区域两 类数组。前者放在“{}”(按下Ctrl+Shift+Enter 组合键自动生成)内部，而且内部各列的数值要用逗号“,”隔开， 各行的数值要用分号“;”隔开。假如你要表示第1 行中的56、78、89 和第2 行中的90、76、80，就应该建立 一个2 行3 列的常量数组“{56,78,89;90,76,80}。 区域数组是一个矩形的单元格区域，该区域中的单元格共用 一个公式。例如公式“=TREND(B1:B3,A1:A3)”作为数组公式使用时，它所引用的矩形单元格区域 “B1:B3,A1:A3”就是一个区域数组。 3 .错误值： 使用错误值作为参数的主要是信息函数，例如“ERROR.TYPE”函数就是以错误值作为参数。 它的语法为“ERROR.TYPE(error_val)”， 如果其中的参数是#NUM!，则返回数值“6”。
Office中高级培训
Page 12
Excel 2.函数公式在日常工作中的运用
函数的参数
5. 单元格引用 单元格引用是函数中最常见的参数，我们可以引用分为相对引用、绝对引用和混合引用三种类型。以存
放在F2 单元格中的公式“=SUM(A2:E2)”为例，当公式由F2 单元格复制到F3 单元格以后，公式中的引用 也会变化为“=SUM(A3:E3)”。如果上述公式改为“=SUM($A $3:$E ＄3)”， 则无论公式复制到何处， 其引用的位置始终是“A3:E3”区域。混合引用有“绝对列和相对行”，或是“绝对行和相对列”两种形式。 前者如“=SUM($A3:$E3)”，后者如“=SUM(A$3:E$3)”。 提示：上面介绍的是Excel 默认的引用方式， 称为“A1引用样式”。如果你要计算处在“宏”内的行和列，必须使用“R1C1 引用样式”。在这种引用 样式中，Excel使用“R”加“行标”和“C”加“列标”的方法指示单元格位置。 6. 嵌套函数
特有快捷键 口 设置单元格格式 ctrl+1 口 转换单元格引用方式 f4 口 数组公式 ctrl+shift+enter 口 扩展选定区域 ctrl+shift+方向 口 单元格内换行 alt+enter 口 添加批注 shift+f2 口 移至表格开始位置 ctrl+home 口 多单元格填入相同内容：ctrl+enter
Office软件中高级培训之——
Excel在日常工作中的高效应用
1
Excel
目录
12
ຫໍສະໝຸດ Baidu
34
操作技巧 公式函数
数据透视表 图表与VBA
Office中高级培训
Page 2
Excel 1.高效管理数据技巧 快捷键运用技巧
通用快捷键 口 复制 ctrl+c 口 剪切 ctrl+x 口 全选 ctrl+a 口 粘贴 ctrl+v 口 撤销 ctrl+z 口 重做 ctrl+y 口 查找 ctrl+f 口 打印 ctrl+p 口 替换 ctrl+H 口 返回桌面 Windows+P
它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称，一个函数只有唯一的一个 名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后 用一个右括号表示函数结束。
参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元 格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。 按照函数的来源，Excel 函数 可以分为内置函数和扩展函数两大类。前者只要启动了Excel， 用户就可以使用它们；而后者必须通过单击 “工具→加载宏”菜单命令加载，然后才能像内置函数那样使用。 2 .什么是公式
语法：IF(logical_test，value_if_true，value_if_false)；
参数：Logical_test 计算结果为TRUE 或FALSE 的任何数值或表达式；而且Value_if_true 和 Value_if_false都可以是一个表
达式；如果logical_test 为FALSE 并且省略value_if_false， 则返回FALSE。
条件格式的拓展应用：1. 表格样式自动生成
Office中高级培训
Page 7
Excel 1.高效管理数据技巧 重复数据与缺失数据的处理
Office中高级培训
Page 8
Excel 1.高效管理数据技巧 数据维护：共享工作簿
Office中高级培训
Page 9
Excel 1.高效管理数据技巧
照相机的功能
除了上面介绍的情况外，函数也可以是嵌套的，即一个函数是另一个函数的参数，例如 “=IF(RIGHT(E2,1)=”1“，男”,“女”)”。 7. 名称和标志
为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名称，从而在公式或函数中直接引 用。由于Excel 工作表多数带有“列标志”。例如一张成绩统计表的首行通常带有“序号”、“姓名”、 “数学”、“物理”等“列标志”(也可以称为字段)，如果单击“工具→选项”菜单命令，在打开的对话框 中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引 用“列标志”了。例如“B2:B46”区域存放着学生的物理成绩，而B1 单元格已经输入了“物理”字样，则 求物理平均分的公式可以写成“=AVERAGE(物理)”。
Office中高级培训
Page 17
Excel 2.函数公式在日常工作中的运用
逻辑类函数
查找引用函数
If And Or Iserror
Vlookup Index Match Offset
Indirect
其他函数
Colum Row Rank Mod
Round Rounddown
Roundup
Office中高级培训