EXCEL常用函數培訓教程.ppt

《EXCEL常用函數培訓教程.ppt》由會員分享，可在線閱讀，更多相關《EXCEL常用函數培訓教程.ppt（30頁珍藏版）》請在裝配圖網上搜索。

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