信息技術(shù)基礎(chǔ) 課件 2.4 銷售統(tǒng)計(jì)表制作

電子表格處理

2.4銷售統(tǒng)計(jì)表制作信息技術(shù)基礎(chǔ)—課程資源學(xué)習(xí)目標(biāo)1.知識(shí)目標(biāo)了解數(shù)據(jù)分析的工具。。理解圖表和數(shù)據(jù)透視表的應(yīng)用。2.技能目標(biāo)能選擇合適的公式或函數(shù)進(jìn)行計(jì)算。能排序Excel電子表格的數(shù)據(jù)。能分類匯總Excel電子表格的數(shù)據(jù)。能創(chuàng)建與編輯Excel電子表格的圖表。能制作Excel電子表格的數(shù)據(jù)透視表。3.素質(zhì)目標(biāo)具備數(shù)據(jù)分析能力，具有信息思維和創(chuàng)新精神。增強(qiáng)數(shù)據(jù)可視化表達(dá)能力。案例分析在Excel中要完成該案例需要使用數(shù)據(jù)驗(yàn)證、填充柄、函數(shù)計(jì)算、條件格式等功能，具體操作可分為５個(gè)任務(wù):任務(wù)1：新建工作簿任務(wù)2：編輯數(shù)據(jù)任務(wù)3：計(jì)算數(shù)據(jù)任務(wù)4：跨工作表引用數(shù)據(jù)任務(wù)5：設(shè)置條件格式任務(wù)1完善數(shù)據(jù)任務(wù)說明根據(jù)“商品銷售情況表”中的數(shù)據(jù)。計(jì)算各商品的降價(jià)幅度，降價(jià)幅度=（市場(chǎng)定價(jià)-成交單價(jià)）/市場(chǎng)定價(jià)（數(shù)值百分比格式，保留兩位小數(shù)）；計(jì)算各商品的銷售額置于“銷售額(萬元)”列，銷售額(萬元)=數(shù)量*成交單價(jià)/10000。利用RANK.EQ函數(shù)銷售額排名由高到低的排名。利用IF函數(shù)給出“業(yè)績表現(xiàn)”列的內(nèi)容:如果銷售額大于50萬，在相應(yīng)單元格內(nèi)填入“業(yè)績優(yōu)秀”，如果銷售額大于30萬，在相應(yīng)單元格內(nèi)填入“業(yè)績良好”，如果銷售額大于20萬，在相應(yīng)單元格內(nèi)填入"業(yè)績合格"否則在相應(yīng)單元格內(nèi)填入"業(yè)績差"。任務(wù)1完善數(shù)據(jù)任務(wù)步驟1）計(jì)算各商品的降價(jià)幅度。單擊選定H3單元格→輸入公式“=(E3-G3)/E3”，設(shè)置為百分比，保留兩位小數(shù)。2）計(jì)算各商品銷售額（萬元）。單擊選定J3單元格→在輸入公式“=G3*I3/10000”，保留兩位小數(shù)3）計(jì)算銷售額排名。將光標(biāo)定位到K3單元格→在編輯欄輸入“=RANK.EQ(J3,$J$3:$J$34,0)”。任務(wù)1完善數(shù)據(jù)任務(wù)步驟4）利用if函數(shù)，根據(jù)銷售額計(jì)算業(yè)績表現(xiàn)。將光標(biāo)定位到L3單元格→在編輯欄輸入“=IF(J3>50,"業(yè)績優(yōu)秀",IF(J3>30,"業(yè)績優(yōu)良",IF(J3>20,"業(yè)績合格","業(yè)績差")))”圖1

If函數(shù)流程圖TRUEFALSE開始銷售額>=50TRUEFALSE銷售額>=30銷售額>=20業(yè)績優(yōu)秀業(yè)績優(yōu)良業(yè)績合格業(yè)績差TRUETRUEFALSE任務(wù)1完善數(shù)據(jù)任務(wù)步驟5）復(fù)制“商品銷售情況表”（移至最后）4次，依次命名為“銷售數(shù)據(jù)排序”、“分類匯總數(shù)據(jù)”、“高級(jí)篩選”、“數(shù)據(jù)透視表”。任務(wù)2排序數(shù)據(jù)任務(wù)說明

