計算機辦公軟件Excel基礎(chǔ)培訓(xùn)ppt課件

1、Excel基礎(chǔ)培訓(xùn)基礎(chǔ)培訓(xùn)主講主講: 肖蓉暉肖蓉暉陜西汽車集團溫州云頂汽車有限公司陜西汽車集團溫州云頂汽車有限公司培訓(xùn)內(nèi)容培訓(xùn)內(nèi)容u 本專題旨在讓同事們掌握EXCEL在日常工作中的經(jīng)常用到的一些功能及技巧，提高Excel實際操作能力的同時提高工作效率 。常用菜單命令的使用常用菜單命令的使用 1常用函數(shù)的使用方法常用函數(shù)的使用方法 233 53 46掌握并熟練運用掌握并熟練運用Excel常用技巧常用技巧數(shù)據(jù)透視表的應(yīng)用數(shù)據(jù)透視表的應(yīng)用圖表的應(yīng)用圖表的應(yīng)用常用菜單命令的使用常用菜單命令的使用v 1. 文件菜單v 1.1 頁面設(shè)置v 2. 編輯菜單v 2.1選擇性粘貼v 2.2. 定位v 3. 插入

2、菜單v 4. 格式菜單v 4.1 隱藏工作表v 4.2 條件格式v 4.3 表格樣式常用菜單命令的使用常用菜單命令的使用v 5. 工具菜單v 5.1 自動更正v 5.2 選項v 6.數(shù)據(jù)菜單v 6.1 分類匯總、有效性、分列、合并計算v 6.2 數(shù)據(jù)透視表函數(shù)和公式函數(shù)和公式1、什么是函數(shù)Excel函數(shù)即是預(yù)先定義，執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。2、什么是公式函數(shù)與公式既有區(qū)別又互相聯(lián)系。如果說前者是Excel預(yù)先定義好的特殊公式，后者就是由用戶自行設(shè)計對工作表進行計算和處理的公式。公式是以“=”號為引導(dǎo)，通過運算符按照一定的順序組合進行數(shù)據(jù)運算處理的等式，函數(shù)則是按特定算法進行計算

3、的產(chǎn)生一個或一組結(jié)果的預(yù)定義特殊公式。 在Excel2019中，函數(shù)共分為11個大類，函數(shù)與公式技巧比較熟練的用戶在面對特定的問題時總是會自覺地想到所要使用的特定函數(shù)所在分類。常用函數(shù)常用函數(shù)1、MAX用途：返回數(shù)據(jù)集中的最大數(shù)值。語法：MAX(number1，number2，.)參數(shù)：Number1，number2，.是需要找出最大數(shù)值的1至30個數(shù)值。2、MIN用途：返回給定參數(shù)表中的最小值。語法：MIN(number1，number2，.)。參數(shù)：Number1，number2，.是要從中找出最小值的1到30個數(shù)字參數(shù)。常用函數(shù)常用函數(shù)3、LEFT或LEFTB用途：根據(jù)指定的字符數(shù)返回文

4、本串中的第一個或前幾個字符。此函數(shù)用于雙字節(jié)字符。語法：LEFT(text，num_chars)或LEFTB(text，num_bytes)。參數(shù)：Text是包含要提取字符的文本串；Num_chars指定函數(shù)要提取的字符數(shù)，它必須大于或等于0。Num_bytes按字節(jié)數(shù)指定由LEFTB提取的字符數(shù)。實例：如果A1=國投羅鉀，則LEFT(A1，2)前往“國投”，LEFTB(A1，2)前往“國”。常用函數(shù)常用函數(shù)4、LEN或LENB用途：LEN返回文本串的字符數(shù)。LENB返回文本串中所有字符的字節(jié)數(shù)。語法：LEN(text)或LENB(text)。參數(shù)：Text待要查找其長度的文本。留意：此函數(shù)用

5、于雙字節(jié)字符，且空格也將作為字符進行統(tǒng)計。實例：如果A1=國投羅鉀，則公式“=LEN(A1)”返回4，=LENB(A1)返回8。常用函數(shù)常用函數(shù)5、MID或MIDB用途：MID返回文本串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。MIDB返回文本串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。MIDB函數(shù)可以用于雙字節(jié)字符。語法：MID(text，start_num，num_chars)或MIDB(text，start_num，num_bytes)。參數(shù)：Text是包含要提取字符的文本串。Start_num是文本中要提取的第一個字符的位置，文本中第一個字符的start_num為1

