Excel操作技巧與實(shí)例(大全)

1、Leo Sino HR Manage Consulting StudioIExcel 操作技巧操作技巧Skills About Excel版本：A Rev.20120820（2012-8-202012-8-20 初版）初版）Email：Leo Sino HR Manage Consulting StudioII目錄1 操作技巧操作技巧.1 11.1 關(guān)于換行 ALT+ENTER.11.2 快速在多上合計(jì)行中求和 .11.3 不復(fù)制隱藏的行或列 .11.4 查找通配符 .21.5 文本處理 .21.5.1 等長(zhǎng)文本的分割 .21.5.2 不等長(zhǎng)文本的分割 .21.5.3 文本的合并 .31.5.

2、4 文本轉(zhuǎn)數(shù)值的 10 種辦法 .31.5.5 把引號(hào)中的字符串“.Trends in Genetics 23 (4): 1929“中的前半部分英文提取出來 .31.5.6 使用分列更改文本為數(shù)值 .41.6 高級(jí)篩選 .61.6.1 如何將一列數(shù)據(jù)中在另外一列出現(xiàn)過的項(xiàng)目篩選出來 .71.7 條件格式 .71.8 數(shù)據(jù)透視表 .81.8.1 應(yīng)用數(shù)據(jù)透視表進(jìn)行各類數(shù)據(jù)分析 .81.8.2 同時(shí)顯示數(shù)據(jù)與百分比： .101.8.3 篩選數(shù)據(jù)： .101.8.4 顯示明細(xì)數(shù)據(jù)： .111.8.5 自動(dòng)更新： .121.8.6 透視圖 .121.8.7 多重合并計(jì)算數(shù)據(jù) .131.9 名稱的引用

3、.151.10 自定義格式語法： .161.11 動(dòng)態(tài)表頭： .171.12 行與列的合計(jì)有時(shí)出現(xiàn)不相等的情況。 .171.13 表格的幾種類型及其處理技巧 .171.14 快速刪除工作表中所有的文本框 .182 函數(shù)數(shù)組函數(shù)數(shù)組.20202.1 函數(shù)寶典 .20Leo Sino HR Manage Consulting StudioIII2.2 自定義函數(shù) 157 個(gè) .202.3 VLOOKUP.202.4 OFFSET.202.5 IS 函數(shù).202.6 數(shù)組公式特征 .212.7 SQL 語句在 EXCEL 中的典型用法.223 圖表圖表.23233.1 復(fù)合餅圖 .233.2 圖表組

4、合 .263.3 甘特圖 .303.4 帶滾動(dòng)條的圖表 .343.5 動(dòng)態(tài)圖表的制作 .364 數(shù)據(jù)分析數(shù)據(jù)分析.39394.1 數(shù)據(jù)有效性 .394.1.1 防止重復(fù)錄入 .394.1.2 只能輸入日期 .404.1.3 輸入的內(nèi)容中必須包括某字符 .414.1.4 制作二級(jí)選項(xiàng)菜單： .414.2 整合數(shù)據(jù)表 .414.3 自定義數(shù)字格式的實(shí)用案例 .464.4 批量取消合并單元格并使用 CTRL+ENTER填充數(shù)據(jù).474.5 隱藏小于 3000 的數(shù)值 .484.6 將一列數(shù)據(jù)轉(zhuǎn)化為多列數(shù)據(jù) .484.7 將多列數(shù)據(jù)轉(zhuǎn)化為一列 .484.8 合并計(jì)算匯總多表數(shù)據(jù) .494.9 利用合并

