Excel会计与财务管理日常应用傻瓜书

01 Excel会计与财务管理入门
1.1 Excel在会计中的应用····························2
1.1.1 认识会计电算化······································2
1.1.2 会计做账的一般流程······························2
1.1.3 使用Excel 进行账务管理·······················2
1.2 Excel在财务管理中的应用····················3
1.2.1 Excel 与财务软件的区别························3
1.2.2 使用Excel 管理与分析财务数据··········3
1.3 Excel 2013基本操作·····························6
1.3.1 启动与退出Excel 2013···························6
1.3.2 认识Excel 2013 工作界面······················7
1.3.3 Excel 2013 的新增功能···························8
1.3.4 自定义工作界面····································10
1.4 工作簿的基本操作································13
1.4.1 新建工作簿············································13
1.4.2 保存工作簿············································14
1.4.3 打开工作簿············································15
1.4.4 关闭工作簿············································16
1.4.5 保护工作簿············································17
1.5 工作表的基本操作································18
1.5.1 新建和删除工作表································18
1.5.2 移动和复制工作表································21
1.5.3 重命名工作表········································23
1.5.4 保护工作表············································23
1.5.5 拆分和冻结工作表································24

02 财务数据的输入与编辑
2.1 单元格操作···········································28
2.1.1 选择单元格············································28
2.1.2 插入与删除单元格································29
2.1.3 合并与拆分单元格································31
2.1.4 保护单元格···········································32
2.2 输入与编辑财务数据··························· 32
2.2.1 输入数据···············································33
2.2.2 编辑数据···············································40
2.3 设置单元格格式··································· 44
2.3.1 设置文字格式·······································44
2.3.2 调整行高与列宽···································45
2.3.3 设置单元格的对齐方式·······················46
2.3.4 设置边框和底纹···································47
2.4 设置表格样式······································ 50
2.4.1 使用单元格样式···································50
2.4.2 使用条件格式·······································51
2.4.3 使用表格样式·······································52

03 公式和函数的应用
3.1 认识公式和函数··································· 56
3.1.1 公式的应用规则···································56
3.1.2 函数的组成与分类·······························57
3.2 公式的应用·········································· 58
3.2.1 输入公式···············································58
3.2.2 编辑公式···············································62
3.2.3 审核公式···············································66
3.3 函数的应用·········································· 67
3.3.1 插入函数···············································67
3.3.2 函数嵌套···············································69
3.3.3 常用函数的应用···································69
3.4 单元格的引用······································ 74
3.4.1 引用样式···············································74
3.4.2 相对引用···············································74
3.4.3 绝对引用···············································75
3.4.4 混合引用···············································75

04 财务数据图表的应用
4.1 图表的结构与类型································78
4.1.1 图表的结构··········································· 78
4.1.2 图表的类型··········································· 78
4.2 创建财务数据图表································80
4.2.1 创建图表··············································· 81
4.2.2 创建组合图表······································· 81
4.3 编辑图表··············································83
4.3.1 修改图表位置······································· 83
4.3.2 更改图表类型······································· 84
4.3.3 调整图表大小······································· 84
4.3.4 美化图表样式······································· 85
4.3.5 设置图表布局······································· 87
4.4 设置图表标签·······································87
4.4.1 设置图表标题······································· 87
4.4.2 设置图表图例······································· 88
4.4.3 设置图表坐标轴··································· 89
4.5 设置图表格式·······································90
4.5.1 设置图表区··········································· 90
4.5.2 设置绘图区··········································· 92
4.6 添加趋势线和误差线····························93
4.6.1 添加趋势线··········································· 93
4.6.2 添加误差线··········································· 94

05 财务数据排序、筛选与汇总
5.1 对数据进行排序···································98
5.1.1 简单排序··············································· 98
5.1.2 多关键字排序······································· 99
5.1.3 自定义排序条件································· 100
5.2 对数据进行筛选·································101
5.2.1 自动筛选数据····································· 101
5.2.2 自定义筛选数据································· 102
5.2.3 高级筛选············································· 103
5.3 对数据进行分类汇总··························105
5.3.1 创建分类汇总····································· 105
5.3.2 删除分类汇总····································· 106
5.4 汇总多个工作表中的数据···················· 106
5.4.1 按位置合并计算································· 106
5.4.2 按分类合并计算································· 108

