工资条制作（打印）

内部培训资料 内部培训资料 注 意 保 存 Excel应用手册 际华三五一五皮革皮鞋有限公司 二〇一〇年四月十三日 目 录 1一、Excel打造计算准确美观大方工资条 4二、制作工资条的几种方法 4（一）重新排序法 4（二）宏命令法 7（三）宏命令法（2） 8（四）宏命令法（3） 10（五）公式法 10方法一： 10方法二：OFFSET(reference,rows,cols,height,width) 11方法三： 11三、函数应用案例── 算账理财 111.零存整取储蓄 112.还贷金额 123.保险收益 124.个税缴纳金额 13四、函数应用案例── 信息统计 131.性别输入 142.出生日期输入 153.职工信息查询 164.职工性别统计 165.年龄统计 176.名次值统计 177.位次阈值统计 18五、函数应用案例── 管理计算 181.授课日数 182.折旧值计算 193.客流均衡度计算 204.销售额预测 205.客流与营业额的相关分析 六、21EXCEL快捷键一览表 一、Excel打造计算准确美观大方工资条 企业的财务管理中总是少不了工资计算和工资条的 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 ，如果能够掌握Excel设计工资条的方法和技巧，即可事半功倍地快速设计好计算准确又美观大方的工资条，否则不仅效率低下而且无法保证准确性。 　　工资条的特点分析 　　首先让我们看看工资条都有些什么特点。 　　1.工资条头可能一行，也可能两行，根据不同企业工资栏目需求而定。但相同处是每一项条目(或者一个工人的工资信息)具有一个条头，条头具有指定数目的重复性。 　　2.每一项条目中间有一个空行，方便裁剪。根据以上特点，我们可以初步拟定工资条头部制作 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 ： 　　首先建立“工资明细表”，用于储存员工工资表信息，也用于平时编辑和汇总。 　　其次建立“工资条目表”，用于引用工资信息，同时产生工资条形式之工资目。 　　需要用到的函数可以因人而异，有很多公式都可以达成目的，但最后选择目标是效率最高又易于理解者。 　　现在，我们通过两个实例向大家详解工资条头的Excel设计过程，并分析其中技巧，希望望大家能从中受益，举一反三，设计出适合各自需求的Excel工资 计算公式 六西格玛计算公式下载结构力学静力计算公式下载重复性计算公式下载六西格玛计算公式下载年假计算公式 。 　　单行工资条头之设计 　　先看下面的数据，这是一个简易的单行条头工资信息摘录(数据随机生成，非实际工资)，见图1。 　　利用此表数据，我们可以通过函数公式来引用数据，生成需要的工资条形式。 　　步骤与公式如下： 　　1.进入“单行表头工资条”工作表。 　　2.选中单元格B1，输入以下公式： =CHOOSE(MOD(ROW()，3)+1，“”，单行表头工资明细!A$1，OFFSET(单行表头工资明细!A$1，ROW()/3+1，)) 　　3.选中单元格B1，鼠标置于单元格右下角，当箭头变成十字形时(图2) 　　则向右拉至J1单元格。然后再选中B1：J1向下拉，直至公式结果出现空白。此时工资条效果见图3。 　　4.基本达到目的了，但表格还没有边框，打印则不太美观。现在为它加上边框：选中B1：J2，并点击工具栏边框按钮中的田字形按钮添加边框;再点击大方框按钮(图4)，使工资表条目四周边框显示粗一些，比较美观。 5.选择B1：J3单元格，向下拖拉，直至有数据的最后一行。最后效果见图5。 　　公式解释： =CHOOSE(MOD(ROW()，3)+1，“”，单行表头工资明细!A$1，OFFSET(单行表头工资明细!A$1，ROW()/3+1，)) 　　1.CHOOSE函数是选择函数，支持30个参数。第一个参数是选择序号(数值)，其余参数是被选值。第一个参数是N则结果返回其余参数中第N个数值。 　　2.MOD函数是求余数函数，支持两个参数，第一个参数是被除数，第二个参数是除数，结果返回余数。 　　3.ROW函数是返回指定行行号之函数，若省略参数时则返回当前行。 　　4.OFFSET函数是返回偏移量之函数。支持五5参数，分别是[参照区域]、[行数]、[列数]、[高度]、[宽度]。 　　5.“”表示空白，返回空。 　　本公式巧妙动用MOD和ROW函数产生一个循环的序列2/3/1/2/3/1/2/3/1，再通过CHOOSE函数参数的变化动态地引用工资明细表的数据，其中“”的作用是当前行行号为3的倍数时返回空，从而产生一个空白行，方便制作工资条后裁剪。 　　当然，实现功能还有很多公式，如用以下IF函数实现等。 　　双行工资条头设计 　　先看数据如图6。 　　双行条头工资条和单行条头实现方法基本相同，仅仅是公式有些差异。现暂列如下： =CHOOSE(MOD(ROW()，4)+1，“”，双行表头工资明细!A$1，双行表头工资明细!A$2，OFFSET(双行表头工资明细!A$1，ROW()/4+2，)) 　　输入公式后向后拉再向下拉至末行，然后通过前面介绍的方法设置边框，其中部分单元格需去掉左边框或者右边框，使之显得美观。最后结果如下见图7。 Excel具有强大的数据处理和打印输出功能，并且易学易用，是广大用户喜欢使用的电子表格处理软件。现在一些单位的财会人员喜欢用Excel打印本单位的职工工资总表与工资条，但在Excel中要将工资总表手工地转换为工资条则是一件比较烦琐的事。本人从网上收集N种方法，供有这个需要的朋友们参考。其中有的本人经过了实际试验，有的还没有。 二、制作工资条的几种方法 （一）重新排序法 在Excel2000做成的工资表，只有第一个人有工资条的条头（如编号、姓名、岗位工资，年限工资……），想输出成工资条的形式。怎么做？ 　　解决方法： 　　（1）、假设原工资表为“表1”，先复制“表1”到某新空白表中，命名为“表2”，删去“表2”中的多余行，使工资条头位于第一行，职工工资数据依次紧排在条头之后。： 　　（2）、在“表2”工资数据的后一列H中，增加“标注”栏，依次向下填充入1，2，3…等步长为1的序列数。 　　（3）、选中“表2”工资条头下（工资条头不选）第2至最后一行，右键：插入，表2中将出现许多新增空行，将条头数据填入这些新增空行中（方法：选中A1：G1，右键“复制”，然后选中所有新增空行，右键“粘贴”即可，也可用拖拽填充法）。然后在其“标注”栏中依次填上1.5，2.5，3.5…。等步长为1的序列数。操作结果示意如下： 　　（4）、再次选中“表2”第2行至最后一行，点击菜单：数据-排序-按“标注”列（递增）排序，点击确定。再选中H“标注”列，右键：“隐藏”或“删除”，这样，工资条即基本制作完毕。 　　（5）、制作完成的工资条裁开后即可分发给同事了。您如果觉得此表格过于拥挤，还可用类似的方法在每个人之间插入空行。 （二）宏命令法      在Excel中新建一个文件，将其命名为“工资表与工资条”，在工作表“sheet1”中输入并编辑好本单位职工工资总表（如表1所示）后，点击“工具”菜单→“宏”→“宏…”→输入宏名“生成工资条”→创建，输入如下的宏的各行文本，输入完成后保存该宏。将工作表“sheet1”复制为另一个工作表“sheet2”中，使“sheet2”成为当前工作表，执行刚才创建的宏，即可很快将表1所示的工资表转换为表2所示的工资条。 宏的内容如下： Sub 生成工资条() Cells.Select '选择整个表去掉表格线 Range("F1").Activate Selection.Borders(xlDiagonalDown).Line Style = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Rows("2:2").Select '选择第2行 Selection.Insert Shift:=xlDown '在第2行前插入一行，保持第2行 为选中状态 num=150 '总人数×3，如工资表中有100人则 为100×3即num=300 col=14 '工资表的栏数，如工资表有17栏则 'col=17 num1 = 4 Do While num1 <= num '循环插入空行 Range(Cells(num1, 1), Cells(num1, col)).Select '选中第num1行的第1列到第col列 Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown num1 = num1 + 3 Loop Range(Cells(1, 1), Cells(1, col)).Select Application.CutCopyMode = False '剪切复制模式无效 Selection.Copy '复制选择区域 Range("A2").Select '选择A2单元格 ActiveSheet.Paste '从A2单元格起粘贴内容 num2 = 5 Do While num2 <= num '循环插入标题行 Range(Cells(1, 1), Cells(1, col)).Select Application.CutCopyMode = False Selection.Copy Cells(num2, 1).Select ActiveSheet.Paste num2 = num2 + 3 Loop Range(Cells(2, 1), Cells(3, col)).Select Application.CutCopyMode = False Selection.Borders(xlDiagonalDown).LineStyle = xlNone '定义表格边框线、内线样式 Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlDash .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlDash .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Copy Range(Cells(5, 1), Cells(6, col)).Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False '接上行删除上行尾的连字符 _，复制表格线样式 num3 = 8 Do While num3 ＜= num '循环复制表格线样式 Range(Cells(num3, 1), Cells(num3 + 1, col)) .Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False num3 = num3 + 3 Loop Rows("1:1").Select '删除多余的一行 Selection.Delete End Sub 以后每月要打印工资表与工资条时，只需将“工资表与工资条”文件打开，修改好工作表“sheet1”中的当月的工资总表数据后将其复制为工作表“sheet2”，并使“sheet2”成为当前工作表，执行宏“生成工资条”即可。 （三）宏命令法（2）                 在日常应用中，我们经常遇到这样的操作，每隔相等的几行，要重复做相同的事情。比如做工资条要打印时，每条记录前插入一个标题列；每隔N行的行高，统一调整为X高等。用常规的操作，如果数据少的话还可以承受，但数据量一大，就变成一件苦差事了。         下面这个宏，能够按指定的选择来选择等差行，选选择N至N+M行，然后再运行此宏，就可以选择从N行开始，每隔M行至到数据结束的行了，代码如下：         Sub SelectRange()         '按选择区域给定参数选择等差行         Dim i As Integer, XRan As Range         If Selection.Areas.Count > 1 Then         MsgBox "选择区域应为连续区域！", vbExclamation, "错误"         ElseIf Selection.Row > ActiveSheet.UsedRange.Rows.Count Then         MsgBox "选择区域应在使用区域内！", vbExclamation, "错误"         Else         Set XRan = Rows(Selection.Row)         For i = Selection.Row + Selection.Rows.Count To _         ActiveSheet.UsedRange.Rows.Count Step Selection.Rows.Count         Set XRan = Union(XRan, Rows(i))         Next         XRan.Select         End If （四）宏命令法（3）            Attribute VB_Name = "模块1" Sub 生成工资条() Cells.Select '选择整个表去掉表格线 Range("F1").Activate Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Rows("2:2").Select '选择第2行 Selection.Insert Shift:=xlDown '在第2行前插入一行，保持第2行为选中状态 num = ((ActiveSheet.UsedRange.Rows.Count) - 2) * 3 '这个数字是工资表中总人数乘以3，例如工资表中有20人，就是num=60 col = ActiveSheet.UsedRange.Columns.Count '这个数字是工资表中的列数，例如工资表中有20列，就是col=20 num1 = 4 Do While num1 <= num '循环插入空行 Range(Cells(num1, 1), Cells(num1, col)).Select '选中第num1行的第1列到第col列 Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown num1 = num1 + 3 Loop Range(Cells(1, 1), Cells(1, col)).Select Application.CutCopyMode = False '剪切复制模式无效 Selection.Copy '复制选择区域 Range("A2").Select '选择A2单元格 ActiveSheet.Paste '从A2单元格起粘贴内容 num2 = 5 Do While num2 <= num '循环插入标题行 Range(Cells(1, 1), Cells(1, col)).Select Application.CutCopyMode = False Selection.Copy Cells(num2, 1).Select ActiveSheet.Paste num2 = num2 + 3 Loop Range(Cells(2, 1), Cells(3, col)).Select Application.CutCopyMode = False Selection.Borders(xlDiagonalDown).LineStyle = xlNone '定义表格边框线、内线样式 Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlDash .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlDash .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Copy Range(Cells(5, 1), Cells(6, col)).Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False '接上行删除上行尾的连字符_，复制表格线样式 num3 = 8 Do While num3 <= num '循环复制表格线样式 Range(Cells(num3, 1), Cells(num3 + 1, col)).Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False num3 = num3 + 3 Loop Rows("1:1").Select '删除多余的一行 Selection.Delete End Sub （五）公式法 制作企业员工的工资条是一个复杂又费时的工作，不过以下有几条方法会让你的工资表自动生成工资条，让你省时又省力。 随着企业员工的不断增多，单独制作每个人的工资条成为越来越复杂的工作内容，现在好了，利用EXCEL函数，你可以将工资表自动生成每个人的工资条： 方法一： 本工资簿包含两张表。 第1张表就是工资表。它第一行为标题行包括序号、姓名、工资项目。 第2张表就是供打印的表，称为“工资条”。它应设置为每三行一组，每组第一行为标题，第二为序号、姓名和各项数据，第三行为空白行。就是说能被3整除的行为为空行，整张表被3除余1的行为标题行，引用标题行，被3除余2的行为各项数据的行。用INDEX(区域,行,列)函数引用，公式为： =IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,工资表!A$1,INDEX(工资表!$A:$M, (ROW()+4)/3,COLUMN()))) 语法解释：如果行数除3余数为0，则空；如果行数除3余数为1，则取工资表A到M列，第1行（不可引用）；如果如果行数除3余数为2，则返回工资表A到M列，，对应的列；行+4/3 解释：行+4/3：行是工资条的行2， 5，8，11……的行+4/3是返回的工资表是的对应的2，3，4行。如工资条的第2行返回工资表中（2+4）/3=2，工资条的第5行返回工资表中（5+4）/3=3，工资条的第8行返回工资表中（8+4）/3=4。 方法二：OFFSET(reference,rows,cols,height,width) OFFSET的参数用法(以某个单元格（参数1referenc）为参照点（起点），下偏移（参数2 rows）行，右偏移（参数3 cols）列，按偏移后的单元格为起点选定下（参数4 height）行数，右（参数5 width）列数的一个区域） =IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,工资表!C$1,OFFSET(工资表!$A$1,((ROW()+1)/3),COLUMN()-1))) 方法三： =IF(MOD(ROW(),3)>0,OFFSET(工资表!$A$1,(MOD(ROW()-1,3))*((ROW()+1)/3),COLUMN()-1),"") 语法解释：此公式是自动生成循环序数.以工资条的7/8/9行的引用来理解： 如果以A7为例：(MOD(ROW(),3)意思是行7除3余数为1>0，则以工资表!$A$1为参照系引用（(7-1)÷3的余数）×（（7+1）÷3）=0，列0； 如果以A8为例：8除3余数为2>0，则以工资表!$A$1为参照系引用（(8-1)÷3的余数）×（（8+1）÷3）=3行，列0； 如果以A9为例：9除3余数为0则空行。 三、函数应用案例── 算账理财 1.零存整取储蓄 “零存整取”是工薪阶层常用的投资方式，这就需要计算该项投资的未来值，从而决定是否选择某种储蓄方式。 (1)函数分解 FV 函数基于固定利率及等额分期付款方式，返回某项投资的未来值。 语法：FV(rate,nper,pmt,pv,type) Rate 为各期利率；Nper为总投资期，即该项投资的付款期总数；Pmt 为各期所应支付的金额，其数值在整个年金期间保持不变；Pv为现值，即从该项投资开始计算时已经入账的款项，或一系列未来付款的当前值的累积和；Type为数字0 或1，用以指定各期的付款时间是在期初还是期末。 (2)实例分析 新建一个工作表，在其A1、B1、C1、D1单元格分别输入“投资利率”、“投资期限”、“投资金额”和“账户初始金 额”。假设妻子新建一个账户每月底存入300 元，年利2.1% (即月息0.00175)， 连续存款5 年， 可以在A2 、B2、C2 、D2 单元格分别输入“0.00175”、“60”、“500”和“1”。 然后选中E2 单元格输入公式“=FV(A2,B2,-C2,D2,1)”， 回车即可获得该投资的到期本金合计为“￥18,994.67”。公式中的“-C2”表示资金是支出的，“C2”前不加负号也可， 这样计算出来的结果就是负值。 如果丈夫也有“零存整取”账户，每月初存入200 元，年利1.28%(即月息0.001667)， 连续存款3 年， 可以在A3、B3 、C3、D3单元格分别输入“0.001667”、“36”、“200”和“0”。然后把E2 单元格中的公式复制到E3 单元格(将光标指向E2 单元格的拖动柄，当黑色十字光标出现后向下拖动一格)，即可得知该投资的到期本金合计“￥7,426.42”。 提示：上述计算结果包括本金和利息，但不包括利息税等其他费用。 2.还贷金额 如今贷款购买住房进行消费的家庭越来越多，计算贷款的月偿还金额是决策的重要依据，下面我们就来设计如何知道自己每月的还款金额。 (1)函数分解 PMT 函数基于固定利率及等额分期付款方式，返回贷款的每期付款额。 语法：PMT(rate,nper,pv,fv,type) Rate 为贷款利率；Nper 为该项贷款的付款总数；Pv 为现值，或一系列未来付款的当前值的累积和；Fv为未来值，或在最后一次付款后希望得到的现金余额；Type为数字0 或1。 (2)实例分析 新建一个工作表，在其A1、B1、C1、D1单元格分别输入“贷款利率”、“还贷年限”、“贷款金额”和“还贷时间”。 假设贷款年利为4.1%(即月息0.00342)，预计的还贷时间为10 年，贷款金额为10 万元，且每月底还贷。可以在A2、B2、C2、D2单元格分别输入“0.00342”、“360”、“100000”和“1”(表示月末还贷，0 表示月初还贷)。然后选中E2 单元格输入公式“= PMT(A2,B2,C2,,D2)”，回车就可以获得每月的还款金额为“￥-481.78”。 上式中C2 后的两个逗号之间还有一个参数，表示还贷期限结束时账户上的余额，对这个例子来说应该是0， 所以可以忽略该参数或写成“= PMT(A2,B2,C2,0,D2)”。 3.保险收益 保险公司开办了一种平安保险，具体办法是一次性缴费12000 元，保险期限为20 年。如果保险期限内没有出险，每年返还1 000 元。请问在没有出险的情况下，它与现在的银行利率相比，这种保险的收益率如何。 (1)函数分解 RATE 函数返回投资的各期利率。该函数通过迭代法计算得出，并且可能无解或有多个解。 语法：RATE(nper,pmt,pv,fv,type,guess) Nper 为总投资期，即该项投资的付款期总数；Pmt 为各期付款额，其数值在整个投资期内保持不变；Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款当前值的累积和；Fv为未来值，或在最后一次付款后希望得到的现金余额；Type为数字0 或1 。 (2)实例分析 新建一个工作表，在其A1、B1、C1、D1单元格分别输入“保险年限”、“年返还金额”、“保险金额”、“年底返还” 和“现行利息”。然后在A2 、B2、C2、D2 和E2 单元格分别输入“20”、“1000”、“12000”、“1”(表示年底返还，0表示年初返还)和“0.02”。然后选中F2 单元格输入公式“=RATE(A2,B2,C2,,D2,E2)”，回车就可以获得该保险的年收益率为“0.06”。要高于现行的银行存款利率，所以还是有利可图的。上面公式中的C2 后面有两个逗号，说明最后一次付款后账面上的现金余额为零。 4.个税缴纳金额 假设个人收入调节税的收缴标准是：工资在800 元以下的免征调节税，工资800 元以上至1 500 元的超过部分按5%的税率征收，1 500 元以上至2 000 元的超过部分按8%的税率征收，高于2 000 元的超过部分按20%的税率征收。我们可以按以下方法设计一个可以修改收缴标准的工作簿： 新建一个工作表，在其A1、B1、C1、D1、E1单元格分别输入“姓名”、“工资总额”、“扣款”、“个税”和“实付工资”。为了方便个税标准的修改，我们可以另外打开一个工作表(例如Sheet2)， 在其A1、B1、C1、D1 、E1 单元格中输入“免征标准”、“低标准”、“中等标准”和“高标准”，然后分别在其下方的单元格内输入“800”、“1500”、“2000”、“2000”。 接下来回到工作表Sheet1 中，选中D 列的D2 单元格输入公式“=IF(C2<=Sheet2!A2," ",IF((C2-Sheet2!A2)<=Sheet2!B2,(C2-Sheet2!A2)*0.05,IF (C2-Sheet2!C2<=Sheet2!C2,(C2-Sheet2!C2)*0.08,IF(C2>Sh eet2!D2,(C2-Sheet2!D2)*0.2))))”，回车后即可计算出C2 单元格中的应缴个税金额。此后用户只需把公式复制到C3、C4 等单元格，就可以计算出其他职工应缴纳的个税金额。 上述公式的特点是把个税的征收标准放到另一个工作表中，如果征税标准发生了变化，用户只需修改相应单元格中的数值，不需要对公式进行修改，可以减少发生计算错误的可能。公式中的IF 语句是逐次计算的，如果第一个逻辑判断“C2-Sheet2!A2)<=Sheet2!B2”成立，即工资收入低于征收标准，则个税计算公式所在单元格被填入空格；如果第一个逻辑判断式不成立，则计算第二个IF 语句，直至计算结束。假如征税标准多于4 个，可以按上述继续嵌套IF 函数(最多7 个)。 四、函数应用案例── 信息统计 使用Excel 管理人事信息，具有无须编程、简便易行的特点。假设有一个人事管理工作表，它的A1、B1、C1、D1 、E1、F1、G1 和H1 单元格分别输入“序号”、“姓名”、“身份证号码”、“性别”、“出生年月”等。自第2 行开始依次输入职工的人事信息。为了尽可能减少数据录入的工作量，下面利用Excel 函数实现数据统计的自动化。 1.性别输入 根据现行的居民身份证号码编码规定，正在使用的18 位的身份证编码。它的第17 位为性别(奇数为男，偶数为女)，第18 位为效验位。而早期使用的是15 位的身份证编码，它的第15 位是性别(奇数为男，偶数为女)。 (1)函数分解 LEN 函数返回文本字符串中的字符数。 语法：LEN(text) Text 是要查找其长度的文本。空格将作为字符进行计数。 MOD 函数返回两数相除的余数。结果的正负号与除数相同。 语法：MOD(number,divisor) Number 为被除数；Divisor为除数。 MID 函数返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。 语法：MID(text,start_num,num_chars) Text 为包含要提取字符的文本字符串；Start_num 为文本中要提取的第一个字符的位置。文本中第一个字符的start_num 为1 ，以此类推；Num_chars指定希望MID 从文本中返回字符的个数。 (2)实例分析 为了适应上述情况，必须设计一个能够适应两种身份编码的性别计算公式，在D2 单元格中输入“=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))”。回车后即可 在单元格获得该职工的性别，而后只要把公式复制到D3、D4等单元格，即可得到其他职工的性别。 为了便于大家了解上述公式的设计思路，下面简单介绍一下它的工作原理：该公式由三个IF 函数构成，其中“IF(MOD(MID(C2,15,1),2)=1,"男","女")”和“IF(MOD(MID(C2,17,1),2)=1,"男","女")”作为第一个函数的参数。公式中“LEN(C2)=15”是一个逻辑判断语句，LEN 函数提取C2 等单元格中的字符长度，如果该字符的长度等于15， 则执行参数中的第一个IF 函数，否则就执行第二个IF 函数。在参数“IF(MOD(MID(C2,15,1),2)=1,"男","女")”中。 MID 函数从C2 的指定位置(第15 位)提取1 个字符，而MOD 函数将该字符与2 相除，获取两者的余数。如果两者能够除尽，说明提取出来的字符是0(否则就是1)。逻辑条件“MOD(MID(C2,15,1),2)=1”不成立，这时就会在D2 单元格中填入“女”，反之则会填入“男”。 如果LEN 函数提取的C2 等单元格中的字符长度不等于15， 则会执行第2个IF函数。除了MID 函数从C2 的指定位置(第17 位，即倒数第2 位)提取1 个字符以外，其他运算过程 与上面的介绍相同。 2.出生日期输入 (1)函数分解 CONCATENATE 函数将几个文本字符串合并为一个文本字符串。 语法：CONCATENATE(text1,text2,...) Text1,text2,...为1～30 个要合并成单个文本项的文本项。文本项可以为文本字符串、数字或对单个单元格的引用。 (2)实例分析 与上面的思路相同，我们可以在E2 单元格中输入公式“=IF(LEN(C2)=15,CONCATENATE("19",MID(C2,7,2),"年 ",MID(C2,9,2),"月",MID(C2,11,2),"日"),CONCCTENCTE(MID(C2,7,4),"年",MID(C2,11,2),"月",MID(C2,13,2),"日"))”。其中“LEN(C2)=15”仍然作为逻辑判断语句使用，它可以判断身份证号码是15 位的还是18 位的，从而调用相应的计算语句。 对15 位的身份证号码来说，左起第7 至12 个字符表示出生年、月、日，此时可以使用MID 函数从身份证号码的特定位置，分别提取出生年、月、日。然后用CONCATENATE 函数将提取出来的文字合并起来，就能得到对应的出生年月日。公式中“19”是针对早期身份证号码中存在2000 年问题设计的，它可以在计算出来的出生年份前加上“19”。对“18”位的身份证号码的计算思路相同，只是它不存在2000 年问题，公式中不用给计算出来的出生年份前加上“19”。 注意：CONCATENATE 函数和MID 函数的操作对象均为文本，所以存放身份证号码的单元格必须事先设为文本格式，然后再输入身份证号。 3.职工信息查询 Excel 提供的“记录单”功能可以查询记录，如果要查询人事管理工作表中的某条记录，然后把它打印出来，必须采用下面介绍的方法。 (1)函数分解 INDEX 函数返回数据清单或数组中的元素值，此元素由行序号和列序号的索引值给定。 INDEX 函数有两种语法形式：数组和引用。数组形式通常返回数值或数值数组，引用形式通常返回引用。当函数INDEX 的第一个参数为数组常数时，使用数组形式。 语法1(数组形式)：INDEX(array,row_num,column_num) Array 为单元格区域或数组常量。如果数组只包含一行或一列，则相对应的参数row_num 或column_num为可选。如果数组有多行和多列，但只使用row_num 或c olumn_num，函数INDEX 返回数组中的整行或整列，且返回值也为数组；Row_num 为数组中某行的行序号，函数从该行返回数值。如果省略row_num， 则必须有column_num；Column_num 为数组中某列的 列序号，函数从该列返回数值。如果省略column_num，则必须有row_num。 语法2(引用形式)：INDEX(reference,row_num,column_num,area_num) Reference 表示对一个或多个单元格区域的引用。如果为引用输入一个不连续的区域，必须用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数row_num 或 column_num 分别为可选项；Row_num 引用中某行的行序号，函数从该行返回一个引用；Column_num引用中某列的列序号，函数从该列返回一个引用；Area_num 选择引用中的一个区域，并返回该区域中row_num 和column_num 的交叉区域。选中或输入的第一个区域序号为1，第二个为2，以此类推。如果省略area_num，函数INDEX 使用区域1。 MATCH 函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。 语法：MATCH(lookup_value,lookup_array,match_type) Lookup_value 为需要在数据表中查找的数值；Lookup_value 为需要在Look_array 中查找的数值；Match_type 为数字-1、0或1 。 (2)实例分析 如果上面的人事管理工作表放在Sheet1 中，为了防止因查询操作而破坏它(必要时可以添加只读保护)，我们可以打开另外一个空白工作表Sheet2，把上一个数据清单中的列标记复制到第一行。假如你要以“身份证号码”作为查询关键字，就要在C2 单元格中输入公式“=INDEX(Sheet1!C2:C600,MATCH( SC S5,Sheet1! SC S2: SC S600,0),1)”。其中的参数“ SC S5”引用公式所在工作表中的C5 单元格(也可以选用其他单元格)，执行查询时要在其中输入查询关键字，也就是待查询记录中的身份证号码。参数“Sheet1!C2:C600”设定INDEX 函数的查询范围，引用的是数 据清单C 列的所有单元格。MATCH函数中的参数“0”指定它查找“Sheet1! SC S2: SC S600”区域中等于 SC S5的第一个值，并且引用的区域“Sheet1! SC S2: SC S600,0”可以按任意顺序排列。上面的公式执行数据查询操作时，首先由MATCH 函数在“Sheet1! SC S2: SC S600” 区域搜索，找到“ SC S5” 单元格中的数据在引用区域中的位置(自上而下第几个单元格)，从而得知待查询数据在引用区域中的第几行。 接下来INDEX 函数根据MATCH 函数给出的行号，返回“Sheet1!C2:C600”区域中对应行数单元格中的数据。假设其中待查询的“身份证号码”是“3234567896”，它位于“Sheet1! SC S2: SC S600”区域的第三行，MATCH函数就会返回“3”。接着INDEX 函数返回“Sheet1!C2:C600”区域中行数是“3”的数据，也就是“3234567896”。 然后，我们将光标放到C2 单元格的填充柄上，当十字光标出现以后向右拖动，从而把C2 中的公式复制到D2、E2 等单元格(然后再向左拖动，以便把公式复制到B2、A2单元格)，这样就可以获得与该身份证号对应的性别、籍贯等数据。 注意：公式复制到D2、E2等单元格以后，INDEX函数引用的区域就会发生变化，由C2:C600 变成D2 :D600、E2:E600等等。但是MATCH 函数返回的(相对)行号仍然由查询关键字给出，此后INDEX 函数就会根据MATCH 函数返回的行号从引用区域中找到数据。 在Sheet2 工作表中进行查询时只要在查询输入单元格中输入关键字，回车后即可在工作表的C2 单元格内看到查询出来的身份证号码。如果输入的身份证号码关键字不存在或输入错误，则单元格内会显示“#N/A”字样。 4.职工性别统计 (1)函数分解 COUNTIF 函数计算区域中满足给定条件的单元格的个数。语法：COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元格数目的单元格区域；Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 (2)实例分析 假设上面使用的人事管理工作表中有599 条记录，统计职工中男性和女性人数的方法是：选中单元格D601(或其他用不上的空白单元格)，统计男性职工人数可以在其中输入公式“="男"&COUNTIF(D2:D600,"男")&"人"”；接着选中单元格D602，在其中输入公式“="女"&COUNTIF(D2:D227,"女")&"人"”。回车后即可得到“男399 人”、“女200 人”。 上式中D2:D600 是对“性别”列数据区域的引用，实际使用时必须根据数据个数进行修改。“男”或“女”则是条件判断语句，用来判断区域中符合条件的数据然后进行统计。“&” 则是字符连接符，可以在统计结果的前后加上“男”、“人”字样，使其更具有可读性。 5.年龄统计 在人事管理工作中，统计分布在各个年龄段中的职工人数也是一项经常性工作。假设上面介绍的工作表的E2:E600 单元格存放职工的工龄，我们要以5 年为一段分别统计年龄小于20 岁、20 至25 岁之间，一直到55 至60 岁之间的年龄段人数，可以采用下面的操作方法。 (1)函数分解 FREQUENCY 函数以一列垂直数组返回某个区域中数据的频率分布。 语法：FREQUENCY(data_array,bins_array) Data_array 为一数组或对一组数值的引用，用来计算频率。如果data_array 中不包含任何数值，函数FREQUENCY 返 回零数组；Bins_array为间隔的数组或对间隔的引用，该间隔用于对data_array 中的数值进行分组。如果bins_array 中不包含任何数值，函数FREQUENCY 返回data_array 中元素的个数。 (2)实例分析 首先在工作表中找到空白的I 列(或其他列)，自I2 单元格开始依次输入20、25、30 、35、40...60， 分别表示统计年龄小于20、20 至25 之间、25 至30 之间等的人数。然后在该列旁边选中相同个数的单元格，例如J2：J10 准备存放各年龄段的统计结果。然后在编辑栏输入公式“=FREQUENCY(YEAR(TODAY())-YEAR(E2:E600),I2:I10)”，按下Ctrl+Shift+Enter 组合键即可在选中单元格中看到计算结果。其中位于J2 单元格中的结果表示年龄小于20 岁的职工人数，J3单元格中的数值表示年龄在20 至25 之间的职工人数等。 6.名次值统计 在工资统计和成绩统计等场合，往往需要知道某一名次(如工资总额第二、第三)的员工的工资是多少。这种统计的操 作方法如下。 (1)函数分解 LARGE 函数返回数据集中第K 个最大值。使用此函数可以根据相对标准来选择数值。 语法：LARGE(array,k) Array 为需要从中选择第K 个最大值的数组或数据区域； K 为返回值在数组或数据单元格区域中的位置(从大到小排)。SMALL 函数返回数据集中第K 个最小值。使用此函数可以返回数据集中特定位置上的数值。法：SMALL(array,k) Array 为需要找到第K 个最小值的数组或数字型数据区域；K为返回的数据在数组或数据区域里的位置(从小到大)。 (2)实例分析 假设C2:C688 区域存放着员工的工资，首先在D 列选取空白单元格D3，在其中输入公式“=LARGE(C2:C688,D2)”。其中D2 作为输入名次变量的单元格，如果你在其中输入3，公式就可以返回C2:C688 区域中第三大的数值。 如果我们把上述公式修改为“=SMALL(C2:C688,D1)”，然后在D1 单元格中输入6，就可以获得C2:C688 区域倒数第六(小)的数值。 为方便起见，你可以给C2:C688 区域定义一个名称“职工工资”。此后可以把上述公式修改为“=LARGE(职工工资,D2)”或“=SMALL(职工工资,D1)”。 7.位次阈值统计 与上例相似，在工资统计和成绩统计等场合，需要知道排名达到总体的前1/3 的工资总额或分数(称为“阈值”)是多 少。这种统计的操作方法如下： (1)函数分解 PERCENTILE 函数返回区域中数值的第K 个百分点的值。可以使用此函数来建立接受阈值。 语法：PERCENTILE(array,k) Array 为定义相对位置的数组或数据区域；K为0 到1 之间的百分点值，包含0和1。 (2)实例分析 假设C2:C200 区域存放着学生的考试成绩，首先在D列选取空白单元格D3，在其中输入公式 “=PERCENTILE(C2:C200,D2)”。其中D2 作为输入百分点变量的单元格，如果你在其中输入0.33，公式就可以返回名次达到前1/3 所需要的成绩。 五、函数应用案例── 管理计算 企业、学校等单位均存在许多管理计算问题，例如计算一个学期有几个授课日、企业在多少个工作日之后交货等等。下面介绍有关问题的几种计算方法。 1.授课日数 (1)函数分解 NETWORKDAYS 函数专门用于计算两个日期值之间完整的工作日数值。这个工作日数值将不包括双休日和专门指定的其他各种假期。 语法：NETWORKDAYS (Start_date,End_date,Holidays) Start_date 表示开始日期；End_date为终止日期，Holidays 表示作为特定假日的一个或多个日期。这些参数值既可以手工输入，也可以对单元格的值进行引用。 (2)实例分析 假设新学期从2003 年9 月1 日开始到2004 年1 月15 日结束，希望知道本学期有多少个授课日，也就是排除双休日和国家法定假日外的授课工作日。这就是计算授课日数或工作日数的问题。首先打开一个空白工作表，在A1、B1、C1 单元格输入“开学时间”、“结束时间”、“法定节日”，然后在其下面的单元格内输入“2003-9-1”、“2004-1-15”、“2003-10-1” “2003-10-2”、“2003-1