Excel财务与会计活用范例大辞典

第一篇 Excel实用技巧篇
Chapter 01 财务人员必备的财务单据制作技巧 1
技巧001 如何实现新建的日记账自动保存在指定位置 2
技巧002 如何确保资产增加数据的准确录入 3
技巧003 将会计科目表固定在最近使用位置的最前面 5
技巧004 如何让财务单据的制作与打印更加得心应手 6
技巧005 使用密码保护工作底稿的结构 9
技巧006 将工资报表设置为只读模式 11
技巧007 如何自动对日记账进行备份 13
技巧008 巧用工作表标签颜色标记正在编辑的部门费用表 15
技巧009 利用工作组快速制作一式三联的借款单 16
技巧010 仅允许用户编辑收据中的填列位置 18
技巧011 如何避免他人填制差旅费报销单时修改计算公式 20
技巧012 巧用间隔填充的方式区分相邻员工的工资记录 23

Chapter 02 确保财务数据的快速填充与有效性 26
技巧013 在不影响计算结果的前提下为单价添加单位 27
技巧014 快速为订单记录添加连续的序号 28
技巧015 快速填充自定义的数据系列 30
技巧016 通过简写快速输入客户公司地址 32
技巧017 隐藏财务报表中的零值 34
技巧018 巧用替换功能将费用数据的格式修改为货币格式 36
技巧019 如何准确记录资产变更并查询在用的资产 39
技巧020 如何将固定资产增加表的表头始终显示 41
技巧021 将预算报表拆分为几部分以对比不同部分 43
技巧022 将期初数和期末数的数据约束在指定范围 44
技巧023 为费用统计表的部门数据提供输入选项 46
技巧024 确保订单编号的唯一性 48

Chapter 03 规范设置表格外观并图形化分析财务数据 51
技巧025 快速为记账凭证设置合适的格式 52
技巧026 如何将评定说明内容在单元格中分行显示 54
技巧027 快速将销售业绩表的行和列互换 56
技巧028 快速制作具有专业美化效果的费用管理表 58
技巧029 突出显示大于900000元的销售额 59
技巧030 在工资报表中突出显示排前三位的工资 61
技巧031 查看数据条的长短比较相邻单元格的业绩大小 62
技巧032 使用迷你图快速查看业绩的变化趋势 64
技巧033 在单元格中快速直观分析公司各月的盈亏状况 66
技巧034 如何让销售成本的变化趋势更直接 67
技巧035 快速调整财务报表中图表的大小和位置 69
技巧036 如何让销售成本数据波动变化更明显 71
技巧037 使用双坐标轴分析两组值差别较大的财务数据 73
技巧038 巧用趋势线让销售成本的变化趋势更加明显 75
技巧039 使用形状为在销售收入图表中添加注释 77
技巧040 如何在饼图中突出显示年度支出最小的项目 78
技巧041 巧妙处理每日营业额分析折线图中的断裂情况 80
技巧042 将没有营业额的日期从图表中去除 82

Chapter 04 各种财务清单与报表的打印技巧 85
技巧043 将多页显示的铺货率分析报表打印到一页上 86
技巧044 为多页的固定资产清单插入页码和公司名称 89
技巧045 如何在收据中添加“内部文档”水印 91
技巧046 打印财务表格中的部分数据 94
技巧047 将打印的多页固定资产清单的每页都添加表头 95
技巧048 使用分页符手动为待打印的工资条分页 97
技巧049 在一张纸中打印4份收据 99
技巧050 巧妙连续打印提成工资中不连续的工资记录 101
技巧051 通过照相机工具将不连续单元格区域打印到一页 102

Chapter 05 快速查看符合条件的财务数据 106
技巧052 按基本工资从高到低的顺序查看工资数据 107
技巧053 在费用统计表中使用多列数据排序 109
技巧054 按产品名称的指定顺序排序查看员工的提成工资 111
技巧055 按凭证号的升序查看数据但首列序号顺序不变 113
技巧056 按照姓名笔划顺序对查看员工的工资 114
技巧057 按类别的从大到小顺序查看项目的支出费用 116
技巧058 查看货物名称为“A4打印纸”的合同履行记录 118
技巧059 查看贷方金额为空的账务信息 120
技巧060 查看支出费用最大的前3条记录 121
技巧061 查看本季度发生的交易订单 123
技巧062 查看科目名称中包含“费用”文本的凭证记录 124
技巧063 查看每年每月1日的费用支出情况 126
技巧064 查看二级名称只有3个字的凭证记录 127
技巧065 查看多个约束条件查询的工资明细记录 128
技巧066 查看结款期为2013年7月的提货明细 130

