EXCEL培訓(xùn)(基礎(chǔ)篇)_20140601

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

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

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

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

5、切換方法 快捷鍵 12122. 公式的構(gòu)成 公式的構(gòu)成 = 函數(shù)名 ( 參數(shù)1，參數(shù)2， ) 函數(shù)的概念 普通函數(shù)：每一個或一組輸入值對應(yīng)一個唯一的輸出結(jié)果 數(shù)組函數(shù)：每一組輸入值對應(yīng)一組輸出結(jié)果 函數(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（鏈接若干個單元格內(nèi)的內(nèi)容，橫向和縱向均可） Replace（替換函數(shù)） Substitute（替換函數(shù)） 查找引用函數(shù) vlookup Match（計算某一個數(shù)在特定順序序列中的順序（排名） Offset（引用，從某個位置出發(fā)，按指定方向、部屬移動后獲得數(shù)） Index（索引函數(shù)，某一區(qū)域內(nèi)指定坐標(biāo)對應(yīng)的數(shù)）Excel培訓(xùn)之1414透視表1. 透視表的概述2. 透視表的結(jié)構(gòu)3. 創(chuàng)建一個透視表4. 透視表的刷新5. 透視表字段設(shè)置6. 透視表排序與篩選15151. 透視表的概述(1) 什么是透視表？ 透視表是用

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

8、篩選可以對重點(diǎn)關(guān)注的內(nèi)容進(jìn)行專題分析。 透視表可以不同的工作表和工作簿提取數(shù)據(jù),甚至不用打開數(shù)據(jù)源文件。 透視表可以生成動態(tài)報表,保持與數(shù)據(jù)源的實時更新。 透視表可以通過添加計算字段或計算項進(jìn)行差異分析。 透視表可以隨時調(diào)用相關(guān)字段的數(shù)據(jù)源明細(xì)數(shù)據(jù)。 數(shù)據(jù)透視圖可以自動生成動態(tài)分析圖表。17171. 透視表的概述(3) 什么情況不適用透視表？ 數(shù)據(jù)源中首行的標(biāo)題字段空缺或出現(xiàn)合并的標(biāo)題。 創(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ù)值的默認(rèn)計數(shù)18182. 透視表的結(jié)構(gòu) 透視表的結(jié)構(gòu) 行區(qū)域 列區(qū)域 報表篩選區(qū)域 值區(qū)域19193. 創(chuàng)建一個透視表 創(chuàng)建一個透視表 步驟1：單擊數(shù)據(jù)源中的任意一個單元格如C8，單擊【插入】選項卡單擊【數(shù)據(jù)透視表】按鈕 步驟2：在彈出的【創(chuàng)建數(shù)據(jù)透視表】對話框中選擇放置數(shù)據(jù)透視表的位置。默認(rèn)的選擇是將數(shù)據(jù)透視表作為新的工作表，保持此選項不變，單擊【確定】按鈕即新建一個Sheet生成一張空的數(shù)據(jù)透視表。如果選擇新建的數(shù)據(jù)透視表存放在已有的工作表，需要選擇【現(xiàn)有工作表】單選按鈕，在【位置】中確定存放位置，單擊【確定】按鈕即在指定位置生成一張

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

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

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