应用技巧实例2

1、Excel 2007 应 用 技 巧 实 例（二）函数的应用2011-06-18 CH LIGHTING ZHANG YU1目录常用基本函数：1、SUM （求和）， SUMIF（条件求和），SUMIFS(多条件求和）2、PRODUCT （乘积）3、AVERAGE（平均值）4、ROUND（取整，按指定小数位）5、INT（四舍五入取整）6、MOD（相除后的余数）逻辑函数：7、IF，AND ,OR8、MAX， MIN，9、RANK （排位）时期和时间函数：10、YEAR（），MONTH（），TODAY（）, datedif(),查找与引用函数：11、COUNT（含数字的单元格个数），COUNTA（文

2、本单元格个数），COUNTBLANK（空白单元格个数），COUNTIF，12、LOOKUP， HLOOKUP，VLOOKUP13、MATCH， MID，OFFSET，COLUMN,2常用基本函数：1，SUM， 求和2，SUMIF，根据条件求和，SUMIF(range,criteria,sum_range) 1）range 为用于条件判断的单元格区域。 2）criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。3）sum_range 是需要求和的实际单元格。实例：销售业绩表3常用基本函数：SUMIFS， 根据多个条件求和SUMIFS对某一区域内满足多重条件的单元格

3、求和。 SUMIFS 和 SUMIF 的参数顺序不同。具体而言，sum_range 参数在 SUMIFS 中是第一个参数，而在 SUMIF 中则是第三个参数。语法：SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)Sum_range 是要求和的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。空值和文本值会被忽略。Criteria_range1, criteria_range2, 是计算关联条件的 1 至 127 个区域。Criteria1, criteria2, 是数字、表达式、单元格引用或文本

4、形式的 1 至 127 个条件，用于定义要对哪些单元格求和实例：求一班的男生的语文总分数4常用基本函数：2、PRODUCT （乘积）3、AVERAGE（平均值）4、ROUND（取整，按指定小数位）实例：学生成绩表2求平均分并保留一位小数5常用基本函数：5、INT（四舍五入）6、MOD（相除后的余数）实例：员工工资明细表计算发工资时所需的人民币张数，6常用基本函数：语法RANK(number, ref, order) Number 必须有一个数字值 ； ref 必须是一个数组或单元格区域包含数字数据值 ； 是可选的 order。如果省略 order，或者将它分配一个值为 0 （零） number

5、 排位是数字的数字的在 ref 中 number 位置，如果 ref 数字的数字的按降序顺序排序。如果 order 分配任何非零值，假定 ref 为排名按升序排序。实例：销售业绩表3使用RANK函数对销售业绩排名7逻辑函数：MAX（） ， MIN（）返回指定区域中的最大值、最小值实例：项目计划表8逻辑函数：IF 函数IF(logical_test,value_if_true,value_if_false) Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。Value_if_true logical_test 为 TRUE 时返回的值。Value_if_fal

6、se logical_test 为 FALSE 时返回的值。实例1：图书借阅表实例2：员工工龄及年假（自动计算员工工龄及休假天数）实例3：商品销售信息2（if 与sum 组合使用）9逻辑函数：AND ，OR 函数AND(logical1,logical2, .) Logical1, logical2, . 表示待检测的 1 到 30 个条件值，各条件值可为 TRUE 或 FALSE。所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FLASE。实例：学生成绩表5使用IF函数和AND函数评定三好学生10时期和时间函数：YEAR（），MONTH（），TODAY（）实例：商品

7、销售报表在销售报表中自动填写当前年份和月份11时期和时间函数：datedif函数DATEDIF(start_date,end_date,unit) Excel隐藏函数，在帮助和插入公式里面没有。DATEDIF(start_date,end_date,unit) Start_date 为一个日期，它代表时间段内的第一个日期或起始日期。 End_date 为一个日期，它代表时间段内的最后一个日期或结束日期。 Unit 为所需信息的返回类型。 Y 时间段中的整年数。 M 时间段中的整月数。 D 时间段中的天数。 MD start_date 与 end_date 日期中天数的差。忽略日期中的月和年。

8、YM start_date 与 end_date 日期中月数的差。忽略日期中的日和年。 YD start_date 与 end_date 日期中天数的差。忽略日期中的年。12时期和时间函数：实例1： 题目： 计算出生日期为1956-4-1人的年龄 公式： =DATEDIF(1956-4-1,TODAY(),Y) NETWORKDAYS（计算两个日期之间的工作日）NETWORKDAYS(start_date,end_date,holidays) HOUR()， MINUTE()13查找与引用函数：COUNT(value1,value2, .) 查找区域中数字个数COUNTA（alue1,valu

9、e2, .）查找区域中文本个数COUNTBLANK（range）查找区域中空值个数COUNTIF(range,criteria)查找区域中符合条件的值的个数使用COUNT函数统计数字个数 例：加班统计表使用COUNTIF函数按性别统计员工人数例：员工基本信息表14查找与引用函数：LOOKUP函数LOOKUP(lookup_value,lookup_vector,result_vector)lookup_value函数LOOKUP在第一个向量中所要查找的数值，它可以为数字、文本、逻辑值或包含数值的名称或引用； lookup_vector只包含一行或一列的区域lookup_vector 的数值可以

10、为文本、数字或逻辑值； LOOKUP_vector 的数值必须按升序排序。result_vector只包含一行或一列的区域其大小必须与lookup_vector 相同。实例：销售业绩表6使用LOOKUP函数查询各地区销售情况15查找与引用函数：HLOOKUP函数HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)实例：业绩奖金核算表16查找与引用函数：VLOOKUP函数VLOOKUP(lookup_value,table_array,row_index_num,range_lookup) 17查找与引用函数：18查找与引用函数

11、：实例2：员工工资表使用VLOOKUP函数批量生成工资条COLUMN(reference)公式 说明 （结果） =COLUMN() 公式所在的列 (1) =COLUMN(A10) 引用的列 (1)19查找与引用函数：Index函数（使用INDEX函数引用指定区域的内容）语法：INDEX(array，row_num，column_num)参数：Array为单元格区域或数组常数；Row_num为数组中某行的行序号，函数从该行返回数值。如果省略row_num，则必须有column_num；Column_num是数组中某列的列序号，函数从该列返回数值。如果省略column_num，则必须有row_num。实例：日