第4單元Excel數(shù)據(jù)加工與處理知識(shí)目標(biāo)（1）掌握Excel中數(shù)據(jù)驗(yàn)證、刪除重復(fù)值等數(shù)據(jù)審核的操作方法（2）熟練掌握Excel中數(shù)據(jù)自動(dòng)篩選與高級(jí)篩選的操作方法（3）熟練掌握Excel中數(shù)據(jù)分類匯總的操作方法（4）熟練掌握Excel中數(shù)據(jù)透視表的操作方法（5）熟練掌握Excel中合并計(jì)算的操作方法技能目標(biāo)素養(yǎng)目標(biāo)學(xué)習(xí)目標(biāo)（1）能夠運(yùn)用Excel中數(shù)據(jù)驗(yàn)證、刪除重復(fù)值等數(shù)據(jù)工具進(jìn)行數(shù)據(jù)審核（2）能夠運(yùn)用Excel中數(shù)據(jù)篩選、分類匯總、合并計(jì)算等數(shù)據(jù)工具靈活處理數(shù)據(jù)（3）能夠生成數(shù)據(jù)透視表，從不同維度分析數(shù)據(jù)（1）堅(jiān)守?cái)?shù)據(jù)分析師的職業(yè)底線，堅(jiān)守誠信和透明原則（2）遵守?cái)?shù)據(jù)倫理和法律規(guī)定，合法合規(guī)處理數(shù)據(jù)（3）培養(yǎng)實(shí)事求是、嚴(yán)謹(jǐn)細(xì)致的工作作風(fēng)和精益求精的工匠精神目錄CONTENTS數(shù)據(jù)審核01數(shù)據(jù)篩選02分類匯總03數(shù)據(jù)透視表合并計(jì)算0405案例引入萬物互聯(lián)互通的時(shí)代下，數(shù)字經(jīng)濟(jì)迅猛發(fā)展。人工智能和大數(shù)據(jù)多維度賦能實(shí)體經(jīng)濟(jì)，線上線下融合等新消費(fèi)模式日益發(fā)展。黨和國家對(duì)數(shù)字經(jīng)濟(jì)高度支持，工信部加快5G技術(shù)與新型消費(fèi)模式的結(jié)合，鼓勵(lì)了我國直播帶貨行業(yè)的發(fā)展與開拓，全國已經(jīng)產(chǎn)生數(shù)字化驅(qū)動(dòng)的消費(fèi)市場，包括電商、實(shí)體商業(yè)直播帶貨以及遍及全國的“村播”。然而，在這個(gè)蓬勃的發(fā)展過程中，我們也發(fā)現(xiàn)了一些需要關(guān)注和改進(jìn)的問題。在直播帶貨，譬如主播的虛假宣傳、選品的質(zhì)量不均、售后保障缺失、摻假售假等，已引起了廣泛關(guān)注。一些主播夸大帶貨效果的行為，雖然在一定程度上取得了短期的銷售成績，但這并不符合長遠(yuǎn)發(fā)展的可持續(xù)性原則。為了促使行業(yè)更加透明和健康，我們需要加強(qiáng)監(jiān)管，鼓勵(lì)主播和商家遵循公平競爭的原則，共同創(chuàng)造一個(gè)誠信、高效的直播帶貨環(huán)境。引思明理數(shù)據(jù)造假不僅損害了數(shù)據(jù)分析的可信度和聲譽(yù)，也對(duì)個(gè)人和組織的利益產(chǎn)生嚴(yán)重影響。政府部門應(yīng)建立有效的數(shù)據(jù)驗(yàn)證和審查機(jī)制，建立反制數(shù)據(jù)造假行為的機(jī)制，保護(hù)數(shù)據(jù)的完整性，促進(jìn)可信、可靠的數(shù)據(jù)分析。作為數(shù)據(jù)分析師，應(yīng)該做到：（1）堅(jiān)守誠信和透明原則，實(shí)事求是，堅(jiān)持以數(shù)據(jù)說話，堅(jiān)定數(shù)據(jù)分析師的職業(yè)操守，用真實(shí)數(shù)據(jù)客觀反映真相，不隨意篡改數(shù)據(jù)，嚴(yán)謹(jǐn)細(xì)致對(duì)待數(shù)據(jù)。（2）遵守倫理和法律規(guī)定，提升對(duì)數(shù)據(jù)倫理和數(shù)據(jù)質(zhì)量的認(rèn)識(shí)，合法合規(guī)處理和使用數(shù)據(jù)。4.1數(shù)據(jù)審核01數(shù)據(jù)審核的類型有效性審核主要是檢查數(shù)據(jù)的有效性。一致性審核主要檢查數(shù)據(jù)之間的一致性問題，一致性審核可以基于不同問題或同一問題的不同部分之間的結(jié)構(gòu)關(guān)系，邏輯性和合法性來進(jìn)行。分布性審核主要是試圖通過數(shù)據(jù)的分布，來辨識(shí)記錄是否遠(yuǎn)遠(yuǎn)脫離分布的正常范圍，即是否為離群值。分布性審核主要是用來發(fā)現(xiàn)和確認(rèn)可疑的數(shù)據(jù)記錄。分布性審核一致性審核有效性審核數(shù)據(jù)有效性驗(yàn)證Excel中的“數(shù)據(jù)驗(yàn)證”功能為數(shù)據(jù)提供數(shù)據(jù)區(qū)間、數(shù)據(jù)類型等簡單審核。例如，對(duì)員工基本資料表中的出生年月進(jìn)行簡單審核，審核條件為：在冊員工出生年月在“1958-1-1”至“1999-12-31”之間。操作步驟如下。（1）選中需要驗(yàn)證的數(shù)據(jù)區(qū)域，選擇“數(shù)據(jù)”→“數(shù)據(jù)工具”→“數(shù)據(jù)驗(yàn)證”選項(xiàng)，彈出“數(shù)據(jù)驗(yàn)證”對(duì)話框。（2）選擇“數(shù)據(jù)”→“數(shù)據(jù)工具”→“數(shù)據(jù)驗(yàn)證”→“圈釋無效數(shù)據(jù)”選項(xiàng)，如圖所示，結(jié)果如圖，可將不符合要求的數(shù)據(jù)顯示出來。（3）在數(shù)據(jù)輸入前，也可以先對(duì)單元格內(nèi)容的取值范圍進(jìn)行設(shè)置，同時(shí)可以設(shè)置“輸入信息”“出錯(cuò)警告”等信息。設(shè)置完成后，再次輸入數(shù)據(jù)時(shí)會(huì)出現(xiàn)標(biāo)簽提示，如輸入的數(shù)據(jù)不合理可彈出警告等，如圖所示。數(shù)據(jù)有效性驗(yàn)證數(shù)據(jù)重復(fù)值的處理Excel中提供了“刪除重復(fù)值”功能，該功能可以將所選區(qū)域中存在的重復(fù)值刪除。選中需要?jiǎng)h除重復(fù)值的單元格，單擊“數(shù)據(jù)”→“數(shù)據(jù)工具”→“刪除重復(fù)值”按鈕，即可刪除重復(fù)值。1．利用“排序”功能尋找重復(fù)值數(shù)據(jù)重復(fù)值的處理單擊“數(shù)據(jù)”→“排序和篩選”→“排序”按鈕，彈出“排序”對(duì)話框，如圖所示。如果要找出員工基本資料表中重復(fù)的員工姓名，可以按照關(guān)鍵字“姓名”進(jìn)行排序，將相同姓名的員工放在一起，以便于辨別重復(fù)值。在排序時(shí)，可以根據(jù)實(shí)際情況選擇排序依據(jù)。排序依據(jù)可選擇的有單元格值、單元格顏色、字體顏色、條件格式圖標(biāo)。在對(duì)文本進(jìn)行排序時(shí)，單擊“選項(xiàng)”按鈕，彈出“排序選項(xiàng)”對(duì)話框，選擇的排序方法可以是“字母”或“筆畫”。2．利用“條件格式”尋找重復(fù)值數(shù)據(jù)重復(fù)值的處理Excel中的“條件格式”是指使用數(shù)據(jù)條、色階或圖標(biāo)集輕松地瀏覽趨勢和模式，以直觀地顯示和突出重要值?？梢詥螕簟伴_始”→“樣式”→“條件格式”按鈕，彈出菜單，選擇“突出顯示單元格規(guī)則”→“重復(fù)值”命令，如圖所示，這樣會(huì)彈出“重復(fù)值”對(duì)話框，可以設(shè)置將重復(fù)值以特殊格式顯示。3．利用"函數(shù)計(jì)算"尋找重復(fù)值數(shù)據(jù)重復(fù)值的處理用戶可用函數(shù)來計(jì)算重復(fù)值，COUNTIF函數(shù)可以計(jì)算單元格內(nèi)容出現(xiàn)的次數(shù)，為此，我們可以用該函數(shù)統(tǒng)計(jì)每個(gè)單元格內(nèi)容出現(xiàn)的次數(shù)，如圖所示。計(jì)算客戶編號(hào)中有無重復(fù)值，利用函數(shù)COUNTIF($A$3:$A$11,A3)計(jì)算每個(gè)客戶編號(hào)出現(xiàn)的次數(shù)，可知編號(hào)"20180008"出現(xiàn)了2次。要記錄每個(gè)編號(hào)第幾次出現(xiàn)，可利用函數(shù)COUNTIF($A$3:A3,A3)，如圖所示?！?0180008”出現(xiàn)2次，可以將出現(xiàn)次數(shù)大于1的數(shù)據(jù)刪除。1．更改顯示格式，修復(fù)部分?jǐn)?shù)據(jù)缺失數(shù)據(jù)的處理Excel自定義數(shù)字格式的格式串最多可由4個(gè)區(qū)段組成，各部分之間用分號(hào)隔開，如圖所示。

