EXCEL使用技巧

1、.主講：主講：Excel使用技巧.1Excel 可以做什么？Excel Microsoft Office 組件之一功能：對數(shù)據(jù)進行記錄、計算與分析應用：管理/財經(jīng)/金融/統(tǒng)計等領(lǐng)域 完成商業(yè)和工業(yè)任務個人事務處理專業(yè)科學運算、統(tǒng)計分析熟練掌握Excel的使用技巧 提高學習和工作效率.2124目錄目錄Excel概述概述基本操作技巧基本操作技巧數(shù)據(jù)處理與分析技巧數(shù)據(jù)處理與分析技巧6公式與函數(shù)公式與函數(shù)數(shù)據(jù)圖表化數(shù)據(jù)圖表化數(shù)據(jù)透視表數(shù)據(jù)透視表Excel 2007Excel 2010Excel 201635. 第一部分第一部分Excel概述概述PART 01Excel基本概念Excel界面.41. E

2、xcel 概述1.1 基本概念工作簿：Excel創(chuàng)建的文件(Book1/2/3)工作表：工作簿的組成部分，存儲和處理數(shù)據(jù)的主要文檔數(shù)據(jù)工作表(Sheet1/2/3)圖表工作表(Chart1/2/3)單元格：工作表存儲數(shù)據(jù)的最基本單元行列交叉點(列標A/B/C+行標1/2/3A1/B2/C3)單元格區(qū)域：多個單元格組成的矩形區(qū)域左上角單元格 : 右下角單元格(A1:F9).51. Excel 概述1.1 基本概念工作簿、工作表、單元格之間的關(guān)系方格方格/ /橫格橫格一頁紙一頁紙筆記本筆記本單元格單元格工作表工作表工作簿工作簿.61. Excel 概述1.2 Excel界面名稱框名稱框編輯欄編輯欄

3、全選全選標題欄標題欄( (工作簿名稱工作簿名稱) )快速訪問工具欄快速訪問工具欄文件文件狀態(tài)欄狀態(tài)欄活動單元格活動單元格工作表標簽工作表標簽 行標題行標題 1列標題列標題 1 ,048,576AXFD(16,384)功能區(qū)功能區(qū).71. Excel 概述1.2 Excel界面功能區(qū)( Excel 2016 菜單欄)選項卡(集中某一項核心任務)組(相關(guān)項的組合)命令(執(zhí)行某一具體任務)更多地顯示出以前隱藏在菜單和工具欄中的命令和功能快速訪問工具欄快速訪問工具欄( ( Excel 2010 工具欄工具欄) )固定顯示最常用的命令對話框啟動器對話框啟動器點擊獲取更多選項，來做更細部的設定.81. E

4、xcel 概述1.2 Excel 界面文件選項卡Excel 選項 (Excel 工具菜單 選項).91. Excel 概述1.2 Excel 界面名稱框+編輯欄編輯欄編輯欄 顯示活動單元格中的數(shù)據(jù)輸入/編輯數(shù)據(jù)（值/公式）名稱框 顯示活動單元格的地址/名稱定義名稱/快速定位單元格或區(qū)域取消取消確認確認插入函數(shù)向?qū)Р迦牒瘮?shù)向?qū)?101. Excel 概述1.2 Excel界面狀態(tài)欄單元格模式單元格模式就緒 / 輸入 / 編輯自動計算結(jié)果自動計算結(jié)果平均值 / 計數(shù) / 最值 / 求和.111. Excel 概述1.2 Excel 界面活動單元格（當前選中的單元格）黑色邊框右下角填充柄（擴展/填充

5、序列）選中的單元格區(qū)域黑色邊框+高亮活動單元格反色填充柄. 第二部分第二部分基本操作技巧基本操作技巧PART 02工作簿工作表快捷操作及美化單元格.132. 基本操作2.1 2.1 工作簿工作簿創(chuàng)建空白工作簿基于模板基于現(xiàn)有工作簿保存打開關(guān)閉XML XML 文件類型文件類型擴展名擴展名工作簿.xlsx啟用了宏的工作簿.xlsm模板.xltx啟用了宏的模板.xltm非 XML 二進制工作簿.xlsb啟用了宏的加載項 .xlam.142. 基本操作2.2 2.2 工作表工作表 凍結(jié)窗格凍結(jié)窗格 例題例題1 1 視圖視圖窗口窗口凍結(jié)窗口凍結(jié)窗口凍結(jié)拆分窗格凍結(jié)拆分窗格.152. 基本操作2.2 2.

