EXCEL培訓(基礎篇)_20140601

1、ExcelExcel培訓培訓（基礎篇）1 12 2Excel 基本操作 公式&函數(shù) 透視表 圖表Excel培訓之3 3基本操作1. 單元格操作2. 排序與篩選3. 名稱的定義4. 數(shù)據(jù)有效性4 4 快速選擇 按【Ctrl】+【箭頭鍵】可移動到工作表中當前數(shù)據(jù)區(qū)域 （數(shù)據(jù)區(qū)域：包含數(shù)據(jù)的單元格區(qū)域，該區(qū)域周圍為空白單元格或數(shù)據(jù)表邊框。）的邊緣。 按【Shift】+【箭頭鍵】可將單元格的選定范圍擴大一個單元格。 按【Ctrl】+【Shift】+【箭頭鍵】可將單元格的選定范圍擴展到與活動單元格同一列或同一行中的最后一個非空白單元格。 快速填充 按【Ctrl】+【G】定位 選擇可見單元格，輸入

2、數(shù)值或公式 按【Ctrl】+【Enter】 絕對引用 按F4或按【Fn】+F4(Win8系統(tǒng)) 日期時間 【Ctrl】+；輸入當前日期 【Ctrl】+：輸入當前時間1. 快捷方式5 51. 單元格操作(1) 單元格基礎 單元格中只能存儲“數(shù)字”和“文本”這兩種形式的數(shù)據(jù) 單元格中相同數(shù)據(jù)的不同顯示方式是通過設置不同的格式來實現(xiàn)的 單元格格式 單元格格式簡介 單元格格式設置 【設置單元格格式】對話框的調用：快捷鍵 6 61. 單元格操作(2) 單元格填充 方法一：快捷鍵 、 方法二：句柄填充（自動填充選項） 默認情況下： 單純數(shù)字或字符 “復制”填充 數(shù)字+字符 “序列”填充 時間日期 “序列”

3、填充 單元格定位 為什么要定位單元格 哪些單元格可以被定位 如何定位“特征”單元格 【定位】對話框的調用： 快捷鍵 、7 72. 排序與篩選 排序 【排序】對話框的調用：快捷鍵 如何自定義排序順序： 編輯自定義列表：office按鈕Excel選項常規(guī) 引用自定義列表：自定義序列 排序的應用：各行插入空白單元行 自動篩選 快捷鍵 篩選TOP項 高級篩選 篩選條件的設置：同行為“并”、跨行為“或” 高級篩選的應用：雙表重復值篩選8 83. 名稱的定義 名稱的概念 名稱是對“常量”、“單元格區(qū)域”和“公式”的封裝 名稱的定義 【名稱管理器】對話框的調用：快捷鍵 名稱定義的特殊方式：地址欄定義法 名稱

4、的作用范圍 Excel應用程序、工作簿和工作表的概念 名稱的應用實例 透視表動態(tài)數(shù)據(jù)源的設置9 94. 數(shù)據(jù)有效性 數(shù)據(jù)有效性的概念 對單元格內輸入的內容進行限制 數(shù)據(jù)有效性的設置 有效性設置 提示性設置 反饋性設置 數(shù)據(jù)有效性的清除 數(shù)據(jù)有效性的應用 避免錄入重復數(shù)據(jù) 二級下拉菜單Excel培訓之1010公式&函數(shù)1. 引用的概念2. 公式的構成3. 常用函數(shù) 時間日期函數(shù) 邏輯函數(shù) 統(tǒng)計函數(shù) 文本函數(shù) 查找引用函數(shù)11111. 引用的概念 引用的概念 根據(jù)行列標識或名稱從指定的單元格或單元格區(qū)域內獲取數(shù)據(jù)的行為 引用的分類 直接引用和間接引用 相對引用和絕對引用（混合引用） 引用的