區(qū)段數(shù)代碼結(jié)構(gòu)1格式代碼作用于所有類型的數(shù)值2第1區(qū)段作用于正數(shù)和零值，第2區(qū)作用于負(fù)數(shù)3第1區(qū)段作用于正數(shù)，第2區(qū)段作用于負(fù)數(shù)，第3區(qū)段作用于零值4分別作用于正數(shù)、負(fù)數(shù)、零值和文本

在數(shù)字格式字符串中，“#”表示只顯示有意義的數(shù)字；“，”為千分位分隔符；“0”表示數(shù)字占位符，如果單元格的內(nèi)容大于占位符，則顯示實(shí)際數(shù)字，如果小于占位符的數(shù)量，則用0補(bǔ)足；“*”表示重復(fù)下一次字符，直到充滿列寬；“@”表示以文本形式顯示。2．確定性插補(bǔ)數(shù)據(jù)缺失數(shù)據(jù)的處理插補(bǔ)方法可分為兩類，一是確定性插補(bǔ)，二是隨機(jī)性插補(bǔ)。確定性插補(bǔ)可采用的方法很多，一般可使用均值插補(bǔ)、推理插補(bǔ)、回歸插補(bǔ)和熱平臺(tái)插補(bǔ)等多種方法。（1）均值插補(bǔ)：用插補(bǔ)類的均值代替缺失值。（2）推理插補(bǔ)：通過對(duì)已有數(shù)據(jù)進(jìn)行推理，來確定插補(bǔ)的值。（3）回歸插補(bǔ)：使用輔助信息及其他記錄中的有效數(shù)據(jù)建立一個(gè)回歸模型，該模型表明兩個(gè)或多個(gè)變量之間的關(guān)系。（4）熱平臺(tái)插補(bǔ)：使用同一插補(bǔ)類中的供者記錄的信息來代替一個(gè)相似的受者記錄中缺失的或不一致的數(shù)據(jù)。