5、計(jì)算核對(duì)數(shù)值型數(shù)據(jù) .505 5 實(shí)例技巧實(shí)例技巧 .51515.1 各種各樣的排名匯總 .515.2 資產(chǎn)折舊 .515.3 巧設(shè)單元格格式打印席卡 .53Leo Sino HR Manage Consulting Studio01 操作技巧操作技巧1.11.1 關(guān)于換行關(guān)于換行 Alt+EnterAlt+Enter同一單元格內(nèi)，有些長(zhǎng)數(shù)據(jù)或條列式內(nèi)容必須強(qiáng)行換行才能對(duì)齊。方法是光標(biāo)移到在需要換行的位置上同時(shí)按下 Alt+Enter 鍵(使用強(qiáng)行換行時(shí)，系統(tǒng)會(huì)同時(shí)選擇自動(dòng)換行功能)。1輸入數(shù)據(jù)隨時(shí)換行用戶若要在輸入數(shù)據(jù)時(shí)換行，只要通過 Alt+Enter 組合鍵即可輕松實(shí)現(xiàn)。此方法同樣可使已

6、輸入內(nèi)容的單元格在光標(biāo)所在處換行。 2單元格區(qū)域內(nèi)換行將某個(gè)長(zhǎng)行轉(zhuǎn)成段落并在指定區(qū)域內(nèi)換行。例如：A10 內(nèi)容很長(zhǎng)，欲將其顯示在 A 列至 C 列之內(nèi)，步驟是：選定區(qū)域 A10:C12(先選 A10)，選擇“編輯填充內(nèi)容重排”，A10 內(nèi)容就會(huì)分布在 A10:C12 區(qū)域中。此法特別適合用于表格內(nèi)的注釋。3調(diào)整單元格格式換行 選定單元格，選擇“格式單元格”，在彈出的對(duì)話框中單擊“對(duì)齊”，選中“自動(dòng)換行”復(fù)選框，單擊確定按鈕即可。4.錄制一個(gè)宏,定義快捷鍵1.21.2 快速在多上合計(jì)行中求和快速在多上合計(jì)行中求和選擇區(qū)域-按 F5-選擇“空值”-按“Alt+=快捷鍵?？焖僭诙嗌虾嫌?jì)行中求和.gi

7、f1.31.3 不復(fù)制隱藏的行或列不復(fù)制隱藏的行或列a 首先選中需要復(fù)制的被隱藏了一些行或列的表格區(qū)域；b 然后點(diǎn)擊“編輯”-“定位”-“定位條件”，在其中選擇“可見單元格”；c 復(fù)制表格區(qū)域，粘貼即可。Leo Sino HR Manage Consulting Studio11.41.4 查找通配符查找通配符在 Excel 中，如何替換單元格中的*、?號(hào)呢？由于二者在“查找和替換”對(duì)話框中扮演通配符的角色，代表多個(gè)、單個(gè)任意字符，而要替換這兩個(gè)符號(hào)本身，需要在前面增加一個(gè)符號(hào)，如附圖所示。同理要替換“”符號(hào)時(shí)，也需要在其前面增加一個(gè)“”符號(hào)。1.51.5 文本處理文本處理1.5.11.5.1

8、 等長(zhǎng)文本的分割等長(zhǎng)文本的分割從字符串的左邊取字符：=Left (字符串，文本長(zhǎng)度)從字符串的右邊取字符：=Right (字符串，文本長(zhǎng)度)從字符串的中間取字符：=Mid (字符串，文本起始位置，文本長(zhǎng)度)1.5.21.5.2 不等長(zhǎng)文本的分割不等長(zhǎng)文本的分割第 1 步：選中要進(jìn)行分割的字符串區(qū)域。第 2 步：點(diǎn)擊“數(shù)據(jù)”“分列”，在文本分列向?qū)е羞x擇適合的分隔符。第 3 步：設(shè)置需要導(dǎo)入的列以及放置該列的位置。Leo Sino HR Manage Consulting Studio21.5.31.5.3 文本的合并文本的合并=Concatenate(文本 1，文本 2，)使用連接符&

