excel函数整理.docVIP

Excel函数整理 一、 单元格区域选定 3 1、自定义名称之动态区域 3 二、 单元格自动输入及输入固定格式 3 1、自动输入序号 3 2、快速输入带有部分重复的数据 3 三、统计运用 3 1、统计非空单元格 3 2、条件函数 4 3、基于单个条件求和 4 4、基于多个条件求和 4 5、单条件发生次数统计 5 6、多条件发生次数统计 5 7、统计不重复个数 5 7、多条件统计不重复个数 6 四、日期与时间函数 6 （一）取出当前系统时间/日期信息 6 （二）取得日期/时间的部分字段值 6 1、返回指定月份的最后一天 6 2、合并日期与文本 6 3、计算两个日期之间的天数、月数或年数 7 4、计算两个日期每月还款后7天提醒 7 5、计算两个日期每月还款前7天提醒 7 6、文本转换为日期 7 7、换为日期 7 8、将分开的年、月、日结合起来。 7 五、查找函数 8 1、查找对应数据，返回对应数据所在列的位置 8 2、在指定区域中计算第k个最大值 8 3、在多条件指定区域中计算第k个最大值 8 3、在指定区域中计算第k个最小值 8 4、单条件查找 8 5、多条件查找 9 6、提取不重复数据函数方法整理 9 六、逻辑函数 10 （一）条件判断 10 七、 文本函数 10 （一）替换 10 (1)大小写转换 10 (1)统计文本字符个数 10 (2)替换指定文本 10 （二）取出字符串中的部分字符 10 （三）去除字符串的空白 11 （四）字符串的比较 11 （五）将数值转换为按指定数字格式表示的文本。 11 1、合并“姓”和“名” 11 2、合并日期与文本 11 3、字符替换 12 4、提取性别信息 12 5、提取出生年月信息 12 八、数字处理 12 1、向下取整： 12 2、按指定位数四舍五入 12 3、金额大小写转换 13 九、排名 13 1、排名次 13 2、中国排名 13 3、条件排名 13 4、0-1随机取数 13 十、银行专用 14 1、等额本息还款法： 14 2、等额本金还款法： 14 3、计算两个日期每月还款前7天提醒 14 十一、数据有效性 14 1、数据有效性： 14 2、自定义数据有效性 14 （1）控制数据的类型 14 （2）控制数据的范围 15 （3）控制数据的长度 15 （4）控制数据的重复性 15 （5）同时控制长度和重复性 15 （6）单元格不接受包含空格的字符 15 十二、条件格式 15 （1）在相关表格在查找标识出包含的记录 15 （2）最大值前5位 15 （3）数组在1－5之间 16 （4）数组按大排列在第6－10之间 16 （5）多条件选取格式 16 十三、隐藏差错 16 单元格区域选定 1、自定义名称之动态区域 自定义名称=OFFSET(销售额!$A$1,0,0,COUNTA(销售额!$A:$A),7)本例中, 列表在一个名称为销售额的工作表中, 起始单元格为A1. 公式中使用的参数是: 引用单元格: 销售额!$A$1 行偏移: 0 列偏移: 0 行数: COUNTA(销售额!$A:$A) 列数: 7，当前列为1??注意: 如果要使用动态的列数, ???? 请将7 替换成: COUNTA(销售额!$1:$1) 相当于销售额!$A$1到g的活动行区域。 单元格自动输入及输入固定格式 1、自动输入序号 在A2单元格中输入＝if(b2=,,counta($b$2:b2)) 返回参数列表中非空值的单元格个数 2、快速输入带有部分重复的数据 设置单元格格式――数字——自定义——类型 （1）定位四位数据：0000 ‘输入13，结果为0013 （2）前几位代码相同：ABC100@ ‘输入12，结果为ABC10012。 （3）文本与输入混合：第0000位 ‘输入12，结果为第0012位。 （4）为单元格数据添加单位（数据可运算）：0.00元 ‘输入12，结果为12.00元。 三、统计运用 1、统计非空单元格 函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。=COUNT(A5:A8) 计算上列数据的最后 4 行中包含数字的单元格的个数返回参数列表中非空值的单元格个数。利用函数 COUNTA 可以计算单元格区域或数组中包含数据的单元格个数。 =COUNTA(A1:A7,Two)计算上列数据中非空单元格以及值“Two”的个数 (7) 3、基于单个条件求和 例1：对于区域B5:B25中包含数值Northwind的每个单元格而言，以下公式可为区域F5:F25中相对应的单元格求和。 公式=SUMIF(B5:B25,Nort