Excel在財務(wù)管理中的高級應(yīng)用(財務(wù)人員必備)

1、Excwl財務(wù)管理高級應(yīng)用2Parti設(shè)計并創(chuàng)建各種財務(wù)分析模型21.1. 國際通用財務(wù)預(yù)測模版解析21.2. 控件的運(yùn)用21.2.1. 調(diào)出【開發(fā)工具】選項卡21. 2. 2.控件類型與選擇21. 2. 3.菜單，按鈕，滾動條等效果的制作21. 2.4.從窗體中獲取數(shù)據(jù)到單元格并參與運(yùn)算31.3. 模擬運(yùn)算表31.4. 敏感性分析模型41.5. 公司估值模型(Conauy Valuation) 416新產(chǎn)品上市測算模型7Part2高級圖表設(shè)計與制作82.1. 經(jīng)典商務(wù)圖表觀摩與分析82.2. 恰當(dāng)選擇圖表類型的原則82.3. 制作圖表的信噪比原則82.4. 專業(yè)商務(wù)圖表的制作82.4.1.

2、頂級商務(wù)圖表的重要標(biāo)志一配色82.4.2. 控制線，趨勢線等新的圖表元素的添加82.4.3. 各類必要格式調(diào)整92.5. 不同數(shù)量級的圖表比較一指數(shù)化92.6. 高速圖表的途徑102.6.1. 快速復(fù)制圖表樣式102.6.2. 將滿意的圖表存為模版102.7. 雙坐標(biāo)圖表102.8. 用廿特圖表示項目進(jìn)程122.9. 動態(tài)圖表觀摩與思路分析14Part3管理儀表盤153.1. 公司管理儀表盤設(shè)計效果觀摩153.2. 儀表盤制作思路1533 利用透視表制作儀表盤1534 利用函數(shù)等制作儀表盤16Part4重整數(shù)據(jù)流174.1. Excel表格結(jié)構(gòu)174.1.1. 數(shù)據(jù)列表174.1.2. 二維表

3、174.2. 鏈接數(shù)據(jù)庫與OLAP報表制作174.3. 多種數(shù)據(jù)整理方法214.3.1. 整合多個數(shù)據(jù)表2143.2. 利用透視表進(jìn)行同比分析264.3.3. 利用透視表進(jìn)行環(huán)比分析274.3.4. 利用透視表計算累計數(shù)28Excel財務(wù)管理高級應(yīng)用Parti 設(shè)計并創(chuàng)建各種財務(wù)分析模型1.1. 國際謹(jǐn)用財努預(yù)測模版解析構(gòu)成要素：1 假設(shè)(Asuniptioii)通貨膨脹，稅率，利率，折舊方法2項目預(yù)測收入(Sales),成本(Cost),費(fèi)用(Expense)，固定資產(chǎn)預(yù)測(FA)3損益表(P&L),資產(chǎn)負(fù)債表(BS),現(xiàn)金流量表(Cashflow) 當(dāng)前數(shù)(Cunent)累計數(shù)(Y

4、ear to date)實際數(shù)(Actual)預(yù)算數(shù)(Budget)4 比較(Compaction)5 儀表盤(Dash board)1.2. 控件的運(yùn)用1.2.1.調(diào)出【開發(fā)工具】選項卡點擊office按鈕-excel選項-常用- “在功能區(qū)顯示開發(fā)工具選項卡”1. 2. 2.控件類型與選擇組合框：用于項目較多時列表框：用于項目較少時念動條：用于需要調(diào)整數(shù)值時，常用于測算模型 按鈕：用于執(zhí)行命令或宏復(fù)選框：用于多選1. 2. 3.菜單，按鈕，滾動條等效果的制作選擇【開發(fā)TA【插入】【表單控件】.選擇【組介框兒按卜鼠標(biāo)左鍵，拖放出合適的人小后釋放鼠標(biāo)，出現(xiàn)卜拉菜單形狀。選中該卜拉菜單，右鍵Ex

5、cel2007應(yīng)用培訓(xùn)選擇【設(shè)置控件格式】為聖切復(fù)制(£)A粘貼)組合©疊放次序)指定宏設(shè)置控件格式g).在【控制】菜單上，去選擇相應(yīng)的數(shù)據(jù)源?！救S陰影】選項是卞拉菜單顯示效果設(shè)定。I大小I鮒I ga I訃BO數(shù)揺源區(qū)域 d) :parameters!$B$31 $B$7禹單元格鋌接C)：I下拉顯示項數(shù))： 8叵三維陰影Q)【單九格鏈接】:該處指定的單元格中將存儲在控件中被選中的項目在數(shù)據(jù)源中的序列號。我們經(jīng)常利用該序列號引用控件選中的值，還町以利用該值制作動態(tài)圖表。1. 2. 4.從窗體中獲取數(shù)據(jù)到單元格并參與運(yùn)算1對滾動條進(jìn)行設(shè)置右鍵【設(shè)置控件格式】【控制】【單元格鏈

