excel財(cái)務(wù)分析講義知識(shí)分享

1、Good is good, but better carries it.精益求精，善益求善。excel財(cái)務(wù)分析講義-EXCEL2003財(cái)務(wù)應(yīng)用入門與范例解析引言1.excel的作用：以表格形式進(jìn)行數(shù)據(jù)處理。（1）方法：記錄、計(jì)算、統(tǒng)計(jì)、分析（2）輸出：表格、圖表、數(shù)據(jù)庫（3）特點(diǎn)：數(shù)據(jù)批量處理、函數(shù)（基本、邏輯、統(tǒng)計(jì)、數(shù)據(jù)庫、財(cái)務(wù)）（4）應(yīng)用：文案辦公、銷售、生產(chǎn)、人事、財(cái)務(wù)、會(huì)計(jì)2.excel財(cái)務(wù)應(yīng)用：（1）日常財(cái)務(wù)：銷售、存貨、費(fèi)用、工資、所得稅（2）會(huì)計(jì)：憑證、科目匯總表、明細(xì)賬、會(huì)計(jì)報(bào)表（3）資產(chǎn)管理：固定資產(chǎn)、折舊（4）財(cái)務(wù)分析：方案優(yōu)選、生產(chǎn)成本預(yù)算、銷售趨勢預(yù)測、方差分析、統(tǒng)計(jì)3

2、其他應(yīng)用（1）財(cái)務(wù)分析：盈虧平衡、敏感性、情景、回歸、財(cái)務(wù)指標(biāo)（2）財(cái)務(wù)管理：財(cái)務(wù)預(yù)算、籌資決策、投資決策、利潤分配決策、營運(yùn)資金第一篇公司日常財(cái)務(wù)管理實(shí)例1：創(chuàng)建財(cái)務(wù)表格體系1.新建工作簿：公司常務(wù)、會(huì)計(jì)報(bào)表、資產(chǎn)管理、財(cái)務(wù)分析與預(yù)算2.打開工作簿：標(biāo)題欄、菜單欄、常用、名稱框、行號(hào)、列標(biāo)、編輯欄、工作表編輯區(qū)、任務(wù)窗格、工作表標(biāo)簽3.新建并復(fù)制（移動(dòng)）工作表：“插入”工作表、“ctrl”+工作表拖動(dòng)復(fù)制4.重命名工作表：右鍵產(chǎn)品生產(chǎn)記錄表、產(chǎn)品銷售記錄表、倉庫存貨管理、公司日常費(fèi)用表、員工工資表5.更改工作表標(biāo)簽顏色：右鍵天藍(lán)6.在工作簿之間復(fù)制（移動(dòng)）工作表：“ctrl”+公司日常費(fèi)用表

3、、產(chǎn)品銷售記錄表“編輯”“移動(dòng)或復(fù)制工作表”財(cái)務(wù)分析與預(yù)算7.自動(dòng)恢復(fù)：工具選項(xiàng)保存實(shí)例2：制作產(chǎn)品生產(chǎn)記錄表數(shù)據(jù)格式（貨幣、日期、文本）設(shè)置單元格格式，數(shù)據(jù)填充序列填充，行高、列寬1.輸入表名和表頭公司常務(wù)產(chǎn)品生產(chǎn)記錄表ENTER向下，SHIFT+ENTER向上，TAB向右，SHIFT+TAB向左2.快速填充編號(hào)：S001S008Z001Z008XH001XH008左鍵輸入名稱3.輸入生產(chǎn)數(shù)量：單元格格式先設(shè)置格式后輸入數(shù)量（無小數(shù)，千分位分隔）4.輸入單價(jià)：先輸入數(shù)值后設(shè)置格式（貨幣，小數(shù)2，￥）5.輸入單位：（1）十字光標(biāo)拖曳連續(xù)輸入相同的字（2）CTRL同時(shí)選取填入CTRL+ENTER

4、輸入不連續(xù)的（3）輸入一個(gè)CTRL+CCTRL選取多個(gè)CTRL+V（4）CTRL拖動(dòng)復(fù)制6.輸入生產(chǎn)日期：先輸入后設(shè)置格式CTRL+十字光標(biāo)拖曳復(fù)制，右鍵拖曳+“復(fù)制單元格”7.輸入生產(chǎn)單位：記憶功能8.調(diào)整行高與列寬：（1）選中區(qū)域右鍵“行高”、“列寬”（2）鼠標(biāo)拖動(dòng)9.小竅門：雙擊鼠標(biāo)左鍵快速輸入到末尾，CTRL+；輸入當(dāng)前日期CTRL+SHIFT+;輸入當(dāng)前時(shí)間，如果選定多張工作表，操作在所有表中出現(xiàn)，數(shù)字前加變?yōu)槲谋拘?0.相關(guān)知識(shí)：數(shù)據(jù)類型、工具選項(xiàng)編輯記憶輸入、按ENTER后移動(dòng)實(shí)例3：美化產(chǎn)品生產(chǎn)記錄表1.合并單元格合并及居中（清除）2.設(shè)置標(biāo)題格式：字體、字號(hào)、調(diào)整行高、填充顏

