Ecel 2003电子表格函数公式大全

电子表格函数公式使用集锦

怎么让excel表格公式只能看不能被改：选定不需要保护的的单元格，

单击右键一设置单元格格式一取消锁定一选定需要保护的的单元格，单击右

键一设置单元格格式一锁定一工具一保护一保护工作表一输入密码一确定。

一、电子表格中由身份证号自动导出年月日的公式

=IF(LEN(E1)=15,“19"&MID(E1,7,2)&"-"&MID(El,9,2)&"-"&MID(El,H

,2),MID(El,7,4)(El,H,2)(El,13,2))转换出生年月如

1986-05-23

说明：

E：列数E1890：第E列第1890行。输入身份证号码

LEN(text):返回本字符串的个数。LEN(“123”)=3

LEN(E1)=15表示如果身份证号码为15个数字

也:表示相加

MID(字符串，M,N)：从该字符串第M位开始，取N位字符。

MID(Al,3,4)=3456,从“Al”单元格中的第“3”位起截取“4”个

数

IF(条件表达式，语句1,语句2)：如果条件成立，那么就执行语句1,

否则执行语句2

LEFT(Al,14)截取Al单元格前14位数

RIGHT(Al,14)截取Al单元格后14位数

身份证号码有两种，如“352124860213541”或

如果E1是15个，为19加上从第7个开始取2个“86”加上-加上从第9个

开始取2个“02”加上-加上从第11个开始取2个“13”合起来为

“1986-02-13”，否则为从第7个开始取4个“1986”加上-加上从第11

个开始取2个“02”加上-加上从第13个开始取2个“13”合起来为

"1986-02-13"o〃19〃、〃-“为直接写入的数。

