经常用到的excel公式

1、办公经常用到的excel公式,序号,简要说明,公式,实例,1,按分类汇总,SUMIF($F$3:$F$5,$E$3:$E$5) SUMIF($H$3:$H$5,=3,$I$3:$I$5) SUMIF($H$3:$H$5,=3,$I$3:$I$5),700,200,530,2,100,330,300,3,230,500,4,300,2,取整数,ROUND(E6*F6,0),3,1.11,2.33,进位到5,CEILING(E7*F7,5),10,2.4,3,7.20 ,保留一位小数,ROUND(E7*F7,1),2.6,1.11,2.33,保留两位小数,ROUND(E8*F8,2),2.59,1

2、.11,2.33,3,按垂直方向查找计算,VLOOKUP(E9,$F$9:$G$10,2,0)*$H$9,4000,伸缩缝,伸缩缝,200,20,VLOOKUP(E10,$F$9:$G$10,2,0)*$H$9,6000,支座,支座,300,4,按水平方向查找计算,HLOOKUP(E11,$F$11:$G$12,$J$11,0)/$H$11,200,伸缩缝,伸缩缝,支座,10,2,HLOOKUP(E12,$F$11:$G$12,$J$11,0)/$H$11,300,支座,2000,3000,5,按水平方向查找自动填写,HLOOKUP(E13,$F$13:$G$16,J13,0),锚板,伸缩缝,