Chapter 06 财务数据管理高级技巧 133
技巧067 删除重复计算的员工工资记录 134
技巧068 在凭证记录表中提取不重复的一级名称 135
技巧069 按一级名称汇总金额数据 137
技巧070 从摘要中提取会计科目 139
技巧071 查看片区和团队的销售总额 140
技巧072 按汇总数据分页打印各部门的工资 142
技巧073 快速隐藏和显示8月2日发生的银行日记账 144
技巧074 将员工10月~12月的销售额季度表中单独汇总 145
技巧075 汇总多表中存放位置不同的相同商品的销售总额 147
技巧076 在打印工作表时不打印其中的图表 149
技巧077 为期初余额添加输入提示文本 152
技巧078 根据数据源快速构建销量分析表格 153
技巧079 使用交叉透视表统计各员工各产品销售额的排名 155
技巧080 在数据透视表中统计所有产品的订单数量和订单数 157
技巧081 灵活查看某段交易时间内的产品订单数量和订单数 160
技巧082 在数据透视表中添加计算字段计算销售税率金 161
技巧083 在Excel中分析Access数据库中的计件数据 163
技巧084 将网页上股票分析数据导入到Excel 165
技巧085 在透视表中查看某结果对应的数据源记录 167
技巧086 在透视表中按公司单独打印其对应的欠款回款记录 168


第二篇 Excel函数拓展篇
Chapter 07 员工工资的计算与管理 171
◎工资基本项目计算 172
001 汇总员工的所有工资项目得到员工的应发工资【SUM()】 172
源文件\第7章\应发工资.xlsx
002 已知工作年限计算员工的年限工资【IF()】 174
源文件\第7章\已知年限计算年限工资.xlsx
003 已知参工时间计算员工的年限工资【DATEDIF()】 176
源文件\第7章\已知参工时间计算年限工资.xlsx
004 已知产品件数和单价计算计件工资【SUMPRODUCT()】 179
源文件\第7章\计件工资.xlsx
005 按照百分比计算提成工资【IF()】 181
源文件\第7章\按百分比计算提成工资.xlsx
006 每2000元提成50元的提成工资计算【FLOOR()】 183
源文件\第7章\单位销售额提成工资计算.xlsx
007 等额多梯度提成方式的提成工资计算【CHOOSE()】 185
源文件\第7章\等额多梯度提成计算.xlsx
008 不等额多梯度提成方式的提成工资计算【VLOOKUP()】 189
源文件\第7章\不等额多梯度提成计算.xlsx
009 将员工的提成工资保留到“角”位【ROUND()】 191
源文件\第7章\处理提成工资.xlsx
010 计算员工加班所用时间【HOUR()】【MINUTE()】 193
源文件\第7章\加班记录统计表.xlsx
011 将加班时间“四舍五入”到0.5的倍数【MROUND()】 196
源文件\第7章\加班工资.xlsx
012 统计员工出勤和缺勤天数并计算项目补助【COUNTIF()】 198
源文件\第7章\项目考勤.xlsx
013 计算员工的考勤扣除【TRANSEPOSE()】 202
源文件\第7章\考勤扣除.xlsx
014 计算员工的应纳税所得额【IF()】 205
源文件\第7章\应纳税所得额.xlsx
015 计算员工应缴纳的个人所得税【VLOOKUP()】 207
源文件\第7章\个人所得税.xlsx
◎工资查询 209
016 根据员工的姓名查询工资数据【COLUMN()】 209
源文件\第7章\员工工资表.xlsx
017 查询提成最高的员工工资数据【OFFSET()】【MATCH()】 212
源文件\第7章\获取提成最多员工数据.xlsx
◎工资统计分析 215
018 获取员工工资的平均值【AVERAGE()】 215
源文件\第7章\平均工资.xlsx
019 计算各部门发放的工资的总和【SUMIF()】 216
源文件\第7章\各部门工资总额.xlsx
020 统计工资达到一定水平的员工人数【COUNTIF()】 218
源文件\第7章\员工工资分布.xlsx
021 获取所有员工的最高和最低工资【MAX()】【MIN()】 220
源文件\第7章\员工工资最值.xlsx
022 突出显示小于平均工资2/3的员工工资【AVERAGE()】 222
源文件\第7章\突出显示低工资.xlsx
◎员工工资的发放 224
023 为员工工资表添加一个打印时间【TEXT()】 224
源文件\第7章\打印时间.xlsx
024 制作工资条【OFFSET()】 227
源文件\第7章\工资条.xlsx
025 计算发放工资所需各面值货币数量【INT()】 229
源文件\第7章\现金发放工资.xlsx

