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

EXCEL 常 用 函 數(shù) 培 訓(xùn) 學(xué) 習(xí) 內(nèi) 容 1 EXCEL快 捷 鍵 操 作 2 數(shù) 學(xué) 與 統(tǒng) 計 函 數(shù) 3 邏 輯 函 數(shù) 4 文 本 函 數(shù) 5 查 找 與 引 用 函 數(shù) 6 數(shù) 據(jù) 分 類 與 匯 總 7 數(shù) 據(jù) 透 視 表 1 EXCEL快 捷 鍵 操 作打 開 工 作 簿 Ctrl+O保 存 工 作 簿 Ctrl+S增 加 一 個 新 的 工 作 薄 Ctrl+N關(guān) 閉 當(dāng) 前 工 作 薄 Ctrl+W恢 復(fù) 選 定 工 作 簿 窗 口 的 大 小 Ctrl+F5最 大 化 選 定 工 作 簿 窗 口 Ctrl+F10最 小 化 選 定 工 作 簿 窗 口 Ctrl+F9 切 換 到 上 一 個 工 作 簿 窗 口 Ctrl+F6切 換 到 下 一 個 工 作 簿 窗 口 Ctrl+Shift+F6實 操 1 EXCEL快 捷 鍵 操 作 相 對 引 用 與 絕 對 引 用 轉(zhuǎn) 換 F4重 新 計 算 工 作 簿 的 所 有 工 作 表 F9輸 入 當(dāng) 前 日 期 Ctrl+；輸 入 當(dāng) 前 時 間 Ctrl+Shift+：向 下 填 充 輸 入 Ctrl+D向 右 填 充 輸 入 Ctrl+R 打 開 “ 單 元 格 格 式 ” 對 話 框 Ctrl+1 1 EXCEL快 捷 鍵 操 作撤 銷 上 步 操 作 Ctrl+Z恢 復(fù) 上 步 操 作 Ctrl+Y重 復(fù) 上 步 操 作 F4剪 切 Ctrl+X復(fù) 制 Ctrl+C粘 貼 Ctrl+V查 找 Ctrl+F替 換 Ctrl+H 定 位 Ctrl+G插 入 批 注 Shift+F2打 開 “ 插 入 函 數(shù) ” 對 話 框 Shift+F3定 義 名 稱 Ctrl+ F3 2 數(shù) 學(xué) 與 統(tǒng) 計 函 數(shù)2.1 ROUND函 數(shù) 將 數(shù) 字 四 舍 五 入用 途 ： 按 指 定 位 數(shù) 四 舍 五 入 某 個 數(shù) 字 。語 法 ： ROUND(number， num_digits)參 數(shù) 意 義 ： Number是 需 要 四 舍 五 入 的 數(shù) 字 ； Num_digits為 指 定 的 位 數(shù) ， Number按 此 位 數(shù) 進(jìn) 行 處 理 。注 意 :（ 1） 在 單 元 格 格 式 中 可 以 設(shè) 置 小 數(shù) 位 數(shù) ， 但 數(shù) 字 本 身 并 沒 有 真 正 的 四 舍 五 入 ， 只 是 以 2位 （ 如 設(shè) 置 為 2） 的 形 式 顯 示 數(shù) 據(jù) ， 如 果 財 務(wù) 中采 用 這 種 方 式 進(jìn) 行 計 算 ， 經(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.2 RANK 函 數(shù) 統(tǒng) 計 排 名用 途 ： 返 回 一 個 數(shù) 字 在 數(shù) 字 列 表 中 的 排 位 。 數(shù) 字 的 排 位 是 其 大 小 與 列 表 中 其 他 值 的 比 值 （ 如 果 列 表 已 排 過 序 ， 則 數(shù) 字 的 排 位 就 是 它 當(dāng) 前 的 位 置 ） 。 語 法 ： RANK(number,ref,order) 參 數(shù) 意 義 ： Number 為 需 要 找 到 排 位 的 數(shù) 字 ； Ref 為 數(shù) 字 列 表 數(shù) 組 或 對 數(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.4 AVERAGE 平 均 值 函 數(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） 。 2數(shù) 學(xué) 與 統(tǒng) 計 函 數(shù)2.5 LARGE函 數(shù) 2.6 SMALL函 數(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。實 例 剖 析 2數(shù) 學(xué) 與 統(tǒng) 計 函 數(shù) 3.1 IF函 數(shù) - 進(jìn) 行 條 件 檢 測功 能 ： 執(zhí) 行 真 假 值 判 斷 ， 根 據(jù) 邏 輯 計 算 的 真 假 值 ， 返 回 不 同 結(jié) 果 。 可 以 使 用 函 數(shù) IF 對 數(shù) 值 和 公 式 進(jìn) 行 條 件 檢 測 。 語 法 ： IF(logical_test,value_if_true,value_if_false) 參 數(shù) ： Logical_test 表 示 計 算 結(jié) 果 為 TRUE 或 FALSE 的 任 意 值 或 表 達(dá) 式 。 Value_if_true logical_test 為 TRUE 時 返 回 的 值 。 Value_if_false logical_test 為 FALSE 時 返 回 的 值 。 實 例 剖 析 3 邏 輯 函 數(shù) 3 邏 輯 函 數(shù)功 能 ： 所 有 參 數(shù) 的 邏 輯 值 為 真 時 ， 返 回 TRUE； 只 要 一 個 參 數(shù) 的 邏 輯 值 為 假 ， 即 返 回 FLASE。語 法 ： AND(logical1,logical2, .) 參 數(shù) ： Logical1, logical2, . 表 示 待 檢 測 的 1 到 30 個 條 件 值 ， 各 條 件 值 可 為 TRUE 或 FALSE。 3.2 AND函 數(shù) 判 斷 多 個 條 件 同 時 為 真 3.3 OR函 數(shù) 判 斷 任 一 參 數(shù) 為 真功 能 ： 在 其 參 數(shù) 組 中 ， 任 何 一 個 參 數(shù) 邏 輯 值 為 TRUE， 即 返 回 TRUE； 所 有 參 數(shù) 的 邏 輯 值 為 FALSE， 才 返 回 FALSE 。語 法 ： OR(logical1,logical2,.) 參 數(shù) ： Logical1,logical2.為 需 要 進(jìn) 行 檢 驗 的 1到 30 個 條 件 表 達(dá) 式 。 實 例 剖 析 條 件 求 和 函 數(shù) SUMIF 語 法 ： SUMIF（ range， criteria， sum_range） 。參 數(shù) ： range是 用 于 條 件 判 斷 的 單 元 格 區(qū) 域 ， criteria是 由 數(shù) 字 、 邏 輯 表 達(dá) 式 等 組 成 的 判 定 條 件 ， 為 確 定 哪 些 單 元 格 將 被 相 加 求 和 的 條 件 ， 其 形 式 可 以 為 數(shù) 字 、 表 達(dá) 式 或 文 本 sum_range為 需 要 求 和 的 單 元 格 、 區(qū) 域 或 引 用 。 實 例 操 作 4.1 MID、 LEFT 、 RIGHT 函 數(shù) -獲 取 字 符4.1.1 MID 函 數(shù) 功 能 ： 從 文 本 字 符 串 中 指 定 的 起 始 位 置 起 返 回 指 定 長 度 的 字 符 語 法 ： Mid(text,start_num,num_chars) 參 數(shù) ： text 準(zhǔn) 備 從 中 提 取 字 符 串 的 文 本 字 符 串 start_num準(zhǔn) 備 提 取 的 第 一 個 字 符 的 位 置 num_chars指 定 所 要 提 取 的 字 符 串 的 長 度4.1.2 RIGHT 函 數(shù)功 能 ： 從 一 個 文 本 字 符 串 的 最 后 一 個 字 符 開 始 返 回 指 定 個 數(shù) 字 符語 法 ： RIGHT(text, num_chars) 參 數(shù) ： text 要 提 取 字 符 的 字 符 串 num_chars要 提 取 的 字 符 數(shù) ， 如 果 忽 略 ， 為 14.1.3 LEFT 函 數(shù) 功 能 ： 從 一 個 文 本 字 符 串 的 第 一 個 字 符 開 始 返 回 指 定 個 數(shù) 字 符語 法 ： LEFT(text, num_chars) 參 數(shù) ： text 要 提 取 字 符 的 字 符 串 num_chars要 提 取 的 字 符 數(shù) ， 如 果 忽 略 ， 為 1 實 例 剖 析 4 文 本 函 數(shù) 4.2 CONCATENATE函 數(shù) 4 文 本 函 數(shù)功 能 ： 將 幾 個 文 本 字 符 串 合 并 為 一 個 文 本 字 符 串 。語 法 ： CONCATENATE (text1,text2,.)參 數(shù) ： Text1, text2, . 為 1 到 30 個 將 要 合 并 成 單 個 文 本 項 的 文 本 項 。 這 些 文 本 項 可 以 為 文 本 字 符 串 、 數(shù) 字 或 對 單 個 單 元 格 的 引 用 。補(bǔ) 充 說 明 ： 也 可 以 用 能 夠 快 速 匯 總 大 量 數(shù) 據(jù) ;能 夠 基 于 原 數(shù) 據(jù) 表 創(chuàng) 建 數(shù) 據(jù) 分 組 ， 并 對 分 組 進(jìn) 行 匯 總 統(tǒng)計 。 7 數(shù) 據(jù) 透 視 表7.2 數(shù) 據(jù) 透 視 表 的 建 立 選 擇 “ 數(shù) 據(jù) ” “數(shù) 據(jù) 透 視 表 和 數(shù) 據(jù) 透 視 圖 ” ; 按 向 導(dǎo) 提 示 做 完 1， 2步 ; 在 第 3步 中 選 擇 “ 布 局 ”按 鈕 ， 出 現(xiàn) 如 圖 所 示 的 對 話 框 。 7 數(shù) 據(jù) 透 視 表 解 釋 : 行 ： 拖 放 到 行 中 的 數(shù) 據(jù) 字 段 中 的 每 個 數(shù) 據(jù) 項 將 占 據(jù) 透 視 表 的 一行 。 列 ： 拖 放 到 列 中 的 數(shù) 據(jù) 字 段 中 的 每 個 數(shù) 據(jù) 項 將 占 據(jù) 透 視 表 的 一列 。 行 和 列 確 定 一 個 二 維 表 格 . 頁 ： 拖 放 在 頁 中 的 字 段 ， Excel將 按 該 字 段 的 數(shù) 據(jù) 項 對 透 視 表進(jìn) 行 分 頁 。 數(shù) 據(jù) ： 進(jìn) 行 計 數(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ù) 謝 謝 ！