Excel財(cái)務(wù)應(yīng)用講義

會(huì)計(jì)學(xué)1Excel財(cái)務(wù)應(yīng)用講義1.1銷售周報(bào)分析計(jì)算周次函數(shù)Weeknum=weeknum(日期，2）2表示一周從星期一開始根據(jù)周次統(tǒng)計(jì)收入Sumifs函數(shù)多條件求和函數(shù)，與之同類的還有countifs,averageifs除了sumifs，sumproduct函數(shù)也可用于多條件求和=sumifs(求和區(qū)域，條件區(qū)域1，條件1，條件區(qū)域2，條件2,…)制作折線圖趨勢(shì)分析2第1頁(yè)/共46頁(yè)1.2客戶分布分析Frequency函數(shù)自定義統(tǒng)計(jì)區(qū)間設(shè)定參照值，取區(qū)間最小值作為參照值選中與統(tǒng)計(jì)區(qū)間數(shù)量相同的連續(xù)單元格輸入frequency函數(shù)

=frequency(數(shù)據(jù)區(qū)域，參照值區(qū)域）同時(shí)按下ctrl+shift+enter鍵最后按下的組合鍵將創(chuàng)建一個(gè)數(shù)組公式不可以單獨(dú)刪除數(shù)組公式中的任一公式，需要全選后才可刪除構(gòu)成比例分析繪制餅圖設(shè)置數(shù)據(jù)標(biāo)簽3第2頁(yè)/共46頁(yè)1.3客戶數(shù)變動(dòng)分析生成透視表選擇日期放入行區(qū)域右鍵-創(chuàng)建組，選擇年在透視表外輸入公式計(jì)算客戶增長(zhǎng)的比例制作圖表光標(biāo)放在透視表內(nèi)，點(diǎn)擊數(shù)據(jù)透視表工具-選項(xiàng)-數(shù)據(jù)透視圖生成透視圖光標(biāo)放在客戶增長(zhǎng)率表內(nèi)，選擇“插入”-折線圖客戶增量分析創(chuàng)建透視表，將日期放入行區(qū)域，將銷量字段放入數(shù)值區(qū)域右鍵-創(chuàng)建組，選擇年和月光標(biāo)放在銷量列，郵件-值匯總依據(jù)-選擇計(jì)數(shù)數(shù)據(jù)透視表工具-選項(xiàng)-數(shù)據(jù)透視圖，生成折線圖4第3頁(yè)/共46頁(yè)1.4基于地圖的數(shù)據(jù)分析作用形象展示基于地理位置的區(qū)域數(shù)據(jù)分析。容易理解，印象深刻，效果出眾。步驟使用地圖矢量圖，為每一個(gè)區(qū)域定義名稱，一般是該區(qū)域名稱的拼音。選中區(qū)域，在名稱框內(nèi)輸入即可。輸入宏，開發(fā)工具-visualbasic,雙擊thisworkbook,在右側(cè)代碼窗口輸入以下代碼：Subuser_click(region_name)ActiveSheet.Shapes(Range("m1").Value).Fill.ForeColor.SchemeColor=xlThemeColorDark1Range("m1").Value=region_nameActiveSheet.Shapes(region_name).Fill.ForeColor.SchemeColor=xlThemeColorAccent6EndSub5第4頁(yè)/共46頁(yè)1.4基于地圖的數(shù)據(jù)分析步驟為每個(gè)區(qū)域圖形指定宏，選中該區(qū)域圖形，右鍵-指定宏，輸入以下宏名稱：

‘thisworkbook.user_click(“區(qū)域名稱”)‘

其中，區(qū)域名稱為為區(qū)域圖形定義的名稱在m1單元格中輸入一個(gè)區(qū)域的名稱代碼，比如北京地區(qū)的代碼為beijing,此時(shí)即可點(diǎn)擊區(qū)域?qū)崿F(xiàn)變色效果。準(zhǔn)備好原始數(shù)據(jù)寫一個(gè)vlookup函數(shù)對(duì)地圖上選中的區(qū)域數(shù)據(jù)進(jìn)行查詢，該查詢的索引字段為m1單元格，該單元格可以任意指定。根據(jù)查詢結(jié)果制作圖表6第5頁(yè)/共46頁(yè)1.5基于地圖的色階分析步驟準(zhǔn)備地圖矢量文件為每一個(gè)區(qū)域圖形定義名稱，選中圖形，在名稱框內(nèi)輸入名稱，名稱設(shè)為該區(qū)域的拼音為數(shù)據(jù)設(shè)置區(qū)間，假設(shè)分為以下5個(gè)區(qū)間：為上面5種色彩所在單元格定義名稱，分別設(shè)為code1-5為這5個(gè)區(qū)間設(shè)置參照值和顏色代碼：7第6頁(yè)/共46頁(yè)1.5基于地圖的色階分析步驟為下圖中的3個(gè)單元格定義名稱，分別為province,vbdata,vbcode,其中數(shù)據(jù)和顏色代碼為vlookup查詢公式，根據(jù)省份在數(shù)據(jù)表中查詢數(shù)據(jù)和所屬顏色代碼制作控件按鈕，開發(fā)工具-控件-插入-按鈕-指定宏-新建，輸入以下宏代碼：Sub按鈕35_單擊()Fori=3To34Range("province").Value=Range("data!a"&i).ValueActiveSheet.Shapes(Range("province").Value).SelectSelection.ShapeRange.Fill.ForeColor.RGB=Range(Range("vbcode").Value).Interior.ColorNextiRange("f8").SelectEndSub8第7頁(yè)/共46頁(yè)1.6復(fù)合增長(zhǎng)率概念CAGR（CompoundAnnualGrowthRate）一個(gè)指標(biāo)（比如銷售收入或凈利潤(rùn)，投資回報(bào)等）在特定時(shí)期內(nèi)的年度增長(zhǎng)率是較長(zhǎng)時(shí)期內(nèi)的測(cè)算，忽略個(gè)別年度的波動(dòng)，將增長(zhǎng)率平滑，反映指標(biāo)的整體表現(xiàn)。公式=（當(dāng)前數(shù)值/基期數(shù)值）^(1/年數(shù)）-19第8頁(yè)/共46頁(yè)1.7波士頓矩陣分析簡(jiǎn)介波士頓矩陣（BCGMatrix），又稱市場(chǎng)增長(zhǎng)率-相對(duì)市場(chǎng)份額矩陣，是由美國(guó)著名的管理學(xué)家、波士頓咨詢公司創(chuàng)始人布魯斯·亨德森于20世紀(jì)60年代末期首創(chuàng)的。布魯斯認(rèn)為決定產(chǎn)品/業(yè)務(wù)結(jié)構(gòu)的要素可分為2類，市場(chǎng)吸引力與企業(yè)實(shí)力。在反映市場(chǎng)引力的眾多指標(biāo)（銷售增長(zhǎng)率、目標(biāo)市場(chǎng)容量、競(jìng)爭(zhēng)對(duì)手強(qiáng)弱及利潤(rùn)高低）中，銷售增長(zhǎng)率是最具代表性的綜合指標(biāo)；而在反映企業(yè)實(shí)力的指標(biāo)，如市場(chǎng)占有率，技術(shù)、設(shè)備、資金利用能力中，市場(chǎng)占有率是最能直接顯示出企業(yè)競(jìng)爭(zhēng)實(shí)力的指標(biāo)。因此，波士頓矩陣選取的縱坐標(biāo)與橫坐標(biāo)分別是“銷售增長(zhǎng)率”及“市場(chǎng)占有率”。由以上2個(gè)因素相互作用，產(chǎn)生4個(gè)不同的象限，劃分出4類性質(zhì)的產(chǎn)品/業(yè)務(wù)：銷售增長(zhǎng)率和市場(chǎng)占有率都較高的產(chǎn)品/業(yè)務(wù)（簡(jiǎn)稱“明星”）；銷售增長(zhǎng)率和市場(chǎng)占有率都較低的產(chǎn)品/業(yè)務(wù)（簡(jiǎn)稱“瘦狗”）；銷售增長(zhǎng)率高而市場(chǎng)占有率低的產(chǎn)品/業(yè)務(wù)（簡(jiǎn)稱“問(wèn)號(hào)”）；銷售增長(zhǎng)率低而市場(chǎng)占有率高的產(chǎn)品/業(yè)務(wù)（簡(jiǎn)“現(xiàn)金?！保?。

10第9頁(yè)/共46頁(yè)問(wèn)題型業(yè)務(wù)現(xiàn)金型業(yè)務(wù)瘦狗型業(yè)務(wù)明星型業(yè)務(wù)高高低低市場(chǎng)增長(zhǎng)市場(chǎng)份額1.7波士頓矩陣分析第10頁(yè)/共46頁(yè)1.7波士頓矩陣分析利用散點(diǎn)圖制作波士頓矩陣選中表格的數(shù)據(jù)列，制作散點(diǎn)圖，行坐標(biāo)為市場(chǎng)占有率，縱坐標(biāo)為增長(zhǎng)率選中行坐標(biāo)軸，設(shè)置坐標(biāo)軸格式，勾選逆序刻度值，將縱坐標(biāo)交叉選項(xiàng)改為坐標(biāo)軸值：0.5，將刻度線類型與坐標(biāo)軸標(biāo)簽設(shè)為無(wú)選中縱坐標(biāo)軸，除了不勾選逆序刻度值，其他設(shè)置與行坐標(biāo)軸一樣Excel散點(diǎn)圖的一個(gè)問(wèn)題是無(wú)法為數(shù)據(jù)添加標(biāo)簽，可以使用一個(gè)第三方程序解決：xychartlabels安裝此程序后excel會(huì)出現(xiàn)一個(gè)新的選項(xiàng)卡xychartlabels,選擇其中的addlabels命令在其中的selectalabelrange選項(xiàng)中，選擇數(shù)據(jù)表中的表前列即可最后可為坐標(biāo)軸添加標(biāo)簽或?yàn)?個(gè)象限添加圖片說(shuō)明。12第11頁(yè)/共46頁(yè)2.1存貨ABC管理概念又稱巴雷特分析法，按照價(jià)值和庫(kù)存數(shù)量的高低，依據(jù)一定的分類標(biāo)準(zhǔn)，確定關(guān)鍵的少數(shù)和次要的多數(shù)。其分清主次，抓住重點(diǎn)的思想廣泛應(yīng)用于存貨管理與成本管理。A類物資是指品種少、實(shí)物量少而價(jià)值高的物資，其成本金額約占70%，而實(shí)物量不超過(guò)20%。C類物資是指品種多、實(shí)物量多而價(jià)值低的物資，其成本金額約占10％，而實(shí)物量不低于50％。B類物資介于A類、C類物資之間。其成本金額約占20％，而實(shí)物量不超過(guò)30％。管理表制作步驟計(jì)算存貨金額及數(shù)量所占比例，并按金額降序排列計(jì)算存貨金額和數(shù)量所占累計(jì)比例13第12頁(yè)/共46頁(yè)2.1存貨ABC管理管理表制作步驟依據(jù)一定標(biāo)準(zhǔn)，進(jìn)行ABC分類。一般將關(guān)鍵的少數(shù)作為A類，次要的多數(shù)作為B和C類，對(duì)A類存貨進(jìn)行重點(diǎn)管理。制作帕累托圖以意大利經(jīng)濟(jì)學(xué)家pareto命名選擇存貨名稱，金額，累計(jì)金額百分比3列制作柱形圖選擇圖表工具-布局，左上角點(diǎn)開圖表元素選擇框，在其中選擇累計(jì)金額百分比點(diǎn)擊設(shè)置所選內(nèi)容格式按鈕，將“系列繪制在”選項(xiàng)由“主坐標(biāo)軸”改為“次坐標(biāo)軸”將累計(jì)金額百分比數(shù)據(jù)系列的圖表類型改為折線圖選中副坐標(biāo)軸，將最大值改為1選中金額數(shù)據(jù)系列，右鍵-設(shè)置數(shù)據(jù)系列格式，將分類間距改為0%14第13頁(yè)/共46頁(yè)152.2進(jìn)銷存表設(shè)計(jì)“表”功能表格與數(shù)據(jù)區(qū)域表內(nèi)的數(shù)據(jù)可以獨(dú)立和更方便的進(jìn)行管理，比如自動(dòng)擴(kuò)展數(shù)據(jù)與公式，添加匯總行，篩選，應(yīng)用表格格式等。創(chuàng)建與編輯將光標(biāo)放在表中，選擇“插入”-表格通過(guò)頂部的表格工具，可以對(duì)表格進(jìn)行各種編輯為表格更換樣式：表格工具-表格樣式，點(diǎn)擊即應(yīng)用新的樣式將表格轉(zhuǎn)化為區(qū)域：表格工具-轉(zhuǎn)化為區(qū)域在表格最后一行下面輸入數(shù)據(jù)，即可自動(dòng)擴(kuò)展表格范圍15第14頁(yè)/共46頁(yè)2.3進(jìn)銷存管理數(shù)據(jù)表設(shè)計(jì)進(jìn)出字段用if函數(shù)控制顯示與否

=if(b2=””,””,c2)Vlookup查詢產(chǎn)品價(jià)格

=vlookup(產(chǎn)品名稱，基礎(chǔ)信息表，價(jià)格所在的列，查詢方式）統(tǒng)計(jì)庫(kù)存創(chuàng)建透視表，行設(shè)置為產(chǎn)品，列設(shè)置為“進(jìn)出”，數(shù)值為數(shù)量數(shù)據(jù)透視表工具-域、項(xiàng)目和集-計(jì)算項(xiàng)輸入名稱“庫(kù)存”，公式設(shè)置為：=進(jìn)貨-發(fā)貨標(biāo)識(shí)低于安全庫(kù)存的存貨：開始-條件格式-新建規(guī)則-突出顯示單元格規(guī)則16第15頁(yè)/共46頁(yè)3.1杠桿平衡模型分析經(jīng)營(yíng)杠桿=邊際貢獻(xiàn)/（邊際貢獻(xiàn)-固定成本）假設(shè)經(jīng)營(yíng)杠桿系數(shù)為2，則表明銷量增長(zhǎng)10%，息稅前利潤(rùn)增長(zhǎng)（2×10%）20%，即高度的經(jīng)營(yíng)杠桿，表示銷貨量的小幅變動(dòng)，會(huì)引起利潤(rùn)的較大變動(dòng)。一家公司的固定成本愈高，其企業(yè)風(fēng)險(xiǎn)也愈高。故有大量固定成本的公司，即具有較高的營(yíng)業(yè)杠桿。企業(yè)一般可通過(guò)增加銷售額，降低單位變動(dòng)成本和固定成本等措施來(lái)降低經(jīng)營(yíng)杠桿和經(jīng)營(yíng)風(fēng)險(xiǎn)。財(cái)務(wù)杠桿是指由于債務(wù)的存在而導(dǎo)致每股利潤(rùn)的變動(dòng)大于息稅前利潤(rùn)變動(dòng)的杠桿效應(yīng)。財(cái)務(wù)杠桿系數(shù)=息稅前利潤(rùn)/(息稅前利潤(rùn)-資本總額*負(fù)債比例*利率）17第16頁(yè)/共46頁(yè)3.1杠桿平衡模型分析復(fù)合杠桿復(fù)合杠桿是指由于固定成本和固定財(cái)務(wù)費(fèi)用的存在而導(dǎo)致的普通股每股利潤(rùn)變動(dòng)率大于產(chǎn)銷量變動(dòng)率的杠桿效應(yīng)。復(fù)合杠桿系數(shù)=經(jīng)營(yíng)杠桿系數(shù)*財(cái)務(wù)杠桿系數(shù)杠桿平衡模型當(dāng)其他因素變動(dòng)時(shí)，測(cè)算銷量的變動(dòng)文件-選項(xiàng)-自定義功能區(qū)，在主選項(xiàng)卡勾選“開發(fā)工具”開發(fā)工具-控件-插入，選擇“數(shù)值調(diào)節(jié)鈕”，為變量添加調(diào)節(jié)鈕控件右鍵選擇數(shù)值調(diào)節(jié)鈕，設(shè)置控件格式，設(shè)置單元格鏈接，并使用鏈接公式使得該單元格鏈接可以控制變量值18第17頁(yè)/共46頁(yè)3.2盈虧平衡分析分析內(nèi)容銷量平衡分析單價(jià)平衡分析變動(dòng)成本平衡分析固定成本平衡分析控件應(yīng)用開發(fā)工具-控件-插入-滾動(dòng)條右鍵-單元格鏈接輸入變量計(jì)算公式：=當(dāng)前值*(（1+單元格鏈接/50-1)/2)，此公式使得變量以每次1%的幅度變化19第18頁(yè)/共46頁(yè)4.1加班計(jì)算返回星期幾=Weekday(日期，2)判斷加班類型=IF(ISERROR(VLOOKUP(B4,$J$2:$J$11,1,FALSE)),IF(OR(E4=6,E4=7),"周末加班","工作日加班"),"節(jié)假日加班")提取不重復(fù)姓名=INDEX(A$2:A$32,MATCH(0,COUNTIF(M$1:M1,A$2:A$32),0))統(tǒng)計(jì)加班合計(jì)數(shù)=SUMPRODUCT(($A$2:$A$32=M2)*($H$2:$H$32))20第19頁(yè)/共46頁(yè)4.2薪酬計(jì)算與分析獎(jiǎng)金匹配hlookup：水平查詢?cè)O(shè)置獎(jiǎng)金比例表，其中的參照值應(yīng)該取區(qū)間的最小值=hlookup(某銷售員獎(jiǎng)金，獎(jiǎng)金比例表，比例所在列，查詢方式）

其中查詢方式應(yīng)該設(shè)置為1，意為模糊查詢。工資變動(dòng)分析將工資與銷售收入數(shù)據(jù)轉(zhuǎn)化為指數(shù)，即基期為100，其他期間為：當(dāng)期數(shù)據(jù)/基期數(shù)據(jù)*100制作折線圖計(jì)算工資與銷售收入相關(guān)性：=correl(工資，銷售收入）計(jì)算工資與銷售收入各自的復(fù)合增長(zhǎng)率

=（當(dāng)期/基期）^(1/年數(shù)）-1

21第20頁(yè)/共46頁(yè)5.1折舊費(fèi)用計(jì)算已經(jīng)計(jì)提折舊月份計(jì)算=datedif(啟用資產(chǎn)日期，today()，”m”)其中m表示月份，還可以是y、d，分別表示年，天。折舊額計(jì)算直線法

=sln(原值，殘值，使用年限）雙倍余額遞減法

=ddb(原值，殘值，使用年限，第幾期）年數(shù)總和法

=syd(原值，殘值，使用年限，第幾期）22第21頁(yè)/共46頁(yè)5.2混合成本分解制作散點(diǎn)圖添加趨勢(shì)線右鍵-添加趨勢(shì)線，選擇線性勾選“顯示公式”和“顯示R平方值”選項(xiàng)公式Y(jié)=7.85x-6，其中7.85為變動(dòng)成本，6為固定成本函數(shù)方式也可使用函數(shù)計(jì)算變動(dòng)成本與固定成本變動(dòng)成本

=slope(成本，產(chǎn)量）固定成本

=intercept(成本，產(chǎn)量）23第22頁(yè)/共46頁(yè)5.3動(dòng)態(tài)費(fèi)用查詢制作控件開發(fā)工具-控件-插入，制作組合框和列表框右鍵-設(shè)置控件格式，為其設(shè)置數(shù)據(jù)源區(qū)域和單元格鏈接寫公式，將單元格鏈接里的序號(hào)變?yōu)榫唧w值

=index(參數(shù)列表，單元格鏈接）寫公式查詢數(shù)據(jù)

=SUMIFS(OFFSET(數(shù)據(jù)!$B$3:$B$42,0,參數(shù)!$C$1),數(shù)據(jù)!$B$3:$B$42,參數(shù)!$E$2,數(shù)據(jù)!$A$3:$A$42,統(tǒng)計(jì)!E8)（請(qǐng)參照案例）繪制餅圖顯示結(jié)果24第23頁(yè)/共46頁(yè)5.4拆分科目與余額分析沒(méi)有規(guī)律，不在同樣的位置，沒(méi)有統(tǒng)一的分隔符不能使用分列工具公式=MID(A2,LENB(A2)-LEN(A2)+1,LEN(A2))Len函數(shù)用于返回字符個(gè)數(shù)，一個(gè)漢字作為一個(gè)字符Lenb中，一個(gè)漢字作為2個(gè)字符Mid函數(shù)用于分拆文本

=mid(需拆分單元格，從第幾位拆分，拆分長(zhǎng)度）25第24頁(yè)/共46頁(yè)5.5費(fèi)用與產(chǎn)量相關(guān)性分析相關(guān)系數(shù)=correl(費(fèi)用，產(chǎn)量）圖表方法繪制折線圖圖表工具-布局-圖表元素選擇框，選擇系列“銷量”設(shè)置所選內(nèi)容格式-系列繪制在改為“次坐標(biāo)軸”26第25頁(yè)/共46頁(yè)6.1利潤(rùn)測(cè)算單變量求解數(shù)據(jù)-模擬分析-單變量求解目標(biāo)單元格設(shè)置為利潤(rùn)所在的單元格目標(biāo)值設(shè)置為目標(biāo)利潤(rùn)可變單元格設(shè)置為要求解的變量，比如銷量或價(jià)格等點(diǎn)擊確定即可看到計(jì)算結(jié)果27第26頁(yè)/共46頁(yè)6.2添加計(jì)算項(xiàng)比較預(yù)算與實(shí)際數(shù)據(jù)多重合并預(yù)算表與實(shí)際表合并預(yù)算與實(shí)際兩個(gè)表添加計(jì)算項(xiàng)將預(yù)算與實(shí)際的上級(jí)字段改名為版本將版本字段放入列標(biāo)簽將光標(biāo)放置在版本字段名上，選擇透視表工具-選項(xiàng)-公式-計(jì)算項(xiàng)將計(jì)算項(xiàng)名稱定義為“差異”，公式內(nèi)容為：=預(yù)算-實(shí)際，然后點(diǎn)擊添加按鈕，即可添加差異計(jì)算項(xiàng)去掉合計(jì)列右鍵-透視表選項(xiàng)-匯總和篩選，取消勾選“顯示行總計(jì)”28第27頁(yè)/共46頁(yè)6.3預(yù)算模板制作思路預(yù)算制作-跟蹤預(yù)算-比較分析模板制作色彩的運(yùn)用：區(qū)分和強(qiáng)調(diào)，引導(dǎo)作用控件的使用：選擇要顯示的不同的數(shù)據(jù)內(nèi)容圖表：直觀的展示導(dǎo)航：便利的操作名稱的運(yùn)用：高效的引用29第28頁(yè)/共46頁(yè)7.1加權(quán)平均資金成本加權(quán)平均資金成本W(wǎng)ACC(weightedaveragecostofcapital)WACC=（債務(wù)/資本）*債務(wù)成本*（1-企業(yè)所得稅稅率）+（1-債務(wù)/資本）*股權(quán)成本控件應(yīng)用開發(fā)工具-控件-滾動(dòng)條右鍵-設(shè)置控件格式-單元格鏈接創(chuàng)建公式關(guān)聯(lián)變量與單元格鏈接Sumproduct用于計(jì)算乘積之和，也可以用于計(jì)算多條件求和與計(jì)數(shù)=sumproduct(區(qū)域1，區(qū)域2)30第29頁(yè)/共46頁(yè)7.2貸款還款計(jì)算等額分期還款方式每期償還金額：=pmt(利率，期數(shù)，貸款金額）每期償還的利息：=ipmt(利率，第幾期，期數(shù)，貸款金額）每期償還的本金：=ppmt(利率，第幾期，期數(shù)，貸款金額）計(jì)算利率：=rate(期數(shù)，每期還款額，貸款額）名義利率與實(shí)際利率將名義利率轉(zhuǎn)化為實(shí)際利率：

=effect(名義利率，每年復(fù)利期數(shù)）將實(shí)際利率轉(zhuǎn)化為名義利率：

=nominal(實(shí)際利率，一年內(nèi)計(jì)息次數(shù)）31第30頁(yè)/共46頁(yè)8.1銀行存款余額調(diào)節(jié)表思路利用vlookup函數(shù)在銀行對(duì)賬單和銀行存款日記賬之間雙向查詢，出現(xiàn)#N/A即未達(dá)賬項(xiàng)，然后利用sumproduct函數(shù)將未達(dá)賬項(xiàng)分類求合計(jì)。公式銀行已收/企業(yè)未收款：=SUMPRODUCT((ISERROR($D$3:$D$19)=TRUE)*($A$3:$A$19>0)*($A$3:$A$19))其中，iserror()=true意味著企業(yè)未達(dá)，a3:a19>0意味著是企業(yè)未收款對(duì)賬單上的其他項(xiàng)目調(diào)節(jié)公式以此類推。32第31頁(yè)/共46頁(yè)8.2應(yīng)收賬款管理統(tǒng)計(jì)收款金額合計(jì)=sumifs(合同金額列，合同編號(hào)列，要統(tǒng)計(jì)的合同編號(hào)）分客戶的賬齡分析創(chuàng)建透視表，賬齡字段放進(jìn)行區(qū)域，金額放進(jìn)數(shù)值區(qū)域光標(biāo)在行標(biāo)簽區(qū)域，右鍵-創(chuàng)建組，將參數(shù)設(shè)為1,90,30將賬齡字段拖進(jìn)列標(biāo)簽將客戶字段拖進(jìn)行標(biāo)簽此時(shí)可以看到每家客戶的應(yīng)收賬款分布匯總情況還可雙擊希望查看明細(xì)的行，對(duì)明細(xì)數(shù)據(jù)進(jìn)行查看33第32頁(yè)/共46頁(yè)8.3應(yīng)收款賬齡分析1【插入】選項(xiàng)卡-選擇【數(shù)據(jù)透視表】2在“字段列表”工作區(qū)中，將過(guò)期天數(shù)字段拖入行標(biāo)簽，將金額兩次拖入數(shù)值區(qū)域3在透視表中的行標(biāo)簽區(qū)域內(nèi)點(diǎn)擊鼠標(biāo)右鍵，在右鍵菜單中選擇“組合”，將起始于，終止于，步長(zhǎng)分別改為：1,120,304光標(biāo)放在“金額2”的列上右鍵，選擇【值字段設(shè)置】，切換為“值顯示方式”，并在列表中選擇“占同列數(shù)據(jù)總和的百分比”34第33頁(yè)/共46頁(yè)8.4應(yīng)付賬款提醒思路利用條件格式進(jìn)行應(yīng)付款提醒步驟計(jì)算到期天數(shù)：=付款日期-today()選中到期天數(shù)列,開始-條件格式-新建規(guī)則-只為包含以下內(nèi)容的單元格設(shè)置格式設(shè)置單元格值<0,為單元格設(shè)置紅色填充色設(shè)置單元格值<10,為單元格設(shè)置藍(lán)色填充色35第34頁(yè)/共46頁(yè)9.1報(bào)銷單制作為分公司，部門，幣種，支付類型等信息設(shè)置有效性在參數(shù)表上創(chuàng)建部門列表數(shù)據(jù)-數(shù)據(jù)有效性設(shè)置有效性條件為“序列”，然后在下方的來(lái)源框選擇參數(shù)表上的部門列表提示：excel2010之前的版本不能跨表選擇有效性序列，需要為首先為序列定義名稱設(shè)置填寫提示信息數(shù)據(jù)-數(shù)據(jù)有效性，切換到“輸入信息”標(biāo)簽，填寫標(biāo)題和輸入信息保護(hù)選中需要填寫的單元格，右鍵-設(shè)置單元格格式-保護(hù)，取消勾選“鎖定”審閱-保護(hù)工作表-輸入密碼36第35頁(yè)/共46頁(yè)9.2名稱定義名稱可以代表一個(gè)單元格或者一個(gè)單元格區(qū)域，或者是常量，公式。名稱的定義：選中需要命名的單元格或區(qū)域，在界面左上角名稱框中輸入名稱后回車；需要注意的是：名稱框只能用于定義單元格和單元格區(qū)域的名稱，公式的名稱需要在【公式】【名稱管理器】中進(jìn)行定義。名稱的引用：需要引用某單元格時(shí)輸入為該單元格定義的名稱：＝名稱步驟1：選中需要命名的某個(gè)單元格或單元格區(qū)域。步驟2：在左上角名稱框輸入命名后回車。刪除名稱：選擇【公式】菜單【名稱管理器】；選中需要?jiǎng)h除的名稱，點(diǎn)擊“刪除”按鈕。37第36頁(yè)/共46頁(yè)9.3報(bào)表匯總快速匯總表=sum(起始表：結(jié)束表！需匯總的單元格）要求：各個(gè)表結(jié)構(gòu)必須一致利用透視表多重合并計(jì)算數(shù)據(jù)區(qū)域匯總調(diào)出多重合并計(jì)算數(shù)據(jù)區(qū)域功能快捷鍵：文件-選項(xiàng)-快速訪問(wèn)工具欄，選擇所有命令，在列表中找到“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А保x到右側(cè)點(diǎn)擊快速訪問(wèn)工具欄上的透視表向?qū)О粹o，選擇“多重合并計(jì)算數(shù)據(jù)區(qū)域”選項(xiàng)，選擇創(chuàng)建單頁(yè)字段，逐個(gè)選擇需合并的表，完成即可。優(yōu)點(diǎn)：數(shù)據(jù)可多可少，可在一個(gè)視圖上選擇查看各個(gè)表的數(shù)據(jù)38第37頁(yè)/共46頁(yè)9.4數(shù)字大寫轉(zhuǎn)化轉(zhuǎn)化效果公式中文小寫公式：=numberstring(b3,1)中文大寫公式：=numberstring(b3,2)逐字轉(zhuǎn)化：=numberstring(b3,3)另外的寫法中文小寫公式：=text(b3,”[dbnum1]”)中文大寫公式：=text(b3,”[dbnum2]”)逐字轉(zhuǎn)化：=text(b3,”[dbnum1]0”)39第38頁(yè)/共46頁(yè)9.5應(yīng)用技巧格式編號(hào)要求：在編號(hào)前加N0.字符，并使得編號(hào)為8位，不足前面補(bǔ)零="NO."&TEXT(A2,"00000000")讀取多個(gè)其他工作表上的數(shù)據(jù)公式：=INDIRECT(D4&"!b2")其中D4是工作表名稱所在單元格，b2是要讀取的數(shù)據(jù)所在單元格復(fù)制公式到其他工作表名稱對(duì)應(yīng)的行取多個(gè)工作表名稱選