2022年EXCEL常用计算公式大全

1、1 1 2 EXCEL常用运算公式大日常办公中少不了 EXCEL 表格的运用,便利、快速、高效；EXCEL表格是基于运算公式而运行的,只有把握了基本公式,才能完成各项运算；经过长时间收集,整理了一些常用工具,基本能够满意日常运用；贵州龙山源酒业有限公司贵州贞丰 联系人：石启洪qq ：4780948081 1 3 4 EXCEL常用运算公式大全5 6 文/ 石启洪整理7 8 整理说明 9 EXCEL 是日常办公中少不了的运算工具,便利、快速、高效；10 由于 EXCEL表格是基于运算公式而运行的,只有把握了基本公式, 才能完成各项运算； 经过长时 间收集,整理了一些常用工具,基本能够满意日常运用

2、；11 12 公式由 包括的为数组公式,在复制粘贴到单元后先去掉 然后按住 Shift 键+Ctrl 键再按 Enter 13 键,自动生成数组公式；14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 目录常用求和运算 .3 一、单组数据加减乘除运算.3 二、多组数据加减乘除运算.3 三、其它应用函数

3、代表 .5 和字篇 .5 一、求和 .5 二、与和 .5 产字篇 .7 一、生产 .7 二、产量 .7 三、产品 .8 四、产值 .10 五、产生 .10 大小于篇 .10 一、大于 .11 二、小于 .12 数据篇 .12 工资篇 .14 价格篇 .15 运算篇 .16 库房篇 .27 一、进库 .28 二、出库 .28 三、库存 .28 成字篇 .28 一、成果 .28 二、与成 .31 金字篇 .33 一、金额 .33 二、奖金 .35 时间篇 .35 一、时间 .35 二、年 .37 三、月 .39 四、日 .41 销售篇 .44 2 3 71 72 73 EXCEL常用运算公式大全7

4、4 75 常用求和运算Enter（确定）”76 一、单组数据加减乘除运算：77 1 单组数据求加和公式：=（ A1+B1）78 举例： 单元格 A1：B1 区域依次输入了数据10 和 5, 运算： 在 C1中输入 =A1+B1 后点击键盘 “79 键后,该单元格就自动显示10 与 5 的和 15；80 2 单组数据求减差公式：=（ A1-B1）81 举例：在 C1 中输入 =A1-B1 即求 10 与 5 的差值 5,电脑操作方法同上；82 3 单组数据求乘法公式：=（ A1*B1）83 举例：在 C1 中输入 =A1*B1 即求 10 与 5 的积值 50,电脑操作方法同上；84 4 单组数

5、据求乘法公式：=（ A1/B1）85 举例：在 C1 中输入 =A1/B1 即求 10 与 5 的商值 2,电脑操作方法同上；86 5 其它应用：87 在 D1中输入 =A13 即求 5 的立方（三次方）；88 在 E1中输入 =B1 （1/3 ） 即求 10 的立方根89 90 小结：91 在单元格输入的含等号的运算式,Excel 中称之为公式,都是数学里面的基本运算,只不过在运算机上有的92 运算符号发生了转变“ ” 与“* ” 同、“ ” 与“/ ” 同、“ ” 与“ 乘方” 相同,开方作为乘方的逆运算,93 把乘方中和指数使用成分数就成了数的开方运算；94 这些符号是按住电脑键盘“Sh

6、ift ” 键同时按住键盘其次排相对应的数字符号即可显示；95 假如同一列的其它单元格都需利用刚才的公式运算,只需要先用鼠标左键点击一下刚才已做好公式的单元格,96 将鼠标移至该单元格的右下角,带显现十字符号提示时,开头按住鼠标左键不动始终沿着该单元格依次往下拉到97 你需要的某行同一列的单元格下即可,即可完成公司自动复制,自动运算；3 4 98 99 二、多组数据加减乘除运算：最右边100 1 多组数据求加和公式： 常用 101 举例说明： =SUM（ A1:A10）, 表示同一列纵向从A1 到 A10 的全部数据相加；102 =SUM（ A1:J1 ） , 表示不同列横向从A1 到 J1

7、的全部第一行数据相加；103 2 多组数据求乘积公式：（较常用）104 举例说明： =PRODUCT（A1:J1 ）表示不同列从A1 到 J1 的全部第一行数据相乘；105 =PRODUCT（A1:A10 ）表示同列从A1 到 A10 的全部的该列数据相乘；106 3 多组数据求相减公式： 很少用 107 举例说明： =A1-SUM（A2:A10）表示同一列纵向从A1 到 A10 的全部该列数据相减；108 =A1-SUM（B1:J1 ）表示不同列横向从A1 到 J1 的全部第一行数据相减；109 4 多组数据求除商公式： 极少用 110 举例说明： =A1/PRODUCT（ B1:J1 ）表

