Excel中級培訓(xùn)教程

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

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

3、B1,A2*B2,A3*B3)/3n函數(shù)是可以嵌套的。n例如：q=sum(average(A1：C1),average(A2:C2)必須記住的函數(shù)n常用計算公式和函數(shù)：q運算符號：+、-、*、/、q邏輯判斷符號：=, , , =, 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公式中，邏輯判斷中使用到an

4、d或or的格式是： and（ ， ）；or（ ， ）;not( )n例如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)”形式（每一個單詞的第一個字母大寫）。nLeft:從字符串的開始返回特定數(shù)量的字符。qLeft(“Add012”,3)=“ADD”nRight:從字符串的尾部返回特定數(shù)量的字符。qRight(“Add012”,3)=012nMid:從字符串的任意位置開始返回特定數(shù)量的字

5、符。qMid(“Add012”,2,2)=“dd”用公式處理文本nTrim：除了兩個單詞之間的單個空格，可以去掉文本參數(shù)中的所有空格。nClean：從字符串中去掉不能打印的字符。當(dāng)引入某種類型數(shù)據(jù)時，常會出現(xiàn)這些“垃圾”字符。nExact：比較兩個字符串是否完全相同。nLen：返回字符串中的字符個數(shù)。nFind: 查找某個字符在文本中的位置。nConcatenate：將兩個單元格中的信息合并到一個單元格中 。 n使用Left和Find函數(shù)分割名字qLeft(A2,find(“”,A2)-1)qRight(A2,len(A2)-find(“”,A2)查找數(shù)值Vlookup,Hlookup,loo

6、kupn目的：查找一個值，返回另一個值。例如，查找姓名，返回電話號碼。n查詢分成兩種：q精確查詢：查詢的數(shù)值如果不存在則輸出不存在的標(biāo)志；q不精確查詢：查詢的數(shù)值如果不存在則輸出小于此數(shù)的最大值所對應(yīng)的值。nvlookup:進行垂直查詢qVlookup(查找的數(shù)值，查找的數(shù)列區(qū)域，返回值所在第幾列，True(False)nTrue或確省，代表不精確匹配，查找列必須為升序排列。nFalse，代表精確匹配，不需按順序排列。nHlookup：進行水平查詢q同Vlookup相同，只不過把列操作變?yōu)樾胁僮鳌Lookup:查詢qlookup(查找的數(shù)值，查找的數(shù)所在列或行，返回值所在列或行)n查找的數(shù)值

7、所在的列必須為升序排列；n為不精確查詢。與時間相關(guān)的公式n您所看到的日期與 Excel 的內(nèi)部日期表示是不同的。對您來說，2005 年 8 月 22 日是一個帶有年、月、日的日期。而對 Excel 來說，2005 年 8 月 22 日是序列數(shù) 38586。n對于 Excel，凌晨 12:00 點則是 0，而中午 12:00 點則是小數(shù) 0.5。 與時間相關(guān)的公式nNow(),返回當(dāng)前的時間。nToday(),返回當(dāng)前的日期。 nA1-B1:計算兩個日期之前的天數(shù)。nYear(A1),moth(A1),day(A1),hour(A1),minute(A1),Second(A1):返回某事件表示的

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

9、?）的單元格可能顯示一個彩色的三角形。引用類型n相對 公式中的每個相對引用單元格在公式被沿列或跨行復(fù)制時都自動改變。n絕對 一個絕對單元格引用是固定的。絕對引用在您將一個公式從一個單元格復(fù)制到另一個單元格時不發(fā)生變化。絕對引用中包含美元符號 ($)，如 $D$9。n混合型 混合單元格引用既可以包含一個絕對列和一個相對行，也可以包含一個絕對行和一個相對列。例如，$A1 是一個到列 A 的絕對引用和到行 1 的相對引用。給區(qū)域命名n為了方面的撰寫公式，Excel 可以給區(qū)域進行命名q命名：n選擇區(qū)域，在名稱框內(nèi)寫入名稱即可。n或者 插入 名稱 定義，選擇區(qū)域與名稱q刪除命名：n插入 名稱 定義，

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

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

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

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

14、格中輸入的內(nèi)容。n例如，在某些指定單元格中只能輸入。目錄1.公式和函數(shù)2.其他功能3.圖表的制作圖表的制作1.創(chuàng)建圖表創(chuàng)建圖表2.選擇合適的圖表類型選擇合適的圖表類型3.復(fù)雜圖表制作復(fù)雜圖表制作4.選擇具有專業(yè)外觀的圖表選擇具有專業(yè)外觀的圖表創(chuàng)建圖表n圖表可以分為內(nèi)嵌圖表和獨立圖表兩種。q嵌入圖表是指該圖表作為“對象”與數(shù)據(jù)放置在同一張工作表上。它可以移動和調(diào)整大小。它還可以與源數(shù)據(jù)一起打印。q獨立圖表為單獨的一頁，可以直接打印n創(chuàng)建圖表的兩種方法q1用圖表向?qū)?chuàng)建圖表q2用“圖表”工具欄創(chuàng)建簡單的圖表使用圖表向?qū)?chuàng)建圖表n圖表用于將數(shù)據(jù)轉(zhuǎn)化成圖片。圖表向?qū)У谝徊絊tep1：選擇希望插入圖表中