=IF(LEN(A1)=15,“19"&MID(A1,7,4),MID(Al,7,6))转换出生年月取如

“198606”

=IF(LEN(Al)=15,CONCATENATEn9z/,MID(Al,7,2),MID(Al,9,2)),

IF(LEN(Al)=18,CONCATENATE(MID(Al,7,4),MID(A1,H,2)),”身份证错

"))转换出生年月取如1986.05

=2010-MID(Bl,1,4)-IF((MID(Bl,5,2)-0)>8,1,0)计算年龄

=IF(LEN(Al)=15,YEAR(N0W())-1900-VALUE(MID(Al,7,2)),IF(LEN(Al)

=18,YEAR(NOW())-VALUE(MID(Al,7,4)),"身份证错〃))计算年龄,月数全部

不算如24岁2个月和24岁H个月都是24岁

=IF(LEN(Al)=15,IF(MOD(VALUE(RIGHT(Al,3)),2)=0,"女"J男

"),IF(LEN(Al)=18,IF(MOD(VALUE(MID(Al,15,3)),2)=0「女"男")，"身份

证错〃))转换性别

二、成绩在年级里的排名菜一一RANK()函数的使用

=RANK(N2,$N$2:$N$1501,0)

N2为所要排名的单元格，$N$2:$N$1501为从N2列到N1501歹U，。表示

为按照降序排列的列表，不为零为按照升序排列的列表

=RANK(C1,$C$1:$C$1O)为10个学生中的第一个的排名

三、利用函数统计考试成绩

=COUNTA(A1:A25)算有数值的单元格个数应考人数

=COUNT(B1:B25)和上面的一样用处算出考试人数

=COUNTBLANK(B1:B25)算出缺考人数

=COUNTIF(B1:B25,"〉=90”)算90分以上人数=COUNTIF(B1:B25,">=80")-

COUNTIF(B1:B25,">=90")算80到90分人数

=MAX(C1:C25)算最高分

工HN(C1:C25)算最低分

=AVERAGE(C1:C25)算平均分

=COUNTIF(Cl:C25,/，>=90/，)/COUNT(Cl:C25)90分以上占百分比

=MEDIAN(B1:B25)算中位数

=MODE(B1:B25)算众数

=STDEVP(B1:B25)算标准差

四、文本格式转换成数值格式

在原单元格上转换：在任一空白单元格输入1—复制1一选定所有需要

改变的单元格一右键一选择性粘贴一选〃乘〃，用0加计算也行

在新单元格上转换：选定新单元格，"=所要转换的文本单元格-0”

在新单元格上转换：复制一选择性粘贴一数值一点击下拉一转换成数值

五、也的应用

=B2&C2表示把两个单元格的数值合为一个如“45”、“67”合为“4567”

=$F$17&C1表示在一个数值前加一个数，在任意单元格输入123设为绝

对值，再加所要添加的单元格

六、IF的应用(满足其中一个条件的判断)

=IF(A2〉89,“优+”,IF(A2>79,“优”,IF(A2〉69,“良+”,IF(A2〉59,“及格

不及格〃))))

=IF(A2>B2,〃超预算〃,〃预算内〃)是对预算执行结果的判断

=IF(A2=100,SUM(B5:B15),n如果A2数字为100,则计算单元格区域

B5:B15,否则返回空文本(")

=IF(AND(Al>0,Al<=10),1,IF(AND(Al>10,Al<100),2,IF(AND(Al>=100,

Al〈200),3,")))当A列中的数值大于0小于等于10时返回1,大于10小

于100返回2,大于等于100小于200返回3

=IF(AND(A1=B1,A1=C1),1,0)如果A1=B1=C1,则在DI显示1,若不相等

则返回0

=IF(Cl>60,IF(AND(C1〉9O),〃优秀〃，〃合格〃)，〃不合格〃)如果单元格C1

的值大于60,则执行第二个参数，在这里为嵌套函数，继续判断单元格C1

的值是否大于90(为了让大家体会一下AND函数的应用，写成AND(C1〉9O),

实际上可以仅写Cl〉90),如果满足在单元格C2中显示优秀字样，不满足显

示合格字样，如果C1的值以上条件都不满足，则执行第三个参数即在单元

格C2中显示不合格字样。

=IF(A1=1,"A”,IF(A1=2,"B”,IF(A1=3,"C”,IF(A1=4,"D",IF(A1=5,"E",

IF(Al=6,"F",IF(Al=7,"G",IF(Al=8,""))))))))&IF(Al=9,"Q",IF(Al=

10,勺"，IF(A1=H,"k”,IF(A1=12,"y”,IF(A1=13,"x”,IF(A1=14,IF(A1=

15,"m”,IF(A1=16,〃o〃，〃〃))))))))&……为多层嵌套

七、AND函数(同时满足条件判断)

AND(条件一，条件二.条件三…)，条件一二三…都成立时返回

TURE,否则返回FALSE

=IF(AND(A1〉6O,B1〉6O,C1〉6O),“及格”，〃不及格“)，当AND(Al>60,

Bl>60,Cl>60)为TURE时返回“及格”，为FALSE时返回“不及格”

=IF(Al<60,"不及格”，IF(AND(Al〉60,A"70),〃及格

”,IF(AND(A1>7O,A1<85),"良好”，IF(A1>85,“优秀”))))可以和IF函数合起

来使用

八、CONCATENATE函数(将几个文本字符串合并为一个文本字符串)

=CONCATENATE(Al,Bl,Cl,DI)

也可以用&(和号)运算符代替函数CONCATENATE实现文本项的

合并。

九、名称和标志

为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名

称，从而在公式或函数中直接引用。例如"B2:B46”区域存放着学生的物理

成绩，求解平均分的公式一般是“=AVERAGE（B2:B46）”。在给B2:B46区域命

名为“物理分数”以后，该公式就可以变为“=AVERAGE（物理分数）”，从而

使公式变得更加直观。

给一个单元格或区域命名的方法是：选中要命名的单元格或单元格区

域，鼠标单击编辑栏顶端的“名称框”，在其中输入名称后回车。也可以选

中要命名的单元格或单元格区域，单击“插入一名称一定义"菜单命令，在

打开的“定义名称”对话框中输入名称后确定即可。如果你要删除已经命名

的区域，可以按相同方法打开“定义名称”对话框，选中你要删除的名称删

除即可。

由于Excel工作表多数带有“列标志”。例如一张成绩统计表的首行通

常带有“序号”、“姓名”、“数学”、“物理”等“列标志”（也可以称为字段），

如果单击“工具一选项”菜单命令，在打开的对话框中单击“重新计算”选

项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以

直接引用“列标志”To例如“B2:B46”区域存放着学生的物理成绩,而B1

单元格已经输入了“物理”字样，则求物理平均分的公式可以写成

/AVERAGE（物理）”。

十、几个常用函数

=ABS（A2）,则在A2单元格中无论输入正数（如100）还是负数（如-100）,

B2中均显示出正数（如100）

=AND（A5>=60,B5>=60）,确认。如果C5中返回TRUE,说明A5和B5中的

数值均大于等于60,如果返回FALSE,说明A5和B5中的数值至少有一个小

于60

=COLUMN（B11）,确认后显示为“2”（即B列）

十一、与求和有关的函数

1、=SUM（H3:H12）求H3至H12的和

2、SUBTOTAL（function_num,refl,ref2,,,,）分类汇总

Functionnum为1到H之间的数字，指定使用何种函数在数据清

单中进行分类汇总计算。

Function_Num函数

1AVERAGE——求算术平均数

2COUNT——计算参数列表中的数字项的个数

3COUNTA——计算单元格区域或数组中包含数据的单元格个数。

4MAX——求最大值

5MIN---求最小值

6PRODUCT——单元格内的乘积

7STDEV——估算样本的标准偏差，反映相对于平均值的离散程度

8STDEVP——整个样本总体的标准偏差

9SUM——求和

10VAR——计算基于给定样本的方差

11VARP——计算基于整个样本总体的方差

例：“=SUBTOTAL（9,A2:A5）对A2至A5列使用SUM函数计算出的分类

汇总（303）”，"-SUBTOTAL。,A2:A5）对A2至A5列使用AVERAGE函数计

算出的分类汇总（75.75）”

3、SUMIF——根据指定条件对若干单元格求和

例：=SUMIF（$C$3:$C$12,“销售部”,$F$3:$F$12）,“$C$3:$C$12"指部

门名称单元格，〃销售部〃指计算其中的“销售部”部门，“$F$3:$F元格指

部门名称相应的数值单元格。

4、SUMPRODUCT——在给定的几组数组中，将数组间对应的元素相

乘，并返回乘积之和

=SUMPR0DUCT(A15:A16,B15:B16)表示“A15*B15+A16*B16”

=SUMPR0DUCT(B2:C4*D2:E4)表示“B2*D2+C2*E2+...+B4*D4+C4*E4”

=SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))计算符合2

个及以上条件的数据个数(4人的单元格输入公式)

姓名性别职称性别中一中二

A男中一男4

B女中二女

C女中一

D男中一

E女中一

F男中二

G女中二

H男中一

I男中一

J女中一

=SUMPRODUCT(($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11)

计算男、女分别是中一或中二的总课时数(在15节单元格中输入公式)

姓名性别职称课时性别中二中一

男

中55

A男一

女

二

中6

B女一

中4

C女

中3

D男一

中8

E女一

中5

F男二

中6

G女二

中4

H男一

中7

I男二

中8

J女

SUMSQ函数：计算多个数值的平方和。如SUMSQ(B2,C2)=B2的平方+C2

的平方。

ROUND函数：如ROUND(B2,2)就是对B2进行四舍五入保留2位小数。

INT(将数字向下舍入到最接近的取整函数)

IF和AND嵌套使用：=IF(AND(Al>60,Bl>60,Cl>60)，"及格”，〃不及格

〃)，当Al,Bl,Cl都大于60时返回“及格”

=IF(Al<60,"不及格”，IF(AND(Al〉60,A"70),〃及格

”,IF(AND(A1〉7O,A"85),"良好",IF(A1>85,〃优秀")))),当Al<60时返回

“不及格”，当60〈A"70时返回“及格”，当70〈A"85时返回“良好”,当

Al>85时返回“优秀”

COUNTIF函数：计算其中满足条件的单元格数目，如

C0UNTIF(B4:B10,"〉90"),计算B4到B10这个范围各科成绩中有多少个数值

大于90的单元格。如COUNTIF($C$2:$C$13,A17),计算$C$2:$C$13这个范

围有多少个A17(A17存放的是姓名)

SUMIF($C$2:$C$13,A17,$B$2:$B$13)计算其中(A17)的销售奖金，

$C$2:$C$13是销售人员的姓名,A17是其中的一个姓名,$B$2:$B$13是销售

金额区域，

IF(C17<50000,10%,15%)*C17如果订单总额小于50000则奖金为10%；

如果订单总额大于等于50000,则奖金为15%

十二、字母大小写转换

LOWER(Al)将Al文字串中的所有字母转换为小写字母。

UPPER(Al)将Al文本转换成大写形式。

PROPER(Al)将Al文字串的首字母及任何非字母字符之后的首字母转换

成大写。将其余的字母转换成小写。

十三、取出字符串中的部分字符

LEFT("Thisisanapple",4)=This从前面取

RIGHT(Z/Thisisanapple",5)=apple从后面取

MID(Z/Thisisanapple”,6,2)=is从中间取

十四、取出当前系统时间/日期信息

NOW()取当前系统“年月日时分”

TODAY。取当前系统“年月日”

YEAR(E5)=2001取单元格的“年”

MONTH(E5)=5取单元格的“月”

DAY(E5)=30取单元格的“日”

HOUR(E5)=12取单元格的“时”

DATEDIF：计算两个日期之间的天数、月数或年数：其中计算年数为

DATEDIF(A24,T0DAY(),"y"),勺〃时间段中的整年数，"M"时间段中的整月

数，〃D〃时间段中的天数，〃MD〃为日期中天数的差，忽略日期中的月和年(直

接天数相减，不够减要向上月借一)，“YF为日期中月数的差，忽略日期中

的日和年(直接月数相减，不够减要向上月借一)，〃YD〃为日期中天数的

差。忽略日期中的年(月日合计相减，不够减要向上月借一)。

VALUE：将代表数字的文字串转换成数字，语法形式