《excel教程》PPT課件

《《excel教程》PPT課件》由會(huì)員分享，可在線閱讀，更多相關(guān)《《excel教程》PPT課件（140頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

1、1 本 培 訓(xùn) 內(nèi) 容 認(rèn) 為 你 有 一 定 的 計(jì) 算 機(jī) 應(yīng) 用能 力 ， 使 用 過 Excel， 懂 得 Excel的 基 本的 操 作 方 法 。 本 培 訓(xùn) 案 例 選 自 工 作 中 遇 到 的 問 題 。 本 培 訓(xùn) 以 提 高 工 作 效 率 為 出 發(fā) 點(diǎn) 。 2 課 程 內(nèi) 容 第 一 部 分 基 礎(chǔ) 知 識(shí) 第 二 部 分 函 數(shù) 與 公 式 第 三 部 分 多 條 件 求 和 與 多 條 件 計(jì) 數(shù) 數(shù) 組 公 式 第 四 部 分 排 序 、 篩 選 、 條 件 格 式 與 數(shù)據(jù) 有 效 性 驗(yàn) 證 第 五 部 分 圖 表 3 第 一 部 分 基 礎(chǔ) 知 識(shí) 一 、

2、 字 段 、 記 錄 與 單 元 格 二 、 單 元 格 三 、 基 本 運(yùn) 算 與 運(yùn) 算 符 四 、 數(shù) 據(jù) 類 型 4 一 、 字 段 、 記 錄 在 數(shù) 據(jù) 庫 中 ， 每 一 列 被 稱 為 一 個(gè) “字 段 ”。 每 個(gè) 字 段 包 含 某 一 專 題 的 信 息 。 就 像 “工 資 表 ”數(shù) 據(jù) 庫 中 ， “姓 名 ”、 “崗 位工 資 ”這 些 都 是 表 中 所 有 行 共 有 的 屬 性 ，所 以 把 這 些 列 稱 為 “姓 名 ”字 段 和 “崗 位 工資 ”字 段 。 5 表 中 的 每 一 行 叫 做 一 個(gè) “記 錄 ”， 每 一 個(gè)記 錄 包 含 這 行 中

3、 的 所 有 信 息 ， 就 像 在 通訊 錄 數(shù) 據(jù) 庫 中 某 個(gè) 人 全 部 的 信 息 。 6 二 、 單 元 格 本 部 分 重 點(diǎn) 講 述 單 元 格 的 引 用 方 式 ： 絕對 應(yīng) 用 和 相 對 引 用 7 相 對 引 用 當(dāng) 把 公 式 復(fù) 制 到 其 他 單 元 格 時(shí) ， 行 或 者列 就 會(huì) 改 變 。 因 為 引 用 的 是 當(dāng) 前 行 或 者列 的 實(shí) 際 偏 移 量 。 如 單 元 格 顯 示 為 A1，向 下 復(fù) 制 會(huì) 自 動(dòng) 變 成 A2， 向 右 復(fù) 制 向B1。 相 對 引 用 實(shí) 際 上 是 引 用 的 單 元 格 的 相 對位 置 。 8 絕 對

4、引 用 ， 當(dāng) 復(fù) 制 公 式 時(shí) ， 行 和 列 不 會(huì)改 變 。 因 為 引 用 的 是 單 元 格 的 實(shí) 際 地 址 。單 元 格 顯 示 為 $A$1。 此 時(shí) 向 下 或 向 右 復(fù) 制 單 元 格 ， 顯 示 的 內(nèi)容 不 變 ， 還 是 $A$1， 所 引 用 的 對 象 不變 ， 還 是 當(dāng) 前 A1單 元 格 的 數(shù) 據(jù) 。 9 混 合 引 用 ， 行 或 者 列 當(dāng) 中 有 一 個(gè) 是 絕 對引 用 ， 另 一 個(gè) 是 相 對 引 用 。 單 元 格 顯 示為 $A1（ 絕 對 引 用 列 ） 或 A$1（ 絕 對 引用 行 ） 。 $A1向 右 復(fù) 制 ， 會(huì) 變 為

5、$A1， 所 引 用 的單 元 格 為 當(dāng) 前 表 的 A1單 元 格 。 向 下 復(fù) 制 ， 會(huì) 變 為 $A2， 所 引 用 的 單 元格 實(shí) 際 是 A2。 10 示 例 ：相 對 引 用 ， 復(fù) 制 公 式 時(shí) 地 址 跟 著 發(fā) 生 變 化 。如 C1單 元 格 有 公 式 ： =A1+B1 當(dāng) 將 公 式 復(fù) 制 到 C2單 元 格 時(shí) 變 為 ：=A2+B2當(dāng) 將 公 式 復(fù) 制 到 D1單 元 格 時(shí) 變 為 ：=B1+C1 11 絕 對 引 用 ， 復(fù) 制 公 式 時(shí) 地 址 不 會(huì) 跟 著 發(fā)生 變 化 ， 如 C1單 元 格 有 公 式 ：=$A$1+$B$1 當(dāng) 將 公

6、 式 復(fù) 制 到 C2單 元 格 時(shí) 仍 為 ：=$A$1+$B$1 當(dāng) 將 公 式 復(fù) 制 到 D1單 元 格 時(shí) 仍 為 ：=$A$1+$B$1 12 混 合 引 用 ， 復(fù) 制 公 式 時(shí) 地 址 的 部 分 內(nèi) 容跟 著 發(fā) 生 變 化 ， 如 C1單 元 格 有 公 式 ：=$A1+B$1 。當(dāng) 公 式 被 復(fù) 制 到 C2單 元 格 時(shí) 會(huì) 變 為 ：=$A2+B$1 當(dāng) 公 式 被 復(fù) 制 到 D1單 元 格 時(shí) 會(huì) 變 為 ：=$A1+C$1 13 規(guī) 律 ： 加 上 了 絕 對 地 址 符 “ $” 的 列 標(biāo)和 行 號(hào) 為 絕 對 地 址 ， 在 公 式 向 旁 邊 復(fù) 制

