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

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

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

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