6、接】，選擇一個單元格2讓控件控制表格的計算假設(shè)點擊-下滾動條，希望變動1%的幅度，則公式應(yīng)該設(shè)為：=當(dāng)前值* (1+(單元格鏈接 /50-1)/2),這樣數(shù)值可以每次點擊1%的幅度正反向變動。1.3.模擬運(yùn)算表1模擬運(yùn)算表概念其實是把若干個柑同公式的輸入生成對應(yīng)的值簡化成一個公式的輸入產(chǎn)生對 應(yīng)的值。2模擬運(yùn)算表與普通公式的區(qū)別模擬運(yùn)算表一次性輸入公式，如有更改也只需要改一個地方不用過女考慮在公式中使用絕對引用還是相對引用表格中的數(shù)據(jù)無法單獨修改公式中引用的參數(shù)必須引用“輸入引用列，行的單元格”指向的單元格普通的運(yùn)算方式(公式，然后復(fù)制到各個對應(yīng)的地方)公式需要復(fù)制到每個對應(yīng)的單元格需要詳細(xì)考

7、慮每個參數(shù)在復(fù)制中需要發(fā)生的/不發(fā)生的變化，以決定使用絕對引 用還是相對引用每次如果需要更改公式，就要將所有的地方再重新復(fù)制一遍 表中的數(shù)據(jù)可以單獨修改公式中引用的參數(shù)直接指向數(shù)據(jù)列/行14敏感性分析模型1槪念硏究和制約利潤的有關(guān)因素發(fā)生某種變化時，利潤變化程度的一種分析方法。 那些對利潤影響人的因素稱為敏感因素，反之，稱為非敏感因素-般情況下，影響利潤的因素有4個：價格、單位變動成本、銷售量和固定成 本2敏感系數(shù)：敏感系數(shù)是反映敏感程度的指標(biāo)某因素的敏感系數(shù)=利潤變化()/該因素變化()3判斷標(biāo)準(zhǔn)；敏感系數(shù)的絕對值1,即肖某影響因素發(fā)生變化時，利潤發(fā)生更人程度的變化, 該影響因素為敏感因素敏

8、感系數(shù)的絕對值VI，即利潤變化的幅度小于影響因素變化的幅度，該因素為 非敏感因素敏感系數(shù)的絕對值=1，即影響因素變化合導(dǎo)致利潤相同程度的變化，該因素亦 為非敏感因素1.5.公司估值模型(Company Valuation )公司估值的概念企業(yè)價值賬面價值 資產(chǎn)負(fù)債表上的資產(chǎn)，反映歷史成本，用于會計目的巾場價值如股票價值，反映未來收益，投資者關(guān)注的是市場價值公司估值 公司估值是指著眼于公司本身，對公司的內(nèi)在價值進(jìn)行評估。公司內(nèi)在價值 決定于公司的資產(chǎn)及其獲利能力。 公司估值是投融資、交易的前提。一家投資機(jī)構(gòu)將一筆資金注入企業(yè)，應(yīng)該 占有的權(quán)益首先取決于企業(yè)的價值。公司估值的作用公司估值有利于我們

9、對公司或英業(yè)務(wù)的內(nèi)在價值進(jìn)行正確評價，從而確立對各種交易進(jìn)行訂價的基礎(chǔ)。 籌集資本(capital raismg): 收購介并(mergers & acquisitions): 公司匝組(corporate restructuring): 出售資產(chǎn)或業(yè)務(wù)(divestiture)財務(wù)模型和公司估值是投資銀行的重要方法，廣泛運(yùn)用于各種交易。估值的不確定性 巴菲特：“如果一項資產(chǎn)目前市價只是略低于其內(nèi)在價值，我們沒有興趣買 入它；只有在有'顯著折扣'時我們才會買入”估值最根本的方法：徹底了解這家公司主要估值方法可比公司法歷史交易法 現(xiàn)金流貼現(xiàn)法（DCF） DCF： Disc

10、ounted Cash Flow 現(xiàn)金流量折現(xiàn)DCF的方法是指：預(yù)測未來現(xiàn)金流，并用貼現(xiàn)率和終值計算方法計算現(xiàn)金 流的貼現(xiàn)值，以此計算企業(yè)價值。使用情形 在發(fā)達(dá)國家市場中，各種數(shù)據(jù)比校完善，公司口趨成熟，DCF成為 通用的估值方法。-中國DCF方法似乎受到許多局限，一方面是基礎(chǔ)數(shù)據(jù)缺乏，另一方 面是一部分公司的持續(xù)經(jīng)營能力令人懷疑。優(yōu)點完幣的評價模型，理論上最完善- 考慮公司發(fā)展的長期性，承認(rèn)資金時間價值缺點 數(shù)據(jù)估算具有高度的主觀性與不確定性貨幣的時間價值與貼現(xiàn)-貨幣具有時間價值當(dāng)前的一元錢比未來的一元錢值詼原因：貨幣投資可以產(chǎn)生增值- 比如存入銀行可以產(chǎn)生利息，未來就不止一元錢了。貼現(xiàn)未來

11、的現(xiàn)金流需要使用一個貼現(xiàn)率（如銀行利率），折算成今天值多少錢， 這個折算的過程叫貼現(xiàn)。估值-對公司估值時，不是簡單地把未來產(chǎn)生的現(xiàn)金流直接相加，而是選取一個恰 當(dāng)?shù)馁N現(xiàn)率，將未來的現(xiàn)金流貼現(xiàn)到現(xiàn)在，然后相加。加權(quán)平均資金成本（WACC）如何確定貼現(xiàn)率？- 一般使用WACC （加權(quán)平均資本成本）作為貼現(xiàn)率 巴菲特使用美國長期國債利率 WA.CC概念- 英文Weighted Average Cost of Capital的縮寫。WA.CC代表公司整體半均資 金成本，町用來衡量一個項目足否值得投資：項目的回報必須不低于WACC。 WACC計算 計算WACC時，先算出構(gòu)成公司資本結(jié)構(gòu)的各個項冃如普通股

