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

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