Chapter 08 日常财务数据管理 235
◎办公财务数据管理 236
026 为办公费用添加计数编号【ROW()】 236
源文件\第8章\办公费用统计表.xlsx
027 计算领用的办公用品的价值【PRODUCT()】 237
源文件\第8章\办公用品领用记录表.xlsx
028 计算车辆使用报销费用【IF()】 239
源文件\第8章\车辆使用管理.xlsx
029 计算车辆使用时给予驾驶员的补助费用【INT()】 241
源文件\第8章\驾驶员补助计算.xlsx
030 忽略隐藏行统计本月办公经费【SUBTOTAL()】 242
源文件\第8章\办公费用统计.xlsx
◎停车费用计算 245
031 计算停车的天数【DAY()】 245
源文件\第8章\计算停车的天数.xlsx
032 计算停车的小时数【TEXT()】 248
源文件\第8章\计算停车小时数.xlsx
033 计算停车的分钟数【TEXT()】 251
源文件\第8章\计算停车分钟数.xlsx
◎简易收支记账 254
034 在收支表中计算结余【SUM()】 254
源文件\第8章\收支表.xlsx
035 汇总收支明细表中的收支数据【IF()】 257
源文件\第8章\汇总每月收支.xlsx
◎收据填写 261
036 将金额分散填充到对应的单位中【LEFT】【RIGHT()】 261
源文件\第8章\收据.xlsx
037 计算收据总金额并将其表示为人民币大写【TEXT()】 265
源文件\第8章\人民币大写.xlsx
038 制作收支瀑布图【SUM()】 268
源文件\第8章\收支瀑布图.xlsx

Chapter 09 生产企业的本量利计算 271
◎生产成本分析管理 272
039 计算各产品的平均生产成本【AVERAGE()】 272
源文件\第9章\季度平均成本.xlsx
040 获取材料在生产各产品的3个最小消耗量【SMALL()】 273
源文件\第9章\材料消耗分析.xlsx
041 跟踪最近3天产品的生产总量【TODAY()】 275
源文件\第9章\最近3天产量跟踪.xlsx
042 获取车间员工的最大产量【INDIRECT】【ADDRESS()】 278
源文件\第9章\车间产量分析.xlsx
043 查询材料采购最为频繁的车间【MODE()】 280
源文件\第9章\材料采购表.xlsx
044 计算各车间的平均生产成本【DAVERAGE()】 282
源文件\第9章\各车间生产成本.xlsx
045 制作可比较任意产品产量的柱形图【OFFSET()】 284
源文件\第9章\产量比较.xlsx
046 罗列铝材下料方案并选择最优方案【SUMPRODUCT()】 291
源文件\第9章\铝材下料方案分析.xlsx
047 确定使利润最大化的产品生产方案【SUMPRODUCT()】 298
源文件\第9章\生产方案分析.xlsx
◎销售量分析与计算 303
048 制作产品热销榜【LARGE()】 303
源文件\第9章\产品热销榜.xlsx
049 计算2012年之前某空调的平均出厂价【YEAR()】 306
源文件\第9章\空调出厂价.xlsx
050 计算本月需要交货的产品总价值【MONTH()】 309
源文件\第9章\产品订单表.xlsx
051 使用简易图表表示产品的销量情况【REPT()】 310
源文件\第9章\副食品销量表.xlsx
052 计算单晶的交易额【SUMIF()】 313
源文件\第9章\订单明细表.xlsx
053 计算2011至2015年产品的经销商数量【MATCH()】 315
源文件\第9章\经销商名单.xlsx
054 确定产品的最佳运输方案【SUMPRODUCT()】 317
源文件\第9章\运输方案分析.xlsx
◎货款收取与利润计算 321
055 计算货款的最后日期【EDATE()】 321
源文件\第9章\提货明细.xlsx
056 计算货款的结款期【EOMONTH()】 324
源文件\第9章\结款日期.xlsx
057 在提货明细表中添加结款期限提醒【TODAY()】 326
源文件\第9章\结款倒计时.xlsx
058 使用不同颜色标记剩余天数不同的提货记录【TODAY()】 329
源文件\第9章\结款提醒.xlsx

