Excel 2007 操作培训—常用函数应用.

Presentation on theme: "Excel 2007 操作培训—常用函数应用."— Presentation transcript:

1 Excel 2007 操作培训—常用函数应用

2 本次课程主要内容 函数的基础知识 1 人力资源工作总结与计划 1 2 2 行政工作总结与计划 常用函数应用技巧

3 第一章 函数的基础知识 1.1 公式中的运算符 1.2 单元格的引用 1.3 函数的输入与编辑 1.4 函数的分类

4 第一章 函数的基础知识 1.1 公式中的运算符 算术运算符 比较运算符 算数运算符 说明 + 加法 - 减法 * 乘法 / 除法 % 百分比
1.1 公式中的运算符 算术运算符 比较运算符 算数运算符 说明 + 加法 - 减法 * 乘法 / 除法 % 百分比 ^ 乘方 比较运算符 说明 = 等号 ＞ 大于号 ＜ 小于号 ＞＝ 大于等于号 ＜＝ 小于等于号 ＜＞ 不等号 IBM Confidential

5 第一章 函数的基础知识 1.1 公式中的运算符 文本运算符 引用运算符 文本运算符 说明 & 多个文本字符串，组合成一个文本显示 引用运算符
公式中的运算符 文本运算符 引用运算符 文本运算符 说明 & 多个文本字符串，组合成一个文本显示 引用运算符 说明 ，（逗号） 引用不相邻的多个单元格 ：（冒号） 引用相邻的多个单元格 （空格） 引用选定的多个单元格的交叉区域 IBM Confidential

6 第一章 函数的基础知识 1.1 公式中的运算符 运算符优先级 优先级 运算种类 1 % 2 ^ 3 *或/ 4 +或- 5 & 6
1.1 公式中的运算符 运算符优先级 优先级 运算种类 1 % 2 ^ 3 *或/ 4 +或- 5 & 6 比较运算符（=、＞、＜……） IBM Confidential

7 第一章 函数的基础知识 1.2 单元格的引用 相对引用 相对引用指公式中的单元格位置将随着公式单元格的位置而改变，如A4 绝对引用 混合引用
1.2 单元格的引用 相对引用 相对引用指公式中的单元格位置将随着公式单元格的位置而改变，如A4 绝对引用 绝对引用是指公式和函数中的位置是固定不变的. 绝对引用是在列字母和行数字之前都加上美元符号”$”，如$A$4，$C$6 混合引用 混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用. 例：行变列不变：$A4 列变行不变：A$4 小窍门：引用方式之间的快速切换 在输入公式的过程中，选定单元格，重复按“F4”，单元格号就会发生以下变化：“A2”→“$A$2”→“A$2”→“$A2” IBM Confidential

8 第一章 函数的基础知识 1.2 单元格的引用 当前工作表之外的引用 引用格式：工作表名称!单元格地址。 多个工作薄中的引用
单元格的引用 当前工作表之外的引用 引用格式：工作表名称!单元格地址。 多个工作薄中的引用 引用格式：[工作薄名称]工作表名！数据源地址 说明：以上两种引用形式一般是直接所需要引用的单元格，格式就会自动生成。 IBM Confidential

9 第一章 函数的基础知识 1.3 函数的输入与编辑 输入函数 修改函数 删除函数 使用“插入函数”对话框
函数的输入与编辑 输入函数 使用“插入函数”对话框 直接输入函数：使用编辑栏；直接在单元格中输入 修改函数 在编辑栏或单元格中直接修改 删除函数 单个单元格函数的删除：Backspace或Delete 多个单元格函数的删除：Delete IBM Confidential

10 第一章 函数的基础知识 1.4 函数的分类 函数类型 函数符号 逻辑函数 IF、AND、OR、NOT 数学函数
函数的分类 函数类型 函数符号 逻辑函数 IF、AND、OR、NOT 数学函数 SUM、PRODUCT、INT、ROUND、ABS、 统计函数 COUNT、AVERAGE、RANK、MAX、MIN 日期函数 DATE、TIME、TODAY、NOW、DATEDIF 文本函数 LEFT、RIGHT、MID、LEN、CONTATENATE、TEXT、REPLACE 查找与引用函数 VLOOKUP、LOOKUP IBM Confidential

11 第二章 常用函数的应用技巧 2.1 逻辑函数 2.2 数学函数 2.3 统计函数 2.4 日期函数 2.5 文本函数 2.6 查找与引用函数

