1 2 3 4 5 6

廣告

網友您好,若是您覺得我寫的工具或文章對您有幫助,
而您或者您的朋友有在博客來購物,可以透過底下連結

如何在博客來購物贊助網站或是點選計數器下方的博客來圖示來讓網站可以永續經營。

2009年4月8日 星期三

Excel資料轉置(利用offset函數)

大部分的資料庫的資料存放格式都是先欄再列,但是有時候在輸入資料時,為了方便輸入就會用以列為主的輸入方式,但是這樣的資料要匯入資料庫時,就會很麻煩,所以我就利用Offset、Column、Row三個函數及相對絕對位置的關念來處理這樣的問題。
2009/5/5:新增把結果跟來源對調
1.先在B1輸入=OFFSET(原始資料!$A1,COLUMN(A1),0) 2.在A2輸入=OFFSET(原始資料!$A$1,ROW(A1)*參數設定!$B$1+COLUMN(A2)-1,0) 3.依照資料的欄位及列數複製公式(同顏色的為同一個公式複製)
4.處理前的資料
5.設定筆數
2010/2/5新增文章:Excel資料轉置(VBA版本)

14 則留言:

王小凱 提到...

一整天大大..

我已經參考了你這一篇

可是剛好我要的功能,剛好是相反的捏><

我有另外找了一些offset函數資料

可是我怎麼用

拖曳的時候,他的值就是給我亂跑...

讓我根本沒辦法正常得到我要的...


------------------------(分隔線)
我目前的資料是"直的"

公司名稱
所在地址
電話
傳真
公司名稱
所在地址
電話
傳真
一直重複在重複下去
------------------------(分隔線)
想要變的結果(如下)
公司名稱|所在地址|電話|傳真
公司名稱|所在地址|電話|傳真
公司名稱|所在地址|電話|傳真

一直重複在重複下去
------------------------(分隔線)

我使用OFFSET函數
原始資料是(Sheet2)
想改變的地方是(sheet3)
我在(sheet3)開頭輸入標題
公司名稱|所在地址|電話|傳真
A1  |B1  |C1|D1

我在A2輸入
=OFFSET(Sheet2!B1,0,0,1,1)
我在A3輸入
=OFFSET(Sheet2!B1,4,0,1,1)

然後框起來,拖曳‥  (我是每四格跳一次資料)
A4得到
=OFFSET(Sheet2!B3,0,0,1,1)
A5得到
=OFFSET(Sheet2!B3,4,0,1,1)
A6得到
=OFFSET(Sheet2!B5,0,0,1,1)

------------------------(分隔線)

即使我改變成

A2輸入
=OFFSET(Sheet2!B1,0,0,1,1)
A3輸入
=OFFSET(Sheet2!B5,0,0,1,1)

A4卻得到
=OFFSET(Sheet2!B3,0,0,1,1)
A5卻得到
=OFFSET(Sheet2!B7,0,0,1,1)
A7卻得到
=OFFSET(Sheet2!B5,0,0,1,1)

即使我在A1。2。3
都輸入了正確的位子(+4+4)
可是他還是會錯誤
=OFFSET(Sheet2!B1,0,0,1,1)
=OFFSET(Sheet2!B5,0,0,1,1)
=OFFSET(Sheet2!B9,0,0,1,1)

大大是否可以給我 Gmail信箱
我想把Excel寄給你
這樣子你可以比較清楚一點 >0<
拜託你了大大~

一整天 提到...

王小凱您好:
我的Email在右上方,就可以看到.

一整天 提到...

王小凱您好:
我己回信給您,以您的例子要先設定筆數為3,然後
步驟1:儲存格從B1拉到C1(因為您設定為3筆),圖黃色的區塊,
步驟2:再從儲存格A2往下拉到C?
,那個?就是您拉的資料如果出現0,表示找不到資料了(圖綠色部分).

ally 提到...

老師
今天又用了您這一頁的功能
感恩ㄋㄟ ^_^
ally 敬上

一整天 提到...

ally您好:
很高興我寫的文章對妳有幫助.也謝謝您的回文,讓我覺得有繼續寫下去的動力.

shen 提到...

您好,想請問若A1儲存格的值是ABCDE,現要將這些值分別切割存放在5個欄位上(A1,B1,C1,D1,E1),也就是每一欄位只有1個字.若A2也有值也是這樣切割(A2,B2,C2...),這樣在excel是要如何透過函數做?因小弟有一asc檔,可以用excel打開,都在同一欄列上且資料很多,但需要用上述方法依字元切欄位.
感謝~~
沈先生

一整天 提到...

沈先生您好:
您可以利用Excel的Mid函數來做,或是利用我寫的Excel VBA來解決.

程式下載,請到[首頁右上方],[檔案下載],找到splitstring.xls下載

Unknown 提到...

老師:
我已經參考你這一篇
但發現我的資料與老師您的範例相反
如果是原始資料是橫的要轉至為直的
要如何寫函數?
我一直都失敗

老師我有寄信到您的email
請老師您可否給我解答
因為我一直用手動調整好浪費時間
照老師的方法自己還是寫不出去
拜託您了 感謝您

alicebee敬上

Unknown 提到...

謝謝您
幫了我很一個很大的忙
感謝您
alicebee敬上

michelle 提到...

老師:

我想請問offset會傳回值, 如原本180,000
結果會是180000 該怎辦 (因以下方會有
加總)?

一整天 提到...

michelle 您好,
因為offset只會移動值不會移動格式,您顯示180,000,是因為您設定格式的關係.所以您可以重新設定格式就可以.

Yoyo Liu 提到...

一整天老師您好!您的文章幫助我解決了很多難題,真的非常感謝!
想請教您的是,倘若在您的這個範例中,資料有標題,而我希望處理後的資料能帶出標題列,
請教老師應該如何操作呢?

例如:原本資料為
姓名 國文 英文 數學
王小明 98 90 89
張大華 95 92 90

處理後的資料要變成:
王小明 國文 98
王小明 英文 90
王小明 數學 89
張大華 國文 95
張大華 英文 92
張大華 數學 90

蔡明和 提到...

Yoyo Liu您好,
請參考底下這一篇.
如何將Excel樞紐分析過的資料還成條列式資料
http://blog.bestdaylong.com/2010/12/excel.html

Yoyo Liu 提到...

一整天老師
太棒了!這是我需要的,非常感謝您!
Yoyo Liu

贊助

彰化一整天粉絲團

About Me

我的相片

很多人問我為什麼叫"一整天",其實這是有原故的.有一天看一本書.就是說如何介紹自己.裡面說到如何讓別人對你的名字有印象.就要讓別人跟你的名字或是身體的一些特徵有關.比如很胖.就會被別人取小胖或是胖子.或是跟名字有關的諧音,那天看布袋戲中有一個叫”一頁書”的角色.所以我就想我的名字.可以想出什麼字出來.結果我把我的名字"明和",拆成日月和,日月和在一起,就代表白天晚上都在一起.就取名為"一整天",而這個"一整天"跟我的名字有關也方便別人記憶,後來發現"一整天"這個名詞太常被用,所以就在"一整天"前面加上"彰化"變成"彰化一整天",為什麼要加彰化.因為我老家在彰化,所以以後別人要找我的部落格的話.我都會跟他們說,請到yahoo或是google打"彰化一整天"第一筆出現的就是我的Blog. PS:現在只要輸入"一整天"可以找到.

Google+ Followers