Excel高效辦公技巧培訓(xùn)課件ppt

1、Excel高效辦公技巧培 訓(xùn) 演講人：職場技能堂演講時間：20XX 課程大 綱 EXCELEXCEL基本操作與數(shù)據(jù)處理基本操作與數(shù)據(jù)處理 0101 EXCELEXCEL數(shù)據(jù)分析數(shù)據(jù)分析 0202 EXCELEXCEL公式與函數(shù)公式與函數(shù) 0303 EXCELEXCEL圖表與圖形圖表與圖形 0404 EXCEL基本操作與數(shù)據(jù) 處理 第一章 基礎(chǔ) 篇 提高 篇 啟動與退出EXCEL 創(chuàng)建工作簿、界 面介紹 工作表操作 單元格操作 設(shè)置表格邊框和底紋 設(shè)置單元格格式、行高 和列寬 數(shù)據(jù)錄入 數(shù)據(jù)有效 性 打印設(shè)置 選擇性黏貼 窗口凍結(jié) 單元格分列 插入復(fù)雜數(shù)據(jù) 第一章 EXCEL基本操作與數(shù)據(jù)處理

2、啟動與退出EXCEL 12啟動退出 利用“開始”菜單啟動Excel 2010 利用桌面上的快捷圖標(biāo)啟動Excel 2010 直接利用已經(jīng)創(chuàng)建的文檔 雙擊Excel窗口左上角的控制菜單圖標(biāo) 選擇“文件”菜單中的“退出”命令 單擊窗口右上角的“關(guān)閉”按鈕 按快捷鍵Alt+F4 第一章 EXCEL基本操作與數(shù)據(jù)處理 桌面右鍵 創(chuàng)建 Excel 創(chuàng)建工作簿 界面介紹 后臺視 圖 快速訪問工 具欄 隱藏選 項卡 功能區(qū)/選項/ 組/命令 編輯 欄 單元格 名稱 自動填 充柄 微型工具欄/常用 菜單 工作表區(qū)/ 標(biāo)簽 狀態(tài) 欄 工作表 視圖 第一章 EXCEL基本操作與數(shù)據(jù)處理 工作表操作 光標(biāo)移至she

3、et名雙擊 01 重命名 同一文檔內(nèi)移動：左 鍵+拖移，復(fù)制： ctrl+左鍵+拖移。 不同文檔間移動：右 鍵選擇移動或復(fù)制 工作表 02 移動和復(fù)制 選擇sheet 右鍵刪除 03 刪除 單元格 選擇單元格右鍵插入、刪除 01 插入 ctrl+C/ctrl+V 02 復(fù)制與粘貼 Delect鍵 03 清除 第一章 EXCEL基本操作與數(shù)據(jù)處理 設(shè)置表格邊框 和底紋 選中需設(shè)置的單元格選擇邊框/底紋 設(shè)置單元格格式、行 高和列寬 單元格格式：單元格格式： 選中單元格右鍵在數(shù) 據(jù)欄選擇格式 奇偶行填充：奇偶行填充： “開始”“條件格式”“新 建規(guī)則”“使用公式確定要 設(shè)置格式的單元格”，輸入公

4、式 =MOD(ROW(),2)=0 并選取 一個填充顏色,即可將所有行 號為偶數(shù)的行添加顏色。 l Excel 2010 自帶很多好 看的套用表格格式,可以 直接用哦 行高和列寬：行高和列寬： 光標(biāo)移至表格頂端選擇 單元格(行/列)設(shè)置行 高/列寬 批量調(diào)整：批量調(diào)整： 選擇批量 行/列 第一章 EXCEL基本操作與數(shù)據(jù)處理 包含文本、數(shù)值、日期、序列數(shù)、時間、批量數(shù)據(jù)、自動填充等，通常輸入文本后設(shè)置單元格 格式(一般使用格式刷更快捷些) 自動填充：自動填充：光標(biāo)移至單元格右下角出現(xiàn)“+”（自動填充柄） 拖移 數(shù)據(jù)錄入 小提小提 示示 如需增加新序列，路徑如下： EXCEL2003：工具-選項

5、-自定義序列 EXCEL2007：Office按鈕-選項-常規(guī)-編輯自定義 列表 EXCEL2010：文件-選項-高級-常規(guī)-編輯自定義列 表 開始-排序和篩選-自定義排 序-自定義序列 使用以下快捷鍵可以快速插入當(dāng)前日期或時間：使用以下快捷鍵可以快速插入當(dāng)前日期或時間： Ctrl+；(分號)在單元格內(nèi)插入當(dāng)前日期， Ctrl+Shift+；(分號)插入當(dāng)前時間，按Ctrl+；(分號)以后輸入一個空格，再按Ctrl+Shift+； (分號)，可以插入當(dāng)前日期和時間,不會自動刷新。 第一章 EXCEL基本操作與數(shù)據(jù)處理 數(shù)據(jù)有效 性 方便錄入、規(guī)范輸入、利用預(yù)定的條件校驗數(shù)據(jù)，以防止無效 數(shù)據(jù)的