在“銷售數(shù)據(jù)排序”工作表中，按主要關(guān)鍵字“銷售部門”自定義序列（銷售一部、銷售二部、銷售三部）排序和次要關(guān)鍵字“類別”的降序進(jìn)行排序。本任務(wù)是對(duì)商品銷售數(shù)據(jù)進(jìn)行排序。任務(wù)2排序數(shù)據(jù)任務(wù)步驟1）在“銷售數(shù)據(jù)排序”工作表中，選中A2:L34或單擊選中其中任意一個(gè)單元格。2）對(duì)所選數(shù)據(jù)按照主要關(guān)鍵字“銷售部門”自定義序列（銷售一部、銷售二部、銷售三部）排序圖1“排序”對(duì)話框任務(wù)2排序數(shù)據(jù)任務(wù)步驟3）添加“次要關(guān)鍵字”：類別，并且按照類別降序排序。圖2設(shè)置次要關(guān)鍵字的“排序”對(duì)話框任務(wù)2排序數(shù)據(jù)任務(wù)步驟排序后結(jié)果如圖3所示。圖3排序后結(jié)果任務(wù)3分類匯總?cè)蝿?wù)說明

在對(duì)數(shù)據(jù)進(jìn)行分析時(shí)，可以用Excel提供的“分類匯總”功能，按要求進(jìn)行匯總。在“分類匯總數(shù)據(jù)”工作表中，按主要關(guān)鍵字“銷售部門”自定義序列（銷售一部、銷售二部、銷售三部）排序和次要關(guān)鍵字“類別”的降序進(jìn)行排序，目的是完成數(shù)據(jù)分類，然后按照要求進(jìn)行數(shù)據(jù)分類匯總。本任務(wù)是對(duì)“分類匯總數(shù)據(jù)”工作表中的數(shù)據(jù)按“銷售部門”對(duì)“銷售額（萬元）”進(jìn)行“求和”匯總，并對(duì)匯總數(shù)據(jù)進(jìn)行數(shù)據(jù)圖表分析。任務(wù)3分類匯總?cè)蝿?wù)步驟1）在“分類匯總數(shù)據(jù)”工作表中，按主要關(guān)鍵字“銷售部門”自定義序列（銷售一部、銷售二部、銷售三部）排序和次要關(guān)鍵字“類別”的降序進(jìn)行排序，完成數(shù)據(jù)分類。圖1“排序”完成分類任務(wù)3分類匯總?cè)蝿?wù)步驟2）將光標(biāo)置于數(shù)據(jù)區(qū)域內(nèi)，選擇“數(shù)據(jù)”→“分級(jí)顯示”→“分類匯總”命令，彈出“分類匯總”對(duì)話框。圖2設(shè)置“分類匯總”圖3“分類匯總”后結(jié)果任務(wù)3分類匯總?cè)蝿?wù)步驟3）查看匯總結(jié)果4）去掉分類匯總的顯示信息，可再次選擇“數(shù)據(jù)”→“分級(jí)顯示”→“分類匯總”命令，在彈出的“分類匯總”對(duì)話框中單擊“全部刪除”按鈕。5）插入圖表分析數(shù)據(jù)。單擊工作表的左上方符號(hào)“2”，按住【Ctrl】鍵的同時(shí)選中“銷售部門”數(shù)據(jù)和“銷售額（萬元）”的匯總結(jié)果，按【Alt】+【；】組合鍵。任務(wù)3分類匯總?cè)蝿?wù)步驟6）選擇“插入”→“圖表”→“柱形圖”→“簇狀柱形圖”命令，即可生成圖表。7）美化圖表。修改圖表標(biāo)題為“各銷售部門銷售額對(duì)比”，添加“數(shù)據(jù)標(biāo)簽”。添加主要縱坐標(biāo)標(biāo)題“單位（萬元）”。8）將水平（分類）軸標(biāo)簽修改為“銷售一部”“銷售二部”“銷售三部”任務(wù)3分類匯總?cè)蝿?wù)步驟9）設(shè)置繪圖區(qū)填充效果為“花束”的紋理填充。10）將圖表插入到“分類匯總數(shù)據(jù)”工作表的“B40:F55”單元格區(qū)域內(nèi)。調(diào)整圖表大小并移動(dòng)到B40:F55單元格區(qū)域內(nèi)。11）結(jié)果如圖1所示圖1“各銷售部門銷售額對(duì)比”圖表任務(wù)4設(shè)置高級(jí)篩選任務(wù)說明高級(jí)篩選無疑是針對(duì)自動(dòng)篩選而言的，高級(jí)篩選具有效率高，方便，易操作等特點(diǎn)。本任務(wù)是在“高級(jí)篩選"工作表中，對(duì)工作表內(nèi)數(shù)據(jù)清單的內(nèi)容進(jìn)行高級(jí)篩選(在數(shù)據(jù)清單前插入四行，條件區(qū)域設(shè)在A1∶L3單元格區(qū)域，請(qǐng)?jiān)趯?duì)應(yīng)字段列內(nèi)輸入條件)，條件是:銷售部門為“銷售一部”或“銷售三部”且銷售額排名在10（包含10）到20（包含20）之間，篩選結(jié)果復(fù)制到A42開始的單元格區(qū)域，工作表名不變，保存“銷售數(shù)據(jù)表.xlsx工作薄。任務(wù)4設(shè)置高級(jí)篩選任務(wù)步驟1）切換到“高級(jí)篩選'工作表→選中A1:A4單元格區(qū)域，并單擊鼠標(biāo)右鍵→選擇“插入…”→在插入對(duì)話框中→選擇“整行”→單擊確定按鈕，在數(shù)據(jù)清單前插入四行。2）設(shè)置“高級(jí)篩選”的條件區(qū)域，如圖1所示。圖1“高級(jí)篩選”的條件區(qū)域任務(wù)4設(shè)置高級(jí)篩選任務(wù)步驟3）在“數(shù)據(jù)”功能區(qū)→“排序和篩選”組中→單擊“高級(jí)”按鈕→彈出“高級(jí)篩選”對(duì)話框→設(shè)置“列表區(qū)域”→$A$6:$L$38→設(shè)置“條件區(qū)域”為“$B$1:$K$3”→→復(fù)制到（T）→高級(jí)篩選!“$A$42:$K$3”→單擊【確定】按鈕,最終篩選結(jié)果如圖2所示。4）保存并關(guān)閉“銷售數(shù)據(jù)表.xlsx”工作薄。圖2“高級(jí)篩選”結(jié)果任務(wù)5創(chuàng)建數(shù)據(jù)透視表任務(wù)說明數(shù)據(jù)透視表是一種交互式的表，可以進(jìn)行某些計(jì)算，如求和與計(jì)數(shù)等。在EXCEL中可以動(dòng)態(tài)地改變它們的版面布置，以便按照不同方式分析數(shù)據(jù)，如果原始數(shù)據(jù)發(fā)生更改，則可以更新數(shù)據(jù)透視表。選擇“數(shù)據(jù)透視表”工作表，對(duì)工作表內(nèi)數(shù)據(jù)清單的內(nèi)容建立數(shù)據(jù)透視表，篩選器為“銷售部門”，按行為“類別”，數(shù)據(jù)為“銷售額(萬元)”求和布局，利用“數(shù)據(jù)透視表樣式淺色9"修飾圖表，添加“鑲邊行”和“鑲邊列”將數(shù)據(jù)透視表置于現(xiàn)有工作表的N2單元格，保存數(shù)據(jù)銷售表.XLSX工作薄任務(wù)5創(chuàng)建數(shù)據(jù)透視表任務(wù)步驟1）選擇“數(shù)據(jù)透視表”工作表，將光標(biāo)置于數(shù)據(jù)區(qū)域中，選擇“插入”→“表格”→“數(shù)據(jù)透視表”→“數(shù)據(jù)透視表”命令，彈出“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，如圖1所示。2）“選擇放置數(shù)據(jù)透視表的位置”，點(diǎn)擊“現(xiàn)有工作表”，位置里單擊選擇“N2”單元格，單擊“確定”按鈕。圖1“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框任務(wù)5創(chuàng)建數(shù)據(jù)透視表任務(wù)步驟3）拖動(dòng)“數(shù)據(jù)透視表字段列表”中的“銷售部門”字段拖到下方“篩選器”中。按照同樣的方法，分別將“類別”字段拖到“行標(biāo)簽”區(qū)域，將“銷售額”拖動(dòng)2次到“數(shù)值”區(qū)域，設(shè)置“數(shù)據(jù)透視表字段”后的效果如圖2所示。4）修改數(shù)據(jù)透視表標(biāo)題行名稱。雙擊“行標(biāo)簽”，修改為“類別”。圖2設(shè)置“數(shù)據(jù)透視表字段”任務(wù)5創(chuàng)建數(shù)據(jù)透視表任務(wù)步驟5）選中N3:P7，單擊選擇“數(shù)據(jù)透視表工具”→“設(shè)計(jì)”選項(xiàng)卡→“數(shù)據(jù)透視表樣式”組→選擇“數(shù)據(jù)透視表樣式淺色9"修飾圖表→并在“數(shù)據(jù)透視表樣式選項(xiàng)”組中，并勾選“鑲邊行”和“鑲邊列"。6）選中N3:P6單元格區(qū)域，單擊選擇“數(shù)據(jù)透視表工具”→“分析”選項(xiàng)卡→“工具”組→“數(shù)據(jù)透視圖”命令→選擇“圖表類型”→“簇狀柱形圖”→單擊“確定”按鈕。任務(wù)5創(chuàng)建數(shù)據(jù)透視表任務(wù)步驟7）選中“圖表”，單擊選擇“數(shù)據(jù)透視圖工具”→“設(shè)計(jì)”選項(xiàng)卡→“類型”組→“修改圖表類型”命令。8）打開“更改圖表類型”對(duì)話框→選擇“組合”圖表類型→單擊“商品銷售占比”右側(cè)的下拉小三角→選擇圖表