EXCEL常用计算公式大全

12EXCEL常用计算公式大日常办公中少不了EXCEL 表格的运用，方便、快速、高效。

EXCEL表格是基于计算公式而运行的，只有掌握了基本公式，才能完成各项运算。

经过长时间收集，整理了一些常用工具，基本能够满足日常运用。

贵州龙山源酒业有限公司贵州贞丰联系人：石启洪3EXCEL常用计算公式大全45文/石启洪整理67整理说明8EXCEL 是日常办公中少不了的运算工具，方便、快速、高效。

9由于EXCEL表格是基于计算公式而运行的，只有掌握了基本公式，才能完成各项运算。

经过长时10间收集，整理了一些常用工具，基本能够满足日常运用。

11公式由{}包括的为数组公式，在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter 12键，自动生成数组公式。

131415161718192021222324252627282930313233目录34常用求和运算 (3)35一、单组数据加减乘除运算 (3)36二、多组数据加减乘除运算 (3)37三、其它应用函数代表 (5)38和字篇 (5)39一、求和 (5)40二、与和 (5)41产字篇 (7)42一、生产 (7)43二、产量 (7)44三、产品 (8)45四、产值 (10)46五、产生 (10)47大小于篇 (10)48一、大于 (11)49二、小于 (12)50数据篇 (12)51工资篇 (14)52价格篇 (15)53计算篇 (16)54库房篇 (27)55一、进库 (28)56二、出库 (28)57三、库存 (28)58成字篇 (28)59一、成绩 (28)60二、与成 (31)61金字篇 (33)62一、金额 (33)63二、奖金 (35)64时间篇 (35)65一、时间 (35)66二、年 (37)67三、月 (39)68四、日 (41)69销售篇 (44)707172EXCEL常用计算公式大全7374常用求和运算75一、单组数据加减乘除运算：76771 单组数据求加和公式：=（A1+B1）78举例：单元格A1：B1区域依次输入了数据10和5，计算：在C1中输入 =A1+B1 后点击键盘“Enter（确定）”79键后，该单元格就自动显示10与5的和15。

802 单组数据求减差公式：=（A1-B1）81举例：在C1中输入 =A1-B1 即求10与5的差值5，电脑操作方法同上；823 单组数据求乘法公式：=（A1*B1）83举例：在C1中输入 =A1*B1 即求10与5的积值50，电脑操作方法同上；844 单组数据求乘法公式：=（A1/B1）举例：在C1中输入 =A1/B1 即求10与5的商值2，电脑操作方法同上；85865 其它应用：87在D1中输入 =A1^3 即求5的立方（三次方）；在E1中输入 =B1^（1/3）即求10的立方根888990小结：91在单元格输入的含等号的运算式，Excel中称之为公式，都是数学里面的基本运算，只不过在计算机上有的92运算符号发生了改变——“×”与“*”同、“÷”与“/”同、“^”与“乘方”相同，开方作为乘方的逆运算，93把乘方中和指数使用成分数就成了数的开方运算。

这些符号是按住电脑键盘“Shift”键同时按住键盘第二排相对应的数字符号即可显示。

9495如果同一列的其它单元格都需利用刚才的公式计算，只需要先用鼠标左键点击一下刚才已做好公式的单元96格，将鼠标移至该单元格的右下角，带出现十字符号提示时，开始按住鼠标左键不动一直沿着该单元格依次往下拉到你需要的某行同一列的单元格下即可，即可完成公司自动复制，自动计算。

9798二、多组数据加减乘除运算：991 多组数据求加和公式：(常用)100101举例说明：=SUM（A1:A10）,表示同一列纵向从A1到A10的所有数据相加；102=SUM（A1:J1）,表示不同列横向从A1到J1的所有第一行数据相加；1032 多组数据求乘积公式：（较常用）104举例说明：=PRODUCT（A1:J1）表示不同列从A1到J1的所有第一行数据相乘；105=PRODUCT（A1:A10）表示同列从A1到A10的所有的该列数据相乘；3 多组数据求相减公式：(很少用)106107举例说明：=A1-SUM（A2:A10）表示同一列纵向从A1到A10的所有该列数据相减；=A1-SUM（B1:J1）表示不同列横向从A1到J1的所有第一行数据相减；1081094 多组数据求除商公式：(极少用)110举例说明：=A1/PRODUCT（B1:J1）表示不同列从A1到J1的所有第一行数据相除；=A1/PRODUCT（A2:A10）表示同列从A1到A10的所有的该列数据相除；111三、其它应用函数代表：1121131 平均函数 =AVERAGE（:）；1142 最大值函数 =MAX （:）；1153 最小值函数 =MIN （:）；1164 统计函数 =COUNTIF（:）：：B5，”>60”)117说明：统计分数大于60分的人数，注意，条件要加双引号，在英文状态下输入。

