辦公小技巧：Excel給不規範的數據「排排坐」

日常工作中在錄入原始數據的時候，總是有很多新手員工不按規範錄入數據，這樣導致後續需要對數據進行歸類整理、檢索的時候就非常不便。比如人事經常將新分配員工都按每個部門排列在一起，這樣在後續員工考勤、工資表製作時帶來很多不便（圖1）。下面就給大家介紹一種方法，可以快速將不規範的數據整理得井井有條。

圖1 不規範數據

數據橫著坐，分列來相助

對於類似數據之間使用的是統一分割符號，如果只是將數據按照行的方式排列，那麼直接利用Excel的分列功能進行整理即可。

選中B列需要分類的數據，點擊「數據/分列」，選擇「其他」分割符號，選擇「頓號」（如果分列的數據包含多個分隔符如逗號，冒號，可以使用查找替換進行分隔符統一後再用分列），這樣即可將每個名字分列在不同單元格里（圖2）。

圖2 使用分列整理數據

數據豎著排，轉換來變換

上述只是將數據橫著排列，而很多操作需要將人名數據豎著排列，比如製作工資單時的排列，需要每個員工一行，按照各自部門進行縱向排列。如果需要將橫排的數據變換為豎排，那麼可以藉助Excel 2016集成查詢編輯器的「轉換」工具實現。定位到A1，點擊「數據從表格」，在彈出的窗口選擇需要排列數據的A1:B4區域數據（圖3）。

圖3 選擇數據

點擊「確定」，接著在打開的查詢編輯器窗口點擊「轉換「選項卡下，依次單擊「拆分列按分割符」，然後選擇「分隔符/自定義」，輸入「頓號」，使用頓號將數據分列，具體操作和上述分列操作類似（圖4）。

小提示：

這裡使用分列和在工作表中使用分列略有不同。如果需要分列的右側列中還有其他的內容，這裡操作會自動擴展插入新的列，右側已有數據列自動後延。而在工作中使用分列，右側有數據時則會被覆蓋掉。因此在需要對數據進行分列操作，如果原始數據中有較多列的內容（如B列是需要分列內容，C列、D列仍然有其他數據），為了不誤刪原始數據，強烈建議使用查詢編輯器的分列進行操作。

圖4 分列數據

完成數據的分列後，此時B列的數據會自動被分列。繼續按住Ctrl鍵依次選中已經完成的分列數據，點擊「轉換逆透視列逆透視列」，這樣被選擇的列會被自動轉化為行的顯示（圖5）。

圖5 逆透視列操作

完成逆透視列操作後，原來數據會新增一列「屬性「，按提示選中增加的屬性列將其刪除，這樣就完成行列的轉換了（圖6）。

圖6 完成行列的轉換

最後點擊「文件關閉並上載」，這樣轉換後的數據會自動添加到一個新的工作表，所有人名按照列的方式進行排列，這樣很方便對人員進行考勤、工資表的製作帶來便利（圖7）。

圖7 完成行列的轉換

從上述操作可以看到，藉助查詢編輯器的「逆透視列」可以很方便進行行列的轉換。藉助同樣的方法我們可以輕鬆實現數據的轉換。比如很多公司的銷售報表都是二維報表格式，但是使用數據透視表來分析銷售數據的時候，我們卻要求數據源為一維表格的形式（圖8）。

圖8 二維轉一維

對於類似二維表格轉換為一維表格，同樣可以使用「逆透視列」的方法。同上定位到A1，全選需要轉換的數據選擇點擊「數據從表格」，打開查詢編輯器窗口。按住Ctrl鍵選擇「銷售區域」和「城市」列，點擊「轉換逆透視列逆透視其他列」，對銷售數據列進行逆透視操作（圖9）。

圖9 逆透視其他列

這樣數據列的內容會自動由原來的行變為列，同時在A、B列會自動填充對應的銷售區域和城市的數據內容（圖10）。

圖10 逆透視其他列後的效果

其他操作同上，保存並上載後，我們就將原來二維表格轉換為一維表格的內容了。這樣在使用透視表分析數據的時候就非常方便了，比如現在需要比較華東、華北城市的三季度的銷售數據對比，原來的二維表格因為每個季度數據是並列排序不好透視選擇，但是轉換為一維表格後進行簡單篩選顯示即可（圖11）。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！