6、，以此類推；Num_chars指定希望MID從文本中返回字符的個數(shù)；Num_bytes指定希望MIDB從文本中按字節(jié)返回字符的個數(shù)。實例：如果A1=國投羅鉀，則公式“=MID(A1，3，2)”前往“羅鉀”，=MIDB(A1，3，2)前往“投”。常用函數(shù)常用函數(shù)6、RIGHT或RIGHTB用途：RIGHT根據(jù)所指定的字符數(shù)返回文本串中最后一個或多個字符。RIGHTB根據(jù)所指定的字節(jié)數(shù)返回文本串中最后一個或多個字符。語法：RIGHT(text，num_chars)，RIGHTB(text，num_bytes)。參數(shù)：Text是包含要提取字符的文本串；Num_chars指定希望RIGHT提取的字符數(shù)

7、，它必須大于或等于0。假設(shè)num_chars大于文本長度，則RIGHT返回所有文本。如果忽略num_chars，則假定其為1。Num_bytes指定欲提取字符的字節(jié)數(shù)。實例：如果A1=國投羅鉀，則公式“=RIGHT(A1，2)”前往“羅鉀”，=RIGHTB(A1，2)前往“鉀”。常用函數(shù)常用函數(shù)7、TEXT用途：將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本。語法：TEXT(value，format_text)。參數(shù)：Value是數(shù)值、計算結(jié)果是數(shù)值的公式、或?qū)?shù)值單元格的引用；Format_text是所要選用的文本型數(shù)字格式，即“單元格格式對話框“數(shù)字選項卡的“分類列表框中顯示的格式，它不能包含星號“

8、*”。留意：運用“單元格格式對話框的“數(shù)字選項卡設(shè)置單元格格式，只會改變單元格的格式而不會影響其中的數(shù)值。使用函數(shù)TEXT可以將數(shù)值轉(zhuǎn)換為帶格式的文本，而其結(jié)果將不再作為數(shù)字參與計算。實例：如果A1=2986.638，則公式“=TEXT(A1，#，#0.00)”返回2，986.64。常用函數(shù)常用函數(shù)8、DATEDIF函數(shù) 函數(shù)名稱：DATEDIF 主要功能：計算返回兩個日期參數(shù)的差值。 使用格式：=DATEDIF(date1,date2,y)、=DATEDIF(date1,date2,m)、=DATEDIF(date1,date2,d) 參數(shù)說明：date1代表前面一個日期，date2代表后面

9、一個日期；ym、d要求返回兩個日期相差的年月、天數(shù)。 應(yīng)用舉例：在C23單元格中輸入公式：=DATEDIF(A23,TODAY(),y)，確認后返回系統(tǒng)當(dāng)前日期用TODAY()表示與A23單元格中日期的差值，并返回相差的年數(shù)。 特別提醒：這是Excel中的一個隱藏函數(shù)，在函數(shù)向?qū)е惺钦也坏降?，可以直接輸入使用，對于計算年齡、工齡等非常有效。常用函數(shù)常用函數(shù)9、LOOKUP用途：返回向量(單行區(qū)域或單列區(qū)域)或數(shù)組中的數(shù)值。該函數(shù)有兩種語法形式：向量和數(shù)組，其向量形式是在單行區(qū)域或單列區(qū)域(向量)中查找數(shù)值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的數(shù)值；其數(shù)組形式在數(shù)組的第一行或第一列查找指

10、定的數(shù)值，然后返回數(shù)組的最后一行或最后一列中相同位置的數(shù)值。語法1(向量形式)：LOOKUP(lookup_value，lookup_vector，result_vector)常用函數(shù)常用函數(shù)語法2(數(shù)組形式)：LOOKUP(lookup_value，array)。參數(shù)1(向量形式)：Lookup_value為函數(shù)LOOKUP在第一個向量中所要查找的數(shù)值。Lookup_value可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。Lookup_vector為只包含一行或一列的區(qū)域。Lookup_vector的數(shù)值可以為文本、數(shù)字或邏輯值。參數(shù)2(數(shù)組形式)：Lookup_value為函數(shù)LOOKU

