Excel數(shù)據(jù)處理與分析實(shí)戰(zhàn)精粹

Excel數(shù)據(jù)處理與分析實(shí)戰(zhàn)技巧精粹1、使用工作表保護(hù)：1）選定全部允許訪問單元格，單擊菜單［格式］—［單元格］；2）在打開“單元格格式”對話框“保護(hù)”選項卡中取消勾選“鎖定”復(fù)選框，然后單擊“確定”按鈕；3）單擊菜單［工具］—［保護(hù)］—［保護(hù)工作表］，在打開“保護(hù)工作表”對話框中取消勾選“選定鎖定單元格”復(fù)選框；4）假如需要，能夠加上工作表保護(hù)密碼并確認(rèn)密碼，最終單擊“確定”按鈕即可；5）此時在工作表中只有未鎖定單元格能夠被選中和編輯，而其余單元格都會受到Excel保護(hù)，無法被選中更無法進(jìn)行編輯。

注意：也能夠把你需要隱藏文字設(shè)置成白色，然后再進(jìn)行以上工作表保護(hù)，那他人既看不到你內(nèi)容也沒方法修改了。

2、標(biāo)題行重復(fù)打?。?）單擊菜單［文件］—［頁面設(shè)置］，在打開“頁面設(shè)置”對話框中單擊“工作表”選項卡；2）假如要指定在頂部重復(fù)一行或連續(xù)幾行，則可單擊“頂端標(biāo)題行”文本框最右邊按鈕，然后在工作表中進(jìn)行對應(yīng)指定；3）假如要指定在左側(cè)重復(fù)一行或連續(xù)幾行，則可單擊“左端標(biāo)題列”文本框最右邊按鈕，然后在工作表中進(jìn)行對應(yīng)指定，以下列圖所表示；4）單擊“確定”按鈕關(guān)閉“頁面設(shè)置”對話框。

3、巧用右鍵和雙擊填充：假如用戶先輸入內(nèi)容到一個單元格，然后用鼠標(biāo)右鍵拖曳此單元格，那么松開右鍵，就會出現(xiàn)一個快捷菜單，菜單上顯示了此次填充能夠選取類型，甚至能夠鏈接到“序列”對話框進(jìn)行更復(fù)雜設(shè)置，以下列圖所表示：

當(dāng)用戶在數(shù)據(jù)區(qū)域中對某一列內(nèi)容進(jìn)行填充時，無須從第一個單元格一直拖曳到最終一個單元格，只需要雙擊第一個單元格填充柄即可。不過使用雙擊方法進(jìn)行填充時候，填充到最終一個單元格位置則取決于左邊一列中第一個空白單元格位置（假如填充列是第一列，則參考右邊列中單元格）。比如在下列圖中因?yàn)锳5是空白單元格，所以B列中填充只進(jìn)行到B4單元格就會停頓。

4、快速輸入對號、錯號、平方與立方：下面數(shù)字均必須在小鍵盤上輸入。名稱快捷鍵名稱快捷鍵名稱快捷鍵名稱快捷鍵對號ALT+41420錯號ALT+41409平方ALT+178立方ALT+1795、神奇選擇性粘貼：1）全部：在絕大多數(shù)情況下等效于常規(guī)粘貼；2）公式：只復(fù)制原始區(qū)域公式；3）數(shù)值：只復(fù)制數(shù)值，假如原始區(qū)域是公式，則只復(fù)制公式計算結(jié)果；4）格式：只復(fù)制原始區(qū)域格式；5）批注：只復(fù)制原始區(qū)域批注；6）有效性驗(yàn)證：只復(fù)制原始區(qū)域中設(shè)置數(shù)字有效性；7）邊框除外：復(fù)制邊框之外全部內(nèi)容；8）列寬：從一列到另一列復(fù)制列寬信息；9）跳過空單元格：能夠有效地預(yù)防原始區(qū)域中空單元格覆蓋粘貼目標(biāo)區(qū)域中單元格內(nèi)容；10）轉(zhuǎn)置：能夠讓原始區(qū)域在復(fù)制后行列交換；11）粘貼鏈接：將建立一個由公式組成連接原始區(qū)域動態(tài)鏈接；12）加：允許用戶進(jìn)行一次簡單數(shù)值運(yùn)算。比如：選定任意一個空白單元格，然后按<Ctrl+C>組合鍵進(jìn)行復(fù)制，按住<Ctrl>鍵單擊全部需要取消超鏈接單元格。假如是一個單元格區(qū)域，能夠先用鼠標(biāo)單擊并按住最外面單元格，然后向上拖動選定整個區(qū)域，待光標(biāo)由手形狀轉(zhuǎn)變?yōu)槭中稳缓笤偎砷_鼠標(biāo)按鍵，單擊菜單［編輯］—［選擇性粘貼］，在打開“選擇性粘貼”對話框中選中“加”單項選擇按鈕，然后單擊“確定”按鈕，這么全部被選定超鏈接就都轉(zhuǎn)換為普通文本了，以下列圖所表示。

6、創(chuàng)建動態(tài)名稱：假如需要創(chuàng)建一個名稱來引用C列中數(shù)據(jù)，但又不希望這個引用區(qū)域包含空白單元格，在這種情況下能夠創(chuàng)建動態(tài)名稱，依照用戶追加或刪除數(shù)據(jù)結(jié)果來自動地調(diào)整引用位置，以達(dá)成一直引用非空白單元格區(qū)域結(jié)果。創(chuàng)建動態(tài)名稱方法以下：1）單擊菜單［插入］—［名稱］—［定義］；2）打開“定義名稱”對話框，在“在當(dāng)前工作薄中名稱”文本框中輸入“Date”，在“引用位置”文本框中輸入公式：=OFFSET(Sheet1!$C$4,,,COUNTA(Sheet1!$C:$C)-1)，以下列圖所表示，單擊“確定”按鈕。

以上公式先計算C列中除了列標(biāo)題以外非空白單元格數(shù)量，然后以C4單元格（首個數(shù)據(jù)單元格）為基準(zhǔn)開始向下定位，定位行數(shù)等于剛才計算出來數(shù)據(jù)。下面能夠在C列以外單元格中經(jīng)過計算來驗(yàn)證此名稱引用是否正確，比如在B1中輸入公式：=SUM(Date)，以下列圖所表示。

假如繼續(xù)追加統(tǒng)計，名稱“Date”引用位置就會自動地發(fā)生改變，B2中計算結(jié)果能夠表現(xiàn)這一點(diǎn)，以下列圖所表示。

注意：以上公式只能正確計算不間斷連續(xù)數(shù)據(jù)，假如表格中數(shù)據(jù)有空白單元格，那么動態(tài)名稱引用位置將發(fā)生錯誤。

