將Excel矩形數(shù)據(jù)區(qū)域轉換成一行或者一列

將Excel矩形數(shù)據(jù)區(qū)域轉換成一行或者一列

將工作表中矩形數(shù)據(jù)區(qū)域轉換成一行或者一列。例如將下圖所示的名為Data的區(qū)域：

轉換成單獨的一列：

或者單獨的一行：

先不要看下面的答案，自已可以先想一想如何實現(xiàn)？

公式思路

獲取數(shù)據(jù)所在的行號和列號，然后提取行列號所在單元格中的數(shù)據(jù)。

公式解析

按行順序將區(qū)域中的數(shù)據(jù)轉換成單獨的一列

在單元格B2中輸入公式：

=IF(INDIRECT(ADDRESS(ROW(Data)+INT((ROW()-ROW($B$2))/COLUMNS(Data)),COLUMN(Data)+MOD(ROW()-ROW($B$2),COLUMNS(Data)),4,,”Sheet1″))=””,””,INDIRECT(ADDRESS(ROW(Data)+INT((ROW()-ROW($B$2))/COLUMNS(Data)),COLUMN(Data)+MOD(ROW()-ROW($B$2),COLUMNS(Data)),4,,”Sheet1″)))

然后下拉至單元格B17，如下圖所示。

其中，ROW(Data)+INT((ROW()-ROW($B$2))/COLUMNS(Data))獲取Data區(qū)域中第1行第1列的數(shù)據(jù)所在的行。數(shù)學原理：用需要輸入數(shù)據(jù)的區(qū)域中當前單元格所在行的行號減去首行行號的結果，除以被提取單元格區(qū)域Data的列數(shù)，所得結果加上Data區(qū)域起始行號，即為要在當前單元格中放置的數(shù)據(jù)在Data區(qū)域中的行號。

COLUMN(Data)+MOD(ROW()-ROW($B$2),COLUMNS(Data))獲取Data區(qū)域中第1行第1列的數(shù)據(jù)所在的列。數(shù)學原理：用需要輸入數(shù)據(jù)的區(qū)域中當前單元格所在行的行號減去首行行號的結果，除以被提取單元格區(qū)域Data的列數(shù)的余數(shù)，用所得結果加上Data區(qū)域起始列號，即為要在當前單元格中放置的數(shù)據(jù)在Data區(qū)域中的列號。

Addrees(行號,列號,4,,”Sheet1”)以相對引用方式返回工作表Sheet1中相應行列號的地址。

INDIRECT(引用地址)返回引用地址所在單元格的數(shù)據(jù)。

使用IF函數(shù)讓結果為空的單元格仍然返回為空，而不是0。

下面的幾種情形與上文介紹的情形相似，原理相同，這里只給出公式，你可以自已調試體會。

按列順序將區(qū)域中的數(shù)據(jù)轉換成單獨的一列

在單元格B2中的公式如下，然后拖動下拉至單元格B17。

按行順序將區(qū)域中的數(shù)據(jù)轉換成單獨的一行

在單元格B2中的公式如下，然后向右拖動拉至單元格Q2。

按列順序將區(qū)域中的數(shù)據(jù)轉換成單獨的一行

在單元格B2中的公式如下，然后向右拖動拉至單元格Q2。

小結

本例的關鍵在于求出需要提取的數(shù)據(jù)在Data區(qū)域中的行列號，其中的運算是精華所在。可以先用代表行列的數(shù)字找到規(guī)律，再用相應求得行號列號的函數(shù)代替。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。