資料分析中的行列的快速轉換方法

日常生產計劃在工作需要對資料分析的時候，遇到一些從ERP匯出的資料，行列的位置非常不標準，如資料在一列，或者在一行；此時如果不對這些資料進行整理的話，想要得到分析結果的非常難。

今天分享的一個案例為某工廠的資料庫匯出的供應商明細表，全部在一列上面了，這樣的話如果領導要求你統計有多少供應商，或者以此為資料庫存進行查詢的時候，會有很多不方便。我們的目標是整理成標準的資料透視格式：

01 操作法：

用操作法是沒有辦法的臨時替代方法，假設這時你對於函式公式也不太懂，領導要求馬上把資料要整理好，最“笨”的方法就是用操作法，也就是複製貼上，此時操作也需要注意觀察；

上面的聯絡方式是有規律的，供應商公司名、地址、電話形成一個迴圈，我們可以透過加輔助列的方法來快速分離。

在邊上加上一列輔助列，錄入1、2、3，按住Ctrl鍵向下拖拽滑鼠的資料結尾，填充一列，123，123，……迴圈的數字列，再分別篩選1、2、3到複製貼上對應的列；

最後透過查詢與替換把“地址”替換成空，選中需要整理的資料，把“電話：（025），替換成“025-”，就得到完成結果。這樣雖然慢一點，但也算是完成領導的任務；

02 函式法：

用函式來解決這類問題，是佳方案，根據不同的office版本有不同的解決方案，越高階的版本越快速，這裡分享幾個版本的解決方案；

Office 2007版本：

用函式INDEX+ROW函式解決，INDEX可以返回指定的行列，利用ROW可以生成規律的數字，透過兩個函式組合，可以快速對資料的行列進行轉換；

透過輔助列發現，供應商名都在1、4、7行，是一個3的迴圈，用函式=ROW（A1）*3-2，向下填充可以得到，同時邊上的分別是：=ROW（A1）*3-1、=ROW（A1）*3；有了個幾號就可以配合INEDX得到下面的結果

錄入公式後：=INDEX（$B$46：$B$60，E54），向下向右填充公式，然後再重複操作法的最後一步，把地址和電話分別替換成對應的標準格式；

Office 2021版本：

用函式FILTER加輔助列搞定，FILTER函式是按條件篩選，既然這個資料有規律是按123，123，……迴圈的數字列，只需要用函式篩選1、2、3到對應的目標區域就可以了，這個函式相當於替代了操作法一一複製貼上了；

=FILTER（$B$67：$B$81，$C$67：$C$81=E65），函式的意思就是分別按1、2、3篩選，這裡因為要向右填充，引用區域一定要鎖定；

Office 365版本：

強烈推薦，不需要輔助列，不需要額外操作，只需要錄入函式：

=WRAPROWS（B88：B102，3），一秒解決，公式的意思很簡單，第一個引數就是需要轉換的區域，第二個引數轉成多少列，這裡是3的迴圈，所以輸入3列，如是4的迴圈，數字變更為4就可以了；

如果需要外再用公式替換地址和電話，把公式變成：

=SUBSTITUTE（SUBSTITUTE（WRAPROWS（B4：B18，3），“地址：”，“”），“電話：（025）”，“025-”）

用SUBSTITUTE函式進行兩次替換成空即可得到最終的結果；

03 總結：

在進行資料分析，多多注意資料的規律，找到一些“特定”的規律，再配合函式公式，可以快速的得到想要的整理結果，另外，更新Office版本也是一個非常不錯的辦法，一般情況下，最新的版本的功能越強大，對應的解決方案也相對簡單；

我是古哥：

從事製造行業18年，在企業運營、供應鏈管理、智慧製造系統等方面具有豐富的實戰經驗。企業智慧化，柔性化計劃運營管理專家，擅長透過企業流程最佳化規範，企業管理、匯入計劃運營提升企業效率；對提高企業準時交貨率，降低企業庫存，輸出智慧製造人才有豐富的經驗。學習PMC生產計劃，關注古哥計劃！