3、伸缩缝,支座,2,HLOOKUP(E14,$F$13:$G$16,J14,0),边梁,伸缩缝,锚板,底盆,3,HLOOKUP(E15,$F$13:$G$16,J15,0),球冠,支座,边梁,球冠,3,HLOOKUP(E16,$F$13:$G$16,J16,0),上顶板,支座,支承梁,上顶板,4,6,按垂直方向查找自动填写,VLOOKUP(E17,$F$17:$J$18,5,0),滑板,伸缩缝,伸缩缝,锚板,边梁,支承梁,滑板,VLOOKUP(E18,$F$17:$J$18,4,0),上顶板,支座,支座,底盆,球冠,上顶板,套筒,7,按垂直方向查找,LOOKUP(E19,$F$19:$F$20,

4、$G$19:$G$20),9000,伸缩缝,伸缩缝,9000,LOOKUP(E20,$F$19:$F$20,$G$19:$G$20),4000,支座,支座,4000,8,按水平方向查找,LOOKUP(E21,$F$21:$G$21,$F$22:$G$22),200,伸缩缝,伸缩缝,支座,LOOKUP(E22,$F$21:$G$21,$F$22:$G$22),600,支座,200,600,9,绝对值,ABS(E23),3.14159265,-3.14159265,10,关于年月日,DATE(year,month,day),2015/9/22,2015,9,22,IF(MONTH(DATE($E$

5、24,$F$24,COLUMN(E24)=$F$24,DATE($E$24,$F$24,COLUMN(A1),),2015/9/1,星期二,二,DATE(LEFT($D25,4),MID($D25,FIND(年,$D25)+1,FIND(月,$D25)-(FIND(年,$D25)+1),MID($D25,FIND(月,$D25)+1,FIND(日,$D25)-(FIND(月,$D25)+1),2015年3月19日,2015/3/19,二一五年三月十九日,贰零壹伍年叁月拾玖日,YEAR MONTH DAY,2015,2015,9,2,22,22,11,货币数字转换为大写,SUBSTITUTE(S

6、UBSTITUTE(IF(E270,负,)&TEXT(TRUNC(ABS(ROUND(E27,2),DBNum2)&元&IF(ISERR(FIND(.,ROUND(E27,2),TEXT(RIGHT(TRUNC(ROUND(E27,2)*10),DBNum2)&IF(ISERR(FIND(.0,TEXT(E27,0.00),角,)&IF(LEFT(RIGHT(ROUND(E27,2),3)=.,TEXT(RIGHT(ROUND(E27,2),DBNum2)&分,IF(ROUND(E27,2)=0,整),零元零,),零元,),叁元整,3,壹佰壹拾壹元壹角壹分,111.11,12,乘积,乘积,单价

7、,人数,多次折扣,PRODUCT(F28,$E$28,G28:J28),581.4,100,10,80%,85%,90%,95%,PRODUCT(F29,$E$28,G29),80,1,80%,PRODUCT(F30,$E$28,G30,H30),360,5,80%,90%,PRODUCT(F31,$E$28,G31,H31,I31),720,20,50%,80%,90%,13,库存安全设置,IF(G35F35,IF(G35=E35,补货,准备),IF(G3511;0.00),),李四,2005年8月21日,2010年8月2日,5年7个月,10年7个月,10 ,王五,1995年8月21日,20

8、05年8月3日,10年7个月,20年7个月,15 ,16,条件求和,SUMIF($E$44:$H$52,D80,$G$44:$J$52),286,班组,产品,产量,班组,产品,产量,SUMIF($E$44:$H$52,平一,$G$44:$J$52),46,平一,D240,24,平一,D320,22,SUM(SUMIF($E$44:$H$52,平一,平二,平三,$G$44:$J$52),362,平二,DT160,30,平二,D80,100,SUMIF(F44:I52,$F$47,G44:J52),30,平三,D80,98,平三,D80,88,17,多条件求和,SUMPRODUCT($E$48:$

9、E$50=$D$48)*($F$47:$J$47=$D$49)*$F$48:$J$50),M15001,M15002,M15003,M15004,M15005,平二,平一,1000,1000,1000,1000,1000,M15002,平二,1000,1000,1000,1000,1000,1000,平三,1000,1000,1000,1000,1000,18,日期,A产量,B产量,C产量,D产量,E产量,212,2015/8/1,102,2015/8/1,110,2015/8/2,98,2015/8/2,100,2015/8/2,92,2015/8/3,99,2015/8/3,108,201

10、5/8/4,85,2015/8/4,86,19,PI(),3.14159265358979 ,20,星座,LOOKUP(-TEXT(D59,m.dd),1.01,1.21,2.19,3.21,4.21,5.21,6.22,7.23,8.23,9.23,10.23,11.22,12.22,魔羯座,水瓶座,双鱼座,牡羊座,金牛座,双子座,巨蟹座,狮子座,处女座,天秤座,天蝎座,射手座,魔羯座),出生日期,星座,生肖,年龄,1993/5/4,金牛座,鸡,22,1967/12/9,射手座,羊,48,1968/5/12,金牛座,猴,47,1935/2/9,水瓶座,猪,81,年龄,DATEDIF(D59,

11、TODAY(),y),2010/3/9,双鱼座,虎,6,属相,MID(猴鸡狗猪鼠牛虎兔龙蛇马羊,MOD(YEAR(D59),12)+1,1),21,统计项目,COUNTIF(E65:J65,),3,22,相隔天数,IF(OR($F72=,$G72=),DATEDIF($F72,$G72,d),设备,第1次检修时间,第2次检修时间,相隔年数,相隔天数,相隔年数,IF(OR($F72=,$G72=),DATEDIF($F72,$G73,y),1.00 ,机器1,2015年1月1日,2016年1月1日,1,365,23,漂亮的日历控件,Private Sub Worksheet_SelectionC

12、hange(ByVal Target As Range) Dim strTmp As String strTmp = Application.ExecuteExcel4Macro(GetDate(200,200) 两个200分别表示左边距和上边距 If strTmp 0 Then ActiveCell = strTmpEnd Sub,24,身份证提取出生年月日,IF(LEN(D77)=15,DATE(MID(D77,7,2),MID(D77,9,2),MID(D77,11,2),IF(LEN(D77)=18,DATE(MID(D77,7,4),MID(D77,11,2),MID(D77,13,

13、2),0)1937年10月14日,2016年3月23日,天秤座,牛,78,78,781993年5月4日,2016年3月23日,金牛座,鸡,22,22,221967年11月8日,2016年3月23日,天蝎座,羊,48,48,4825,多条件提取,班级,姓名,查询班级,奔跑班,COUNTIF(F$81:F81,J$80),1,奔跑班,张三,2,奔跑班,李四,姓名,张三,3,奔跑班,王五,李四,IFERROR(VLOOKUP(ROW(E1),$E$80:$G$90,3,0),),4

14、,奔跑班,赵六,王五,4,兄弟班,郑琦,赵六,4,兄弟班,钱吧,4,兄弟班,周就,4,嘉宾班,乌石,4,嘉宾班,胡三,4,嘉宾班,陆玖,26,多条件判断,IF(F92100)*I95:I100),3,2,6,B,222,SUM(IF(H95:H100=B)+(H95:H100=C),I95:I100),4,2,8,A,234,5,2,10,C,341,6,2,12,D,223,464,7,2,14,56,563,28,乘法口诀,D103:D108*E102:J102,1,2,3,4,5,6,1,1,2,3,4,5,6,2,2,4,6,8,10,12,3,3,6,9,12,15,18,4,4,8

15、,12,16,20,24,5,5,10,15,20,25,30,6,6,12,18,24,30,36,29,直接得到单元格中的结果,val(E109),57,6*8+9,VBA编辑见模块1,8,我有(2+2)*2双隐形的翅膀,30,提取同行不重复定义函数,tt(E111:I111,-),1-2-3-4-5-6,1,2,3,4,5,6,VBA编辑见模块2,杭州-石家庄-美国-北京,杭州,杭州,石家庄,美国,美国,北京,北京-丽江-杭州-上海,北京,北京,丽江,杭州,杭州,上海,31,提取上班时间,IF(E115-D115)*244,4,FLOOR(E115-D115)*24,0.1),上午上班日

16、期时间,上午下班日期时间,上午上班,上午加班,合计上班,提取加班时间,FLOOR(MAX(0,(TEXT(G115,h:mm:ss)-TIMEVALUE(5:00:00 PM)*24),0.5),2015/8/1 7:28,2015/8/1 12:35,4.00 ,1.00 ,8.00 ,2015/8/3 7:26,2015/8/3 12:00,4.00 ,0.50 ,8.00 ,下午上班日期时间,下午下班日期时间,下午上班,下午加班,合计加班,2015/8/1 13:07,2015/8/1 20:47,4.00 ,3.50 ,4.50 ,2015/8/3 13:00,2015-8-3 21:

17、00:51,4.00 ,4.00 ,4.50 ,32,本函数示例正则表达式如何处理字符串（VBA编辑见模块3）,正则应用, 应用示例 , 示例函数参数说明 ,=Reg(单元格或内容,1)-提取汉字,30303角铝,角铝,=Reg(单元格或内容,1),提取汉字,=Reg(单元格或内容,2)-剔除汉字,TP6+9A+TP6z中空玻璃,TP6+9A+TP6z,=Reg(单元格或内容,2),剔除汉字,=Reg(单元格或内容,3)-提取字母不分大小写,ck-t7-R050,cktR,=Reg(单元格或内容,3),提取字母不分大小写,=Reg(单元格或内容,4)-提取大写字母,101TYHfd01,TYH

18、,=Reg(单元格或内容,4),提取大写字母,=Reg(单元格或内容,5)-提取小写字母,ck-t7-050,ckt,=Reg(单元格或内容,5),提取小写字母,=Reg(单元格或内容,6)-剔除数字,30303角铝,角铝,=Reg(单元格或内容,6),剔除数字,=Reg(单元格或内容,7)-提取数字,101TYHfd01,10101,=Reg(单元格或内容,7),提取数字,=Reg(单元格或内容,8)-提取数字及小数点,1.234阿波罗,1.234,=Reg(单元格或内容,8),提取数字及小数点,33,三角函数,角度,弧度,正弦,余弦,正切,余切,正弦,弧度,SIN(E130),300000,0.524 ,0.500 ,0.866 ,0.577 ,1.732 ,1.732 ,COS(E130),450000,0.785 ,0.707 ,0.707 ,1.000 ,1.000 ,1.000 ,TAN(E130),600000,1.