EXCEL函数巧应用

1、    excel函数巧应用    刘晓路摘要：随着办公自动化的普及，excel软件以其方便、强大的制表功能被广泛使用，其函数功能强大，灵巧应用将使繁琐的工作变得轻松，进一步的提高工作效率。本文通过制作员工情况表的实例，展示excel函数的使用技巧和操作技能。abstract： with the popularity of office automation， excel software has been widely used for its convenience and powerful tabulation function. and its

2、function is powerful. the smart applications of it will make the tedious work become easier， and can further improve the efficiency. through the production example of employee table， this paper demonstrates the use function and operation skills of excel.关键词：excel；函数；选择性粘贴key words： excel；function；pa

3、ste special：tp31 ：a ：1006-4311（2017）13-0237-020 引言传统的办公方式使人们耗费了大量的时间和精力去手工处理那些繁杂、重复的工作，手工处理的延时和差错，正是现代化工作中应该去除的弊端。随着办公自动化的发展和普及，它为我们带来了积极变化，通过办公自动化软件的运用，可节约大量时间成本，提高工作效率。下面就一例实际办公中制作员工情况表之事例，综合研究excel函数的几点使用技巧，方便、快捷的处理好这些繁多复杂的数据，起到事半功倍的作用。（注：本文以excel2003版本为例）函数作为excel处理数据的最重要手段，分为9类，其中本文涉及的函数有逻辑类函数i

4、f，文本类函数mid、substitute、text，数学与三角类函数mod，日期与时间类函数year、now、today、datedif等。打開excel，制作职工情况表，并填入基本信息：姓名、身份证号、参加工作时间。余下的项目：性别、年龄、出生日期、工龄则不需手工逐一填写，通过excel自带的强大函数功能，我们可以轻松且快速地将其提取出来。1 从身份证号中提取性别、出生日期、年龄已知“b”列为身份证号码（身份证号为18位新一代身份证），“c”列为性别，“d”列为出生日期，“e”列为年龄（见图1）。1.1 判断性别“男”或“女”将光标指针放到“性别”列单元格（c2）内，输入公式“=if（mo

5、d（mid（b2，17，1），2）=0，"女"，"男"）”函数意义：mid函数是从指定的起始位置起返回指定长度的字符，即从b2（身份证）单元格中取出第17位起1位长度的字符；mod为取余函数，可以用来判断取出的值为奇偶数；if是逻辑判断函数，如果条件满足，即如是偶数，余数为0，返回一个值“女”，否则返回另一个值“男”。当按下回车后，就会发现“男”或“女”的性别信息已在c2单元格出现了。拖动向下填充柄，其它单元格内就会出现相应的信息。1.2 提取“出生日期”将光标指针放到“出生日期”列单元格（d2）内，输入公式“=mid（b2，7，4）&"

6、;年"&mid（b2，11，2）&"月"&mid（b2，13，2）&"日"”函数意义：用mid函数从b2（身份证）单元格中提取第7位起4位长度的字符、第11位起2位长度的字符、第13位起2位长度的字符，即出生日期；&为文本连接符，是将两个文本值连接或串起来产生一个连续的文本值，即在出生日期中插入“年、月、日”，形成“某年某月某日”的形式。回车后，该病人的出生日期出现在d2单元格内，选中该单元格后拖动填充柄，相应的出生日期便一目了然。1.3 推算“年龄”将光标指针放到“年龄”列单元格（e2）内，输入公式“=

7、year（now（）-（mid（b2，7，4）”函数意义：year与now是日期与时间函数，前者是返回日期的年份值，后者返回当前的日期与时间格式，即为2017；减去mid函数提取的b2（身份证）单元格中第7位起4位长度的字符，即为病人真实的年龄。回车并下拉填充柄，全部病人的年龄信息轻松显现。这样，通过函数功能，我们就可以在excel表中快速提取需要的数据到相应的单元格中，极大地减轻了工作量。2 利用“参加工作时间”自动计算“工龄”已知“f”列为“参加工作时间”“g”列为工龄，“h”列为空白列（见图1）。2.1 修改“参加工作时间”格式原表中“参加工作时间”以数字格式输入，既不符规范，也不利于下

8、一步的“工龄”计算，需将它改为“×年×月”格式。将光标指针放在空白列处（h2）内输入公式“=substitute（h2，"."，"年"）&"月"”函数意义：substitute表示将字符串中的部分字符串用新字符串代替。回车并下拉填充柄，以“×年×月”形式表示的“参加工作时间”在h 列中全部显示。 将此格式复制入g列（即原“参加工作时间”列）。如果直接使用的“剪切”与“粘贴”，会因引用“计算公式”的原故，出现错误。这里要用到excel的“选择性粘贴”功能。选定h列中的内容，右击鼠标，选择“

9、复制”，将光标移至g列，选定需覆盖的单元格，点击“选择性粘贴”打开小窗口，选定“值和数字格式”选项，点击“确定”后，“参加工作时间”格式修改完成。最后将h列删除（见图2）。2.2 计算工龄将光标指标放到“工龄”列单元格（g2），輸入公式“=text（datedif（f2，today（），"y"），"0年；"）&text（datedif（f2，today（），"ym"），"0个月；"）函数意义：today：返回今天的日期，不需要参数；datedif：datedif（start_date，end_date，un

10、it）start_date 为一个日期，它代表时间段内的第一个日期或起始日期。end_date 为一个日期，它代表时间段内的最后一个日期或结束日期。unit 为所需信息的返回类型。（注：结束日期必须大于起始日期）"y" 时间段中的整年数。"ym" start_date 与end_date 日期中月数的差。忽略日期中的年。text：人称它是万能函数，其宗旨就是将计算结果设置规定的格式。三个分号的作用是去除0值，否则直接计算结果&“年”，可能会出现0年2个月。回车并下拉填充柄，工龄计算自动完成。3 结论excel是制作表格的常用软件，通过对其函数功能的不断深入了解与熟悉运用，将办公人员从繁琐、无序、低端的工作中解放出来，从而提高办公