信息技術基礎項目化教程（Windows 10+WPS Office 2019）課件 模塊4 項目二 計算和分析 WPS 表格中的數據（10學時）

模塊4

WPS表格操作與應用項目二計算和分析WPS表格中的數據為了總結2023年遠安特色農產品的推廣銷售成果，駐村工作隊隊員小李在前期收集整理銷售數據的基礎上，使用WPS表格對農產品銷售數據進行計算，使用圖表、數據透視表等對結果進行分析。知識目標（1）學習WPS表格中公式和函數的使用方法。（2）學習WPS表格中圖表的使用。（3）學習WPS表格中分析數據的方法。技能目標（1）能夠直接引用其他工作表的數據并完成數據計算。（2）能夠應用公式、函數計算表格中的數據。（3）能夠插入合適的圖表，并對圖表進行編輯、設置圖表格式。（4）能夠根據數據源創建需要的數據透視表并對其進行設置。（5）能夠快速為數據透視表應用合適的樣式并正確分析數據透視圖中的數據。素養目標（1）提高計算表格數據的效率。（2）提升分析、統計數據的能力。（3）培養科學嚴謹的工作作風。（4）增強振興鄉村的責任感。任務一計算“農產品銷售表”數據--完成效果小李通過WPS表格的公式、函數等對特色農產品銷售結果進行計算，制作“農產品銷售表”。（一）使用VLOOKUP函數VLOOKUP函數用于根據給定條件，在指定的區域中垂直查找與之匹配的數據，它的語法結構如下。VLOOKUP(查找值,數據表,列序數,[匹配條件])其中，“查找值”是必需參數，是希望在數據表中查找的值。“數據表”是必需參數，是包含查找值的區域或表。“列序數”是必需參數，返回值所在列的列標。“匹配條件”是可選參數，返回邏輯值，用于指定查找是精確匹配還是近似匹配。如果“匹配條件”為FALSE或0，則進行精確匹配；如果為TRUE或1，則進行近似匹配；如果省略，則默認進行近似匹配。（1）打開“農產品銷售表.et”工作簿，在“農產品銷售表”中選擇G3單元格，單擊編輯欄左邊的“插入函數”按鈕，打開插入函數對話框，在“選擇函數”列表框中選擇“VLOOKUP”選項，單擊“確定”按鈕，如圖4-32所示。（2）在打開的“函數參數”對話框中輸入參數，單擊“確定”按鈕。如圖4-33所示。（3）拖動G3單元格右下角的填充柄至G93單元格。（二）使用公式公式是指以等號（=）開頭，運用各種運算符號將常量或單元格引用連接起來形成的表達式，一般用于數據的簡單運算。（1）在“農產品銷售表”中選擇H3單元格，在編輯欄中輸入公式“=F3*G3”，如圖4-34所示。按Enter鍵確認，H3單元格中會顯示計算結果。（2）將該公式向下填充至H93單元格。（三）使用MAX、MIN函數MAX函數和MIN函數分別用于求一組數據中的最大值和最小值。它的語法結構如下。MAX（或MIN）(number1,number2,...)其中，number1、number2等參數表示要比較大小的數據，若參數為單元格引用，則只會計算參數引用的數據。（1）在“農產品銷售表”中選擇H94單元格，在編輯欄中輸入公式“=MAX(H3:H93)”，按Enter鍵確認。（2）在“農產品銷售表”中選擇H95單元格，在編輯欄中輸入公式“=MIN(H3:H93)”，按Enter鍵確認。（四）使用SUM函數、AVERAGE函數SUM函數用于返回某一單元格區域中所有數據之和。它的語法結構如下。SUM(number1,number2,...)其中，number1、number2等參數為需要求和的參數。若參數為單元格引用，則只會計算參數引用的數據。AVERAGE函數返回參數的平均值（算術平均值）。它的語法結構如下。AVERAGE(number1,number2,...)其中，number1、number2等參數為需要計算平均值的參數。若參數為單元格引用，則只會計算參數引用的數據。（1）在“農產品銷售表”中選擇H96單元格，在編輯欄中輸入公式“=SUM(H3:H93)”，按Enter鍵確認。（2）在“農產品銷售表”中選擇H97單元格，在編輯欄中輸入公式“=AVERAGE(H3:H93)”，按Enter鍵確認。（五）使用RANK.EQ函數RANK.EQ函數返回一列數據的排位，其大小與列表中的其他數據相關；如果多個數據具有相同的排位，則返回該組數據的最高排位。它的語法結構如下。RANK.EQ(數值,引用,[排位方式])其中，“數值”是必需參數，是要找到其排位的數據。“引用”是必需參數，可以是數據列表，也可以是對數據列表的引用。“排位方式”是可選參數，如果“排位方式”為0或省略，將使數據的排位按降序排列；如果“排位方式”不為0，將使數據的排位按升序排列。（1）在“農產品銷售表”中選擇I3單元格，單擊編輯欄左邊的“插入函數”按鈕，打開“插入函數”對話框，在“選擇函數”列表框中選擇“RANK.EQ”選項，單擊“確定”按鈕。（2）在打開的“函數參數”對話框中輸入參數，單擊“確定”按鈕，如圖4-35所示。（3）拖動I3單元格右下角的填充柄至I93單元格。（六）使用IF函數IF函數是一個判斷函數，它可以對值和期待值進行邏輯比較，判斷值是否滿足給定條件。如果條件為真，函數返回一個值；如果條件為假，函數返回另一個值。它的語法結構如下。IF(測試條件,真值,假值)其中，“測試條件”是必需參數，表示要測試的條件。“真值”是必需參數，表示“測試條件”的結果為TRUE時返回的值。“假值”是可選參數，表示“測試條件”的結果為FALSE時返回的值。（1）在“農產品銷售表”中選擇J3單元格，單擊編輯欄左邊的“插入函數”按鈕，打開“插入函數”對話框，在“選擇函數”列表框中選擇“IF”選項，單擊“確定”按鈕。（2）在打開的“函數參數”對話框中輸入參數，單擊“確定”按鈕，如圖4-36所示。（3）拖動J3單元格右下角的填充柄至J93單元格。（七）使用IFS函數IFS函數是一個判斷函數，它可以對值和期待值進行邏輯比較，判斷值是否滿足一個或多個條件，并返回與“真值1”對應的值。IFS函數可以取代多個嵌套的IF函數。它的語法結構如下。IFS(測試條件1,真值1,[測試條件2,真值2],[測試條件3,真值3],…)其中，“測試條件1”是必需參數，是計算結果為TRUE或FALSE時的表達式。“真值1”是必需參數，表示當“測試條件1”的計算結果為TRUE時返回的結果。“測試條件2”～“測試條件127”是可選參數，是計算結果為TRUE或FALSE的表達是。“真值2”～“真值127”是可選參數，表示當“測試條件N”的計算結果為TRUE時返回的結果。每個“測試條件N”對應一個“真值N”。（1）在“農產品銷售表”中選擇K3單元格，單擊編輯欄左邊的“插入函數”按鈕，打開“插入函數”對話框，在“選擇函數”列表框中選擇“IFS”選項，單擊“確定”按鈕。（2）在打開的“函數參數”對話框中輸入參數，單擊“確定”按鈕，如圖4-37所示。（3）拖動K3單元格右下角的填充柄至K93單元格。（八）使用COUNTIFS函數COUNTIFS函數用于將條件應用于跨多個區域的單元格，并統計滿足所有條件的單元格個數。它的語法結構如下。COUNTIFS(區域1,條件1,[區域2,條件2],...)其中，“區域1”是必需參數，表示在其中計算關聯條件的第一個區域。“條件1”是必需參數，條件的形式為數字、表達式、單元格引用或文本，它定義了要計數的單元格范圍。“區域2,條件2”等是可選參數，表示附加的區域及其關聯條件。（1）在“農產品銷售表”中選擇N4單元格，單擊編輯欄左邊的“插入函數”按鈕，打開“插入函數”對話框，在“選擇函數”列表框中選擇“COUNTIFS”選項，單擊“確定”按鈕。（2）在打開的“函數參數”對話框中輸入參數，單擊“確定”按鈕，如圖4-38所示。（3）拖動N4單元格右下角的填充柄至N6單元格。（4）采用相同的方法計算O4：P6單元格區域的數據。（九）使用SUMIF函數、AVERAGEIF函數SUMIF函數用于根據指定條件對若干單元格的數據進行求和。它的語法結構如下。SUMIF(區域,條件,求和區域)其中，“區域”為進行條件判斷的單元格區域，“條件”為確定哪些單元格將被相加求和的條件，“求和區域”是需要求和的實際單元格。AVERAGEIF函數用于返回某個區域內滿足給定條件的所有單元格的平均值（算術平均值）。它的語法結構如下。AVERAGEIF(區域,條件,求平均值區域)其中，“區域”為進行條件判斷的單元格區域，“條件”為確定哪些單元格將被相加求平均值的條件，“求平均值區域”是需要求平均值的實際單元格。（1）在“農產品銷售表”中選擇N10單元格，單擊編輯欄左邊的“插入函數”按鈕，打開“插入函數”對話框，在選擇函數”列表框中選擇“SUMIF”選項，單擊“確定”按鈕。（2）在打開的“函數參數”對話框中輸入參數，單擊“確定”按鈕，如圖4-39所示。（3）拖動N10單元格右下角的填充柄至N12單元格。（4）在“農產品銷售表”中選擇O10單元格，單擊編輯欄左邊的“插入函數”按鈕，打開“插入函數”對話框，在選擇函數”列表框中選擇“AVERAGEIF”選項，單擊“確定”按鈕。（5）在打開的“函數參數”對話框中輸入參數，單擊“確定”按鈕，如圖4-40所示。（6）拖動O10單元格右下角的填充柄至O12單元格。（十）使用SUMIFS函數、AVERAGEIFS函數SUMIFS函數用于計算滿足多個條件的全部參數的總和。它的語法結構如下。SUMIFS(求和區域,區域1,條件1,[區域2,條件2],...)其中，“求和區域”是必需參數，表示要求和的單元格區域。“區域1”是必需參數，表示使用“條件1”進行條件判斷的區域。“區域1”和“條件1”用于設置搜索某個區域是否符合特定條件的搜索對，一旦在該區域中找到了對應項，將計算“求和區域”中相應值的和。“條件1”是必需參數，用于指定計算“區域1”中的哪些單元格的和。“區域2,條件2”等是可選參數，表示附加的區域及其關聯條件。AVERAGEIFS函數用于返回滿足多個條件的所有單元格的平均值（算術平均值）。它的語法結構如下。AVERAGEIFS(求平均值區域,區域1,條件1,[區域2,條件2],...)其中，“求平均值區域”是必需參數，表示要求平均值的單元格區域。“區域1”是必需參數，表示使用“條件1”進行條件判斷的區域。“區域1”和“條件1”用于設置搜索某個區域是否符合特定條件的搜索對，一旦在該區域中找到了對應項，將計算“求平均值區域”中相應值的平均值。“條件1”是必需參數，用于指定計算“區域1”中的哪些單元格的平均值。“區域2,條件2”等是可選參數，表示附加的區域及其關聯條件。（1）在“農產品銷售表”中選擇N16單元格，單擊編輯欄左邊的“插入函數”按鈕，打開“插入函數”對話框，在“選擇函數”列表框中選擇“SUMIFS”選項，單擊“確定”按鈕。（2）在打開的“函數參數”對話框中輸入參數，單擊“確定”按鈕，如圖4-41所示。（3）拖動N16單元格右下角的填充柄至N18單元格。（4）在“農產品銷售表”中選擇O16單元格，單擊編輯欄左邊的“插入函數”按鈕，打開“插入函數”對話框，在“選擇函數”列表框中選擇“AVERAGEIFS”選項，單擊“確定”按鈕。（5）在打開的“函數參數”對話框中輸入參數，單擊“確定”按鈕，如圖4-42所示。（6）拖動O16單元格右下角的填充柄至O18單元格。任務二使用圖表分析“農產品銷售表”數據--

