excel公式函数应用大全修改Word文档格式.docx
《excel公式函数应用大全修改Word文档格式.docx》由会员分享，可在线阅读，更多相关《excel公式函数应用大全修改Word文档格式.docx（23页珍藏版）》请在冰豆网上搜索。
此函数可以对所有的以参数形式给出的数字相乘，并返回乘积。
某企业2005年度贷款金额为100000元，利率为1.5%，贷款期限为12个月。
如图所示，直接在单元格E4中输入以下公式：
“=PRODUCT（B4,C4,D4）”。
8、RAND函数：
此函数可以返回大于等于0及小于1的均匀分布随机数，每次计算工作表时都将返回一个新的数值。
如果要使用函数RAND生成一个随机数，并且使之不随单元格的计算而改变，可以在编辑栏中输入“=RAND（）”，保持编辑状态，然后按[F9]键，将公式永久性地改为随机数。
在全班50名同学中以随机方式抽出20名进行调查，如图，在单元格中输入开始号码以及结束号码，然后在单元格B4中输入以下公式：
“=1+RAND（）*49”。
9、ROUND函数：
此函数为四舍五入函数。
如图12，例如：
将数字“12.3456”按照指定的位数进行四舍五入，可以在D3单元格中输入以下公式：
“=ROUND（B3,C3）”。
10、ROUNDDOWN函数：
此函数为向下舍入函数。
出租车的计费标准是：
起步价为5
元，前10公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收2元。
输入不同的公里数，如图所示，然后计算其费用。
可以在C3单元格中输入以下公式：
“=IF（B3<
=10,5+ROUNDDOWN（B3,0）*2,20+ROUNDDOWN（（B3-10）*2,0）*2）”。
11、DATE函数：
在实际工作中经常会用到此函数来显示日期。
如图，在单元格中输入相应的年、月和图书馆日等信息，然后在单元格E3中输入以下公式：
“=DATE（B3,C3,D3）”。
12、AND函数：
当所有参数的逻辑值为真时，AND函数的返回值为TRUE；
只要有一个参数的逻辑值为假，该函数的返回值则为FALSE。
假设有一组民意调查数据或者调查结果，如图所示，下面根据各个年龄段（18～34、35～49、50～64和65以上）对数据进行分类，以判断出各个年龄段的调查结果。
1）统计年龄在18～34岁之间的人的调查结果，在单元格E7中输入以下公式：
“=IF（AND（C7>
=18,C7<
=34）,D7,"
"
）”，在该公式中使用AND函数判断单元格C7中的值是否在18～34岁之间，然后根据返回的逻辑值再利用IF函数得到结果，即如果为真则返回单元格D7中的值，否则返回空值；
2）统计年龄在35～49岁之间的人的调查结果，在单元格F7中输入以下公式：
=35,C7<
=49）,D7,"
）”；
3）统计年龄在65岁以上的
人的调查结果，在单元格H7中输入以下公式：
=50,C7<
=64）,D7,"
）”。
13、OR函数：
判断逻辑值并集的计算结果，在所有的参数中只要有一个逻辑值为TRUE，该函数的返回值即为TRUE。
例如已知某企业的员工姓名和出生年份两列值，如图所示，然后根据输入的年份判断员工中是否有这一年出生的人，并且统计出共有几个。
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年出生的。
该公式要以数组公式的形式输入。
14、INDEX函数：
该函数返回指定单元格中的内容。
假设在图中所示的课程表中：
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））”。
15、MATCH函数：
在数组中查找数值的相应位置。
该函数使用方法如图所示。
16、ROWS函数：
该函数的应用方法如图所示。
17、计算所得税：
假设规定：
工资、薪金所得按月征收，对每月收入超过800元以上的部分征税，适用5%至45%的9级超额累进税率，即：
纳税所得额（计税工资）=每月工资（薪金）所得—800元（不计税部分）；
超额累进应纳税款=纳税所得额×
按全额累进所用税率—速算扣除数。
当工资为“5800”和“3000”元的时候，计算其应缴纳的所得税的金额，具体操作步骤如下：
1）如图所示，在单元格C15和C16中输入工资金额“5800”和“3000”，然后在单元格D15中输入“=IF（$C15<
=$F$2,0,（$C15-$F$2）*VLOOKUP（（$C15-$F$2）,$D$4:
$F$12,2,1）-
VLOOKUP（（$C15-$F$2）,$D$4:
$F$12,3,1））”，此时即可计算出缴纳的所得税；
2）在单元格E15中输入以下公式“=$C15-$D15”，此时即可计算出实发工资。
18、计算考核成绩：
在公司或者企业内部为了激励员工更加积极地工作经常会制定一些考核制度，下面以计算某公司员工第一季度的考核成绩为例，介绍一下部分查找函数的实际应用方法，具体的操作步骤如下：
1）新建一个工作薄，将其中的工作表Sheet1、Sheet2和Sheet3分别命名为“各季度缺勤记录”、“部长意见”和“第一季度考核表”，然后在前两个工作表中输入所需要的数据信息，如图45、图46所示；
2）在工作表“第一季度考核表”中输入员工编号、员工姓名以及相关的标题项目，如图47所示；
3）计算“缺勤记录”：
在单元格D3中输入以下公式：
“=INDEX（各季度缺勤记录!
D2:
$G$9,2,1）”；
4）计算“出勤成绩”：
在单元格E3中输入以下公式：
“=IF（D3<
30,30-D3,0）”，即如果缺勤30天以上出勤成绩就是0分；
5）计算“工作能力”：
在单元格F3中输入以下公式：
“=INDEX（部长意见!
D3:
E9,1,1）”；
6）计算“工作态度”：
在单元格G3中输入以下公式：
“=VLOOKUP（B3,部长意见!
$B$3:
$E$9,4）”；
7）计算“季度考核成绩”：
在单元格H3中输入以下公式：
“=SUM（E3:
G3）”，即出勤成绩、工作能力及工作态度之和。
19、CONCATENATE函数：
此函数用来合并字符串。
该函数的用法见图所示。
20、FIND函数：
此函数用来查找文本串。
21、FIXED函数：
此函数对数字进行格式化。
22、LEN函数：
此函数用来查找文本的长度。
23、LOW函数：
此函数用来将文本转换为小写。
24、MID函数：
此函数可以返回文本字符串中从指定位置开始的特定字符。
该数目由用户指定。
1）如图所示：
从身份证号码中提取生日：
在网上注册一些表格时经常需要填写身份证号码，填写完毕系统就会自动地生成出生日期，这里以某公司员工为例，根据其身份证号码提取出生年月日。
首先在工作表中输入员工的姓名和身份证号码等数据信息，如图所示，然后在单元格D3中输入以下公式：
“=MID（C3,7,8）”，在该公式中，利用MID函数返回身份证号码中从第7位字符开始的共8个字符，即该员工的出生日期，众所周知，身份证前6位代表
的是省份、市、县编号，然后从第7位开始是出生年月日，共8位，后面的数字代表其他的意义；
2）拆分电话号码：
工作表中输入已知的电话号码，如图所示，然后在单元格C3中输入以下公式：
“=MID（B3,5,7）”，此时即可获得电话。
25、REPLACE函数：
此函数可以使用其他的文本字符串并根据所指定的字符数替换某个文本字符串中的部分。
例如某市的电话号码要升位，在原来的电话号码的前面加一个“8”，下面使用REPLACE函数完成已知电话号码的升位。
具体的操作步骤如下：
1）输入已知的电话号码，如图所示；
2）计算升位后的电话号码，在单元格C3中输入以下公式：
“=REPLACE（B3,1,4,"
05328"
）”，在该公式中，使用REPLACE函数用“0108”替换B3中字符
串中第一位开始的前4位数字，结果相当于区号不变，在原电话号码的前面加一个“8”。
其中“05328”加引号是以文本的形式输入的，否则忽略0。
26、SEARCH函数：
此函数可以查找文本字符串。
27、TEXT函数：
此函数用来将数值转换为指定格式。
28、DAVERAGE函数：
此函数可以返回列表或者数据库中满足指定条件的列中数值的平均值。
1）在单元格中输入需要处理的问题，如计算“语文大于59分的平均成绩”和“英语的平均成绩”，如图所示；
2）在单元格C12中输入以下公式：
“=DAVERAGE（B2:
E8,C10,C10:
C11）”；
3）在单元格C13中输入以下公式：
E8,4,E2:
E8）”。
29、DCOUNT函数：
使用此函数可以返回数据库或者列表中满足指定条件并且包含数字的单元格个数。
1）如图78所示，首先在单元格中输入需要处理的问题，然后在单元格C12中输入以下公式：
“=DCOUNT（B2:
E8,B10,B10:
B11）”，即可得到数学成绩及格的单元格个数；
2）在单元格C13中输入以下公式：
E8,2,B10:
B11）”，即可得到语文成绩大于70并且数学成绩及格的单元格个数。
30、DGET函数：
使用此函数可以从列表或者数据库的列中提取符合指定条件的单个值。
如图所示，在单元格C12中输入以下公式：
“=DGET（B2:
E8,1,D10:
D11）”，即可查找出英语成绩大于89分的同学的姓名；
在单元格C13中输入以下公式：
E8,1,B10:
C11）”，即可
查找出语文和数学成绩全部大于80分的同学的姓名。
31、DMAX函数：
此函数用以返回指定条件的最大数值。
首先在单元格中输入需要处理的问题，如图所示，然后分别在单元格C12和C13中输入以下公式：
“=DMAX（B2:
D11）”、“=DMAX（B2:
E8,D10,B10:
D11）”。
DMIN函数的使用方法与
DMAX函数相似，不过此函数用以返回指定条件的最小数值。
32、DSUM函数：
此函数用以返回指定条件的数字之和。
首先在单元格中输入需要处理的问题，如图所示，然后在单元格C12和C13中输入以下公式：
“=DSUM（B1:
F6,1,C8:
C9）”、
F6,5,C8:
D9）”。
33、区分函数COUNT和COUNTA：
1）制作1月出勤加班统计表，表中包括员工1月出勤加班统计表以及需要统计的内容，如图所示；
2）使用COUNT函数统计各列单元格的个数，在单元格B13中输入以下公式：
“=COUNT（B3:
B11）”，此时可以看到包含文字的单元格和空白单元格被忽略了，只统计包含数字的单元格；
3）使用COUNTA函数统计各列单元格的个数，在单元格B14中输入以下公式：
“=COUNTA（B3:
B11）”，此时可以看到包含文字的单元格也统计在
内了。
34、区分函数MAX和MAXA：
现以某公司1月份的出勤和加班记录表为例，来看一下MAX和MAXA函数在实际工作中的具体的应用。
在此表中分别计算迟到、旷工和加班次数最多的员工的姓名，具体的操作步骤如下：
1）制作如图所示的表格；
2）计算迟到次数最多的员工的姓
名，在单元格J5中输入以下公式：
C11,MATCH（MAX（E3:
E11）,E3:
E11,0）,1）”,即
可得到迟到次数最多的员工的姓名即“李宁”；
3）计算旷工次数最多的员工的姓名，在单元格J7中输入以下公式：
C11,MATCH（MAXA（F3:
F11）,F3:
F11,0）,1）”，即可得到旷
工次数最多的员工的姓名即“邵刚”。
35、MODE函数：
该函数用以返回出现频率最高的数值。
假设已知某些同学的语文、数学和英语成绩如图所示，现计算各科成绩中出现次数最多的分数，在单元格C14中输入以下公式：
“=MODE（C4:
C13）”。
36、PERCENTRANK函数：
该函数用以返回百分比排位。
、
37、RANK函数：
该函数用以排名次。
92、FV函数：
此函数用来计算投资未来值。
1）计算本利和：
例如某公司为某项投资存款，银
行已有的存款是50000元，以后每年存款20000元，年利率是8%，试计算10年后的本息和为
多少？
如果每月存入2000元，那么10年后的本利和又是多少？
制作如图所示的表格，计算按存款时10年后的本金和利息之和，在单元格D5中输入以下公式：
“=FV（C3,D3,E3,B3,0）”；
计算按月存款时10年后的本金和利息之和，在单元格D6中输入以下公式：
“=FV（C3/12,D3*12,F3,B3,0）”，在该公式中“C3/12”表示月利率，“D3*12”表示总的月份数，每月的存款和先期的存款都是负值，这样计算出来的结果就是正值，“0”表示每次月末存款；
2）计算累计金额：
例如如果年息为6%，那么5年之后2000元的累计金额是多少？
将已知数据和相关项目输入到工作表中建立数据模型，如图所示，在单元格B6中输入以下公式：
“=FV（B3,B4,0,B5,0）”；
3）计算到期还款额：
例如用户向银行借款5000元，期限6年，利息6%，请问到期还款额为多少？
4）计算帐户总额：
如何期初余额为5000元，每月的月末存入
600元，如果月息为0.75%，请问三年后此帐户中的总额是多少钱？
将已知数据和相关项目输入到工作表中建立数据模型，如图所示，在单元格B7中输入以下公式：
“=FV（B3,B4,B5,B6,0）”，即可求出三年后的账户总额。
93、FVSCHEDULE函数：
此函数用来计算本金未来值。
例如某人存入银行150000元，一年内年利率在不断地变化，请计算一年后的存款额。
1）制作如图所示的表格，其中包括一年内不同的年利率以及由此得出的月利率；
2）计算在各种利率条件下一年后的总存款数，在单元格D15中输入以下公式：
“=FVSCHEDULE（C2,F3:
F14）”，即计算不同的利
率条件下150000元的存款1年后的存款额是多少。
94、IPMT函数：
此函数用来返回利息偿还额。
例如计算贷款利息，假设有一位消费者为买房而向银行贷款200000元，贷款期限为10年，年息为4.5%，按月偿还，请计算付款中的利息，具体的操作步骤如下：
1）制作如图所示的表格，其中包括贷款金额、贷款时间和年利息；
2）计算第一个月应付的利息：
在单元格D5中输入以下公式：
“=IPMT（D3/12,1,C3*12,B3）”；
3）计算最后一个月应付的利息：
在单元格D6中输入以下公式：
“=IPMT（D3/12,120,C3*12,B3）”。
95、NPER函数：
该函数用来返回投资总期数。
该函数的用法如图所示。
96、PMT函数：
该函数用来返回每期付款额。
1）计算偿还额：
某公司从银行贷款200000元，分6年偿还，年利率为8%，现需计算按年偿还和按月偿还的还款额，条件为等额偿还，具体的操作步骤如下：
制作如图所示的表格，计算按年还款时的年初偿还额，在单元格E4中输入以下公式：
“=PMT（C7,C5,C3,0,1）”；
计算年末偿还额，在单元格F4中输入以下公式：
“=PMT（C7,C5,C3）”；
计算按月偿还时的月初偿还额，在单元格E8中输入以下公式：
“=PMT（C7/12,C5*12,C3,0,1）”，在该公式中“C7/12”表示月利率，“C5*12”表示还款总时间，因为是按月计算，所以是6*12=72个月，“1”仍表示是月初还款；
计算按月还款时的月末偿还额，在单元格F8中输入以下公式：
“=PMT（C7/12,C5*12,C3）”；
2）计算存款金额：
例如如果某公司需要为某个项目准备资金，该项目在两年后预计需要100000元，假设银行的存款年利率为10%，那么从现在起公司每月或者每年应当存入的金额是多少？
制作如图所示的表格，计算按年存款时的年初存款额，在单元格E4中输入以下公式：
“=PMT（C7,C5,0,C3,1）”；
计算按年存款时的年末存款额，在单元格F4中输入以下公式：
“=PMT（C7,C5,0,C3）”；
计算按月存款时的月初存款额，在单元格E8中输入以下公式：
“=PMT（C7/12,C5*12,0,C3,1）”，在该公式中使用PMT函数计算按月存款时的月初存款额，公式中各项参数的意义依次为：
“C7/12”为月利率，“C5*12”为存款总期数，“0”为本金，“100000”为未来值，“1”表示存款方式是期初；
计算按月末存款额，在单元格F8中输入以下公式：
“=PMT（C7/12,C5*12,0,C3）”；
3）计算利润租金：
假设一房产的租赁利益当前可以以230000元出售，租期4年，预付每月的租金6000元，不得重新定价或者涨价，如果接受了0.75%的收益，请问可以得到多少利润租金？
制作如图所示的表格，计算利润租金，在单元格B7中输入以下公式：
“=PMT（B6,B4*12,-230000,0,1）+B5”；
4）计算贷款偿还额：
假设有一笔期限为15年，月利息为0.65%的30000元的贷款，请计算月偿还额为多少？
制作如图所示的表格，计算月偿还额，在单元格B6中输入以下公式：
“=PMT（B4,B5*12,-B3,0,0）”。
98、PV函数：
该函数用来返回投资现值。
1）计算贷款额：
某公司想贷款进行投资，其
能承受的能力为每月支付10000元，以年利息5%进行10年贷款，请计算该公司能承受的最多贷款额是多少？
制作如图所示的表格，在单元格E3中输入以下公式：
“=PV（C3/12,D3*12,-B3,0,0）”，即按月计算付款数；
2）计算投资值：
现有一项保险年金，购买该保险后可以在今后25年内每月末领回1000元，假设购买时需先缴付15000元，投资回报率为4.5%，请计算这笔投资是否值得。
“=PV（C3/12,D3*12,-B3,0,0）”，即可得到需要投资的现值。
从上面的计算的结果中可以看出，计算出来的投资现值“179910.32”大于预交付的购买保险的费用“150000”，所以购买此保险还是值得的。
99、RATE函数：
该函数返回年金的各期利率。
该函数的用法如图107所示。
升级会员 