9、：1.5.41.5.4 文本轉(zhuǎn)數(shù)值的文本轉(zhuǎn)數(shù)值的 1010 種辦法種辦法,文本轉(zhuǎn)數(shù)值的10種方法.gif文本轉(zhuǎn)數(shù)值的10種辦法.xls1.5.51.5.5 把引號(hào)中的字符串把引號(hào)中的字符串“.Trends.Trends inin GeneticsGenetics 2323 (4):(4): 1921929 9“中的前半中的前半部分英文提取出來部分英文提取出來使用數(shù)組公式=LEFT(A1,MIN(FIND(ROW(1:10)-1,A1&1/17)-1)也可使用公式=LEFT(A1,MIN(FIND(ROW(1:10)-1,A1&0123456789)-1)說明：1/17是一個(gè)包含

10、了0到9的數(shù)字小數(shù)。目的是在原數(shù)據(jù)的后面連接上“0123456789，以避免 find 時(shí)出錯(cuò)。這是簡(jiǎn)化公式的常用手法，與此類似作用的還有519。1.5.61.5.6 使用分列更改文本為數(shù)值使用分列更改文本為數(shù)值如果你發(fā)現(xiàn)在 Excel 中輸入的公式計(jì)算結(jié)果不正確，也許是單元格中保存Leo Sino HR Manage Consulting Studio3的數(shù)字被保存為文本類型的原因。這時(shí)需要將文本類型的數(shù)這轉(zhuǎn)為數(shù)值，方法如下：Leo Sino HR Manage Consulting Studio41.61.6 高級(jí)篩選高級(jí)篩選由于自動(dòng)篩選只能篩選出簡(jiǎn)單條件的數(shù)據(jù)，因此如要在復(fù)雜條件下進(jìn)行篩

11、選就需要采用高級(jí)篩選的方式。在需要進(jìn)行篩選的數(shù)據(jù)表外設(shè)定篩選條件。在對(duì)話框中設(shè)定：列表區(qū)域?yàn)閿?shù)據(jù)表區(qū)域；條件區(qū)域?yàn)閯偛旁跀?shù)據(jù)表外部設(shè)定的條件區(qū)域，要包括字段名稱和條件所在的單元格區(qū)域。Leo Sino HR Manage Consulting Studio51.6.11.6.1 如何將一列數(shù)據(jù)中在另外一列出現(xiàn)過的項(xiàng)目篩選出來如何將一列數(shù)據(jù)中在另外一列出現(xiàn)過的項(xiàng)目篩選出來1.71.7 條件格式條件格式條件格式可以使得符合特定條件的記錄按照某種設(shè)定的格式顯示。選中要進(jìn)行條件格式設(shè)定的單元格范圍，執(zhí)行【格式】【條件格式】在條件格式設(shè)置界面進(jìn)行條件的設(shè)定：Leo Sino HR Manage Cons

12、ulting Studio61.81.8 數(shù)據(jù)透視表數(shù)據(jù)透視表1.8.11.8.1 應(yīng)用數(shù)據(jù)透視表進(jìn)行各類數(shù)據(jù)分析應(yīng)用數(shù)據(jù)透視表進(jìn)行各類數(shù)據(jù)分析選擇“數(shù)據(jù)”“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，進(jìn)入透視表設(shè)置向?qū)?。Leo Sino HR Manage Consulting Studio7點(diǎn)擊“下一步”，選擇正確的數(shù)據(jù)范圍。點(diǎn)擊“下一步”，選擇“布局”按鈕。在如下圖界面上，將右側(cè)的字段拖入左側(cè)相應(yīng)區(qū)域內(nèi)。形成如下圖的布局，點(diǎn)擊確定按鈕。Leo Sino HR Manage Consulting Studio8生成如下的透視表后，將鼠標(biāo)懸停在想要移動(dòng)的字段上，鼠標(biāo)左鍵按下，將字段拖放到其他區(qū)域，生成自己需要

