Excel 技能培訓-初級篇ppt課件

1、Microsoft Excel-2-內部文檔-2-大綱-3-內部文檔-3-我們要學什么-4-內部文檔-4-基礎知識操作技巧常用函數數據透視表現實問題轉換報表圖表數據管理數據分析要學的內容以后講我們要學什么 1985 1985年年Microsoft ExcelMicrosoft Excel問世，被公認為是功能最完整、技術最先進和使用最簡便的電問世，被公認為是功能最完整、技術最先進和使用最簡便的電子表格軟件。子表格軟件。 Excel Excel可以創(chuàng)建和修改工作表、圖表，以及分析管理數據。可以創(chuàng)建和修改工作表、圖表，以及分析管理數據。分析分析-5-內部文檔-5-Excel基礎知識-6-內部文檔-6

2、-Excel界面-7-內部文檔-7-Excel基本介紹 一個EXCEL文件是由“文件-工作簿-工作表-行、列(X,Y)-單元格構成的。 工作簿：就是處理和存儲數據的文件； 一個工作簿可以創(chuàng)建256個工作表； 一個工作表包括65536行,256列； 一個工作表包括65536*256個單元格 EXCEL是一個容量非常大的數據表格！l注：注：EXCEL2019EXCEL2019在數據容量上超過了在數據容量上超過了EXCEL2019EXCEL2019lEXCEL2019EXCEL2019一個工作表包括一個工作表包括10485761048576行，行，1638416384列；列；lEXCEL2019EX

3、CEL2019一個工作表包括一個工作表包括1048576 1048576 * * 16384 16384個單元格個單元格-8-內部文檔-8-Excel常用快捷鍵單元格復制： “Ctrl + C”單元格粘貼： “Ctrl + V”單元格剪切： “Ctrl + X” 撤銷最后一次操作：“Ctrl + Z”復制上一個單元格：“Ctrl + ”向下填充：“Ctrl + D”向右填充：“Ctrl + R” 獲得幫助信息： “F1”編輯當前單元格： “F2”重復上一步操作： “F4”隨機數：randbetween（*，*）-9-內部文檔-9-Excel常用技巧-10-內部文檔-10-Excel常用技巧-1

4、1-內部文檔-11-EXCEL數據輸入技巧-1數據的定義：數據是數值、文字、日期、時間、公式和符號的總稱。數據的分類：數據分為字符數據和數值數據。數據輸入常用方法：1、非數值數字的輸入：數字前加單引號或設置單元格屬性為“文本”如：身份證號碼輸入2、分數的輸入：“=+ 0 + 分子 + / + 分母” 如：直接輸1/3，EXCEL默認為1月3日，應該寫成01/33、當前日期的輸入：“Ctrl + ；”(分號)4、當前時間的輸入：“Ctrl + ：”(冒號)5、批量錄入相同數據：“ Ctrl + Enter”先選擇需要批量輸入相同數據的區(qū)域，在輸入數據，使用快捷點Ctrl+Enter6、在單元格中

5、換行：“Alt + Enter”留意留意“；/ -”均為英文均為英文狀態(tài)下的符號狀態(tài)下的符號-12-內部文檔-12-EXCEL數據輸入技巧-27、快速輸入大寫中文數字 將光標移至需要輸入大寫數字的單元格中。在單元格中輸入相應的小寫數字。右擊該單元格，點擊“設置單元格格式”，從彈出的“單元格格式對話框中選擇“數字選項；然后從“特殊列表框中選擇“中文大寫數字選項。最后單擊“確定按鈕即可.8、快速隱藏 在打印工作表時，我們有時需要把某些行或者列隱藏起來，可是用菜單命令或調整行號(列標)分界線的方法比較麻煩,有一個簡單方法：在英文狀態(tài)下，按“Ctrl+9或“Ctrl+0組合鍵，就可以快速隱藏光標所在的

6、行或列。 9、快速選定單元格在Excel中，如果要選定已選定單元格周圍的單元格，可以按上下方向鍵進行移動，可為了節(jié)省時間，讓操作的速度更高。-13-內部文檔-13-快速移動到活動單元格在拖動鼠標的同時，按住“Ctrl鍵選擇不連續(xù)區(qū)域在拖動鼠標的同時，按住“Shift鍵可以選中兩點之間區(qū)域“Ctrl + Shift + 8” 或“Ctrl + * ”可以選中當前單元個周圍連續(xù)的矩形區(qū)域“Ctrl + Home” 將光標移動到第一個單元格“Ctrl + End ”將光標移動到表格最后一個單元格“Ctrl + 向右方向鍵可將光標移動到當前區(qū)域最右列(其他方向箭頭依此類推)“Ctrl + Shift

