EXCEL实用函数公式图文汇集.docx
《EXCEL实用函数公式图文汇集.docx》由会员分享，可在线阅读，更多相关《EXCEL实用函数公式图文汇集.docx（30页珍藏版）》请在冰点文库上搜索。
EXCEL实用函数公式图文汇集
EXCEL2003实用公式、函数
一、单组数据加减乘除运算：
①单组数据求加和公式：
=（A1+B1）
举例：
单元格A1：
B1区域依次输入了数据10和5，计算：
在C1中输入=A1+B1后点击键盘“Enter（确定）”键后，该单元格就自动显示10与5的和15。
②单组数据求减差公式：
=（A1-B1）
举例：
在C1中输入=A1-B1即求10与5的差值5，电脑操作方法同上；
③单组数据求乘法公式：
=（A1*B1）
举例：
在C1中输入=A1*B1即求10与5的积值50，电脑操作方法同上；
④单组数据求乘法公式：
=（A1/B1）
举例：
在C1中输入=A1/B1即求10与5的商值2，电脑操作方法同上；
⑤其它应用：
在D1中输入=A1^3即求5的立方（三次方）；
在E1中输入=B1^（1/3）即求10的立方根
小结：
在单元格输入的含等号的运算式，Excel中称之为公式，都是数学里面的基本运算，只不过在计算机上有的运算符号发生了改变——“×”与“*”同、“÷”与“/”同、“^”与“乘方”相同，开方作为乘方的逆运算，把乘方中和指数使用成分数就成了数的开方运算。
这些符号是按住电脑键盘“Shift”键同时按住键盘第二排相对应的数字符号即可显示。
如果同一列的其它单元格都需利用刚才的公式计算，只需要先用鼠标左键点击一下刚才已做好公式的单元格，将鼠标移至该单元格的右下角，带出现十字符号提示时，开始按住鼠标左键不动一直沿着该单元格依次往下拉到你需要的某行同一列的单元格下即可，即可完成公司自动复制，自动计算。
二、多组数据加减乘除运算：
①多组数据求加和公式：
（常用）
举例说明：
=SUM（A1:
A10）,表示同一列纵向从A1到A10的所有数据相加；
=SUM（A1:
J1）,表示不同列横向从A1到J1的所有第一行数据相加；
②多组数据求乘积公式：
（较常用）
举例说明：
=PRODUCT（A1:
J1）表示不同列从A1到J1的所有第一行数据相乘；
=PRODUCT（A1:
A10）表示同列从A1到A10的所有的该列数据相乘；
③多组数据求相减公式：
（很少用）
举例说明：
=A1-SUM（A2:
A10）表示同一列纵向从A1到A10的所有该列数据相减；
=A1-SUM（B1:
J1）表示不同列横向从A1到J1的所有第一行数据相减；
④多组数据求除商公式：
（极少用）
举例说明：
=A1/PRODUCT（B1:
J1）表示不同列从A1到J1的所有第一行数据相除；
=A1/PRODUCT（A2:
A10）表示同列从A1到A10的所有的该列数据相除；
三、其它应用函数代表：
①平均函数=AVERAGE（:
）；②最大值函数=MAX（:
）；③最小值函数=MIN（:
）；④统计函数=COUNTIF（:
）：
举例：
Countif（A1：
B5，”>60”）
说明：
统计分数大于60分的人数，注意，条件要加双引号，在英文状态下输入。
1、请教excel中同列重复出现的货款号应怎样使其合为一列，并使款号后的数值自动求和？
比如：
A列B列
0018
0026
0025
0032
应怎样使其四行变为三行，切使002号后的数值为11？
第一方法：
对第A列进行分类汇总。
第二：
用透视表对A列进行分析。
2、比如以下四个数值，
1
2
3
4
求和为10，第三行隐藏，我希望得到答案是7。
谢谢!
问题补充：
=SUBTOTAL（109,A1:
A4）是正确的，但其中109是什么意思，请解释一下。
好象101.103等除了108都好用，谢谢!
=SUBTOTAL（9,A1:
A4）才是求和，但不行。
最佳答案
SUBTOTAL
返回数据清单或数据库中的分类汇总。
通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的数据清单。
一旦创建了分类汇总，就可以通过编辑SUBTOTAL函数对该数据清单进行修改。
语法
SUBTOTAL（function_num,ref1,ref2,...）
Function_num为1到11之间的数字，指定使用何种函数在数据清单中进行分类汇总计算。
Function_Num函数
1AVERAGE平均值2COUNT个数
3COUNTA非空格的个数4MAX最大值
5MIN最小值6PRODUCT乘积
7STDEV标准偏差8STDEVP总体标准偏差
9SUM和10VAR方差
11VARP总体方差
Ref1,ref2,为要进行分类汇总计算的1到29个区域或引用。
如果在ref1,ref2,…中有其他的分类汇总（嵌套分类汇总），将忽略这些嵌套分类汇总，以避免重复计算。
函数SUBTOTAL将忽略由数据清单筛选时产生的任何隐藏行。
在需要对筛选过的数据清单中的可见数据进行分类汇总时，这一点很重要。
如果所指定的某一引用为三维引用，函数SUBTOTAL将返回错误值#VALUE!
。
示例
SUBTOTAL（9,C3:
C5）将使用函数SUM产生单元格区域"C3:
C5"的分类汇总。
补充：
Function_Num函数
如果为101--111就是上面对应参数隐藏数值不参与计算。
1、LN函数：
该函数是计算自然对数，公式为：
“=LN（B3）”。
2、LOG函数：
该函数是计算指定底数的对数，公式为：
“=LOG10（B3）”。
3、MOD函数：
该函数是计算两数相除的余数。
如图7，判断C3能否被B3整除，可以在D4单元格中输入以下公式：
“=IF（MOD（B3,C3）=0,"是","否"）”。
图7
4、PI函数：
使用此函数可以返回数字3.14159265358979，即数学常量PI，可精确到小数点后14位。
如图8，计算球体的面积，可以在C4单元格中输入以下公式：
“=PI（）*（B3^2）*4）”；计算球体的体积，可以在D4单元格中输入以下公式：
“=（B3^3）*（4*PI（）））/3”。
图8
5、POWER函数：
此函数用来计算乘幂。
如图9，首先在单元中输入底数和指数，然后在D3中输入以下公式：
“=POWER（B3,C3）”。
图9
6、PRODUCT函数：
此函数可以对所有的以参数形式给出的数字相乘，并返回乘积。
例如：
某企业2005年度贷款金额为100000元，利率为1.5%，贷款期限为12个月。
如图10所示，直接在单元格E4中输入以下公式：
“=PRODUCT（B4,C4,D4）”。
图10
7、RADIANS函数：
此函数是用来将弧度转换为角度的。
可以在C3单元格中输入以下公式：
“=RADIANS（B3）”。
8、RAND函数：
此函数可以返回大于等于0及小于1的均匀分布随机数，每次计算工作表时都将返回一个新的数值。
如果要使用函数RAND生成一个随机数，并且使之不随单元格的计算而改变，可以在编辑栏中输入“=RAND（）”，保持编辑状态，然后按[F9]键，将公式永久性地改为随机数。
例如：
在全班50名同学中以随机方式抽出20名进行调查，如图11，在单元格中输入开始号码以及结束号码，然后在单元格B4中输入以下公式：
“=1+RAND（）*49”。
图11
9、ROUND函数：
此函数为四舍五入函数。
如图12，例如：
将数字“12.3456”按照指定的位数进行四舍五入，可以在D3单元格中输入以下公式：
“=ROUND（B3,C3）”。
10、ROUNDDOWN函数：
此函数为向下舍入函数。
例如：
出租车的计费标准是：
起步价为5元，前10公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收2元。
输入不同的公里数，如图13所示，然后计算其费用。
可以在C3单元格中输入以下公式：
“=IF（B3<=10,5+ROUNDDOWN（B3,0）*2,20+ROUNDDOWN（（B3-10）*2,0）*2）”。
图13
11、ROUNDUP函数：
此函数为向上舍入函数。
例如：
现在网吧的管理一般是采用向上舍入法，不满一个单元按照一个单位计算。
现假设每30分钟计价0.5元，请计算如图14中所示的上网所花费的费用。
1）计算上网天数：
首先在单元格C3中输入以下公式：
“=B3-A3”；2）计算上网分钟数：
上网分钟数实际上就等于上网天数乘以60再乘以24，所以应在单元格D3中输入以下公式：
“=C3*60*24”；3）计算计费时间：
本例中规定每30分钟计费一次，不满30分钟以30分钟计价，所以应在单元格E3中输入以下公式：
“=ROUNDUP（D3/30,0）”；4）计算上网费用：
在单元格G3中输入以下公式：
“=E3*F3”。
图14
12、SUBTOTAL函数：
使用该函数可以返回列表或者数据库中的分类汇总。
通常利用[数据]—[分类汇总]菜单项可以很容易地创建带有分类汇总的列表。
Function_num
函数返回值
Function_num
函数返回值
Function_num
函数返回值
1
Average
5
Min
9
Sum
2
Count
6
Product
10
Var
3
Counta
7
Stdev
11
warp
4
max
8
Stdevp
例如某班部分同学的考试成绩如图15，1）显示最低的语文成绩：
首先在单元格B9中输入“显示最低的语文成绩”的字样，然后在单元格E9中输入以下公式：
“=SUBTOTAL（5,C3:
C7）”；2）显示最高的数学成绩：
首先在单元格B10中输入“显示最高的数学成绩”的字样，然后在单元格E10中输入以下公式：
“=SUBTOTAL（4,D3
7）”。
图15
13、计算库存量和奖金：
假设某公司在月底要根据员工的业绩发放工资并进行产品的库存统计，本例中规定员工的基本工资为600元，奖金按照销售业绩的8%提成，总工资等于基本工资与奖金之和。
如图16，1）在工作表中输入相应的数据信息；2）计算“现存库量”：
在单元格C15中输入以下公式：
“=C14-SUM（C3:
C9）”；3）计算“销售业绩”：
在单元格G3中输入以下公式：
“=SUMPRODUCT（C3:
F3,$C$13
F$13）”,函数SUMPRODUCT是计算数组C3：
F3与数组$C$13
F$13乘积的和，用数学公式表示出来就是：
“=10*3050.5+10*1560.99+5*4489.9+20*2119”；4）计算奖金：
奖金是按照销售业绩的8%提成得到的，这样计算出来的结果可能会是小数，不好找零钱，所以这里采用向上舍入的方式得到整数，在单元格H3中输入以下公式：
“=ROUNDUP（G3*8%,0）”；5）计算总工资：
由于总工资=基本工资+奖金，所以在单元格J3中输入以下公式：
“=SUM（H3:
I3）”。
图16
14、计算工资和票面金额：
假设某公司的销售人员的销售情况如图17所示，按照销售业绩的5%计算销售提成，下面需要结合上例中的函数来计算销售人员的销售业绩以及奖金工资，然后再计算出发放工资时需要准备的票面数量。
1）计算销售业绩：
在单元格H13中输入以下公式：
“=SUMPRODUCT（C3:
G3,$C$11
G$11）”；2）计算提成：
在本例中假设提成后出现小于1元的金额则舍入为1，所以需要使用ROUNDUP函数，在单元格I3中输入以下公式：
“=ROUNDUP（H3*5%,0）”；3）计算工资：
在单元格K3中输入以下公式：
“=I3+J3”；4）计算100元的面值：
在单元格L3中输入以下公式：
“=INT（K3/$L$2）”；5）计算50元的面值：
在单元格M3中输入以下公式：
“=INT（MOD（K3,$L$2）/$M$2）”，此公式是使用MOD函数计算发放“MOD（K3,$L$2）”张100元后剩下的工资，然后利用取整函数INT得到50元票面的数量；6）计算10元的面值：
在单元格N3中输入以下公式：
“=INT（MOD（K3,$M$2）/$N$2）”；7）计算5元的面值：
在单元格O3中输入以下公式：
“=INT（MOD（K3,$N$2）/$O$2）”；8）计算1元的面值：
在单元格P3中输入以下公式：
“=INT（MOD（K3,$O$2）/$P$2）”。
图17
15、DATE函数：
在实际工作中经常会用到此函数来显示日期。
例如：
如图18，在单元格中输入相应的年、月和图书馆日等信息，然后在单元格E3中输入以下公式：
“=DATE（B3,C3,D3）”。
图18
16、DATEIF函数：
假设有两个已知日期——开始日期和截止日期，那么可以利用DATEIF函数来计算它们之间相差的年数、月数或者天数等。
如图19，在单元格D3中输入以下公式：
“=DATEDIF（B3,C3,"y"）”。
图19
17、DAYS360函数：
该函数计算两个日期之间的天数，在财务中经常会用到，如果财务系统是基于一年12个月并且每月30天，可以使用该函数帮助计算借款天数或者支付款项等。
例如：
某企业不同时间的贷款如图20所示，然后利用DAYS360函数来计算其借款的时间，并且计算出还款利息。
1）计算“借款天数”：
在单元格D3中输入以下公式：
“=DAYS360（B3,C3）”；2）计算“还款利息”：
在单元格G3中输入以下公式：
“=D3*E3*F”。
图20
18、WEEKDAY函数：
使用此函数可以返回某个日期为星期几。
语法：
WEEKDAY（serial_number,return_type）：
其中参数serial_number代表要查找的那一天的日期，参数return_type为确定返回值类型的数字，详细内容如下表：
参数值
函数返回值
1或者省略
返回数字1（星期日）到数字7（星期六）之间的数字。
2
返回数字1（星期一）到数字7（星期日）之间的数字。
3
返回数字0（星期一）到数字6（星期日）之间的数字。
例如：
计算当前日期是星期几：
如图21所示，在单元格B3中输入计算当前日期的公式：
“=WEEKDAY（B3,2）”。
图21
19、WEEKNUM函数：
使用此函数可以计算一年中的第几周。
例如：
已知2006年6月9日是星期五，下面利用WEEKNUM函数计算在参数不同的情况下返回的周数。
如图22所示，在单元格B3中输入计算当前日期的公式：
“=WEEKNUM（B3,C3）”。
图22
20、WORKDAY函数：
使用此函数可以返回某个日期（起始日期）之前或之后相隔指定工作日的某一日期的日期值，工作日不包括周末和专门指定的日期。
假设某出版社要求某个编辑从2006年3月1日起开始写稿，利用80天将其完成（其中不包括三天节假日），此时可以利用WORKDAY函数计算出完成日期。
如图23所示，在单元格中输入上述信息，然后在单元格C7中输入以下公式：
“=WORKDAY（C2,C3,C4:
C6）”。
图23
21、OR函数：
判断逻辑值并集的计算结果，在所有的参数中只要有一个逻辑值为TRUE，该函数的返回值即为TRUE。
例如已知某企业的员工姓名和出生年份两列值，如图27所示，然后根据输入的年份判断员工中是否有这一年出生的人，并且统计出共有几个。
1）在单元格D3中输入判断值“1975”，即判断是否有1975年出生的人，然后在单元格E3中输入以下公式：
“{=OR（D3=C3:
C8}”，在该公式中，表示将D2单元格中的值与数据区域“C3:
C8”中的每一个值作比较，判断是否相等。
如果任何一人比较结果为真，函数OR则返回TRUE，也就是D3单元格中的值位于这个列表中。
由于是在一个数组中查找是否存在某个指定的值，所以公式要以数组的形式输入，输入公式后要按[Ctrl]+[Shift]+[Enter]组合键完成；3）计算1975年出生的人数，在单元格E3中输入以下公式：
“{=SUM（IF（D3=C3:
C8,1,0）}”，在该公式中先使用IF函数将单元格D3中的值与数据区域“C3:
C8”中的每一个值进行比较，如果两个值相等则返回1，否则返回0。
然后利用SUM函数对所有的返回值求和，最后得到的数据就是“1975”出现的次数，即有几个人是1975年出生的。
该公式要以数组公式的形式输入。
图27
22、ADDRESS函数：
该函数使用方法如图28所示。
图28
23、AREAS函数：
该函数使用方法如图29所示。
图29
24、CHOOSE函数：
例如评定学生成绩，利用该函数可以评定销售人员的业务能力，还可以返回成绩的档次以及是否及格等，其计算方法都是一样的。
下面以学生成绩表为例看一下CHOOSE函数的应用方法。
1）首先在工作表中输入如图30所示的学生成绩，然后在单元格F3中输入以下公式：
“=SUM（C3:
E3）/3”，此时即可计算出学生的平均成绩；2）利用CHOOSE函数计算成绩名次，在G3单元格中输入以下公式：
“=CHOOSE（IF（F3>=90,1,IF（F3>=80,2,IF（F3>=70,3,IF（F3>=60,4,5））））,"优秀","良好","一般","及格","不及格"）”，在该公式中用到了多个IF函数，用以判断平均成绩属于哪个区间，再使用CHOOSE函数返回不同情况下的结果，这里把成绩分为了5个档次，即平均分90以上的是“优秀”、80到90之间的是“良好”、70到80之间的为“一般”、60到70之间的为“及格”、60以下的为“不及格”。
图30
25、COLUMN函数：
该函数使用方法如图31所示。
图31
26、COLUMNS函数：
该函数使用方法如图32所示。
图32
27、HLOOKUP函数：
在实际工作中此函数的应用非常广泛，下面举例说明。
在计算销售奖金时，不同的销售业绩对应不同的奖金比例，因此首先需要使用HLOOKUP函数查询奖金比例，然后再计算销售奖金。
1）输入如图33所示的业绩奖金以及员工的销售业绩；2）查找适当的奖金比例，在单元格D7中输入以下公式：
“=HLOOKUP（D3,$B$3
G$4,2）”；3）分别在单元格D8、D9、D10中输入以下公式：
“=HLOOKUP（E3,$B$3
G$4,2）”、“=HLOOKUP（F3,$B$3
G$4,2）”、“=HLOOKUP（G3,$B$3
G$4,2）”；3）计算奖金：
在单元格E7中输入以下公式：
“=C7*D7”。
图33
28、HYPERLINK函数：
该函数使用方法如图34所示。
图34
29、INDEX函数：
该函数返回指定单元格中的内容。
假设在图35所示的课程表中：
1）查找出星期三第4节课所上的课程：
只需在单元格C13中输入以下公式：
“=INDEX（C3:
H9,C12,C11）”；2）返回星期五的所有课程：
选中单元格区域“J2:
J9”，然后输入以下公式：
“{=INDEX（B2:
H9,,6）}”，此时即可显示出星期五的所有课程；3）计算路程：
已知各地之间相隔的距离如图36所示，那么如何计算A地和D地之间相隔的距离呢？
只需在单元格C11中输入以下公式：
“=INDEX（B2:
G7,MATCH（C9,B2:
B7,0）,MATCH（C10,B2:
G2,0））”。
图35
图36
30、INDIRECT函数：
该函数使用方法如图37所示。
图37
31、REPLACE函数：
此函数可以使用其他的文本字符串并根据所指定的字符数替换某个文本字符串中的部分。
例如某市的电话号码要升位，在原来的电话号码的前面加一个“8”，下面使用REPLACE函数完成已知电话号码的升位。
具体的操作步骤如下：
1）输入已知的电话号码，如图62所示；2）计算升位后的电话号码，在单元格C3中输入以下公式：
“=REPLACE（B3,1,4,"05328"）”，在该公式中，使用REPLACE函数用“0108”替换B3中字符串中第一位开始的前4位数字，结果相当于区号不变，在原电话号码的前面加一个“8”。
其中“05328”加引号是以文本的形式输入的，否则忽略0。
图62
32、REPT函数：
此函数可以按照给写的次数重复显示文本，也可以通过REPT函数不断地重复显示某一个文本字符串来对单元格进行填充。
该函数的用法见图63所示。
图63
33、RIGHT函数：
使用此函数可以根据所指定的字符数返回文本字符串中最后一个或者多个字符。
例如：
1）拆分姓名，在实际中人的姓名一般是由姓和名两部分组成的，下面介绍如何利用RIGHT函数将其拆分开，具体的操作步骤如下：
在单元格中输入一些姓名，如图64所示，然后在单元格C3中输入以下公式：
“=RIGHT（B3,2）”；2）判断性别：
假设有一个关于生活消费方面的调查，调查者为了书写方便也为了便于进行统计分析，在对被调查者编号时指定其最后一位表示性别，用“1”代表男性，用“2”代表女性，首先在工作表中输入已知信息，如图65所示，然后在单元格D3中输入以下公式：
“=IF（RIGHT（C3,1）="1","男","女"）”，在该公式中，使用RIGHT函数返回编号中的最后一个字符，再利用IF函数判断。
如果返回的结果为“1”则为“男”，反之为“女”，由于函数返回的是字符，所以“1”要加引号，当有多种情况时还可以使用嵌套的IF函数。
图64
图65
34、SEARCH函数：
此函数可以查找文本字符串。
该函数的用法见图66所示。
图66
35、T函数：
此函数可以返加引用的文本。
该函数的用法见图67所示。
图67
36、TEXT函数：
此函数用来将数值转换为指定格式。
该函数的用法见图68所示。
图68
37、TRIM函数：
此函数用来清除文本中的空格。
该函数的用法见图69所示。
图69
38、UPPER函数：
此函数用来将文本转换为大写。
该函数的用法见图70所示。
图70
39、处理人员信息：
文本函数在实际工作中也是一种常用的函数类型。
一些大型的企业为了提高员工的素质，使员工能及时地接触到该行业的最新科技信息，有关负责人会时常请一些专家对自己的员工进行培训。
下面介绍如何利用文本函数处理人员信息，具体的操作步骤如下：
1）在工作表中输入需要的标题项目以及人员编号、姓名和性别等数据信息，以便于在后面使用，如图71所示；2）从姓名中提取姓：
在单元格E3中输入以下公式：
“=IF（LEN（C3）=4,LEFT（C3,2）,LEFT（C3,1））”，由于中国人的姓名有两个字的，有3个字的，还有4个字符，4个字的名字一般是复姓，所以要使用IF函数判断姓名的长度是不是4，如果姓名的长度等于4，则使用LEFT函数返回左边的两个字符，否则返回左边的1个字符；3）从姓名中提取名：
在单元格E3中输入以下公式：
“=IF（LEN（C3）=2,RIGHT（C3,1）,RIGHT（C3,2））”，在该公式中使用IF函数判断姓名的长度是不是等于2，若等于2则利用RIGHT函数返回最右侧的1个字符，若不等于2则返回最右侧的两个字符；4）添加称呼：
在单元格G3中输入以下公式：
“=IF（D3="男",CONCATENATE（E3,"先生"）,CONCATENATE（E3,"女士"））”，在该公式中，首先使用IF函数判断性别是“男”还是“女”，如果是“男”则返回先生，如果是“女”则返回女士，然后利用CONCATENATE函数将判断结果和姓连接起来组成该专家的称呼；5）安排入住的宾馆房间号：
在单元格H3中输入以下公式：
“=IF（B3<=3,"滨海假日"&TEXT（B3,"300"）,"清泉宾馆"&TEXT（B3,"200"））”，在安排专家的宾馆房间时，假设前三
升级会员 