Office辦公應(yīng)用項目教程（Office 2019）課件

工業(yè)和信息化精品系列教材Excel2019進階操作項目四Office辦公應(yīng)用項目教程（Office2019）（AIGC版）（微課版）目錄計算數(shù)據(jù)——制作創(chuàng)新大賽成績統(tǒng)計表任務(wù)一管理數(shù)據(jù)——制作工廠實習(xí)工資表任務(wù)二可視化數(shù)據(jù)——制作特色農(nóng)產(chǎn)品銷售統(tǒng)計表任務(wù)三項目實訓(xùn)本任務(wù)的目標(biāo)是制作一張學(xué)校創(chuàng)新大賽成績統(tǒng)計表，參考效果如右圖所示。本任務(wù)將重點講解公式的計算、函數(shù)的應(yīng)用，以及借助AIGC工具查詢并使用函數(shù)等操作。長文檔編輯——制作職業(yè)道德規(guī)范文檔任務(wù)一任務(wù)目標(biāo)任務(wù)技能（一）認識與使用公式在Excel2019中，在“=”符號后可根據(jù)需要輸入相應(yīng)的常量、運算符、單元格地址或函數(shù)來組成公式的內(nèi)容。Excel2019中的公式一般由5個部分組成，即“=”號、常量、運算符、單元格地址（包括單元格區(qū)域地址）和函數(shù)。如下圖所示。1．公式的組成（一）認識與使用公式Excel2019常見的運算符有4種。2．運算符的類型01算術(shù)運算符02比較運算符03文本連接運算符04引用運算符（一）認識與使用公式當(dāng)公式中出現(xiàn)多個運算符時，Excel2019會默認從左到右依次進行計算，但如果運算符不是相同的優(yōu)先級時，則將按照右圖所示的順序進行計算。3．運算符的運算順序（一）認識與使用公式單元格引用通過行號和列標(biāo)來標(biāo)識公式中所使用的數(shù)據(jù)地址，Excel2019會自動根據(jù)公式中的行號和列標(biāo)來查找單元格，從而達到引用單元格中數(shù)據(jù)的目的。不同的引用方式會導(dǎo)致公式的計算結(jié)果的不同。在Excel2019中，常用的單元格引用方式有3種，包括以下3種。4．單元格的引用方式相對引用絕對引用混合引用（一）認識與使用公式公式的輸入、確認、修改、復(fù)制等操作與普通數(shù)據(jù)相比有一定區(qū)別，在實際操作中要特別注意，否則一不小心就會改變公式的內(nèi)容。5．輸入與編輯公式輸入與確認公式修改公式復(fù)制公式（二）函數(shù)的組成與應(yīng)用Excel函數(shù)具有特定的語法格式，要想利用函數(shù)完成數(shù)據(jù)計算，就需要遵從該函數(shù)的語法格式進行操作。一個完整的函數(shù)由“=”符號、函數(shù)名、參數(shù)括號和函數(shù)參數(shù)構(gòu)成，其中“=”符號用于區(qū)別普通數(shù)據(jù)；函數(shù)名用于指定調(diào)用的函數(shù)；參數(shù)括號用于劃分參數(shù)區(qū)域；函數(shù)參數(shù)用于參與函數(shù)計算，它可以是常量和單元格引用地址；“,”號用于分隔函數(shù)參數(shù)，如下圖所示。1．函數(shù)的語法格式（二）函數(shù)的組成與應(yīng)用選擇需要插入函數(shù)的單元格，單擊編輯欄左側(cè)的“插入函數(shù)”按鈕，或單擊【公式】/【函數(shù)庫】中的“插入函數(shù)”按鈕，打開“插入函數(shù)”對話框，在“或選擇類別”下拉列表中選擇函數(shù)的所屬類別選項。如下圖所示。2．插入函數(shù)（二）函數(shù)的組成與應(yīng)用嵌套函數(shù)是指將一個函數(shù)作為另一個函數(shù)的參數(shù)來使用。除了可以直接在編輯欄中將函數(shù)的某個參數(shù)設(shè)置為另一個函數(shù)外，還可按以下方法來使用嵌套函數(shù)：按插入函數(shù)的方法打開“函數(shù)參數(shù)”對話框，將文本插入點定位到需要更改的參數(shù)對應(yīng)的文本框中，單擊名稱框右側(cè)的下拉按鈕，在彈出的下拉列表中選擇“其他函數(shù)”選項，打開“插入函數(shù)”對話框，在其中選擇需要作為參數(shù)的函數(shù)后，再在彈出的對話框中設(shè)置嵌套函數(shù)的參數(shù)，如下圖所示。3．嵌套函數(shù)任務(wù)實施（一）使用公式計算表格數(shù)據(jù)下面使用公式匯總每個參賽作品的總分數(shù)據(jù)，具體操作如下。1引用單元格地址2

