編按：

當(dāng)遇到不規(guī)范數(shù)據(jù)源時，所有常規(guī)操作都瞬間無效，這是讓每個exceler都頭大的問題。因此，重新整理不規(guī)范數(shù)據(jù)源就成了大家必須掌握的excel技巧之一。今天，小E為大家?guī)淼木褪莈xcel中最強的數(shù)據(jù)整理技巧——power query！讓它幫你完成數(shù)據(jù)清洗和后續(xù)數(shù)據(jù)核算，很輕松！

小伙伴們，大家好！今天帶來一個數(shù)據(jù)清洗的教程。

從后期數(shù)據(jù)分析的角度來說，大家都希望數(shù)據(jù)源越規(guī)范越好。數(shù)據(jù)規(guī)范的最基本原則更是必須遵守，即數(shù)值和文本不能放在一個單元格里。但是，筆者今天還是收到了一個不懂規(guī)則的“豬”隊友提交的表格：

問題是分析每個人的平均分。比如張三的平均分算式是(97+92)/2，李四的平均分算式是(85+69+38)/3……依次類推。

但是筆者面對這樣格式雜亂的數(shù)據(jù)表格，感覺到了棘手，本來已經(jīng)熟練應(yīng)用的函數(shù)也不能馬上套用了。先壓壓驚，筆者想到了excel中的數(shù)據(jù)清洗工具——power query。

小貼士：power query是2016及以上的office excel才內(nèi)置的功能，16版本下使用需要安裝插件。

Step 01

用鼠標點擊數(shù)據(jù)源任意位置，然后點擊“數(shù)據(jù)”選項卡下的“自表格/區(qū)域”，將數(shù)據(jù)加載到power query編輯器。

Step 02

在power query編輯器頁面，用鼠標點擊“主頁”，選擇“拆分列”選項卡下的“按分隔符”。

在彈出的界面中，選擇“自定義”，并在下方輸入框中輸入相應(yīng)的符號。因為這里的姓名和分數(shù)之間是用頓號隔開的，所以在輸入框中輸入頓號。然后點擊“確定”即可。

這樣便將人名和分數(shù)分開了，效果如下：

Step 03

拆分分數(shù)列。

用鼠標點擊分數(shù)列，用鼠標點擊“主頁”，選擇“拆分列”選項卡下的“按分隔符”（和步驟2相同）。然后在彈出的界面中，在符號自定義處輸入“-”，在拆分位置中選擇“每次出現(xiàn)分隔符時”，在高級選項下選擇“行”，最后點擊確定。

這時，數(shù)據(jù)已經(jīng)清洗到比較規(guī)范的程度了，如下圖：

Step 04

有了規(guī)范的數(shù)據(jù)后計算平均分。用鼠標點擊 “主頁”下的“分組依據(jù)”選項卡。在彈出的對話框中，分別設(shè)置“分數(shù).1”、“平均分”、“平均值”、“分數(shù).2”。

分析：

“分數(shù).1”對應(yīng)的是數(shù)據(jù)源的人名列；“平均分”是新列名；“平均值”表示要對分數(shù)列做的計算類型，此時是計算“平均值”；“分數(shù).2”對應(yīng)的是數(shù)據(jù)源的分數(shù)列。

將4個要素選擇完畢后點“確定”。所有人的分數(shù)的平均值就算出來啦。

如果沒有后續(xù)數(shù)據(jù)分析應(yīng)用需求，大家可以把鼠標移動到“平均分”列上，點擊鼠標右鍵，選擇“刪除其他列”，即只保留平均分列。

Step 05

將power query界面關(guān)閉，并上載至現(xiàn)有工作表。然后選擇原始數(shù)據(jù)源“分數(shù)”列右側(cè)的單元格，用鼠標點擊“確定”即可。

最后便將計算后的平均分加載到了數(shù)據(jù)源旁邊。

注意：

如果后續(xù)有數(shù)據(jù)新增或者改動，只需要右鍵刷新即可。

小伙伴們學(xué)會了嗎？power query是數(shù)據(jù)清洗利器，適當(dāng)?shù)臅r候使用可以事半功倍哦。

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

Excel運用規(guī)范1：一個單元格只記錄一條信息

只再說最后一次：Excel數(shù)據(jù)源表家規(guī)

快速整理不規(guī)范的Excel表格的7個公式

Excel和Word強強聯(lián)手,整理不規(guī)范數(shù)據(jù)，快如閃電!

版權(quán)申明：

本文作者過兒；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。