建模工具之Excel基本知識

1、從大規(guī)模生產(chǎn)到個性化生產(chǎn)從大規(guī)模生產(chǎn)到個性化生產(chǎn) 從以產(chǎn)品為中心到以客戶為中心從以產(chǎn)品為中心到以客戶為中心 從傳統(tǒng)流程到流程再造從傳統(tǒng)流程到流程再造從單個企業(yè)競爭到供應(yīng)鏈競爭從單個企業(yè)競爭到供應(yīng)鏈競爭從區(qū)域競爭到全球化競爭從區(qū)域競爭到全球化競爭從傳統(tǒng)管理轉(zhuǎn)向信息化管理從傳統(tǒng)管理轉(zhuǎn)向信息化管理學(xué)習(xí)計算機(jī)財務(wù)管理的理由學(xué)習(xí)計算機(jī)財務(wù)管理的理由提高財務(wù)管理決策的速度提高財務(wù)管理決策的速度能夠根據(jù)具體情況隨時改變決策程能夠根據(jù)具體情況隨時改變決策程序，提高決策的準(zhǔn)確性序，提高決策的準(zhǔn)確性 降低決策成本，節(jié)省人力和時間降低決策成本，節(jié)省人力和時間 學(xué)習(xí)計算機(jī)財務(wù)管理是培養(yǎng)會計、學(xué)習(xí)計算機(jī)財務(wù)管理是培養(yǎng)會

2、計、財務(wù)管理方面高級人才的需要財務(wù)管理方面高級人才的需要計算機(jī)財務(wù)管理的內(nèi)容計算機(jī)財務(wù)管理的內(nèi)容計算機(jī)建模工具計算機(jī)建模工具 Excel基本知識基本知識 財務(wù)管理財務(wù)管理分析圖分析圖工作表工作表計算機(jī)財務(wù)管理系統(tǒng)計算機(jī)財務(wù)管理系統(tǒng) 投資決策投資決策籌資決策籌資決策營運資金管理營運資金管理銷售及利潤管理銷售及利潤管理財務(wù)分析財務(wù)分析財務(wù)計劃財務(wù)計劃財務(wù)預(yù)算財務(wù)預(yù)算第一章第一章 建模工具建模工具ExcelExcel基本知識基本知識 Excel概述概述Excel的窗口界面的窗口界面Excel的工作簿的工作簿Excel的函數(shù)的函數(shù)Excel的工具的工具 學(xué)習(xí)目標(biāo)學(xué)習(xí)目標(biāo)：掌握掌握excel單元格的基本

3、知識單元格的基本知識掌握掌握excel函數(shù)函數(shù)理解并會用理解并會用excel基本工具基本工具作業(yè)作業(yè)：練習(xí)練習(xí)IF函數(shù)的使用函數(shù)的使用ExcelExcel概述概述Excel的基本特點的基本特點 強(qiáng)大的表格處理功能：強(qiáng)大的表格處理功能：“智能感知智能感知”技術(shù)技術(shù) 豐富的函數(shù)豐富的函數(shù) ：統(tǒng)計、財務(wù)、工程、數(shù)學(xué)等 強(qiáng)大的繪圖功能強(qiáng)大的繪圖功能 ：表、圖、文三者結(jié)合 豐富的分析工具：方案、單邊量求解等豐富的分析工具：方案、單邊量求解等 便捷的宏（便捷的宏（VBA：Visual Basic for Application） Excel的用途的用途 創(chuàng)建數(shù)據(jù)間的邏輯關(guān)系創(chuàng)建數(shù)據(jù)間的邏輯關(guān)系 利用數(shù)據(jù)間

4、固定的邏輯關(guān)系進(jìn)行試算分析利用數(shù)據(jù)間固定的邏輯關(guān)系進(jìn)行試算分析 重組數(shù)據(jù)間的邏輯關(guān)系重組數(shù)據(jù)間的邏輯關(guān)系ExcelExcel的窗口界面的窗口界面標(biāo)題欄標(biāo)題欄菜單條菜單條(menu)工具欄工具欄(toolbar)編輯欄編輯欄工作表工作表(sheet)名字框名字框公式欄公式欄Excel的菜單的菜單 下拉式菜單下拉式菜單 菜單項及主要功能：注意問題菜單項及主要功能：注意問題P19 簡捷菜單（快顯菜單）：右鍵引出簡捷菜單（快顯菜單）：右鍵引出 Excel的工具欄的工具欄 常用工具欄：標(biāo)準(zhǔn)工具欄和格式化工具欄常用工具欄：標(biāo)準(zhǔn)工具欄和格式化工具欄 增加工具欄：增加工具欄： 視圖視圖工具欄工具欄； 右鍵出現(xiàn)

