為什麼這篇excel直轉橫公式鄉民發文收入到精華區：因為在excel直轉橫公式這個討論話題中，有許多相關的文章在討論，這篇最有參考價值！作者JieJuen (David)看板Office標題[目錄] Excel FAQ: 用OFFSET...
轉置可說是超級FAQ呀，而且各種要求的型式實在太過豐富
雖然"同理可得"，但還是怕第一次用的人霧裡看花啊
再加上這樣的問題很難下標題，也不知如何爬文
已經值得寫一篇"目錄"了 XD
1. 最基本的轉置:
A1 A1 B1 A1 A2 A3
A2 ←→ A1 A2 A3 A2 B2 ←→ B1 B2 B3
A3 A3 B3
法一:複製後使用"選擇性貼上"(右鍵)/轉置
法二:公式
=OFFSET($A$1,COLUMN(A:A)-1,ROW(1:1)-1)
法三法四法五法...(略):TRANSPOSE.INDEX.INDIRECT.
#17HrwsuY 3912 411/24 JieJuen □ [算表] EXCEL:連結文字-多格 名稱快速鍵
#179QED4n 3634 10/29 JieJuen R: [問題] excel 儲存格直式轉成橫式
(後來發現...這是我在本版的第一篇文章,就是探討這個問題...果真是超級FAQ..)
#19SncH-X 8349 1 1/18 yazad □ [算表] EXCEL 數值計算,轉置,OFFSET
2. n個一列
A C D E F G H I J
-------------------------------------------------------
A1 A1 A2 A3 A4 A5 A6 A7 A8
A2 A9 A10 A11 A12 A13 A14 A15 A16
A3 A17..............
=OFFSET($A$1,8*(ROW(1:1)-1)+(COLUMN(A:A)-1),)
#14hFpz-t 156 3 7/06 yggdrasils □ [算表] EXCE行轉列的問題
#17GX8JVz 3872 211/20 JieJuen R: [算表] 將數字分行
#17AV_DVx 3675 m 411/02 JieJuen □ [算表] Excel較少被提及的函數與小技巧
#179LxLi_ 3633 10/29 JieJuen R: [算表] Excel橫軸、縱軸轉換問題
反方向轉換
#18kUksd_ 6803 8/31 jojoba26 □ [算表] 請問如何將一矩陣中的數字改為一列?
#19ms3w5H 8938 2 3/20 SunFsClony □ [算表] EXCEL橫轉直--n個一列→一欄,轉置
(此文中有進一步說明)
#1A4t_PYW 9677 1 5/20 sqnan □ [算表] 如何把兩欄資料交錯併成一欄
3. n個一欄
A B C D
--------------------------------------
A1 A1 A61 A121
A2 A2 A62 A122
A3 A3 A63 A123
... ............
A60 A60 A120 A180
A61
...
=OFFSET($A$1,60*(COLUMN(A:A)-1)+ROW(1:1)-1,)
#17zd4Nd_ 5272 4/05 JieJuen R: [問題] 如何每60個資料貼成新的一欄?
#17IPksbe 3929 11/25 JieJuen R: [算表] Excel 將數字分行(複雜版)
#18CPPtCB 5806 5/19 JieJuen R: [問題] 把EXCEL的資料往左方和上方移動
反方向轉換
#17ApXUrR 3684 211/02 JieJuen R: [問題] excel欄位複製技巧
4. 特殊
#17Gzd7Yd 3894 311/21 JieJuen □ [算表] Excel 將數字分行(複雜版)
(有分解步驟說明)
#17QvpPeG 4231 12/21 JieJuen □ [算表] EXCEL:轉置.OFFSET.
#17c99kCl 4597 1/24 JieJuen R: [問題] excel大量資料換行排序...
#17lnsiKr 4822 1 2/23 JieJuen R: [問題] 由網頁複製表格至excel的問題
#17ZtnOkT 4514 1 1/18 JieJuen R: [請問] 關於excel的儲存格
#17ZROdMM 4502 1/16 JieJuen R: [問題] EXCEL格式問題
#17uanOZN 5084 3/20 JieJuen R: [算表] excel空一格不填滿(座位表)
#17JKPsDJ 3953 511/28 JieJuen R: 如何倒置一列資料?
#17DTGT-0 3785 211/10 JieJuen R: [問題] EXCEL列印問題
#18ATHugM 5736 5/14 JieJuen R: [算表] 請問如何用EXCEL進行S型編班
#18AVQIXw 5737 5/14 JieJuen R: [算表] 參照某陣列～只列出前三名
#18L-wxvp 6138 1 6/18 bathtub □ [問題] excel自動選擇行數
#18kqSdaW 6810 2 9/01 fgj □ [算表] 請問如何自動寫 =A1,A4,A7...
#1AkCcaqk 10867 4 9/22 rakahasa □ [算表] 直行資料轉入橫行格式,INDEX
(跳列取資料)
#18c24dvE 6564 8/05 ksk0516 □ [問題] 請教一個關於Excel重複資料的問題
#18i-ouEG 6752 1 8/26 kokomo1 □ [算表]如何將兩列的值移至另一欄的儲存格
#18kG7YCX 6799 8/30 JieJuen R: [問題] excel有類似查表的功能嗎?
#18l99OO_ 6823 3 9/02 ljuber □ [算表] Excel資料計算問題
(動態範圍)
#18lI97QP 6832 9/02 JieJuen R: [算表] 請問一下課表作法
#18NDZDwj 6184 1 6/21 JieJuen R: [問題] excel 製作課表??
#18tAyo90 7070 9/26 JieJuen R: [算表] excel排列資料 --VLOOKUP,OFFSET
#19S2toyW 8324 1/16 JieJuen R: [算表] excel 兩格一起動？--OFFSET排列
#19Skh2T7 8347 1/18 JieJuen R: [算表] 詢、抓取網頁資料,OFFSET,VBA,End
#198oLiPb 7703 11/19 JieJuen R: [算表] 多數欄位如何改成同一欄?--轉置,
#19jSPDv4 8814 3/10 JieJuen R: [算表] 有關整理資料並重新排列的問題
#19mXg4IQ 8924 2 3/19 JieJuen R: [算表] excel資料分類--排列,轉置,OFFSET
#19odlTD2 9036 3/26 JieJuen □ [算表] 玩轉陣列--特殊轉置
(多欄改至同一欄,原資料每欄(列)個數不定)
#19mwCV9O 8941 3 3/20 windknife18 R: [算表] EXCEL 大量插入欄位問題?--OFFSET
#1AlP8bOB 10898 2 9/26 andreli □ [算表] 對角線遞增,欄列同時遞增,a1,b2,c3
===========================================================================
說明:
利用OFFSET傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。
就是從一個點出發，看往右、往下走多少格，走到之後傳回它的值。
用到的語法:
OFFSET(出發點,往下走幾格,往右走幾格)
例: OFFSET(A1,2,3) 等於 D3 這一格的值
現在的問題在於到底要往右(下)走幾格?
若能做到:寫下這條公式之後，往右往下拉可以指定到不同的位置
例: OFFSET(A1,2,3) 往右拉後要變成
OFFSET(A1,3,3) 指定的位置往下一格
就可以隨我們意，重新分配資料的位置。
拖曳公式時，相對參照會隨之改變
例: =A1 往右拉會變成 =B1
但我們希望變化的是一個數字，以便放到OFFSET裡變化指定的位置
利用ROW與COLUMN可以將參照的變化轉換為數字的變化
ROW傳回參照的列號 COLUMN傳回參照的欄號
例: ROW(A3)=3 例: COLUMN(B1)=2
現在有了可變化的數字，缺點是:該格被刪掉(移動)時會錯誤
例: 將A3刪除(如:下方儲存格上移)時 ROW(A3) 會變成 ROW(#REF!)
ROW(A4) 會變成 ROW(A3)
改進方法:參照整列
例: ROW(3:3)
如此將整列刪除(移動)時才會錯誤
這可變化的數字，讓它從0開始遞增，
就像是從起點開始走，比較好想。
所以上文會有 (ROW(1:1)-1) 和 (COLUMN(A:A)-1) 這樣的式子
較能讓人看清這個基本元素 要乘(除)多少
若仍可能刪除整列，則使用兩個ROW相減(或兩個COLUMN)
例: ROW()-ROW($B$1) 其中B1為公式起始處(從B1開始往右往下拖曳公式)
或是參照到另一工作表的儲存格
例: ROW(Sheet2!3:3)
複雜一點的變化，可能會用到MOD函數，取餘數之意。
有小數的時候，OFFSET只認整數，即自動取INT之意。
例: OFFSET(A8,1/3,) 為 A8
OFFSET(A8,-1/3,) 為 A7
函數的參數不寫(有個逗點但沒有數字)表示0
接下來，就是抓出要求型式的規則，
把以上元素放到OFFSET中，
大功告成！(我知道這有點太過於輕描淡寫 XD)
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 218.164.48.8
推 BlackCyclone:很精彩的一篇說明!! 謝謝 04/05 11:04
推 bbpeonf:推超強又超認真，但為什麼板主都不M起來 04/05 13:52
→ JieJuen:^^ 04/05 15:03
※(04/05 06:07)(05/19 23:03)(06/18 02:51)(08/15 05:38)(08/28 13:56)(08/30 16:34)※(08/31 10:08)(09/01 14:01)(09/02 19:49)(09/26 18:11)(11/19 04:32)(01/16 14:57)
推 mimicz:實用好文!!!! 09/01 20:16
→ JieJuen:^^ 09/02 19:26
推 whiteeye:這種文不m，版主有在管版嗎-.- 09/09 15:08
→ JieJuen:結果在樓上的推荐下 我就變成板主來m自己的文章了 XD 09/16 13:51
※(01/18 17:19)(01/18 20:58)(03/10 09:52)(03/19 19:52)(03/20 17:51)(03/21 01:24)※(03/26 05:24)(05/20 16:21)(09/23 21:47)
※ 編輯: JieJuen 來自: 114.47.35.29 (09/26 14:04)
推 searoar:推推 10/20 11:18