12、、優(yōu)先股、 公司債及其他長期負(fù)債各自的資金成本或要求回報率，然后將這些回報率按 各項目在資本結(jié)構(gòu)中的權(quán)重加權(quán)，即可算出加權(quán)平均資本成本。計算公式 WACC=（債務(wù)/資本）*債務(wù)成本1* （】企業(yè)所得稅稅率）+ （1 債務(wù)/資本）* 股權(quán)成本其中，債務(wù)成本和股權(quán)成本用債務(wù)人和股東要求的收益率表示。公司估值現(xiàn)金流貼現(xiàn)法就是把公司所右投資者的現(xiàn)金流，包括債權(quán)人和股 權(quán)投資者的現(xiàn)金流折現(xiàn)，而折現(xiàn)率則是加權(quán)平均資本成本。因此，WACC是 投資公司價值評估的一個重要的計算參數(shù)。股本成本的計算股權(quán)成本=無風(fēng)險收益率+風(fēng)險溢價*Be【a無風(fēng)險收益率：國債利率或銀行利率風(fēng)險溢價：股票市場長期平均的回報率高出無風(fēng)

13、險收益率的部分-Beta：特定企業(yè)相對于股票山場的波動性，反映特定企業(yè)自身的風(fēng)險如何計算Beta。首先要分析可比公司的beta,由于可比公司的資本機(jī)構(gòu)可能與被估值企業(yè)差異 很大，所以要將可比公司的beta折算成無杠桿的beta-町比公司無杠桿beta的平均值/中間值用作被估值企業(yè)的風(fēng)險系數(shù)，為反映財務(wù)風(fēng)險，需要重新考農(nóng)被估值企業(yè)的資本結(jié)構(gòu)，計算有杠桿beta無杠桿beta和有杠桿beta的關(guān)系：-D=債務(wù)，E=股權(quán)，t=稅率，Pref./E=優(yōu)先股- 無杠桿 beta=*杠桿 beta/（ 1 +（D/E）（ 1 -t）+（Pref./E） IPO定價與發(fā)行公司估值的關(guān)系 IPO概念I(lǐng)PO定價

14、（Initial Public Offering）是指首次獲準(zhǔn)公開發(fā)行股票匕市的公司與其承 銷商共同確定的將股票公開發(fā)售給特定或非特定投資者的價格。兩者關(guān)系 公司估值是IPO定價的基礎(chǔ)IPO定價與公司估值不能等同。IPO還通過選擇合適的發(fā)欝機(jī)制來確定影響價 格的因素（如市場需求）以城終敲定價格 自由現(xiàn)金流量 自由現(xiàn)金流是一種財務(wù)方法，用來衡量企業(yè)實際持有的能夠回報股東的現(xiàn)金。指 在不危及公司生存與發(fā)展的前提卜町供分配給股東（和債權(quán)人）的最人現(xiàn)金額。 自由現(xiàn)金流量等于企業(yè)的稅后凈營業(yè)利潤（即將公司不包括利息收支的營業(yè)利潤 扣除實付所得稅稅金之后的數(shù)額）加上折II及攤銷等非現(xiàn)金支出，再減去營運(yùn)資本

15、 的追加和物業(yè)廠房設(shè)備及其他資產(chǎn)方面的投資。它是公司所產(chǎn)生的稅后現(xiàn)金流最 總額，町以提供給公司資本的所有供應(yīng)者，包括債權(quán)人和股東。” 自由現(xiàn)金流量=（稅后凈利潤+利息+折舊及攤銷等非現(xiàn)金支出）一（資本支出+營運(yùn) 資本增加）中國計算公式=經(jīng)營活動產(chǎn)生的現(xiàn)金流量凈額-資本性支出=經(jīng)營活動產(chǎn)生的現(xiàn)金流屋凈額-（購建固定、無形和其他長期資產(chǎn)所支付的現(xiàn) 金-處置固定、無形和其他長期資產(chǎn)而收回的現(xiàn)金凈額）公司估值財務(wù)模型建立財務(wù)模型的重要步驟： 在excel里建立歷史的現(xiàn)金流量表，并把資產(chǎn)負(fù)債表，損亞表利現(xiàn)金流屋表鏈接在一 起 現(xiàn)金流計算-營運(yùn)資本=存貨+應(yīng)收賬款+其他流動資產(chǎn)應(yīng)付賬款其他流動負(fù)債 現(xiàn)金=

16、凈利潤+折舊# ；f本-資本支出+新增股本-股利+債務(wù)建模-財務(wù)模型架構(gòu)經(jīng)營預(yù)測收入預(yù)測成本費(fèi)用預(yù)測可以使用占收入的百分比來表述運(yùn)營資本預(yù)測根據(jù)有關(guān)資產(chǎn)，負(fù)債相對于收入或成本的周轉(zhuǎn)天數(shù)來計算存貨周轉(zhuǎn)天數(shù)應(yīng)收賬款周轉(zhuǎn)天數(shù)應(yīng)付賬款周轉(zhuǎn)天數(shù)資本支出預(yù)測融資政策債務(wù)政策 以還債為首要任務(wù),還是保持適卅的債務(wù)比例？何時是償還債務(wù)的最佳時機(jī)？如何達(dá)到 最佳資本結(jié)構(gòu)？- 融資政策計劃新增多少股本或貸款運(yùn)營資本政策- 需要保持的最低現(xiàn)金模型建立格式 手工輸入，公式，鏈接分別使用不同色彩標(biāo)識- 邏輯清晰- 適當(dāng)使用名稱定義注釋和幫助系統(tǒng)靈活性- 便于對變量進(jìn)行調(diào)整1.6.新產(chǎn)品上市測算模型制作含稅控件 開發(fā)工具

