前幾天有個朋友問了這樣一個問題,是關於數據清洗與轉化的。
如下圖所示,是原有的數據結構,是非常地不合理與規範的數據結構:
需要將上面的數據整理成如下圖所示的規範的數據:
對於上面的這個問題,處理的首先的方法考慮Power Qeury或者VBA的方法,那麼本節案例中將採用的是Excel中自帶的數據清洗功能Power Qeury.
主要思路:先添加一個自定列為當前行,再將record轉化成Table,然後兩兩拆分後進行合併,轉化成Record結構後再展開即可。
具體的操作步驟如下:
Step-01:先將原始數據加載至Power Query中,如圖所示:
Step-02:在Excel Power Query編輯器中使用Table.AddColumn添加一個自定義列,引用到當前的行再轉換成表,再使用Table.ColumnNames獲取數據源中的標題行,然後使用List.FirstN獲取前6行即可。
Step-03:接著面的步驟,將上面的自定義中的每一個table裡面的表降標題處理。如下圖所示:
再將每一列轉化成一個List,可以使用Table.ToRecord函數如下圖所示:
Step-04:使用List.Split函數將每兩個List拆分為一個List,如下圖所示:
使用List.Transfrom函數與List.Combine函數將每個List中的兩個List的內容進行合併,如圖所示:
Step-05:最後再次使用List.Transform函數來構造Record結構用於展開。
Step-06:最後層層展開,刪除沒用的列即可。如圖所示:
最終的代碼如下圖所示:
當然還可以使用Table.Group函數進行清理,具體代碼如下圖所示:
閱讀更多 Excel聚焦 的文章