7、+ 向下箭頭選擇當前單元格至當前區(qū)域最后一行的內容-14-內部文檔-14-查找功能常用技巧 1.模糊查找 按“CTRL+F ”進入查找，在查找里直接輸查找內容為模糊查找。 2.精確查找 在查找里點開選項，將“單元格匹配的勾打上為精確查找 3.值查找 在查找里點開選項，“查找范圍選擇值為“值查找。（如果單元格里是公式那必須用值查找）-15-內部文檔-15-自動填充-1自動填充，就是根據已輸入的數據，按照系統(tǒng)規(guī)定的規(guī)則或用戶指定的規(guī)律，自動將數據填充到選定的區(qū)域。 如果單元格中是文本、數據填充柄起復制的作用如果單元格中是日期填充柄起到填充序列的作用-16-內部文檔-16-自動填充-2初始選擇初始選

8、擇擴展序列擴展序列1，2，34，5，69：0010：00，11：00，12：00MonTue，Wed，Thu星期一星期二，星期三，星期四JanFeb，Mar，Apr一月，四月七月，十月，一月1月1日，3月1日5月1日，7月1日，9月1日2019，20002019，2019，2019Text1，textAText2，textA ，Text3，textA產品1產品2，產品3-17-內部文檔-17-自動填充-3要填充比較復雜的數據時，可以試試右鍵來拖動填充柄選中單元格-編輯-填充-序列-18-內部文檔-18-數據分列的妙用 1用用ExcelExcel輸入數據時，如果在一行內輸入內容過長，可以采取快速

9、分列方輸入數據時，如果在一行內輸入內容過長，可以采取快速分列方法。具體操作如下法。具體操作如下: : 在選中單元格中輸入數據或文字在選中單元格中輸入數據或文字, ,然后選定所要分割的數據區(qū)域然后選定所要分割的數據區(qū)域; ; 單擊菜單欄【數據】單擊菜單欄【數據】 - -【分列】【分列】 ; ; 在彈出的對話框中選擇【固定寬度】在彈出的對話框中選擇【固定寬度】, ,點擊點擊“下一步下一步” ;” ; 在【數據預覽】中在【數據預覽】中, ,按住分列線按住分列線, ,移動至指定位置移動至指定位置, ,點擊點擊“下一步下一步” ;” ; 選擇【列數據格式】選擇【列數據格式】, ,點擊點擊“完成完成”.”

10、.電話號碼電話號碼交易時間1389227734020191001 08:21:501874032275020191001 08:38:501377291420820191001 08:39:481372079442020191001 08:49:071348829488620191001 09:08:391829125964920191001 09:10:221870022235120191001 09:42:59電話號碼電話號碼交易日期交易日期交易時間13892277340201910018:21:5018740322750201910018:38:501377291420820191001

11、8:39:4813720794420201910018:49:0713488294886201910019:08:3918291259649201910019:10:2218700222351201910019:42:59-19-內部文檔-19-數據分列的妙用 2批量轉換日期格式批量轉換日期格式 以前在以前在ExcelExcel中輸入職工出生時間時，為了簡單都輸入成中輸入職工出生時間時，為了簡單都輸入成“yymmdd“yymmdd方式，但上級部門一律要求輸入成方式，但上級部門一律要求輸入成“yyyy-mm-“yyyy-mm-dddd格式，那么一千多名職工出生時間肯定不能每個手工轉化。格式，那么

12、一千多名職工出生時間肯定不能每個手工轉化。 最快速的方法是：先選定要轉化的區(qū)域。點擊【數據】最快速的方法是：先選定要轉化的區(qū)域。點擊【數據】【分列】，出現【文本分列向導】對話框。勾選【分列】，出現【文本分列向導】對話框。勾選“固定寬度固定寬度”，連續(xù)兩次點擊【下一步】按鈕，在步驟三對話框的【列數據格式】連續(xù)兩次點擊【下一步】按鈕，在步驟三對話框的【列數據格式】中，選擇中，選擇“日期日期”，并選定，并選定“YMD“YMD方式，按下【完成】按鈕，方式，按下【完成】按鈕，以前的文本即轉化成了需要的日期了。以前的文本即轉化成了需要的日期了。-20-內部文檔-20-選擇性粘貼 1選擇要行列互換的數據區(qū)域