8、示不同列从A1到 J1 的全部第一行数据相除；111 =A1/PRODUCT（ A2:A10）表示同列从A1 到 A10 的全部的该列数据相除；112 三、其它应用函数代表：113 1 平均函数 =AVERAGE（: ）；114 2 最大值函数 =MAX （: ）；115 3 最小值函数 =MIN （: ）；116 4 统计函数 =COUNTIF（: ）： B5,”60” 117 说明：统计分数大于60 分的人数,留意,条件要加双引号,在英文状态下输入；118 和字篇119 一、求和120 1 、对三组生产数据求和:=SUMB2:B7,D2:D7,F2:F7 121 2、对生产表中大于100

9、的产量进行求和:=SUMB2:B11100*B2:B11 122 3、对生产表大于110 或者小于100 的数据求和 :=SUMB2:B11110*B2:B11 123 4、对一车间男性职工的工资求和:=SUMB2:B10= 一车间 *C2:C10=男*D2:D10 124 5、对姓赵的女职工工资求和:=SUMLEFTA2:A10=赵 *C2:C10=女 *D2:D10 125 6、LEFTleft：说明,就是最左边的（最开头的）如：石启洪最左边就是“ 石” 对应RIGHTright126 的 （最终的）；4 5 127 7、对 1400 到 1600 之间的工资求和 :=SUMSUMIFB2

10、:B10,60*B2:B1160*B2:B1180,ROW2:110 133 11、既求积也求和 :=IFD2,PRODUCTC2:D2,SUMOFFSETE2,-3,3 134 12、按文字描述求和 :=SUMISNUMBERFINDA$2:A$8,D2*B$2:B$8 135 13、求积、求和两相136 宜:=SUMIFC2=,INDIRECTE&LOOKUP1,0/ISERROR0/$C$1:C1=,ROW$C$2:C2&:E&ROW-1,C2*D137 2 138 14、将全部数据转换成保留两位小数再求和 :=SUM-TEXTB2:B11*C2:C11,0.00 139 15、将全部数

11、据转换成保留两位小数再求和 :=SUM-TEXTB2:B11*C2:C11,0.00 140 141 二、与和142 1、求前三名产量之和 :=SUMLARGEB2:B10,1,2,3 143 2、求数组中最大值 :=LARGEB2:B10,1 144 3、求全部工作表相同区域数据之和 :=SUMA组:E 组.B2:B9 145 4、求图书订购价格总和 :=SUMB2:E2= 参考价格 .A$2:A$7* 参考价格 .B$2:B$7 146 5、求当前表以外的全部工作表相同区域的总和 :=SUM一月 : 五月 .B2 147 6、用 SUM函数计数 :=SUMB2:B9= 男 *1 148 7

12、、求 1 累加到 100 之和 :=SUMROW1:100 149 8、多个工作表不同区域求前三名产量和 :=SUMLARGECHOOSE1,2,3,4,5,A 组.B2:B9,B 组 .B2:B9,C 组 .B2:B9,D150 组.B2:B9,E 组.B2:B9,ROW1:3 151 9、运算仓库进库数量之和 :=SUMIFB2:B10,= 进库 ,C2:C10 152 10、运算仓库大额进库数量之和 :=SUMIFB2:B8,1000 153 11、求前三名和后三名的数据之和 :=SUMIFB2:B10,&LARGEB2:B10,4+SUMIFB2:B10,0,A1:A10 155 13

13、、分别统计收入和支出 : 收入 =SUMIFB2:B130,B2:B13; 支出156 =SUMIFSUBSTITUTEIFB2:B13,B2:B13,0, 负,-*1LARGEB2:C11,11*B2:C11 168 22、运算 8 月份笔筒和毛笔的进货数量 :=SUMIFMONTHA2:A11=8,IFB1:H1= 笔筒 +B1:H1= 毛笔169 ,B2:H11 170 23、统计家具类和文具类产品在 1 月份的出库次数 :=SUMB2:B11= 文具类 , 家具类171 *IFC2:C110,MONTHC2:C11=1 172 24、运算员工工作天数和月数 :=DATEDIFB2,C2

14、,M 173 25、对班级和成果升序排174 列:=1*MIDSMALL1*$A2:$A12&TEXT$B2:$B12,000,ROW$A$2:$A$12-1,1,2,1,3 175 26、依据下拉列表中的时间和产品名运算销量冠176 军:=INDEXA2:A11,MATCHMAXOFFSETC2,MATCHJ2,C1:H1,0-1,ROWS2:11,*B2:B11=K2,OFFSETC2,MAT177 CHJ2,C1:H1,0-1,ROWS2:11,*B2:B11=K2,0 178 27、分别运算每个班第一名的成果和姓名 : 名次179 =MAXSUBTOTAL9,OFFSETB$1,ROW

15、$2:$31-1,1,COLUMNSC:I*B$2:B$31=K2; 名180 =OFFSETA$1,MODMAXSUBTOTAL9,OFFSETB$1,ROW$2:$31-1,1,COLUMNSC:I*1000+ROW$2:$31*B$2:181 B$31=K2,1000-1, 182 28、依据评委评分和权重安排统计最终得分 :=SUMB2:F8*A2:A8=B10*TRANSPOSEI2:I6 183 29、区分工种和达标率运算奖金 :=LOOKUPC2*100,1*LEFT 达标与奖金标准 .B$1:K$1,FIND%, 达标与奖金标184 准.B$1:K$1-1,OFFSET 达标与

