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

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