EXCEL常用函數(shù)培訓(xùn)教程.ppt

EXCEL常用函數(shù)培訓(xùn) 學(xué)習(xí)內(nèi)容 1EXCEL快捷鍵操作2數(shù)學(xué)與統(tǒng)計函數(shù)3邏輯函數(shù)4文本函數(shù)5查找與引用函數(shù)6數(shù)據(jù)分類與匯總7數(shù)據(jù)透視表 1EXCEL快捷鍵操作 實操 1EXCEL快捷鍵操作 1EXCEL快捷鍵操作 2數(shù)學(xué)與統(tǒng)計函數(shù) 2 1ROUND函數(shù) 將數(shù)字四舍五入用途 按指定位數(shù)四舍五入某個數(shù)字 語法 ROUND number num digits 參數(shù)意義 Number是需要四舍五入的數(shù)字 Num digits為指定的位數(shù) Number按此位數(shù)進行處理 注意 1 在單元格格式中可以設(shè)置小數(shù)位數(shù) 但數(shù)字本身并沒有真正的四舍五入 只是以2位 如設(shè)置為2 的形式顯示數(shù)據(jù) 如果財務(wù)中采用這種方式進行計算 經(jīng)常會出現(xiàn)幾分錢的誤差的 2 如果num digits大于0 則四舍五入到指定的小數(shù)位 實例 如果A1 65 25 則公式 ROUND A1 1 返回65 3如果num digits等于0 則四舍五入到最接近的整數(shù) 實例 如果A1 65 25 則公式 ROUND A1 0 返回65如果num digits小于0 則在小數(shù)點左側(cè)按指定位數(shù)四舍五入 實例 如果A1 65 25 則公式 ROUND A1 1 返回70 實例剖析 2數(shù)學(xué)與統(tǒng)計函數(shù)2 2RANK函數(shù) 統(tǒng)計排名 用途 返回一個數(shù)字在數(shù)字列表中的排位 數(shù)字的排位是其大小與列表中其他值的比值 如果列表已排過序 則數(shù)字的排位就是它當(dāng)前的位置 語法 RANK number ref order 參數(shù)意義 Number為需要找到排位的數(shù)字 Ref為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用 Order為一數(shù)字 指明排位的方式 如果order為0或省略 則按照降序排列 如果order不為零則按升序排列的列表 注意 相同數(shù)值用RANK函數(shù)計算得到的序數(shù) 名次 相同 但會導(dǎo)致后續(xù)數(shù)字的序數(shù)空缺 實例剖析 2 3最大值函數(shù)MAX 最小值函數(shù)MIN語法 MAX number1 number2 MIN number1 number2 參數(shù)意義 number1 number2 是需要找出最大值 最小值 的1至30個數(shù)值 數(shù)組或引用 2 4AVERAGE 平均值函數(shù)語法 AVERAGE number1 number2 參數(shù)意義 number1 number2 是需要計算平均值的1 30個參數(shù) 注意 函數(shù)中的參數(shù)可以是數(shù)字 空白單元格 邏輯值或數(shù)字的文本形式 如果參數(shù)是不能轉(zhuǎn)換為數(shù)字的內(nèi)容將導(dǎo)致錯誤 如果參數(shù)為數(shù)組或引用 則只有數(shù)組或引用中的數(shù)字參與計算 空白單元格 邏輯值或文本則被忽略 2數(shù)學(xué)與統(tǒng)計函數(shù) 功能 返回數(shù)據(jù)集中第K個最大值 語法 LARGE array k 參數(shù) array為需要找到第k個最大值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域 k為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置 從大到小 實例 如果A1 24 A2 5 A3 7 A4 15 A5 0 A6 35 A7 2 A8 5 則公式 LARGE A1 A8 3 返回15 即數(shù)組中第3個小的數(shù)字是15 A4 數(shù)學(xué)與統(tǒng)計函數(shù)2 5LARGE函數(shù) 2 6SMALL函數(shù)用途 返回數(shù)據(jù)集中的第K個最小值 語法 SMALL array k 參數(shù) array為需要找到第k個最小值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域 k為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置 從小到大 實例 如果A1 24 A2 5 A3 7 A4 15 A5 0 A6 9 A7 2 A8 5 則公式 SMALL A1 A8 5 返回7 即數(shù)組中第5個小的數(shù)字是7 實例剖析 數(shù)學(xué)與統(tǒng)計函數(shù) 3 1IF函數(shù) 進行條件檢測功能 執(zhí)行真假值判斷 根據(jù)邏輯計算的真假值 返回不同結(jié)果 可以使用函數(shù)IF對數(shù)值和公式進行條件檢測 語法 IF logical test value if true value if false 參數(shù) Logical test表示計算結(jié)果為TRUE或FALSE的任意值或表達(dá)式 Value if truelogical test為TRUE時返回的值 Value if falselogical test為FALSE時返回的值 實例剖析 3邏輯函數(shù) 3邏輯函數(shù) 功能 所有參數(shù)的邏輯值為真時 返回TRUE 只要一個參數(shù)的邏輯值為假 即返回FLASE 語法 AND logical1 logical2 參數(shù) Logical1 logical2 表示待檢測的1到30個條件值 各條件值可為TRUE或FALSE 3 2AND函數(shù) 判斷多個條件同時為真 3 3OR函數(shù) 判斷任一參數(shù)為真 功能 在其參數(shù)組中 任何一個參數(shù)邏輯值為TRUE 即返回TRUE 所有參數(shù)的邏輯值為FALSE 才返回FALSE 語法 OR logical1 logical2 參數(shù) Logical1 logical2 為需要進行檢驗的1到30個條件表達(dá)式 實例剖析 條件求和函數(shù)SUMIF 語法 SUMIF range criteria sum range 參數(shù) range是用于條件判斷的單元格區(qū)域 criteria是由數(shù)字 邏輯表達(dá)式等組成的判定條件 為確定哪些單元格將被相加求和的條件 其形式可以為數(shù)字 表達(dá)式或文本sum range為需要求和的單元格 區(qū)域或引用 實例操作 4 1MID LEFT RIGHT函數(shù) 獲取字符4 1 1MID函數(shù)功能 從文本字符串中指定的起始位置起返回指定長度的字符語法 Mid text start num num chars 參數(shù) text準(zhǔn)備從中提取字符串的文本字符串start num準(zhǔn)備提取的第一個字符的位置num chars指定所要提取的字符串的長度4 1 2RIGHT函數(shù)功能 從一個文本字符串的最后一個字符開始返回指定個數(shù)字符語法 RIGHT text num chars 參數(shù) text要提取字符的字符串num chars要提取的字符數(shù) 如果忽略 為14 1 3LEFT函數(shù)功能 從一個文本字符串的第一個字符開始返回指定個數(shù)字符語法 LEFT text num chars 參數(shù) text要提取字符的字符串num chars要提取的字符數(shù) 如果忽略 為1實例剖析 4文本函數(shù) 4 2CONCATENATE函數(shù) 4文本函數(shù) 功能 將幾個文本字符串合并為一個文本字符串 語法 CONCATENATE text1 text2 參數(shù) Text1 text2 為1到30個將要合并成單個文本項的文本項 這些文本項可以為文本字符串 數(shù)字或?qū)蝹€單元格的引用 補充說明 也可以用 和號 運算符代替函數(shù)CONCATENATE實現(xiàn)文本項的合并 實例剖析 4文本函數(shù) 4 3LOWER UPPER PROPER 大小寫轉(zhuǎn)換 4 3 1LOWER功能 將一個文本字符串中的所有大寫字母轉(zhuǎn)換為小寫字母 語法 LOWER text 參數(shù) Text是要轉(zhuǎn)換為小寫字母的文本 4 3 2UPPER功能 將文本轉(zhuǎn)換為大寫形式語法 UPPER text 參數(shù) Text是要轉(zhuǎn)換為大寫形式的文本4 3 3PROPER功能 將文本字符串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫 將其余的字母轉(zhuǎn)換成小寫 語法 PROPER text 參數(shù) Text包括在一組雙引號中的文本字符串 返回文本值的公式或是對包含文本的單元格的引用 實例剖析 功能 在表格數(shù)組的首列查找指定的值 并由此返回表格數(shù)組當(dāng)前行中其他列的值 VLOOKUP中的V參數(shù)表示垂直方向 語法 VLOOKUP lookup value table array col index num range lookup 參數(shù) Lookup value為需要在數(shù)據(jù)表首列進行搜索的值 Table array為兩列或多列數(shù)據(jù) 使用對區(qū)域或區(qū)域名稱的引用 table array第一列中的值是由lookup value搜索的值 這些值可以是文本 數(shù)字或邏輯值 文本不區(qū)分大小寫 Col index num為table array中待返回的匹配值的列序號 Col index num為1時 返回table array第一列中的數(shù)值 為2 返回table array第二列中的數(shù)值 以此類推 Range lookup為邏輯值 指定希望VLOOKUP查找精確的匹配值還是近似匹配值 1 如果為FALSE VLOOKUP將只尋找精確匹配值 在此情況下 table array第一列的值不需要排序 如果table array第一列中有兩個或多個值與lookup value匹配 則使用第一個找到的值 如果找不到精確匹配值 則返回錯誤值 N A 2 如果為TRUE或省略 則返回精確匹配值或近似匹配值 也就是說 如果找不到精確匹配值 則返回小于lookup value的最大數(shù)值 table array第一列中的值必須以升序排序 否則VLOOKUP可能無法返回正確的值 實例剖析 5查找與引用函數(shù)VLOOKUP 6數(shù)據(jù)分類與匯總 6 1概述分類匯總是對工作表中指定的行或列中的數(shù)據(jù)進行匯總統(tǒng)計 通過折疊或展開原工作表中的行 列數(shù)據(jù)及匯總結(jié)果 從匯總和明細(xì)兩種角度顯示數(shù)據(jù) 可快捷地創(chuàng)建各種匯總報告 分類匯總的數(shù)據(jù)折疊層次可達(dá)8層 6 2功能在數(shù)據(jù)表中顯示一組或多組數(shù)據(jù)的分類匯總數(shù)據(jù) 在分組數(shù)據(jù)上完成不同的計算 如求和 統(tǒng)計個數(shù) 求平均數(shù) 求最大 最小值 等 6數(shù)據(jù)的分類與匯總6 3建立分類匯總準(zhǔn)備工作 分類匯總表的每個數(shù)據(jù)列都有列標(biāo)題 對要進行分類的列排序 說明 排序的列標(biāo)題稱為分類匯總關(guān)鍵字 在進行分類總時 只能指定排序后的列標(biāo)題為匯總關(guān)鍵字 操作方法 選擇 數(shù)據(jù) 分類匯總 菜單 打開分類匯總對話框 在對話框中完成操作 6數(shù)據(jù)的分類與匯總 匯總結(jié)果保存替換當(dāng)前分類匯總 最后一次的匯總會取代以前的分類匯總 每組數(shù)據(jù)分頁 各種不同的分類數(shù)據(jù)將被分頁保存 匯總結(jié)果顯示在數(shù)據(jù)下方 原數(shù)據(jù)的下方會顯示匯總計算的結(jié)果 說明 以上3種方式可同時選中 Excel的默認(rèn)選擇是第1和第3項 6數(shù)據(jù)的分類與匯總 實例剖析 6數(shù)據(jù)的分類與匯總 分類匯總的刪除對剛建立的分類匯總 若要恢復(fù)為原始數(shù)據(jù)表 則選擇 編輯 中的 撤消 菜單項取消分類匯總 或單擊撤消按鈕 若建立分類匯總之后又進行了其他操作 可按如下方法刪除分類匯總 1 單擊分類匯總表中的任一單元格 2 選擇 數(shù)據(jù) 分類匯總 菜單項 3 單擊 分類匯總 對話框中的 全部刪除 按鈕 實例剖析 7數(shù)據(jù)透視表 7 1數(shù)據(jù)透視表的作用能夠改變數(shù)據(jù)表的行 列布局 能夠快速匯總大量數(shù)據(jù) 能夠基于原數(shù)據(jù)表創(chuàng)建數(shù)據(jù)分組 并對分組進行匯總統(tǒng)計 7數(shù)據(jù)透視表 7 2數(shù)據(jù)透視表的建立選擇 數(shù)據(jù) 數(shù)據(jù)透視表和數(shù)據(jù)透視圖 按向?qū)崾咀鐾? 2步 在第3步中選擇 布局 按鈕 出現(xiàn)如圖所示的對話框 7數(shù)據(jù)透視表 解釋 行 拖放到行中的數(shù)據(jù)字段中的每個數(shù)據(jù)項將占據(jù)透視表的一行 列 拖放到列中的數(shù)據(jù)字段中的每個數(shù)據(jù)項將占據(jù)透視表的一列 行和列確定一個二維表格 頁 拖放在頁中的字段 Excel將按該字段的數(shù)據(jù)項對透視表進行分頁 數(shù)據(jù) 進行計數(shù)或匯總的字段名稱 7數(shù)據(jù)透視表 7 3修改數(shù)據(jù)透視表顯示或隱藏行 列中的數(shù)據(jù)項在行或列字段的下拉列表條中選擇或取消復(fù)選標(biāo)志 重新組織數(shù)據(jù)透視表行 列置換 鼠標(biāo)移至行字段上按住拖動到列字段上 列方法類似 在字段內(nèi)移動個別項 鼠標(biāo)按在要移動字段邊框線上拖動到指定位置放開 在原有行列字段上增加或減少字段增加 單擊數(shù)據(jù)透視表 在數(shù)據(jù)透視表工具欄上將所需字段拖入行或列的字段處 減少 選擇數(shù)據(jù)透視表行或列的字段名 拖到表外 7數(shù)據(jù)透視表 修改數(shù)據(jù)透視表的概要函數(shù)概要函數(shù) 即數(shù)據(jù)透視表中使用的透視函數(shù) 如 求和 計數(shù) 平均 百分比 最大值 標(biāo)準(zhǔn)偏差等 操作方法 選擇數(shù)據(jù)透視表中數(shù)據(jù)字段 在對象菜單或數(shù)據(jù)透視表工具欄中選擇 字段設(shè)置 在數(shù)據(jù)透視表對話框的匯總方式列表中選擇所需函數(shù) 7數(shù)據(jù)透視表 修改透視表數(shù)據(jù)的顯示方式 見數(shù)據(jù)透視表 修改數(shù)據(jù)透視表的顯示方式 操作方法 選擇數(shù)據(jù)透視表中數(shù)據(jù)字段 在對象菜單或數(shù)據(jù)透視表工具欄中選擇 字段設(shè)置 在數(shù)據(jù)透視表對話框中單擊 選項 按鈕 在 數(shù)據(jù)顯示方式 列表中選擇所需的顯示方式 實例剖析 綜合案例分析1 案例 如近期從系統(tǒng)導(dǎo)出SV 5700BOM表 發(fā)現(xiàn)與前期所導(dǎo)出的BOM表 相關(guān)物料及用量均有差異 如后期作了技術(shù)更改 現(xiàn)要找出有變化的物料 做具體分析 案例中應(yīng)用到的函數(shù) 1 CONCATENATE函數(shù) 2 條件求和函數(shù)SUMIF 3 數(shù)據(jù)透視表 4 查找與引用函數(shù)VLOOKUP 案例2根據(jù)銷售訂單統(tǒng)計相關(guān)數(shù)據(jù) 謝謝