16、奖金标准 .B$1,MATCHB2,达标与奖金标准 .A$2:A$4,0,10 6 7 185 30、提取每日累计出库数和每日库存数: 日期 =INDEXA:A,ROWA1*2; 累计出库数非生产186 =SUMISODDROWINDIRECT2:&ROWA1*2+1*OFFSETC$1,1,ROWS$1:1*2;每日库存数187 =SUMSUMIFOFFSETB$1,1,ROWA1*2,进库 , 出库 ,C$2*1,-1 188 31、运算生产部人数和非生产部人数: 生产部人数 =SUMNOTISERRFIND 车间 ,A2:A11*B2:C11;189 部人数 =SUMISERRFIND

17、车间 ,A2:A11*B2:C11 190 32、依据利息和存款数运算存款达到1 万元需要几个月:=NPERA2,0,-B2,C2*12 191 33、依据投资金额、时间和目标收益运算增长率:=RATEB2,0,-A2,C2 192 34、依据贷款、利率和时间运算某段时间的利息:=CUMIPMTB2/12,C2*12,A2,1,24,0 193 35、依据贷款、利率和时间运算需偿仍的本金:=CUMPRINCB2/12,C2*12,A2,1,24,0 194 36、以年限总和折旧法运算折旧值:=SYDA$2,B$2,C$2,ROWA1 195 196 产字篇197 一、生产198 1、对三组生产

18、数据求和 :=SUMB2:B7,D2:D7,F2:F7 199 2、对生产表中大于 100 的产量进行求和 :=SUMB2:B11100*B2:B11 200 3、对生产表大于 110 或者小于 100 的数据求和 :=SUMB2:B11110*B2:B11 201 4、运算每小时生产产值 :=PRODUCTC2:E2 202 5、生产 A 产品且无反常的机台平均产量 :=AVERAGEIFSC2:C11,B2:B11,A,D2:D11, 203 6、依据员工生产产品的废品率记分 :=MAXMIN6-B2*100-5,10,0 204 7、哪种产品生产次数最多 :=TEXTMODEB2:B9*

19、1,00 205 8、本月需要完成几批货物生产 :=SUMNB2:B11=TEXTTODAY,MMMM 206 9、运算生产车间反常机台个数 :=COUNTC2:C11 207 10、运算生产速度是否达标 :=YEARFRACC2,D2250 219 6、统计产量达标率 :=TEXTCOUNTIFB2:B11,=800/COUNTB2:B11,0.00 220 7、运算大于等于前 10 个最大产量之和 :=SUMPRODUCTB2:C11LARGEB2:C11,11*B2:C11 221 8、按周汇总产222 量:=SUMWEEKDAY$B1,2-WEEKDAY$B1:$AF1,2+COLUM

20、N$B1:$AF1-1=1+COLUMNA1-1*7*$B2:$AF2223 224 9、运算全部人的一周产量并排225 名:=INDEX1:1,RIGHTLARGESUBTOTAL9,OFFSET$A2:$A8,COLUMN$B:$J-1,*10+COLUMN$B:$J-1,COLUMN226 A1+1 227 10、依据下拉列表引用不同工作表的产量 :=INDIRECTADDRESS11,2,1,1,D1 228 11、运算连续三天之总产量大于等于 25 万元的次229 13、运算产量最高的季230 度:=TEXTMATCHMAXSUBTOTAL9,OFFSETA1,0,3,6,9,1,3

21、,SUBTOTAL9,OFFSETA1,0,3,6,9,1,3,0,231 DBNum10季度 232 14、排列 12 月中产量倒数第一名次数最多者名233 单:=INDEXB:B,SMALLIFCOUNTIFB$2:B$13,B$2:B$13=MAXCOUNTIF$B$2:$B$13,$B$2:$B$13*MATCH$B$234 2:$B$13,$B$2:$B$13,0=ROW$2:$13-1,ROW$2:$13,1048576,ROWA1& 235 15、依据产量运算员工产量得分 :=LOOKUPB2,3,0.5*ROW$1:$11-1 236 16、提取产量冠军的组别 :=IFCOUN

22、TAB2:E2,LOOKUP1,0/ISTEXTB2:E2,B$1:E$1, 237 17、建立文件目录 产量表 &TEXTROW1:1,DBNum1& 月产量238 表.xlsxsheet1.A1,TEXTROW1:1,DBNum1& 月产量表 239 18、挑选产量最高工作表 :=HYPERLINK#&CHAR64+MODMAXSUBTOTAL9,INDIRECTCHAR64+ROW1:8&240 组.B2:B11*100+ROW1:8,100& 组.A1, 跳至最大产量组 241 19、运算连续三天之总产量大于等于 25 万元的次242 数:=SUMPRODUCTNSUBTOTAL9,O

