《EXCEL使用技巧》PPT課件

- 0 - 主講：王娟敏Excel使用技巧 - 1 - Excel 可以做什么？Excel Microsoft Office 組件之一 功能：對數(shù)據(jù)進(jìn)行記錄、計算與分析 應(yīng)用： 管理/財經(jīng)/金融/統(tǒng)計等領(lǐng)域 完成商業(yè)和工業(yè)任務(wù) 個人事務(wù)處理 專業(yè)科學(xué)運算、統(tǒng)計分析 熟練掌握Excel的使用技巧 提高學(xué)習(xí)和工作效率 12021-6-6 - 2 - 124目錄 Excel概述基本操作技巧數(shù)據(jù)處理與分析技巧 6 公式與函數(shù)數(shù)據(jù)圖表化數(shù)據(jù)透視表 Excel 2007Excel 2010Excel 201635 - 3 - 第一部分Excel概述PART 01 Excel基本概念Excel界面 - 4 - 1. Excel 概述1.1 基本概念Excel創(chuàng)建的文件(Book1/2/3)工作簿的組成部分，存儲和處理數(shù)據(jù)的主要文檔 數(shù)據(jù)工作表(Sheet1/2/3) 圖表工作表(Chart1/2/3)工作表存儲數(shù)據(jù)的最基本單元 行列交叉點(列標(biāo)A/B/C+行標(biāo)1/2/3A1/B2/C3)多個單元格組成的矩形區(qū)域 左上角單元格 : 右下角單元格(A1:F9)工作簿：工作表：單元格：單元格區(qū)域： 42021-6-6 - 5 - 1. Excel 概述1.1 基本概念工作簿、工作表、單元格之間的關(guān)系方格/橫格 一頁紙 筆記本 52021-6-6 單元格 工作表 工作簿 - 6 - 1. Excel 概述1.2 Excel界面 62021-6-6 名 稱 框編 輯 欄全 選 標(biāo) 題 欄 (工 作 簿 名 稱 )快 速 訪 問 工 具 欄文 件狀 態(tài) 欄 活 動 單 元 格工 作 表 標(biāo) 簽 行 標(biāo) 題 1 列 標(biāo) 題 1 ,048,576 AXFD(16,384)功 能 區(qū) - 7 - 1. Excel 概述1.2 Excel界面功能區(qū)( Excel 2016 菜單欄)選項卡(集中某一項核心任務(wù))組(相關(guān)項的組合)命令(執(zhí)行某一具體任務(wù)) 更多地顯示出以前隱藏在菜單和工具欄中的命令和功能 72021-6-6 快 速 訪 問 工 具 欄 ( Excel 2010 工 具 欄 ) 固 定 顯 示 最 常 用 的 命 令對 話 框 啟 動 器 點 擊 獲 取 更 多 選 項 ， 來 做 更 細(xì) 部 的設(shè) 定 - 8 - 1. Excel 概述1.2 Excel 界面文件選項卡 Excel 選項 (Excel 工具菜單 選項) 82021-6-6 - 9 - 1. Excel 概述1.2 Excel 界面名稱框+編輯欄 92021-6-6 編輯欄 顯示活動單元格中的數(shù)據(jù)輸入/編輯數(shù)據(jù)（值/公式） 名 稱 框 顯 示 活 動 單 元 格 的 地 址 /名 稱定 義 名 稱 /快 速 定 位 單 元 格 或 區(qū) 域取 消確 認(rèn)插 入 函 數(shù) 向 導(dǎo) - 10 - 1. Excel 概述1.2 Excel界面狀態(tài)欄單元格模式就緒 / 輸入 / 編輯自動計算結(jié)果平均值 / 計數(shù) / 最值 / 求和 102021-6-6 - 11 - 1. Excel 概述1.2 Excel 界面活動單元格（當(dāng)前選中的單元格）l 黑色邊框l 右下角填充柄（擴(kuò)展/填充序列）選中的單元格區(qū)域l 黑色邊框+高亮 l 活動單元格反色l 填充柄 112021-6-6 - 12 - 第二部分基本操作技巧PART 02 工作簿工作表快捷操作及美化單元格 - 13 - 2. 基本操作2.1 工作簿創(chuàng)建空白工作簿基于模板基于現(xiàn)有工作簿保存 打開關(guān)閉 132021-6-6 XML 文 件 類 型 擴(kuò) 展 名工 作 簿 .xlsx啟 用 了 宏 的 工 作 簿 .xlsm模 板 .xltx啟 用 了 宏 的 模 板 .xltm非 XML 二 進(jìn) 制 工 作 簿 .xlsb啟 用 了 宏 的 加 載 項 .xlam - 14 - 2. 基本操作2.2 工作表 凍結(jié)窗格 例題1 視圖窗口凍結(jié)窗口凍結(jié)拆分窗格 142021-6-6 - 15 - 2. 基本操作2.2 工作表 拆分窗格視圖窗口拆分。以選定單元格左上角位置進(jìn)行拆分 152021-6-6 - 16 - 2. 基本操作2.2 工作表 并排查看視圖窗口新建窗口-全部重排-垂直并排/同步滾輪 162021-6-6 - 17 - 2. 基本操作2.2 工作表 快速美化快速美化套用表格格式兩種方法 172021-6-6 單擊數(shù)據(jù)表，開始-套用表格格式Ctrl+T組合鍵 【技巧】將表格轉(zhuǎn)換為普通數(shù)據(jù)區(qū)域：淡季表格中任意一個單元格，表格工具/設(shè)計選項卡轉(zhuǎn)換為區(qū)域按鈕單擊數(shù)據(jù)表，按Ctrl+T快捷鍵 - 18 - 2. 基本操作2.2 工作表 快速美化 例題2快速美化使用格式刷、單元格樣式選中設(shè)置好的表頭區(qū)域，單擊開始樣式組單元格樣式下拉箭頭新建單元格樣式，創(chuàng)建自定義樣式，快速美化其他工作表或工作簿 182021-6-6 - 19 - 2. 基本操作2.2 工作表 設(shè)置格式與高級打印單擊頁面布局 頁面設(shè)置-打印標(biāo)題 192021-6-6 設(shè)置打印標(biāo)題后，打印長表格時每頁都有標(biāo)題行 - 20 - 2. 基本操作2.2 工作表 設(shè)置格式與高級打印 例1 分頁打印 202021-6-6 選擇要分頁處左上角的單元格，頁面布局-分隔符-插入分頁符。插入分頁符后，Excel自動按選定的單元格的左邊框和上邊框，將工作表劃分為多個打印區(qū)域。 - 21 - 2. 基本操作2.2 工作表 打印工作簿單擊文件-打印-在打印設(shè)置中設(shè)置“打印整個工作簿” 212021-6-6 - 22 - 2. 基本操作2.2 工作表 工作表的移動與復(fù)制選擇: Shift連續(xù) / Ctrl不連續(xù) + 單擊工作表標(biāo)簽命令：插入/刪除、重命名、移動/復(fù)制 222021-6-6 勾選 復(fù)制 在打開的工作簿之間移動右鍵 - 23 - 2. 基本操作2.3 單元格 定位單元格的技巧 232021-6-6 定位單元格：鼠標(biāo)左鍵單擊1、Ctrl+Home快速定位A1單元格2、Ctrl+方向鍵快速定位水平或垂直方向的的始末單元格3、Ctrl+End快速定位已使用區(qū)域右下角單元格4、名稱框定位當(dāng)要定位的單元格不在當(dāng)前屏幕內(nèi)時： - 24 - 2. 基本操作2.3 單元格 選取單元格的高招 例3 242021-6-6 快速準(zhǔn)確的選取單元格區(qū)域1、Shift+對角單元格：快速選中任意目標(biāo)區(qū)域2、Ctrl+Shift+Home：快速從活動單元格到A1區(qū)域3、Ctrl+Shift+方向鍵：快速選中當(dāng)前行、列數(shù)據(jù)區(qū)域4、Ctrl+A：快速選中當(dāng)前數(shù)據(jù)區(qū)域（全選）5、Ctrl+單擊工作表標(biāo)簽：多表批量操作 - 25 - 2. 基本操作2.3 單元格 輸入數(shù)據(jù)手動輸入數(shù)據(jù)多個單元格/區(qū)域同時輸入相同數(shù)據(jù)Ctrl+Enter單元格內(nèi)數(shù)據(jù)多行顯示自動換行/Alt+Enter自動輸入數(shù)據(jù)填充柄 自動填充向外拖動 擴(kuò)展填充向內(nèi)拖動 清除 252021-6-6 填充柄 - 26 - 第三部分?jǐn)?shù)據(jù)處理與分析技巧PART 03 粘貼與分列 查找定位 數(shù)據(jù)有效性 條件格式 數(shù)據(jù)排序 篩選技巧 分類匯總 合并計算 - 27 - 3.數(shù)據(jù)處理與分析技巧3.1粘貼 選擇性粘貼-轉(zhuǎn)置實現(xiàn)行列內(nèi)容快速轉(zhuǎn)換 讓原始區(qū)域在粘貼后行列互換，并能自動調(diào)整所有公式轉(zhuǎn)置后正常計算 272021-6-6 - 28 - 3.數(shù)據(jù)處理與分析技巧3.1粘貼 例3 選擇性粘貼-轉(zhuǎn)換為數(shù)值 選擇性粘貼-運算（結(jié)構(gòu)相同） 282021-6-6 - 29 - 3.數(shù)據(jù)處理與分析技巧3.1 分列 根據(jù)分隔符號、字符個數(shù)將目標(biāo)拆分成多列。 1、確定數(shù)據(jù)拆分方式 2、選擇分隔符號或設(shè)置分列線 3、為拆分后的各列設(shè)置數(shù)據(jù)格式 292021-6-6 - 30 - 3.數(shù)據(jù)處理與分析技巧3.1 分列 例4 根據(jù)固定寬度方式拆分單元格。 從身份證號碼中提取“地區(qū)代碼”和“出生日期” 302021-6-6 - 31 - 3.數(shù)據(jù)處理與分析技巧3.2 查找與替換 例5 查找與替換基礎(chǔ)應(yīng)用 開始編輯查找和選擇查找（Ctrl+F）/替換（Ctrl+H） 312021-6-6 - 32 - 3.數(shù)據(jù)處理與分析技巧3.2 查找與替換 按格式查找替換單元格內(nèi)容 322021-6-6 - 33 - 3.數(shù)據(jù)處理與分析技巧3.2 查找與替換 合并單元格填入數(shù)據(jù) 選中合并單元格取消合并定位空值“=+方向上鍵”Ctrl+Enter批量錄入 332021-6-6 - 34 - 3.數(shù)據(jù)處理與分析技巧3.3數(shù)據(jù)有效性 342021-6-6 選中單元格時出現(xiàn)提示信息輸入無效數(shù)據(jù)時顯示出錯警告 圖 標(biāo) 類 型停 止 禁 止 輸 入 無 效 數(shù) 據(jù)警 告 不 禁 止 輸 入 無 效 數(shù)據(jù)是 /否 /取 消 接 受 /編 輯 /刪除 信 息 不 禁 止 輸 入 無 效 數(shù)據(jù) 確 定 /取 消 接 受 /拒 絕限制 數(shù)據(jù)類型 數(shù)值范圍數(shù)據(jù)范圍 - 35 - 3.數(shù)據(jù)處理與分析技巧3.3數(shù)據(jù)有效性 例6 圈釋無效數(shù)據(jù)找出不符合要求數(shù)據(jù)選擇數(shù)據(jù)區(qū)域，為數(shù)據(jù)添加有效性，使用圈釋無效數(shù)據(jù)功能 352021-6-6 - 36 - 3.數(shù)據(jù)處理與分析技巧3.4 條件格式 例7條件格式基于條件更改單元格區(qū)域的外觀 362021-6-6 - 37 - 3.數(shù)據(jù)處理與分析技巧3.5數(shù)據(jù)排序 例8普通排序內(nèi)置序列自定義排序自定義序列單列/行排序/多列/行排序 372021-6-6 - 38 - 3.數(shù)據(jù)處理與分析技巧3.6數(shù)據(jù)篩選 自動篩選/自定義篩選僅顯示滿足條件的數(shù)據(jù)，隱藏 不滿足條件的數(shù)據(jù) 382021-6-6 /數(shù)字篩選 自動篩選刪除重復(fù)項-快速刪除工作表重復(fù)欄目 - 39 - 3.數(shù)據(jù)處理與分析技巧3.6數(shù)據(jù)篩選 例10 高級篩選根據(jù)多個條件進(jìn)行篩選 列出條件（文本/表達(dá)式）高級篩選中選擇列表與條件區(qū)域 392021-6-6 - 40 - 3.數(shù)據(jù)處理與分析技巧3.7分類匯總 例9對數(shù)據(jù)按類別進(jìn)行匯總統(tǒng)計分析分類匯總之前一定要先進(jìn)行排序 402021-6-6 - 41 - 3.數(shù)據(jù)處理與分析技巧3.8合并計算 3例題 對多張明細(xì)表快速合并計算生成匯總表 412021-6-6多張表格式需相同 - 42 - 第四部分公式與函數(shù)基礎(chǔ)PART 04 公式基礎(chǔ)技巧 常用函數(shù) - 43 - 4. 公式與函數(shù)4.1 公式 公式和函數(shù)的區(qū)別與聯(lián)系公式：對數(shù)據(jù)執(zhí)行計算和操作的等式函數(shù)：預(yù)先定義的計算公式，實現(xiàn)復(fù)雜計算和操作 432021-6-6 公式：=(A1+A2+A3+ +A100)/100函數(shù)：=AVERAGE(A1:A100) - 44 - 4. 公式與函數(shù)4.1 公式 公式的組成以等號開始（=）引用（單元格 /區(qū)域 /名稱 ）運算符函數(shù)常量(數(shù)字或文本) 442021-6-6 公式：= 表達(dá)式=(A3-average)/SUM(A1:A20)+12 - 45 - 4. 公式與函數(shù)4.1 公式 例10相對引用/絕對引用相對引用：公式所在單元格位置改變，則引用改變 示例：=B2-B3絕對引用：公式所在單元格位置改變，引用固定不變示例：=$B2/SUM($B$2:$B$12) 通過“$”固定行號列標(biāo) 452021-6-6 - 46 - 4. 公式與函數(shù)4.2 函數(shù)常用函數(shù)SUM(number1,number2, ) 求和函數(shù)AVERAGE(number1,number2, ) 算術(shù)平均函數(shù)IF(logical_test,value_if_true,value_if_false) 判斷函數(shù)COUNTIF（range，criteria）條件計數(shù)函數(shù)SUMIF(range，criteria，sum_range)條件求和函數(shù)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)縱向查找函數(shù) 462021-6-6 - 47 - 4. 公式與函數(shù)4.2 函數(shù)SUM函數(shù)：SUM（Number1,Number2）用途：計算所有參數(shù)數(shù)值的和 47 - 48 - 4. 公式與函數(shù)4.2 函數(shù)AVERAGE函數(shù)：AVERAGE(number1,number2, )用途：計算所有參數(shù)數(shù)值的平均值 48 - 49 - 4. 公式與函數(shù)4.2 函數(shù) 例11IF函數(shù)：IF(Logical,Value_if_true,Value_if_false)用途：判斷是否滿足條件，如果滿足，就返回一個值；如果不滿足則返回另一個值 49 - 50 - 4. 公式與函數(shù)4.2 函數(shù) 例13COUNTIF函數(shù)：COUNTIF(Range,Criteria) 50 用途：計算符合特定條件的個數(shù)要計算其中非空單元格數(shù)目的區(qū)域 以數(shù)字、表達(dá)式、文本形式定義的條件 - 51 - 4. 公式與函數(shù)4.2 函數(shù) 例12SUMIF函數(shù)：SUMIF（Range,Criteria,Sum_Range） 51 用途：對滿足條件的單元格求和條件區(qū)域 求和條件 實際求和區(qū)域 - 52 - 4. 公式與函數(shù)4.2 函數(shù) 例14VLOOKUP函數(shù)：按列查詢，要求查詢的函數(shù)在第一列VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 52 查找的值 查找的區(qū)域 返回數(shù)據(jù)在區(qū)域的第幾列數(shù) 模糊匹配（TRUS/省略）精確匹配（FALSE/0） - 53 - 第五部分?jǐn)?shù)據(jù)圖表化PART 05 圖表基礎(chǔ) 創(chuàng)建圖表 編輯美化圖表 - 54 - 5. 數(shù)據(jù)圖表化5.1 圖表 圖表是數(shù)據(jù)的圖形化表示 圖表構(gòu)成 圖表區(qū) 繪圖區(qū) 數(shù)據(jù)系列（某一行/列） 橫坐標(biāo)軸（分類軸） 縱坐標(biāo)軸（數(shù)值軸） 網(wǎng)格線 圖例 標(biāo)題（圖表標(biāo)題、橫縱坐標(biāo)軸標(biāo)題 ） 數(shù)據(jù)標(biāo)簽 542021-6-6 - 55 - 5. 數(shù)據(jù)圖表化5.1 圖表 常用類型 柱形圖：顯示一段時間內(nèi)的數(shù)據(jù)變化/各項之間的比較情況 折線圖：顯示在相等時間間隔下數(shù)據(jù)的變化趨勢 餅圖：顯示一個數(shù)據(jù)系列中各項大小與各項總和的比例 條形圖：顯示各項之間的比較情況(適于分類軸標(biāo)簽很長的情況) 面積圖：強(qiáng)調(diào)數(shù)量隨時間變化的程度/引起對總值趨勢的注意 552021-6-6 - 56 - 5. 數(shù)據(jù)圖表化5.1 圖表 其他類型 股價圖：顯示股價的波動顯示溫度的波動 曲面圖：顯示兩組數(shù)據(jù)之間的不同組合 圓環(huán)圖：顯示各個部分與整體之間的關(guān)系 氣泡圖：對成組的三個變量進(jìn)行比較 雷達(dá)圖：比較若干數(shù)據(jù)系列的聚合值 562021-6-6 - 57 - 5. 數(shù)據(jù)圖表化5.2 快速創(chuàng)建圖表 方法一：使用功能區(qū)命令 方法二：使用快捷鍵（Alt+F1組合鍵） 方法三：選定數(shù)據(jù)區(qū)域創(chuàng)建 572021-6-6 - 58 - 5. 數(shù)據(jù)圖表化5.2 快速編輯圖表 582021-6-6 快速編輯項目：圖表元素、樣式及顏色、數(shù)據(jù)系列、類別 - 59 - 5. 數(shù)據(jù)圖表化5.3 編輯美化圖表 592021-6-6 柱形圖：數(shù)據(jù)按大小順序排列，突出強(qiáng)調(diào)一部分分類標(biāo)簽文字橫排，便于閱讀柱與柱之間的間距要小于柱的寬度去掉圖例，數(shù)據(jù)直接標(biāo)注的柱形上拒絕三維立體，顏色不超過三種亮色暗色交替，保證黑白打印可讀去掉所有輔助網(wǎng)絡(luò)線 - 60 - 5. 數(shù)據(jù)圖表化5.3 編輯美化圖表 602021-6-6 折線圖：線條要足夠粗，明顯粗過所有非數(shù)據(jù)元素多條折線時，需要強(qiáng)調(diào)的那根，用最粗線型或最深的顏色表示不要使用圖例，直接標(biāo)記在折線后拒絕三維立體，刪除網(wǎng)絡(luò)線等輔助元素不好放太多線條（3），必要時分開做圖標(biāo) - 61 - 5. 數(shù)據(jù)圖表化5.3 編輯美化圖表 612021-6-6 餅圖：適合強(qiáng)調(diào)某部分，次要部分可合并去掉標(biāo)注線和圖例，數(shù)據(jù)標(biāo)注在扇區(qū)拒絕三維餅圖，避免五顏六色扇區(qū)分離/填充暖色/特效/白邊框突出技巧：旋轉(zhuǎn)扇區(qū)/設(shè)置扇區(qū)分離調(diào)整圖表布局 - 62 - 5. 數(shù)據(jù)圖表化5.3 編輯美化圖表 5例題 622021-6-6 復(fù)合圖表：兩軸線柱組合圖適合數(shù)據(jù)相差較大的圖表利用次坐標(biāo)軸完成設(shè)置新版本直接可選擇最匹配的模板 - 63 - 第六部分?jǐn)?shù)據(jù)透視表PART 06 結(jié)構(gòu)與布局 分組與計算 排序與篩選 - 64 - 6. 數(shù)據(jù)透視表6.1 結(jié)構(gòu)與布局創(chuàng)建：單擊數(shù)據(jù)區(qū)域任一單元格選擇區(qū)域，插入透視表 642021-6-6 選擇基礎(chǔ)數(shù)據(jù)區(qū)域透視表存放位置 - 65 - 表格區(qū)字段清單區(qū) 版面設(shè)置區(qū) 6. 數(shù)據(jù)透視表 6.1 結(jié)構(gòu)與布局透視表界面： 字段清單區(qū)、版面設(shè)置區(qū)、表格區(qū)在透視表字段列表中，拖動字段到相關(guān)區(qū)域在透視表字段列表中，拖動字段到相關(guān)區(qū)域 - 66 - 6. 數(shù)據(jù)透視表 6.1 結(jié)構(gòu)與布局分類匯總：是否顯示分類匯總與顯示位置。報表布局：根據(jù)不同需要選擇報表的布局的格式 壓縮形式 大綱形式 表格形式 - 67 - 6. 數(shù)據(jù)透視表 6.2 分組與計算按日期組合創(chuàng)建分組 - 68 - 6. 數(shù)據(jù)透視表 6.2 分組與計算根據(jù)公式計算透視表字段 - 69 - 6. 數(shù)據(jù)透視表 6.3 排序與篩選 方法一：利用透視表字段列表進(jìn)行排序 方法二：利用字段的下拉列表進(jìn)行排序 - 70 - 6. 數(shù)據(jù)透視表 6.3 排序與篩選 方法一：利用字段下拉列表進(jìn)行篩選 方法二：利用字段標(biāo)簽進(jìn)行篩選 - 71 - 712021-6-6