6、2 工作表工作表 拆分窗格拆分窗格視圖視圖窗口窗口拆分。以選定單元格左上角位置進行拆分拆分。以選定單元格左上角位置進行拆分.162. 基本操作2.2 2.2 工作表工作表 并排查看并排查看視圖視圖窗口窗口新建窗口新建窗口- -全部重排全部重排- -垂直并排垂直并排/ /同步滾輪同步滾輪.172. 基本操作2.2 2.2 工作表工作表 快速美化快速美化快速美化快速美化套用表格格式套用表格格式兩種方法兩種方法單擊數(shù)據(jù)表，開始單擊數(shù)據(jù)表，開始-套用表格格式套用表格格式Ctrl+T組合鍵【技巧技巧】將表格轉(zhuǎn)換為普通數(shù)據(jù)區(qū)域：將表格轉(zhuǎn)換為普通數(shù)據(jù)區(qū)域：淡季表格中任意一個單元格，表格工淡季表格中任意一個單

7、元格，表格工具具/設計選項卡設計選項卡轉(zhuǎn)換為區(qū)域按鈕轉(zhuǎn)換為區(qū)域按鈕單擊數(shù)據(jù)表，按單擊數(shù)據(jù)表，按Ctrl+T快捷鍵快捷鍵 .182. 基本操作2.2 2.2 工作表工作表 快速美化快速美化 例題例題2 2快速美化快速美化使用格式刷、單元格樣式使用格式刷、單元格樣式選中設置好的表頭區(qū)域，單擊開始樣式組單元格樣式下拉箭頭新建單元格樣式，創(chuàng)建自定義樣式，快速美化其他工作表或工作簿.192. 基本操作2.2 2.2 工作表工作表 設置格式與高級打印設置格式與高級打印單擊頁面布局單擊頁面布局頁面設置頁面設置- -打印標題打印標題設置打印標題后，打設置打印標題后，打印長表格時每頁都有印長表格時每頁都有標題行

8、標題行.202. 基本操作2.2 2.2 工作表工作表 設置格式與高級打印設置格式與高級打印 例例1 1 分頁打印分頁打印選擇要分頁處左上角的選擇要分頁處左上角的單元格，頁面布局單元格，頁面布局-分分隔符隔符-插入分頁符。插入分頁符。插入分頁符后，插入分頁符后，Excel自動按選定的單元格的自動按選定的單元格的左邊框和上邊框，將工左邊框和上邊框，將工作表劃分為多個打印區(qū)作表劃分為多個打印區(qū)域。域。.212. 基本操作2.2 2.2 工作表工作表 打印工作簿打印工作簿單擊文件單擊文件- -打印打印- -在打印設置中設置在打印設置中設置“打印整個工作簿打印整個工作簿”.222. 基本操作2.2 2

9、.2 工作表工作表 工作表的移動與復制工作表的移動與復制選擇選擇: Shift: Shift連續(xù)連續(xù) / Ctrl / Ctrl不連續(xù)不連續(xù) + + 單擊工作表標簽單擊工作表標簽命令：插入命令：插入/ /刪除、重命名、移動刪除、重命名、移動/ /復制復制勾選勾選 復制復制 在打開的工作簿之間移動在打開的工作簿之間移動右鍵右鍵.232. 基本操作2.3 2.3 單元格單元格 定位單元格的技巧定位單元格的技巧定位單元格：鼠標左鍵單擊定位單元格：鼠標左鍵單擊1、Ctrl+Home快速定位快速定位A1單元格單元格2、Ctrl+方向鍵快速定位水平或垂直方向鍵快速定位水平或垂直方向的的始末單元格方向的的始