15、的數(shù)據(jù)，同時選擇列標(biāo)題和行標(biāo)題 。Step2：單擊工具欄上的“圖表向?qū)А?按鈕打開“圖表向?qū)А?。Step3：當(dāng)該向?qū)Т蜷_時，會自動選擇柱形圖類型。您可以方便地選擇其他圖表類型 。Step4：接下來，您可以單擊向?qū)У撞康摹巴瓿伞卑粹o 。 圖表向?qū)У诙絥可以通過選擇系列產(chǎn)生在“行”或“列”，來選擇數(shù)據(jù)比較的方式?！跋盗小边x項卡n在該選項卡上，您可以為圖表刪除或添加數(shù)據(jù)系列。例如，您可能決定只將其中兩個月的數(shù)據(jù)插入圖表，而不是將您在工作表上選擇的所有三個月的數(shù)據(jù)插入圖表。通過該選項卡可以進行更改，而無須返回到工作表，并且可以預(yù)覽所做的更改。n注意注意 在該選項卡上刪除或添加數(shù)據(jù)系列時，并不會改變工

16、作表上的數(shù)據(jù)。圖表向?qū)У谌絥為圖表添加說明性的文字是一個好習(xí)慣。n“標(biāo)題” 選項卡上有三個文本框，對應(yīng)于該圖表的三個標(biāo)題：一個對應(yīng)于圖表標(biāo)題，位于最上端；另外兩個分別對應(yīng)于圖表的兩個坐標(biāo)軸（水平和垂直）。在輸入標(biāo)題后，它們將出現(xiàn)在該選項卡的預(yù)覽中。 圖表向?qū)У谌絥“圖表向?qū)А敝羞€有更多的選項卡， q坐標(biāo)軸坐標(biāo)軸 您可以在此處隱藏或顯示沿坐標(biāo)軸顯示的信息。 q網(wǎng)格線網(wǎng)格線 您可以在此處隱藏或顯示貫穿圖表的線條。 q圖例圖例 您可以在此處將圖表圖例放置于圖表的不同位置。 q數(shù)據(jù)標(biāo)志數(shù)據(jù)標(biāo)志 您可以在此處使用各個值的行和列標(biāo)題（以及數(shù)值本身）為圖表加上標(biāo)簽。請小心操作，因為很容易使圖表變得混亂并

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

18、擇正確的圖表類型CorrelationCorrelation關(guān)關(guān) 連連 性性 FrequencyFrequency頻頻 率率 Time series Time series 時時 間間 順序順序 ItemItem項項 目目 componentcomponent成成 分分 PiePie圓圓 型型 BarBar橫橫 條條 型型 ColumnColumn直直 條條 型型 LineLine線線 型型 DotDot點點 型型 基基 本本 圖圖 表表 對比對比 創(chuàng)建組合圖表創(chuàng)建組合圖表n有時，一種圖表類型最適合于一部分?jǐn)?shù)據(jù)，而另一種圖表類型最適合于其余數(shù)據(jù)。您不必為此左右為難，因為利用 Excel，您可以

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