6、產(chǎn)生 任務(wù)：設(shè)計一個下拉列表任務(wù)：設(shè)計一個下拉列表 操作步驟：操作步驟： 數(shù)據(jù)數(shù)據(jù)有效性設(shè)置允許中選擇”序列”來源欄中輸入允許輸入的值 (例如男,女）記得中間用英文逗號,隔開 第一章 EXCEL基本操作與數(shù)據(jù)處理 打印設(shè)置 Excel表格編輯的最后一項任務(wù)往往是打印。在實際工作中，我們的打印任務(wù) 往往不是那么簡單輕松，比如我們希望只打印工作表中需要的區(qū)域，或者希 望能打印公式本身而不是公式的計算結(jié)果等等。Excel能為我們提供靈活的打 印選擇，我們需要對上圖的功能進行巧妙應(yīng)用。 第一章 EXCEL基本操作與數(shù)據(jù)處理 選擇性粘貼帶實時預(yù)覽的粘貼功 能 【公式】：只粘帖文本和公式，不粘帖字體、格式

7、 （字體、對齊、文字方向、數(shù)字格式、底紋等）、邊 框、注釋、內(nèi)容校驗等；（當(dāng)復(fù)制公式時，單元格引 用將根據(jù)所用引用類型而變化。如要使單元格引用保 證不變，請使用絕對引用。） 【數(shù)值】：只粘帖文本，單元格的內(nèi)容是計算公式的話 只粘帖計算結(jié)果，這兩項不改變目標(biāo)單元格的格式； 【圖片】 ：將表格以圖片形式復(fù)制，可以較好 地保持原有的形狀。 【格式】：僅粘貼源單元格格式，但不能粘貼單 元格的有效性粘帖格式，包括字體、對齊、文字 方向、邊框、底紋等，不改變目標(biāo)單元格的文字 內(nèi)容。（功能相當(dāng)于格式刷）； 【批注】：把源單元格的批注內(nèi)容拷貝過來，不 改變目標(biāo)單元格的內(nèi)容和格式； 【有效性驗證】：將復(fù)制單元格

8、的數(shù)據(jù)有效性規(guī) 則粘貼到粘貼區(qū)域，只粘帖有效性驗證內(nèi)容，其 他不變； 【除邊框外的所有內(nèi)容和格式】：粘帖除邊框外 的所有內(nèi)容和格式，保持目標(biāo)單元格和源單元格 相同的內(nèi)容和格式； 【列寬】：將某個列寬或列的區(qū)域粘貼到另一個 列或列的區(qū)域，使目標(biāo)單元格和源單元格擁有同 樣的列寬，不改變內(nèi)容和格式 【公式和數(shù)字格式】：僅從選中的單元格粘貼公 式和所有數(shù)字格式選項； 【值和數(shù)字格式】：僅從選中的單元格粘貼值和 所有數(shù)字格式選項； 【置換】 ：行列進行互換 第一章 EXCEL基本操作與數(shù)據(jù)處理 窗口凍 結(jié) 當(dāng)我們在制作一個Excel表格時，有時會遇到行數(shù)和 列數(shù)都比較多，一旦向下或向右滾屏?xí)r，上面的標(biāo)題

9、 行或最左邊的那些列就會跟著滾動，這樣在處理數(shù)據(jù) 時往往難以分清各行或列數(shù)據(jù)對應(yīng)的標(biāo)題，這時就可 以利用EXCEL的凍結(jié)窗格這一功能來解決這個問題。 效果：效果：始終顯示標(biāo)題欄 操作：操作： 選中開始凍結(jié)的單 元格 視圖凍結(jié)窗口 第一章 EXCEL基本操作與數(shù)據(jù)處理 分列 Excel中分列是對某一數(shù)據(jù)按一定的規(guī) 則分成兩列以上。分列時，選擇要進行 分列的數(shù)據(jù)列或區(qū)域，再從數(shù)據(jù)菜單中 選擇分列，分列有向?qū)?，按照向?qū)нM行 即可。關(guān)鍵是分列的規(guī)則，有固定列寬， 但一般應(yīng)視情況選擇某些特定的符號， 如空格、逗號、分號等。 插入復(fù)雜數(shù)據(jù) 若是插入形狀/超鏈接/文本框，按 導(dǎo)向進行即可；若是插入對象，操

