EXCEL函数运用

excel xp函数运用实例四则 excel xp函数运用实例四则。

实例一：身份证中信息的提取。

1.持证人性别的判断：我们知道，旧身份证(15位)号码的最后1位数值，表示持证人的性别，若为奇数则为男，若为偶数则为女。

我们利用excel函数，对身份证号码进行适当处理，即可自动判断持证人的性别，并将结果填入相应的单元格中(此处假定身份证号码保存在b2单元格中，结果填入c2单元格中)。

（1）利用函数的嵌套来判断。在c2单元格中输入公式：=if(right(b2，1)="1"，"男"， if(right(b2，1)="3"，"男"，

if(right(b2，1)="5"，"男"， if(right(b2，1)="7"，"男"，

if(right(b2，1)="9"，"男"，"女"))

（2）综合运用函数来判断。在c2单元格中输入公式：=if(or(right(b2，1)="1"，right(b2，1)="3"，right(b2，1)="5"，right(b2，1)="7"，right(b2，1)="9")，男"，"女")。

在上面两个公式中，前一个是利用if函数的嵌套来实现的，由于函数最多只能嵌套7层，因而有一定的局限性，且比较麻烦；后一个公式由于采用了or函数，既没有层数的限制，也简单一些。

（3）运用其他函数来判断。我们在c2单元格中输入公式“=if(right(b2，1)/2=int(right(b2，1)/2)，"女"，"男")”或“=if(mod(right(b2，1)，2)=0，"女"，"男")”即可实现。

2.持证人出生时间的提取：同样，旧身份证号码中，第7—12位数字代表的是持证人的出生年月日，我们只要在d2(假定将出生时间存放在d2单元格中)单元格中输入公式：

=19&mid(b2，7，2)&"年"&mid(b2，9，2)&"月"&mid(b2，11，2)&"日"，即可从b2单元格的身份证号码中将出生日期自动提取出来，并以中文习惯形式显示出来(如“2023年03月04日”等)。

小技巧：执行“视图→工具栏→公式审核”命令，打开“公式审核”工具条(图1)，按最右边的“公式求值”按钮，在随后弹出的“公式求值”对话框中，反复按“求值”按钮，即可逐项核查运算的结果。

注释：上述公式中所用的函数：①if──逻辑函数：

判断一个条件是否满足，如果满足返回一个值，如果不满足返回另一个值。②or──逻辑函数：如果任一参数值为true，则返回true；只有当所有参数值均为false，才返回false。

③right──文本函数：从一个字符串的最后一个字符开始返回指定个数的字符(也可以使用rightb函数)。④mid──文本函数：

从文本字符串中指定的起始位置起返回指定长度的字符。⑤mod──数学函数：返回两数相除的余数。

⑥int──数学函数：将数值向下取整为最接近的整数。

实例二：批量插入固定字符。

大家知道新的身份证号码(18位)将旧身份证号码的年份由2位改为4位。现在，我们要将年份的前两位(19)插入旧身份证号码中，如果一个一个地去插入，显然既麻烦又容易出错，如果利用excel的函数来做，则既方便又准确。

此处假定旧身份号码保存在b列中，插入“19”后的号码暂时保存在c列中。我们在c2单元格中输入公式：=left(b2，6)&19&right(b2，9)。

再次选中c2，将鼠标移到右下角成“细十字”状(我们称之为“填充柄”)，按住左键向下拖拉，即可将上述公式复制到c列的以下单元格中(在复制过程中，系统会智能化地改变相应的单元格)。

小技巧：选中c列，按“复制”按钮，再选中b列，执行“编辑→选择性粘贴”命令，打开“选择性粘贴”对话框，选中“粘贴”下面的“数值”选项，然后按“确定”按钮，再将c列删除，就可以将c列的值正确地复制到b列中，从而不影响原有**的结构。

注释：上述公式中用到一个新函数：left──文本函数：从一个字符串的第一个字符开始返回指定个数的字符(也可以使用leftb函数)。

实例三：学生成绩的统计。

图2是一张教师(特别是班主任)非常熟悉的“学生成绩统计表”，以前在统计各项数据时，大家通常采用的是笔算或按计算器的办法来进行的。现在可以用excel来帮我们快速、准确地完成这些繁杂的统计工作。

1.总分的统计：选中h3单元格(用于存放学生丁1总分的单元格)，输入公式：

=sum(c3:g3)，按下enter键后，丁1的总分即计算出来，并填入h3单元格中。用填充柄将该公式复制到h4—h47单元格中(假定该班级有45名同学)，将其他同学的总分统计出来。

用类似的方法，可以将某一学科的总分统计出来，并填入第48行相应的单元格中。

2.平均分的计算：选中c49单元格，输入公式：=**erage(c3:c47)，按下enter键后，语文学科的平均分即计算出来。

3.最高(低)分的统计：选中c50单元格，输入公式=max(c3:

c47)，挑出语文学科的最高分；选中c51单元格，输入公式：=min(c3:c47)，挑出语文学科最低分。

4.各分数段学生人数的统计：分别选中c52和c57单元格，输入公式：

=countif(c3:c47，">90")和=countif(c3:c47，"<50")，就统计出了语文学科大于等于90分和低于50分的学生人数；分别选中c53、c54、c单元格，依次输入公式=countif(c3:

c47，">80")-countif(c3:c47，">90")、countif(c3:c47，">70")-countif(c3:

c47，">80")、countif(c3:c47，">60")-countif(c3:c47，">70")、countif(c3:

c47，">50")-countif(c3:c47，">60")，即可统计出语文学科其他各分数段的学生人数。

5.名次的排定：选中i3单元格，输入公式：

=rank(h3，$h$3:$h$47)，按下enter键后，丁1同学总分的名次即排定，并填入i3单元格中。用填充柄就可以将i3单元格中的公式复制到i4—i47单元格中，用于排定其他学生的名次。

对公式中引用的参数做适当的修改，还可以排出每位同学各个学科成绩的名次。

小技巧：①图2的**制作好了以后，将“学号、姓名、各科成绩”等内容清空，再执行“文件→另存为”命令，打开“另存为”对话框，将“文件类型”选定为“模板”，并给定一个文件名，将该工作簿(成绩统计表)保存为模板，方便以后的使用。②如果要增加学生或增加学科，只要插入空行(列)就行了，完全不必更改公式(系统会智能化地对公式进行调整)。

③对于没有成绩的学生，其对应的单元格一定要为空，千万不要填入数字“0”，以免造成统计出错。

注释：上述公式中用到几个新的函数：sum──数学函数：

返回单元格区域中所有数值的和。**erage──统计函数：计算参数的算术平均数。

max(min)──统计函数：返回一组数值中的最大(小)值，忽略逻辑值和文本字符。countif──统计函数：

计算某个区域中满足给定条件单元格的数目。

实例四：多条件统计──数组公式的使用。

图3是一张普通的工资表，现在我们要统计职称为“高工”、性别为“男”的职工基础工资之和(数据位于a1f102区域中)。用一般的函数公式难以达到这一目的，我们可用数组公式来实现。

选中e103单元格(用于保存统计结果的单元格)，先输入公式：=sum(if(c3:c102="男"，if(d3:

d102="高工"，e3:e102)))然后在按住“ctrl+shift”组合键(非常关键！！！的同时按下enter键即可。

小技巧：如果你不想让别人改动你所输入的公式，可以这样操作：选中所有无公式的单元格(按住ctrl键，可以用鼠标选中不连续的单元格)，执行“格式→单元格”命令，打开“单元格格式”设置对话框，点击“保护”标签，将“锁定”选项前面的“∨”去掉，确定返回。

再执行“工具→保护→保护工作表”命令，打开“保护工作表”对话框，两次输入密码后，确定返回即可。

特别提醒：实际上，上面我们输入的是一个数组公式，两端各有一个数组公式标志“{}公式变成了：，这个数组公式标志“{}是不能直接用键盘输入的！

3．逻辑函数if

语法：if（logical_test，value_if_true，value_if_false）。

参数：logical_test是结果为true（真）或false（假）的数值或表达式；value_if_true是logical_test为true时函数的返回值，如果logical_test为ture并且省略了value_if_true，则返回true。而且value_if_true可以是一个表达式；value_if_false是logical_test为false时函数的返回值。

如果logical_test为false并且省略value_if_false，则返回false。value_if_false也可以是一个表达式。

应用实例一：个人收入调节税计算。

假设个人收入调节税的稽征办法是：工资在1000元以下的免征调节税，工资1000元以上至1500元的超过部分按5%的税率征收，1500元以上至2000元的超过部分按8%的税率征收，高于2000元的超过部分按30%的税率征收。

假如工作表的b列存放职工姓名，c列存放工资，选中d列的空白单元格输入公式“=if（c2<=1000，″″if（（c2-1000）<=1500，（c2-1000）*0.05，if（c2-e2<=1500，（c2-1500）*0.08，if（c2>2000，（c2-2000）*0.

3）））回车后即可计算出c2工资应缴纳的收入调节税。

公式中的if语句是逐次计算的，如果第一个逻辑判断c2<=1000成立，则公式所在单元格被填入空格；如果第一个逻辑判断式不成立，则计算第二个if语句；直至计算结束。如果税率征收标准发生了变化，只须改变逻辑和计算式中的值，如和2000等即可。

EXCEL函数运用

Excel函数运用

excel函数运用

Excel中数组函数的运用 excel函数

其他用户还读了