23、FFSET$B$1,ROW1:10-1,3=25 243 8 9 244 三、产品245 1、求入库最多的产品数246 量:=MAXMMULTTRANSPOSEB2:B11*A2:A11=A,B,C,D,A2:A11=A,B,C,D*1 247 2、运算 A 产品每日库存数 :=MMULTNROW2:17=TRANSPOSEROW2:17,B2:B17=A*C2:C17-D2:D17 248 3、运算 C产品最大入库量 :=MAXMMULTNA2:A11=C,TRANSPOSEB2:B11*A2:A11=C 249 4、依据产品规格运算产品体250 积:=PRODUCTLEFTB2,FIND*

24、,B2-1,MIDB2,FIND*,B2+1,FIND*,B2,FIND*,B2+1-1-FIND*,B2251 ,RIGHTB2,LENB2-FIND*,B2,FIND*,B2+1 252 5、将产品型号规范化 :=IFMIDA2,5,2=00,A2,REPLACEA2,5,00 253 6、产品规格格式转换 :=SUBSTITUTESUBSTITUTEA2,：,*,*& 254 7、统计未检验完成的产品数 :=COUNTBLANKB2:B11 255 8、统计季度最高产值合计 :=MAXSUBTOTAL9,OFFSETB2,COLUMNB:E-2,ROWS2:10,1 256 9、提取产品

25、最终报价和最高报价 :=INDEXC:C,MAXA2:A11=B*ROW2:11 257 10、运算文具类产品和家具类产品最小利率 :=TEXTMINIFISNUMBERSEARCH. 具类258 ,A2:A11,B2:B11,0.00% 259 11、统计售价 850 元以上的产品最低利率是多少 :=DMINA1:D11,F4,F1:F2 260 12、统计文具类和厨具类产品的最低单价 :=DMINA1:B11,2,D1:D2 261 13、查看产品曾经销售的全部价262 位:=IFROWA1SUM1/COUNTIFB$2:C$11,B$2:C$11,SMALLB$2:C$11,1+COUN

26、TIFB$2:C$11,0,MONTHC2:C11=1 268 16、依据下拉列表中的时间和产品名运算销量冠269 军:=INDEXA2:A11,MATCHMAXOFFSETC2,MATCHJ2,C1:H1,0-1,ROWS2:11,*B2:B11=K2,OFFSETC2,MAT270 CHJ2,C1:H1,0-1,ROWS2:11,*B2:B11=K2,0 271 17、依据下拉列表中的产品提取姓名与销272 量:=IFERROR1/MODSMALLIFB2:B11=K1,1/SUBTOTAL9,OFFSETC2,ROW2:11-2,0,1,COLUMNSC:H+ROW2:1273 1,RO

27、W1:10,1, 9 10274 18、从多个产品相同单价的单价表中引用单275 价:=SUMPRODUCTCOUNTIFOFFSETA$2,ROW$2:$4-2,0,1,4,G2*E$2:E$4*H2 276 20、区分大小写提取产品单价 :=MMULTEXACTB2:B11,TRANSPOSE单价表 .A2:A5*TRANSPOSE 单价277 表.B2:B5,1;1;1;1 278 21、排列导致产品不良的主279 因:=IFERRORTINDEX$A:$A,SMALLIF$B$2:$B$11=LARGEIFFREQUENCY$B$2:$B$11,$B$2:$B$11,$B$2:$B$1

28、1280 ,ROWA1,ROW$2:$11,COLUMNA1, 281 22、运算两个产品不同时期的单价 :=HLOOKUPMONTHA2,IFB2=塑胶机282 ,0,3,8;25,19,18,0,5,10;12.5,10,11,2 283 23、产生混合编号 :=TEXTCOUNTIFC$1:C1,*,DBNum2&TEXTROW-MATCH ,C$1:C1,000; 284 24、运算产品体积 :=IFISERRORFIND/,B2,B23,PRODUCT1*TRIMMIDSUBSTITUTEB2,/,REPT 285 ,100,1,100,200,100 286 25、按月份统计每个产

29、品的机器返修数287 量:=SUMPRODUCTISNUMBERFINDF$2,$A$2:$A$11*TEXT$B$2:$B$11,YM=TEXT$E3,YM*$C$2:$C$11 288 26、从产品规格中提取直径、长、宽 : 长 直径 =LOOKUP9.9E+307,-RIGHTIFISNUMBERFIND289 ,A2,REPLACEA2,FIND ,A2,100,A2,ROW$1:$100; 宽=IFISNUMBERFIND 290 ,A2,-RIGHTA2,LENA2-FIND ,A2,0 291 27、依据产品规格运算体积 : 体积292 =EVALUATESUBSTITUTESU

30、BSTITUTESUBSTITUTESheet1.XFD4,L,*,W,*,H, 293 禁止录入不完整的产品规格 : 数据有效性设置-自定义 :=ISNUMBERSEARCH长 .* 宽.*高.*,B2 294 295 四、产值296 1、运算每小时生产产值 :=PRODUCTC2:E2 297 2、每隔 4 行合计产值 :=IFMODROW,5=1,SUMOFFSETF2,-4,4,D2*E2 298 299 五、产生300 1、产生 100 到 200 之间带小数的随机数 :=RAND*200-100+100 301 2、产生 ll 到 20 之间的不重复随机整数 :=RANKA2:A1

