Excel公式與函數(shù)

項(xiàng) 目 6 Excel公 式 與 函 數(shù) 項(xiàng) 目 6 Excel公 式 與 函 數(shù)項(xiàng) 目 提 出以 “ 公 式 與 函 數(shù) ” 工 作 簿 為 項(xiàng) 目 。 其 中 “ 公 式 ” 工 作 表 的內(nèi) 容 如 圖 6-1所 示 。 圖 6-1 “ 公 式 ” 表 的 內(nèi) 容 項(xiàng) 目 6 Excel公 式 與 函 數(shù)項(xiàng) 目 提 出(1) 怎 樣 完 成 公 式 “ 總 評(píng) 成 績(jī) ” 、 “ 及 格 否 ” 、 “ 偏 差 ” 的輸 入 ？(2) 在 公 式 與 函 數(shù) 中 ， 怎 樣 判 斷 單 元 地 址 的 引 用 類 型 ？(3) 對(duì) 公 式 給 出 的 錯(cuò) 誤 信 息 ， 如 何 分 析 ， 如 何 改 正 公 式 ？(4) 各 種 類 型 的 函 數(shù) 又 如 何 使 用 ？ 各 參 數(shù) 的 范 圍 又 如 何 確 定 ？項(xiàng) 目 分 析所 有 公 式 都 是 以 “ =” 開 始 的 表 達(dá) 式 ， 通 過(guò) 公 式 的 運(yùn) 算 后 ，公 式 所 在 的 單 元 格 顯 示 是 公 式 的 值 ， 公 式 的 本 身 在 編 輯 欄中 顯 示 。函 數(shù) 存 在 于 公 式 中 ， 是 公 式 的 重 要 組 成 部 分 ， 每 個(gè) 函 數(shù) 由函 數(shù) 名 及 函 數(shù) 參 數(shù) 組 成 ， 經(jīng) 函 數(shù) 運(yùn) 算 后 ， 得 到 一 個(gè) 或 幾 個(gè) 函 數(shù) 的 值 。 項(xiàng) 目 6 Excel公 式 與 函 數(shù)通 過(guò) 本 項(xiàng) 目 的 學(xué) 習(xí) ， 應(yīng) 掌 握 的 內(nèi) 容 主 要 有 ：(1) 公 式 的 表 達(dá) 形 式 是 以 等 于 號(hào) 開 始 ， 后 接 表 達(dá) 式 的 等 式 ，表 達(dá) 式 中 包 括 常 量 ， 單 元 格 地 址 (相 當(dāng) 于 變 量 )、 各 種 運(yùn) 算 符(算 術(shù) 、 文 本 、 關(guān) 系 、 引 用 )、 函 數(shù) 等 對(duì) 象 。(2) 在 公 式 中 ， 引 用 單 元 格 地 址 有 3種 方 式 ， 相 對(duì) 引 用 、 絕對(duì) 引 用 和 混 合 引 用 。(3) 函 數(shù) 是 數(shù) 據(jù) 處 理 必 不 可 少 的 計(jì) 算 工 具 ， 依 靠 函 數(shù) ， 可 以獲 取 新 的 數(shù) 據(jù) ， 函 數(shù) 主 要 有 數(shù) 字 函 數(shù) 、 文 本 函 數(shù) 、 統(tǒng) 計(jì) 函數(shù) 和 邏 輯 函 數(shù) 等 。(4) 單 元 格 公 式 輸 入 有 兩 種 情 形 ， 一 是 直 接 輸 入 ， 對(duì) 熟 練 者適 用 ； 二 是 公 式 向 導(dǎo) 輸 入 ， 對(duì) 初 學(xué) 者 適 用 。 項(xiàng) 目 6 Excel公 式 與 函 數(shù)本 項(xiàng) 目 的 重 點(diǎn) 內(nèi) 容 為 ：(1) 公 式 的 建 立 。(2) 函 數(shù) 的 使 用 。(3) 地 址 的 引 用 。Excel公 式 與 函 數(shù) 的 操 作 內(nèi) 容 導(dǎo) 航 見 表 6-1。表 6-1 公 式 函 數(shù) 操 作 導(dǎo) 航 內(nèi)容操作提示公 式 單 元 格 中 以 “ =” 開 始 的 表 達(dá) 式運(yùn) 算 符 運(yùn) 算 規(guī) 則 ， 直 接 輸 入相 對(duì) 引 用 直 接 輸 入絕 對(duì) 引 用 在 行 和 列 前 加 “ $” 號(hào) ， 或 者 按 F4鍵混 合 引 用 在 行 或 列 前 加 “ $” 號(hào) ， 或 者 按 F4鍵函 數(shù) 直 接 輸 入 或 導(dǎo) 航 輸 入 ， 注 意 函 數(shù) 參 數(shù) 及 函 數(shù) 值 6.1 單 元 格 公 式6.1.1 輸 入 公 式任 務(wù) 1在 “ 公 式 ” 工 作 表 中 ， 輸 入 D4單 元 格 計(jì) 算 公 式 (總 評(píng) =平 時(shí)成 績(jī) 30%+期 末 成 績(jī) 70%)。操 作 步 驟選 中 D4單 元 格 ， 輸 入 公 式 “ =B4*30%+C4*70%” ， 按Enter鍵 ， 或 者 單 擊 編 輯 欄 左 側(cè) 的 “ 確 定 ” 按 鈕 。 D4單 元 格 中 顯 示 計(jì) 算 結(jié) 果 ， 編 輯 欄 中 顯 示 當(dāng) 前 單 元 格 的 公式 ， 如 圖 6-2所 示 。 6.1 單 元 格 公 式6.1.1 輸 入 公 式 圖 6-2 輸 入 公 式 6.1 單 元 格 公 式6.1.1 輸 入 公 式操 作 技 巧(1) 在 公 式 輸 入 過(guò) 程 中 ， 公 式 中 出 現(xiàn) 單 元 格 引 用 時(shí) ， 可 用鼠 標(biāo) 直 接 單 擊 單 元 格 獲 取 單 元 格 地 址 ， 比 直 接 輸 入 更 方 便 。(2) 雙 擊 公 式 所 在 的 單 元 格 ， 單 元 格 處 于 編 輯 狀 態(tài) ， 可 以看 到 被 該 公 式 引 用 的 所 有 單 元 格 或 單 元 格 區(qū) 域 將 以 不 同 的顏 色 顯 示 在 公 式 單 元 格 中 ， 并 在 相 應(yīng) 的 單 元 格 或 單 元 格 區(qū)域 顯 示 相 同 顏 色 的 邊 框 ， 便 于 用 戶 檢 查 并 修 改 單 元 格 或 單元 格 區(qū) 域 的 引 用 。 6.1 單 元 格 公 式6.1.2 運(yùn) 算 符1 算 術(shù) 運(yùn) 算 符 表 6-2 算 術(shù) 運(yùn) 算 符 的 說(shuō) 明運(yùn)算符名稱示例- 負(fù) 號(hào) -88% 百 分 號(hào) A1%、 A1%+A2 指 數(shù) A12+、 - 加 法 、 減 法 A1+A2、 A1-A2*、 / 乘 法 、 除 法 A1*A2、 A1/A2 6.1 單 元 格 公 式6.1.2 運(yùn) 算 符2 文 本 連 接 運(yùn) 算 符任 務(wù) 2在 C10、 C11單 元 格 中 ， 連 接 同 行 左 邊 兩 個(gè) 單 元 格 內(nèi) 容 。 操 作 步 驟選 擇 C10單 元 格 ， 輸入 公 式 “ =A10 & B10”； 選 擇 C11單 元格 ， 輸 入 公 式 “ =A11 & B11”， 計(jì) 算 結(jié) 果 如 圖 6-3所 示 。 圖 6-3 計(jì) 算 結(jié) 果 6.1 單 元 格 公 式6.1.2 運(yùn) 算 符3 關(guān) 系 運(yùn) 算 符 表 6-3 關(guān) 系 運(yùn) 算 符 的 說(shuō) 明運(yùn)算符名稱示例= 等 于 A1=A2、 = 大 于 、 大 于 等 于 A1A2、 A3=A4 、 = 小 于 、 小 于 等 于 A1A2、 A1=A2 不 等 于 A1A2比 較 運(yùn) 算 符 的 運(yùn) 算 優(yōu) 先 級(jí) 均 相 同 ， 使 用 比 較 運(yùn) 算 符 的 參 數(shù) 一般 為 數(shù) 值 型 ， “ =” 或 “ ” 也 可 用 于 字 符 型 與 邏 輯 型 。 6.1 單 元 格 公 式6.1.2 運(yùn) 算 符3 關(guān) 系 運(yùn) 算 符任 務(wù) 2輸 入 E4單 元 格 計(jì) 算 公 式 ， 當(dāng) 總 評(píng) 大 于 等 于 60分 時(shí) ， 為 及格 ， 用 TRUE表 示 ， 否 則 用 FALSE表 示 。操 作 步 驟選 擇 E4單 元 格 ， 輸 入 “ =D4=60”， 顯 示 結(jié) 果 為 TRUE。 6.1 單 元 格 公 式6.1.2 運(yùn) 算 符4 引 用 運(yùn) 算 符一 個(gè) 引 用 單 元 格 地 址 表 示 公 式 中 要 使 用 的 單 元 格 的 數(shù) 值 ，相 當(dāng) 于 一 個(gè) 變 量 ， 當(dāng) 被 引 用 單 元 格 數(shù) 值 改 變 時(shí) ， 公 式 計(jì) 算結(jié) 果 也 同 步 改 變 。 表 6-4 引 用 運(yùn) 算 符 的 說(shuō) 明 運(yùn)算符名稱示例: 區(qū) 域 運(yùn) 算 符 A1:A5 所 有 區(qū) 域, 聯(lián) 合 運(yùn) 算 符 A1:A2,A4:A6 合 并 區(qū) 域空 格 交 叉 運(yùn) 算 符 A1:A 3:A6 共 有 區(qū) 域 6.1 單 元 格 公 式6.1.2 運(yùn) 算 符4 引 用 運(yùn) 算 符操 作 技 巧聯(lián) 合 運(yùn) 算 符 中 ， 運(yùn) 算 符 左 右 區(qū) 域 中 如 果 有 重 疊 區(qū) 域 ， 則 參與 計(jì) 算 兩 次 ， 具 體 運(yùn) 算 次 序 為 ： 如 果 公 式 中 同 時(shí) 用 到 多 個(gè)運(yùn) 算 符 ， Excel將 按 如 下 所 示 的 順 序 進(jìn) 行 運(yùn) 算 ： “ -” (負(fù)號(hào) )、 “ %” 、 “ ” 、 “ *” 和 “ /” 、 “ +” 和 “ -” (減 號(hào) )、“ &” 、 比 較 運(yùn) 算 符 (=、 、 =、 )， 如 果 公 式包 含 相 同 優(yōu) 先 級(jí) 的 運(yùn) 算 符 ， 則 從 左 到 右 進(jìn) 行 運(yùn) 算 。如 果 要 改 變 運(yùn) 算 過(guò) 程 的 順 序 ， 可 將 公 式 中 要 先 計(jì) 算 的 部 分用 括 號(hào) 括 起 來(lái) 。 例 如 ， 公 式 “ =(B4+C4)/(D4-F4)” ， 首 先 計(jì) 算 B4+C4， 然 后 計(jì) 算 D4-F4， 再 相 除 。 6.2 單 元 格 引 用6.2.1 相 對(duì) 引 用任 務(wù) 4復(fù) 制 D4單 元 格 公 式 到 D5:D7， 查 看 相 對(duì) 引 用 地 址 的 變 化 。操 作 步 驟向 下 拖 動(dòng) 填 充 柄 ， 完 成 公 式 的 復(fù) 制 。操 作 技 巧當(dāng) 公 式 復(fù) 制 到 D5時(shí) ， 公 式 變 為 “ =B5*30%+C5*70%” ，這 是 因 為 從 D4到 D5， 列 沒 有 變 化 ， 而 行 向 下 偏 移 了 一 行 ，所 以 公 式 中 單 元 格 的 地 址 列 標(biāo) 沒 變 ， 行 號(hào) 自 動(dòng) 增 1， 以 維持 公 式 所 在 單 元 格 與 被 引 用 的 單 元 格 之 間 的 相 互 位 置 不 變 。 6.2 單 元 格 引 用6.2.2 絕 對(duì) 引 用任 務(wù) 5在 F4單 元 格 中 輸 入 計(jì) 算 公 式 “ =D4-$D$8” ， 并 將 公 式 復(fù) 制 到F5:F7單 元 格 ， 查 看 絕 對(duì) 地 址 。 偏 差 等 于 總 分 與 期 望 值 之差 。操 作 步 驟選 擇 F4單 元 格 ， 輸 入 公 式 “ =D4-$D$8”， 并 向 下 填 充 到 F7單 元 格 。 6.2 單 元 格 引 用6.2.2 絕 對(duì) 引 用操 作 技 巧(1) 編 輯 地 址 引 用 類 型 時(shí) ， 可 用 鼠 標(biāo) 直 接 獲 取 單 元 格 地 址 ， 再 連續(xù) 按 F4鍵 ， 循 環(huán) 改 變 引 用 的 類 型 ， 而 不 必 手 工 輸 入 “ $” 號(hào) 。(2) 由 于 公 式 需 要 復(fù) 制 到 F5:F7時(shí) ， 且 公 式 為 相 應(yīng) 總 評(píng) 與 固 定 單元 格 D8之 差 ， 故 D8單 元 格 應(yīng) 為 絕 對(duì) 地 址 。 即 不 管 公 式 復(fù) 制 到 任何 單 元 格 ， 引 用 的 永 遠(yuǎn) 是 D8單 元 格 中 的 數(shù) 據(jù) 。 6.2 單 元 格 引 用6.2.3 混 合 引 用1 引 用 類 型 的 判 斷(1) 公 式 填 充 到 同 一 行 或 者 同 一 列 。 以 單 元 格 或 者 單 元 格區(qū) 域 為 對(duì) 象 ， 判 斷 單 元 格 的 引 用 類 型 ， 引 用 類 型 為 絕 對(duì) 引用 或 者 相 對(duì) 引 用 。任 務(wù) 6總 分 與 偏 差 公 式 引 用 類 型 的 判 斷 。操 作 步 驟1） 前 期 準(zhǔn) 備 。 把 單 元 格 公 式 以 “ 公 式 ” 形 式 顯 示 ， 選 擇“ 工 具 ” |“ 選 項(xiàng) ” 命 令 ， 在 “ 選 項(xiàng) ” 對(duì) 話 框 的 “ 視 圖 ” 選 項(xiàng) 卡 中 的 “ 窗 口 選 項(xiàng) ” 區(qū) 域 中 ， 選 中 “ 公 式 ” 復(fù) 選 框 ， 這時(shí) 表 中 的 公 式 將 以 公 式 形 式 顯 示 。 6.2 單 元 格 引 用6.2.3 混 合 引 用1 引 用 類 型 的 判 斷操 作 步 驟2） 輸 入 主 公 式 與 輔 助 公式 。 按 相 對(duì) 引 用 方 法 ，輸 入 D4單 元 格 公 式 ， 作為 主 公 式 ， 在 相 鄰 的 單元 格 D5中 ， 以 相 同 的 順序 與 方 法 輸 入 D5單 元 格公 式 ， 作 為 輔 助 公 式 。同 理 輸 入 F4、 F5單 元 格 主 公 式 與 輔 助 公 式 ， 如圖 6-4所 示 。 圖 6-4 同 行 或 同 列 單 元 格 引 用類 型 的 判 斷 6.2 單 元 格 引 用6.2.3 混 合 引 用1 引 用 類 型 的 判 斷操 作 步 驟3） 判 斷 。 比 較 主 公 式 與 輔 助 公 式 ， 在 相 同 的 對(duì) 應(yīng) 位 置 ，如 果 不 是 同 一 個(gè) 單 元 格 或 者 單 元 區(qū) 域 ， 則 為 相 對(duì) 引 用 ， 否則 為 絕 對(duì) 引 用 。D4、 D5單 元 格 公 式 中 ， 沒 有 相 同 的 地 址 ， 單 元 格 的 引 用為 相 對(duì) 引 用 。在 F4、 F5兩 對(duì) 公 式 中 ， D8相 同 ， 說(shuō) 明 在 D4單 元 格 主 公 式中 ， D8為 絕 對(duì) 引 用 ， 重 新 編 輯 主 公 式 ， 在 絕 對(duì) 引 用 的 列 、行 前 加 “ $” ， 修 改 后 的 主 公 式 為 “ D4-$D$8”。 4） 后 期 工 作 。 刪 除 輔 助 公 式 ， 取 消 選 中 “ 視 圖 ” 選 項(xiàng) 卡中 的 “ 公 式 ” 復(fù) 選 框 。 6.2 單 元 格 引 用6.2.3 混 合 引 用1 引 用 類 型 的 判 斷任 務(wù) 7選 擇 “ 九 九 乘 法 表 ” 工 作 表 ， 進(jìn) 行 公 式 的 引 用 類 型 的 判 斷 ，并 制 作 “ 九 九 乘 法 表 ” 。(2) 公 式 填 充 到 不 同 行 或 列 。 以 單 元 格 的 行 或 者 列 為 對(duì) 象 ，判 斷 單 元 格 的 引 用 ， 單 元 格 引 用 為 絕 對(duì) 引 用 或 者 混 合 引 用 。操 作 步 驟1） 前 期 準(zhǔn) 備 。 把 單 元 格 公 式 以 “ 公 式 ” 形 式 顯 示 。 6.2 單 元 格 引 用6.2.3 混 合 引 用1 引 用 類 型 的 判 斷操 作 步 驟2） 輸 入 主 公 式 與 輔 助 公式 。 按 相 對(duì) 引 用 方 法 ，輸 入 I3單 元 格 公 式 ， 作為 主 公 式 ， 在 右 下 角 單元 格 J4中 ， 以 相 同 的 順序 與 方 法 輸 入 單 元 格 公式 ， 作 為 輔 助 公 式 ， 要注 意 主 公 式 與 輔 助 公 式 所 在 的 單 元 格 位 于 不 同行 與 列 。 如 圖 6-5所 示 。 圖 6-5 不 同 行 列 引 用 類 型 的 判 斷 6.2 單 元 格 引 用6.2.3 混 合 引 用1 引 用 類 型 的 判 斷操 作 步 驟3） 判 斷 。 從 左 至 右 ， 比 較 I3、 J4單 元 格 主 公 式 與 輔 助 公式 中 的 行 或 者 列 ， 在 相 同 的 對(duì) 應(yīng) 位 置 ， 不 同 的 行 或 列 為 相對(duì) 引 用 ， 相 同 的 行 或 列 為 絕 對(duì) 引 用 ， 在 絕 對(duì) 引 用 前 加 “ $”符 號(hào) 。主 公 式 修 改 為 “ =$H*I$2”。4） 后 期 工 作 。 刪 除 輔 助 公 式 ， 取 消 選 中 “ 視 圖 ” 選 項(xiàng) 卡中 的 “ 公 式 ” 復(fù) 選 框 。 將 主 公 式 復(fù) 制 到 I3:Q11區(qū) 域 ， 完 成“ 九 九 乘 法 表 ” 的 制 作 。 6.2 單 元 格 引 用6.2.3 混 合 引 用2 公 式 的 移 動(dòng)當(dāng) 公 式 移 動(dòng) 后 ， 所 引 用 單 元 地 址 還 是 原 來(lái) 的 地 址 ， 不 會(huì) 改 變 。3 單 元 格 或 單 元 格 區(qū) 域 引 用 的 移 動(dòng)公 式 所 引 用 的 單 元 格 或 單 元 格 區(qū) 域 (不 論 是 相 對(duì) 引 用 還 是 絕 對(duì)引 用 、 混 合 引 用 )移 動(dòng) 后 ， 則 公 式 會(huì) 自 動(dòng) 作 相 應(yīng) 的 改 變 ， 引 用變 為 單 元 格 或 單 元 格 區(qū) 域 移 動(dòng) 后 的 新 地 址 ， 維 持 公 式 的 返 回值 不 變 。 6.3 錯(cuò) 誤 信 息表 6-5 Excel 2003錯(cuò) 誤 提 示 信 息錯(cuò)誤信息錯(cuò)誤原因# 列 寬 不 夠 ， 或 者 使 用 了 負(fù) 數(shù) 的 日 期 或 時(shí) 間#DIV/0! 公 式 或 函 數(shù) 被 零 或 空 單 元 格 除#NAME? 公 式 中 的 文 本 不 可 識(shí) 別#N/A 數(shù) 值 對(duì) 函 數(shù) 或 公 式 不 可 用 #NULL! 出 現(xiàn) 空 單 元 格 或 引 用 了 不 正 確 的 單 元 格#VALUE 操 作 數(shù) 類 型 與 運(yùn) 算 符 要 求 不 匹 配#NUM! 公 式 或 函 數(shù) 中 使 用 無(wú) 效 數(shù) 字 值#REF! 單 元 格 引 用 無(wú) 效 6.3 錯(cuò) 誤 信 息單 擊 出 現(xiàn) 錯(cuò) 誤 提 示信 息 的 單 元 格 (但#除 外 )， 將 出現(xiàn) 一 個(gè) 黃 色 菱 形 里的 感 嘆 號(hào) ， 單 擊 該感 嘆 號(hào) ， 會(huì) 出 現(xiàn) 用于 檢 查 、 修 正 錯(cuò) 誤的 操 作 命 令 ， 如 圖6-6所 示 。 圖 6-6 被 零 除 錯(cuò) 誤 信 息 6.3 錯(cuò) 誤 信 息例 如 ， 字 符 型 數(shù)值 單 元 格 ， 雖 然提 示 可 能 有 錯(cuò) 誤 ，但 可 以 根 據(jù) 實(shí) 際情 況 忽 略 錯(cuò) 誤 ，如 圖 6-7所 示 。 圖 6-7 字 符 型 數(shù) 值 提 示 信 息 6.4 函 數(shù)6.4.1 函 數(shù) 的 輸 入 方 法函 數(shù) 的 一 般 格 式 為 ： 函 數(shù) 名 (， ， ， )。1 直 接 輸 入直 接 輸 入 ， 就 像 輸 入 其 他 普 通 的 數(shù) 據(jù) 一 樣 ， 但 如 果 函 數(shù) 單獨(dú) 使 用 ， 要 先 輸 入 等 號(hào) “ =” 。 直 接 輸 入 函 數(shù) 需 要 對(duì) 函 數(shù)比 較 了 解 ， 在 輸 入 過(guò) 程 中 會(huì) 有 相 應(yīng) 的 提 示 。2 利 用 函 數(shù) 向 導(dǎo) 輸 入利 用 編 輯 欄 旁 邊 的 “ 插 入 函 數(shù) ” 按 鈕 ， 再 根 據(jù) 對(duì) 話框 的 提 示 輸 入 。 這 種 方 法 比 較 直 觀 ， 建 議 使 用 本 方 法 。 特別 適 應(yīng) 參 數(shù) 較 多 的 或 嵌 套 使 用 的 函 數(shù) 。 6.4 函 數(shù)6.4.2 數(shù) 學(xué) 函 數(shù)1 絕 對(duì) 值 函 數(shù)格 式 ： ABS(number)功 能 ： 返 回 number參 數(shù) 的 絕 對(duì) 值 。2 取 整 函 數(shù)格 式 ： INT(number)功 能 ： 將 number參 數(shù) 向 左 取 整 為 最 接 近 或 相 等 的 整 數(shù) 。在 A2單 元 格 中 輸 入 公 式 ： =INT(1234.5)， 其 值 為 1234。在 A3單 元 格 中 輸 入 公 式 ： =INT(-456.78)， 其 值 為 -157。 6.4 函 數(shù)6.4.2 數(shù) 學(xué) 函 數(shù)3 截 尾 函 數(shù)格 式 ： TRUNC(number,num_digits)功 能 ： TRUNC函 數(shù) 是 將 number參 數(shù) 按 num_digits參 數(shù)的 位 數(shù) 直 接 截 取 ， 不 四 舍 五 入 。如 果 num_digits等 于 0， 則 截 取 小 數(shù) 取 整 數(shù) 部 分 ， 這 時(shí)num_digits參 數(shù) 可 省 略 。如 果 num_digits大 于 0， 則 截 取 到 指 定 的 小 數(shù) 位 。如 果 num_digits小 于 0， 則 在 小 數(shù) 點(diǎn) 左 側(cè) 進(jìn) 行 截 取 。 6.4 函 數(shù)6.4.2 數(shù) 學(xué) 函 數(shù)4 四 舍 五 入 函 數(shù)格 式 ： ROUND(number,num_digits)功 能 ： ROUND函 數(shù) 是 對(duì) number參 數(shù) 按 num_digits參 數(shù)的 位 數(shù) 進(jìn) 行 四 舍 五 入 ， 注 意 此 參 數(shù) 不 能 省 略 ， 沒 有 默 認(rèn) 值 。如 果 num_digits等 于 0， 則 四 舍 五 入 到 最 接 近 的 整 數(shù) 。如 果 num_digits小 于 0， 則 在 小 數(shù) 點(diǎn) 左 側(cè) 進(jìn) 行 四 舍 五 入 。5 求 余 函 數(shù)格 式 ： MOD(number, divisor)功 能 ： 返 回 number參 數(shù) 除 以 divisor參 數(shù) 的 余 數(shù) ， 結(jié) 果 的正 負(fù) 號(hào) 與 除 數(shù) 相 同 。 6.4 函 數(shù)6.4.2 數(shù) 學(xué) 函 數(shù)6 平 方 根 函 數(shù)格 式 ： SQRT(number)功 能 ： 計(jì) 算 number的 平 方 根 。7 圓 周 率 函 數(shù)格 式 ： PI( )功 能 ： 返 回 圓 周 率 的 數(shù) 值 ， 是 個(gè) 無(wú) 參 函 數(shù) 。8 隨 機(jī) 函 數(shù)格 式 ： RAND( )功 能 ： 隨 機(jī) 產(chǎn) 生 一 個(gè) 大 于 等 于 0及 小 于 1的 數(shù) ， 注 意 產(chǎn) 生 的隨 機(jī) 數(shù) 可 以 等 于 0， 但 不 會(huì) 等 于 1。 每 次 打 開 工 作 簿 時(shí) 都 會(huì) 更 新 結(jié) 果 。 6.4 函 數(shù)6.4.2 數(shù) 學(xué) 函 數(shù)任 務(wù) 8打 開 “ 公 式 函 數(shù) ” 工 作 簿 ， 選 擇 “ 數(shù) 學(xué) 函 數(shù) ” 表 ， 計(jì) 算 函 數(shù) 的 值 。隨 機(jī) 生 成 一 個(gè) a與 b之 間 的 整 數(shù) 的 公 式 為 =INT(RAND( )*(b-a+1)+a。操 作 步 驟各 數(shù) 學(xué) 函 數(shù) 的 計(jì) 算 如 圖 6-8所 示 。 圖 6-8 數(shù) 學(xué) 函 數(shù) 的 計(jì) 算 6.4 函 數(shù)6.4.2 數(shù) 學(xué) 函 數(shù)操 作 技 巧隨 機(jī) 生 成 一 個(gè) a與 b之 間 的 整 數(shù) 的 公 式 為 =INT(RAND( )*(b-a+1)+a。6.4.3 統(tǒng) 計(jì) 函 數(shù)格 式 ： SUM(number1,number2,)功 能 ： 返 回 所 有 參 數(shù) number1、 number2、 的 和 。1 求 和 函 數(shù) 6.4 函 數(shù)6.4.3 統(tǒng) 計(jì) 函 數(shù)操 作 技 巧當(dāng) 參 數(shù) 為 邏 輯 常 量 值 ， 則 FALSE轉(zhuǎn) 化 為 0， TRUE轉(zhuǎn) 化 為 1； 數(shù)值 型 字 符 轉(zhuǎn) 換 為 數(shù) 字 。 但 是 如 果 參 數(shù) 為 單 元 格 引 用 ， 則 僅 計(jì) 算數(shù) 值 型 的 數(shù) 據(jù) ， 不 計(jì) 算 空 白 單 元 格 、 邏 輯 值 、 字 符 型 的 數(shù) 值 。格 式 ： SUMIF(range,criteria,sum_range)功 能 ： 根 據(jù) 指 定 的 條 件 ， 對(duì) 指 定 的 單 元 格 區(qū) 域 求 和 。2 條 件 求 和 函 數(shù) 6.4 函 數(shù)6.4.3 統(tǒng) 計(jì) 函 數(shù)格 式 ： AVERAGE(number1,number2,)功 能 ： 求 算 術(shù) 平 均 值 。3 平 均 值 函 數(shù)格 式 ： COUNT(value1,value2,)與COUNTA(value1,value2,)功 能 ： 都 是 用 于 統(tǒng) 計(jì) 個(gè) 數(shù) 。 COUNT函 數(shù) 是 統(tǒng) 計(jì) 引 用 參 數(shù)內(nèi) 數(shù) 值 型 的 單 元 格 個(gè) 數(shù) ； 而 COUNTA函 數(shù) 統(tǒng) 計(jì) 非 空 單 元 格的 個(gè) 數(shù) ， 不 考 慮 單 元 格 是 什 么 數(shù) 據(jù) 類 型 。4 計(jì) 數(shù) 函 數(shù) 6.4 函 數(shù)6.4.3 統(tǒng) 計(jì) 函 數(shù)操 作 技 巧如 果 單 元 格 內(nèi) 有 空 格 ， 則 不 是 非 空 單 元 格 。格 式 ： COUNTIF(range,criteria)功 能 ： 計(jì) 算 在 range范 圍 內(nèi) 符 合 criteria條 件 的 單 元 格 數(shù) 目 ，criteria參 數(shù) 必 須 能 在 range范 圍 內(nèi) 匹 配 ， 否 則 返 回 值 為 0。5 條 件 計(jì) 數(shù) 函 數(shù)操 作 技 巧條 件 計(jì) 數(shù) 只 有 COUNTIF， 而 沒 有 COUNTAIF， 統(tǒng) 計(jì) 滿 足 條 件 的 非 空 單 元 格 的 個(gè) 數(shù) 。 6.4 函 數(shù)6.4.3 統(tǒng) 計(jì) 函 數(shù)格 式 ： MAX(number1,number2,)與MIN(number1,number2,)功 能 ： MAX函 數(shù) 是 返 回 參 數(shù) 中 的 最 大 值 ， 而 MIN函 數(shù) 則 是返 回 參 數(shù) 中 的 最 小 值 。6 最 大 值 與 最 小 值 函 數(shù) 6.4 函 數(shù)6.4.3 統(tǒng) 計(jì) 函 數(shù)任 務(wù) 9打 開 “ 公 式 函 數(shù) ” 工 作 簿 ， 選 擇 “ 統(tǒng) 計(jì) 函 數(shù) ” 表 ， 計(jì) 算 函 數(shù) 的值 。操 作 步 驟各 統(tǒng) 計(jì) 函 數(shù) 的 計(jì) 算 如 圖 6-9所 示 圖 6-9 統(tǒng) 計(jì) 函 數(shù) 的 計(jì) 算 6.4 函 數(shù)6.4.3 統(tǒng) 計(jì) 函 數(shù)操 作 步 驟計(jì) 算 基 本 工 資 平 均 值 函 數(shù)輸 入 如 下 ：(1) 選 擇 單 元 格 C12， 單擊 工 具 欄 上 的 “ 插 入 函 數(shù) ”按 鈕 ， 彈 出 “ 插 入 函 數(shù) ”對(duì) 話 框 。 如 圖 6-10所 示 ，在 “ 或 選 擇 類 別 ” 下 拉 列表 框 中 選 擇 “ 統(tǒng) 計(jì) ” ， 在“ 選 擇 函 數(shù) ” 列 表 框 中 選擇 AVERAGE， 單 擊 “ 確 定 ” 按 鈕 。 圖 6-10 “ 插 入 函 數(shù) ” 對(duì) 話 框 6.4 函 數(shù)6.4.3 統(tǒng) 計(jì) 函 數(shù)操 作 步 驟(2) 彈 出 “ 函 數(shù) 參數(shù) ” 對(duì) 話 框 ， 單擊 “ Number1”參 數(shù) 框 右 側(cè) 的“ 折 疊 ” 按 鈕 ，選 擇 計(jì) 算 平 均 值的 單 元 格 區(qū) 域C2:C8， 再 單 擊“ 折 疊 ” 按 鈕 ，單 擊 “ 確 定 ” 按 鈕 ， 如 圖 6-11所示 。 圖 6-11 “ 函 數(shù) 參 數(shù) ” 對(duì) 話 框 6.4 函 數(shù)6.4.3 統(tǒng) 計(jì) 函 數(shù)操 作 技 巧(1) 函 數(shù) 分 為 “ 財(cái) 務(wù) ” 、 “ 日 期 與 時(shí) 間 ” 、 “ 數(shù) 學(xué) 與 三 角函 數(shù) ” 、 “ 統(tǒng) 計(jì) ” 、 “ 查 找 與 引 用 ” 、 “ 數(shù) 據(jù) 庫(kù) ” 、 “ 文本 ” 、 “ 編 輯 ” 和 “ 信 息 ” 九 大 類 。 而 “ 常 用 函 數(shù) ” 指 的是 本 計(jì) 算 機(jī) 最 近 使 用 過(guò) 的 函 數(shù) ， 并 非 是 工 作 中 的 常 用 函 數(shù) 。(2) 在 輸 入 公 式 時(shí) ， 注 意 將 輸 入 法 設(shè) 置 為 英 文 輸 入 法 ， 不要 設(shè) 為 中 文 輸 入 法 。(3) 要 清 楚 函 數(shù) 的 名 稱 及 其 作 用 ， 不 要 將 英 文 拼 錯(cuò) ， 一 些英 文 拼 寫 相 近 的 函 數(shù) 不 要 混 淆 ， 如 COUNT與 COUNTA。(4) 相 似 功 能 的 函 數(shù) ， 要 理 解 其 使 用 的 場(chǎng) 合 。 例 如 ， SUM是 求 和 ； SUMIF是 條 件 求 和 ， 其 條 件 僅 涉 及 數(shù) 據(jù) 庫(kù) 的 一 個(gè) 字 段 ； DSUM是 數(shù) 據(jù) 庫(kù) 求 和 ， 條 件 涉 及 數(shù) 據(jù) 庫(kù) 的 兩 個(gè) 及 以上 字 段 時(shí) ， 使 用 該 函 數(shù) 。 6.4 函 數(shù)6.4.3 統(tǒng) 計(jì) 函 數(shù)操 作 技 巧(5) 要 明 白 函 數(shù) 的 參 數(shù) 及 其 數(shù) 據(jù) 類 型 。 要 留 意 插 入 函 數(shù) 時(shí)彈 出 的 “ 函 數(shù) 參 數(shù) ” 對(duì) 話 框 中 的 參 數(shù) 說(shuō) 明 ， 參 數(shù) 引 用 的 是單 元 格 還 是 區(qū) 域 。(6) 要 清 楚 函 數(shù) 的 返 回 值 是 什 么 數(shù) 據(jù) 類 型 、 什 么 意 義 ， 尤其 是 要 注 意 文 本 函 數(shù) 。(7) 在 編 輯 欄 輸 入 包 含 函 數(shù) 的 公 式 時(shí) ， 注 意 不 要 用 鼠 標(biāo) 單擊 其 他 無(wú) 關(guān) 的 單 元 格 ， 否 則 容 易 出 錯(cuò) 。 編 輯 完 成 后 按Enter鍵 退 出 。 6.4 函 數(shù)6.4.4 日 期 時(shí) 間 函 數(shù)1 年 月 日 轉(zhuǎn) 換 為 日 期 函 數(shù)格 式 ： DATE(year,month,day)功 能 ： 返 回 指 定 日 期 的 序 列 數(shù) 。2 時(shí) 分 秒 轉(zhuǎn) 換 為 時(shí) 間 函 數(shù)格 式 ： TIME(hour,minute,second)功 能 ： 函 數(shù) 的 結(jié) 果 hour時(shí) 、 minute 分 、 second秒 時(shí) 刻對(duì) 應(yīng) 的 小 數(shù) 值 。 如 果 所 在 的 單 元 格 數(shù) 字 格 式 設(shè) 為 “ 常 規(guī) ” ，則 結(jié) 果 顯 示 為 時(shí) 間 格 式 。 6.4 函 數(shù)6.4.4 日 期 時(shí) 間 函 數(shù)3 日 期 轉(zhuǎn) 換 為 年 月 日格 式 ： YAER(serial_number)、 MONTH(serial_number)與 DAY(serial_number)功 能 ： 分 別 返 回 序 列 數(shù) 的 年 、 月 、 日 的 數(shù) 值 。4 日 間 轉(zhuǎn) 換 為 時(shí) 分 秒格 式 ： HOUR(serial_number)、MINUTE(serial_number)與 SECOND(serial_number)功 能 ： 分 別 返 回 序 列 數(shù) 的 時(shí) 、 分 、 秒 的 數(shù) 值 。 6.4 函 數(shù)6.4.4 日 期 時(shí) 間 函 數(shù)5 系 統(tǒng) 日 期 時(shí) 間 函 數(shù)格 式 ： NOW( )功 能 ： 返 回 當(dāng) 前 日 期 和 時(shí) 間 。 如 果 預(yù) 先 將 所 在 的 單 元 格 數(shù)字 格 式 設(shè) 為 “ 常 規(guī) ” ， 則 顯 示 為 所 對(duì) 應(yīng) 的 序 列 號(hào) ， 小 數(shù) 點(diǎn)左 邊 的 數(shù) 表 示 日 期 ， 右 邊 的 數(shù) 表 示 時(shí) 間 。6 系 統(tǒng) 日 期 函 數(shù)格 式 ： TODAY( )功 能 ： 返 回 當(dāng) 前 日 期 。 如 果 預(yù) 先 將 所 在 的 單 元 格 的 格 式 設(shè)置 為 “ 常 規(guī) “ ， 則 結(jié) 果 顯 示 為 序 列 號(hào) 。 如 果 沒 有 對(duì) 單 元 格作 任 何 設(shè) 置 ， 則 顯 示 形 式 為 日 期 。 6.4 函 數(shù)6.4.4 日 期 時(shí) 間 函 數(shù)任 務(wù) 10打 開 “ 公 式 函 數(shù) ” 工 作 簿 ， 選 擇 “ 日 期 時(shí) 間 函 數(shù) ” 表 ， 計(jì)算 函 數(shù) 的 值 。操 作 步 驟各 日 期 時(shí) 間 函 數(shù) 的 計(jì) 算 如 圖 6-12所 示 。 圖 6-12 日 期 時(shí) 間 函 數(shù) 的 計(jì) 算 算 6.4 函 數(shù)6.4.5 文 本 函 數(shù)1 左 截 取 函 數(shù) 和 右 截 取 函 數(shù)格 式 ： LEFT(text,num_chars)，RIGHT(text,num_chars)功 能 ： 都 是 對(duì) text參 數(shù) 截 取 子 字 符 串 ， LEFT函 數(shù) 是 從 左起 截 取 num_chars個(gè) 字 符 ， 而 RIGHT函 數(shù) 是 從 右 起 截 取num_chars個(gè) 字 符 ， 省 略 num_chars參 數(shù) ， 則 默 認(rèn) 為 1。2 中 間 截 取 函 數(shù)格 式 ： MID(text,start_num,num_chars)功 能 ： 返 回 字 符 串 參 數(shù) text中 從 start_num位 置 開 始 的num_chars個(gè) 字 符 。 6.4 函 數(shù)6.4.5 文 本 函 數(shù)3 文 本 的 長(zhǎng) 度格 式 ： LEN(text)功 能 ： 返 回 文 本 字 符 串 中 的 字 符 個(gè) 數(shù) 。 6.4 函 數(shù)6.4.5 文 本 函 數(shù)任 務(wù) 11打 開 “ 公 式 函 數(shù) ” 工 作 簿 ， 選 擇 “ 文 本 函 數(shù) ” 表 ， 計(jì) 算 函數(shù) 的 值 。操 作 步 驟各 文 本 函 數(shù) 計(jì) 算 如 圖 6-13所 示 。 圖 6-13 各 文 本 函 數(shù) 的 計(jì) 算 6.4 函 數(shù)6.4.5 文 本 函 數(shù)操 作 技 巧文 本 長(zhǎng) 度 不 區(qū) 分 中 英 文 ， 每 個(gè) 字 符 計(jì) 算 為 1， 包 括 空 格 。6.4.6 邏 輯 函 數(shù)1 邏 輯 非 函 數(shù)格 式 ： NOT(logical)功 能 ： 對(duì) 參 數(shù) 求 相 反 的 邏 輯 值 ， 即 如 果 參 數(shù) 值 為 FALSE，則 NOT函 數(shù) 返 回 TRUE； 如 果 參 數(shù) 值 為 TRUE， 則 NOT函數(shù) 返 回 FALSE。 6.4 函 數(shù)6.4.6 邏 輯 函 數(shù)2 邏 輯 與 函 數(shù)格 式 ： AND(logical,logical2,)功 能 ： AND函 數(shù) ， 在 所 有 參 數(shù) 中 ， 只 要 有 一 個(gè) 參 數(shù) 的 邏輯 值 為 FALSE， 則 結(jié) 果 為 FALSE； 如 果 所 有 參 數(shù) 的 邏 輯值 都 為 TRUE， 結(jié) 果 才 為 TRUE。3 邏 輯 或 函 數(shù)格 式 ： OR(logical1,logical2,)功 能 ： OR函 數(shù) ， 在 所 有 參 數(shù) 中 ， 只 要 有 一 個(gè) 參 數(shù) 的 邏 輯值 為 TRUE， 則 結(jié) 果 為 TRUE； 如 果 所 有 參 數(shù) 的 邏 輯 值 都為 FALSE， 結(jié) 果 才 為 FALSE。 6.4 函 數(shù)6.4.6 邏 輯 函 數(shù)4 條 件 函 數(shù)格 式 ： IF(logical_test,value_if_true,value_if_false)功 能 ： Logical_test參 數(shù) 是 一 個(gè) 結(jié) 果 為 TRUE或 FALSE的表 達(dá) 式 ， 如 果 其 結(jié) 果 為 TRUE， 則 該 函 數(shù) 返 回value_if_true； 否 則 返 回 value_if_false。 圖 6-14 條 件 函 數(shù) 流 程 圖 6.4 函 數(shù)6.4.6 邏 輯 函 數(shù)任 務(wù) 12打 開 “ 公 式 函數(shù) .xls” 工 作 簿 ，選 擇 “ 邏 輯 函 數(shù) ”表 ， 計(jì) 算 函 數(shù) 的值 ， 如 圖 6-15所示 。 圖 6-15 邏 輯 表 計(jì) 算 6.4 函 數(shù)6.4.6 邏 輯 函 數(shù)操 作 步 驟(1) 計(jì) 算 補(bǔ) 考 否 。 D2單 元 格 公 式 為 ：“ =OR(B260,C2=80,C2=80)”(3) 計(jì) 算 英 語(yǔ) 等 級(jí) 。等 級(jí) 計(jì) 算 分 析 如 下 ： 根 據(jù) 條 件 ， 畫 出 流 程圖 ， 如 圖 6-16所 示 。 圖 6-16 具 體 二 重 條 件 函 數(shù) 流 程 圖 6.4 函 數(shù)6.4.6 邏 輯 函 數(shù)使 用 函 數(shù) 向 導(dǎo) 輸 入 公 式 的 操 作 步 驟 如 下 ：(1) 選 擇 單 元 格 C2， 選 擇 “ 插 入 ” |“ 函 數(shù) ” 命 令 ， 在 “ 插 入函 數(shù) ” 對(duì) 話 框 的 “ 選 擇 函 數(shù) ” 選 項(xiàng) 區(qū) 域 中 選 擇 IF函 數(shù) ， 單 擊“ 確 定 ” 按 鈕 ； 或 者 在 C2單 元 格 中 直 接 輸 入 =IF( )， 單 擊 “ 插入 函 數(shù) ” 按 鈕 ， 彈 出 “ 函 數(shù) 參 數(shù) ” 對(duì) 話 框 ， 如 圖 6-17所 示 。 圖 6-17 “ 函 數(shù) 參 數(shù) ” 對(duì) 話 框 6.4 函 數(shù)6.4.6 邏 輯 函 數(shù)使 用 函 數(shù) 向 導(dǎo) 輸 入 公 式 的 操 作 步 驟 如 下 ：(2) 選 中 Logical_test文 本 框 ， 單 擊 B2單 元 格 再 在 輸 入 框 中輸 入 “ =80”， 選 中 Value_true文 本 框 ， 直 接 輸 入 “ 優(yōu) ” ，可 以 不 輸 入 雙 引 號(hào) ， 系 統(tǒng) 自 動(dòng) 添 加 ； 選 中 Value_if_false文 本框 ， 直 接 輸 入 IF( )。(3) 單 擊 編 輯 欄 中 內(nèi) 嵌 的 IF( )函 數(shù) 名 ， 即 外 層 IF函 數(shù) 的 第 三 個(gè)參 數(shù) 的 位 置 ， 進(jìn) 入 內(nèi) 層 的 IF函 數(shù) “ 函 數(shù) 參 數(shù) ” 輸 入 對(duì) 話 框 ，輸 入 對(duì) 應(yīng) 參 數(shù) 。(4) 返 回 外 層 的 IF函 數(shù) ， 只 需 在 “ 編 輯 欄 ” 中 單 擊 外 層 IF函 數(shù)的 名 的 位 置 。 6.4 函 數(shù)6.4.6 邏 輯 函 數(shù)使 用 函 數(shù) 向 導(dǎo) 輸 入 公 式 的 操 作 步 驟 如 下 ：(5) 如 果 又 要 進(jìn) 入 內(nèi) 層 IF函 數(shù) ， 再 單 擊 內(nèi) 層 IF函 數(shù) 名 。(6) 檢 查 無(wú) 誤 后 ， 按 “ 確 定 ” 按 鈕 或 單 擊 編 輯 欄 上 的 按 鈕 結(jié)束 公 式 的 輸 入 。操 作 技 巧公 式 輸 入 以 后 ， 發(fā) 現(xiàn) 有 誤 ， 又 希 望 用 向 導(dǎo) 修 改 ， 這 時(shí) 可 選擇 公 式 所 在 的 單 元 格 ， 直 接 單 擊 “ 插 入 函 數(shù) ” 按 鈕 ， 再 次打 開 “ 函 數(shù) 參 數(shù) ” 對(duì) 話 框 。 修 改 后 ， 按 “ 確 定 ” 按 鈕 結(jié) 束修 改 。 對(duì) 于 其 他 內(nèi) 嵌 的 函 數(shù) 也 可 按 照 此 方 法 輸 入 ， 做 到 舉 一 反 三 。 6.4 函 數(shù)6.4.7 頻 率 分 布 函 數(shù)格 式 ： FREQUENCY(data_array,bins_array)功 能 ： 按 照 bins_array參 數(shù) 設(shè) 置 的 間 隔 ， 計(jì) 算 data_array參 數(shù) 所 在 數(shù) 據(jù) 的 頻 率 分 布 。任 務(wù) 13打 開 “ 公 式 函 數(shù) ” 工 作 簿 ， 選 擇 “ 頻 率 分 布 函 數(shù) ” 表 ， 統(tǒng)計(jì) “ 計(jì) 算 機(jī) ” 分 數(shù) 在 以 下 分 數(shù) 段 的 人 數(shù) ， 60分 以 下 (即 不及 格 )、 60 69、 70 79、 80 89、 90 99、 100分 。 6.4 函 數(shù)6.4.7 頻 率 分 布 函 數(shù)操 作 步 驟(1) 在 H3:H7區(qū) 域 內(nèi)依 次 輸 入 間 隔 點(diǎn) 59、69、 79、 89、 99，注 意 不 是 60、 70、80、 90、 100， 因 為每 個(gè) 間 隔 點(diǎn) 的 含 義 是小 于 或 等 于 ， 如 果 設(shè)置 為 60而 不 是 59的話 ， 會(huì) 將 60分 當(dāng) 作 不及 格 統(tǒng) 計(jì) ， 如 圖 6-18 所 示 。 圖 6-18 FREQUENCY函 數(shù) 應(yīng) 用 6.4 函 數(shù)6.4.7 頻 率 分 布 函 數(shù)操 作 步 驟(2) 選 擇 I3:I8區(qū) 域 (比 分 隔 區(qū) 域 向 下 多 一 個(gè) 單 元 格 )， 在 編輯 欄 輸 入 “ =FREQUENCY (E3:E17,H3:H8)”(3) 按 Ctrl+Shift+Enter組 合 鍵 。 結(jié) 果 顯 示 在 I3:I8區(qū) 域 ，其 中 I8單 元 格 指 的 是 99分 以 上 的 學(xué) 生 人 數(shù) 。