7、時(shí) 不 會(huì) 發(fā) 生 變 化 ， 沒 有 加 上 絕 對 地 址 符號(hào) 的 列 標(biāo) 和 行 號(hào) 為 相 對 地 址 ， 在 公 式 向旁 邊 復(fù) 制 時(shí) 會(huì) 跟 著 發(fā) 生 變 化 。 混 合 引 用時(shí) 部 分 地 址 發(fā) 生 變 化 。 $在 誰 的 前 面 就 絕 對 引 用 誰 ， F4是 在 四種 引 用 間 相 互 轉(zhuǎn) 換 的 快 捷 鍵 。 14 單 元 格 的 另 外 一 種 表 示 方 法 ： R1C1 R=Row C=Column， 優(yōu) 勢 ， 便 于 對 單 元格 位 置 進(jìn) 行 計(jì) 算 和 判 斷 。 了 解 即 可 ！ 15 三 、 運(yùn) 算 符 、 優(yōu) 先 級(jí) 與 基本

8、運(yùn) 算 簡 單 說 常 用 的 運(yùn) 算 符 就 是 加 減 乘 除 。 優(yōu) 先 級(jí) 很 復(fù) 雜 。 我 簡 單 總 結(jié) 為 ： 與 小 學(xué)數(shù) 學(xué) 課 講 的 優(yōu) 先 級(jí) 大 體 相 同 。 16 示 例 ： =1+1 =（ A1+A2） *3 =（ A1+（ A2+A3） /2） *8 =25 注 意 ， excel中 的 括 號(hào) 僅 有 （ ） 一 種 ， 不 是 運(yùn) 算 符 ， 不 是 括 號(hào) 。 17 四 、 數(shù) 據(jù) 類 型 不 同 于 數(shù) 據(jù) 庫 ， excel的 數(shù) 據(jù) 類 型 非 常 簡單 ， 只 有 三 種 ： 數(shù) 值 文 本 公 式 18 (一 ) 數(shù) 值 概 括 地 說 ，

9、數(shù) 值 可 以 理 解 為 一 些 數(shù) 據(jù) 類型 的 數(shù) 量 ， 數(shù) 值 有 一 個(gè) 共 同 的 特 點(diǎn) ， 就是 常 常 用 于 各 種 數(shù) 學(xué) 計(jì) 算 。 工 資 金 額 、部 門 人 數(shù) 、 員 工 年 齡 等 等 數(shù) 據(jù) ， 都 屬 于數(shù) 值 類 型 。 19 注 意 ： 我 們 常 常 使 用 日 期 、 時(shí) 間 數(shù) 據(jù) 也都 屬 于 數(shù) 值 類 型 的 數(shù) 據(jù) 。 可 以 通 過 在 單元 格 區(qū) 域 點(diǎn) 擊 鼠 標(biāo) 右 鍵 -單 元 格 類 型 -數(shù)字 -分 類 改 變 顯 示 格 式 或 轉(zhuǎn) 換 數(shù) 據(jù) 類 型 。 20 (二 ) 文 本 說 明 性 ， 解 釋 性 的 數(shù) 據(jù)

10、 描 述 我 們 稱 為 文本 類 型 。 文 本 當(dāng) 然 是 非 數(shù) 值 類 型 的 。 比如 ， 員 工 信 息 表 的 列 標(biāo) 題 “姓 名 ”、 “性別 ”、 “部 門 ”等 字 符 都 屬 于 文 本 類 型 。 21 文 本 和 數(shù) 值 有 時(shí) 候 容 易 混 淆 ， 比 如 手 機(jī)號(hào) 碼 “1398888888”， 銀 行 帳 號(hào)“310009000”， 從 外 表 上 它 是 數(shù) 字 組 成的 ， 但 實(shí) 際 上 我 們 應(yīng) 告 訴 Excel把 它 們 作為 文 本 處 理 ， 因 為 它 們 并 不 是 數(shù) 量 ， 而是 描 述 性 的 文 本 。 22 通 過 在 電 話

11、 號(hào) 碼 前 加 半 角 （ 英 文 狀 態(tài) 下 ）的 “ ” 號(hào) ， 告 訴 Excel把 之 后 的 內(nèi) 容 當(dāng)做 文 本 處 理 。 如 139083900001 注 意 號(hào) 會(huì) 顯 示 在 公 式 欄 中 ， 但 不 會(huì) 被 打印 出 來 。 23 注 ： 部 分 文 本 可 以 通 過 特 殊 公 式 （ value）轉(zhuǎn) 換 數(shù) 值 型 數(shù) 據(jù) 。（ 了 解 即 可 ） Value函 數(shù) 可 以 把 將 代 表 數(shù) 字 的 文 本 字符 串 轉(zhuǎn) 換 成 數(shù) 字 。 用 法 ： =value（ ） 24 (三 ) 公 式 Excel把 公 式 列 為 不 同 于 “數(shù) 值 ”和 “文

12、本 ”之 外 的 第 三 種 數(shù) 據(jù) 類 型 。 公 式 的 共 同 特 點(diǎn) 是 以 “=”號(hào) 開 頭 ， 它 可 以是 簡 單 的 數(shù) 學(xué) 式 ， 也 可 以 是 包 含 各 種Excel函 數(shù) 的 式 子 。 25 注 意 ： excel中 凡 是 以 “ =” 開 頭 的 都 是公 式 ， 除 數(shù) 組 公 式 外 ， 所 有 公 式 也 必 須以 “ =” 開 頭 （ 本 質(zhì) 上 說 ， 數(shù) 組 公 式 也是 以 “ =” 開 頭 的 ） 。 26 第 二 部 分 函 數(shù) 與 公 式 27 一 、 文 本 函 數(shù)二 、 統(tǒng) 計(jì) 函 數(shù)三 、 邏 輯 值 與 邏 輯 函 數(shù)四 、 時(shí) 間

