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 與財務軟件的區(qū)別························3
1.2.2 使用Excel 管理與分析財務數(shù)據(jù)··········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 財務數(shù)據(jù)的輸入與編輯
2.1 單元格操作···········································28
2.1.1 選擇單元格············································28
2.1.2 插入與刪除單元格································29
2.1.3 合并與拆分單元格································31
2.1.4 保護單元格···········································32
2.2 輸入與編輯財務數(shù)據(jù)··························· 32
2.2.1 輸入數(shù)據(jù)···············································33
2.2.2 編輯數(shù)據(jù)···············································40
2.3 設置單元格格式··································· 44
2.3.1 設置文字格式·······································44
2.3.2 調(diào)整行高與列寬···································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 公式和函數(shù)的應用
3.1 認識公式和函數(shù)··································· 56
3.1.1 公式的應用規(guī)則···································56
3.1.2 函數(shù)的組成與分類·······························57
3.2 公式的應用·········································· 58
3.2.1 輸入公式···············································58
3.2.2 編輯公式···············································62
3.2.3 審核公式···············································66
3.3 函數(shù)的應用·········································· 67
3.3.1 插入函數(shù)···············································67
3.3.2 函數(shù)嵌套···············································69
3.3.3 常用函數(shù)的應用···································69
3.4 單元格的引用······································ 74
3.4.1 引用樣式···············································74
3.4.2 相對引用···············································74
3.4.3 絕對引用···············································75
3.4.4 混合引用···············································75

04 財務數(shù)據(jù)圖表的應用
4.1 圖表的結構與類型································78
4.1.1 圖表的結構··········································· 78
4.1.2 圖表的類型··········································· 78
4.2 創(chuàng)建財務數(shù)據(jù)圖表································80
4.2.1 創(chuàng)建圖表··············································· 81
4.2.2 創(chuàng)建組合圖表······································· 81
4.3 編輯圖表··············································83
4.3.1 修改圖表位置······································· 83
4.3.2 更改圖表類型······································· 84
4.3.3 調(diào)整圖表大小······································· 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 設置圖表區(qū)··········································· 90
4.5.2 設置繪圖區(qū)··········································· 92
4.6 添加趨勢線和誤差線····························93
4.6.1 添加趨勢線··········································· 93
4.6.2 添加誤差線··········································· 94

05 財務數(shù)據(jù)排序、篩選與匯總
5.1 對數(shù)據(jù)進行排序···································98
5.1.1 簡單排序··············································· 98
5.1.2 多關鍵字排序······································· 99
5.1.3 自定義排序條件································· 100
5.2 對數(shù)據(jù)進行篩選·································101
5.2.1 自動篩選數(shù)據(jù)····································· 101
5.2.2 自定義篩選數(shù)據(jù)································· 102
5.2.3 高級篩選············································· 103
5.3 對數(shù)據(jù)進行分類匯總··························105
5.3.1 創(chuàng)建分類匯總····································· 105
5.3.2 刪除分類匯總····································· 106
5.4 匯總多個工作表中的數(shù)據(jù)···················· 106
5.4.1 按位置合并計算································· 106
5.4.2 按分類合并計算································· 108

06 應用數(shù)據(jù)透視表與數(shù)據(jù)透視圖
6.1 數(shù)據(jù)透視表的基本操作·······················112
6.1.1 數(shù)據(jù)透視表的用途······························112
6.1.2 創(chuàng)建數(shù)據(jù)透視表··································112
6.1.3 更改數(shù)據(jù)源··········································113
6.1.4 刪除數(shù)據(jù)透視表··································114
6.2 編輯數(shù)據(jù)透視表··································115
6.2.1 更改數(shù)據(jù)透視表布局··························115
6.2.2 更改數(shù)據(jù)透視表樣式··························116
6.3 使用數(shù)據(jù)透視圖分析數(shù)據(jù)···················118
6.3.1 認識數(shù)據(jù)透視圖··································118
6.3.2 創(chuàng)建數(shù)據(jù)透視圖··································118
6.3.3 為數(shù)據(jù)透視圖添加數(shù)據(jù)標簽············· 120
6.3.4 添加或刪除數(shù)據(jù)透視圖字段············· 120
6.3.5 篩選數(shù)據(jù)透視圖中的數(shù)據(jù)················· 121
6.3.6 刷新數(shù)據(jù)透視圖································· 122
6.3.7 刪除數(shù)據(jù)透視圖································· 123
6.4 編輯數(shù)據(jù)透視圖································· 124
6.4.1 更改數(shù)據(jù)透視圖類型和布局············· 124
6.4.2 更改數(shù)據(jù)透視圖樣式························· 125