繼續(xù)輸入公式3完成公式4返回計算結(jié)果（二）使用函數(shù)統(tǒng)計表格數(shù)據(jù)下面使用函數(shù)統(tǒng)計參賽作品的排名數(shù)據(jù)，具體操作如下。2設(shè)置參數(shù)3

轉(zhuǎn)換單元格區(qū)域的引用方式1選擇函數(shù)4填充統(tǒng)計結(jié)果5設(shè)置單元格底紋（三）借助文心一言統(tǒng)計平均分1復(fù)制文心一言提供的公式2

粘貼并修改公式3修改公式4統(tǒng)計其他組別參賽作品的平均分在不清楚如何使用公式或函數(shù)進行統(tǒng)計的情況下，可以借助文心一言來尋求答案。下面借助文心一言統(tǒng)計不同組別參賽作品的平均分，以此來了解不同組別的作品質(zhì)量，具體操作如下。計算數(shù)據(jù)——制作創(chuàng)新大賽成績統(tǒng)計表可視化數(shù)據(jù)——制作特色農(nóng)產(chǎn)品銷售統(tǒng)計表任務(wù)三管理數(shù)據(jù)——制作工廠實習(xí)工資表任務(wù)二任務(wù)一項目實訓(xùn)目錄本任務(wù)的目標(biāo)是制作一張簡單的工廠實習(xí)工資表，用于對實習(xí)生的實習(xí)工資情況進行管理，參考效果如右圖所示。本任務(wù)將重點講解使用AIGC工具生成VBA代碼的方法，以及排序、篩選和分類匯總數(shù)據(jù)等操作。管理數(shù)據(jù)——制作工廠實習(xí)工資表任務(wù)二任務(wù)目標(biāo)任務(wù)技能（一）認識數(shù)據(jù)清單如果表格中包含數(shù)據(jù)的單元格區(qū)域是連續(xù)的，且表格數(shù)據(jù)以列為分段記錄，每一列為一個項目，存放類型數(shù)據(jù)，以行為數(shù)據(jù)記錄，除表格標(biāo)題外的第一行為項目字段，其余各行為一個完整的數(shù)據(jù)記錄，包含每個項目的數(shù)據(jù)，那么這種表格就屬于數(shù)據(jù)清單，如下圖所示。（二）數(shù)據(jù)的排序快速排序是指以選擇的單元格所在的項目數(shù)據(jù)為排序依據(jù)，快速對數(shù)據(jù)記錄進行排序操作。1．快速排序快速排序數(shù)據(jù)的方法為：選擇表格中作為排序依據(jù)的項目對應(yīng)列下任意包含數(shù)據(jù)的單元格，單擊【數(shù)據(jù)】/【排序和篩選】中的“升序”按鈕，數(shù)據(jù)記錄將以升序方式排列；單擊“降序”按鈕，數(shù)據(jù)記錄將以降序方式排列，如上圖所示。（二）數(shù)據(jù)的排序多關(guān)鍵字排序是指以多個排序關(guān)鍵字作為排序依據(jù)來排列數(shù)據(jù)記錄。2．多關(guān)鍵字排序設(shè)置多關(guān)鍵字排序的方法為：選擇表格中任意包含數(shù)據(jù)的單元格，單擊【數(shù)據(jù)】/【排序和篩選】中的“排序”按鈕，打開“排序”對話框，在“排序依據(jù)”下拉列表框中選擇某個表格項目作為第一個排序依據(jù)，在“次序”欄中的下拉列表中選擇所需的排列方式，然后單擊“添加條件”按鈕，在“次要關(guān)鍵字”下拉列表中選擇另一個項目作為第二個排序依據(jù)，在“次序”欄中的下拉列表中選擇所需的排列方式。如上圖所示。（三）數(shù)據(jù)的篩選指定篩選內(nèi)容是指篩選出指定內(nèi)容的數(shù)據(jù)記錄。1．指定篩選內(nèi)容指定篩選內(nèi)容的方法為：選擇任意包含數(shù)據(jù)的單元格，單擊【數(shù)據(jù)】/【排序和篩選】中的“篩選”按鈕，然后單擊篩選項目右側(cè)的下拉按鈕，在彈出的下拉列表中單擊選中指定內(nèi)容對應(yīng)的復(fù)選框，單擊“確定”按鈕，此時表格中將僅顯示篩選后的指定內(nèi)容，如右圖所示。（三）數(shù)據(jù)的篩選設(shè)置篩選條件是指通過對某個項目設(shè)置條件來篩選出此項目下滿足該條件的數(shù)據(jù)記錄。2．設(shè)置篩選條件設(shè)置篩選條件的方法為：進入篩選狀態(tài)，單擊所需項目右側(cè)的下拉按鈕，在彈出的下拉列表中選擇“數(shù)字篩選”選項，在彈出的子列表中選擇某種條件選項，并在打開的對話框中設(shè)置條件，最后單擊“確定”按鈕，如右圖所示。（三）數(shù)據(jù)的篩選相較于條件篩選，高級篩選可通過設(shè)置更加復(fù)雜的篩選條件來篩選出滿足條件的數(shù)據(jù)記錄。3．高級篩選例如，若要篩選出類型為“蔬菜”，且銷售人員姓名中第二個字為“曉”的數(shù)據(jù)記錄，可以先在表格的其他區(qū)域輸入高級篩選的條件，這里將“類型”限定為“蔬菜”，“銷售人員”限定為“*曉*”，然后單擊【數(shù)據(jù)】/【排序和篩選】中的“高級”按鈕，打開“高級篩選”對話框，在“列表區(qū)域”文本框中引用表格數(shù)據(jù)所在的地址，在“條件區(qū)域”文本框中引用條件數(shù)據(jù)所在的地址，最后單擊“確定”按鈕，表格中將顯示經(jīng)過高級篩選后的數(shù)據(jù)記錄，如上圖所示。（四）數(shù)據(jù)的分類匯總分類匯總數(shù)據(jù)可以將數(shù)據(jù)記錄按照某種指定類型重新排列，然后對同類型的數(shù)據(jù)記錄進行匯總計算。分類匯總數(shù)據(jù)的方法為：按照簡單排序的方法以分類的項目為排序依據(jù)排列數(shù)據(jù)，然后單擊【數(shù)據(jù)】/【分級顯示】中的“分類匯總”按鈕，打開“分類匯總”對話框，在“分類字段”下拉列表中選擇排序依據(jù)對應(yīng)的項目選項，在“匯總方式”下拉列表中選擇某種匯總方式，在“選定匯總項”列表框中單擊復(fù)選框設(shè)置匯總項（可設(shè)置一個或多個匯總項），最后單擊“確定”按鈕，如上圖所示。任務(wù)實施（一）借助文心一言使用VBA代碼填充底紋為美化數(shù)據(jù)，通常采用為數(shù)據(jù)單元格填充底紋的方法如果需要按隔行填充相同底紋的方式來填充表格數(shù)據(jù)，當(dāng)數(shù)據(jù)量少的時候可以手動進行填充，但是當(dāng)數(shù)據(jù)量較多時，這種方法就顯得非常繁瑣。此時可以利用文心一言生成填充底紋的VBA代碼，將其應(yīng)用在表格中，實現(xiàn)自動填充的效果，大幅提高操作效率。下面借助文心一言使用代碼為“實習(xí)工資表.xlsx”填充底紋，具體操作如下。1復(fù)制代碼2插入模塊（一）借助文心一言使用VBA代碼填充底紋3粘貼代碼4執(zhí)行宏5自動填充底紋（二）排序數(shù)據(jù)1按實習(xí)天數(shù)降序排列數(shù)據(jù)2設(shè)置排序主要關(guān)鍵字3排序結(jié)果下面通過排序數(shù)據(jù)來了解學(xué)生的實習(xí)天數(shù)，然后通過多關(guān)鍵字排序查看實發(fā)實習(xí)工資的情況，具體操作如下。（三）篩選數(shù)據(jù)1以實發(fā)實習(xí)工資為篩選依據(jù)2設(shè)置實發(fā)實習(xí)工資的篩選條件3以實習(xí)天數(shù)為篩選依據(jù)下面利用Excel2019的篩選功能，篩選出實發(fā)實習(xí)工資不低于4000元、實習(xí)天數(shù)小于80天，且考勤扣除小于50元的數(shù)據(jù)記錄，具體操作如下。（三）篩選數(shù)據(jù)4設(shè)置實習(xí)天數(shù)的篩選條件5以考勤扣除為篩選依據(jù)6設(shè)置考勤扣除的篩選條件7篩選結(jié)果（四）分類匯總數(shù)據(jù)1按實習(xí)部門排序數(shù)據(jù)2匯總實習(xí)天數(shù)下面根據(jù)不同的實習(xí)部門，分類匯總出各部門學(xué)生的平均實習(xí)天數(shù)和平均實發(fā)實習(xí)工資，具體操作如下。（四）分類匯總數(shù)據(jù)3匯總結(jié)果4將工作簿保存為“Excel啟用宏的工作簿”格式管理數(shù)據(jù)——制作工廠實習(xí)工資表任務(wù)二可視化數(shù)據(jù)——制作特色農(nóng)產(chǎn)品銷售統(tǒng)計表任務(wù)三計算數(shù)據(jù)——制作創(chuàng)新大賽成績統(tǒng)計表任務(wù)一項目實訓(xùn)目錄本任務(wù)的目標(biāo)是為某種特色農(nóng)產(chǎn)品制作一張銷售統(tǒng)計表，詳細記錄一段時期內(nèi)該農(nóng)產(chǎn)品的銷售數(shù)據(jù)，并利用圖表等可視化手段形象生動地展示銷售數(shù)據(jù)，參考效果如右圖所示。本任務(wù)將重點講解使用AIGC工具創(chuàng)建圖表、圖表的編輯與美化、數(shù)據(jù)透視表和數(shù)據(jù)透視圖的創(chuàng)建與設(shè)置等操作。可視化數(shù)據(jù)——制作特色農(nóng)產(chǎn)品銷售統(tǒng)計表任務(wù)三任務(wù)目標(biāo)任務(wù)技能（一）圖表的構(gòu)成圖表的組成根據(jù)圖表類型的不同而不同，以常見的二維簇狀柱形圖為例，其構(gòu)成部分包括圖表標(biāo)題、圖例、數(shù)據(jù)系列、數(shù)據(jù)標(biāo)簽、網(wǎng)格線、坐標(biāo)軸、坐標(biāo)軸標(biāo)題等，如下圖所示。（二）圖表的類型柱形圖可以清晰地對比數(shù)據(jù)之間的大小情況。當(dāng)用戶需要對比一組數(shù)據(jù)的大小時，可以選擇柱形圖來表現(xiàn)數(shù)據(jù)。除此以外，柱形圖還能反映數(shù)據(jù)在一段時間內(nèi)的變化情況，如右圖所示。1．柱形圖（二）圖表的類型折線圖可以將同一數(shù)據(jù)系列的數(shù)據(jù)以點或線的形式表示出來，從而直觀地顯示數(shù)據(jù)的變化趨勢。當(dāng)用戶需要分析數(shù)據(jù)在一定時期內(nèi)的變化情況時，就可以選擇折線圖來表現(xiàn)數(shù)據(jù)，如右圖所示。2．折線圖（二）圖表的類型餅圖可以顯示單個數(shù)據(jù)系列中各項數(shù)據(jù)的大小與數(shù)據(jù)總和的比較，從而直觀地顯示出各數(shù)據(jù)占數(shù)據(jù)總和的比例。當(dāng)用戶需要分析局部數(shù)據(jù)占整體數(shù)據(jù)的比例時，就可以選擇餅圖來表現(xiàn)數(shù)據(jù)，如左圖所示。3．餅圖（二）圖表的類型組合圖是指使用兩種或兩種以上類型的圖表來展示數(shù)據(jù)的組合圖表，不同的圖表可以擁有一個共同的橫坐標(biāo)軸和不同的縱坐標(biāo)軸。當(dāng)兩組或多組數(shù)據(jù)系列的數(shù)值大小差異過大時，就可以選擇組合圖來更好地表現(xiàn)數(shù)據(jù)。左圖所示為柱形圖和折線圖組成的組合圖。4．組合圖（三）創(chuàng)建與編輯圖表在Excel2019中創(chuàng)建圖表往往是先選擇已有的數(shù)據(jù)為數(shù)據(jù)源，然后選擇合適的圖表類型來進行創(chuàng)建。創(chuàng)建圖表的方法為：選擇數(shù)據(jù)源所在的單元格區(qū)域，單擊【插入】/【圖表】中代表某種圖表類型按鈕，在彈出的下拉列表中選擇需要的圖表選項，如下圖所示。1．創(chuàng)建圖表（三）創(chuàng)建與編輯圖表編輯圖表包括調(diào)整圖表和美化圖表等操作，合理調(diào)整與美化圖表，能夠提升圖表的美觀性、專業(yè)性和可讀性。2．編輯圖表調(diào)整圖表布局的方法為：單擊【圖表設(shè)計】/【圖表布局】中的“添加圖表元素”按鈕，在彈出的下拉列表中選擇需要添加或隱藏的圖表組成部分，在彈出的子列表中選擇所需的選項，如選擇“圖表標(biāo)題”選項，在彈出的下拉列表中選擇“無”選項，可將圖表標(biāo)題隱藏，若選擇“圖表上方”選項，則可使圖表標(biāo)題出現(xiàn)在圖表的上方，如上圖所示。（四）數(shù)據(jù)透視表與數(shù)據(jù)透視圖數(shù)據(jù)透視表是一種數(shù)據(jù)分析工具，通常用于匯總和分析大量數(shù)據(jù)。1．?dāng)?shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表的方法為：單擊【插入】/【表格】中的“數(shù)據(jù)透視表”按鈕，打開“來自表格或區(qū)域的數(shù)據(jù)透視表”對話框，在“表/區(qū)域”文本框中設(shè)置數(shù)據(jù)源，在“選擇放置數(shù)據(jù)透視表的位置”欄中設(shè)置數(shù)據(jù)透視表的創(chuàng)建位置，然后單擊“確定”按鈕創(chuàng)建空白數(shù)據(jù)透視表，此時在打開的“數(shù)據(jù)透視表字段”任務(wù)窗格中將所需字段拖曳至下方相應(yīng)的列表框中后，便可完成數(shù)據(jù)透視表的創(chuàng)建操作，如上圖所示。（四）數(shù)據(jù)透視表與數(shù)據(jù)透視圖數(shù)據(jù)透視圖可以直接創(chuàng)建，也可以在數(shù)據(jù)透視表的基礎(chǔ)上進行創(chuàng)建。2．?dāng)?shù)據(jù)透視圖直接創(chuàng)建數(shù)據(jù)透視圖的方法為：單擊【插入】/【圖表】中的“數(shù)據(jù)透視圖”按鈕，打開“創(chuàng)建數(shù)據(jù)透視圖”對話框，按創(chuàng)建數(shù)據(jù)透視表的方法設(shè)置數(shù)據(jù)透視圖的數(shù)據(jù)源和創(chuàng)建位置后，單擊“確定”按鈕，然后打開的“數(shù)據(jù)透視圖字段”任務(wù)窗格中將所需字段拖曳至下方相應(yīng)的列表框中，便可創(chuàng)建類型為柱形圖的數(shù)據(jù)透視圖，同時將自動創(chuàng)建相應(yīng)的數(shù)據(jù)透視表，如上圖所示。任務(wù)實施（一）借助文心一言創(chuàng)建圖表當(dāng)用戶制作好一張表格后，若不知道應(yīng)該如何利用圖表來分析表格數(shù)據(jù)時，則可以借助AIGC工具來獲取靈感或幫助。下面在文心一言中提出利用圖表分析數(shù)據(jù)的要求，然后通過文心一言的回答找到合適的方法，并在Excel2019中創(chuàng)建出相應(yīng)的圖表，具體操作如下。1輸入并發(fā)送需求3創(chuàng)建的折線圖效果2選擇圖表類型（二）編輯與美化圖表下面對創(chuàng)建的折線圖進行適當(dāng)編輯和美化，以提升圖表的美觀性和可讀性，具體操作如下。1應(yīng)用布局樣式2

