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

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