[ChneChen的技术文档][OFFICE应用]EXCEL公式[2013/0108更新]

因为工作等原因,常用到EXCEL,于是,自己就总结了一下一些常用的EXCEL公式(Chnechen于2013/01/08更新):



[判断类]

判断b1的值是不是等于c1+d1+e1
=IF(B1=(SUM(C1:E1)),"正确","错误请检查")

多条件判断：
=IF(Sheet1!D3="","未输入",(IF(Sheet1!D3>=11,(IF(Sheet1!D3<=5334,(IF(Sheet1!D3<5301,(IF(Sheet1!D3<=82,"正确","错误")),"正确")),"错误")),"错误")))
//如果没输显示“未输入”，
//如果输入数据》=11且《=82 或者输入数据》=5301且《=5334, 显示“正确”，
//否则就是显示“错误”，
//注意，IF语句最多只能镶套7层
---------------------------------------------------------------

[求和类]

对满足大理市 下关镇  XX社区条件的特定字段(H3:H845)求和：
=SUM(IF(E3:E845<>"下关镇",0,IF(B3:B845<>"大理市",0,JH3:H845)))

对满足大理市 下关镇  XX社区条件的特定字段(H3:H845)求和：
=SUM(IF(G3:G845<>"XX社区",0,IF(E3:E845<>"下关镇",0,IF(B3:B845<>"大理市",0,H3:H845))))
//解释：如果 G3:G845<>"XX社区",那么返回0, 否则返回本身（也就是说="XX社区",返回本身,<>"XX社区",返回0,）

 

[计算类]

IF(C1="主叫", ROUNDUP(G1/60,0),0)
//此公式为计算电话话费时用,
//含义如果C1内容为"主叫",那就对G1(此为通话时长)进行转换,转换为通话分钟数
// ROUNDUP:远离零值，向上舍入数字。

语法

ROUNDUP(number,num_digits)

Number    为需要向上舍入的任意实数。

Num_digits    四舍五入后的数字的位数。

---------------------------------------------------------------

[统计类]

统计E3:E845满足条件 大理 的个数
COUNTIF(E3:E845,"大理")

统计sheet3表C3:C3000 满足条件 *A*的个数
=COUNTIF(sheet3!C$3:sheet3!C$3000,"*A*")
//*A* 指：如ZAQWSX 中只要有A，就加1

G2:G845中满足B2:B845="大理市"个数
=sum(if(B2:B845="大理市",0,G2:G845))
=SUMIF(B2:B845,"大理市",G2:G845)

 

COUNTA

返回参数列表中非空值的单元格个数。利用函数 COUNTA 可以计算单元格区域或数组中包含数据的单元格个数。

语法
COUNTA(value1,value2,...)
Value1, value2, ...    为所要计算的值，参数个数为 1 到 30 个。在这种情况下，参数值可以是任何类型，它们可以包括空字符 ("")，但不包括空白单元格。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。如果不需要统计逻辑值、文字或错误值，请使用函数 COUNT。
示例
=COUNTA(B3:B25,D3:D25)&"人"
------------------------------------------------------------

[其它类]

生成10-50内的随机数（四舍五入到整数）
=ROUND((RAND()*(40)+10),0)

RANK
返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。
语法
RANK(number,ref,order)
Number    为需要找到排位的数字。
Ref    为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。
Order    为一数字，指明排位的方式。
如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。
如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。
---------------------------------------------------------------------

 

 

//统计 D1单元格数据是否在 已办照 表 C$1:C$236 单元格中出现，  当已办照 表 C$1:C$236没有内容为D1单元格数据时，显示“未验”，否则显示“已验”

 =IF(ISERROR(MATCH(D1,已办照!C$1:C$236,0)),"未验","已验")

 

//统计 "日用百货"内容  是否在 F2单元格中出现，  当F2单元格中没有包含"日用百货"内容时，显示“”，否则显示“日用百货”

//注意  绝对匹配用 "日用百货",  仅包含用   "*日用百货*",

=IF(ISERROR(MATCH("*日用百货*",F2,0)),"","日用百货")

 

 MATCH函数

　　含义：返回指定数值在指定数组区域中的位置

　　语法：MATCH(lookup_value, lookup_array, match_type)

　　lookup_value：需要在数据表（lookup_array）中查找的值。

　　lookup_array：可能包含有所要查找数值的连续的单元格区域。

　　match_type：为1时，查找小于或等于lookup_value的最大数值，lookup_array必须按升序排列：

　　                        为0时，查找等于lookup_value的第一个数值，lookup_array按任意顺序排列：

　　                        为-1时，查找大于或等于lookup_value的最小数值，lookup_array必须按降序排列。

 

ISERROR函数

返回 Boolean 值，指出表达式是否为一个错误值。

---------------------------------------------------------------------

 

//返回D1单元格内容并在最前面加上532900

"532900"&D1