2．確定性插補(bǔ)數(shù)據(jù)缺失數(shù)據(jù)的處理如需插補(bǔ)D167單元格的數(shù)據(jù)，D167是2005年江蘇大豆的畝產(chǎn)數(shù)據(jù)，可以通過表中各字段的關(guān)系推斷出D167=D166*(1+G167),則利用公式很容易得出數(shù)據(jù)值約為151.1484。3．隨機(jī)性插補(bǔ)數(shù)據(jù)如需插補(bǔ)D167單元格數(shù)據(jù)，即2005年江蘇省大豆單位畝產(chǎn)，縱觀江蘇、安徽、山東3省的2005年數(shù)據(jù)可知，2005年是減產(chǎn)的，安徽減產(chǎn)在24%左右，山東減產(chǎn)在8%左右。為此，可利用隨機(jī)插補(bǔ)形式，即利用D167=D166*(1-INT(RAND()*(24-8)+8)/100)可以得到一個(gè)接近值隨機(jī)數(shù)。離群值的處理離群值是指在數(shù)據(jù)中有一個(gè)或幾個(gè)與其他數(shù)值相比差異較大的值。在進(jìn)行數(shù)據(jù)預(yù)處理時(shí)，應(yīng)該先檢測離群值，再進(jìn)行相應(yīng)的處理。一般處理的方法有：（1） 刪除：最簡單的方法以就掐頭去尾，將離群值去掉；（2） 調(diào)整權(quán)數(shù)：降低離群值的權(quán)數(shù)，使它們的影響變小。4.2數(shù)據(jù)篩選02（1）選中需要篩選的數(shù)據(jù)，單擊“數(shù)據(jù)”→“排序與篩選”→“篩選”按鈕，可看到數(shù)據(jù)表頭字段名上出現(xiàn)下拉框，如圖所示。