10、末單元格3、Ctrl+End快速定位已使用區(qū)域右快速定位已使用區(qū)域右下角單元格下角單元格4、名稱框定位、名稱框定位當要定位的單元格不在當前屏當要定位的單元格不在當前屏幕內(nèi)時：幕內(nèi)時：.242. 基本操作2.3 2.3 單元格單元格 選取單元格的高招選取單元格的高招 例例3 3快速準確的選取單元格區(qū)域快速準確的選取單元格區(qū)域1、Shift+對角單元格：快速選中任意對角單元格：快速選中任意目標區(qū)域目標區(qū)域2、Ctrl+Shift+Home：快速從活動單：快速從活動單元格到元格到A1區(qū)域區(qū)域3、Ctrl+Shift+方向鍵：快速選中當前方向鍵：快速選中當前行、列數(shù)據(jù)區(qū)域行、列數(shù)據(jù)區(qū)域4、Ctrl+A

11、：快速選中當前數(shù)據(jù)區(qū)域：快速選中當前數(shù)據(jù)區(qū)域（全選）（全選）5、Ctrl+單擊工作表標簽：多表批量操單擊工作表標簽：多表批量操作作.252. 基本操作2.3 2.3 單元格單元格 輸入數(shù)據(jù)輸入數(shù)據(jù)手動輸入數(shù)據(jù)手動輸入數(shù)據(jù)多個單元格多個單元格/ /區(qū)域同時輸入相同數(shù)據(jù)區(qū)域同時輸入相同數(shù)據(jù)Ctrl+EnterCtrl+Enter單元格內(nèi)數(shù)據(jù)多行顯示單元格內(nèi)數(shù)據(jù)多行顯示自動換行自動換行/Alt+Enter/Alt+Enter自動輸入數(shù)據(jù)自動輸入數(shù)據(jù)填充柄填充柄 自動填充自動填充向外拖動向外拖動 擴展填充擴展填充向內(nèi)拖動向內(nèi)拖動 清除清除填充柄填充柄. 第三部分第三部分數(shù)據(jù)處理與分析技巧數(shù)據(jù)處理與分析

12、技巧PART 03 粘貼與分列 查找定位 數(shù)據(jù)有效性 條件格式 數(shù)據(jù)排序 篩選技巧 分類匯總 合并計算.273.數(shù)據(jù)處理與分析技巧3.13.1粘貼粘貼 選擇性粘貼選擇性粘貼- -轉(zhuǎn)置轉(zhuǎn)置實現(xiàn)行列內(nèi)容快速轉(zhuǎn)換實現(xiàn)行列內(nèi)容快速轉(zhuǎn)換 讓原始區(qū)域在粘貼后行列互換，并能自動調(diào)整所有公式轉(zhuǎn)置后正常計算.283.數(shù)據(jù)處理與分析技巧3.13.1粘貼粘貼 例例3 3 選擇性粘貼選擇性粘貼- -轉(zhuǎn)換為數(shù)值轉(zhuǎn)換為數(shù)值 選擇性粘貼選擇性粘貼- -運算（結(jié)構(gòu)相同）運算（結(jié)構(gòu)相同）.293.數(shù)據(jù)處理與分析技巧3.1 3.1 分列分列 根據(jù)分隔符號、字符個數(shù)將目標拆分成多列。根據(jù)分隔符號、字符個數(shù)將目標拆分成多列。 1、確

13、定數(shù)據(jù)拆分方式 2、選擇分隔符號或設置分列線 3、為拆分后的各列設置數(shù)據(jù)格式.303.數(shù)據(jù)處理與分析技巧3.1 3.1 分列分列 例例4 4 根據(jù)固定寬度方式拆分單元格。根據(jù)固定寬度方式拆分單元格。 從身份證號碼中提取“地區(qū)代碼”和“出生日期”.313.數(shù)據(jù)處理與分析技巧3.2 3.2 查找與替換查找與替換 例例5 5 查找與替換基礎(chǔ)應用查找與替換基礎(chǔ)應用 開始編輯查找和選擇查找（Ctrl+F）/替換（Ctrl+H）.323.數(shù)據(jù)處理與分析技巧3.2 3.2 查找與替換查找與替換 按格式查找替換單元格內(nèi)容按格式查找替換單元格內(nèi)容.333.數(shù)據(jù)處理與分析技巧3.2 查找與替換 合并單元格填入數(shù)據(jù)