11、P在數(shù)組中所要查找的數(shù)值。Lookup_value可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。如果函數(shù)LOOKUP找不到lookup_value，則使用數(shù)組中小于或等于lookup_value的最大數(shù)值。Array為包含文本、數(shù)字或邏輯值的單元格區(qū)域，它的值用于與lookup_value進行比較。留意：Lookup_vector的數(shù)值必須按升序排列，否則LOOKUP函數(shù)不能返回正確的結(jié)果，參數(shù)中的文本不區(qū)分大小寫。lookup函數(shù)實例函數(shù)實例常用函數(shù)常用函數(shù)10、COUNTIF用途：統(tǒng)計某一區(qū)域中符合條件的單元格數(shù)目。語法：COUNTIF(range，criteria)參數(shù)：range為需

12、要統(tǒng)計的符合條件的單元格數(shù)目的區(qū)域；Criteria為參與計算的單元格條件，其形式可以為數(shù)字、表達式或文本(如36、160和男等)。其中數(shù)字可以直接寫入，表達式和文本必須加引號。實例：假設(shè)A1:A5區(qū)域內(nèi)存放的文本分別為女、男、女、男、女，則公式“=COUNTIF(A1:A5，女)”返回3。常用函數(shù)常用函數(shù)11、IF函數(shù)：“=IF()”是條件函數(shù)，最多可套嵌7層。其語法是：=IF(logical_test,value_if_true,value_if_false)=IF(第1個條件，滿足該條件返回值，IF(第2個條件，滿足該條件的返回值.IF(最后一個條件，滿足該條件的返回值，不滿足所有前列條

13、件的返回值)。12、VLOOKUP函數(shù)：VLOOKUP(lookup_value,table_array,col_index_num,rangelookup) VLOOKUP查找值，區(qū)域，列序號，邏輯值）“查找值”：為需要在數(shù)組第一列中查找的數(shù)值，它可以是數(shù)值、引用或文字符串?！皡^(qū)域”：數(shù)組所在的區(qū)域，如“B2:E10”，也可以使用對區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫或數(shù)據(jù)清單。常用函數(shù)常用函數(shù)“列序號”：即希望區(qū)域數(shù)組中待返回的匹配值的列序號，為1時，返回第一列中的數(shù)值，為2時，返回第二列中的數(shù)值，以此類推；若列序號小于1，函數(shù)VLOOKUP 返回錯誤值 #VALUE!；如果大于區(qū)域的列數(shù)，函

14、數(shù)VLOOKUP返回錯誤值 #REF!?！斑壿嬛怠保簽門RUE或FALSE。它指明函數(shù) VLOOKUP 返回時是精確匹配還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于“查找值的最大數(shù)值；假設(shè)“邏輯值為FALSE，函數(shù) VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值 #N/A。假設(shè)“查找值為文本時，“邏輯值一般應(yīng)為 FALSE 。逆向查詢語法：=VLOOKUP(查找值,IF(1,0,查找區(qū)域,目標區(qū)域),2,0)1、判斷的條件2、跟蹤數(shù)據(jù)的區(qū)域3、返回第幾列的數(shù)據(jù)4、是否精確匹配例子：=vlookup(a2,sheet1!$a$2

15、:$f$100,6,true) 常用函數(shù)常用函數(shù)13、MATCH用途：返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。如果需要找出匹配元素的位置而不是匹配元素本身，則應(yīng)該使用MATCH函數(shù)。語法：MATCH(lookup_value，lookup_array，match_type)。參數(shù)：Lookup_value為需要在數(shù)據(jù)表中查找的數(shù)值，它可以是數(shù)值(或數(shù)字、文本或邏輯值)、對數(shù)字、文本或邏輯值的單元格引用。Lookup_array是可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域，Lookup_array可以是數(shù)組或數(shù)組引用；常用函數(shù)常用函數(shù)Match_type為數(shù)字-1、0或1，它說明Exc

16、el如何在lookup_array中查找lookup_value。如果match_type為1，函數(shù)MATCH查找小于或等于lookup_value的最大數(shù)值。如果match_type為0，函數(shù)MATCH查找等于lookup_value的第一個數(shù)值。如果match_type為-1，函數(shù)MATCH查找大于或等于lookup_value的最小數(shù)值。留意：MATCH函數(shù)返回lookup_array中目標值的位置，而不是數(shù)值本身比如：有一列數(shù)據(jù)：A1:A7 分別時 A B C D E F G ，B1的值為F這個時候D1輸入=match(b1,a1:a7,0) 返回值就是6(F在A1:A7中的行號）14

17、、SUMIF函數(shù)：用途：根據(jù)指定條件對若干單元格、區(qū)域或引用求和。語法：SUMIF(range，criteria，sum_range)=sumif(條件區(qū),條件,求和區(qū))參數(shù)：Range為用于條件判斷的單元格區(qū)域，Criteria是由數(shù)字、邏輯表達式等組成的判定條件，Sum_range為需要求和的單元格、區(qū)域或引用。常用函數(shù)常用函數(shù)15、SUMPRODUCT函數(shù)：用途：1、（常規(guī)應(yīng)用在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和。 2、（變通應(yīng)用多條件求和。語法：SUMPRODUCT(array1，array2，array3，.)參數(shù)：Array1，array2，array3，.為

18、2至30個數(shù)組，其相應(yīng)元素需要進行相乘并求和。1 數(shù)量 單價2 1233 2454 478銷售總額=SUMPRODUCT(A2:A6,B2:B6)=662EXCEL日常操作中的常用技巧日常操作中的常用技巧 大量的數(shù)據(jù)輸入是一件比較繁瑣的工作，除了提高輸入的速度外，還可以使用Excel提供的幾種用于輔助輸入的方法：1、不連續(xù)單元格填充同一數(shù)據(jù)：選中一個單元格，按住Ctrl鍵，用鼠標單擊其他單元格，就將這些單元格全部都選中了。2、快速輸入的技巧：輸入范圍的選定，Ctrl+單元格；如果需要輸入的文本和上一個單元格的文本相同，直接按下“CtrlD或R）”鍵就可以完成輸入，其中“Ctrl+D是向下填充，

19、“Ctrl+R是向右填充。如果多個單元格需要輸入同樣的文本，我們可以在按住Ctrl鍵的同時，用鼠標點擊需要輸入同樣文本的所有單元格，然后輸入該文本，再按下“Ctrl+Enter鍵即可，右鍵下拉列表選項快速錄入。EXCEL日常操作中的常用技巧日常操作中的常用技巧3、單元格內(nèi)容的合并：方法一：連字符“&”合并， 方法二：利用CONCATENATE函數(shù)。=CONCATENATE(B2,C2)4、單元格內(nèi)容的分列：方法一：工具菜單數(shù)據(jù)-分列），方法二：LEFT、RIGHT函數(shù)。例如aaa-111 B1=leftA1,3C1=RIGHT(A1,3)5、格式：自定義格式只改變數(shù)據(jù)的顯示外觀，并不改變數(shù)據(jù)的

20、值，也就是說不影響數(shù)據(jù)的計算。靈活運用好自定義格式功能，將會給實際工作帶來很大的方便。EXCEL日常操作中的常用技巧日常操作中的常用技巧6、單元格錯誤信息函數(shù)處理：EXCEL日常操作中的常用技巧日常操作中的常用技巧7、數(shù)字、文本的快速轉(zhuǎn)換：數(shù)字轉(zhuǎn)變?yōu)槲谋靖袷饺缟矸葑C號數(shù)字前加“”，文本格式轉(zhuǎn)變?yōu)閿?shù)字格式：（1、選擇性粘貼，2、函數(shù)：VALUE(TEXT) ）。8、建立分類下拉列表：1、方法一2、方法二分級菜單INDIRECT()運用）9、“m2”、“m3”、“C快速輸入：ALT+178、179、186。10、禁止復(fù)制隱藏行或列中的數(shù)據(jù)：選中要復(fù)制的區(qū)域，單擊“編輯定位命令，在打開的對話框中點擊

