使用Excel函数功能提升办公效率的相关策略

2018-01-08安徽师范大学文学院陈蕴智

办公室业务 2017年23期

文/安徽师范大学文学院陈蕴智

文/安徽师范大学文学院陈蕴智

Excel是微软公司推出的办公软件中的重要组成部分，其强大的函数功能可以提供数据分析、数据处理等操作，为许多领域的工作提供了十分便捷的数据统计和运算支持。使用IF、INDEX、COLUMN、ROW、MOD等函数配合可以快速制作工资条，不仅方法快捷而且制作出的工资条简洁明了、便于发放。利用Excel中的函数，我们还可以提取出身份证号码中所包含的每一个身份证号码持有人的基本个人信息，比如性别、出生年月日和籍贯等。

Excel；函数功能；办公效率

Excel函数具有强大的数据分析和数据处理功能，其中利用Excel函数可以高效地进行工资管理和提取身份证号码个人信息，这对于办公室工作人员来说，是一项必备的技能。

一、利用Excel函数进行工资管理

在企业中，如何准确、快速地统计出每个月的员工工资是一项繁重的任务，如果采用手动统计的话会造成大量的时间浪费，所统计出的数据往往也容易出错。对于大型企业而言，他们一般会购买专业的财务软件来进行工资统计等工作，对于一些中小企业而言，对员工工资进行统计、分析时，使用Excel函数不仅可以减少公司运营成本，同样能有效地完成工作、提高工作效率。

（一）设计工资表样式。在Excel中，把工资表中所需要的项目输入工作表中。我们首先设计出一个简单的工作表，在工作表中依次将编号、月份、姓名、所属部门、基本工资、奖金、应发工资、个人所得税、实发工资等项目输入表格中。