5、切換方法 快捷鍵 12122. 公式的構成 公式的構成 = 函數(shù)名 ( 參數(shù)1，參數(shù)2， ) 函數(shù)的概念 普通函數(shù)：每一個或一組輸入值對應一個唯一的輸出結果 數(shù)組函數(shù)：每一組輸入值對應一組輸出結果 函數(shù)的輸入 普通函數(shù)： 數(shù)組函數(shù)：13133. 常用函數(shù) 時間日期函數(shù) weekday weeknum datedif 邏輯函數(shù) or and not If isna 統(tǒng)計函數(shù) count/countif/countifs(兩個或多個條件) sum/sumif/sumifs average/averageif/averageifs 文本函數(shù) Trim(刪除多余的空格) Left（從左邊開始取X個數(shù)）

6、 Right（從右邊開始取X個數(shù)） Mid（取中間X個數(shù)） Concatenate（鏈接若干個單元格內的內容，橫向和縱向均可） Replace（替換函數(shù)） Substitute（替換函數(shù)） 查找引用函數(shù) vlookup Match（計算某一個數(shù)在特定順序序列中的順序（排名） Offset（引用，從某個位置出發(fā)，按指定方向、部屬移動后獲得數(shù)） Index（索引函數(shù)，某一區(qū)域內指定坐標對應的數(shù)）Excel培訓之1414透視表1. 透視表的概述2. 透視表的結構3. 創(chuàng)建一個透視表4. 透視表的刷新5. 透視表字段設置6. 透視表排序與篩選15151. 透視表的概述(1) 什么是透視表？ 透視表是用

7、來從EXCEL數(shù)據(jù)清單、關系數(shù)據(jù)庫文件(Access數(shù)據(jù)庫、SQL Server數(shù)據(jù)庫、文本文件、網(wǎng)站數(shù)據(jù))或OLAP多維數(shù)據(jù)集中的特殊字段中總結信息的分析工具。 創(chuàng)建透視表時，用戶可指定所需的字段、透視表的組織形式和要執(zhí)行的計算類型。 建好透視表后，可以對透視表重新安排，以便從不同的角度查看數(shù)據(jù)。透視表的名字來源于它具有“透視”表格的能力，例如將列首的標題轉到行首。這使其成為非常強大的工具。16161. 透視表的概述(2) 透視表可以做什么？ 透視表可以解決利用函數(shù)公式對超大容量的數(shù)據(jù)庫進行數(shù)據(jù)統(tǒng)計帶來的速度瓶頸。 透視表可以通過行、列和頁字段的轉換進行多角度的數(shù)據(jù)分析。 透視表通過對字段的

8、篩選可以對重點關注的內容進行專題分析。 透視表可以不同的工作表和工作簿提取數(shù)據(jù),甚至不用打開數(shù)據(jù)源文件。 透視表可以生成動態(tài)報表,保持與數(shù)據(jù)源的實時更新。 透視表可以通過添加計算字段或計算項進行差異分析。 透視表可以隨時調用相關字段的數(shù)據(jù)源明細數(shù)據(jù)。 數(shù)據(jù)透視圖可以自動生成動態(tài)分析圖表。17171. 透視表的概述(3) 什么情況不適用透視表？ 數(shù)據(jù)源中首行的標題字段空缺或出現(xiàn)合并的標題。 創(chuàng)建透視表后會出現(xiàn)空白字段 每列數(shù)據(jù)的中數(shù)據(jù)類型不一致。 創(chuàng)建透視表后只按一種數(shù)據(jù)類型分類匯總會出現(xiàn)數(shù)據(jù)丟失 數(shù)據(jù)源中出現(xiàn)數(shù)據(jù)斷行。 創(chuàng)建透視表后會出現(xiàn)數(shù)據(jù)丟失 數(shù)據(jù)源中有合并的單元格。 創(chuàng)建透視表后會出現(xiàn)數(shù)

9、據(jù)丟失 數(shù)據(jù)源中有空白的單元格。 創(chuàng)建透視表后會出現(xiàn)對數(shù)值的默認計數(shù)18182. 透視表的結構 透視表的結構 行區(qū)域 列區(qū)域 報表篩選區(qū)域 值區(qū)域19193. 創(chuàng)建一個透視表 創(chuàng)建一個透視表 步驟1：單擊數(shù)據(jù)源中的任意一個單元格如C8，單擊【插入】選項卡單擊【數(shù)據(jù)透視表】按鈕 步驟2：在彈出的【創(chuàng)建數(shù)據(jù)透視表】對話框中選擇放置數(shù)據(jù)透視表的位置。默認的選擇是將數(shù)據(jù)透視表作為新的工作表，保持此選項不變，單擊【確定】按鈕即新建一個Sheet生成一張空的數(shù)據(jù)透視表。如果選擇新建的數(shù)據(jù)透視表存放在已有的工作表，需要選擇【現(xiàn)有工作表】單選按鈕，在【位置】中確定存放位置，單擊【確定】按鈕即在指定位置生成一張