06 应用数据透视表与数据透视图
6.1 数据透视表的基本操作·······················112
6.1.1 数据透视表的用途······························112
6.1.2 创建数据透视表··································112
6.1.3 更改数据源··········································113
6.1.4 删除数据透视表··································114
6.2 编辑数据透视表··································115
6.2.1 更改数据透视表布局··························115
6.2.2 更改数据透视表样式··························116
6.3 使用数据透视图分析数据···················118
6.3.1 认识数据透视图··································118
6.3.2 创建数据透视图··································118
6.3.3 为数据透视图添加数据标签············· 120
6.3.4 添加或删除数据透视图字段············· 120
6.3.5 筛选数据透视图中的数据················· 121
6.3.6 刷新数据透视图································· 122
6.3.7 删除数据透视图································· 123
6.4 编辑数据透视图································· 124
6.4.1 更改数据透视图类型和布局············· 124
6.4.2 更改数据透视图样式························· 125

07 员工薪酬管理
7.1 制作员工基本信息表························· 128
7.1.1 制作员工基本信息表························· 128
7.1.2 设置表格格式····································· 129
7.1.3 制作员工当月信息表························· 130
7.2 制作员工工资明细表························· 132
7.2.1 创建员工工资表框架························· 132
7.2.2 计算员工应得工资····························· 133
7.2.3 计算员工出勤工资····························· 137
7.2.4 计算代扣社保和公积金······················138
7.2.5 计算员工实发工资······························140

08 制作凭证记录表
8.1 制作会计科目表·································142
8.1.1 创建会计科目表的框架······················142
8.1.2 冻结窗格··············································143
8.2 制作记账凭证·····································144
8.2.1 导入数据··············································144
8.2.2 计算借方金额······································145
8.2.3 计算贷方金额······································148
8.3 制作凭证明细表·································149
8.3.1 创建凭证明细表的框架······················149
8.3.2 输入表格数据······································151
8.3.3 试算平衡··············································153
8.4 制作总账表·········································155

09 制作财务报表
9.1 制作资产负债表·································160
9.2 制作利润表·········································167
9.3 制作现金流量表·································170

10 流动资产管理
10.1 货币资金管理···································180
10.1.1 创建现金持有量分析表····················180
10.1.2 计算最佳现金持有量························181
10.2 交易性金融资产管理························182
10.2.1 交易性金融资产购入核算················182
10.2.2 交易性金融资产处置核算················184
10.3 存货管理··········································185
10.3.1 创建存货管理表的框架····················185
10.3.2 计算产品的各项明细数据················187
10.3.3 制作存货目录表································191
10.3.4 创建“返回”超链接························193

11 固定资产管理
11.1 制作固定资产管理表························ 196
11.1.1 创建固定资产管理表框架···············196
11.1.2 添加固定资产记录····························198
11.1.3 计算固定资产折旧····························199
11.2 制作固定资产清理表························ 201
11.3 制作固定资产变动单························ 204
11.3.1 制作固定资产变动单·······················204
11.3.2 绘制并设置组合框····························206
11.3.3 引用数据············································208
11.4 固定资产折旧的汇总处理················· 209
11.4.1 插入数据透视表································209
11.4.2 创建数据透视图································210

12 财务分析管理
12.1 制作财务比率分析表······················· 214
12.2 财务趋势分析·································· 217
12.3 财务比较分析·································· 221
12.3.1 制作资产负债表环比分析表···········221
12.3.2 制作资产负债表结构分析表···········222
12.4 财务综合分析·································· 224
12.4.1 制作财务比率综合分析表···············224
12.4.2 制作杜邦分析表·······························226

13 产品库存管理
13.1 制作基本资料代码表······················· 232
13.2 产品入库管理·································· 235
13.2.1 创建产品入库表的框架···················235
13.2.2 引用并计算数据·······························237
13.3 产品出库管理·································· 239
13.4 库存总账管理·································· 242
13.4.1 创建库存管理表·······························242
13.4.2 录入本期入库与出库数据···············244
13.4.3 计算期末库存··································· 245

14 财务预算管理
14.1 日常财务预算···································248
14.1.1 制作销售预算表······························· 248
14.1.2 制作生产预算表······························· 250
14.1.3 制作直接材料和采购预算表··········· 251
14.1.4 制作直接人工成本预算表··············· 254
14.1.5 制作制造费用预算表······················· 255
14.1.6 制作成本预算表······························· 255
14.1.7 制作销售及管理费用预算表··········· 257
14.2 现金预算········································· 258
14.2.1 引用日常预算表中的数据··············· 258
14.2.2 预算现金··········································· 260
14.3 利润预算········································· 261

15 财务预测管理
15.1 销售预测········································· 266
15.2 利润预测········································· 268
15.3 成本预测········································· 275