Excel函數操作題第16套

Excel函數操作題第16套

某公司銷售部門主管大華擬對本公司產品前兩季度的銷售情況進行統計，按下述要求幫助大華完成統計工作：

1. 在考生文件夾下，將“Excel素材.xlsx”文件另存為“Excel.xlsx”(“.xlsx”為擴展名)，后續操作均基于此文件，否則不得分。

2. 參照“產品基本信息表”所列，運用公式或函數分別在工作表“一季度銷售情況表”、“二季度銷售情況表”中,填入各型號產品對應的單價，并計算各月銷售額填入F列中。其中單價和銷售額均為數值、保留兩位小數、使用千位分隔符。(注意：不得改變這兩個工作表中的數據順序)

3. 在“產品銷售匯總表”中,分別計算各型號產品的一、二季度銷量、銷售額及合計數，填入相應列中。所有銷售額均設為數值型、小數位數0，使用千位分隔符，右對齊。

4. 在“產品銷售匯總表”中，在不改變原有數據順序的情況下，按一二季度銷售總額從高到低給出銷售額排名，填入I列相應單元格中。將排名前3位和后3位的產品名次分別用標準紅色和標準綠色標出。

5. 為“產品銷售匯總表”的數據區域A1:I21套用一個表格格式，包含表標題，并取消列標題行的篩選標記。

6. 根據“產品銷售匯總表”中的數據，在一個名為“透視分析”的新工作表中創建數據透視表，統計每個產品類別的一、二季度銷售及總銷售額，透視表自A3單元格開始、并按一二季度銷售總額從高到低進行排序。結果參見文件“透視表樣例.png”。

7. 將“透視分析”工作表標簽顏色設為標準紫色，并移動到“產品銷售匯總表”的右側。

對 應 步 驟

1、復制Excel素材，粘貼一份，重命名為Excel。不用輸入擴展名，打開Excel做題即可。

2、題中要求產品信息表添加一二季度對應的單價，我們可以利用型號查詢對應的單價即可。

用vlookup查找函數即可，

vlookup函數參數(查找的數據，查找的范圍，返回的值在范圍的第幾列，精確查找寫0)

查找的數據為產品型號：

查找的范圍為B2:C21(在這里可能有同學會把A列也選上，這是不對的，vlookup函數的限制是查找的數據必須位于查找范圍的第一列，比如我們查找的是產品型號，那么產品型號必須位于范圍的第一列。)

最后返回的單價值在范圍的第2列，所以輸入2，精確查找就寫0

結果如下：=VLOOKUP(B2,產品基本信息表!$B$2:$C$21,2,0)

最后銷售額=銷量*單價

二季度表方法和上面一樣，結果如圖。

最后不要忘記把這兩個表的單價和銷售額的列數據，格式設置成保留兩位小數的數值，勾選使用千位分隔符

3、如圖，求一季度各個產品的銷量總和。用條件求和函數即可，條件為產品型號。

條件求和函數sumif，sumif函數參數(條件區域，條件，求和區域)

條件區域為各個產品型號的區域，區域是固定不變的需要鎖定

條件為產品型號，

求和區域為銷量區域，區域是固定不變的需要鎖定

最終結果如圖：

=SUMIF(一季度銷售情況表!$B$2:$B$44,產品銷售匯總表!B2,一季度銷售情況表!$D$2:$D$44)

同理一季度的銷售額也這么求，不過求和區域不是銷售量，而是一季度表中銷售額的一列

同理，二季度和二季度的銷售額也用這個方法，用二季度表的數據做即可，結果如圖：

一二季度銷售總量=一季度銷量+二季度銷量

一二季度銷售總額=一季度銷售額+二季度銷售額

最后按ctrl鍵選中銷售額的數據，右擊設置單元格格式→數值，0位小數，勾選使用千位分隔符，并且右對齊

4、排名用rank排名函數即可

rank函數參數(排名的數據，排名的區域)

排名的區域是固定不變的需要鎖定

設置條件格式的題目要求是前三名是紅色字體，后3名是綠色字體。

但是如果我們直接去設置的話，前三名不是1，2，3，而是最大的數字如18,19,20

所以我們需要反著設置。

選中排名所在的區域→條件格式→項目選取規則→后10項

改為后3項→自定義格式為字體下的標準紅色→確定

同理，還是選中排名的區域→條件格式→項目選取規則→前10項→改為前3項→自定義格式為字體下的標準綠色

5、選中A1：I21區域，套用表格樣式，表包含標題確定，隨便選擇一個樣式即可,

切換到數據選項卡，保存篩選標記不選中，如果選中單擊一下即可

6、光標定位于產品銷售匯總表的任意一個單元格，插入→數據透視表

然后重命名此表為透視分析

行標簽是產品類別代碼，值為一季度銷售額，二季度銷售額，一二季度銷售總額

光標定位銷售總額的單元格→開始選項卡→排序和降序→降序

選擇數據透視表的數據區域→右擊設置單元格格式→數值，0位小數，勾選使用千位分隔符

選中D3單元格，在編輯欄內更改名字為兩個季度銷售總額

最后把透視分析表移動到產品銷售匯總表的右側，右擊更改標簽顏色為紫色

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。