31、1,A2:A11+10 302 3、产生 -50 到 100 之间的随机整数 :=RANDBETWEEN-50,100 303 4、产生 1 到 100 之问的奇数随机1011304 数:=INDEXIFMODROW1:100,2,ROW1:100,ROW1:100-1,RANDBETWEEN1,100 305 5、产生 1 到 10 之间随机不重复306 数:=LARGEIFCOUNTIFA$1:A1,ROW$1:$10=0,ROW$1:$10,RANDBETWEEN1,12-ROW 307 6、依据同学成果自动产生评语 :=IFAVERAGEB2:D260, 不及格 ,IFAVERAGEB

32、2:D290, 良好308 ,IFAVERAGEB2:D2100, 优秀 , 满分 309 7、产生大、小写字母 A 到 Z 的序列 : 大写字母 =CHARROWA65,小写字母 =CHARROWA65+32 310 8、产生大写字母 A 到 ZZ 的字母序311 列:=IFROW26,CHARMODROW-1,26+312 65, 313 9、产生三个字母组成的随机字符314 串:=CHARRANDBETWEEN65,90&CHARRANDBETWEEN65,90&CHARRANDBETWEEN65,90 315 10、用公式产生换行符 :=A2&CHAR10&B2 316 11、在 A列

33、产生 1 到 12 月的英文月份名 :=TEXTROW&-1,mmmm 317 12、在 A1:A7 区域产生星期一到星期日的英文全称 :=TEXTROW1:7+1,DDDD 318 13、产生“ 坐标”:=CHAR64+COLUMNA1 319 14、产生每两行累加 1 的编号 :=IFROW=1,1,IFMODROW,3,COUNTOFFSETA$1,ROW-1+1, 320 15、运算成果排名,不能产生并列名321 次:=SUMPRODUCT-A$2:A$15=A2*$C$2:$C$15+1/ROW$C$2:$C$15C2+1/ROW2:2+1 322 16、在具有合并单元格的 A列产生

34、自然数编号 :=1+COUNTOFFSET$A$2,ROW-2, 323 17、引用合并区域时防止产生 0 值:=IFA1,A1,OFFSETB1,-1, 324 18、以固定余额递减法运算资产折旧值 :=DBA$2,B$2,C$2,ROWA1,12 325 19、以双倍余额递减法运算资产折旧值 :=DDBA$2,B$2,C$2,1,2 326 20、使用双倍余额递减法运算任何期间的资产折旧值 :=VDBA$2,B$2,C$2*12,7,12,2 327 21、以超产 80 为单位运算超产奖 :=SUMMROUNDB2:B11-700,80*IFB2:B11=700,1,-1/80*50 32

35、8 329 大小于篇330 一、大于331 1、对生产表中大于 100 的产量进行求和 :=SUMB2:B11100*B2:B11 332 2、对生产表大于 110 或者小于 100 的数据求和 :=SUMB2:B11110*B2:B11 333 3、汇总鼠标所在列中大于 600 的数1112334 据:=SUMIFINDIRECTR2C&CELLcol&:R8C&CELLcol,FALSE,600 335 4、判定单元格的数据类型 :=TEXTA2, 大于 ; 小于 ; ; 文本 336 5、运算每个车间大于 250 的平均产量 :=AVERAGEIFB2:C11,250 337 6、统计大

36、于 80 分的三好同学个数 :=COUNTIFSB2:B11, 三好同学 ,C2:C11,80 338 7、运算语文成果大于 90 分者的最高总成果 :=DMAXA1:E11,5,G1:G2 339 8、运算大于等于前 10 个最大产量之和 :=SUMPRODUCTB2:C11LARGEB2:C11,11*B2:C11 340 9、运算第一次收入金额大于 30 元时的金额是多少 :=INDEXB:B,MINIFA2:A11=A2*B2:B1130,ROW2:11 341 10、记录最终一次销量大于 3000 的地342 址:=ADDRESSMODMAXIFISNUMBERB2:D7,B2:D7

37、,03000*ROWB2:D7+IFISNUMBERB2:D7,B2:D7,0300343 0*COLUMNB2:D7*1000,1000,INTMAXIFISNUMBERB2:D7,B2:D7,03000*ROWB2:D7+IFISNUMBERB2:344 D7,B2:D7,03000*COLUMNB2:D7*1000/1000 345 11、计第奎续三天之总产量大于等于 25 万元的次346 数:=SUMPRODUCTNSUBTOTAL9,OFFSET$B$1,ROW1:10-1,3=25 347 12、统计各班全部科目成果大于 60 分者人348 数:=MMULTNTRANSPOSEA2

