Excel培訓(xùn)—中級(jí)

1、Excel培訓(xùn)中級(jí)Excel中級(jí)培訓(xùn)目標(biāo)-Excel方法論n能夠熟練的運(yùn)用各種公式來完成Excel計(jì)算任務(wù)。n方便的處理Excel中的文字信息。n對(duì)Excel提供的各種功能有所了解，并且能夠靈活的在日常工作中運(yùn)用。n繪制圖表，美化圖表。中級(jí)培訓(xùn)內(nèi)容-Excel方法論1.用公式簡(jiǎn)化你的Excel工作2.了解Excel提供的各種功能3.繪制圖表輔助數(shù)據(jù)閱讀目錄1.用公式簡(jiǎn)化你的Excel工作1.公式簡(jiǎn)介2.常用的計(jì)算公式和函數(shù)3.一次鍵入和自動(dòng)填充4.引用格式5.邏輯公式6.文字處理公式7.與時(shí)間相關(guān)的公式8.其他9.選擇性粘貼2.其他功能3.圖表的制作公式和函數(shù)簡(jiǎn)介n公式和函數(shù)使得電子表格變得非

2、常有用。尤其是在處理大量數(shù)據(jù)的時(shí)候。n使用Excel中的公式來計(jì)算電子表格中的數(shù)據(jù)以得到結(jié)果。當(dāng)數(shù)據(jù)更新后，無需做額外的工作，公式將自動(dòng)更新結(jié)果。n函數(shù)可以大大加強(qiáng)公式的功能和進(jìn)行普通運(yùn)算符不能完成的計(jì)算。Excel包括300多個(gè)函數(shù)，甚至可以從第三方供應(yīng)商處購買額外的專用函數(shù)甚至可以創(chuàng)建自己的定值函數(shù)（VBA），但是通常使用的也只有十幾個(gè)函數(shù)。公式和函數(shù)簡(jiǎn)介n公式由下列幾個(gè)元素組成：q運(yùn)算符，如“+”，“*”；q單元格（或區(qū)域）引用；q數(shù)值或文本；q工作表函數(shù)，如“sum”，“average”；n例如：q=sum(A1*B1,A2*B2,A3*B3)/3n函數(shù)是可以嵌套的。n例如：q=sum

