Excel會(huì)計(jì)與財(cái)務(wù)管理日常應(yīng)用傻瓜書(shū)

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

02 財(cái)務(wù)數(shù)據(jù)的輸入與編輯
2.1 單元格操作···········································28
2.1.1 選擇單元格············································28
2.1.2 插入與刪除單元格································29
2.1.3 合并與拆分單元格································31
2.1.4 保護(hù)單元格···········································32
2.2 輸入與編輯財(cái)務(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è)置單元格的對(duì)齊方式·······················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 認(rèn)識(shí)公式和函數(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 相對(duì)引用···············································74
3.4.3 絕對(duì)引用···············································75
3.4.4 混合引用···············································75

04 財(cái)務(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)建財(cái)務(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 添加趨勢(shì)線和誤差線····························93
4.6.1 添加趨勢(shì)線··········································· 93
4.6.2 添加誤差線··········································· 94

05 財(cái)務(wù)數(shù)據(jù)排序、篩選與匯總
5.1 對(duì)數(shù)據(jù)進(jìn)行排序···································98
5.1.1 簡(jiǎn)單排序··············································· 98
5.1.2 多關(guān)鍵字排序······································· 99
5.1.3 自定義排序條件································· 100
5.2 對(duì)數(shù)據(jù)進(jìn)行篩選·································101
5.2.1 自動(dòng)篩選數(shù)據(jù)····································· 101
5.2.2 自定義篩選數(shù)據(jù)································· 102
5.2.3 高級(jí)篩選············································· 103
5.3 對(duì)數(shù)據(jù)進(jìn)行分類匯總··························105
5.3.1 創(chuàng)建分類匯總····································· 105
5.3.2 刪除分類匯總····································· 106
5.4 匯總多個(gè)工作表中的數(shù)據(jù)···················· 106
5.4.1 按位置合并計(jì)算································· 106
5.4.2 按分類合并計(jì)算································· 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 認(rèn)識(shí)數(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 制作員工工資明細(xì)表························· 132
7.2.1 創(chuàng)建員工工資表框架························· 132
7.2.2 計(jì)算員工應(yīng)得工資····························· 133
7.2.3 計(jì)算員工出勤工資····························· 137
7.2.4 計(jì)算代扣社保和公積金······················138
7.2.5 計(jì)算員工實(shí)發(fā)工資······························140

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

09 制作財(cái)務(wù)報(bào)表
9.1 制作資產(chǎn)負(fù)債表·································160
9.2 制作利潤(rùn)表·········································167
9.3 制作現(xiàn)金流量表·································170

10 流動(dòng)資產(chǎn)管理
10.1 貨幣資金管理···································180
10.1.1 創(chuàng)建現(xiàn)金持有量分析表····················180
10.1.2 計(jì)算最佳現(xiàn)金持有量························181
10.2 交易性金融資產(chǎn)管理························182
10.2.1 交易性金融資產(chǎn)購(gòu)入核算················182
10.2.2 交易性金融資產(chǎn)處置核算················184
10.3 存貨管理··········································185
10.3.1 創(chuàng)建存貨管理表的框架····················185
10.3.2 計(jì)算產(chǎn)品的各項(xiàng)明細(xì)數(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 計(jì)算固定資產(chǎn)折舊····························199
11.2 制作固定資產(chǎn)清理表························ 201
11.3 制作固定資產(chǎn)變動(dòng)單························ 204
11.3.1 制作固定資產(chǎn)變動(dòng)單·······················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 財(cái)務(wù)分析管理
12.1 制作財(cái)務(wù)比率分析表······················· 214
12.2 財(cái)務(wù)趨勢(shì)分析·································· 217
12.3 財(cái)務(wù)比較分析·································· 221
12.3.1 制作資產(chǎn)負(fù)債表環(huán)比分析表···········221
12.3.2 制作資產(chǎn)負(fù)債表結(jié)構(gòu)分析表···········222
12.4 財(cái)務(wù)綜合分析·································· 224
12.4.1 制作財(cái)務(wù)比率綜合分析表···············224
12.4.2 制作杜邦分析表·······························226

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

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

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