《物流數(shù)據(jù)分析》課件-任務(wù)3.1 數(shù)據(jù)管理分析

任務(wù)3.1數(shù)據(jù)管理分析【任務(wù)目的】1.掌握數(shù)據(jù)清單的概念；2.掌握數(shù)據(jù)排序和篩選操作；3.掌握數(shù)據(jù)分類匯總操作；4.掌握合并計(jì)算的操作。【工作任務(wù)】建立如圖所示的數(shù)據(jù)清單，并按要求完成排序、篩選、分類匯總、合并計(jì)算等操作。1.優(yōu)先按照部門升序，再按薪水降序排序2.篩選北京分公司軟件部薪水高于5000的員工3.篩選出小時(shí)報(bào)酬在35以上或者薪水在5000以上的員工4.按部門統(tǒng)計(jì)“工作時(shí)數(shù)”和“薪水”的平均值5.

將1分店和2分店的銷售數(shù)據(jù)表進(jìn)行合并【知識(shí)鏈接】數(shù)據(jù)清單是一種包含一行列標(biāo)題和多行數(shù)據(jù)且同列數(shù)據(jù)的類型和格式完全相同的Excel工作表，構(gòu)建數(shù)據(jù)清單時(shí)要遵循以下原則：（1）列標(biāo)志應(yīng)位于數(shù)據(jù)清單的第一行，用以查找和組織數(shù)據(jù)、創(chuàng)建報(bào)表；（2）同一列中數(shù)據(jù)項(xiàng)的類型和格式應(yīng)當(dāng)完全相同；（3）避免在數(shù)據(jù)清單中間放置空白的行或列，但需將數(shù)據(jù)清單和其他數(shù)據(jù)隔開時(shí)，應(yīng)在它們之間留出至少一個(gè)空白的行或列；（4）不得在數(shù)據(jù)清單內(nèi)出現(xiàn)任何合并單元格；（5）盡量在一張工作表上建立一個(gè)數(shù)據(jù)清單。2.Excel中常用數(shù)據(jù)管理功能數(shù)據(jù)排序：是指在不影響數(shù)據(jù)內(nèi)容的前提下，改變數(shù)據(jù)的順序，以將關(guān)注的數(shù)據(jù)以顯眼的方式進(jìn)行顯示。數(shù)據(jù)篩選：是指在指定列上設(shè)置過濾條件，將不滿足過濾條件的數(shù)據(jù)進(jìn)行隱藏，只顯示滿足條件的數(shù)據(jù)的操作，可以讓用戶更聚焦要關(guān)注的數(shù)據(jù)。分類匯總：是指將數(shù)據(jù)按照特定的分類依據(jù)（例如按部門、按性別等）將數(shù)據(jù)分為多個(gè)子集，再對(duì)每個(gè)子集進(jìn)行統(tǒng)計(jì)計(jì)算的操作。合并計(jì)算：是指按照項(xiàng)目的匹配，對(duì)同類數(shù)據(jù)進(jìn)行匯總，匯總的方式包括求和、計(jì)數(shù)、平均值、最大值、最小值等等。【實(shí)施步驟】1.建立數(shù)據(jù)清單2.數(shù)據(jù)排序3.數(shù)據(jù)篩選4.分類匯總5.合并計(jì)算1.建立數(shù)據(jù)清單參照下圖，在工作表中輸入數(shù)據(jù)建立數(shù)據(jù)清單，輸入時(shí)要注意日期的規(guī)范輸入，如果表中日期格式不規(guī)范，該如何修改？2.數(shù)據(jù)排序（1）簡(jiǎn)單排序：按薪水降序排序簡(jiǎn)單排序是指以單列為排序依據(jù)進(jìn)行排序。在H列的任意位置單擊，然后單擊“數(shù)據(jù)”選項(xiàng)卡下的“降序”按鈕，即可完成排序。注意：如果某些工作表最后還存在“合計(jì)”或者“平均值”等行，在排序之前必須先選中排序區(qū)域A1：H21，避免“合計(jì)”等行參與排序。將排序后的工作表另存為“簡(jiǎn)單排序.xlsx”工作簿文件。（2）多關(guān)鍵字排序：優(yōu)先按部門升序，再按薪水降序排序重新打開“員工薪水.xlsx”工作簿，選擇A1：H21單元格區(qū)域，單擊“數(shù)據(jù)”|“排序和篩選”組的“排序”命令，在如左圖所示的“排序”對(duì)話框中，設(shè)置排序依據(jù)的列和次序，排序后的效果如右圖所示。將排序后的工作表另存為“多關(guān)鍵字排序.xlsx”。3.數(shù)據(jù)篩選所謂篩選是指僅顯示需要關(guān)注的數(shù)據(jù)，不需要關(guān)注的數(shù)據(jù)隱藏起來。篩選的關(guān)鍵在于定義篩選條件。（1）按選定單元格進(jìn)行篩選：篩選出北京分公司的數(shù)據(jù)行重新打開“員工薪水.xlsx”工作簿，右擊D列中任意一個(gè)值為“北京”的單元格，在彈出的快捷菜單中選擇“篩選”，在子菜單中選擇“按所選單元格的值篩選”，即可完成篩選。取消篩選：?jiǎn)螕簟皵?shù)據(jù)”選項(xiàng)卡下的“篩選”按鈕，即可取消篩選，數(shù)據(jù)恢復(fù)到全部顯示的初始狀態(tài)。（2）多條件簡(jiǎn)單篩選：篩選出在北京分公司軟件部工作，并且薪水高于5000元的員工選擇A1：H21單元格，單擊“數(shù)據(jù)”選項(xiàng)卡下的“篩選”，此時(shí)列標(biāo)題出現(xiàn)下拉箭頭，單擊“部門”下拉組合框，勾選“軟件部”，單擊“分公司”下拉組合框，勾選“北京”，單擊“薪水”下拉組合框，選擇“數(shù)字篩選”中“自定義篩選”，在如左圖所示的對(duì)話框中輸入“大于或等于”和“5000”。篩選后的效果如右圖所示。將篩選后的工作表另存為“多條件簡(jiǎn)單篩選.xlsx”。（3）高級(jí)篩選：篩選出小時(shí)報(bào)酬在35以上或者薪水在5000以上的員工高級(jí)篩選主要解決簡(jiǎn)單篩選無法實(shí)現(xiàn)的情況，例如條件涉及到多列，但是條件之間用“或”連接，即求并集的情況。簡(jiǎn)單篩選中先后在多列上設(shè)置篩選條件，實(shí)質(zhì)上是求交集，解決的是多個(gè)條件之間用“且”連接的問題。高級(jí)篩選必須首先編輯篩選條件。重新打開“員工薪水.xlsx”工作簿，選中數(shù)據(jù)列表下方的任意單元格，開始編輯篩選條件，例如在A23：B25單元格區(qū)域中，編輯如圖所示的篩選條件。注意：由于本題兩個(gè)條件是“或