14、合并單元格填入數(shù)據(jù) 選中合并單元格取消合并定位空值“=+方向上鍵”Ctrl+Enter批量錄入.343.數(shù)據(jù)處理與分析技巧3.33.3數(shù)據(jù)有效性數(shù)據(jù)有效性選中選中單元格時單元格時出現(xiàn)提示出現(xiàn)提示信息信息輸入無效數(shù)據(jù)時輸入無效數(shù)據(jù)時顯示出錯警告顯示出錯警告圖標圖標 類型類型停止 禁止輸入無效數(shù)據(jù)警告 不禁止輸入無效數(shù)據(jù)是/否/取消接受/編輯/刪除 信息 不禁止輸入無效數(shù)據(jù) 確定/取消接受/拒絕限制限制 數(shù)據(jù)類型數(shù)據(jù)類型數(shù)值范圍數(shù)值范圍數(shù)據(jù)范圍數(shù)據(jù)范圍.353.數(shù)據(jù)處理與分析技巧3.33.3數(shù)據(jù)有效性數(shù)據(jù)有效性 例例6 6 圈釋無效數(shù)據(jù)圈釋無效數(shù)據(jù)找出不符合要求數(shù)據(jù)找出不符合要求數(shù)據(jù) 選擇數(shù)據(jù)區(qū)域

15、，為數(shù)據(jù)添加有效性，使用圈釋無效數(shù)據(jù)功能.363.數(shù)據(jù)處理與分析技巧3.4 3.4 條件格式條件格式 例7條件格式條件格式基于條件更改單元格區(qū)域的外觀基于條件更改單元格區(qū)域的外觀.373.數(shù)據(jù)處理與分析技巧3.53.5數(shù)據(jù)排序數(shù)據(jù)排序 例8普通排序普通排序內(nèi)置序列內(nèi)置序列自定義排序自定義排序自定義序列自定義序列單列單列/ /行排序行排序/ /多列多列/ /行排序行排序.383.數(shù)據(jù)處理與分析技巧3.63.6數(shù)據(jù)篩選數(shù)據(jù)篩選 自動篩選自動篩選/ /自定義篩選自定義篩選僅顯示滿足條件的數(shù)據(jù)，隱僅顯示滿足條件的數(shù)據(jù)，隱藏藏 不滿足條件的數(shù)據(jù)不滿足條件的數(shù)據(jù)/數(shù)字篩選數(shù)字篩選自動篩選自動篩選刪除重復項

16、刪除重復項-快速刪除工作表重復欄目快速刪除工作表重復欄目.393.數(shù)據(jù)處理與分析技巧3.63.6數(shù)據(jù)篩選數(shù)據(jù)篩選 例例1010 高級篩選高級篩選根據(jù)多個條件進行篩選根據(jù)多個條件進行篩選 列出條件（文本/表達式）高級篩選中選擇列表與條件區(qū)域.403.數(shù)據(jù)處理與分析技巧3.73.7分類匯總分類匯總 例9對數(shù)據(jù)按類別進行匯總對數(shù)據(jù)按類別進行匯總統(tǒng)計分析統(tǒng)計分析分類匯總之前一定要先分類匯總之前一定要先進行進行排序排序.413.數(shù)據(jù)處理與分析技巧3.83.8合并計算合并計算 3 3例題例題 對多張明細表快速合并計算生成匯總表對多張明細表快速合并計算生成匯總表多張表格式需相同多張表格式需相同. 第四部分第

