› 精華區beta 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 □ [算表] EXCEL:連結文字-多
　 　
◇ #179QED4n R: [問題] excel 儲存格直式
　 (後來發現...這是我在本版的第一篇文章,就是
　 探討這個問題...果真是超級FAQ..)
　 　
　 　
　 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 □ [算表] EXCE行轉列的問題
◇ #17GX8JVz R: [算表] 將數字分行
　 (含MOD簡介)
◇ #17AV_DVx □ [算表] Excel較少被提及的
◇ #179LxLi_ R: [算表] Excel橫軸、縱軸轉
　 　
　 反方向轉換
◇ #18kUksd_ □ [算表] 請問如何將一矩陣
　 　
　 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_ R: [問題] 如何每60個資料貼
◇ #17IPksbe R: [算表] Excel 將數字分行(
◇ #18CPPtCB R: [問題] 把EXCEL的資料往左
　 　
　 反方向轉換
◇ #17ApXUrR R: [問題] excel欄位複製技巧
　 　
　 4. 特殊
　 　
◇ #17Gzd7Yd □ [算表] Excel 將數字分行(
　 (有分解步驟說明)
◇ #17QvpPeG □ [算表] EXCEL:轉置.OFFSE
◇ #17c99kCl R: [問題] excel大量資料換行
◇ #17lnsiKr R: [問題] 由網頁複製表格至e
◇ #17ZtnOkT R: [請問] 關於excel的儲存格
◇ #17ZROdMM R: [問題] EXCEL格式問題
◇ #17uanOZN R: [算表] excel空一格不填滿
◇ #17JKPsDJ R: 如何倒置一列資料?
◇ #17DTGT-0 R: [問題] EXCEL列印問題
◇ #18ATHugM R: [算表] 請問如何用EXCEL進
◇ #18AVQIXw R: [算表] 參照某陣列～只列
◇ #18L-wxvp □ [問題] excel自動選擇行數
　 (跳列取資料)
◇ #18c24dvE □ [問題] 請教一個關於Excel
◇ #18i-ouEG □ [算表]如何將兩列的值移至
◇ #18kG7YCX R: [問題] excel有類似查表的
　 (動態範圍)
◇ #18kqSdaW □ [算表] 請問如何自動寫 =A
　 (跳列取資料)
◇ #18l99OO_ □ [算表] Excel資料計算問題
　 (動態範圍)
◇ #18lI97QP R: [算表] 請問一下課表作法
◇ #18NDZDwj R: [問題] excel 製作課表??
　 　
　 　
　 　
　 　
　 　
　 　
　 　
　 　
　 　
　 　
　 　
　 　
　 　
　 　
　 ==========================================
　 說明:
　 　
　 利用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)
　 　
　 ===========================================
　 原文：
◇ #17zepYsA [算表] Excel FAQ: 用OFFSET 轉置.
　 　
　 製作：
　 將原始文章轉成"精華區大型文章"不是件易事，
　 自然也是要靠"OFFSET"來完成囉~!
　 http://2y.drivehq.com/p/OffsetArticle.xls
　 　
　 　
　 　
　 　
　 　
　 　
◇ Edit Date