Excel給不規(guī)范的數(shù)據(jù)“排排坐”

技術(shù)宅

數(shù)據(jù)橫著坐 分列來相助

對于類似數(shù)據(jù)之間使用的是統(tǒng)一分割符號，如果只是將數(shù)據(jù)按照行的方式排列，那么直接利用Excel的分列功能進行整理即可。

選中B列需要分類的數(shù)據(jù)，點擊“數(shù)據(jù)→分列”，選擇“其他”分割符號，選擇“頓號”（如果分列的數(shù)據(jù)包含多個分隔符，如逗號、冒號，可以使用查找替換進行分隔符統(tǒng)一后再用分列），這樣即可將每個名字分列在不同的單元格里（圖2）。

數(shù)據(jù)豎著排 轉(zhuǎn)換來變換

上述只是將數(shù)據(jù)橫著排列，而很多操作需要將人名數(shù)據(jù)豎著排列，比如制作工資單時的排列，需要每個員工一行，按照各自部門進行縱向排列。如果需要將橫排的數(shù)據(jù)變換為豎排，那么可以借助Excel 2016集成查詢編輯器的“轉(zhuǎn)換”工具實現(xiàn)。定位到A1，點擊“數(shù)據(jù)→從表格”，在彈出的窗口選擇需要排列數(shù)據(jù)的A1：B4區(qū)域數(shù)據(jù)（圖3），點擊“確定”。

接著在打開的查詢編輯器窗口點擊“轉(zhuǎn)換”選項卡，依次單擊“拆分列→按分割符”，然后選擇“分隔符→自定義”，輸入“頓號”，使用頓號將數(shù)據(jù)分列，具體操作和上述分列操作類似（圖4）。

完成數(shù)據(jù)的分列后，此時B列的數(shù)據(jù)會自動被分列。繼續(xù)按住Ctrl鍵依次選中已經(jīng)完成的分列數(shù)據(jù)，點擊“轉(zhuǎn)換→逆透視列→逆透視列”，這樣被選擇的列會被自動轉(zhuǎn)換為行的顯示（圖5）。

完成逆透視列的操作后，原來的數(shù)據(jù)會新增一列“屬性”，按提示選中增加的屬性列將其刪除，這樣就完成行列的轉(zhuǎn)換了（圖6）。

最后點擊“文件→關(guān)閉并上載”，這樣轉(zhuǎn)換后的數(shù)據(jù)會自動添加到一個新的工作表，所有人名按照列的方式進行排列，這樣很方便對人員進行考勤，給工資表的制作帶來便利（圖7）。

從上述操作可以看到，借助查詢編輯器的“逆透視列”可以很方便地進行行列的轉(zhuǎn)換。用同樣的方法我們可以輕松實現(xiàn)數(shù)據(jù)的轉(zhuǎn)換。比如很多公司的銷售報表都是二維報表格式，但是使用數(shù)據(jù)透視表來分析銷售數(shù)據(jù)的時候，我們卻要求數(shù)據(jù)源為一維表格的形式（圖8）。

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

這樣數(shù)據(jù)列的內(nèi)容會自動由原來的行變?yōu)榱校瑫r在A、B列會自動填充對應(yīng)的銷售區(qū)域和城市的數(shù)據(jù)內(nèi)容（圖10）。

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