（二）计算补贴、应发工资、所得税、实发工资。1.使用IF函数计算奖金。IF函数可以依据指定条件的逻辑判断计算真假值，输出不同的结果。语法格式为：IF（Logical_test，Value_ if_true，Value_if_false）。输入Logical_test的目的是表示逻辑判断的表达式；输入Value_if_true的目的为表示当判断条件为逻辑“真（true）”时返回的结果，输入Value_if_false的目的为表示当判断条件为逻辑“假（false）”时返回的结果。另外，IF函数可以嵌套使用，使用时最多可嵌套七层。比如，我们简单设定每个部门奖金的发放金额为：人事部每人200元，业务部每人300元，行政部每人500元，财务部每人600元，只需要在F3单元格中输入公式：“=IF(D3=“人事部”200，IF（D3=“业务部”300，IF（D3=“行政部”500，600）”，然后将公式填充到F3：F8单元格区域，这样每名员工需要发多少奖金就会自动生成。如果公司是按照工龄或者职称等标准来计算奖金，也可以用这种方法来进行数据运算。2.使用SUM函数计算应发工资。SUM函数的功能是计算参数数值的和。做法是：在G3单元格输入公式“=SUM（E3，F3）”，可以获得G3单元格“金城武”的应发工资数额，然后填充公式至G4：G8单元格区域即可。3.假设应发工资额不超过1000元的员工工资需要按照5%的税率征收个人所得税，应发工资超过1000元时，超出的部分按照10%的税率征收。做法是：在H3单元输入公式“=IF（G3<=1000，G3*0.05，（G3-000）*0.1+1000*0.05）”，可以获得H3单元格“金城武”的个人所得税数额，然后填充公式至H 4：H 8单元格区域即可。4.在I 3单元格输入公式：“=SUM（G3，H3）”可计算出“金城武”的实发工资数额，然后填充公式至I4：I8单元格区域，即可计算出所有员工的实发工资数额，如“图1”所示。

图1

（三）制作工资条。根据工资表制作工资条是办公室或财务部等部门的日常工作之一。工资条作为员工工资项目的清单，其数据来源于已经制作完成后的员工工资表。一般工资条由三行单元格构成，第一行是工资项目，第二行是对应的工资数据，第三行留出空行以便于打印后裁剪。制作工资条需要四个函数的结合，下面按顺序说明具体做法：1.ROW函数。ROW函数代表返回所选择的某一个单元格的行数。ROW函数的语法格式为=ROW（reference）如果省略reference，则默认返回ROW函数所在单元格的行数。2.COLUMN函数。COLUMN函数可以返回引用的列标号，COLUMN函数的语法格式为=COLUMN（reference），Reference的意思是需要得到其列标的单元格或单元格区域。如果省略Reference，则默认返回为COLUMN函数所在单元格的引用。3.INDEX函数。INDEX函数的功能是返回区域或者表格中的值或对值的引用。连续区域中INDEX函数的公式格式是=INDEX（array，ROW_ num，COLUMN_num），其中array的功能是表示我们要引用的区域，ROW_num的功能是表示要引用的行数，COLUMN_num的功能是表示要引用的列数，最终的结果就是引用出区域内行列交叉处的内容。4.MOD函数。MOD函数是一个求余函数，其格式为：=MOD（number，divisor），即是两个数值表达式做除法运算后的余数。其中number是被除数，divisor则是除数。通过以上函数和IF函数的结合，就可以通过Excel制作出工资条。第一步，打开如“图2”所示的工资数据的Excel文件，这个表格存放了工资的原始数据，共有I列。将sheet1重命名为“工资表”，将sheet2重命名为“工资条”，下面我们在“工资条”的工作表中设计工资条样式。第二步，在“工资条”的A1单元格中输入公式：“=IF（MOD，ROW，3）=0”“IF（MOD，ROW，3）=1”工资表A$1，INDEX（工资表$A：$I，（ROW+ 4）/3，COLUMN）。这个函数公式使用两层判断式：如果行号/3=0时，也就是当行号是3的倍数时，返回空值；否则就是下一层结果：当行号/3=1，也就是当行号是3的倍数加1时，返回《工资表》工作表的A$2数据；否则就返回“工资表”工作表的$A：$I区域的第“（ROW+4/3）”行与第“COLUMN”列交叉的数据。第三步，确认后选择A1单元格，拖动A1单元格右下角的“+”，将公式横向填充至I1单元格，这样工资条中项目行就全部出来了。选定A3：I3，拖动I1单元格右下角的“+”，将公式竖向填充至最后一行，到此工资条的制作就全部完成了。如“图2”所示。这样就可以方便地将每个员工的工资条进行裁剪和发放了。

图2

以上通过一个简单的例子介绍了如何使用Excel函数制作企业工资表和工资条，除此之外，我们还可以使用邮件合并、VBA编程等方法来完成工资表和工资条的制作。根据现实情况进行合理选择和修改，就可以更加自如地面对以后工作中出现的更复杂的工资项目和计算过程，满足不同性质企业的工资管理需要。

二、利用身份证号码提取个人信息

Excel函数除了可以为企业在工资管理中提供便捷的操作方法，也可以为学校在收集学生信息时提供帮助，例如根据学生的身份证号码自动提取出生年月日、籍贯或自动判断性别。以现在使用的第二代的18位身份证为例，身份证号码的第1到第2位表示所在的省份（直辖市或自治区），第3到第4位表示所在的地级市（自治州或盟），第5到第6位表示所在的县（县级市、区），第7到第14位表示出生年月日，第15到第17位表示顺序号，其中第17位如果是奇数则表示性别为男，偶数表示性别为女，第18位是一个校验码，是为了防止前十七位出现重复。

举一个简单的例子，首先建立一个Excel表格，输入姓名、身份证号码的项目和数据。

（一）判断性别。在“性别”项目列C2单元格中输入函数：=IF（MOD，MID（B2，17，1，2）=0，女，男），输出结果为“女”，确定C2单元格然后将鼠标指针放至C2右下角，出现“+”后往下拉，就会自动生成性别列所有结果：李四性别为女，王五性别为男。

（二）自动生成户籍地。在用Excel自动生成户籍所在地前，我们需要先建立一个包含身份证前六位号码和对应户籍所在地的表格，这个信息可以直接从全国身份证号码表中直接提取。接下来的操作方法为：在“籍贯”列D2单元格中输入公式：=LOOKUP（LEFT（D2，6），户籍所在地$A$1：$B$547），可得结果张三籍贯为“安徽省砀山县”，按照上述自动生成的方法，下拉整列“籍贯”列，输出结果为：李四籍贯为“北京市密云县”、王五籍贯为“江苏省雨花台区”。

（三）自动生成出生年月日。根据学生的身份证号码提取出生年月日比较简单，具体操作方法为：在“出生年月日”列E2单元格输入公式：=MID（B2，7，4）&—&MID（B2，11，2）&—&MID（B2，13，2），输出结果为：张三的出生年月日是1992—2—28，李四出生年月日是1993—4—17，王五出生年月日是1993—05—06。完成后的表格如“图3”所示。