3、(average(A1：C1),average(A2:C2)必須記住的函數(shù)n常用計(jì)算公式和函數(shù)：q運(yùn)算符號(hào)：+、-、*、/、q邏輯判斷符號(hào)：=, , , =, 60,”pass”,”fail”).qIf(and(A160,A260),”all pass”, ”fail”).nIf可以與其他的命令組合使用，例如：qSumif：sumif(區(qū)域，”0”，求和區(qū)域）qCountif： countif(區(qū)域，”0”）邏輯公式nTrue False 1，0nAnd()，OR(), Not()n在Excel公式中，邏輯判斷中使用到and或or的格式是： and（ ， ）；or（ ， ）;not( )n例如

4、nIf(and(A11,B11,C11),1,0)nIf(or(A11,B11,C11),1,0)nIf(not(A11),1,0)用公式處理文本nUpper: 把文本全部轉(zhuǎn)換為大寫。nLower: 把文本全部轉(zhuǎn)換為小寫。nProper: 把文本轉(zhuǎn)換為“恰當(dāng)”形式（每一個(gè)單詞的第一個(gè)字母大寫）。nLeft:從字符串的開始返回特定數(shù)量的字符。qLeft(“Add012”,3)=“ADD”nRight:從字符串的尾部返回特定數(shù)量的字符。qRight(“Add012”,3)=012nMid:從字符串的任意位置開始返回特定數(shù)量的字符。qMid(“Add012”,2,2)=“dd”用公式處理文本nTri

5、m：除了兩個(gè)單詞之間的單個(gè)空格，可以去掉文本參數(shù)中的所有空格。nClean：從字符串中去掉不能打印的字符。當(dāng)引入某種類型數(shù)據(jù)時(shí)，常會(huì)出現(xiàn)這些“垃圾”字符。nExact：比較兩個(gè)字符串是否完全相同。nLen：返回字符串中的字符個(gè)數(shù)。nFind: 查找某個(gè)字符在文本中的位置。nConcatenate：將兩個(gè)單元格中的信息合并到一個(gè)單元格中 。 n使用Left和Find函數(shù)分割名字qLeft(A2,find(“”,A2)-1)qRight(A2,len(A2)-find(“”,A2)查找數(shù)值Vlookup,Hlookup,lookupn目的：查找一個(gè)值，返回另一個(gè)值。例如，查找姓名，返回電話號(hào)碼。n

6、查詢分成兩種：q精確查詢：查詢的數(shù)值如果不存在則輸出不存在的標(biāo)志；q不精確查詢：查詢的數(shù)值如果不存在則輸出小于此數(shù)的最大值所對(duì)應(yīng)的值。nvlookup:進(jìn)行垂直查詢qVlookup(查找的數(shù)值，查找的數(shù)列區(qū)域，返回值所在第幾列，True(False)nTrue或確省，代表不精確匹配，查找列必須為升序排列。nFalse，代表精確匹配，不需按順序排列。nHlookup：進(jìn)行水平查詢q同Vlookup相同，只不過把列操作變?yōu)樾胁僮?。nLookup:查詢qlookup(查找的數(shù)值，查找的數(shù)所在列或行，返回值所在列或行)n查找的數(shù)值所在的列必須為升序排列；n為不精確查詢。與時(shí)間相關(guān)的公式n您所看到的日期

7、與 Excel 的內(nèi)部日期表示是不同的。對(duì)您來說，2005 年 8 月 22 日是一個(gè)帶有年、月、日的日期。而對(duì) Excel 來說，2005 年 8 月 22 日是序列數(shù) 38586。n對(duì)于 Excel，凌晨 12:00 點(diǎn)則是 0，而中午 12:00 點(diǎn)則是小數(shù) 0.5。 與時(shí)間相關(guān)的公式nNow(),返回當(dāng)前的時(shí)間。nToday(),返回當(dāng)前的日期。 nA1-B1:計(jì)算兩個(gè)日期之前的天數(shù)。nYear(A1),moth(A1),day(A1),hour(A1),minute(A1),Second(A1):返回某事件表示的各細(xì)項(xiàng)。nNetworkdays(A1,B1):計(jì)算兩個(gè)日期之前的工作日

8、天數(shù)。nWorkday(A1,B1,C1)：從某日期開始，經(jīng)過指定數(shù)目的工作日之后的日期 。n 公式錯(cuò)誤n有時(shí) Excel 會(huì)由于公式錯(cuò)誤而無法進(jìn)行計(jì)算。如果出現(xiàn)這種情況，您將在單元格中看到一個(gè)錯(cuò)誤值，而不是結(jié)果。以下是三個(gè)常見的錯(cuò)誤值： q# 列的寬度不夠顯示該單元格中的內(nèi)容?？梢酝ㄟ^下列方法進(jìn)行改正：增加列寬、縮減內(nèi)容以適應(yīng)列寬或者應(yīng)用其他數(shù)字格式。q q#REF! 單元格引用無效。單元格可能被刪除或粘貼覆蓋。 q#NAME? 函數(shù)名拼寫錯(cuò)誤或者使用了 Excel 不能識(shí)別的名字。您應(yīng)該知道，帶有錯(cuò)誤值（如 #NAME?）的單元格可能顯示一個(gè)彩色的三角形。引用類型n相對(duì) 公式中的每個(gè)相對(duì)引

9、用單元格在公式被沿列或跨行復(fù)制時(shí)都自動(dòng)改變。n絕對(duì) 一個(gè)絕對(duì)單元格引用是固定的。絕對(duì)引用在您將一個(gè)公式從一個(gè)單元格復(fù)制到另一個(gè)單元格時(shí)不發(fā)生變化。絕對(duì)引用中包含美元符號(hào) ($)，如 $D$9。n混合型 混合單元格引用既可以包含一個(gè)絕對(duì)列和一個(gè)相對(duì)行，也可以包含一個(gè)絕對(duì)行和一個(gè)相對(duì)列。例如，$A1 是一個(gè)到列 A 的絕對(duì)引用和到行 1 的相對(duì)引用。給區(qū)域命名n為了方面的撰寫公式，Excel 可以給區(qū)域進(jìn)行命名q命名：n選擇區(qū)域，在名稱框內(nèi)寫入名稱即可。n或者 插入 名稱 定義，選擇區(qū)域與名稱q刪除命名：n插入 名稱 定義， 選擇名稱，然后刪除查找更多的函數(shù)1.“插入”函數(shù)”，選擇函數(shù)搜索需要的函

10、數(shù)函數(shù)分類選擇具體的函數(shù)選中函數(shù)的簡(jiǎn)易幫助選中函數(shù)的詳細(xì)幫助查找更多的函數(shù)2.填寫函數(shù)參數(shù)。填寫參數(shù)參數(shù)幫助打印公式n公式在一般情況下僅顯示結(jié)果。n也可以顯示工作表中的所有公式，然后再隱藏所有公式。n“工具”“公式審核”“公式審核模式”。要隱藏公式，再單擊“公式審核模式”。 n快捷鍵CTRL+（“1”鍵旁邊）組合鍵來顯示和隱藏公式。 靈活運(yùn)用公式的案例n1，在兩個(gè)名單里面查找匹配n2，在同一個(gè)名單里面找出重復(fù)的樣本n3，用公式完成報(bào)告n4，隨機(jī)抽選樣本目錄1.公式和函數(shù)2.其他功能其他功能1.分列分列2.高級(jí)篩選高級(jí)篩選3.條件格式條件格式4.分類匯總分類匯總5.數(shù)據(jù)有效性數(shù)據(jù)有效性3.圖表的

11、制作分列n分列可以將一條數(shù)據(jù)轉(zhuǎn)換為多條數(shù)據(jù)：n操作步驟qStep 1:”數(shù)據(jù)”分列”。qStep 2:選擇分列方式：分割符號(hào) 或者 固定寬度qStep 3:在相應(yīng)的位置填寫分列的符號(hào)，例如本例在“其他”欄中填寫“.” 。qStep 4:下一步完成，即可。n注意：分列會(huì)由一列生成多列，所以在分之前一定要預(yù)留空白位置，以防止覆蓋掉后面的數(shù)據(jù)。高級(jí)篩選n“數(shù)據(jù)區(qū)域”選擇所有的數(shù)據(jù)和表頭；n“條件區(qū)域”選擇篩選條件，如例題；n可以選擇不重復(fù)的記錄；n可以選擇將篩選結(jié)果復(fù)制到其他位置。條件格式n條件格式可以突出顯示符合條件的單元格。例如，可以通過設(shè)置使區(qū)域內(nèi)的所有負(fù)數(shù)背景顏色為紅色。n條件格式為動(dòng)態(tài)的，

12、如果把單元格寫入一個(gè)負(fù)數(shù)，既可以出現(xiàn)紅色背景，改為正數(shù)則背景消失。條件格式的使用n“格式”條件格式”，打開下列對(duì)話框。選擇滿足條件的格式選擇是數(shù)值還是公式選擇條件，如大于，小于，介于等條件限制添加其他條件，最多三個(gè)條件條件格式的使用n條件格式可以用于：q自動(dòng)標(biāo)記出錯(cuò)誤、異常、值得注意的數(shù)據(jù)q自動(dòng)描繪整體表格格式分類匯總n選擇要進(jìn)行分類匯總的單元格區(qū)域。如下列數(shù)據(jù)省份ASNAMEQ8.1_HRQ8B_HRQ8C_HRQ8D_HRQ23_HRQ24_HR安徽 鄧中亞3535.2安徽 鄧中亞4331.安徽 鄧中亞455544安徽 鄧中亞433240安徽 鄧中亞4332.安徽 鄧中亞45554.安徽

13、鄧中亞4332.安徽 鄧中亞4555.安徽 鄧中亞155551安徽 鄧中亞5555.北京 孫春淵455552北京 孫春淵455541甘肅 張志學(xué)555341甘肅 張志學(xué)4554.甘肅 張志學(xué)555545甘肅 張志學(xué)533542分類匯總n“數(shù)據(jù)”分類匯總” ，彈出如下對(duì)話框。選擇分類的標(biāo)準(zhǔn)選擇數(shù)據(jù)匯總的方式，如求和，求平均值。定需要匯總的數(shù)據(jù)列匯總結(jié)果的輸出位置分類匯總n匯總結(jié)果見右圖。n注意分類字段必須要排序。有效性nExcel的數(shù)據(jù)有效性特性在很多方面類似于條件格式特性。這個(gè)特性使用戶可以建立一定的規(guī)則，它規(guī)定可以向單元格中輸入的內(nèi)容。n例如，在某些指定單元格中只能輸入。目錄1.公式和函數(shù)2

14、.其他功能3.圖表的制作圖表的制作1.創(chuàng)建圖表創(chuàng)建圖表2.選擇合適的圖表類型選擇合適的圖表類型3.復(fù)雜圖表制作復(fù)雜圖表制作4.選擇具有專業(yè)外觀的圖表選擇具有專業(yè)外觀的圖表創(chuàng)建圖表n圖表可以分為內(nèi)嵌圖表和獨(dú)立圖表兩種。q嵌入圖表是指該圖表作為“對(duì)象”與數(shù)據(jù)放置在同一張工作表上。它可以移動(dòng)和調(diào)整大小。它還可以與源數(shù)據(jù)一起打印。q獨(dú)立圖表為單獨(dú)的一頁，可以直接打印n創(chuàng)建圖表的兩種方法q1用圖表向?qū)?chuàng)建圖表q2用“圖表”工具欄創(chuàng)建簡(jiǎn)單的圖表使用圖表向?qū)?chuàng)建圖表n圖表用于將數(shù)據(jù)轉(zhuǎn)化成圖片。圖表向?qū)У谝徊絊tep1：選擇希望插入圖表中的數(shù)據(jù)，同時(shí)選擇列標(biāo)題和行標(biāo)題 。Step2：?jiǎn)螕艄ぞ邫谏系摹皥D表向?qū)А?/p>