17、四部分公式與函數(shù)基礎(chǔ)公式與函數(shù)基礎(chǔ)PART 04 公式基礎(chǔ)技巧 常用函數(shù).434. 公式與函數(shù)4.1 4.1 公式公式 公式和函數(shù)的區(qū)別與聯(lián)系公式和函數(shù)的區(qū)別與聯(lián)系公式：對數(shù)據(jù)執(zhí)行計算和操作的等式函數(shù)：預先定義的計算公式，實現(xiàn)復雜計算和操作公式：公式：=(A1+A2+A3+A100)/100=(A1+A2+A3+A100)/100函數(shù)：函數(shù)：=AVERAGE(A1:A100)=AVERAGE(A1:A100).444. 公式與函數(shù)4.1 4.1 公式公式 公式的組成公式的組成以等號開始（=）引用（單元格/區(qū)域/名稱）運算符函數(shù)常量(數(shù)字或文本)公式：公式：= 表達式表達式=(A3-averag

18、e)/SUM(A1:A20)+12=(A3-average)/SUM(A1:A20)+12.454. 公式與函數(shù)4.1 4.1 公式公式 例例1010相對引用相對引用/ /絕對引用絕對引用相對引用：公式所在單元格位置改變，則引用改變 示例：=B2-B3絕對引用：公式所在單元格位置改變，引用固定不變示例：=$B2/SUM($B$2:$B$12) 通過“$”固定行號列標.464. 公式與函數(shù)4.2 4.2 函數(shù)函數(shù)常用函數(shù)常用函數(shù)SUMSUM(number1,number2, ) 求和函數(shù)AVERAGEAVERAGE(number1,number2, ) 算術(shù)平均函數(shù)IFIF(logical_te

19、st,value_if_true,value_if_false) 判斷函數(shù)COUNTIFCOUNTIF（range，criteria）條件計數(shù)函數(shù)SUMIFSUMIF(range，criteria，sum_range)條件求和函數(shù)VLOOKUPVLOOKUP(lookup_value,table_array,col_index_num,range_lookup)縱向查找函數(shù).474. 公式與函數(shù)4.2 4.2 函數(shù)函數(shù)SUMSUM函數(shù)：函數(shù)：SUMSUM（Number1,Number2Number1,Number2）用途：計算所有參數(shù)數(shù)值的和用途：計算所有參數(shù)數(shù)值的和.484. 公式與函數(shù)4.

20、2 4.2 函數(shù)函數(shù)AVERAGEAVERAGE函數(shù)：函數(shù)：AVERAGE(number1,number2, )AVERAGE(number1,number2, )用途：計算所有參數(shù)數(shù)值的平均值用途：計算所有參數(shù)數(shù)值的平均值.494. 公式與函數(shù)4.2 4.2 函數(shù)函數(shù) 例例1111IFIF函數(shù)：函數(shù)：IF(Logical,Value_if_true,Value_if_false)IF(Logical,Value_if_true,Value_if_false)用途：判斷是否滿足條件，如果滿足，就返回一個值；用途：判斷是否滿足條件，如果滿足，就返回一個值；如果不滿足則返回另一個值如果不滿足則返回

21、另一個值.504. 公式與函數(shù)4.2 4.2 函數(shù)函數(shù) 例例1313COUNTIFCOUNTIF函數(shù)：函數(shù)：COUNTIF(Range,Criteria) COUNTIF(Range,Criteria) 用途：計算符合特定條件的個數(shù)用途：計算符合特定條件的個數(shù)要計算其中非空單元格數(shù)目的區(qū)域以數(shù)字、表達式、文本形式定義的條件.514. 公式與函數(shù)4.2 4.2 函數(shù)函數(shù) 例例1212SUMIFSUMIF函數(shù)：函數(shù)：SUMIFSUMIF（Range,Criteria,Sum_RangeRange,Criteria,Sum_Range）用途：對滿足條件的單元格求和用途：對滿足條件的單元格求和條件區(qū)域

22、求和條件實際求和區(qū)域.524. 公式與函數(shù)4.2 4.2 函數(shù)函數(shù) 例例1414VLOOKUPVLOOKUP函數(shù)：按列查詢，要求查詢的函數(shù)在第一列函數(shù)：按列查詢，要求查詢的函數(shù)在第一列VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)查找的值查找的區(qū)域返回數(shù)據(jù)在區(qū)域的第幾列數(shù)模糊匹配（TRUS/省略）精確匹配（FALSE/0）. 第五部分第五部分數(shù)據(jù)圖表化數(shù)據(jù)圖表化PART 05 圖表基礎(chǔ) 創(chuàng)建圖表 編輯美化圖表.545. 數(shù)據(jù)圖表化5.1 5.1 圖表圖表 圖表是數(shù)據(jù)的圖形化表示圖表是數(shù)據(jù)的圖形化表示 圖表構(gòu)成圖表構(gòu)成圖表

