Excell嘔心瀝血之經(jīng)典篇.ppt

《Excell嘔心瀝血之經(jīng)典篇.ppt》由會員分享，可在線閱讀，更多相關《Excell嘔心瀝血之經(jīng)典篇.ppt（21頁珍藏版）》請在裝配圖網(wǎng)上搜索。

Excel常用公式 目錄 1 函數(shù)IF2 函數(shù)VLOOKUP3 函數(shù)OR4 函數(shù)AND5 函數(shù)RAND6 函數(shù)ROUND7 函數(shù)LEN8 函數(shù)MID9 函數(shù)AVERAGE10 公式常用錯誤及解決辦法 1 函數(shù)IF 函數(shù)名稱 IF主要功能 根據(jù)對指定條件的邏輯判斷的真假結果 返回相對應的內(nèi)容 使用格式 IF Logical test Value if true Value if false 參數(shù)說明 Logical test代表邏輯判斷表達式 value if true是Logical test為TRUE時函數(shù)的返回值 value if true可以是一個表達式 value if false是Logical test為FALSE時函數(shù)的返回值 value if false也可以是一個表達式 應用舉例 1 在B2單元格中輸入公式 IF A2 26 TRUE FALSE 確認后 B2單元格中顯示 TRUE 2 在B3單元格中輸入公式 IF A3 26 錯誤 確認后 B3單元格中顯示 0 3 在B4單元格中輸入公式 IF A4 26 錯誤 確認后 B4單元格中顯示 FALSE 4 在B5單元格中輸入公式 IF A5 26 CW 確認后 B5單元格中顯示 0 5 在B6單元格中輸入公式 IF A6 20 IF LEN A6 4 IF LEN A6 3 三位數(shù) 兩位數(shù) 數(shù)值偏大 數(shù)值偏小 確認后 B6單元格中顯示 數(shù)值偏小 2 函數(shù)vlookup 函數(shù)名稱 VLOOKUP主要功能 在數(shù)據(jù)表的首列查找指定的數(shù)值 并由此返回數(shù)據(jù)表當前行中指定列處的數(shù)值 使用格式 VLOOKUP lookup value table array col index num range lookup 參數(shù)說明 lookup value代表需要查找的數(shù)值table array代表需要在其中查找數(shù)據(jù)的單元格區(qū)域col index num為在table array區(qū)域中待返回的匹配值的列序號 當col index num為2時 返回table array第2列中的數(shù) 值 為3時 返回第3列中的數(shù)值 range lookup為一邏輯值 如果為TRUE或省略 則返回近似匹配值 也就是說如果找不到精確匹配值 則返回小于lookup value的最大數(shù)值 如果為FALSE 則返回精確匹配值 如果找不到 則返回錯誤值 N A 2 函數(shù)vlookup 應用舉例 1 在C4單元格中輸入公式 VLOOKUP D3 A2 B6 2 FALSE 在D3單元格中輸入C確認后 C4單元格中顯示 13 2 在C4單元格中輸入公式 VLOOKUP D3 A2 B6 2 在D3單元格中輸入F確認后 C4單元格中顯示 15 3 在C4單元格中輸入公式 VLOOKUP D3 A2 B6 2 FALSE 在D3單元格中輸入F確認后 C4單元格中顯示 N A 2 函數(shù)vlookup 注意 文本不區(qū)分大小寫 3 函數(shù)OR 函數(shù)名稱 OR主要功能 返回邏輯值 僅當所有參數(shù)值均為邏輯 假 FALSE 時返回函數(shù)結果邏輯 假 FALSE 否則都返回邏輯 真 TURE 使用格式 OR logical1 logical2 參數(shù)說明 Logical1 Logical2 Logical3 表示待測試的條件式或表達式 最多達30個 應用舉例 如果A1 6 A2 8 則公式 OR A1 A2 A2 A1 A2 返回TRUE 而公式 OR A1 A2 A1 A2 返回FALSE 4 函數(shù)AND 函數(shù)名稱 AND主要功能 所有參數(shù)的邏輯值為真時返回TRUE 真 只要有一個參數(shù)的邏輯值為假 則返回FALSE 假 使用格式 AND logical1 logical2 參數(shù)說明 Logical1 Logical2 Logical3 表示待測試的條件式或表達式 最多達30個 應用舉例 在C5單元格輸入公式 AND A2 60 B2 60 確認 如果C2中返回TRUE 說明A2和B2中的數(shù)值均大于等于60 如果返回FALSE 說明A2和B2中的數(shù)值至少有一個小于60 5 函數(shù)RAND 函數(shù)名稱 RAND主要功能 返回一個大于等于0小于1的隨機數(shù) 每次計算工作表 按F9鍵 將返回一個新的數(shù)值 使用格式 RAND 參數(shù) 不需要注意 如果要生成a b之間的隨機實數(shù) 可以使用公式 RAND b a a 如果在某一單元格內(nèi)應用公式 RAND 然后在編輯狀態(tài)下按住F9鍵 將會產(chǎn)生一個變化的隨機數(shù) 應用舉例 公式 RAND 1000 返回一個大于等于0 小于1000的隨機數(shù) 6 函數(shù)ROUND 函數(shù)名稱 ROUND主要功能 按指定位數(shù)四舍五入某個數(shù)字 使用格式 ROUND number num digits 參數(shù) Number是需要四舍五入的數(shù)字 Num digits為指定的位數(shù) Number按此位數(shù)進行處理 注意 如果num digits大于0 則四舍五入到指定的小數(shù)位 如果num digits等于0 則四舍五入到最接近的整數(shù) 如果num digits小于0 則在小數(shù)點左側按指定位數(shù)四舍五入 應用舉例 如果A1 65 25 則公式 ROUND A1 1 返回65 3 ROUND 82 149 2 返回82 15 ROUND 21 5 1 返回20 7 函數(shù)LEN 函數(shù)名稱 LEN主要功能 統(tǒng)計文本字符串中字符數(shù)目 使用格式 LEN text 參數(shù)說明 text表示要統(tǒng)計的文本字符串 應用舉例 如果A2中為 asdsf 在B2單元格中輸入公式 LEN B2 確認后即顯示出統(tǒng)計結果 5 8 函數(shù)MID 函數(shù)名稱 MID主要功能 從一個文本字符串的指定位置開始 截取指定數(shù)目的字符 使用格式 MID text start num num chars 參數(shù)說明 text代表一個文本字符串 start num代表指定的起始位置 num chars表示要截取的數(shù)目 應用舉例 如下圖 在B1單元格中輸入公式 MID A1 4 2 確認后 即可顯示出 30 9 函數(shù)AVERAGE 函數(shù)名稱 AVERAGE主要功能 求出所有參數(shù)的算術平均值使用格式 AVERAGE number1 number2 參數(shù)說明 number1 number2 需要求平均值的數(shù)值或引用單元格 區(qū)域 參數(shù)不超過30個 應用舉例 在B8單元格中輸入公式 AVERAGE B7 D7 F7 H7 7 8 確認后 即可求出B7至D7區(qū)域 F7至H7區(qū)域中的數(shù)值和7 8的平均值 10 公式常見錯誤及解決辦法 經(jīng)常用Excel可能都會遇到一些錯誤值信息 如 N A VALUE DIV O 等等 出現(xiàn)這些錯誤的原因有很多種 如果公式不能計算正確結果 Excel將顯示一個錯誤值 例如 在需要數(shù)字的公式中使用文本 刪除了被公式引用的單元格 或者使用了寬度不足以顯示結果的單元格 以下是幾種常見的錯誤及其解決方法 1 原因 如果單元格所含的數(shù)字 日期或時間比單元格寬 或者單元格的日期時間公式產(chǎn)生了一個負值 就會產(chǎn)生 錯誤 解決方法 如果單元格所含的數(shù)字 日期或時間比單元格寬 可以通過拖動列表之間的寬度來修改列寬 如果使用的是1900年的日期系統(tǒng) 那么Excel中的日期和時間必須為正值 用較早的日期或者時間值減去較晚的日期或者時間值就會導致 錯誤 如果公式正確 也可以將單元格的格式改為非日期和時間型來顯示該值 10 公式常見錯誤及解決辦法 2 VALUE 當使用錯誤的參數(shù)或運算對象類型時 或者當公式自動更正功能不能更正公式時 將產(chǎn)生錯誤值 VALUE 原因一 在需要數(shù)字或邏輯值時輸入了文本 Excel不能將文本轉換為正確的數(shù)據(jù)類型 解決方法 確認公式或函數(shù)所需的運算符或參數(shù)正確 并且公式引用的單元格中包含有效的數(shù)值 例如 如果單元格A1包含一個數(shù)字 單元格A2包含文本 學籍 則公式 A1 A2 將返回錯誤值 VALUE 可以用SUM工作表函數(shù)將這兩個值相加 SUM函數(shù)忽略文本 SUM A1 A2 原因二 將單元格引用 公式或函數(shù)作為數(shù)組常量輸入 解決方法 確認數(shù)組常量不是單元格引用 公式或函數(shù) 原因三 賦予需要單一數(shù)值的運算符或函數(shù)一個數(shù)值區(qū)域 解決方法 將數(shù)值區(qū)域改為單一數(shù)值 修改數(shù)值區(qū)域 使其包含公式所在的數(shù)據(jù)行或列 10 公式常見錯誤及解決辦法 3 DIV O 當公式被零除時 將會產(chǎn)生錯誤值 DIV O 原因一 在公式中 除數(shù)使用了指向空單元格或包含零值單元格的單元格引用 在Excel中如果運算對象是空白單元格 Excel將此空值當作零值 解決方法 修改單元格引用 或者在用作除數(shù)的單元格中輸入不為零的值 原因二 輸入的公式中包含明顯的除數(shù)零 例如 5 0 解決方法 將零改為非零值 10 公式常見錯誤及解決辦法 4 NAME 在公式中使用了Excel不能識別的文本時將產(chǎn)生錯誤值 NAME 原因一 刪除了公式中使用的名稱 或者使用了不存在的名稱 解決方法 確認使用的名稱確實存在 選擇菜單 插入 名稱 定義 命令 如果所需名稱沒有被列出 請使用 定義 命令添加相應的名稱 原因二 名稱的拼寫錯誤 解決方法 修改拼寫錯誤的名稱 原因三 在公式中使用標志 解決方法 選擇菜單中 工具 選項 命令 打開 選項 對話框 然后單擊 重新計算 標簽 在 工作薄選項 下 選中 接受公式標志 復選框 原因四 在公式中輸入文本時沒有使用雙引號 解決方法 Excel將其解釋為名稱 而不理會用戶準備將其用作文本的想法 將公式中的文本括在雙引號中 例如 下面的公式將一段文本 總計 和單元格B50中的數(shù)值合并在一起 總計 B50原因五 在區(qū)域的引用中缺少冒號 解決方法 確認公式中 使用的所有區(qū)域引用都使用冒號 例如 SUM A2 B34 10 公式常見錯誤及解決辦法 5 N A原因 當在函數(shù)或公式中沒有可用數(shù)值時 將產(chǎn)生錯誤值 N A 解決方法 如果工作表中某些單元格暫時沒有數(shù)值 請在這些單元格中輸入 N A 公式在引用這些單元格時 將不進行數(shù)值計算 而是返回 N A 6 REF 當單元格引用無效時將產(chǎn)生錯誤值 REF 原因 刪除了由其他公式引用的單元格 或將移動單元格粘貼到由其他公式引用的單元格中 解決方法 更改公式或者在刪除或粘貼單元格之后 立即單擊 撤消 按鈕 以恢復工作表中的單元格 10 公式常見錯誤及解決辦法 7 NUM 當公式或函數(shù)中某個數(shù)字有問題時將產(chǎn)生錯誤值 NUM 原因一 在需要數(shù)字參數(shù)的函數(shù)中使用了不能接受的參數(shù) 解決方法 確認函數(shù)中使用的參數(shù)類型正確無誤 原因二 使用了迭代計算的工作表函數(shù) 例如 IRR或RATE 并且函數(shù)不能產(chǎn)生有效的結果 解決方法 為工作表函數(shù)使用不同的初始值 原因三 由公式產(chǎn)生的數(shù)字太大或太小 Excel不能表示 解決方法 修改公式 使其結果在有效數(shù)字范圍之間 8 NULL 當試圖為兩個并不相交的區(qū)域指定交叉點時將產(chǎn)生錯誤值 NULL 原因 使用了不正確的區(qū)域運算符或不正確的單元格引用 解決方法 如果要引用兩個不相交的區(qū)域 請使用聯(lián)合運算符逗號 公式要對兩個區(qū)域求和 請確認在引用這兩個區(qū)域時 使用逗號 如 SUM A1 A13 D12 D23 如果沒有使用逗號 Excel將試圖對同時屬于兩個區(qū)域的單元格求和 但是由于A1 A13和D12 D23并不相交 所以他們沒有共同的單元格 Thankyouverymuch