07 員工薪酬管理
7.1 制作員工基本信息表························· 128
7.1.1 制作員工基本信息表························· 128
7.1.2 設置表格格式····································· 129
7.1.3 制作員工當月信息表························· 130
7.2 制作員工工資明細表························· 132
7.2.1 創(chuàng)建員工工資表框架························· 132
7.2.2 計算員工應得工資····························· 133
7.2.3 計算員工出勤工資····························· 137
7.2.4 計算代扣社保和公積金······················138
7.2.5 計算員工實發(fā)工資······························140

08 制作憑證記錄表
8.1 制作會計科目表·································142
8.1.1 創(chuàng)建會計科目表的框架······················142
8.1.2 凍結窗格··············································143
8.2 制作記賬憑證·····································144
8.2.1 導入數(shù)據(jù)··············································144
8.2.2 計算借方金額······································145
8.2.3 計算貸方金額······································148
8.3 制作憑證明細表·································149
8.3.1 創(chuàng)建憑證明細表的框架······················149
8.3.2 輸入表格數(shù)據(jù)······································151
8.3.3 試算平衡··············································153
8.4 制作總賬表·········································155

09 制作財務報表
9.1 制作資產(chǎn)負債表·································160
9.2 制作利潤表·········································167
9.3 制作現(xiàn)金流量表·································170

10 流動資產(chǎn)管理
10.1 貨幣資金管理···································180
10.1.1 創(chuàng)建現(xiàn)金持有量分析表····················180
10.1.2 計算最佳現(xiàn)金持有量························181
10.2 交易性金融資產(chǎn)管理························182
10.2.1 交易性金融資產(chǎn)購入核算················182
10.2.2 交易性金融資產(chǎn)處置核算················184
10.3 存貨管理··········································185
10.3.1 創(chuàng)建存貨管理表的框架····················185
10.3.2 計算產(chǎn)品的各項明細數(shù)據(jù)················187
10.3.3 制作存貨目錄表································191
10.3.4 創(chuàng)建“返回”超鏈接························193

11 固定資產(chǎn)管理
11.1 制作固定資產(chǎn)管理表························ 196
11.1.1 創(chuàng)建固定資產(chǎn)管理表框架···············196
11.1.2 添加固定資產(chǎn)記錄····························198
11.1.3 計算固定資產(chǎn)折舊····························199
11.2 制作固定資產(chǎn)清理表························ 201
11.3 制作固定資產(chǎn)變動單························ 204
11.3.1 制作固定資產(chǎn)變動單·······················204
11.3.2 繪制并設置組合框····························206
11.3.3 引用數(shù)據(jù)············································208
11.4 固定資產(chǎn)折舊的匯總處理················· 209
11.4.1 插入數(shù)據(jù)透視表································209
11.4.2 創(chuàng)建數(shù)據(jù)透視圖································210

12 財務分析管理
12.1 制作財務比率分析表······················· 214
12.2 財務趨勢分析·································· 217
12.3 財務比較分析·································· 221
12.3.1 制作資產(chǎn)負債表環(huán)比分析表···········221
12.3.2 制作資產(chǎn)負債表結構分析表···········222
12.4 財務綜合分析·································· 224
12.4.1 制作財務比率綜合分析表···············224
12.4.2 制作杜邦分析表·······························226

13 產(chǎn)品庫存管理
13.1 制作基本資料代碼表······················· 232
13.2 產(chǎn)品入庫管理·································· 235
13.2.1 創(chuàng)建產(chǎn)品入庫表的框架···················235
13.2.2 引用并計算數(shù)據(jù)·······························237
13.3 產(chǎn)品出庫管理·································· 239
13.4 庫存總賬管理·································· 242
13.4.1 創(chuàng)建庫存管理表·······························242
13.4.2 錄入本期入庫與出庫數(shù)據(jù)···············244
13.4.3 計算期末庫存··································· 245

14 財務預算管理
14.1 日常財務預算···································248
14.1.1 制作銷售預算表······························· 248
14.1.2 制作生產(chǎn)預算表······························· 250
14.1.3 制作直接材料和采購預算表··········· 251
14.1.4 制作直接人工成本預算表··············· 254
14.1.5 制作制造費用預算表······················· 255
14.1.6 制作成本預算表······························· 255
14.1.7 制作銷售及管理費用預算表··········· 257
14.2 現(xiàn)金預算········································· 258
14.2.1 引用日常預算表中的數(shù)據(jù)··············· 258
14.2.2 預算現(xiàn)金··········································· 260
14.3 利潤預算········································· 261

15 財務預測管理
15.1 銷售預測········································· 266
15.2 利潤預測········································· 268
15.3 成本預測········································· 275