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

博士學(xué)位論文./Excel操作技巧SkillsAboutExcel版本：ARev.20120820〔2012-8-20初版目錄TOC\o"1-3"\h\z1操作技巧11.1關(guān)于換行Alt+Enter11.2快速在多上合計行中求和11.3不復(fù)制隱藏的行或列11.4查找通配符21.5文本處理2等長文本的分割2不等長文本的分割2文本的合并3文本轉(zhuǎn)數(shù)值的10種辦法3把引號中的字符串".TrendsinGenetics23<4>:192–9"中的前半部分英文提取出來3使用分列更改文本為數(shù)值41.6高級篩選6如何將一列數(shù)據(jù)中在另外一列出現(xiàn)過的項目篩選出來71.7條件格式71.8數(shù)據(jù)透視表8應(yīng)用數(shù)據(jù)透視表進(jìn)行各類數(shù)據(jù)分析8同時顯示數(shù)據(jù)與百分比：10篩選數(shù)據(jù)：10顯示明細(xì)數(shù)據(jù)：11自動更新：12透視圖12多重合并計算數(shù)據(jù)131.9名稱的引用151.10自定義格式語法：161.11動態(tài)表頭：171.12行與列的合計有時出現(xiàn)不相等的情況.171.13表格的幾種類型及其處理技巧171.14快速刪除工作表中所有的文本框182函數(shù)數(shù)組202.1函數(shù)寶典202.2自定義函數(shù)157個202.3vLookup202.4Offset202.5IS函數(shù)202.6數(shù)組公式特征212.7SQL語句在EXCEL中的典型用法223圖表233.1復(fù)合餅圖233.2圖表組合263.3甘特圖303.4帶滾動條的圖表343.5動態(tài)圖表的制作364數(shù)據(jù)分析394.1數(shù)據(jù)有效性39防止重復(fù)錄入39只能輸入日期40輸入的內(nèi)容中必須包括某字符41制作二級選項菜單：414.2整合數(shù)據(jù)表414.3自定義數(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合并計算匯總多表數(shù)據(jù)494.9利用合并計算核對數(shù)值型數(shù)據(jù)505實例技巧515.1各種各樣的排名匯總515.2資產(chǎn)折舊515.3巧設(shè)單元格格式打印席卡53.操作技巧關(guān)于換行Alt+Enter同一單元格內(nèi),有些長數(shù)據(jù)或條列式內(nèi)容必須強行換行才能對齊.方法是光標(biāo)移到在需要換行的位置上同時按下Alt+Enter鍵<使用強行換行時,系統(tǒng)會同時選擇自動換行功能>.1．輸入數(shù)據(jù)隨時換行用戶若要在輸入數(shù)據(jù)時換行,只要通過Alt+Enter組合鍵即可輕松實現(xiàn).此方法同樣可使已輸入內(nèi)容的單元格在光標(biāo)所在處換行.2．單元格區(qū)域內(nèi)換行將某個長行轉(zhuǎn)成段落并在指定區(qū)域內(nèi)換行.例如：A10內(nèi)容很長,欲將其顯示在A列至C列之內(nèi),步驟是：選定區(qū)域A10:C12<先選A10>,選擇"編輯→填充→內(nèi)容重排",A10內(nèi)容就會分布在A10:C12區(qū)域中.此法特別適合用于表格內(nèi)的注釋.3．調(diào)整單元格格式換行選定單元格,選擇"格式→單元格",在彈出的對話框中單擊"對齊",選中"自動換行"復(fù)選框,單擊[確定]按鈕即可.4.錄制一個宏,定義快捷鍵快速在多上合計行中求和選擇區(qū)域按F5選擇"空值"按"Alt+="快捷鍵.不復(fù)制隱藏的行或列a首先選中需要復(fù)制的被隱藏了一些行或列的表格區(qū)域；b然后點擊"編輯"-"定位"-"定位條件",在其中選擇"可見單元格"；c復(fù)制表格區(qū)域,粘貼即可.查找通配符在Excel中,如何替換單元格中的*、?號呢？由于二者在"查找和替換"對話框中扮演通配符的角色,代表多個、單個任意字符,而要替換這兩個符號本身,需要在前面增加一個~符號,如附圖所示.同理要替換"~"符號時,也需要在其前面增加一個"~"符號.文本處理等長文本的分割從字符串的左邊取字符：=Left<字符串,文本長度>從字符串的右邊取字符：=Right<字符串,文本長度>從字符串的中間取字符：=Mid<字符串,文本起始位置,文本長度>不等長文本的分割第1步：選中要進(jìn)行分割的字符串區(qū)域.第2步：點擊"數(shù)據(jù)"－"分列",在文本分列向?qū)е羞x擇適合的分隔符.第3步：設(shè)置需要導(dǎo)入的列以及放置該列的位置.文本的合并=Concatenate<文本1,文本2,…>使用連接符&：文本轉(zhuǎn)數(shù)值的10種辦法,把引號中的字符串".TrendsinGenetics23<4>:192–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是一個包含了0到9的數(shù)字小數(shù).目的是在原數(shù)據(jù)的后面連接上"0123456789",以避免find時出錯.這是簡化公式的常用手法,與此類似作用的還有5^19.使用分列更改文本為數(shù)值如果你發(fā)現(xiàn)在Excel中輸入的公式計算結(jié)果不正確,也許是單元格中保存的數(shù)字被保存為文本類型的原因.這時需要將文本類型的數(shù)這轉(zhuǎn)為數(shù)值,方法如下：高級篩選由于自動篩選只能篩選出簡單條件的數(shù)據(jù),因此如要在復(fù)雜條件下進(jìn)行篩選就需要采用高級篩選的方式.在需要進(jìn)行篩選的數(shù)據(jù)表外設(shè)定篩選條件.在對話框中設(shè)定：列表區(qū)域為數(shù)據(jù)表區(qū)域；條件區(qū)域為剛才在數(shù)據(jù)表外部設(shè)定的條件區(qū)域,要包括字段名稱和條件所在的單元格區(qū)域.如何將一列數(shù)據(jù)中在另外一列出現(xiàn)過的項目篩選出來條件格式條件格式可以使得符合特定條件的記錄按照某種設(shè)定的格式顯示.選中要進(jìn)行條件格式設(shè)定的單元格范圍,執(zhí)行[格式][條件格式]在條件格式設(shè)置界面進(jìn)行條件的設(shè)定：數(shù)據(jù)透視表應(yīng)用數(shù)據(jù)透視表進(jìn)行各類數(shù)據(jù)分析選擇"數(shù)據(jù)"－"數(shù)據(jù)透視表和數(shù)據(jù)透視圖",進(jìn)入透視表設(shè)置向?qū)?點擊"下一步",選擇正確的數(shù)據(jù)范圍.點擊"下一步",選擇"布局"按鈕.在如下圖界面上,將右側(cè)的字段拖入左側(cè)相應(yīng)區(qū)域內(nèi).形成如下圖的布局,點擊確定按鈕.生成如下的透視表后,將鼠標(biāo)懸停在想要移動的字段上,鼠標(biāo)左鍵按下,將字段拖放到其他區(qū)域,生成自己需要的數(shù)據(jù)顯示.數(shù)據(jù)百分比顯示：右鍵菜單－字段設(shè)置－選項,將數(shù)據(jù)顯示方式改為"占同列數(shù)據(jù)總和的百分比"同時顯示數(shù)據(jù)與百分比：在[布局]里將銷售收入兩次拖入數(shù)據(jù)區(qū)域?qū)⑵渲幸粋€銷售收入改為百分比顯示在報表項目上輸入新的名稱可以修改項目名稱將報表項目拖拽到列標(biāo)題位置,可以將數(shù)值和百分比改為按列排列篩選數(shù)據(jù)：點擊字段名稱后面的下拉菜單,可以對字段內(nèi)容進(jìn)行篩選.顯示明細(xì)數(shù)據(jù)：雙擊需要查看明細(xì)的數(shù)據(jù),在新的工作表上將顯示構(gòu)成此匯總數(shù)據(jù)的所有明細(xì)數(shù)據(jù).對數(shù)據(jù)進(jìn)行排序：把光標(biāo)放在行位置的報表項目上,[右鍵菜單][字段設(shè)置],[高級]按鈕,左側(cè)可以進(jìn)行排序設(shè)置.按照日期分組：將日期放入行區(qū)域,[右鍵菜單][組及顯示明細(xì)數(shù)據(jù)][組合]在[步長]中選擇需要的分組標(biāo)準(zhǔn)按照數(shù)值分組：將銷售收入放入行區(qū)域,[右鍵菜單][組及顯示明細(xì)數(shù)據(jù)][組合]在[步長]中選擇需要的分組標(biāo)準(zhǔn)插入計算字段：在[名稱]后給計算字段定義名稱在[公式]后輸入計算字段的計算公式,可以引用下面的字段自動更新：[右鍵菜單][表格選項][打開時刷新]可以讓報表始終有更新后的數(shù)據(jù)顯示.透視圖可以單獨生成數(shù)據(jù)透視圖,也可以基于現(xiàn)有的透視表生成透視圖.如果已經(jīng)有現(xiàn)成的透視表,通過點擊數(shù)據(jù)透視表工具條上的圖表按鈕可以生成一個透視圖.通過拖動透視圖上的各個字段到右側(cè)或者底部的位置,可以方便地改變圖表組織數(shù)據(jù)的方式.隱藏透視圖字段按鈕：把光標(biāo)懸停在任意字段上,右鍵菜單,選擇[隱藏數(shù)據(jù)透視圖字段按鈕],即可以將字段按鈕隱藏；再次顯示字段按鈕：點擊透視圖工具條上第一項,在出現(xiàn)的下拉菜單中選擇[隱藏數(shù)據(jù)透視圖字段按鈕]如果有些數(shù)據(jù)無法在數(shù)據(jù)透視表內(nèi)部分析,可引用透視表的數(shù)據(jù)作為分析基礎(chǔ).多重合并計算數(shù)據(jù)如下圖格式的即為二維表：現(xiàn)在我們需要將數(shù)個格式相同的二維表匯總為一張表格,且可以區(qū)分不同表格屬性進(jìn)行分析,比如三張表分別為北京,上海,XX分公司的表格.我們使用的方法是利用透視表多重合并計算數(shù)據(jù)區(qū)域的功能.選擇數(shù)據(jù)-數(shù)據(jù)透視表和數(shù)據(jù)透視圖選擇第3個數(shù)據(jù)源類型：多重合并計算數(shù)據(jù)區(qū)域,點擊下一步按鈕：在出現(xiàn)的界面上選擇：自定義字段選擇需要被合并的表格區(qū)域,點擊添加將其添加到所有區(qū)域；將頁字段數(shù)據(jù)改為1,在項標(biāo)志處輸入該表格的標(biāo)志,使用相同的方法將其他需要合并的表格全部添加.透視表生成后如下圖所示,已經(jīng)將多個表格合并成一個：雙擊字段名,可以對字段名進(jìn)行修改：名稱的引用名稱名稱可以代表一個單元格或者一個單元格區(qū)域,或者是常量,公式.名稱的定義：選中需要命名的單元格或區(qū)域,在界面左上角名稱框中輸入名稱后回車；名稱的刪除：插入－名稱－定義,選中需要刪除的名稱點擊刪除按鈕,點確定；名稱的引用：需要引用某單元格時輸入該單元格的名稱：＝名稱第1步：選中需要命名的某個單元格或單元格區(qū)域.第2步：在左上角名稱框輸入命名后回車.刪除已有命名：選擇"插入"－"名稱"－"定義"；選中需要刪除的名稱,點擊"刪除"按鈕.名稱命名的優(yōu)點：避免絕對引用的錯誤對公式進(jìn)行文字化表述,讓公式更加容易理解可以在整個工作簿中通用,引用方便自定義格式語法：大于條件值]格式；[小于條件值]格式；[等于條件值]格式；文本格式動態(tài)表頭："ABC公司"&YEAR<NOW<>>&"年"&MONTH<NOW<>>&"月報表"行與列的合計有時出現(xiàn)不相等的情況.選擇"工具"－"選項"－"重新計算"－"以顯示精度為準(zhǔn)",選中該選項.表格的幾種類型及其處理技巧數(shù)據(jù)表類型操作目標(biāo)匯總方法是否需要重復(fù)制作是否與數(shù)據(jù)源有鏈接關(guān)系多字段數(shù)據(jù)列表記錄疊加并按表名區(qū)分導(dǎo)入外部數(shù)據(jù)+SQL語句不需要有二維表多個二維表生成透視表透視表多維數(shù)據(jù)區(qū)域合并不需要有同一文件內(nèi)的報表數(shù)據(jù)匯總=sum<begin:end!B2>不需要有不同文件中的報表數(shù)據(jù)匯總合并計算不需要有非標(biāo)準(zhǔn)數(shù)據(jù)表匯總建議修改原表的結(jié)構(gòu)或者僅作為終端報表,上層再建立一個數(shù)據(jù)源表作為收集數(shù)據(jù)使用需要無快速刪除工作表中所有的文本框有時公莫名其妙地在工作表中添加許多文本框,想刪除所有文本框可以用附圖中的方法.其他插入的對象,如圖片、自選圖形等都可以用同樣的方式刪除哦.函數(shù)數(shù)組函數(shù)寶典自定義函數(shù)157個vLookupOffsetIS函數(shù)可以檢驗數(shù)值的類型并根據(jù)參數(shù)取值返回TRUE或FALSE.函數(shù)如果為下面的內(nèi)容,則返回TRUEISBLANK值為空白單元格.ISERR值為任意錯誤值〔除去#N/A.ISERROR值為任意錯誤值〔#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!.ISLOGICAL值為邏輯值.ISNA值為錯誤值#N/A〔值不存在.ISNONTEXT值為不是文本的任意項〔注意此函數(shù)在值為空白單元格時返回T.ISNUMBER值為數(shù)字.ISREF值為引用.ISTEXT值為文本.數(shù)組公式特征對單元格區(qū)域進(jìn)行多重計算的計算方式與普通計算公式的區(qū)別是錄入公式結(jié)束后,需要同時按下ctrl+shift+enter,其特征是在公式兩端會出現(xiàn)一對大括號.SQL語句在EXCEL中的典型用法圖表復(fù)合餅圖當(dāng)數(shù)據(jù)系列內(nèi)值的大小差異較大時,可以制作復(fù)合餅圖以便數(shù)據(jù)顯示更為清楚,如下圖所示：選擇餅圖中的復(fù)合餅圖子圖表類型：點擊下一步,切換到圖例標(biāo)簽,將顯示圖例的勾去掉：切換到數(shù)據(jù)標(biāo)志標(biāo)簽,把類別名稱和百分比打勾：選中數(shù)據(jù)系列,右鍵菜單,選擇數(shù)據(jù)系列格式：切換到選項標(biāo)簽,將第二繪圖區(qū)包含最后的值改為：4復(fù)合餅圖制作完成..圖表組合一個圖表中的不同數(shù)據(jù)系列可以采用不同的圖表類型顯示,如下圖：首先制作一個柱形圖：在圖表工具條上選擇數(shù)據(jù)系列Compensation:在圖表工具條上選擇數(shù)據(jù)系列格式按鈕：在數(shù)據(jù)系列格式界面上,切換到坐標(biāo)軸標(biāo)簽,將主坐標(biāo)軸改為次坐標(biāo)軸：右鍵點擊Compensation,選擇圖表類型：將其圖表類型改為折線圖；用同樣的方式操作Production系列,將其圖表類型改為面積圖；完成圖表組合的制作.下拉菜單式圖表：寫入如下的公式：index函數(shù)查找出的值由A9單元格來指定.制作三維餅圖,其數(shù)據(jù)區(qū)域為A3:I3,A9:I9,需要手工選取.點擊"視圖"-"工具欄"-"窗體",調(diào)出窗體工具條：在窗體工具條上選擇組合框,在餅圖上拖放出一個組合框：右鍵選中組合框,在菜單中選擇"設(shè)置控件格式"：在控制標(biāo)簽中做如下設(shè)置：其中單元格鏈接為存儲控件選中項目序號的單元格.完成下拉菜單式圖表的制作：甘特圖如果想制作如下圖的甘特圖,需要以下4列數(shù)據(jù)：首先制作堆積條形圖：點擊下一步,切換到系列標(biāo)簽,刪除結(jié)束日期系列：生成的條形圖如下圖所示：下面轉(zhuǎn)換縱坐標(biāo)的項目排列次序：雙擊縱坐標(biāo),切換到刻度,將分類次序反轉(zhuǎn)和數(shù)據(jù)軸交叉于最大分類選項打勾.此時縱坐標(biāo)次序已經(jīng)反轉(zhuǎn)雙擊開始日期數(shù)據(jù)系列,在數(shù)據(jù)系列格式對話框中將邊框和內(nèi)部都改選為無,此時開始日期數(shù)據(jù)系列隱藏.此時需要將橫坐標(biāo)開始日期改為真正的項目開始日期：在excel中,每一個日期都對應(yīng)一個數(shù)值,選中B2,查看2008-7-1對應(yīng)數(shù)值為39630雙擊橫坐標(biāo),切換到刻度,將最小值改為39630,同樣將橫坐標(biāo)最大值改為真正的項目結(jié)束日期.甘特圖制作完成.帶滾動條的圖表該圖表中可以隨著點擊滾動條的動作而動態(tài)翻看源數(shù)據(jù)中的大量數(shù)據(jù).定義兩個動態(tài)引用的名稱：Period:=offset<'7.動態(tài)圖表'!$A$1,'7.動態(tài)圖表'!$D$1,0,10,1>Data:=offset<'7.動態(tài)圖表'!$B$1,'7.動態(tài)圖表'!$D$1,0,10,1>制作折線圖,選擇系列標(biāo)簽,刪除period系列：將"值"和"分類〔X軸標(biāo)志"設(shè)置為如下圖內(nèi)容：調(diào)出窗體工具條,在圖表外繪制滾動條,右鍵選中滾動條,選擇設(shè)置控件格式：在單元格鏈接中設(shè)置為D1:動態(tài)圖表的制作選中北京,XX,上海以下的區(qū)域,分別定義北京,XX,上海的名稱選擇視圖-工具欄-窗體,選擇選項按鈕,畫出如下三個選項按鈕：右鍵選中選項按鈕,在右鍵菜單中選擇設(shè)置控件格式,在跳出的對話框中選擇控制標(biāo)簽,在單元格鏈接中選擇A16：在A17單元格中輸入公式：=choose<a16,"北京","上海","XX">,為A17定義名稱：choose在A2,A3,A4單元格中輸入以下內(nèi)容：在B16單元中寫入以下公式,并復(fù)制到該行1-12月的單元格中：=VLOOKUP<A3,INDIRECT<choose>,COLUMN<>-1,0>依據(jù)此數(shù)據(jù)表制作雙曲線圖：數(shù)據(jù)分析數(shù)據(jù)有效性防止重復(fù)錄入只能輸入日期輸入的內(nèi)容中必須包括某字符=not<iserror<find<"中國",g30>>>制作二級選項菜單：首先將一級選項的每個項目定義一個名稱,該名稱內(nèi)容包括相應(yīng)的二級項目；制作一級項目的有效性；制作二級項目的有效性：內(nèi)容為：=INDIRECT<g6>,其中g(shù)6為設(shè)定了有效性的一級選項所在的單元格.整合數(shù)據(jù)表我們經(jīng)常需要將幾個表格中的數(shù)據(jù)整合成一張表,如下圖所示,將5個月的數(shù)據(jù)〔目前分布在5張表格上整合到一個總表上：我們使用的方法是利用office的查詢工具query進(jìn)行的,這樣做的優(yōu)點在于今后對于新數(shù)據(jù)的更新可以自動化刷新,而不需要重復(fù)的進(jìn)行整合操作〔如復(fù)制粘貼.office的查詢工具query在典型安裝office時是不會被安裝的,我們需要首先檢查我們的office是否已經(jīng)安裝了query,方法如下：打開Excel,點擊[數(shù)據(jù)]菜單中[導(dǎo)入外部數(shù)據(jù)]－[新建數(shù)據(jù)庫查詢],若系統(tǒng)提示安裝,則說明該功能未被安裝,此時可能需要在光驅(qū)中插入Office安裝光盤完成該功能的安裝.在光驅(qū)中插入安裝光盤后,安裝過程中選擇自定義安裝；點開Excel前面的加號,選中要安裝的功能前面的下拉菜單,選擇[從本機運行]；點開[Office工具]前面的加號,選中[MicrosoftQuery]的下拉菜單,選擇[從本機運行],然后執(zhí)行余下的安裝過程即可.下面開始整合工作：選擇exce