”連接，實(shí)質(zhì)是求兩個(gè)條件的并集，故兩個(gè)條件錯(cuò)行放置，如果是“且”連接，其實(shí)質(zhì)是求兩個(gè)條件的交集，則需要放在同行。單擊“數(shù)據(jù)”選項(xiàng)卡下“排序和篩選”組中的“高級(jí)”按鈕，彈出“高級(jí)篩選”對(duì)話框，將篩選“方式”設(shè)置為“將篩選結(jié)果復(fù)制到其他位置”（因?yàn)楦呒?jí)篩選無法還原，防止丟失原始數(shù)據(jù)），“列表區(qū)域”輸入A1：H21單元格區(qū)域，“條件區(qū)域”輸入A23:B25單元格區(qū)域，“復(fù)制到”輸入A27，表示從A27單元格開始顯示篩選結(jié)果，點(diǎn)擊“確定”進(jìn)行篩選，篩選結(jié)果如圖所示，將篩選后結(jié)果另存為“高級(jí)篩選.xlsx”。思考與點(diǎn)撥：高級(jí)篩選的條件區(qū)域如何編輯？高級(jí)篩選的條件區(qū)域中同行條件表示條件之間是“且”的關(guān)系，需要兩個(gè)條件同時(shí)滿足；不同行條件之間是“或”的關(guān)系，只滿足一個(gè)條件即可。4.分類匯總（1）按部門統(tǒng)計(jì)“工作時(shí)數(shù)”和“薪水”的平均值打開“員工薪水.xlsx”工作簿，選擇A1：H21單元格，單擊“數(shù)據(jù)”|“排序和篩選”菜單的“排序”命令，設(shè)置部門按照升序排序（降序也可）。注意：分類匯總之前都需要對(duì)分類依據(jù)的列進(jìn)行排序，其目的是將相同值的行集中到一起。選擇A1：H21單元格，單擊“數(shù)據(jù)”|“分級(jí)顯示”菜單的“分類匯總”命令，彈出如左圖所示的“分類匯總”對(duì)話框；在該對(duì)話框中設(shè)置分類字段為“部門”，匯總方式為“平均值”，選定匯總項(xiàng)為“工作時(shí)數(shù)”和“薪水”；再選定“替換當(dāng)前分類匯總”和“匯總結(jié)果顯示在數(shù)據(jù)下方”。選定完成后單擊“確定”按鈕，系統(tǒng)自動(dòng)按照部門分類匯總，計(jì)算工作時(shí)數(shù)和薪水的平均值，分類匯總后的效果如右圖所示。將工作簿另存為“分類匯總.xlsx”。（2）分級(jí)顯示只顯示總計(jì)平均值，忽略部門差異：?jiǎn)螕舴诸悈R總結(jié)果行號(hào)左側(cè)的按鈕“1”，只顯示一級(jí)匯總結(jié)果，如圖所示。按部門顯示匯總結(jié)果：?jiǎn)螕舴诸悈R總結(jié)果行號(hào)左側(cè)的按鈕“2”，可顯示各部門匯總情況及總計(jì)平均值，如圖所示。單擊“2”下方的“+”號(hào)按鈕，可以展開對(duì)應(yīng)部門的明細(xì)，單擊“-”號(hào)按鈕則可以實(shí)現(xiàn)折疊顯示。顯示數(shù)據(jù)明細(xì)：?jiǎn)螕舴诸悈R總結(jié)果行號(hào)左側(cè)的按鈕“3”，則可以顯示各級(jí)匯總情況和數(shù)據(jù)明細(xì)，如圖所示。（3）刪除分類匯總將前面所做的分類匯總刪除：?jiǎn)螕舴诸悈R總區(qū)域的任意單元格，單擊“數(shù)據(jù)”選項(xiàng)卡下的“分類匯總”按鈕，在彈出的“分類匯總”對(duì)話框中點(diǎn)擊“全部刪除”，即可刪除分類匯總，數(shù)據(jù)恢復(fù)到初始狀態(tài)。5.合并計(jì)算數(shù)據(jù)合并可以把來自不同源數(shù)據(jù)區(qū)的數(shù)據(jù)進(jìn)行匯總，并進(jìn)行合并計(jì)算。請(qǐng)?jiān)谕还ぷ鞑局蟹謩e設(shè)計(jì)“1分店銷售數(shù)量統(tǒng)計(jì)表”、“2分店銷售數(shù)量統(tǒng)計(jì)表”、“合并銷售數(shù)據(jù)”三個(gè)工作表，如左圖、中圖、右圖所示。選中“合并銷售數(shù)據(jù)”工作表的B3：D6單元格區(qū)域，選擇“數(shù)據(jù)”選項(xiàng)卡下“數(shù)據(jù)工具”組中的“合并計(jì)算”命令，彈出如圖所示的“合并計(jì)算”對(duì)話框。在對(duì)話框“函數(shù)”下拉列表中選擇“求和”，在對(duì)話框“引用位置”參數(shù)中選擇“一分店銷售數(shù)據(jù)”工作表中的B3：D6區(qū)域，單擊“添加”按鈕，再選取“二分店銷售數(shù)據(jù)”的B3：D6區(qū)域（選擇“瀏覽”可以選取不同工作簿中的引用位置），單擊“添加”按鈕，選中“創(chuàng)建指向源數(shù)據(jù)的鏈接”（當(dāng)源數(shù)據(jù)變化時(shí)，合并計(jì)算結(jié)果也隨之變化），單擊“確定”完成合并計(jì)算。合并計(jì)算后的工作表如右圖所示。【任務(wù)小結(jié)】數(shù)據(jù)排序、篩選、分類匯總等操作都是建立在數(shù)據(jù)清單基礎(chǔ)上的，數(shù)據(jù)清單僅由列標(biāo)題和數(shù)據(jù)行構(gòu)成，原則上不能對(duì)表格進(jìn)行合并操作。排序通常是對(duì)行排序，但也可以對(duì)列進(jìn)行排序。篩選可以分為簡(jiǎn)單篩選和高級(jí)篩選，高級(jí)篩選可以實(shí)現(xiàn)更為復(fù)雜的篩選邏輯，但需要自定義篩選條件區(qū)域。分類匯總是日常工作中極為實(shí)用的一種數(shù)據(jù)處理方式，需要注意的是分類匯總之前需要先按照分類依據(jù)進(jìn)行排序?！緦?shí)踐訓(xùn)練】請(qǐng)?jiān)赟heet1中制作如圖所示的表格，然后完成下列要求。1.將數(shù)據(jù)復(fù)制到Sheet2中，并將工作表命名為“按列排序”。按照“基本工資”從高到低排序，如果“基本工資”相同，則按照“獎(jiǎng)金”從高到低排序，如果“獎(jiǎng)金”相同，則按照“車費(fèi)補(bǔ)助”從高到低排序。2.將數(shù)據(jù)復(fù)制到Sheet3中，并將工作表命名為“按行排序”。改變列順序，要求列標(biāo)題從左至右按照首字的漢語拼音升序排序。3.將數(shù)據(jù)復(fù)制到Sheet4中，并將工作表命名為“簡(jiǎn)單篩選”。篩選出基本工資在2500元及以上，3000元以下并且獎(jiǎng)金在400元及以上的員工信息。4.將數(shù)據(jù)復(fù)制到Sheet5中，并將工作表命名為“高級(jí)篩選”。篩選出基本工資大于等于3000或者是獎(jiǎng)金大于等于400的員工信息。5.將數(shù)據(jù)復(fù)制到Sheet6中，并將工作表命名為“分類匯總”。按部門匯總出基本