13、日 期 函 數(shù) 函 數(shù) 是 excel的 精 華 所 在 。 本 部 分 選 在 工作 中 常 用 的 部 分 函 數(shù) 進(jìn) 行 講 解 。 本 部 分 采 用 對 函 數(shù) 意 義 解 釋 加 示 例 的 方法 講 解 函 數(shù) 的 具 體 使 用 ， 一 般 不 分 析 語法 結(jié) 構(gòu) 。 28 一 、 文 本 函 數(shù) Left 根 據(jù) 所 指 定 的 字 符 數(shù) ， LEFT 返 回 文 本字 符 串 中 第 一 個(gè) 字 符 或 前 幾 個(gè) 字 符 ， 即返 回 文 本 左 邊 的 幾 個(gè) 字 符 。 示 例 ： =left（ ”重 慶 廣 播 電 視 集 團(tuán) （ 總臺(tái) ） ”， 2） 計(jì) 算

14、結(jié) 果 ： 重 慶 29 Right 根 據(jù) 所 指 定 的 字 符 數(shù) 返 回 文 本 字 符 串 中最 后 一 個(gè) 或 多 個(gè) 字 符 ， 即 返 回 文 本 右 邊的 幾 個(gè) 字 符 。 示 例 ： =right（ ”重 慶 廣 播 電 視 集 團(tuán) （ 總臺(tái) ） ”， 4） 計(jì) 算 結(jié) 果 ： （ 總 臺(tái) ） 30 Mid 返 回 文 本 字 符 串 中 從 指 定 位 置 開 始 的 特定 數(shù) 目 的 字 符 ， 該 數(shù) 目 由 用 戶 指 定 示 例 ： =mid（ “ 重 慶 廣 播 電 視 集 團(tuán) （ 總臺(tái) ） ” ， 3,2） ， 結(jié) 果 ： 廣 播 31 Len 返 回 文

15、本 字 符 串 中 的 字 符 數(shù) 示 例 ： =len（ “ 重 慶 廣 播 電 視 集 團(tuán) （ 總臺(tái) ） ” ） 結(jié) 果 ： 12 32 Replace REPLACE 使 用 其 他 文 本 字 符 串 并 根 據(jù)所 指 定 的 字 符 數(shù) 替 換 某 文 本 字 符 串 中 的部 分 文 本 。 =REPLACE(“ 重 慶 廣 播 電 視 集 團(tuán) （ 總臺(tái) ） ” ,3,2,abc)， 結(jié) 果 ： 重 慶 abc電視 集 團(tuán) （ 總 臺(tái) ） 33 Clean 刪 除 文 本 中 不 能 打 印 的 字 符 。 對 從 其 他應(yīng) 用 程 序 中 輸 入 的 文 本 使 用 本 函 數(shù)

16、， 將刪 除 其 中 含 有 的 當(dāng) 前 操 作 系 統(tǒng) 無 法 打 印的 字 符 。 例 如 ， 可 以 刪 除 通 常 出 現(xiàn) 在 數(shù)據(jù) 文 件 頭 部 或 尾 部 、 無 法 打 印 的 低 級(jí) 計(jì)算 機(jī) 代 碼 。 如 從 工 改 程 序 導(dǎo) 出 數(shù) 據(jù) 中 存 在 的 換 行 符等 。 34 SUBSTITUTE 在 文 本 字 符 串 中 替 換 指 定 的 文 本 。 示 例 ： =SUBSTITUTE(“ 重 慶 廣 播 電視 集 團(tuán) （ 總 臺(tái) ） ” ,廣 播 ,abc) 結(jié) 果 ：重 慶 abc電 視 集 團(tuán) （ 總 臺(tái) ） 35 FIND 用 于 在 第 二 個(gè) 文 本

17、 串 中 定 位 第 一 個(gè) 文 本串 ， 并 返 回 第 一 個(gè) 文 本 串 的 起 始 位 置 的值 ， 該 值 從 第 二 個(gè) 文 本 串 的 第 一 個(gè) 字 符算 起 。 示 例 ： =FIND(“廣 播 ”,“ 重 慶 廣 播 電 視集 團(tuán) （ 總 臺(tái) ） ” ) ， 結(jié) 果 ： 3. 36 連 字 符 & 連 字 符 不 屬 于 函 數(shù) ， 主 要 功 能 為 連 接 兩個(gè) 文 本 字 符 串 。 為 便 于 掌 握 ， 將 其 放 在文 本 函 數(shù) 部 分 講 解 。 示 例 ： =“ 重 慶 ” &“ 廣 播 ” 結(jié) 果 ： 重 慶廣 播 37 文 本 函 數(shù) 綜 合 應(yīng) 用

18、示 例 見 Excel文 件 。 38 二 、 統(tǒng) 計(jì) 函 數(shù) 本 部 分 函 數(shù) 示 例 需 要 大 量 數(shù) 據(jù) ， 因 此 ，示 例 在 excel操 作 ， 不 在 此 處 一 一 羅 列 。 39 Count 返 回 包 含 數(shù) 字 的 單 元 格 的 個(gè) 數(shù) 以 及 返 回參 數(shù) 列 表 中 的 數(shù) 字 個(gè) 數(shù) 。 40 Counta 返 回 參 數(shù) 列 表 中 非 空 值 的 單 元 格 個(gè) 數(shù) 。利 用 函 數(shù) COUNTA 可 以 計(jì) 算 單 元 格 區(qū)域 或 數(shù) 組 中 包 含 數(shù) 據(jù) 的 單 元 格 個(gè) 數(shù) 。 簡 單 說 ， 數(shù) 人 頭 就 用 這 個(gè) 函 數(shù) 。 41

19、Countblank 計(jì) 算 指 定 單 元 格 區(qū) 域 中 空 白 單 元 格 （ 不包 括 數(shù) 值 零 ） 的 個(gè) 數(shù) 。 42 Countif 計(jì) 算 區(qū) 域 中 滿 足 給 定 條 件 的 單 元 格 的 個(gè)數(shù) 。 條 件 可 以 表 示 為 數(shù) 字 、 文 本 ， 可 以 直 接引 用 ， 也 可 存 放 在 單 元 格 中 。 比 如 ： 32、“32”、 “32” 、 “電 視 或 B4。 43 簡 單 說 ， 有 條 件 要 求 的 數(shù) 人 頭 就 用Countif函 數(shù) 。 這 是 我 們 工 作 中 遇 到 統(tǒng) 計(jì) 時(shí) 使 用 最 最 多的 函 數(shù) 之 一 。 重 點(diǎn) 掌