38、:A21=H3:H6,NCOUNTIFOFFSETC2:F2,ROW2:21-2,=60=4 349 13、对生产表大于 110 或者小于 100 的数据求和 :=SUMB2:B11110*B2:B11 350 351 三、小于352 1、判定单元格的数据类型 :=TEXTA2, 大于 ; 小于 ; ; 文本 353 2、对生产表大于 110 或者小于 100 的数据求和 :=SUMB2:B11110*B2:B11354 355 数据篇356 1、对三组生产数据求和 :=SUMB2:B7,D2:D7,F2:F7 357 2、对生产表大于 110 或者小于 100 的数据求和 :=SUMB2:B

39、11110*B2:B11 358 37、求全部工作表相同区域数据求和 :=SUMA 组:E 组 .B2:B9 359 3、求前三名和后三名的数据求和 :=SUMIFB2:B10,&LARGEB2:B10,4+SUMIFB2:B10,600 362 5、汇总奇数行数据 :=SUMPRODUCTMODROW2:13,2*C2:C13 1213363 6、汇总 3 的倍数列的数据 :=SUMIFMODCOLUMNA:I,3=0,A2:I10 364 7、将数据转换成接近 6 的倍数 :=MROUNDA1,6 365 8、运算值为 l 万的整数倍数的数据个数 :=SUMNB2:B10*C2:C10=R

40、OUNDDOWNB2:B10*C2:C10,-4 366 9、判定两列数据是否相等 :=IFA1=B1, 相等 , 367 10、运算两列数据同行相等的个数 :=SUMNA1:A10=B1:B1 368 11、有挑选地汇总数据 :=SUMIFA2:A11=A 组,C 组,C2:C11 369 12、返回自动换行单元格的其次行数据 :=RIGHTA2,LENA2-FINDCHAR10,A2 370 13、将全部数据转换成保留两位小数再求和 :=SUM-TEXTB2:B11*C2:C11,0.00 371 14、将数据显示为小数点对齐 :=TEXTB2,#.0. 372 15、判定单元格的数据类型

41、 :=TEXTA2, 大于 ; 小于 ; ; 文本 373 16、将三列数据交换位置 :=TEXT1,-1,0,C1:C5&;&.&B1:B5&;&A1:A5 374 17、将数据重复显示 5 次:=SUBSTITUTETEXTA2&.,., 375 18、将数据对齐显示,将空白以“. ” 占位 :=WIDECHARREPT.,10-LENB2&B2 376 19、从卡机数据提取打卡时间 :=730-MIDA2,14,4 377 20、依据卡机数据判定员工部门 :=CHOOSEMATCH-RIGHTA2,3,1,38,14,11,8,21,43,9,28,0, 生产部 , 业378 务部 ,

42、总务部 , 人事部 , 食堂 , 保卫部 , 选购部 , 送货部 , 财务部 379 21、从汉字与数字混合字串中提取温度数据 :=MAXIFERROR-RIGHTLEFTB2,LENB2-1,ROW$1:$10,0 380 22、运算两列数据相同个数 :=SUMCOUNTIFA2:A11,B2:B11 381 23、统计区域中不重复数据个数 :=SUM1/COUNTIFB2:B8,B2:B8 382 24、提取不重复数据 :=INDEXB:B,MATCH0,COUNTIF$D$1:D1,B$2:B$11,0+1 383 25、排除单位提取数据 :=MAXIFERRORABSLEFTA2,RO

43、W$1:$100,*IFLEFTA2=-,-1,1 384 26、提取引用区域右下角的数385 据:=INDIRECTADDRESSROWB3:D7+ROWSB3:D7-1,COLUMNB3:D7+COLUMNSB3:D7-1 386 27、合并三个工作表的数据 :=INDIRECTCHOOSEMODROWA2-1,3+1,一年级 .A&INTROWA3/3+1, 二年387 级.A&INTROWA3/3+1, 三年级 .A&INTROWA3/3+1 388 28、累计数据 :=SUMOFFSETB$2,ROW-1 389 29、多列、隔行数据汇总 :=SUMMMULTD2:G11,TRANS

44、POSECOLUMND:G0*A2:A11=赵仍珠 390 30、从电话簿中挑选性引用数据 :=INDEX$A:$B,ROWA1*3-2,COLUMNA:A 391 31、插入空行分割数据 :=IFMODROW,30,INDEXA:A,ROWA2*2/3, 392 32、在同一行查找数据 :=HLOOKUPMAXA2:H2,IF1;0,B2:H2,A2:G2,2,FALSE 1314393 33、不区分大小写判定两列相同数据个数 :=COUNTMATCHA2:A11,B2:B11,0 394 34、提取 A 列最终一个数据 :=INDIRECTA&MATCH1,0/A:A 395 35、利用公

45、式对入库表进行数据分396 析:=INDEXB:B,SMALLIFMATCHB$2:B$200,B$2:B$200,0=ROW$2:$200-1,ROW$2:$200,65536,ROWA1&397 398 36、对合并区域进行数据查询 :=OFFSETB1,MATCHG2,A2:A13,0-1+MATCHH2, 冰箱 , 空调 , 洗衣机399 ,0,MATCHI2,C1:E1,0 400 37、反向查找数据 :=LENA2-LOOKUP100,SEARCHB2,A2,ROW$1:$99-LENB2+2 401 38、使用通配符查找全部符合条件的数据 :=IFERRORLOOKUP1,0/S