15、 按鈕打開“圖表向?qū)А薄tep3：當(dāng)該向?qū)Т蜷_時(shí)，會(huì)自動(dòng)選擇柱形圖類型。您可以方便地選擇其他圖表類型 。Step4：接下來，您可以單擊向?qū)У撞康摹巴瓿伞卑粹o 。 圖表向?qū)У诙絥可以通過選擇系列產(chǎn)生在“行”或“列”，來選擇數(shù)據(jù)比較的方式。“系列”選項(xiàng)卡n在該選項(xiàng)卡上，您可以為圖表刪除或添加數(shù)據(jù)系列。例如，您可能決定只將其中兩個(gè)月的數(shù)據(jù)插入圖表，而不是將您在工作表上選擇的所有三個(gè)月的數(shù)據(jù)插入圖表。通過該選項(xiàng)卡可以進(jìn)行更改，而無須返回到工作表，并且可以預(yù)覽所做的更改。n注意注意 在該選項(xiàng)卡上刪除或添加數(shù)據(jù)系列時(shí)，并不會(huì)改變工作表上的數(shù)據(jù)。圖表向?qū)У谌絥為圖表添加說明性的文字是一個(gè)好習(xí)慣。n“標(biāo)

16、題” 選項(xiàng)卡上有三個(gè)文本框，對(duì)應(yīng)于該圖表的三個(gè)標(biāo)題：一個(gè)對(duì)應(yīng)于圖表標(biāo)題，位于最上端；另外兩個(gè)分別對(duì)應(yīng)于圖表的兩個(gè)坐標(biāo)軸（水平和垂直）。在輸入標(biāo)題后，它們將出現(xiàn)在該選項(xiàng)卡的預(yù)覽中。 圖表向?qū)У谌絥“圖表向?qū)А敝羞€有更多的選項(xiàng)卡， q坐標(biāo)軸坐標(biāo)軸 您可以在此處隱藏或顯示沿坐標(biāo)軸顯示的信息。 q網(wǎng)格線網(wǎng)格線 您可以在此處隱藏或顯示貫穿圖表的線條。 q圖例圖例 您可以在此處將圖表圖例放置于圖表的不同位置。 q數(shù)據(jù)標(biāo)志數(shù)據(jù)標(biāo)志 您可以在此處使用各個(gè)值的行和列標(biāo)題（以及數(shù)值本身）為圖表加上標(biāo)簽。請(qǐng)小心操作，因?yàn)楹苋菀资箞D表變得混亂并且難于閱讀。 q數(shù)據(jù)表數(shù)據(jù)表 您可以在此處顯示包含用于創(chuàng)建圖表的所有數(shù)據(jù)