17、-插入-表單控件，選擇“復(fù)選框”，繪制復(fù)選框形狀 右鍵點擊復(fù)選框，設(shè)置控件格式，在控制標(biāo)簽下設(shè)蜀單元格鏈接 設(shè)置價格公式為：=if（bl=tme,價格，價格/1.17）第7頁共29頁Excel2007應(yīng)用培訓(xùn)Part2高級圖表設(shè)計與制作21經(jīng)典商努圖表觀摩與分析22恰當(dāng)選擇圖表類型的原則2.3. 制作圖表的信噪比原則我們在做設(shè)計的時候，要考慮的不是還能添加一些什么元素，而是要考慮還能不能再減少一 些什么元素。2.4. 專業(yè)商勢圖表的制作2.4.1. 頂級商務(wù)圖表的重要標(biāo)志一配色1. 獲取商業(yè)周刊風(fēng)格的圖表樣本：搜索圖片網(wǎng)址：2. 利用coloipix軟件獲取商業(yè)周刊圖表配色RGB值3. 調(diào)整E

18、xcel色彩為商業(yè)周刊風(fēng)格的彩色設(shè)置【圖表工民格式選項卡】【形狀填充】【其他填充顏色】【口定義】，對其RGB的值進(jìn)行 設(shè)定：2.4.2. 控制線，趨勢線等新的圖表元素的添加制線通常為一條水平線,它可以是平均值線,也可以是預(yù)算線,或者某個特定的值, 用以評估實際情況是否超過了平均水平或者達(dá)到了預(yù)算，并以直觀的形式提供預(yù)警。 制作方法：1.利用average函數(shù)得出樣本數(shù)據(jù)的平均值，并以該數(shù)值在數(shù)據(jù)表中添加一列輔助數(shù) 據(jù)，然后利用數(shù)據(jù)表制作柱形圖。dataaverageSalesbeijingr n390150 shanghai6390100 1- I- guangzhou |110990:Il

19、ll II II ll IIchengdu10290wuhan_3790UJSiRZCmWWSyShenyang11690 data everogeSales1502.選中紅色average數(shù)據(jù)系列，調(diào)整其圖表類型為折線圖，即町得到水平的控制線第9頁共29頁Excel2007應(yīng)用培訓(xùn)第#頁共29頁Excel2007應(yīng)用培訓(xùn)3.還可以添加其他必要的各種元素，用于強(qiáng)化圖表主題。2.4.3.各類必要格式調(diào)整1. 將圖表西文和數(shù)字字體改為Anal2. 將橫坐標(biāo)刻度線刪除選中橫坐標(biāo)，選擇【圖表工具】【布局選項卡】【設(shè)盤所選內(nèi)容格式】,“主要刻度線類型” 選擇“無”。3. 數(shù)據(jù)系列分類間距改為40%選中某

20、數(shù)據(jù)系列，右鍵【設(shè)置數(shù)據(jù)系列格式】4. 對數(shù)據(jù)排序后再制作柱形圖5. 消除折線圖兩端的空白：選中橫坐標(biāo)，右鍵【設(shè)置坐標(biāo)軸格式】.將位置坐標(biāo)軸改 為在刻度線之上選項：|位富坐襖軸：在刻廃線上（K）©刻度線之旬型）2.5. 不同數(shù)量級的圖表比較一指數(shù)化用于比較不同數(shù)最級的變化趨勢。用各期數(shù)據(jù)/基期數(shù)據(jù)之后得到的指數(shù)做圖表其中, 基期是第一期數(shù)據(jù)。26高速圖表的途徑2.6.1.快速復(fù)制圖表樣式如果需要制作一個和現(xiàn)冇圖表完全相同的圖表，不需要重復(fù)對各類格式進(jìn)行設(shè)宜，只需復(fù)制 圖表，【選擇性粘貼】【格式】，即可。2.6.2.將滿意的表存為模版第11頁共29頁Excel2007應(yīng)用培訓(xùn)如果希望將

21、制作好的圖表保存卜來供今后調(diào)用，選擇【另存為模版】；調(diào)用時，選擇【更改 圖表類型】，在【模版】類別中選擇自己的模版即可。2.7.雙坐標(biāo)圖表利用卜面的數(shù)據(jù)制作雙坐標(biāo)圖衷:234567區(qū) 徽建蘇海江東 安福江上浙華實際數(shù)6853895616103119261953263370計劃數(shù)83521392623578206932354190090完成率82.1%64 3%68. 3S57. 6%83. 0%70. 3%第#頁共29頁Excel2007應(yīng)用培訓(xùn)第#頁共29頁Excel2007應(yīng)用培訓(xùn)選擇【插入】菜單，選擇圖表區(qū)域中的柱形圖，第種子圖表類型簇狀柱形圖。卻煦二編色O柱形圖折卿 護(hù)圖 條形圖 酬囹

