問題求助SOS：如何將一列含有多組的層級數(shù)據(jù)，重新分組？
小編馬上去翻了翻以往的文章，發(fā)現(xiàn)有一篇，其中的知識點可以幫助到我解決問題： 這個excel戶籍關系姓名轉(zhuǎn)置問題，就算不在居委會工作，也能輕松應對 這篇文章中每個“戶主”對應的“家庭成員”可以變相的理解為“多組的層級數(shù)據(jù)”，但是這篇文章所介紹的一級標題數(shù)據(jù)是“戶主”，二級所屬數(shù)據(jù)是“家庭成員”，且每組的“戶主”在上，“家庭成員”數(shù)據(jù)在下，每組都是這個特點，一級標題數(shù)據(jù)是“不同姓名”，二級所屬數(shù)據(jù)是“電話號碼（數(shù)量不等）”，且每組的“姓名”在下，“電話”數(shù)據(jù)在上。雖然一二級數(shù)據(jù)上下方向略有差異，但是難度卻上升了好幾個等級。A列是一列分組信息。我們發(fā)現(xiàn)這一列信息是分層分等級的，一級分層是“各個姓名”，二級分層是各個姓名所屬的“電話號碼”，每組信息都是由“數(shù)量不等的電話號碼”+單個姓名組成，若干組這樣的數(shù)據(jù)排成了一列。有一個特點：每組的電話在上方，姓名在下方，某個人電話的數(shù)量可能不止一個，這也是難點所在。我們想要將這A列數(shù)據(jù)重新分組，布局重構，變成右表這種多行形式數(shù)據(jù)，每行為一組數(shù)據(jù)，姓名對應一個電話或多個電話。首先我們使用REGEXP正則表達式函數(shù)：=REGEXP($A$2:A2,"[一-龜]+",1) 我們在動態(tài)逐漸擴展的$A$2:A2區(qū)域內(nèi)（開始單元格凍結，結束單元格隨公式下拉填充逐漸擴大），判斷每個擴展區(qū)域內(nèi)是否是文本（姓名漢字），這樣會形成由邏輯值TRUE與FALSE組成的數(shù)組溢出判斷結果。FALSE表示判斷結果為非文本姓名（本例中特指電話數(shù)字）。=REGEXP($A$2:A2,"[一-龜]+",1)*1將邏輯值TRUE和FALSE轉(zhuǎn)換為數(shù)字1或0。由于REGEXP返回的每個擴展區(qū)域的判斷結果為縱向數(shù)組溢出，所以我們需要將其轉(zhuǎn)置為橫向的數(shù)組溢出，使用TRANSPOSE轉(zhuǎn)置函數(shù)：=TRANSPOSE(REGEXP($A$2:A2,"[一-龜]+",1)*1)這樣做的目的就是可以在得到B2返回結果后，直接下拉填充公式，獲取$A$2:A2~$A$2:A11所有各個擴展區(qū)域下的判斷結果。接著我們對各個擴展區(qū)域內(nèi)的判斷結果求和：=SUM(TRANSPOSE(REGEXP($A$2:A2,"[一-龜]+",1)*1))便可得到一列序號，也就是動態(tài)逐漸擴展的$A$2:A2區(qū)域內(nèi)，文本值（姓名值）各個的總個數(shù)。=IF(ISTEXT(A2),"",SUM(TRANSPOSE(REGEXP($A$2:A2,"[一-龜]+",1)*1)))ISTEXT(A2)：判斷A列的每個單元格是否為文本，如果是，返回TRUE，否則返回FALSE。IF函數(shù)：如果A列單元格是文本（姓名）的話，我們返回空值，否則返回上一步公式的返回結果。驚喜的發(fā)現(xiàn)：電話部分各組之間留下的序號正好是分組序號：同一組之間序號相同，不同組別之間序號遞增（從0開始）。繼續(xù)使用REGEXP正則表達式函數(shù)：=TOCOL(REGEXP(A2:A11,"[一-龜]+"),2) 將上一步返回的數(shù)組溢出提取結果繼續(xù)轉(zhuǎn)置為一列，且轉(zhuǎn)置時忽略錯誤值。那么就會剩下純姓名部分。 =FILTER(A$2:A$11,B$2:B$11=ROW(A1)-1)ROW(A1)-1會獲得0~n（等差為1）的行號。然后我們分別篩選當B$2:B$11區(qū)域的序號分別為0~n時，A$2:A$11區(qū)域?qū)碾娫捫畔ⅰ?/span>由于上一步FILTER返回的篩選結果為縱向數(shù)組溢出，為了可以下拉填充公式，我們需要使用TRANSPOSE函數(shù)轉(zhuǎn)置：=TRANSPOSE(FILTER(A$2:A$11,B$2:B$11=ROW(A1)-1))這樣縱向數(shù)組溢出轉(zhuǎn)置為橫向數(shù)組溢出，直接下拉填充公式就可以得到所有的分組電話了。學習Excel，如果你沒有天賦，那就一直重復，當你快到本能反應的時候，你的重復就是別人眼中的天賦，沖破捆綁，展翅翱翔。回顧關鍵內(nèi)容，善用圖片表達，學會建立聯(lián)系，拓展深度廣度，濃縮關鍵概念，應用到行動中，善于歸納總結，嘗試進行分享。
|
