1 EXCEL培 訓 EXCEL是 日 常 工 作 中 應(yīng) 用 最 普遍 的 工 具 。 對 于 審 計 人 員 來 說 ，不 但 要 掌 握 基 本 的 操 作 ， 還 要 學會 一 些 操 作 技 巧 以 及 數(shù) 據(jù) 分 析 、函 數(shù) 、 圖 表 等 部 分 高 級 功 能 ， 提升 EXCEL操 作 水 平 ， 提 高 工 作效 率 。引言 3EXCEL 功 能 分 類 2函 數(shù)1操 作 3分 析5VBA4圖 表 4基 礎(chǔ) 操 作 is the 1rd 5 頁 面 設(shè) 置 和 打 印 設(shè) 置 頁 面 位 置 打 印 頂 端 標 題 行 設(shè) 置 頁 眉 頁 腳 設(shè) 置 分 頁 符 使 用 “ 照 相 機 ” 6 2 設(shè) 置 頁 邊 距 設(shè) 置 居 中 方 式 7 3 設(shè) 置 頁 眉 /頁 腳頁 眉 /頁 腳 設(shè) 置 對 話 框 8圖 4-136 自 定 義 頁 眉 對 話 框 頁 眉 /頁 腳 設(shè) 置 對 話 框 9 4 工 作 表 設(shè) 置 頂 端 標 題 行 10 worldsis the trailing only常 用 快 捷 鍵CTRL+C： 復 制CTRL+Z： 剪 切CTRL+V： 粘 貼CTRL+Y(F4)： 重 復 上 一 步 操 作在 行 或 列 間 雙 擊 鼠 標 左 鍵 ， 行 或 列 自 動 寬 度 自 動 適 應(yīng)F8SHIFT+鼠 標 左 鍵 ： 移 動 列 或 區(qū) 塊輸 入 身 份 證 ： 在 單 元 格 中 ， 先 打 個 逗 號 ， 然 后 輸 入 數(shù) 字 11 worldsis the trailing only常 用 快 捷 鍵插 入 空 白 單 元 格插 入 多 行 多 列轉(zhuǎn) 換 行 列 次 序反 轉(zhuǎn) 表 格 ： 復 制 表 格 右 鍵 選 擇 性 粘 貼 選 擇 “ 轉(zhuǎn) 置 ” 12 excel中 如 何 設(shè) 單 元 格 為 下 拉 選 擇 框 ， 從 而 使 其 他 的 單 元 格 的 數(shù) 值 與本 單 元 格 的 選 擇 相 對 應(yīng) 的 變 化 ？ 點 菜 單 數(shù) 據(jù) -有 效 性 -設(shè) 置 選 項 卡 -允 許 下 拉 選 單 中 ， 選 擇 序 列 -在下 面 的 來 源 中 輸 入 所 有 可 選 的 內(nèi) 容 用 逗 號 隔 開 ， 如 1， 2，3， 或 者 點 右 邊 的 選 擇 按 鈕 到 文 件 里 面 選 擇 一 個 區(qū) 域 ， 如 果希 望 輸 入 的 數(shù) 據(jù) 不 受 這 個 條 件 的 限 制 ， 比 如 我 想 輸 入4而 軟 件 不 報 錯 的 話 ， 就 切 換 到 出 錯 警 告 選 項 卡 ，去 掉 輸 入 無 效 數(shù) 據(jù) 時 顯 示 出 錯 警 告 前 面 的 鉤 ， 確 定 之 后 ， 看 一 看 是不 是 有 了 下 拉 選 擇 框 。 其 它 的 單 元 格 數(shù) 值 需 要 隨 之 變 化 的話 ， 需 要 設(shè) 置 公 式 ， 建 議 使 用 IF公 式 的 嵌 套 ， 這 個 公 式 比 較 簡 單 ，容 易 掌 握 。 13 隱 藏 工 作 表 的 方 法 如 下 ： （ 1） 選 擇 要 隱 藏 的 工 作 表 。 （ 2） 單 擊 “ 格 式 ” 菜 單 中 “ 工 作 表 ” 命 令 ， 在 彈 出 的 級 聯(lián) 菜 單 中 單擊 “ 隱 藏 ” 命 令 。 取 消 隱 藏 工 作 表 的 方 法 如 下 ： （ 1） 單 擊 “ 格 式 ” 菜 單 中 的 “ 工 作 表 ” 命 令 ， 在 彈 出 的 子 菜 單 中 單擊 “ 取 消 隱 藏 ” 命 令 。 （ 2） 在 彈 出 的 “ 取 消 隱 藏 ” 對 話 框 中 選 擇 要 取 消 隱 藏 的 工 作 表 。隱 藏 /取 消 工 作 表 的 方 法 14 1 工 作 表 窗 口 的 拆 分 拆 分 可 分 為 三 種 ： 水 平 拆 分 、 垂 直 拆 分 、 水 平 和 垂 直 同 時 拆 分 。 2 工 作 表 窗 口 的 凍 結(jié) 窗 口 凍 結(jié) 操 作 方 法 ： （ 1） 選 擇 凍 結(jié) 位 置 （ 2） 選 擇 “ 窗 口 ” 菜 單 中 的 “ 凍 結(jié) 窗 口 ” 命 令 。工 作 表 窗 口 拆 分 15圖 4-36 工 作 表 窗 口 拆 分 示 例 16圖 4-37 窗 口 凍 結(jié) 示 例 17 （ 1） 格 式 復 制 方 法 一 ： 單 擊 “ 編 輯 ” 菜 單 中 的 “ 復 制 ” 命 令 。 選 擇 目 標區(qū) 域 ， 單 擊 “ 編 輯 ” 菜 單 中 的 “ 選 擇 性 粘 貼 ” 命 令 ， 在 打 開 的選 擇 性 粘 貼 對 話 框 中 選 中 “ 格 式 ” 按 鈕 。 方 法 二 ： 選 “ 格 式 刷 ” 按 鈕 。 （ 2） 格 式 刪 除 選 擇 要 刪 除 格 式 的 區(qū) 域 。 選 擇 “ 編 輯 ” 菜 單 中 的 “ 清 除 ”命 令 ， 在 彈 出 的 子 菜 單 中 選 擇 “ 格 式 ” 子 命 令 。 工 作 表 格 式 復 制 /刪 除 largest economyis the 2rd 函 數(shù) 19 Excel中 的 公 式 及 函 數(shù)公 式 是 Excel中 數(shù) 值 關(guān) 系 的 具 體 體 現(xiàn) ， 構(gòu) 成 了Excel的 核 心 ， 正 確 地 使 用 任 何 一 個 公 式 必 須 明 確地 把 握 下 面 三 個 因 素 ：1. 公 式 中 的 參 數(shù) ： 公 式 中 需 要 哪 些 參 數(shù) ， 自 變 量 來 源于 何 外 ， 公 式 如 何 引 用 其 他 區(qū) 域 或 單 元 格2. 公 式 的 運 算 ： 公 式 的 運 算 實 質(zhì) 性 機 理 是 什 么 ， 有 什么 約 束 條 件3. 公 式 運 算 的 結(jié) 果 表 現(xiàn) ： 公 式 運 算 的 結(jié) 果 表 現(xiàn) 為 什 么， 其 格 式 如 何 20 Excel中 公 式 的 輸 入Excel中 的 公 式 通 常 都 是 以 “ =” 開 頭 的 一個 運 算 式 或 函 數(shù) 式 ， 可 以 將 “ =” 引 導 符 看成 是 Excel公 式 的 標 志 。 例 如 ： G5單 元 中 有 如 下 內(nèi) 容 =G1+G3+G4*2 又 如 ： N9單 元 中 有 如 下 內(nèi) 容 =SUM（ A9:M9） 這 些 都 是 Excel中 最 簡 單 的 公 式 示 例 21 Excel公 式 中 的 參 數(shù)Excel中 公 式 中 的 參 數(shù) 有 下 列 三 種 情 況 ：1. 常 數(shù) ： 公 式 中 使 用 某 些 常 數(shù) 來 參 與 運 算 ， 或 由 常 數(shù)指 明 公 式 的 運 算 方 式2. 系 統(tǒng) 變 量 ： 公 式 的 某 些 變 量 來 源 于 計 算 機 系 統(tǒng) 的 自身 參 數(shù) ， 如 機 器 時 間 等3. 地 址 引 用 ： 對 其 他 工 作 溥 、 工 作 表 、 區(qū) 域 或 單 元 格的 內(nèi) 容 的 引 用 ， 這 種 引 用 在 是 根 據(jù) 被 引 用 對 象 的 位置 進 行 的 ， 所 以 Excel中 又 稱 之 為 地 址 引 用 22 Excel公 式 中 對 地 址 的 引 用 方 式在 Excel中 公 式 對 其 他 地 址 的 引 用 分 為 三 種 情 況 ：1. 相 對 引 用 ： 即 對 另 一 單 元 的 地 址 引 用 是 按 與 當 前 單 元 的特 定 距 來 確 定 的 ， 這 種 引 用 在 該 公 式 被 復 制 時 會 自 動 調(diào)整2. 絕 對 引 用 ： 即 對 被 引 用 單 元 采 用 絕 對 的 行 列 號 來 引 用 ，公 式 不 會 自 動 調(diào) 整3. 混 合 引 用 ： 行 或 列 之 一 采 用 絕 對 引 用如 果 有 區(qū) 域 或 單 元 格 命 名 存 在 ， 還 可 以 按 名 引 用 23 Excel公 式 相 對 與 絕 對 地 址 操 作在 Excel中 輸 入 公 式 時 ， 相 對 地 址 與 絕 對 地 址 可以 進 行 轉(zhuǎn) 換 操 作 ： 凡 行 或 列 地 址 前 出 現(xiàn) 有 “ $” 標 志 的 ， 該 行 或 列 的地 址 即 為 絕 對 地 址 引 用 ， 否 則 為 相 對 地 址 引 用 在 地 址 輸 入 時 ， 相 對 地 址 和 絕 對 地 址 間 可 以 用 F4鍵 進 行 循 環(huán) 轉(zhuǎn) 換G3 F4 $G$3 F4 G$3 F4 $G3 24 Excel公 式 中 對 地 址 的 操 作 方 式在 Excel中 輸 入 公 式 需 要 進 行 地 址 引 用 時可 以 采 用 的 操 作 方 式 為 ：1. 直 接 輸 入 式 ： 直 接 輸 入 被 引 用 對 象 的 行 列 地 址2. 交 互 指 定 式 ： 即 用 鼠 標 或 光 標 鍵 直 接 交 互 式 指 出被 引 用 對 象 的 地 址3. 名 稱 粘 貼 式 ： 如 果 引 用 的 對 象 已 有 命 名 ， 可 以 采用 F3鍵 來 粘 貼 該 對 象 的 名 稱 于 公 式 之 中 25 MS Excel公 式 的 自 動 調(diào) 整在 Excel中 公 式 的 相 對 此 用 部 分 會 ， 在 該 公 式 自身 的 位 置 發(fā) 生 變 化 ， 或 在 其 引 用 對 象 的 位 置 發(fā) 生變 化 時 ， 會 自 動 進 行 相 應(yīng) 的 調(diào) 整 ， 但 應(yīng) 注 意 一 些細 節(jié) ： 當 公 式 被 復 制 到 它 處 時 ， 會 自 動 調(diào) 整 當 公 式 被 移 動 到 它 處 時 ， 不 會 自 動 調(diào) 整 如 被 引 用 對 象 為 一 區(qū) 域 ， 則 在 區(qū) 域 中 插 入 或 刪 除 行列 時 ， 就 注 意 引 用 這 一 對 象 的 公 式 調(diào) 整 情 況 26 Excel公 式 中 的 函 數(shù)函 數(shù) 是 Excel中 完 成 復 雜 運 行 的 一 種 程 序 片 斷 ，它 通 常 是 Excel自 身 內(nèi) 含 的 ， 必 須 時 也 可 以 由 用 戶通 過 VBA擴 充 。由 于 函 數(shù) 是 Excel復 雜 運 行 的 核 心 ， 而 電 子 表 格又 是 以 運 算 作 為 主 要 功 能 的 ， 所 以 一 個 電 子 表 格軟 件 ， 其 內(nèi) 含 的 函 數(shù) 數(shù) 量 基 本 上 可 以 直 接 表 征 這個 軟 件 的 功 能 廣 度 和 深 度 。 Excel內(nèi) 部 蘊 含 有 豐 富的 積 各 類 函 數(shù) 。 27 1 使 用 公 式公 式 中 的 運 算 符 有 以 下 四 類 ： （ 1） 算 術(shù) 運 算 符 （ 2） 比 較 運 算 符 （ 3） 文 本 運 算 符 （ &） （ 4） 引 用 運 算 符 （ :） 函 數(shù) 28 輸 入 公 式 29 拖 拽 鼠 標 復 制 公 式 30 公 式 和 函 數(shù) /使 用 函 數(shù)Excel有 豐 富 的 內(nèi) 部 函 數(shù) ， 其 類 型 包 括 ： 財 務(wù) 函 數(shù) 、 日 期 與 時 間 函 數(shù) 、 數(shù) 學 與 三 角 函 數(shù) 、 統(tǒng)計 函 數(shù) 、 查 找 與 引 用 函 數(shù) 、 數(shù) 據(jù) 庫 函 數(shù) 、 文 本 函 數(shù) 、 邏 輯函 數(shù) 、 信 息 函 數(shù)（ 1） 函 數(shù) 的 輸 入（ 2） 自 動 求 和（ 3） 自 動 計 算（ 4） 函 數(shù) 復 制 31 Excel常 用 函 數(shù) 簡 介函數(shù)名功能說明基本語法SUM 求 和 SUM(數(shù) 1,數(shù) 2， )AVERAGE 求 均 值 AVERAGE(數(shù) 1,數(shù) 2， )IF 條 件 操 作 IF(條 件 表 達 式 ,為 真 時 取 值 ,為 假 時 取 值 ) COUNT 數(shù) 值 單 元 計 數(shù) COUNT(數(shù) 1,數(shù) 2， )MAX 求 最 大 值 MAX(數(shù) 1,數(shù) 2， )SIN 正 弦 函 數(shù) SIN(數(shù) )SUMIF 條 件 求 和 SUM(比 較 區(qū) 域 ， 條 件 表 達 式 ， 求 和 區(qū) 域 ）PMT 分 期 償 還 額 PMT(利 率 ， 總 額 ， 現(xiàn) 值 ， 未 來 值 ， 期 初 /期 末 )STDEV 樣 本 標 準 差 STDEV(數(shù) 1,數(shù) 2， ) 32 （ 1） 函 數(shù) 的 輸 入 函 數(shù) 的 輸 入 有 兩 種 方 法 ： 一 為 直 接 輸 入 ， 一 為 粘 貼 函 數(shù) 法 （ 即 使 用函 數(shù) 向 導 ） 。 圖 4-16 粘 貼 函 數(shù) 對 話 框輸 入 公 式 33 自 動 求 和自 動 求 和 34 選 擇 的 區(qū) 域自 動 計 算 結(jié) 果 顯 示 區(qū) 35 1 相 對 引 用2 絕 對 引 用3 三 維 地 址 引 用5 Vlookup的 使 用單 元 格 引 用 36largest economy 3rd 分 析 37 篩 選 數(shù) 據(jù) 表1 自 動 篩 選2 自 定 義 篩 選3 高 級 篩 選 38 下 拉 箭 頭 按 扭 的 選 擇 39 自 動 篩 選 之 后 的 清 單 40 多 條 件 篩 選 的 篩 選 結(jié) 果 41 圖4-81 設(shè)置篩選條件 篩選結(jié)果 42 2 自 定 義 篩 選 “自 定 義 自 動 篩 選 方 式 ” 對 話 框 43篩 選 結(jié) 果 44 高 級 篩 選 如 果 用 戶 要 使 用 高 級 篩 選 ， 一 定 要 先 建 立 一 個 條 件 區(qū) 域。 條 件 區(qū) 域 用 來 指 定 篩 選 的 數(shù) 據(jù) 必 須 滿 足 的 條 件 。 空 二 行 條 件 區(qū)域 圖 4-85 條 件 區(qū) 域 45高 級 篩 選 對 話 框 46篩 選 結(jié) 果 顯 示 數(shù) 據(jù) 表 格 47復 制 后 的 篩 選 結(jié) 果 復 制 的 篩選 結(jié) 果 48 數(shù) 據(jù) 排 序 1 默 認 排 序2 按 一 列 排 序3 按 多 列 排 序 49 默 認 排 序Excel在 默 認 排 序 時 是 根 據(jù) 單 元 格 中 的 數(shù) 據(jù) 進 行 排序 的 。 在 按 升 序 排 序 時 ， Excel使 用 如 下 順 序 ： （ 1） 數(shù) 值 從 最 小 的 負 數(shù) 到 最 大 的 正 數(shù) 排 序 。 （ 2） 文 本 和 數(shù) 字 的 文 本 按 從 09， az， AZ的 順 序 排 列 。 （ 3） 邏 輯 值 False 排 在 True 之 前 。 （ 4） 所 有 錯 誤 值 的 優(yōu) 先 級 相 同 。 （ 5） 空 格 排 在 最 后 。 50 按 一 列 排 序設(shè)置排序方式 主關(guān)鍵字排列順序 51 按 “ 筆 試 ” 字 段 降 序 排 列 之 后 的 結(jié) 果 52以 機 試 降 序 排 列 的 結(jié) 果 53 3 按 多 列 排 序設(shè) 置 多 列 排 序 對 話 框 以 筆 試 為 主 要 關(guān) 鍵 字 ， 機 試 為次 要 關(guān) 鍵 字 的 排 序 結(jié) 果 54 分 類 匯 總 1 簡 單 的 分 類 匯 總2 創(chuàng) 建 多 級 分 類 匯 總 3 分 級 顯 示 數(shù) 據(jù) 4 清 除 分 類 匯 總 55 1 簡 單 的 分 類 匯 總 分 類 匯 總 的 結(jié) 果設(shè) 置 分 類 匯 總 對 話 框 56 創(chuàng) 建 多 級 分 類 匯 總 設(shè) 置 二 級 分 類 匯 總 的 結(jié) 果 57 分 級 顯 示 數(shù) 據(jù) 下 面 介 紹 分 級 視 圖 中 的 各 個 按 鈕 的 功 能 ： 一 級 數(shù) 據(jù) 按 鈕 ： 只 顯 示 數(shù) 據(jù) 表 格 中 的 列 標 題 和 匯 總 結(jié)果 ， 該 級 為 最 高 級 。 二 級 數(shù) 據(jù) 按 鈕 ： 顯 示 分 類 匯 總 結(jié) 果 即 二 級 數(shù) 據(jù) 。 三 級 數(shù) 據(jù) 按 鈕 ： 顯 示 所 有 的 詳 細 數(shù) 據(jù) 即 三 級 數(shù) 據(jù) 。 分 級 顯 示 按 鈕 ： 表 示 高 一 級 向 低 一 級 展 開 顯 示 。 分 級 顯 示 按 鈕 ： 表 示 低 一 級 折 疊 為 高 一 級 數(shù) 據(jù) 顯 示 。 58 顯示一級數(shù)據(jù)顯示前兩級數(shù)據(jù) 59 清 除 分 類 匯 總 顯 示 明 細 數(shù) 據(jù)