常用函数公式及用法

电子表格常用函数公式及用法
1、 求和公式：
二
SUM(A2:A50) ——对A2到A50这一区域进行求和；
2、 平均数公式：
二
AVERAGE(A2:A56) ——对A2到A56这一区域求平均数；
3、 最高分：
=MAX(A2:A56) ——求A2到A56区域(55名学生)的最高分；
4、 最低分:
=MIN(A2:A56) ——求A2到A56区域(55名学生)的最低分；
5、 等级：
=IF(A2>=90,优良及格不及格
6、 男女人数统计：
二
C0UNTIF(D1:D15,男—— 统计男生人数
二
C0UNTIF(D1:D15,女—— 统计女生人数
7、 分数段人数统计：
方法一：
求 A2 到 A56 区域 100 分人数：
二
COUNTI F(A2:A56,
求A2到A56区域60分以下的人数；
二
COUNTIF(A2 :A56,
求A2到A56区域大于等于90分的人数；
二
COUNTIF(A2:A 56,
求A2到A56区域大于等于80分而小于90分的人数；
1 9

=COUNTIF(A1:A29,
求A2到A56区域大于等于60分而小于80分的人数;
二
COUNTIF(A1:A29,
方法二：
(1) =COUNTIF(A2:A56,——求 A2 到 A56 区域 100 分的人
数；假设把结果存放于 A57单元格；
(2) =COUNTIF(A2:A56,— A57 ——求 A2 到 A56 区域大
于等于95而小于100分的人数；假设把结果存放于 A58单元格；
(3) =COUNTIF(A2:A56,— SUM(A57:A58) ——求 A2 到 A56
区域大于等于90而小于95分的人数；假设把结果存放于 A59单元
格；
(4) =COUNTIF(A2:A56,— SUM(A57:A59) ——求 A2 到 A56
区域大于等于85而小于90分的人数；
8、 求 A2 到 A56 区域优秀率：=(C0UNTIF(A2:A56,
9、 求 A2 到 A56 区域及格率：=(COUNTIF(A2:A56,
10、 排名公式：
=RANK(A2 , A$$2:A$$56)――对55名学生的成绩进行排名；
11、 标准差：
二
STDEV(A2:A56) ——求A2到A56区域(55人)的成
绩波动情况(数值越小，说明该班学生间的成绩差异较小，反之，说
明该班存在两极分化)；
12、 条件求和：
二
SUMIF(B2:B56,男, K2:K56) ——假设B列存放
2 9

学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班
男生的成绩之和；
13、多条件求和： {
二
SUM(IF(C3:C322
二
男}
――假设C列(C3:C322区域)存放学生的性别，G列(G3:G322
区域)存放学生所在班级代码(1、2、3、4、5)，则此函数返回的
结果表示求一班的男生人数；这是-个数组函数，输完后要按
-
…} ”。){} ”不能手工输
+ Shift + Enter组合键
(
产生
入,
{…，
Ctrl
只能
用组合键产生。“ 1,1,0 ”中的第一个1表示班级，第二个1表示计算
次数(倍数)。
14、查找重复内容公式：
(1) 标记重复两次以上的全部数据
=IF(C0UNTIF(A:A,A2)>1,重复。“重复”可以根据需要更改为其 它
数据。A2表示此栏结果对应的是第二行。
(2) 标记超过一个以上的其它重复数据(只留一个)
=IF(COUNTIF(A2:A56,A2)=1,重复
(3) 提取不重复数据
方法1：将A列的姓名查找重复，并提取到C列。C2单元格输入公式：
=OFFSET($$ A$$1,MATCH(,COUNTIF($$C$$1:C1,$$A$$2:$$A$$17),),)&，然
后 按 ctrl+shift+enter 三键结束。
方法2：将A列不重复数据标识到C列，单击数据一一高级筛选完成。
选择操作的数据区域，设置数据的复制位置，最重要的就是勾选上“选
择不重复的记录”。
3 9

方法3：用数据比对公式
二
COUNTIF($$A$$9:A9,A9。通过筛选，筛选出
“ 1”的数据，然后复制粘贴到其他的单元格中即可。 这样C列提取
到的就是A列不重复数据。
(3)多行多列提取不重复数据，如下:

1 ------
A B

D

1
销售人员
徐航
周德宇
王学敏
王謄宇
周德宇
刘敬奎
周德宇
王学敏
夭琦
王学敏
p
4
王腾宇
5
周德宇
王腾宇
王学敏
8
周德宇
L 9
曹泽鑫
「
「





房天琦
王学敏
王腾宇
周德宇
曹泽癱


刘敬璽

D2输入公式:

=INDIRECT(TEXT(RIGHT(MIN(IF(C 0UNTIF($$D$$1:D1,$$A$$2:$$B$$ 9), 4
八
8,ROW($$2:$$ 9)*100+COLUMN(A:B)*10001)),4),，
然后按ctrl+shift+enter三键结束。这样就把 AB列不重复数据提取 到D
列。
15、用出生年月来计算年龄公式：
=DATEDIF(A3,TODAY(),或=DATEDIF(A3,。
=TRUNC((DA YS360(D3,NOW( )))360,0)
16、用身份证来计算年龄公式：
二
DATEDIF(TEXT(MID(A2,7 ,8),
17、从输入的18位身份证号的出生年月计算公式：
=CONCATENATE(MID(E2,7,4),
18、 从输入的身份证号码内让系统自动提取性别公式：
4 9
<

=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1) ,2)=1,
男女
19、 在Word中三个小窍门：
连续输入三个“ ~”得一条波浪线。
连续输入三个-“可得一条直线。
连续输入三个“=可得一条双直线。
男
，
” 女
20、 excel中当某一单元格符合特定条件，如何在另一单元格显示特
定的颜色比如：
A1 > 1时，C1显示红色
0A1<0时，C1显示黄色
方法如下：
1、 单元击C1单元格，点格式”涤‘件格式”条件1设为：
公式=A1 = 1
2、 点格式”> 字体”> 颜色”点击红色后点 确定”
条件2设为：
公式=AND(A1>0,A1<1)
3、 点格式”> 字体”> 颜色”点击绿色后点 确定”
条件3设为：
公式
二
A1<0
点格式”> 字体”> 颜色”点击黄色后点 确定”
5 9

4、三个条件设定好后，点 确定”即出。
21、 EXCEL中如何控制每列数据的长度并避免重复录入
（1）用数据有效性定义数据长度。
用鼠标选定你要输入的数据范围，点数据有效性设置，有 效性
条件设成允许文本长度等于（具体条件可根据你的需要 改
变）。
还可以定义一些提示信息、出错警告信息和是否打开中文输入法等， 定
义好后点确定”。
（2 ）用条件格式避免重复。
选定A列，点格式条件格式，将条件设成公式
二
C0UNTIF
（$$A:$$A,$$A1）>1 ”，点格式字体颜色，选定红色后 点两次确
定。
这样设定好后你输入数据如果长度不对会有提示，如果数据重复字体 将
会变成红色。
22、 在EXCEL中如何把B列与A列不同之处标识出来？
（一）、如果是要求A、B两列的同一行数据相比较：
假定第一行为表头，单击A2单元格，点格式”> 条件格式”将条件 设
为
：
单元格数值”不等于”
二
B2
点格式”> 字体”> 颜色”选中红色，点两次 确定”
用格式刷将A2单元格的条件格式向下复制。
B列可参照此方法设置。
6 9

(二)、如果是A列与B列整体比较(即相同数据不在同一行)： 假定第
一行为表头，单击A2单元格，点格式”> 条件格式”将条件 设为
：
公式”
二
COUNTIF($$B:$$B,$$A2)=0
点格式”> 字体”> 颜色”选中红色，点两次 确定”
用格式刷将A2单元格的条件格式向下复制。
B列可参照此方法设置。
按以上方法设置后，AB列均有的数据不着色，A列有B列无或者B 列有
A列无的数据标记为红色字体。
23、EXCEL中怎样批量地处理按行排序
假定有大量的数据
(
数值
)
，需要将每一行按从大到小排序，如何操 作？
由于按行排序与按列排序都是只能有一个主关键字
，
主关键字相同时
才能按次关键字排序。所以，这一问题不能用排序来解决。解决方法 如
下：
1、假定你的数据在A至E列，请在F1单元格输入公式：
二
LARGE($$A1:$$E1,C0LUMN(A1))
用填充柄将公式向右向下复制到相应范围。
你原有数据将按行从大到小排序出现在 F至J列。如有需要可用 选 择
性粘贴数值”复制到其他地方。
注：第1步的公式可根据你的实际情况(数据范围)作相应的修改。 如果
要从小到大排序，
公式改为:=SMALL($$A1:$$E1,COLUMN(A1))
24、 巧用函数组合进行多条件的计数统计
例：第一行为表头，A列是 姓名”，B列是班级”，C列是语文成绩”,
7 9

D列是 录取结果”，现在要统计 班级”为二”，语文成绩”大于等于
104，录取结果”为重本”的人数。统计结果存放在本工作表的其他列。
公式如下：
二
SUM(IF((B2:B9999
二
二
二
重 本
输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号
。
25、 如何判断单元格里是否包含指定文本？
假定对A1单元格进行判断有无指定文本以下任一公式均可
：
=IF(COUNTIF(A1,指定文本有无
=IF(ISERROR(FIND(” 指定文本无有
求某一区域内不重复的数据个数
例如求A1:A100范围内不重复数据的个数，某个数重复多次出现只
算一个。有两种计算方法：
一是利用数组公式：
=SUM(1COUNTIF(A1:A100,A1:A100))
输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号
。

二是利用乘积求和函数：
二
SUMPR0DUCT(1C0UNTIF(A1:A100,A1:A100))
26、 一个工作薄中有许多工作表如何快速整理出一个目录工作表
1、用宏3.0取出各工作表的名称，方法：
8 9

Ctrl+F3出现自定义名称对话框，取名为 X，在引用位置”框中输入:
二
MID(OK(1),FIND(确
定
2、用HYPERLINK函数批量插入连接，方法：
在目录工作表(一般为第一个sheet )的A2单元格输入公式：
=HY PERLINK(
将公式向下填充，直到出错为止，目录就生成了。
9 9