【正文】 《 Excel 在財務(wù)管理中的應(yīng)用 》 教案 2 項目一 Excel 基礎(chǔ) 【項目要求】 (一 ) 了解 Excel 的基本功能 (二 ) 掌握各種單元格的編輯操作 (三 ) 掌握工作表的各種操作 (四 ) 掌握公式的概念和公式的基本操作 (五 ) 掌握函數(shù)的概念，能夠運用函數(shù)對工作表進行計算和分析 【項目內(nèi)容】 (一 )Excel的優(yōu)勢和功能 (二 )使用“自定義”選項對話框 (三 )移動快速訪問工具欄 (四 )添加和刪除快速訪問工具欄中的命令 (五 )設(shè)置屏幕顯示方式 (六 )簡單窗口設(shè)置 (七 )認識工作簿、工作表和單元格 (八 )公式 【相關(guān)知識點析】 ① 主要概念 工作簿 —— Microsoft Office Excel 2020 工作簿是包含一個或多個工作表的文件，可以用其中的工作表來組織各種相關(guān)信息。一個工作簿至少必須有一個工作表，最多可以有 255 個工作表。工作簿中的工作表越多，文檔容量就越大，運行起來相對較慢。 工作表 —— 工作表在 Excel中用于存儲和處理數(shù)據(jù)的主要文檔，也稱為電子表格。工作表是由單元格組合而成的一個平面整體，是一個平面二維表格，而且工作表總是存儲在工作簿中。Excel 2020 的工作表在行和列的數(shù)目上都做了很大的擴充，共有 1 048 576 行、 16 384列。默認情況下，工作表是不能完全顯示全部行和列的，用戶若 需要選擇，可以拖動 Excel窗口右側(cè)和下方的滾動條進行。 單元格 —— 在工作表中，行與列交叉形成的矩形區(qū)域，稱為單元格。單元格是工作表輸入、編輯、運輸?shù)幕締挝?，單元格中可以存放文字、?shù)字和公式等信息。 單元格引用 —— 在 Excel 2020 中，對于工作表的一系列的操作都是以單元格為最小單元進行的。而針對于單元格的相關(guān)操作，則常常借助于單元格的地址，對其進行引用，來達到操 3 作的目的。 如，兩個單元格內(nèi)容相加： A1+B3 相對地址 —— 以列標(biāo)和行號組成，如 A1， B2， C3 等。在進行公式復(fù)制等操作時，若引用公式的單元 格地址發(fā)生變動，公式中的相對地址會隨之變動。 絕對地址 —— 以列標(biāo)和行號前加上符號“ $”構(gòu)成，如 $A$1。在進行公式復(fù)制等操作時，當(dāng)引用公式的單元格地址發(fā)生變動時，公式中的絕對地址保持不變。 Excel 函數(shù) —— Excel 函數(shù)是 Excel 內(nèi)部預(yù)先定義的特殊公式，可以執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)。函數(shù)最終返回結(jié)果為值。 函數(shù)由函數(shù)名稱和參數(shù)構(gòu)成。 函數(shù)名稱決定了函數(shù)的功能和用途。函數(shù)參數(shù)規(guī)定了函數(shù)的運算對象、順序或結(jié)構(gòu)等。參數(shù)可以是一個或多個，多個參數(shù)之間以逗號分隔。參數(shù)可以是常量、邏輯值、數(shù)組、單元格引用等，也可以 是嵌套使用函數(shù)。參數(shù)的類型和位置必須滿足函數(shù)的語法要求，否則將返回錯誤信息。 ② 知識拓展 拓展 1：怎樣在保存 Excel 工作簿時自動刪除個人信息 有時創(chuàng)建 Excel工作簿或 Word文檔后不希望同時保存作者信息、文檔屬性等附加信息。在 Excel 2020中，依次單擊 “ 文件（或 Office按鈕） → 選項（或 Excel選項） → 信任中心→ 信任中心設(shè)置 ” ，在 “ 文檔待定設(shè)置 ” 下也可看到該選項，但默認該選項是灰色的。這是由于該選項在 2020版的 Excel、 PowerPoint和 Word中被禁用。 要激活該選項，讓保存工作簿時自動刪除個人信息，可以在上圖中單擊 “ 文檔檢查器 ” 4 按鈕，彈出 “ 文檔檢查器 ” 對話框，然后單擊 “ 檢查文檔 ” 按鈕， Excel會在對話框中顯示檢查結(jié)果，然后單擊 “ 文檔屬性和個人信息 ” 項中的 “ 全部刪除 ” 按鈕刪除個人信息。 也可通過下面的方法直接運行 “ 文檔檢查器 ” ： Excel 2020： 依次單擊 “ Office按鈕 → 準(zhǔn)備 → 檢查文檔 ” 。 這樣操作以后， “ 保存時從文件屬性中刪除個人信息 ” 選項就會變?yōu)榭蛇x狀態(tài)。該選項僅對當(dāng)前工作簿有效。 拓展 2：將一列單元格內(nèi)容合并到一個單元格中 假如 A 列中有一列名單，現(xiàn)在需要將這些名單合并到一個單元格中，并用某種標(biāo)點符號將其隔開，在 Excel中該怎樣操作呢？ 5 由于 A列中的名單較多，不宜用“ amp?！被?CONCATENATE函數(shù)進行合并，這時可用復(fù)制粘貼和替換的方法，步驟如下： 一、復(fù)制該列內(nèi)容到目標(biāo)單元格。 方法一：用記事本或 Word 將這列名單復(fù)制到記事本或 Word中，然后再復(fù)制粘貼回 Excel中。這里的技巧是：不能直接選擇某個單元格進行粘貼，而是要雙擊目標(biāo)單元格或選擇目標(biāo)單元格后單擊編輯欄，當(dāng)光標(biāo)在其中閃爍時再進行粘貼才能將其粘貼到一個單元格內(nèi)。 方法二：利用剪貼板進行操作 Excel剪貼板：在 Excel 2020中，選擇“開始”選項卡，在“剪貼板”組中單擊右下角的對話框啟動器打開剪貼板。 ，復(fù)制的內(nèi)容即可顯示在剪貼板中。 6 。然后單擊剪貼板中所復(fù)制的內(nèi)容，該列名單即可復(fù)制到目標(biāo)單元格中，如下圖的 C5單元格。 二、替換換行符。 用上述方法將多個單元格內(nèi)容合并到一個單元格后，原來的各行內(nèi)容之間還包含了一個換行符，可以用下面的方法將這些換行符替換為分隔符號： ，按 Ctrl+H打開“查找和替換”對話框。 “查找內(nèi)容”右側(cè)的文本框，按住 Alt 鍵，在數(shù)字小鍵盤中輸入“ 0010”，然后再松開 Alt鍵。在“替換為”右側(cè)的文本框中根據(jù)需要輸入某種分隔符號，如頓號、空格等，如果無需分隔各行內(nèi)容，則“替換為”保留為空。單擊“全部替換”即可。 需要注意的是，進行這種替換后如果緊接著要再次替換換行符，在打 開“查找和替換”對話框后， Excel會“記住”要替換的換行符，這時可直接單擊“全部替換”按鈕。另外，之后要在工作表中再進行其他內(nèi)容的查找或替換，有時即使所查找的內(nèi)容明顯存在于工作表中， Excel也可能會給出找不到搜索內(nèi)容的提示。原因是以前在“查找內(nèi)容”后輸入的換行符雖然不可見，但卻包含了兩行，如果直接在“查找內(nèi)容”后輸入新的查找內(nèi)容，實際上是輸入到了第一行中， Excel自然會找不到搜索內(nèi)容了。解決的方法是： 方法 A：在“查找內(nèi)容”后輸入新的查找內(nèi)容后，將光標(biāo)定位到所輸入的內(nèi)容后，按下 Delete鍵刪 除后面的不可見內(nèi)容，再進行查找或替換。 方法 B：關(guān)閉并重新啟動 Excel。 7 項目二 Excel 高級應(yīng)用 【項目要求】 (一 )掌握數(shù)據(jù)管理和分析的方法 (二 )掌握記錄單的使用 (三 )掌握數(shù)據(jù)透視表和透視圖的概念和應(yīng)用 【項目內(nèi)容】 (一 )數(shù)據(jù)管理與分析 (二 )圖表 (三 )使用圖形和圖片 【相關(guān)知識點解析】 ① 主要概念 排序 ——對一列或多列中的數(shù)據(jù)按文本（升序或降序）、數(shù)字（升序或降序）以及日期和時間（升序或降序）進行排序。還可以按自定義序列（如大、中和小）或格式（包括單元格顏色、字體顏色或圖標(biāo)集）進行排序。大多數(shù)排序操作都是針對列進行的，但是，也可以針對行進行。 篩選 ——篩選過的數(shù)據(jù)僅顯示那些滿足指定條件 （條件：所指定的限制查詢或篩選的結(jié)果集中包含哪些記錄的條件）的行，并隱藏那些不希望顯示的行。篩選數(shù)據(jù)之后，對于篩選過的數(shù)據(jù)的子集，不需要重新排列或移動就可以復(fù)制、查找、編輯、設(shè)置格式、制作圖表和打印?？梢园炊鄠€列進行篩選。篩選 器是累加的，這意味著每個追加的篩選器都基于當(dāng)前篩選器，從而進一步減少了數(shù)據(jù)的子集。 數(shù)據(jù)透視表（圖） ——記錄數(shù)量眾多、以流水賬形式記錄、結(jié)構(gòu)復(fù)雜的工作表，為了將其中的一些內(nèi)在規(guī)律顯現(xiàn)出來，可將工作表重新組合并添加算法。即建立數(shù)據(jù)透視表（圖）。數(shù)據(jù)透視表（圖）能幫助用戶分析、組織數(shù)據(jù)。利用它可以很快地從不同角度對數(shù)據(jù)進行分類匯兌。 ② 知識拓展 拓展 1：在 Excel2020 中快速刪除重復(fù)數(shù)據(jù)項 在 Excel以前的版本中，可以通過“高級篩選”來刪除重復(fù)記錄，在 Excel2020 中不但保留了“高級篩選”這個功能，而 更為值得注意的是 Excel 2020 增加了一個“刪除重復(fù)項”按鈕，從而這項操作變得更加方便、快捷。首先選擇需要刪除重復(fù)項的所有單元格。如果是要對所有數(shù)據(jù)進行這一操作，可以任意選擇一個單元格。 8 在工具欄中選擇“數(shù)據(jù)”選項卡，再單擊“排序與篩選”區(qū)中的“刪除重復(fù)項”按鈕。 在彈出的“刪除重復(fù)項”對話框選擇要檢查的字段，最終將只對選中的字段進行比較。這里選擇后三個字段。 最后單擊“確定”按鈕，多余的重復(fù)項就被刪除了。 9 拓展 2：怎樣在 Excel 中篩選加粗的文字？ 有時要在 Excel中篩選加粗的文字，雖然 Excel沒有提供直接的方法，但可以先通過輔助列來確定單元格內(nèi)容是否加粗，然后再對輔助列進行篩選，從而間接地篩選出加粗的文字。判斷單元格文字是否加粗可以用 函數(shù)或自定義函數(shù)來進行。下面以篩選圖中A1:A16 區(qū)域的加粗文字為例，介紹在 Excel 2020 中的操作方法： 方法一、輔助列 + 定義名稱 用 函數(shù)可以判斷單元格格式，如加粗、傾斜、下劃線等，但該函數(shù)只能用于定義的名稱中。 B2 單元格，按快捷鍵 Ctrl+F3，在 Excel 2020 中 打開 “名稱管理器 ”， 單擊 “名稱管理器 ”中的 “新建 ”按鈕，打開 “新建名稱 ”對話框。 在 “名稱 ”后輸入一個名稱，如 “cuti”，在 “引用位置 ”后的文本框中輸入公式： 10 =(20,A2)amp。T(NOW()) 函數(shù)的第一個參數(shù)為 “20”，用于判斷指定的單元格是否為粗體。后面加上“T(NOW())”，是為了當(dāng)工作表中的任意單元格進行計算時（或按 F9 鍵），該函數(shù)也能重新計算。單擊 “確定 ”，然后關(guān)閉 “名稱管理器 ”。 B2 單元格中輸入公式： =cuti 然后拖動填充柄將公式填充到 B3:B16 區(qū)域，如果 A列文字為粗體，則公式返回 TRUE。 B 列為 TRUE 的單元格，即可篩選出 A列中的粗體文字。 11 方法二、輔助列 +自定義函數(shù) Alt +F11，打開 VBA編輯器，單擊菜單 “插入 → 模塊 ”，在代碼窗口中輸入下面的代碼： Function Bold(rCell As Range) Bold = End Function 方法將該自定義函數(shù)標(biāo)記為易失性函數(shù)，這樣當(dāng)工作表中的任意單元格進行計算（或按 F9 鍵）時，該函數(shù)也將重新計算。 VBA編輯器，返回到 Excel界面。在 B2 單元格中輸入公式： =bold(A2) 然后拖動填充柄，將公式填充到 B3:B16 區(qū)域。如果 A列文字為粗體，則公式返回 TRUE。 B 列。 注意：如果公式輸入完畢后，對 A 列中的某些單元格進行了加粗或取消加粗操作，上述兩種方法中的公式不會立即自動刷新，這時按 F9 鍵刷新即可。 ③ 案例分析 案例 1：怎樣篩選第 8 位為 “8”和 “6”的手機號？ 在 Excel工作表中有一列手機號，怎樣篩選出從左往右數(shù)第 8 位為 “8”和 “6”的手機號？ 要在一列數(shù)值中篩選出某位置為指定數(shù)字的數(shù)據(jù)，有很多方法都可以實現(xiàn)。例如用高級篩選、用輔助列 +公式等方法，本文介紹用自動篩選的方法。假如手機號碼在 A2:A100 區(qū)域中，要篩選出其中第 8 位為 “6”和 “8”的手機號，方法如下。 12 A列中的數(shù)值轉(zhuǎn)換為文本。 由于在自定義自動篩選時要用到通配符（？、 *），而通配符無法應(yīng)用于數(shù)值，故先將A列中的手機號轉(zhuǎn)換為文本。如果手機號原本就是以文本形式存儲的數(shù)字，則跳過此步。 選擇 A列中的手機號碼，在 Excel 2020 功能區(qū)中選擇 “數(shù)據(jù) ”選項卡，在 “數(shù)據(jù)工具 ”組中單擊 “分列 ”。 在彈出的對話框中單擊兩次 “