13、的數(shù)據(jù)顯示。數(shù)據(jù)百分比顯示：右鍵菜單字段設(shè)置選項(xiàng)，將數(shù)據(jù)顯示方式改為“占同列數(shù)據(jù)總和的百分比”1.8.21.8.2 同時(shí)顯示數(shù)據(jù)與百分比：同時(shí)顯示數(shù)據(jù)與百分比：1) 在【布局】里將銷售收入兩次拖入數(shù)據(jù)區(qū)域2) 將其中一個(gè)銷售收入改為百分比顯示3) 在報(bào)表項(xiàng)目上輸入新的名稱可以修改項(xiàng)目名稱4) 將報(bào)表項(xiàng)目拖拽到列標(biāo)題位置，可以將數(shù)值和百分比改為按列排列1.8.31.8.3 篩選數(shù)據(jù)：篩選數(shù)據(jù)：點(diǎn)擊字段名稱后面的下拉菜單，可以對(duì)字段內(nèi)容進(jìn)行篩選。Leo Sino HR Manage Consulting Studio91.8.41.8.4 顯示明細(xì)數(shù)據(jù)：顯示明細(xì)數(shù)據(jù)：雙擊需要查看明細(xì)的數(shù)據(jù)，在新的

14、工作表上將顯示構(gòu)成此匯總數(shù)據(jù)的所有明細(xì)數(shù)據(jù)。1. 對(duì)數(shù)據(jù)進(jìn)行排序：把光標(biāo)放在行位置的報(bào)表項(xiàng)目上，【右鍵菜單】【字段設(shè)置】，【高級(jí)】按鈕，左側(cè)可以進(jìn)行排序設(shè)置。2. 按照日期分組：1) 將日期放入行區(qū)域，【右鍵菜單】【組及顯示明細(xì)數(shù)據(jù)】【組合】2) 在【步長(zhǎng)】中選擇需要的分組標(biāo)準(zhǔn)3. 按照數(shù)值分組：1) 將銷售收入放入行區(qū)域，【右鍵菜單】【組及顯示明細(xì)數(shù)據(jù)】【組合】2) 在【步長(zhǎng)】中選擇需要的分組標(biāo)準(zhǔn)4. 插入計(jì)算字段：Leo Sino HR Manage Consulting Studio10在【名稱】后給計(jì)算字段定義名稱在【公式】后輸入計(jì)算字段的計(jì)算公式，可以引用下面的字段1.8.51.8.

15、5 自動(dòng)更新：自動(dòng)更新：【右鍵菜單】【表格選項(xiàng)】【打開時(shí)刷新】可以讓報(bào)表始終有更新后的數(shù)據(jù)顯示。1.8.61.8.6 透視圖透視圖可以單獨(dú)生成數(shù)據(jù)透視圖，也可以基于現(xiàn)有的透視表生成透視圖。如果已經(jīng)有現(xiàn)成的透視表，通過點(diǎn)擊數(shù)據(jù)透視表工具條上的圖表按鈕可以生成一個(gè)透視圖。Leo Sino HR Manage Consulting Studio11通過拖動(dòng)透視圖上的各個(gè)字段到右側(cè)或者底部的位置，可以方便地改變圖表組織數(shù)據(jù)的方式。隱藏透視圖字段按鈕：把光標(biāo)懸停在任意字段上，右鍵菜單，選擇【隱藏?cái)?shù)據(jù)透視圖字段按鈕】，即可以將字段按鈕隱藏；再次顯示字段按鈕：點(diǎn)擊透視圖工具條上第一項(xiàng)，在出現(xiàn)的下拉菜單中選擇

16、【隱藏?cái)?shù)據(jù)透視圖字段按鈕】如果有些數(shù)據(jù)無法在數(shù)據(jù)透視表內(nèi)部分析，可引用透視表的數(shù)據(jù)作為分析基礎(chǔ)。1.8.71.8.7 多重合并計(jì)算數(shù)據(jù)多重合并計(jì)算數(shù)據(jù)如下圖格式的即為二維表：Leo Sino HR Manage Consulting Studio12現(xiàn)在我們需要將數(shù)個(gè)格式相同的二維表匯總為一張表格，且可以區(qū)分不同表格屬性進(jìn)行分析，比如三張表分別為北京，上海，深圳分公司的表格。我們使用的方法是利用透視表多重合并計(jì)算數(shù)據(jù)區(qū)域的功能。選擇數(shù)據(jù)-數(shù)據(jù)透視表和數(shù)據(jù)透視圖選擇第 3 個(gè)數(shù)據(jù)源類型：多重合并計(jì)算數(shù)據(jù)區(qū)域，點(diǎn)擊下一步按鈕：在出現(xiàn)的界面上選擇：自定義字段Leo Sino HR Manage Co

