Excel在財(cái)務(wù)管理中的高級(jí)應(yīng)用

1、excel在管理中的應(yīng)用 excel在管理中的應(yīng)用 主講內(nèi)容：主講內(nèi)容： excel的應(yīng)用基礎(chǔ)； 公式和函數(shù)的綜合應(yīng)用； 數(shù)據(jù)透視表； excel的預(yù)測 ； excel運(yùn)用中的幾個(gè)小技巧 ； excel vba的靈活運(yùn)用； 數(shù)據(jù)的假設(shè)分析； excel在管理中的應(yīng)用 1、excel的應(yīng)用基礎(chǔ)；的應(yīng)用基礎(chǔ)； n1.1數(shù)據(jù)錄入 n1.2代碼化 n1.3數(shù)據(jù)清單、數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)勾稽關(guān)系 n1.4引用的使用 excel在管理中的應(yīng)用 1.1、數(shù)據(jù)錄入 n日期在excel中其實(shí)質(zhì)為數(shù)字 n1900-1-1代表1 n分?jǐn)?shù)與日期輸入 n5/8在excel中被識(shí)別為5月8日 n分?jǐn)?shù)5/8的輸入方式：0+空格+

2、 5/8 excel在管理中的應(yīng)用 1.2、代碼化（數(shù)據(jù)標(biāo)準(zhǔn)化） n以電子計(jì)算機(jī)為計(jì)算工具處理財(cái)務(wù)數(shù)據(jù)的基本概念。 n代碼化對(duì)系統(tǒng)原始數(shù)據(jù)按一定的規(guī)律進(jìn)行編碼的 處理。數(shù)據(jù)處理代碼化，縮短了數(shù)據(jù)項(xiàng)的長度，減少 數(shù)據(jù)占用的存儲(chǔ)空間，提高會(huì)計(jì)數(shù)據(jù)處理的速度和精 度。方便計(jì)算機(jī)進(jìn)行排序、分類、匯總等操作。 n輸入方式：+代碼；如：現(xiàn)金科目代碼輸入方 式：1001 excel在管理中的應(yīng)用 1.3、數(shù)據(jù)清單、數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)勾稽關(guān)系 n 在excel 中，數(shù)據(jù)庫是作為一個(gè)數(shù)據(jù)清單來看待。我們可以理解數(shù)據(jù)清單就 是數(shù)據(jù)庫。在一個(gè)數(shù)據(jù)庫中，信息按記錄存儲(chǔ)。每個(gè)記錄中包含信息內(nèi)容的各項(xiàng)， 稱為字段。excel

3、 提供了一整套功能強(qiáng)大的命令集，使得管理數(shù)據(jù)清單（數(shù)據(jù)庫） 變得非常容易。我們可以完成下列工作： 排序在數(shù)據(jù)清單中，針對(duì)某些列的數(shù)據(jù)，我們可以用數(shù)據(jù)菜單中的排序 命令來重新組織行的順序。可以選擇數(shù)據(jù)和選擇排序次序，或建立和使用一個(gè)自 定義排序次序。 篩選可以利用“數(shù)據(jù)”菜單中的“篩選”命令來對(duì)清單中的指定數(shù)據(jù)進(jìn) 行查找和其它工作。一個(gè)經(jīng)篩選的清單僅顯示那些包含了某一特定值或符合一組 條件的行，暫時(shí)隱藏其它行。 數(shù)據(jù)記錄單一個(gè)數(shù)據(jù)記錄單提供了一個(gè)簡單的方法，讓我們從清單或數(shù) 據(jù)庫中查看、更改、增加和刪除記錄，或用你指定的條件來查找特定的記錄。 自動(dòng)分類匯總利用“數(shù)據(jù)”菜單的“分類匯總”命令，在清

4、單中插入分 類匯總行，匯總你所選的任意數(shù)據(jù)。當(dāng)你插入了分類匯總后，microsoft excel自 動(dòng)為你在清單底部插入一個(gè)“總計(jì)”行。 excel在管理中的應(yīng)用 數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)勾稽關(guān)系 n數(shù)據(jù)結(jié)構(gòu)是指同一數(shù)據(jù)元素類中各數(shù)據(jù)元素之間存在 的關(guān)系。數(shù)據(jù)結(jié)構(gòu)分別為邏輯結(jié)構(gòu)、存儲(chǔ)結(jié)構(gòu)、物理 結(jié)構(gòu)和數(shù)據(jù)的運(yùn)算。 n數(shù)據(jù)勾稽關(guān)系-數(shù)據(jù)邏輯結(jié)構(gòu) n合理的存儲(chǔ)結(jié)構(gòu)、物理結(jié)構(gòu)，能夠極大提高工作效率； n數(shù)據(jù)邏輯結(jié)構(gòu)和數(shù)據(jù)的運(yùn)算，是編輯公式的基礎(chǔ)，善 于利用數(shù)據(jù)邏輯結(jié)構(gòu)和數(shù)據(jù)的運(yùn)算，完成工作的自動(dòng) 校驗(yàn)工作。 excel在管理中的應(yīng)用 excel電子表格的規(guī)范化電子表格的規(guī)范化 n建立和使用excel數(shù)據(jù)庫表格

5、時(shí)，用戶應(yīng)遵循以下的 規(guī)范： n（1） 一個(gè)數(shù)據(jù)庫最好單獨(dú)占據(jù)一個(gè)工作表，避免將 多個(gè)數(shù)據(jù)庫放到一個(gè)工作表上。 n（2）數(shù)據(jù)記錄緊接在字段名下面，不要使用空白行 將字段名和第一條記錄數(shù)據(jù)分開。 n（3）避免在數(shù)據(jù)庫中間放置空白行或空白列，任意 兩行的內(nèi)容不能完全相同。 n（4）避免將關(guān)鍵數(shù)據(jù)放到數(shù)據(jù)庫左右兩側(cè)，防止數(shù) 據(jù)篩選時(shí)這些數(shù)據(jù)被隱藏。 excel在管理中的應(yīng)用 excel電子表格的規(guī)范化電子表格的規(guī)范化 n（5）字段名的字體、對(duì)齊方式、格式、邊框等樣式， 應(yīng)當(dāng)與其他數(shù)據(jù)的格式相區(qū)別。 n（6）條件區(qū)域不要放在數(shù)據(jù)庫的數(shù)據(jù)區(qū)域下方。因 為用記錄單添加數(shù)據(jù)時(shí)，excel會(huì)在原數(shù)據(jù)庫的下邊

6、添加數(shù)據(jù)記錄，如果數(shù)據(jù)庫的下邊非空，就不能利用 記錄單添加數(shù)據(jù)。 n（7）不要用合并單元格 n（8）字與字之間及每一個(gè)字前后都不要有空格，即 信息庫中所有填寫內(nèi)容都不要有空格 excel在管理中的應(yīng)用 excel電子表格的規(guī)范化電子表格的規(guī)范化 n（9）使用統(tǒng)一的表格樣式。比如各部門的考勤計(jì)劃 表,由人資設(shè)計(jì)，下發(fā)，各部門填寫完成后收回，對(duì)于 統(tǒng)一的樣式，可以方便的合并，集中處理 。 n（10）使用一致的名稱 n（11）采用計(jì)算機(jī)認(rèn)同的數(shù)據(jù)格式，如日期格式應(yīng)采 用2009-5-23等，而不采用2009.5.23。 n（12）對(duì)數(shù)據(jù)進(jìn)行分析處理時(shí)應(yīng)建立副本（不在同一 工作?。┻M(jìn)行操作，不破壞原始