7、單元格中切換中英文輸入法：比如選定A列，單擊菜單［數(shù)據(jù)］—［有效性］打開“數(shù)據(jù)有效性”對話框，選擇“輸入法模式”選項卡，在“模式”下拉列表中選擇“打開”選項，然后單擊“確定”按鈕即可，用一樣方法能夠把B列“輸入法模式”設(shè)置為“關(guān)閉（英文模式）”，假如用戶打開了某一個漢字輸入法，那么選定A列單元格時將激活該輸入法，選定B列單元格時將屏蔽該輸入法，而顯示“英語（美國）”圖標(biāo)，當(dāng)用戶需要限制某個單元格區(qū)域不能輸入漢字時就能夠使用此方法，以免重復(fù)地切換中英文輸入法而帶來麻煩。

8、限制輸入重復(fù)數(shù)據(jù)：選定A列，單擊菜單［數(shù)據(jù)］—［有效性］打開“數(shù)據(jù)有效性”對話框；2）選擇“設(shè)置”選項卡，在“允許”下拉列表中選擇“自定義”選項，在“公式”文本框中輸入“=COUNTIF(A:A,A1)=1”，單擊“確定”按鈕。

9、禁止重復(fù)報餐：某企業(yè)為方便員工中午用餐，在3家餐館為員工定餐，員工能夠依照個人喜好選擇其中一家餐館報餐。負(fù)責(zé)報餐工作人員制作了一個Excel文件，以下列圖所表示：

讓每位員工自己在工作表上報餐，方法為：在自己名字與日期交叉單元格輸入數(shù)字1，工作人員只需對對應(yīng)單元格區(qū)域求和就能夠算出某家餐館共有幾位員工報餐，方便與餐館結(jié)賬，為了預(yù)防同一員工同一天在多家餐館重復(fù)報餐，能夠設(shè)置數(shù)據(jù)有效性來禁止，方法以下：1）選定B2單元格，單擊菜單［插入］—［名稱］—［定義］打開“定義名稱”對話框；2）在“在當(dāng)前工作簿中名稱”文本框中輸入名稱“Count”，在“引用位置”文本框中輸入“=”，鼠標(biāo)單擊第1張工作表（鑫德海）標(biāo)簽，按住<Shift>鍵不放用鼠標(biāo)再單擊最終一張工作表（一品套餐）標(biāo)簽，放開<Shift>鍵，輸入“B2”，然后單擊“確定”按鈕，以下列圖所表示：

3）選定“鑫海德”工作表單元格區(qū)域B2：F26，單擊菜單［數(shù)據(jù)］—［有效性］打開“數(shù)據(jù)有效性”對話框；4）選擇“設(shè)置”選項卡，在“允許”下拉列表中選擇“自定義”選項，在“公式”文本框中輸入“=SUM(COUNT)=1”，并取消“忽略空值”復(fù)選框勾選狀態(tài)；5）切換到“犯錯警告”選項卡，在“樣式”下拉列表中選擇“停頓”選項，在“標(biāo)題”文本框中輸入“注意！”，在“錯誤信息”文本框中輸入“請不要重復(fù)報餐！”，然后單擊“確定”按鈕；6）重復(fù)步驟3～步驟5，為另外兩個工作表設(shè)置相同數(shù)據(jù)有效性。

經(jīng)過設(shè)置以上數(shù)據(jù)有效性后，同一員工在同一天就不能在多家餐館重復(fù)報餐了。如A12單元格“西門雪”在“鑫德?！眻蟛秃螅偃缫凇暗摱τ洝痹賵笠淮尾?，Excel就會彈出“注意！”對話框，阻止用戶繼續(xù)報餐。

公式解析：名稱Count是一個三維引用名稱，返回3個工作表同一位置單元格引用，B2單元格有效性公式“=SUM(COUNT)”即表示“=鑫德海！B2+祿鼎記！B2+一品套餐！B2”。

10、創(chuàng)建二級下拉菜單_源為單個工作表：下列圖所表示是“查詢”表B1單元格創(chuàng)建下拉菜單，可供選擇選項為“煙”和“酒”，C1單元格能依照B1單元格所選擇不一樣內(nèi)容產(chǎn)生不一樣序列內(nèi)容下拉菜單，而且下拉菜單中不能包含空格，要創(chuàng)建這么下拉菜單，方法以下：

1）使用直接在有效性中輸入序列內(nèi)容創(chuàng)建下拉菜單方法，為“查詢”表B1單元格創(chuàng)建序列為“煙”和“酒”下拉菜單，以下列圖所表示：

2）單擊菜單［插入］—［名稱］—［定義］彈出“定義名稱”對話框，在“當(dāng)前工作薄中名稱”文本框中輸入“PingMing”，在“引用位置”文本框中輸入公式：=OFFSET(數(shù)據(jù)!$A$2,,MATCH(查詢!$B2,數(shù)據(jù)!$1:$1,)-1,COUNTA(OFFSET(數(shù)據(jù)!$A$2,,MATCH(查詢!$B2,數(shù)據(jù)!$1:$1,)-1,65535)))，以下列圖12所表示，然后單擊“確定”按鈕。

3）選定“查詢”表中C1單元格，單擊菜單［數(shù)據(jù)］—［有效性］打開“數(shù)據(jù)有效性”對話框；

4）切換到“設(shè)置”選項卡，在“允許”下拉列表中選擇“序列”選項，在“起源”文本框中輸入“=PinMing”，然后單擊“確定”按鈕。

5）在“查詢”表D1單元格中輸入公式：“=SUMIF(數(shù)據(jù)!A:C,C2,數(shù)據(jù)!B:D)”。

11、導(dǎo)入Word文檔中表格：Word文檔中表格不能直接導(dǎo)入Excel工作表中，不過用戶能夠采?。蹚?fù)制］—［粘貼］方法將Word文檔中表格復(fù)制到Excel工作表中。但假如文檔中表格較多時，復(fù)制起來就會很不方便。這里介紹一下經(jīng)過網(wǎng)頁文件快速導(dǎo)入Word文檔中數(shù)據(jù)表格方法。1）打開Word文檔“示例5”，在Word工作窗口中單擊菜單［文件］—［另存為］對話框，在“保留類型”下拉列表中選擇“單個文件網(wǎng)頁”，然后單擊“保留”按鈕將該文檔另存為網(wǎng)頁文件；2）單擊菜單［數(shù)據(jù)］—［導(dǎo)入外部數(shù)據(jù)］—［新建Web查詢］打開“新建Web查詢”對話框；3）在“新建Web查詢”對話框“地址”下拉列表文本框中輸入剛才保留文件完整路徑，如file:///H:/fscommand/project/第2篇%20數(shù)據(jù)輸入和導(dǎo)入/Chapter7%20導(dǎo)入外部數(shù)據(jù)/示例5.mht，然后單擊“轉(zhuǎn)到”按鈕打開網(wǎng)頁文件；4）在“新建Web查詢”對話框中分別單擊兩個表格左上角“”標(biāo)識將其選中，標(biāo)識同時分別會變?yōu)椤啊?，然后單擊“?dǎo)入”按鈕打開“導(dǎo)入數(shù)據(jù)”對話框；5）在“數(shù)據(jù)放置位置”組合框中選中“現(xiàn)有工作表”單項選擇按鈕，并在文本框中輸入數(shù)據(jù)導(dǎo)入起始單元格位置“=$A$1”；6）單擊“確定”按鈕即可導(dǎo)入數(shù)據(jù)，完成Word文檔中表格導(dǎo)入工作。

12、快速插入多個單元格：更加快捷方法是：先選定目標(biāo)單元格區(qū)域，然后按住<Shift>鍵，把光標(biāo)移動到選定區(qū)域右下角，當(dāng)光標(biāo)變成份隔箭頭時，以下列圖所表示，再往右或者往下拖動，拖動距離就等于插入單元格數(shù)量，拖動方向等于“活動單元格”移動方向。

13、快速改變行列次序：把光標(biāo)移動到C列右側(cè)黑色邊框上，按住<Shift>鍵開始往左拖動，這時我們能夠看到光標(biāo)左側(cè)出現(xiàn)了一條工字形虛框，以下列圖所表示，把這條虛線拖動到A列與B列之間單元格邊框上，然后松開鼠標(biāo)左鍵，列次序改變即完成。

14、快速縮放數(shù)值：許多用戶在工作中經(jīng)常需要處理很大數(shù)字，而利用下面自定義數(shù)字格式就能夠在不改變數(shù)值本身同時對它們進(jìn)行縮放，以下列圖所表示。

15、單元格文本數(shù)據(jù)分行：在下列圖中，A1單元格是由一個由多個成語連接而成字符串，各個字符之間沒有間隔，現(xiàn)需要將這一字符串中各個成語分成多行并排顯示，方法以下：

1）將A列列寬調(diào)整到顯示4個漢字寬度；2）選中A1單元格，單擊菜單[編輯]—[填充]—[內(nèi)容重排]彈出“文本將超出選定區(qū)域”警告窗口；3）單擊警告窗口中“確定”按鈕即可得到分行結(jié)果。

注意：使用這種方法也能夠?qū)⒍嘈袛?shù)據(jù)合并成一行。

16、多行多列數(shù)據(jù)轉(zhuǎn)為單列數(shù)據(jù)：

1）在下列圖中選中A1：C3單元格區(qū)域，連續(xù)按兩次<Ctrl+C>組合鍵打開“剪貼板”任務(wù)窗格；

2）雙擊E1單元格激活編輯狀態(tài)，然后單擊“剪貼板”中剛才復(fù)制內(nèi)容，這些內(nèi)容將粘貼到E1單元格并顯示在編輯欄中；

3）在編輯欄中全部選取之前粘貼過來數(shù)據(jù)，然后單擊“慣用”工具欄中“復(fù)制”按鈕；4）按<ESC>鍵退出單元格編輯狀態(tài)，然后選中E1單元格，單擊“慣用”工具欄中“粘貼”按鈕；

5）將E列列寬調(diào)整為顯示單個字符寬度，再單擊菜單[編輯]—[填充]—[內(nèi)容重排]，將會彈出“文本將超出選定區(qū)域”警告窗口，然后單擊“確定”按鈕繼續(xù)操作；

6）數(shù)據(jù)內(nèi)容重排后，轉(zhuǎn)換后單列數(shù)據(jù)已經(jīng)具備雛形，但單元格顯示仍不完全，此時選中E列，重新將列寬寬度調(diào)大，以使其能夠完全地顯示數(shù)據(jù)內(nèi)容，最終結(jié)果以下列圖所表示。

17、排序字母與數(shù)字混合內(nèi)容：1）在B1單元格中輸入以下公式：=LEFT(A1,1)&RIGHT("000"&RIGHT(A1,LEN(A1)-1),3)；2）把B1單元格中公式向下復(fù)制到B10單元格；3）選中B2單元格，然后單擊“慣用”工具欄中“升序排序”按鈕。

注意：下列圖中，A列是原始數(shù)據(jù)，B列是套公式后得到數(shù)據(jù)，C列選擇性粘貼B列數(shù)值后排序數(shù)據(jù)。

18、快速刪除空行：1）在下列圖C1單元格輸入公式“=A1=""”，然后復(fù)制公式向下填充到C20單元格；2）選中C1單元格，單擊“慣用”工具欄中“升序排序”按鈕出現(xiàn)“排序警告”對話框，選中“擴(kuò)展選定區(qū)域”單項選擇按鈕后單擊“確定”按鈕即可得到排序結(jié)果；3）去除C列內(nèi)容，最終得到刪除空行、緊縮數(shù)據(jù)行效果。

19、快速制作工資條：1）在下列圖J2：J11單元格區(qū)域依次填入數(shù)字1至10，再將一樣次序10個數(shù)字復(fù)制到J11：J21單元格區(qū)域；2）選中J2單元格，單擊“慣用”工具欄中“升序排序”按鈕對數(shù)據(jù)區(qū)域進(jìn)行升序排序；3）選中A3：J120單元格區(qū)域，按<Ctrl+G>組合鍵打開“定位”對話框，選中“空值”單項選擇按鈕，然后單擊“確定”按鈕即可選中當(dāng)前區(qū)域中空單元格；4）輸入公式“=A1”，按<Ctrl+Enter>組合鍵確認(rèn)公式輸入。

20、一次性刪除全部重復(fù)數(shù)據(jù)：1）在下列圖中B2單元格輸入公式：=IF(COUNTIF(A$2:A2,A2)>1,1)，復(fù)制公式向下填充到B10單元格；選中B2單元格，單擊“慣用”工具欄中“降序排序”按鈕；B列顯示為1所對應(yīng)A列編號重復(fù)1次以上編號，去除這部分?jǐn)?shù)據(jù)即可得到不包含重復(fù)項數(shù)據(jù)表。

21、了解高級篩選：在下列圖中表格是一張包含了篩選條件區(qū)域數(shù)據(jù)列表，假如要將“人員類別”字段中為“經(jīng)理人員”統(tǒng)計經(jīng)過高級篩選功效篩選出來，詳細(xì)步驟以下：

1）單擊菜單[數(shù)據(jù)]—[篩選]—[高級篩選]彈出“高級篩選”對話框；

2）在“方式”組合框中選中“將篩選結(jié)果復(fù)制到其余位置”單項選擇按鈕；

3）然后將光標(biāo)定位在“列表區(qū)域”文本框中，在表格內(nèi)選取A4：F14單元格區(qū)域，此區(qū)域地址會自動地填寫在“列表區(qū)域”文本框中，此區(qū)域即為當(dāng)前數(shù)據(jù)列表所在單元格區(qū)域；

4）與步驟3類似，以下列圖所表示，在“條件區(qū)域”文本框中選取B1：B2單元格區(qū)域，在“復(fù)制到”區(qū)域中選擇A18：F18，最終單擊“確定”按鈕得到篩選結(jié)果；

5）以上面一樣方式能夠繼續(xù)篩選出數(shù)量大于900結(jié)果。

22、使用“視圖管理器”簡化重復(fù)篩選操作：在下列圖數(shù)據(jù)列表中，假如希望依照不一樣工廠名和季節(jié)條件篩選出相關(guān)統(tǒng)計，而且篩選條件在不停地發(fā)生改變，那么利用“視圖管理器”功效能夠大大地簡化高級篩選設(shè)置工作。

1）設(shè)置數(shù)據(jù)列表篩選區(qū)域，經(jīng)過數(shù)據(jù)有效性將“工廠名”設(shè)置為可選擇下拉菜單；

2）在條件區(qū)域選擇條件。使用高級篩選，并選擇在原數(shù)據(jù)區(qū)域顯示篩選結(jié)果，以下列圖書所表示；

3）單擊菜單[視圖]—[視圖管理器]彈出“視圖管理器”對話框；

4）單擊“添加”按鈕彈出“添加視圖”對話框，定義“名稱”為“1”，然后單擊“確定”按鈕關(guān)閉對話框，此時當(dāng)前窗口設(shè)置即被定義為視圖名稱1；

當(dāng)用戶改變篩選條件后，如選擇“工廠名”為“2工廠”，使用“視圖管理器”能夠快速地得到篩選結(jié)果，而無須重新設(shè)置高級篩選，方法以下：

1）單擊菜單[數(shù)據(jù)]—[篩選]—[全部顯示]，取消上次高級篩選顯示結(jié)果，顯示整個數(shù)據(jù)表；

2）單擊菜單[視圖]—[視圖管理器]彈出“視圖管理器”對話框；

3）選中定義視圖名稱1，然后點(diǎn)擊“顯示”按鈕，這么在關(guān)閉“視圖管理器”對話框同時也立刻顯示出了新篩選條件下篩選結(jié)果。

依此方法，假如需要數(shù)次修改篩選條件，只要每次在改變條件后先恢復(fù)全部數(shù)據(jù)顯示，然后調(diào)用“視圖管理器”顯示之前所保留定義視圖，就能夠立刻顯示更改篩選條件后結(jié)果，這比起每一次設(shè)置“高級篩選”對話框要方便不少。

23、利用高級篩選拆分?jǐn)?shù)據(jù)列表：下列圖中表是一張包含“表2”數(shù)據(jù)列表，“表2”是“表1”子集（每個編碼都只有一條統(tǒng)計），現(xiàn)在要將“表1”中不包含于“表2”中數(shù)據(jù)拆分出來，此時能夠借助高級篩選功效實(shí)現(xiàn)，方法以下：

1）在A10單元格輸入“篩選條件”，然后在A11單元格輸入篩選條件公式：=ISNA(MATCH(A3,$F$3:$F$5,0))；

2）選中A2：D7單元格區(qū)域，單擊菜單[數(shù)據(jù)]—[篩選]—[高級篩選]彈出“高級篩選”對話框，然后按下列圖所表示進(jìn)行設(shè)置；

3）單擊“確定”按鈕關(guān)閉“高級篩選”對話框即可得到篩選結(jié)果，實(shí)現(xiàn)數(shù)據(jù)列表拆分目標(biāo)，以下列圖所表示。

公式思緒解析：=ISNA(MATCH(A3,$F$3:$F$5,0))，該公式經(jīng)過MATCH函數(shù)，在“表2”編碼字段中查找“表1”中編碼，假如“表1”編碼包含在“表2”當(dāng)中，則返回數(shù)值，假如沒有找到則返回錯誤值。然后利用ISNA函數(shù)返回MATCH函數(shù)計算結(jié)果中錯誤值，表示此編碼不包含在“表2”之中。

24、標(biāo)識中標(biāo)企業(yè)和中標(biāo)金額：下列圖顯示是10家企業(yè)對5個工程項目標(biāo)投標(biāo)情況表，為了更醒目地顯示每個項目標(biāo)中標(biāo)企業(yè)（此處假定金額最大者判為中標(biāo)）及其中標(biāo)金額，能夠使用條件格式進(jìn)行標(biāo)識，方法以下：

1）選定單元格區(qū)域B3：F12，單擊菜單[格式]—[條件格式]彈出“條件格式”對話框；

2）在“條件1（1）”下方下拉列表中選擇“公式”，在右側(cè)文本框中輸入“=B3=MAX(B$3:B$12”；

3）單擊“格式”按鈕，在彈出“單元格格式”對話框中選擇“字體”選項卡，在“字形”列表框中選擇“加粗”，在“顏色”下拉列表中選擇“白色”，然后選擇“圖案”選項卡，設(shè)置“單元格底紋”顏色為“黑色”；

4）單擊“確定”按鈕關(guān)閉“單元格格式”對話框，然后單擊“條件格式”對話框中“確定”按鈕關(guān)閉“條件格式”對話框。

若要對中標(biāo)企業(yè)也深入標(biāo)識，能夠在A3：A12設(shè)置條件，方法和B3：F12條件格式設(shè)置相同，公式為：

=OR(B3:F3=SUBTOTAL(4,OFFSET($B$3:$B$12,,COLUMN($B:$F)-2)))；

公式中使用了對投標(biāo)金額區(qū)域三維引用：OFFSET($B$3:$B$12,,COLUMN($B:$F)-2)，引用了每個項目投標(biāo)金額單元格區(qū)域：B3：B12、C3：C12、D3：D12、E3：E12、F3：F12，再用分類匯總函數(shù)SUBTOTAL對每個區(qū)域求最大值（即各項目標(biāo)中標(biāo)金額），結(jié)果為{1280000,1290000,1270000,1260000,1300000}，最終用OR函數(shù)判斷各個企業(yè)在每個項目中是否有某個投標(biāo)金額等于中標(biāo)金額，假如有則應(yīng)用所設(shè)置格式。

25、制作國際象棋棋盤底紋：設(shè)置下列圖所表示是國際象棋棋盤式底紋方法以下：

1）選擇單元格區(qū)域A1：A19，單擊菜單[格式]—[條件格式]；

2）在彈出“條件格式”對話框中，在“條件1（1）”下方下拉列表中選擇“公式”，在右側(cè)文本框中輸入公式“=MOD(ROW()+COLUMN(),2)<>0”，此公式用于判斷行號與列號之和除以2余數(shù)是否為0，假如為0，說明行數(shù)與列數(shù)奇偶性相同，不填充單元格底紋顏色，其余則填充單元格為淺綠色；在條件格式中，當(dāng)公式結(jié)果返回一個數(shù)字時，非0數(shù)字即判斷為條件成立，0和錯誤值則判斷為條件不成立，所以上面公式也能夠簡寫為：=MOD(ROW()+COLUMN(),2)；

3）單擊“格式”按鈕，設(shè)置對應(yīng)顏色；

4）在“條件格式”對話框中單擊“添加”按鈕，在“條件2（2）”下方下拉列表中選擇“公式”，在右側(cè)文本框中輸入公式“=MOD(ROW()+COLUMN(),2)=0”；

5）單擊“格式”按鈕，設(shè)置對應(yīng)顏色。

26、標(biāo)識重復(fù)值：下列圖是某電腦城一些電腦設(shè)備型號表，現(xiàn)要求把設(shè)備型號重復(fù)統(tǒng)計全部用玫瑰紅底紋標(biāo)識出來，方法以下：

1）出現(xiàn)重復(fù)就標(biāo)識：輸入公式=COUNTIF($C$2:$C$27,$C2)>1；

2）第2次重復(fù)以后才標(biāo)識：輸入公式=COUNTIF($C$2:$C2,$C2)>1；

3）首次與其余重復(fù)次數(shù)標(biāo)識不一樣顏色：要求有重復(fù)不過第1次出現(xiàn)統(tǒng)計用黃色底紋標(biāo)識，條件1輸入公式=COUNTIF($C$2:$C2,$C2)>，設(shè)置玫瑰紅；條件2輸入公式=COUNTIF($C$2:$C$27,$C2)>1，設(shè)置黃色。

27、多匯總方式分類匯總：這里以下列圖中數(shù)據(jù)列表為例，假如希望先按“部門”字段對“繳費(fèi)基數(shù)”進(jìn)行匯總，再按“部門”字段求出“年紀(jì)”平均值，最終按“部門”字段求出“工齡”最大值，方法以下：

1）選擇數(shù)據(jù)列表中任意一個單元格，如A2單元格，單擊菜單[數(shù)據(jù)]—[排序]打開“排序”對話框，在“主要關(guān)鍵字”下拉列表中選擇“部門”字段，右側(cè)排序次序選擇“升序”選項，然后單擊“確定”按鈕完成份類字段排序；

2）單擊菜單[數(shù)據(jù)]—[分類匯總]打開“分類匯總”對話框，以下列圖所表示，在“分類字段”下拉列表中選擇“部門”，在“匯總方式”下拉列表中選擇“求和”，在“選定匯總項”列表框中勾選“繳費(fèi)基數(shù)”復(fù)選框，并勾選“匯總結(jié)果顯示在數(shù)據(jù)下方”復(fù)選框，然后單擊“確定”按鈕關(guān)閉“分類匯總”對話框；

3）其它設(shè)置同時驟2，唯一不一樣地方是取消勾選“替換當(dāng)前分類匯總”復(fù)選框，最終結(jié)果以下列圖所表示（該圖只截取了上半小部分）：

28、使用合并計算建立分戶報表：合并計算能夠按類別進(jìn)行合并，假如引用區(qū)域行列方向均包含了多個類別，則可利用合并計算功效將引用區(qū)域中全部類別匯總到同一表格上并顯示全部明細(xì)。比如8月份南京、上海、海口、珠海4個城市銷售額數(shù)據(jù)分別在4個不一樣工作表中，南京、上海銷售表結(jié)構(gòu)和數(shù)據(jù)分別以下列圖所表示：

1）選中“匯總”工作表A3單元格作為結(jié)果表起始單元格，單擊菜單[數(shù)據(jù)]—[合并計算]打開“合并計算”對話框；

2）在“函數(shù)”下拉列表中選擇“求和”，在“引用位置”文本框中分別添加“南京”、“上?！薄ⅰ昂？凇?、“珠?！钡?個工作表中數(shù)據(jù)區(qū)域，并在“標(biāo)簽位置”組合框中勾選“首行”和“最左列”復(fù)選框，然后單擊“確定”按鈕即可得到各個城市銷售額匯總明細(xì)，以下列圖所表示。

29、利用合并計算進(jìn)行數(shù)值型數(shù)據(jù)查對：利用合并計算“按類別”合并功效，用戶能夠?qū)┈崝?shù)據(jù)查對工作變得輕松，以下列圖所表示是新舊兩組數(shù)據(jù)，現(xiàn)要將這兩組數(shù)據(jù)差異找出來，方法以下：

1）更改新舊數(shù)據(jù)表中“代號”字段列標(biāo)題，使兩個數(shù)據(jù)表第二段標(biāo)題不相同。比如將舊數(shù)據(jù)表中列標(biāo)題“代號”更名為“舊代號”，將新數(shù)據(jù)表列標(biāo)題“代號”更名為“新代號”；

2）選中A17單元格作為結(jié)果存放起始位置，在菜單欄上單擊[數(shù)據(jù)]—[合并計算]打開“合并計算”對話框；

3）在“引用位置”文本框中依次選取添加舊數(shù)據(jù)表A2：B11區(qū)域和新數(shù)據(jù)表D2：E13區(qū)域，在“函數(shù)”下拉列表中選擇“求和”，在“標(biāo)簽位置”組合框中同時勾選“首行”和“最左列”復(fù)選框，然后單擊“確定”按鈕，結(jié)果以下列圖所表示；

4）為了深入顯示出新舊數(shù)據(jù)不一樣之處，能夠在D18單元格中輸入以下公式：=N(B18<>C18)，并復(fù)制公式向下填充至D28單元格；

5）補(bǔ)齊標(biāo)題名稱，選中A17：D28區(qū)域，在菜單欄上單擊[數(shù)據(jù)]—[篩選]—[自動篩選]；

6）單擊D17單元格中自動篩選標(biāo)識，然后選擇篩選值為“1”統(tǒng)計即可得到新舊數(shù)據(jù)差異查對結(jié)果。

30、利用數(shù)據(jù)透視表統(tǒng)計考試分?jǐn)?shù)：利用數(shù)據(jù)透視表“字段設(shè)置”中不一樣匯總方式，用戶還能夠?qū)ψ侄沃袛?shù)值型數(shù)據(jù)進(jìn)行匯總統(tǒng)計，比如求最大值、最小值、平均值等。比如要對下列圖所表示“各班成績”表進(jìn)行統(tǒng)計，求出各班級分?jǐn)?shù)最大值、最小值、平均值，方法以下：

1）選中數(shù)據(jù)源“各班成績”工作內(nèi)任意一個數(shù)據(jù)區(qū)域，單擊[數(shù)據(jù)]—[數(shù)據(jù)透視表和數(shù)據(jù)透視圖]；

2）在彈出“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟1”對話框中單擊“完成”按鈕生成一個空白數(shù)據(jù)透視表，顯示“數(shù)據(jù)透視表字段列表”窗口；

3）將“數(shù)據(jù)透視表字段列表”窗口中“班級”字段拖曳至數(shù)據(jù)透視表“列字段”位置，以下列圖所表示：

4）將“數(shù)據(jù)透視表字段列表”窗口中“成績”字段拖曳至數(shù)據(jù)透視表“數(shù)據(jù)項區(qū)域”，再重復(fù)兩次操作；

5）選中A5單元格（求和項：成績），單擊右鍵，在彈出快捷菜單中選擇“字段設(shè)置”命令，在彈出“數(shù)據(jù)透視表字段”對話框中“匯總方式”列表框中選擇“最大值”選項，然后單擊“確定”按鈕；

6）依據(jù)步驟5完成最小值和平均值計算，最終止果以下列圖所表示。

31、在數(shù)據(jù)透視表中添加計算項：數(shù)據(jù)透視表創(chuàng)建完成后，用戶無法更改或者移動數(shù)據(jù)透視表中任何區(qū)域，也不能在數(shù)據(jù)透視表中插入單元格或是添加公式進(jìn)行自定義計算。假如需要在數(shù)據(jù)透視表中創(chuàng)建自定義計算項目，必須使用“添加計算字段”或“添加計算項”功效。下列圖展示了一張已經(jīng)創(chuàng)建成功數(shù)據(jù)透視表，下面經(jīng)過添加計算項來達(dá)成對比“國內(nèi)”與“進(jìn)口”產(chǎn)品各年差異目標(biāo)。

1）選中數(shù)據(jù)透視表中“類別”按鈕標(biāo)題，然后單擊“數(shù)據(jù)透視表”工具欄中[“數(shù)據(jù)透視表]—[公式]—[計算項]，以下列圖所表示：

2）彈出“在‘類別’中插入計算字段”對話框，在“名稱”文本框中輸入“差額”，把光標(biāo)定位到“公式”文本框中，單擊“字段”列表框中“類別”選項，接著雙擊右側(cè)“項”列表框中出現(xiàn)“國內(nèi)”選項，然后輸入減號“—”，再雙擊項列表框中“進(jìn)口”選項，以下列圖所表示：

3）單擊“添加”按鈕，最終單擊“確定”按鈕關(guān)閉對話框。此時數(shù)據(jù)透視表列字段區(qū)域中已經(jīng)插入了一個新項目“差額”，其數(shù)值就是“國內(nèi)”字段數(shù)據(jù)與“進(jìn)口”字段數(shù)據(jù)差值。不過這里會出現(xiàn)一個問題，數(shù)據(jù)透視表中行“總計”將匯總?cè)啃许椖?，包含新添加“差額”項，所以其結(jié)果不再具備實(shí)際意義。為了使行“總計”恢復(fù)實(shí)際統(tǒng)計功效，需要繼續(xù)對數(shù)據(jù)透視表進(jìn)行設(shè)置；

4）在數(shù)據(jù)透視表任意一個區(qū)域單擊右鍵，在彈出快捷菜單中選擇“表格選項”命令；

5）在彈出“數(shù)據(jù)透視表選項”對話框中取消勾選“行總計”復(fù)選項框，然后單擊“確定”按鈕關(guān)閉對話框；

6）重復(fù)步驟1繼續(xù)添加計算項。將國內(nèi)和進(jìn)口進(jìn)行共計，最終結(jié)果以下列圖所表示。

32、切換顯示公式和運(yùn)算結(jié)果：單擊菜單［工具］—［選項］彈出“選項”對話框，以下列圖所表示，在“視圖”選項卡“窗口選項”組合框中勾選“公式”復(fù)選框，這么用戶在單元格輸入公式時顯示則是公式本身而不是公式運(yùn)算結(jié)果。另外用戶還能夠使用<Ctrl+'>組合鍵（右撇號，通常在鍵盤上位于數(shù)字1左側(cè)一個鍵）快速地切換該選項。

33、制作按揭貸款分析表：假設(shè)購置一套住房，需要向銀行貸款約15～30萬元，分10～30年時間還貸，在不一樣貸款利率情況下，要計算等額還款方式下每個月所需按揭費(fèi)用，方法以下：

1）選中A1單元格，單擊[數(shù)據(jù)]—［有效性］，打開“數(shù)據(jù)有效性對話框”，選中“設(shè)置”選項卡。在“允許”下拉列表中選擇“序列”，在“起源”文本框中輸入各種貸款額度，如“15萬元，20萬元，25萬元，30萬元”，各個選項之間用半角逗號間隔，然后然后單擊“確定”按鈕關(guān)閉對話框。接下來在A1單元格數(shù)據(jù)有效性下拉列表中選擇一個貸款額度，比如“15萬元”。2）在C3：C7單元格內(nèi)輸入15～30年間貸款年限，在D2：L2單元格內(nèi)輸入各種貸款利率，比如本例中使用了從至今歷年來公積金貸款年率，添加行列標(biāo)題美化格式后結(jié)果以下列圖所表示；

3）選中C2單元格，輸入公式：=PMT(A3/12,A4*12,-LEFT(A1,2)*10000，公式結(jié)果顯示為錯誤值“#DIV/0!”，此結(jié)果沒有實(shí)際意義，并不影響模擬運(yùn)算表繼續(xù)操作；

4)選中單元格區(qū)域C2：L7，單擊菜單[數(shù)據(jù)]—［模擬運(yùn)算表］打開“模擬運(yùn)算表”對話框，在“輸入引用行單元格”文本框中輸入“$A$3”，在“輸入引用列單元格”文本框中輸入“$A$4”，以下列圖所表示：

5）單擊“確定”按鈕完成操作，調(diào)整數(shù)據(jù)顯示格式后結(jié)果以下列圖所表示：

上圖中詳細(xì)地顯示了貸款15萬元情形下，各種貸款年率和貸款時間所對應(yīng)按月等額還款方式詳細(xì)按揭金額。在A1單元格下拉列表中選擇不一樣貸款金額選項，模擬運(yùn)算表中就會自動地改變顯示對應(yīng)月度按揭金額。

公式解析：=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)。PMT函數(shù)是一個財務(wù)函數(shù)，用于計算固定利率等額分期付款方式下每期付款額。該函數(shù)語法為PMT(rate,nper,pv,fv,type)，其中rate指是固定利率，利率時間單位必須與貸款期限單位以及最終計算還款時間頻率單位相一致，在本例中D2：L2區(qū)域利率單位為“年”，最終計算目標(biāo)為按月還款額，所以需要將年率除以12得到月率。rate參數(shù)指向單元格A3，這個單元格沒有實(shí)際意義，只作為引用位置。nper指是付款總期數(shù)，也就是貸款期限，在本例中需要將C3：C7中對應(yīng)年份乘以12得到月份數(shù)，以期與rate參數(shù)單位保持一致。Nper參數(shù)指向單元格A4，與上面A3單元格一樣，此單元格也沒有實(shí)際意義，只用做模擬運(yùn)算表參數(shù)引用位置。pv指現(xiàn)值，在貸款模型中即指貸款總額，本例中需要從A1單元格字符串中取出對應(yīng)數(shù)值同時乘上單位“萬”，公式中“LEFT(A1,2)*10000”部分即是起此作用。fv指終值，在貸款模型中終值為零，可省略。type指付款方式，為1時代表期初付款，為0或省略時代表期末付款，在貸款模型中通常均為期末付款方式。

假如想要了解全部按揭期結(jié)束總還款金額，能夠直接在C2單元格內(nèi)將公式改為：

=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)*A4*12

顯示結(jié)果以下列圖所表示：

假如想要深入了解全部還款金額中所包含利息部分，能夠?qū)⒐叫薷臑椋?/p>

=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)*A4*12-LEFT(A1,2)*1000

顯示結(jié)果以下列圖所表示：

34、設(shè)置間隔底紋：我們原來經(jīng)常利用是在奇、偶數(shù)行方法來設(shè)置底紋，那我們這里來例1：按不一樣地域名稱設(shè)置間隔底紋：也就是依照間隔區(qū)域名稱來設(shè)置間隔顏色，以下列圖所表示：

從圖中我們能夠看出，當(dāng)在B3單元格時，我們統(tǒng)計A列區(qū)域名稱個數(shù)為1，也就是只有北京1個地域；而當(dāng)在B6單元格時，我們統(tǒng)計A列區(qū)域名稱個數(shù)為2，也就是有北京、上海2個地域；而當(dāng)在B9單元格時，我們統(tǒng)計A列區(qū)域名稱個數(shù)為3，也就是有北京、上海、重慶3個地域，依照這么規(guī)律，我們能夠利用COUNTA來統(tǒng)計A列區(qū)域中單元格個數(shù)，那么我們選中A2：C13這一需要設(shè)置間隔底紋單元格區(qū)域，點(diǎn)擊［格式］—［條件格式］，在公式欄中輸入“=MOD(COUNTA($A$2:$A2),2)=1”，將其顏色設(shè)為淡黃色，然后添加公式，在公式欄中輸入“=MOD(COUNTA($A$2:$A2),2)=0”，將其顏色設(shè)為淡綠色，完成隔色任務(wù)。

公式說明：公式中“$A$2:$A2”是A列中條件區(qū)域，“COUNTA($A$2:$A2)”統(tǒng)計出A列中符合條件區(qū)域個數(shù)，“=MOD(COUNTA($A$2:$A2),2)=1”是利用求模函數(shù)，將剛才統(tǒng)計出來A列個數(shù)與2相除得出余數(shù)，假如余數(shù)=1，則這一區(qū)域顏色被填為淡黃色。同理，“=MOD(COUNTA($A$2:$A2),2)=0”，就是余數(shù)為0，則這一區(qū)域顏色被填為淡綠色。而區(qū)域個數(shù)是1、2、3、4、5…次序遞增1排列，那么其區(qū)域個數(shù)與2相除后余數(shù)必定是1、0、1、0、1…，非常有規(guī)律，于是我們就實(shí)現(xiàn)了不一樣地域設(shè)置間隔底紋。

35、依照不一樣名稱設(shè)置間隔底紋：那么我們在看看下面這個圖：

從圖中我們能夠看出，部門這一列已經(jīng)排序，那么我們怎樣才能按照不一樣部門進(jìn)行底紋顏色設(shè)置呢？我們能夠依照上面例1原理，找到公式規(guī)律，“=MOD(SUM(--($B$2:$B2<>$B$1:$B1)),2”。

公式說明：“SUM(--($B$2:$B2<>$B$1:$B1)”表示統(tǒng)計$B$2:$B2與$B$1:$B1不相等數(shù)目，其中“—”是將邏輯值轉(zhuǎn)換為數(shù)值，“MOD(SUM(--($B$2:$B2<>$B$1:$B1)),2”是求模函數(shù)，此公式后面沒有等于0或1即為非0，此時我們將這一區(qū)域定義為玫紅，至此完全顏色間隔分隔。

36、數(shù)據(jù)有效性中使用函數(shù)：比如，我們輸入商品單價時通常輸入兩位小數(shù)，而且價格不可能為負(fù)數(shù)，那么用“=C5=FLOOR(C5,0.01)”這個公式能夠處理問題。

公式說明：假如C5單元格中準(zhǔn)備輸入數(shù)字0.234，那么“=FLOOR(0.234,0.01)”將0.234沿絕對值減小方向向下舍入，使其等于最靠近0.01倍數(shù)(0.23)，假如C5單元格中準(zhǔn)備輸入數(shù)字-2.5，那么=FLOOR(-2.5,0.01)將返回錯誤值，因?yàn)?2.5和0.01符號不一樣(#NUM!)。所以依照此公式輸入數(shù)據(jù)有效性規(guī)則和警告提醒，就能夠?qū)崿F(xiàn)在C5單元格中只能輸入兩位正小數(shù)控制。

37、選擇適宜趨勢線類型：線性關(guān)系是眾多數(shù)據(jù)關(guān)系中比較簡單一個類型，其直線樣式很輕易判斷，但很多情況下數(shù)據(jù)規(guī)律比較復(fù)雜，“線性”趨勢線并非適適用于全部數(shù)據(jù)類型趨勢預(yù)測問題，為不一樣數(shù)據(jù)類型選擇適宜趨勢線是正確使用趨勢線進(jìn)行預(yù)測分析主要前提。某城市對本市中、小學(xué)生中不一樣年紀(jì)男性身高進(jìn)行了抽樣調(diào)查，得到以下列圖所表示統(tǒng)計結(jié)果，要求依照現(xiàn)在統(tǒng)計結(jié)果預(yù)測年紀(jì)為6歲男性平均身高。

1)選中數(shù)據(jù)區(qū)域A2：B14，單擊菜單［插入］—［圖表］打開“圖表向?qū)А?步驟之1—圖表類型”對話框，在“標(biāo)準(zhǔn)類型”選項卡中選中左側(cè)“圖表類型”列表框中“XY散點(diǎn)圖”，然后在右側(cè)“子圖表類型”中選擇“平滑線散點(diǎn)圖”；

2)單擊“完成”按鈕即可生成XY散點(diǎn)圖，進(jìn)行坐標(biāo)軸設(shè)置和圖表格式美化后效果以下列圖所表示，其中以年紀(jì)數(shù)據(jù)作為X軸，以身高數(shù)據(jù)作為Y軸。

3)在圖表中單擊任意一個數(shù)據(jù)點(diǎn)以選中數(shù)據(jù)系列，然后單擊鼠標(biāo)右鍵，在彈出快捷菜單中選擇“添加趨勢線”命令打開“添加趨勢線”對話框，選擇“類型”選項卡，在“趨勢預(yù)測/回歸分析類型”組合框中選擇“線性”趨勢線類型，如上圖所表示。

4）選擇“選項”選項卡，同時勾選其中“顯示公式”和“顯示R平方值”復(fù)選框；

5）單擊“確定”按鈕，結(jié)果如上圖所表示，圖表中不但顯示了新添加趨勢線，而且顯示了此趨勢線公式以及R平方值。R平方值也稱為“決定系數(shù)”，反應(yīng)了趨勢線估量值與對應(yīng)實(shí)際數(shù)據(jù)之間擬合程度，其數(shù)值范圍位于0與1之間。當(dāng)R平方值等于或靠近于1時，趨勢線對于實(shí)際數(shù)據(jù)擬合程序最高，此時趨勢線最可靠，經(jīng)過此趨勢線預(yù)測得到數(shù)據(jù)也最準(zhǔn)確。

6）按時上面步驟能夠得到線性、對數(shù)、多項式、乘冪、指數(shù)趨勢圖，經(jīng)過比較，“對數(shù)”趨勢線對應(yīng)R平方值0.9946是其中最大值，將需要預(yù)測年紀(jì)6作為自變量x代入公式中，得到應(yīng)變量y即預(yù)測身高值函數(shù)公式以下：=54.1*LN(6)+14.635，經(jīng)過公式預(yù)測得到當(dāng)?shù)?歲男性平均身高大約為111.57厘米。

7）假如用戶對現(xiàn)有數(shù)據(jù)類型比較了解，那么有時候并不需要分別嘗試每一個趨勢線類型，而依據(jù)一定經(jīng)驗(yàn)選擇適宜趨勢線即可。

38、計算住房貸款之一：貸款按揭買房是現(xiàn)實(shí)生活中很熱門話題之一，許多購置者通常先考慮自己能夠承受月供范圍，然后再計算能夠貸款額度和期限，對于這類問題能夠借助單變量求解工具來處理。假設(shè)某客戶在買房前，預(yù)期每個月還款額為2500元，需要貸款30萬元，現(xiàn)在貸款年利率假設(shè)為5.22%，計算還清貸款所需要時間方法以下：

1）將已知條件輸入工作表中，形成以下列圖所表示數(shù)據(jù)區(qū)域：

2）在B3單元格內(nèi)輸入公式“=PMT(B1/12,B4,-B2)”建立起計算模型；

3）在B4單元格內(nèi)設(shè)定可變單元格初始值，比如1，然后選定B3單元格，單擊菜單[工具]—[單變量求解]打開“單變量求解”對話框，將“目標(biāo)值”設(shè)為2500，在“可變單元格”文本框中輸入“B4”，以下列圖所表示：

4）單元“確定”按鈕即可，顯示最終求解結(jié)果，以下列圖所表示，能夠看到在每個月還款2500元左右水平下，大約需要170個月即大約左右能夠還清全部貸款。

注意：假如使用財務(wù)函數(shù)，那么在本技巧第1幅圖所表示表格B4單元格內(nèi)直接輸入公式“=NPER(B1/12,B3,-B2)”也能夠得到結(jié)果。

39、在Excel中安裝規(guī)劃求解工具：使用默認(rèn)方式安裝Excel，通常不能直接使用規(guī)劃求解工具，需要用戶手動添加相關(guān)組件后才能使用此功效。規(guī)劃求解工具在Excel中是作為一個預(yù)置加載宏存在，假如用戶使用菜單欄上“工具”菜單列表中沒有“規(guī)劃求解”命令，則可參考以下方法進(jìn)行：

1）在Excel工作窗口中單擊菜單[工具]—[加載宏]打開“加載宏”對話框，勾選列表中“規(guī)劃求解”工具前復(fù)選框，以下列圖4所表示；

2）單元“確定”按鈕彈出警告對話框，系統(tǒng)問詢用戶是否需要現(xiàn)在安裝規(guī)劃求解加載宏；

3）單擊“是”按鈕，Excel開始安裝過程，并顯示安裝狀態(tài)對話框，直至安裝完成。

在規(guī)劃求解加載宏添加完成之后，在菜單欄上單擊“工具”，就能夠在其下拉菜單中顯示“規(guī)劃求解”命令。

40、求解旅行商問題：它路線需要經(jīng)過網(wǎng)絡(luò)中全部節(jié)點(diǎn)，而且最終形成回路。對于每個節(jié)點(diǎn)來說，都要被訪問到而且只訪問一次，同時對于某個節(jié)點(diǎn)來說，訪問者起源必定是唯一。某送奶工天天從配送出發(fā)需要送奶至6個不一樣位置小區(qū)，然后將6個地方搜集來空瓶再送回配送點(diǎn)，經(jīng)過長時間觀察統(tǒng)計，送奶工將6個小區(qū)之間騎行所需平均時間整理以下列圖所表示，其中配送點(diǎn)就設(shè)置在A小區(qū)附近，所以能夠?qū)小區(qū)視作出發(fā)點(diǎn)。現(xiàn)在送奶工想要知道，怎樣規(guī)劃一天送奶路線，能夠使得花費(fèi)在路上時間最少。此問題即為一個經(jīng)典旅行商問題，能夠用Excel規(guī)劃求解工具來解答，方法以下：

1）依照題目需求，在原有題目條件下方建立規(guī)劃求解所需公式模型，以下列圖所表示，其中C12：H17單元格區(qū)域用于統(tǒng)計實(shí)際路徑選擇情況，能夠用數(shù)字0表示路徑未選擇，用數(shù)字1表示選擇從某地出發(fā)前往另一地。此區(qū)域?qū)⒆鳛橐?guī)劃求解可變單元格區(qū)域。但需要注意是：其中A小區(qū)至A小區(qū)、B小區(qū)至B小區(qū)等類似路徑在實(shí)際中是不存在，所以在規(guī)劃求解時需要確保C12、D13…H17等單元格取值不可為1。1列用于統(tǒng)計抵達(dá)各地點(diǎn)起源數(shù)目。依照旅行商問題特征，每個地點(diǎn)訪問起源地是唯一，在I12單元格內(nèi)輸入公式“=SUM（C12：H12）”，然后向下復(fù)制填充至I17單元格。第18行用于統(tǒng)計各出發(fā)地前往目標(biāo)地數(shù)目，依照旅行商問題特征，每個出發(fā)地目標(biāo)地點(diǎn)也是唯一確定，在C18單元格內(nèi)輸入公式“=SUM（C12：C17）”，然后向下復(fù)制填充至H18單元格。J列用于統(tǒng)計訪問路線確定情況下各條線路所需時間，能夠在J12單元格內(nèi)輸入公式“=SUMPRODOCT(C3:H3,C12:H12)”,然后向下復(fù)制填充至J17單元格。J18單元格用于累計J12：J17單元格中時間，即走完整條送奶路線總時間，能夠在單元格中輸入公式“=SUM(J12:J17)”，此單元格將作為規(guī)劃求解目標(biāo)單元格；

HYPERLINK