22、取點圖其他圖表圏表二堆拄疋圖點擊橫坐標(biāo)上“計劃數(shù)”右側(cè)位置，則町以選中完成率的數(shù)據(jù)系列實附數(shù))+劃數(shù)完成車第#頁共29頁Excel2007應(yīng)用培訓(xùn)第#頁共29頁Excel2007應(yīng)用培訓(xùn)點擊右継，在右鍵菜單中選擇【設(shè)置數(shù)據(jù)系列格式】:第#頁共29頁Excel2007應(yīng)用培訓(xùn)更改至列罔表美型刪除重設(shè)以匚配樣式笆)選提數(shù)碁迥三鮒轉(zhuǎn)®酒加數(shù)揭標(biāo)空迪添力臨刪遲)芳 設(shè)置瓏至列招式®，在數(shù)據(jù)系列格式界面上，將系列繪制在由“主坐標(biāo)軸”改為“次坐標(biāo)軸”I-系列迢項 険充邊電筋目系列選項爭夕壘a分隔 Q £3三御艷或分類問距醴)無同距j753大剛魄粘的WitQ王坐初站尖坐護(hù)0Q)

23、再次選中系列“完成率”，右鍵菜單選擇【更改系列圖表類型】:刪險重設(shè)呃酎寫迪ah更改殺列圖表類型Q%三維旋轉(zhuǎn)®添加加標(biāo)簽®添加設(shè)置站至列梧式(£)選擇折線圖，點確定圖表標(biāo)題2580.0%70.0%60.0%50.0%40.0%30.0%20.0%10.0%安矗 sa江蘇上?5浙江實環(huán)計計劃數(shù)一長危巫90.0%0 3%或坐標(biāo)圖表制作完成。第#頁共29頁Excel2007應(yīng)用培訓(xùn)28用日特圖表示項目迸程制作甘特圖,需要以下4列數(shù)據(jù):ABC1D1任務(wù)升始日期肋需時1日天數(shù)結(jié)鬼日期22008-7-142008-7-53頃目啟可2008-7-5L<112008-7-94

24、鴛求分祈2003-7-752008-7-125系統(tǒng)分忻2008-7-9122008-7-216羽槍設(shè)立2008-7-20202002-8-97編與代碼2008-7-25252008-8-198文科制作2008-8-1822008-8-20首先制作堆枳條形圖；然后點擊【設(shè)計】菜單卜的【選擇數(shù)據(jù)】選中“結(jié)束LI期”系列, 按【刪除】按鈕將其刪除。在【布局】菜單利用當(dāng)前所選內(nèi)容功能區(qū)的下拉菜單，選擇“垂直軸”，然后點擊【設(shè) 置所選內(nèi)容格式】按鈕：垂亙供別）軸毎設(shè)®儺內(nèi)容格式罵重設(shè)以蘭前所選內(nèi)容在出現(xiàn)的【設(shè)置坐標(biāo)軸格式】對話框上，將【逆序類別】選項打勾，將橫坐標(biāo)軸交叉選項改 為【最大分類】選

25、項，將縱坐標(biāo)的項目排列次序反轉(zhuǎn)：選中開始口期數(shù)據(jù)系列，右鍵點擊，選擇【設(shè)置數(shù)據(jù)系列格式】選項 數(shù)據(jù)系列格式對話框中將邊框和填充都改選為無。設(shè)董數(shù)克奚列梧式系列選頂邊框顏色 邊框樣式陰影三維格式填充i無填充®純色填丸）©浙變煩充®O圖片或紋理頃充邏）&自動也）以互補(bǔ)色代裘負(fù)值Q）第15頁共29頁Excel2007應(yīng)用培訓(xùn)此時需要將橫坐標(biāo)開始口期改為真正的項目開始口期：在excel中，每一個日期都對應(yīng)一個數(shù)值，選中B2,右鍵點擊，選擇【設(shè)置單元格格式】, 將其數(shù)字類型改為“常規(guī)”，査看2008-7-1對應(yīng)數(shù)值為39630,點擊【取消】退出：設(shè)言裝元袍fS云當(dāng)坂

26、申元格格弍不包含仔佰恃走的數(shù)寧咯式。第#頁共29頁Excel2007應(yīng)用培訓(xùn)選中橫坐標(biāo)點擊右鍵，選擇【設(shè)置坐標(biāo)軸格式】，切換到【刻度】.將最小值改為39630. 同樣將橫坐標(biāo)最大值改為真正的項目結(jié)束日期39680.坐標(biāo)磁頁H?t線殺說色陰黒三維格式 對齊方式坐腳選項最小值:最大値：©主要刻度戦:U 次要亥隈單位：O1更序刻度值）自動® 自云辦QD 皀動?；囟≦）0 81定 回定Q） 固守CE）巴對數(shù)別滾a）基：|io|顯示羊位）：【無在圖表上顯示渙!雯卑仁標(biāo)簽（S）第#頁共29頁Excel2007應(yīng)用培訓(xùn)甘特圖制作完成。2.9.動態(tài)圖表觀摩與思路分析選中北京，深圳，上海工作

27、表上A2:M7的區(qū)域，分別定義北京，深圳，上海的名稱; 選擇【開發(fā)工具】【插入】【表單控件】,選擇“選項”按鈕，畫出如下三個選項按鈕:第#頁共29頁Excel2007應(yīng)用培訓(xùn)右鍵選中選項按鈕，在右鍵菜單中選擇【設(shè)垃控件格式】.在跳出的對話框中選擇【控制】 標(biāo)簽.在【單元格鏈接】中選擇A16：顏色與繇I大小I保護(hù)I廈性I恤| ： WJ 值未迭擇Q）Q已選擇C）混臺型（M）在A17單元格中輸入公式：=choose（al6, ”北京”,”上?！?”深圳”） 將北京工作表上的業(yè)務(wù)列表復(fù)制粘貼到制作雙曲線圖表的肖前工作表上; 選中A3, A4單元格，選擇【數(shù)據(jù)】菜單中的數(shù)據(jù)有效性：分列刪陰數(shù)菇合并計箕假

