星期二, 3月 13, 2007

MS Excel隱藏的攝影功能

梅酒 @ 星期二, 3月 13, 2007
今天在Google上隨便搜尋有關Excel的東西,意外發現這個網站
Excel - 小技巧 - 隱藏的攝影功能
自己簡單地實做一下,大驚豔啊!他一定會是個超好用的功能啦!
順手就寫了個MEMO。
趁這個機會順便再介紹一個人的網誌,可以在那學到很多Excel技巧哦!
Yahoo知識+:Excel應用知識名人

下載範例檔:



實做一下,隨便弄了一個表格、一些資料如下:

(圖一)
1. 把這個區塊選取起來。這個區塊是準備變成圖片的。



(圖二)
2. 按著SHIFT,滑鼠點「編輯」,下拉式選單有個「貼上圖片連結」的選項。點他。


(圖三)
3. 出現了一個「圖片」(紅色框起來的部份),圖片工具列也蹦出來了。這圖片有什麼了不起的?他如果僅是死的圖片,那我用Print Screen不就得了?!這個功能厲害的當然是因為這個圖片不是死的囉!


(圖四)
4. 當你修改原始表格裡的資料,這個「圖片」也會跟著改變哦!酷吧!?


下載範例檔:paste linking pic.xls


~~~好吧!其實也還好啦!不過這更加強了我對EXCEL骨子裡都是函數的看法了!畫曲線圖的那種圖表也是一個函數,這個例子講了以後,這其實也是一個函數。你點選這個圖,除了跳出一個「圖片工具列」之外,在「資料編輯列」那邊顯示出「=$A$3:$E$7」,表示的就是這個圖片引用的區域儲存格(參照)。
因此,如果你把這個圖片copy到該檔案的其他sheet的話,這圖片所引用的則是該sheet的「=$A$3:$E$7」這個範圍的資料。

星期日, 3月 11, 2007

excel轉置表格同時保持連結

梅酒 @ 星期日, 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()的方法真是簡單明瞭又快速。