5、色、字體顏色、加粗單元格格式或常用選項(xiàng)卡3.設(shè)置表頭格式：字體、字號(hào)、顏色、對(duì)齊、單元格底紋4.數(shù)據(jù)區(qū)的文字格式和對(duì)齊格式：CTRL選擇BG列字體選擇A列字體，選擇DF列字體，選擇CE列增加縮進(jìn)量+右對(duì)齊5.為表格添加邊框：單元格格式內(nèi)細(xì)外粗6.為數(shù)據(jù)區(qū)域配置底紋：單元格格式圖案實(shí)例4.制作產(chǎn)品銷售記錄表復(fù)制產(chǎn)品生產(chǎn)記錄表中數(shù)據(jù)套用內(nèi)置表格樣式1.錄入產(chǎn)品銷售記錄數(shù)據(jù)：輸入標(biāo)題復(fù)制產(chǎn)品生產(chǎn)記錄表中數(shù)據(jù)，CTRL+C與CTRL+V，編號(hào)、名稱智能粘貼（匹配目標(biāo)區(qū)域格式）單位、單價(jià)選擇性粘貼（數(shù)值）單價(jià)為不帶￥貨幣類型輸入銷售量、銷售額合并標(biāo)題區(qū)2.套用表格格式：全選格式自動(dòng)套用格式序列2清除邊框

6、3.修改套用的表格格式：表頭字體顏色、填充顏色標(biāo)題字號(hào)、字體“單位”居中“單價(jià)、銷售量”增加縮進(jìn)量+右對(duì)齊保存4.相關(guān)知識(shí)：自定義單元格區(qū)域名稱插入名稱自定義實(shí)例5：計(jì)算產(chǎn)品銷售額編輯公式、填充公式、自動(dòng)求和1.插入折價(jià)率：銷售額前面插入列輸入折價(jià)率設(shè)置為百分比，小數(shù)位數(shù)22.計(jì)算銷售額：單價(jià)*銷售量*折價(jià)率右鍵拖曳（不帶格式填充）3.計(jì)算銷售總額：A19輸入“銷售總額”G19進(jìn)行SUM求和格式刷復(fù)制G16底紋4.小竅門：在單元格中顯示公式工具選項(xiàng)視圖公式5.公式的規(guī)則：運(yùn)算次序，運(yùn)算符類型（算術(shù)、比較、文本、引用）6.相對(duì)引用和絕對(duì)引用：（1）相對(duì)：根據(jù)源公式單元格相對(duì)位置引用（2）絕對(duì)：單

7、元格地址保持不變，可通過凍結(jié)實(shí)現(xiàn)$（3）F4可以在單元格絕對(duì)和相對(duì)引用之間切換實(shí)例6制作個(gè)性的銷售報(bào)表到賬百分比計(jì)算、添加背景、創(chuàng)建藝術(shù)字1.錄入與運(yùn)算銷售數(shù)據(jù)：新建工作簿命名為銷售報(bào)表錄入銷售記錄計(jì)算到賬百分比設(shè)置百分比，小數(shù)兩位左鍵拖曳2.添加背景：格式工作表背景全球策略3.插入藝術(shù)字：插入圖片藝術(shù)字選擇字形字體字號(hào)；設(shè)置藝術(shù)字格式填充顏色填充效果預(yù)設(shè)碧海青天線條1磅線條顏色橘黃;藝術(shù)字字符間距120%藝術(shù)字形狀左牛角形；控點(diǎn)拉伸旋轉(zhuǎn)；把藝術(shù)字放到A14.注意：背景圖片不能被打印實(shí)例7：快速制作大量銷售報(bào)表創(chuàng)建模板、區(qū)分單雙月、樣式功能1.創(chuàng)建銷售報(bào)告工作簿模板打開實(shí)例6報(bào)表加粗產(chǎn)品名稱列

8、A2:D2字體、顏色改變?nèi)∠羞吙蛏舷聝蓷l邊圖案單元格底紋刪除SHEET23另存為模板2.創(chuàng)建基于模板的工作簿新建選擇模板插入多張工作表，重命名修改二月份數(shù)據(jù)標(biāo)題改為二月份修改三月份、四月份3.創(chuàng)建“表頭”與“產(chǎn)品名稱”樣式二月份表頭格式樣式輸入“表頭”添加（先創(chuàng)建表頭在設(shè)置格式）修改打開單元格格式字體、圖案選擇A3:A16（先設(shè)置格式再創(chuàng)建樣式）字體顏色格式樣式輸入“品名”4.在雙月報(bào)表中應(yīng)用樣式四月份表頭應(yīng)用樣式A3:A16應(yīng)用品名樣式保存工作簿實(shí)例8：匯總產(chǎn)品銷售情況合并計(jì)算1.打開“月銷售報(bào)表”“四月份”前插入新工作表（應(yīng)用月銷售報(bào)表模板），命名為“第一季度銷售情況表”刪除A3:D1

9、6內(nèi)容標(biāo)題改為“第一季度銷售情況表”2.激活A(yù)3選擇“數(shù)據(jù)”“合并計(jì)算”選擇“求和”引用位置為“一月份”A3:C16，同時(shí)添加二三月份選擇“最左列”“創(chuàng)建連至源數(shù)據(jù)的鏈接”3.計(jì)算合并后的“到賬百分比”注：取消組合時(shí)，可先“顯示明細(xì)數(shù)據(jù)”，再“取消組合”，然后用“撤消”恢復(fù)。還可以“清除分級(jí)顯示”，然后“自動(dòng)建立分級(jí)顯示”恢復(fù)實(shí)例9：分析銷售情況排序、自動(dòng)篩選1.對(duì)單列內(nèi)容進(jìn)行排序“四月份”前插入一空白工作表,命名為“第一季度銷售情況分析表”按實(shí)例8在A3單元格進(jìn)行合并計(jì)算，清除“創(chuàng)建連至源數(shù)據(jù)的鏈接”輸入標(biāo)題“第一季度銷售情況分析表”和表頭激活“簽單額”任意單元格對(duì)“簽單額”按常用工具欄“升