28、設(shè)分析 更復(fù)項有效性r-繃ST旦將【有效性】對話框上的有效性條件，由【任何值】改為【序列】琳有效性設(shè)畫L輸入信息I出錯警吿|瞅法模式有效性條件何PIH忽略空值CB）艾本走 顯ms文自第#頁共29頁Excel2007應(yīng)用培訓(xùn)第#頁共29頁Excel2007應(yīng)用培訓(xùn)在【來源】框中選擇粘貼過來的業(yè)務(wù)列表第#頁共29頁Excel2007應(yīng)用培訓(xùn)數(shù)堀有效性i殳置輸入值息|出諸誓吿丨諭入法模式'有數(shù)性雜件允詐):蔚1F “忽畸空值©)數(shù)據(jù)(D):“提供,拉爺頭広)介于來源:=$H$f$H$6區(qū)在B16單元中寫入以下公式，并復(fù)制到該行1-12月的單元格中: 牙=VL00KUP($A$3TI

29、NDIRECT($A$17), COLUMN()-1, 0) 依據(jù)此數(shù)據(jù)表制作雙曲線圖Part3管理儀表盤3.1.公司管理儀表盤設(shè)計效果觀摩3.2.儀表盤制作思路如果數(shù)據(jù)源為數(shù)據(jù)列表，那么町以使用透視表作為制作儀表盤的工具。 如果數(shù)據(jù)源不是數(shù)據(jù)列表，那么町以選擇恰當(dāng)?shù)牟罃?shù)與公式來制作儀表盤。3.3. 利用透視表制作儀表盤1. 創(chuàng)建3個表，分別命名為數(shù)據(jù)源，計算表，儀表盤，分別放置原始數(shù)據(jù)，計算過程和終 端用戶訪問界面。2. 利用原始數(shù)據(jù)創(chuàng)建透視表，如下圖：AB,1行業(yè)2 T3少猶血:早寸4分區(qū)5川購分區(qū)23343c i劭r；吃19221T廣序分區(qū)3如汎8河南分區(qū)247569黑吉分巨19878V