5、簡捷菜單右鍵出現(xiàn)簡捷菜單工具欄工具欄自定義自定義項項 刪除工具欄：刪除工具欄：方法同上方法同上 新建工具欄：新建工具欄：工具工具自定義自定義工具欄工具欄選項卡選項卡新建新建按鈕按鈕ExcelExcel的工作簿的工作簿( (book)book) 工作表工作表(sheet) 切換工作表、選定工作表、插入工作表、切換工作表、選定工作表、插入工作表、移動工作表、復(fù)制工作表、重新命名工移動工作表、復(fù)制工作表、重新命名工作表、刪除工作表、作表、刪除工作表、工作表個數(shù)設(shè)定（默認(rèn)255個，工具選項常規(guī)） 單元格單元格(cell)與單元格區(qū)域與單元格區(qū)域 滾動條滾動條分割框分割框 單元格與單元格區(qū)域單元格與單元

6、格區(qū)域 單元格單元格(cells) 單元格個數(shù)：單元格個數(shù)：256列列65536行行 單元格地址單元格地址：相對地址（相對地址（A1）、絕對地址（）、絕對地址（$A$1） 單元格引用：單元格引用：相對引用、絕對引用、混合引用相對引用、絕對引用、混合引用 單元格區(qū)域單元格區(qū)域 單元格區(qū)域的表示方法單元格區(qū)域的表示方法 連續(xù)區(qū)域（冒號：）如連續(xù)區(qū)域（冒號：）如B1：C2 合集區(qū)域（逗號，）如合集區(qū)域（逗號，）如B1，B2，C1，C2 交集區(qū)域（空格交集區(qū)域（空格 ）如）如“B1：C3 C2：D5”公共區(qū)域公共區(qū)域C2：C3 單元格區(qū)域的選定單元格區(qū)域的選定 拖動選定拖動選定 、shift選定選定

7、、整行整列選定、整行整列選定 、交叉選定、交叉選定 、Ctrl選定選定 ExcelExcel的函數(shù)的函數(shù)(function)函數(shù)的基本語法函數(shù)的基本語法 函數(shù)名稱（參數(shù)函數(shù)名稱（參數(shù)1，參數(shù)，參數(shù)2，）參數(shù)的類型參數(shù)的類型（P28） 數(shù)值、數(shù)值、“文本文本”、單元地址、邏輯值、錯誤值、單元地址、邏輯值、錯誤值等等函數(shù)的使用函數(shù)的使用手動輸入：手動輸入：=SUM（10，20，30）插入插入函數(shù)函數(shù)錯誤值錯誤值原因解決方法輸入到單元格中的數(shù)據(jù)太長或單元格公式所產(chǎn)生的結(jié)果太大，使結(jié)果在單元格中顯示不下適當(dāng)增加列寬DIV/0!除數(shù)為0修改公式使除數(shù)不為0N/A數(shù)值對函數(shù)或公式不可用（在建立模型時，用戶

8、可以在單元格中輸入#N/A，以表明正在等待數(shù)據(jù)。任何引用含有#N/A值的單元格都將返回#N/A。）在等待數(shù)據(jù)的單元格內(nèi)填充上數(shù)據(jù)。NUM! 提供了無效的參數(shù)給工作表函數(shù)，或是公式的結(jié)果太大或太小而無法在工作表中表示。確認(rèn)函數(shù)中使用的參數(shù)類型正確。如果是公式結(jié)果太大或太小，就要修改公式，使其結(jié)果在-110307和110307之間。錯誤值原因解決方法NUM! 提供了無效的參數(shù)給工作表函數(shù)，或是公式的結(jié)果太大或太小而無法在工作表中表示。確認(rèn)函數(shù)中使用的參數(shù)類型正確。如果是公式結(jié)果太大或太小，就要修改公式，使其結(jié)果在-110307和110307之間。VALUE!使用了不正確的參數(shù)或運算符確認(rèn)公式或函數(shù)

9、所需的運算符或參數(shù)正確，并且公式引用的單元格中包含有效的數(shù)值。NULL！在公式中的兩個范圍之間插入一個空格以表示交叉點，但這兩個范圍沒有公共單元格。修如果要引用兩個不相交的區(qū)域，使用和運算或者并運算符REF!單元格引用無效修改公式中的無效單元格引用NAME?使用了Excel所不能識別的文本，比如可能是輸錯了名稱，或是輸入了一個已刪除的名稱，如果沒有將文字串括在雙引號中，也會產(chǎn)生此錯誤值如果是使用了不存在的名稱而產(chǎn)生這類錯誤，應(yīng)確認(rèn)使用的名稱確實存在；如果是名稱，函數(shù)名拼寫錯誤應(yīng)就改正過來；條件函數(shù)條件函數(shù)If函數(shù)函數(shù)基本語法基本語法 if (logical_test, value_if_tru

10、e, value_if_false)功能功能 判斷一個條件是否滿足判斷一個條件是否滿足,如果滿足則返回一個如果滿足則返回一個值值value_if_true,如果不滿足則返回另一個值如果不滿足則返回另一個值value_if_false 。If函數(shù)舉例函數(shù)舉例查找函數(shù)查找函數(shù)VlookupVlookup函數(shù)函數(shù)基本語法基本語法vlookup (lookup_value,table_array,col_index_num,range_lookup) lookup_value需要在表中搜索的值； table_array搜索區(qū)域（不包含上表頭）； col_index_num滿足條件的單元格在表格區(qū)域中的