自動(dòng)篩選（2）如果篩選條件是“男性、姓李、80后員工”，則單擊“性別”下拉框，彈出下拉列表框，選擇“男”，如圖所示；單擊“姓名”下拉框，彈出下拉列表框，選擇“文本篩選”→“開頭是”選項(xiàng)，如圖4所示，彈出“自定義自動(dòng)篩選方式”對(duì)話框，設(shè)置開頭是“李”即可，如圖所示；單擊“出生年月”下拉框，彈出下拉列表框，選擇“日期篩選”→“自定義篩選”選項(xiàng)，如圖所示，彈出“自定義自動(dòng)篩選方式”對(duì)話框，選擇“在以下日期之后或與之相同”選項(xiàng)，輸入“1980-1-1”，如圖所示。最終的篩選結(jié)果如圖所示。

自動(dòng)篩選自動(dòng)篩選適合單一條件或多個(gè)條件的綜合，高級(jí)篩選可以實(shí)現(xiàn)復(fù)雜多條件的篩選。“高級(jí)篩選”的操作要點(diǎn)是：對(duì)“條件區(qū)域”的設(shè)置。“條件區(qū)域”設(shè)置：高級(jí)篩選“或”的關(guān)系“與”的關(guān)系單擊"數(shù)據(jù)"→"排序和篩選"→"高級(jí)"按鈕，彈出"高級(jí)篩選"對(duì)話框，如圖所示，"方式"是指篩選的結(jié)果顯示的位置，"列表區(qū)域"是指需要篩選的原數(shù)據(jù)表，"條件區(qū)域"是要求篩選的條件表達(dá)區(qū)域，"復(fù)制到"是指結(jié)果顯示的位置。該案例顯示的結(jié)果如圖所示。高級(jí)篩選例如：需要篩選出所有女性員工或者1986年以后出生的員工。4.3分類匯總03例如，以某小家電商城的銷售數(shù)據(jù)為例，統(tǒng)計(jì)每日銷售商品的數(shù)量與金額，具體步驟如下。（1）根據(jù)題意對(duì)數(shù)據(jù)按下單日期進(jìn)行數(shù)據(jù)排序，使得同一天的銷售數(shù)據(jù)在一起；（2）分類匯總，單擊“數(shù)據(jù)”→“分級(jí)顯示”→“分類匯總”按鈕，彈出“分類匯總”對(duì)話框，要求匯總每日的銷售數(shù)量與金額，則分類字段為“下單日期”，匯總方式是“求和”，選定匯總項(xiàng)是“數(shù)量”與“金額”，如圖所示，替換當(dāng)前分類匯總，結(jié)果顯示在數(shù)據(jù)下方，統(tǒng)計(jì)結(jié)果如圖所示。統(tǒng)計(jì)結(jié)果最左邊可見分類匯總分3級(jí)，如圖所示是2級(jí)，1級(jí)只顯示總的匯總結(jié)果，3級(jí)顯示全部的內(nèi)容。分類匯總分類匯總是Excel提供的分類、統(tǒng)計(jì)計(jì)算相關(guān)數(shù)據(jù)行的工具。通過分類匯總與總計(jì)可快速計(jì)算相關(guān)數(shù)據(jù)行。如果要再細(xì)分，匯總出“每天每種商品的銷售量與銷售額”，可以采用嵌套的分類匯總，此時(shí)需要在原先分類匯總的基礎(chǔ)上進(jìn)行二次分類匯總，分類字段選擇“產(chǎn)品名稱”，在“分類匯總”對(duì)話框中取消選擇“替換當(dāng)前分類匯總”復(fù)選框，如圖所示，匯總結(jié)果如圖所示。此處經(jīng)過兩次分類匯總，分4級(jí)顯示。分類匯總分類匯總操作的注意點(diǎn)：（1）在進(jìn)行分類匯總的操作時(shí)，一定要讓源數(shù)據(jù)先按照分類字段排序后，再分類匯總，這樣才能達(dá)到分類匯總的目的。（2）如需根據(jù)多個(gè)分類字段進(jìn)行分類匯總時(shí)，二級(jí)分類匯總時(shí)，選擇項(xiàng)中不能勾選“替換當(dāng)前分類匯總”，在原來分類匯總的基礎(chǔ)上繼續(xù)分類匯總。分類匯總4.4數(shù)據(jù)透視表04

