Excel財(cái)務(wù)應(yīng)用教程1

/實(shí)例十一投資風(fēng)險(xiǎn)分析實(shí)例十一投資風(fēng)險(xiǎn)分析在市場(chǎng)經(jīng)濟(jì)的今天，投資活動(dòng)愈發(fā)顯得頻繁和重要。由于投資活動(dòng)充滿不確定性，所以任何投資總要承擔(dān)一定的風(fēng)險(xiǎn)。如果決策面臨的不確定性比較大，足以影響投資方案的選擇，就應(yīng)該對(duì)不同的方案進(jìn)行計(jì)量，例如計(jì)算比較各種方案的期望凈現(xiàn)值，作為投資決策的依據(jù)。Excel中大量的財(cái)務(wù)、統(tǒng)計(jì)等各種函數(shù)和其強(qiáng)大的表格功能，加上簡(jiǎn)單易行的操作，使其成為輔助投資風(fēng)險(xiǎn)分析的良好助手。其中的“方案管理器”更有助于如投資決策這種多方案問題的分析。本例中，某企業(yè)現(xiàn)在面臨兩種投資方案：新建廠房生產(chǎn)新產(chǎn)品和擴(kuò)建廠房生產(chǎn)現(xiàn)有產(chǎn)品。新建廠房須投資300萬元，擴(kuò)建廠房須投資100萬元。產(chǎn)品的市場(chǎng)前景不能確定。究竟使用那種方案，須考慮多種因素，而兩種方案的預(yù)計(jì)凈現(xiàn)值比較是必須考慮的重要依據(jù)。本例目標(biāo)：學(xué)習(xí)使用IF函數(shù)學(xué)習(xí)設(shè)置單元格的有效數(shù)據(jù)范圍學(xué)習(xí)使用NPV函數(shù)計(jì)算凈現(xiàn)值學(xué)習(xí)在工作表中進(jìn)行方案管理學(xué)習(xí)設(shè)置共享工作簿步驟一：建立工作表該企業(yè)目前面臨5種可能的市場(chǎng)前景，各前景的說明和預(yù)計(jì)發(fā)生概率如表11-1。已知基本折現(xiàn)率為15%，廠房使用年限為4年。表11-1各前景的說明和發(fā)生概率前景說明概率前景1新產(chǎn)品暢銷，現(xiàn)有產(chǎn)品滯銷25%前景2第一年現(xiàn)有產(chǎn)品暢銷，一年后新產(chǎn)品暢銷35%前景3前兩年現(xiàn)有產(chǎn)品暢銷，兩年后新產(chǎn)品暢銷20%前景4前三年現(xiàn)有產(chǎn)品暢銷，三年后新產(chǎn)品暢銷10%前景5現(xiàn)有產(chǎn)品暢銷，新產(chǎn)品滯銷10%首先新建名為“投資風(fēng)險(xiǎn)分析”的工作簿，并在其中建立計(jì)算凈現(xiàn)值的表格（如圖11-1所示）。擬在工作表中先由各種前景的概率計(jì)算出各年的期望年凈收益，再用函數(shù)計(jì)算凈現(xiàn)值。圖11-1建立凈現(xiàn)值計(jì)算表格步驟二：輸入邏輯公式在本例中，要計(jì)算兩種不同的方案的預(yù)計(jì)凈現(xiàn)值，并加以比較。為在一張表格中計(jì)算兩種不同的方案的預(yù)計(jì)凈現(xiàn)值，使用邏輯函數(shù)IF來計(jì)算各種前景下的各年凈收益。一、IF函數(shù)簡(jiǎn)介IF函數(shù)用于執(zhí)行真假值判斷，根據(jù)邏輯測(cè)試的真假值，返回不同的結(jié)果?？梢允褂煤瘮?shù)IF對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)。語法：IF(logical_test,value_if_true,value_if_false)參數(shù)：Logical_test可以是計(jì)算結(jié)果為TRUE或FALSE的任何數(shù)值或表達(dá)式。Value_if_true是Logical_test為TRUE時(shí)函數(shù)的返回值。如果logical_test為TRUE并且省略value_if_true，則返回TRUE。Value_if_true可以為某一個(gè)公式。Value_if_false是Logical_test為FALSE時(shí)函數(shù)的返回值。如果logical_test為FALSE并且省略value_if_false，則返回FALSE。Value_if_false可以為某一個(gè)公式。說明：函數(shù)IF可以嵌套七層，用value_if_false和value_if_true參數(shù)可以構(gòu)造復(fù)雜的檢測(cè)條件。在計(jì)算參數(shù)value_if_true和value_if_false后，函數(shù)IF返回相應(yīng)語句執(zhí)行后的返回值。如果函數(shù)IF的參數(shù)包含數(shù)組，則在執(zhí)行IF語句時(shí)，數(shù)組中的每一個(gè)元素都將計(jì)算。如果某些value_if_true和value_if_false參數(shù)為操作提取函數(shù)，則執(zhí)行所有的操作。二、使用IF函數(shù)下面使用IF函數(shù)計(jì)算各年凈現(xiàn)值。已知如果產(chǎn)品暢銷，預(yù)計(jì)年凈收益為180萬元。如果產(chǎn)品滯銷，預(yù)計(jì)年凈收益為20萬元。操作步驟如下：1.將單元格B4命名為“投資”，將單元格B5命名為“產(chǎn)品”。2.單擊選中B10單元格。由前景說明可知該單元格對(duì)應(yīng)的情況下，新產(chǎn)品暢銷，現(xiàn)有產(chǎn)品滯銷。也就是說如果企業(yè)生產(chǎn)的產(chǎn)品為新產(chǎn)品，則年凈收益為180萬元，如果企業(yè)生產(chǎn)的產(chǎn)品是現(xiàn)有產(chǎn)品，則年凈收益為20萬元。3.單擊“粘貼函數(shù)”按鈕，彈出“粘貼函數(shù)”對(duì)話框（如圖11-2所示）。圖11-2粘貼IF函數(shù)4.在“函數(shù)分類”列表框中單擊選中“邏輯”，在“函數(shù)名”列表框中單擊選中“IF”，單擊“確定”，彈出“IF函數(shù)”框（如圖11-3所示）。圖11-3使用IF函數(shù)5.在“Logical_test”編輯框中鍵入“產(chǎn)品="新產(chǎn)品"”。6.在“Value_if_true”編輯框中鍵入“180”，在“Value_if_false”編輯框中鍵入“20”，單擊“確定”按鈕。由于“產(chǎn)品”單元格中還沒有數(shù)值，即不為“新產(chǎn)品”，所以B10單元格中顯示數(shù)值“20”（如圖11-4所示）。圖11-4邏輯函數(shù)計(jì)算結(jié)果將B10單元格中的公式復(fù)制到所有對(duì)應(yīng)新產(chǎn)品暢銷的單元格中。然后在對(duì)應(yīng)現(xiàn)有產(chǎn)品暢銷的單元格中輸入邏輯計(jì)算公式。在熟悉IF函數(shù)以后，也可以直接在編輯欄中鍵入引用IF函數(shù)的公式，而不必使用“粘貼函數(shù)”按鈕。操作步驟如下：1.單擊B11單元格。2.在編輯欄中鍵入“=IF（產(chǎn)品="現(xiàn)有產(chǎn)品"，180，20）”。3.單擊“輸入”按鈕。4.將B11單元格中的公式復(fù)制到所有對(duì)應(yīng)現(xiàn)有產(chǎn)品暢銷的單元格中。由于“產(chǎn)品”單元格中沒有數(shù)值，即既不為“新產(chǎn)品”，也不為“現(xiàn)有產(chǎn)品”，所以所有的年凈收益單元格中都顯示數(shù)值“20”。按照生產(chǎn)新產(chǎn)品的方案在“投資”單元格和“產(chǎn)品”單元格中鍵入數(shù)據(jù)，計(jì)算表格中顯示對(duì)應(yīng)的年凈收益數(shù)值，如圖11-5所示。圖11-5生產(chǎn)新產(chǎn)品時(shí)的年凈收益步驟三：設(shè)置單元格的有效數(shù)據(jù)范圍在上個(gè)步驟中，用于計(jì)算年凈收益的邏輯函數(shù)引用了“產(chǎn)品”單元格。計(jì)算結(jié)果由“產(chǎn)品”單元格中的數(shù)據(jù)決定。如果在向該單元格中輸入數(shù)據(jù)時(shí)稍出差錯(cuò)，例如，不小心多鍵入了一個(gè)空格，將會(huì)造成年凈收益的計(jì)算錯(cuò)誤。為了避免這種情況的發(fā)生，為該單元格設(shè)置有效的數(shù)據(jù)范圍，使該單元格的數(shù)據(jù)輸入只能從下拉列表中選擇。操作步驟如下：1.選定“產(chǎn)品”單元格（B5）。2.單擊“數(shù)據(jù)”菜單中的“有效數(shù)據(jù)”命令，彈出“有效數(shù)據(jù)”對(duì)話框（如圖11-6所示）。圖11-6設(shè)置單元格的有效數(shù)據(jù)范圍3.在“許可”下拉列表中選擇“序列”。4.在“來源”編輯框中鍵入“新產(chǎn)品,現(xiàn)有產(chǎn)品”。注意：在“來源”編輯框中鍵入的可選單元格數(shù)據(jù)系列中，必須用英文輸入法下的逗號(hào)來分隔。5.選中“提供下拉箭頭”復(fù)選框。6.單擊“錯(cuò)誤警告”選項(xiàng)卡（如圖11-7所示）。圖11-7設(shè)置錯(cuò)誤警告信息7.確定選中“輸入無效數(shù)據(jù)時(shí)顯示出錯(cuò)警告”復(fù)選框。8.在“圖案樣式”下拉列表框中選擇“信息”。9.在“錯(cuò)誤信息”編輯框中鍵入“請(qǐng)?jiān)谙吕斜碇羞x擇輸入選項(xiàng)”。10.單擊“確定”按鈕。經(jīng)過上述步驟，Excel為“產(chǎn)品”單元格設(shè)置下拉列表。當(dāng)單擊該單元格時(shí)，將在單元格右側(cè)顯示下拉箭頭按鈕，單擊下拉箭頭按鈕，顯示在“有效數(shù)據(jù)”對(duì)話框中設(shè)置的列表選項(xiàng)，如圖11-8所示。圖11-8為單元格設(shè)置下拉列表如果在單元格中輸入了錯(cuò)誤的數(shù)據(jù)，例如在“新”字與“產(chǎn)”字之間多輸入了一個(gè)空格，完成輸入時(shí)，Excel將顯示出錯(cuò)信息，如圖11-9所示。圖11-9顯示出錯(cuò)信息步驟四：計(jì)算凈現(xiàn)值下面計(jì)算投資的凈現(xiàn)值。所謂凈現(xiàn)值是指未來各期支出（負(fù)值）和收入（正值）的當(dāng)前值的總和。它是用來比較方案優(yōu)劣的重要指標(biāo)。一、NPV函數(shù)簡(jiǎn)介NPV函數(shù)基于一系列現(xiàn)金流和固定的各期貼現(xiàn)率，返回一項(xiàng)投資的凈現(xiàn)值。語法：NPV(rate、value1、value2……)參數(shù)：rate為各期貼現(xiàn)率，是一固定值。value1，value2……代表1到29筆支出和收入的參數(shù)值。value1、value2……所屬各期間的長(zhǎng)度必須相等，而且支付和收入的時(shí)間都發(fā)生在期末。NPV按次序使用value1、value2……來注釋現(xiàn)金流的次序。所以一定要保證支出和收入的數(shù)額按正確的順序輸入。如果參數(shù)是數(shù)值、空白單元格、邏輯值或表示數(shù)值的文字表達(dá)式，則都會(huì)計(jì)算在內(nèi)；如果參數(shù)是錯(cuò)誤值或不能轉(zhuǎn)化為數(shù)值的文字，則被忽略。如果參數(shù)是一個(gè)數(shù)組或引用，只有其中的數(shù)值部分計(jì)算在內(nèi)。忽略數(shù)組或引用中的空白單元格、邏輯值、文字和錯(cuò)誤值。說明：函數(shù)NPV假定投資開始于value1現(xiàn)金流所在日期的前一期，并結(jié)束于最后一筆現(xiàn)金流的當(dāng)期。函數(shù)NPV依據(jù)未來的現(xiàn)金流計(jì)算。如果第一筆現(xiàn)金流發(fā)生在第一個(gè)周期的期初，則第一筆現(xiàn)金必須加入到函數(shù)NPV的結(jié)果中，而不應(yīng)包含在values參數(shù)中。如果n是values參數(shù)表中的現(xiàn)金流的次數(shù)，則NPV的公式為：二、計(jì)算年凈收益期望值在用NPV函數(shù)計(jì)算凈現(xiàn)值時(shí)，需要用到各期的凈收益值。在計(jì)算凈現(xiàn)值之前首先計(jì)算年凈收益期望值。在B15單元格中鍵入計(jì)算第一年期望凈現(xiàn)值的計(jì)算公式“=B10*$F10+B11*$F11+B12*$F12+B13*$F13+B14*$F14”選中該單元格，用鼠標(biāo)拖拉填充柄，將公式復(fù)制到其他各年的期望年凈收益單元格中。計(jì)算表格中顯示各期的年凈收益期望值，如圖11-10所示。圖11-10計(jì)算年凈收益期望值三、使用NPV函數(shù)計(jì)算凈現(xiàn)值下面用NPV函數(shù)計(jì)算凈現(xiàn)值，操作步驟如下：1.單擊B16單元格。提示：在對(duì)單元格進(jìn)行合并和居中操作后，合并的單元格的引用采用合并區(qū)域左上角的單元格的引用。2.單擊“粘貼函數(shù)”按鈕。3.在“粘貼函數(shù)”對(duì)話框中，在“函數(shù)分類”列表框中選擇“財(cái)務(wù)”，在“函數(shù)名”列表框中選擇“NPV”。4.單擊“確定”，彈出“NPV函數(shù)”框（如圖11-11所示）。5.在“Rate”編輯框中鍵入“基準(zhǔn)折現(xiàn)率”單元格的引用。6.在各“Value”編輯框中鍵入各期年凈收益期望值的單元格引用。圖11-11設(shè)置NPV函數(shù)的參數(shù)7.單擊“確定”。8.由于NPV函數(shù)沒有計(jì)算本年度的現(xiàn)金流，還應(yīng)在原來的基礎(chǔ)上減去投資額。在編輯框中原公式的后面加上“-投資”，公式成為“=NPV(B3,B15,C15,D15,E15)-投資”。經(jīng)過上述步驟，計(jì)算表格中顯示出凈現(xiàn)值的計(jì)算結(jié)果（如圖11-12所示）。圖11-12計(jì)算凈現(xiàn)值步驟五：多方案求解在圖11-12所示的計(jì)算表格中，只顯示出一種方案的計(jì)算結(jié)果。在基本數(shù)據(jù)表格中，輸入另一種方案的數(shù)據(jù)，可得出另一方案的凈現(xiàn)值。對(duì)于這種多方案的問題，使用“方案管理器”可以更好地管理數(shù)據(jù)和信息。還可創(chuàng)建方案總結(jié)報(bào)告和方案數(shù)據(jù)透視表，便于對(duì)各方案進(jìn)行分析比較。一、創(chuàng)建方案創(chuàng)建方案的步驟如下：1.單擊“工具”菜單中的“方案”命令，彈出“方案管理器”對(duì)話框（如圖11-13所示）。圖11-13“方案管理器”對(duì)話框2.單擊“添加”按鈕，彈出“添加方案”對(duì)話框（如圖11-14所示）。圖11-14添加方案3.在“方案名”編輯框中，鍵入方案名稱“擴(kuò)建廠”。4.在“可變單元格”編輯框中，鍵入“投資，產(chǎn)品”。提示：對(duì)話框中“保護(hù)”標(biāo)題下的復(fù)選框用于設(shè)置對(duì)方案的保護(hù)。選中“防止更改”復(fù)選框可禁止對(duì)方案的更改，選中“隱藏”復(fù)選框，可隱藏方案。在這里設(shè)置了對(duì)方案的保護(hù)后，還應(yīng)對(duì)工作表進(jìn)行保護(hù)，方法是：將鼠標(biāo)指向“工具”菜單中的“保護(hù)”選項(xiàng)，然后單擊子菜單中的“保護(hù)工作表”命令。在編輯或刪除方案之前，必須清除“防止更改”復(fù)選框。5.單擊“確定”按鈕，彈出“方案變量值”對(duì)話框（如圖11-15所示）。圖11-15輸入方案變量值6.在“方案變量值”對(duì)話框中，鍵入投資額為“100”，產(chǎn)品為“現(xiàn)有產(chǎn)品”。7.單擊“添加”按鈕，重復(fù)步驟3到步驟6，創(chuàng)建名為“新建廠”的方案，投資額為“300”，產(chǎn)品為“新產(chǎn)品”。8.單擊“確定”按鈕，在方案管理器中顯示已有的方案（如圖11-16所示）。圖11-16在“方案管理器”中顯示已有的方案列表此時(shí)在“方案”列表框中單擊選中一種方案，單擊“刪除”按鈕可將其刪除，單擊“編輯”按鈕，進(jìn)入“編輯方案”對(duì)話框，可對(duì)其進(jìn)行編輯。9.單擊“關(guān)閉”按鈕。經(jīng)過上述步驟，方案的創(chuàng)建完成。二、顯示方案目前，計(jì)算表格中顯示的是“新建廠”方案的數(shù)據(jù)，下面在表格中顯示“擴(kuò)建廠”方案的計(jì)算結(jié)果。操作步驟如下：1.單擊“工具”菜單中的“方案”命令。2.在“方案管理器”對(duì)話框中，單擊選中“方案”列表框中的“擴(kuò)建廠”選項(xiàng)。3.單擊“顯示”按鈕。4.單擊“關(guān)閉”按鈕。在計(jì)算表格中顯示出對(duì)“擴(kuò)建廠”方案的計(jì)算結(jié)果（如圖11-17所示）。圖11-17“擴(kuò)建廠”方案的計(jì)算結(jié)果三、創(chuàng)建方案總結(jié)報(bào)告為了對(duì)方案進(jìn)行比較分析，可創(chuàng)建方案總結(jié)報(bào)告。為明晰數(shù)據(jù)，首先將顯示凈現(xiàn)值的合并單元格命名為“凈現(xiàn)值”。創(chuàng)建方案總結(jié)報(bào)告的操作步驟如下：1.單擊“工具”菜單中的“方案”命令。2.單擊“總結(jié)”按鈕，彈出“方案總結(jié)”對(duì)話框（如圖11-18所示）。圖11-18“方案總結(jié)”對(duì)話框3.單擊“方案總結(jié)”選項(xiàng)按鈕。4.在“結(jié)果單元格”編輯框中，鍵入“凈現(xiàn)值”。提示：在“結(jié)果單元格”中，可輸入多個(gè)單元格的引用或名稱，各引用需用逗號(hào)分隔。在生成方案總結(jié)報(bào)告時(shí)不一定需要結(jié)果單元格，而在生成方案數(shù)據(jù)透視表報(bào)告時(shí)則一定需要。5.單擊“確定”。Excel在當(dāng)前工作表之前插入一張名為“方案總結(jié)”的工作表，在其中顯示各方案和表格當(dāng)前值的計(jì)算結(jié)果，如圖11-19所示。圖11-19方案總結(jié)報(bào)告步驟六：共享工作簿投資決策的指定決不是簡(jiǎn)單的比較各方案的預(yù)計(jì)凈現(xiàn)值就可以決定的，畢竟凈現(xiàn)值只是需要考慮的一個(gè)重要的方面，而且計(jì)算得出的結(jié)果只是預(yù)測(cè)結(jié)果。投資決策的指定，還需要考慮其他方面的各種因素。在企業(yè)管理中進(jìn)行科學(xué)的決策，應(yīng)避免一人說了算。眾人討論的結(jié)果要更加科學(xué)、可靠。電腦網(wǎng)絡(luò)使得人們不必坐在一起，就可以進(jìn)行討論。在用連網(wǎng)的電腦進(jìn)行決策討論時(shí)，應(yīng)共享所有的決策參考數(shù)據(jù)。將這個(gè)輔助投資風(fēng)險(xiǎn)分析的工作簿設(shè)置為共享工作簿，可使多人同時(shí)參閱該工作簿或?qū)υ摴ぷ鞑具M(jìn)行操作，例如在工作簿中添加批注。一、共享工作簿簡(jiǎn)介通過建立共享工作簿，可以與其他人同時(shí)審閱和編輯同一份工作，還可以查看各自所做的改動(dòng)。當(dāng)多人一起在共享工作簿上工作時(shí)，Excel會(huì)保持信息不斷被更新。在一個(gè)共享工作簿中，各個(gè)用戶可以輸入數(shù)據(jù)，插入行和列，添加和更改公式，還可以更改格式。每個(gè)用戶能夠獨(dú)立地篩選工作表以顯示感興趣的數(shù)據(jù)行。Excel可以為每一位用戶保留各自的視面，其中包含用戶的各種篩選設(shè)置。各用戶都可通過單擊“保存”按鈕，以按自己所做的更改更新共享工作簿。還可得到所有其他用戶保存的更改內(nèi)容。Excel能夠自動(dòng)按指定的時(shí)間間隔對(duì)更改進(jìn)行更新。在保存一個(gè)共享工作簿時(shí)，可以用自己所做的更改替換他人作出的相沖突的更改，也可以審查每一種更改以決定是否接受。通過保存沖突日志，可以保留被接受更改的記錄，并可以查看一個(gè)記錄著詳細(xì)更改情況的日志工作表，其中包含用戶間互相沖突的更改內(nèi)容。保留沖突日志還使用戶能夠合并共享工作簿的各種副本。在共享工作簿時(shí)，既可以給每個(gè)人相同的權(quán)限，也可以通過保護(hù)共享工作簿來限制權(quán)限。二、設(shè)置共享工作簿下面將這個(gè)輔助投資風(fēng)險(xiǎn)分析的工作簿設(shè)置為共享工作簿，操作步驟如下：1.單擊“工具”菜單中的“共享工作簿”命令，彈出“共享工作簿”對(duì)話框（如圖11-20所示）。圖11-20設(shè)置共享工作簿2.單擊“編輯”選項(xiàng)卡，選中“允許多用戶同時(shí)編輯，同時(shí)允許工作簿合并”復(fù)選框，然后單擊“確定”。3.單擊“確定”，保存工作簿。4.在“文件”菜單中單擊“另存為”命令，然后將共享工作簿保存在其他用戶可以訪問到的一個(gè)網(wǎng)絡(luò)資源上。注意：如果要將共享工作簿復(fù)制到一個(gè)網(wǎng)絡(luò)資源上，應(yīng)確保該工作簿與其他工作簿或文檔的任何鏈接都保持完整?？梢允褂谩熬庉嫛辈藛沃械摹版溄印泵顚?duì)鏈接定義進(jìn)行修正。這一步驟同時(shí)也啟用了沖突日志，使用它可以查看對(duì)共享工作簿的更改信息，以和在有沖突時(shí)修改的取舍情況。三、撤消工作簿的共享狀態(tài)如果不再需要其他人對(duì)共享工作簿進(jìn)行更改，可以將自己作為唯一用戶打開并操作該工作簿。撤消工作簿的共享狀態(tài)的操作如下：1.單擊“工具”菜單中的“共享工作簿”命令，然后單擊“編輯”選項(xiàng)卡。2.確認(rèn)自己是在“正在使用本工作簿的用戶”框中的唯一一位用戶，如果還有其他用戶，他們都將丟失未保存的工作內(nèi)容。3.清除“允許多用戶同時(shí)編輯，同時(shí)允許工作簿合并”復(fù)選框，然后單擊“確定”按鈕。4.當(dāng)提示到對(duì)其他用戶的影響時(shí)，單擊“是”按鈕。注意：一旦撤消了工作簿的共享狀態(tài)，將中斷所有其他用戶與共享工作簿的聯(lián)系、關(guān)閉沖突日志，并清除已存儲(chǔ)的沖突日志，此后就不能再查看沖突日志，或是將共享工作簿的此備份與其他備份合并。為了確保其他用戶不會(huì)丟失工作進(jìn)度，應(yīng)在撤消工作簿共享之前確認(rèn)所有其他用戶都已得到通知，這樣，他們就能事先保存并關(guān)閉共享工作簿。四、保護(hù)共享工作簿能夠訪問保存有共享工作簿的網(wǎng)絡(luò)資源的所有用戶，都可以訪問共享工作簿。如果希望防止對(duì)共享工作簿的某些訪問，可以通過保護(hù)共享工作簿和沖突日志來實(shí)現(xiàn)。與一般工作簿一樣，也可以為共享工作簿指定一個(gè)打開時(shí)輸入的密碼，且方法相同，詳細(xì)內(nèi)容參閱本書實(shí)例七。防止他人對(duì)共享工作簿進(jìn)行更改的操作步驟如下：1.為設(shè)置共享工作簿的改動(dòng)密碼，先撤消對(duì)工作簿的共享。2.隱藏不希望其他用戶看到的某些行和列，取消允許其他人進(jìn)行更改的指定區(qū)域的鎖定。3.在“工具”菜單中將鼠標(biāo)指向“保護(hù)”子菜單，然后單擊“保護(hù)并共享工作簿”命令，彈出“保護(hù)共享工作簿”對(duì)話框（如圖11-21所示）。4.選中“以追蹤修訂方式共享”復(fù)選框。5.設(shè)置其他用戶在關(guān)閉沖突日志或撤消工作簿共享狀態(tài)時(shí)須輸入的密碼，在“密碼”框中鍵入密碼，單擊“確定”。6.在“確認(rèn)密碼”對(duì)話框中再輸入一遍密碼，單擊“確定”。7.在出現(xiàn)提示時(shí)，單擊“確定”保存工作簿，這樣可以共享此工作簿并且啟用沖突日志。圖11-21“保護(hù)共享工作簿”對(duì)話框注意：為工作簿提供共享保護(hù)以后，其他用戶就不能撤消工作簿共享狀態(tài)或者關(guān)閉沖突日志。在一個(gè)已經(jīng)共享的工作簿中，可以啟用對(duì)共享和沖突日志的保護(hù)，但是不能為這種保護(hù)指定密碼。如果需指定密碼，必須首先撤消工作簿的共享狀態(tài)。本例中重要講述了邏輯公式的建立，設(shè)置單元格的有效數(shù)據(jù)范圍，財(cái)務(wù)函數(shù)NPV的使用，多方案求解問題以和工作簿的共享。IF函數(shù)用于執(zhí)行真假值判斷，根據(jù)邏輯測(cè)試的真假值，返回不同的結(jié)果。可以使用函數(shù)IF對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)。通過對(duì)單元格有效數(shù)據(jù)范圍的設(shè)置，可以防止由于數(shù)據(jù)輸入錯(cuò)誤造成的計(jì)算錯(cuò)誤。本例中，為單元格設(shè)置了有效數(shù)據(jù)的下拉列表。對(duì)于多方案的問題，使用“方案管理器”可以更好地管理數(shù)據(jù)和信息。還可創(chuàng)建方案總結(jié)報(bào)告和方案數(shù)據(jù)透視表，便于對(duì)各方案進(jìn)行分析比較。通過建立共享工作簿，可以與其他人同時(shí)審閱和編輯同一份工作，還可以查看各自所作的改動(dòng)。問題一：有效數(shù)據(jù)有哪些類型答：“有效數(shù)據(jù)”對(duì)話框中顯示的選項(xiàng)根據(jù)“許可”和“數(shù)據(jù)”下拉列表框中設(shè)置的不同而不同?？蛇x的有效數(shù)據(jù)的類型如下：任何數(shù)值對(duì)輸入數(shù)據(jù)不作任何限制。如果希望不檢查輸入的正確性而只顯示輸入信息，可使用此設(shè)置。自定義這種設(shè)置允許輸入公式、使用表達(dá)式或者引用其他單元格中的計(jì)算值來判定輸入數(shù)值的正確性。公式必須以等號(hào)“＝”開始，且得出的必須是True或False。日期指定輸入的數(shù)值必須為日期。在“數(shù)據(jù)”下拉列表框中單擊選定一個(gè)操作符，接著填充下面的編輯框，如“起始日期”、“終止日期”，可指定輸入日期的范圍。小數(shù)指定輸入的數(shù)值必須為數(shù)字或小數(shù)。在“數(shù)據(jù)”下拉列表框中單擊選定一個(gè)操作符，接著填充下面的編輯框，如“最小值”、“最大值”，可指定輸入小數(shù)的范圍。序列為有效數(shù)據(jù)指定序列。在“來源”編輯框中此輸入工作簿上包含有效數(shù)據(jù)的區(qū)域的引用或名稱或者以逗號(hào)為間隔符直接鍵入有效數(shù)據(jù)（例如：銷售部、生產(chǎn)部、技術(shù)部、公關(guān)部）。選中“提供下拉箭頭”復(fù)選框，將在用戶單擊單元格時(shí)顯示一下拉箭頭，讓用戶在有效數(shù)據(jù)序列中選擇。文本指定有效數(shù)據(jù)的字符數(shù)。在“數(shù)據(jù)”下拉列表框中單擊選定一個(gè)操作符，接著填充下面的編輯框，如“最小值”、“最大值”，可指定輸入數(shù)據(jù)字符數(shù)的范圍。時(shí)間指定輸入的數(shù)值必須為時(shí)間。在“數(shù)據(jù)”下拉列表框中單擊選定一個(gè)操作符，接著填充下面的編輯框，如“起始時(shí)間”、“終止時(shí)間”，可指定輸入時(shí)間的范圍。整數(shù)指定輸入的數(shù)值必須為整數(shù)。在“數(shù)據(jù)”下拉列表框中單擊選定一個(gè)操作符，接著填充下面的編輯框，如“最小值”、“最大值”，可指定輸入整數(shù)的范圍。問題二：為何“數(shù)據(jù)”菜單中的“有效數(shù)據(jù)”命令不能使用答：由于下列原因，“有效數(shù)據(jù)”命令將不能使用：正在輸入數(shù)據(jù)。當(dāng)設(shè)置了有效數(shù)據(jù)范圍或信息的單元格中正在進(jìn)行編輯時(shí)，“有效數(shù)據(jù)”命令不能使用。工作表內(nèi)容可能處于保護(hù)狀態(tài)。如果要取消保護(hù)，指向“工具”菜單中的“保護(hù)”子菜單，再單擊“撤消工作表保護(hù)”命令。工作簿可能是共享工作簿的。盡管仍可以繼續(xù)輸入數(shù)據(jù)，并且輸入信息和錯(cuò)誤信息也照常顯示，但是在工作簿被共享時(shí)，“有效數(shù)據(jù)”命令不能使用。問題三：使用共享工作簿有哪些限制答：使用共享工作簿時(shí)，Excel的某些功能無效，如果需要使用這些功能，應(yīng)在將工作簿共享之前進(jìn)行操作，或撤消工作簿的共享狀態(tài)。在共享工作簿中，不能完成下列操作：刪除工作表合并單元格可以在將工作簿共享之前查看合并單元格的單元格。定義或使用條件格式可以在工作簿共享之前查看條件格式的使用效果。設(shè)置或更改數(shù)據(jù)有效性的限制和消息可以在工作簿共享之前查看所設(shè)置的限制和消息的效果。成塊插入或刪除單元格可以插入或刪除整個(gè)行和列。插入或更改圖表、圖片、對(duì)象或超級(jí)鏈接使用繪圖工具設(shè)置密碼來保護(hù)單獨(dú)的工作表或整個(gè)工作簿在工作簿共享之前使用的保護(hù)措施，在工作簿共享之后依然有效。更改或刪除密碼在工作簿共享之前設(shè)置的密碼，在工作簿共享之后依然有效。保存、查看或更改方案創(chuàng)建組或分級(jí)顯示數(shù)據(jù)插入自動(dòng)分類匯總創(chuàng)建模擬運(yùn)算表創(chuàng)建數(shù)據(jù)透視表或更改已存在的數(shù)據(jù)透視表布局寫入、更改、查看、記錄或分配宏可以將共享工作簿中所錄制的宏保存到另一個(gè)未共享的工作簿中。在共享工作簿中，還可以使用工作簿共享之前創(chuàng)建的宏，但在這種情況下，如果所使用的宏中包含某個(gè)此時(shí)無效的操作，宏將在運(yùn)行到此無效操作時(shí)停止運(yùn)行。問題四：如何為工作簿保存沖突日志答：如果為工作簿保存沖突日志，Excel會(huì)同時(shí)開啟工作簿共享。保存沖突日志的操作步驟如下：1.單擊“工具”菜單中的“共享工作簿”命令。2.單擊“編輯”選項(xiàng)卡。3.選定“允許多用戶同時(shí)編輯，同時(shí)允許工作簿合并”復(fù)選框。4.單擊“高級(jí)”選項(xiàng)卡（如圖11-22所示）。圖11-22保存沖突日志5.在“修訂”標(biāo)題下，單擊“保存修訂記錄”，接著在“天”微調(diào)編輯框中鍵入希望保留沖突日志的天數(shù)。6.單擊“確定”按鈕。當(dāng)彈出對(duì)話框提示保存工作簿時(shí)，再次單擊“確定”按鈕。問題五：如何查看共享工作簿中有關(guān)相互沖突更改的信息答：在創(chuàng)建共享工作簿以后，沖突日志就被啟用，可以查看以前有關(guān)相互沖突更改的信息。如果關(guān)閉沖突日志，Excel將不再保留有關(guān)相互沖突更改的信息。查看沖突日志的操作步驟如下：1.指向“工具”菜單中的“修訂”子菜單，然后單擊“突出顯示修訂”命令，彈出“突出顯示修訂”對(duì)話框（如圖11-23所示）。圖11-23查看沖突日志2.選中“時(shí)間”選框，然后單擊“時(shí)間”框中的“全部”。3.確認(rèn)“修訂者”和“位置”復(fù)選框已被清除。4.選中“在新工作表上顯示修訂”復(fù)選框，然后單擊“確定”按鈕。Excel在工作簿中插入“沖突日志”工作表（如圖11-24所示），可在其中查看工作表的更改記錄。圖11-24查看沖突日志被保留的相互沖突的更改在“操作類型”列中顯示為“成功”，用于在沖突日志工作表中說明被舍棄更改的數(shù)據(jù)行，將在“操作失敗”列中顯示行號(hào)。第十章完成復(fù)雜計(jì)算公式是對(duì)單元格中數(shù)值進(jìn)行計(jì)算的等式，使用公式可以進(jìn)行數(shù)據(jù)計(jì)算。函數(shù)是Excel2000中預(yù)定的內(nèi)置公式，使用函數(shù)可以提高公式計(jì)算的效率。數(shù)組是一種計(jì)算工具，可用來建立產(chǎn)生多個(gè)數(shù)值或?qū)σ唤M數(shù)據(jù)進(jìn)行操作的公式。綜合使用公式、函數(shù)和數(shù)組可以在Excel中完成復(fù)雜計(jì)算。第一節(jié)創(chuàng)建與編輯公式使用公式可進(jìn)行例如加、減、乘、除等簡(jiǎn)單的計(jì)算，也可以完成很復(fù)雜的財(cái)務(wù)、統(tǒng)計(jì)和科學(xué)計(jì)算，還可以用公式進(jìn)行比較或操作文本。公式是工作表的核心，如果沒有公式，Excel2000這樣的電子表格軟件就失去了其存在的意義。下面是幾個(gè)公式的例子：＝73十27＝sum(A1：B7)＝收入—支出上面的例子體現(xiàn)了Excel公式的語法，即公式以等號(hào)開頭，后面緊接著運(yùn)算數(shù)和運(yùn)算符，運(yùn)算數(shù)可以是常數(shù)、單元格引用、單元格名稱和工作表函數(shù)。一、創(chuàng)建公式1．公式中的運(yùn)算符Excel的運(yùn)算符有以下4類：·算術(shù)運(yùn)算符：完成基本數(shù)學(xué)運(yùn)算，如加、減、乘、除等，它們連接數(shù)字并產(chǎn)生計(jì)算結(jié)果。·比較運(yùn)算符：用來比較兩個(gè)數(shù)值大小關(guān)系的運(yùn)算符，它們返回邏輯值TRUE或FALSE?！の谋具\(yùn)算符：用來將多個(gè)文本連接成組合文本。·引用運(yùn)算符：可以將單元格區(qū)域合并運(yùn)算。各種運(yùn)算符的含義和示例請(qǐng)見表10．1。表10.1Excel公式中的運(yùn)算符算術(shù)運(yùn)算符含義示例+（加號(hào)）加1+2–（減號(hào)）減2–1–（負(fù)號(hào)）負(fù)數(shù)–1*（星號(hào)）乘2*2/（斜杠）除4/2%（百分比）百分比12%?（脫字符）乘冪3?2（續(xù)表）比較運(yùn)算符含義示例=（等號(hào)）等于A1=A2>（大于號(hào)）大于A1>A2<（小于號(hào)）小于A1<A2>=（大于等于號(hào)）大于等于A1>=A2<=（小于等于號(hào)）小于等于A1<=A2<>（不等號(hào)）不等于A1<>A2文本運(yùn)算符含義示例&（連字符）將兩個(gè)文本連接起來產(chǎn)生連續(xù)的文本“學(xué)會(huì)”&“求知”產(chǎn)生“學(xué)會(huì)求和”引用運(yùn)算符含義示例：（冒號(hào)）區(qū)域運(yùn)算符，對(duì)兩個(gè)引用之間包括這兩個(gè)引用在內(nèi)的所有單元格進(jìn)行引用A1：D1（引用A1到D1范圍內(nèi)的所有單元格），（逗號(hào)）聯(lián)合運(yùn)算符，將多個(gè)引用合并為一個(gè)引用SUM（A1：D1，A2：C2）將A1：D2和A2：C2兩個(gè)區(qū)域合并為一個(gè)（空格）交叉運(yùn)算符，產(chǎn)生同時(shí)屬于兩個(gè)引用的單元格區(qū)域的引用SUM（A1：F1B1：B4）（B1同時(shí)屬于兩個(gè)引用A1：F1，B1：B4）2．公式的運(yùn)算順序運(yùn)算符（優(yōu)先級(jí)從高到低）說明：（冒號(hào)），（逗號(hào)）（空格）–（負(fù)號(hào)）%（百分號(hào)）?（脫字符）*和/+和–&=、>、<、>=、<=、<>區(qū)域運(yùn)算符聯(lián)合運(yùn)算符交叉運(yùn)算符–5百分比乘冪乘和除加和減文本運(yùn)算符比較運(yùn)算符每個(gè)運(yùn)算符都有自己的運(yùn)算優(yōu)先級(jí)，表10.2列出了各種運(yùn)算符的優(yōu)先級(jí)，對(duì)于不同優(yōu)先級(jí)的運(yùn)算，按照優(yōu)先級(jí)從高到低的順序進(jìn)行。對(duì)于同一優(yōu)先級(jí)的運(yùn)算，按照從左到右的順序進(jìn)行。使用括號(hào)把公式中優(yōu)先級(jí)低的運(yùn)算括起來，可以改變運(yùn)算的順序。表10.2各種運(yùn)算符的優(yōu)先級(jí)二、公式的輸入1．在編輯欄中輸入公式像輸入數(shù)字或文本一樣鍵入公式后，再按Enter鍵或單擊“輸入”按鈕。2．在單元格里直接輸入公式雙擊要輸入公式的單元格，或者先選中單元格再按F2鍵后，在單元格中輸入公式，最后按Enter鍵。3.舉例說明輸入公式的具體步驟(1)建立一個(gè)如圖10.1所示的工作表。(2)選定單元格D2。(3)在編輯欄中輸入“＝B2+C2”。(4)按Enter鍵，D2中將顯示公式的計(jì)算結(jié)果158。例10．1創(chuàng)建并輸入公式，將圖10．1工作表中B4和C4單元格中的兩個(gè)文本連接為一個(gè)文本。(1)選定單元格E4。(2)在編輯欄中輸入“＝”，然后用鼠標(biāo)單擊B4單元格，發(fā)現(xiàn)編輯欄中顯示“＝B2”，在編輯欄中輸入“＆”，再用鼠標(biāo)單擊C4單元格，編輯欄中顯示“＝B4＆C4”。(3)按Enter鍵，E4單元格中顯示“學(xué)會(huì)求知”，如圖10．2所示，這是公式“＝B4&C4”的計(jì)算結(jié)果。圖10．1工作表示例圖10．2文本連接的計(jì)算結(jié)果實(shí)用技巧當(dāng)要在一個(gè)單元格區(qū)域中輸入同一個(gè)公式時(shí)，先選定該區(qū)域，如圖10．3所示，輸入公式(“B6十C6”或“B6:B8十C6：C8”)后，按“Ctrl十Enter”鍵，執(zhí)行以上操作就不必在每個(gè)單元格中逐一輸入公式。圖10．3選定單元格區(qū)域示例在按Enter鍵確認(rèn)輸人的公式之前，公式實(shí)際上并沒有被存儲(chǔ)在單元格中，可以單擊編輯欄左邊的“取消”按鈕或按Esc鍵來取消輸入的公式。三、公式的編輯單元格中的公式也可以像單元格中的其他數(shù)據(jù)一樣進(jìn)行編輯，例如修改、復(fù)制、移動(dòng)等。1．修改公式修改公式同修改單元格中數(shù)據(jù)的方法一樣。先單擊包含要修改公式的單元格，如果要?jiǎng)h除公式中的某些項(xiàng)，在編輯欄中用鼠標(biāo)選中要?jiǎng)h除的部分后，再按Backspace或者Delete。鍵。如要替換公式中的某些部分，須先選中被替換的部分，然后再進(jìn)行修改。在未確認(rèn)之前單擊“取消”按鈕或按Esc鍵放棄本次修改。如果已確認(rèn)修改但還未進(jìn)行其他命令，單擊“編輯”菜單中的“撤消”命令或按“Ctrl十Z”鍵仍可放棄本次修改。2．復(fù)制公式以將圖10．3單元格D2中的公式復(fù)制到單元格D6中為例，操作步驟如下：(1)選定單元格D2。(2)單擊“編輯”菜單中的“復(fù)制”命令，或按“Ctrl十C”快捷鍵。(3)單擊D6單元格。(4)單擊“編輯”菜單中的“選擇性粘貼”選項(xiàng)，彈出如圖10．4所示的“選擇性粘貼”對(duì)話框。圖10.4“選擇性粘貼”對(duì)話框(5)在“選擇性粘貼”對(duì)話框中選擇“公式”單選按鈕。(6)單擊“確定”按鈕，D6中顯示26，即已將D2中的公式復(fù)制過來。3．移動(dòng)公式以將圖10.3單元格D2中的公式復(fù)制到單元格G2中為例，操作步驟如下：圖10.4“選擇性粘貼”對(duì)話框(1)選定D2單元格。(2)將鼠標(biāo)移到D2單元格的邊框上，當(dāng)鼠標(biāo)變?yōu)榘咨^時(shí)按下左鍵。(3)拖動(dòng)鼠標(biāo)到G2單元格。(4)釋放左鍵。也可以用菜單命令或“常用”工具欄上的工具按鈕像移動(dòng)單元格一樣來移動(dòng)公式。四、使用復(fù)雜公式1.公式中的數(shù)值轉(zhuǎn)換在Excel中數(shù)據(jù)是分類型的，例如數(shù)字型、文本型、邏輯型等。在公式中，每個(gè)運(yùn)算符都只能連接特定類型的數(shù)據(jù)。如果運(yùn)算符連接的數(shù)值與所需的類型不同，Excel能自動(dòng)轉(zhuǎn)換數(shù)值類型。表10.3給出了幾個(gè)數(shù)值轉(zhuǎn)換的例子。表10.3公式中數(shù)值轉(zhuǎn)換示例公式運(yùn)算結(jié)果說明=“4”*“7”28當(dāng)使用+、–、*、/等運(yùn)算符時(shí)，Excel認(rèn)為運(yùn)算數(shù)是數(shù)字。Excel自動(dòng)將字符型數(shù)據(jù)“4”和“7”轉(zhuǎn)換為數(shù)字=“99/7/20”–“98/7/20”365Excel將具有yy/mm/dd格式的文本當(dāng)作日期，將日期轉(zhuǎn)換成序列數(shù)之后，再進(jìn)行計(jì)算SUM（“3+2”，5）3&“Word”#VALUE！3Word返回出錯(cuò)值，因?yàn)镋xcel不能將文本（3+2）轉(zhuǎn)換成數(shù)字，而SUM（“5”，5）可以返回10當(dāng)公式中需要文本型數(shù)值時(shí)，Excel自動(dòng)將數(shù)字轉(zhuǎn)換成文本2．日期和時(shí)間的使用在Excel2000中不僅可以對(duì)數(shù)字和字符進(jìn)行計(jì)算，也可以對(duì)日期和時(shí)間進(jìn)行計(jì)算。Excel中顯示的時(shí)間和日期數(shù)字(例如34412.25)，是以1990年1月1日星期日為日期起點(diǎn)，數(shù)值設(shè)定為1；以午夜零時(shí)(0：00：00)為時(shí)間起點(diǎn)，數(shù)值設(shè)定為0.0，其范圍是24小時(shí)。日期的計(jì)算中經(jīng)常用到兩個(gè)日期之差，例如公式＝“98／10／20”—“98／10／5”，計(jì)算結(jié)果為15。也可以進(jìn)行其他計(jì)算，例如公式＝“99／7／20”十“99／7／5”，計(jì)算結(jié)果為72707。在Excel2000中輸入日期時(shí)如果以短格式輸入年份(年份輸入兩位數(shù))，Excel將做如下處理：(1)如果年份在00至29之間，Excel將作為2000至2029年處理，例如輸入10／10／20，Excel認(rèn)為這個(gè)日期是2010年10月20日。(2)如果年份在30至99之間，Excel將其作為1930至1999年處理，例如輸入73／3／23，Excel認(rèn)為這個(gè)日期是1973年3月23日。五、公式返回的錯(cuò)誤值和產(chǎn)生原因在使用公式進(jìn)行計(jì)算時(shí)，有時(shí)會(huì)在單元格中看到“#NAME?”、“#VALUE?”等信息。這些都是使用公式時(shí)出現(xiàn)錯(cuò)誤后返回的錯(cuò)誤值，產(chǎn)生原因請(qǐng)見表10．4。表10.4公式返回的錯(cuò)誤值和其產(chǎn)生原因返回的錯(cuò)誤值產(chǎn)生的原因#####！公式計(jì)算的結(jié)果太長(zhǎng)，單元格容納不下，增加單元格的列寬可以解決這個(gè)問題#DIV/0除數(shù)為零#N/A公式中無可用的數(shù)值或缺少函數(shù)參數(shù)#NAME？使用了Excel不能識(shí)別的名稱#NULL！使用了不正確的區(qū)域運(yùn)算或不正確的單元格引用#NUM！在需要數(shù)字參數(shù)的函數(shù)中使用了不能接受的參數(shù)，或者公式計(jì)算結(jié)果的數(shù)字太大或太小，Excel無法表示#REF！公式中引用了無效單元格#VALUE！需要數(shù)字或邏輯值時(shí)輸入了文本第二節(jié)單元格的引用單元格的引用就是指單元格的地址，單元格的引用把單元格中的數(shù)據(jù)和公式聯(lián)系起來。在創(chuàng)建和使用復(fù)雜公式時(shí)，單元格的引用是非常有用的。Excel2000通過單元格引用來指定工作薄中的單元格或單元格區(qū)域。一、單元格引用和引用樣式單元格引用的作用在于標(biāo)識(shí)工作表上的單元格和單元格區(qū)域，并指明使用數(shù)據(jù)的位置。通過引用可以在公式中使用單元格中的數(shù)據(jù)。單元格引用有不同的表示方法，即可以直接用相應(yīng)的地址表示，也可以用單元格的名字表示。用地址來表示單元格引用有兩種樣式：·A1引用樣式：這是默認(rèn)樣式。這種引用是用字母來表示列(從A到IV共256列)，用數(shù)字來表示行(從1到65536)。引用的時(shí)候，先寫列字母再寫行數(shù)字，如B2?！1C1樣式，R代表Row，是行的意思；C代表Column，是列的意思。在R1C1引用樣式中，用R加行數(shù)字和C加列數(shù)字來表示單元格的位置，如R3C2指位于第3行第2列上的單元格。在A1引用樣式中又包括絕對(duì)引用、相對(duì)引用和混合引用三種樣式?！は鄬?duì)引用相對(duì)引用的意義是指單元格引用會(huì)隨公式所在單元格的位置變更而改變。也就是說，相對(duì)引用在被復(fù)制到其他單元格時(shí)，其單元格引用地址發(fā)生改變。相對(duì)引用的樣式是用字母表示列，用數(shù)字表示行，例如A1、B2等，但是只使用相對(duì)引用是無法滿足使用需要的?！そ^對(duì)引用絕對(duì)引用是指引用特定位置的單元格。如果公式中的引用是絕對(duì)引用，那么復(fù)制后的公式引用不會(huì)改變。絕對(duì)引用的樣式是在列字母和行數(shù)字之前加上美元符$’，例如由$A$2、$B$5都是絕對(duì)引用?！せ旌弦贸讼鄬?duì)引用和絕對(duì)引用之外，還有混合引用。當(dāng)需要固定某行引用而改變列引用，或者需要固定某列引用而改變行引用時(shí)，就要用到混合引用，例如$B5、B$5都是混合引用。在Excel2000中，使用F4鍵可以快速改變單元格引用的類型。示例如下：(1)選擇單元格A1然后鏈入：“＝$B$2”。(2)按F4鍵將引用變?yōu)榻^對(duì)引用，該公式變?yōu)椋骸埃?B$2”。(3)再按F4健將引用變?yōu)榛旌弦?絕對(duì)行，相對(duì)列)，公式變?yōu)椋骸埃紹$2”。(4)再按F4鍵將引用變?yōu)榱硪环N混合形式(絕對(duì)列，相對(duì)行)，公式變?yōu)椋骸埃?B2”。(5)再按P4鍵返回到原來的相對(duì)引用形式。二、輸入單元格引用在Excel中使用鼠標(biāo)輸入單元格引用比用鍵盤節(jié)省時(shí)間而且準(zhǔn)確率高。例10.2用鼠標(biāo)在單元格A3中輸入對(duì)A1和A2的引用。(1)選擇A3，然后鍵人一個(gè)等號(hào)“＝”。(2)單擊A1并鍵人一個(gè)加號(hào)“十”。(3)單擊A2后按Enter鍵。當(dāng)單擊某單元格時(shí)，閃爍的邊框環(huán)繞著該單元格，同時(shí)在A3中插入了對(duì)該單元格的引用。在結(jié)束公式的輸入時(shí)，必須按Enter鍵。若沒按Enter鍵并且選擇了別的單元格，Excel便認(rèn)為要在公式中包括該單元格的引用，而不僅僅是以前指定的單元格。在向活動(dòng)單元格輸入數(shù)值或其他單元格的引用時(shí)，活動(dòng)單元格不必出現(xiàn)在當(dāng)前窗口中創(chuàng)建公式時(shí)，可以通過滾動(dòng)條波動(dòng)工作表來選擇工作表中距離公式單元格較遠(yuǎn)的單元格。不管活動(dòng)單元格位于工作表的什么位置，編輯欄總是顯示活動(dòng)單元格中的內(nèi)容。實(shí)用技巧若滾動(dòng)工作表后活動(dòng)單元格不再可見，按“Ctrl＋Backspace”鍵可快速重新顯示活動(dòng)單元格。在編輯公式時(shí)，被該公式所引用的所有單元格和單元格區(qū)域都將以彩色顯示在存放公式的單元格中，并在相應(yīng)單元格和單元格區(qū)域的周圍顯示具有相同顏色的邊框。三、引用其他工作表中的單元格在Excel中，不僅可以引用當(dāng)前工作表的單元格，還可以引用工作簿中其他工作表，其方法是：在公式中同時(shí)包括工作表引用和單元格引用。例如，要引用工作表Sheet9中的B2單元格，應(yīng)在公式中輸入Sheet9!B2。感嘆號(hào)將工作表引用和單元格引用分開。如果工作表已命名，只需使用工作表名字再加上單元格引用。但是如果工作表名字中包含空格，必須用單引號(hào)括住工作表引用。使用鼠標(biāo)也可以引用工作簿中另一張工作表的單元格或單元格范圍，其方法是：進(jìn)入輸入公式的狀態(tài)，然后單擊需要引用的單元格所在的工作表標(biāo)簽，選中需要引用的單元格，則該單元格引用會(huì)顯示在編輯欄中。如果工作表名字包括空格，Excel2000會(huì)自動(dòng)用單引號(hào)括住工作表引用，最后按Enter鍵完成公式的輸入。四、引用其他工作薄中的單元格在Excel中，不但可以引用同一工作薄中不同工作表的單元格，還能引用不同工作薄中的單元格。其方法是：在公式中同時(shí)包括工作薄引用、工作表引用和單元格引用。例如：＝[Book1]Sheetl!$A$1一[Book2]Sheet2!$B$1在上面的公式中，[Book1]和[Book2]是兩個(gè)不同工作簿的名稱，Sheet1和Sheet2是分別屬于兩個(gè)工作簿的工作表的名稱。$A$1和$B$1表示單元格的絕對(duì)引用。若引用的工作簿已關(guān)閉，那么在引用中將出現(xiàn)該工作簿存放位置的全部路徑，例如：＝Sheet1!$A$1－‘C:\MYDOCUMENTS\[Book2.XLS]Sheet2’!$B$1第三節(jié)函數(shù)函數(shù)是一些已經(jīng)定義好的公式，Excel2000中的大多數(shù)函數(shù)是常用公式的簡(jiǎn)寫形式。函數(shù)通過參數(shù)接收數(shù)據(jù)，輸入的參數(shù)應(yīng)放到函數(shù)名后并且用括號(hào)括起來。各函數(shù)使用特定類型的參數(shù)，例如：數(shù)字、引用、文本或編輯值等。函數(shù)大多數(shù)情況下返回的是計(jì)算的結(jié)果，也可以返回文本、引用、邏輯值、數(shù)組或者工作表的信息。在Excel2000中，不僅提供了大量的內(nèi)置函數(shù)，還可以根據(jù)特定的需要使用VisualBasic自定義函數(shù)。使用公式時(shí)盡可能地使用內(nèi)置函數(shù)，它可以節(jié)省輸入時(shí)間，減少錯(cuò)誤發(fā)生。一、Excel內(nèi)置函數(shù)Excel提供了大量的內(nèi)置函數(shù)，按照功能進(jìn)行分類，如表10．5所示。表10.5內(nèi)置函數(shù)分類分類功能簡(jiǎn)介數(shù)據(jù)庫工作表函數(shù)分析數(shù)據(jù)清單中的數(shù)值是否符合特定條件日期與時(shí)間函數(shù)在公式中分析和處理日期值和時(shí)間值工程函數(shù)用于工作分析信息函數(shù)確定存儲(chǔ)在單元格中數(shù)據(jù)的類型財(cái)務(wù)函數(shù)進(jìn)行一般的財(cái)務(wù)計(jì)算邏輯函數(shù)進(jìn)行邏輯判斷或者進(jìn)行復(fù)合檢驗(yàn)統(tǒng)計(jì)函數(shù)對(duì)數(shù)據(jù)區(qū)域進(jìn)行統(tǒng)計(jì)分析查找和引用函數(shù)在數(shù)據(jù)清單中查找特定數(shù)據(jù)或者查找一個(gè)單元格的引用文本函數(shù)在公式中處理字符串?dāng)?shù)學(xué)和三角函數(shù)進(jìn)行數(shù)學(xué)計(jì)算二、常用函數(shù)Excel2000提供了幾百個(gè)內(nèi)置函數(shù)，下面只介紹常用的函數(shù)，有關(guān)其他函數(shù)的用法，可以使用Excel2000的幫助進(jìn)行學(xué)習(xí)。1．SUM函數(shù)功能：SUM函數(shù)用于計(jì)算單個(gè)或多個(gè)參數(shù)之和。語法：SUM(number1,number2，……)number1,number2，……為1到30個(gè)需要求和的參數(shù)。參數(shù)：邏輯值、數(shù)字、數(shù)字的文本形式、單元格的引用。例10．3SUM(10，20)等于30。SUM(A1：E1)等于從A1到E1共5個(gè)單元格中數(shù)值的和。2．SUMIF函數(shù)功能：SUMIF函數(shù)對(duì)符合指定條件的單元格求和。語法：SUMIF(range,criteria,sum_range)range用于條件判斷的單元格區(qū)域。Criteria確定哪些單元格符合相加的條件。其形式可以是數(shù)字、表達(dá)式或文本。Sum_range是需要求和的實(shí)際單元格區(qū)域，只有當(dāng)range中的相應(yīng)單元格滿足criteria中的條件時(shí)，才對(duì)sum_range中相應(yīng)的單元格求和。若省略sum_range，則對(duì)range中滿足條件的單元格求和。例10.4設(shè)A1：A4中的數(shù)據(jù)是10、20、30、40，而B1：B4中的數(shù)據(jù)是100、200、300、400，那么SUMIF(A1：A4，“＞15”，B1：B4)等于900，因?yàn)锳2、A3、A4中的數(shù)據(jù)滿足條件，所以相應(yīng)地對(duì)B2、B3、B4進(jìn)行求和。3．AVERAGE函數(shù)功能：AVERAGE函數(shù)對(duì)所有參數(shù)計(jì)算算術(shù)平均值。語法：AVERAGE(number1,number2，……)number1,number2，……為需要計(jì)算平均值的1到30個(gè)參數(shù)。參數(shù)應(yīng)該是數(shù)字或包含數(shù)字的單元格引用、數(shù)組或名字。例10.5AVERAGE(1，2，3，4，5)等于3。4.DAY函數(shù)功能：DAY函數(shù)將某一日期的表示方法從日期序列數(shù)形式轉(zhuǎn)換成它所在月份中的序數(shù)(即某月的第幾天)，用整數(shù)1到31表示。語法：DAY(serial_number)serial_number是用于日期和時(shí)間計(jì)算的日期時(shí)間代碼，可以是數(shù)字或文本，如“98／10／20”。例10.6DAY(“98／10/20”)等于20。DAY(“5—OCT”)等于5。5．TODAY函數(shù)和NOW函數(shù)功能：TODAY函數(shù)返回計(jì)算機(jī)內(nèi)部時(shí)鐘的當(dāng)前日期。NOW函數(shù)返回計(jì)算機(jī)內(nèi)部時(shí)鐘的當(dāng)前日期和時(shí)間。語法：TODAY()NOW()這兩個(gè)函數(shù)都不需要輸入?yún)?shù)。6．LEFT和RIGHT函數(shù)功能：LEFT函數(shù)返回字符串最左端的子字符串。RIGHT函數(shù)返回字符串最右端的子字符串。語法：LEFT(text,num_chars)text為包含要提取子字符串的字符串。Num_chars為子字符串的長(zhǎng)度。例10.7LEFT(“MicrosoftExcel2000”，9)等于“Microsoft”。RICHT(“Iamastudent”，7)等于“student”。7．TRUNC函數(shù)功能：將數(shù)字截為整數(shù)或保留指定位數(shù)的小數(shù)。語法：TRUNC(number，num_digits)number為需要截尾取整的數(shù)字。num_digits為指定取整精度的數(shù)字(小數(shù)位數(shù))，默認(rèn)值為0。例10.8TRUNC(8.6)等于8。TRUNC(—8.67，1)等于—8.6。8．INT函數(shù)功能：返回實(shí)數(shù)向下取整后的整數(shù)值。語法：INT(number)number是需要取整的實(shí)數(shù)。例10.9INT(7.6)等于7。INT(—7.6)等于—8。9．LIG和LOG10函數(shù)功能：LOG函數(shù)返回指定底數(shù)的對(duì)數(shù)，LOCl0函數(shù)返回以10為底的常用對(duì)數(shù)。語法：LOG(number,base)LOG10(number)number是需要計(jì)算對(duì)數(shù)的正實(shí)數(shù)。base為對(duì)數(shù)的底數(shù)。LDG函數(shù)默認(rèn)的底數(shù)為10。例10.10LOG(9，2)等于3。L6G10(1000)等于3。10．TYPE函數(shù)功能：返回?cái)?shù)據(jù)的類型。語法：TYPE(value)value為需要返回類型的數(shù)據(jù)，請(qǐng)見表10.6所示。圖10.6TYPE函數(shù)返回值參數(shù)valueTYPE函數(shù)返回值數(shù)字文本邏輯值公式錯(cuò)誤值數(shù)組12481664例10.11YTPE(10)等于1。如果A1單元格包含“EXCEL”，則TYPE（A1）等于2。三、在公式中使用函數(shù)與鍵入公式一樣，在編輯欄中也可以鍵入任何函數(shù)。如果能記住函數(shù)的參數(shù)，直接從鍵盤輸入函數(shù)是最快的方法。但是當(dāng)面對(duì)眾多的函數(shù)，特別是函數(shù)名十分相似的一些函數(shù)以和參數(shù)眾多的函數(shù)時(shí)，使用“函數(shù)選項(xiàng)板”可以使工作會(huì)得很容易?！昂瘮?shù)選項(xiàng)板”可以顯示函數(shù)的名稱、該函數(shù)的每個(gè)參數(shù)、函數(shù)功能和參數(shù)的描述、函數(shù)的當(dāng)前結(jié)果和整個(gè)公式的結(jié)果等。(1)選定要輸入公式的單元格，如果在編輯欄中輸入公式，將插入點(diǎn)移至要插入函數(shù)的位置。(2)單擊“插入”菜單中的“函數(shù)”命令，或單擊“常用”工具欄中的“粘貼函數(shù)”按鈕，打開“粘貼函數(shù)”對(duì)話框，如目10.5所示。圖10.5“粘貼函數(shù)”對(duì)話框(3)在“函數(shù)分類”列表中選擇函數(shù)類型，在“函數(shù)名”列表中選擇具體函數(shù)，例如“SUM”函數(shù)。（4）單擊“確定”按鈕，在編輯欄下面彈出函數(shù)選項(xiàng)板，如圖10.6所示。圖10.6函數(shù)選項(xiàng)板(5)在參數(shù)框中輸入相應(yīng)參數(shù)，按Tab鍵或“Shift＋Tab”組合健在參數(shù)框間切換。(6)按回車鍵或單擊“確定”按鈕，完成函數(shù)的輸入。在創(chuàng)建公式的過程中，輸入等號(hào)后，“名稱框”切換為“函數(shù)框”，在“函數(shù)框”中顯示最近一次用到的工作表函數(shù)。單擊“函數(shù)框”右邊的箭頭，可以查看其它可用的工作表函數(shù)。如果沒有所需的函數(shù)，可單擊列表底部的“其它函數(shù)”選項(xiàng)，打開“粘貼函數(shù)”對(duì)話框，其中包含所有可用的工作表函數(shù)。四、編輯函數(shù)在編輯公式中所含函數(shù)時(shí)，一些比較小的修改，可以手工編輯，但是如果要對(duì)函數(shù)進(jìn)行比較大的改動(dòng)，還應(yīng)該使用“函數(shù)選項(xiàng)板”。(1)單擊要編輯函數(shù)的單元格。(2)單擊“插入”菜單中的“函數(shù)”命令；或單擊“編輯欄”左側(cè)的“編輯公式”按鈕，以顯示“函數(shù)選項(xiàng)板”。(3)“函數(shù)選項(xiàng)板”將顯示公式中的第一個(gè)函數(shù)和它的所有參數(shù)。(4)在函數(shù)的編輯欄中單擊任意位置，可編輯第一個(gè)函數(shù)或同一公式中的其他函數(shù)。第四節(jié)使用數(shù)組數(shù)組是一種計(jì)算工具，可用來建立對(duì)兩組或更多組值進(jìn)行操作的公式，這些值稱為數(shù)組參數(shù)，數(shù)組公式返回的結(jié)果既可以是單個(gè)也可以是多個(gè)。數(shù)組區(qū)域是共享同一數(shù)組公式的單元格區(qū)域。數(shù)組公式是小空間內(nèi)進(jìn)行大量計(jì)算的強(qiáng)有力方法，它可以替代很多重復(fù)的公式。一、數(shù)組公式的創(chuàng)建和輸入（1）如果希望數(shù)組公式返回一個(gè)結(jié)果，單擊需要輸入數(shù)組公式的單元格；如果希望數(shù)組公式返回多個(gè)結(jié)果，選定需要輸入數(shù)組公式的單元格區(qū)域。(2)鍵入公式的內(nèi)容。(3)按“Ctrl十Shift十Enter”組合鍵，鎖定數(shù)組公式，Excel自動(dòng)在編輯欄中公式的兩邊加上大括號(hào)，表明它是一個(gè)數(shù)組公式。注意：不要自己鍵入大括號(hào)，否則Excel會(huì)認(rèn)為輸入的是一個(gè)正文標(biāo)簽。例10.12計(jì)算期末考試個(gè)人成績(jī)總和。(1)選定區(qū)域H4到H11，如圖10．7所示。(2)輸入公式“＝B4:B11十C4：C11十D4：D11十E4：E11十F4：F11十G4：G11”。(3)按“Ctrl十Shift十Enter”組合鍵結(jié)束輸入，計(jì)算結(jié)果如圖10．8所示。輸人數(shù)組函數(shù)的方法與輸人數(shù)組公式的方法相同。二、使用數(shù)組常量在數(shù)組公式中，通常使用單元格區(qū)域引用，