excel在财务管理中的应用电子教案

excel在财务管理中的应用电子教案内容摘要：

的填制方法； （四）能够运用 Excel设计、制作工资表； （五）能够运用 Excel对工资数据进行排序、筛选和汇总等统计分析； （六）能够运用数据透视表和数据透视图进行相关分析； 【项目内容】 （一）工资核算基础 （二）制作工资表 （三）工资数据的统计分析 【相关知识点析】 ① 主要概念 工资 —— 是指支付给劳动者的劳动报酬，是企业成本费用的重要组成部分。 工资总额是指各单位在一定时期内直接支付给本单位全部职工的劳动报酬总额。 工资总额由以下六个部分组成：即计时工资、计件工资、奖金、津贴和补贴、加班加点工资和特殊情况下支付的工资。 应纳税所得额 —— 每月取得工资收入后，先减去个人承担的基本养老保险、医疗保险、失业保险和住房公积金后，再减去每月 3500元为应纳税所得额。 应纳个人所得税额 =应纳税所得额适用税率 速算扣除数 =（每月收入 3500）适用税率 速算扣除数 ② 知识拓展 拓展 1： Excel 快速打造计算准确美观大方的工资条 企业的财务管理中总是少不了 工资计算和工资条的设计 ，如果能够掌握 Excel 设计工资条 的方法和技巧，即可事半功倍地快速设计好计算准确又美观大方的工资条，否则不仅效率低下而且无法保证准确性。 工资条的特点分析 首先让我们看看工资条都有些什么特点。 ，也可能两行，根据不同企业工资栏目需求而定。 但相同处是每一项条目 (或者一个工人的工资信息 )具有一个条头，条头具有指定数目的重复性。 ，方便裁剪。 26 根据以上特点，我们可以初步拟定 工资条头部制作方案 ： 首先建立 “工资明细表 ”，用于储存员工工资表信息，也用于平时编辑和汇总。 其次建立 “工资条目表 ”，用于引用工资信息，同时产生工资条形式之工资目。 需要用到的函数可以因人而异，有很多公式都可以达成目的，但最后选择目标是效率最高又易于理解者。 现在，我们通过两个实例向大家详解工资条头的 Excel 设计过程，并分析其中技巧，希望大家能从中受益，举一反三，设计出适合各自需求的 Excel 工资计算公式。 单行工资条头之设计 先看下面的数据，这是一个简易的单行表头工资信息摘录 (数据随机生成，非实际工资 )，见图 1。 利用此表数据，我们可以通过函数公式来引用数据，生成需要的工资条形式。 步骤与公式如下： “单行表头工资条 ”工作表。 B1，输入以下公式： =CHOOSE(MOD(ROW()， 3)+1， “”，单行表头工资明细 !A$1， OFFSET(单行表头工资明细 !A$1，ROW()/3+1， )) B1，鼠标置于单元格右下角，当箭头变成十字形时 (图 2) 27 则向右拉至 J1 单元格。 然后再选中 B1： J1 向下拉，直至公式结果出现空白。 此时工资条效果见图 3。 ，但表格还没有边框，打印则不太美观。 现在为它加上边框：选中 B1： J2，并点击工具栏边框按钮中的田字形按钮添加边框。 再点击大方框按钮 (图 4)，使工资表条目四周边框显示粗一些，比较美观。 B1： J3 单元格，向下拖拉，直至有数据的最后一行。 最后效果见图 5。 28 公式解释： =CHOOSE(MOD(ROW()， 3)+1， “”，单行表头工资明细 !A$1， OFFSET(单行表头工资明细 !A$1，ROW()/3+1， )) 函数是选择函数，支持 30 个参数。 第一个参数是选择序号 (数值 )，其余参数是被选值。 第一个参数是 N 则结果返回其余参数中第 N 个数值。 函数是求余数函数，支持两个参数，第一个参数是被除数，第二个参数是除数，结果返回余数。 函数是返回指定行行号之函 数，若省略参数时则返回当前行。 函数是返回偏移量之函数。 支持 5 个 参数，分别是 [参照区域 ]、 [行数 ]、 [列数 ]、 [高度 ]、 [宽度 ]。 5.“”表示空白，返回空。 本公式巧妙动用 MOD 和 ROW 函数产生一个循环的序列 2/3/1/2/3/1/2/3/1，再通过 CHOOSE 函数参数的变化动态地引用工资明细表的数据，其中 “”的作用是当前行行号为 3 的倍数时返回空，从而产生一个空白行，方便制作工资条后裁剪。 当然，实现功能还有很多公式，如用以下 IF 函数实现等，各位用户自己去多摸索吧： =IF(MOD(ROW()， 3)=1，单行表头工资明细 !A$1， IF(MOD(ROW()， 3)=2， OFFSET(单行表头工资明细 !A$1， ROW()/3+1， 0)， “”)) 双行工资条头设计 先看数据如图 6。 29 双行条头工资条和单行条头实现方法基本相同，仅仅是公式有些差异。 列示如下： =CHOOSE(MOD(ROW()， 4)+1， “”，双行表头工资明细 !A$1，双行表头工资明细 !A$2， OFFSET(双行表头工资明细 !A$1， ROW()/4+2， )) 输入公式后向后拉再向下拉至末行，然后通过前面介绍的方法设置边框，其中部分单元格需去掉左边框或者右边框，使之显得美观。 最后结果如下见图 7。 拓展 2：用 Excel 制作工资条的两种简单方法 Excel 软件中的数据处理功能非常强大，很多人都用它来制作 工资表 ，再打印出 工资条。 很多文章都介绍过如何把工资表制成工资条，但这些方法非常复杂，不利于操作。 笔者经过研究，找到了两种 用 Excel制作工资条 的非常简单的方法，希望能对有这方面要求的朋友有所帮助。 方法一：排序法 30 第一步：打开工资表 → 在工资表最后一空白列输入 1， 2， 3……( 注：输入的数字与工资表的行数相同 )(图1)。 第二步：在刚输入的数字下面向下输入 ， …… ，比上面的数据少一行，本列在 K9 中输入 (图2)。 31 第三步：在 K 列任何一个单元格中单击左键 → 单击 工具栏上的升序排列按钮 → 选择工资表 → 编辑 → 定位 → 定位条件选择 “空值 ”→ 确定。 第四步：在编辑栏中输入 =A$1→ 按住 Ctrl 键不放敲回车键。 第五步：在单元格 K15 下方输入 ， …… 一直输入到 → 单击工具栏上的升序排列按钮 → 删除 K 列的数据。 加一下边框便大功告成了 (图 3)。 方法二：函数法 第一步：打开工资表 → 单击 Sheet2 标签 → 在 Sheet2 工作表的 A1 单元格中输入=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$A:$N,INT((ROW()+4)/3),COLUMN())))→ 向后拖到 J1 单元格 (注：拖的长度应与原表长度相同 )(图 4)。 32 第二步：选择 A1:J1 区域向下拖 → 拖到第 20 行即可 (注：拖的时候可能拖多或拖少，这时要看一下原表 )→ 加一下边框便成功了 (图 5)。 ③ 案例分析 案例 1： Excel 函数做工资条格式的成绩通知单 33 班主任工作繁杂琐碎，每到各种考试，向学生反馈成绩就是一件必须完成的事情。 现在不主张把所有学生的成绩全部打出并直接张贴，又得让学生尽快知道自己的成绩，所以选用＂成绩通知单＂的形式给学生成绩。 “成绩通知单 ”在格式上有点像工资条，方便发给学生，让学生了解自己一段时间内的收获。 下面介绍利用 Excel 软件的 排序函数 rank()以及 筛选功能 轻松整理工资条格式的学生成绩单的方法和技巧。 用 rank()函数计算排名 首先，我们要计算学生各科及总成绩在班里的排名，让学生了解自己各科成绩及总成绩在班上的位置。 计算过程使用 rank 函数 (图 1)。 在 C2 单元格内输入 “=rank(B2， B： B)”即可得到 B2 数值在 B 列中的位置 (名次 )。 回车后双击 C2 单元格右下角的 小黑 点，即可完成所有同学的班级名次。 用同样的方法完成其他各科的名次。 然后将整个表单按姓名排序，以便发放成绩条时学生知道下一个可能是他，节省时间。 注意，很多班主任将总成绩从高到低排序，然后在右边一列中填充数列 3…… ，这样是没有并列名次的。 添加标题行 首先，将标题栏一行剪切到最后，并在最左边插入列。 然后在 A A2 处分别输入 5，并选中这两个单元格，双击 (或往下拉 )A2 单元格右下角的小黑点，实现等差数列 “ 8……” 的填充。 用同样的方法实现标题栏前面数列 “ 7……” 的填充，填充个数与上次相同。 并选中全部标题栏 (不含最左边的数 )，用 鼠标 选中右下角的小黑点，往下拉，实现标题栏的复制。 再在最后 (标题栏后 )用同样的方法实现数列 “ 9……” 的填充，填充个数与上次相同，且每行只有填充的数列。 然后按所填充的数列从小到大排列。 到此，完成标题行的添加 (图 2)。 添加表格框及页面设置 34 选中整个表单 (一定要选中整个表单 )，主菜单选择 “数据 ”→“ 筛选 ”→“ 自动筛选 ”， “姓名 ”一栏内选择 “非空白 ”，然后选中文字并给文字加上表格框 (之前没有任何表格框 )(图 3)。 同样可以在 “姓名 ”一栏中选择 “空白 ”，调整成绩条之间的行间距。 最后，同样通过选择 “数据 ”→“ 筛选 ”→“ 自动筛选 ”去掉筛选，删除 A 列的数列并设置好页边距。 这样，我们就可以得到我们想要的成绩条了(图 4)。 案例 2： Excel 财务应用实例：按职称分级计算工资 本月公司员工的工资要调整，经理要求按每个人的职称来加工资。 具体要求：职称为初级的本月加 20元，职称为中级的本月加 50 元，职称为高级的本月加 70 元。 但是因为公司人数太多，逐个添加不但麻烦还容易出错，但运用条件函数即可方便实现。 第一步： Excel 打开工资表，在实发工资下面输入 =IF(B2=“初级 ”， G2+20， IF(B2=“中级 ”， G2+50，IF(B2=“高级 ”)， G2+70))。 注： (B2 代表职称。 G2 代表应发工资，图 1)。 35 第二步：回车，向下快速填充即可。 36 项目五 Excel 在固定资产中的应用 【项目要求】 （二）能够根据单位实际设计固定资产卡片的格式； （三）能够利用 Excel对固定资产进行查询； （四）能够运用 Excel提取各种表格，进行统计分析。 【项目内容】 (一 )固定资产管理基础 (二 )固定资产增减变动管理 (三 )固定资产折旧管理 (四 )固定资产的统计分析 【相关知识点析】 ① 主要概念 流动资金 —— 流动资金 常常被喻为企业的“血液”，它的流动和运动，反映在企业生产经营中的各个环节。 广义的流动资金，指企业全部的流动资产，包括现金、存货（材料、在制品、及成品）、应收账款、有价证券、预付款等项目。 狭义的流动资金为流动资产扣除流动负债后的余额，即净营运资金。 现金 —— 泛指企业在日常经营过程中进行交换和支付的一种手段，包括库存现金、各种形式的银行存款、银行汇票、银行本票等，是流动性最强 的资产。 应收账款 —— 是指企业因出售商品或提供劳务时应收而尚未收回的款项。 应收账款的作用主要体现在两方面：一方面可以增加销售，另一方面能够减少存货，加速资金周转。 信用标准 —— 是客户获得企业的商业信用所应具备的最低条件。 如果顾客达不到信用标准条件，便不能享受企业的信用或只能享受较低的信用优惠。 评价客户信用标准常用的方式是5C评估法，即品质、能力、资本、抵押品和条件。 信用条件 —— 是企业提供给客户信用所规定的条件，包括信用期间、折扣期间和现金折扣率。 信用期间是指企业允许客户从购货到付款之间的时间，即企业给予客 户的付款期限。 折扣期间是企业确定的客户可享受现金折扣的付款时间。 现金折扣率是企业对客户在信用期内提前付款给予的优惠额度。 存货 —— 是企业一项重要的资产，它是指企业在生产经营过程中为销售或耗费而储备的各种物资，包括：商品、材料、燃料、低值易耗品、包装物、在产品、半成品、产成品等。 ② 知识拓展 拓展 1：利用 Excel的 VBA。