46、EARCH*医院402 *,IFCOUNTIF$C$1:C3,A$2:A$12=0,A$2:A$12,A$2:A$12, 403 39、引用单元格数据同时引用格式 :=IFTODAYA2,TEXTA2, 格式 404 40、分别汇总当前表以外的全部工作表数405 据:AcSht=GET.CELL62;sheets=GET.WORKBOOK1;WorkBook=GET.CELL66;=IFERRORREPLACEINDEXsheets,S406 MALLIFTRANSPOSEsheetsAcSht,ROWINDIRECT1:&COLUMNSsheets,ROWA2,1,LENWorkBook+2

47、,407 , 408 41、利用列表框挑选数据 : 挑选 =IFGET.OBJECT78, 列表框 1,GET.OBJECT78, 列表框409 1*TRANSPOSEROWsheet1.$A$2:$A$8 410 42、将数据分列,提取省市县 : 分列 =EVALUATE&SUBSTITUTESUBSTITUTESheet1.$A5, 省, 省, 市411 , 市,& 412 43、将数据间隔着色 : 条件格式 :=MODSUMN$B$2:$B2$B$1:$B1,2=0 413 44、B 列中禁止输入重复数据 : 数据有效性设置- 自定义 :=COUNTIFB:B,B8=1 414 45、仅

48、答应录入英文姓名 : 数据有效性设置- 自定义 :=SUM-ERROR.TYPEINDIRECTMIDSUBSTITUTEA2, 415 ,ROWINDIRECT1:&LENSUBSTITUTEA2, ,1&1=3=LENSUBSTITUTEA2, , 416 46、强制录入规范化的日期 : 数据有效性设置-自定义 :=LENA2=8*TEXTA2,#-00-00 417 47、让 A列只能输入质数 : 数据有效性设置- 自定义 :=ORA2=2,A2=3,PRODUCTMODA2,ROWINDIRECT2:& 418 INTA20.5 419 48、设置 D列只能录入男职工的姓名 : 数据有

49、效性设置- 自定义 :=VLOOKUPD2,A:B,2,0= 男 420 49、禁止录入不完整的产品规格 : 数据有效性设置- 自定义 :=ISNUMBERSEARCH长.*宽.* 高.*,B2 421 422 工资篇1415423 1、对一车间男性职工的工资求和:=SUMB2:B10= 一车间 *C2:C10=男*D2:D10 女424 2、对姓赵的女职工工资求和:=SUMLEFTA2:A10=赵*C2:C10=女*D2:D10 425 3、对 1400 到 1600 之间的工资求和:=SUMSUMIFB2:B10,0,1,3,5,10,300,500,500,500,500 434 11、

50、运算二车间女职工的平均工资:=AVERAGEIFB2:B10=二车间 *C2:C10=女,D2:D10 435 12、运算一车间和三车间女职工的平均工资:=AVERAGEIFB2:B10= 一车间 +B2:B10=三车间 *C2:C10=436 ,D2:D10 437 13、运算平均工资 不忽视无薪人员:=ROUNDAVERAGEAB2:B10,2 438 14、生成工资结算日期:=TEXTEOMONTHB2,0+1,e 年 M月 D日 439 15、统计两倍工资的加班小时数:=SUMPRODUCT-TEXTROWINDIRECTB2&:&EOMONTHB2,0,AAA= 六440 *8 44

51、1 16、运算暂时工的工资:=ROUNDTIMEVALUESUBSTITUTESUBSTITUTEB2, 分,小时 ,:/8/24*50, 442 17、运算本日工时工资:=HOURC2-TIMEVALUE8:00-1-ROUNDUPB2-TIMEVALUE8:00,0*6 443 18、运算每日工时工资:=8*5*IFWEEKDAYA2,26,1,1.5+B2-8*5*1.5 444 19、工资查询 :=IFERROROFFSETD1,MATCHF2&G2&H2,A2:A11&B2:B11&C2:C11,0,G2& 无此人 445 446 价格篇447 1、求图书订购价格总和 :=SUMB2

52、:E2= 参考价格 .A$2:A$7* 参考价格 .B$2:B$7 448 2、按季度引用不同价格并统计金额与累计 :=IFA2 累计 ,LOOKUPCOUNTIFOFFSETA$1,1,0,ROWS$2:2,449 合计 +1,ROW$2:$5-1,F$2:F$5*B2,SUMC1:C$2*A1:A$2 累计 450 3、按图书编号汇总价格 : 图书 =EVALUATE&SUBSTITUTESheet1.B2,/,& 451 452 函数篇1516453 1、平均函数 =AVERAGE（: ）454 2、最大值函数 =MAX （: ）455 3、最小值函数 =MIN （: ）456 4、统计