10、作步驟為對象有文件創(chuàng)建瀏覽 更改圖標(biāo) 更改標(biāo)題 確定 EXCEL數(shù)據(jù)分 析 第二章 基礎(chǔ) 篇 提高 篇 數(shù)據(jù)的排序 數(shù)據(jù)的篩選 數(shù)據(jù)分類 匯總 數(shù)據(jù)透 視 第二章 EXCEL數(shù)據(jù)分析 數(shù)據(jù)的排序 數(shù)據(jù)的排序是把一列或多列無序的數(shù) 據(jù)變成有序的數(shù)據(jù)，這樣更能方便地 管理數(shù)據(jù)，在進行數(shù)據(jù)排序時可以按 照默認(rèn)排序順序，也可以按照單列或 多列排序順序和自定義排序順序來排 序。排序時，Excel將利用指定的排 序順序重新排列行、列以及各單元格。 操作步驟：操作步驟： 鼠標(biāo)選擇標(biāo)題欄開始 排序與篩選篩選 單擊下拉菜單鈕選擇顏色 或勾選需要顯示的內(nèi)容 操作步驟：操作步驟： 鼠標(biāo)選擇標(biāo)題欄開始排序與篩 選自定

11、義排序選擇是否帶擴展 (帶擴展同行數(shù)據(jù)聯(lián)動換位子，否則 不聯(lián)動) 添加篩選條件X3次點 選部門、職務(wù)、工號、升序/降序 勾選包含標(biāo)題（很關(guān)鍵） 數(shù)據(jù)的篩選 第二章 EXCEL數(shù)據(jù)分析 數(shù)據(jù)分類與匯 總 對重復(fù)數(shù)據(jù)中的值進行 分類匯總 數(shù)據(jù)透視 數(shù)據(jù)透視表是交互式報表，可快速 合并和比較大量數(shù)據(jù)。您可旋轉(zhuǎn)其 行和列以看到源數(shù)據(jù)的不同匯總， 而且可顯示感興趣區(qū)域的明細(xì)數(shù)據(jù) EXCEL公式與函 數(shù) 第三章 基礎(chǔ) 篇 提高 篇 單元格 引用 進階函 數(shù) 相對引用 絕對引用 混合引用 常用函 數(shù) 基礎(chǔ)運算函數(shù) COUNT函數(shù) 日期和時間函數(shù) 嵌套函 數(shù) IF函數(shù) VLOOKUP函數(shù) SUMIF函數(shù) DA

12、TEDIF函數(shù) 字符串截取函數(shù) SUMPRODUCT函數(shù) 多重IF函數(shù) 第三章 EXCEL公式與函數(shù) 絕對引用（$C$1） 相對引用(C1) 混合引用($C1、 C$1) 單元格引用 如果在D4單元格中輸入“=$C$4”，那么不論你將D4 向哪個方向拖動，自動填充的公式都是“=$C$4”。 原來 誰前面帶上了“$”號，在進行拖動時誰就不變。如果都 帶上了“$”，在拖動時兩個位置都不能變 在一個工作表中，在C4、C5中的數(shù)據(jù)分別是60、50。 如果在D4單元格中輸入“=C4”，那么將D4向下拖動到D5 時，D5中的內(nèi)容就變成了50，里面的公式是“=C5”，將 D4向右拖動到E4，E4中的內(nèi)容是6

13、0，里面的公式變成了 “=D4” 現(xiàn)在在D4單元格中輸入“=$C4”，將D4向右拖動到E4， E4中的公式還是“=$C4”，而向下拖動到D5時，D5中的公式 就成了“=$C5”。如果在D4單元格中輸入“=C$4”，那么將 D4向右拖動到E4時，E4中的公式變?yōu)椤?D$4”，而將D4向下 拖動到D5時，D5中的公式還是“=C$4” 第三章 EXCEL公式與函數(shù) 分類分類函數(shù)名稱函數(shù)名稱作用說明作用說明 基礎(chǔ)運算函數(shù) SUM求和 AVERAGE求平均值 MAX取最大值 MIN取最小值 ROUND對數(shù)字四舍五入的保留指定位數(shù)的小數(shù)點位數(shù) LEN統(tǒng)計單元格內(nèi)的字符數(shù) COUNT函數(shù) COUNT統(tǒng)計數(shù)值