12 2.1 逻辑函数 IF函数： 功能：执行真假值判断，根据逻辑测试的真假值返回不同的结果
2.1 逻辑函数 IF函数： 功能：执行真假值判断，根据逻辑测试的真假值返回不同的结果 格式： IF(Logical_test,Value_if_true,Value_if_false) AND函数： 功能：指定的多个条件式全部成立，则返回TRUE。 格式： ： AND(Logical1,Logical2,...) OR函数： 功能：指定的多个条件式中一个或一个以上的条件成立，则返还TRUE。 格式： OR(Logical1,Logical2,...) NOT函数： 功能：指定条件式不成立，则返回TRUE。 格式： NOT(Logical) IBM Confidential

13 2.2 数学函数 SUM函数： 功能：计算单元格区域中所有数值的和 格式： SUM(Number1,Number2,...)
2.2 数学函数 SUM函数： 功能：计算单元格区域中所有数值的和 格式： SUM(Number1,Number2,...) SUMIF函数： 功能：对满足单一指定条件的单元格求和 格式： SUMIF(Range,Criteria,Sum_range) SUMIFS函数： 功能：对满足多个指定条件的单元格求和 格式： SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) PRODUCT函数： 功能：计算所有参数的乘积 格式：PRODUCT(Number1,Number2,...) SUMPRODUCT 函数： 功能1：①将数组中对应的元素相乘，并返回乘积之和； 格式：SUMPRODUCT（array1,array2,array3, ...） 功能2：用于计算符合2个及以上条件的数据个数（与COUNTIFS函数功能类似）； 格式：SUMPRODUCT（条件1*条件2*……） 功能3：用于计算符合多个条件的数据求和（与SUMIFS函数功能类似）； 格式： SUMPRODUCT（条件1*条件2*……，求和数据区域）. IBM Confidential

14 2.2 数学函数 INT函数： 功能：对数值向下取整 格式：INT(Number) ABS 函数： 功能：求数值的绝对值
2.2 数学函数 INT函数： 功能：对数值向下取整 格式：INT(Number) ABS 函数： 功能：求数值的绝对值 格式： ABS（number） ROUND函数： 功能：按指定位数对数值四舍五入 格式： ROUND(Number,Num_digits) 参数： 位数 四舍五入的位置 正数 n 在小数点后第n+1位进行四舍五入 在小数点后第1位进行四舍五入 负数 n 在整数第n位四舍五入 省略位数 不能省略 IBM Confidential