Chapter 10 销售企业的进销存分析 336
◎进货分析与计算 337
059 查询指定厂家提供的某种产品的价格【VLOOKUP()】 337
源文件\第10章\供货价格.xlsx
060 从到货产品中随机抽检5种产品【RANDBETWEEN()】 343
源文件\第10章\产品抽检.xlsx
◎销售分析与计算 347
061 统计各个利润区间的商品数量【FREQUENCY()】 347
源文件\第10章\利润分析.xlsx
062 根据单价表和销量表汇总销售额【OFFSET()】 350
源文件\第10章\销售额汇总.xlsx
063 查找所有包含指定信息的商品【FIND()】 352
源文件\第10章\查找特定商品.xlsx
064 获取商品的最新零售价【SUBSTITUTE()】 357
源文件\第10章\单价更新记录.xlsx
065 制作商品的标价标签【OFFSET()】 361
源文件\第10章\标价标签.xlsx
066 统计会员在项链和戒指上的总消费【SUMIF()】 363
源文件\第10章\统计会员在项链和手镯上的总消费.xlsx
067 每10中商品汇总一次利润【SUM()】 365
源文件\第10章\利润汇总.xlsx
068 以“万元”为单位统计销售额【ROUND()】 367
源文件\第10章\商品销售额.xlsx
069 存在不同计量单位时汇总销售额【IF()】 369
源文件\第10章\食材销售额.xlsx
070 计算打折期间的总营业额【MMULT()】 371
源文件\第10章\打折销售.xlsx
071 根据产品名称制作产品编号【VLOOKUP()】 373
源文件\第10章\商品编号.xlsx
072 根据销售员姓名查询指定月份销售业绩【HLOOKUP()】 376
源文件\第10章\销售业绩.xlsx
073 制作电器的周营业额排行榜【MATCH()】 379
源文件\第10章\电器营业额.xlsx
074 制作区域销量动态子母饼图【OFFSET()】 383
源文件\第10章\区域销量统计.xlsx
◎库存分析与计算 391
075 判断是否需要进货并换行显示判断结果【CHAR()】 391
源文件\第10章\月度库存管理.xlsx
076 获取产品包装的长宽高并计算库存体积【FIND()】 393
源文件\第10章\库存规格.xlsx
077 通过产品名称查询不同仓库中库存数量【INDIRECT()】 398
源文件\第10章\库存查询.xlsx
078 将已输入供应单位显示在待选下拉列表中【OFFSET()】 401
源文件\第10章\入库明细表.xlsx
079 在入库记录表中自动记录入库时间【NOW()】 404
源文件\第10章\入库记录表.xlsx
080 使用细分柱形图展示产品库存量【MAX()】 406
源文件\第10章\库存分析.xlsx

Chapter 11 资金时间价值的计算 410
◎投资预算 411
081 公司每月存入一笔钱，年终有多少钱【FV()】 411
源文件\第11章\存款分析之零存整取.xlsx
082 公司将一笔钱存一年半之后有多少【FV()】 413
源文件\第11章\存款分析之整存整取.xlsx
083 通过现值判断是否值得投资【PV()】 415
源文件\第11章\投资判断.xlsx
084 判断投资商铺在未来8年能否收回成本【NPV()】 416
源文件\第11章\商铺投资分析.xlsx
085 计算购买好设备至少使用多少年才划算【NPER()】 418
源文件\第11章\机器选购判断.xlsx
086 每年存入多少钱5年之后才有500万【FV()】 420
源文件\第11章\偿债基金.xlsx
087 每月还贷的本金相同，则每月应还贷多少【ISPMT()】 423
源文件\第11章\等额本金还贷.xlsx
◎方案选择 427
088 分析不同还款期数和年利率下每期还款的金额【PMT()】 427
源文件\第11章\分期付款分析.xlsx
089 贷款方案分析【PMT()】 429
源文件\第11章\贷款方案分析.xlsx
090 在投资额相同的情况下确定最佳投资方案【NPV()】 433
源文件\第11章\方案抉择.xlsx
091 在投资额不同的情况下确定最佳投资方案【NPV()】 435
源文件\第11章\项目选择.xlsx
◎收益率分析 437
092 通过内部收益率判断项目是否值得投资【IRR()】 437
源文件\第11章\项目投资分析.xlsx
093 考虑投资成本和在投资收益计算内部收益率【MIRR()】 441
源文件\第11章\再投资分析.xlsx
094 计算不定期现金流的内部收益率【XIRR()】 443
源文件\第11章\不定期现金流.xlsx

Chapter 12 固定资产折旧与维护 445
◎固定资产折旧 446
095 计算每期折旧额相近的固定资产的折旧额【SLN()】 446
源文件\第12章\直线折旧法.xlsx
096 根据固定资产的工作量计提折旧额【SLN()】 448
源文件\第12章\计提汽车本月折旧.xlsx
097 按照固定的百分比进行对固定资产进行折旧【DB()】 449
源文件\第12章\生产线折旧.xlsx
098 第一年从年中开始计提折旧额【DB()】 451
源文件\第12章\年中折旧.xlsx
099 使用双倍余额递减法加速计提资产折旧【DDB()】 455
源文件\第12章\双倍余额递减法.xlsx
100 使用年数总和法加速计提资产折旧【SYD()】 459
源文件\第12章\年数总和法.xlsx
101 使双倍余额递减法可以将应折旧额计提完【VDB()】 462
源文件\第12章\计提折旧.xlsx
102 判断加速折旧法对所得税的影响【SYD()】 464
源文件\第12章\分析折旧法的影响.xlsx
◎资产维护 469
103 提供可供选择机床维修人员配置方案【BINOM.DIST()】 469
源文件\第12章\维修人员配备方案.xlsx
104 分析资产的盘盈盘亏及数量【ABS()】 473
源文件\第12章\资产盘点.xlsx
显示全部信息