第五卷 分析數據
序
本章要點：
在工作中經常要對數據進行各種分析。Excel中對於分析數據和有效數據處理提供了強大的方法。
我們可以用模擬運算表分析公式中某些數值的變化對計算結果的影響；而對於不同的方案，可以通過方案管理器對方案進行各種編輯，合並方案或者創建方案總結報告；對於一些有待確定的數值，可以用單變量求解對數據進行分析處理計算。我們還可以為工作表中的單元格或者單元格區域設置有效數據的範圍，使得輸入錯誤的數據時會出現錯誤信息；或者通過審核分析可以很輕鬆地將其標識出來。
本章將敘述模擬運算表、方案管理器、單變量求解以及有效數據處理的內容。
第一章 模擬運算表
模擬運算表是將取自工作表中一個單元格區域的數據進行模擬運算，顯示公式中某些數值的變化對計算結果的影響。
當你在工作表中使用了隻有一個或兩個變量的公式時，就可以使用模擬運算表來分析公式中的變量對結果的影響。
第二章 創建單變量模擬運算表
單變量模擬運算表的結構特點是，其輸入數值被排列在一列中（列引用）或一行中（行引用）。單變量模擬運算表中使用的公式必須引用輸入單元格。所謂輸入單元格，就是將被替換的含有輸入數據的單元格。這個概念可能一下很難弄懂，通過下麵的學習你將對其有確切的了解。
創建單變量模擬運算表的步驟如下：
（1） 在一列或一行中，鍵入要替換工作表上的輸入單元格的數值序列；
（2） 如果輸入數值被排成一列，在第一個數值的上一行且處於數值列右側的單元格中，鍵入所需的公式。在同一行中，在第一個公式的右邊，分別鍵入其它公式；如果輸入數值被排成一行，在第一個數值左邊一列且處於數值行下方的單元格內，鍵入所需的公式。在同一列中，在第一個公式的下方，分別鍵入其它公式；
（3） 選定包含公式和需要被替換的數值的單元格區域；
（4） 單擊"數據(D)"菜單，在彈出的菜單中選擇"模擬運算表(T)..."選項，將彈出"模擬運算表"對話框，如圖12-1所示；
圖12-1 "模擬運算表"對話框
（5） 如果模擬運算表是列方向的，在"輸入引用列的單元格(C)"編輯框中，為輸入單元格鍵入引用；如果模擬運算表是行方向的，在"輸入引用行的單元格(R)"編輯框中，為輸入單元格鍵入引用；
（6） 單擊"確定"按鈕。
下麵舉一個例子說明如何建立單變量模擬運算表。
例如對於銀行的存款，不同的利率將會產生不同的利息，如果要查看不同的利率對利息產生的影響，可以用創建單變量模擬運算表來進行。如圖12-2所示，為存期為一年的存款情況。
圖12-2 存款情況
（1） 在工作表中鍵入用於創建單變量模擬運算表的數據，如圖12-3所示。
圖12-3 用於創建單變量模擬運算表的數據
在這裏，B2單元格是輸入單元格，在單元格D5到D8單元格中輸入用來替換輸入單元格中的數據"10%，7%，8%，12%"；單元格E4到H4中輸入了公式，這些公式都引用了輸入單元格，分別為"=$A$5*$B$2，=$A$6*$B$2，=$A$7*$B$2，=$A$8*$B$2"，並設置D5到H8單元格區域的貨幣格式。
（2） 選定包含公式和需要被替換的實質的單元格區域D4到H8，如圖12-4所示；
圖12-4 選擇所需的單元格區域
（3） 單擊"數據(D)"菜單，在彈出的菜單中選擇"模擬運算表(T)..."選項，將彈出"模擬運算表"對話框；
（4） 在"輸入引用列的單元格(C)"編輯框中，鍵入輸入單元格的引用"$B$2"，或者通過單擊編輯框右端的折疊按鈕來選取輸入單元格，如圖12-5所示；
圖12-5 選取輸入單元格的引用
（5） 單擊"確定"按鈕，結果如圖12-6所示；
圖12-6 單變量模擬運算表返回的計算結果
第三章 創建雙變量模擬運算表
單變量模擬運算表雖然好用，但是有一個不足之處，就是對數據進行分析時，隻能查看單個變量對計算結果的影響。如果希望查看公式中兩個變量同時改變對計算結果的影響，就必須使用雙變量模擬運算表。如果已經掌握創建單變量模擬運算表的方法，創建雙變量模擬運算表其實是很簡單的了。下麵我們就舉例來說明如何創建雙變量模擬運算表。
在圖12-7所示的工作表中，要查看本金和利率變化對利息的影響，步驟如下：
圖12-7 原始公式
（1） 在工作表中鍵入用於創建雙變量模擬運算表的數據，如圖12-8所示。
在這裏，B2單元格是列輸入單元格，在單元格D5到D8單元格中輸入用來替換列輸入單元格中的數據"10%，7%，8%，12%"；A5單元格為行輸入單元格，單元格E4到H4中輸入用來替換行輸入單元格中的數據"￥25,000，￥22,000，￥15,000"， 在單元格D4中輸入公式"=$A$5*$B$2"，並設置D5到H8單元格區域的貨幣格式。
圖12-8 用於創建雙變量模擬運算表的數據
（2） 選定包含公式以及數值行和列的單元格區域，如圖12-9所示；
（3） 單擊"數據(D)"菜單，在彈出的菜單中選擇"模擬運算表(T)..."選項，將彈出"模擬運算表"對話框；
（4） 在"輸入引用行的單元格(R)"編輯框中，鍵入行輸入單元格的引用"$A$5"，或者通過單擊編輯框右端的折疊按鈕來選取行輸入單元格；在"輸入引用列的單元格(C)"編輯框中，鍵入列輸入單元格的引用"$B$2"，或者通過單擊編輯框右端的折疊按鈕來選取輸入單元格；如圖12-10所示；
圖12-9 選擇數據
圖12-10 鍵入行和列輸入單元格的引用
（5） 單擊"確定"按鈕，結果如圖12-11所示；
圖12-11 雙變量模擬運算表返回的計算結果
* 注意
在雙變量模擬運算表中的兩組輸入數值的相交單元格需要輸入一個公式，該公式必須引用兩個不同的輸入單元格。
第四章 為模擬運算表增加公式
創建好模擬運算表以後，我們可以根據需要來向其中增加公式，或者對表中的公式進行修改。
為現有的模擬運算表添加公式的步驟如下：
（1） 如果輸入數值被排列在一列中，在模擬運算表首行現有公式右麵的空白單元格中鍵入新公式；如果輸入數值被排列在一行中，在模擬運算表首列現有公式下麵的空白單元格中鍵入新公式。
（2） 選定模擬運算表，包含有新公式的行或者列；
（3） 單擊"數據(D)"菜單，在彈出的菜單中選擇"模擬運算表(T)..."選項，將彈出"模擬運算表"對話框；
（4） 如果模擬運算表是列方向的，在"輸入引用列的單元格(C)"編輯框中，為輸入單元格鍵入引用；如果模擬運算表是行方向的，在"輸入引用行的單元格(R)"編輯框中，為輸入單元格鍵入引用；
（5） 單擊"確定"按鈕。
第五章 將模擬運算表的計算結果轉換為常量
由於計算結果存放在數組中，所以必須將計算結果轉換為常量才能夠使用，而且所有計算結果必須都轉換為常量，不能隻轉換個別計算結果。
將模擬運算表的計算結果轉換為常量的步驟如下：
（1） 選定模擬運算表中的所有計算結果單元格；
（2） 單擊"常用"工具欄中的"複製"按鈕；
（3） 單擊"編輯(E)"菜單，在彈出的菜單中選擇"選擇性粘貼(S)..."選項，將彈出"選擇性粘貼"對話框，如圖12-12所示；
圖12-12 "選擇性粘貼"對話框
（4） 在"選擇性粘貼"對話框中的"粘貼"欄中，選擇"數值(V)"選項；
（5） 單擊"確定"按鈕。
第六章 清除模擬運算表的計算結果
由於計算結果存放在數組中，所以必須清除所有計算結果，而不能隻清除個別計算結果。如果不是要清除整個模擬運算表，在進行刪除操作時，請確認沒有選定其中的公式和輸入數值。
清除模擬運算表的計算結果的步驟如下：
（1） 選定模擬運算表中的所有計算結果單元格；
（2） 單擊"編輯(E)"菜單，在彈出的菜單中選擇"清除(A)"選項，然後在彈出的子菜單中選擇"內容(C)"選項。
第七章 清除整個模擬運算表
如果要清楚整個模擬運算表，可以按照以下的步驟操作：
（1） 選定整個模擬運算表，包括所有的公式、輸入數值、計算結果、格式和批注。
（2）單擊"編輯(E)"菜單，在彈出的菜單中選擇"清除(A)"選項，然後在彈出的子菜單中選擇"全部(A)"選項。
第八章 方案管理器
在實際工作中往往會遇到多方案的選擇問題。在隻有1個或者2個變量的工作表中，調整一下方案不會有什麼麻煩。但是，如果在工作表中引用了很多的位置變量，直接在工作表中調整方案就很不方便。方案管理器可以將要調整的位置變量作為獨立的方案建立並保存數據集。使用這些方案可以查看基於不同假設的多個結果。
第九章 建立方案
如果需要用到某個方案，我們可以使用方案管理器來創建所需的方案。下麵是用方案管理器創建方案的具體步驟：
（1） 單擊"工具(T)"菜單，在彈出的菜單中選擇"方案(E)..."選項，將彈出"方案管理器"對話框，如圖12-13所示；
（2） 單擊"添加(A)..."按鈕，將出現"添加方案"對話框，如圖12-14所示；
圖12-13 "方案管理器"對話框圖12-14 "添加方案"對話框
（3） 在"方案名(N)"編輯框中，鍵入方案名稱；
（4） 在"可變單元格"編輯框中，輸入對需要反複修改的單元格的引用，如果引用多個單元格區域，則每個單元格區域引用之間用逗號隔開；
（5） 在"保護"單選框中，選擇所需的選項；
（6） 單擊"確定"按鈕，將出現"方案變量值"對話框，如圖12-15所示；
（7） 在"方案變量值"對話框中，鍵入可變單元格所需的數值；
（8） 如果要創建方案，請單擊"確定"按鈕；如果要創建多個方案，單擊"添加(A)"按鈕，然後重複步驟（3）到步驟（7），完成方案創建後，單擊"確定"按鈕；返回"方案管理器"，如圖12-16所示；
（9） 單擊"方案管理器"對話框中的"關閉"按鈕，完成創建方案。如果要在可變單元格中顯示方案，單擊"顯示(S)"按鈕。
圖12-15 "方案變量值"對話框
圖12-16 "方案管理器"對話框
下麵舉例子來說明建立方案的過程。
例如某公司出租寫字樓的利潤預測表如圖12-17所示。因為市場和其他因素的變化可能產生不同的結果，所以需要為公司計算出不同的方案，以便決策。按如下步驟可以為公司建立悲觀和樂觀兩個方案。
圖12-17 利潤預測表
（1） 為了方便起見，先為單元格C2到C8命名，方法是：選中B2到C7單元格，單擊"插入(I)"菜單，在彈出的菜單中將鼠標指向"名稱(N)"選項，然後在彈出的子菜單中選擇"指定(C)..."選項，將彈出"指定名稱"對話框，如圖12-18所示；
（2） 在"名稱在"對話框中，選擇"最左列(L)"選項，單擊"確定"按鈕，則C2到C7單元格將以各項目名稱命名；
（3） 單擊"工具(T)"菜單，在彈出的菜單中選擇"方案(E)..."選項，將彈出"方案管理器"對話框，參見圖12-12所示；
（4） 單擊"添加(A)..."按鈕，將出現"添加方案"對話框，如圖12-19所示；
圖12-18 "指定名稱"對話框
圖12-19 "添加方案"對話框
（5） 在"方案名(N)"編輯框中，鍵入方案名稱"基本方案"；
（6） 在"可變單元格"編輯框中，輸入可變單元格的引用"C2:C7"；
（7） 在"保護"單選框中，選擇所需的選項；
（8） 單擊"確定"按鈕，將出現"方案變量值"對話框，如圖12-20所示；
圖12-20 "方案變量值"對話框
（9） 在"方案變量值"對話框中，鍵入可變單元格所需的數值；
（10）單擊"方案變量值"對話框的"確定"按鈕，返回"方案管理器"對話框；
（11）按照步驟（4）到（10）分別建立"悲觀方案"和"樂觀方案"；
（12）單擊"方案管理器"的"關閉"按鈕，完成方案的建立。
在"方案管理器"對話框中，單擊"顯示(S)"按鈕，該方案的結果將取代可變單元格的值，結果如圖12-21所示；
(a) 基本方案
(b) 悲觀方案
(c) 樂觀方案
圖12-21 建立的方案
第十章 編輯方案
我們不僅可以使用方案管理器來創建我們所需的各種方案而且我們可以根據需要來對已有的方案進行編輯。
編輯方案的步驟如下：
（1） 單擊"工具(T)"菜單，在彈出的菜單中選擇"方案(E)..."選項，將彈出"方案管理器"對話框，參見圖12-12所示；
（2） 在"方案(C)"列表框中，選擇要修改的方案，單擊"編輯(E)..."按鈕，將出現"編輯方案"對話框，如圖12-22所示；
圖12-22 "編輯方案"對話框
（3） 根據需要對所需的方案進行修改；
（4） 單擊"確定"按鈕，將出現"方案變量值"對話框，參見圖12-20所示；
（5） 在"方案變量值"對話框中，鍵入可變單元格所需的數值；
（6） 單擊"確定"按鈕，返回"方案管理器"對話框；
（7） 單擊"關閉"按鈕。
* 注意
如果在對方案進行修改後，沒有改變它原來的名稱。則修改後的可變單元格中的新值將替換原來方案中的值。
第十一章 合並方案
對於多個工作簿中各種不同方案，我們可以進行合並操作，這樣就可以非常方便地查看工作簿中不同的方案。
合並方案的步驟如下：
（1） 打開所有要合並的工作簿；
（2） 單擊 "工具(T)"菜單，在彈出的菜單中選擇"方案(E)..."選項，將彈出"方案管理器"對話框，參見圖12-12所示；
（3） 單擊"合並(M)..."按鈕，將出現"合並方案"對話框，如圖12-23所示；
（4） 單擊"工作簿(B)"下拉列表框右端的下拉箭頭，在彈出的下拉列表中選擇所需的工作簿的名稱；
（5） 在"工作表(S)"列表框中，選擇要合並方案的工作表的名稱；
（6） 單擊"合並方案"對話框中的"確定"按鈕，返回"方案管理器"對話框；
（7） 單擊"方案管理器"的"關閉"按鈕。
圖12-23 "合並方案"對話框
第十二章 刪除方案
對於已經不需要的方案，我們可以將其刪除。具體步驟如下：