星期日, 3月 11, 2007

excel轉置表格同時保持連結

Major John @ 星期日, 3月 11, 2007
在excel中遇到又要「連結」儲存格,又要「轉置」表格的話,真不知道該怎麼辦!沒辦法很直覺的用手動操作出來。
當你複製了幾格儲存格要貼到其他儲存格時,可以按右鍵選擇「選擇性貼上」,可是注意到,勾選了「轉置」的選項「貼上連結」的按鈕就不見了。
(圖一)
尷尬了。如果我剛好是要重新整理排列原有的資料的話,除了不想動到原來的資料之外,還想要做到「如果原有的資料有更動/新增的話,就自動變更」,這樣一定要用連結的嘛!可是又要轉置原來的資料呢?就比較棘手了。


要把上面橫向排列的資料轉置成下面那個藍色的區塊(轉置),而且上面的資料如果有變更/改了文字或數字,下面會自動跟著改變(連結)。

只好用函數來解決這個問題了。在這裡會用到的函數有ROW(),ADDRESS(),INDIRECT()

先講excel的基本觀念。單一個函數的功能可能沒有很強大,可是當很多函數組合起來,一個包一個,就能組合出很有用的功能囉!
假設我們有個包了三層的函數,A(B(C(xx))),A包B,B又包C。而實際解析的過程是C解析出值丟到B(被B引用),B解析出值又丟到A來(被A引用),最後A解析出最後的結果。反正就是,從裡面算出來,裡面的先算
函數中的被括號包起來的叫做引數

=ROW()

ROW這個函數的功用就是秀出該儲存格是第幾列。
(圖二)
這個函數還真是單調無比,可是他用和其他函數包在一起的時候就非常方便了!
(圖三)
把滑鼠移動到儲存格的右下角,滑鼠會變成一個十字形,滑鼠往下移動就可以將這個儲存格複製下去了!這在之後的應用會非常方便。


=ADDRESS(列,欄,參照位址的形式,,sheet的名稱)
在ADDRESS中除了列與欄的引數是必填的之外,其他引數如果沒有特別指定的話,可以只寫成「=ADDRESS(列,欄)」這樣就好了。
=ADDRESS(2,5)會顯示$E$2。第五欄第二列的位址就是E2囉!前面出現錢字號「$」只是絕對位址而已,相對或絕對在這case並不會影響結果。可是如果都只填常數的函數的話,複製到其他儲存格後的結果還是一樣的,ADDRESS(2,5)永遠都是E2。
這個函數就是我找到可以做轉置的關鍵函數了。因為我想到,要是可以把列與欄的數字調換過來,在延伸複製到其他儲存格,不就可以有轉置的效果了嘛?轉置就是行變列,列變行嘛!

我們要讓ADDRESS出現的結果隨著你複製到其他儲存格而有所不同,有相對應的結果出來。=ADDRESS(ROW(),5),照著圖三的方式把這儲存格往下拉。
(圖四)
會E5, E6, E7.....這樣增加。這樣只有「列」的位址增加。剛剛說的,把列的引數和欄的引數調換就有轉置的效果啦!
我們要的結果希望是A1, B1, C1, D1......顯示在A8, A9, A10, A11......
=ADDRESS(1,ROW()-7)
(圖五)
引數寫成ROW()-7,-7是因為放在第8列要讓他ADDRESS的引數顯示成A1, B1, C1, D1.....。

唉,看那麼久,累了嘛?沒關係,我快講完了。

接著再ADDRESS外麵包一個INDIRECT()函數就OK啦!
(圖六)
INDIRECT(位址)函數就是顯示該位址儲存格的內容囉!

(圖七)
同理,在B8欄以下就是用=INDIRECT(ADDRESS(2,ROW()-7))囉!
C8欄以下就是用=INDIRECT(ADDRESS(3,ROW()-7)),D、E欄都是以此類推。不難吧!?

如此一來,你修改了上面橫是排列的資料後,下面縱向排列的結果就會自動變化哦。


UPDATE 2007/04/12:
我今天看了INDEX的用法,好像會比上述的還要簡單耶!上面的方法需要兩個函數包在一起,用INDEX的話好像只需要一個。XD不過等我有空再改。目前在想其他的東西。


UPDATE 2007/12/6:

講了那麼久用INDEX函數來達到轉置且保持連結的解法,過了半年才開始動筆寫。
其實是因為要找資料當範例的不好找,我又不想拿公司的資料出來。麻煩。
昨天寫了使用SUMPRODUCT這個函數來做兩條件加總,裡面的資料順便就拿來用好了。拿來轉置一下。


範例檔來囉:(強烈建議下載回去看,比較具體)


INDEX(陣列, 列, 欄)

陣列:指定預備要轉置的資料陣列,這裡是A1:W3。
:指定哪一列的資料。這裡故意把列的部份寫入COLUMN()這個函數(欄)代入。因為轉置就是欄→列;列→欄才叫轉置ㄇㄟ。這個目的就跟上面講的ADDRESS裡面包的東西的操作概念一樣。
:故意寫ROW(),要-7是因為目前從ROW()=8開始,要讓它變成1,所以ROW()-7。

用INDEX()的方法真是簡單明瞭又快速。

1 則留言: