Excel使用技巧5 數(shù)據(jù)分析和管理技巧.doc

ExcelExcel 使用技巧使用技巧 五、 數(shù)據(jù)分析和管理技巧 1.1.管理加載宏管理加載宏 2.2.在工作表之間使用超級(jí)連接在工作表之間使用超級(jí)連接 3.3.快速鏈接網(wǎng)上的數(shù)據(jù)快速鏈接網(wǎng)上的數(shù)據(jù) 4.4.跨表操作數(shù)據(jù)跨表操作數(shù)據(jù) 5.5.查看查看 E EXCELXCEL中相距較遠(yuǎn)的兩列數(shù)據(jù)中相距較遠(yuǎn)的兩列數(shù)據(jù) 6.6.如何消除縮位后的計(jì)算誤差（微軟如何消除縮位后的計(jì)算誤差（微軟 O OFFICEFFICE技巧大賽獲獎(jiǎng)作品）技巧大賽獲獎(jiǎng)作品） 7.7.利用選擇性粘貼命令完成一些特殊的計(jì)算利用選擇性粘貼命令完成一些特殊的計(jì)算 8.8.W WEBEB查詢查詢 9.9.在在 E EXCELXCEL中進(jìn)行快速計(jì)算中進(jìn)行快速計(jì)算 10.10.自動(dòng)篩選前自動(dòng)篩選前 1010 個(gè)個(gè) 11.11.同時(shí)進(jìn)行多個(gè)單元格的運(yùn)算（微軟同時(shí)進(jìn)行多個(gè)單元格的運(yùn)算（微軟 O OFFICEFFICE技巧大賽獲獎(jiǎng)作品）技巧大賽獲獎(jiǎng)作品） 12.12.讓讓 E EXCELXCEL出現(xiàn)錯(cuò)誤數(shù)據(jù)提示出現(xiàn)錯(cuò)誤數(shù)據(jù)提示 13.13.用用“超級(jí)連接超級(jí)連接”快快速跳轉(zhuǎn)到其它文件速跳轉(zhuǎn)到其它文件 一、數(shù)據(jù)分析和管理技巧 1.1.管理加載宏 ExcelExcel 包括各種特殊作用的加載宏，它們使用自定義的函數(shù)、包括各種特殊作用的加載宏，它們使用自定義的函數(shù)、 向?qū)?、?duì)話框和其他工具，擴(kuò)充了工作表的基本功能。默認(rèn)情況下，向?qū)?、?duì)話框和其他工具，擴(kuò)充了工作表的基本功能。默認(rèn)情況下， 每個(gè)加載宏都配置為在第一次使用時(shí)安裝，也就是說在第一次需要每個(gè)加載宏都配置為在第一次使用時(shí)安裝，也就是說在第一次需要 某個(gè)加載宏時(shí)，都要找某個(gè)加載宏時(shí)，都要找 OfficeOffice 光盤安裝，這是非常麻煩的事。為了光盤安裝，這是非常麻煩的事。為了 避免這種情況，你可以一次性將以后可能需要的加載宏安裝，或者避免這種情況，你可以一次性將以后可能需要的加載宏安裝，或者 全部安裝它們。單擊全部安裝它們。單擊“工具工具加載宏加載宏”，出現(xiàn)，出現(xiàn)“加載宏加載宏”對(duì)話框，對(duì)話框， 選擇可能對(duì)你有用的加載宏，如選擇可能對(duì)你有用的加載宏，如“分析工具庫分析工具庫”、“規(guī)劃求解規(guī)劃求解”、 “條件求和向?qū)l件求和向?qū)А钡?，單擊等，單擊“確定確定”，ExcelExcel 會(huì)提示所選加載宏尚會(huì)提示所選加載宏尚 沒有安裝，詢問是否現(xiàn)在安裝，選擇沒有安裝，詢問是否現(xiàn)在安裝，選擇“是是”，然后插入，然后插入 OfficeOffice 安裝安裝 光盤完成安裝。光盤完成安裝。 不要在每次啟動(dòng)不要在每次啟動(dòng) ExcelExcel 時(shí)加載每個(gè)加載宏，因?yàn)檫@時(shí)加載每個(gè)加載宏，因?yàn)檫@ 樣將減慢啟動(dòng)過程，而且每個(gè)加載宏都占用了大量的內(nèi)存。建議你樣將減慢啟動(dòng)過程，而且每個(gè)加載宏都占用了大量的內(nèi)存。建議你 將將“自動(dòng)保存自動(dòng)保存”加載，井設(shè)置適當(dāng)?shù)募虞d，井設(shè)置適當(dāng)?shù)摹白詣?dòng)保存時(shí)間間隔自動(dòng)保存時(shí)間間隔”，這樣，這樣 在在 ExcelExcel 使用過程中能自動(dòng)創(chuàng)建備份文件，避免了掉電時(shí)丟失尚未使用過程中能自動(dòng)創(chuàng)建備份文件，避免了掉電時(shí)丟失尚未 保存的文件內(nèi)容。保存的文件內(nèi)容。 2.2.在工作表之間使用超級(jí)連接 首先需要在被引用的其他工作表中相應(yīng)的部分插入書簽，然后首先需要在被引用的其他工作表中相應(yīng)的部分插入書簽，然后 在引用工作表中插入超級(jí)鏈接，注意在插入超級(jí)鏈接時(shí)，可以先在在引用工作表中插入超級(jí)鏈接，注意在插入超級(jí)鏈接時(shí)，可以先在 “插入超級(jí)鏈接插入超級(jí)鏈接”對(duì)話框的對(duì)話框的“鏈接到文件或鏈接到文件或 URL”URL”設(shè)置欄中輸入目設(shè)置欄中輸入目 標(biāo)工作表的路徑和名稱，再在標(biāo)工作表的路徑和名稱，再在“文件中有名稱的位置文件中有名稱的位置”設(shè)置欄中輸設(shè)置欄中輸 入相應(yīng)的書簽名，也可以通過入相應(yīng)的書簽名，也可以通過“瀏覽瀏覽”方式選擇。完成上述操作之方式選擇。完成上述操作之 后，一旦使用鼠標(biāo)左鍵單擊工作表中帶有下劃線的文本的任意位置，后，一旦使用鼠標(biāo)左鍵單擊工作表中帶有下劃線的文本的任意位置， 即可實(shí)現(xiàn)即可實(shí)現(xiàn) ExcelExcel 自動(dòng)打開目標(biāo)工作表并轉(zhuǎn)到相應(yīng)的位置處。自動(dòng)打開目標(biāo)工作表并轉(zhuǎn)到相應(yīng)的位置處。 3.3.快速鏈接網(wǎng)上的數(shù)據(jù) 你可以用以下方法快速建立與網(wǎng)上工作簿數(shù)據(jù)的鏈接你可以用以下方法快速建立與網(wǎng)上工作簿數(shù)據(jù)的鏈接: : 1.1.打開打開 InternetInternet 上含有需要鏈接數(shù)據(jù)的工作簿，并在工作簿選定數(shù)據(jù)，然上含有需要鏈接數(shù)據(jù)的工作簿，并在工作簿選定數(shù)據(jù)，然 后單擊后單擊“編輯編輯復(fù)制復(fù)制”命令命令; ; 2.2.打開需要?jiǎng)?chuàng)建鏈接的工作簿，在需打開需要?jiǎng)?chuàng)建鏈接的工作簿，在需 要顯示鏈接數(shù)據(jù)的區(qū)域中，單擊左上角單元格要顯示鏈接數(shù)據(jù)的區(qū)域中，單擊左上角單元格; ; 3.3.單擊單擊“編輯編輯選選 擇性粘貼擇性粘貼”命令，在命令，在“選擇性粘貼選擇性粘貼”對(duì)話框中，選擇對(duì)話框中，選擇“粘貼鏈接粘貼鏈接” 按鈕即可。按鈕即可。 若你想在創(chuàng)建鏈接時(shí)不打開若你想在創(chuàng)建鏈接時(shí)不打開 InternetInternet 工作簿，可單擊工作簿，可單擊 需要鏈接處的單元格，然后鍵入需要鏈接處的單元格，然后鍵入(=)(=)和和 URLURL 地址及工作簿位置，如地址及工作簿位置，如: : =http:/www.J 4.4.跨表操作數(shù)據(jù) 設(shè)有名稱為設(shè)有名稱為 Sheet1Sheet1、Sheet2Sheet2 和和 Sheet3Sheet3 的的 3 3 張工作表，現(xiàn)要用張工作表，現(xiàn)要用 Sheet1Sheet1 的的 D8D8 單元格的內(nèi)容乘以單元格的內(nèi)容乘以 4040，再加上，再加上 Sheet2Sheet2 的的 B8B8 單元格單元格 內(nèi)容乘以內(nèi)容乘以 6060作為作為 Sheet3Sheet3 的的 A8A8 單元格的內(nèi)容，則應(yīng)該在單元格的內(nèi)容，則應(yīng)該在 Sheet3Sheet3 的的 A8A8 單元格輸入以下算式單元格輸入以下算式:=Sheet1!D8:=Sheet1!D84040Sheet2!B8Sheet2!B86060。 5.5.查看 Excel 中相距較遠(yuǎn)的兩列數(shù)據(jù) 在在 ExcelExcel 中，若要將距離較遠(yuǎn)的兩列數(shù)據(jù)中，若要將距離較遠(yuǎn)的兩列數(shù)據(jù)( (如如 A A 列與列與 Z Z 列列) )進(jìn)行進(jìn)行 對(duì)比，只能不停地移動(dòng)表格窗內(nèi)的水平滾動(dòng)條來分別查看，這樣的對(duì)比，只能不停地移動(dòng)表格窗內(nèi)的水平滾動(dòng)條來分別查看，這樣的 操作非常麻煩而且容易出錯(cuò)。利用下面這個(gè)小技巧，你可以將一個(gè)操作非常麻煩而且容易出錯(cuò)。利用下面這個(gè)小技巧，你可以將一個(gè) 數(shù)據(jù)表數(shù)據(jù)表“變變”成兩個(gè)，讓相距較遠(yuǎn)的數(shù)據(jù)同屏顯示。成兩個(gè)，讓相距較遠(yuǎn)的數(shù)據(jù)同屏顯示。 把鼠標(biāo)指針移把鼠標(biāo)指針移 到工作表底部水平滾動(dòng)條右側(cè)的小塊上，鼠標(biāo)指針便會(huì)變成一個(gè)雙到工作表底部水平滾動(dòng)條右側(cè)的小塊上，鼠標(biāo)指針便會(huì)變成一個(gè)雙 向的光標(biāo)。把這個(gè)小塊拖到工作表的中部，你便會(huì)發(fā)現(xiàn)整個(gè)工作表向的光標(biāo)。把這個(gè)小塊拖到工作表的中部，你便會(huì)發(fā)現(xiàn)整個(gè)工作表 被一分為二，出現(xiàn)了兩個(gè)數(shù)據(jù)框，而其中的都是當(dāng)前工作表內(nèi)的內(nèi)被一分為二，出現(xiàn)了兩個(gè)數(shù)據(jù)框，而其中的都是當(dāng)前工作表內(nèi)的內(nèi) 容。這樣你便可以讓一個(gè)數(shù)據(jù)框中顯示容。這樣你便可以讓一個(gè)數(shù)據(jù)框中顯示 A A 列數(shù)據(jù)，另一個(gè)數(shù)據(jù)框中列數(shù)據(jù)，另一個(gè)數(shù)據(jù)框中 顯示顯示 Z Z 列數(shù)據(jù)，從而可以進(jìn)行輕松的比較。列數(shù)據(jù)，從而可以進(jìn)行輕松的比較。 6.6.如何消除縮位后的計(jì)算誤差（微軟 Office 技巧大賽獲 獎(jiǎng)作品） 有時(shí)我們輸入的數(shù)字是小數(shù)點(diǎn)后兩位數(shù)，但是在精度要求上只有時(shí)我們輸入的數(shù)字是小數(shù)點(diǎn)后兩位數(shù)，但是在精度要求上只 要一位，縮位后顯示沒問題，但其計(jì)算結(jié)果卻是有誤差的。解決方要一位，縮位后顯示沒問題，但其計(jì)算結(jié)果卻是有誤差的。解決方 法是法是: :單擊單擊“工具工具選項(xiàng)選項(xiàng)重新計(jì)算重新計(jì)算”，選中，選中“以顯示值為準(zhǔn)以顯示值為準(zhǔn)”（），（）， 這樣計(jì)算結(jié)果就沒有誤差了。這樣計(jì)算結(jié)果就沒有誤差了。 事實(shí)上并不是計(jì)算上有誤差，而是顯事實(shí)上并不是計(jì)算上有誤差，而是顯 示設(shè)置的四舍五入。采用本技巧提供的方法，可以解決顯示中的問示設(shè)置的四舍五入。采用本技巧提供的方法，可以解決顯示中的問 題，但同時(shí)會(huì)改變數(shù)值的精度，在使用前題，但同時(shí)會(huì)改變數(shù)值的精度，在使用前 ExcelExcel 會(huì)給你一個(gè)警告。會(huì)給你一個(gè)警告。 7.7.利用選擇性粘貼命令完成一些特殊的計(jì)算 如果某如果某 ExcelExcel 工作表中有大量數(shù)字格式的數(shù)據(jù)，并且你希望將工作表中有大量數(shù)字格式的數(shù)據(jù)，并且你希望將 所有數(shù)字取負(fù)，請(qǐng)使用選擇性粘貼命令，操作方法如下所有數(shù)字取負(fù)，請(qǐng)使用選擇性粘貼命令，操作方法如下: : 在一個(gè)空在一個(gè)空 單元格中輸入單元格中輸入“-1”“-1”，選擇該單元格，并點(diǎn)擊，選擇該單元格，并點(diǎn)擊“編輯編輯復(fù)制復(fù)制”命令，命令， 選擇目標(biāo)單元格。點(diǎn)擊選擇目標(biāo)單元格。點(diǎn)擊“編輯編輯選擇性粘貼選擇性粘貼”命令，選中粘貼欄下命令，選中粘貼欄下 的數(shù)值和運(yùn)算欄下的乘，點(diǎn)擊的數(shù)值和運(yùn)算欄下的乘，點(diǎn)擊“確定確定”，所有數(shù)字將與，所有數(shù)字將與-1-1 相乘。你相乘。你 也可以使用該方法將單元格中的數(shù)值縮小也可以使用該方法將單元格中的數(shù)值縮小 10001000 或更大倍數(shù)?；蚋蟊稊?shù)。 8.8.Web 查詢 保持保持 ExcelExcel 工作表總是最新工作表總是最新 WebWeb 頁上經(jīng)常包含適合在頁上經(jīng)常包含適合在 ExcelExcel 中中 進(jìn)行分析的信息，例如，可以在進(jìn)行分析的信息，例如，可以在 ExcelExcel 中使用直接從中使用直接從 WebWeb 頁上獲取頁上獲取 的信息分析股票報(bào)價(jià)。但如果你需要經(jīng)常更新信息就必須借助的信息分析股票報(bào)價(jià)。但如果你需要經(jīng)常更新信息就必須借助 WebWeb 頁上的最新信息?，F(xiàn)在頁上的最新信息?，F(xiàn)在 ExcelExcel 20022002 可以用可刷新可以用可刷新 WebWeb 查詢簡(jiǎn)化這個(gè)查詢簡(jiǎn)化這個(gè) 任務(wù)來創(chuàng)建新的可刷新任務(wù)來創(chuàng)建新的可刷新 WebWeb 查詢，方法如下查詢，方法如下: : 在瀏覽器中瀏覽要查在瀏覽器中瀏覽要查 詢數(shù)據(jù)的詢數(shù)據(jù)的 WebWeb 頁，把數(shù)據(jù)復(fù)制并粘貼到頁，把數(shù)據(jù)復(fù)制并粘貼到 ExcelExcel 工作表中。在粘貼的工作表中。在粘貼的 數(shù)據(jù)下方將出現(xiàn)一個(gè)粘貼選項(xiàng)智能標(biāo)記，單擊粘貼選項(xiàng)智能標(biāo)記右數(shù)據(jù)下方將出現(xiàn)一個(gè)粘貼選項(xiàng)智能標(biāo)記，單擊粘貼選項(xiàng)智能標(biāo)記右 邊的箭頭，再單擊創(chuàng)建可刷新的邊的箭頭，再單擊創(chuàng)建可刷新的 WebWeb 查詢，在新建查詢，在新建 WebWeb 查詢對(duì)話框查詢對(duì)話框 中，點(diǎn)擊你想要查詢的數(shù)據(jù)表前面的黃色箭頭，單擊導(dǎo)入。中，點(diǎn)擊你想要查詢的數(shù)據(jù)表前面的黃色箭頭，單擊導(dǎo)入。 在在 ExcelExcel 中可以手動(dòng)或自動(dòng)刷新這個(gè)數(shù)據(jù)。手動(dòng)刷新方法如下中可以手動(dòng)或自動(dòng)刷新這個(gè)數(shù)據(jù)。手動(dòng)刷新方法如下: :在外部在外部 數(shù)據(jù)工具欄上，單擊數(shù)據(jù)區(qū)域?qū)傩园粹o，在刷新控制下面選中你想數(shù)據(jù)工具欄上，單擊數(shù)據(jù)區(qū)域?qū)傩园粹o，在刷新控制下面選中你想 要的選項(xiàng)的復(fù)選框。要的選項(xiàng)的復(fù)選框。 注意注意: :當(dāng)你從當(dāng)你從 WebWeb 站點(diǎn)獲取數(shù)據(jù)時(shí)，可能會(huì)丟站點(diǎn)獲取數(shù)據(jù)時(shí)，可能會(huì)丟 失一些格式或內(nèi)容，像腳本、失一些格式或內(nèi)容，像腳本、gifgif 圖像或單個(gè)單元中的數(shù)據(jù)列表。圖像或單個(gè)單元中的數(shù)據(jù)列表。 9.9.在 Excel 中進(jìn)行快速計(jì)算 查看一系列單元格的最大值的操作方法查看一系列單元格的最大值的操作方法: : 選擇你感興趣的單元選擇你感興趣的單元 格，你將看到所選單元格的總和顯示在狀態(tài)欄中。狀態(tài)欄就是工作格，你將看到所選單元格的總和顯示在狀態(tài)欄中。狀態(tài)欄就是工作 表窗口下方的水平區(qū)域。如果沒有出現(xiàn)狀態(tài)欄，單擊視圖菜單中的表窗口下方的水平區(qū)域。如果沒有出現(xiàn)狀態(tài)欄，單擊視圖菜單中的 狀態(tài)欄，鼠標(biāo)右擊狀態(tài)欄，然后單擊最大值，現(xiàn)在你就可以在狀態(tài)狀態(tài)欄，鼠標(biāo)右擊狀態(tài)欄，然后單擊最大值，現(xiàn)在你就可以在狀態(tài) 欄中看到最大值了。該方法可以計(jì)算選定單元格的平均值、總和、欄中看到最大值了。該方法可以計(jì)算選定單元格的平均值、總和、 最小值。此外，你還可使用該方法計(jì)算包含數(shù)字的單元格的數(shù)量最小值。此外，你還可使用該方法計(jì)算包含數(shù)字的單元格的數(shù)量 （選擇計(jì)數(shù)值），或者計(jì)算已填充單元格的數(shù)量（選擇計(jì)數(shù)）。（選擇計(jì)數(shù)值），或者計(jì)算已填充單元格的數(shù)量（選擇計(jì)數(shù)）。 10.10.自動(dòng)篩選前 10 個(gè) 有時(shí)你可能想對(duì)數(shù)值字段使用自動(dòng)篩選來顯示數(shù)據(jù)清單里的前有時(shí)你可能想對(duì)數(shù)值字段使用自動(dòng)篩選來顯示數(shù)據(jù)清單里的前 n n 個(gè)最大值或最小值，解決的方法是使用個(gè)最大值或最小值，解決的方法是使用“前前 1010 個(gè)個(gè)”自動(dòng)篩選。當(dāng)自動(dòng)篩選。當(dāng) 你在自動(dòng)篩選的數(shù)值字段下拉列表中選擇你在自動(dòng)篩選的數(shù)值字段下拉列表中選擇“前前 1010 個(gè)個(gè)”選項(xiàng)時(shí)，將出選項(xiàng)時(shí)，將出 現(xiàn)現(xiàn)“自動(dòng)篩選前自動(dòng)篩選前 1010 個(gè)個(gè)”對(duì)話框，這里所謂對(duì)話框，這里所謂“前前 1010 個(gè)個(gè)”是一個(gè)一般是一個(gè)一般 術(shù)語，并不僅局限于前術(shù)語，并不僅局限于前 1010 個(gè)，你可以選擇最大或最小和定義任意的個(gè)，你可以選擇最大或最小和定義任意的 數(shù)字，比如根據(jù)需要選擇數(shù)字，比如根據(jù)需要選擇 8 8 個(gè)、個(gè)、1212 個(gè)等。個(gè)等。 11.11.同時(shí)進(jìn)行多個(gè)單元格的運(yùn)算（微軟 Office 技巧大賽獲 獎(jiǎng)作品） 如果我們現(xiàn)在有多個(gè)單元格的數(shù)據(jù)要和一個(gè)數(shù)據(jù)進(jìn)行加減乘除如果我們現(xiàn)在有多個(gè)單元格的數(shù)據(jù)要和一個(gè)數(shù)據(jù)進(jìn)行加減乘除 運(yùn)算，那么一個(gè)一個(gè)運(yùn)算顯然比較麻煩，其實(shí)利用運(yùn)算，那么一個(gè)一個(gè)運(yùn)算顯然比較麻煩，其實(shí)利用“選擇性粘貼選擇性粘貼” 功能就可以實(shí)現(xiàn)同時(shí)運(yùn)算。下面我們一起來看一個(gè)實(shí)例。功能就可以實(shí)現(xiàn)同時(shí)運(yùn)算。下面我們一起來看一個(gè)實(shí)例。 我們要將我們要將 C1C1、C4C4、C5C5、D3D3、E11E11 單元格數(shù)據(jù)都加上單元格數(shù)據(jù)都加上 2525，那么可以這樣做，那么可以這樣做: :首先首先 在一個(gè)空白的單元格中輸入在一個(gè)空白的單元格中輸入 2525，選中這個(gè)單元格后點(diǎn)擊鼠標(biāo)右鍵選，選中這個(gè)單元格后點(diǎn)擊鼠標(biāo)右鍵選 擇擇“復(fù)制復(fù)制”。然后按住。然后按住 CtrlCtrl 鍵依次點(diǎn)擊鍵依次點(diǎn)擊 C1C1、C4C4、C5C5、D3D3、E11E11 單元單元 格，將這些單元格選中。接下來點(diǎn)擊鼠標(biāo)右鍵選擇格，將這些單元格選中。接下來點(diǎn)擊鼠標(biāo)右鍵選擇“選擇性粘貼選擇性粘貼”， 在在“選擇性粘貼選擇性粘貼”對(duì)話框中勾選對(duì)話框中勾選“運(yùn)算運(yùn)算”框內(nèi)的框內(nèi)的“加加”選項(xiàng)，點(diǎn)擊選項(xiàng)，點(diǎn)擊 “確定確定”?，F(xiàn)在我們可以看到，這些單元格中的數(shù)據(jù)都同時(shí)被加上?，F(xiàn)在我們可以看到，這些單元格中的數(shù)據(jù)都同時(shí)被加上 了了 2525。 12.12.讓 Excel 出現(xiàn)錯(cuò)誤數(shù)據(jù)提示 ExcelExcel 除了可以對(duì)單元格或單元格區(qū)域設(shè)置數(shù)據(jù)有效性條件并除了可以對(duì)單元格或單元格區(qū)域設(shè)置數(shù)據(jù)有效性條件并 進(jìn)行檢查外，還可以在用戶選擇單元格或單元格區(qū)域時(shí)顯示幫助性進(jìn)行檢查外，還可以在用戶選擇單元格或單元格區(qū)域時(shí)顯示幫助性 “輸入信息輸入信息”，也可以在用戶輸入了非法數(shù)據(jù)時(shí)提示，也可以在用戶輸入了非法數(shù)據(jù)時(shí)提示“錯(cuò)誤警告錯(cuò)誤警告”。 選取單元格或單元格區(qū)域，單擊選取單元格或單元格區(qū)域，單擊“數(shù)據(jù)數(shù)據(jù)有效性有效性”命令，單擊命令，單擊“輸輸 入信息入信息”選項(xiàng)卡，選定選項(xiàng)卡，選定“選定單元格時(shí)顯示輸入信息選定單元格時(shí)顯示輸入信息”復(fù)選框，輸復(fù)選框，輸 入標(biāo)題，如入標(biāo)題，如“注意注意”，輸入顯示信息如，輸入顯示信息如“這里應(yīng)輸入負(fù)數(shù)！這里