EXCEL常用计算公式大全
1
1
2
EXCEL常用计算公式大
日常办公中少不了EXCEL 表格的运用，方便、快速、高效。
EXCEL表格是基于计算公式而运行的，只有掌握了基本公式，才能完成各项运算。
经过长时间收集，整理了一些常用工具，基本能够满足日常运用。
贵州龙山源酒业有限公司
贵州贞丰
联系人：石启洪
qq：478094808
1
3
EXCEL常用计算公式大全
4
5
文/石启洪整理
6
7
整理说明
8
EXCEL 是日常办公中少不了的运算工具，方便、快速、高效。
9
由于EXCEL表格是基于计算公式而运行的，只有掌握了基本公式，才能完成各项运算。经过长时10
间收集，整理了一些常用工具，基本能够满足日常运用。
11
公式由{}包括的为数组公式，在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter 12
键，自动生成数组公式。
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
目录
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)
70
71
72
EXCEL常用计算公式大全
73
74
常用求和运算
75
一、单组数据加减乘除运算：
76
1 单组数据求加和公式：=（A1+B1）
77
78
举例：单元格A1：B1区域依次输入了数据10和5，计算：在C1中输入 =A1+B1 后点击键盘“Enter（确定）”
键后，该单元格就自动显示10与5的和15。
79
80
2 单组数据求减差公式：=（A1-B1）
81
举例：在C1中输入 =A1-B1 即求10与5的差值5，电脑操作方法同上；
3 单组数据求乘法公式：=（A1*B1）
82
83
举例：在C1中输入 =A1*B1 即求10与5的积值50，电脑操作方法同上；
84
4 单组数据求乘法公式：=（A1/B1）
85
举例：在C1中输入 =A1/B1 即求10与5的商值2，电脑操作方法同上；
86
5 其它应用：
87
在D1中输入 =A1^3 即求5的立方（三次方）；
88
在E1中输入 =B1^（1/3）即求10的立方根
89
小结：
90
91
在单元格输入的含等号的运算式，Excel中称之为公式，都是数学里面的基本运算，只不过在计算机上有的运算符号发生了改变——“×”与“*”同、“÷”与“/”同、“^”与“乘方”相同，开方作为乘方的逆运算，
92
93
把乘方中和指数使用成分数就成了数的开方运算。
94
这些符号是按住电脑键盘“Shift”键同时按住键盘第二排相对应的数字符号即可显示。
95
如果同一列的其它单元格都需利用刚才的公式计算，只需要先用鼠标左键点击一下刚才已做好公式的单元格，96
将鼠标移至该单元格的右下角，带出现十字符号提示时，开始按住鼠标左键不动一直沿着该单元格依次往下拉到97
你需要的某行同一列的单元格下即可，即可完成公司自动复制，自动计算。
98
二、多组数据加减乘除运算：
99
100
1 多组数据求加和公式：(常用)
101
举例说明：=SUM（A1:A10）,表示同一列纵向从A1到A10的所有数据相加；
102
=SUM（A1:J1）,表示不同列横向从A1到J1的所有第一行数据相加；
103
2 多组数据求乘积公式：（较常用）
举例说明：=PRODUCT（A1:J1）表示不同列从A1到J1的所有第一行数据相乘；
104
105
=PRODUCT（A1:A10）表示同列从A1到A10的所有的该列数据相乘；
106
3 多组数据求相减公式：(很少用)
107
举例说明：=A1-SUM（A2:A10）表示同一列纵向从A1到A10的所有该列数据相减；
108
=A1-SUM（B1:J1）表示不同列横向从A1到J1的所有第一行数据相减；
109
4 多组数据求除商公式：(极少用)
举例说明：=A1/PRODUCT（B1:J1）表示不同列从A1到J1的所有第一行数据相除；
110
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、对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)
121
2、对生产表中大于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}
122
3、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}
123
4、对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*D2:D10)}
5、对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)="赵")*(C2:C10="女")*D2:D10)}
124
125
6、LEFT(left)：解释，就是最左边的（最开始的）如：石启洪最左边就是“石”对应RIGHT(right)最右边126
的（最后的）。
7、对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})}
127
128
8、对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2)
129
9、对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2)
130
10、计算成绩在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)}
11、既求积也求和:=IF(D2<>"",PRODUCT(C2:D2),SUM(OFFSET(E2,-3,,3)))
133
134
12、按文字描述求和:{=SUM(ISNUMBER(FIND(A$2:A$8,D2))*B$2:B$8)}
135
13、求积、求和两相
136
宜:=SUM(IF(C2="",INDIRECT("E"&LOOKUP(1,0/ISERROR((0/$C$1:C1="")),ROW($C$2:C2))&":E"&(ROW()-1)),C2*D 137
2))
138
14、将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))}
139
15、将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))}
140
二、与和
141
142
1、求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3}))
2、求数组中最大值:=LARGE(B2:B10,1)
143
144
3、求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9)
145
4、求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)}
146
5、求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2)
147
6、用SUM函数计数:{=SUM((B2:B9="男")*1)}
148
7、求1累加到100之和:{=SUM(ROW(1:100))}
149
8、多个工作表不同区域求前三名产量和:{=SUM(LARGE(CHOOSE({1,2,3,4,5},A组!B2:B9,B组!B2:B9,C组!B2:B9,D 150
组!B2:B9,E组!B2:B9),ROW(1:3)))}
151
9、计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10)
10、计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000")
152
153
11、求前三名和后三名的数据之和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2:B10,4))
12、计算A1：A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}
154
155
13、分别统计收入和支出:收入{=SUM(IF(B2:B13>0,B2:B13))};支出
156
{=SUM(IF(SUBSTITUTE(IF(B2:B13<>"",B2:B13,0),"负","-")*1<0,SUBSTITUTE(B2:B13,"负","-")*1))}
157
14、给公式添加运算说明:=CONCATENATE("你好",B2,"2008")&T(N("公式含义:连接“你好”和单元格B2、“2008”158
"))
159
15、计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10="一车间")+(B2:B10="三车间")*(C2:C10="女160
"),D2:D10))}
16、统计小米、华为和联想已隹出手机个数:=SUM(COUNTIF(B2:B11,"*"&{"小米","华为","联想"}&"*"))
161
162
17、统计二班和三班数学竞赛获奖人数:=SUM(COUNTIFS(B2:B11,{"二班","三班"},C2:C11,"数学*"))
163
18、提取产品最后报价和最高报价:{=INDEX(C:C,MAX((A2:A11="B")*ROW(2:11)))}
19、计算文具类产品和家具类产品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH("(?具类
164
165
",A2:A11)),B2:B11)),"0.00%")}
166
20、统计文具类和厨具类产品的最低单价:=DMIN(A1:B11,2,D1:D2)
167
21、计算大于等于前10个最大产量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)
168
22、计算8月份笔筒和毛笔的进货数量:{=SUM(IF(MONTH(A2:A11)=8,IF((B1:H1="笔筒")+(B1:H1="毛笔
169
"),B2:H11)))}
170
23、统计家具类和文具类产品在1月份的出库次数:{=SUM((B2:B11={"文具类","家具类
171
"})*(IF(C2:C11>0,MONTH(C2:C11)=1)))}
172
24、计算员工工作天数和月数:=DATEDIF(B2,C2,"M")
173
25、对班级和成绩升序排
174
列:{=1*MID(SMALL(1*($A2:$A12&TEXT($B2:$B12,"000")),ROW($A$2:$A$12)-1),{1,2},{1,3})}
175
26、根据下拉列表中的时间和产品名计算销量冠
176
军:{=INDEX(A2:A11,MATCH(MAX(OFFSET(C2,,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2)),OFFSET(C2,,MAT 177
CH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),0))}
178
27、分别计算每个班第一名的成绩和姓名:名次
179
{=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: 180
181
B$31=K2)),1000)-1,)}
28、根据评委评分和权重分配统计最后得分:{=SUM(B2:F8*(A2:A8=B10)*TRANSPOSE(I2:I6))}
182
183
29、区分工种和达标率计算奖金:=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))
30、提取每日累计出库数和每日库存数:日期=INDEX(A:A,ROW(A1)*2);累计出库数
185
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})}
188
31、计算生产部人数和非生产部人数:生产部人数{=SUM((NOT(ISERR(FIND("车间",A2:A11)))*B2:C11))};非生产189
部人数{=SUM((ISERR(FIND("车间",A2:A11)))*B2:C11)}
190
32、根据利息和存款数计算存款达到1万元需要几个月:=NPER(A2,0,-B2,C2)*12
191
33、根据投资金额、时间和目标收益计算增长率:=RATE(B2,0,-A2,C2)
192
34、根据贷款、利率和时间计算某段时间的利息:=CUMIPMT(B2/12,C2*12,A2,1,24,0)
193
35、根据贷款、利率和时间计算需偿还的本金:=CUMPRINC(B2/12,C2*12,A2,1,24,0)
194
36、以年限总和折旧法计算折旧值:=SYD(A$2,B$2,C$2,ROW(A1))
195
产字篇
196
一、生产
197
198
1、对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)
199
2、对生产表中大于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}
200
3、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}
201
4、计算每小时生产产值:=PRODUCT(C2:E2)
202
5、生产A产品且无异常的机台平均产量:=AVERAGEIFS(C2:C11,B2:B11,"A",D2:D11,"")
203
6、根据员工生产产品的废品率记分:=MAX(MIN(6-(B2*100-5),10),0)
204
7、哪种产品生产次数最多:{=TEXT(MODE(B2:B9*1),"00")}
205
8、本月需要完成几批货物生产:{=SUM(N(B2:B11=TEXT(TODAY(),"MMMM")))}
206
9、计算生产车间异常机台个数:=COUNT(C2:C11)
207
10、计算生产速度是否达标:=YEARFRAC(C2,D2)<=(E2/B2)
208
11、计算生产部人数和非生产部人数:生产部人数{=SUM((NOT(ISERR(FIND("车间",A2:A11)))*B2:C11))};非生产
部人数{=SUM((ISERR(FIND("车间",A2:A11)))*B2:C11)}
209
210
12、根据卡机数据判断员工部门:=CHOOSE(MATCH(--RIGHT(A2,3),{1,38,14,11,8,21,43,9,28},0),"生产部","业211
务部","总务部","人事部","食堂","保卫部","采购部","送货部","财务部")
二、产量
212
213
1、求前三名产量之和:=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 214
215
组!B2:B9,E组!B2:B9),ROW(1:3)))}
216
3、跨表求积:=PRODUCT(产量表:单价表!B2)
217
4、计算各组别第三名产量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}
218
5、计算每个车间大于250的平均产量:=AVERAGEIF(B2:C11,">250")
219
6、统计产量达标率:=TEXT(COUNTIF(B2:B11,">=800")/COUNT(B2:B11),"0.00")
220
7、计算大于等于前10个最大产量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)
221
8、按周汇总产
222
量:{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$AF1,2))+(COLUMN($B1:$AF1)-1)=(1+(COLUMN(A1)-1)*7))*$B2:$AF2) }
223
224
9、计算所有人的一周产量并排
名:{=INDEX(1:1,RIGHT(LARGE(SUBTOTAL(9,OFFSET($A2:$A8,,COLUMN($B:$J)-1,,))*10+COLUMN($B:$J)-1,COLUMN 225
226
(A1)))+1)}
227
10、根据下拉列表引用不同工作表的产量:=INDIRECT(ADDRESS(11,2,1,1,D1))
228
11、计算连续三天之总产量大于等于25万元的次
229
13、计算产量最高的季
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),"[ 231
DBNum1]0季度")
232
14、罗列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$ 234
2:$B$13,$B$2:$B$13,0)=ROW($2:$13)-1),ROW($2:$13),1048576),ROW(A1)))&""}
235
15、根据产量计算员工产量得分:{=LOOKUP(B2,{3,0.5}*(ROW($1:$11)-1))}
236
16、提取产量冠军的组别:=IF(COUNTA(B2:E2),LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1),"")
237
17、建立文件目录:=HYPERLINK("[E:\产量表\"&TEXT(ROW(1:1),"[DBNum1]")&"月产量
表.xlsx]sheet1!A1",TEXT(ROW(1:1),"[DBNum1]")&"月产量表")
238
239
18、选择产量最高工作表:{=HYPERLINK("#"&CHAR(64+MOD(MAX(SUBTOTAL(9,INDIRECT(CHAR(64+ROW(1:8))&"
240
组!B2:B11"))*100+ROW(1:8)),100))&"组!A1","跳至最大产量组")}
241
19、计算连续三天之总产量大于等于25万元的次
242
数:=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,,3))>=25))
243
三、产品
244
245
1、求入库最多的产品数
246
量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={"A","B","C","D"})),(A2:A11={"A","B","C","D"})*1))}
247
2、计算A产品每日库存数:{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17="A")*(C2:C17-D2:D17))} 248
3、计算C产品最大入库量:{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))}
249
4、根据产品规格计算产品体
250
积:=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)))
252
5、将产品型号规范化:=IF(MID(A2,5,2)="00",A2,REPLACE(A2,5,,"00"))
6、产品规格格式转换:=SUBSTITUTE(SUBSTITUTE(A2,"：","("),"*",")*")&")"
253
254
7、统计未检验完成的产品数:=COUNTBLANK(B2:B11)
8、统计季度最高产值合计:{=MAX(SUBTOTAL(9,OFFSET(B2,,COLUMN(B:E)-2,ROWS(2:10),1)))}
255
256
9、提取产品最后报价和最高报价:{=INDEX(C:C,MAX((A2:A11="B")*ROW(2:11)))}
257
10、计算文具类产品和家具类产品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH("(?具类
258
",A2:A11)),B2:B11)),"0.00%")}
259
11、统计售价850元以上的产品最低利率是多少:=DMIN(A1:D11,F4,F1:F2)
260
12、统计文具类和厨具类产品的最低单价:=DMIN(A1:B11,2,D1:D2)
13、查看产品曾经销售的所有价
261
262
位:{=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)))} 263
14、提取销量的前三名的外销产品名
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)}
266
15、统计家具类和文具类产品在1月份的出库次数:{=SUM((B2:B11={"文具类","家具类
267
"})*(IF(C2:C11>0,MONTH(C2:C11)=1)))}
268
16、根据下拉列表中的时间和产品名计算销量冠
269
军:{=INDEX(A2:A11,MATCH(MAX(OFFSET(C2,,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2)),OFFSET(C2,,MAT 270
CH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),0))}
17、根据下拉列表中的产品提取姓名与销
271
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:1 273
1)),ROW(1:10)),1),"")}
18、从多个产品相同单价的单价表中引用单
274
275
价:=SUMPRODUCT(COUNTIF(OFFSET(A$2,ROW($2:$4)-2,0,1,4),G2)*E$2:E$4)*H2
276
20、区分大小写提取产品单价:{=MMULT((EXACT(B2:B11,TRANSPOSE(单价表!A2:A5)))*TRANSPOSE(单价
277
表!B2:B5),{1;1;1;1})}
278
21、罗列导致产品不良的主
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 ),ROW(A1)),ROW($2:$11)),COLUMN(A1)))),"")}
280
281
22、计算两个产品不同时期的单价:=HLOOKUP(MONTH(A2),IF(B2="塑胶机
282
",{0,3,8;25,19,18},{0,5,10;12.5,10,11}),2)
283
23、产生混合编号:=TEXT(COUNTIF(C$1:C1,"*"),"[DBNum2]")&TEXT(ROW()-MATCH("々",C$1:C1),"(000);;") 284
24、计算产品体积:=IF(ISERROR(FIND("/",B2)),B2^3,PRODUCT(1*TRIM(MID(SUBSTITUTE(B2,"/",REPT("
285
",100)),{1,100,200},100))))
25、按月份统计每个产品的机器返修数
286
287
量:=SUMPRODUCT(ISNUMBER(FIND(F$2,$A$2:$A$11))*(TEXT($B$2:$B$11,"YM")=TEXT($E3,"YM"))*$C$2:$C$11) 288
26、从产品规格中提取直径、长、宽:长(直径)=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)
291
27、根据产品规格计算体积:体积
292
=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD4,"(L)","*"),"(W)","*"),"(H)",""))
293
禁止录入不完整的产品规格:数据有效性设置-自定义:=ISNUMBER(SEARCH("长?*宽?*高?*",B2))
294
四、产值
295
296
1、计算每小时生产产值:=PRODUCT(C2:E2)
297
2、每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2)
298
五、产生
299
300
1、产生100到200之间带小数的随机数:=RAND()*(200-100)+100
301
2、产生ll到20之间的不重复随机整数:{=RANK(A2:A11,A2:A11)+10}
302
3、产生-50到100之间的随机整数:=RANDBETWEEN(-50,100)
303
4、产生1到100之问的奇数随机
数:{=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100))}
304
305
5、产生1到10之间随机不重复
306
数:{=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10))=0,ROW($1:$10)),RANDBETWEEN(1,12-ROW()))}
307
6、根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)<60,"不及格",IF(AVERAGE(B2:D2)<90,"良好
308
",IF(AVERAGE(B2:D2)<100,"优秀","满分")))
309
7、产生大、小写字母A到Z的序列:大写字母=CHAR(ROW(A65))，小写字母=CHAR(ROW(A65)+32)
310
8、产生大写字母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)+ 312
65),"")
9、产生三个字母组成的随机字符
313
314
串:=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))
10、用公式产生换行符:=A2&CHAR(10)&B2
315
316
11、在A列产生1到12月的英文月份名:=TEXT((ROW())&"-1","mmmm")
317
12、在A1:A7区域产生星期一到星期日的英文全称:{=TEXT(ROW(1:7)+1,"DDDD")}
318
13、产生“坐标”:=CHAR(64+COLUMN(A1))
319
14、产生每两行累加1的编号:=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,,,ROW()-1))+1,""))
320
15、计算成绩排名，不能产生并列名
321
次:=SUMPRODUCT(--((A$2:A$15=A2)*(($C$2:$C$15)+1/ROW($C$2:$C$15))>C2+1/ROW(2:2)))+1
322
16、在具有合并单元格的A列产生自然数编号:=1+COUNT(OFFSET($A$2,,,ROW()-2,))
323
17、引用合并区域时防止产生0值:=IF(A1<>"",A1,OFFSET(B1,-1,))
324
18、以固定余额递减法计算资产折旧值:=DB(A$2,B$2,C$2,ROW(A1),12)
325
19、以双倍余额递减法计算资产折旧值:=DDB(A$2,B$2,C$2,1,2)
326
20、使用双倍余额递减法计算任何期间的资产折旧值:=VDB(A$2,B$2,C$2*12,7,12,2)
327
21、以超产80为单位计算超产奖:{=SUM(MROUND(B2:B11-700,80*IF(B2:B11>=700,1,-1)))/80*50}
328
大小于篇
329
一、大于
330
331
1、对生产表中大于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}
332
2、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}
333
3、汇总鼠标所在列中大于600的数
334
据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600")
335
4、判断单元格的数据类型:=TEXT(A2,"大于○;小于○;○;文本")
336
5、计算每个车间大于250的平均产量:=AVERAGEIF(B2:C11,">250")
337
6、统计大于80分的三好学生个数:{=COUNTIFS(B2:B11,"三好学生",C2:C11,">80")}
7、计算语文成绩大于90分者的最高总成绩:=DMAX(A1:E11,5,G1:G2)
338
339
8、计算大于等于前10个最大产量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)
340
9、计算第一次收入金额大于30元时的金额是多少:=INDEX(B:B,MIN(IF((A2:A11=A2)*(B2:B11>30),ROW(2:11)))
10、记录最后一次销量大于3000的地
341
342
址:{=ADDRESS(MOD(MAX((IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*ROW(B2:D7)+(IF(ISNUMBER(B2:D7),B2:D7,0)>300 343
0)*COLUMN(B2:D7)*1000),1000),INT(MAX((IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*ROW(B2:D7)+(IF(ISNUMBER(B2: 344
D7),B2:D7,0)>3000)*COLUMN(B2:D7)*1000)/1000))}
345
11、计第奎续三天之总产量大于等于25万元的次
346
数:=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,,3))>=25))
347
12、统计各班所有科目成绩大于60分者人
348
数:{=MMULT(N(TRANSPOSE(A2:A21)=H3:H6),N(COUNTIF(OFFSET(C2:F2,ROW(2:21)-2,),">=60")=4))}
349
13、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}
350
三、小于
351
1、判断单元格的数据类型:=TEXT(A2,"大于○;小于○;○;文本")
352
2、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}
353
354
数据篇
355
356
1、对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)
357
2、对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}
358
37、求所有工作表相同区域数据求和:=SUM(A组:E组!B2:B9)
359
3、求前三名和后三名的数据求和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2:B10,4))
4、汇总鼠标所在列中大于600的数
360
361
据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600")
362
5、汇总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)
6、汇总3的倍数列的数据:{=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10))}
363
364
7、将数据转换成接近6的倍数:=MROUND(A1,6)
365
8、计算值为l万的整数倍数的数据个数:{=SUM(N((B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)))}
366
9、判断两列数据是否相等:=IF(A1=B1,"相等","")
367
10、计算两列数据同行相等的个数:{=SUM(N(A1:A10=B1:B1
368
11、有选择地汇总数据:{=SUM(IF(A2:A11={"A组","C组"},C2:C11))}
369
12、返回自动换行单元格的第二行数据:=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))
370
13、将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))}
371
14、将数据显示为小数点对齐:=TEXT(B2,"#.0????")
15、判断单元格的数据类型:=TEXT(A2,"大于○;小于○;○;文本")
372
373
16、将三列数据交换位置:{=TEXT({1,-1,0},C1:C5&";"&"!"&B1:B5&";"&A1:A5)}
17、将数据重复显示5次:=SUBSTITUTE(TEXT(A2&"?","@@@@@"),"?","")
374
375
18、将数据对齐显示，将空白以“.”占位:=WIDECHAR(REPT(".",10-LEN(B2))&B2)
376
19、从卡机数据提取打卡时间:=730>--MID(A2,14,4)
377
20、根据卡机数据判断员工部门:=CHOOSE(MATCH(--RIGHT(A2,3),{1,38,14,11,8,21,43,9,28},0),"生产部","业378
务部","总务部","人事部","食堂","保卫部","采购部","送货部","财务部")
379
21、从汉字与数字混合字串中提取温度数据:{=MAX(IFERROR(--RIGHT(LEFT(B2,LEN(B2)-1),ROW($1:$10)),0))}
22、计算两列数据相同个数:{=SUM(COUNTIF(A2:A11,B2:B11))}
380
381
23、统计区域中不重复数据个数:{=SUM(1/COUNTIF(B2:B8,B2:B8))}
382
24、提取不重复数据:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)}
383
25、消除单位提取数据:{=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100))),))*IF(LEFT(A2)="-",-1,1)}
384
26、提取引用区域右下角的数
385
据:=INDIRECT(ADDRESS(ROW(B3:D7)+ROWS(B3:D7)-1,COLUMN(B3:D7)+COLUMNS(B3:D7)-1))
386
27、合并三个工作表的数据:=INDIRECT(CHOOSE(MOD(ROW(A2)-1,3)+1,"一年级!A"&INT((ROW(A3))/3)+1,"二年387
级!A"&INT((ROW(A3))/3)+1,"三年级!A"&INT((ROW(A3))/3)+1))
388
28、累计数据:{=SUM(OFFSET(B$2,,,ROW()-1))}
389
29、多列、隔行数据汇总:{=SUM(MMULT(D2:G11,TRANSPOSE(COLUMN(D:G)^0))*(A2:A11="赵还珠"))}
30、从电话簿中选择性引用数据:=INDEX($A:$B,ROW(A1)*3-2,COLUMN(A:A))
390
391
31、插入空行分割数据:=IF(MOD(ROW(),3)>0,INDEX(A:A,ROW(A2)*2/3),"")
392
32、在同一行查找数据:{=HLOOKUP(MAX(A2:H2),IF({1;0},B2:H2,A2:G2),2,FALSE)}
33、不区分大小写判断两列相同数据个数:{=COUNT(MATCH(A2:A11,B2:B11,0))}
393
394
34、提取A列最后一个数据:{=INDIRECT("A"&(MATCH(1,0/(A:A<>""))))}
395
35、利用公式对入库表进行数据分
396
析:{=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)))&" 397
"}
398
36、对合并区域进行数据查询:=OFFSET(B1,MATCH(G2,A2:A13,0)-1+MATCH(H2,{"冰箱","空调","洗衣机
399
"},0),MATCH(I2,C1:E1,0))
400
37、反向查找数据:=LEN(A2)-LOOKUP(100,SEARCH(B2,A2,ROW($1:$99)))-LEN(B2)+2
401
38、使用通配符查找所有符合条件的数据:{=IFERROR(LOOKUP(1,0/SEARCH("*医院
*",IF(COUNTIF($C$1:C3,A$2:A$12)=0,A$2:A$12,)),A$2:A$12),"")}
402
403
39、引用单元格数据同时引用格式:=IF(TODAY()>A2,"",TEXT(A2,格式))
40、分别汇总当前表以外的所有工作表数
404
405
据:AcSht=GET.CELL(62);sheets=GET.WORKBOOK(1);WorkBook=GET.CELL(66);{=IFERROR(REPLACE(INDEX(sheets,S 406
MALL(IF(TRANSPOSE(sheets)<>AcSht,ROW(INDIRECT("1:"&COLUMNS(sheets)))),ROW(A2))),1,LEN(WorkBook)+2," 407
"),"")}
408
41、利用列表框筛选数据:筛选=IF(GET.OBJECT(78,"列表框 1"),GET.OBJECT(78,"列表框
409
1")*TRANSPOSE(ROW(sheet1!$A$2:$A$8)))
410
42、将数据分列，提取省市县:分列=EVALUATE("{"""&SUBSTITUTE(SUBSTITUTE(Sheet1!$A5,"省","省"","""),"市411
","市"",""")&"""}")
412
43、将数据间隔着色:条件格式:=MOD(SUM(N($B$2:$B2<>$B$1:$B1)),2)=0
413
44、B列中禁止输入重复数据:数据有效性设置-自定义:=COUNTIF(B:B,B8)=1
414
45、仅允许录入英文姓名:数据有效性设置-自定义:=SUM(--(ERROR.TYPE(INDIRECT(MID(SUBSTITUTE(A2,"
415
",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),1)&1))=3))=LEN(SUBSTITUTE(A2," ",""))
416
46、强制录入规范化的日期:数据有效性设置-自定义:=(LEN(A2)=8)*TEXT(A2,"#-00-00")
47、让A列只能输入质数:数据有效性设置-自定义:=OR(A2=2,A2=3,PRODUCT(MOD(A2,ROW(INDIRECT("2:"&
417
418
INT(A2^0.5))))))
419
48、设置D列只能录入男职工的姓名:数据有效性设置-自定义:=VLOOKUP(D2,A:B,2,0)="男"
420
49、禁止录入不完整的产品规格:数据有效性设置-自定义:=ISNUMBER(SEARCH("长?*宽?*高?*",B2))
421
工资篇
422
1、对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*D2:D10)}
423
424
2、对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)="赵")*(C2:C10="女")*D2:D10)}
425
3、对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})}
426
4、对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2)
427
5、对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2)
428
6、汇总所有车间人员工资:=SUMPRODUCT(--NOT(ISERROR(FIND("车间",A2:A10))),C2:C10)
7、汇总A组男职工的工资:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男A组")*D2:D11),ROW(2:11)^0)}
429
430
8、设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明
431
细!A$1,ROW()/3+1,0),""))
432
9、工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10)
433
10、根据工作时间计算12月工资:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500}))
434
11、计算二车间女职工的平均工资:{=AVERAGE(IF((B2:B10="二车间")*(C2:C10="女"),D2:D10))}
12、计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10="一车间")+(B2:B10="三车间")*(C2:C10="女435
436
"),D2:D10))}
437
13、计算平均工资(不忽略无薪人员):=ROUND(AVERAGEA(B2:B10),2)
14、生成工资结算日期:=TEXT(EOMONTH(B2,0)+1,"e年M月D日")
438
439
15、统计两倍工资的加班小时数:=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&":"&EOMONTH(B2,0))),"AAA")="六
440
"))*8
441
16、计算临时工的工资:=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,"分",""),"小时",":"))/(8/24)*50,) 442
17、计算本日工时工资:=(HOUR(C2-TIMEVALUE("8:00"))-1-ROUNDUP(B2-TIMEVALUE("8:00"),0))*6
443
18、计算每日工时工资:=8*5*IF(WEEKDAY(A2,2)<6,1,1.5)+(B2-8)*5*1.5
444
19、工资查询:{=IFERROR(OFFSET(D1,MATCH(F2&G2&H2,A2:A11&B2:B11&C2:C11,0),),G2&"无此人")}
445
价格篇
446
447
1、求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)}
448
2、按季度引用不同价格并统计金额与累计:{=IF(A2<>"累计",LOOKUP(COUNTIF(OFFSET(A$1,1,0,ROWS($2:2),)," 449
合计")+1,ROW($2:$5)-1,F$2:F$5)*B2,SUM(C1:C$2*(A1:A$2<>"累计")))}
450
3、按图书编号汇总价格:图书=EVALUATE("{"""&SUBSTITUTE(Sheet1!B2,"/",""",""")&"""}")
451
函数篇
452
1、平均函数 =AVERAGE（:）
453
454
2、最大值函数 =MAX （:）
455
3、最小值函数 =MIN （:）
456
4、统计函数 =COUNTIF（:）
457
5、用SUM函数计数:{=SUM((B2:B9="男")*1)}
458
6、FLOOR函数处理正负数混合区域:=FLOOR(A1*100,10*(IF(A1>0,1,-10)))
459
7、让VLOOKUP函数在多区域查找:=VLOOKUP(A11,CHOOSE(MATCH(B11,{"一年级","二年级","三年级
460
"},0),A1:B9,D1:E9,G2:H9),2,0)
461
职工篇
462
463
1、对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)="赵")*(C2:C10="女")*D2:D10)}
2、汇总A组男职工的工资:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男A组")*D2:D11),ROW(2:11)^0)}
464
465
3、根据年龄判断职工是否退休:=OR(AND(B2="男",C2>60),AND(B2="女",C2>55))
466
4、根据年龄与职务判断职工是否退休:=OR(AND(B2="男",D2>60+(C2="干部")*3),AND(B2="女",D2>55+(C2="干部467
")*3))
468
5、计算二车间女职工的平均工资:{=AVERAGE(IF((B2:B10="二车间")*(C2:C10="女"),D2:D10))}
469
6、计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10="一车间")+(B2:B10="三车间")*(C2:C10="女470
"),D2:D10))}
471
7、计算女职工的最大年龄:{=MAX((B2:B11="女")*C2:C11)}
472
8、计算每个职工的得
473
分:=LOOKUP(,-FIND(B2,{"A**","A*","A","B**","B*","B","C**","C*","C","D"}),11-ROW($1:$10))
474
9、根据身份证号码汇总男、女职工总数:男{=SUM(--ISODD(MID(B2:B10,15,3)))};女
475
{=SUM(--ISEVEN(MID(B2:B10,15,3)))}
476
10、设置D列只能录入男职工的姓名:数据有效性设置-自定义:=VLOOKUP(D2,A:B,2,0)="男"
477
计算篇
478
479
1、计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10)
480
2、计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000")
481
3、计算车间男性与女性人员的差:=SUM(SUMIFS(C2:C11,B2:B11,{"女","男"},A2:A11,"*车间")*{-1,1})
482
4、计算参保人数:=SUMPRODUCT((C2:C11="是")*1)
5、计算A1：A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}
483
484
6、计算1到10的自然数的积:=FACT(10)
485
7、计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49)
486
8、计算1到15之间奇数相乘的结果:=FACTDOUBLE(15)
487
9、计算每小时生产产值:=PRODUCT(C2:E2)
488
10、计算车间盈亏:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1;1;1}))
489
11、计算各组别第三名产量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}
490
12、计算C产品最大入库量:{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))}
491
13、计算累计入库数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)}
14、计算每日库存数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)}
492
493
15、计算A产品每日库存数:{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17="A")*(C2:C17-D2:D17))}
16、在不同班级有同名前提下计算学生人
494
495
数:{=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)),ROW(2:17)^0))}
496
17、计算前进中学参赛人
497
数:{=SUM(IFERROR(1/MMULT(N((A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17))*(A2:A17="前进中学498
")),ROW(2:17)^0),0))}
499
18、计算达标率:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)}
19、计算成绩在60-80分之间合计数与个数:求和
500
501
{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)*B2:B11),ROW(2:11)^0)}，求个数
502
{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)}
503
20、计算象棋比赛对局次数l:=COMBIN(B1,B2)
504
21、计算五项比赛对局总次数:{=SUM(COMBIN(B2:B5,2))}
505
22、计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2)
506
23、计算中奖率:=TEXT(1/PERMUT(B1,B2),"0.00%")
507
24、计算最大公约数:=GCD(B1:B5)
508
25、计算最小公倍数:=LCM(B1:B5)
509
26、计算余数:=MOD(A2,B2)
27、根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),"男","女")
510
511
28、计算零钞:5角=INT(MOD(SUM(B2:B10),1)/0.5);2角=INT(MOD(MOD(SUM(B2:B10),1),0.5)/0.2);1角
512
=MOD(MOD(MOD(SUM(B2:B10),1),0.5),0.2)/0.1
29、计算预报温度与实际温度的最大误差值:{=MAX(ABS(C2:C8-B2:B8))}
513
514
30、计算个人所得
515
税:=ROUND(0.05*SUM(H2-1600-{0,500,2000,5000,20000,40000,60000,80000,100000}+ABS(H2-1600-{0,500,2000 516
,5000,20000,40000,60000,80000,100000}))/2,0)
517
31、计算Al:A10区域开三次方之平均值:{=AVERAGE(POWER(A1:A10,1/30))}
518
32、计算Al:A10区域倒数之积:{=PRODUCT(POWER(A1:A10,-1))}
519
33、根据等边三角形周长计算面积:=SQRT(B1/2*POWER(B1/2-B1/3,3))
520
34、计算A1:B10区域中偶数个数:=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)))
521
35、根据重量单价计算金额，结果以万为单位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000
36、计算年假天数:=TRUNC((TODAY()-B2)*((TODAY()-B2)>=365)/365*5)
522
523
37、根据上机时间计算上网费用:=(TRUNC(B2)+(B2-TRUNC(B2)>=0.5))*1.5+(MOD(B2,1)<0.5)
38、根据员工工龄计算年资:=C2+CEILING(B2*30,30)*(INT(B2)>0)
524
525
39、计算机上网费用:=CEILING(B2,30)/30*2
526
40、以超产80为单位计算超产奖:{=SUM(MROUND(B2:B11-700,80*IF(B2:B11>=700,1,-1)))/80*50}
527
41、计算需要多少零钞:{=SUM(ROUNDDOWN(B2:B10*C2:C10,{0,-1})*{1,-1})}
528
42、计算值为l万的整数倍数的数据个数:{=SUM(N((B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)))}
529
43、计算完成工程需求人数:{=SUM(ROUNDUP(B2:B11/C2:C11,))}
44、计算两列数据同行相等的个数:{=SUM(N(A1:A10=B1:B10))}
530
531
45、计算同行相等且长度为3的个数:{=SUM((A1:A10=B1:B10)*(LEN(A1:A10)=3))}
532
46、根据业绩计算需要发放多少奖金:{=SUM(IF(B2:B11>80000,1000,500))}
533
47、根据工作时间计算12月工资:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500}))
534
48、计算异常停机时间:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11<>"",C2:C11,0),"修机",""),"换原料
535
","")*1)}
536
49、计算最大数字行与文本行:{=MAX(IF(B:B<>"",ROW(A:A)))}
537
50、计算汉字全角半角混合字符串中的字母个数:=LEN(ASC(A2))*2-LENB(ASC(A2))
538
51、计算混合字符串中汉字个数:=LEN(A2)-(LENB(WIDECHAR(A2))-LENB(ASC(A2)))
539
52、计算单元格中数字个
数:{=SUM((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>47)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))), 540
541
1))<58))}
542
53、计算单元格中大写加小写字母个
数:{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))>64)*(CODE(UPPER(MID(A2,ROW(INDIRECT("1 543
544
:"&LEN(A2))),1)))<91))}
545
54、计算平均成绩及评判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2)),": ",IF(AVERAGE(B2:D2)>=60,"","不546
"),"及格")
547
55、计算字符串中英文字母个
548
数:{=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),LOWER(MID(A2,ROW(INDIRECT("1:"&LE 549
N(A2))),1))))))}
550
56、计算字符串中单词个
551
数:{=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1)),MID(PROPER(A2),ROW(INDIRECT("1: "&LEN(A2))),1))))}
552
553
57、计算字符串中的数字个数:=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1))))
58、根据身份证号码计算出生日期:=IF(LEN(B2)=15,19,"")&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),"#年00月00日554
555
")
556
59、计算A列的日期有几个属于第二季度:{=SUM((--(TEXT(A1:A11,"m"))>{3,6})*{1,-1})}
557
60、计算达成率，以不同格式显示:=TEXT(B2/800,"[>=1]0.0倍;[>0]0.00%;")
558
61、计算字母“A”的首次出现位置，忽略大小写:=TEXT(SEARCH("a",A2&"a"),"[>"&LEN(A2)&"]没找到;第
559
"&SEARCH("a",A2&"a")&"个")
62、计算年终奖:=TEXT(B2,"[>3]15!0!0;[>1]1!0!0!0;5!0!0;")
560
561
63、计算星期日完工的工程个数:{=COUNT((TEXT(B2:B10+C2:C10-1,"AAA")="日")^0)}
562
64、计算本月星期日的个
563
数:{=SUM(N(TEXT(TODAY()-TEXT(TODAY(),"d")+ROW(INDIRECT("1:"&DAY(DATE(,TEXT(TODAY(),"m")+1,)))),"AAA 564
")="日"))}
565
65、计算每个季度的天数:{=SUM(--TEXT(DATE(2008,3*ROW(A1)-ROW($1:$3)+2,),"d"))}
566
66、根据起止时间计算经过时
567
间:=TEXT(INT(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+(LEN(B4)=8)),"#!:00"))*24*60)/60)+MOD(((TEX 568
T(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+(LEN(B4)=8)),"#!:00"))*24*60),60.1)%,"0小时.00分钟")
569
67、计算订单金额并以“百万”为单位显示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000
68、计算单元格中数字个数:=LEN(A2)*2-LENB(A2)
570
571
69、计算购物金额中小数位数最多是几:{=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)))-1}
572
70、计算英文句子中有几个单词:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,"'"," ")," ",""))+1
EXCEL表格公式大全
E X C E L表格公式大全 集团标准化工作小组 #Q8QGGQT-GX8G08Q8-GNQGJ8-MHHGN#
Excel表格公式大全 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式： =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2 ))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女 "),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和： =SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数： =AVERAGE(K2:K56) ——对K2到K56这一区域求平均数； 3、排名： =RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级： =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))
5、学期总评： =K2*+M2*+N2* ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分： =MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分： =MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1） =COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2） =COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～分的人数；假设把结果存放于K60单元格； （5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60) ——求K2到K56区域70～分的人数；假设把结果存放于K61单元格； （6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61) ——求K2到K56区域60～分的人数；假设把结果存放于K62单元格； （7） =COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格； 说明：COUNTIF函数也可计算某一区域男、女生人数。
inpExcel常用电子表格公式大全
Excel常用电子表格公式大全 授课：XXX
1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式： =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2 ,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女 "),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表 的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M 列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格；（3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求 授课：XXX
Excel常用电子表格公式大全
Excel常用电子表格公式大全 2011-04-24 08:53:41 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式： =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女 "),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名；
4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；
Excel常用电子表格公式大全【汇总篇】
Excel 常用电子表格公式大全【汇总篇】 篇一：Excel 常用电子表格公式汇总 Excel 常用电子表格公式汇总 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式： =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的 18 位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1," 男 "," 女 "),IF(MOD(MID(C2,17,1),2)=1," 男 "," 女 ")) 公式内的“C2”代表的是输入身份证号码的单元格。 5、求和： =SUM(K2:K56)——对 K2 到 K56 这一区域进行求和； 6、平均数： =AVERAGE(K2:K56)——对 K2 K56 这一区域求平均数； 7、排名： =RANK(K2，K$2:K$56)——对 55 名学生的成绩进行排名； 8、等级： =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 9、 学期总评： =K2*0.3+M2*0.3+N2*0.4 ——假设 K 列、 M 列和 N 列分别存放着学生的“平 时总评”、“期中”、“期末”三项成绩； 10、最高分： =MAX(K2:K56) ——求 K2 到 K56 区域（55 名学生）的最高分； 11、最低分： =MIN(K2:K56) ——求 K2 到 K56 区域（55 名学生）的最低分； 12、分数段人数统计： （1） =COUNTIF(K2:K56,"100") ——求 K2 到 K56 区域 100 分的人数；假设把结果存放于 K57 单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求 K2 到 K56 区域 95～99.5 分的人数；假设把结 果存放于 K58 单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58)——求 K2 到 K56 区域 90～94.5 分的人数； 假设把结果存放于 K59 单元格； （4） =COUNTIF(K2:K56,">=85")－SUM(K57:K59)——求 K2 到 K56 区域 85～89.5 分的人数； 假设把结果存放于 K60 单元格； （5） =COUNTIF(K2:K56,">=70")－SUM(K57:K60)——求 K2 到 K56 区域 70～84.5 分的人数； 假设把结果存放于 K61 单元格； （6） =COUNTIF(K2:K56,">=60")－SUM(K57:K61)——求 K2 到 K56 区域 60～69.5 分的人数； 假设把结果存放于 K62 单元格； （7） =COUNTIF(K2:K56," 说明：COUNTIF 函数也可计算某一区域男、女生人数。 如：=COUNTIF(C2:C351,"男") ——求 C2 到 C351 区域（共 350 人）男性人数； 1 / 10
15个常用的Excel函数公式
15个常用的Excel函数公式，拿来即用 1、查找重复内容 =IF(COUNTIF(A:A,A2)>1,"重复","") 2、重复内容首次出现时不提示 =IF(COUNTIF(A$2:A2,A2)>1,"重复","") 3、重复内容首次出现时提示重复 =IF(COUNTIF(A2:A99,A2)>1,"重复","")
4、根据出生年月计算年龄 =DATEDIF(A2,TODAY(),"y") 5、根据身份证号码提取出生年月 =--TEXT(MID(A2,7,8),"0-00-00") 6、根据身份证号码提取性别 =IF(MOD(MID(A2,15,3),2),"男","女") 7、几个常用的汇总公式 A列求和：=SUM(A:A)
A列最小值：=MIN(A:A) A列最大值：=MAX (A:A) A列平均值：=AVERAGE(A:A) A列数值个数：=COUNT(A:A) 8、成绩排名 =RANK.EQ(A2,A$2:A$7) 9、中国式排名（相同成绩不占用名次） =SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1 10、90分以上的人数
=COUNTIF(B1:B7,">90") 11、各分数段的人数 同时选中E2:E5，输入以下公式，按Shift+Ctrl+Enter =FREQUENCY(B2:B7,{70;80;90}) 12、按条件统计平均值 =AVERAGEIF(B2:B7,"男",C2:C7) 13、多条件统计平均值 =AVERAGEIFS(D2:D7,C2:C7,"男",B2:B7,"销售")
Excel表格公式大全
沁园春·雪 北国风光，千里冰封，万里雪飘。望长城内外，惟余莽莽；大河上下，顿失滔滔。 山舞银蛇，原驰蜡象，欲与天公试比高。 须晴日，看红装素裹，分外妖娆。江山如此多娇，引无数英雄竞折腰。惜秦皇汉武，略输文采；唐宗宋祖，稍逊风骚。 一代天骄，成吉思汗，只识弯弓射大雕。 俱往矣，数风流人物，还看今朝。 1、查找重复内容公式：=IF(COUNTIF(A:A，A2)>1，"重复","")。
2、用出生年月来计算年龄公式： =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1，"男"，"女 ""),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=A VERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N 列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计：
电子表格常用函数公式
电子表格常用函数公式 1．去掉最高最低分函数公式： =SUM（所求单元格…注：可选中拖动?）—MAX（所选单元格…注：可选中拖动?）—MIN（所求单元格…注：可选中拖动?） （说明：“SUM”是求和函数，“MAX”表示最大值，“MIN”表示最小值。）2．去掉多个最高分和多个最低分函数公式： =SUM（所求单元格）—large(所求单元格，1)—large(所求单元格，2) —large(所求单元格，3)—small(所求单元格，1) —small(所求单元格，2) —small(所求单元格，3) （说明：数字123分别表示第一大第二大第三大和第一小第二小第三小，依次类推） 3．计数函数公式： count 4．求及格人数函数公式：（”>=60”用英文输入法） =countif(所求单元格，”>=60”) 5．求不及格人数函数公式：（”<60”用英文输入法） =countif(所求单元格，”<60”) 6．求分数段函数公式：（“所求单元格”后的内容用英文输入法） 90以上：=countif(所求单元格，”>=90”) 80——89：=countif(所求单元格，”>=80”)—countif(所求单元格，”<=90”) 70——79：=countif(所求单元格，”>=70”)—countif(所求单元
格，”<=80”) 60——69：=countif(所求单元格，”>=60”)—countif(所求单元格，”<=70”) 50——59：=countif(所求单元格，”>=50”)—countif(所求单元格，”<=60”) 49分以下： =countif(所求单元格，”<=49”) 7．判断函数公式： =if(B2,>=60,”及格”，”不及格”) （说明：“B2”是要判断的目标值，即单元格） 8．数据采集函数公式： =vlookup(A2,成绩统计表，2，FALSE) （说明：“成绩统计表”选中原表拖动，“2”表示采集的列数） 公式是单个或多个函数的结合运用。 AND “与”运算，返回逻辑值，仅当有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。条件判断 AVERAGE 求出所有参数的算术平均值。数据计算 COLUMN 显示所引用单元格的列标号值。显示位置 CONCATENATE 将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。字符合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。条件统计 DATE 给出指定数值的日期。显示日期
常用excel函数公式大全
常用的excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。
2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。
2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。 四、求和公式
1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明：如果标题行没有规则用第2个公式 2、单条件求和 公式：F2 =SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法
3、单条件模糊求和 公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。
4、多条件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明：在sumifs中可以使用通配符* 5、多表相同位置求和 公式：b2 =SUM(Sheet1:Sheet19!B2) 说明：在表中间删除或添加表后，公式结果会自动更新。 6、按日期和产品求和
Excel常用函数公式大全(实用)
Excel常用函数公式大全 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；
Excel表格公式大全(苦心整理)
Excel表格公式大全 来源：鲍利的日志 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56)——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56)——对K2K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56)——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56)——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56)——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100")——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58)——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59)——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格； （5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60)——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格；
EXCEL常用函数公式大全与举例
EXCEL常用函数公式大全及举例 一、相关概念 （一）函数语法 由函数名+括号+参数组成 例：求和函数：SUM（A1,B2,…) 。参数与参数之间用逗号“,”隔开（二）运算符 1. 公式运算符：加（+）、减（-）、乘（*）、除（/）、百分号（%）、乘幂（＾） 2. 比较运算符：大与（>）、小于（<）、等于（=）、小于等于（<=）、大于等于（>=）、不等于（<>） 3. 引用运算符：区域运算符（：）、联合运算符（,） （三）单元格的相对引用与绝对引用 例： A1 $A1 锁定第A列 A$1 锁定第1行 $A$1 锁定第A列与第1行 二、常用函数 （一）数学函数 1. 求和 =SUM（数值1,数值2,……） 2. 条件求和 =SUMIF（查找的范围,条件(即对象),要求和的范围） 例：（1）=SUMIF(A1:A4,”>=200”,B1:B4) 函数意思：对第A1栏至A4栏中，大于等于200的数值对应的第B1列至B4列中数值求和 （2）=SUMIF(A1:A4,”<300”,C1:C4)
函数意思：对第A1栏至A4栏中，小于300的数值对应的第C1栏至C4栏中数值求和 3. 求个数 =COUNT（数值1,数值2,……） 例：（1） =COUNT(A1:A4) 函数意思：第A1栏至A4栏求个数（2） =COUNT(A1:C4) 函数意思：第A1栏至C4栏求个数 4. 条件求个数 =COUNTIF（范围,条件） 例：（1） =COUNTIF(A1:A4，”<>200”) 函数意思：第A1栏至A4栏中不等于200的栏求个数 （2）=COUNTIF(A1:C4,”>=1000”) 函数意思：第A1栏至C4栏中大于等1000的栏求个数 5. 求算术平均数 =AVERAGE（数值1,数值2,……） 例：（1） =AVERAGE(A1,B2) （2） =AVERAGE(A1:A4) 6. 四舍五入函数 =ROUND(数值,保留的小数位数) 7. 排位函数 =RANK（数值,范围,序别） 1-升序 0-降序 例：（1） =RANK(A1,A1:A4,1) 函数意思：第A1栏在A1栏至A4栏中按升序排序，返回排名值。 （2） =RANK(A1,A1:A4,0) 函数意思：第A1栏在A1栏至A4栏中按降序排序，返回排名值。 8. 乘积函数 =PRODUCT（数值1,数值2,……） 9. 取绝对值 =ABS(数字) 10. 取整 =INT(数字) （二）逻辑函数
Excel表格公式大全 太强大了!
Excel表格公式大全太强大了！ 2014-02-23免费订阅??每天一点经济学 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女 "),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的 人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的 人数；假设把结果存放于K60单元格；
excel中的一些常用计算公式
如何使用电子表格excel中的一些常用计算公式 2010-05-11 23:17:27| 分类：电脑赤脚医生| 标签：|字号大中小订阅 一、年龄计算公式： =IF((YEAR(E2)=2008)*(MONTH(E2)>8),0,2008-YEAR(E2) IF(MONTH(E2)<=8,0,-1)) 其中e2为单元格，2008为当前年份 二、身份证号中提取出生年月= MID(J11, 7, 4) & "年" & MID(J11, 11, 2) & "月" & MID(J11, 13, 2)&"日" 三、班级平均分公式=IF(COUNTIF($C$2:$C$24,J26)=0,"",SUMIF($C$2:$C$24,J26,$D$2)/COUNTIF($C$2:$C$24,J26)) 四、Excel表中身份证号码提取出生年月、性别、年龄的使用技巧 如何从Excel表中的身份证号码中提取出生年月、性别、年龄呢？看了本文就知道了。 方法一： 1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数); 2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。 18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。 从身份证号码中提取出表示出生年、月、日的数字，用文本函数MID()可以达到目的。MID()——从指定位置开始提取指定个数的字符（从左向右）。 对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。综合上述分析，可以通过下述操作，完成形如1978-12-24样式的出生年月日自动提取： 假如身份证号数据在A1单元格，在B1单元格中编辑公式 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)) 回车确认即可。 如果只要“年-月”格式，公式可以修改为 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11,2)) 3.这是根据身份证号码（15位和18位通用）自动提取性别的自编公式，供需要的朋友参考： 说明：公式中的B2是身份证号 根据身份证号码求性别： =IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0,"女","男"),"身份证错")) 根据身份证号码求年龄： =IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4)),"身份证错")) 4.Excel表中用Year\Month\Day函数取相应的年月日数据; 方法二：
excel常用公式详解
一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,'') 说明：如果是错误值则显示为空，否则正常显示。
2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2='未到期'),'补款','') 说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2)
说明：如果返回值大于0说明在另一个表中存在，0则不存在。 2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。 四、求和公式
1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明：如果标题行没有规则用第2个公式 2、单条件求和 公式：F2 =SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法
3、单条件模糊求和 公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如'*A*'就表示a前和后有任意多个字符，即包含A。
4、多条件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&'*',B2:B7,B11) 说明：在sumifs中可以使用通配符* 5、多表相同位置求和 公式：b2 =SUM(Sheet1:Sheet19!B2) 说明：在表中间删除或添加表后，公式结果会自动更新。 6、按日期和产品求和
最常用Excel函数公式大全
Excel函数公式大全工作中最常用Excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。 ? 2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明：两个条件同时成立用AND,任一个成立用OR函数.
? 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。 ? 2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。
? 四、求和公式 1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3) 说明：如果标题行没有规则用第2个公式 ? 2、单条件求和 公式：F2 =SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法
? 3、单条件模糊求和 公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。 ? 4、多条件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明：在sumifs中可以使用通配符*
excel表格公式大全
一、在Word中三个小窍门： ①连续输入三个“~”可得一条波浪线。 ②连续输入三个“-”可得一条直线。 连续输入三个“=”可得一条双直线。 一、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比 如： A1〉1时，C1显示红色 0
2、用条件格式避免重复。 选定A列，点"格式"->"条件格式"，将条件设成“公式=COUNTIF($A:$A,$A1)>1”， 点"格式"->"字体"->"颜色"，选定红色后点两次"确定"。 这样设定好后你输入数据如果长度不对会有提示，如果数据重复字体将会变成红色。 三、在EXCEL中如何把B列与A列不同之处标识出来？ （一）、如果是要求A、B两列的同一行数据相比较： 假定第一行为表头，单击A2单元格，点“格式”->“条件格式”，将条件设为: “单元格数值” “不等于”=B2 点“格式”->“字体”->“颜色”，选中红色，点两次“确定”。 用格式刷将A2单元格的条件格式向下复制。 B列可参照此方法设置。 （二）、如果是A列与B列整体比较（即相同数据不在同一行）： 假定第一行为表头，单击A2单元格，点“格式”->“条件格式”，将条件设为: “公式”=COUNTI F($B:$B,$A2)=0 点“格式”->“字体”->“颜色”，选中红色，点两次“确定”。 用格式刷将A2单元格的条件格式向下复制。 B列可参照此方法设置。 按以上方法设置后，AB列均有的数据不着色，A列有B列无或者B列有A列无的数据标记为红色字体。 四、EXCEL中怎样批量地处理按行排序 假定有大量的数据(数值)，需要将每一行按从大到小排序，如何操作？ 由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字 排序。所以，这一问题不能用排序来解决。解决方法如下： 1、假定你的数据在A至E列，请在F1单元格输入公式： =LARGE($A1:$E1,COLUMN(A1)) 用填充柄将公式向右向下复制到相应范围。 你原有数据将按行从大到小排序出现在F至J列。如有需要可用“选择性粘贴/数值” 复制到其他地方。
如何使用电子表格excel中的提取数字常用计算公式
如何使用电子表格excel中的提取数字常用计算公式 一、年龄计算公式： =IF((YEAR(E2)=2008)*(MONTH(E2)>8),0,2008-YEAR(E2) IF(MONTH(E2)<=8,0,-1)) 其中e2为单元格，2008为当前年份 二、身份证号中提取出生年月= MID(J11, 7, 4) & "年" & MID(J11, 11, 2) & "月" & MID(J11, 13, 2)&"日" 三、班级平均分公式 =IF(COUNTIF($C$2:$C$24,J26)=0,"",SUMIF($C$2:$C$24,J26,$D$2)/COUNTIF($C$2:$C$24,J26)) 四、Excel表中身份证号码提取出生年月、性别、年龄的使用技巧 如何从Excel表中的身份证号码中提取出生年月、性别、年龄呢？看了本文就知道了。 方法一： 1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数); 2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。
18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。 从身份证号码中提取出表示出生年、月、日的数字，用文本函数MID()可以达到目的。MID()——从指定位置开始提取指定个数的字符（从左向右）。 对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。综合上述分析，可以通过下述操作，完成形如1978-12-24样式的出生年月日自动提取： 假如身份证号数据在A1单元格，在B1单元格中编辑公式 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"& MID(A1,13,2)) 回车确认即可。 如果只要“年-月”格式，公式可以修改为 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11,2)) 3.这是根据身份证号码（15位和18位通用）自动提取性别的自编公式，供需要的朋友参考： 说明：公式中的B2是身份证号
(完整版)excel基本常用函数公式大全
1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式： =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分；
7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格； （5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60) ——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格； （6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61) ——求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格； （7）=COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格；