17、的表。如果您要將圖表放置于工作簿中的獨(dú)立工作表上，并且希望通過圖表查看數(shù)據(jù)，您可能需要這么做。接下來是下一個(gè)。 圖表向?qū)У谒牟絥選擇“作為新工作表輸入”，則單獨(dú)為新的一頁工作表，您可以為其選擇標(biāo)題。n選擇“作為其中的對(duì)象插入”，它將與用于創(chuàng)建它的工作表數(shù)據(jù)在同一個(gè)工作表中。n在圖表向?qū)魏我徊街?，都可以點(diǎn)擊“完成”來結(jié)束圖標(biāo)的制作。 選擇正確的圖表類型n選擇最有效的圖表類型將使您的數(shù)據(jù)更清晰、更有力，同時(shí)能夠表達(dá)更多信息。Microsoft Office Excel 提供了很多種圖表類型以及用于選擇和預(yù)覽它們的簡(jiǎn)單方法。選擇正確的圖表類型CorrelationCorrelation關(guān)關(guān) 連連

18、性性 FrequencyFrequency頻頻 率率 Time series Time series 時(shí)時(shí) 間間 順序順序 ItemItem項(xiàng)項(xiàng) 目目 componentcomponent成成 分分 PiePie圓圓 型型 BarBar橫橫 條條 型型 ColumnColumn直直 條條 型型 LineLine線線 型型 DotDot點(diǎn)點(diǎn) 型型 基基 本本 圖圖 表表 對(duì)比對(duì)比 創(chuàng)建組合圖表創(chuàng)建組合圖表n有時(shí)，一種圖表類型最適合于一部分?jǐn)?shù)據(jù)，而另一種圖表類型最適合于其余數(shù)據(jù)。您不必為此左右為難，因?yàn)槔?Excel，您可以將兩種或更多類型的圖表組合在單個(gè)圖表中。 創(chuàng)建組合圖表創(chuàng)建組合圖表n右鍵