10、空的數(shù)據(jù)透視表。 步驟3：在【數(shù)據(jù)透視表字段列表】對話框內將部門字段移動至【列標簽】區(qū)域、科目名稱字段移動至【行標簽】區(qū)域、勾選【借方】字段。20204. 透視表的刷新 刷新透視表 源數(shù)據(jù)發(fā)生變化后,數(shù)據(jù)透視表并不會自動的刷新數(shù)據(jù)。要對數(shù)據(jù)透視表進行刷新,通常的做法就是在數(shù)據(jù)透視表上單擊鼠標右鍵在彈出的擴展菜單中選擇【刷新】命令。 利用數(shù)據(jù)透視表工具也可以實現(xiàn)刷新數(shù)據(jù)透視表。單擊數(shù)據(jù)透視表在【數(shù)據(jù)透視表工具】【選項】選項卡中單擊【刷新】按鈕。 利用功能區(qū)的【數(shù)據(jù)】選項卡也可以實現(xiàn)刷新數(shù)據(jù)透視表。在【數(shù)據(jù)】選項卡中單擊【全部刷新】按鈕。 透視表刷新設置 設置在打開文件時進行刷新 刷新鏈接在一起的

11、數(shù)據(jù)透視表 刷新基于外部數(shù)據(jù)的數(shù)據(jù)透視表 直接單擊【數(shù)據(jù)】選項卡【連接】按鈕 在彈出的【工作簿連接】對話框中單擊【屬性】按鈕 在彈出的【連接屬性】對話框中勾選【允許后臺刷新】21215. 透視表字段設置 透視表的字段設置 改變數(shù)據(jù)透視表字段的匯總方式 對同一字段應用多種匯總方式 使用自定義的值顯示方式 字段與項的重命名 改變數(shù)據(jù)透視表中的錯誤值顯示22226. 透視表排序與篩選 透視表排序 利用數(shù)據(jù)透視表字段列表對字段排序 利用數(shù)據(jù)透視表工具欄進行排序 手動排序 透視表篩選 利用字段的下拉列表進行篩選 自動篩選數(shù)據(jù)透視表 篩選最大前5項數(shù)據(jù)Excel培訓之2323圖表1. 創(chuàng)建一個圖表2. 圖

12、表的構成與設置3. 圖表制作實例24241. 創(chuàng)建一個圖表 自動創(chuàng)建圖表 選擇數(shù)據(jù)源F11 手動創(chuàng)建圖表 選擇數(shù)據(jù)源【插入】【圖表】選擇適當?shù)膱D表類型25252. 圖表的構成與設置 圖表的構成 數(shù)據(jù)系列 圖表元素 標簽類：標題、坐標軸標題、圖例、數(shù)據(jù)標簽、數(shù)據(jù)表 坐標軸：坐標軸、網(wǎng)絡線 背景類：繪圖區(qū) 分析類：趨勢線、誤差線 數(shù)據(jù)系列的設置 選擇、添加、修改、刪除 圖表元素的設置 選擇、添加、修改、刪除 圖表類型的修改26266. 圖表制作實例(1) 實例1 次坐標軸的使用 坐標軸的設置2525282827274444666633330102030405060702012M012012M022012M032012M042012M052012M06單位：百萬2012年上半年各月KPI完成情況目標值實際值27276. 圖表制作實例(2) 實例2 條形圖的使用 數(shù)據(jù)系列的次序 坐標軸標簽的位置2,0001,00001,0002,0003,0004,0002012M012012M022012M032012M042012M052012M06單位：千封殺用戶凍結用戶28286. 圖表制作實例(3) 實例3 利用“誤差線”對關鍵節(jié)點進行標記 圖表背景的設置DOTC