Excel會計與財務(wù)管理日常應(yīng)用傻瓜書

01 Excel會計與財務(wù)管理入門
1.1 Excel在會計中的應(yīng)用····························2
1.1.1 認識會計電算化······································2
1.1.2 會計做賬的一般流程······························2
1.1.3 使用Excel 進行賬務(wù)管理·······················2
1.2 Excel在財務(wù)管理中的應(yīng)用····················3
1.2.1 Excel 與財務(wù)軟件的區(qū)別························3
1.2.2 使用Excel 管理與分析財務(wù)數(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 關(guān)閉工作簿············································16
1.4.5 保護工作簿············································17
1.5 工作表的基本操作································18
1.5.1 新建和刪除工作表································18
1.5.2 移動和復(fù)制工作表································21
1.5.3 重命名工作表········································23
1.5.4 保護工作表············································23
1.5.5 拆分和凍結(jié)工作表································24

02 財務(wù)數(shù)據(jù)的輸入與編輯
2.1 單元格操作···········································28
2.1.1 選擇單元格············································28
2.1.2 插入與刪除單元格································29
2.1.3 合并與拆分單元格································31
2.1.4 保護單元格···········································32
2.2 輸入與編輯財務(wù)數(shù)據(jù)··························· 32
2.2.1 輸入數(shù)據(jù)···············································33
2.2.2 編輯數(shù)據(jù)···············································40
2.3 設(shè)置單元格格式··································· 44
2.3.1 設(shè)置文字格式·······································44
2.3.2 調(diào)整行高與列寬···································45
2.3.3 設(shè)置單元格的對齊方式·······················46
2.3.4 設(shè)置邊框和底紋···································47
2.4 設(shè)置表格樣式······································ 50
2.4.1 使用單元格樣式···································50
2.4.2 使用條件格式·······································51
2.4.3 使用表格樣式·······································52

03 公式和函數(shù)的應(yīng)用
3.1 認識公式和函數(shù)··································· 56
3.1.1 公式的應(yīng)用規(guī)則···································56
3.1.2 函數(shù)的組成與分類·······························57
3.2 公式的應(yīng)用·········································· 58
3.2.1 輸入公式···············································58
3.2.2 編輯公式···············································62
3.2.3 審核公式···············································66
3.3 函數(shù)的應(yīng)用·········································· 67
3.3.1 插入函數(shù)···············································67
3.3.2 函數(shù)嵌套···············································69
3.3.3 常用函數(shù)的應(yīng)用···································69
3.4 單元格的引用······································ 74
3.4.1 引用樣式···············································74
3.4.2 相對引用···············································74
3.4.3 絕對引用···············································75
3.4.4 混合引用···············································75

04 財務(wù)數(shù)據(jù)圖表的應(yīng)用
4.1 圖表的結(jié)構(gòu)與類型································78
4.1.1 圖表的結(jié)構(gòu)··········································· 78
4.1.2 圖表的類型··········································· 78
4.2 創(chuàng)建財務(wù)數(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 設(shè)置圖表布局······································· 87
4.4 設(shè)置圖表標(biāo)簽·······································87
4.4.1 設(shè)置圖表標(biāo)題······································· 87
4.4.2 設(shè)置圖表圖例······································· 88
4.4.3 設(shè)置圖表坐標(biāo)軸··································· 89
4.5 設(shè)置圖表格式·······································90
4.5.1 設(shè)置圖表區(qū)··········································· 90
4.5.2 設(shè)置繪圖區(qū)··········································· 92
4.6 添加趨勢線和誤差線····························93
4.6.1 添加趨勢線··········································· 93
4.6.2 添加誤差線··········································· 94

05 財務(wù)數(shù)據(jù)排序、篩選與匯總
5.1 對數(shù)據(jù)進行排序···································98
5.1.1 簡單排序··············································· 98
5.1.2 多關(guān)鍵字排序······································· 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 應(yīng)用數(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ù)標(biāo)簽············· 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 設(shè)置表格格式····································· 129
7.1.3 制作員工當(dāng)月信息表························· 130
7.2 制作員工工資明細表························· 132
7.2.1 創(chuàng)建員工工資表框架························· 132
7.2.2 計算員工應(yīng)得工資····························· 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 凍結(jié)窗格··············································143
8.2 制作記賬憑證·····································144
8.2.1 導(dǎo)入數(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 制作財務(wù)報表
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 繪制并設(shè)置組合框····························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 財務(wù)分析管理
12.1 制作財務(wù)比率分析表······················· 214
12.2 財務(wù)趨勢分析·································· 217
12.3 財務(wù)比較分析·································· 221
12.3.1 制作資產(chǎn)負債表環(huán)比分析表···········221
12.3.2 制作資產(chǎn)負債表結(jié)構(gòu)分析表···········222
12.4 財務(wù)綜合分析·································· 224
12.4.1 制作財務(wù)比率綜合分析表···············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 財務(wù)預(yù)算管理
14.1 日常財務(wù)預(yù)算···································248
14.1.1 制作銷售預(yù)算表······························· 248
14.1.2 制作生產(chǎn)預(yù)算表······························· 250
14.1.3 制作直接材料和采購預(yù)算表··········· 251
14.1.4 制作直接人工成本預(yù)算表··············· 254
14.1.5 制作制造費用預(yù)算表······················· 255
14.1.6 制作成本預(yù)算表······························· 255
14.1.7 制作銷售及管理費用預(yù)算表··········· 257
14.2 現(xiàn)金預(yù)算········································· 258
14.2.1 引用日常預(yù)算表中的數(shù)據(jù)··············· 258
14.2.2 預(yù)算現(xiàn)金··········································· 260
14.3 利潤預(yù)算········································· 261

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