19、單擊其中一個(gè)白色柱形。 n單擊快捷菜單上的“圖表類型”。 n在“圖表類型”列表中，單擊“折線圖”。在“子圖表類型”區(qū)域中，單擊中間行中的第一個(gè)圖表，然后單擊“確定”。 創(chuàng)建次坐標(biāo)軸 n有時(shí)，組合圖表中的不同數(shù)據(jù)集之間的差異如此巨大，以至于有的數(shù)據(jù)系列無法清楚的表現(xiàn)，例如一組數(shù)據(jù)為數(shù)值，而另外一組為百分比。n您可以通過添加另一個(gè)（次）圖表坐標(biāo)軸來解決這個(gè)問題。 創(chuàng)建次坐標(biāo)軸n單擊圖表區(qū)域外部的單元格選中該圖表（在它的四個(gè)角上將顯示黑色正方形。 n右鍵單擊圖表底部附近的“傭金”折線，然后單擊“數(shù)據(jù)系列格式”。（如果在菜單上看到的是“數(shù)據(jù)點(diǎn)格式”，表明您選擇了折線上的某個(gè)點(diǎn)而不是整個(gè)數(shù)據(jù)系列。在這種情況下，請(qǐng)單擊空白圖表區(qū)域并重試。） n單擊“坐標(biāo)軸”選項(xiàng)卡，單擊“次坐標(biāo)軸”，然后單擊“確定”。 將“列表”功能用于圖表 n無論您何時(shí)更改工作表數(shù)據(jù)，Excel 都會(huì)立即更新任何使用該數(shù)據(jù)的圖表。但如果您將一個(gè)完整的新數(shù)據(jù)行添加到工作表中，會(huì)發(fā)生什么現(xiàn)象呢？圖表不會(huì)更改。除非該圖表使用的數(shù)據(jù)已經(jīng)應(yīng)用了“列表”功能。n“列表”功能是 Excel 2003 中新增的一種使圖表保持最新的方法。n使用 Excel 2003“數(shù)據(jù)”菜單上的“列表”命令將您的