10、序”進(jìn)行排序2.多列內(nèi)容的組合排序激活任意數(shù)據(jù)單元格“數(shù)據(jù)”，“排序”主要關(guān)鍵字“到賬百分比”降序次要關(guān)鍵字“簽單額”升序（薯?xiàng)l到賬額改為490160）3.自動(dòng)篩選激活任意數(shù)據(jù)單元格“數(shù)據(jù)”，“篩選”，“自動(dòng)篩選”“到賬額”“前10個(gè)”中間輸入5確定“全部”顯示全部4.自定義篩選“到賬百分比”“自定義”大于或等于0.85，小于或等于0.95“數(shù)據(jù)”，“篩選”，“自動(dòng)篩選”退出篩選實(shí)例10：創(chuàng)建銷售圖表1.創(chuàng)建柱形圖在“第一季度銷售情況表”中插入柱形圖：選取A2:C58，選擇“插入”“圖表”，或常用工具欄“圖表向?qū)А边x擇柱形圖第四個(gè)下一步，“標(biāo)題”中輸入“產(chǎn)品銷售情況圖表”“圖例”位置選擇“底部

11、”下一步，“圖表位置”“作為其中的對(duì)象插入”完成調(diào)整圖表到工作表右側(cè)，調(diào)整大小合適2.美化圖表標(biāo)題與圖例（1）圖表標(biāo)題：雙擊標(biāo)題字體：黑體，14，加粗、傾斜、淺藍(lán)色圖案：邊框“自定義”,樣式選一種，顏色海綠，粗細(xì)最粗，陰影，區(qū)域“無”確定拖到圖表正上方（2）圖例：雙擊圖例區(qū)打開“圖例格式”“圖案”“區(qū)域”選擇“淡紫”確定選中簽單額前圖例項(xiàng)標(biāo)示打開“圖例項(xiàng)標(biāo)示格式”“區(qū)域”選“黃色”確定3.美化坐標(biāo)軸（1）雙擊X軸任意產(chǎn)品名稱“字體”幼圓、10號(hào)、粉紅色“圖案”“自定義”樣式選一種，顏色藍(lán)色，最粗“對(duì)齊”60度確定（2）Y軸“圖案”設(shè)為相同“字體”幼圓、10號(hào)、綠色確定4.美化背景墻及網(wǎng)格線（1

12、）雙擊背景墻，打開“背景墻格式”“圖案”“填充效果”“漸變”“預(yù)設(shè)”選擇“紅日西斜”確定（2）雙擊網(wǎng)格線，打開“網(wǎng)格線格式”“圖案”“自定義”顏色綠色確定5.為整個(gè)圖表區(qū)填充紋理背景圖表區(qū)空白處雙擊，打開“圖表區(qū)格式”“圖案”“區(qū)域”“填充效果”“紋理”選擇“花束”確定6.在圖表中添加“到賬額”圖例（1）在圖表空白區(qū)單擊右鍵選擇“源數(shù)據(jù)”按CTRL選中D2:D58確定將圖例改為鮮綠色去掉簽單額前面沒用的圖例項(xiàng)標(biāo)示（2）坐標(biāo)軸名稱：選中圖表，“圖表”“圖表選項(xiàng)”或單擊右鍵選擇X軸輸入“產(chǎn)品名稱”，Z軸輸入“金額（元）”（3）雙擊“金額（元）”，打開“坐標(biāo)軸標(biāo)題格式”“字體”隸書、12、褐色“對(duì)齊

13、”90度（4）雙擊“產(chǎn)品名稱”，打開“坐標(biāo)軸標(biāo)題格式”“字體”隸書、12、褐色確定拖到最右端實(shí)例11：導(dǎo)入日常費(fèi)用表導(dǎo)入WORD文檔，插入剪貼畫1.復(fù)制WORD表格中的數(shù)據(jù)(1)把WORD中日常費(fèi)用表數(shù)據(jù)COPY粘貼到“公司日常費(fèi)用表”中(2)A1:I2合并并居中單元格(注意上下左右都居中)(3)A3:I3褐色填充、白色字體2.插入剪貼畫(1)“插入”圖片剪貼畫取消“所有收藏集”選中展開“OFFICE收藏夾”選中“標(biāo)志”和“工業(yè)”(2)“結(jié)果類型”“剪貼畫”(3)搜索選中剪貼畫插入拖到左上角實(shí)例12：管理倉庫存貨用記錄單創(chuàng)建數(shù)據(jù)庫1.創(chuàng)建存貨數(shù)據(jù)庫(1)A1:I1合并居中輸入“公司倉庫存貨數(shù)據(jù)

14、庫”18、楷體、下劃線、藍(lán)色(2)A2:I2“編號(hào)、名稱、數(shù)量、單價(jià)、單位、類別、出庫量、入庫量、備注”褐色填充、12、加粗黑體、白色(3)A2:I2作為列標(biāo)簽選中數(shù)據(jù)記錄單輸入數(shù)據(jù)單擊新建（只輸入三行、TAB在記錄單中向下切換）(4)文字都居中，數(shù)字右對(duì)齊(5)A2:I16加邊框、外粗內(nèi)細(xì)2.登記入庫量通過記錄單改變?nèi)霂炝?1)激活任意數(shù)據(jù)單元格數(shù)據(jù)、記錄單單擊“條件”“名稱”輸入XX護(hù)膚油把入庫量改為200備注改為2004-3-303.刪除紀(jì)錄刪除營養(yǎng)品的紀(jì)錄激活任意數(shù)據(jù)單元格數(shù)據(jù)、記錄單單擊“條件”“類別”輸入營養(yǎng)品4.快速分類匯總先排序、后匯總選取A2:I16數(shù)據(jù)分類匯總分類字段“類別

