Excel在財務管理中的高級應用

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

16、 not函數(shù)：功能是對參數(shù)的邏輯值求反 n這三個函數(shù)一般與if函數(shù)結合使用 excel在管理中的應用 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ù)組當前行中指定列處的數(shù)值。 vlookup(lookup_value,table_array,col_index_num,range_lookup) （3）hlookup函數(shù)：從表格或數(shù)值數(shù)組的首行查找指定的

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

18、理中的應用 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相關概念：函數(shù)的嵌套 n實驗：工資 excel在管理中的應用 2.2.4應收賬款的賬齡分析 ne4=if(today()-$b4=0,today()- $b4=30,today()- $b4=60,today()- $b4=90,$c4,) n實驗：應收賬款賬齡分析 excel在管理中的應用 2.2.5文本函數(shù)綜合應用 nleft或leftb n用途：根據(jù)指定的字符數(shù)返回文本串中的第一

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

20、參數(shù)：text待要查找其長度的文本。 注意：此函數(shù)用 于雙字節(jié)字符，且空格也將作為字符進行統(tǒng)計。 實例： 如果a1=電腦愛好者，則公式“=len(a1)”返回5， =lenb(a1)返回10。 excel在管理中的應用 2.2.5文本函數(shù)綜合應用 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 是文本中要 提取的第一個字符的位置，文本中第一個字符的start_num 為1 ， 以此類推；num_chars指定希望mid 從文本中返回字符的個數(shù)； num_bytes指定希望midb 從文本中按字節(jié)返回字符的個數(shù)。實 例：如果a1=電子計算機，則公式“=mid(a1，3，2)” 返回“計 算”，=midb(a1，3，2)返回“子”。 excel在管理中的應用 2.2.5文本函數(shù)綜合應用 nright 或rightb n用途：right 根據(jù)所指定的字符數(shù)返回文本串中最后一個或多 個字符。rightb根據(jù)所指定的字節(jié)數(shù)返回文本串中最后

22、一個或 多個字符。 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ù)。 實例： 如果a1=學習的革命，則公式“=right(a1，2)”返回“革命”， =rightb(a1，2)返回“命”。 excel在管理中的應用 2.2.5文本函數(shù)綜合應用 ntrim 用途：除了單詞之間

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

41、y yy y n y 1n- t1 1 excel在管理中的應用 2移動平均法一次移動平均法 n對于一次移動平均序列值的計算，可以使用遞 推算法來減少工作量。遞推算法的計算公式為： n上述公式說明，第t+1期的預測值是在第t期預 測值t的基礎上加上一個修正值得出的。不難看 出，修正項的作用和n的大小有關。在實際應 用中，移動平均時段n的選擇帶有一定的經驗 性，n過長或過短，各有利弊，可以多取幾個n 值，將取不同的n值得出的預測結果分別同實 際值比較，計算其預測誤差，最后選用預測誤 差最小的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在管理中的應用 一次移動平均法操作步驟 n1在excel中，以列方式將數(shù)據(jù)輸入到工作表中，并建立一次移動平均法預 測模型的基本結構。 n2打開“工具”菜單，選擇“數(shù)據(jù)分析”命令，打開 “數(shù)據(jù)分析”對話框。 n3在“數(shù)據(jù)分析”對話框中，選擇“移動平衡”選項，單擊“確定”，會 打開“移動平均”對話框， n4在“輸入?yún)^(qū)域”中輸入各月份銷售量。 n5在“間隔”中輸入“3”，即表示n=3。 n6在輸出區(qū)域中輸入“c3”。 n7在對話框中選擇“圖表輸出”。 n8單擊“確定”。 n9將c5：c13單元格區(qū)域復制到d6：d14單元格區(qū)域中。 n10選

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

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

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

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

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

48、中的應用 5.4 成本預測與資金需要量預測 n5.4.1 成本預測 n5.4.2 資金需要量預測 excel在管理中的應用 5.4.1 成本預測 n成本預測是根據(jù)企業(yè)未來的發(fā)展目標和現(xiàn)實條件，參 考其他資料，利用專門方法對企業(yè)未來成本水平及其 發(fā)展趨勢所進行的推測與估算。 excel在管理中的應用 5.4.2回歸分析 （1）一元線性回歸模型 （2）一元非線性回歸模型 excel在管理中的應用 5.4.2回歸分析 （3）多元線性回歸模型 （4）多元非線性回歸模型 例如，柯柏道格拉斯生產函數(shù) 式中：l和k分別為勞動力和固定資本 excel在管理中的應用 5.4.3excel的預測函數(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在管理中的應用 5.4.3.1 linest函數(shù) nlinest n使用最小二乘法對已知數(shù)據(jù)進行最佳直線擬合，并返 回描述此直線的數(shù)組。 nlinest(known_ys,known_xs,const,stats) n直線的公式為： ny = mx + b or ny = m1x1 + m2x2 + . + b（如果有多個區(qū)域的 x 值） excel在管理中的應用 5.4.3.2 logest函數(shù) n在回歸分析中，計算最符合數(shù)據(jù)的指數(shù)回歸

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

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

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

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

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

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

56、優(yōu)安排 產品名稱 甲產 品 乙產 品每月工廠最大工時總數(shù)（小時）360 產品價格（元）160180 每月工廠最大材料總量（千克）240 單位變動成本（元/件）60100 每月工廠最大能源總量（千瓦）850 單位產品消耗工時（小 時）69 單位產品消耗材料（千 克）74 單位產品消耗能源（千 瓦）1815 產品每月最大銷售量 （件）無30 excel在管理中的應用 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實驗：產品生產最優(yōu)安排 產品生產最優(yōu)安排 excel在管理中的應用 運輸模型 銷地 b1b2b3b4產量 運價 產地 a11.520.339 a270.81.428 a31.20.322.56 銷量7583 excel在管理中的應用 運輸模型 n（1）根據(jù)運輸問題的數(shù)學模型可以建立如下的（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