完成效果為了更直觀地展示2023年遠安特色農產品的銷售成果，駐村工作隊隊員小李采用WPS圖表對“農產品銷售表”進行分析。（一）插入圖表（1）打開“農產品銷售表（圖表制作）.et”工作簿。（2）按住Ctrl鍵選擇“農產品銷售表”的M21:M24和AA21:AA24單元格區域，單擊“插入”選項卡中的“插入餅圖或圓環圖”按鈕，在彈出的下拉列表中選擇“二維餅圖”欄中的“餅圖”選項，如圖4-44所示。（3）將圖表移動到所選數據區域下方，并將圖表標題更改為“各公司銷售額比例”，如圖4-45所示。（4）選擇M21:Y24單元格區域，單擊“插入”選項卡中的“插入柱形圖”按鈕，在彈出的下拉列表中選擇“二維柱形圖”欄中的“簇狀柱形圖”選項。將圖表移動到餅圖右側，并將圖表標題更改為“各公司每月銷售額分析”。（二）調整圖表布局應用布局樣式和添加圖表元素可以更改圖表的整體布局。具體操作如下。（1）選擇餅圖，單擊“圖表工具”選項卡中的“快速布局”按鈕，在彈出的下拉列表中選擇“布局1”選項，如圖4-46所示。（2）選擇柱形圖，單擊“圖表工具”選項卡中的“添加元素”按鈕，在彈出的下拉列表中選擇“軸標題”選項，在彈出的子列表中選擇“主要橫向坐標軸”選項，如圖4-47所示。（3）將橫向軸標題修改為“月份”，然后使用相同的方法添加主要縱向坐標軸，并將縱向軸標題修改為“銷售額（元）”。選擇縱向軸標題，單擊鼠標右鍵，在彈出的快捷菜單中選擇“設置坐標軸標題格式”命令。（4）在打開的“屬性”任務窗格中打開“標題選項”選項卡，單擊“大小與屬性”按鈕，在“文字方向”下拉列表中選擇“豎排（從右向左）”選項，如圖4-48所示。（5）選擇柱形圖，單擊“圖表工具”選項卡中的“添加元素”按鈕，在彈出的下拉列表中選擇“數據標簽”選項，在彈出的子列表中選擇“數據標簽外”選項，如圖4-49所示。（6）單擊“圖表工具”選項卡中的“添加元素”按鈕，在彈出的下拉列表中選擇“圖例”選項，在彈出的子列表中選擇“右側”選項。任務三透視分析“農產品銷售表”數據--

