案例10：淘寶店寶貝銷售情況

辦公軟件高級應用案例10淘寶店寶貝銷售情況分析知識要點Excel函數(shù)查找和引用函數(shù)文本函數(shù)邏輯（條件）函數(shù)及的嵌套時間與日期函數(shù)財務函數(shù)排位函數(shù)數(shù)據(jù)填充數(shù)組公式的使用分類匯總數(shù)據(jù)有效性數(shù)據(jù)透視表和數(shù)據(jù)透視圖任務描述淘寶網(wǎng)中某商店寶貝銷售記錄是網(wǎng)絡銷售最終用電子表格Excel以流水帳的形式記錄、保存和處理。通過Excel2003提供的功能進行銷售金額、折扣率等的計算，對銷售情況進行統(tǒng)計和分析，如商品結構分析、各省份銷售量分析和分析一年中的旺銷季節(jié)，根據(jù)財務分析知道自己的貸款每月還貸金額。商家可根據(jù)分析的結果調整商品結構和營銷策略，以求得投資的最大效益。同時根據(jù)客服電話迅速查詢到顧客的具體情況，縮短了響應時間，提高顧客的滿意度。具體要求任務1：數(shù)據(jù)填充使用VLOOKUP函數(shù)，對Sheet1中的“寶貝單價”列進行填充。要求：根據(jù)Sheet2中的“寶貝價格表”價格，使用VLOOKUP函數(shù)對“寶貝銷售記錄表”中的“寶貝單價”列根據(jù)不同的寶貝標題進行填充。注意：函數(shù)中如果需要用到絕對地址的將使用絕對地址進行計算，其他方式無效。具體要求任務2：使用數(shù)組公式計算在Sheet1中使用數(shù)組公式計算寶貝的銷售金額。要求：金額=寶貝數(shù)量×寶貝單價，并將“寶貝銷售記錄表”復制到Sheet3任務3：使用LEFT函數(shù)與分類匯總在Sheet3中完成以下操作：將銷售金額列的數(shù)值數(shù)據(jù)內容清除，重新使用金額=寶貝數(shù)量×寶貝單價（不使用數(shù)組公式計算，因數(shù)組公式計算后數(shù)據(jù)清單中記錄不能移動）。使用函數(shù)和選擇性粘貼將收貨地址只顯示前三個漢字（省分名），并將“寶貝銷售記錄表”復制到Sheet4。使用分類匯總功能按各省份對寶貝數(shù)量和銷售金額進行匯總。具體要求任務4：數(shù)據(jù)有效性設置在Sheet2的H2單元格中設置為只能錄入5位數(shù)字或文本。當錄入位數(shù)錯誤時，提示錯誤原因，樣式為“警告”，錯誤信息為“只能錄入5位數(shù)字或文本”。任務5：使用財務函數(shù)PMT、IPMT根據(jù)Sheet2中的貸款情況，使用財務函數(shù)對貸款償還金額進行計算。要求：計算“按年償還還款金額（年末）”，將結果填入Sheet2中的I13單元格中。計算“第9個月貸款利息金額”，并將結果填入到Sheet2中I14單元格中。具體要求任務6：IF函數(shù)的嵌套和高級篩選在Sheet4中完成以下操作：在銷售金額列后增加一列“打折后金額”，并根據(jù)Sheet2中的“折扣率表”的折扣率計算打折后金額填入相應單元格（保留2位小數(shù)）。對“寶貝銷售記錄表”進行高級篩選，篩選條件為“收貨人姓名”→李惜，“聯(lián)系手機”篩選結果保存放在啟始位置是Sheet4中A208單元格。具體要求任務7：創(chuàng)建數(shù)據(jù)透視圖和使用RANK函數(shù)排名根據(jù)Sheet4中的“寶貝銷售記錄表”創(chuàng)建一個數(shù)據(jù)透視圖Chart1。顯示各月份各省的銷售金額的匯總。將對應的數(shù)據(jù)透視表保存在Sheet5中。在Sheet5數(shù)據(jù)透視表中的總計行后增加一行，該行第1列輸入“銷售排名”并使用RANK函數(shù)對各省寶貝的銷售金額進行排名，并將結果保存在“銷售排名”行中。知識拓展知識點1：財務函數(shù)的使用財務函數(shù)是財務計算和財務分析的專業(yè)工具，有了這些函數(shù)，可能很方便地解決復雜的財務運算，在提高財務工作效率的同時，更有效地保障了財務數(shù)據(jù)計算的準確性。下面我們再介紹幾種常用的處理財務中相關的函數(shù)。使用FV函數(shù)計算投資未來收益值FV函數(shù)是基于固定利率及等額分期付款方式，返回某項投資的未來值，其完整格式為：FV（rate,nper,pmt,pv,type）其中：rate表示的是各期利率，例如當利益為8%時，使用8%/4計算一個季度的還款額；nper表示總投資期，即該項投資總的付款期數(shù)；pmt表示各期支出金額，在整個投資期內不變；pv表示從該項投資開始計算時已經(jīng)入賬的款項，或一系列未來付款當前值的累積和。如果忽略，pv=0；type數(shù)值0或1，指定付款時間是期初還是期末。1=期初，0或忽略=期末。使用SLN函數(shù)計算設備每日、每月、每年的折舊值SLN函數(shù)計算機的是某項資產在一個期間中的線性折舊值，其完整格式為：SLN（cost,salvage,life）其中：cost表示的是資產原值；salvage表示的是資產在折舊期末的價值，即資產殘值；life表示折舊期限，即資產的使用壽命。知識拓展知識點2：分類匯總分類匯總是對數(shù)據(jù)列表指定的行或列中的數(shù)據(jù)進行匯總統(tǒng)計，統(tǒng)計的內容可以由用戶指定，通過折疊或展開行、列數(shù)據(jù)和匯總結果。從匯總和明細兩種角度顯示數(shù)據(jù)，可以快捷地創(chuàng)建各種匯總報告。在創(chuàng)建分類匯總之前，首先要保證要進行分類匯總的數(shù)據(jù)區(qū)域必須是一個連續(xù)的數(shù)據(jù)區(qū)域，而且每個數(shù)據(jù)列都有列標題；然后必須對要進行分類匯總的列進行排序。這個排序的列標題稱為分類匯總的關鍵字，分類匯總時只能指定排序后的列標題為匯總關鍵字。我們還可以對多項指標進行匯總，并且可以進行嵌套分類匯總。操作步驟為執(zhí)行一次分類匯總后再執(zhí)行一次分類匯總。我們只需將“替換當前分類匯總”的勾去除就可以。知識拓展知識點3：數(shù)據(jù)有效性對于需要經(jīng)常處理數(shù)據(jù)的人們來說，Excel的“數(shù)據(jù)有效性”功能應該掌握。數(shù)據(jù)有效性用于定義可以在單元格中輸入或應該在單元格中輸入哪些數(shù)據(jù)。您可以配置數(shù)據(jù)有效性以防止用戶輸入無效數(shù)據(jù)。如果愿意，您可以允許用戶輸入無效數(shù)據(jù)，但當用戶嘗試在單元格中鍵入無效數(shù)據(jù)時會向其發(fā)出警告。此外，您還可以提供一些消息，以定義您期望在單元格中輸入的內容，以及幫助用戶更正錯誤的說明。數(shù)據(jù)有效性的功能，可以限制單元格的內容為指定整數(shù)、指定小數(shù)、指定日期段，指定時間段、指定文本長度、指定內容或指點定公式結果。數(shù)據(jù)輸入限制為下拉列表中的值給單元格添加標注自動實現(xiàn)輸入法中英文轉換數(shù)據(jù)唯一性檢驗知識拓展知識點4：數(shù)據(jù)透視表數(shù)據(jù)透視表是交互式報表，它集排序、篩選、分類匯總與一體，可快速合并和比較大量數(shù)據(jù)。您可旋轉其行和列以看到源數(shù)據(jù)的不同匯總，而且可顯示感興趣區(qū)域的明細數(shù)據(jù)。數(shù)據(jù)透視表的格式可以修改，像格式化工作表一樣，將數(shù)據(jù)透視表的格式設置成各種需要的樣式。用戶可以使用“自動套用格式”命令將Excel內置的數(shù)據(jù)透視表格式應用于選中的數(shù)據(jù)透視表。我們只需選定數(shù)據(jù)透視表，單擊“數(shù)據(jù)透視表”工具欄中的“設置報告格式”按鈕，在對話框中選定要應用的格式就可以。小結本實訓是從淘寶網(wǎng)中的網(wǎng)絡銷售商店利用網(wǎng)絡渠道得到的顧客真實銷售數(shù)據(jù)，