7、數(shù)據(jù)。 excel在管理中的應(yīng)用 excel電子表格的規(guī)范化電子表格的規(guī)范化 n數(shù)據(jù)有效性+名稱+下拉菜單 n實(shí)驗(yàn)：電子表格的規(guī)范化電子表格的規(guī)范化 excel在管理中的應(yīng)用 1.4、引用的使用； n引用的作用在于標(biāo)識(shí)工作表上的單元格或單元格區(qū)域， 并指明公式中所使用的數(shù)據(jù)的位置。通過引用，可以 在公式中使用工作表不同部分的數(shù)據(jù)，或者在多個(gè)公 式中使用同一個(gè)單元格的數(shù)值。還可以引用同一個(gè)工 作簿中不同工作表上的單元格和其他工作簿中的數(shù)據(jù)。 引用不同工作簿中的單元格稱為鏈接。 n相對(duì)引用 n絕對(duì)引用（混合引用） n鏈接 n名稱 excel在管理中的應(yīng)用 1.4、引用的使用； n相對(duì)引用-在創(chuàng)建公

8、式時(shí)，單元格或單元格區(qū)域的引用通常是相 對(duì)于包含公式的單元格的相對(duì)位置。隨公式位置的改變，其引用 的單元格也會(huì)相應(yīng)發(fā)生變化。 n絕對(duì)引用-復(fù)制公式時(shí)excel不調(diào)整引用，如$c$1。包括絕對(duì)引 用單元格的公式，無論將其復(fù)制到什么位置，總是引用特定的單 元格。 n鏈接-引用不同工作簿中的單元格。 n每次按f4鍵時(shí)，excel會(huì)在以下組合間切換：絕對(duì)列與絕對(duì)行（ 例如，$a$1）；相對(duì)列與絕對(duì)行(a$1)；絕對(duì)列與相對(duì)行($a1) 以及相對(duì)列與相對(duì)行(a1)，當(dāng)切換到用戶所需的引用時(shí)，按回車 鍵確認(rèn)即可。 excel在管理中的應(yīng)用 1.4動(dòng)態(tài)引用 noffset函數(shù)可以對(duì)動(dòng)態(tài)數(shù)據(jù)進(jìn)行查詢，它以指定

9、的引用為參照系，通 過給定偏移量得到新的引用。返回的引用可以為一個(gè)單元格或單元格區(qū) 域，并可以指定返回的行數(shù)或列數(shù)，其語法格式如下： noffset(reference,rows,cols,height,width) n可以看出，該函數(shù)最多包含五個(gè)參數(shù)，后兩個(gè)參數(shù)為可選項(xiàng)。其中： reference作為偏移量參照系的引用區(qū)域，它必須為對(duì)單元格或相 連單元格區(qū)域的引用；否則，函數(shù) offset 返回錯(cuò)誤值 #value!。 rows 為相對(duì)于偏移量參照系左上角單元格，上（下）偏移的行數(shù)。 cols 為相對(duì)于偏移量參照系左上角單元格，左（右）偏移的列數(shù)。 heigh 為高度，即所要返回的引用區(qū)域的