數(shù)據(jù)透視表是Excel提供的一種交互式報(bào)表，可以根據(jù)不同的分析目的組織和匯總數(shù)據(jù)，使用起來更加靈活，可以得到需要的分析結(jié)果，是一種動(dòng)態(tài)數(shù)據(jù)分析工具。數(shù)據(jù)透視表以某線上商城的銷售數(shù)據(jù)為例，數(shù)據(jù)透視表具體操作如下。（1）選擇需要建立數(shù)據(jù)透視表的數(shù)據(jù)，單擊“插入”→“表格”→“數(shù)據(jù)透視表”按鈕，彈出“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，根據(jù)實(shí)際情況選擇數(shù)據(jù)透視表放置的位置，單擊“確定”按鈕，進(jìn)入數(shù)據(jù)透視表的設(shè)置界面。數(shù)據(jù)透視表以某線上商城的銷售數(shù)據(jù)為例，數(shù)據(jù)透視表具體操作如下。（2）在右側(cè)的“數(shù)據(jù)透視表字段”面板中列出了源數(shù)據(jù)中每列的名稱。選中各字段，默認(rèn)文本字段出現(xiàn)在“行”標(biāo)簽中，數(shù)值出現(xiàn)在“值”字段中，用戶可以直接拖動(dòng)字段放置到“篩選器”“列”“行”“值”等位置上。拖動(dòng)的結(jié)果顯示的是每天每種商品的銷量與銷售額。數(shù)據(jù)透視表以某線上商城的銷售數(shù)據(jù)為例，數(shù)據(jù)透視表具體操作如下。（3）“行”標(biāo)簽、“列”標(biāo)簽中的字段可以互換，“值”字段中可以切換值匯總的方式，如求和、計(jì)數(shù)、平均值、最大值等。在“值”字段處單擊，在彈出菜單中選擇“值字段設(shè)置”，彈出“值字段設(shè)置”對(duì)話框，如圖所示，可切換“值”字段的匯總方式，也可修改值顯示方式，如圖所示，“金額”值顯示方式為“父級(jí)匯總的百分比”，可以顯示每天每類商品銷售金額占比。數(shù)據(jù)透視表以某線上商城的銷售數(shù)據(jù)為例，數(shù)據(jù)透視表具體操作如下。（4）“篩選器”區(qū)域可以添加不同維度的數(shù)據(jù)透視，比如需要查看每個(gè)省份的數(shù)據(jù)，可以將“客戶省份”拖動(dòng)到“篩選器”區(qū)域，綜上，形成如圖4-38所示的數(shù)據(jù)透視。4.5合并計(jì)算05合并計(jì)算Excel提供的合并計(jì)算功能可以匯總報(bào)表不同單元格區(qū)域中的數(shù)據(jù)，在單個(gè)輸出區(qū)域中顯示合并計(jì)算結(jié)果，能夠幫助用戶將指定單元格區(qū)域中的數(shù)據(jù)進(jìn)行同類匯總計(jì)算。例：將兩個(gè)銷售渠道的銷售金額進(jìn)行合并，求出總銷售金額，具體步驟如下。（1）選擇合并計(jì)算結(jié)果顯示位置，如單元格A11。（2）單擊“數(shù)據(jù)”→“數(shù)據(jù)工具”→“合并計(jì)算”按鈕，彈出“合并計(jì)算”對(duì)話框，如圖所示。合并計(jì)算（3）在“函數(shù)”中選擇“求和”，并添加“所有引用位置”，選擇兩個(gè)不同銷售渠道的數(shù)據(jù)進(jìn)行添加，如圖所示。（4）設(shè)置標(biāo)簽位置，如選擇“最左列”“首行”復(fù)選框，則表示最左列、首行原樣顯示。（5）合并計(jì)算的結(jié)果如圖所示，可根據(jù)實(shí)際需求修改其字段名稱，如圖所示。

任務(wù)描