财务管理必会Excel之财务入门工资表

1大家好！大家好！见面喝酒少不了！你不喝，我不喝，国家造酒往哪搁？你不醉，我不醉，国家马路谁来 睡？你不醉，我不醉，国家马路谁来睡？ 财务入门工资表 CHAPTER 1· 基 础 篇 Excel给多数人留下的印象是可以创建出各 式各样的表格。在财务工作中需要创建的表格有 很多种：有临时使用的，有长期使用的；有带内 容的 ，有空白的；有需要计算的，有不需要计算 的，可谓是五花八门。 下面就从工资表的创建开始，为大家讲解财 务工作中常用的各种表格。 PART1 2 财务管理必会Excel应用100例办公室之 · 第1章 财务入门工资表 财务工作是一个用数字说话的工作，而表格是最直观的表现形式。随 着无纸化办公的进一步推广，写写画画的传统财务管理方式正逐渐被电子 表格所取代，从事财务管理的工作人员除了掌握基本的财务管理软件，还 需要熟练掌握Excel工具软件，让工作变得更轻松愉快。 常有从事财务工作的工作人员戏称自己为“表哥”、“表姐”，因为 在日常财务工作中财务管理是否出色，答卷就是各位表哥、表姐的表格是 否漂亮。而作为一名财务人员，接触最多的或许就是员工工资表，让我们 先从工资表说起。 本章要点： 1.Excel基本操作、SUM函数、NOW函数 2.隐藏网格线、快速输入、DATEDIF函数 3.VLOOKUP函数、ROUND函数、LOOKUP函数 4.取消零值显示、INDEX函数、ROW函数 怎样在Excel中输入“上标2”和“上标3”？ 个人所得税税率表效果图 个人信息表效果图 3 财务入门工资表 CHAPTER 1· 1.1 银行代发工资表 一个小公司的工资发放表是简单明了的，没有任何明细，只有一个笼统的金额。将 现金直接发放到员工手中的方式也越来越少，更多是银行代发。每个月财务人员需要向 银行提交一份员工工资报表。现在，我们来学习一个简单的Excel表格的制作，初级表哥 和表姐请从此处开始学习。 代发工资表效果图 1.1.1 创建新表格 首先，要先弄明白两个名词——工作簿和工作表，在Excel的使用中，我们经常将遇 到这两个名词。工作簿是用Excel创建的文件，它可以包含多个工作表。默认情况下，一 个工作簿中含有3个工作表，用户可以根据自身需要添加或删除工作表。 步骤01 创建工作簿 单击Windows的“开始”菜单→“程序” →“Microsoft Office” →“Microsoft Office 在按住Alt键的同时，按下小键盘上的数字“178”、“179”即可输入“上标2”和“上标3”。 4 财务管理必会Excel应用100例办公室之 · Excel 2007”启动Microsoft Offi ce Excel 2007,系统自动创建一个新的工作簿Book1。 步骤02 为工作簿命名 单击窗口左上角的Offi ce按钮→选择“保存”弹出“另存为”对话框。在电脑中选择 保存的路径，并新建一个文件夹，命名为“工资表”按确定。在“另存为”对话框，将 “文件名”文本框中的文件名修改为“4月工资表.xls”，单击“保存”按钮即可完成工 作簿的命名和保存。 表哥、表姐在财务工作中，将处理大量的表格，文件的保存与分类显得尤其重要。 将工作表按照各自的用途分类保存，让工作显得井井有条，同时对于以后文件的查找、 整理工作也带来极大的便利。 步骤03 重命名工作表 双击工作簿下方的工作表Sheet1标签，进入标签重命名状态，输入“4月工资表”， 按回车键Enter确认。 步骤04 删除多余的工作表 右键单击Sheet2工作表标签弹出快捷菜单，选择“删除”将Sheet2工作表删除，用同 样的方法删除Sheet3工作表。 怎样在多个单元格内快速填入相同内容？ 5 财务入门工资表 CHAPTER 1· 有心的表哥、表姐马上就能想到，既然能删除，同样能创建新的工作表。用快捷菜 单中的“插入”，就能新建更多的工作表。如果不想在电脑中重复制作大量相同的工作 簿，那就可以将类似的工作表放在以个工作簿中。例如本例中，就可以在一个工作簿中 保存2009年1月到12月的公司工资表。鼠标单击Office按钮旁的“保存”按钮，就可以将 上述的设置保存下来了。 1.1.2 录入数据 接下来，就是数据的填写了。Excel中的数据包括数值型和文本型。数值型包括数字 “0~9”、“+”、“-”、“E”、“%”、小数点和千分位符号等。输入数值时，在默 认情况下显示的是靠右边对齐方式。若输入的数值超过单元格宽度，Excel会自动以科学 计数法表达。若输入的小数位超过设置的单元格位数，Excel将自动地进行四舍五 入。Excel在计算时是以输入的数值为准，而不是显示的数值。 在Excel中，文本数据包括汉字、英文字母、数字、空格和键盘能输入的其他符号。 在默认的情况下是靠左对齐的方式，当输入数字型字符时可在字符前面加以个单引号。 当输入的内容超过了单元格的宽度而无法显示时，可以调整单元格的列宽。可以简单得 采用鼠标拖拽的方式实现。对于批量处理可以按住Shift不放，然后用鼠标选择需要调整 的行或列，然后进行调整。对于跨行跨列的单元格，可以按着Ctrl键不放，然后选择需要 调整的行或列进行调整。 步骤01 输入文本 在相应的单元格中输入表项，并填写其中的内容，如员工的姓名等。 选中多个单元格后，输入字符，然后按Ctrl+Enter组合键，即可在选中的每个单元格中填入上述相同的字符。 6 财务管理必会Excel应用100例办公室之 · 步骤02 设置文本格式 在账号这个表项中，银行的卡号是作为文本型数据输入的，选中B4：B15、E4：E14 单元格区域，点击鼠标右键，选择弹出的“设置单元格格式” →“数字” →“文本”， 单击“确定”按钮。 步骤03 账号的录入 银行的账号总是那么长，单在Excel中输入大于11位的数字，显示的数字不是我们想 要表现的形式，这个时候可以将单元格格式设置为文本，也可以在输入数字钱先输入半 角单引号，再输入数字。适当调整列宽，以完整显示账号。 怎样才能快速复制上单元格的内容？ 7 财务入门工资表 CHAPTER 1· 步骤04 输入金额 选中输入金额的C4单元格，鼠标右键弹出快捷菜单，选择“设置单元格格式” → “数字” →“货币”，在右边对话框选择货币符号，单击“确定”。现在，输入的数字 前就自动生成人民币的符号了。将金额列设置为“文本”格式，然后完成金额的输入。 步骤05 设置时间自定义格式 在F2单元格，点击鼠标右键弹出快捷菜单，选择“设置单元格格式” →“数字” → “自定义”，并在右边的“类型”中选择“yyyy”年“m”月，点击确定。在F2输入时 间“2009-4-30”按回车，F2单元格显示出“2009年4月”。 1.1.3 公式与函数 当数据录入完成，就要通过一些公式来进行统计计算，正确运用Excel中的函数，可 以轻松实现金额统计等工作。在这个工资表中，将用到求和函数SUM，可以实现对选定 单元格区域所有的数字求和。 步骤01 SUM函数 在A17单元格输入“总计”，将F17设置为“货币”格式。选中F17单元格，在编辑 栏中输入求和公式“=SUM(C4:C15,F4:F14)”然后按回车键，此时，就会在F17单元格显 选中下面的单元格，按Ctrl+ '（'为西文的单引号）组合键，即可将上一单元格的内容快速复制下来。 8 财务管理必会Excel应用100例办公室之 · 示数字“48,430.00）。 本例中的求和公式，就是将C列和F列中的工资金额相加，在F17输出最终结果。 步骤02 NOW函数 NOW函数可以返回计算机系统内部时钟的当前日期和时间，可以给制作人返回一个 打印时间。在E18输入“打印时间”。选中F18，在右键菜单的“设置单元格格式” → “日期” →类型中选择“2001-3-14”项，按确定。 选中F18单元格，在编辑栏中输入“=NOW()”，按回车键，将在F18中显示当前打 印时间“2009-5-4”。 1.1.4 表格设置 经过以上的步骤，一个工资表所要实现的功能都完成了。单此时的表格，给人的感 觉只是一堆数字和文字的罗列。我们还需要进一步的设置，才能让人有直观的感觉。 步骤01 设置标 选中A1单元格，点击格式中的“合并后居中”按钮，将“英才公司4月工资发放 表”设置居中。在右键菜单选择“设置单元格格式” →“字体”，将标题的字体、字号 做财务表格的时候经常需要输入日期，怎样才能快速输入系统日期？ 9 财务入门工资表 CHAPTER 1· 和字形进行设置。 用同样的方法，对表中其他部分进行调整。 步骤02 设置表格边框 现在，我们需要给表格添加横竖线，让这堆文字更像一个表格。选中A1单元格，按 着Shift键不放再单击F18单元格，就选中了整个表格。在右键菜单中选择“设置单元格格 式” →“边框”，对边框线进行设置，然后点击确定按钮完成设置。 步骤03 设置颜色 为了突出显示某一部分的文字或数字，可以通过改变字体或单元格的颜色来实现。选 择需要突出显示的部分，在“设置单元格格式” →“填充”中设置单元格的背景颜色等。 按下Ctrl+；组合键，即可快速输入系统日期。按下Ctrl+shift+；组合键，即可快速输入系统时间。 10 财务管理必会Excel应用100例办公室之 · 1.1.5 表格预览与保存 步骤01 打印预览 工资表的美化工作完成，别急着打印出来，先看以下实际打印效果。单击Office按 钮，选择“打印” →“打印预览”。 步骤02 调整页面边距 在打印预览状态，可以非常直观地通过拖动代表页面边距的虚线，调整页面 的边距。 步骤03 表格保存 工资表已经完成了，将表格保存下来吧！保存这个动作建议在制作表格的过程中要 多用，万一遇到电脑死机、停电等意外事故，之前的劳动也不会白费。 一般情况下，在单元格中输入的任何数字，均以“右对齐”格式放置，而且Excel会把它当成数字来处理，如 何输入文本数字？ 11 财务入门工资表 CHAPTER 1· 1.2 个人所得税税率表 表哥、表姐并不会只面对一张简单的工资表，姓名、银行账号、金额这么单纯的工 资表也只是提交银行的一张简表。对于公司内部，会有一张更详细的明细表，包含基本 工资、绩效工资、补贴、加班费、考勤扣款、个人所得税和代扣的养老保险等各种名 目，明确告知员工工资的详细构成。每个企业都有一套完整的工资核算体系，与多项考 核指标有关，这都需要一系列的员工信息管理表支持。接下来，系统学习一下用Excel管 理员工资料并制作工资表的过程。 个人所得税税率表效果图 步骤01 新建工作簿 生成一个新的工作簿，并命名，修改工作表标签名。在右键菜单中选择“工作表标 签颜色”，更改工作表标签颜色。用不同的颜色来标识工作表，让枯燥的数字工作多一 丝色彩。 如果你要输入文本格式的数字，除了事先将单元格设置为文本格式外，只要在数字前多输一个’（单引号） 即可。 12 财务管理必会Excel应用100例办公室之 · 步骤02 填写内容 个人所得税时工资的重要组成部分，通常变动较小，可单独制作一张税率表，其中 的比例按照国家相关规定做调整即可。根据个人所得税征收比例填写表格内容。 步骤03 美化表格 在财务工作中经常会遇到分数，分数的输入比较麻烦，怎么才能快速输入分数？ 13 财务入门工资表 CHAPTER 1· 设置字体和框线，对于起征额一栏，有些边框线不要，只需要在边框设置时，点掉 不要的边框线就行了，操作非常直观。当表格全部完成，可以再“视图”中点击“显 示、隐藏“按钮，将表格之外的网格线隐藏不显示，这样在浏览时页面清爽多了。 1.3 个人信息表 公司员工的个人相关信息资料，听起来好像是人事部门才该面对的问题。但在实际 财务管理中，员工的工资却与很多信息相关联，比如说员工的工作年限等。做财务工作 的表哥、表姐也要为员工建立一个小小的个人档案，只是比人事管理要简单很多。 个人信息表效果图 步骤01 数据录入 新建一个工作表，工作表标签修改为“员工基础资料表”，并录入数据。在录入 “员工代码”一栏时，有一些可以取巧省力的方法。在A2单元格输入“A001”，然后 选中A2单元格，将鼠标放到单元格右下角，当鼠标变成黑色的实心十字符号时，按下鼠 标左键不放，向下拉动鼠标至A20单元格。松开鼠标，Excel自动排序生成了员工代码。 如果事先将Excel单元格设置为“分数”格式，即可在其中输入2/3.5/6这样的分数数据。除此以外，可以在 “常规”单元格中按“0空格分数”的格式输入分数，如输入的“0□2/3”（□表示空格）即可。 14 财务管理必会Excel应用100例办公室之 · Excel最大的作用就是避免重复无意义的工作，用同样的方法，使用鼠标拖动，可以在部 门一列中复制相同的部门名称。 我们还可以使用快捷键组合实现重复内容的快速输入，Ctrl+D可 用于列快速重复输入，而Ctrl+R可以完成行快速重复输入。 技巧 步骤02 编制公式 选中H2单元格，在编辑栏中输入公式“=DATEDIF(E2，基本资料表！$B$1,” y”)*50”，按回车键确认。选中H2单元格，将光标放到单元格的右下角，当光标变成黑 十字形状时，按住鼠标左键不放，向下拖到H20单元格松开，就能完成年功工资一列的 公式复制。 ：DATEDIF函数 这里用到了DATEDIF函数，在财务工作应用中很广泛，用于计算两个日期之间的天 数、月数或年数。 函数语法 DATEDIF(start_date,end_date,unit) start_date：为一个日期，代表时间段内的第一个日期或起始日期。 end_date：为一个日期，代表时间段内的最后一个日期或结束日期。 Unit：为所需返回的类型，所包括的类型有： 怎样在特定的单元格快速切换特定的输入法？ 15 财务入门工资表 CHAPTER 1· “Y” 时间段中的整年数 “M” 时间段中的整月数 “D” 时间段中的天数 “MD” start_date与end_date日期中的天数的差，忽略日期中的月和年。 “YM” start_date与end_date日期中的月数的差，忽略日期中的日和年。 “YD” start_date与end_date日期中的天数的差，忽略日期中的年。 Unit 返回的结果 在“年功工资”中，公式起始日期为“员工基础资料表”工作表中的E2单元格中的 日期，也就是员工“进厂时间”，结束日期为“基础资料表”工作表中B1单元格中的日 期。在这个公式中，只取了两个时间段之间的整年数，员工每工作一整年就多50元钱， 整年数*50就得到了该员工的年功工资。 步骤03 设置单元格格式 选中H2到H20单元格，点击鼠标右键，在快捷菜单中选择“数字” →“常规”，确 定后，单元格中就自动计算出每个员工的年功工资数了。稍微调整表格字体、边框等， 表格就完成了。 1.4 个人当月信息表 员工的当月信息是工资表的一个重要项目，包含了出勤、加班、养老保险和补贴等 重要信息。因为存在一定变数，可单独成表，然后按照当月实际情况进行修改，然后供 其他工作表调用其中的数据，这样才是一个系统而全面的工资表体系。 单击“数据→有效性”，打开“数据有效性”对话框中的“输入法模式”选项卡，选中“模式”下拉列表中 的“打开”，单击“确定”按钮。选中了某种输入法有效的单元格，已经设置的输入法就会被自动激活。 16 财务管理必会Excel应用100例办公室之 · 个人当月信息表效果图 步骤01 数据的调用 在第一行输入表格标题，接下来就是数据的调用。个人当月信息中的员工代码、姓 名、部门等数据与“员工基础资料表”中的内容是相同的，那么这张工作表中就无需反 复输入这些数据，而是采用调用数据的方法。这样做还有一个好处，当员工资料发生变 化时，不需要核对修改每一张表格，只需要修改第一张表格的资料，其他工作表就可以 实时自动变更。以姓名为例，选中B2单元格，在编辑栏中输入公式“=VLOOKUP(A2,员 工基础资料表!A:C,2,0)”，按回车键确定。用同样的方法调用部门中的数据。 知识点：VLOOKUP函数 这里用到了VLOOKUP函数，VLOOKUP函数的功能是在表格和数值数组的首列查找指定 的数值，并且返回表格和数组当前行中指定列处的数值。VLOOKUP中的V代表“列”。 函数语法 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) lookup_value：为需要在数字第1列中查找的数值，可以为数值引用或文本字符串。 table_array：需要在其中查找数据的数据表。 怎样给单元格数据的尾部快速添加信息？ 17 财务入门工资表 CHAPTER 1· col_index_num：为table_array中待返回的匹配值的序列号。为1标识返回第一列中的 数值，为2返回第二列中的数值，以此类推。 range_lookup：为一个逻辑值，指明VLOOKUP函数返回时是精确匹配还是近似匹配。 步骤02 复制公式 将光标放在B2单元格的右下角，当光标变成黑十字形状时，按住鼠标左键不放，向 下拖动鼠标到A20，松开鼠标，就能完成此列公式的复制，自动调用了“员工基础资料 表”中的员工姓名。用同样的方法完成“部门”一列的数据读取。 步骤03 出勤 在D列输入“应出勤天数”，然后根据每个人的实际情况，输入“缺勤天数”。在 如果要给单元格数据的尾部添加信息，可以选中该单元格然后按F2键。光标就会在数据尾部出现，输入数据 后回车即可。 18 财务管理必会Excel应用100例办公室之 · F2单元格输入计算公式“=D2-E2”，按回车键确定。这样就在F2中输出该员工的实出勤 天数。选中F2单元格，光标在右下角变成黑十字形状后，按住鼠标左键向下拖动，完成 该列单元格公式的复制。 步骤04 完善数据表格 将表格中剩余数据填写完成，设置字体、字号、边框线、单元格宽度，将表格完善。在 “页面布局”→“网格线”→“查看”，将查看前的勾去掉，这样就取消网格线显示。 1.5 工资明细表 前面的一系列工作，包括员工基础表、个人所得税税率表、个人当月信息表的制 作，都是为工资明细表提供支撑的。通过读取上述工作表中的数据，我们就能轻松得到 一张工资明细表。这张明细表一目了然，非常方便查阅。 工资明细表效果图 只知道函数名称，但又不了解它的参数使用方法，怎样快速插入？ 19 财务入门工资表 CHAPTER 1· 1.5.1 编制工作表 步骤01 新建工作表 前面说过，一个工作簿默认有3个工作表，现在我们需要在此工作簿增加一个工 作表。在工作表标签点击鼠标右键，选择“插入”→“工作表”，重新命名为“工资 明细表”。在A1单元格输入“工资明细表”，将A2单元格的格式设置为“数字” → “日期” →“2001年3月14日”。选中A2单元格，在编辑栏中输入公式“=基础资料 表!B1”，调用“基础资料表”B1单元格的日期。 步骤02 调用前表的员工代码、部门和姓名 在第三行输入工作表的标题，也就是工资表的各项明细，根据内容适当调整单元格 大小。通过编辑公式，调用“员工基础资料表”中的员工代码、部门、姓名。 在A4单元格输入公式“=员工基础资料表!A2”，按回车键确定。在B4单元格输入 公式“=VLOOKUP(A4,员工基础资料表!A:H,3,0)”，按回车键确定。选中C4单元格，在 如果知道所使用的函数名称，但又不了解它的参数使用方法，可以在编辑栏中输入等号及函数名，然后按 Ctrl+A键，Excel自动打开参数输入对话框，就能直接输入各个参数了。 20 财务管理必会Excel应用100例办公室之 · 编辑栏输入公式“=VLOOKUP(A4,员工基础资料表!A:H,2,0)”，按回车键确认。将光标 放在单元格右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拖拽到相应位 置。这样就能完成整列的公式复制，调用“员工基础资料表“中的数据。 步骤03 编制“基础工资”公式 选中单元格D4，设置单元格格式为“数值”，并设置“小数位数”的值为“2”， 然后在编辑栏中输入公式“=ROUND(VLOOKUP(A4,员工基础资料表!A:H,6,0)/ VLOOKUP(A4,相关资料!A:G,4,0)*VLOOKUP(A4,相关资料!A:G,6,0),0)”，按回车键确 认。使用公式复制的方法，设置D列的公式。 知识点：ROUND函数 ROUND函数用来返回某个数字按指定数取整后的数字。 函数语法 ROUND(number,num_digits) Number：需要进行四舍五入的数字 num_digits：指定的位数，按此位数进行四舍五入。 函数说明 如果num_digits大于0，则四舍五入到指定的小数位。 如果num_digits等于0，则四舍五入到最接近的整数。 如果num_digits小于0，则在小数点的左侧进行四舍五入。 步骤04 编制“绩效工资”公式 选中E 4单元格，设置单元格格式为“数值”，并设置“小数位数”的值为 “2”，然后在编辑栏中输入公式“=ROUND(VLOOKUP(A4,员工基础资料表!A: 怎样快速编辑单元格中的内容以及快速修改标签名称？ 21 财务入门工资表 CHAPTER 1· H,7,0)*VLOOKUP(A4,相关资料!A:G,7,0),0)”，按回车键确认。使用公式复制法，拖拽完 成E列单元格的公式。 1.5.2 加班工资、养老费等费用的公式编制 除了基本工资和绩效工资，员工工资中还包括日工资、加班费、补贴和养老费 等多种项目，在前面的表中，我们对员工的当月信息进行了记录，现在就是调用计 算的过程。 步骤01 年功工资 选中F4单元格，在编辑栏中输入公式“=VLOOKUP(A4,员工基础资料表 !A: H,8,0)”，按回车键确认。用拖拽的方法完成F列单元格的公式复制。 步骤02 通讯补助 选中G4单元格，在编辑栏中输入公式“=VLOOKUP(A4,相关资料!A:J,10,0)”，按回 如果想对Excel单元格中的内容进行编辑，只须用鼠标双击需要编辑的位置，光标就会插入十字光标的双击 点。用鼠标双击Excel工作表的标签，直接输入文字并回车，可以快速修改标签的名称。 22 财务管理必会Excel应用100例办公室之 · 车键确认。使用公式复制的方法，完成G5：G22单元格区域公式。 步骤03 应发合计 选中H4：H22单元格区域，设置单元格格式为“数值”，并设置“小数位数”的 值为“2”。选中H4单元格，然后在编辑栏中输入公式“=SUM(D4:G4)”，按回车键确 认。用拖拽的方法完成H列单元格公式的复制。 步骤04 日工资 选中I4:I22单元格区域，设置单元格格式为“数值”，并设置“小数位数”的值为 “2”。选中I4单元格，在编辑栏中输入公式“=ROUND(H4/VLOOKUP(A4,相关资料!A: D,4,0),0)”，按回车键确认。用拖拽的方法将公式复制至I5：I22单元格区域。 怎样将某一单元格（或区域）的格式（字体、字号、行高、列宽等）应用于多个位置？ 23 财务入门工资表 CHAPTER 1· 步骤05 正常加班工资 选中J4单元格，在编辑栏中输入公式“=VLOOKUP(A4,相关资料!A:L,8,0)*I4*2”， 按回车确认。表示正常加班给予双倍工资补偿。用拖拽的方法将公式复制到J5：J22单元 格区域。 步骤06 节日加班工资 选中K4单元格，在编辑栏中输入公式“=VLOOKUP(A4,相关资料!A:L,9,0)*I4*3”， 按回车键确认。表示按规定，节日加班给予三倍工资补偿。用拖拽的方法将公式复制到 K5：K22单元格区域。 可以将上述单元格（或区域）选中，然后双击“格式刷”按钮，鼠标拖过要设置格式的若干区域，完成后再 次单击“格式刷”结束操作。 24 财务管理必会Excel应用100例办公室之 · 步骤07 工资合计 选中L4：L22单元格区域，设置单元格格式为“数值”，并设置“小数位数”的值 为“2”。选中单元格L4，在编辑栏中输入公式“=H4+J4+K4”，按回车键确认。使用拖 拽的方法将公式复制到L5：L22单元格区域。 步骤08 住宿费 选中单元格N4，在编辑栏中输入公式“=VLOOKUP(A4,相关资料!A:L,11,0)”，按回 车键确认。用拖拽的方法将公式复制到N5：N22单元格区域。 Excel默认单元格输入一行文本，怎样实现多行文本输入？ 25 财务入门工资表 CHAPTER 1· 步骤09 代扣养老保险金 选中O4单元格，在编辑栏中输入公式“=VLOOKUP(A4,相关资料!A:L,12,0)”，按回 车键确认。用拖拽的方法将公式复制到O5:022单元格区域。 1.5.3 计算个人所得税 在前面我们制作了一张个人所得税税率表，现在就要用到这张税率表，计算每个员 工该缴纳的个人所得税了 步骤01 应纳税所得额 选中R3单元格，输入“应纳税所得额”。选中R4单元格，在编辑栏中输入公式 “=IF(L4>基础资料表!$F$6,L4-基础资料表!$F$6,0)”，按回车键确认。使用拖拽的方法 完成R5:R22单元格区域的公式复制。 当你需要在单元格中开始一个新行时，只要按 Alt+Enter组合键即可，从而在一个单元格中输入两行乃至多行 文本。 26 财务管理必会Excel应用100例办公室之 · 步骤02 税率 选中S3单元格，输入“税率”。选中S4单元格，在编辑栏中输入公式：“=IF(R4=0, 0,LOOKUP(R4,基础资料表!$C$6:$C$15,基础资料表!$D$6:$D$15))”，按回车键确认。使 用拖拽的方法将公式复制到S5:S22单元格区域。 知识点：LOOKUP函数 LOOKUP函数是用来返回向量或数组中的数值。 函数语法 LOOKUP函数的语法有两种形式，向量和数组，在我们涉及的例子中就是向量。 向量形式的语法 LOOKUP(lookup_value,lookup_vector,result_vector) lookup_value：为需要查找的数值，数值可以是数字、文本、逻辑值或者包含数值的 名称或引用。 lookup_vector：为之包含一行或一列的区域，数值可以是数字、文本或逻辑值。如 怎样同时观察距离较远的两列数据？ 27 财务入门工资表 CHAPTER 1· 果是树枝则必须按升序排列，否则函数不能返回正确的结果。 result_vector：为只包含一行或一列的区域，且如果lookup_vector为行（列）， result_vector也只能为行（列），包含的数值的个数也必须相同。 在我们这个例子中的公式，所表达的意思是：如果R4=0则返回0值，否则要在“基 础资料表”工作表中的C6：C15中查找等于R4的值或是小于R4又最接近R4的值，并返回 同行中D（E）列的值。 步骤03 速算扣除数 选中T3单元格，输入“速算扣除数”。选中T4单元格，在编辑栏中输入公式： “=IF(R4=0,0,LOOKUP(R4,基础资料表!$C$6:$C$15,基础资料表!$E$6:$E$15))”，按回车 键确认。用拖拽的方法将公式复制到T5:T22单元格区域。 步骤04 个人所得税 选中M4:M22单元格，设置单元格格式为“数值”，并设置“小数位数”的值为 将鼠标移到水平滚动条右端的拆分框上，变成双向光标后水平拖动，即可用竖线将当前工作表分割为左右两 个窗格。拖动其中的滚动条使需要观察的数据可见，此后即可按常规方法使两列数据同步滚动。 28 财务管理必会Excel应用100例办公室之 · “2”，然后在M4编辑栏中输入公式：“=R4*S4-T4”，按回车键确认。使用拖拽的方 法，将公式复制到M5:M22单元格区域。 步骤05 实发合计 选中P4:P22单元格区域，设置单元格格式为“数值”，并设置“小数位数”的值为 “2”。选中P4单元格，输入公式：“=L4-M4-N4-O4”，按回车键确认。用拖拽的方 法，将公式复制到P5:P22单元格区域。 步骤06 各项合计 选中A23单元格，输入“合计”。选中D23单元格，点击“编辑”→求和符号Σ，自 动在D23生成求和公式：“=SUM(D4:D22)”，计算出“基础工资”一项的合计。使用同 样的方法，完成其余各项的求和。 步骤07 美化表格 工作表的数据录入和公式计算均以完成，最后一步就是美化工作表，该合并的合并 居中，不需要突出显示的让字体颜色浅一些，然后对字体、字号、边框线等进行一系列 怎样让Excel自动填充固定位数的小数点或固定个数的零？ 29 财务入门工资表 CHAPTER 1· 设置，同时取消网格线显示，让表更清爽。 1.6 制作员工工资条 每个月工资发放之后，正规的公司应发给每个员工一个工资条。上面有员工当月工 资的详细构成。但不能将工资明细表剪条发放，因为每个数字缺少对应项目，这就需要 重新制作一张专门用来打印的工资条。作为劳动者，应该妥善保存工资条，当发生劳动 争议时会用得到。还要养成良好的习惯，认真保存好一些文件、单据，例如：工资条、 奖励文件、处罚通知、考勤记录、邮件等等，当发生劳动争议时，这些都可以成为保护 弱势劳动者的最好证据。 打印工资条效果图 单击“工具”菜单中的“选项”，打开“编辑”选项卡，选中“自动设置小数点”复选项。如果需要自动填 充小数点，应在“位数”框中输入（或选择）小数点右面的位数（如“2”）。 30 财务管理必会Excel应用100例办公室之 · 1.6.1 表格制作 步骤01 编制工资条公式 插入新的工作表，重命名为“工资条表”。选中A1单元格，在编辑栏中输入公式， 并按回车键确认。公式为：“=IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,工资明细 表!A$3,INDEX(工资明细表!$A:$Q,INT((ROW()-1)/3)+4,COLUMN())))”。 选中A1单元格，将光标放到右下角，当光标变为黑十字形状时，按住鼠标左键不 放，朝右拖动鼠标，拖到P列松开左键，完成第一行公式的复制。 步骤02 复制公式 选中A1:P1单元格区域，将光标放在P1单元格的右下角，当光标变成黑十字形状时， 按住鼠标左键不放，向下拖动鼠标，达到相应位置后松开左键，即可完成公式的复制。 这个时候，我们就可以看到，工资条的功能已经基本实现了。 怎样将某个长行转成段落并在指定区域内换行，例如：A10内容很长，欲将其显示在A列至C列之内？ 31 财务入门工资表 CHAPTER 1· 步骤03 设置格式 选中A1单元格，点击鼠标右键，在快捷菜单中选择“设置单元格格式”→“边框” →“外边框”，点击确定完成设置。 步骤04 使用格式刷 选中A1单元格，点击格式刷按钮，当光标变成空心十字和格式刷符号的组合时，按 住左键不放，拖动鼠标完成整个表格格式的复制。 步骤05 取消零值显示 点击左上角的Office按钮，打开菜单，点击 下角的“Excel选项”→“高级”→“此工作表 的显示选项”→去掉“在具有零值的单元格中 显示零”前面的勾→确定。现在，当前工作表 中的零值将不显示出来。 步骤为：选定区域A10:C12（先选A10），选择“编辑”菜单之“填充”的“内容重排”，A10内容就会分布 在A10:C12区域中。此法特别适合用于表格内的注释。 32 财务管理必会Excel应用100例办公室之 · 知识点：设置零值不显示 在Excel2003等版本中，设置零值不显示，是单击菜单“工具”→“选项”→“视图”选 项卡中，撤销“零值”复选框即可。而Excel2007中，很多功能都放到了Excel选项中。 步骤06 完善表格 根据单元格显示内容的宽度适当调整单元格的距离，并对字体、字号和文本居中进 行设置，完善工作表。现在，将工资条打印出来，裁剪后即可发放了。 如果用户有一大批小于1的数字要录入到工作表中，怎么样快速录入？ 33 财务入门工资表 CHAPTER 1· 1.6.2 函数说明 本例中用到多个函数，这里逐一讲解一下。 知识点：INDEX函数 INDEX函数用来返回表或区域中的值或值的引用。函数有两种形式：数组和引用。 数组形式通常用来返回数值或数组数值，引用形式通常返回引用，这里我们学习到得是 数组形式。 函数语法 INDEX(array,row_num,column_num) array：为单元格区域或数组常量。如果数组值包含一行或以列，则只要选择相对 应的一个参数row_num或column_num。如果数组有多行或多列，但是只使用row_num或 column_num，INDEX函数则返回数组中的整行或整列，且返回值也为数组。 row_num：为数组中的某行的行序号，函数从该行返回数值。如果省略row_num，则 必须有column_num。 c o l u m n _ n u m：为数组中某列的序列号，函数从该列返回数值。如果省略 column_num，则必须有row_num。 函数说明 如果同时使用row_num和column_num，INDEX函数则返回row_num和column_num交 叉处的单元格的数值。 知识点：ROW函数 ROW函数用来返回引用的行号。 函数语法 ROW(reference) Reference：为需要得到其行号的单元格或单元格区域。 函数说明 如果省略reference，则指ROW函数对所在单元格的引用。如果reference为一个单元 格区域，并且ROW函数作为垂直数组输入，ROW函数则将reference的行号以垂直数组的 形式返回。 选择“工具”→“选项”，单击“编辑”标签，选中“自动设置小数点”复选框，在“位数”编辑框中微调 需要显示在小数点后面的位数，用户可以根据自己的需要进行输入，单击“确定”按钮即可。 34 大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家好大家 好大家好大家好大家好 财务管理必会Excel应用100例办公室之 · 知识点：COLUMN函数 COLUMN函数用来返回给定引用的列标。 函数语法 COLUMN(reference) Reference：为需要得到其列标的单元格或单元格区域。 函数说明 如果省略reference，则假定为是对COLUMN函数所在的单元格的引用。如果 reference为一个单元格区域，并且COLUMN函数作为水平数组输入，COLUMN函数则将 reference中的列标以水平数组形式返回。 本例公式说明 =IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,工资明细表!A$3,INDEX(工资明细 表!$A:$Q,INT((ROW()-1)/3)+4,COLUMN()))) 首先 INDEX(工资明细表 !$A:$Q, INT((ROW()-1) /3 )+4，其中行参数为 INT((ROW()-1)/3)+4，如果在第一行输入该参数，结果是4，向下拖拽公式治20行，可以 看到结果是4;4;4;5;5;5;5;6;6;6……如果用“INT((ROW()-1)/3)+4”做INDEX的行参数， 公式将连续3行重复返回指定区域内的第4、5、6行的内容，而指定区域是“工资明细 表”工作表，第四行以下是人员记录的第一行，这样就可以每隔3行得到下一条记录。用 COLUMN()做INDEX的列参数，当公式向右侧拖拽时，列参数COLUMN()也随之增加。 如果公式到此为止，返回的结果是每隔连续3行显示下一条记录，与期望的结果还有 一定的差距。希望得到的结果是第一行显示字段、第二行显示记录、第三行为空，这就 需要做判断取值。如果当前行是第一行或是3的整数倍加1行，结果返回“工资明细表” 工作表的字段行。如果当前行是第二行或是3的整数倍加两行，公式返回INDEX的结果； 如果当前行是3的整数倍行，公式返回空。 公式中的第一个IF判断IF(MOD(ROW(),3)=0,””,******)用来判断3的整数倍行的 情况，如果判断结果为“真”则返回空，第二个判断IF(MOD(ROW(),3)=1,工资明细 表!A$3,******)用来判断3的整数倍加1时的情况，判断结果为“真”则返回工资明细 表!A$3即字段行的内容；余下的情况则返回INDEX函数段的结果。