EXCEL培訓

1 EXCEL培訓培訓 EXCEL是日常工作中應用最普是日常工作中應用最普遍的工具。對于審計人員來說，遍的工具。對于審計人員來說，不但要掌握基本的操作，還要學不但要掌握基本的操作，還要學會一些操作技巧以及數(shù)據(jù)分析、會一些操作技巧以及數(shù)據(jù)分析、函數(shù)、圖表等部分高級功能，提函數(shù)、圖表等部分高級功能，提升升EXCEL操作水平，提高工作操作水平，提高工作效率。效率。 引言 3 EXCEL 功能分類 2函數(shù) 1操作 3分析 5VBA 4圖表 4 基礎操作基礎操作 is the 1rd 5 頁面設置和打印頁面設置和打印 設置頁面位置 打印頂端標題行 設置頁眉頁腳 設置分頁符 使用“照相機” 6 2設置頁邊距 設置居中方式設置居中方式 7 3設置頁眉設置頁眉/頁腳頁腳 頁眉頁眉/ /頁腳設置對話框頁腳設置對話框 8 圖圖4-136 自定義頁眉對話框自定義頁眉對話框 頁眉頁眉/ /頁腳設置對話框頁腳設置對話框 9 4工作表 設置頂端標題行設置頂端標題行 10 worlds is the trailing only 常用快捷鍵常用快捷鍵 CTRL+CCTRL+C：復制：復制 CTRL+ZCTRL+Z：剪切：剪切 CTRL+VCTRL+V：粘貼：粘貼 CTRL+Y(F4)CTRL+Y(F4)：重復上一步操作：重復上一步操作 在行或列間雙擊鼠標左鍵，行或列自動寬度自動適應在行或列間雙擊鼠標左鍵，行或列自動寬度自動適應 F8F8 SHIFT+SHIFT+鼠標左鍵：移動列或區(qū)塊鼠標左鍵：移動列或區(qū)塊 輸入身份證：在單元格中，先打個逗號，然后輸入數(shù)字輸入身份證：在單元格中，先打個逗號，然后輸入數(shù)字 11 worlds is the trailing only 常用快捷鍵常用快捷鍵 插入空白單元格插入空白單元格 插入多行多列插入多行多列 轉(zhuǎn)換行列次序轉(zhuǎn)換行列次序 反轉(zhuǎn)表格：復制表格反轉(zhuǎn)表格：復制表格右鍵選擇性粘貼右鍵選擇性粘貼選擇“轉(zhuǎn)置”選擇“轉(zhuǎn)置” 12 excel中如何設單元格為下拉選擇框，從而使其他的單元格的數(shù)值與本單元格的選擇相對應的變化？ 點菜單 數(shù)據(jù)-有效性-設置選項卡-允許下拉選單中，選擇序列-在下面的來源中輸入所有可選的內(nèi)容用逗號隔開，如1，2，3，或者點右邊的選擇按鈕到文件里面選擇一個區(qū)域，如果希望輸入的數(shù)據(jù)不受這個條件的限制，比如我想輸入4而軟件不報錯的話，就切換到 出錯警告 選項卡，去掉 輸入無效數(shù)據(jù)時顯示出錯警告 前面的鉤，確定之后，看一看是不是有了下拉選擇框。 其它的單元格數(shù)值需要隨之變化的話，需要設置公式，建議使用IF公式的嵌套，這個公式比較簡單，容易掌握。 13 隱藏工作表的方法如下：隱藏工作表的方法如下： （1 1）選擇要隱藏的工作表選擇要隱藏的工作表。 （2 2）單擊單擊“格式格式”菜單中菜單中“工作表工作表”命令命令，在彈出的級聯(lián)菜單中在彈出的級聯(lián)菜單中單擊單擊“隱藏隱藏”命令命令。 取消隱藏工作表的方法如下：取消隱藏工作表的方法如下： （1 1）單擊單擊“格式格式”菜單中的菜單中的“工作表工作表”命令命令，在彈出的子菜單中在彈出的子菜單中單擊單擊“取消隱藏取消隱藏”命令命令。 （2 2）在彈出的在彈出的“取消隱藏取消隱藏”對話框中選擇要取消隱藏的工作表對話框中選擇要取消隱藏的工作表。 隱藏隱藏/取消工作表的方法取消工作表的方法 14 1 1工作表窗口的拆分工作表窗口的拆分 拆分可分為三種：水平拆分拆分可分為三種：水平拆分、垂直拆分垂直拆分、水平和垂直同時拆分水平和垂直同時拆分。 2 2工作表窗口的凍結工作表窗口的凍結 窗口凍結操作方法：窗口凍結操作方法： （1 1）選擇凍結位置）選擇凍結位置 （2 2）選擇選擇“窗口窗口”菜單中的菜單中的“凍結窗口凍結窗口”命令命令。 工作表窗口拆分工作表窗口拆分 15 圖圖4-36 工作表窗口拆分示例工作表窗口拆分示例 16 圖圖4-37 窗口凍結示例窗口凍結示例 17 （1 1）格式復制格式復制 方法一： 單擊“編輯”菜單中的“復制”命令。選擇目標區(qū)域，單擊“編輯”菜單中的“選擇性粘貼”命令，在打開的選擇性粘貼對話框中選中“格式”按鈕。 方法二：選“格式刷”按鈕。 （2 2）格式刪除格式刪除 選擇要刪除格式的區(qū)域。選擇“編輯”菜單中的“清除”命令，在彈出的子菜單中選擇“格式”子命令。 工作表格式復制工作表格式復制/刪除刪除 largest economy is the 2rd 函函 數(shù)數(shù) 19 ExcelExcel中的公式及函數(shù)中的公式及函數(shù) 公式是公式是ExcelExcel中數(shù)值關系的具體體現(xiàn)，構成了中數(shù)值關系的具體體現(xiàn)，構成了ExcelExcel的核心，正確地使用任何一個公式必須明確的核心，正確地使用任何一個公式必須明確地把握下面三個因素地把握下面三個因素： 1. 公式中的參數(shù)公式中的參數(shù)：公式中需要哪些參數(shù)，自變量來源公式中需要哪些參數(shù)，自變量來源于何外，公式如何引用其他區(qū)域或單元格于何外，公式如何引用其他區(qū)域或單元格 2. 公式的運算：公式的運算實質(zhì)性機理是什么，有什公式的運算：公式的運算實質(zhì)性機理是什么，有什么約束條件么約束條件 3. 公式運算的結果表現(xiàn)公式運算的結果表現(xiàn)：公式運算的結果表現(xiàn)為什么公式運算的結果表現(xiàn)為什么，其格式如何，其格式如何 20 ExcelExcel中公式的輸入中公式的輸入 ExcelExcel中的公式通常都是以中的公式通常都是以“= =”開頭的一開頭的一個運算式或函數(shù)式，可以將個運算式或函數(shù)式，可以將“= =”引導符看引導符看成是成是E Excelxcel公式的標志。公式的標志。 例如：例如：G5G5單元中有如下內(nèi)容單元中有如下內(nèi)容 =G1+G3+G4*2=G1+G3+G4*2 又如：又如：N9N9單元中有如下內(nèi)容單元中有如下內(nèi)容 =SUM=SUM（A9A9: :M9M9） 這些都是這些都是ExcelExcel中最簡單的公式示例中最簡單的公式示例 21 ExcelExcel公式中的參數(shù)公式中的參數(shù) ExcelExcel中公式中的參數(shù)有下列三種情況：中公式中的參數(shù)有下列三種情況： 1. 1. 常數(shù)：公式中使用某些常數(shù)來參與運算，或由常數(shù)常數(shù)：公式中使用某些常數(shù)來參與運算，或由常數(shù)指明公式的運算方式指明公式的運算方式 2. 2. 系統(tǒng)變量：公式的某些變量來源于計算機系統(tǒng)的自系統(tǒng)變量：公式的某些變量來源于計算機系統(tǒng)的自身參數(shù)，如機器時間等身參數(shù)，如機器時間等 3. 3. 地址引用：對其他工作溥、工作表、區(qū)域或單元格地址引用：對其他工作溥、工作表、區(qū)域或單元格的內(nèi)容的引用，這種引用在是根據(jù)被引用對象的位的內(nèi)容的引用，這種引用在是根據(jù)被引用對象的位置進行的，所以置進行的，所以ExcelExcel中又稱之為地址引用中又稱之為地址引用 22 ExcelExcel公式中對地址的引用方式公式中對地址的引用方式 在在ExcelExcel中公式對其他地址的引用分為三種情況中公式對其他地址的引用分為三種情況： 1. 相對引用：即對另一單元的地址引用是按與當前單元的相對引用：即對另一單元的地址引用是按與當前單元的特定距來確定的，這種引用在該公式被復制時會自動調(diào)特定距來確定的，這種引用在該公式被復制時會自動調(diào)整整 2. 絕對引用：即對被引用單元采用絕對的行列號來引用，絕對引用：即對被引用單元采用絕對的行列號來引用，公式不會自動調(diào)整公式不會自動調(diào)整 3. 混合引用：行或列之一采用絕對引用混合引用：行或列之一采用絕對引用 如果有區(qū)域或單元格命名存在，還可以按名引用如果有區(qū)域或單元格命名存在，還可以按名引用 23 ExcelExcel公式相對與絕對地址操作公式相對與絕對地址操作 在在ExcelExcel中輸入公式時，相對地址與絕對地址可中輸入公式時，相對地址與絕對地址可以進行轉(zhuǎn)換操作以進行轉(zhuǎn)換操作： 凡行或列地址前出現(xiàn)有凡行或列地址前出現(xiàn)有“$ $”標志的，該行或列的標志的，該行或列的地址即為絕對地址引用，否則為相對地址引用地址即為絕對地址引用，否則為相對地址引用 在地址輸入時，相對地址和絕對地址間可以用在地址輸入時，相對地址和絕對地址間可以用F4F4鍵進行循環(huán)轉(zhuǎn)換鍵進行循環(huán)轉(zhuǎn)換 G3 F4 $G$3 F4 G$3 F4 $G3 24 ExcelExcel公式中對地址的操作方式公式中對地址的操作方式 在在ExcelExcel中輸入公式需要進行地址引用時中輸入公式需要進行地址引用時可以采用的操作方式為可以采用的操作方式為： 1. 1. 直接輸入式：直接輸入被引用對象的行列地址直接輸入式：直接輸入被引用對象的行列地址 2. 2. 交互指定式：即用鼠標或光標鍵直接交互式指出交互指定式：即用鼠標或光標鍵直接交互式指出被引用對象的地址被引用對象的地址 3. 3. 名稱粘貼式：如果引用的對象已有命名，可以采名稱粘貼式：如果引用的對象已有命名，可以采用用F3F3鍵來粘貼該對象的名稱于公式之中鍵來粘貼該對象的名稱于公式之中 25 MS ExcelMS Excel公式的自動調(diào)整公式的自動調(diào)整 在在ExcelExcel中公式的相對此用部分會，在該公式自中公式的相對此用部分會，在該公式自身的位置發(fā)生變化，或在其引用對象的位置發(fā)生身的位置發(fā)生變化，或在其引用對象的位置發(fā)生變化時，會自動進行相應的調(diào)整，但應注意一些變化時，會自動進行相應的調(diào)整，但應注意一些細節(jié)細節(jié)： 當公式被復制到它處時，會自動調(diào)整當公式被復制到它處時，會自動調(diào)整 當公式被移動到它處時，不會自動調(diào)整當公式被移動到它處時，不會自動調(diào)整 如被引用對象為一區(qū)域，則在區(qū)域中插入或刪除行如被引用對象為一區(qū)域，則在區(qū)域中插入或刪除行列時，就注意引用這一對象的公式調(diào)整情況列時，就注意引用這一對象的公式調(diào)整情況 26 ExcelExcel公式中的函數(shù)公式中的函數(shù) 函數(shù)是函數(shù)是ExcelExcel中完成復雜運行的一種程序片斷中完成復雜運行的一種程序片斷，它通常是它通常是ExcelExcel自身內(nèi)含的自身內(nèi)含的，必須時也可以由用戶必須時也可以由用戶通過通過VBAVBA擴充擴充。 由于函數(shù)是由于函數(shù)是E Excelxcel復雜運行的核心復雜運行的核心，而電子表格而電子表格又是以運算作為主要功能的又是以運算作為主要功能的，所以一個電子表格所以一個電子表格軟件軟件，其內(nèi)含的函數(shù)數(shù)量基本上可以直接表征這其內(nèi)含的函數(shù)數(shù)量基本上可以直接表征這個軟件的功能廣度和深度個軟件的功能廣度和深度。E Excelxcel內(nèi)部蘊含有豐富內(nèi)部蘊含有豐富的積各類函數(shù)的積各類函數(shù)。 27 1使用公式使用公式 公式中的運算符有以下四類：公式中的運算符有以下四類： （1 1）算術運算符）算術運算符 （2 2）比較運算符）比較運算符 （3 3）文本運算符（）文本運算符（& &） （4 4）引用運算符（）引用運算符（: :） 函函 數(shù)數(shù) 28 輸入公式輸入公式 29 拖拽鼠標復制公式拖拽鼠標復制公式 30 公式和函數(shù)公式和函數(shù)/ /使用函數(shù)使用函數(shù) ExcelExcel有豐富的內(nèi)部函數(shù)有豐富的內(nèi)部函數(shù)，其類型包括：其類型包括： 財務函數(shù)財務函數(shù)、日期與時間函數(shù)日期與時間函數(shù)、數(shù)學與三角函數(shù)數(shù)學與三角函數(shù)、統(tǒng)統(tǒng)計函數(shù)計函數(shù)、查找與引用函數(shù)查找與引用函數(shù)、數(shù)據(jù)庫函數(shù)數(shù)據(jù)庫函數(shù)、文本函數(shù)文本函數(shù)、邏輯邏輯函數(shù)函數(shù)、信息函數(shù)信息函數(shù) （1 1）函數(shù)的輸入函數(shù)的輸入 （2 2）自動求和自動求和 （3 3）自動計算自動計算 （4 4）函數(shù)復制函數(shù)復制 31 ExcelExcel常用常用函數(shù)簡介函數(shù)簡介 函數(shù)名 功能說明 基本語法 SUM 求和 SUM(數(shù)1,數(shù)2，) AVERAGE 求均值 AVERAGE(數(shù)1,數(shù)2，) IF 條件操作 IF(條件表達式,為真時取值,為假時取值) COUNT 數(shù)值單元計數(shù) COUNT(數(shù)1,數(shù)2，) MAX 求最大值 MAX(數(shù)1,數(shù)2，) SIN 正弦函數(shù) SIN(數(shù)) SUMIF 條件求和 SUM(比較區(qū)域，條件表達式，求和區(qū)域） PMT 分期償還額 PMT(利率，總額，現(xiàn)值，未來值，期初/期末) STDEV 樣本標準差 STDEV(數(shù)1,數(shù)2，) 32 （1）函數(shù)的輸入 函數(shù)的輸入有兩種方法：一為直接輸入，一為粘貼函數(shù)法（即使用函數(shù)向?qū)В?圖圖4-16 粘貼函數(shù)對話框粘貼函數(shù)對話框 輸入公式輸入公式 33 自動求和自動求和 自動求和自動求和 34 選擇的區(qū)域選擇的區(qū)域 自動計算自動計算 結果顯示區(qū)結果顯示區(qū) 35 1 1相對引用相對引用 2 2絕對引用絕對引用 3 3三維地址引用三維地址引用 5 5VlookupVlookup的使用的使用 單元格引用單元格引用 36 largest economy 3rd 分析分析 37 篩選數(shù)據(jù)表篩選數(shù)據(jù)表 1 1自動篩選自動篩選 2 2自定義篩選自定義篩選 3 3高級篩選高級篩選 38 下拉箭頭按扭的選擇下拉箭頭按扭的選擇 39 自動篩選之后的清單自動篩選之后的清單 40 多條件篩選的篩選結果多條件篩選的篩選結果 41 圖圖4-81 設置篩選條件設置篩選條件 篩選結果篩選結果 42 2自定義篩選自定義篩選 “自定義自動篩選方式”對話框自定義自動篩選方式”對話框 43 篩選結果篩選結果 44 高級篩選高級篩選 如果用戶要使用高級篩選，一定要先建立一個條件區(qū)域如果用戶要使用高級篩選，一定要先建立一個條件區(qū)域。條件區(qū)域用來指定篩選的數(shù)據(jù)必須滿足的條件。條件區(qū)域用來指定篩選的數(shù)據(jù)必須滿足的條件。 空二行空二行 條件區(qū)條件區(qū)域域 圖圖4-85 條件區(qū)域條件區(qū)域 45 高級篩選對話框高級篩選對話框 46 篩選結果顯示數(shù)據(jù)表格篩選結果顯示數(shù)據(jù)表格 47 復制后的篩選結果復制后的篩選結果 復制的篩復制的篩選結果選結果 48 數(shù)據(jù)排序數(shù)據(jù)排序 1 1默認排序默認排序 2 2按一列排序按一列排序 3 3按多列排序按多列排序 49 默認排序默認排序 ExcelExcel在默認排序時是根據(jù)單元格中的數(shù)據(jù)進行排在默認排序時是根據(jù)單元格中的數(shù)據(jù)進行排序的序的。在按升序排序時在按升序排序時，ExcelExcel使用如下順序：使用如下順序： （1 1）數(shù)值從最小的負數(shù)到最大的正數(shù)排序數(shù)值從最小的負數(shù)到最大的正數(shù)排序。 （2 2）文本和數(shù)字的文本按從文本和數(shù)字的文本按從0 0 9 9，azaz，AZAZ的順序排列的順序排列。 （3 3）邏輯值邏輯值FalseFalse 排在排在TrueTrue 之前之前。 （4 4）所有錯誤值的優(yōu)先級相同所有錯誤值的優(yōu)先級相同。 （5 5）空格排在最后空格排在最后。 50 按一列排序按一列排序 設置排序方式設置排序方式 主主關關鍵鍵字字排排列列順順序序 51 按按“筆試筆試”字段降序排列之后的結果字段降序排列之后的結果 52 以機試降序排列的結果以機試降序排列的結果 53 3按多列排序按多列排序 設置多列排序?qū)υ捒蛟O置多列排序?qū)υ捒?以筆試為主要關鍵字，機試為以筆試為主要關鍵字，機試為次要關鍵字的排序結果次要關鍵字的排序結果 54 分類匯總分類匯總 1 1簡單的分類匯總簡單的分類匯總 2 2創(chuàng)建多級分類匯總創(chuàng)建多級分類匯總 3 3分級顯示數(shù)據(jù)分級顯示數(shù)據(jù) 4 4清除分類匯總清除分類匯總 55 1簡單的分類匯總簡單的分類匯總 分類匯總的結果分類匯總的結果 設置分類匯總對話框設置分類匯總對話框 56 創(chuàng)建多級分類匯總創(chuàng)建多級分類匯總 設置二級分類匯總的結果設置二級分類匯總的結果 57 分級顯示數(shù)據(jù)分級顯示數(shù)據(jù) 下面介紹分級視圖中的各個按鈕的功能：下面介紹分級視圖中的各個按鈕的功能： 一級數(shù)據(jù)按鈕：一級數(shù)據(jù)按鈕： 只顯示數(shù)據(jù)表格中的列標題和匯總結只顯示數(shù)據(jù)表格中的列標題和匯總結果果，該級為最高級該級為最高級。 二級數(shù)據(jù)按鈕：二級數(shù)據(jù)按鈕： 顯示分類匯總結果即二級數(shù)據(jù)顯示分類匯總結果即二級數(shù)據(jù)。 三級數(shù)據(jù)按鈕：三級數(shù)據(jù)按鈕： 顯示所有的詳細數(shù)據(jù)即三級數(shù)據(jù)顯示所有的詳細數(shù)據(jù)即三級數(shù)據(jù)。 分級顯示按鈕：分級顯示按鈕： 表示高一級向低一級展開顯示表示高一級向低一級展開顯示。 分級顯示按鈕：分級顯示按鈕： 表示低一級折疊為高一級數(shù)據(jù)顯示表示低一級折疊為高一級數(shù)據(jù)顯示。 58 顯示一級數(shù)據(jù)顯示一級數(shù)據(jù) 顯示前兩級數(shù)據(jù)顯示前兩級數(shù)據(jù) 59 清除分類匯總清除分類匯總 顯示明細數(shù)據(jù)顯示明細數(shù)據(jù) 60 溫馨提示： 本PPT課件下載后，即可編輯修改， 也可直接使用。 （希望本課件對您有所幫助）