13、，單擊【復制】按鈕或按下【Ctrl+C】鍵復制所選區(qū)域。選擇行列互換后數據所在區(qū)域的第一個單元格區(qū)域左上角）。單擊【編輯】菜單上的【選擇性粘貼】在對話框中選擇【轉置】復選框，單擊【確定】。-21-內部文檔-21-選擇性粘貼 2對單元格進行同增同減操作 在Excel中，如果要對某一單元格或某一區(qū)域中的每個單元格中的數值進行同加、同減、同乘或同除操作時，可以使用“選擇性粘貼功能輕松實現。 例如：讓選定區(qū)域中的每個單元格都同乘以2，首先在選定區(qū)域外的某個單元格中輸入2，選擇這個單元格并復制 ;然后選擇要進行同乘操作的單元格區(qū)域，單擊“編輯菜單，選擇“選擇性粘貼命令，在“運算欄中選擇“乘單選項，單擊“

14、確定按鈕.結果顯示在了每個單元格中 。 使用這個功能還可以對單元格進行自身操作，例如可以進行單元格的自身相加、相減、相乘以及相除 。-22-內部文檔-22-定位在在ExcelExcel中，經常要選定空白單元格，逐個選定比較麻煩，中，經常要選定空白單元格，逐個選定比較麻煩，如果使用下面的方法就方便多了：翻開如果使用下面的方法就方便多了：翻開“編輯編輯定位定位”，在在“定位窗口中，按下定位窗口中，按下“定位條件按鈕；選擇定位條件按鈕；選擇“定位定位條件中的條件中的“空值空值”，再按，再按“確定確定”，空白單元格即被全，空白單元格即被全部選定。如果要選定只含數據的單元格，在上面方法的部選定。如果要選

15、定只含數據的單元格，在上面方法的“定位條件窗口中，選擇定位條件窗口中，選擇“常量常量”，再點，再點“確定確定”，含，含有數據的單元格全部選定。有數據的單元格全部選定。 快速選定快速選定“空白和空白和“數據單元格數據單元格-23-內部文檔單元格的引用技巧 相對引用 單元格地址會隨公式所在單元格的位置變更而改變。如：“A1 + B1” 絕對引用 單元格地址將不隨公式所在單元格的位置變化而改變。如：“$A$1 + $B$1” 混合引用 只給行號或列號加“$”符號。如：A$1，$B2,復制后部分地址改變。-24-內部文檔顯示標題技巧 1在打印時讓每頁都顯示標題在Excel中制作一個長表格時，默認打印時

16、，并不是每頁都會顯示出頂端標題行和左側標題列，它只會在第一頁上顯示，那如何能讓表格打印出來時，每頁都帶有標題欄呢？可以這樣設置：翻開“文件菜單，選擇“頁面設置選項，彈出“頁面設置對話框，單擊“工作表選項卡，在“打印標題欄中的“頂端標題行項和“左側標題列項中設置好要顯示的標題單元格，單擊“確定按鈕。這樣在打印長表格時，每頁都會顯示出標題的內容了。 -25-內部文檔顯示標題技巧 2編輯時讓每頁都顯示標題在編輯狀態(tài)時向后翻頁會看不到第一行的標題行，在操作數據時就會很不方便。這個問題很簡單就可以解決，方法如下： 選中標題所在行的下面一行單元格，翻開“窗口菜單，選擇“凍結窗格選項，上面一行就被固定了，無

17、論怎么向下翻頁，它都始終在第一行不會移動。-26-內部文檔-26-使用Excel公式與函數-27-內部文檔-27-公式和函數什么是函數Excel函數即是預先定義，執(zhí)行計算、分析等處理數據任務的特殊公式。以常用的求和函數SUM為例，它的語法是SUM(number1,number2 ,.)”。其中“SUM稱為函數名稱，一個函數只有唯一的一個名稱，它決定了函數的功能和用途。函數名稱后緊跟左括號，接著是用逗號分隔的稱為參數的內容，最后用一個右括號表示函數結束。什么是公式函數與公式既有區(qū)別又互相聯(lián)系。如果說前者是Excel預先定義好的特殊公式，后者就是由用戶自行設計對工作表進行計算和處理的公式。以公式“