20、握 ！ 44 Sumif 是 要 根 據(jù) 條 件 計(jì) 算 的 單 元 格 區(qū) 域 。 每 個(gè)區(qū) 域 中 的 單 元 格 都 必 須 是 數(shù) 字 和 名 稱 、數(shù) 組 和 包 含 數(shù) 字 的 引 用 。 空 值 和 文 本 值將 被 忽 略 。 45 Rank 返 回 一 個(gè) 數(shù) 字 在 數(shù) 字 列 表 中 的 排 位 。 數(shù)字 的 排 位 是 其 大 小 與 列 表 中 其 他 值 的 比值 。 46 Average 返 回 參 數(shù) 的 平 均 值 （ 算 術(shù) 平 均 值 ） 。 注意 ： 0值 計(jì) 入 ， 空 單 元 格 不 計(jì) 入 。 47 Max 返 回 一 組 值 中 的 最 大 值

21、48 Min 返 回 一 組 值 中 的 最 小 值 。 49 Round 返 回 某 個(gè) 數(shù) 字 按 指 定 位 數(shù) 取 整 后 的 數(shù) 字 ，即 四 舍 五 入 。 函 數(shù) 的 第 二 個(gè) 參 數(shù) 表 示 四 舍 五 入 的 位 數(shù) ，0表 示 四 舍 五 入 到 整 數(shù) 位 。 大 于 0， 則 四舍 五 入 到 指 定 的 小 數(shù) 位 。 小 于 0， 則 在小 數(shù) 點(diǎn) 左 側(cè) 進(jìn) 行 四 舍 五 入 。 50 Vlookup 在 表 格 數(shù) 組 的 首 列 查 找 指 定 的 值 ， 并 由此 返 回 表 格 數(shù) 組 當(dāng) 前 行 中 其 他 列 的 值 。 51 統(tǒng) 計(jì) 函 數(shù) 綜

22、合 應(yīng) 用 示 例 ： 求 “ 職 員 ” 崗 位 工 資 的 平 均 值 ， 并 四 舍五 入 至 元 。 見 Excel。 52 三 、 邏 輯 值 與 邏 輯 函 數(shù) 53 邏 輯 值 邏 輯 值 包 括 True和 False兩 類 。 可 簡 單 理 解 為 ： True， 正 確 ； False，錯(cuò) 誤 。 邏 輯 值 可 參 與 基 本 運(yùn) 算 。 True在 計(jì) 算 時(shí)按 照 1處 理 ， Fals在 計(jì) 算 時(shí) 按 照 0處 理 。 54 邏 輯 值 也 可 以 是 邏 輯 比 較 的 結(jié) 果 ， 如（ 1=1） ， （ 性 別 =“ 男 ” ） 。 比 較 結(jié) 果正 確 時(shí)

23、 ， 結(jié) 果 為 True， 錯(cuò) 誤 時(shí) 為 False。 案 例 見 Excel。 55 同 樣 ， 在 If函 數(shù) 判 斷 的 條 件 中 ， 0和 1也可 以 等 效 為 False和 True處 理 （ 這 種 說法 不 嚴(yán) 謹(jǐn) ， 但 有 助 于 理 解 問 題 ） 邏 輯 值 一 般 不 會(huì) 被 作 為 結(jié) 果 直 接 使 用 ，而 是 作 為 公 式 判 斷 的 中 間 值 而 存 在 。 56 邏 輯 函 數(shù)If 根 據(jù) 對 指 定 的 條 件 計(jì) 算 結(jié) 果 為 TRUE 或 FALSE， 返 回 不 同 的 結(jié) 果 。 57 本 函 數(shù) 應(yīng) 用 較 多 ， 對 本 函 數(shù)

24、的 講 解 采 用微 軟 的 標(biāo) 準(zhǔn) 格 式 更 利 于 大 家 理 解 。 函 數(shù) 格 式 ： IF(logical_test,value_if_true,value_if_false) 即 ， IF（ 邏 輯 判 斷 ， 如 果 正 確 返 回 值 ，如 果 錯(cuò) 誤 返 回 值 ） 58 Logical_test 表 示 計(jì) 算 結(jié) 果 為 TRUE 或 FALSE 的 任 意 值 或 表 達(dá) 式 。 例 如 ， A10=100 就 是 一 個(gè) 邏 輯 表 達(dá) 式 ；如 果 單 元 格 A10 中 的 值 等 于 100， 表達(dá) 式 的 計(jì) 算 結(jié) 果 為 TRUE； 否 則 為 FALS

25、E。 此 參 數(shù) 可 使 用 任 何 比 較 運(yùn) 算 符 。 59 常 見 的 比 較 運(yùn) 算 符 如 下 ： “=”， ”,“ =” ,“ ” “ =” ， “ ”（ 不 等 于 ） 。 簡 單 說 ， 邏 輯 表 達(dá) 式 就 是 把 兩 個(gè) 東 西 放在 一 起 比 較 。 60 Value_if_true 是 logical_test 為 TRUE 時(shí) 返 回 的 值 。 也 就 是 邏 輯 表 達(dá) 式為 “ 真 ” 時(shí) 函 數(shù) 返 回 的 值 。 Value_if_false 是 logical_test 為 FALSE 時(shí) 返 回 的 值 。 也 就 是 邏 輯 表 達(dá)式 為 “ 假

26、 ” 時(shí) 函 數(shù) 返 回 的 值 。 61 如 果 logical_test 為 FALSE 而 value_if_false 被 省 略 （ 即 value_if_true 后 沒 有 逗 號(hào) ） ， 則 會(huì) 返回 邏 輯 值 FALSE。 （ value_if_true 不 能 被 省 略 ） 62 應(yīng) 用 案 例 （ 見 Excel） ： 簡 單 應(yīng) 用 。 =IF(B240,正 數(shù) ,負(fù) 數(shù) 或 零 “) 63 綜 合 應(yīng) 用 。 案 例 一 ： “ 工 資 樣 表 ” 備 注 列 ； 案 例 二 ： “ 工 資 樣 表 ” 序 號(hào) 列 ； 案 例 三 ： “ 保 健 藥 品 費(fèi) ”

