《最新EXCEL常用计算公式大全.docx》由会员分享，可在线阅读，更多相关《最新EXCEL常用计算公式大全.docx（44页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-dateEXCEL常用计算公式大全EXCEL常用计算公式大全贵州龙山源酒业有限公司贵州贞丰联系人：石启洪qq：478094808日常办公中少不了EXCEL 表格的运用，方便、快速、高效。EXCEL表格是基于计算公式而运行的，只有掌握了基本公式，才能完成各项运算。经过长时间收集，整理了一些常用工具，基本能够满足日常运用。EXCEL常用计算公式大全-EXCEL常用计算公式大全文/
2、石启洪 整理整理说明 EXCEL 是日常办公中少不了的运算工具，方便、快速、高效。由于EXCEL表格是基于计算公式而运行的，只有掌握了基本公式，才能完成各项运算。经过长时间收集，整理了一些常用工具，基本能够满足日常运用。公式由包括的为数组公式，在复制粘贴到单元后先去掉然后按住Shift键+Ctrl键再按Enter键，自动生成数组公式。目 录常用求和运算.3一、 单组数据加减乘除运算.3二、 多组数据加减乘除运算.3三、 其它应用函数代表.5和字篇.5一、 求和.5二、 与和.5产字篇.7一、 生产.7二、 产量.7三、 产品.8四、产值.10五、 产生.10大小于篇.10一、大于.11二、 小
3、于.12数据篇.12工资篇.14价格篇.15计算篇.16库房篇.27一、进库.28二、出库.28三、库存.28成字篇.28 一、成绩.28 二、与成.31金字篇.33一、金额.33二、奖金.35时间篇.35一、 时间.35二、 年.37三、 月.39四、 日.41销售篇 .44EXCEL常用计算公式大全常用求和运算一、单组数据加减乘除运算：1 单组数据求加和公式：=（A1+B1） 举例： 单元格A1：B1区域依次输入了数据10和5， 计算： 在C1中输入 =A1+B1 后点击键盘“Enter（确定）”键后，该单元格就自动显示10与5的和15。2 单组数据求减差公式：=（A1-B1） 举例：在C
4、1中输入 =A1-B1 即求10与5的差值5，电脑操作方法同上；3 单组数据求乘法公式：=（A1*B1） 举例：在C1中输入 =A1*B1 即求10与5的积值50，电脑操作方法同上； 4 单组数据求乘法公式：=（A1/B1） 举例：在C1中输入 =A1/B1 即求10与5的商值2，电脑操作方法同上；5 其它应用： 在D1中输入 =A13 即求5的立方（三次方）； 在E1中输入 =B1（1/3） 即求10的立方根小结： 在单元格输入的含等号的运算式，Excel中称之为公式，都是数学里面的基本运算，只不过在计算机上有的运算符号发生了改变 “”与“*”同、“”与“/”同、“”与“乘方”相同，开方作为
5、乘方的逆运算，把乘方中和指数使用成分数就成了数的开方运算。这些符号是按住电脑键盘“Shift”键同时按住键盘第二排相对应的数字符号即可显示。如果同一列的其它单元格都需利用刚才的公式计算，只需要先用鼠标左键点击一下刚才已做好公式的单元格，将鼠标移至该单元格的右下角，带出现十字符号提示时，开始按住鼠标左键不动一直沿着该单元格依次往下拉到你需要的某行同一列的单元格下即可，即可完成公司自动复制，自动计算。二、多组数据加减乘除运算：1 多组数据求加和公式：(常用) 举例说明：=SUM（A1:A10）,表示同一列纵向从A1到A10的所有数据相加； =SUM（A1:J1）,表示不同列横向从A1到J1的所有第
6、一行数据相加； 2 多组数据求乘积公式：（较常用） 举例说明：=PRODUCT（A1:J1）表示不同列从A1到J1的所有第一行数据相乘； =PRODUCT（A1:A10）表示同列从A1到A10的所有的该列数据相乘；3 多组数据求相减公式：(很少用) 举例说明：=A1-SUM（A2:A10）表示同一列纵向从A1到A10的所有该列数据相减； =A1-SUM（B1:J1）表示不同列横向从A1到J1的所有第一行数据相减；4 多组数据求除商公式：(极少用) 举例说明：=A1/PRODUCT（B1:J1）表示不同列从A1到J1的所有第一行数据相除； =A1/PRODUCT（A2:A10）表示同列从A1到A
7、10的所有的该列数据相除；三、其它应用函数代表： 1 平均函数 =AVERAGE（:）； 2 最大值函数 =MAX （:）； 3 最小值函数 =MIN （:）； 4 统计函数 =COUNTIF（:）：B5，”60”) 说明：统计分数大于60分的人数，注意，条件要加双引号，在英文状态下输入。 和字篇一、求和 1、对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)2、对生产表中大于100的产量进行求和:=SUM(B2:B11100)*B2:B11)3、对生产表大于110或者小于100的数据求和:=SUM(B2:B11110)*B2:B11)4、对一车间男性职工的工资求和:=SUM(
8、B2:B10=一车间)*(C2:C10=男)*D2:D10)5、 对姓赵的女职工工资求和:=SUM(LEFT(A2:A10)=赵)*(C2:C10=女)*D2:D10)6、 LEFT(left)：解释，就是最左边的 （最开始的）如：石启洪 最左边就是“石”对应RIGHT(right)最右边的 （最后的）。7、 对1400到1600之间的工资求和:=SUM(SUMIF(B2:B10,60)*(B2:B1160)*(B2:B1180),ROW(2:11)0)11、 既求积也求和:=IF(D2,PRODUCT(C2:D2),SUM(OFFSET(E2,-3,3)12、 按文字描述求和:=SUM(IS
9、NUMBER(FIND(A$2:A$8,D2)*B$2:B$8)13、 求积、求和两相宜:=SUM(IF(C2=,INDIRECT(E&LOOKUP(1,0/ISERROR(0/$C$1:C1=),ROW($C$2:C2)&:E&(ROW()-1),C2*D2)14、 将所有数据转换成保留两位小数再求和:=SUM(-TEXT(B2:B11*C2:C11,0.00)15、 将所有数据转换成保留两位小数再求和:=SUM(-TEXT(B2:B11*C2:C11,0.00)二、 与和1、 求前三名产量之和:=SUM(LARGE(B2:B10,1,2,3)2、 求数组中最大值:=LARGE(B2:B10
10、,1)3、 求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9)4、 求图书订购价格总和:=SUM(B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)5、 求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2)6、 用SUM函数计数:=SUM(B2:B9=男)*1)7、 求1累加到100之和:=SUM(ROW(1:100)8、 多个工作表不同区域求前三名产量和:=SUM(LARGE(CHOOSE(1,2,3,4,5,A组!B2:B9,B组!B2:B9,C组!B2:B9,D组!B2:B9,E组!B2:B9),ROW(1:3)9、 计算仓库进库数量之和:=
11、SUMIF(B2:B10,=进库,C2:C10)10、 计算仓库大额进库数量之和:=SUMIF(B2:B8,1000)11、 求前三名和后三名的数据之和:=SUMIF(B2:B10,&LARGE(B2:B10,4)+SUMIF(B2:B10,0,A1:A10)13、 分别统计收入和支出:收入=SUM(IF(B2:B130,B2:B13);支出=SUM(IF(SUBSTITUTE(IF(B2:B13,B2:B13,0),负,-)*1LARGE(B2:C11,11)*B2:C11)22、 计算8月份笔筒和毛笔的进货数量:=SUM(IF(MONTH(A2:A11)=8,IF(B1:H1=笔筒)+(B
12、1:H1=毛笔),B2:H11)23、 统计家具类和文具类产品在1月份的出库次数:=SUM(B2:B11=文具类,家具类)*(IF(C2:C110,MONTH(C2:C11)=1)24、 计算员工工作天数和月数:=DATEDIF(B2,C2,M)25、 对班级和成绩升序排列:=1*MID(SMALL(1*($A2:$A12&TEXT($B2:$B12,000),ROW($A$2:$A$12)-1),1,2,1,3)26、 根据下拉列表中的时间和产品名计算销量冠军:=INDEX(A2:A11,MATCH(MAX(OFFSET(C2,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)
13、*(B2:B11=K2),OFFSET(C2,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),0)27、 分别计算每个班第一名的成绩和姓名:名次=MAX(SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,COLUMNS(C:I)*(B$2:B$31=K2);名=OFFSET(A$1,MOD(MAX(SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,COLUMNS(C:I)*1000+ROW($2:$31)*(B$2:B$31=K2),1000)-1,)28、 根据评委评分和权重分配统计最后得分:=SU
14、M(B2:F8*(A2:A8=B10)*TRANSPOSE(I2:I6)29、 区分工种和达标率计算奖金:=LOOKUP(C2*100,1*LEFT(达标与奖金标准!B$1:K$1,FIND(%,达标与奖金标准!B$1:K$1)-1),OFFSET(达标与奖金标准!B$1,MATCH(B2,达标与奖金标准!A$2:A$4,0),10)30、 提取每日累计出库数和每日库存数:日期=INDEX(A:A,ROW(A1)*2);累计出库数=SUM(ISODD(ROW(INDIRECT(2:&(ROW(A1)*2)+1)*OFFSET(C$1,1,ROWS($1:1)*2);每日库存数=SUM(SUMI
15、F(OFFSET(B$1,1,ROW(A1)*2),进库,出库,C$2)*1,-1)31、 计算生产部人数和非生产部人数:生产部人数=SUM(NOT(ISERR(FIND(车间,A2:A11)*B2:C11);非生产部人数=SUM(ISERR(FIND(车间,A2:A11)*B2:C11)32、 根据利息和存款数计算存款达到1万元需要几个月:=NPER(A2,0,-B2,C2)*1233、 根据投资金额、时间和目标收益计算增长率:=RATE(B2,0,-A2,C2)34、 根据贷款、利率和时间计算某段时间的利息:=CUMIPMT(B2/12,C2*12,A2,1,24,0)35、 根据贷款、利
16、率和时间计算需偿还的本金:=CUMPRINC(B2/12,C2*12,A2,1,24,0)36、 以年限总和折旧法计算折旧值:=SYD(A$2,B$2,C$2,ROW(A1)产字篇一、生产1、 对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)2、 对生产表中大于100的产量进行求和:=SUM(B2:B11100)*B2:B11)3、 对生产表大于110或者小于100的数据求和:=SUM(B2:B11110)*B2:B11)4、 计算每小时生产产值:=PRODUCT(C2:E2)5、 生产A产品且无异常的机台平均产量:=AVERAGEIFS(C2:C11,B2:B11,A,D2
17、:D11,)6、 根据员工生产产品的废品率记分:=MAX(MIN(6-(B2*100-5),10),0)7、 哪种产品生产次数最多:=TEXT(MODE(B2:B9*1),00)8、 本月需要完成几批货物生产:=SUM(N(B2:B11=TEXT(TODAY(),MMMM)9、 计算生产车间异常机台个数:=COUNT(C2:C11)10、 计算生产速度是否达标:=YEARFRAC(C2,D2)250)6、 统计产量达标率:=TEXT(COUNTIF(B2:B11,=800)/COUNT(B2:B11),0.00)7、 计算大于等于前10个最大产量之和:=SUMPRODUCT(B2:C11LAR
18、GE(B2:C11,11)*B2:C11)8、 按周汇总产量:=SUM(WEEKDAY($B1,2)-WEEKDAY($B1:$AF1,2)+(COLUMN($B1:$AF1)-1)=(1+(COLUMN(A1)-1)*7)*$B2:$AF2)9、 计算所有人的一周产量并排名:=INDEX(1:1,RIGHT(LARGE(SUBTOTAL(9,OFFSET($A2:$A8,COLUMN($B:$J)-1,)*10+COLUMN($B:$J)-1,COLUMN(A1)+1)10、 根据下拉列表引用不同工作表的产量:=INDIRECT(ADDRESS(11,2,1,1,D1)11、 计算连续三天之
19、总产量大于等于25万元的次13、 计算产量最高的季度:=TEXT(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,0,3,6,9,1,3),SUBTOTAL(9,OFFSET(A1,0,3,6,9,1,3),0),DBNum10季度)14、 罗列12月中产量倒数第一名次数最多者名单:=INDEX(B:B,SMALL(IF(COUNTIF(B$2:B$13,B$2:B$13)=MAX(COUNTIF($B$2:$B$13,$B$2:$B$13)*(MATCH($B$2:$B$13,$B$2:$B$13,0)=ROW($2:$13)-1),ROW($2:$13),1048576),R
20、OW(A1)&15、 根据产量计算员工产量得分:=LOOKUP(B2,3,0.5*(ROW($1:$11)-1)16、 提取产量冠军的组别:=IF(COUNTA(B2:E2),LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1),)17、 建立文件目录:=HYPERLINK(E:产量表&TEXT(ROW(1:1),DBNum1)&月产量表.xlsxsheet1!A1,TEXT(ROW(1:1),DBNum1)&月产量表)18、 选择产量最高工作表:=HYPERLINK(#&CHAR(64+MOD(MAX(SUBTOTAL(9,INDIRECT(CHAR(64+ROW(1:8)&组
21、!B2:B11)*100+ROW(1:8),100)&组!A1,跳至最大产量组)19、 计算连续三天之总产量大于等于25万元的次数:=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,3)=25)三、产品1、 求入库最多的产品数量:=MAX(MMULT(TRANSPOSE(B2:B11)*(A2:A11=A,B,C,D),(A2:A11=A,B,C,D)*1)2、 计算A产品每日库存数:=MMULT(N(ROW(2:17)=TRANSPOSE(ROW(2:17),(B2:B17=A)*(C2:C17-D2:D17)3、 计算C产品最大入库量:=MA
22、X(MMULT(N(A2:A11=C),TRANSPOSE(B2:B11)*(A2:A11=C)4、 根据产品规格计算产品体积:=PRODUCT(LEFT(B2,FIND(*,B2)-1),MID(B2,FIND(*,B2)+1,FIND(*,B2,FIND(*,B2)+1)-1-FIND(*,B2),RIGHT(B2,LEN(B2)-FIND(*,B2,FIND(*,B2)+1)5、 将产品型号规范化:=IF(MID(A2,5,2)=00,A2,REPLACE(A2,5,00)6、 产品规格格式转换:=SUBSTITUTE(SUBSTITUTE(A2,：,(),*,)*)&)7、 统计未检验
23、完成的产品数:=COUNTBLANK(B2:B11)8、 统计季度最高产值合计:=MAX(SUBTOTAL(9,OFFSET(B2,COLUMN(B:E)-2,ROWS(2:10),1)9、 提取产品最后报价和最高报价:=INDEX(C:C,MAX(A2:A11=B)*ROW(2:11)10、 计算文具类产品和家具类产品最小利率:=TEXT(MIN(IF(ISNUMBER(SEARCH(?具类,A2:A11),B2:B11),0.00%)11、 统计售价850元以上的产品最低利率是多少:=DMIN(A1:D11,F4,F1:F2)12、 统计文具类和厨具类产品的最低单价:=DMIN(A1:B1
24、1,2,D1:D2)13、 查看产品曾经销售的所有价位:=IF(ROW(A1)SUM(1/COUNTIF(B$2:C$11,B$2:C$11),SMALL(B$2:C$11,1+COUNTIF(B$2:C$11,0,MONTH(C2:C11)=1)16、 根据下拉列表中的时间和产品名计算销量冠军:=INDEX(A2:A11,MATCH(MAX(OFFSET(C2,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),OFFSET(C2,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),0)17、 根据下拉列表中的产品提
25、取姓名与销量:=IFERROR(1/MOD(SMALL(IF(B2:B11=K1,1/SUBTOTAL(9,OFFSET(C2,ROW(2:11)-2,0,1,COLUMNS(C:H)+ROW(2:11),ROW(1:10),1),)18、 从多个产品相同单价的单价表中引用单价:=SUMPRODUCT(COUNTIF(OFFSET(A$2,ROW($2:$4)-2,0,1,4),G2)*E$2:E$4)*H220、 区分大小写提取产品单价:=MMULT(EXACT(B2:B11,TRANSPOSE(单价表!A2:A5)*TRANSPOSE(单价表!B2:B5),1;1;1;1)21、 罗列导致
26、产品不良的主因:=IFERROR(T(INDEX($A:$A,SMALL(IF($B$2:$B$11=LARGE(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),ROW(A1),ROW($2:$11),COLUMN(A1),)22、 计算两个产品不同时期的单价:=HLOOKUP(MONTH(A2),IF(B2=塑胶机,0,3,8;25,19,18,0,5,10;12.5,10,11),2)23、 产生混合编号:=TEXT(COUNTIF(C$1:C1,*),DBNum2)&TEXT(ROW()-MATCH(,C$1:C1),(000);)24、
27、计算产品体积:=IF(ISERROR(FIND(/,B2),B23,PRODUCT(1*TRIM(MID(SUBSTITUTE(B2,/,REPT( ,100),1,100,200,100)25、 按月份统计每个产品的机器返修数量:=SUMPRODUCT(ISNUMBER(FIND(F$2,$A$2:$A$11)*(TEXT($B$2:$B$11,YM)=TEXT($E3,YM)*$C$2:$C$11)26、 从产品规格中提取直径、长、宽:长(直径)=LOOKUP(9.9E+307,-RIGHT(IF(ISNUMBER(FIND(,A2),REPLACE(A2,FIND(,A2),100,),
28、A2),ROW($1:$100);宽=IF(ISNUMBER(FIND(,A2),-RIGHT(A2,LEN(A2)-FIND(,A2),0)27、 根据产品规格计算体积:体积=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD4,(L),*),(W),*),(H),)禁止录入不完整的产品规格:数据有效性设置-自定义:=ISNUMBER(SEARCH(长?*宽?*高?*,B2)四、产值1、 计算每小时生产产值:=PRODUCT(C2:E2)2、 每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,4,)
29、,D2*E2)五、产生1、 产生100到200之间带小数的随机数:=RAND()*(200-100)+1002、 产生ll到20之间的不重复随机整数:=RANK(A2:A11,A2:A11)+103、 产生-50到100之间的随机整数:=RANDBETWEEN(-50,100)4、 产生1到100之问的奇数随机数:=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100)5、 产生1到10之间随机不重复数:=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10)=0,ROW($1:$10),RA
30、NDBETWEEN(1,12-ROW()6、 根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)60,不及格,IF(AVERAGE(B2:D2)90,良好,IF(AVERAGE(B2:D2)100,优秀,满分)7、 产生大、小写字母A到Z的序列:大写字母=CHAR(ROW(A65)，小写字母=CHAR(ROW(A65)+32)8、 产生大写字母A到ZZ的字母序列:=IF(ROW()26,CHAR(MOD(ROW()-1,26)+65),)9、 产生三个字母组成的随机字符串:=CHAR(RANDBETWEEN(65,90)&CHAR(RANDBETWEEN(65,90)&CHAR(R
31、ANDBETWEEN(65,90)10、 用公式产生换行符:=A2&CHAR(10)&B211、 在A列产生1到12月的英文月份名:=TEXT(ROW()&-1,mmmm)12、 在A1:A7区域产生星期一到星期日的英文全称:=TEXT(ROW(1:7)+1,DDDD)13、 产生“坐标”:=CHAR(64+COLUMN(A1)14、 产生每两行累加1的编号:=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,ROW()-1)+1,)15、 计算成绩排名，不能产生并列名次:=SUMPRODUCT(-(A$2:A$15=A2)*($C$2:$C$15)+1/ROW($C$2:$C$15)C2+1/ROW(2:2)+116、 在具有合并单元格的A列产生自然数编号:=1+COUNT(OFFSET($A$2,ROW()-2,)17、 引用合并区域时防止产生0