PTT數位生活區 / Office

[目錄] Excel FAQ:用OFFSET轉置.分行(欄).行轉列

看板Office作者 (David)時間16年前 (2008/04/05 04:17), 編輯推噓5(5推 0噓 3→)
留言8則, 6人參與, 最新討論串1/1
轉置可說是超級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
推
04/05 11:04, , 1F
很精彩的一篇說明!! 謝謝
04/05 11:04, 1F
推
04/05 13:52, , 2F
推超強又超認真，但為什麼板主都不M起來
04/05 13:52, 2F
→
04/05 15:03, , 3F
^^
04/05 15:03, 3F
※(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)
推
09/01 20:16, , 4F
實用好文!!!!
09/01 20:16, 4F
→
09/02 19:26, , 5F
^^
09/02 19:26, 5F
推
09/09 15:08, , 6F
這種文不m，版主有在管版嗎-.-
09/09 15:08, 6F
→
09/16 13:51, , 7F
結果在樓上的推荐下 我就變成板主來m自己的文章了 XD
09/16 13:51, 7F
※(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)
推
10/20 11:18, , 8F
推推
10/20 11:18, 8F
‣ 更多 JieJuen 的文章
文章代碼(AID): #17zepYsA (Office)
Office 近期熱門文章
更多 近期熱門文章 >>
PTT數位生活區 即時熱門文章
更多 即時熱門文章 >>
‣ 更多 JieJuen 的文章
文章代碼(AID): #17zepYsA (Office)