27、的 模 擬 計(jì) 算 ； 案 例 四 ： If與 Vlookup函 數(shù) 綜 合 應(yīng) 用 。 見 Excel。 64 四 、 時(shí) 間 日 期 函 數(shù)（ 一 ） Excel處 理 日 期 和 時(shí) 間 的 方 式 在 Excel中 ， 日 期 只 是 一 個(gè) 數(shù) 字 ， 更 確 切地 說 ， 日 期 是 一 個(gè) 序 列 號(hào) 。 此 序 列 號(hào) 可以 表 示 自 從 1900年 1月 0日 以 來 的 所 有日 期 ， 1900年 1月 1日 是 序 列 號(hào) 為 1的 日期 。 序 列 號(hào) 為 2的 日 期 是 1900年 1月 2日 。 65 要 把 日 期 序 列 號(hào) 表 示 為 日 期 ， 必 須

28、 把 單元 格 格 式 化 成 日 期 格 式 。 Excel處 理 1900年 之 前 的 時(shí) 間 時(shí) ， 只 能將 其 時(shí) 間 處 理 成 文 本 格 式 ， 在 輸 入 此 類型 日 期 時(shí) Excel并 不 會(huì) 顯 示 錯(cuò) 誤 ， 它 只 是一 個(gè) 文 本 ， 而 同 樣 的 一 些 時(shí) 間 函 數(shù) 對 其也 不 起 任 何 作 用 。 66 關(guān) 于 工 作 中 經(jīng) 常 遇 到 的 “ 1905” 問 題 。 1905系 輸 入 數(shù) 據(jù) 時(shí) 不 規(guī) 范 的 結(jié) 果 ， 如1980年 1月 1日 ， 僅 輸 入 為 1980， 在 經(jīng)過 excel轉(zhuǎn) 為 時(shí) 間 日 期 型 數(shù) 據(jù) 時(shí)

29、， excel認(rèn) 為 1980為 時(shí) 間 序 數(shù) ， 所 以 顯 示 為1905年 6月 2日 ： 67 1980/365=5.42， 滿 5年 后 余 0.42年 ，即 滿 5 年 的 次 年 ，（ 1900+4+1=1905） ， 即 1905年 ；1980-（ 5*365+1） =154天 （ 因 1904年 為 閏 年 ， 因 此 本 處 計(jì) 算 時(shí) 在 5*365后+1天 ） ， 154/30=5.13， 即 滿 5個(gè) 月 后余 0.13個(gè) 月 ， 即 6月 ， 154-（ 31+28+31+30+31） =3， 3-1=2日 。 68 為 什 么 計(jì) 算 出 來 為 3后 還 要

30、減 1？ excel認(rèn) 為 1900年 為 閏 年 ， 這 是 excel的歷 史 bug之 一 ， 微 軟 知 悉 此 問 題 并 選 擇繼 續(xù) 假 定 1900年 為 閏 年 。http:/ 因 此 3-1=2， 即 第 2天 。 所 以 結(jié) 果 為 1905年 6月 2日 。 69 反 之 ， 在 excel中 輸 入 日 期 ， 1905年 6月2日 ， 并 更 改 數(shù) 據(jù) 類 型 為 數(shù) 值 型 ， 可 以顯 示 出 此 數(shù) 值 為 1980。 70 通 過 以 上 案 例 可 以 看 出 ： Excel內(nèi) 部 是 將 日 期 按 照 數(shù) 值 存 儲(chǔ) ， 只 是顯 示 的 時(shí) 候 按

31、 照 人 們 習(xí) 慣 的 如 期 格 式 顯示 而 已 ， 這 樣 做 的 目 的 的 便 于 日 日 期 間的 計(jì) 算 。 71 （ 二 ） 日 期 函 數(shù) Date 返 回 代 表 特 定 日 期 的 序 列 號(hào) 。 如 果 在 輸入 函 數(shù) 前 ， 單 元 格 的 格 式 為 “常 規(guī) ”， 則結(jié) 果 將 設(shè) 為 日 期 格 式 。 72 Datedif 以 年 或 月 或 日 為 單 位 返 回 兩 個(gè) 日 期 之 間的 差 73 Year 返 回 某 日 期 對 應(yīng) 的 年 份 。 返 回 值 為 1900 到 9999 之 間 的 整 數(shù) 。 74 Month 返 回 以 序 列

32、號(hào) 表 示 的 日 期 中 的 月 份 。 月份 是 介 于 1（ 一 月 ） 到 12（ 十 二 月 ）之 間 的 整 數(shù) 。 75 Day 返 回 以 序 列 號(hào) 表 示 的 某 日 期 的 天 數(shù) ， 用整 數(shù) 1 到 31 表 示 。 76 Today 返 回 當(dāng) 前 日 期 的 序 列 號(hào) 。 序 列 號(hào) 是 Excel 日 期 和 時(shí) 間 計(jì) 算 使 用 的 日 期 -時(shí)間 代 碼 。 如 果 在 輸 入 函 數(shù) 前 ， 單 元 格 的格 式 為 “常 規(guī) ”， 則 結(jié) 果 將 設(shè) 為 日 期 格 式 。無 參 數(shù) 。 77 Now 返 回 當(dāng) 前 日 期 和 時(shí) 間 所 對 應(yīng)

