天天看點

Excel中利用OFFSET函數解決很多個分塊的一次性轉置問題(非純手動)

  今天遇到一個問題,需要将Excel中的一門課的平時成績根據每個同學的作業次數進行轉置排列。

  原始EXCEL表中内容如下:

Excel中利用OFFSET函數解決很多個分塊的一次性轉置問題(非純手動)

上表每個同學對應一個學号,該學号同學的作業和分數相應已知,現在為了統計友善,我要得到如下表的效果:

Excel中利用OFFSET函數解決很多個分塊的一次性轉置問題(非純手動)

因為學生人數衆多,所有不能手動一個個調,需要利用excel的強大功能,是以我查到了可以使用OFFSET函數。

下面我将給出步驟和解釋,如何通過上面第一張表來獲得第二張表的效果:

首先,在第一張表的基礎上進行修改,選中C2單元格,然後再輸入框中輸入OFFSET($B$1,(ROW()-2)*7+COLUMN()-COLUMN($B$1),0,1,1) 如下表,

Excel中利用OFFSET函數解決很多個分塊的一次性轉置問題(非純手動)

offset函數的文法格式在百度上可以得到,這裡為了友善大家,給出如下:

   offset函數的文法格式

=offset(reference,rows,cols,height,width)

=Offset(參照單元格,行偏移量,列偏移量,傳回幾行,傳回幾列)

我們将B1作為參照單元格,寫成$B$1的形式(注意這裡我們将B1作為ROW()的固定參數使用,即不随單元格的變化而發生變化,是以不能寫成B1的形式)

ROW()表示標明目前單元格的行号(如標明C2後,ROW()表示傳回C2的行号2),同理,COLUMN()表示標明目前單元格的列号。若在選中目前單元格C2的情況下,想要獲得其他單元格的行号,且不随單元格變化而變化,(如B1),則寫為COLUMN($B$1)。

是以OFFSET($B$1,(ROW()-2)*7+COLUMN()-COLUMN($B$1),0,1,1)表示以B1作為參照單元格不變,向下偏移(ROW()-2)*7+COLUMN()-COLUMN($B$1)個機關,向右偏移0個機關(即向右不偏移),最後,傳回值為1行1列(即1個數)。

在選中C2單元格的情況下,點選回車之後,生成95:

Excel中利用OFFSET函數解決很多個分塊的一次性轉置問題(非純手動)

右拉單元格,得到:

Excel中利用OFFSET函數解決很多個分塊的一次性轉置問題(非純手動)

這樣,由上表可以發現,第一個學号同學的六次成績已經由縱列轉化為行。

所有其他同學的成績,隻要下拉C2-H2單元格即可得到:

Excel中利用OFFSET函數解決很多個分塊的一次性轉置問題(非純手動)

上表中C-H列即為轉化之後的成績,但是學号和每一行成績不比對,是以,我們建立一張表,将每一個學号所占的間距縮小到一行單元格,并将倒數第二章表的

C-E列複制到新表中,最終得到的結果如下:

Excel中利用OFFSET函數解決很多個分塊的一次性轉置問題(非純手動)

是以,到這裡,我們就解決了目标問題。

繼續閱讀