excel表格公式函数大全

excel表格公式函数大全 篇一:Excel表格函数公式大全 EXCEL函数大全 数据库和清单管理函数 DAVERAGE 返回选定数据库项的平均值 DCOUNT 计算数据库中包含数字的单元格的个数 DCOUNTA 计算数据库中非空单元格的个数 DGET 从数据库中提取满足指定条件的单个记录 DMAX 返回选定数据库项中的最大值 DMIN 返回选定数据库项中的最小值 DPRODUCT 乘以特定字段(此字段中的记录为数据库中满足指定条件的记录)中的值 DSTDEV 根据数据库中选定项的示例估算 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 偏差 DSTDEVP 根据数据库中选定项的样本总体计算标准偏差 DSUM 对数据库中满足条件的记录的字段列中的数字求和 DVAR 根据数据库中选定项的示例估算方差 DVARP 根据数据库中选定项的样本总体计算方差 GETPIVOTDATA 返回存储在数据透视表中的数据 日期和时间函数 DATE 返回特定时间的系列数 DATEDIF 计算两个日期之间的年、月、日数 DATEVALUE 将文本格式的日期转换为系列数 DAY 将系 1 列数转换为月份中的日 DAYS360 按每年360天计算两个日期之间的天数 EDATE 返回在开始日期之前或之后指定月数的某个日期的系列数 EOMONTH 返回指定月份数之前或之后某月的最后一天的系列数 HOUR 将系列数转换为小时 MINUTE 将系列数转换为分钟 MONTH 将系列数转换为月 NETWORKDAYS 返回两个日期之间的完整工作日数 NOW 返回当前日期和时间的系列数 SECOND 将系列数转换为秒 TIME 返回特定时间的系列数 TIMEVALUE 将文本格式的时间转换为系列数 TODAY 返回当天日期的系列数 WEEKDAY 将系列数转换为星期 WORKDAY 返回指定工作日数之前或之后某日期的系列数 YEAR 将系列数转换为年 YEARFRAC 返回代表START_DATE(开始日期)和END_DATE(结束日期)之间天数的以年为单位的分数 DDE 和外部函数 CALL 调用动态链接库(DLL)或代码源中的过程 REGISTER.ID 返回已注册的指定DLL或代码源的注册ID SQL.REQUEST 连接外部数据源，并从工作表中运行查询，然后将结果作为数组返回，而无需进行宏编程。 2 有关CALL和REGISTER函数的其他信息 工程 路基工程安全技术交底工程项目施工成本控制工程量增项单年度零星工程技术标正投影法基本原理 函数 BESSELI 返回经过修改的贝塞尔函数IN(X) BESSELJ 返回贝塞尔函数JN(X) BESSELK 返回经过修改的贝塞尔函数KN(X) BESSELY 返回贝塞尔函数YN(X) XLFCTBIN2DEC、BIN2DEC 将二进制数转换为十进制数 BIN2HEX 将二进制数转换为十六进制数 BIN2OCT 将二进制数转换为八进制数 COMPLEX 将实系数和虚系数转换为复数 CONVERT 将一种度量单位制中的数字转换为另一种度量单位制 DEC2BIN 将十进制数转换为二进制数 DEC2HEX 将十进制数转换为十六进制数 DEC2OCT 将十进制数转换为八进制数 DELTA 检测 工程第三方检测合同工程防雷检测合同植筋拉拔检测方案传感器技术课后答案检测机构通用要求培训 两个值是否相等 ERF 返回误差函数 ERFC 返回余误差函数 GESTEP 检测数字是否大于某个阈值 HEX2BIN 将十六进制数转换为二进制数 HEX2DEC 将十六进制数转换为十进制数 HEX2OCT 将十六进制数转换为八进制数 IMABS 返回复数的绝对值(模) IMAGINARY 返回复数的虚系数 IMARGUMENT 返回参数THETA，一个以弧度表示的角 IMCONJUGATE 返回复数的共轭复数 IMCOS 返回复数的余弦 IMDIV 返回两个复数的商 IMEXP 返回复数的指数 IMLN 返回复数的自然对数 IMLOG10 返回复数 3 的常用对数 IMLOG2 返回复数的以2为底数的对数 IMPOWER 返回复数的整数幂 IMPRODUCT 返回两个复数的乘积 IMREAL 返回复数的实系数 IMSIN 返回复数的正弦 IMSQRT 返回复数的平方根 IMSUB 返回两个复数的差 IMSUM 返回两个复数的和 OCT2BIN 将八进制数转换为二进制数 OCT2DEC 将八进制数转换为十进制数 OCT2HEX 将八进制数转换为十六进制数 ? 2010-5-12 09:32 ? 回复 2楼 Excel2003常用函数 1.NOW 用途:返回当前日期和时间所对应的序列号。 语法:NOW() 参数:无 实例:如果正在使用的是1900日期系统，而且计算机的内部时钟为2001-1-2812:53，则公式“=NOW()”返 9.54。 2.TODAY 用途:返回系统当前日期的序列号。 参数:无 语法:TODAY() 实例:公式“=TODAY()”返回2001-8-28(执行公式时的系统时间)。 3.CELL 用途:返回某一引用区域的左上角单元格的格式、位置或内容等信息，该函数主要用于保持与其它电子表格程序的兼 语法:CELL(info_type，reference) 参数:Info_type为一个文本值，指明所需要的单元格信息的类型。Reference表 4 示要获取其有关信息的单元格。如果忽略，则在info_type 中所指定的信息将返回给最后更改的单元格。 实例:公式 “=CELL(row，A20)”返回20，如果A3单元格包含TOTAL， 则 篇二:EXCEL表格函数公式大全 Excel常用函数公式及技巧搜集(常用的) 【身份证信息,提取】 从身份证号码中提取出生年月日 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)*1 =IF(A2<,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)+0,) 显示格式均为yyyy-m-d。(最简 单的公式，把单元格设置为日期格式) =IF(LEN(A2)=15,19&MID(A2,7,2)&-&MID(A2,9,2)&-&MID(A2,11,2),MID(A2,7,4)&-&MID(A2,11,2)&-&MID(A2,13,2)) 显示格式为yyyy-mm-dd。(如果 要求 对教师党员的评价套管和固井爆破片与爆破装置仓库管理基本要求三甲医院都需要复审吗 为“1995/03/29”格式 的话，将”-” 换成”/”即可) =IF(D4=,,IF(LEN(D4)=15,TEXT((19&MID(D4,7,6)),00 5 00年00月00日 ),IF(LEN(D4)=18,TEXT(MID(D4,7,8),0000年00月00 日)))) 显示格式为yyyy年mm月dd日。(如果将公式中“0000 年00月00日”改成“0000-00-00”,则显示格式为 yyyy-mm-dd) =IF(LEN(A1:A2)=18,MID(A1:A2,7,8),19&MID(A1:A2,7,6)) 显示格式为yyyymmdd。 =TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0 =IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2)) =MID(A1,7,4)&年&MID(A1,11,2)&月 &MID(A1,13,2)&日 =IF(A1<,TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)) 从身份证号码中提取出性别 6 =IF(MOD(MID(A1,15,3),2),男,女)(最简单公式) =IF(MOD(RIGHT(LEFT(A1,17)),2),男,女) =IF(A2<” ”,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,” 女”),) =IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,男,女) 从身份证号码中进行年龄判断 =IF(A3<””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),TODAY(),”Y”),) =DATEDIF(A1，TODAY()，“Y”) (以上公式会判断是否已过生日而自动增减一岁) =YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900 =YEAR(TODAY())-IF(LEN(A1)=15,19&MID(A1,7,2),MID(A1,7,4)) =YEAR(TODAY())-VALUE(MID(B1,7,4))&岁 =YEAR(TODAY())-IF(MID(B1,18,1)=,CONCATENATE(19,MID(B1,7,2)),MID(B1,7,4)) 按身份证号号码计算至今天年龄 =DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00),TODAY(),y) 7 以2006年10月31日为基准日,按按身份证计算年龄(周岁) 的公式 =DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)*1,2006-10-31,y) 按身份证号分男女年龄段 按身份证号分男女年龄段，身份证号在K列，年龄段在J 列(身份证号为18位) 男性16周岁以下为1 男性16周岁(含16周岁)以上至50周岁为 2 男性50周岁(含50周岁)以上至60周岁为 3 男性60周岁(含60周岁)以上为4 女性16周岁以下为1 女性16周岁(含16周岁)以上至45周岁为 2 女性45周岁(含45周岁)以上至55周岁为 3 女性55周岁(含55周岁)以上为4 =MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),y),{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1))) =SUM(--(DATEDIF(MID(K1,7,4)&/&MID(K1,11,2)&/&MID(K1,13,2),TODAY(),y)={0,16,45,55}+{0 8 ,0,5,5}*MOD(MID(K1,17,1),2))) ????????????????????????? 【年龄和工龄计算】 根据出生年月计算年龄 ,DATEDIF(A1,TODAY(),y) ,DATEDIF(A1,TODAY(),y)&周岁 ,DATEDIF(A1,NOW(),y) 根据出生年月推算生肖 中国人有12生肖，属什么可以推算出来。即用诞生年份除以12，再用除不尽的余数对照如下:0?猴，1?鸡，2?狗，3?猪，4?鼠，5?牛，6?虎，7?兔，8?龙，9?蛇，10?马，11?羊例如:XXX出生于1921年，即用1921年除以12，商得数为160，余数为1，对照上面得知余数1对应生肖是鸡，XXX就属鸡。 =MID(猴鸡狗猪鼠牛虎兔龙蛇马 羊,MOD(YEAR(A2),12)+1,1)(2007) 如何求出一个人到某指定日期的周岁, =DATEDIF(起始日期,结束日期,Y) 计算距离退休年龄的公式 =IF(E2=,,IF(E2=V2,已经退休,距离退休还有 9 &DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONT H(U2),DAY(U2)),Y)&年 &DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONT H(U2),DAY(U2)),YM)&个月 &DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONT H(U2),DAY(U2)),Md)&天)) 其中E2为年龄(可用身份证号码的公式生成); V2为法定退休年龄(男60，女50)公式为:=IF(D2=,,IF(D2=男,60,50)) D2为男或女(可用身份证号码的公式生成);U2为出生 年月日(可用身份证号码的公式生成)。 求工齡 =DATEDIF(B2,TODAY(),y) =DATEDIF(B2,TODAY(),ym) =DATEDIF(B2,TODAY(),md) =DATEDIF(B2,TODAY(),y)&年 &DATEDIF(B2,TODAY(),ym)&月 &DATEDIF(B2,TODAY(),md)&日 计算工龄 =DATEDIF(C6,C8,y)求两日期间的年数 =DATEDIF(C6,C8,ym)求两日期间除去整年数剩余的月 10 数 =DATEDIF(C6,C8,m)求两日期间的总月数 如果只需要算出周年的话，可以用 =datedif(1978-8,2006-5,Y) 年龄及工龄计算 有出生年月如何求年龄, 有工作时间如何求工龄,(求出的结果为多少年另几个月，如:0303的形式，即3年零3个月)。 a1是出生年月或工作时间: =datedif(a1,today(),y) =text(datedif(a1,today(),y),00)&text(datedif(a1,today(), m),00) 如 [B2]=1964-9-1 则: =TEXT(DATEDIF(B2,TODAY(),y),00)&TEXT(MOD( DATEDIF(B2,TODAY(),m),12),00)'显示 4009 =TEXT(DATEDIF(B2,TODAY(),y),00年)&TEXT(MOD(DATEDIF(B2,TODAY(),m),12),00月) '显示 40年09月 如果你找不到 DATEDIF 函数，也可以不用 DATEDIF 函数, 11 如 [B2]=1964-9-1 则: =TEXT(RIGHT(YEAR(NOW()-B2),2),00)&TEXT(M OD(MONTH(NOW()-B2)-1,12),00)'显示 4009 =TEXT(RIGHT(YEAR(NOW()-B2),2)&年&MOD(MONTH(NOW()-B2)-1,12)&个月 ,) '显示 40年09个月 自动算出工龄日期格式为(yyyy.mm.dd) 能否用:(yyyy.mm.dd)这种格式来计算出工龄有多长呢~, 以前用这样一段 (=TEXT(RIGHT(YEAR(NOW()-A1),2)&年 &MOD(MONTH(NOW()-A1)-1,12)&个月,) )。 但这种方法只能用:(yyyy-mm-dd)这样的日期格式才能实现～ 你不妨把“.”替换成“-”，不就行了吗，再说后者是日期的一种标准格式， =TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A1,.,-)),2)& amp;年 12 &MOD(MONTH(NOW()-SUBSTITUTE(A1,.,-))-1,12) &个月,) ???????????????????????? ? 【时间和日期应用】 自动显示当前日期公式 =YEAR(NOW()) 当前年 =MONTH(NOW()) 当前月 =DAY((NOW())) 当前日 如何在单元格中自动填入当前日期 Ctrl+; 如何判断某日是否星期天 =WEEKDAY(A2,2) =TEXT(A1,aaaa) =MOD(A1,7)<2 某个日期是星期几 比如2007年2月9日，在一单元格内显示星期几。 =TEXT(A1,aaa) (五) =TEXT(A1,aaaa) (星期五) =TEXT(A1,ddd) (Fri) =TEXT(A1,dddd) (Friday) 什么函数可以显示当前星期 13 如:星期二 10:41:56 =TEXT(NOW(),aaaa hh:mm:ss) 求本月天数 设A1为2006-8-4 求本月天数 A1=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) 也有更簡便的公式:=DAY(EOMONTH(NOW(),0)) 需加 載分析工具箱。 当前月天数: =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-DATE(YEAR(TODAY()),MONTH(TODAY()),1) 用公式算出除去当月星期六、星期日以外的天数 =SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&:&DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)1)) 显示昨天的日期 每天需要单元格内显示昨天的日期，但双休日除外。 例如，今天是7月3号的话，就显示7月2号，如果是7 月9号，就显示7月6号。 =IF(TEXT(TODAY(),AAA)=一,TODAY()-3,IF(TEXT(TODAY(),AAA)=日 ,TODAY()-2,TODAY()-1)) 14 =IF(TEXT(TODAY(),AAA)=一,TODAY()-3,TODAY()-1) 关于取日期 怎么设个公式使A1在年月日向后推5年,变成2011-7-15 =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) =EDATE(A1,12*5) 如何对日期进行上、中、下旬区分 =LOOKUP(DAY(A1),{0,11,21,31},{上旬,中旬,下旬,下旬}) 如何获取一个月的最大天数 =DAY(DATE(2(转载于:www.XltkWJ.Com 小 龙文档 网:excel表格公式函数大全)002,3,1)-1)或=DAY(B1-1),B1为 2001-03-01 日期格式转换公式 将 “01/12/2005” 转换成“20050112”格式 ,RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2) , YEAR($A2)&TEXT(MONTH($A2),00)&TEXT( DAY($A2),00) 该公式不用设置数据有效性,但要设置储存 格格式。 也可以用下列两方法: 1、先转换成文本, 然后再用字符处理函数。 2、[数据]-[分列] [日期]-[MDY] 将“2005年9月”转换成“200509”格式 15 先用公式:=text(a1,yyyymm)+0然后将单元格格式为常 规。 将“2005-8-6”格式转换为“20050806”格式 用公式:=TEXT(A1,YYYYMMDD) 反之，将20050806转为日期2005-8-6格式，可用公式: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) 另四种公式: ,text(a1,0000-00-00)显示:2005-08-06 , --TEXT(A1,#-00-00)，把单元格设置为日期格式 显示: 2005-8-6 ,TEXT(20050806,0000-00-00)*1，单元格设置日 期型 显示:2005-8-6 , VALUE(LEFT(A1,4)&-&MID(A1,5,2)&-&RIGHT(A1,2))显示:2005-8-6 将“20060501”转换为“2006-05-01”格式 =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)) 将“199306”转换为“1993-6” 公式1:=LEFT(A3,4)&-&RIGHT(A3,2)*1 公式2:=--TEXT(A3*100+1,#-00-00) 公式2需要设置单 元格格式，自定义:e-m 公式3:=TEXT(TEXT(A3&01,0000-00-00),e-m) 把198405转换成1984.05 一、查找—1984，替换—1984. 16 二、如果全部是年月的话，我个人建议， 1、采取辅助,mid(xxxxxx,1,4) & . & right(xxxxxx,2) 篇三:Excel常用的函数计算公式大全 EXCEL的常用计算公式大全 一、单组数据加减 乘除 小学二年级加减乘除混合运算最新二年级加减乘除混合运算二年级加减乘除混合运算四年级奥数巧算乘除法三年级加减乘除竖式 运算: ? 单组数据求加和公式:=(A1+B1) 举例: 单元格A1:B1区域依次输入了数据10和5， 计算: 在C1中输入 =A1+B1 后点击键盘“Enter(确定)”键后，该单元格就自动显示10与5的和15。 ? 单组数据求减差公式:=(A1-B1) 举例:在C1中输入 =A1-B1 即求10与5的差值5，电脑操作方法同上; ? 单组数据求乘法公式:=(A1*B1) 举例:在C1中输入 =A1*B1 即求10与5的积值50，电脑操作方法同上; ? 单组数据求乘法公式:=(A1/B1) 举例:在C1中输入 =A1/B1 即求10与5的商值2，电脑操作方法同上; ?其它应用: 在D1中输入 =A1 即求5已做好公式的单元格，将鼠标移至该单元格的右下角，带出现十字符号提示时，开始按住鼠标左键不动一直沿着该单元格依次往下拉到你需要的某行同一列的单元格下即可，即可完成公司自动复制，自动计算。 17 二、多组数据加减乘除运算: ? 多组数据求加和公式:(常用) 举例说明:=SUM(A1:A10),表示同一列纵向从A1到A10的所有数据相加; =SUM(A1:J1),表示不同列横向从A1到J1的所有第一行数据相加; ? 多组数据求乘积公式:(较常用) 举例说明:=PRODUCT(A1:J1)表示不同列从A1到J1的所有第一行数据相乘; =PRODUCT(A1:A10)表示同列从A1到A10的所有的该列数据相乘; ? 多组数据求相减公式:(很少用) 举例说明:=A1-SUM(A2:A10)表示同一列纵向从A1到A10的所有该列数据相减; =A1-SUM(B1:J1)表示不同列横向从A1到J1的所有第一行数据相减; ? 多组数据求除商公式:(极少用) 举例说明:=A1/PRODUCT(B1:J1)表示不同列从A1到J1的所有第一行数据相除; =A1/PRODUCT(A2:A10)表示同列从A1到A10的所有的该列数据相除; 三、其它应用函数代表: ? 平均函数 =AVERAGE(:);? 最大值函数 =MAX (:);? 最小值函数 =MIN (:);? 统计函数 =COUNTIF(:):举例:Countif ( A1:B5，”60”) 说明:统计分数大于60分的人数，注意，条件要加双引 18 号，在英文状态下输入。 1、 请教excel中同列重复出现的货款号应怎样使其合为一列，并使款号后的数值自动求和, 应怎样使其四行变为三行，切使002号后的数值为11, 第一方法:对第A列进行分类汇总。第二:用透视表对A列进行分析。 2、比如以下四个数值， 1 2 3 4 求和为10，第三行隐藏，我希望得到答案是7。谢谢! 问题补充: =SUBTOTAL(109,A1:A4)是正确的，但其中109是什么意思，请解释一下。好象101.103等除了108都好用，谢谢! =SUBTOTAL(9,A1:A4)才是求和，但不行。 最佳答案 SUBTOTAL 返回数据清单或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的数据清单。一旦创建了分类汇总，就可以通过编辑 SUBTOTAL 函数对该数据清单进行修改。 语法 19 SUBTOTAL(function_num,ref1,ref2,...) Function_num 为 1 到 11 之间的数字，指定使用何种函数在数据清单中进行分类汇总计算。 Function_Num 函数 1 AVERAGE平均值2 COUNT 个数 3 COUNTA 非空格的个数 4 MAX 最大值 5 MIN 最小值 6 PRODUCT 乘积 7 STDEV 标准偏差8 STDEVP 总体标准偏差 9 SUM和10 VAR 方差 11 VARP 总体方差 Ref1, ref2, 为要进行分类汇总计算的 1 到 29 个区域或引用。 如果在 ref1, ref2,… 中有其他的分类汇总(嵌套分类汇总)，将忽略这些嵌套分类汇总，以避免重复计算。 函数 SUBTOTAL 将忽略由数据清单筛选时产生的任何隐藏行。在需要对筛选过的数据清单中的可见数据进行分类汇总时，这一点很重要。 如果所指定的某一引用为三维引用，函数 SUBTOTAL 将返回错误值 #VALUE!。 示例 SUBTOTAL(9,C3:C5) 将使用函数 SUM 产生单元格区域C3:C5的分类汇总。 补充: Function_Num 函数 20 如果为101--111就是上面对应参数 隐藏数值不参与计算。 3、excel中公式和数值怎样才能同时隐藏，但自动求和时又能引用,谢谢～ 我想在单元格中同时隐藏公式和数值，但自动求和时又能引用单元格中的数值,谢谢～ 已经尝试过很多种办法，要不只能隐藏公式，要不就只能隐藏数值，如果字体颜色改用白色，在正常情况是看不到，但在自动求和选中此列时还是会看见，哪位高手能帮我解决，非常感谢～ 推荐答案保护，不允许选中此列。 4、 21