33、的 序 列 號(hào) 。如 果 在 輸 入 函 數(shù) 前 ， 單 元 格 的 格 式 為 “常規(guī) ”， 則 結(jié) 果 將 設(shè) 為 日 期 格 式 。 無 參 數(shù) 。 78 日 期 型 函 數(shù) 應(yīng) 用 ： 把 文 本 型 字 符 串 轉(zhuǎn) 化 成 日 期 型 數(shù) 據(jù) 如 ， A1單 元 格 內(nèi) 容 為 文 本 20050107，在 A2單 元 格 中 輸 入 =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)， 結(jié) 果 為 2005年 1月 7日 。 79 身 份 證 提 取 出 生 日 期 ： =IF(LEN(A2)=15,DATE(19&MID(A2,7,2),MID(A2

34、,9,2),MID(A2,11,2),IF(LEN(A2)=18,DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2),) 80 身 份 證 提 取 性 別 ： =IF(A2=,CHOOSE(MOD(IF(LEN(A2)=15,RIGHT(A2,1),IF(LEN(A2)=18,MID(A2,17,1),),2)+1,女 ,男 ) 81 身 份 證 號(hào) 碼 18位 轉(zhuǎn) 15位 =IF(LEN(J3)=15,J3,LEFT(REPLACE(J3,7,2,”),15) 82 計(jì) 算 兩 個(gè) 日 起 之 間 的 年 份 數(shù) 、 月 份 數(shù) 、日 期 數(shù) =DATEDI

35、F(AF11,AG11,Y) =year（ af11） -year（ AG11） =DATEDIF(AF11,AG11,M) =DATEDIF(AF11,AG11,D) 83 確 定 一 年 是 否 為 閏 年 ： =IF(MONTH(DATE(YEAR(A1),2,29)=2,閏 年 ,非 閏 年 ) 84 對 于 日 期 型 數(shù) 據(jù) 的 提 醒 ： 務(wù) 必 按 照 日 期 型 數(shù) 據(jù) 的 要 求 輸 入 數(shù) 據(jù) ，并 確 保 數(shù) 據(jù) 格 式 一 致 ！ 否 則 很 難 進(jìn) 行 計(jì)算 和 篩 選 查 找 等 操 作 。 85 第 三 部 分 多 條 件 求 和 與 多條 件 計(jì) 數(shù) 數(shù) 組

36、公 式 86 一 、 了 解 數(shù) 組 、 數(shù) 組 公 式二 、 數(shù) 組 的 特 征三 、 如 何 使 用 數(shù) 組 公 式四 、 數(shù) 組 公 式 常 見 應(yīng) 用多 條 件 求 和 ， 多 條 件 計(jì) 數(shù) 一 、 了 解 數(shù) 組 、 數(shù) 組 公 式 官 方 定 義 很 復(fù) 雜 ， 但 簡 單 說 ， 數(shù) 組 就 是一 組 數(shù) 。 87 在 Excel 中 ， 數(shù) 組 中 的 元 素 可 以 位 于 一行 （ 稱 為 一 維 水 平 數(shù) 組 ） 中 ， 也 可 位 于一 列 （ 稱 為 一 維 垂 直 數(shù) 組 ） 中 或 多 行 和多 列 （ 二 維 數(shù) 組 ） 中 。 無 法 在 Excel 中

37、創(chuàng) 建 三 維 數(shù) 組 或 三 維 數(shù)組 公 式 。 88 數(shù) 組 公 式 是 指 可 以 在 數(shù) 組 的 一 項(xiàng) 或 多 項(xiàng)上 執(zhí) 行 多 個(gè) 計(jì) 算 的 公 式 。 數(shù) 組 公 式 可 以返 回 多 個(gè) 結(jié) 果 ， 也 可 返 回 一 個(gè) 結(jié) 果 。 在 excel公 式 中 ， 整 個(gè) 公 式 被 包 含 的 公式 都 是 數(shù) 組 公 式 。 89 例 如 ， 可 以 將 數(shù) 組 公 式 放 入 單 元 格 區(qū) 域中 ， 并 使 用 數(shù) 組 公 式 計(jì) 算 列 或 行 的 小 計(jì) 。 也 可 以 將 數(shù) 組 公 式 放 入 單 個(gè) 單 元 格 中 ，然 后 計(jì) 算 單 個(gè) 量 。 位 于

38、 多 個(gè) 單 元 格 中 的數(shù) 組 公 式 稱 為 多 單 元 格 公 式 ， 位 于 單 個(gè)單 元 格 中 的 數(shù) 組 公 式 稱 為 單 個(gè) 單 元 格 公式 。 90 多 條 件 計(jì) 數(shù) 、 多 條 件 求 和 案 例 較 為 復(fù) 雜 ，見 Excel。 91 第 四 部 分 排 序 、 篩 選 、 條件 格 式 與 數(shù) 據(jù) 有 效 性 驗(yàn) 證 一 、 常 規(guī) 排 序 二 、 按 照 自 定 義 序 列 進(jìn) 行 排 序 三 、 篩 選 四 、 條 件 格 式 五 、 數(shù) 據(jù) 有 效 性 驗(yàn) 證 92 一 、 常 規(guī) 排 序 首 先 ， 選 定 需 要 排 序 的 數(shù) 據(jù) 區(qū) 域 93 O

39、ffice003： 數(shù) 據(jù) 菜 單 排 序 選 擇 按 照 哪 一 列 進(jìn) 行 排 序 ， 并 選 擇 升 序 、降 序 。 94 Office2007 開 始 （ 或 數(shù) 據(jù) ） 菜 單 排 序 依 次選 擇 需 要 關(guān) 鍵 字 ， 并 選 擇 升 序 、 降 序 95 二 、 按 照 自 定 義 序 列 進(jìn) 行排 序 按 照 自 定 義 序 列 進(jìn) 行 排 序 ， 即 按 照 指 定順 序 排 序 。 Exce排 序 功 能 默 認(rèn) 僅 能 對 數(shù) 字 按 照 大 小排 列 或 者 對 漢 字 文 本 按 照 音 序 進(jìn) 行 排 列 ，不 能 直 接 對 漢 字 按 照 指 定 的 順 序

40、 （ 如 ：職 員 、 專 技 、 工 勤 ） 排 列 。 96 Office2003 第 一 步 ： 創(chuàng) 建 自 定 義 序 列 定 義 自 定 義 序 列 ， 也 就 是 所 告 訴 excel你需 要 的 順 序 ， 比 如 職 員 、 專 技 、 工 勤 。 97 方 法 ： 在 Excel中 輸 入 需 要 自 定 義 的 序 列 職員 、 專 技 、 工 勤 （ 位 置 不 限 ， 但 數(shù) 據(jù) 需連 續(xù) ） 如 下 圖 ： 98 99 工 具 選 項(xiàng) 自 定 義 序 列 ， 選 擇 數(shù)據(jù) （ 或 者 輸 入 序 列 ， 數(shù) 據(jù) 間 使 用 半 角 的逗 號(hào) 隔 開 ） 導(dǎo) 入 。

41、此 時(shí) ， 右 側(cè) 自 定義 序 列 窗 口 最 底 下 一 行 即 顯 示 剛 剛 導(dǎo) 入的 自 定 義 序 列 。 如 下 圖 ： 100 101 102 第 二 步 ： 按 照 自 定 義 序 列 排 序 選 擇 需 要 排 序 的 數(shù) 據(jù) 區(qū) 域 。 點(diǎn) 擊 數(shù) 據(jù) 菜 單 排 序 菜 單 ， 如 下 圖 ： 103 104 在 彈 出 來 的 排 序 窗 口 中 首 先 選 擇 主 要 關(guān)鍵 字 （ 即 按 照 哪 一 列 進(jìn) 行 排 序 ） 。 本 例 中 選 擇 “ 崗 位 類 別 ” 。 點(diǎn) 擊 右 下 角 的 “ 選 項(xiàng) ” 按 鈕 ， 如 下 圖 ： 105 106 在 彈

42、 出 來 的 排 序 選 項(xiàng) 窗 口 中 點(diǎn) 擊 下 拉 菜單 。 選 擇 剛 剛 導(dǎo) 入 的 “ 職 員 ， 專 技 ， 工 勤 ”選 項(xiàng) 。 點(diǎn) 擊 “ 確 定 ” 。 107 108 回 到 “ 排 序 ” 串 口 ， 點(diǎn) 擊 確 定 。 排 序 完 成 。 結(jié) 果 如 下 圖 。 109 110 office20007 第 一 步 ： 設(shè) 置 自 定 義 序 列 單 擊 office按 鈕 ， 再 單 擊 “Excel選 項(xiàng) ”，在 “常 用 ”選 卡 里 ， 單 擊 “編 輯 自 定 義 列 表 ”按 鈕 ， 來 添 加 自 定 義 序 列 。 111 112 113 后 續(xù) 步 驟

43、 同 office2003。 自 定 義 序 列 ， 除 了 可 以 以 這 個(gè) 序 列 為 基礎(chǔ) 排 序 數(shù) 據(jù) 外 ， 可 以 方 便 用 來 填 充 之 外 。 114 三 、 篩 選 簡 單 篩 選 ： 略 115 按 照 指 定 數(shù) 據(jù) 進(jìn) 行 篩 選 案 例 ： 給 定 10人 身 份 證 號(hào) 碼 ， 請 在 工 資樣 表 中 篩 選 出 該 10人 的 工 資 記 錄 。 本 案 例 ， 無 法 通 過 簡 單 篩 選 完 成 。 116 操 作 ： 步 驟 一 ： 將 要 篩 選 的 條 件 數(shù) 據(jù) （ 本 例 中即 給 定 的 身 份 證 號(hào) 碼 ） 列 于 表 格 旁 邊

44、的單 元 格 中 。 注 意 ： 條 件 數(shù) 據(jù) 應(yīng) 增 加 列 標(biāo) 簽 （ 表 頭 ） ，本 例 中 為 “ 身 份 證 號(hào) 碼 ” 。 此 列 標(biāo) 簽 應(yīng)與 要 執(zhí) 行 篩 選 的 表 格 對 應(yīng) 列 的 表 頭 一 致 。 117 步 驟 二 ： 選 中 需 要 篩 選 的 原 始 表 格 。 注 意 ， 務(wù) 必 選 中 表 頭 。 Office2003: 數(shù) 據(jù) -篩 選 -高 級(jí) 篩 選 Office2007： 數(shù) 據(jù) -篩 選 -高 級(jí) 直 接 選 擇 條 件 區(qū) 域 ， 確 定 。 詳 見 下 圖 。 118 119 120 121 122 如 需 增 加 篩 選 條 件 ， 如

45、 本 例 中 要 求 篩 選身 份 證 號(hào) 碼 為 以 上 10人 且 工 資 序 列 為“ 專 業(yè) 技 術(shù) ” ， 崗 位 工 資 900的 人 員 信息 ， 在 條 件 區(qū) 域 增 加 條 件 數(shù) 據(jù) 即 可 。 詳 見 案 例 。 123 四 、 條 件 格 式 定 義 ： 對 符 合 某 一 種 或 多 種 種 條 件 的 數(shù)據(jù) 設(shè) 定 特 殊 的 顯 示 格 式 ， 比 如 對 月 績 效工 資 在 100元 以 下 的 員 工 ， 薪 酬 數(shù) 據(jù) 自動(dòng) 顯 示 為 紅 色 加 粗 。 示 例 見 Excel。 操 作 方 法 ： 略 。 124 五 、 數(shù) 據(jù) 有 效 性 驗(yàn) 證

46、定 義 ： 對 特 定 單 元 格 或 數(shù) 據(jù) 區(qū) 域 限 定 用戶 輸 入 的 數(shù) 據(jù) 類 型 、 范 圍 、 長 度 等 。 比如 手 機(jī) 號(hào) 碼 一 項(xiàng) ， 限 制 用 戶 輸 入 長 度 為8為 的 數(shù) 字 。 對 出 生 日 期 單 元 格 限 制 輸入 的 數(shù) 據(jù) 類 型 為 日 期 型 ， 且 數(shù) 據(jù) 范 圍 為從 1910年 1月 1日 -1996年 12月 31日 。 示 例 見 Excel。 操 作 方 法 ： 略 。 125 第 五 部 分 圖 表 Excel圖 表 可 以 將 數(shù) 據(jù) 圖 形 化 ， 更 直 觀 的顯 示 數(shù) 據(jù) ， 使 數(shù) 據(jù) 的 比 較 或 趨 勢

47、變 得 一目 了 然 ， 從 而 更 容 易 表 達(dá) 我 們 的 觀 點(diǎn) 。 126 圖 表 在 數(shù) 據(jù) 統(tǒng) 計(jì) 中 用 途 很 大 ， 具 體 到 我們 的 工 作 中 的 應(yīng) 用 ： 薪 酬 數(shù) 據(jù) 統(tǒng) 計(jì) 分 析 ； 人 員 結(jié) 構(gòu) 統(tǒng) 計(jì) 分 析 ； 教 育 培 訓(xùn) 的 課 時(shí) 、 專 業(yè) 、 參 加 人 員 等 數(shù)據(jù) 統(tǒng) 計(jì) 分 析 。 127 圖 表 是 用 來 表 現(xiàn) 數(shù) 據(jù) 間 的 某 種 相 對 關(guān) 系 。 在 常 規(guī) 狀 態(tài) 下 我 們 一 般 運(yùn) 用 柱 形 圖 比 較數(shù) 據(jù) 間 的 多 少 關(guān) 系 ； 用 折 線 圖 反 映 數(shù) 據(jù) 間 的 趨 勢 關(guān) 系 ； 用 餅

48、圖 表 現(xiàn) 數(shù) 據(jù) 間 的 比 例 分 配 關(guān) 系 。 128 簡 單 案 例 ： 通 過 簡 單 案 例 的 學(xué) 習(xí) ， 掌 握 Excel圖 表 的基 本 功 能 ， 比 如 ， 圖 表 類 型 ， 標(biāo) 題 、 圖例 、 顏 色 、 數(shù) 據(jù) 的 修 改 以 及 布 局 等 。 見 Excel。 129 較 為 復(fù) 雜 的 案 例 ： 雙 坐 標(biāo) 軸 圖 表 。 案 例 ： 依 據(jù) 下 表 繪 制 坐 標(biāo) 軸 分 別 為 廣 告收 入 （ 柱 形 圖 ） 和 增 幅 （ 折 線 圖 ） 的 圖表 。 130年 度 廣 告 收 入 增 幅2005年 469 -2006年 527 12.37%2

49、007年 601 14.04%2008年 702 16.81%2009年 782 11.40% 操 作 步 驟 ： 1、 選 中 數(shù) 據(jù) （ 兩 列 坐 標(biāo) 軸 上 的 數(shù) 據(jù) 全部 選 中 ） 。 2、 點(diǎn) 擊 插 入 、 圖 表 、 選 擇 主 坐 標(biāo) 軸 上要 使 用 的 圖 表 類 型 ， （ 本 例 主 坐 標(biāo) 軸 確定 為 廣 告 收 入 ） 點(diǎn) 擊 確 定 。 131 3、 點(diǎn) 擊 已 經(jīng) 出 現(xiàn) 的 圖 表 ， 點(diǎn) 擊 菜 單“ 圖 表 工 具 ” -布 局 ， 在 下 拉 猜 到 中 選擇 次 要 坐 標(biāo) 軸 的 系 列 （ 本 例 中 為 增 幅 ） ，此 時(shí) 次 要 坐

50、標(biāo) 軸 所 要 表 示 的 數(shù) 據(jù) 被 選 中 。 本 例 中 次 要 坐 標(biāo) 軸 所 代 表 的 數(shù) 字 相 對 于主 要 坐 標(biāo) 軸 太 小 ， 很 難 手 動(dòng) 選 擇 ， 如 實(shí)際 操 作 中 數(shù) 據(jù) 能 夠 手 動(dòng) 選 擇 ， 可 跳 過 這一 步 。 132 4、 在 次 要 坐 標(biāo) 軸 的 柱 狀 圖 上 點(diǎn) 右 鍵 ，設(shè) 置 數(shù) 據(jù) 系 列 格 式 ， 選 擇 次 要 坐 標(biāo) 軸 。 5、 再 次 在 次 要 坐 標(biāo) 軸 的 數(shù) 據(jù) 圖 表 區(qū) 域上 點(diǎn) 右 鍵 ， 更 改 圖 表 數(shù) 據(jù) 系 列 類 型 。 6、 選 擇 需 要 的 圖 表 類 型 ， 本 處 按 要 求選 擇

51、 折 線 圖 。 133 注 意 ： 雙 坐 標(biāo) 軸 的 圖 表 不 能 設(shè) 置 為 三 維模 式 。 134 結(jié) 語經(jīng) 驗(yàn) 總 結(jié) ：1、 表 格 和 數(shù) 據(jù) 應(yīng) 盡 可 能 的 規(guī) 范 。 比 如 同 列 數(shù) 據(jù) 格 式 一 致 。 整 個(gè) 表 格 中 盡 量 不 合 并 單 元 格 。 135 2、 善 于 發(fā) 現(xiàn) 數(shù) 據(jù) 的 規(guī) 律 性 。 Excel公 式 和 程 序 能 夠 大 量 處 理 的 數(shù) 據(jù) ，但 前 提 必 須 是 這 些 數(shù) 據(jù) 或 數(shù) 據(jù) 之 間 具 有某 種 規(guī) 律 性 。 計(jì) 算 機(jī) 幾 乎 無 法 處 理 沒 有 規(guī) 律 性 的 數(shù) 據(jù) 。規(guī) 律 性 越 強(qiáng)

52、公 式 越 簡 單 ， 規(guī) 律 性 越 弱 ，公 式 就 會(huì) 越 復(fù) 雜 ， 直 至 沒 有 必 要 用 公 式 ，或 者 不 能 處 理 。 136 3、 Excel不 是 萬 能 的 ， 要 Excel做 的 事情 你 自 己 必 須 會(huì) 做 ， 至 少 要 在 邏 輯 上 會(huì)做 ， 否 則 ， 不 要 交 給 Excel 去 做 。 137 4、 學(xué) 會(huì) 使 用 各 種 幫 助 系 統(tǒng)（ 1） 隨 時(shí) 使 用 Excel幫 助 菜 單 （ F1） ， 并學(xué) 會(huì) 復(fù) 制 其 中 的 案 例 以 加 深 印 象 。 什 么 時(shí) 候 使 用 excel幫 助 信 息 ？ 簡 單 問 題 ， 單 一 函 數(shù) 的 使 用 ， 具 體 函 數(shù)參 數(shù) 要 求 等 。 138 （ 2） 學(xué) 會(huì) 使 用 網(wǎng) 絡(luò) 。 網(wǎng) 絡(luò) 上 有 大 量 的 資 源 和 生 動(dòng) 的 案 例 。 什 么 時(shí) 候 通 過 網(wǎng) 絡(luò) 尋 找 有 關(guān) excel的 幫助 信 息 ？ 復(fù) 雜 問 題 ， 多 函 數(shù) 的 綜 合 使 用 等 。 139 謝 謝 ！ 140