完成效果為了多角度地分析“農產品銷售表”，駐村工作隊隊員小李用WPS表格制作了數據透視表和數據透視圖，以對數據進行交互式分析。（一）創建數據透視表數據透視表是一種對大量數據進行快速匯總和建立交叉表的交互式表格，用戶可以轉換行以查看數據源的不同匯總結果，也可以選擇顯示不同頁面以篩選數據，還可以根據需要選擇顯示區域中的明細數據。（1）打開“農產品銷售表（透視分析）.et”工作簿，選擇“農產品銷售表”中的A2:K93單元格區域，單擊“插入”選項卡中的“數據透視表”按鈕，打開“創建數據透視表”對話框，保持默認設置，單擊“確定”按鈕，如圖4-51所示。（2）系統將新建一個名為“Sheet1”的工作表，并在工作表中插入空白數據透視表。將工作表重命名為“農產品銷售表透視分析”，并將其移動至“農產品銷售表”之后。（3）在“數據透視表”任務窗格中將“將字段拖動至數據透視表區域”中的“公司”拖曳到“列”字段列表框中，將“產品名稱”拖曳到“行”字段列表框中，將“銷售額（元）”拖曳到“值”字段列表框中，將“月份”拖曳到“篩選器”字段列表框中，如圖4-52所示。（二）美化數據透視表為了美化數