EXCEL實(shí)用技巧EXCEL常用函數(shù)簡(jiǎn)介.ppt

EXCEL實(shí)用技巧 EXCEL常用函數(shù)簡(jiǎn)介 生技部2009年11月25日 主要內(nèi)容 SUM函數(shù) 1 AVERAGE AVERAGEA TRIMMEAN函數(shù) 2 COUNT COUNTA計(jì)數(shù)函數(shù) 3 4 5 函數(shù)基礎(chǔ)知識(shí) ROUND TRUNC函數(shù) 主要內(nèi)容 IF函數(shù) COUNTIF SUMIF 7 8 9 公式中常見的錯(cuò)誤信息 10 FREQUNCY陣列函數(shù) MOD函數(shù)在美化表格中的應(yīng)用 常用函數(shù)快捷方式 1 函數(shù)基礎(chǔ)知識(shí) 1 什么是Excel函數(shù)Excel函數(shù)即是預(yù)先定義 執(zhí)行計(jì)算 分析等處理數(shù)據(jù)任務(wù)的特殊公式 2 函數(shù)的結(jié)構(gòu) 函數(shù)以等號(hào) 開始 后面緊跟函數(shù)名稱和左括號(hào) 然后以逗號(hào)分隔輸入?yún)?shù) 最后是右括號(hào) 結(jié)構(gòu)如下圖所示 函數(shù)的語法為 函數(shù)名 參數(shù) 參數(shù) 其中括號(hào)是不可少的 1 函數(shù)基礎(chǔ)知識(shí) 引用運(yùn)算符 冒號(hào) 空格 逗號(hào) 感嘆號(hào)冒號(hào) 是區(qū)域運(yùn)算符逗號(hào) 是合并運(yùn)算符空格 是交叉運(yùn)算符 取引用區(qū)域的公共部分 感嘆號(hào) 是三維引用運(yùn)算符 可以引用另一張工作表中的數(shù)據(jù) 起其表示形式為 工作表名 單元格引用區(qū)域 1 函數(shù)基礎(chǔ)知識(shí) AVERAGE算術(shù)平均值COUNT數(shù)值型數(shù)據(jù)的個(gè)數(shù)MAX最大值MIN最小值LEFT從左邊截取字符RIGHT從右邊截取字符MID從字符串截取字符INT整數(shù)值SUM求和ROUND按指定位數(shù)四舍五入某數(shù)字DATE特定日期的序列數(shù)YEAR特定年份的序列數(shù)TIME特定時(shí)間的序列數(shù)NOW當(dāng)前時(shí)間的序列數(shù)IF X Z1 X2 當(dāng)X為TRUE時(shí) 函數(shù)取值Z1 否則取值Z2 統(tǒng)計(jì)函數(shù) 文字函數(shù) 數(shù)學(xué) 三角函數(shù) 日期與時(shí)間函數(shù) 邏輯函數(shù) 1 相對(duì)引用 將公式復(fù)制到新位置時(shí) Excel將相對(duì)于新位置修改公式中單元格的引用 I3 F3 G3 H3 如果該公式被復(fù)制到I6單元 則公式自動(dòng)變?yōu)镕6 G6 H6這就是地址的相對(duì)引用 即公式在復(fù)制時(shí)單元格地址隨復(fù)制而改變 2 絕對(duì)引用 有時(shí)我們?cè)趶?fù)制公式時(shí) 不希望公式中的地址隨復(fù)制而改變 如 I列每個(gè)單元格的公式均為F3 G3 H3 這時(shí)就要用到絕對(duì)地址引用 絕對(duì)引用就是公式在復(fù)制時(shí)不改變單元格地址 在單元格字母和數(shù)字前都加上一個(gè) 符號(hào)就將地址變成絕對(duì)地址 如 要實(shí)現(xiàn)上述操作應(yīng)把公式修改為 F 3 G 3 H 33 混合引用 在公式地址中即有相對(duì)引用又有絕對(duì)引用稱為混合引用 如 A 1或 A1 當(dāng)公式不是被復(fù)制到一行 列 而是一個(gè)區(qū)域時(shí)常使用混合引用 例如 1 函數(shù)基礎(chǔ)知識(shí) 地址的引用方法 1 函數(shù)基礎(chǔ)知識(shí) 嵌套函數(shù) 什么是嵌套函數(shù) 就是一個(gè)函數(shù)的參數(shù)本身也是一個(gè)函數(shù) 如下圖中所示的公式使用了嵌套的AVERAGE函數(shù) 并將結(jié)果與50相比較 這個(gè)公式的含義是 如果單元格F2到F5的平均值大于50 則求F2到F5的和 否則顯示數(shù)值0 嵌套函數(shù)注意事項(xiàng) 1 在Excel中 一個(gè)公式最多可以嵌套7層函數(shù) 2 嵌套函數(shù)是一個(gè)難度性較大的知識(shí)點(diǎn) 我們要清楚函數(shù)的意義及結(jié)構(gòu) 并且要熟悉其使用方法 括號(hào)必須成對(duì)出現(xiàn) 2 SUM函數(shù) 行或列求和H3單元格 SUM B3 G3 B11單元格 SUM B3 B10 2 SUM函數(shù) 區(qū)域求和H11單元格 SUM B3 G10 2 SUM函數(shù) 鼠標(biāo)拖拉法選中存放求和結(jié)果的單元格 點(diǎn)擊工具欄中的 自動(dòng)求和 按鈕用鼠標(biāo)拖過需要求和的所有單元格 然后回車即可注 如果需要求和的單元格是不連續(xù)的 則可按住Ctrl鍵分別拖過它們 3 AVERAGE AVERAGEA TRIMMEAN函數(shù) AVERAGE函數(shù)用途 求算數(shù)平均數(shù)使用方法 同SUM函數(shù)相同 只需把剛才所有 SUM 的部分替換成 AVERAGE AVERAGE B3 G3 AVERAGE B3 B10 AVERAGE B3 G10 3 AVERAGE AVERAGEA TRIMMEAN函數(shù) AVERAGEA函數(shù) 3 AVERAGE AVERAGEA TRIMMEAN函數(shù) TRIMMEAN函數(shù)用途 求數(shù)據(jù)集的內(nèi)部平均值 即可以按照要求去掉所有數(shù)據(jù)中最高的和最低的一些數(shù)據(jù) 然后求平均值 使用方法 TRIMMEAN array percent 例 需要求內(nèi)部平均值的所有數(shù)據(jù)區(qū)域 需要去除的數(shù)據(jù)占全部數(shù)據(jù)的比例 4 COUNT COUNTA函數(shù) 用途 計(jì)數(shù)COUNT 統(tǒng)計(jì)所選中的單元格區(qū)域中數(shù)字的個(gè)數(shù)COUNTA 統(tǒng)計(jì)所選中的單元格區(qū)域中非空單元格的個(gè)數(shù) 包括數(shù)字 文字等 例 5 ROUND TRUNC函數(shù) 功能 ROUND函數(shù) 對(duì)小數(shù)按照位數(shù)要求四舍五入 亦可通過設(shè)置 單元格格式 實(shí)現(xiàn)同樣功能 ROUND number num digits number就是將要進(jìn)行四舍五入的數(shù)字num digits則是需要保留小數(shù)點(diǎn)后幾位TRUNC函數(shù) 對(duì)小數(shù)進(jìn)行取整 同INT函數(shù)功能 TRUNC number number就是需要進(jìn)行取整的數(shù)字 5 ROUND TRUNC函數(shù) 使用方法 B2單元格 ROUND A2 2 代表保留的小數(shù)位數(shù)C2單元格 ROUND A2 0 注 如果位數(shù) 0 則舍入到指定的小數(shù)位 如果位數(shù) 0 則舍入到整數(shù) 如果位數(shù) 0 則在小數(shù)點(diǎn)左側(cè)進(jìn)入舍入 D2單元格 TRUNC A2 或 INT A2 對(duì)小數(shù)進(jìn)行取整 6 IF函數(shù) COUNTIF SUMIF 例 在B12單元格中輸入 IF B11 60 合格 不合格 邏輯判斷式 當(dāng)邏輯判斷式為真時(shí) 返回的值 當(dāng)邏輯判斷式為假時(shí) 返回的值 6 IF函數(shù) COUNTIF SUMIF COUNTIF函數(shù)功能 統(tǒng)計(jì)符合條件要求的單元格的數(shù)量例 在B5單元格中輸入 countif B2 B4 20 可以求出在B2到B4這三個(gè)單元格中大于20的數(shù)字有幾個(gè) 6 IF函數(shù) COUNTIF SUMIF SUMIF函數(shù)功能 統(tǒng)計(jì)符合條件要求的單元格中數(shù)字之和例 在B5單元格中輸入 sumif B2 B4 20 可以求出在B2到B4這三個(gè)單元格中大于20的數(shù)字之和 即 加總符合某特定搜尋準(zhǔn)則的儲(chǔ)存格 IF函數(shù)可以嵌套使用 最多為7層 如 IF C2 90 優(yōu) IF C2 75 良 IF C2 60 中 差 FREQUENCY函數(shù)功能 計(jì)算某一個(gè)範(fàn)圍內(nèi)的值出現(xiàn)的次數(shù) 並傳回一個(gè)垂直數(shù)值陣列 例如 用FREQUENCY來計(jì)算某些範(fàn)圍內(nèi)的考試成績(jī)各有幾個(gè)人 由於FREQUENCY回陣列 因此必須輸入為陣列公式 附註 範(fàn)例的公式必須輸入為一個(gè)陣列公式 在複製範(fàn)例到另一個(gè)空白工作表之後 選取範(fàn)圍A13 A16儲(chǔ)存格開始公式 按一下F2 然後按下CTRL SHIFT ENTER 如果公式不是一個(gè)陣列公式 就只有單一結(jié)果1 7 FREQUENCY陣列函數(shù) 9 MOD函數(shù)在美化表格中的應(yīng)用 功能 設(shè)置間隔式底紋例 如何實(shí)現(xiàn) 8 MOD函數(shù)在美化表格中的應(yīng)用 選中需要設(shè)置間隔式底紋的區(qū)域 格式 條件格式 彈出的 條件格式 對(duì)話框 如圖 在左側(cè)的下拉菜單中選擇 公式 并在右側(cè)區(qū)域輸入 MOD ROW 2 然后點(diǎn)擊 格式 按鈕 8 MOD函數(shù)在美化表格中的應(yīng)用 在彈出的 單元格格式 對(duì)話框中 選擇 圖案 選項(xiàng)卡 然后選擇需要的顏色 確定 點(diǎn)擊 條件格式 對(duì)話框中的 確定 按鈕 9 常用函數(shù)快捷方式 在excel表格的右下角有一個(gè)常用函數(shù)快捷方式的區(qū)域 如同所示 可以右鍵點(diǎn)擊該區(qū)域 使用六種常用函數(shù)這六種常用函數(shù) 分別是 平均值 AVERAGE 計(jì)數(shù) COUNTA 計(jì)數(shù)值 COUNT 最大值 MAX 最小值 MIN 求和 SUM 10 公式中常見的錯(cuò)誤信息 小結(jié) 1 要熟記每個(gè)常用函數(shù)的表達(dá)方式及意義 對(duì)于函數(shù)的多層嵌套 要分清嵌套的層次及順序 2 鞏固每個(gè)常用函數(shù)的意義及使用方法