excel函数实例应用教程(1).xls

文文本本与与数数字字函函数数之之 AscTRUE为近似匹配 3 3 实实例例1 1 题题目目 提取数据B10 D15中寻找月份第四行数值100 一月二月三月行1寻找月份 二月 108097行2需要挑出的行 4 209069行3结果是 100 3010045行4 4011051行5 5012077行6 公公式式 HLOOKUP G10 B 10 D 15 G11 FALSE 结结果果 100 简简要要说说明明根据 寻找月份 提取 数据区 的 需要挑出的行 第四行月份数据 采用精确匹配0 4 4 实实例例2 2 题题目目 在价格表中求项目的单价 价格表 砖砖木木材材玻玻璃璃行1项目单价 0 501 002 50行2砖0 50 公公式式 HLOOKUP F24 B 23 D 24 2 0 结结果果 0 5 简简要要说说明明 根据 项目 提取 价格表 的 需要挑出的行 第二行数据 采用精确匹配0 5 5 实实例例3 3 题题目目 在数据A30 D33中 查找首行字母B 并返回同列第三行的值 AxlesAxlesBearingsBearingsBoltsBolts行15 449行2 5710行3 6811行4 公公式式 HLOOKUP B B 30 D 33 3 TRUE 近似匹配 结结果果 5 简简要要说说明明 在首行查找字母B 并返回同列中第3行的值 由于B不是精确匹配 因此将使用小于 B 的最大值 Axles 6 6 实实例例4 4 HLOOKUPHLOOKUP函函数数数数组组的的应应用用 公公式式 HLOOKUP 3 1 2 3 A B C D E F 2 0 结结果果 C 简简要要说说明明在数组常量的第一行中查找 3 并返回同列中第 2 行的值 C 1 2 3 A B C 1 2 3 A B C D E F D E F 相相当当于于 123 ABC DEF 普普通通公公式式 HLOOKUP 3 D 42 F 44 2 0 结结果果 C 整整理理人人 ExceL ExceL精精英英培培训训网网 BIN YANG168BIN YANG168 1 2 3 A B C 1 2 3 A B C D E F D E F 相相当当于于 在表格或数值数组的首行查找指定的数值 并由此返回表格或数组当前列中指定行处的数值 HLOOKUPHLOOKUP 在数据表第一行中查找的数值 数据表 匹配值的行序号 匹配属性 匹配属性 FALSE 0 省略为精确匹配 TRUE为近似匹配 根据 寻找月份 提取 数据区 的 需要挑出的行 第四行月份数据 采用精确匹配0 根据 项目 提取 价格表 的 需要挑出的行 第二行数据 采用精确匹配0 在首行查找字母B 并返回同列中第3行的值 由于B不是精确匹配 因此将使用小于 B 的最大值 Axles 在数组常量的第一行中查找 3 并返回同列中第 2 行的值 C 简简要要说说明明 CELLCELL 返回某一引用区域的左上角单元格的格式 位置或内容等信息 语语法法 CELL info type reference CELL info type reference Info typeInfo type 为一个文本值 指定所需要的单元格信息的类型 下面列出 info type 的可能值及 相应的结果 ReferenceReference 表示要获取其有关信息的单元格 如果忽略 则在 info type 中所指定的信息将返 回给最后更改的单元格 INFOINFO 返回有关当前操作环境的信息 语语法法 INFO type text INFO type text Type textType text 为文本 指明所要返回的信息类型 CELLCELL函函数数实实例例应应用用 实实例例1 1 题题目目 CELLCELL 返回某一引用区域的左上角单元格的格式 位置或内容等信息 语语法法 CELL info type reference CELL info type reference Info typeInfo type 为一个文本值 指定所需要的单元格信息的类型 下面列出 info type 的可能值及 相应的结果 ReferenceReference 表示要获取其有关信息的单元格 如果忽略 则在 info type 中所指定的信息将返 回给最后更改的单元格 INFOINFO 返回有关当前操作环境的信息 语语法法 INFO type text INFO type text Type textType text 为文本 指明所要返回的信息类型 公公式式 结结果果 简简要要说说明明 实实例例2 2 题题目目 公公式式 结结果果 简简要要说说明明 实实例例3 3 题题目目 公公式式1 1 公公式式2 2 结结果果1 1 结结果果2 2 简简要要说说明明 综综合合运运用用 题题目目 公公式式 结结果果 简简要要说说明明 INFOINFO函函数数实实例例应应用用 实实例例1 1 题题目目 公公式式 结结果果 简简要要说说明明 实实例例2 2 题题目目 公公式式 结结果果 简简要要说说明明 CELLCELL 返回某一引用区域的左上角单元格的格式 位置或内容等信息 语语法法 CELL info type reference CELL info type reference Info typeInfo type 为一个文本值 指定所需要的单元格信息的类型 下面列出 info type 的可能值及 相应的结果 ReferenceReference 表示要获取其有关信息的单元格 如果忽略 则在 info type 中所指定的信息将返 回给最后更改的单元格 INFOINFO 返回有关当前操作环境的信息 语语法法 INFO type text INFO type text Type textType text 为文本 指明所要返回的信息类型 取取得得本本工工作作表表名名 CELLCELL 返回某一引用区域的左上角单元格的格式 位置或内容等信息 语语法法 CELL info type reference CELL info type reference Info typeInfo type 为一个文本值 指定所需要的单元格信息的类型 下面列出 info type 的可能值及 相应的结果 ReferenceReference 表示要获取其有关信息的单元格 如果忽略 则在 info type 中所指定的信息将返 回给最后更改的单元格 INFOINFO 返回有关当前操作环境的信息 语语法法 INFO type text INFO type text Type textType text 为文本 指明所要返回的信息类型 CELLCELL函函数数实实例例应应用用 REPLACE CELL FileName A1 1 FIND CELL FileName A1 REPLACE CELL FileName A1 1 FIND CELL FileName A1 CellCell 其他为近似匹配 3 3 实实例例1 1 题题目目 提取品名的单价 品品名名单单价价查查找找品品名名单单价价 稿纸5 00稿纸5 台灯15 00 桌子75 00 铅笔0 50 公公式式 VLOOKUP E12 B12 C15 2 0 结结果果 5 简简要要说说明明根据 查找品名 提取 数据区 的 第二列单价数据 采用精确匹配0 4 4 实实例例2 2 题题目目 查找某业务员对某客户的销售额 辅助列业业务务员员客客户户销销售售额额业业务务员员客客户户 张三丰商场A张三丰商场A100张三丰店铺C 张三丰超市B张三丰超市B200 李四光店铺C李四光店铺C300 李四光超市B李四光超市B400 张三丰店铺C张三丰店铺C500 李四光商场A李四光商场A600 公公式式 VLOOKUP F22 KT002 李四光 KT003 王麻子 KT004 赵六儿 可以看到 在该内存数组中 工号 位于 姓名 的左侧 Vlookup在该内存数组中查找 注意 1 0 是一行二列 横向 常量数组 后面的两个区域是多行一列 纵向 即两个数组的方向不同 这样才会生成两列多行数组 整整理理人人 ExceL ExceL精精英英培培训训网网 BIN YANG168BIN YANG168 销销售售额额 500 VLOOKUPVLOOKUP 在数据表第一列中查找的数值 数据表 匹配值的列序号 匹配属性 匹配属性 FALSE 0 省略为精确匹配 其他为近似匹配 根据 查找品名 提取 数据区 的 第二列单价数据 采用精确匹配0 利用辅助列A21 A27 扩大数据区 来查找满足2个条件的引用 VLOOKUP B40 IF 1 0 C 34 C 37 B 34 B 37 2 0 这个公式先由 IF 1 0 C 34 C 37 B 34 B 37 计算出内存数组 KT001 张三丰 KT002 李四光 KT003 王麻子 KT004 赵六儿 可以看到 在该内存数组中 工号 位于 姓名 的左侧 Vlookup在该内存数组中查找 注意 1 0 是一行二列 横向 常量数组 后面的两个区域是多行一列 纵向 即两个数组的方向不同 这样才会生成两列多行数组 整整理理人人 ExceL ExceL精精英英培培训训网网 BIN YANG168BIN YANG168 统统计计函函数数之之 COUNTIFCOUNTIF 函函数数 1 1 简简要要说说明明 计算给定区域内满足特定条件的单元格的数目 2 2 基基本本语语法法 COUNTIF COUNTIF 区区域域 条条件件 区域 为需要计算其中满足条件的单元格数目的单元格区域 条件 为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 3 3 实实例例1 1 题题目目 COUNTIFCOUNTIF基基础础运运用用 数数据据类类型型 Excel精英培训 1002 21 1 学习Excel2 22 2 2001 11 1 Excelpx 网1 12 2 606 精英财务1 12 2 1 12 2 财务学习4002 21 1 进入Excelpx 2 22 2 题题目目公公式式结结果果 求真空单元格的个数 COUNTIF B 20 C 27 3 求非真空单元格的个数 COUNTIF B 20 C 27 13 求含空格单元格的个数 SUM ASC MID B20 C26 1 1 2 求为真空 假空单元格的个数 COUNTIF B 20 C 26 4 求为假空单元格的个数 COUNTIF B 20 C 26 COUNTIF B 20 C 26 1 求为文本单元格的个数 COUNTIF B 20 C 27 9 求为数值单元格的个数 COUNT B 20 C 27 4 求包含EXCEL的单元格的个数 COUNTIF B 20 C 27 excel 4 求以EXCEL打头的单元格的个数 COUNTIF B 20 C 27 excel 2 求第三位是EXCEL的单元格的个数 COUNTIF B 20 C 27 excel 1 求大于等于200的单元格的数 COUNTIF B 20 C 27 200 3 求大于 100且小于606的单元格的 数 SUM COUNTIF B 20 C 27 100 606 1 1 3 求大于 100且小于606的单元格的 数 SUM COUNTIF B 20 C 27 100 606 1 1 2 4 4 实实例例2 2 题题目目 求求不不重重复复值值个个数数 EXCELPX EXCEL精英培训网 EXCELPX EXCEL EXCEL精英培训网 EXCELPX EXCEL精英培训网 EXCEL 公公式式 SUM 1 COUNTIF B47 B54 B47 B54 SUM 1 COUNTIF B47 B54 B47 B54 结结果果 3 3 简简要要说说明明 COUNTIF B47 B54 B47 B54 COUNTIF B47 B54 B47 B54 可可以以得得到到数数据据系系列列中中各各数数据据值值的的重重复复个个数数 再再求求其其倒倒 数数和和即即可可得得到到不不重重数数据据的的个个数数 公公式式是是数数组组公公式式 需需用用EXCELEXCEL SHIFTSHIFT ENTERENTER同同时时按按下下才才能能生生成成 5 5 实实例例3 3 题题目目 利利用用countif countif 标标记记不不重重复复的的值值 公公式式 IF COUNTIF B 69 B69 B69 1 1 IF COUNTIF B 69 B69 B69 1 1 结结果果 编号记号计算中间过程示例 a211111 a21112 a211211 a215111 a21122 a212211 a21222 a212311 a21232 a213111 a21312 a214111 a21412 a21413 a21414 a21415 a21512 a21513 a21514 简简要要说说明明 COUNTIF B 69 B69 B69 COUNTIF B 69 B69 B69 中中 B 69 B69B 69 B69固固定定首首行行的的变变动动区区域域 countifcountif计计算算值值为为1 1的的即即 为为首首次次计计数数的的值值 5 5 实实例例4 4 题题目目 利利用用countif countif 列列出出不不重重复复的的值值 数数据据为为延延用用 实实例例3 3 的的数数据据 公公式式 INDEX B 69 B 87 MATCH COUNTIF B 94 B94 B 69 B 87 INDEX B 69 B 87 MATCH COUNTIF B 94 B94 B 69 B 87 结结果果 a2111 a2112 a2151 a2122 a2123 a2131 a2141 处理错误值 处理错误值 简简要要说说明明 本本例例利利用用INDEXINDEX MATCHMATCH和和COUNTIFCOUNTIF相相结结合合 获获取取数数据据系系列列中中不不重重复复值值 是是COUNTIFCOUNTIF较较 为为综综合合的的运运用用 对对于于公公式式的的理理解解需需要要一一定定的的函函数数基基础础 列列出出本本例例的的目目的的是是让让大大家家了了解解COUNTIFCOUNTIF函函数数与与其其他他函函数数组组织织后后的的强强大大的的功功能能 编编写写后后记记 countif countif 函函数数是是EXCELEXCEL函函数数中中最最常常用用的的函函数数之之一一 在在其其基基本本用用法法下下蕴蕴含含着着十十分分丰丰富富 的的组组织织运运用用 教教程程中中列列出出该该函函数数常常基基本本用用法法 以以及及组组合合用用法法 大大家家可可以以在在熟熟练练掌掌握握基基本本 用用情情况况下下 多多多多挖挖掘掘其其组组合合用用法法 从从而而更更巧巧妙妙地地解解决决实实际际问问题题 假假空空单单元元格格为为字字符符型型 真真空空单单元元格格为为数数据据型型 含含空空格格单单元元格格为为字字符符型型 简简要要说说明明 这为一数组公式 可求出半角 全角空格 空格可能多于一个 注 不包括空格单元格 注 不包括空格单元格 注意 这里用的是count 函数 值不包括真 空单元格 所求字符可在任何位置 号代表任意字符 COUNTIFCOUNTIF 函函数数 计算给定区域内满足特定条件的单元格的数目 COUNTIF COUNTIF 区区域域 条条件件 区域 为需要计算其中满足条件的单元格数目的单元格区域 条件 为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 每一个 代表一个字符 求一区间内值的个数 求一区间内值的个数 countif函数中的条件可 以是一条件组 COUNTIF B47 B54 B47 B54 COUNTIF B47 B54 B47 B54 可可以以得得到到数数据据系系列列中中各各数数据据值值的的重重复复个个数数 再再求求其其倒倒 数数和和即即可可得得到到不不重重数数据据的的个个数数 公公式式是是数数组组公公式式 需需用用EXCELEXCEL SHIFTSHIFT ENTERENTER同同时时按按下下才才能能生生成成 整整理理人人 ExceL ExceL精精英英培培训训网网JssyJssy COUNTIF B 69 B69 B69 COUNTIF B 69 B69 B69 中中 B 69 B69B 69 B69固固定定首首行行的的变变动动区区域域 countifcountif计计算算值值为为1 1的的即即 为为首首次次计计数数的的值值 利利用用countif countif 列列出出不不重重复复的的值值 数数据据为为延延用用 实实例例3 3 的的数数据据 INDEX B 69 B 87 MATCH COUNTIF B 94 B94 B 69 B 87 INDEX B 69 B 87 MATCH COUNTIF B 94 B94 B 69 B 87 本本例例利利用用INDEXINDEX MATCHMATCH和和COUNTIFCOUNTIF相相结结合合 获获取取数数据据系系列列中中不不重重复复值值 是是COUNTIFCOUNTIF较较 为为综综合合的的运运用用 对对于于公公式式的的理理解解需需要要一一定定的的函函数数基基础础 列列出出本本例例的的目目的的是是让让大大家家了了解解COUNTIFCOUNTIF函函数数与与其其他他函函数数组组织织后后的的强强大大的的功功能能 countif countif 函函数数是是EXCELEXCEL函函数数中中最最常常用用的的函函数数之之一一 在在其其基基本本用用法法下下蕴蕴含含着着十十分分丰丰富富 的的组组织织运运用用 教教程程中中列列出出该该函函数数常常基基本本用用法法 以以及及组组合合用用法法 大大家家可可以以在在熟熟练练掌掌握握基基本本 用用情情况况下下 多多多多挖挖掘掘其其组组合合用用法法 从从而而更更巧巧妙妙地地解解决决实实际际问问题题 日日期期与与时时间间函函数数之之 DATEDATE函函数数 1 1 简简要要说说明明 根据提供的年月日数字返回一个完整的日期 2 2 基基本本语语法法 DATEDATE 年年 月月 日日 3 3 实实例例1 1 题题目目 公公式式 结结果果 简简要要说说明明 4 4 实实例例2 2 题题目目 公公式式 结结果果 简简要要说说明明 5 5 实实例例3 3 题题目目 公公式式 结结果果 简简要要说说明明 计计算算年年份份为为20062006 月月份份为为6 6月月 日日期期为为1212号号的的日日期期 DATE 2006 6 12 DATE 2006 6 12 2006 6 122006 6 12 根根据据给给出出的的年年月月日日返返回回一一个个完完整整的的日日期期 根根据据下下面面给给出出的的1515位位身身份份证证号号 返返回回出出生生日日期期 410105780512625410105780512625 DATE MID C25 7 2 MID C25 9 2 MID C25 11 2 DATE MID C25 7 2 MID C25 9 2 MID C25 11 2 1978 5 121978 5 12 根根据据身身份份证证号号利利用用MIDMID函函数数截截取取年年 月月 日日作作为为DATEDATE函函数数的的参参数数 计计算算20062006年年8 8月月的的最最后后一一天天日日期期 DATE 2006 9 0 DATE 2006 9 0 2006 8 312006 8 31 DATEDATE第第3 3个个参参数数如如果果0 0 即即为为本本月月1 1日日的的前前一一天天 如如果果是是 1 1则则是是本本月月1 1号号的的倒倒推推2 2天天 根据提供的年月日数字返回一个完整的日期 DATEDATE 年年 月月 日日 整整理理人人 ExceL ExceL精精英英培培训训网网兰兰色色幻幻想想 日日期期与与时时间间函函数数之之 DAYDAY MONTHMONTH YEARYEAR 函函数数 1 1 简简要要说说明明 DAY 返回指定日期的具体天数 MONTH 返回指定日期的具体月份 YEAR 返回指定日期的具体年龄 2 2 基基本本语语法法 dayday 日日期期 TODAYTODAY 日日期期 TODAYTODAY 日日期期 3 3 实实例例1 1 题题目目 公公式式 返回天数 返回月份 返回年份 结结果果 返回天数 返回月份 返回年份 简简要要说说明明 4 4 实实例例2 2 题题目目 公公式式 结结果果 简简要要说说明明 5 5 实实例例3 3 题题目目 公公式式 结结果果 简简要要说说明明 返返回回单单元元格格c18c18的的