30、 i3dl分區(qū)182313. 選擇“數(shù)據(jù)透視表工具選項卡” - “選項” - “數(shù)據(jù)透視圖”，創(chuàng)建透視圖4. 右鍵點擊透視圖，選擇移動圖表，將透視圖移動到儀表盤工作表上。5. 選中透視表，選擇“數(shù)據(jù)透視表工具”-“選項”-“移動數(shù)據(jù)透視表”，把透視表也移 動到儀表盤工作表上。6. 將透視表主體部分隱藏，只保留報表篩選區(qū)域與透視圖，這樣就完成了動態(tài)的儀表盤中 的一個指標(biāo)，如下圖：lilt213業(yè)3233 13536卜轉(zhuǎn) lasrp匚世和£1!9&Q分區(qū)銷偉屮(人gw 爭«叭 兇兇3?科粗 UJntlc untjlr 兇8廣H8求HI 皿3?£»0&

31、#171;: klAlrRX8«=-第19頁共29頁Excel2007應(yīng)用培訓(xùn)第#頁共29頁Excel2007應(yīng)用培訓(xùn)3.4. 利用函數(shù)等制作儀表盤1. 目標(biāo)：制作儀衷盤上的一個指標(biāo)：可分區(qū)來看的產(chǎn)品銷量的動態(tài)指標(biāo).2. 首先利用透視表得到分區(qū)與產(chǎn)品的列表，然后制作分區(qū)的列表框：“開發(fā)工具” - “插入” - “表單控件” - “組合框”右鍵點擊繪制好的組合框，右鍵菜單里選擇“設(shè)宣控件格式”，在數(shù)據(jù)源區(qū)域里選擇分 區(qū)列表，在單元格鏈接里選擇分區(qū)列表上方的單元格：第#頁共29頁Excel2007應(yīng)用培訓(xùn)3. 在任意單元格里，輸入函數(shù)：=Choose (c2p c3, c4,c20)4.

32、 在產(chǎn)品列表右側(cè)的列里輸入以下函數(shù)：玉 二SUHFRODUCT 匸九t機(jī) 眶$2:巧$389ASh“t2!$D$2k(九21 $F$2:丹$3899=Sh"t2!G3* Icht乙！$G$2:$G$3899)5. 根據(jù)査詢結(jié)果制作柱形圖，此圖表為動態(tài)指標(biāo)，在列表框中選擇不同分區(qū)，即可查看不 同分區(qū)的不同產(chǎn)品的銷量情況，如卜圖：Part4重整數(shù)據(jù)流4.1. Excel表格結(jié)構(gòu)4.1.1. 數(shù)據(jù)列表數(shù)據(jù)列表是數(shù)據(jù)庫內(nèi)表的結(jié)構(gòu)，其主要特征是：每一列都是一個獨立的維度。這種結(jié)構(gòu)適合 快速地對數(shù)據(jù)進(jìn)行各種整理與分析。4.1.2. 二維表二維表是指-個表格的行與列中齊有一個維度。這種表格不適介快

33、速對數(shù)據(jù)進(jìn)行齊種整理與 分析。4.2.鏈接數(shù)據(jù)庫與OLAP報表制作ODBC的介紹存儲數(shù)據(jù)并不是Excel的特長，Excel設(shè)計和優(yōu)化的目的是綜介、分析及顯示數(shù)據(jù)。它僅有 一些基本的數(shù)據(jù)庫功能，當(dāng)用戶覺得簡便的重用性人于功能時，就應(yīng)該使用它們。Excel中右一些方法，町以將這些工具和真實數(shù)據(jù)庫管理系統(tǒng)緊密結(jié)合在一起，利用這些方 法,用戶可以創(chuàng)建-些系統(tǒng)，在數(shù)據(jù)庫管理系統(tǒng)的強(qiáng)人功能和工作表的功能中達(dá)到一種平衡。ODBC是開放式數(shù)據(jù)庫互連(Open Database Connectivity)的縮寫。它是種標(biāo)準(zhǔn)，許參數(shù) 據(jù)庫程序和數(shù)據(jù)庫管理系統(tǒng)都釆用這種標(biāo)準(zhǔn)。如果用戶刈一個數(shù)據(jù)源，如與ODBC兼容的

34、 Access數(shù)據(jù)庫，那么就可以將數(shù)據(jù)輕松的導(dǎo)入到Excel中。不是所冇的數(shù)據(jù)庫都冇同樣的結(jié) 構(gòu)。為了確定這些數(shù)據(jù)庫是否可以和其他應(yīng)用軟件相連ODBC軟件生產(chǎn)商提供了驅(qū)動程序, 在安裝Microsoft Office時，按轉(zhuǎn)程序會自動地為Access、Paradox> d BASE以及SQL Server 等安裝幾個驅(qū)動程序。在將數(shù)據(jù)從數(shù)據(jù)庫導(dǎo)入到Excel中之前，用戶需要確認(rèn)自C想使用的 數(shù)拯庫類型。這樣，Excel才能知道使用何種驅(qū)動程序和數(shù)據(jù)庫相連。在確定想要使用的數(shù)據(jù)源類型后，還必須提供一個査詢(query)。査詢是用標(biāo)準(zhǔn)的結(jié)構(gòu)査詢 語言寫出的一系列指令，這種查詢語言一般形成SQ

35、L一那些指令提供了一些信息，如使用數(shù) 據(jù)庫中的哪些表，需要表中哪些字段的數(shù)據(jù)，以及在選擇要檢索記錄的過程中是否運(yùn)用某些 標(biāo)準(zhǔn)等。Microsoft Office還有一個叫做Microsoft Query的程序。從數(shù)據(jù)庫導(dǎo)入數(shù)據(jù)不久， Microsoft Query就開始運(yùn)行了。它將幫助用戶設(shè)計査詢，并從數(shù)據(jù)庫中檢索所需要的記錄。從數(shù)據(jù)庫導(dǎo)入數(shù)據(jù)是一個能夠兩步完成的過程：首先確定數(shù)據(jù)源，然后再創(chuàng)建查詢命令。點擊“獲取數(shù)據(jù)”。選擇“新數(shù)據(jù)源”，按確定按鈕。第#頁共29頁選擇數(shù)據(jù)源輸入數(shù)據(jù)源名稱.選擇相應(yīng)的數(shù)據(jù)庫驅(qū)動，點擊連接”。在卜圖界面上點擊"選擇”按鈕。在下圖界面上去相應(yīng)路徑選擇數(shù)據(jù)

36、庫，在左側(cè)選中數(shù)據(jù)庫后點擊確定按鈕。Excel2007應(yīng)用培訓(xùn)回到卞圖界面后選擇默認(rèn)表。新數(shù)據(jù)源建好后出現(xiàn)在數(shù)據(jù)源列表中，選中后確定。在數(shù)中選擇需要的字段匚第23頁共29頁Excel2007應(yīng)用培訓(xùn)ls| Proj ectclproj eclIDProj ectNa/neOIClientCityOnTripPvnAur"1諸選擇查詢結(jié)果所需包含的數(shù)據(jù)列: 可用的表和列):碩覽選走列中的數(shù)據(jù):<1查詢結(jié)果中的列©:預(yù)噸E I 選項)I對以利用某字段對數(shù)據(jù)進(jìn)行篩選。上步|丁 步型)；| 職消點擊完成，進(jìn)入透視表設(shè)胃界面。第#頁共29頁Excel2007應(yīng)用培訓(xùn)第#頁共29

37、頁Excel2007應(yīng)用培訓(xùn)對透視表進(jìn)行布局，以卞步驟同透視表操作。第#頁共29頁Excel2007應(yīng)用培訓(xùn)列©行00數(shù)«®)| C:炒 11 StZ I OrCrip I I AK |I PfoducV"|s*n ceTyj帑助on J L.】.瞬二取消 43多種數(shù)據(jù)整理方法4.3.L整合多個數(shù)據(jù)表我們經(jīng)常需要將幾個表格中的數(shù)據(jù)整合成一張表，如下圖所示，將5個月的數(shù)據(jù)（目前分布 在5張表格上）整合到一個總表上：BA 月份341J 1J1 J561J7181J911101J111J121 31J1JM V就緒月月月月月hi月月月月月月r-1E年份D品名規(guī)

38、洛M602200M632813M632813M632813M632813W212330容戶名稱商品編陽 廣刃I番禺區(qū)XM602200 廣州番禺區(qū)XM6328L3 廣劌I番禺E XM6328 13 廠州幣禺匡XM632813 廣刃I番禺因XM6328 13 L4H苗陽 IXXWP1 1«R（S 廣丹番禺IEKW 廣iHl番禺 固*邨少12弓兮仃1W212330 廣刃I番禺IEKV212330 W212330 廣州番12330 W212330 廣刃H匕京屏 FMOBiqqO FM081440 廣冊匕卓畀X邨501033 W501033我們使用的方法是利用office的査詢工具query進(jìn)

39、行的這樣做的優(yōu)點在于今后對于新數(shù) 據(jù)的更新可以自動化刷新.而不需要重復(fù)的進(jìn)行整合操作（如復(fù)制粘貼）。下而開始整合工作：選擇合適的數(shù)據(jù)源類型.這里選擇自其它來源中的Microsoft query:致?lián)敢暠砜茢?shù)據(jù)透祝圖向?qū)Р紝拥?5頁共29頁來自 SQL Server創(chuàng)題y SQL Server球?qū)⒑脤?dǎo)入 Excel件為表或站適狽表.來自分析眼務(wù)創(chuàng)建B SQL Server分析£貉寥維舞集的連接.檸站導(dǎo)入Excel作為表或*Ci軽視表來自XML琳導(dǎo)入打開XML文件或其映射到Excel.、來自數(shù)據(jù)連接向?qū)ㄟ^使用琳連接向?qū)Ш蚈LEDB導(dǎo)入未列出來自 Microsoft Query通過便用