修改坐標(biāo)軸標(biāo)題5調(diào)整圖表的位置和大小3刪除網(wǎng)格線并設(shè)置字體4添加圖表標(biāo)題（三）創(chuàng)建數(shù)據(jù)透視表和數(shù)據(jù)透視圖1創(chuàng)建數(shù)據(jù)透視表2

添加字段3添加坐標(biāo)軸標(biāo)題4添加數(shù)據(jù)標(biāo)簽由于無法直接利用表格數(shù)據(jù)對比不同銷售員的銷售額數(shù)據(jù)，因下面通過創(chuàng)建數(shù)據(jù)透視表和數(shù)據(jù)透視圖的方式來匯總得到不同銷售員的銷售額數(shù)據(jù)，借助柱形圖實現(xiàn)對比目的，具體操作如下。5調(diào)整圖表管理數(shù)據(jù)——制作工廠實習(xí)工資表任務(wù)二可視化數(shù)據(jù)——制作特色農(nóng)產(chǎn)品銷售統(tǒng)計表任務(wù)三計算數(shù)據(jù)——制作創(chuàng)新大賽成績統(tǒng)計表任務(wù)一項目實訓(xùn)目錄一、實訓(xùn)要求項目實訓(xùn)制作考試成績匯總表實訓(xùn)1考試成績匯總表可以顯示出每位學(xué)生的考試結(jié)果，不僅便于教師查閱考試成績，也方便教師通過排序、篩選等管理操作分析學(xué)生的考試情況?，F(xiàn)需要制作一張某班級的期末考試成績匯總表，要求表格不僅能展現(xiàn)出每位學(xué)生的考試成績，還可以快速查詢到指定學(xué)生的總分、平均分和排名數(shù)據(jù)。參考效果如上圖所示。實訓(xùn)思路任務(wù)實訓(xùn)制作考試成績匯總表實訓(xùn)1（1）打開“考試成績匯總表.xlsx”素材文件，利用SUM函數(shù)、AVERAGE函數(shù)和RANK.EQ函數(shù)分別計算出每位學(xué)生的總分、平均分和排名數(shù)據(jù)。（2）利用數(shù)據(jù)驗證功能中的“序列”增加驗證條件，引用學(xué)生姓名所在的單元格區(qū)域，實現(xiàn)通過選擇的方式輸入學(xué)生姓名的功能。（3）詢問文心一言應(yīng)該如何在表格中實現(xiàn)成績查詢的操作。（4）結(jié)合文心一言給出的答案使用相應(yīng)的函數(shù)或公式，并根據(jù)所選的學(xué)生姓名返回學(xué)生的總分、平均分和排名數(shù)據(jù)。一、實訓(xùn)要求項目實訓(xùn)制作體育用品采購表