21、“定位條件按鈕，選中“可見單元格選項，然后復(fù)制粘貼。EXCEL日常操作中的常用技巧日常操作中的常用技巧11、避開EXCEL誤差：1、利用Round函數(shù)對中間值四舍五入2、正確設(shè)置Excel選項來避開“誤差”。上述函數(shù)方法雖然方便，不過遇上大型Excel表格時，如果每個單元格都要設(shè)置這個函數(shù)，工作量未免太巨大，這時我們就可以進入Excel的工具菜單選項重新計算，在“工作簿選項處勾選“以顯示精度為準”，上述Excel“誤差也可有效避免，而默認情況下這個小選項是沒有被勾選的12、統(tǒng)計不重復(fù)數(shù)據(jù)個數(shù)：=SUM(1/COUNTIF(A1:A12,A1:A12)然后按下ctrl+shift+enter鍵可

22、在選中單元格里看到統(tǒng)計值13、分級菜單 插入-稱號-指定,選擇首行然后返回B1數(shù)據(jù)有效性-序列在來源中輸入=INDIRECT(A1)EXCEL日常操作中的常用技巧日常操作中的常用技巧v 在Excel中錄入數(shù)據(jù)時，有時會要求某列單元格中的數(shù)據(jù)具有唯一性，例如身份證號碼、發(fā)票號碼之類的數(shù)據(jù)。但是我應(yīng)該如何做才能確保不會輸入重復(fù)一樣的數(shù)據(jù)呢？ v 【答】：為了保證數(shù)據(jù)的唯一性，我們可以這樣做：選定目標單元格區(qū)域這里假設(shè)為A1:A10），依次單擊“數(shù)據(jù)有效性”，翻開“數(shù)據(jù)有效性對話框。在“設(shè)置選項卡中單擊“允許下拉列表，選擇“自定義”。然后，在“公式中輸入“=countif($A$1:$A$10,$A

23、1)=1”（不包括引號）。接著，單擊切換到“出錯警告選項卡，在“款式中選擇“停頓”，然后分別在“標題和“錯誤信息中輸入錯誤提示標題和信息。設(shè)置完畢后單擊“確定退出。此時，我們再在目標單元格錄入數(shù)據(jù)時，Excel就會自動對數(shù)據(jù)的唯一性進行校驗。當(dāng)出現(xiàn)重復(fù)數(shù)據(jù)時，Excel中會出現(xiàn)前面設(shè)置的錯誤提示信息。EXCEL日常操作中的常用技巧日常操作中的常用技巧v當(dāng) Microsoft Excel 導(dǎo)入或合并多條記錄時，可能會建立重復(fù)的記錄。本文介紹如何 建立不重復(fù)記錄的匯總列表。 v要建立不重復(fù)記錄的匯總列表，請按照下列步驟操作： 1. 選擇要排序的行或記錄上方的列標題。 v2. 在數(shù)據(jù)菜單中，指向篩選

24、，然后單擊高級篩選。如果收到如下提示，請單擊確定。v未檢測到任何標題。能不能將選定區(qū)域的首行用作標題行？ v3. 在方式下，單擊“將篩選結(jié)果復(fù)制到其他位置”。 v4. 單擊以選中“選擇不重復(fù)的記錄復(fù)選框。 v5. 在“列表區(qū)域框中，鍵入或選擇一個記錄范圍如 A3:H10）。 v6. 在“條件區(qū)域框中，鍵入或選擇相同的記錄范圍即 A3:H10） v7. 在“復(fù)制到框中，鍵入用于存放信息的第一個單元格的地址，或直接選擇該單元格。請確保該區(qū)域有足夠的空間，以免覆蓋已存在的數(shù)據(jù)。v 留心：只能在活動的工作表中執(zhí)行此操作。 v8. 單擊確定。 v如果原始數(shù)據(jù)的第一條記錄是重復(fù)的，則該記錄在新列表中將出現(xiàn)

25、兩次。只潛藏第一行。v如果就地執(zhí)行高級篩選命令，工作表仍包含所有記錄。重復(fù)的記錄將被潛藏。要變通解決這一問題，請指向“格式菜單上的“行”，然后單擊“取消潛藏 ”。Excel圖表應(yīng)用圖表應(yīng)用所謂制作圖表就是把在Excel文件中的數(shù)據(jù)用圖形表示出來，使之更直觀醒目。制作出來的圖表可以是柱形圖、條形圖、餅圖、圓柱圖、散點圖、折線圖甚至三維圖等。在Excel中把圖表做好后，就可以把它們復(fù)制到Word文檔中去。制作圖表也有專用的圖表工具，在Excel窗口可以用“視圖”“工具欄的命令調(diào)出“圖表工具。但是，如果不是經(jīng)常作圖表，最好利用“圖表向?qū)聿僮?，只需四步操作即可完成一個圖表。方法如下：Excel圖表應(yīng)用圖表應(yīng)用1.首先要在Excel窗口輸入擬建立圖表的數(shù)據(jù)見下圖，這是職工文化水平的數(shù)據(jù)，是我杜撰的），并選定