财务管理必会Excel之应收应付款表格.pdf

48 财务管理必会Excel应用100例办公室之 第3章 应收应付款表格 一手收钱 一手付钱 在收支之间取得一种动态的平衡 作为财务工 作人员 经常会面对不同的应收款单位和应付款单位 如果只面对一家或 几家公司 财务人员或许能靠超强的记忆力记得每笔应收款或应付款 但 如果往来单位一多 财务们还能清楚记得每一笔款项吗 财务人员尽管对 数字和账务有职业的敏感 但面对大量的账务 仍有力不从心的时候 而 报表账单就是最好的提醒工具 特别是动态的电子账单或报表 随着日期 的改变 提醒财务人员需及时处理那些到期账务 善用Excel 可以给财务 工作带来更大的便利 本章要点 1 IF函数 IF函数与ROUND函数的应用 2 图表的制作方法 SUMIF函数 条件格式 如果在一个Excel文件中含有多个工作表 如何将多个工作表一次设置成同样的页眉和页脚 如何才能一次打 印多个工作表 图表效果图 49 应收应付款表格CHAPTER 3 3 1 应收款表 某公司年终盘点 需要了解当年应收款的拖欠情况 以此作为第二年销售计划调整 的依据 现在有8家销售商有拖欠款项 公司需要财务部出具一份详细的应收款清单 应收款效果图 步骤01 新建工作表 启动Excel2007创建新的工作簿 将Sheet1工作表标签改名为 应收账款 在第一 行输入工作表名称 选中I4单元格 在右键快捷菜单中选择 设置单元格格式 数字 日期 2001 3 14 确定 然后在编辑栏输入日期 2008 12 30 在A2 N2单元格区域 输入标题名称 并适当调整列宽 方便完全显示单元格内容 步骤02 录入数据 将8家未付款公司的数据一一录入 包括开票日期 发票号码 公司名称 应收金 额 已收款金额 收款期等数据 把鼠标移到工作表的名称处 然后点右键 在弹出的菜单中选择 选择全部工作表 的菜单项 这时你的所 有操作都是针对全部工作表了 50 财务管理必会Excel应用100例办公室之 步骤03 未收款金额计算 选中F3单元格 在编辑栏中输入公式 D3 E3 按回车键确认 将光标放在F3 的右下角 当光标变成黑十字形状时 按住鼠标左键不放 向下拖动鼠标到F10单元格松 开 完成该列公式的复制 步骤04 到期日期计算 选中H3单元格 在编辑栏中输入公式 A3 G3 按回车键确认 将光标放在 H3的右下角 当光标变成黑十字形状时 按住鼠标左键不放 向下拖动鼠标到H10单元 格松开 完成该列公式的复制 EXCEL中有序号一栏 由于对表格进行调整 序号全乱了 可要是手动一个一个改序号实在太慢太麻烦 用什 么方法可以快速解决 51 应收应付款表格CHAPTER 3 步骤05 判断是否到期 选中I3单元格 在编辑栏中输入公式 IF H3 I 1 否 是 按回车键确认 将光标放在I3的右下角 当光标变成黑十字形状时 按住鼠标左键不放 向下拖动鼠标 到I10单元格松开 完成该列公式的复制 知识点 IF函数 IF函数用来执行真假值判断 根据逻辑计算的真假值返回不同的结果 函数语法 IF logical test value if true value if false logical test 表示计算结果为TRUE或FALSE的任意值或表达式 value if true 表示logical test为TRUE时返回的值 value if false 表示logical test为FALSE时返回的值 函数说明 IF函数最多可以嵌套7层 使用value if true及value if false参数可以构造复杂的检 测条件 在计算参数value if false及value if true后 IF函数返回相应语句执行后的返回 值 如果IF函数的参数包含数组 在执行KF语句时数组中的每一个元素都将被计算 步骤06 未到期金额计算 选中J3单元格 在编辑栏中输入公式 IF I 1 H30 I 1 H330 I 1 H360 I 1 H390 D3 E3 0 公式输入完成后 按回车键确认 同时使用上述公式复制的方法完成该列单元格区 域的公式 用Excel做成的工资表 只有第一个人有工资条的条头 如编号 姓名 岗位工资 想输出成工资条的形 式 怎么做 53 应收应付款表格CHAPTER 3 步骤08 合计 针对四个时间段的应收款进行合计 选中J11单元格 点击 开始 编辑 在J11自动生成求和公式 SUM J3 J10 使用同样的方法 完成K11 L11 M11 N11的合计运算 步骤09 完善表格 对表格的字体 字号 边框线进行设置 取消网格线显示 3 2 应付款表 作为一个有信誉的公司 付款及时 不拖不赖是很重要的 但付款的同时要考虑公 司现金流的问题 什么时候付 付多少都需要财务人员为公司提供数据支持 现在 有 一家公司有10个供货商应付款 金额不等 公司计划近期付款 并有两种不同的付款方 案 方案一 小于或等于1000元的账户一次性付清 大于1000元的账户偿付应付金额的 50 方案二 小于或等于3000元的账户一次性付清 大于3000元的账户偿还应付金额 这个问题应该这样解决 先复制一张工资表 然后在页面设置中选中工作表选项 设置打印工作表行标题 选好工资条的条头 然后在每一个人之间插入行分页符 再把页长设置成工资条的高度即可 54 财务管理必会Excel应用100例办公室之 的40 现在公司需要财务人员制作一份表格 按照两种方案给出支付明细 并计算总 还款金额 应付款表效果图 步骤01 新建工作表 启动Excel2007创建新的工作簿 将Sheet1工作表改名为 应付款明细 并保存 在 A1单元格输入 应付款明细表 在A2 D2单元格区域输入标题名称 步骤02 录入数据并求和 将供货单位 应付金额按照实际情况一一录入 在A14输入 合计 在B14编制求和公 在Excel中小数点无法输入 按小数点 显示的却是逗号 无论怎样设置选项都无济于事 该怎么办 55 应收应付款表格CHAPTER 3 式 选中B14单元格 在编辑栏中输入求和公式 SUM B3 B12 按回车键确认 步骤03 编制方案一的公式 现在需要编制公式实现方案一得功能 小于或等于1000元的账户一次性付清 大于 1000元的账户首次支付应付金额的50 选中C3单元格 在编辑栏中输入公式 IF B 3 1000 B3 ROUND B3 50 2 按回车键确认 选中C3单元格 将光标放在单元格右下角 当光标变成黑十字形状时 按住鼠标左 键不放 向下拖动鼠标到C12松开 完成C4 C12单元格区域的公式复制 这里我们用到了IF函数和ROUND函数 这里做简单说明 根据逻辑值 B3 1000 如果B3单元格中的数字小于或等于 1000 则返回B3单元格中的数字 如果B3单元格中的数字大于1000 则 返回B3单元格中庶子的50 并且这个返回的数字四舍五入到小数点后两 位 ROUND函数就是这个作用 小知识 步骤04 方案一求和 在Windows的控制面板中 点击 区域设置 图标 在弹出的 区域设置属性 对话面板上在 区域设置 里选择 中文 中国 在 区域设置属性 对话面板上在 数字 属性里把小数点改为 56 财务管理必会Excel应用100例办公室之 选中C13单元格 在编辑栏中输入公式 SUM C3 C12 按回车键确认 现在 方案一的应付款明细 和公司首付款的总金额就出来了 步骤05 编辑方案二的公式 选中D3单元格 在编辑栏中输入公式 IF B3 3000 B3 ROUND B3 40 2 按回车键确认 选中D3单元格 将光标放在单元格右下角 当光标变成黑十字 形状时 按住鼠标左键不放 向下拖动鼠标到D12松开 完成D4 D12单元格区域的 公式复制 步骤06 编制求和公式 选中D13单元格 在编辑栏中输入公式 SUM D3 D12 按回车键确认 现在 方案二的应付款明细 和公司首付款的总金额也计算好了 步骤07 完善表格 设置表格边框线 适当调整行高列宽 调整标题的字体 字号和文字居中显示等 现在 两种方案的应付款表就完成了 如何快速选取特定区域 57 应收应付款表格CHAPTER 3 3 3 应付款图表 对于财务人员来说 数字是有魔力的 搭建出一座财务的高塔 非常神奇 但对于 非财务人员来说 数字却是枯燥的 乏味的 死板的 表哥 表姐辛苦汇总而来的表格 对于某些人来说 有种遥远而陌生的感觉 但图表却不同 形象且直观地反应出一些客 观事实 对于那些对数字不感冒的人 图表是拉近距离非常好的表现形式 Excel就有非 常丰富的图表绘制工具 通过各种几何图形和色彩传达着信息 下面 我们就以应付款 明细表为例 为大家讲解图表的绘制 图表效果图 使用F5键可以快速选取特定区域 例如 要选取A2 A1000 最简便的方法是按F5键 出现 定位 窗口 在 引用 栏内输入需选取的区域A2 A1000 58 财务管理必会Excel应用100例办公室之 步骤01 指定数据源 选中A3单元格 按住Shift键盘不放 再点击B12单元格 就选中了用于绘图 的数据 步骤02 柱形图 选择 插入 图表 柱形图 簇状柱形图 也就是柱形图的 第一个 步骤03 标准的柱形图 这时 Excel自动生成了一个标准的簇状柱形图 样式也非常简单 仔细观察 会发 现Excel上方快捷按钮变成了图表工具 如何快速返回选中区域 59 应收应付款表格CHAPTER 3 步骤04 修改图表样式 在图表工具的 图表样式 中 我们可以对柱状图的颜色和形状进行选择 这里我 们选择了深紫色且柱子呈立体效果的 样式30 步骤05 调整柱体尺寸 在图表工具 格式 大小 将柱体的尺寸修改为22厘米 按Ctr BacksPae 即退格键 60 财务管理必会Excel应用100例办公室之 步骤06 设置坐标轴格式 在图表工具 布局 坐标轴 对横竖坐 标轴的线条颜色 线型 对齐方式等进行简 单设置 步骤07 添加数据标签 如何进行强制换行 61 应收应付款表格CHAPTER 3 用鼠标点击图标中的柱子 这个时候 所有柱子的四角就多了一些小圆圈 将光标 放到小圆圈上 单击鼠标右键 弹出快捷菜单 在快捷菜单中点击 添加数据标签 现在 每个柱子的头顶就多了一行数据 这正是我们想要的直观感觉 步骤08 修改数据列名称 在步骤七的右键菜单中 点击 选择数据源 点击 图例项 系列 中的 编 辑 按钮 弹出 编辑数据系列 对话框 在系列名称中 将 系列1 修改为 应付金 额 点击确定按钮 步骤09 删除图表标题 现在 图表的正上方多了一个标题 应付金额 这个标题是我们所不需要的 双 击标题选中该标题 直接将文字删除即可 步骤10 调整图表位置 在图标上点击鼠标右键 选择 设置绘图区格式 在弹出的对话框中 可以为绘 图区设置填充色等 现在 图表已经完成了 用鼠标拖动到合适的位置 还可以对图表 的宽度和高度进行调整 在单元格中按下 Alt Enter 组合键 即可立即换行 62 财务管理必会Excel应用100例办公室之 3 4 到期示意图 在公司财务工作中 长期会使用承兑汇票 承兑汇票到期后需要用现金偿还 这张 到期示意图 可以清楚显示到期偿还的金额 还有到规定日期还剩余多少天数 相关人 员从这张表可以随时掌握承兑汇票的现状 到期示意图效果图 步骤01 新建表格并录入数据 启动Excel2007新建一个工作簿 将Sheet1改名为 到期示意表 在第一行单元格 输入标题 并完成A2 E20单元格区域的数据录入 超过15位数字如何输入 63 应收应付款表格CHAPTER 3 步骤02 输入给定日期 选中H2单元格 输入给定日期 2009 2 28 作为到期示意的日期判断标准 适当 调整列宽 保证所有数据完整显示 步骤03 编制到期示意公式 有两种方法可以解决 1 将单元格设置为文本 2 在输入数字前先输入 64 财务管理必会Excel应用100例办公室之 选中F2单元格 在编辑栏中输入公式 IF AND D2 H 20 D2 H 2 0 按回车键确认 将光标放在F2单元格的右下角 当光标变成黑十字形状时 按住鼠标左键不放 向下拖动鼠标到F20单元格松开 就完成了改列公式的复制 步骤04 到期示意 以给定日期为标准 未来30天内到期的汇票记录显示剩余天数 不满足条件的汇票 记录 如已经过期的则显示为 0 步骤05 插入SUMIF函数 现在 我们来编制汇总金额的公式 选中H6单元格 点击 开始 编辑 符号 边的下拉列表按钮 其他函数 弹出 插入函数 对话框 在 选择类别 中选择 数 学与三角函数 在 选择函数 列表中选择 SUMIF 函数 点击确定 步骤06 输入函数各项参数 弹出 函数参数 对话框 将光标放到 Range 文本框中 用鼠标在工作表中选中 F2 F20单元格区域 这是条件判断的区域 在 Criteria 文本框中输入 0 这是 条件 将光标定位到 Sum range 文本框 然后用鼠标在工作表中选定E2 E20单元格区 域 这是求和区域 点击确认按钮 如果隐藏了B列 如果让它显示出来 65 应收应付款表格CHAPTER 3 步骤07 生成汇总金额 这时 H6单元格自动生成了公式 SUMIF F2 F20 0 E2 E20 并计算出汇 总金额 知识点 SUMIF函数 SUMIF函数将根据指定的条件对若干个单元格求和 函数语法 SUMIF range criteria sum range range 为用于条件判断的单元格区域 criteria 为确定哪些单元格将被相加求和的条件 其形式可以为数字 表达式或文本 sum range 是需要求和的实际单元格 函数说明 只有在区域中相应的单元格符合条件的情况下sum range中的单元格才可求和 如果 忽略了sum range 则对区域中的单元格求和 本例公式说明 其各个参数值指定SUMIF函数从F2 F20单元格区域查找大于零的记录 并对E列中同 一行的相应单元格的数值进行汇总 F列是已经计算得到的到期剩余天数 E列是该汇票 的金额 通过这样的条件求和 即可得到30天内即将到期的汇票总金额 步骤08 完善表格 表格到此已经实现了功能 现在需要进一步美化 设置表格标题的字体 字号和填 充颜色 同时为了突出显示给定日期和将到期金额 选中H2和H6单元格 在 设置单元 格格式 中为这两个单元格选择一个醒目的填充色 选中A到C列 点击右键 取消隐藏 也可以选中A到C列 双击选中任一列宽线或改变任一列宽 或者将鼠标 移到AC列之间 等鼠标变为双竖线时拖动之 66 财务管理必会Excel应用100例办公室之 表格中的0很影响表格的美观 可以通过设置取消零值的显示 点击Office按钮 Excel选项 高级 此工作表的显示选项 去掉复选项 在具有零值的单元格中显示零 确定 步骤09 到期提示的另一种提示方法 在上面得到期提示表中 到期提示是到给定日期剩余的天数 如果你不想看到还有 多少天 而只是需要一个更明显的字样 提示到期了 那么 你可以通过下面的设置实 现 选择F2 F20单元格区域 点击鼠标右键弹出快捷菜单 选择 设置单元格格式 数字 自定义 然后在右边的 类型 列表中选择 0 到 期 G 通用格式 点击 确定 步骤10 到期提示 现在 可以看到到期提示发生了变化 喜欢哪一种方法 全凭个人喜欢 如何EXCEL中实现行列互换 67 应收应付款表格CHAPTER 3 3 5 到期示意表的提醒功能 尽管非常用心 非常在意 你也不可能清楚记得每一件事 但电脑还可以自动对重 要的事情进行提示 比如 到期应付款项 可以自动填充颜色来提醒财务人员 更方便 查看 就上面例子 按照给定日期 自动对将要到期的汇票