11、列序號； range_lookup查找時精確匹配還是大致匹配，T或忽略則精確匹配；F或0則大致匹配。功能功能 搜索表區(qū)域首列滿足條件的元素，確定待檢索單元格在搜索表區(qū)域首列滿足條件的元素，確定待檢索單元格在區(qū)域中的行列號，再進(jìn)一步返回選定單元格的值。區(qū)域中的行列號，再進(jìn)一步返回選定單元格的值。vlookup函數(shù)舉例函數(shù)舉例數(shù)據(jù)庫函數(shù)數(shù)據(jù)庫函數(shù)dgetdget函數(shù)函數(shù)基本語法基本語法 dget（database，field，criteria） D構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域（包含表頭）（包含表頭）； F表示該列在列表中位置的數(shù)字； C包含指定條件的單元格區(qū)域（包括列列標(biāo)簽以及列標(biāo)簽下滿足條件的某

12、個單元格）；功能功能 從數(shù)據(jù)庫中提取指定條件且唯一存在的記錄從數(shù)據(jù)庫中提取指定條件且唯一存在的記錄Dget函數(shù)舉例函數(shù)舉例ExcelExcel的工具的工具 單變量求解單變量求解(Single variable resolve ) 方案求解方案求解(scheme resolve)數(shù)據(jù)透視表數(shù)據(jù)透視表(pivot table)宏宏(macro)模擬運算表模擬運算表單變量求解單變量求解定義：求解含有一個變量的方程的根。定義：求解含有一個變量的方程的根。步驟：步驟：1）確定目標(biāo)單元格和變量）確定目標(biāo)單元格和變量 2）在目標(biāo)單元格中輸入公式）在目標(biāo)單元格中輸入公式 3）工具工具單變量求解單變量求解 例題

13、例題1方案求解方案求解定義：定義：方案求解就是通過建立方案的方式計方案求解就是通過建立方案的方式計算、分析并匯總不同可能條件下的預(yù)期數(shù)據(jù)，算、分析并匯總不同可能條件下的預(yù)期數(shù)據(jù)，其結(jié)果可以以報告的方式提供。其結(jié)果可以以報告的方式提供。步驟：步驟： 1）建立原始數(shù)據(jù)區(qū)域和數(shù)據(jù)分析區(qū)域；）建立原始數(shù)據(jù)區(qū)域和數(shù)據(jù)分析區(qū)域； 2）定義可變單元格的名稱，建立邏輯關(guān)系）定義可變單元格的名稱，建立邏輯關(guān)系. 3）使用）使用工具工具方案方案管理器管理器添加添加的方式添加每的方式添加每個方案；個方案； 4）從方案管理器中選擇）從方案管理器中選擇摘要摘要得出方案結(jié)果報告。得出方案結(jié)果報告。例題例題2 確保數(shù)據(jù)清單的每一列都有列標(biāo)，光標(biāo)確保數(shù)據(jù)清單的每一列都有列標(biāo)，光標(biāo)置于清單中置于清單中數(shù)據(jù)數(shù)據(jù)數(shù)據(jù)透視表和圖表報告數(shù)據(jù)透視表和圖表報告選擇選擇圖表類型圖表類型下一步下一步選取數(shù)據(jù)源選取數(shù)據(jù)源下一步下一步根據(jù)需要將右邊字段拖到左邊的圖上，根據(jù)需要將右邊字段拖到左邊的圖上，設(shè)置好版式設(shè)置好版式確定確定完成完成 例題例題3數(shù)據(jù)透視表數(shù)據(jù)透視表建立自定義函數(shù)建立自定義函數(shù) 1）工具工具宏宏Visual Basic編輯編輯器器插入插入模塊模塊 2）插入插入過程過程彈出彈出添加過程添加過程對話框?qū)υ捒?3）添加相應(yīng)過程語句，并關(guān)閉窗口）添加相應(yīng)過程語句，并關(guān)閉窗口使用使用 插入插入函數(shù)函數(shù)自定義函數(shù)自定