23、區(qū)繪圖區(qū) 數(shù)據(jù)系列（某一行/列）橫坐標軸（分類軸）縱坐標軸（數(shù)值軸）網(wǎng)格線圖例標題（圖表標題、橫縱坐標軸標題 ）數(shù)據(jù)標簽 .555. 數(shù)據(jù)圖表化5.1 5.1 圖表圖表 常用類型常用類型柱形圖柱形圖：顯示一段時間內(nèi)的數(shù)據(jù)變化/各項之間的比較情況折線圖折線圖：顯示在相等時間間隔下數(shù)據(jù)的變化趨勢餅圖餅圖：顯示一個數(shù)據(jù)系列中各項大小與各項總和的比例條形圖條形圖：顯示各項之間的比較情況(適于分類軸標簽很長的情況)面積圖面積圖：強調(diào)數(shù)量隨時間變化的程度/引起對總值趨勢的注意.565. 數(shù)據(jù)圖表化5.1 5.1 圖表圖表 其他類型其他類型股價圖：顯示股價的波動顯示溫度的波動曲面圖：顯示兩組數(shù)據(jù)之間的不同組

24、合圓環(huán)圖：顯示各個部分與整體之間的關(guān)系氣泡圖：對成組的三個變量進行比較雷達圖：比較若干數(shù)據(jù)系列的聚合值.575. 數(shù)據(jù)圖表化5.2 5.2 快速創(chuàng)建圖表快速創(chuàng)建圖表 方法一：使用功能區(qū)命令 方法二：使用快捷鍵（Alt+F1組合鍵） 方法三：選定數(shù)據(jù)區(qū)域創(chuàng)建.585. 數(shù)據(jù)圖表化5.2 5.2 快速編輯圖表快速編輯圖表快速編輯項目：圖表元素、樣快速編輯項目：圖表元素、樣式及顏色、數(shù)據(jù)系列、類別式及顏色、數(shù)據(jù)系列、類別.595. 數(shù)據(jù)圖表化5.3 5.3 編輯美化圖表編輯美化圖表柱形圖柱形圖：數(shù)據(jù)按大小順序排列，突出強調(diào)一部分數(shù)據(jù)按大小順序排列，突出強調(diào)一部分分類標簽文字橫排，便于閱讀分類標簽文字

25、橫排，便于閱讀柱與柱之間的間距要小于柱的寬度柱與柱之間的間距要小于柱的寬度去掉圖例，數(shù)據(jù)直接標注的柱形上去掉圖例，數(shù)據(jù)直接標注的柱形上拒絕三維立體，顏色不超過三種拒絕三維立體，顏色不超過三種亮色暗色交替，保證黑白打印可讀亮色暗色交替，保證黑白打印可讀去掉所有輔助網(wǎng)絡線去掉所有輔助網(wǎng)絡線.605. 數(shù)據(jù)圖表化5.3 5.3 編輯美化圖表編輯美化圖表折線圖：折線圖：線條要足夠粗，明顯粗過所有非數(shù)據(jù)元素線條要足夠粗，明顯粗過所有非數(shù)據(jù)元素多條折線時，需要強調(diào)的那根，用最粗線型或最深的顏色表示多條折線時，需要強調(diào)的那根，用最粗線型或最深的顏色表示不要使用圖例，直接標記在折線后不要使用圖例，直接標記在折線后拒絕三維立體，刪除網(wǎng)絡線等輔助元素拒絕三維立體，刪除網(wǎng)絡線等輔助元素不好放太多線條（不好放太多線條（3），必要時分開做圖標），必要時分開做圖標.615. 數(shù)據(jù)圖表化5.3 5.3 編輯美化圖表編輯美化圖表餅圖：餅圖：適合強調(diào)某部分，次要部分可合并適合強調(diào)某部分，次要部分可合并去掉標注線和圖例，數(shù)據(jù)標注在扇區(qū)去掉標注線和圖例，數(shù)據(jù)標注在扇區(qū)拒絕三維餅圖，避免五顏六色拒絕三維餅圖，避免五顏六色扇區(qū)分離扇區(qū)分離/填充暖色填充暖色/特效特效/白邊框突出白邊框突出技巧：技巧：旋轉(zhuǎn)扇區(qū)旋轉(zhuǎn)扇區(qū)/設置扇區(qū)分離設置扇區(qū)分離調(diào)整圖表布局調(diào)整圖表布局.625. 數(shù)據(jù)圖表化5.3 5.3