和字篇118一、求和1191201、对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)1212、对生产表于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}1223、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}1234、对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*D2:D10)}1245、对姓的女职工工资求和:{=SUM((LEFT(A2:A10)="")*(C2:C10="女")*D2:D10)}1256、LEFT(left)：解释，就是最左边的（最开始的）如：石启洪最左边就是“石”对应RIGHT(right)最右边的（最后的）。

1261277、对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})}8、对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2)1281299、对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2)13010、计算成绩在60-80分之间合计数与个数:求和131{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)*B2:B11),ROW(2:11)^0)}，求个数132{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)}13311、既求积也求和:=IF(D2<>"",PRODUCT(C2:D2),SUM(OFFSET(E2,-3,,3)))13412、按文字描述求和:{=SUM(ISNUMBER(FIND(A$2:A$8,D2))*B$2:B$8)}13513、求积、求和两相136宜:=SUM(IF(C2="",INDIRECT("E"&LOOKUP(1,0/ISERROR((0/$C$1:C1="")),ROW($C$2:C2))&":E"&(ROW()-1)),C2*D 1372))14、将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))}13813915、将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))}140二、与和1411、求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3}))1421432、求数组中最大值:=LARGE(B2:B10,1)1443、求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9)1454、求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)}1465、求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2)1476、用SUM函数计数:{=SUM((B2:B9="男")*1)}7、求1累加到100之和:{=SUM(ROW(1:100))}1481498、多个工作表不同区域求前三名产量和:{=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)))}1501519、计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10)15210、计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000")15311、求前三名和后三名的数据之和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2:B10,4)) 15412、计算A1：A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}15513、分别统计收入和支出:收入{=SUM(IF(B2:B13>0,B2:B13))};支出156{=SUM(IF(SUBSTITUTE(IF(B2:B13<>"",B2:B13,0),"负","-")*1<0,SUBSTITUTE(B2:B13,"负","-")*1))}15714、给公式添加运算说明:=CONCATENATE("你好",B2,"2008")&T(N("公式含义:连接“你好”和单元格B2、“2008”158"))15、计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10="一车间")+(B2:B10="三车间")*(C2:C10="女159160"),D2:D10))}16、统计小米、华为和联想已隹出手机个数:=SUM(COUNTIF(B2:B11,"*"&{"小米","华为","联想"}&"*"))16116217、统计二班和三班数学竞赛获奖人数:=SUM(COUNTIFS(B2:B11,{"二班","三班"},C2:C11,"数学*"))16318、提取产品最后报价和最高报价:{=INDEX(C:C,MAX((A2:A11="B")*ROW(2:11)))}19、计算文具类产品和家具类产品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH("(?具类164165",A2:A11)),B2:B11)),"0.00%")}16620、统计文具类和厨具类产品的最低单价:=DMIN(A1:B11,2,D1:D2)16721、计算大于等于前10个最大产量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)16822、计算8月份笔筒和毛笔的进货数量:{=SUM(IF(MONTH(A2:A11)=8,IF((B1:H1="笔筒")+(B1:H1="毛笔169"),B2:H11)))}23、统计家具类和文具类产品在1月份的出库次数:{=SUM((B2:B11={"文具类","家具类170171"})*(IF(C2:C11>0,MONTH(C2:C11)=1)))}17224、计算员工工作天数和月数:=DATEDIF(B2,C2,"M")17325、对班级和成绩升序排174列:{=1*MID(SMALL(1*($A2:$A12&TEXT($B2:$B12,"000")),ROW($A$2:$A$12)-1),{1,2},{1,3})}17526、根据下拉列表中的时间和产品名计算销量冠军:{=INDEX(A2:A11,MATCH(MAX(OFFSET(C2,,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2)),OFFSET(C2,,MAT 176177CH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),0))}17827、分别计算每个班第一名的成绩和:名次179{=MAX(SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,,COLUMNS(C:I)))*(B$2:B$31=K2))};名180{=OFFSET(A$1,MOD(MAX((SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,,COLUMNS(C:I)))*1000+ROW($2:$31))*(B$2: 181B$31=K2)),1000)-1,)}28、根据评委评分和权重分配统计最后得分:{=SUM(B2:F8*(A2:A8=B10)*TRANSPOSE(I2:I6))}18218329、区分工种和达标率计算奖金:=LOOKUP(C2*100,1*LEFT(达标与奖金标准!B$1:K$1,FIND("%",达标与奖金标184准!B$1:K$1)-1),OFFSET(达标与奖金标准!B$1,MATCH(B2,达标与奖金标准!A$2:A$4,0),,,10))18530、提取每日累计出库数和每日库存数:日期=INDEX(A:A,ROW(A1)*2);累计出库数186{=SUM(ISODD(ROW(INDIRECT("2:"&(ROW(A1)*2)+1)))*OFFSET(C$1,1,,ROWS($1:1)*2))};每日库存数187{=SUM(SUMIF(OFFSET(B$1,1,,ROW(A1)*2),{"进库","出库"},C$2)*{1,-1})}31、计算生产部人数和非生产部人数:生产部人数{=SUM((NOT(ISERR(FIND("车间",A2:A11)))*B2:C11))};非生产188189部人数{=SUM((ISERR(FIND("车间",A2:A11)))*B2:C11)}19032、根据利息和存款数计算存款达到1万元需要几个月:=NPER(A2,0,-B2,C2)*1219133、根据投资金额、时间和目标收益计算增长率:=RATE(B2,0,-A2,C2)19234、根据贷款、利率和时间计算某段时间的利息:=CUMIPMT(B2/12,C2*12,A2,1,24,0)19335、根据贷款、利率和时间计算需偿还的本金:=CUMPRINC(B2/12,C2*12,A2,1,24,0)36、以年限总和折旧法计算折旧值:=SYD(A$2,B$2,C$2,ROW(A1))194195产字篇196一、生产1971981、对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)2、对生产表于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}1992003、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}2014、计算每小时生产产值:=PRODUCT(C2:E2)2025、生产A产品且无异常的机台平均产量:=AVERAGEIFS(C2:C11,B2:B11,"A",D2:D11,"")2036、根据员工生产产品的废品率记分:=MAX(MIN(6-(B2*100-5),10),0)2047、哪种产品生产次数最多:{=TEXT(MODE(B2:B9*1),"00")}8、本月需要完成几批货物生产:{=SUM(N(B2:B11=TEXT(TODAY(),"MMMM")))}2052069、计算生产车间异常机台个数:=COUNT(C2:C11)20710、计算生产速度是否达标:=YEARFRAC(C2,D2)<=(E2/B2)11、计算生产部人数和非生产部人数:生产部人数{=SUM((NOT(ISERR(FIND("车间",A2:A11)))*B2:C11))};非生产208209部人数{=SUM((ISERR(FIND("车间",A2:A11)))*B2:C11)}21012、根据卡机数据判断员工部门:=CHOOSE(MATCH(--RIGHT(A2,3),{1,38,14,11,8,21,43,9,28},0),"生产部","业211务部","总务部","人事部","食堂","保卫部","采购部","送货部","财务部")二、产量2122131、求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3}))2、多个工作表不同区域求前三名产量和:{=SUM(LARGE(CHOOSE({1,2,3,4,5},A组!B2:B9,B组!B2:B9,C组!B2:B9,D 214215组!B2:B9,E组!B2:B9),ROW(1:3)))}2163、跨表求积:=PRODUCT(产量表:单价表!B2)4、计算各组别第三名产量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}2172185、计算每个车间大于250的平均产量:=AVERAGEIF(B2:C11,">250")6、统计产量达标率:=TEXT(COUNTIF(B2:B11,">=800")/COUNT(B2:B11),"0.00")2192207、计算大于等于前10个最大产量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)2218、按周汇总产量:{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$AF1,2))+(COLUMN($B1:$AF1)-1)=(1+(COLUMN(A1)-1)*7))*$B2:$AF2) 222223}2249、计算所有人的一周产量并排225名:{=INDEX(1:1,RIGHT(LARGE(SUBTOTAL(9,OFFSET($A2:$A8,,COLUMN($B:$J)-1,,))*10+COLUMN($B:$J)-1,COLUMN 226(A1)))+1)}22710、根据下拉列表引用不同工作表的产量:=INDIRECT(ADDRESS(11,2,1,1,D1))22811、计算连续三天之总产量大于等于25万元的次22913、计算产量最高的季230度:=TEXT(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,{0,3,6,9},1,3))),SUBTOTAL(9,OFFSET(A1,{0,3,6,9},1,3)),0),"[ 231DBNum1]0季度")23214、罗列12月中产量倒数第一名次数最多233者:{=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$ 2342:$B$13,$B$2:$B$13,0)=ROW($2:$13)-1),ROW($2:$13),1048576),ROW(A1)))&""}15、根据产量计算员工产量得分:{=LOOKUP(B2,{3,0.5}*(ROW($1:$11)-1))}23523616、提取产量冠军的组别:=IF(COUNTA(B2:E2),LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1),"")23717、建立文件目录:=HYPERLINK("[E:\产量表\"&TEXT(ROW(1:1),"[DBNum1]")&"月产量238表.xlsx]sheet1!A1",TEXT(ROW(1:1),"[DBNum1]")&"月产量表")23918、选择产量最高工作表:{=HYPERLINK("#"&CHAR(64+MOD(MAX(SUBTOTAL(9,INDIRECT(CHAR(64+ROW(1:8))&"240组!B2:B11"))*100+ROW(1:8)),100))&"组!A1","跳至最大产量组")}24119、计算连续三天之总产量大于等于25万元的次242数:=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,,3))>=25))243三、产品2442451、求入库最多的产品数246量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={"A","B","C","D"})),(A2:A11={"A","B","C","D"})*1))}2472、计算A产品每日库存数:{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17="A")*(C2:C17-D2:D17))} 2483、计算C产品最大入库量:{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))}4、根据产品规格计算产品体249250积:=PRODUCT(LEFT(B2,FIND("*",B2)-1),MID(B2,FIND("*",B2)+1,FIND("*",B2,FIND("*",B2)+1)-1-FIND("*",B2 251)),RIGHT(B2,LEN(B2)-FIND("*",B2,FIND("*",B2)+1)))5、将产品型号规化:=IF(MID(A2,5,2)="00",A2,REPLACE(A2,5,,"00"))2522536、产品规格格式转换:=SUBSTITUTE(SUBSTITUTE(A2,"：","("),"*",")*")&")"2547、统计未检验完成的产品数:=COUNTBLANK(B2:B11)2558、统计季度最高产值合计:{=MAX(SUBTOTAL(9,OFFSET(B2,,COLUMN(B:E)-2,ROWS(2:10),1)))}2569、提取产品最后报价和最高报价:{=INDEX(C:C,MAX((A2:A11="B")*ROW(2:11)))}25710、计算文具类产品和家具类产品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH("(?具类",A2:A11)),B2:B11)),"0.00%")}25825911、统计售价850元以上的产品最低利率是多少:=DMIN(A1:D11,F4,F1:F2)26012、统计文具类和厨具类产品的最低单价:=DMIN(A1:B11,2,D1:D2)13、查看产品曾经销售的所有价261262位:{=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,"<="&E1)))} 26314、提取销量的前三名的外销产品名264称:{=LOOKUP(0,0/($B$2:$B$10*100+ROW($2:$10)=(LARGE(IF(RIGHT(A$2:A$10,3)="外265销)",B$2:B$10*100+ROW($2:$10)),ROW(A1)))),A$2:A$10)}26615、统计家具类和文具类产品在1月份的出库次数:{=SUM((B2:B11={"文具类","家具类267"})*(IF(C2:C11>0,MONTH(C2:C11)=1)))}26816、根据下拉列表中的时间和产品名计算销量冠269军:{=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))}27027117、根据下拉列表中的产品提取与销272量:{=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),"")}27327418、从多个产品相同单价的单价表中引用单275价:=SUMPRODUCT(COUNTIF(OFFSET(A$2,ROW($2:$4)-2,0,1,4),G2)*E$2:E$4)*H227620、区分大小写提取产品单价:{=MMULT((EXACT(B2:B11,TRANSPOSE(单价表!A2:A5)))*TRANSPOSE(单价277表!B2:B5),{1;1;1;1})}27821、罗列导致产品不良的主279因:{=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 280),ROW(A1)),ROW($2:$11)),COLUMN(A1)))),"")}28122、计算两个产品不同时期的单价:=HLOOKUP(MONTH(A2),IF(B2="塑胶机282",{0,3,8;25,19,18},{0,5,10;12.5,10,11}),2)28323、产生混合编号:=TEXT(COUNTIF(C$1:C1,"*"),"[DBNum2]")&TEXT(ROW()-MATCH("々",C$1:C1),"(000);;") 28424、计算产品体积:=IF(ISERROR(FIND("/",B2)),B2^3,PRODUCT(1*TRIM(MID(SUBSTITUTE(B2,"/",REPT("285",100)),{1,100,200},100))))25、按月份统计每个产品的机器返修数286287量:=SUMPRODUCT(ISNUMBER(FIND(F$2,$A$2:$A$11))*(TEXT($B$2:$B$11,"YM")=TEXT($E3,"YM"))*$C$2:$C$11) 28826、从产品规格中提取直径、长、宽:长(直径)=LOOKUP(9.9E+307,--RIGHT(IF(ISNUMBER(FIND("×289",A2)),REPLACE(A2,FIND("×",A2),100,""),A2),ROW($1:$100)));宽=IF(ISNUMBER(FIND("×290",A2)),--RIGHT(A2,LEN(A2)-FIND("×",A2)),0)29127、根据产品规格计算体积:体积292=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD4,"(L)","*"),"(W)","*"),"(H)",""))293禁止录入不完整的产品规格:数据有效性设置-自定义:=ISNUMBER(SEARCH("长?*宽?*高?*",B2))294四、产值2951、计算每小时生产产值:=PRODUCT(C2:E2)2962972、每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2)298五、产生2993001、产生100到200之间带小数的随机数:=RAND()*(200-100)+1003012、产生ll到20之间的不重复随机整数:{=RANK(A2:A11,A2:A11)+10}3023、产生-50到100之间的随机整数:=RANDBETWEEN(-50,100)4、产生1到100之问的奇数随机303304数:{=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100))}3055、产生1到10之间随机不重复306数:{=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10))=0,ROW($1:$10)),RANDBETWEEN(1,12-ROW()))}3076、根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)<60,"不及格",IF(AVERAGE(B2:D2)<90,"良好308",IF(AVERAGE(B2:D2)<100,"优秀","满分")))7、产生大、小写字母A到Z的序列:大写字母=CHAR(ROW(A65))，小写字母=CHAR(ROW(A65)+32)3093108、产生大写字母A到ZZ的字母序311列:=IF(ROW()<27,CHAR(MOD(ROW()-1,26)+65),CHAR(65+(ROW()-1)/26-1))&IF(ROW()>26,CHAR(MOD(ROW()-1,26)+ 31265),"")3139、产生三个字母组成的随机字符314串:=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))10、用公式产生换行符:=A2&CHAR(10)&B231531611、在A列产生1到12月的英文月份名:=TEXT((ROW())&"-1","mmmm")31712、在A1:A7区域产生星期一到星期日的英文全称:{=TEXT(ROW(1:7)+1,"DDDD")}13、产生“坐标”:=CHAR(64+COLUMN(A1))31831914、产生每两行累加1的编号:=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,,,ROW()-1))+1,""))15、计算成绩排名，不能产生并列名320321次:=SUMPRODUCT(--((A$2:A$15=A2)*(($C$2:$C$15)+1/ROW($C$2:$C$15))>C2+1/ROW(2:2)))+132216、在具有合并单元格的A列产生自然数编号:=1+COUNT(OFFSET($A$2,,,ROW()-2,))32317、引用合并区域时防止产生0值:=IF(A1<>"",A1,OFFSET(B1,-1,))32418、以固定余额递减法计算资产折旧值:=DB(A$2,B$2,C$2,ROW(A1),12)32519、以双倍余额递减法计算资产折旧值:=DDB(A$2,B$2,C$2,1,2)20、使用双倍余额递减法计算任何期间的资产折旧值:=VDB(A$2,B$2,C$2*12,7,12,2)32632721、以超产80为单位计算超产奖:{=SUM(MROUND(B2:B11-700,80*IF(B2:B11>=700,1,-1)))/80*50}328大小于篇329一、大于3303311、对生产表于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}3322、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}3333、汇总鼠标所在列于600的数据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600") 3344、判断单元格的数据类型:=TEXT(A2,"大于○;小于○;○;文本")5、计算每个车间大于250的平均产量:=AVERAGEIF(B2:C11,">250")3353366、统计大于80分的三好学生个数:{=COUNTIFS(B2:B11,"三好学生",C2:C11,">80")}3377、计算语文成绩大于90分者的最高总成绩:=DMAX(A1:E11,5,G1:G2)8、计算大于等于前10个最大产量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)3383399、计算第一次收入金额大于30元时的金额是多少:=INDEX(B:B,MIN(IF((A2:A11=A2)*(B2:B11>30),ROW(2:11))) 34010、记录最后一次销量大于3000的地址:{=ADDRESS(MOD(MAX((IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*ROW(B2:D7)+(IF(ISNUMBER(B2:D7),B2:D7,0)>300 3413420)*COLUMN(B2:D7)*1000),1000),INT(MAX((IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*ROW(B2:D7)+(IF(ISNUMBER(B2:343D7),B2:D7,0)>3000)*COLUMN(B2:D7)*1000)/1000))}34411、计第奎续三天之总产量大于等于25万元的次345数:=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,,3))>=25))34612、统计各班所有科目成绩大于60分者人347数:{=MMULT(N(TRANSPOSE(A2:A21)=H3:H6),N(COUNTIF(OFFSET(C2:F2,ROW(2:21)-2,),">=60")=4))}34813、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}349三、小于3501、判断单元格的数据类型:=TEXT(A2,"大于○;小于○;○;文本")3512、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}352353数据篇3543551、对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)3562、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}35737、求所有工作表相同区域数据求和:=SUM(A组:E组!B2:B9)3583、求前三名和后三名的数据求和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2:B10,4)) 3594、汇总鼠标所在列于600的数据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600") 3605、汇总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)3616、汇总3的倍数列的数据:{=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10))}3627、将数据转换成接近6的倍数:=MROUND(A1,6)8、计算值为l万的整数倍数的数据个数:{=SUM(N((B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)))}3633649、判断两列数据是否相等:=IF(A1=B1,"相等","")10、计算两列数据同行相等的个数:{=SUM(N(A1:A10=B1:B136536611、有选择地汇总数据:{=SUM(IF(A2:A11={"A组","C组"},C2:C11))}36712、返回自动换行单元格的第二行数据:=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))13、将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))}36836914、将数据显示为小数点对齐:=TEXT(B2,"#.0????")37015、判断单元格的数据类型:=TEXT(A2,"大于○;小于○;○;文本")37116、将三列数据交换位置:{=TEXT({1,-1,0},C1:C5&";"&"!"&B1:B5&";"&A1:A5)}37217、将数据重复显示5次:=SUBSTITUTE(TEXT(A2&"?",""),"?","")37318、将数据对齐显示，将空白以“.”占位:=WIDECHAR(REPT(".",10-LEN(B2))&B2)19、从卡机数据提取打卡时间:=730>--MID(A2,14,4)37437520、根据卡机数据判断员工部门:=CHOOSE(MATCH(--RIGHT(A2,3),{1,38,14,11,8,21,43,9,28},0),"生产部","业376务部","总务部","人事部","食堂","保卫部","采购部","送货部","财务部")21、从汉字与数字混合字串中提取温度数据:{=MAX(IFERROR(--RIGHT(LEFT(B2,LEN(B2)-1),ROW($1:$10)),0))} 37737822、计算两列数据相同个数:{=SUM(COUNTIF(A2:A11,B2:B11))}37923、统计区域中不重复数据个数:{=SUM(1/COUNTIF(B2:B8,B2:B8))}38024、提取不重复数据:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)}38125、消除单位提取数据:{=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100))),))*IF(LEFT(A2)="-",-1,1)}38226、提取引用区域右下角的数据:=INDIRECT(ADDRESS(ROW(B3:D7)+ROWS(B3:D7)-1,COLUMN(B3:D7)+COLUMNS(B3:D7)-1))38338427、合并三个工作表的数据:=INDIRECT(CHOOSE(MOD(ROW(A2)-1,3)+1,"一年级!A"&INT((ROW(A3))/3)+1,"二年385级!A"&INT((ROW(A3))/3)+1,"三年级!A"&INT((ROW(A3))/3)+1))28、累计数据:{=SUM(OFFSET(B$2,,,ROW()-1))}38638729、多列、隔行数据汇总:{=SUM(MMULT(D2:G11,TRANSPOSE(COLUMN(D:G)^0))*(A2:A11="还珠"))}38830、从簿中选择性引用数据:=INDEX($A:$B,ROW(A1)*3-2,COLUMN(A:A))38931、插入空行分割数据:=IF(MOD(ROW(),3)>0,INDEX(A:A,ROW(A2)*2/3),"")39032、在同一行查找数据:{=HLOOKUP(MAX(A2:H2),IF({1;0},B2:H2,A2:G2),2,FALSE)}39133、不区分大小写判断两列相同数据个数:{=COUNT(MATCH(A2:A11,B2:B11,0))}34、提取A列最后一个数据:{=INDIRECT("A"&(MATCH(1,0/(A:A<>""))))}39239335、利用公式对入库表进行数据分394析:{=INDEX(B:B,SMALL(IF(MATCH(B$2:B$200,B$2:B$200,0)=ROW($2:$200)-1,ROW($2:$200),65536),ROW(A1)))&""}39539636、对合并区域进行数据查询:=OFFSET(B1,MATCH(G2,A2:A13,0)-1+MATCH(H2,{"冰箱","空调","洗衣机397"},0),MATCH(I2,C1:E1,0))39837、反向查找数据:=LEN(A2)-LOOKUP(100,SEARCH(B2,A2,ROW($1:$99)))-LEN(B2)+239938、使用通配符查找所有符合条件的数据:{=IFERROR(LOOKUP(1,0/SEARCH("*医院400*",IF(COUNTIF($C$1:C3,A$2:A$12)=0,A$2:A$12,)),A$2:A$12),"")}39、引用单元格数据同时引用格式:=IF(TODAY()>A2,"",TEXT(A2,格式))40140240、分别汇总当前表以外的所有工作表数403据:AcSht=GET.CELL(62);sheets=GET.WORKBOOK(1);WorkBook=GET.CELL(66);{=IFERROR(REPLACE(INDEX(sheets,S MALL(IF(TRANSPOSE(sheets)<>AcSht,ROW(INDIRECT("1:"&COLUMNS(sheets)))),ROW(A2))),1,LEN(WorkBook)+2," 404405"),"")}40641、利用列表框筛选数据:筛选=IF(GET.OBJECT(78,"列表框 1"),GET.OBJECT(78,"列表框4071")*TRANSPOSE(ROW(sheet1!$A$2:$A$8)))40842、将数据分列，提取省市县:分列=EVALUATE("{"""&SUBSTITUTE(SUBSTITUTE(Sheet1!$A5,"省","省"","""),"市409","市"",""")&"""}")41043、将数据间隔着色:条件格式:=MOD(SUM(N($B$2:$B2<>$B$1:$B1)),2)=041144、B列中禁止输入重复数据:数据有效性设置-自定义:=COUNTIF(B:B,B8)=141245、仅允许录入英文:数据有效性设置-自定义:=SUM(--(ERROR.TYPE(INDIRECT(MID(SUBSTITUTE(A2,"",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),1)&1))=3))=LEN(SUBSTITUTE(A2," ",""))41341446、强制录入规化的日期:数据有效性设置-自定义:=(LEN(A2)=8)*TEXT(A2,"#-00-00")41547、让A列只能输入质数:数据有效性设置-自定义:=OR(A2=2,A2=3,PRODUCT(MOD(A2,ROW(INDIRECT("2:"&416INT(A2^0.5))))))41748、设置D列只能录入男职工的:数据有效性设置-自定义:=VLOOKUP(D2,A:B,2,0)="男"41849、禁止录入不完整的产品规格:数据有效性设置-自定义:=ISNUMBER(SEARCH("长?*宽?*高?*",B2))419工资篇4204211、对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*D2:D10)}4222、对姓的女职工工资求和:{=SUM((LEFT(A2:A10)="")*(C2:C10="女")*D2:D10)}4233、对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})}4244、对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2)4255、对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2)4266、汇总所有车间人员工资:=SUMPRODUCT(--NOT(ISERROR(FIND("车间",A2:A10))),C2:C10)4277、汇总A组男职工的工资:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男A组")*D2:D11),ROW(2:11)^0)}8、设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明428429细!A$1,ROW()/3+1,0),""))4309、工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10)10、根据工作时间计算12月工资:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500}))43143211、计算二车间女职工的平均工资:{=AVERAGE(IF((B2:B10="二车间")*(C2:C10="女"),D2:D10))}43312、计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10="一车间")+(B2:B10="三车间")*(C2:C10="女"),D2:D10))}43443513、计算平均工资(不忽略无薪人员):=ROUND(AVERAGEA(B2:B10),2)14、生成工资结算日期:=TEXT(EOMONTH(B2,0)+1,"e年M月D日")43643715、统计两倍工资的加班小时数:=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&":"&EOMONTH(B2,0))),"AAA")="六438"))*843916、计算临时工的工资:=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,"分",""),"小时",":"))/(8/24)*50,) 44017、计算本日工时工资:=(HOUR(C2-TIMEVALUE("8:00"))-1-ROUNDUP(B2-TIMEVALUE("8:00"),0))*644118、计算每日工时工资:=8*5*IF(WEEKDAY(A2,2)<6,1,1.5)+(B2-8)*5*1.519、工资查询:{=IFERROR(OFFSET(D1,MATCH(F2&G2&H2,A2:A11&B2:B11&C2:C11,0),),G2&"无此人")}442443价格篇4441、求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)}4454462、按季度引用不同价格并统计金额与累计:{=IF(A2<>"累计",LOOKUP(COUNTIF(OFFSET(A$1,1,0,ROWS($2:2),)," 447合计")+1,ROW($2:$5)-1,F$2:F$5)*B2,SUM(C1:C$2*(A1:A$2<>"累计")))}3、按图书编号汇总价格:图书=EVALUATE("{"""&SUBSTITUTE(Sheet1!B2,"/",""",""")&"""}")448449函数篇4501、平均函数 =AVERAGE（:）4514522、最大值函数 =MAX （:）4533、最小值函数 =MIN （:）4544、统计函数 =COUNTIF（:）4555、用SUM函数计数:{=SUM((B2:B9="男")*1)}4566、FLOOR函数处理正负数混合区域:=FLOOR(A1*100,10*(IF(A1>0,1,-10)))7、让VLOOKUP函数在多区域查找:=VLOOKUP(A11,CHOOSE(MATCH(B11,{"一年级","二年级","三年级457458"},0),A1:B9,D1:E9,G2:H9),2,0)459职工篇4604611、对姓的女职工工资求和:{=SUM((LEFT(A2:A10)="")*(C2:C10="女")*D2:D10)}4622、汇总A组男职工的工资:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男A组")*D2:D11),ROW(2:11)^0)}4633、根据年龄判断职工是否退休:=OR(AND(B2="男",C2>60),AND(B2="女",C2>55))4644、根据年龄与职务判断职工是否退休:=OR(AND(B2="男",D2>60+(C2="干部")*3),AND(B2="女",D2>55+(C2="干部465")*3))4665、计算二车间女职工的平均工资:{=AVERAGE(IF((B2:B10="二车间")*(C2:C10="女"),D2:D10))}4676、计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10="一车间")+(B2:B10="三车间")*(C2:C10="女"),D2:D10))}4684697、计算女职工的最大年龄:{=MAX((B2:B11="女")*C2:C11)}4708、计算每个职工的得分:=LOOKUP(,-FIND(B2,{"A**","A*","A","B**","B*","B","C**","C*","C","D"}),11-ROW($1:$10))4714729、根据汇总男、女职工总数:男{=SUM(--ISODD(MID(B2:B10,15,3)))};女{=SUM(--ISEVEN(MID(B2:B10,15,3)))} 47310、设置D列只能录入男职工的:数据有效性设置-自定义:=VLOOKUP(D2,A:B,2,0)="男"474计算篇4754761、计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10)4772、计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000")4783、计算车间男性与女性人员的差:=SUM(SUMIFS(C2:C11,B2:B11,{"女","男"},A2:A11,"*车间")*{-1,1})4794、计算参保人数:=SUMPRODUCT((C2:C11="是")*1)5、计算A1：A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}4804816、计算1到10的自然数的积:=FACT(10)7、计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49)4824838、计算1到15之间奇数相乘的结果:=FACTDOUBLE(15)4849、计算每小时生产产值:=PRODUCT(C2:E2)48510、计算车间盈亏:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1;1;1}))48611、计算各组别第三名产量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}48712、计算C产品最大入库量:{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))}13、计算累计入库数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)}48848914、计算每日库存数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)}49015、计算A产品每日库存数:{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17="A")*(C2:C17-D2:D17))}16、在不同班级有同名前提下计算学生人491492数:{=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)),ROW(2:17)^0))}49317、计算前进中学参赛人494数:{=SUM(IFERROR(1/MMULT(N((A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17))*(A2:A17="前进中学495")),ROW(2:17)^0),0))}49618、计算达标率:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)}19、计算成绩在60-80分之间合计数与个数:求和497498{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)*B2:B11),ROW(2:11)^0)}，求个数499{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)}50020、计算象棋比赛对局次数l:=COMBIN(B1,B2)50121、计算五项比赛对局总次数:{=SUM(COMBIN(B2:B5,2))}50222、计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2)50323、计算中奖率:=TEXT(1/PERMUT(B1,B2),"0.00%")50424、计算最大公约数:=GCD(B1:B5)50525、计算最小公倍数:=LCM(B1:B5)。