17、nsulting Studio13選擇需要被合并的表格區(qū)域，點(diǎn)擊添加將其添加到所有區(qū)域；將頁字段數(shù)據(jù)改為 1，在項(xiàng)標(biāo)志處輸入該表格的標(biāo)志，使用相同的方法將其他需要合并的表格全部添加。透視表生成后如下圖所示，已經(jīng)將多個(gè)表格合并成一個(gè)：雙擊字段名，可以對(duì)字段名進(jìn)行修改：Leo Sino HR Manage Consulting Studio141.91.9 名稱的引用名稱的引用名稱名稱可以代表一個(gè)單元格或者一個(gè)單元格區(qū)域，或者是常量，公式。名稱的定義：選中需要命名的單元格或區(qū)域，在界面左上角名稱框中輸入名稱后回車；名稱的刪除：插入名稱定義，選中需要?jiǎng)h除的名稱點(diǎn)擊刪除按鈕，點(diǎn)確定；名稱的引用：需要引

18、用某單元格時(shí)輸入該單元格的名稱：名稱第 1 步：選中需要命名的某個(gè)單元格或單元格區(qū)域。第 2 步：在左上角名稱框輸入命名后回車。刪除已有命名：選擇“插入”“名稱”“定義”；選中需要?jiǎng)h除的名稱，點(diǎn)擊“刪除”按鈕。Leo Sino HR Manage Consulting Studio15名稱命名的優(yōu)點(diǎn)：1) 避免絕對(duì)引用的錯(cuò)誤2) 對(duì)公式進(jìn)行文字化表述，讓公式更加容易理解3) 可以在整個(gè)工作簿中通用，引用方便1.101.10 自定義格式語法：自定義格式語法：大于條件值格式；小于條件值格式；等于條件值格式；文本格式1.111.11 動(dòng)態(tài)表頭：動(dòng)態(tài)表頭：ABC 公司&YEAR(NOW()&a

19、mp;年&MONTH(NOW()&月報(bào)表1.121.12 行與列的合計(jì)有時(shí)出現(xiàn)不相等的情況。行與列的合計(jì)有時(shí)出現(xiàn)不相等的情況。選擇“工具”“選項(xiàng)”“重新計(jì)算”“以顯示精度為準(zhǔn)”，選中該選項(xiàng)。1.131.13 表格的幾種類型及其處理技巧表格的幾種類型及其處理技巧數(shù)據(jù)表類型操作目標(biāo)匯總方法是否需要重復(fù)制作是否與數(shù)據(jù)源有鏈接關(guān)系多字段數(shù)據(jù)列表記錄疊加并按表名區(qū)分導(dǎo)入外部數(shù)據(jù) + SQL 語句不需要有Leo Sino HR Manage Consulting Studio16二維表多個(gè)二維表生成透視表透視表多維數(shù)據(jù)區(qū)域合并不需要有同一文件內(nèi)的報(bào)表數(shù)據(jù)匯總=sum(begin:end!B

20、2)不需要有不同文件中的報(bào)表數(shù)據(jù)匯總合并計(jì)算不需要有非標(biāo)準(zhǔn)數(shù)據(jù)表匯總建議修改原表的結(jié)構(gòu)或者僅作為終端報(bào)表，上層再建立一個(gè)數(shù)據(jù)源表作為收集數(shù)據(jù)使用需要無1.141.14 快速刪除工作表中所有的文本框快速刪除工作表中所有的文本框有時(shí)公莫名其妙地在工作表中添加許多文本框，想刪除所有文本框可以用附圖中的方法。其他插入的對(duì)象，如圖片、自選圖形等都可以用同樣的方式刪除哦。Leo Sino HR Manage Consulting Studio17Leo Sino HR Manage Consulting Studio182 函數(shù)數(shù)組函數(shù)數(shù)組2.12.1 函數(shù)寶典函數(shù)寶典函數(shù)寶典2011.11版.xls2.

21、22.2 自定義函數(shù)自定義函數(shù) 157157 個(gè)個(gè)Excel自定義函數(shù)157個(gè).xls2.32.3 vLookupvLookupvlookup函數(shù)詳解Excel_Home.xls2.42.4 OffsetOffsetOFFSET引用函數(shù)應(yīng)用_ByGdliyy.xls2.52.5 ISIS 函數(shù)函數(shù)可以檢驗(yàn)數(shù)值的類型并根據(jù)參數(shù)取值返回 TRUE 或 FALSE。函數(shù)函數(shù)如果為下面的內(nèi)容，則返回如果為下面的內(nèi)容，則返回 TRUETRUEISBLANK值為空白單元格。ISERR值為任意錯(cuò)誤值（除去 #N/A）。Leo Sino HR Manage Consulting Studio19ISERROR

22、值為任意錯(cuò)誤值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。ISLOGICAL值為邏輯值。ISNA值為錯(cuò)誤值 #N/A（值不存在）。ISNONTEXT值為不是文本的任意項(xiàng)（注意此函數(shù)在值為空白單元格時(shí)返回 T）。ISNUMBER值為數(shù)字。ISREF值為引用。ISTEXT值為文本。2.62.6 數(shù)組公式特征數(shù)組公式特征對(duì)單元格區(qū)域進(jìn)行多重計(jì)算的計(jì)算方式與普通計(jì)算公式的區(qū)別是錄入公式結(jié)束后，需要同時(shí)按下 ctrl + shift + enter ，其特征是在公式兩端會(huì)出現(xiàn)一對(duì)大括號(hào)。Leo Sino HR Manage Consulting

23、Studio202.72.7 SQLSQL 語句在語句在 EXCELEXCEL 中的典型用法中的典型用法Leo Sino HR Manage Consulting Studio213 圖表圖表3.13.1 復(fù)合餅圖復(fù)合餅圖當(dāng)數(shù)據(jù)系列內(nèi)值的大小差異較大時(shí)，可以制作復(fù)合餅圖以便數(shù)據(jù)顯示更為清楚，如下圖所示：選擇餅圖中的復(fù)合餅圖子圖表類型：點(diǎn)擊下一步，切換到圖例標(biāo)簽，將顯示圖例的勾去掉：Leo Sino HR Manage Consulting Studio22切換到數(shù)據(jù)標(biāo)志標(biāo)簽，把類別名稱和百分比打勾：選中數(shù)據(jù)系列，右鍵菜單，選擇數(shù)據(jù)系列格式：切換到選項(xiàng)標(biāo)簽，將第二繪圖區(qū)包含最后的值改為：4Leo

24、 Sino HR Manage Consulting Studio23復(fù)合餅圖制作完成。Leo Sino HR Manage Consulting Studio243.23.2 圖表組合圖表組合一個(gè)圖表中的不同數(shù)據(jù)系列可以采用不同的圖表類型顯示，如下圖：首先制作一個(gè)柱形圖：在圖表工具條上選擇數(shù)據(jù)系列 Compensation:在圖表工具條上選擇數(shù)據(jù)系列格式按鈕：Leo Sino HR Manage Consulting Studio25在數(shù)據(jù)系列格式界面上，切換到坐標(biāo)軸標(biāo)簽，將主坐標(biāo)軸改為次坐標(biāo)軸：右鍵點(diǎn)擊 Compensation,選擇圖表類型：將其圖表類型改為折線圖；用同樣的方式操作 Pr

25、oduction 系列，將其圖表類型改為面積圖；完成圖表組合的制作。下拉菜單式圖表：Leo Sino HR Manage Consulting Studio26寫入如下的公式：index 函數(shù)查找出的值由 A9 單元格來指定。制作三維餅圖，其數(shù)據(jù)區(qū)域?yàn)?A3:I3,A9:I9,需要手工選取。點(diǎn)擊“視圖”-“工具欄”-“窗體”，調(diào)出窗體工具條：在窗體工具條上選擇組合框，在餅圖上拖放出一個(gè)組合框：Leo Sino HR Manage Consulting Studio27右鍵選中組合框，在菜單中選擇“設(shè)置控件格式”：在控制標(biāo)簽中做如下設(shè)置：其中單元格鏈接為存儲(chǔ)控件選中項(xiàng)目序號(hào)的單元格。完成下拉菜單

26、式圖表的制作：Leo Sino HR Manage Consulting Studio283.33.3 甘特圖甘特圖如果想制作如下圖的甘特圖，需要以下 4 列數(shù)據(jù)：首先制作堆積條形圖：Leo Sino HR Manage Consulting Studio29點(diǎn)擊下一步，切換到系列標(biāo)簽，刪除結(jié)束日期系列：生成的條形圖如下圖所示：Leo Sino HR Manage Consulting Studio30下面轉(zhuǎn)換縱坐標(biāo)的項(xiàng)目排列次序：雙擊縱坐標(biāo)，切換到刻度，將分類次序反轉(zhuǎn)和數(shù)據(jù)軸交叉于最大分類選項(xiàng)打勾。此時(shí)縱坐標(biāo)次序已經(jīng)反轉(zhuǎn)雙擊開始日期數(shù)據(jù)系列，在數(shù)據(jù)系列格式對(duì)話框中將邊框和內(nèi)部都改選為無，此時(shí)

27、開始日期數(shù)據(jù)系列隱藏。Leo Sino HR Manage Consulting Studio31此時(shí)需要將橫坐標(biāo)開始日期改為真正的項(xiàng)目開始日期：在 excel 中，每一個(gè)日期都對(duì)應(yīng)一個(gè)數(shù)值，選中 B2,查看 2008-7-1 對(duì)應(yīng)數(shù)值為39630雙擊橫坐標(biāo)，切換到刻度，將最小值改為 39630，同樣將橫坐標(biāo)最大值改為真正的項(xiàng)目結(jié)束日期。Leo Sino HR Manage Consulting Studio32甘特圖制作完成。3.43.4 帶滾動(dòng)條的圖表帶滾動(dòng)條的圖表該圖表中可以隨著點(diǎn)擊滾動(dòng)條的動(dòng)作而動(dòng)態(tài)翻看源數(shù)據(jù)中的大量數(shù)據(jù)。定義兩個(gè)動(dòng)態(tài)引用的名稱：Period: = offset(7.動(dòng)

28、態(tài)圖表!$A$1,7.動(dòng)態(tài)圖表!$D$1,0,10,1)Data: = offset(7.動(dòng)態(tài)圖表!$B$1,7.動(dòng)態(tài)圖表!$D$1,0,10,1)制作折線圖，選擇系列標(biāo)簽，刪除 period 系列：Leo Sino HR Manage Consulting Studio33將“值”和“分類（X）軸標(biāo)志”設(shè)置為如下圖內(nèi)容：調(diào)出窗體工具條，在圖表外繪制滾動(dòng)條，右鍵選中滾動(dòng)條，選擇設(shè)置控件格式：Leo Sino HR Manage Consulting Studio34在單元格鏈接中設(shè)置為 D1:3.53.5 動(dòng)態(tài)圖表的制作動(dòng)態(tài)圖表的制作選中北京，深圳，上海以下的區(qū)域，分別定義北京，深圳，上海的名

29、稱選擇視圖-工具欄-窗體，選擇選項(xiàng)按鈕，畫出如下三個(gè)選項(xiàng)按鈕：Leo Sino HR Manage Consulting Studio35右鍵選中選項(xiàng)按鈕，在右鍵菜單中選擇設(shè)置控件格式，在跳出的對(duì)話框中選擇控制標(biāo)簽，在單元格鏈接中選擇 A16：在 A17單元格中輸入公式：=choose(a16,”北京”,”上?！?”深圳”)，為 A17定義名稱：choose在 A2,A3,A4單元格中輸入以下內(nèi)容：在 B16單元中寫入以下公式，并復(fù)制到該行1-12月的單元格中：=VLOOKUP(A3,INDIRECT(choose),COLUMN()-1,0)Leo Sino HR Manage Consul

30、ting Studio36依據(jù)此數(shù)據(jù)表制作雙曲線圖：Leo Sino HR Manage Consulting Studio374 數(shù)據(jù)分析數(shù)據(jù)分析4.14.1 數(shù)據(jù)有效性數(shù)據(jù)有效性4.1.14.1.1 防止重復(fù)錄入防止重復(fù)錄入Leo Sino HR Manage Consulting Studio384.1.24.1.2 只能輸入日期只能輸入日期Leo Sino HR Manage Consulting Studio394.1.34.1.3 輸入的內(nèi)容中必須包括某字符輸入的內(nèi)容中必須包括某字符=not(iserror(find(中國(guó),g30)4.1.44.1.4 制作二級(jí)選項(xiàng)菜單：制作二級(jí)選

31、項(xiàng)菜單：首先將一級(jí)選項(xiàng)的每個(gè)項(xiàng)目定義一個(gè)名稱，該名稱內(nèi)容包括相應(yīng)的二級(jí)項(xiàng)目；制作一級(jí)項(xiàng)目的有效性；制作二級(jí)項(xiàng)目的有效性：內(nèi)容為：=INDIRECT(g6)，其中 g6為設(shè)定了有效性的一級(jí)選項(xiàng)所在的單元格。4.24.2 整合數(shù)據(jù)表整合數(shù)據(jù)表我們經(jīng)常需要將幾個(gè)表格中的數(shù)據(jù)整合成一張表，如下圖所示，將 5 個(gè)月的數(shù)據(jù)（目前分布在 5 張表格上）整合到一個(gè)總表上：我們使用的方法是利用 office 的查詢工具 query 進(jìn)行的，這樣做的優(yōu)點(diǎn)在于今后對(duì)于新數(shù)據(jù)的更新可以自動(dòng)化刷新，而不需要重復(fù)的進(jìn)行整合操作（如復(fù)制粘貼）。office 的查詢工具 query 在典型安裝 office 時(shí)是不會(huì)被安裝的

32、，我們需要首先檢查我們的 office 是否已經(jīng)安裝了 query，方法如下：Leo Sino HR Manage Consulting Studio40打開 Excel，點(diǎn)擊【數(shù)據(jù)】菜單中【導(dǎo)入外部數(shù)據(jù)】【新建數(shù)據(jù)庫查詢】，若系統(tǒng)提示安裝，則說明該功能未被安裝，此時(shí)可能需要在光驅(qū)中插入Office 安裝光盤完成該功能的安裝。在光驅(qū)中插入安裝光盤后，安裝過程中選擇自定義安裝；點(diǎn)開 Excel 前面的加號(hào)，選中要安裝的功能前面的下拉菜單，選擇【從本機(jī)運(yùn)行】；點(diǎn)開【Office 工具】前面的加號(hào)，選中【Microsoft Query】的下拉菜單，選擇【從本機(jī)運(yùn)行】，然后執(zhí)行余下的安裝過程即可。下面

33、開始整合工作：選擇 excel files*:選擇要導(dǎo)入數(shù)據(jù)的 excel 文件：Leo Sino HR Manage Consulting Studio41出現(xiàn)選擇數(shù)據(jù)表的界面：如果以上界面提示錯(cuò)誤，點(diǎn)擊以上界面的“選項(xiàng)”按鈕，出現(xiàn)以下界面，將系統(tǒng)表打勾即可：任選一個(gè)字段到右側(cè)，點(diǎn)下一步按鈕：Leo Sino HR Manage Consulting Studio42直到以下界面出現(xiàn)，選擇第二項(xiàng)，點(diǎn)完成按鈕：此時(shí)出現(xiàn) query 程序界面，點(diǎn)擊工具條上 SQL 按鈕：Leo Sino HR Manage Consulting Studio43出現(xiàn)以下界面：在界面中將 SQL 語句改為以下：出現(xiàn)以下提示，點(diǎn)確定按鈕：Leo Sino HR Manage Consulting Studio44出現(xiàn)數(shù)據(jù)查詢結(jié)果，選文件菜單-將數(shù)據(jù)返回