18、=SUM(E1:H1)*A1+26為例，它要以等號“=”開場，其內部可以包括函數、援用、運算符和常量。上式中的“SUM(E1:H1)”是函數，“A1則是對單元格A1的引用(使用其中存儲的數據)，“26則是常量，“*”和“+”則是算術運算符。-28-內部文檔常用函數SUM SUM 用途：返回某一單元格區(qū)域中所有數字之和。 語法：SUM(number1，number2，.)。 參數：Number1，number2，.為1到N個需要求和的數值。 用法舉例：-29-內部文檔常用函數SUMIF SUMIF 用途：對滿足單個條件的單元格求和。 語法：SUMIF (range，criteria，sum_ra

19、nge) 參數： Range為用于條件判斷的單元格區(qū)域 Criteria是由數字、邏輯表達式等組成的判定條件 Sum_range為需要求和的單元格、區(qū)域或引用 用法舉例：-30-內部文檔常用函數DSUM DSUM 用途：求滿足給定多個條件的數據庫中記錄的字段列數據的和。 語法：DSUM ( database , field , criteria ) 參數： Database表示構成列表或數據庫的單元格區(qū)域。是引用的數據。 Field 指定函數所使用的數據列。列表中的數據列必須在第一行具有標志項?？梢杂秒p引號括住的列標簽，或是表示該列在列表中位置的數值。是數值型數據。 Criteria 為一組包

20、含給定條件的單元格區(qū)域。區(qū)域包括列標簽及列標簽下滿足某個條件的單元格。 用法舉例：-31-內部文檔常用函數MAX 和 MIN MAX 用途：返回數據集中的最大數值。 語法：MAX(number1，number2，.) 參數：Number1，number2，.是需要找出最大數值的1至N個數值。 MIN 用途：返回數據集中的最大數值。 語法：MIN(number1，number2，.) 參數：Number1，number2，.是需要找出最小數值的1至N個數值。 用法舉例：-32-內部文檔常用函數COUNT 和 COUNTIF COUNT 用途：返回包含數字以及包含參數列表中的數字的單元格的個數。

21、語法: COUNT(value1,value2,.) 參數：Value1, value2, . 為包含或引用各種類型數據的參數1 到 N個），但 只有數字類型的數據才被計算。 COUNTIF 用途：統(tǒng)計某一區(qū)域中符合條件的單元格數目。 語法：COUNTIF (range，criteria) 參數： range為需要統(tǒng)計的符合條件的單元格數目的區(qū)域； Criteria為參與計算的單元格條件，其形式可以為數字、表達式或文本。其中數字可以直接寫入，表達式和文本必須加引號。 用法舉例：-33-內部文檔常用函數ROUND/ROUNDUP/ROUNDDOWNROUND用途：返回某個數字按指定位數取整后的數

22、字。 語法：ROUND (number , num_digits) 參數：Number為需要進行四舍五入的數字。Num_digits為指定的位數，按此位數進行四舍五入。 ROUNDUP：不做四舍五入的向上取整 如算通話分鐘數ROUNDDOWN：不做四舍五入的向下取整用法舉例：ROUND(“2.1547”,3) = 2.155ROUNDUP (“2.1547”,3) = 2.155ROUNDDOWN (“2.1547”,3) = 2.154-34-內部文檔常用函數LEFT 和 RIGHT LEFT 用途：基于所指定的字符數返回文本字符串中的左邊第一個或前幾個字符 語法：LEFT (text ,

23、num_chars) 參數： Text是包含要提取字符的文本字符串。 Num_chars指定要由 LEFT 所提取的字符數。 RIGHT：為取右 用法舉例： LEFT (“ad246g”,3) = ad2 RIGHT (“ad246g”,3) = 46g-35-內部文檔常用函數MID MID 由用戶自行指定開始的位置和字符的長度。由用戶自行指定開始的位置和字符的長度 語法： MID(text,start_num,num_chars) 參數： Text 是包含要提取字符的文本字符串，可以直接輸入含有目標文字的單元格名稱。 Start_num 是文本中要提取的第一個字符的位置。文本中第一個字符的

24、start_num 為 1，以此類推。 Num_chars 指定希望 MID 從文本中返回字符的個數。 用法舉例： 假設A1身份證號碼提取生日的公式是“=MID(A1,7,4)&年&MID(A1,11,2)&月&MID(A1,13,2)&日”，返回的是“2019年12月31日”。-36-內部文檔常用函數RANK RANK 用途：根據指定條件對若干單元格、區(qū)域或引用求和。 語法：RANK (number , ref , order) 參數： Number：為需要找到排位的數字。 Ref：為數字列表數組或對數字列表的引

25、用。 Order：為一數字，指明排位的方式。假設 order 為 0零或省略，Microsoft Excel 對數字的排位是基于 ref 為按照降序排列的列表; 假設 order 不為零，Microsoft Excel 對數字的排位是基于 ref 為按照升序排列的列表。 用法舉例：-37-內部文檔常用函數IF IF 用途：根據指定條件對若干單元格、區(qū)域或引用求和。 語法：IF (logical_test , value_if_true , value_if_false) 參數： Logical_test：表示計算結果為 TRUE 或 FALSE 的任意值或表達式。例如，A10=100 就是一個