15 2.3 统计函数 COUNT函数： 功能：计算数值数据的个数 格式：COUNT((Number1,Number2,...)
2.3 统计函数 COUNT函数： 功能：计算数值数据的个数 格式：COUNT((Number1,Number2,...) COUNTA函数： 功能：计算指定单元格区域中非空单元格的个数，包含文本或逻辑值。 格式：COUNTA(Value1,Value2,...) COUNTBLANK函数： 功能：计算空白单元格的个数 格式： COUNTBLANK(Range) COUNTIF函数： 功能：计算满足指定条件的数据个数 格式： COUNTIF(Range,Criteria) COUNTIFS函数： 功能：计算多个区域中满足给定条件的单元格的个数。 格式： COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,…) 备注：COUNT与COUNTA函数的区别之处在于，COUNTA函数中数值以外的文本或逻辑值可以算作统计数据的个数。 IBM Confidential

16 2.3 统计函数 AVERAGE函数： 功能：计算参数的平均值 格式： AVERAGE(Number1,Number2,...)
2.3 统计函数 AVERAGE函数： 功能：计算参数的平均值 格式： AVERAGE(Number1,Number2,...) AVERAGEA函数： 功能：计算参数列表中非空单元格中数值的平均值求参数的平均值 格式： AVERAGEA(Value1,Value2,...) AVERAGEIF函数： 功能：计算某个区域内满足给定条件的所有单元格的平均值 格式： AVERAGEIF(Range,Criteria,Average_range) AVERAGEIFS函数： 功能：计算满足多个给定条件的所有单元格的平均值 格式： AVERAGEIFS(所求平均值区域，区域1，条件1，区域2，条件2……) 演示一下AVERAGE和AVERAGEA函数的区别 IBM Confidential

17 2.3 统计函数 RANK函数： 功能：返回一个数值在一组数值中的排位 格式： RANK(nubmer,ref,order)
2.3 统计函数 RANK函数： 功能：返回一个数值在一组数值中的排位 格式： RANK(nubmer,ref,order) 参数： nubmer:指定需找到排位的数值或者数值所在的单元格； ref：指定包含数值的单元格区域或者区域名称； order：指明排位的方式，1代表升序，0代表降序，如果省略则用降序排位。 MAX函数： 功能：返回一组值中的最大值 格式： MAX(Number1,Number2,...) MIN函数： 功能：返回一组值中的最小值 格式： MIN(Number1,Number2,...) 演示一下AVERAGE和AVERAGEA函数的区别 IBM Confidential

18 2.4 日期函数 类别 函数 格式 功能 当前日期 TODAY TODAY() 返回当前的日期 NOW NOW() 返回当前的日期和时间
2.4 日期函数 类别 函数 格式 功能 当前日期 TODAY TODAY() 返回当前的日期 NOW NOW() 返回当前的日期和时间 用序列号表示日期 YEAR YEAR(Serial_number) 返回某日期序对应的年份数 MONTH MONTH(Serial_number) 返回某日期对应的月份数 DAY DAY(Serial_number) 返回某日期对应的月份数中的日序数 WEEKDAY WEEKDAY(serial_number，return_type) 返回某日期的星期数。 特定日期的序列号 DATE DATE(Year,Month,Day) 求以年、月、日表示的日期的序列号 TIME TIME(hour,minute,second) 返回特定时间的序列号 期间差 DATEDIF DATEDIF(start_date,end_date,unit) 用指定的单位计算两个日期之间的天数 DATEDIF代码说明： Y:两个日期之间间隔的年数；M：两个日期之间间隔的月数；D：两个日期之间间隔的天数； YM:两个日期之间的月份，不计年分差；YD：两个日期之间间隔的天数，忽略年数；MD:两个日期之间不计年份和月份的天。 WEEKDAY函数返回值：在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数。serial_number 是要返回日期数的日期，return_type为确定返回值类型的数字，数字1 或省略则1 至7 代表星期天到星期六，数字2 则1 至7 代表星期一到星期天，数字3则0至6代表星期一到星期天。 备注：DATEDIF函数代码说明 Y：两个日期之间间隔的年数；M：两个日期之间间隔的月数；D：两个日期之间间隔的天数； YM：两个日期之间的月份，不计年分差；YD：两个日期之间间隔的天数，忽略年数； MD：两个日期之间不计年份和月份的天。 IBM Confidential

19 2.5 文本函数 类别 函数 格式 功能 提取部分文本字符串 LEFT LEFT(Text,Num_chars)
2.5 文本函数 类别 函数 格式 功能 提取部分文本字符串 LEFT LEFT(Text,Num_chars) 从一个文本字符串的第一个字符开始返回指定个数的字符 RIGHT RIGHT(Text,Num_chars) 从字符串的最后一个字符开始返回指定字符数的字符 MID MID(Text,Start_num,Num_chars) 从字符串中指定的位置起返回指定长度的字符 文本长度 LEN LEN(Text) 返回文本字符串的字符数 合并文本 CONTATENATE CONTATENATE(Text1,Text2,……) 将多个文本字符合并成一个 数字转换为文本 TEXT TEXT(Value,Format_text) 将数值转换为按指定数值格式表示的文本 替代文本字符串 REPLACE REPLACE(Old_text,Start_num,Num_chars,New_text) 按照指定的字符位置和字节数，将指定的字符串替换为另一个文本字符串 SUBSTITUTE SUBSTITUTE(Text,Old_text,New_text,Instance_num) 用新字符串替换字符窜中的部分字符串。 演示一下REPLACE和SUBSTITUTE函数区别. SUBSTITUTE函数中的参数instance_num 为一数值，用来指定以new_text 替换第几次出现的old_text；如果指定了instance_num，则只有满足要求的old_text 被替换；否则将用new_text 替换Text 中出现的所有old_text。 备注：REPLACE和SUBSTITUTE函数区别 如果需要在某一文本字符串中替换指定的文本，请使用函数 SUBSTITUTE； 如果需要在某一文本字符串中替换指定位置处的任意文本，请使用函数REPLACE。 IBM Confidential

20 2.6 查找与引用函数 VLOOKUP函数： 功能：在首列查找数值，并返回当前行中指定列处的数值。
2.6 查找与引用函数 VLOOKUP函数： 功能：在首列查找数值，并返回当前行中指定列处的数值。 格式： VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup) 参数： ①需要查找的数值；②指定查找范围；③返回匹配值得列序号；④用TRUE或FALSE指定查找方法。如果为TRUE或省略，则返回近似匹配值；如果为FALSE或0，则返回精确匹配值，如果找不到，则返回错误值“#N/A”。 HLOOKUP函数： 功能：在首行查找数值，并返回当前列中指定行处的数值。 格式： HLOOKUP(Lookup_value,Table_array,Row_index_num,Range_lookup) LOOKUP函数（向量形式） 功能：在向量中查找一个值 格式： LOOKUP(lookup_value,lookup_vector,result_vector) VLOOKUP函数（数组形式）： 功能：在数组中查找一个值 格式： LOOKUP(lookup_value,array) 向量是指含一行或一列的区域，LOOKUP函数的向量型是指在单行区域或单列区域（成为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。数组型是在第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处得数值。 IBM Confidential

21 卓越服务 快乐生活 THANK YOU ! 20% 战略功能