53、函数 =COUNTIF（: ）457 5、用 SUM函数计数 :=SUMB2:B9= 男 *1 458 6、FLOOR函数处理正负数混合区域 :=FLOORA1*100,10*IFA10,1,-10 459 7、让 VLOOKUP函数在多区域查找 :=VLOOKUPA11,CHOOSEMATCHB11,一年级 , 二年级 , 三年级460 ,0,A1:B9,D1:E9,G2:H9,2,0 461 462 职工篇463 1、对姓赵的女职工工资求和 :=SUMLEFTA2:A10= 赵*C2:C10= 女*D2:D10 464 2、汇总 A 组男职工的工资 :=MMULTTRANSPOSENB2:

54、B11&C2:C11=男 A组*D2:D11,ROW2:110 465 3、依据年龄判定职工是否退休 :=ORANDB2=男,C260,ANDB2= 女,C255 466 4、依据年龄与职务判定职工是否退休 :=ORANDB2=男,D260+C2= 干部 *3,ANDB2= 女,D255+C2= 干部467 *3 468 5、运算二车间女职工的平均工资 :=AVERAGEIFB2:B10= 二车间 *C2:C10= 女,D2:D10 469 6、运算一车间和三车间女职工的平均工资 :=AVERAGEIFB2:B10= 一车间 +B2:B10= 三车间 *C2:C10= 女470 ,D2:D10

55、 471 7、运算女职工的最大年龄 :=MAXB2:B11= 女 *C2:C11 472 8、运算每个职工的得473 分:=LOOKUP,-FINDB2,A*,A*,A,B*,B*,B,C*,C*,C,D,11-ROW$1:$10 474 9、依据身份证号码汇总男、女职工总数 : 男=SUM-ISODDMIDB2:B10,15,3; 女475 =SUM-ISEVENMIDB2:B10,15,3 476 10、设置 D列只能录入男职工的姓名 : 数据有效性设置- 自定义 :=VLOOKUPD2,A:B,2,0= 男 477 478 运算篇女 , 男,A2:A11,*车间 *-1,1 479 1、

56、运算仓库进库数量之和:=SUMIFB2:B10,= 进库 ,C2:C10 480 2、运算仓库大额进库数量之和:=SUMIFB2:B8,1000 481 3、运算车间男性与女性人员的差:=SUMSUMIFSC2:C11,B2:B11,482 4、运算参保人数:=SUMPRODUCTC2:C11=是 *1 1617483 5、运算 A1：A10 区域正数的平方和 :=SUMSQIFA1:A100,A1:A10 484 6、运算 1 到 10 的自然数的积 :=FACT10 485 7、运算 50 到 60 之间的整数相乘的结果 :=FACT60/FACT49 486 8、运算 1 到 15 之间

57、奇数相乘的结果 :=FACTDOUBLE15 487 9、运算每小时生产产值 :=PRODUCTC2:E2 488 10、运算车间盈亏 :=SUMMMULTB3:E50*B3:E5,1;1;1;1,MMULTB3:E5=TRANSPOSEROW2:11,B2:B11 492 14、运算每日库存数 :=MMULTNROW2:11=TRANSPOSEROW2:11,B2:B11-C2:C11 493 15、运算 A 产品每日库存数 :=MMULTNROW2:17=TRANSPOSEROW2:17,B2:B17=A*C2:C17-D2:D17 494 16、在不同班级有同名前提下运算同学人495 数

58、:=SUM1/MMULTNA2:A17&B2:B17&C2:C17=TRANSPOSEA2:A17&B2:B17&C2:C17,ROW2:170 496 17、运算前进中学参赛人497 数:=SUMIFERROR1/MMULTNA2:A17&B2:B17&C2:C17=TRANSPOSEA2:A17&B2:B17&C2:C17*A2:A17= 前进中学498 ,ROW2:170,0 499 18、运算达标率 :=MMULTTRANSPOSENA2:A1160*B2:B1160*B2:B11=365/365*5 523 37、依据上机时间运算上网费用 :=TRUNCB2+B2-TRUNCB2=0

59、.5*1.5+MODB2,10 525 39、运算机上网费用 :=CEILINGB2,30/30*2 526 40、以超产 80 为单位运算超产奖 :=SUMMROUNDB2:B11-700,80*IFB2:B11=700,1,-1/80*50 527 41、运算需要多少零钞 :=SUMROUNDDOWNB2:B10*C2:C10,0,-1*1,-1 528 42、运算值为 l 万的整数倍数的数据个数 :=SUMNB2:B10*C2:C10=ROUNDDOWNB2:B10*C2:C10,-4 529 43、运算完成工程需求人数 :=SUMROUNDUPB2:B11/C2:C11, 530 44

60、、运算两列数据同行相等的个数 :=SUMNA1:A10=B1:B10 531 45、运算同行相等且长度为 3 的个数 :=SUMA1:A10=B1:B10*LENA1:A10=3 532 46、依据业绩运算需要发放多少奖金 :=SUMIFB2:B1180000,1000,500 533 47、依据工作时间运算 12 月工资 :=C2+SUMIFB20,1,3,5,10,300,500,500,500,500 534 48、运算反常停机时间 :=SUMSUBSTITUTESUBSTITUTEIFC2:C11,C2:C11,0, 修机 , 换原料535 ,*1 536 49、运算最大数字行与文本行