【正文】 左上角單元格地址：右下角單元格地址表示。第 4單元 Excel2022的使用技巧 2 ?學(xué)習(xí)目標(biāo) 會(huì)對(duì)數(shù)據(jù)表進(jìn)行數(shù)據(jù)有效性設(shè)置； 會(huì)利用相關(guān)函數(shù)對(duì)數(shù)據(jù)表中數(shù)據(jù)進(jìn)行統(tǒng)計(jì)計(jì)算； 會(huì)對(duì)數(shù)據(jù)清單中的數(shù)據(jù)進(jìn)行排序、篩選及分類匯總； 能夠利用數(shù)據(jù)透視表對(duì)數(shù)據(jù)清單匯總、繪制圖表 3 目錄 案例 1 數(shù)據(jù)填充與數(shù)據(jù)有效性設(shè)置 案例 2 成績計(jì)算與統(tǒng)計(jì) 案例 3 成績分析 案例 4 成績表格式化 案例 6 產(chǎn)品銷售數(shù)據(jù)管理 案例 7 教工信息統(tǒng)計(jì)與分析 4 任務(wù)一 學(xué)生成績錄入與數(shù)據(jù)有效性控制 ?在錄入含有大量數(shù)據(jù)記錄的過程中，不可避免地出現(xiàn)錄入錯(cuò)誤的現(xiàn)象。如 A1:D100表示從 A1單元格到 D100單元格之間的矩形區(qū)域。 12 自定義序列 單擊 “ 工具 → 選項(xiàng) ” 命令，在 “ 自定義序列中添加或?qū)耄缦聢D所示。 21 4． 課堂實(shí)踐 22 5． 評(píng)價(jià)與總結(jié) 教師根據(jù)實(shí)踐情況 ， 或強(qiáng)調(diào) 、 演示或讓學(xué)生上臺(tái)操作 老師或?qū)W生總結(jié)本單元的知識(shí)點(diǎn) 23 6． 課外延伸 24 任務(wù)二 學(xué)生成績表的統(tǒng)計(jì)與分析 教師在某門課程教學(xué)結(jié)束時(shí)，通常都要對(duì)教學(xué)進(jìn)行評(píng)價(jià)，對(duì)學(xué)生的平時(shí)考查成績、期末成績、平時(shí)表現(xiàn)分?jǐn)?shù)等進(jìn)行統(tǒng)計(jì)計(jì)算，最后給每一位同學(xué)一個(gè)最終成績或等級(jí)。 ? COUNTIF(range,criteria),返回區(qū)域中滿足給定條件的單元格的個(gè)數(shù)，其中： ? range是統(tǒng)計(jì)的單元格區(qū)域； 29 ? criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件。 33 AND(logical1,logical2, ...) 所有參數(shù)的邏輯值為真時(shí)，返回 TRUE；只要有一個(gè)參數(shù)的邏輯值為假，即返回 FALSE。 如： =TEXT(20220,“$”)，返回值 “ $”。 39 （ 3）操作步驟： 40 （ 4）操作步驟： 41 （ 5）的操作步驟： 42 （ 6）的操作步驟： 43 有問題，查幫助 44 4． 課堂實(shí)踐 45 5． 評(píng)價(jià)與總結(jié) （ 視情況而定 ） 第一組同學(xué)（ 2位為一組）主動(dòng)上臺(tái)演示（ 1）（ 3）。如： =date(2022,11,12)。 例如，將工作表中 J2單元格中的計(jì)算公式SUM(E2:I2)復(fù)制到 J10單元格中就變成了SUM(E10:I10)。在該對(duì)話框中，可以對(duì)單元格（區(qū)域）數(shù)據(jù)的數(shù)字顯示方式、對(duì)齊方式、字體、邊框、圖案等進(jìn)行設(shè)置。 79 6． 課外延伸 80 案例 5 投資理財(cái)與貸款計(jì)算 【 場景描述 】 81 1．案例分析 在本案例中，可應(yīng)用 PMT（）函數(shù)計(jì)算基于固定利率及等額分期付款方式，返回貸款的每期付款額來解決 (1)中的問題。 在彈出的“ 函數(shù)參考 ” 對(duì)話框的 Rate中輸入 A4/12， 在 Nper框中輸入 A5*12， 在 PV框中輸入 1000或 A6， 如圖 422所示 ，單擊 “ 確定 ” 按鈕 。 ②研究 IPMT（）函數(shù)、 ISPMT（）及 NPV（）的用法及意義，試舉例。數(shù)據(jù)清單的每一列稱為一個(gè)字段，列標(biāo)題稱為字段名，從數(shù)據(jù)清單第二行開始的每一行都稱為一條記錄。 如：篩選銷售量在 3000以上（包括 3000），銷售員為姓王的記錄。透視表制作步驟如下： 單擊 “ 數(shù)據(jù) → 數(shù)據(jù)透視表和數(shù)據(jù)透視圖 ” 命令 → “ 數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?” → …… ， → 轉(zhuǎn)操作步驟見實(shí)現(xiàn)方法。但如果能熟練地應(yīng)用 Excel中的各種統(tǒng)計(jì)函數(shù)、數(shù)據(jù)透視表及圖表功能，一定能起到事半功倍的效果。 123 ② DSUM(database,field,criteria) 返回列表或數(shù)據(jù)庫的列中滿足指定條件的數(shù)字之和。其中： 125 ? Lookup_value為函數(shù) LOOKUP在第一個(gè)向量中所要查找的數(shù)值。 （ 3）圖表的創(chuàng)建與編輯 ①圖表的創(chuàng)建 操作步驟：選擇數(shù)據(jù)區(qū)域 → 單擊工具欄上的 “ 圖表向?qū)?” 按鈕 → 在 “ 圖表向?qū)?—4步驟之 1—圖表類型 ”對(duì)話框中選擇圖表類型及子類型 → …… 128 ② 圖表的編輯 跳轉(zhuǎn)本案例實(shí)現(xiàn)方法（ 6）、（ 7） 129 3． 實(shí)現(xiàn)方法 （ 1） 操作步驟： 130 同樣，在 I8輸入公式 “ =DAVERAGE(A1:E31,E1,I1:J2)”求女副教發(fā)表論文的平均值。 136 （ 6）繪制三維餅圖的操作步驟： 137 138 （ 7）繪制三維簇狀柱形圖及編輯操作步驟： 139 140 （ 8）保護(hù)工作簿、工作表的操作步驟： 141 4． 課堂實(shí)踐 （ 1）實(shí)踐本案例（ 1）、（ 2）。其中： ? Data_array 為用于計(jì)算頻率的數(shù)組或?qū)σ唤M數(shù)值的引用； ? Bins_array為間隔的數(shù)組或?qū)﹂g隔的引用，也就是分隔區(qū)間的分段點(diǎn)，由每個(gè)數(shù)據(jù)區(qū)間的最大值一組數(shù)據(jù)構(gòu)成。 144 145 ② 數(shù)據(jù)分析命令 146 （ 2）課外練習(xí) ①用 FREQUENCY函數(shù)和數(shù)據(jù)分析兩種方法統(tǒng)計(jì) “ 6.課外延伸 ” 中頻度分析問題。 （ 3）實(shí)踐本案例（ 6）、（ 7）、（ 8）。 132 （ 3）操作步聚： 選擇單元格區(qū)域 A2： E31→ 單擊 “ 格式 → 條件格式 ” 命令 → “ 條件 1”左框中選擇 “ 公式 ” ，右框中輸入 “ =AND($B2=”女 ” , $E2=MXA($E$2:$E$31))”→ 單擊 “ 格式 ” 按鈕 → 設(shè)置單元格底紋為 “ 淺綠色 ” ，如圖 446所示 → “ 確定 ” 。 ? Lookup_vector為只包含一行或一列的區(qū)域。 ③ DMAX(database,field,criteria)，返回列表或數(shù)據(jù)庫的列中滿足指定條件的最大數(shù)值。 119 打開 “ ”文件，完成下列各項(xiàng)工作： 120 1．案例分析 本案例（ 1）、（ 2）等涉及到數(shù)據(jù)庫統(tǒng)計(jì)函數(shù)知識(shí)點(diǎn)，如統(tǒng)計(jì)博士或教授發(fā)表論文之和等；（ 3）中的問題可以用前面已學(xué)過的條件格式知識(shí)來解決；（ 4）中涉及到數(shù)據(jù)查詢函數(shù) vlookup 與 lookup的應(yīng)用問題；（ 5）可以用前面學(xué)習(xí)過 Countif 、 Sumif等進(jìn)行統(tǒng)計(jì)，也可以用本案例學(xué)習(xí)的數(shù)據(jù)庫函數(shù)進(jìn)行統(tǒng)計(jì)。 然后進(jìn)行高級(jí)篩選。 高級(jí)篩選首先要?jiǎng)?chuàng)建篩選條件區(qū)域，如果篩選時(shí)要求多個(gè)條件同時(shí)滿足，則稱這些條件為 “ 與 ” 的關(guān)系；如果篩選只要求滿足多