10、行數(shù)。width 為 寬度，即 所要返回的引用區(qū)域的列數(shù)。 excel在管理中的應(yīng)用 動(dòng)態(tài)引用 ncounta() nsum() n實(shí)驗(yàn)：工資統(tǒng)計(jì)表 nb1=sum(offset(工資!m2,counta(工資!a:a) nb2=sum(offset(工資!l2,counta(工資!a:a) excel在管理中的應(yīng)用 2、公式和函數(shù)的綜合應(yīng)用； n由用戶自行設(shè)計(jì)對(duì)工作表進(jìn)行計(jì)算和處理的公式。公 式的組成：運(yùn)算單元、運(yùn)算符、函數(shù)及參數(shù)、引用、 常數(shù)、文本、時(shí)間等 n公式中元素的結(jié)構(gòu)或次序決定了最終的計(jì)算結(jié)果。 excel中的公式遵循一個(gè)特定的語法或次序：最前面 是等號(hào)（=），后面是參與計(jì)算的元素

11、（運(yùn)算數(shù)）， 這些參與計(jì)算的元素又是通過運(yùn)算符隔開的。每個(gè)運(yùn) 算數(shù)可以是不改變的數(shù)值（常量數(shù)值）、單元格或引 用單元格區(qū)域、標(biāo)志、名稱、或工作表函數(shù)。 excel在管理中的應(yīng)用 2.1.1公式中的運(yùn)算符 （1）算術(shù)運(yùn)算符：完成基本的數(shù)學(xué)運(yùn)算。 （2）比較操作符：比較運(yùn)算符用于比較兩個(gè)值。當(dāng)用操 作符比較兩個(gè)值時(shí)，結(jié)果是一個(gè)邏輯值，不是true 就是false。 （3）文本運(yùn)算符：使用和號(hào)（”隔開。假如 你要表示第1 行中的56、78、89 和第2 行中的90、76、80，就 應(yīng)該建立一個(gè)2 行3 列的常量數(shù)組“56,78,89;90,76,80。 區(qū)域 數(shù)組是一個(gè)矩形的單元格區(qū)域，該區(qū)域中的單

12、元格共用一個(gè)公式。 例如公式“=trend(b1:b3,a1:a3)”作為數(shù)組公式使用時(shí)，它所 引用的矩形單元格區(qū)域“b1:b3,a1:a3”就是一個(gè)區(qū)域數(shù)組。 excel在管理中的應(yīng)用 2.1.6數(shù)組公式數(shù)組公式 n1.數(shù)組公式的輸入 n數(shù)組公式與一般公式不同之處在于它被括在大括號(hào)（）中，其 輸入步驟如下： n（1）選中一個(gè)單元格或者單元格區(qū)域。 n說明：說明：如果數(shù)組公式只是返回一個(gè)結(jié)果，需要選擇保存用來保存 結(jié)果的那一個(gè)單元格；如果數(shù)組公式返回多個(gè)結(jié)果，則需要選中 需要保留數(shù)組公式計(jì)算結(jié)果的單元格區(qū)域。 n（2）按照前面介紹的公式輸入規(guī)則，輸入公式的內(nèi)容。 n（3）公式輸完后，按【ctr

13、l+shift+enter】組合鍵結(jié)束操作。 n實(shí)驗(yàn)：工資k2:=e2:e37+f2:f37-h2:h37+i2:i37-j2:j37 excel在管理中的應(yīng)用 2.2、函數(shù)的運(yùn)用 nexcel 函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù) 據(jù)任務(wù)的特殊公式。 n函數(shù)的結(jié)構(gòu)以函數(shù)名稱開始，后面是左圓括號(hào)、以逗 號(hào)分隔的參數(shù)和右圓括號(hào)。如果函數(shù)是以公式的形式 出現(xiàn)，需在函數(shù)名稱前面輸入等號(hào)（=）。 n函數(shù)處理數(shù)據(jù)的方式與公式處理數(shù)據(jù)的方式是相同的 ，函數(shù)通過引用參數(shù)接收數(shù)據(jù)，并返回結(jié)果。大多數(shù) 情況下返回的是計(jì)算的結(jié)果，也可以返回文本、引用 、邏輯值、數(shù)值或工作表的信息。 excel在管理中的應(yīng)用

14、2.2.1函數(shù)的語法格式 函數(shù)名（參數(shù)1，參數(shù)2，參數(shù)n）。 在使用函數(shù)時(shí)，應(yīng)注意以下幾個(gè)問題： （1）函數(shù)名與其后的括號(hào)“（”之間不能有空格。 （2）當(dāng)有多個(gè)參數(shù)時(shí)，參數(shù)之間要用逗號(hào)“，”分 隔。 （3）參數(shù)部分總長度不能超過1024個(gè)字符。 （4）參數(shù)可以是數(shù)值、文本、邏輯值、單元格地址 或單元格區(qū)域地址，也可以是各種表達(dá)式或函數(shù)。 （5）函數(shù)中的逗號(hào)“，”、引號(hào)“”等都是半角字符， 而非全角的中文字符。 excel在管理中的應(yīng)用 2.2.2常用函數(shù)介紹 1求和函數(shù) （1）無條件求和sum函數(shù) 該函數(shù)的功能是計(jì)算所選取的單元格區(qū)域中所有數(shù)值的和。 （2）條件求和sumif函數(shù) sumif函

15、數(shù)的功能是根據(jù)指定條件對(duì)若干單元格求和。 （3）sumproduct函數(shù) 功能是在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并 返回乘積之和。 （4）dsum函數(shù) 功能是對(duì)數(shù)據(jù)庫表格進(jìn)行多條件匯總。 實(shí)驗(yàn)：電器銷售 excel在管理中的應(yīng)用 excel在管理中的應(yīng)用 2.2.2常用函數(shù)介紹 2條件函數(shù) if函數(shù)也稱條件函數(shù)，它根據(jù)參數(shù)條件的真假，返 回不同的結(jié)果。 3計(jì)數(shù)函數(shù) count函數(shù)：計(jì)算給定區(qū)域內(nèi)數(shù)值型參數(shù)的數(shù)目 countif函數(shù)：計(jì)算給定區(qū)域內(nèi)滿足特定條件的 單元格的數(shù)目。 excel在管理中的應(yīng)用 2.2.2常用函數(shù)介紹 4邏輯函數(shù) and函數(shù)：表示邏輯與 or函數(shù)：表示邏輯或

16、 not函數(shù)：功能是對(duì)參數(shù)的邏輯值求反 n這三個(gè)函數(shù)一般與if函數(shù)結(jié)合使用 excel在管理中的應(yīng)用 2.2.2常用函數(shù)介紹 5查找函數(shù) （1）lookup函數(shù)：返回向量（單行區(qū)域或單列區(qū)域）或數(shù)組中的 數(shù)值。lookup(lookup_value,lookup_vector,result_vector) （2）vlookup函數(shù)：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值， 并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。 vlookup(lookup_value,table_array,col_index_num,range_lookup) （3）hlookup函數(shù)：從表格或數(shù)值數(shù)組的首行查找指定的

17、數(shù)值， 并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。 hlookup(lookup_value,table_array,row_index_num,range_lookup) excel在管理中的應(yīng)用 2.2.3用函數(shù)快速制作工資條 nmod(number,divisor)求余數(shù)函數(shù)；number 為被 除數(shù)，divisor 為除數(shù)。 nrow()求行號(hào)函數(shù) ncolumn()求列號(hào)函數(shù) nindex() 引用函數(shù) index函數(shù)：返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引 用。 index(array,row_num,column_num) excel在管理中的應(yīng)用 找出數(shù)據(jù)結(jié)構(gòu)關(guān)系 excel在管

18、理中的應(yīng)用 2.2.3用函數(shù)快速制作工資條 n=if(mod(row(),3)=0,if(mod(row(),3)=1,工 資!a$1,index(工 資!$a:$n,(row()+4)/3,column() n相關(guān)概念：函數(shù)的嵌套 n實(shí)驗(yàn)：工資 excel在管理中的應(yīng)用 2.2.4應(yīng)收賬款的賬齡分析 ne4=if(today()-$b4=0,today()- $b4=30,today()- $b4=60,today()- $b4=90,$c4,) n實(shí)驗(yàn)：應(yīng)收賬款賬齡分析 excel在管理中的應(yīng)用 2.2.5文本函數(shù)綜合應(yīng)用 nleft或leftb n用途：根據(jù)指定的字符數(shù)返回文本串中的第一

19、個(gè)或前 幾個(gè)字符。 n語法：left(text，num_chars)或 leftb(text， num_bytes)。 n參數(shù)：text 是包含要提取字符的文本串；num_chars 指定函數(shù)要提取的字符數(shù)，它必須大于或等于0。 num_bytes按字節(jié)數(shù)指定由leftb 提取的字符數(shù)。 實(shí)例：如果a1=電腦愛好者，則left(a1，2)返回 “電腦”，leftb(a1，2)返回“電”。 excel在管理中的應(yīng)用 2.2.5文本函數(shù)綜合應(yīng)用 nlen 或lenb n用途：len 返回文本串的字符數(shù)。lenb 返回文本串 中所有字符的字節(jié)數(shù)。 n語法：len(text)或lenb(text)。

20、參數(shù)：text待要查找其長度的文本。 注意：此函數(shù)用 于雙字節(jié)字符，且空格也將作為字符進(jìn)行統(tǒng)計(jì)。 實(shí)例： 如果a1=電腦愛好者，則公式“=len(a1)”返回5， =lenb(a1)返回10。 excel在管理中的應(yīng)用 2.2.5文本函數(shù)綜合應(yīng)用 nmid 或midb n用途：mid 返回文本串中從指定位置開始的特定數(shù)目的字符，該 數(shù)目由用戶指定。midb返回文本串中從指定位置開始的特定數(shù) 目的字符，該數(shù)目由用戶指定。midb函數(shù)可以用于雙字節(jié)字符。 n語法：mid(text，start_num， num_chars)或midb(text， start_num，num_bytes)。 n參數(shù)：

21、text 是包含要提取字符的文本串。start_num 是文本中要 提取的第一個(gè)字符的位置，文本中第一個(gè)字符的start_num 為1 ， 以此類推；num_chars指定希望mid 從文本中返回字符的個(gè)數(shù)； num_bytes指定希望midb 從文本中按字節(jié)返回字符的個(gè)數(shù)。實(shí) 例：如果a1=電子計(jì)算機(jī)，則公式“=mid(a1，3，2)” 返回“計(jì) 算”，=midb(a1，3，2)返回“子”。 excel在管理中的應(yīng)用 2.2.5文本函數(shù)綜合應(yīng)用 nright 或rightb n用途：right 根據(jù)所指定的字符數(shù)返回文本串中最后一個(gè)或多 個(gè)字符。rightb根據(jù)所指定的字節(jié)數(shù)返回文本串中最后

22、一個(gè)或 多個(gè)字符。 n語法：right(text，num_chars)，rightb(text，num_bytes) 。 參數(shù)：text 是包含要提取字符的文本串；num_chars 指定希望 right 提取的字符數(shù)，它必須大于或等于0。如果num_chars 大于文本長度，則right 返回所有文本。如果忽略num_chars， 則假定其為1。num_bytes 指定欲提取字符的字節(jié)數(shù)。 實(shí)例： 如果a1=學(xué)習(xí)的革命，則公式“=right(a1，2)”返回“革命”， =rightb(a1，2)返回“命”。 excel在管理中的應(yīng)用 2.2.5文本函數(shù)綜合應(yīng)用 ntrim 用途：除了單詞之間

23、的單個(gè)空格外，清除文本中的所 有的空格。如果從其他應(yīng)用程序中獲得了帶有不規(guī)則 空格的文本，可以使用trim 函數(shù)清除這些空格。 n語法：trim(text)。 參數(shù)：text是需要清除其中空格 的文本。 實(shí)例：如果a1=firstquarterearnings，則公 式“=trim(a1)”返回“firstquarterearnings”。 excel在管理中的應(yīng)用 2.2.5文本函數(shù)綜合應(yīng)用 n實(shí)驗(yàn)：人事管理自動(dòng)判斷性別 n=if(len(e2)=15,if(mod(mid(e2,15,1),2)=1,男,女 ),if(mod(mid(e2,17,1),2)=1,男,女) n實(shí)驗(yàn)：賬務(wù)處理自

24、動(dòng)分類匯總 ng2: n=sum(if(trim(b2)=left(trim(憑證清 單!$e$2:$e$99),len(trim(期末余額表!b2),憑證清 單!$h$2:$h$99) excel在管理中的應(yīng)用 3、數(shù)據(jù)透析 n數(shù)據(jù)透視表是一種對(duì)大量數(shù)據(jù)快速匯總和建立交叉列 表的動(dòng)態(tài)工作表，而數(shù)據(jù)透視圖是一種能夠根據(jù)數(shù)據(jù) 處理需要，查看部分?jǐn)?shù)據(jù)的圖表對(duì)比效果，有些類似 前面介紹的動(dòng)態(tài)圖表功能，另外，excel中還可以根 據(jù)數(shù)據(jù)透視表制作不同格式的數(shù)據(jù)透視報(bào)告。 n數(shù)據(jù)透視分析就是從數(shù)據(jù)庫的特定字段中概括信息， 從而方便從各個(gè)角度查看、分析數(shù)據(jù)，并可對(duì)數(shù)據(jù)庫 中的數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)，它在exce

25、l中的實(shí)現(xiàn)工具是 數(shù)據(jù)透視表。 excel在管理中的應(yīng)用 數(shù)據(jù)透視表可以做什么 n1、數(shù)據(jù)透視表可以解決利用函數(shù)公式對(duì)超大容量的數(shù)據(jù)庫進(jìn)行 數(shù)據(jù)統(tǒng)計(jì)帶來的速度瓶頸。 n2、數(shù)據(jù)透視表可以通過行、列和頁字段的轉(zhuǎn)換進(jìn)行多角度的數(shù) 據(jù)分析。 n3、數(shù)據(jù)透視表通過對(duì)字段的篩選可以對(duì)重點(diǎn)關(guān)注的內(nèi)容進(jìn)行專 題分析。 n4、數(shù)據(jù)透視表可以不同的工作表和工作簿提取數(shù)據(jù),甚至不用打 開數(shù)據(jù)源文件。 n5、數(shù)據(jù)透視表可以生成動(dòng)態(tài)報(bào)表,保持與數(shù)據(jù)源的實(shí)時(shí)更新。 n6、數(shù)據(jù)透視表可以通過添加計(jì)算字段或計(jì)算項(xiàng)進(jìn)行差異分析。 n7、數(shù)據(jù)透視表可以隨時(shí)調(diào)用相關(guān)字段的數(shù)據(jù)源明細(xì)數(shù)據(jù)。 n8、數(shù)據(jù)透視圖可以自動(dòng)生成動(dòng)態(tài)分析圖表

26、。 excel在管理中的應(yīng)用 什么情況不適用數(shù)據(jù)透視表 n1、數(shù)據(jù)源中首行的標(biāo)題字段空缺或出現(xiàn)合并的標(biāo)題。 -創(chuàng)建數(shù)據(jù)透視表后會(huì)出現(xiàn)空白字段 n2、每列數(shù)據(jù)的中數(shù)據(jù)類型不一致。 -創(chuàng)建數(shù)據(jù)透視 表后只按一種數(shù)據(jù)類型分類匯總,會(huì)出現(xiàn)數(shù)據(jù)丟失 n3、數(shù)據(jù)源中出現(xiàn)數(shù)據(jù)斷行。 -創(chuàng)建數(shù)據(jù)透視表后會(huì) 出現(xiàn)數(shù)據(jù)丟失 n4、數(shù)據(jù)源中有合并的單元格。 -創(chuàng)建數(shù)據(jù)透視表后 會(huì)出現(xiàn)數(shù)據(jù)丟失 n5、數(shù)據(jù)源中有空白的單元格。 -創(chuàng)建數(shù)據(jù)透視表后 會(huì)出現(xiàn)對(duì)數(shù)值的默認(rèn)計(jì)數(shù) excel在管理中的應(yīng)用 創(chuàng)建數(shù)據(jù)透視表操作步驟： n步驟1單擊數(shù)據(jù)源1中的任意一個(gè)單元格如c8,單擊【插入】選項(xiàng) 卡單擊【數(shù)據(jù)透視表】按鈕 n步驟2

27、在彈出的【創(chuàng)建數(shù)據(jù)透視表】對(duì)話框中選擇放置數(shù)據(jù)透視 表的位置默認(rèn)的選擇是將數(shù)據(jù)透視表作為新的工作表，保持此選 項(xiàng)不變,單擊【確定】按鈕即新建一個(gè)sheet生成一張空的數(shù)據(jù)透 視表。如果選擇新建的數(shù)據(jù)透視表存放在已有的工作表,需要選 擇【現(xiàn)有工作表】單選按鈕,在【位置】中確定存放位置,單擊 【確定】按鈕即在指定位置生成一張空的數(shù)據(jù)透視表。 n步驟3在【數(shù)據(jù)透視表字段列表】對(duì)話框內(nèi)將部門字段移動(dòng)至 【列標(biāo)簽】區(qū)域、科目名稱字段移動(dòng)至【行標(biāo)簽】區(qū)域、勾選 【金額】字段。 excel在管理中的應(yīng)用 刷新數(shù)據(jù)透視表 n1、源數(shù)據(jù)發(fā)生變化后,數(shù)據(jù)透視表并不會(huì)自動(dòng)的刷新 數(shù)據(jù)。要對(duì)數(shù)據(jù)透視表進(jìn)行刷新,通常的

28、做法就是在數(shù) 據(jù)透視表上單擊鼠標(biāo)右鍵在彈出的擴(kuò)展菜單中選擇 【刷新】命令。 n2、利用數(shù)據(jù)透視表工具也可以實(shí)現(xiàn)刷新數(shù)據(jù)透視表。 單擊數(shù)據(jù)透視表在【數(shù)據(jù)透視表工具】【選項(xiàng)】選 項(xiàng)卡中單擊【刷新】按鈕。 n3、利用功能區(qū)的【數(shù)據(jù)】選項(xiàng)卡也可以實(shí)現(xiàn)刷新數(shù) 據(jù)透視表。在【數(shù)據(jù)】選項(xiàng)卡中單擊【全部刷新】按 鈕。 excel在管理中的應(yīng)用 1.設(shè)置在打開文件時(shí)進(jìn)行刷新 n如果希望excel在每次 打開數(shù)據(jù)透視表所在的 工作表時(shí)都進(jìn)行刷新,可 以在數(shù)據(jù)透視表中單擊 鼠標(biāo)右鍵【數(shù)據(jù)透視 表選項(xiàng)】【數(shù)據(jù)】 勾選【打開文件時(shí)刷新 數(shù)據(jù)】。 excel在管理中的應(yīng)用 2.刷新基于外部數(shù)據(jù)的數(shù)據(jù)透視表 n步驟1 直接

29、單擊【數(shù)據(jù)】選項(xiàng)卡【連接】按鈕 n步驟2 在彈出的【工作簿連接】對(duì)話框中單擊【屬性】 按鈕 n步驟3 在彈出的【連接屬性】對(duì)話框中勾選【允許后 臺(tái)刷新】。如果希望數(shù)據(jù)透視表每隔一定時(shí)間就自動(dòng) 刷新，可以勾選【刷新頻率】并設(shè)定刷新的間隔時(shí)間。 如果希望數(shù)據(jù)透視表在打開時(shí)自動(dòng)刷新，可以勾選 【打開文件時(shí)刷新數(shù)據(jù)】。 excel在管理中的應(yīng)用 數(shù)據(jù)透視表的字段設(shè)置 n默認(rèn)設(shè)置下,excel對(duì)數(shù)據(jù)透視表數(shù)據(jù)區(qū)的數(shù)字字段 應(yīng)用求和函數(shù),而對(duì)非數(shù)字字段或數(shù)據(jù)源中有空白單元 格的情況下應(yīng)用計(jì)數(shù)函數(shù)。在【數(shù)據(jù)透視表字段】對(duì) 話框中有很多可供選擇的計(jì)算方式。 n1、改變數(shù)據(jù)透視表字段的匯總方式 n2.對(duì)同一字段

30、應(yīng)用多種匯總方式 excel在管理中的應(yīng)用 使用自定義的值顯示方式 n 除了上面列舉的標(biāo)準(zhǔn)匯總函數(shù)外，excel也提供了 一套自定義的計(jì)算。通過他們，用戶可以在數(shù)據(jù)透視 表數(shù)據(jù)區(qū)中顯示每項(xiàng)占同行或同列總值的百分比，或 創(chuàng)建動(dòng)態(tài)匯總，或顯示每個(gè)數(shù)值占基準(zhǔn)值的百分比。 excel在管理中的應(yīng)用 使用自定義的值顯示方式 n操作方法：在“求 和項(xiàng): 金額2字段 上單擊鼠標(biāo)右鍵 【字段設(shè)置】 【值顯示方式】選 項(xiàng)卡單擊值顯示 方式的下拉按鈕選 擇占總和的百分 比。 excel在管理中的應(yīng)用 自定義計(jì)算功能 普通數(shù)據(jù)區(qū)域字段顯示為數(shù)據(jù)透視表中的原始數(shù)據(jù) 差異數(shù)據(jù)區(qū)域字段與指定的基本字段和基本項(xiàng)的差值。 百

31、分比數(shù)據(jù)區(qū)域顯示為基本字段和基本項(xiàng)的百分比。 差異百分比數(shù)據(jù)區(qū)域字段顯示為與基本字段項(xiàng)的差異百分比。 按某一字段匯總數(shù)據(jù)區(qū)域字段顯示為基本字段項(xiàng)的匯總。 占同行數(shù)據(jù)總和的 百分比 數(shù)據(jù)區(qū)域字段顯示為每個(gè)數(shù)據(jù)項(xiàng)占該行所有項(xiàng)總和的百分 比。 占同列數(shù)據(jù)總和的 百分比 數(shù)據(jù)區(qū)域字段顯示為每個(gè)數(shù)據(jù)項(xiàng)占該列所有項(xiàng)總和的百分 比。 占總和的百分比 數(shù)據(jù)區(qū)域字段分別顯示為每個(gè)數(shù)據(jù)項(xiàng)占該列和行所有項(xiàng)總 和的百分比。 指數(shù) 使用公式：(單元格的值)(總體匯總之和)/(行匯 總)(列匯總) excel在管理中的應(yīng)用 excel在管理中的應(yīng)用 數(shù)據(jù)組合與取消組合 n數(shù)據(jù)透視表通過對(duì)數(shù)字、日期、文本等不同數(shù)據(jù)類型 的

32、數(shù)據(jù)項(xiàng)采取多種組合方式，大大增強(qiáng)了數(shù)據(jù)透視表 分類匯總的適應(yīng)性。 n步驟1 在日期列(行標(biāo)簽)字段項(xiàng)中的任意一個(gè)單元格 上單擊鼠標(biāo)右鍵【組合】。(此外，也可以利用【數(shù) 據(jù)透視表工具】【選項(xiàng)】【將所選內(nèi)容分組】按 鈕) n步驟2 在【分組】對(duì)話框中,步長選中“月”。 n步驟3 單擊【確定】按鈕。 excel在管理中的應(yīng)用 數(shù)據(jù)透視表的計(jì)算字段和計(jì)算項(xiàng) n1、在數(shù)據(jù)透視表中插入計(jì)算字段 n計(jì)算字段是通過對(duì)數(shù)據(jù)透視表中現(xiàn)有的字段執(zhí)行計(jì)算 后得到的新字段。 n2、在數(shù)據(jù)透視表中插入計(jì)算項(xiàng) n計(jì)算項(xiàng)是在數(shù)據(jù)透視的現(xiàn)有字段中插入新的項(xiàng)，通過 對(duì)該字段的其他項(xiàng)執(zhí)行計(jì)算后得到該項(xiàng)的值。 excel在管理中的應(yīng)

33、用 1、在數(shù)據(jù)透視表中插入計(jì)算字段 計(jì)算字段是通過對(duì)數(shù)據(jù)透視表中現(xiàn)有的字段執(zhí)行 計(jì)算后得到的新字段。 excel在管理中的應(yīng)用 2、在數(shù)據(jù)透視表中插入計(jì)算項(xiàng) excel在管理中的應(yīng)用 數(shù)據(jù)透析多重區(qū)域合并 n用2007,發(fā)現(xiàn)數(shù)據(jù)透視表里面沒有多重區(qū)域合并的選 項(xiàng)了,那有什么辦法用2007實(shí)現(xiàn)多表合并? n快捷鍵alt+d+p，調(diào)用數(shù)據(jù)透視表向?qū)А?n實(shí)驗(yàn)：電器銷售 excel在管理中的應(yīng)用 4、數(shù)據(jù)的假設(shè)分析 n手動(dòng)假設(shè)分析 n單變量假設(shè)分析 n雙變量假設(shè)分析 n多變量假設(shè)分析（方案管理器） excel在管理中的應(yīng)用 4.1手動(dòng)假設(shè)分析 n“單變量求解”是一組命令的組成部分，如果已知單個(gè) 公式

34、的預(yù)期結(jié)果，而用于確定此公式結(jié)果的輸入值未 知。 n實(shí)驗(yàn)：假設(shè)分析-單變量求解 n函數(shù)： pmt(rate，nper，pv，fv，type) excel在管理中的應(yīng)用 4.2單變量假設(shè)分析 n單變量模擬運(yùn)算 n實(shí)驗(yàn)：假設(shè)分析-單變量模擬運(yùn)算 excel在管理中的應(yīng)用 4.3雙變量假設(shè)分析 n雙變量數(shù)據(jù)表中的兩組輸入數(shù)值使用同一個(gè)公式。這 個(gè)公式必須引用兩個(gè)不同的輸入單元格。 n實(shí)驗(yàn)：假設(shè)分析雙變量模擬運(yùn)算 excel在管理中的應(yīng)用 4.4多變量假設(shè)分析（方案管理器） n方案是 excel 保存在工作表中并可進(jìn)行自動(dòng)替換的一 組值?？梢允褂梅桨竵眍A(yù)測工作表模型的輸出結(jié)果。 同時(shí)還可以在工作表中創(chuàng)

35、建并保存不同的數(shù)值組，然 后切換到任意新方案以查看不同的結(jié)果。 n實(shí)驗(yàn)：方案 excel在管理中的應(yīng)用 5、excel的預(yù)測應(yīng)用 nexcel的預(yù)測函數(shù) nexcel的數(shù)據(jù)分析工具預(yù)測 n銷售預(yù)測的準(zhǔn)確度分析 n規(guī)劃求解工具預(yù)測 excel在管理中的應(yīng)用 5.1 預(yù)測概述 n預(yù)測是用科學(xué)的方法預(yù)計(jì)、推斷事物發(fā)展的必要性或 可能性的行為，即根據(jù)過去和現(xiàn)在預(yù)計(jì)未來，由已知 推斷未知的過程。 n5.1.1 預(yù)測分析步驟 n5.1.2 預(yù)測分析方法 n5.1.3 預(yù)測分析內(nèi)容 excel在管理中的應(yīng)用 5.1.1 預(yù)測分析步驟 n（1）確定預(yù)測目標(biāo)。 n（2）收集和整理資料。 n（3）選擇預(yù)測方法。

36、n（4）分析判斷。 n（5）檢查驗(yàn)證。 n（6）修正預(yù)測值。 n（7）報(bào)告預(yù)測結(jié)論。 excel在管理中的應(yīng)用 5.1.2 預(yù)測分析方法定量預(yù)測法 n1定量預(yù)測法 n定量預(yù)測法是在掌握與預(yù)測對(duì)象有關(guān)的各種要素的定量資 料的基礎(chǔ)上，運(yùn)用現(xiàn)代數(shù)學(xué)方法進(jìn)行數(shù)據(jù)處理，據(jù)以建立 能夠反映有關(guān)變量之間規(guī)律性聯(lián)系的各類預(yù)測模型的方法 體系，可分為趨勢外推分析法和因果分析法。 excel在管理中的應(yīng)用 5.1.2 預(yù)測分析方法定量預(yù)測法 n（1）趨勢外推分析法。這種方法是將時(shí)間作為制約預(yù)測對(duì)象變 化的自變量，把未來作為歷史的自然延續(xù)，屬于按事物的自身發(fā) 展趨勢進(jìn)行預(yù)測的動(dòng)態(tài)預(yù)測方法。 n該方法的基本原理是：企

37、業(yè)過去和現(xiàn)在存在的某種發(fā)展趨勢將會(huì) 延續(xù)下去，而且過去和現(xiàn)在發(fā)展的條件同樣適用于未來，可以將 未來視為歷史的自然延續(xù)。因此，該方法又被稱為時(shí)間序列分析 法。 n（2）因果分析法。這種方法是根據(jù)變量之間存在的因果關(guān)系函 數(shù)，按預(yù)測因素的未來變動(dòng)趨勢來推測預(yù)測對(duì)象（即因變量）未 來的相關(guān)預(yù)測方法。 n該方法的基本原理是：預(yù)測對(duì)象受到許多因素的影響，這些因素 之間存在著復(fù)雜的關(guān)系，通過對(duì)這些變量內(nèi)在規(guī)律性的研究可以 建立一定的數(shù)學(xué)模型，在已知自變量的條件下，可利用模型直接 推測預(yù)測對(duì)象的未來值。 excel在管理中的應(yīng)用 5.1.2 預(yù)測分析方法定性預(yù)測法 n2定性預(yù)測法 n定性預(yù)測法是由有關(guān)方面的

38、專業(yè)人員根據(jù)個(gè)人經(jīng)驗(yàn)和知識(shí)，結(jié)合 預(yù)測對(duì)象的特點(diǎn)進(jìn)行綜合分析，對(duì)事物的未來狀況和發(fā)展趨勢做 出推測的預(yù)測方法。它一般不需要進(jìn)行復(fù)雜的定量分析，適用于 缺乏完備的歷史資料或有關(guān)變量之間缺乏明顯的數(shù)量關(guān)系等情況 下的預(yù)測。 n實(shí)際工作中常常將二者結(jié)合應(yīng)用，相互取長補(bǔ)短，以提高預(yù)測的 準(zhǔn)確性和預(yù)測結(jié)論的可信度。 excel在管理中的應(yīng)用 5.1.3 預(yù)測分析內(nèi)容 n預(yù)測分析的基本內(nèi)容包括： n（1）銷售預(yù)測。 n（2）利潤預(yù)測。 n（3）成本預(yù)測。 n（4）資金預(yù)測。 excel在管理中的應(yīng)用 5.2 銷售預(yù)測 n狹義的銷售預(yù)測是對(duì)銷售量的預(yù)測。用于銷售量預(yù)測的常用方法 有判斷分析法、趨勢外推分析法

39、、因果分析法和產(chǎn)品壽命周期推 斷法等。其中，判斷分析法和產(chǎn)品壽命周期推斷法屬于定性預(yù)測 法，趨勢外推分析法和因果分析法屬于定量預(yù)測法。 n5.2.1 趨勢外推分析法: n5.2.2 因果分析法 excel在管理中的應(yīng)用 5.2.1 趨勢外推分析法 n1算術(shù)平均法 n2移動(dòng)平均法 excel在管理中的應(yīng)用 1算術(shù)平均法 n算術(shù)平均法又稱簡單平均法，它是直接將若干時(shí)期實(shí) 際銷售業(yè)務(wù)量的算術(shù)平均值作為銷售預(yù)測值的一種預(yù) 測方法。 n這種方法的優(yōu)點(diǎn)是計(jì)算過程很簡單，缺點(diǎn)是沒有考慮 遠(yuǎn)近期銷售業(yè)務(wù)量的變動(dòng)對(duì)預(yù)測銷售狀況的影響程度 的不同，從而使不同時(shí)期資料的差異簡單平均化，所 以，該方法只適用于對(duì)各期銷

40、售業(yè)務(wù)量比較穩(wěn)定，且 沒有季節(jié)性變化的食品和日常用品等的銷售預(yù)測。 n q y t n 時(shí)間序列期數(shù) 業(yè)務(wù)量之和已知時(shí)間序列各期銷售 預(yù)測銷售量 1 excel在管理中的應(yīng)用 2移動(dòng)平均法 n（1）一次移動(dòng)平均法。一次移動(dòng)平均法根據(jù)時(shí)間序列 逐項(xiàng)移動(dòng)，依次計(jì)算包含一定項(xiàng)數(shù)的序時(shí)平均數(shù)，形 成一個(gè)序時(shí)平均時(shí)間數(shù)序列，據(jù)以進(jìn)行預(yù)測。其預(yù)測 模型的計(jì)算公式為： n從上述公式可以看出，第t期的移動(dòng)平均數(shù)只能作為 t+1期的預(yù)測值，如果要預(yù)測數(shù)期以后的值，這種方法 就無能為力了。但是，在實(shí)際工作中，當(dāng)企業(yè)或商店 要逐月預(yù)測下個(gè)月的成百上千種產(chǎn)品的進(jìn)貨或銷售情 況時(shí)，這種方法還是很適用的。 t tt t

41、y yy y n y 1n- t1 1 excel在管理中的應(yīng)用 2移動(dòng)平均法一次移動(dòng)平均法 n對(duì)于一次移動(dòng)平均序列值的計(jì)算，可以使用遞 推算法來減少工作量。遞推算法的計(jì)算公式為： n上述公式說明，第t+1期的預(yù)測值是在第t期預(yù) 測值t的基礎(chǔ)上加上一個(gè)修正值得出的。不難看 出，修正項(xiàng)的作用和n的大小有關(guān)。在實(shí)際應(yīng) 用中，移動(dòng)平均時(shí)段n的選擇帶有一定的經(jīng)驗(yàn) 性，n過長或過短，各有利弊，可以多取幾個(gè)n 值，將取不同的n值得出的預(yù)測結(jié)果分別同實(shí) 際值比較，計(jì)算其預(yù)測誤差，最后選用預(yù)測誤 差最小的n值。 n yy y n yy n y n yyy n y y nttntt n k kt n k ntn

42、tkt n k kt t 1 1 1 1 0 1 excel在管理中的應(yīng)用 一次移動(dòng)平均法操作步驟 n1在excel中，以列方式將數(shù)據(jù)輸入到工作表中，并建立一次移動(dòng)平均法預(yù) 測模型的基本結(jié)構(gòu)。 n2打開“工具”菜單，選擇“數(shù)據(jù)分析”命令，打開 “數(shù)據(jù)分析”對(duì)話框。 n3在“數(shù)據(jù)分析”對(duì)話框中，選擇“移動(dòng)平衡”選項(xiàng)，單擊“確定”，會(huì) 打開“移動(dòng)平均”對(duì)話框， n4在“輸入?yún)^(qū)域”中輸入各月份銷售量。 n5在“間隔”中輸入“3”，即表示n=3。 n6在輸出區(qū)域中輸入“c3”。 n7在對(duì)話框中選擇“圖表輸出”。 n8單擊“確定”。 n9將c5：c13單元格區(qū)域復(fù)制到d6：d14單元格區(qū)域中。 n10選

43、定e6單元格，輸入公式：=（d6-b6）2。然后將此公式分別復(fù)制到e7： e14單元格區(qū)域。 excel在管理中的應(yīng)用 2移動(dòng)平均法二次移動(dòng)平均法 n（2）二次移動(dòng)平均法 n二次移動(dòng)平均法是對(duì)時(shí)間序列計(jì)算一次移動(dòng)平 均數(shù)后，再對(duì)一次移動(dòng)平均數(shù)序列進(jìn)行一次移 動(dòng)平均運(yùn)算。 n二次移動(dòng)平均法的計(jì)算公式為： n yyy y nttt t 11 excel在管理中的應(yīng)用 2移動(dòng)平均法二次移動(dòng)平均法 n二次移動(dòng)平均預(yù)測模型為： n二次移動(dòng)平均數(shù)并不能直接用于預(yù)測，其目的是用來求出平滑系 數(shù)。求解平滑系數(shù)估計(jì)值的公式為： tbay tttt 1 2 2 ttt ttt yy n b yya excel在管

44、理中的應(yīng)用 2移動(dòng)平均法趨勢平均法 n趨勢平均法是指在移動(dòng)平均法計(jì)算n期時(shí)間序列移動(dòng) 平均值的基礎(chǔ)上，進(jìn)一步計(jì)算趨勢值的移動(dòng)平均值， 進(jìn)而利用特定基期銷售量移動(dòng)平均值和趨勢值移動(dòng)平 均值來預(yù)測未來銷售量的一種方法。其計(jì)算公式為： n預(yù)測銷售量=基期銷售量移動(dòng)平均值+基期趨勢值移動(dòng) 平均值*基期與預(yù)測期的時(shí)間間隔； n某期的趨勢值=該期銷售量移動(dòng)平均值-上期銷售量移 動(dòng)平均值； n基期趨勢值移動(dòng)平均值=最后一個(gè)移動(dòng)期趨勢值之和/ 趨勢值移動(dòng)的期數(shù)。 excel在管理中的應(yīng)用 5.2excel的數(shù)據(jù)分析工具預(yù)測 n加載分析工具庫的方法 n 【工具】 【加載宏】 【分析工具庫】 n實(shí)驗(yàn)：銷售預(yù)測 e

45、xcel在管理中的應(yīng)用 5.3 利潤預(yù)測 n5.3.1 目標(biāo)利潤預(yù)測 n5.3.2 利潤敏感性分析 n5.3.3 概率分析法在利潤預(yù)測中的應(yīng)用 excel在管理中的應(yīng)用 5.3.1 目標(biāo)利潤預(yù)測 n（1）調(diào)查研究，確定利潤率標(biāo)準(zhǔn)。 n（2）計(jì)算目標(biāo)利潤基數(shù)。將選定的利潤率標(biāo)準(zhǔn)乘上企 業(yè)預(yù)期應(yīng)該達(dá)到的有關(guān)業(yè)務(wù)量及資金指標(biāo)，便可測算出 目標(biāo)利潤基數(shù)。其計(jì)算公式為： n目標(biāo)利潤基數(shù)=有關(guān)利潤標(biāo)準(zhǔn)*相關(guān)指標(biāo) n（3）確定目標(biāo)利潤修正值。 n（4）最終下達(dá)目標(biāo)利潤并分解落實(shí)納入預(yù)算體系。最 終下達(dá)的目標(biāo)利潤應(yīng)該為目標(biāo)利潤基數(shù)與修正值的代數(shù) 和，即： n最終下達(dá)的目標(biāo)利潤=目標(biāo)利潤基數(shù)+目標(biāo)利潤修正值 e

46、xcel在管理中的應(yīng)用 5.3.2 利潤敏感性分析 n1利潤敏感性分析的主要任務(wù) n利潤敏感性分析的主要任務(wù)是計(jì)算有關(guān)因素的 利潤靈敏指標(biāo)，揭示利潤與有關(guān)因素之間的相 對(duì)數(shù)量關(guān)系，并利用靈敏指標(biāo)進(jìn)行利潤預(yù)測。 n2利潤敏感分析的假定條件 n利潤敏感性分析的假定條件如下： n（1）有限因素的假定。 n（2）單獨(dú)變動(dòng)的假定。 n（3）利潤增長的假定。 n（4）同一變動(dòng)幅度的假定。 excel在管理中的應(yīng)用 5.3.2 利潤敏感性分析 n3利潤敏感性指標(biāo)的計(jì)算 n利潤敏感性分析的關(guān)鍵是計(jì)算利潤受各個(gè)因素 影響的靈敏度指標(biāo)。某因素的利潤靈敏度指標(biāo) 為該因素按上述假定單獨(dú)變動(dòng)1%后使利潤增 長的百分比指

47、標(biāo)，其計(jì)算公式為： n第i個(gè)因素利潤靈敏度指標(biāo)（si）=該因素的中 間變量基數(shù)/利潤基數(shù)*1%=mi/p*1%。 n式中的中間變量是指同時(shí)符合以下兩個(gè)條件的 計(jì)算替代指標(biāo)，即中間變量的變動(dòng)率必須等于 因素的變動(dòng)率，中間變量變動(dòng)額的絕對(duì)值必須 等于利潤的變動(dòng)額。 n實(shí)驗(yàn)：利潤敏感性分析 excel在管理中的應(yīng)用 5.3.3 概率分析法在利潤預(yù)測中的應(yīng)用 n蒙特卡羅模擬方法是一種比較實(shí)用的模擬預(yù)測方法。 n其基本原理是，通過對(duì)有關(guān)參數(shù)進(jìn)行大量的模擬實(shí)驗(yàn) 來了解所預(yù)測的指標(biāo)的分布情況，在此基礎(chǔ)之上做出 合理的預(yù)測。 n實(shí)驗(yàn)：蒙特卡羅模擬 nrand() nrandbetween() excel在管理

48、中的應(yīng)用 5.4 成本預(yù)測與資金需要量預(yù)測 n5.4.1 成本預(yù)測 n5.4.2 資金需要量預(yù)測 excel在管理中的應(yīng)用 5.4.1 成本預(yù)測 n成本預(yù)測是根據(jù)企業(yè)未來的發(fā)展目標(biāo)和現(xiàn)實(shí)條件，參 考其他資料，利用專門方法對(duì)企業(yè)未來成本水平及其 發(fā)展趨勢所進(jìn)行的推測與估算。 excel在管理中的應(yīng)用 5.4.2回歸分析 （1）一元線性回歸模型 （2）一元非線性回歸模型 excel在管理中的應(yīng)用 5.4.2回歸分析 （3）多元線性回歸模型 （4）多元非線性回歸模型 例如，柯柏道格拉斯生產(chǎn)函數(shù) 式中：l和k分別為勞動(dòng)力和固定資本 excel在管理中的應(yīng)用 5.4.3excel的預(yù)測函數(shù) 1 line

49、st函數(shù) 2 logest函數(shù) 3 trend函數(shù) 4 growth函數(shù) 5 forecast函數(shù) 6 slope函數(shù) 7 intercept函數(shù) excel在管理中的應(yīng)用 5.4.3.1 linest函數(shù) nlinest n使用最小二乘法對(duì)已知數(shù)據(jù)進(jìn)行最佳直線擬合，并返 回描述此直線的數(shù)組。 nlinest(known_ys,known_xs,const,stats) n直線的公式為： ny = mx + b or ny = m1x1 + m2x2 + . + b（如果有多個(gè)區(qū)域的 x 值） excel在管理中的應(yīng)用 5.4.3.2 logest函數(shù) n在回歸分析中，計(jì)算最符合數(shù)據(jù)的指數(shù)回歸

50、擬合曲線， 并返回描述該曲線的數(shù)值數(shù)組。因?yàn)榇撕瘮?shù)返回?cái)?shù)值 數(shù)組，故必須以數(shù)組公式的形式輸入。 nlogest(known_ys,known_xs,const,stats) n此曲線的公式為： ny = b*mx 或 ny = (b*(m1x1)*(m2x2)*_)（如果有多個(gè) x 值） excel在管理中的應(yīng)用 5.4.3.3 trend函數(shù) n返回一條線性回歸擬合線的值。即找到適合已知數(shù)組 known_ys 和 known_xs 的直線（用最小二乘法）， 并返回指定數(shù)組 new_xs 在直線上對(duì)應(yīng)的 y 值。 ntrend(known_ys,known_xs,new_xs,const) e

51、xcel在管理中的應(yīng)用 5.4.3.4 growth函數(shù) n根據(jù)現(xiàn)有的數(shù)據(jù)預(yù)測指數(shù)增長值。根據(jù)現(xiàn)有的 x 值和 y 值，growth 函數(shù)返回一組新的 x 值對(duì)應(yīng)的 y 值。 可以使用 growth 工作表函數(shù)來擬合滿足現(xiàn)有 x 值 和 y 值的指數(shù)曲線。 n語法語法 ngrowth(known_ys,known_xs,new_xs,const) excel在管理中的應(yīng)用 5.4.3.5 forecast函數(shù) n根據(jù)已有的數(shù)值計(jì)算或預(yù)測未來值。此預(yù)測值為基于 給定的 x 值推導(dǎo)出的 y 值。已知的數(shù)值為已有的 x 值 和 y 值，再利用線性回歸對(duì)新值進(jìn)行預(yù)測?？梢允褂?該函數(shù)對(duì)未來銷售額、庫存

52、需求或消費(fèi)趨勢進(jìn)行預(yù)測。 n語法語法 nforecast(x,known_ys,known_xs) excel在管理中的應(yīng)用 5.4.3.6 slope函數(shù) n返回根據(jù) known_ys 和 known_xs 中的數(shù)據(jù)點(diǎn)擬合的 線性回歸直線的斜率。斜率為直線上任意兩點(diǎn)的重直 距離與水平距離的比值，也就是回歸直線的變化率。 n語法語法 nslope(known_ys,known_xs) excel在管理中的應(yīng)用 5.4.3.7 intercept函數(shù) n利用現(xiàn)有的 x 值與 y 值計(jì)算直線與 y 軸的截距。截距 為穿過已知的 known_xs 和 known_ys 數(shù)據(jù)點(diǎn)的線性 回歸線與 y 軸

53、的交點(diǎn)。當(dāng)自變量為 0（零）時(shí)，使用 intercept 函數(shù)可以決定因變量的值。例如，當(dāng)所 有的數(shù)據(jù)點(diǎn)都是在室溫或更高的溫度下取得的，可以 用 intercept 函數(shù)預(yù)測在 0c 時(shí)金屬的電阻。 n語法語法 nintercept(known_ys,known_xs) n實(shí)驗(yàn)：利潤與成本預(yù)測 excel在管理中的應(yīng)用 5.5規(guī)劃求解 n在計(jì)劃管理中，經(jīng)常會(huì)遇到各種規(guī)劃問題，例如：人力資源的調(diào) 度、產(chǎn)品生產(chǎn)的安排、運(yùn)輸線路的規(guī)劃、生產(chǎn)材料的搭配、采購 批次的確定等。這類問題有一個(gè)共同要求，那就是：如何合理利 用各種約束資源實(shí)現(xiàn)最佳的經(jīng)濟(jì)效益，也就是達(dá)到常量最高、利 潤最大、成本最低、費(fèi)用最省等

54、目標(biāo)。這就是本節(jié)要解決的在約 束條件下尋求目標(biāo)函數(shù)最優(yōu)的規(guī)劃問題。一般來講，這類規(guī)劃問 題都具有如下三個(gè)特點(diǎn)： n（1）所求問題都有單一的目標(biāo)，如求生產(chǎn)的最低成本，求運(yùn)輸 的最佳路線，求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時(shí)間以及求 其他目標(biāo)函數(shù)的最佳值等。 n（2）總是有明確的不等式約束條件。比如庫存不能低于一定的 數(shù)量，否則造成原料短缺或產(chǎn)品缺貨；生產(chǎn)產(chǎn)品不能超過一定額 度，否則會(huì)造成商品積壓等。 n（3）問題都有直接或間接影響約束條件的一組輸入值。 excel在管理中的應(yīng)用 5.5規(guī)劃求解 n規(guī)劃求解工具用于解決復(fù)雜的方程求值及各類線性或非線形有約 束優(yōu)化問題。規(guī)劃問題種類繁多。從數(shù)學(xué)角度來

55、看，規(guī)劃問題都 有下述共同特征，這些特征也構(gòu)成了excel規(guī)劃求解工具界面中 的主要部分： n決策變量：每個(gè)規(guī)劃問題都有一組需要求解的未知數(shù)，稱作決策 變量。這組決策變量的一組確定值就代表一個(gè)具體的規(guī)劃方案。 在excel規(guī)劃求解工具中，可變單元格代表的是決策變量。 n約束條件：對(duì)于規(guī)劃問題的決策變量通常都有一定的限制條件， 稱作約束條件。約束條件可以用與決策變量有關(guān)的不等式或等式 來表示。 n目標(biāo)：每個(gè)問題都有一個(gè)明確的目標(biāo)，如利潤最大或成本最小。 目標(biāo)通?？捎门c決策變量有關(guān)的函數(shù)表示。在excel規(guī)劃求解工 具中，包含公式的目標(biāo)單元格代表的是目標(biāo)函數(shù)。 excel在管理中的應(yīng)用 產(chǎn)品生產(chǎn)最

56、優(yōu)安排 產(chǎn)品名稱 甲產(chǎn) 品 乙產(chǎn) 品每月工廠最大工時(shí)總數(shù)（小時(shí)）360 產(chǎn)品價(jià)格（元）160180 每月工廠最大材料總量（千克）240 單位變動(dòng)成本（元/件）60100 每月工廠最大能源總量（千瓦）850 單位產(chǎn)品消耗工時(shí)（小 時(shí)）69 單位產(chǎn)品消耗材料（千 克）74 單位產(chǎn)品消耗能源（千 瓦）1815 產(chǎn)品每月最大銷售量 （件）無30 excel在管理中的應(yīng)用 n規(guī)劃求解：（1）可以建立如下的（lp）模型 nmax(y)=140*x1+180*x2 n6x1+9x2=360 n7x1+4x2=240 n18x1+15x2=6 nx2=0 nx1、x2,int n實(shí)驗(yàn)：產(chǎn)品生產(chǎn)最優(yōu)安排 產(chǎn)品生產(chǎn)最優(yōu)安排 excel在管理中的應(yīng)用 運(yùn)輸模型 銷地 b1b2b3b4產(chǎn)量 運(yùn)價(jià) 產(chǎn)地 a11.520.339 a270.81.428 a31.20.322.56 銷量7583 excel在管理中的應(yīng)用 運(yùn)輸模型 n（1）根據(jù)運(yùn)輸問題的數(shù)學(xué)模型可以建立如下的（lp）模型 nmin w=1.5*x11+2*x12+0.3*x13+3*x14+7*x21+0.8*x22+1.4*x23+ 2*x24+1.2*x31+0.3*x32+2*x33+2.5*x34 nx11+x12+x13+x14=9 nx21+x22+x23+x24=8 nx31+x