14、類單元格個數(shù)（只有數(shù)字類的單元格才被統(tǒng)計） COUNTA 統(tǒng)計非空單元格個數(shù)（含空格、日期、邏輯值、文字的單元格 均納入統(tǒng)計） COUNTIF統(tǒng)計符合指定條件的單元格個數(shù)(非文本格式下的數(shù)字才被統(tǒng)計） 日期和時間函 數(shù) YEAR提取年份 DAY提取日 MONTH提取月 HOUR提取小時 TODAY返回當(dāng)前日期 常用函數(shù) 第三章 EXCEL公式與函數(shù) 進階函數(shù) IFIF函數(shù)函數(shù) 根據(jù)邏輯表達式測試的結(jié)果，返回相應(yīng)的值。 函數(shù)語法：函數(shù)語法：IF(條件表達式,結(jié)果1,結(jié)果2) 語法理解：語法理解：如果就否則 VLOOKUPVLOOKUP函數(shù)函數(shù) 在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由 此返回表

15、格或數(shù)組當(dāng)前行中指定列處的數(shù)值。 函數(shù)語法：函數(shù)語法：VLOOKUP(查找值,查找區(qū)域,返回結(jié)果 的列號,查找方式) 語法理解：語法理解：在“查找區(qū)域”的首列查找“查找 值”,并由此返回區(qū)域內(nèi)當(dāng)前行中指定列的數(shù)值 SUMIFSUMIF函數(shù)函數(shù) 根據(jù)指定條件對若干單元格、區(qū)域或引用求和。 函數(shù)語法：函數(shù)語法：SUMIF（條件判斷區(qū)域,條件,求和區(qū) 域） 語法理解：語法理解：如果滿足什么條件就求和，其實 就是按條件求和。 DATEDIFDATEDIF函數(shù)函數(shù) 返回兩個日期之間的年月日間隔數(shù)。 函數(shù)語法：函數(shù)語法：DATEDIF (開始日期,結(jié)束日期,單位代碼) 年齡或年限的計算方法（在英文輸入狀態(tài)

16、下進行）： 語法：語法：=YEAR（終止日期）-YEAR（出生或開始日 期） 語法：語法：=DATEDIF(出生或開始日期,終止日期， Y) 語法：語法：=DATEDIF(出生或開始日期,TODAY(),Y) 說明：說明：日期可以直接單元格引用、可以 / 或- 格式輸入，但不能輸入（.）。 字符串截取函數(shù)字符串截取函數(shù) LEFTLEFT函數(shù)：函數(shù)： LEFT(文本串,要提取的字符個數(shù)) 從左邊開始提取指定個數(shù)的字符 RIGHTRIGHT函數(shù)：函數(shù)： RIGHT(文本串,要提取的字符個數(shù)) 從右邊開始提取指定個數(shù)的字符 MIDMID函數(shù)：函數(shù)： MID(文本串,文本中要提取的第一個字符的位置,要

17、提取 的字符個數(shù)) 第三章 EXCEL公式與函數(shù) 嵌套函數(shù) SUMPRODUCTSUMPRODUCT函數(shù)函數(shù)IFIF函數(shù)嵌套函數(shù)嵌套 計算相應(yīng)的區(qū)域或數(shù)組乘積的和 公式運算=SUMPRODUCT(A2:A6,B2:B6,C2:C6) 實際運算 =A2*B2*C2+A3*B3*C3+A4*B4*C4+A5*B5*C5+A6*B6*C6 (即三個區(qū)域A2:A6,B2:B6,C2:C6同行數(shù)據(jù)積的和) 計算符合計算符合2 2個及以上條件的數(shù)據(jù)個數(shù)或數(shù)據(jù)的和個及以上條件的數(shù)據(jù)個數(shù)或數(shù)據(jù)的和 公式運算 =SUMPRODUCT($C$1:$C$9=$G5)*($D$1:$D$9=H$4),$E$1: $E

18、$9) 實際運算=SUMPRODUCT（條件1*條件2，求和數(shù)據(jù)區(qū)域) 多條件引用多條件引用/ /匹配匹配 公式運算 =SUMPRODUCT($C$1:$C$9=$G5)*($D$1:$D$9=H$4)*($E$1: $E$9) 實際運算=SUMPRODUCT（條件1*條件2*要引用的數(shù)據(jù)區(qū)域) IF函數(shù)嵌套就是有多個IF函數(shù)一層套一層，什么時 候會用到IF嵌套呢，是有多個條件要分別判斷時會 用到,語法如下： = IF(true,1, IF(true,2, IF(true,3,end)語法解釋：語法解釋：IF（第一個條件,第一個條件成立時返回 的值, IF(第二個條件,第二個條件成立時返回的值,