在日常工作中，有時會遇到系統導出來的多個字段數據，隻在同一列的情況。比如下圖所示：

示例中，左邊數據每四行為一條完整的記錄，比如 2-5 行，6-9 行……我們需要将它轉成右邊的格式，才方便進行常規的統計分析。

解決這種數據一列轉多列的問題，最開始我使用的是 VBA，但學了 PQ 之後，點點鼠标就可以完成啦！

PQ 鼠标操作法

❶ 打開數據文件，添加數據到 Power Query 編輯器。

具體操作步驟：

① 将鼠标定位在數據區域的任意單元格，單擊【數據】選項卡；

② 選擇【自表格/區域】，Excel 會自動擴展選區；

③ 如果彈出的對話框，勾選「表包含标題」，單擊【确定】即可。

PS：【自表格/區域】在不同版本中，可能被稱為【從表格】。

❷ 通過觀察可以看到，交易數據列名和内容通過冒号分隔開，因此先按分隔符拆分列。

❸ 接下來是添加「索引列」，并以「交易數據.1」為依據，對「交易數據.2」進行透視。

具體操作步驟：

① 單擊「添加列」—「索引列」，選擇「從 0 開始」；

② 選中「交易數據.1」，單擊「轉換」—「透視列」，值列選擇「交易數據.2」，高級選項選擇「不要聚合」。

透視以後的數據變化很大。

雖然結果和我們想要的格式更進一步了，但是每條記錄數據都被行列交錯分布開來了。

這時我們需要利用「向上填充」和「篩選」進一步清洗。

❹ 選中「産品」、「金額」、「銷售人」三列，向上填充已有數據。

具體操作步驟：

① 單擊【産品】列，向右拖動下方的滾動條到末尾；

② 按住 【Shift】 鍵盤，再次單擊末尾列的标題，這樣就選中了最後三列；

③ 單擊【轉換】選項卡—【填充】，從下拉選項中選擇【向上】。

完成填充以後數據表如下：

已經非常接近幹淨的數據了。

❺ 最後，将「機構列」的空值（null）篩選掉，并且删除「索引列」就可以得到想要的數據表。

具體操作步驟：

① 單擊「機構」列，點擊該列右上角的【篩選】按鈕；

② 從彈出的窗口中取消勾選「null」 ；

③ 單擊「索引列」，單擊鼠标右鍵，選擇【删除】。

點擊「關閉并上載」，将數據上載到 Excel 中就可以啦！

公式 Plus 法

像這種有規律地把表格中的多個單元格分成一組；每組在不同行中，并且在列的方向按次序保存的情況。

也可以使用拉登老師介紹過的 P_INDEX 函數解決。

❶ 先使用常規的分列功能，将交易數據按「中文的冒号」分隔。

❷ 使用 P_INDEX 函數，輸入相應參數獲取【機構】列數據。

❸ 需要将公式往右邊填充，可以使用 Column 函數實現動态引用。

最終使用到的公式為：

=P_INDEX($B$2,$B$6,$B$10,1,100,ROW(A1),COLUMN(A1)

總結一下

現在，我們來将兩種方法對比一下~

鼠标操作法學會菜單功能就行了。主要記住以下幾個要點：

❶ 按分隔符分列；

❷ 添加索引列作為透視列；

❸ 向上填充，注意「機構」列不填充；

❹ 篩選去除空行（null）。

公式 Plus 法的幾個要點：

❶ 需要先下載「公式 Plus」插件，本文文末留言獲取；

❷ 按分隔符拆分列；

❸ 使用 P_INDEX 自定義函數；

❹ 配合使用 Row 及 Column 函數實現動态引用。

今日内容分享就到這裡，我們下期再見~

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!