|
前面分享過EXCEL中簡單的單列數(shù)據(jù)轉(zhuǎn)單行，或單行數(shù)據(jù)轉(zhuǎn)單列【EXCEL行列互轉(zhuǎn)三種方法 】，其中有一個方法用的就是OFFSET函數(shù)與ROW、COLUMN函數(shù)的嵌套。 今天運用OFFSET+ROW+COLUMN函數(shù)組合處理更多稍微復雜一點的數(shù)據(jù)。 ①先列后行，比如一列數(shù)據(jù)轉(zhuǎn)為四列多行。 在目標區(qū)域第一個單元格B19輸入以下公式，然后向右拉到第4列，再下拉直到出現(xiàn)空白。 =OFFSET($A$1,COLUMN(A1)-1+(ROW(A1)-1)*4,)&"" ②先行后列，比如一列數(shù)據(jù)轉(zhuǎn)為四行多列。 在單元格H19輸入以下公式，然后向下拉到第4行，再右拉直到出現(xiàn)空白。 =OFFSET($A$1,ROW(A1)-1+COLUMN(A1)*4-4,)&"" 2.一行轉(zhuǎn)多列多行（或多行多列） ①先列后行，比如一行數(shù)據(jù)轉(zhuǎn)為三列多行。 同樣，此例在單元格B6輸入以下公式，然后向右拉到第3列，再下拉直到出現(xiàn)空白。 =OFFSET($A$1,,(COLUMN(A1)-1)+(ROW(A1)-1)*3,)&"" ②先行后列，比如一列數(shù)據(jù)轉(zhuǎn)為三行多列。 在單元格H6輸入以下公式，然后向下拉到第3行，再右拉直到出現(xiàn)空白。 =OFFSET($A$1,,(ROW(A1)-1)+(COLUMN(A1)-1)*3,)&"" 3.多行多列轉(zhuǎn)一列 比如源數(shù)據(jù)六行五列： ①先向下引用源數(shù)據(jù)，再向右引用。 單元格B9輸入以下公式，下拉直到出現(xiàn)空白。 =OFFSET($A$1,MOD(ROW(A6),6),ROW(A6)/6-1,)&"" ②先向右引用源數(shù)據(jù)，再向下引用。 單元格D9輸入以下公式，下拉直到出現(xiàn)空白。 =OFFSET($A$1,ROW(A5)/5-1,MOD(ROW(A5),5))&"" 4.多行多列轉(zhuǎn)一行 多行多列數(shù)據(jù)轉(zhuǎn)成單行的情況實際應用中應該不算常見，不過這里也一并整理了，若需要可直接套用公式。 比如源數(shù)據(jù)五行六列： ①先向右引用源數(shù)據(jù)，再向下引用。 單元格B8輸入以下公式，右拉直到出現(xiàn)空白。 =OFFSET($A$1,INT(COLUMN(F1)/6)-1,MOD(COLUMN(F1),6))&"" ②先向下引用源數(shù)據(jù)，再向右引用。 單元格B9輸入以下公式，右拉直到出現(xiàn)空白。 =OFFSET($A$1,MOD(COLUMN(E1),5),INT(COLUMN(E1)/5)-1)&"" 說明： ①ROW()返回行號，比如ROW(A2)=2； ②COLUMN()返回列號，比如COLUMN(F1)=6； ③MOD()求余數(shù)，比如MOD(1,5)=1，即1÷5余數(shù)是1，再比如MOD(5,5)=0，因為5被5整除，沒有余數(shù)； ④上面每個公式最后都加了&""，作用是把引用空單元格得到的0去掉，顯示為空白； ⑤上面公式中用到不少逗號【,】（英文半角狀態(tài)下），不能隨意省略或移動位置，每一個小豆芽都有它存在的意義，有興趣研究的可以了解一下OFFSET的基礎用法【讓你的EXCEL表格動起來——OFFSET函數(shù)】。 |
|
|
來自： 萌二書屋 > 《我的原創(chuàng)EXCEL》