40、Microsoft Query冋導(dǎo)和ODBC導(dǎo)入未列出格式的:選J? excel files*：選擇要導(dǎo)入數(shù)據(jù)的excel文件:#Excel2007應(yīng)用培訓(xùn)選擇工作簿第27頁共29頁Excel2007應(yīng)用培訓(xùn)第#頁共29頁Excel2007應(yīng)用培訓(xùn)秦列11-二館表匯總. 案列11一12-12-案例 13(ma Lisa"« 案洌14-財務(wù)報表菟Vr只讀)數(shù)搖庫名幺)|各字段數(shù)據(jù)列表匯總.Js目錄：e： V. excel培訓(xùn)材料e： Vexcfel&ppt 2008 (r3 excel&pp 訓(xùn)車 & excels訓(xùn)材料第#頁共29頁Excel2007

41、應(yīng)用培訓(xùn)第#頁共29頁Excel2007應(yīng)用培訓(xùn)嚴(yán)件類型：|Exc“文件做"0工J馳動器&)：|目TVj 冋絡(luò)|第#頁共29頁Excel2007應(yīng)用培訓(xùn)第#頁共29頁Excel2007應(yīng)用培訓(xùn)查詢結(jié)果中的列(O:歹rIA / £ 月月月月月 12 3 4 5 , 丿 丿 J El±l匣匣匡表迭頂出現(xiàn)選擇數(shù)據(jù)衣的界面:查詢向?qū)?選擇列X諸選擇查詢結(jié)果所齋包含的數(shù)據(jù)列 可用的表和列):預(yù)覽選定列中的數(shù)據(jù)呵|預(yù)覽(!)I遶項W. 1<上一步)|下一步) >|職消I如果以上界面提示錯誤，點擊以匕界面的“選項”按鈕，出現(xiàn)以卜界面將系統(tǒng)表打勾即嘰第#頁共2

42、9頁Excel2007應(yīng)用培訓(xùn)第#頁共29頁Excel2007應(yīng)用培訓(xùn)任選一個字段到右側(cè)，點卜一步按鈕:第#頁共29頁Excel2007應(yīng)用培訓(xùn)第29頁共29頁Excel2007應(yīng)用培訓(xùn)查詢結(jié)果中的列C）：I-融碼格 粗編規(guī) 二招名份節(jié)色 朋斑商品年季頷Am預(yù)覽選定列中的數(shù)據(jù)団| 預(yù)覽|選項Q）|下一步取消 |直到以下界面出現(xiàn)，選擇第二項.點完成按鈕:此時出現(xiàn)query程序界而，點擊工具條上SQL按鈕:r licrosoft Query文件電）編揖電）觀團(tuán)電）格式表©）條件C）記錄窗口I畐闔阿（S 岡可nnm rrwi匣'查詢未自Ezcel Files訂貨單價訂貨金就訂貨數(shù)量

43、規(guī)格尺碼卜雖單位V月份出現(xiàn)以f界面:查詢向?qū)?選擇列請選揮查詢結(jié)果所需包含的數(shù)據(jù)列 可用的表和列）:在界面中將SQL語句改為以下:出現(xiàn)以下提示，點確定按鈕:出現(xiàn)數(shù)據(jù)查詢結(jié)果，選文件菜單-將數(shù)據(jù)返回EXCEL:扌 licrosoft Query文件妁鋼輯)視圉格式表©)表定義Q)執(zhí)行SQL g)fij建OLAP多維數(shù)據(jù)集!職消并返回 Microsoft Office Excel ©將數(shù)據(jù)返回 Microsoft Office®選擇在新工作表中存儲數(shù)據(jù):第#頁共29頁Excel2007應(yīng)用培訓(xùn)數(shù)據(jù)的放置位置O現(xiàn)有工作表g）：|二$£$18新建工作暮近 屬性)參數(shù)如