26、邏輯表達式，如果單元格 A10 中的值等于 100，表達式即為 TRUE，否則為 FALSE。 Value_if_true：logical_test 為 TRUE 時返回的值。 Value_if_false：logical_test 為 FALSE 時返回的值。 用法舉例：-38-內部文檔常用函數VLOOKUPuVLOOKUPu用途：在表格或數值數組的首列查找指定的數值，并由此返回表格或數組中該數值所在行中指定列處的數值。u語法： VLOOKUP查找值，區(qū)域，列序號，邏輯值）u參數：u“查找值”：為需要在數組第一列中查找的數值，它可以是數值、引用或文字符串。 u“區(qū)域”：數組所在的區(qū)域，如“B

27、2:E10”，也可以使用對區(qū)域或區(qū)域名稱的引用，例如數據庫或數據清單。 u“列序號”：即希望區(qū)域數組中待返回的匹配值的列序號，為1時，返回第一列中的數值，為2時，返回第二列中的數值，以此類推；u“邏輯值”：為TRUE/1或FALSE/0。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于“查找值的最大數值；假設“邏輯值為FALSE，函數 VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值u用法舉例：-39-內部文檔-39-Excel數據排序、篩選與分類匯總-40-內部文檔-40-Excel數據排序、篩選與分類匯總-41-內部文檔排序是根據數據清單中某

28、個字段的值排列各行記錄的順序。如果要求對一列數據對數據清單排序，在常用工具欄中單擊【升序】【降序】 按鈕即可。如果要示對多列的數據對數據清單排序，操作步驟如下：1、選中【數據】菜單中的【排序】菜單項，彈出【排序】對話框。數據排序-42-內部文檔2、在彈出【排序】對話框中，根據數據清單中有無“標題行”，在“當前數據清單設置區(qū)中選擇“有標題行或“無標題行選項3、在“主要關鍵字”、“次要關鍵字”、“第三關鍵字中選擇相關要求4、單擊“確定”，此時表中的數據按照要求進行排序數據排序-43-內部文檔數據篩選是將不滿足條件的數據暫時隱藏起來，只顯示符合條件的數據。Excel提供“自動篩選和“高級篩選前者進行

29、簡單件的篩選，后者進行復雜的條件篩選。使用自動篩選的方法：1、選中數據清單的標題行，單擊【數據】菜單中的【挑選】菜單項的【自動篩選】，在選中數據的每個字段名的右側都會出現下拉箭頭。數據篩選數據篩選-44-內部文檔2、如果要顯示分數前幾名考生，可單擊一拉箭頭，點擊“前10個”，系統(tǒng)會彈出【選前10個】對話框，用戶可根據需求進行設置，最后按“確定”。3、如果要進行某個不等式條件或某個區(qū)間進行篩選，右可單擊一拉箭頭，點擊“自定義”，系統(tǒng)會彈出【自定義】對話框，用戶可根據需求進行設置，最后按“確定”。數據篩選-45-內部文檔如果篩選條件很多，可以考慮使用“高級篩選”，運用“高級篩選步驟 如下：1、在與

30、數據清單不相連的區(qū)域設置條件，將有“與關系的條件放在同一行，有“或關系的條件放在不同的行注： “與表示兩個條件同時成立，“或表示兩個條件中有一個成立）2、單擊數據清單中的任一單元格，單擊【數據】菜單中的【挑選】菜單項的【高級篩選】命令，彈出【高級篩選】對話框。數據的高級篩選-46-內部文檔3、在【高級篩選】對話框中選擇數據區(qū)域和條件區(qū)域，4、可以選擇兩種放置篩選結果的方式，一種是“在原有區(qū)域顯示篩選結果另一種是“將當前篩選結果復制到其他位置”，單擊【確定】。數據的高級篩選-47-內部文檔分類匯總之前要先對數據進行排序選中數據，單擊【數據】菜單中的【分類匯總】菜單項，出現【分類匯總】對話框在【分類匯總】對話框中設置：分類字段，匯總方式，匯總項。各項設置后單擊【確定】按鈕，完成分類匯總分類匯總-48-內部文檔-48-Excel數據條件格式-49-內部文檔條件格式的使用技巧什么是條件格式對選定區(qū)域中符合指定條件的數據以指定格式顯示如何設置條件格式格式 - 條件格式使用條件格式顯示單元格的數據例：將表格中小于60的數字以紅色顯示目的：突出顯示滿足某個條件數