EXCEL函数技巧大全.xls

EXCELEXCEL常常用用函函数数目目录录 序号 分 类 函数名称及链接函数名称及链接语法作用和功能 1 日 期 与 时 间 函 数 DATEDATEdate year month day 返回特定的日期序列号 2EDATEEDATEedate start date months 按指定日期上下推月份并回到相应日期 3DATEVALUEDATEVALUEdatevalue date text 将各种日期格式改为序列号便于计算 4YEARYEARyear serial number 返回日期序列号或日期格式中的年份 5MONTHMONTHmonth serial number 返回日期序列号或日期格式中的月份 6DAYDAYday serial number 返回日期序列号或日期格式中的天数 7HOURHOURhour serial number 返回日期序列号或日期格式中的小时数 8MINUTEMINUTEminute serial number 返回日期序列号或日期格式中的分钟数 9TODAYTODAYtoday 返回当前日期序列号 10NOWNOWnow 返回当前日期序列号并含当前时间 11WORKDAYWORKDAYworkday start day days holidays 一段时间内工作日计算函数 12NETWORKDAYSNETWORKDAYS networkdays start dayte end dat e holidays 计算两个日期之前的有效工作日天数 不包括周末和专门指定 假日 13WEEKDAYWEEKDAY weekday serial number return ty pe 返回某日期序列号代表的是星期数 14DATEDIFDATEDIFdateif start date end date unit 与Lotus兼容而设 用于计算两个日期之间的天数 月数和年数 15时间函数应用实例时间函数应用实例 16 查 找 函 数 OFFSETOFFSET offset reference rows cols height width 以指定的引用为参照系 通过给定偏移量得到新引用 17MATCHMATCH match lookup value lookup array match type 返回指定方式下的与指定数值匹配的数组中元素的相应位置 18INDIRECTINDIRECTindirect ref text al 返回由文本字符串指定的引用 并将数值计算 19CHOOSECHOOSE choose index num valuel value2 按照给定的索引号 返回列表中的数值 20ADDRESSADDRESS address row num column num a bs num al sheet text 按给定的行号和列标来建立文本类型的单元格地址 行号和列 标可指定 也可由公计算产生 文本类型的地址也可以计算 21COLUMNCOLUMNcolumn refernce 返回单元格所在列的列标 22COLUMNSCOLUMNScolumns refernce 返回数组或引用的列数 23HYPERLINKHYPERLINK hyperlink link location friendly na me 快捷的跳转方式以打开网络及其它工作簿 24LOOKUP向量LOOKUP向量 lookup lookup value lookup vect or result vector 在单行区域或单列区域中查打数值 然后返第2个单行区域或 单列区域中相同位置的数值 25LOOKUP数组LOOKUP数组lookup lookup value array 在数组的第一行或列中查找指定的数值 然后返回最后一行或 列中相同位置的数值 26LOOKUP应用1LOOKUP应用1 EXCELEXCEL常常用用函函数数目目录录 27HLOOKUPHLOOKUP hlookup lookup value table array row index num range lookup 在表格或数值数组的首行查找指定数值 并由此返回表格或数 组当前列中指定行处的数值 28VLOOKUPVLOOKUP vlookup lookup value table array col index num range looup 在表格或数值数组的首列查找指定的数值 并由此返回表格或 数组当前行中指定的列处的数值 29VLOOKUP应用1VLOOKUP应用1 30VLOOKUP应用实例2VLOOKUP应用实例2 31VLOOKUP应用3VLOOKUP应用3 32VLOOKUP处理查找错误VLOOKUP处理查找错误 33VLOOKUP模块VLOOKUP模块 34数组公式构造原理数组公式构造原理 35INDEX 数组形式INDEX 数组形式 index array row num column nu m 返回列表或数组中的元素值 此元素由行序号和列序号的索引 值给定 36INDEX 引用形式INDEX 引用形式 index reference row num column num area num 返回指定的行与列交叉处的单元格引用 如果引用由不连续区 域组成 可选择其中之一区域 37 数 学 函 数 SUMSUMsum number1 number2 返回该区域内所有数据之和 38SUMIFSUMIFsumif number1 number2 按指定条件对区域内所数据之和 39SUMPRODUCTSUMPRODUCTsumproduct array1 array2 在给定的几给数组中 将数组间对应的元素相乘 并返回乘积之 和 40SUBTOTALSUBTOTALsubtotal function num ref1 ref2 在数据库分类汇总后再进行相关汇总工作 41ROUNDROUNDround number num digits 对数值进行四舍五入 42ROUNDUPROUNDUProundup number num digits 对数值按指定位数进行向上取整 43ROUNDDOWNROUNDDOWNrounddown number num digits 对数值按指定位数进行向下取整 44ABSABSabs number 对单元格数值进行绝对值处理 45MODMODmod number divisor 求余函数 46RANDRANDrand 产生随机函数 47 文 本 函 数 ASCASCasc text 将双字节字符转换为单字节字符 48LEN LENBLEN LENB len text lenb text 测试字符串的长度 49LEFT LEFTBLEFT LEFTB left text num chars leftb text num bytes 从字符串左边第一个字符按参数定义提取字符LEFTB则提取双 字节字符 50RIGHT RIGHTBRIGHT RIGHTB right text num chars right text num bytes 从字符串右边第一个字符按参数定义提取字符RIGHTB则提取双 字节字符 51MID MIDBMID MIDBmid text start num num chars 在单元格中按指定位置按参数定义提取字符 52CONCATENATECONCATENATEconcatente text1 text2 用于连接字符串 其功能与 值为空白单元格 值为除 N A之外的任意错误值 值为逻辑值 值为文本 值为错误值 即不存在之值 值为不是文本的任意项 此函数的值为空 值为数字 值为引用 工 程 函 数 应用及备注 易失性函数 易失性函数 不含法定及特殊假日 年龄和工龄 对记录进行排序 生成工资条 建立动态 引用区 特性是返回区域内每个记录第一次出现的 位置 常用于学生成绩等级转换 个税统计 返 回区域查询 第四个参数sheet text如果是false 则 表示R1C1样式 用于列向引用求和 等等 当查找区域无时 自动在取数区域取最大 值 EXCELEXCEL常常用用函函数数目目录录 模糊查询 重复记录查询 多条件查询等 效果非常好 文本为0 逻辑值为1 正负数相除结果不同 即将全角字符改为半角字符 lenb测试双字节字符 B则为更换的字节数 在公式中经常使用 来表示and 效果一样 在公式中经常使用 来表示or 对数值和公式进行条件检测 全部参数参与计数 逻辑值文本值为零 只取含数值的单元格个数 统计区域中包含数据的单元格个数 本公式与COUNTA相反 注意 的假空单 元格也被视为空 此函数必须用数组公式输入 非常有用 默认0 表示从最大开始排 公式中可设不同格式 相当于单元格格式 设置 DATE返回主目录 函数功能 返回代表特定日期的序列号 如果在输入函数前 单元格格式为 常规 则结果将设为日期格式 返回日期与时间函数 表 达 式 DATE year month day 参数含义 Year 参数 year 可以为一到四位数字 Microsoft Excel 将根据 所使用的日期系统来解释 year 参数 默认情况下 Microsoft Excel for Windows 将使用 1900 日期系统 而 Microsoft Excel for Macintosh 将使用 1904 日期系统 Month 代表每年中月份的数字 如果所输入的月份大于 12 将从指 定年份的一月份开始往上加算 例如 DATE 2008 14 2 返回代表 2009 年 2 月 2 日的序列号 Day 代表在该月份中第几天的数字 如果 day 大于该月份的最大 天数 则将从指定月份的第一天开始往上累加 例如 DATE 2008 1 35 返回代表 2008 年 2 月 4 日的序列号 说 明 Microsoft Excel 可将日期存储为可用于计算的序列号 默认情 况下 1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序 列号是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系 统 函数 DATE 在年 月 日为变量的公式中非常有用 DATE DATE 参参数数引引用用 年月日 2008818 公式说明 结果 DATE C11 D11 E11 返回上述日期的序列号 使用 1900 日期系统 2008 8 18 或 39678 注注释释 若要将数字转换为日期形式 请选中单元格 在 格式 菜单上单击 单元格 再单击 数字 选项卡 然后单击 分类 框中的 常规 示示例例一一计划撰写西藏游记完成时间 某旅行家计划游遍西藏 回家后出版一本西藏游记 下表是他的旅游和出版计划 试计算出他想完成这项计划的所需的时间 计划耗时年月日 前期研究时间200811 开始旅行时间200831 旅行耗时8 与出版社沟通2 编辑撰写180 改稿时间120 完成时间2009 9 26 注注 计计算算公公式式 DATE E27 F27 F26 F28 F29 G30 G31 DATE E27 F27 F26 F28 F29 G30 G31 即即将将年年月月日日全全部部单单独独算算再再合合并并成成序序列列号号 再再在在单单元元格格中中设设定定日日期期格格式式即即可可 日期 2020 1 1 DATE 2020 1 1 2012 7 7 TODAY 公式说明 M11 M12M11 M12相差天数 4659 DATE具有创建日期的功能 返回主目录 返回日期与时间函数 某旅行家计划游遍西藏 回家后出版一本西藏游记 下表是他的旅游和出版计划 试计算出他想完成这项计划的所需的时间 EDATE返回主目录 函数功能 返回代表指定日期 start date 之前或之后用于指示月份数的日期的序 列号 使用函数 EDATE 可以计算与发行日处于一月中同一天的到期日的日期 如果该函数不可用 并返回错误值 NAME 请安装并加载 分析工具库 加载宏 返回日期与时间函数 表 达 式 EDATE start date months 参数含义 Start date 为一个代表开始日期的日期 应使用 DATE 函数来输入日 期 或者将日期作为其他公式或函数的结果输入 例如 使用函数 DATE 2008 5 23 输入日期 2008 年 5 月 23 日 如果将日期以文本的形式 输入 则会出现问题 Month 为 start date 之前或之后的月数 正数表示未来日期 负数 表示过去日期 说 明 Microsoft Excel 可将日期存储为用于计算的序列号 默认情况下 1900 年 1 月 1 日的默认序列号是 1 而 2008 年 1 月 1 日的默认序列号是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系统 如果 start date 不是有效日期 函数 EDATE 返回错误值 VALUE 如果 months 不是整数 将截尾取整 EDATE EDATE 参参数数引引用用 日 期 2007 1 30 公 式说明 结果 EDATE C10 1 此函数表示上述日期之后一个月的日 期 2007 2 28 EDATE C10 1 此函数表示上述日期之前一个月的日 期 2006 12 30 EDATE C10 2 此函数表示上述日期之后五个月的日 期 2007 3 30 注注释释 若要查看序列号所代表的日期 请选择该日期所在的单元格 并单击 格式 菜单上的 单元格 再单击 数字 选项卡 然后单击 分类 框中的 日期 示示例例一一推断工程开 竣工时间 知道某项工程的开间 预计月数 计算工程竣工时间 已知某项工程已竣工日期和已用月数 求开工时间 开工时间预计月数预计竣工时间 2006 2 6182007 8 6 2007 1 1162008 5 1 竣工时间已用月数计算开工时间 2007 3 1 352004 4 1 2006 11 12 212005 2 12 返回主目录 返回日期与时间函数 注注释释 若要查看序列号所代表的日期 请选择该日期所在的单元格 并单击 格式 菜单上的 单元格 知道某项工程的开间 预计月数 计算工程竣工时间 已知某项工程已竣工日期和已用月数 求开工时间 DATEVALUE返回主目录 函数功能 返回 date text 所表示的日期的序列号 函数 DATEVALUE 的主要 功能是将以文本表示的日期转换成一个序列号 返回日期与时间函数 表 达 式 DATEVALUE date text 参数含义 Date text 代表以 Microsoft Excel 日期格式表示的日期的文 本 例如 2008 1 30 或 30 Jan 08 就是带引号的文本 它用于 代表日期 在使用 Excel for Windows 中的默认日期系统时 date text 必须表示 1900 年 1 月 1 日到 9999 年 12 月 31 日之间的一个日期 而在使用 Excel for Macintosh 中的默认日期系统时 date text 必须表示 1904 年 1 月 1 日到 9999 年 12 月 31 日之间的一个日期 如果 date text 超出 上述范围 则函数 DATEVALUE 返回错误值 VALUE 如果省略 date text 中的年份部分 则函数 DATEVALUE 使用计算 机系统内部时钟的当前年份 date text 中的时间信息将被忽略 说 明 Microsoft Excel 可将日期存储为可用于计算的序列号 默认情况 下 1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号 是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系统 大部分函数都会自动将日期值转换成序列号 DATEVALUE DATEVALUE 参参数数引引用用 公式说明 结果 DATEVALUE 2008年 8月18日 使用 1900 日期系统时的文本日期 序列号 39678 DATEVALUE 18 OCT 08 使用 1900 日期系统时的文本日期 序列号 39739 DATEVALUE 2018 8 18 使用 1900 日期系统时的文本日期 序列号 43330 40620 使用 1900 日期系统时的文本日期 序列号 39159 注注释释 若要查看序列号所代表的日期 请选择该日期所在的单元格 单击 格式 菜单上的 单元格 再单击 数字 选项卡 然后单击 分类 框中的 日期 2006 8 18 407636 返回日期与时间函数 注注释释 若要查看序列号所代表的日期 请选择该日期所在的单元格 单击 格式 菜单上的 单元格 YEAR返回主目录 函数功能 返回某日期对应的年份 返回值为 1900 到 9999 之间的整数 返回日期与时间函数 表 达 式 YEAR serial number 参数含义 Serial number 为一个日期值 其中包含要查找年份的日期 应使用 DATE 函数来输入日期 或者将日期作为其他公式或函数的 结果输入 例如 使用 DATE 2008 5 23 输入 2008 年 5 月 23 日 如果日期以文本的形式输入 则会出现问题 说 明 Microsoft Excel 可将日期存储为可用于计算的序列号 默认情 况下 1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序 列号是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系 统 由 YEAR MONTH 和 DAY 函数返回的值是 Gregorian 值 不管 所提供的日期值是怎样的显示格式 例如 如果所提供的日期值显示 格式是 Hijri YEAR MONTH 和 DAY 函数返回的值将是对应 Gregorian 日期的值 YEAR YEAR 参参数数引引用用 日 期 2008 8 18 18 8 18 公 式说明 结果 YEAR C10 第一个日期的年份 2008 YEAR C11 第二个日期的年份 2018 返回日期与时间函数 MONTH返回主目录 函数功能 返回以序列号表示的日期中的月份 月份是介于 1 一月 到 12 十二月 之间的整数 返回日期与时间函数 表 达 式 MONTH serial number 参数含义 Serial number 表示一个日期值 其中包含要查找的月份 应使 用 DATE 函数来输入日期 或者将日期作为其他公式或函数的结果输入 例如 使用 DATE 2008 5 23 输入 2008 年 5 月 23 日 如果日期 以文本的形式输入 则会出现问题 说 明 Microsoft Excel 可将日期存储为可用于计算的序列号 默认情况 下 1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号 是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系统 由 YEAR MONTH 和 DAY 函数返回的值为公历值 而不用考虑所给 日期值的显示格式 例如 如果所给日期值的显示格式为 Hijri 则 YEAR MONTH 和 DAY 函数的返回值将是与等价的公历日期相关的值 MONTH MONTH 参参数数引引用用 日 期 2008 8 18 公 式说明 结果 MONTH C10 上面日期中的月份 8 示示例例一一标识8月份的采购回来的产品 某采购员为了掌握每年8月份所需采购的IT产品 需要从数据库中调查以前8月份采购的产品 并以 号标识出来 品 名采购日期年月日 MP32006 5 5 DC2007 8 1 MP42008 6 9 DV2006 4 8 MP52008 8 18 PS TWO2007 8 2 IF MONTH D20 8 返回日期与时间函数 某采购员为了掌握每年8月份所需采购的IT产品 需要从数据库中调查以前8月份采购的产品 并以 号标识出来 DAY返回主目录 函数功能 返回以序列号表示的某日期的天数 用整数 1 到 31 表示 返回日期与时间函数 表 达 式 DAY serial number 参数含义 Serial number 为要查找的那一天的日期 应使用 DATE 函数 来输入日期 或者将日期作为其他公式或函数的结果输入 例如 可使 用函数 DATE 2008 5 23 输入日期 2008 年 5 月 23 日 如果日期以 文本的形式输入 则会出现问题 说 明 Microsoft Excel 可将日期存储为可用于计算的序列号 默认情况 下 1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号 是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系统 不论日期值以何种格式显示 YEAR MONTH 和 DAY 函数返回的值 都是 Gregorian 值 例如 如果日期的显示格式是回历 则 YEAR MONTH 和 DAY 函数返回的值将是与相同的 Gregorian 日期相 关联的值 DAY DAY 参参数数引引用用 日 期 2008 8 18 公 式说明 结果 DAY C10 上述日期的天数 18 注注 DAY DAY的的作作用用是是将将日日期期中中的的天天数数以以数数据据的的方方式式取取出出来来 返回日期与时间函数 HOUR返回主目录 函数功能 返回时间值的小时数 即一个介于 0 12 00 A M 到 23 11 00 P M 之间的整数 返回日期与时间函数 表 达 式 HOUR serial number 参数含义 Serial number 表示一个时间值 其中包含要查找的小时 时 间有多种输入方式 带引号的文本字符串 例如 6 45 PM 十进 制数 例如 0 78125 表示 6 45 PM 或其他公式或函数的结果 例如 TIMEVALUE 6 45 PM 说 明 Microsoft Excel for Windows 和 Excel for Macintosh 使用不 同的默认日期系统 时间值为日期值的一部分 并用十进制数来表示 例如 12 00 PM 可表示为 0 5 因为此时是一天的一半 HOUR HOUR 参参数数引引用用 日 期 2 18 18 AM 6 18 18 PM 19 30 公 式说明 结果 HOUR C10 返回第一个时间值的小时数 2 HOUR C11 返回第二个时间值的小时数 18 HOUR C12 返回第三个时间值的小时数 19 返回日期与时间函数 MINUTE返回主目录 函数功能 返回时间值中的分钟 为一个介于 0 到 59 之间的整数 返回日期与时间函数 表 达 式 MINUTE serial number 参数含义 Serial number 表示一个时间值 其中包含要查找的分钟 时 间有多种输入方式 带引号的文本字符串 例如 6 45 PM 十进制 数 例如 0 78125 表示 6 45 PM 或其他公式或函数的结果 例如 TIMEVALUE 6 45 PM 说 明 Microsoft Excel for Windows 和 Microsoft Excel for the Macintosh 使用不同的默认日期系统 时间值为日期值的一部分 并用 十进制数表示 例如 12 00 PM 可表示为 0 5 因为此时是一天的一半 MINUTE MINUTE 参参数数引引用用 日 期 8 28 00 PM 公 式说明 结果 MINUTE C10 上面时间中的分钟数 28 返回日期与时间函数 TODAY返回主目录 函数功能 返回当前日期的序列号 序列号是 Microsoft Excel 日期和时间计算 使用的日期 时间代码 如果在输入函数前 单元格的格式为 常规 则 结果将设为日期格式 返回日期与时间函数 表 达 式 TODAY 参数含义 说 明 Microsoft Excel 可将日期存储为可用于计算的序列号 默认情况下 1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系统 TODAY TODAY 参参数数引引用用 公 式说明 结果 TODAY 返回今天日期的序列号 2012 07 07 示示例例一一标记制表日期 下表是月亮公司庶务组采购登记表 为方便打印 设置自动标上当天的制表日期 月亮公司庶务采购登记表 制表日期 2012 7 7 品 名型 号单价 元 数 量总计 元 电热杯BS 12520500 电风扇Q057210255250 碎纸机HG 252003600 饮水机UOT 1125151875 制表日期 2012 07 07 返回日期与时间函数 NOW返回主目录 函数功能 返回当前日期的序列号 序列号是 Microsoft Excel 日期和时间计 算使用的日期 时间代码 如果在输入函数前 单元格的格式为 常规 则结果将设为日期格式 返回日期与时间函数 表 达 式 NOW 参数含义 说 明 Microsoft Excel 可将日期存储为可用于计算的序列号 默认情况 下 1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号 是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系统 序列号中小数点右边的数字表示时间 左边的数字表示日期 例如 序列号 5 表示时间为中午 12 00 函数 NOW 只有在重新计算工作表 或执行含有此函数的宏时改变 它并不会随时更新 NOW NOW 参参数数引引用用 公 式说明 结果 NOW 按时期时间格式返回当前日期和 时间 2012 07 07 3 31 PM NOW 返回当前日期和时间的序列号 41097 64707 返回主目录 返回日期与时间函数 WORKDAY返回主目录 函数功能 返回某日期 起始日期 之前或之后相隔指定工作日的某一日期的日 期值 工作日不包括周末和专门指定的假日 在计算发票到期日 预期交 货时间或工作天数时 可以使用函数 WORKDAY 来扣除周末或假日 如果该函数不可用 并返回错误值 NAME 请安装并加载 分析工具 库 加载宏 返回日期与时间函数 表 达 式 WORKDAY start date days holidays 参数含义 Start date 为一个代表开始日期的日期 Days 为 Start date 之前或之后不含周末及节假日的天数 Days 为正值将产生未来日期 为负值产生过去日期 Holidays 为可选的列表 表示需要从工作日历中排除的日期值 如各种省 市 自治区和国家 地区的法定假日或非法定假日 此列表可以是 包含日期的单元格区域 也可以是由代表日期的序列号所构成的数组常量 说 明 Microsoft Excel 可将日期存储为可用于计算的序列号 默认情况下 1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系统 如果任何参数为非法日期值 则函数 WORKDAY 将返回错误值 VALUE 如果 start date 加 days 产生非法日期值 函数 WORKDAY 返回错误 值 NUM 如果 days 不是整数 将截尾取整 应使用 DATE 函数来输入日期 或者将日期作为其他公式或函数的结 果输入 例如 使用函数 DATE 2008 5 23 输入 2008 年 5 月 23 日 如果日期以文本的形式输入 则会出现问题 WORKDAY WORKDAY 参参数数引引用用 日 期说明 2008 8 1起始日期 169完成所需天数 2008 10 18假 日 2008 12 5假 日 2009 2 27假 日 公 式说明 结果 WORKDAY C11 C12 从起始日期开始 169 个工作日的日期 2009 3 26 WORKDAY C11 C12 C13 C 15 从起始日期开始 169 个工作日的日期 除去假日 2009 3 30 注注释释 若要将数字显示为日期 请选择单元格并单击 格式 菜单上的 单元格 再单击 数字 选项卡 然后单击 分类 框中的 日期 若要将上例中的假日所在单元格的区域转换为数组常量 请选择 A4 A6 再按 F9 示示例例一一实际工作日统计 按每周五个工作日的算法 计算行政事务处理完成某项事务的具体终止日期时间 行政事务当前日期完成工作日节假日终止日期 资格审查2007 4 2952007 5 12007 5 9 业务调研2007 5 20302007 5 22007 7 2 下发调令2007 6 10202007 5 32007 7 6 接收安置2007 7 272007 6 12007 7 11 新人考察2007 7 15602007 7 182007 10 9 综合评价2007 8 6102007 8 82007 8 21 返回主目录 返回日期与时间函数 注注释释 若要将数字显示为日期 请选择单元格并单击 格式 菜单上的 单元格 再单击 数字 选项卡 然后单击 分类 框中的 日期 NETWORKDAYS返回主目录 函数功能 返回参数 start data 和 end data 之间完整的工作日数值 工作日 不包括周末和专门指定的假期 可以使用函数 NETWORKDAYS 根据某一特 定时期内雇员的工作天数 计算其应计的报酬 如果该函数不可用 并返回错误值 NAME 请安装并加载 分析工具 库 加载宏 返回日期与时间函数 表 达 式 NETWORKDAYS start date end date holidays 参数含义 Start date 为一个代表开始日期的日期 End date 为终止日期 Holidays 表示不在工作日历中的一个或多个日期所构成的可选区 域 例如 省 市 自治区和国家 地区的法定假日以及其他非法定假日 该 列表可以是包含日期的单元格区域 或是表示日期的序列号的数组常量 说 明 Microsoft Excel 可将日期存储为可用于计算的序列号 默认情况下 1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系统 如果任何参数为无效的日期值 则函数 NETWORKDAYS 将返回错误值 VALUE 应使用 DATE 函数来输入日期 或者将日期作为其他公式或函数的结 果输入 例如 使用函数 DATE 2008 5 23 输入 2008 年 5 月 23 日 如果日期以文本的形式输入 则会出现问题 NETWORKDAYS NETWORKDAYS 参参数数引引用用 2007 1 31 日 期说明2007 2 28 2007 5 6项目的开始日期制表日期 2012 07 07 2008 11 1项目的终止日期 2007 10 1假 日 2007 12 5假 日 2008 3 8假 日 公 式说明 结果 NETWORKDAYS C11 C12 上面的开始日期和终止日期之间工作日的数值 390 NETWORKDAYS C11 C12 C13 上面的开始日期和终止日期之间工作日的数值 不包 括第一个假日 389 NETWORKDAYS C11 C12 C13 D15 上面的开始日期和终止日期之间工作日的数值 不包 括上面所列出的假日 388 注注释释 在最后一个示例中 若要将用于假日的单元格区域转化为数组常量 请在公式中选中引用的区域 A4 A6 再按 F9 示示例例一一计算两个日期之间的有效工作日天数 按每周五个工作日的算法 计算行政事务处理完成某项事务的具体终止日期时间 行政事务开始日期终止日期节假日工作日天数 资格审查2007 4 302007 5 82007 5 14 业务调研2007 5 202007 7 22007 5 230 下发调令2007 6 102007 7 62007 5 320 接收安置2007 7 22007 7 112007 6 18 新人考察2007 7 152007 10 92007 7 1860 综合评价2007 8 62007 8 212007 8 811 注注释释 在最后一个示例中 若要将用于假日的单元格区域转化为数组常量 请在公式中选中引用的区域 A4 A6 再按 F9 WEEKDAY返回主目录 函数功能 返回某日期为星期几 默认情况下 其值为 1 星期天 到 7 星期 六 之间的整数 返回日期与时间函数 表 达 式 WEEKDAY serial number return type 参数含义 Serial number 表示一个顺序的序列号 代表要查找的那一天的日 期 应使用 DATE 函数输入日期 或者将函数作为其他公式或函数的结果 输入 例如 使用 DATE 2008 5 23 输入 2008 年 5 月 23 日 如果日 期以文本的形式输入 则会出现问题 Return type 为确定返回值类型的数字 Return typeReturn type返返回回的的数数字字 1 或省略 数字 1 星期日 到数字 7 星期六 同 Microsoft Excel 早期版本 2数字 1 星期一 到数字 7 星期日 3数字 0 星期一 到数字 6 星期日 说 明 Microsoft Excel 可将日期存储为可用于计算的序列号 默认情况下 1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系统 WEEKDAY WEEKDAY 参参数数引引用用 日 期 2008 3 8 公 式说明 结果 星期日 具有数字 1 星期日 到数字 7 星期 六 的星期号 7 WEEKDAY C14 2 具有数字 1 星期一 到数字 7 星期 日 的星期号 6 WEEKDAY C14 3 具有数字 0 星期一 到数字 6 星期 日 的星期号 5 关于统计分析工具 Microsoft Excel 提供了一组数据分析工具 称为 分析工具库 在建立复杂统计或工程分析时可节省步骤 只需为每一个分析工具提供必要的数据和参数 该工具就会使用适当的统计或工程宏函数 在输出表格中显示相应的结果 其中有些工具在生成输出表格时还能同时生成图表 相关的工作表函数 Excel 还提供了许多其他统计 财务和工程工作表函数 某些统计函数是内置函数 而其他函数只有在安装了 分析工具库 之后才能使用 访问数据分析工具 分析工具库 包括下述工具 若要使用这些工具 请单击 工具 菜单中的 数据分析 如果没有显示 数据分析 命令 则需要加载 分析工具库 加载项程序 实际应用 用户编号 用户工 号 用户姓 名 考勤日期第一次第二次第三次 第四 次 第五次 000001YX0010000 关安2008 12 108 2617 48 000001YX0010000 关安2008 12 208 2617 36 出勤小时 IF WEEKDAY D2 2 6 WEEKDAY D2 2 7 IF HOUR LARGE F2 J2 1 E2 8 8 平时加班 IF WEEKDAY E40 2 6 WEEKDAY E40 2 7 IF HOUR LARGE G40 K40 1 F40 9 HOUR LARGE G40 K40 1 F40 9 周末加班 IF WEEKDAY E40 2 6 WEEKDAY E40 2 7 HOUR LARGE G40 K40 1 F40 1 注注 先先使使用用WEEKDAYWEEKDAY判判断断当当前前日日期期是是否否周周六六和和周周日日 如如果果是是则则取取0 0 如如果果不不是是 再再利利LARGELARGE公公式式用用最最大大值值减减去去第第一一次次打打卡卡时时间间 超超过过者者计计为为8 8 EXCELEXCEL许许多多公公式式应应组组合合使使用用 如如果果单单纯纯来来看看公公式式的的作作用用并并不不大大 组组合合使使用用后后会会产产生生意意外外的的强强大大效效果果 返回主目录 返回日期与时间函数 Microsoft Excel 提供了一组数据分析工具 称为 分析工具库 在建立复杂统计或工程分析时可节省步骤 只需为每一个分析工具提供必要的数据和参数 该工具就会使用适当的统计或工程宏函数 在输出表格中显示相应的结果 其中有些工具在生成输出表格时还能同时生成图表 相关的工作表函数 Excel 还提供了许多其他统计 财务和工程工作表函数 某些统计函数是内置函数 而其他函数只有在安装了 分析工具库 之后才能使用 第六次班别出勤小时平时加班周末加班 B8 B8 IF WEEKDAY D2 2 6 WEEKDAY D2 2 7 IF HOUR LARGE F2 J2 1 E2 8 8 IF WEEKDAY E40 2 6 WEEKDAY E40 2 7 IF HOUR LARGE G40 K40 1 F40 9 HOUR LARGE G40 K40 1 F40 9 IF WEEKDAY E40 2 6 WEEKDAY E40 2 7 HOUR LARGE G40 K40 1 F40 1 注注 先先使使用用WEEKDAYWEEKDAY判判断断当当前前日日期期是是否否周周六六和和周周日日 如如果果是是则则取取0 0 如如果果不不是是 再再利利LARGELARGE公公式式用用最最大大值值减减去去第第一一次次打打卡卡时时间间 超超过过者者计计为为8 8 EXCELEXCEL许许多多公公式式应应组组合合使使用用 如如果果单单纯纯来来看看公公式式的的作作用用并并不不大大 组组合合使使用用后后会会产产生生意意外外的的强强大大效效果果 Microsoft Excel 提供了一组数据分析工具 称为 分析工具库 在建立复杂统计或工程分析时可节省步骤 只需为每一个分析工具提供必要的数据和参数 该工具就会使用适当的统计或工程宏函数 在输出表格中显示相应的结果 其中有些工具在生成输出表格时还能同时生成图表 DATEDIF返回主目录 函数功能 计算两个日期之间的天数 月数或年数 此函数是为与 Lotus 1 2 3 兼容而提供的 返回日期与时间函数 表 达 式 DATEDIF start date end date unit 参数含义 Start date 代表一段时期的第一个日期或起始日期的日期 日期可 以放在引号内作为文本字符串输入 如 2001 1 30 也可以作为序列 数 如 36921 如果使用的是 1900 日期系统 则它代表 2001 年 1 月 30 日 输入 或作为其他公式或函数的结果 如 DATEVALUE 2001 1 30 输入 End date 代表一段时期的最后一个日期或结束日期的日期 Unit 要返回的信息的类型 UnitUnit返返 回回 Y 一段时期内完整的年数 M 一段时期内完整的月数 D 一段时期内的天数 MD start date 和 end date 之间相差的天数 忽略日期的月 数和年数 YM start date 和 end date 之间相差的月数 忽略日期的天 数和年数 YD start date 和 end date 之间相差的天数 忽略日期的年数 说 明 日期是作为有序序列数进行存储的 因此可将其用于计算 默认情况 下 1899 年 12 月 31 日的序列数为 1 而 2008 年 1 月 1 日的序列数 为 39448 因为它是 1900 年 1 月 1 日之后的第 39 448 天 DATEDIF 函数在需要计算年龄的公式中很有用 DATEDIF DATEDIF 参参数数引引用用 Start date End date公 式说明 结果 2006 6 12008 8 1 DATEDIF C17 D17 Y 这段时期经历了两个完整年 2 2006 6 22008 8 1 DATEDIF C18 D18 D 2006 年 6 月 2 日和 2008 年 8 月 1 日之间有 791 天 791 2006 6 32008 8 1 DATEDIF C19 D19 YD 6 月 3 日和 8 月 1 日之间有 59 天 忽略日期的年数 59 2006 6 42008 8 1 DATEDIF C20 D20 MD 4 和 1 start date 的天数和 end date 的天数 之间相 差的天数 忽略日期的月数和年数 28 2008 6 22008 8 11 示示例例一一日期相减计算 第一日期第二日期类 型相差出生日期 1988 8 18 1988 8 182008 12 18日7427年 23 DATEDIF I29 TODAY y 1988 8 182008 12 18月244月 10 DATEDIF I29 TODAY ym 1988 8 182008 12 18年20日 19 DATEDIF I29 TODAY md 1988 8 182008 12 18同年122可以把它们连接在一起成为一个文本 1988 8 182008 12 18同年月4年龄是23 年 10 月 零 19 日 1988 8 182008 12 18同年同月023年10月19日 2007 5 12008 5 112 2007 5 22008 5 111 当出现差异时 可用下面的公式 TEXT DATEDIF 2008 5 1 m DATEDIF 2007 5 3 m 0 姓姓名名身身份份证证号号码码性性别别出出生生日日期期年年龄龄 欧阳芬110221650815224女1965 8 154646 李可1978 10 123333 朱菊511801520925520女1952 9 255959 周桂香110221290815224女1929 8 158282 西门雄11010119700626