Excel在財務管理中的高級應用(財務人員必備)

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

2、頂級商務圖表的重要標志一配色82.4.2. 控制線，趨勢線等新的圖表元素的添加82.4.3. 各類必要格式調整92.5. 不同數(shù)量級的圖表比較一指數(shù)化92.6. 高速圖表的途徑102.6.1. 快速復制圖表樣式102.6.2. 將滿意的圖表存為模版102.7. 雙坐標圖表102.8. 用廿特圖表示項目進程122.9. 動態(tài)圖表觀摩與思路分析14Part3管理儀表盤153.1. 公司管理儀表盤設計效果觀摩153.2. 儀表盤制作思路1533 利用透視表制作儀表盤1534 利用函數(shù)等制作儀表盤16Part4重整數(shù)據(jù)流174.1. Excel表格結構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. 利用透視表進行同比分析264.3.3. 利用透視表進行環(huán)比分析274.3.4. 利用透視表計算累計數(shù)28Excel財務管理高級應用Parti 設計并創(chuàng)建各種財務分析模型1.1. 國際謹用財努預測模版解析構成要素：1 假設(Asuniptioii)通貨膨脹，稅率，利率，折舊方法2項目預測收入(Sales),成本(Cost),費用(Expense)，固定資產預測(FA)3損益表(P&L),資產負債表(BS),現(xiàn)金流量表(Cashflow) 當前數(shù)(Cunent)累計數(shù)(Y

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

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

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

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

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

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

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

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

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

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

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

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

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

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

18、xcel色彩為商業(yè)周刊風格的彩色設置【圖表工民格式選項卡】【形狀填充】【其他填充顏色】【口定義】，對其RGB的值進行 設定：2.4.2. 控制線，趨勢線等新的圖表元素的添加制線通常為一條水平線,它可以是平均值線,也可以是預算線,或者某個特定的值, 用以評估實際情況是否超過了平均水平或者達到了預算，并以直觀的形式提供預警。 制作方法：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ù)系列，調整其圖表類型為折線圖，即町得到水平的控制線第9頁共29頁Excel2007應用培訓第#頁共29頁Excel2007應用培訓3.還可以添加其他必要的各種元素，用于強化圖表主題。2.4.3.各類必要格式調整1. 將圖表西文和數(shù)字字體改為Anal2. 將橫坐標刻度線刪除選中橫坐標，選擇【圖表工具】【布局選項卡】【設盤所選內容格式】,“主要刻度線類型” 選擇“無”。3. 數(shù)據(jù)系列分類間距改為40%選中某

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

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

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

23、再次選中系列“完成率”，右鍵菜單選擇【更改系列圖表類型】:刪險重設呃酎寫迪ah更改殺列圖表類型Q%三維旋轉®添加加標簽®添加設置站至列梧式(£)選擇折線圖，點確定圖表標題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%或坐標圖表制作完成。第#頁共29頁Excel2007應用培訓28用日特圖表示項目迸程制作甘特圖,需要以下4列數(shù)據(jù):ABC1D1任務升始日期肋需時1日天數(shù)結鬼日期22008-7-142008-7-53頃目啟可2008-7-5L<112008-7-94

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

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

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

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

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

29、NDIRECT($A$17), COLUMN()-1, 0) 依據(jù)此數(shù)據(jù)表制作雙曲線圖Part3管理儀表盤3.1.公司管理儀表盤設計效果觀摩3.2.儀表盤制作思路如果數(shù)據(jù)源為數(shù)據(jù)列表，那么町以使用透視表作為制作儀表盤的工具。 如果數(shù)據(jù)源不是數(shù)據(jù)列表，那么町以選擇恰當?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)的儀表盤中 的一個指標，如下圖：lilt213業(yè)3233 13536卜轉 lasrp匚世和£1!9&Q分區(qū)銷偉屮(人gw 爭«叭 兇兇3?科粗 UJntlc untjlr 兇8廣H8求HI 皿3?£»0&

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

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

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

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

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

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

37、頁Excel2007應用培訓對透視表進行布局，以卞步驟同透視表操作。第#頁共29頁Excel2007應用培訓列©行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ù)表我們經常需要將幾個表格中的數(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進

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

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

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

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

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