15、”匯總方式“求和”選定匯總項(xiàng)“數(shù)量、出庫量、入庫量”選中“替換當(dāng)前分類匯總”“匯總結(jié)果顯示在數(shù)據(jù)下方”實(shí)例13：制作員工工資表的用法，MAX、MIN，狀態(tài)欄察看，拆分窗格和凍結(jié)表頭1.創(chuàng)建工資表(1)A1:L1合并居中輸入“員工工資表”20、隸書、加粗、下劃線、藍(lán)色(2)合并A2:L2輸入結(jié)算日期和統(tǒng)計(jì)日期范圍、10、楷體、紅色(3)合并A3:A4序號(hào)B3:B4姓名C3:C4職位D3:G3應(yīng)付工資H3:K3應(yīng)扣工資L3:L4實(shí)發(fā)工資數(shù)據(jù)填入（直接COPY，注意001文本格式）(4)姓名和職位列居中，其它數(shù)據(jù)增加縮進(jìn)并右對(duì)齊A3:L20加邊框(5)上例同法加入公司徽標(biāo)2.計(jì)算實(shí)發(fā)工資用計(jì)算應(yīng)領(lǐng)、

16、應(yīng)扣小計(jì)，再算實(shí)發(fā)工資(1)選取D5:G5單擊下拉G5到G20(2)選取H5:K5單擊下拉K5到K20(3)L5中輸入=G5-K5拖動(dòng)到L203.查看與分析員工業(yè)績(1)激活L21輸入=選擇MAX函數(shù)(2)激活E21輸入=選擇統(tǒng)計(jì)中MIN函數(shù)不選擇E20(3)只在狀態(tài)欄顯示而不填入工作表：狀態(tài)欄上單擊右鍵4.拆分工作表窗格和凍結(jié)工作表表頭拆分：(1)垂直和水平滾動(dòng)條=標(biāo)記雙擊（2）激活某個(gè)單元格窗口、拆分凍結(jié)：激活C5窗口、凍結(jié)窗格效果為垂直滾動(dòng)上凍結(jié)、水平滾動(dòng)左凍結(jié)實(shí)例14：計(jì)算個(gè)人收入所得稅IF函數(shù)的應(yīng)用(1)激活員工工資表M5輸入=IF(L5-8000,0,IF(L5-800500,5%

17、*(L5-800),IF(L5-8002000,10%*(L5-800)-25,IF(L5-8005000,15%*(L5-800)-125,IF(L5-80020000,20%*(L5-800)-375)拖到L20(2)激活N5輸入=L5-M5拖到N20(3)調(diào)整標(biāo)題對(duì)齊實(shí)例15：評(píng)定銷售業(yè)績IF函數(shù)的應(yīng)用，添加批注（1）在“公司常務(wù)”工作簿中最后插入“銷售業(yè)績評(píng)定表”（2）選擇“員工工資表”A3:G19單元格粘貼到“銷售業(yè)績評(píng)定表”相同區(qū)域，設(shè)置格式如圖15-2所示（3）F4中輸入公式=IF(E41300,較差,IF(E42000,一般,IF(E42500,良好,優(yōu)秀)（4）F16添加批注

18、，顯示、隱藏、批注格式設(shè)置、用戶名更改相關(guān)知識(shí)：追蹤單元格（在月銷售報(bào)表工作簿中）（1）雙擊（2）調(diào)出“工具欄”中“公式審核”練習(xí)：1.同時(shí)滿足筆試和機(jī)試都大于等于60的時(shí)候，才顯示通過，否則顯示補(bǔ)考2.平均成績60以上為合格，70以上為良好，80以上為優(yōu)秀實(shí)例16：創(chuàng)建工資透視表布局、格式設(shè)置、刪除和添加字段、顯示和隱藏明細(xì)數(shù)據(jù)、導(dǎo)出某一數(shù)字明細(xì)記錄1.創(chuàng)建數(shù)據(jù)透視表復(fù)制“員工工資表”到最后，設(shè)置成如圖16-3所示，更改“實(shí)發(fā)工資”公式。創(chuàng)建數(shù)據(jù)透視表在新工作表中，布局設(shè)為如圖16-8所示，提成匯總方式為求平均值。將改工作表命名為“工資透視表”。2.設(shè)置格式自動(dòng)套用格式“報(bào)表4”。3.添加和

19、刪除字段刪除“曠工”、添加“實(shí)發(fā)工資”字段到列區(qū)域4.使用數(shù)據(jù)透視表通過下拉按鈕選擇僅察看銷售經(jīng)理的數(shù)據(jù)；也可通過雙擊隱藏銷售經(jīng)理的數(shù)據(jù)5.導(dǎo)出某一數(shù)字明細(xì)紀(jì)錄雙擊“銷售經(jīng)理”數(shù)據(jù)區(qū)導(dǎo)出全部銷售經(jīng)理數(shù)據(jù)；雙擊某一人數(shù)據(jù)導(dǎo)出該人數(shù)據(jù)。實(shí)例17：創(chuàng)建工資透視圖表數(shù)據(jù)圖格式設(shè)置、數(shù)據(jù)察看與分析1.創(chuàng)建數(shù)據(jù)透視圖上表中刪除“遲到”與“事假”字段，去除“保潔員”數(shù)據(jù)單擊圖表向?qū)Р迦雸D表，圖表類型選擇圓環(huán)圖第二個(gè)2.美化數(shù)據(jù)透視圖圖表選項(xiàng)中設(shè)置圖表標(biāo)題為“員工工資透視圖”，加粗、隸書、18號(hào)、淺藍(lán)色數(shù)據(jù)系列格式中設(shè)置內(nèi)徑大小為40%，繪圖區(qū)填充花束紋理，圖表區(qū)填充預(yù)設(shè)麥浪滾滾3.察看與分析數(shù)據(jù)數(shù)據(jù)系列格式

20、中顯示基本工資數(shù)值；顯示所有業(yè)務(wù)員提成類別名稱和比例實(shí)例18：建立相關(guān)表格的聯(lián)系控件工具箱、窗體、超鏈接創(chuàng)建概念：宏是自動(dòng)執(zhí)行某種操作的命令。INDEX能從引用或數(shù)組中選擇值。1.添加鏈接按鈕（1）打開“公司常務(wù)”中“倉庫存貨管理”從“視圖”菜單中“工具欄“中調(diào)出“控件工具箱”用“命令按鈕”插入按鈕右鍵選擇屬性設(shè)置：背景藍(lán)色、字體黃色、生產(chǎn)記錄表、楷體、加粗、四號(hào)從“插入”菜單中選擇“超鏈接”（2）復(fù)制“生產(chǎn)記錄表”按鈕，改名為“銷售記錄表”，改變超鏈接2.創(chuàng)建下拉列表框（1）在第二行插入一空行從“視圖”菜單中“工具欄“中調(diào)出“窗體”點(diǎn)擊“組合框”加入到B2設(shè)置控件格式：數(shù)據(jù)區(qū)域?yàn)锽4:B15

21、，單元格鏈接，先是數(shù)量為6，三維陰影（2）C2中輸入=INDEX(B4:B15,A2)，顯示A2中對(duì)應(yīng)行數(shù)的數(shù)據(jù)實(shí)例19：保護(hù)重要的財(cái)務(wù)數(shù)據(jù)單元格、工作表、控件、工作簿的保護(hù)，文檔的打開權(quán)限密碼1.保護(hù)單元格單元格格式鎖定、隱藏（隱藏公式）注：只有在工作表保護(hù)狀態(tài)才有效2.保護(hù)工作表選中“員工工資表”“工具”菜單里“保護(hù)”的“保護(hù)工作表”撤銷保護(hù)注：防止非授權(quán)用戶更改受保護(hù)的單元格3.保護(hù)控件打開“倉庫存貨管理”-右鍵“設(shè)置控件格式”中“保護(hù)”選擇鎖定注：只有在工作表保護(hù)狀態(tài)才有效4.保護(hù)工作簿可以防止非授權(quán)用戶添加、移動(dòng)、刪除、顯示隱藏的工作表，防止他人更改工作簿顯示窗口的大小和位置。（1）

22、隱藏員工工資表“格式”菜單中“工作表”中“隱藏”（2）“工具”菜單中“保護(hù)”的“保護(hù)工作簿”“結(jié)構(gòu)”、“窗口”（3）撤銷工作簿保護(hù)5.保護(hù)整個(gè)EXCEL文檔設(shè)置打開權(quán)限密碼和修改權(quán)限密碼“公司常務(wù)”“工具”菜單中“選項(xiàng)”的“安全性”實(shí)例20：打印評(píng)定表和工資表頁面設(shè)置、打印設(shè)置、區(qū)域打印法1.打印員工業(yè)績評(píng)定表（1）打開“員工業(yè)績評(píng)定表”進(jìn)行“頁面設(shè)置”：如圖20-1、20-2、20-3打印預(yù)覽點(diǎn)擊“頁邊距”微調(diào)列寬（2）進(jìn)入“打印”調(diào)節(jié)如圖20-8所示2.區(qū)域打印法打印某位員工的工資條1.“員工工資表”頁面設(shè)置：橫向、A4選取A1:N5區(qū)域打印可以隱藏不需要的行，再進(jìn)行打印2.頁面設(shè)置的“頂

23、端標(biāo)題”中插入$1:$4打印區(qū)域中選中要打印的員工紀(jì)錄第二篇常見會(huì)計(jì)賬務(wù)處理實(shí)例1：記賬憑證1.收付款憑證：根據(jù)模板制作收付款憑證知識(shí)點(diǎn)：數(shù)據(jù)-有效性-序列2.轉(zhuǎn)帳憑證：根據(jù)模板制作轉(zhuǎn)帳憑證實(shí)例2：快速登記現(xiàn)金日記賬1.給憑證快速編號(hào)要求：1.在G5中輸入“=COUNTIF($F$5:F5,F5),然后應(yīng)用到本列所有數(shù)據(jù)單元格2.在H5中輸入“=F5&G5,然后應(yīng)用到本列所有數(shù)據(jù)單元格3.隱藏F列和G列4.選定第2、3、4行，選擇“數(shù)據(jù)篩選自動(dòng)篩選”，單擊“憑證類別及編號(hào)”右側(cè)小按鈕，選擇“自定義”，自定義篩選方式為：等于現(xiàn)*5.復(fù)制B5:I67單元格區(qū)域到“現(xiàn)金日記賬”工作表A5單元格知識(shí)點(diǎn)

24、：COUNTIF函數(shù)，自動(dòng)篩選COUNTIF(）函數(shù)的功能是統(tǒng)計(jì)區(qū)域中滿足給定條件的單元格的個(gè)數(shù)，語法規(guī)則為COUNTIF(range,criteria),range為要統(tǒng)計(jì)其中滿足條件的單元格數(shù)目的單元格區(qū)域；criteria為確定哪些單元格將被統(tǒng)計(jì)在內(nèi)的條件。例如，COUNTIF(A5:C11,1),表示統(tǒng)計(jì)A5:C11區(qū)域中，值為1的單元格個(gè)數(shù)。2.快速登記現(xiàn)金日記賬要求：1.在G5中輸入=IF(LEFT(E5,2)=現(xiàn)收,VLOOKUP(E5,本月會(huì)計(jì)憑證!$H$5:$M$78,5,FALSE),VLOOKUP(E5,本月會(huì)計(jì)憑證!$H$5:$M$78,3,FALSE),應(yīng)用到該列所有

25、單元格2.在H5中輸入“=IF(LEFT(E5,2)=現(xiàn)收,VLOOKUP(E5,本月會(huì)計(jì)憑證!$H$5:$M$78,4,FALSE),),應(yīng)用到該列所有單元格3.在I5中輸入“=IF(LEFT(E5,2)=現(xiàn)付,VLOOKUP(E5,本月會(huì)計(jì)憑證!$H$5:$M$78,6,FALSE),)，應(yīng)用到該列所有單元格4.在H27中計(jì)算借方合計(jì)數(shù)，在I27中計(jì)算貸方合計(jì)數(shù)，在K27中計(jì)算期末余額，在J27中填寫借或貸5.去掉復(fù)制時(shí)帶來的邊框知識(shí)點(diǎn)：LEFT()函數(shù)，VLOOKUP函數(shù)，IF函數(shù)的組合應(yīng)用1.LEFT()為從左邊開始截取字段函數(shù)。例如，LEFT(E5,2)表示對(duì)E5單元格中的值從左到右

26、截取兩個(gè)字符。2.VLOOKUP()函數(shù)的功能是在單元格區(qū)域的首列查找指定的數(shù)值，并返回該值當(dāng)前行中指定列處的數(shù)值。例如，VLOOKUP(E5,本月會(huì)計(jì)憑證!$H$5:$M$78,4,FALSE)表示，在“本月會(huì)計(jì)憑證!$H$5:$M$78”區(qū)域第一列中查找本表中E5的值，然后返回區(qū)域中該值同一行中第4列的數(shù)值，F(xiàn)ALSE表示精確返回。實(shí)例3：快速登記銀行存款日記賬要求：（注意不要改變版面顏色設(shè)置）1.點(diǎn)擊“本月會(huì)計(jì)憑證”工作表標(biāo)簽。重新設(shè)置“憑證類別及編號(hào)”的自定義篩選。自定義篩選方式為：等于銀*2.復(fù)制B5:I78單元格區(qū)域到“銀行存款日記賬”工作表A5單元格。注意：粘貼時(shí)用“選擇性粘貼”

27、-“值和數(shù)值格式”3.點(diǎn)擊“本月會(huì)計(jì)憑證”工作表標(biāo)簽。設(shè)置“摘要”的自定義篩選。自定義篩選方式為：等于提現(xiàn)再次設(shè)置“摘要”的自定義篩選。自定義篩選方式為：等于現(xiàn)金存入4.復(fù)制前兩次篩選出的四筆業(yè)務(wù)粘貼到“銀行存款日記賬”中。5.根據(jù)憑證日期進(jìn)行升序排序6.在G5中輸入“=SUMPRODUCT(本月會(huì)計(jì)憑證!$H$5:$H$78=銀行存款日記賬!$E5)*(本月會(huì)計(jì)憑證!$L$5:$L$78=銀行存款日記賬!G$3)*(本月會(huì)計(jì)憑證!$M$5:$M$78)，應(yīng)用到所有黃色區(qū)域7.在L5中輸入“=SUMPRODUCT(本月會(huì)計(jì)憑證!$H$5:$H$78=銀行存款日記賬!$E5)*(本月會(huì)計(jì)憑證!$

28、J$5:$J$78=銀行存款日記賬!L$3)*(本月會(huì)計(jì)憑證!$K$5:$K$78)，應(yīng)用到所有綠色區(qū)域。8.用SUM（)計(jì)算各合計(jì)數(shù)，計(jì)算期末余額知識(shí)點(diǎn)：SUMPRODUCT()函數(shù)SUMPRODUCT()是多條件求和函數(shù)，即同時(shí)滿足幾個(gè)條件的數(shù)據(jù)之和，也可以作為查找值函數(shù)來用。例如：“=SUMPRODUCT(A2:A17=鄭州)*(B2:B17=2)*(C2:C17)”,其中(A2:A17=鄭州)表示第一個(gè)條件，即A2：A17范圍內(nèi)，單元格數(shù)值為鄭州；(B2:B17=2)表示第二個(gè)條件，即B2：B17單元格中數(shù)值為2；(C2:C17)表示要求和的數(shù)值，即把C2：C17范圍內(nèi)同時(shí)滿足上述條件

29、的數(shù)值加在一起。注意各個(gè)條件之間要用*連接起來，*號(hào)相當(dāng)于邏輯AND。實(shí)例5：快速編制科目匯總表要求：1.匯總借方發(fā)生額。在C5單元格中輸入“=SUMIF(本月會(huì)計(jì)憑證!$J$5:$J$78,科目匯總表!A5,本月會(huì)計(jì)憑證!$K$5:$K$78)，應(yīng)用到本列所有單元格2.匯總貸方發(fā)生額。在D5單元格中輸入“=SUMIF(本月會(huì)計(jì)憑證!$L$5:$L$78,科目匯總表!A5,本月會(huì)計(jì)憑證!$M$5:$M$78)，應(yīng)用到本列所有單元格3.計(jì)算合計(jì)數(shù)。分別在C26和D26單元格中計(jì)算借貸合計(jì)數(shù)。4，試算平衡。在E26單元格中輸入“=IF(C26=D26,試算平衡,試算不平衡)5.輸入記賬憑證起訖號(hào)數(shù)

30、。在合并后的單元格E5中輸入此次科目匯總表所匯總的記賬憑證的編號(hào)。知識(shí)點(diǎn)：SUMIF()函數(shù)該函數(shù)是根據(jù)指定條件對(duì)單元格區(qū)域求和。語法規(guī)則為SUMIF(range,criteria,sum_range)，range表示用于條件判斷的區(qū)域，criteria表示確定哪些單元格將被相加的條件，sum_range表示需要求和的區(qū)域。例如，在C5單元格中輸入“=SUMIF(本月會(huì)計(jì)憑證!$J$5:$J$78,科目匯總表!A5,本月會(huì)計(jì)憑證!$K$5:$K$78)表示在“本月會(huì)計(jì)憑證!$J$5:$J$78”中查找“科目匯總表!A5”，然后在“本月會(huì)計(jì)憑證!$K$5:$K$78”對(duì)滿足條件的值進(jìn)行求和。實(shí)例

31、6：快速登記總分類賬要求：1.復(fù)制會(huì)計(jì)科目表到科目匯總表前面。2.在會(huì)計(jì)科目表（2）中E列后插入一列，命名為“期初余額”，把各科目期初余額的數(shù)據(jù)填入相應(yīng)位置。3.切換到科目匯總表，選取A5:A25,選擇“插入名稱定義”，把該區(qū)域命名為KM14.在H3單元格中設(shè)置數(shù)據(jù)有效性，序列來源中輸入“=KM1”5.在I6單元格中輸入“=VLOOKUP($H$3,會(huì)計(jì)科目表(2)!$C$2:$F$162,3,FALSE)6.在H6單元格中輸入“=VLOOKUP($H$3,會(huì)計(jì)科目表(2)!$C$2:$F$162,4,FALSE)7.在F7單元格中輸入“=VLOOKUP($H$3,科目匯總表!$A$5:$D$

32、25,3,FALSE)8.在G7單元格中輸入“=VLOOKUP($H$3,科目匯總表!$A$5:$D$25,4,FALSE)9.在I8單元格中輸入“=I6+F7-G710.在H8單元格中輸入借或貸實(shí)例7：對(duì)賬要求：一、進(jìn)行現(xiàn)金賬務(wù)核對(duì)1.在C3單元格中輸入“=現(xiàn)金日記賬!K4在D3單元格中輸入”=現(xiàn)金日記賬!H27在E3單元格中輸入“=現(xiàn)金日記賬!I27在F3單元格中輸入”=C3+D3-E32.在C4單元格中輸入“=總分類賬!I6在D4單元格中輸入”=總分類賬!F7在E4單元格中輸入“=總分類賬!G7在F4單元格中輸入”=C4+D4-E43.在C5中輸入“=IF(C3=C4,相符,不相符)，應(yīng)

33、用到D5、E5、F54.在G3單元格中輸入”=IF(C5=不相符,錯(cuò)誤,IF(D5=不相符,錯(cuò)誤,IF(E5=不相符,錯(cuò)誤,IF(F5=不相符,錯(cuò)誤,正確)二、進(jìn)行銀行存款賬務(wù)核對(duì)按照現(xiàn)金賬務(wù)核對(duì)的方法進(jìn)行第三篇日常財(cái)務(wù)管理與分析實(shí)例1：應(yīng)收賬款賬齡分析要求：一、填充表格1.在G2單元格中輸入=TODAY(),顯示當(dāng)天的日期2.計(jì)算F列的余額。3.計(jì)算G列的賬齡。在G4單元格中輸入=($G$2-B4)/360,應(yīng)用到該列所有單元格二、制作數(shù)據(jù)透視表布局：客戶名稱在行區(qū)域，賬齡在列區(qū)域。在賬齡字段上點(diǎn)擊右鍵，選擇組及顯示明細(xì)數(shù)據(jù)-組合,設(shè)置為起始于0，終止于3.5，步長為0.5三、制作數(shù)據(jù)透視圖

34、選擇柱形圖第二個(gè)實(shí)例2：應(yīng)收賬款催款單要求：1.用TODAY()函數(shù)在G3單元格中顯示當(dāng)天日期2.設(shè)置C4單元格的有效性。切換到“應(yīng)收賬款賬齡分析”工作表，把數(shù)據(jù)透視表中的客戶名稱列定義為“客戶”（插入名稱定義）。切換到“催款單”工作表設(shè)置C4單元格的有效性。3.統(tǒng)計(jì)每個(gè)客戶所欠貨款合計(jì)數(shù)。在E5單元格中輸入=SUMIF(應(yīng)收賬款賬齡分析!$C$4:$C$27,$C$4,應(yīng)收賬款賬齡分析!$F$4:$F$27)實(shí)例3：提取壞賬準(zhǔn)備要求：1.在C列計(jì)算每個(gè)客戶應(yīng)收賬款總額。在C3單元格中輸入=SUMIF(應(yīng)收賬款賬齡分析!$C$4:$C$27,$B3,應(yīng)收賬款賬齡分析!$F$4:$F$27),應(yīng)

35、用到該列所有單元格。2.計(jì)算平均賬齡。賬齡總數(shù)/賒賬次數(shù)在D3單元格中輸入=SUMIF(應(yīng)收賬款賬齡分析!$C$4:$C$27,B3,應(yīng)收賬款賬齡分析!$G$4:$G$27)/COUNTIF(應(yīng)收賬款賬齡分析!$C$4:$C$27,B3),應(yīng)用到該列所有單元格。3.如果賬齡小于0.5年，不提取壞賬，如果賬齡在0.51年之間，壞賬提取比率為0.005，如果賬齡在11.5年之間，壞賬提取比率為0.01，如果賬齡在1.5年以上，壞賬提取比率為0.02。在E3中輸入=IF(？),應(yīng)用到該列所有單元格。4.計(jì)算提取壞賬的金額。應(yīng)收賬款*壞賬提取比率5.選取B列和F列數(shù)據(jù)制作壞賬分布狀況圖表。選擇餅圖第5

36、個(gè)，圖例項(xiàng)位于圖表底部，數(shù)據(jù)標(biāo)志顯示百分比，圖表名稱為壞賬分布狀況。實(shí)例4：復(fù)利終值計(jì)算及資金變化圖表要求：1.在B5單元格中計(jì)算500000元本金期限1年的復(fù)利終值。輸入公式：=B2*(1+B3/12)122.按月計(jì)算本金、利息、本利和。3.根據(jù)A7:M10區(qū)域制作圖表。圖表名稱為：本金、利息及本利和變化圖表選擇自定義類型兩軸線柱圖，本利和與本金的Y軸為主坐標(biāo)軸，同時(shí)圖表類型為柱形圖；利息的Y軸在次坐標(biāo)軸，同時(shí)圖表類型為折線圖。提示：1.復(fù)利終值公式：F=P*(1+r)n2.月利率=$B$3/12實(shí)例5：復(fù)利現(xiàn)值計(jì)算及資金變化圖表要求：1.在B5單元格中計(jì)算復(fù)利現(xiàn)值。輸入公式：=B2/(1+

37、B3)B42.按年計(jì)算本金、利息、本利和。3.根據(jù)A7:F10區(qū)域制作圖表。圖表名稱為：本金、利息及本利和變化圖表選擇自定義類型兩軸線柱圖，本利和與本金的Y軸為主坐標(biāo)軸，同時(shí)圖表類型為柱形圖；利息的Y軸在次坐標(biāo)軸，同時(shí)圖表類型為折線圖。顯示本利和的趨勢線和趨勢線公式。提示：復(fù)利現(xiàn)值公式：P=F/(1+r)n實(shí)例6：年金相關(guān)計(jì)算知識(shí)點(diǎn)：1.終值函數(shù)FV(rate,nper,pmt,pv,type)。rate為利率，nper為投資期數(shù)，pmt為每期投入的金額，也稱為年金，pv是第一期期初投入的本金，type等于0時(shí)表示年金發(fā)生在期末，type等于1時(shí)表示年金發(fā)生在期初，默認(rèn)為0。應(yīng)用一、預(yù)付年金的

38、終值例如：某人3年后需要一筆比較大的學(xué)習(xí)費(fèi)用支出，計(jì)劃從現(xiàn)在起每月初存入2000元，三年期零存整取月息0.1725%，那么3年以后該賬戶的存款額會(huì)是多少？公式寫為：=FV(0.1725%,36,-2000,0,1)應(yīng)用二、一次性收付款項(xiàng)的終值例如：某人在年初存入50000元，3年期整存整取月利率為0.27%，3年后其賬戶中的銀行存款為多少？公式寫為：=FV(0.27%,36,0,-50000,1)練習(xí)：應(yīng)用三、普通年金的終值某人3年后需要投資買房，計(jì)劃從現(xiàn)在起每月末存入銀行1500元，三年期零存整取月息0.1725%，那么3年以后他賬戶的存款額會(huì)是多少？2.現(xiàn)值函數(shù)PV(rate,nper,p

39、mt,fv,type)rate為利率，nper為投資期數(shù)，pmt為每期收回的金額，也稱為年金，fv是最后一次支付后希望得到的現(xiàn)金余額，type等于0時(shí)表示年金發(fā)生在期末，type等于1時(shí)表示年金發(fā)生在期初，默認(rèn)為0。如果投資回收的現(xiàn)值大于實(shí)際投資的價(jià)值，則這項(xiàng)投資是有收益的。應(yīng)用一、預(yù)付年金的現(xiàn)值例如：假設(shè)要購買一項(xiàng)保險(xiǎn)年金，該保險(xiǎn)可以在今后20年內(nèi)于每月初回報(bào)600元。此項(xiàng)年金的購買成本為80,000元，假定月投資回報(bào)率為0.6%。問這項(xiàng)投資是否有收益？該項(xiàng)年金的現(xiàn)值為：=PV(0.6%,12*20,600,0,1),計(jì)算結(jié)果為：￥-76,662.29。負(fù)值表示這是一筆付款，也就是第一年年初應(yīng)支付的金額。年金的現(xiàn)值（￥76,662.29）小于實(shí)際支付的（￥80,000）。因此，這不是一項(xiàng)合算的投資。應(yīng)用二、一次性