20點～excel同列資料如何展開

Question

來源資料如下：
sheet 1
－－A－－B－－C－－D－－
－單位－案次－冊數－日期－
人事處－２－－１－－2005/3/1
總務處－５－－３－－2005/4/1
教務處－４－－１－－2005/3/1
：
：

可否依"冊數"展開資料，同列欄位值複製即可
展開情形須如下：
sheet 1
－－A－－B－－C－－D－－
－單位－案次－冊數－日期－
人事處－２－－１－－2005/3/1
總務處－５－－１－－2005/4/1
總務處－５－－１－－2005/4/1
總務處－５－－１－－2005/4/1
教務處－４－－１－－2005/3/1
：
：

需展開筆數約5000筆，冊數最多達99冊
請excel達人幫幫忙，謝謝～

To Rex:
雖然你的解答和我提的需求是不同的，但還是謝謝你～
To whitefox:
照你的方法是ok的，但因為我的筆數約5000筆，展開後至少會有30000筆，所以每開啟一次檔案都要花很久的時間
有沒有可使用VBA之類的方式也可達成呢？謝謝你～

To 想飛:
程式碼是要放在sheet1還是sheet2？
兩個地方都測試過皆出現"class range的select方法失敗"
請問我是不是什麼地方弄錯了～

Answer 1

假設原始資料在 Sheet1
建議在 Sheet2 建立展開資料如下：

第一列為相同的標題欄
Sheet2!A2 輸入公式
=IF(ROW()-1>SUM(Sheet1!$C:$C),"",OFFSET(Sheet1!$A$1,MATCH(ROW()-1,Sheet1!$E:$E,1)-1,COLUMN()-1))
將公式往右複製到 B2:D2
將 C2 更改為數字 1
再將 A2:D2 的公式往下複製，直到 A 欄出現空白資料，表示已經展開完畢，完成。

注意１：若 D 欄出現數字，請將 D 欄儲存格格式改為日期即可
注意２：若 C 欄出現 1,2,3,4....，請將 C2 和 C3 都手動改為 1，反白 C2 和 C3 再往下複製即可。


2007-01-03 16:01:48 補充：
sorry, 少打了一個補充欄位，
請在原始資料 Sheet1!E2 輸入 1
Sheet1!E3 輸入公式 =SUM(C$2:C2) 1
再往下複製公式到所有資料，
這是在計算該筆資料的冊數在全部資料的總冊數中，是從第幾冊開始起算的。

2007-01-03 16:03:43 補充：
(知識＋的編輯有點問題....... >_<)
Sheet1!E3 公式應為 =SUM(C$2:C2)＋1

2007-01-04 16:29:18 補充：
如果原始資料有空白值, 展開之後，空白值會變成０。
要解決這個問題，可以加上 ISBLANK() 來判斷原始資料是否空白。將原來公式中 OFFSET(...) 那一段改成
IF(ISBLANK(OFFSET(...),"",OFFSET(...))
這樣的話，空白欄位就會維持空白，不會變成０了。

2007-01-04 16:29:34 補充：
新的完整公式如下，你可以試用看看
=IF(ROW()-1>SUM(Sheet1!$C:$C),"",IF(ISBLANK(OFFSET(Sheet1!$A$1,MATCH(ROW()-1,Sheet1!$E:$E,1)-1,COLUMN()-1)),"",OFFSET(Sheet1!$A$1,MATCH(ROW()-1,Sheet1!$E:$E,1)-1,COLUMN()-1)))

Answer 2

To 想飛
真的很抱歉…因為我在選最佳解前一直都沒收到你的信件
現在也還是沒收到，有可能是奇摩信箱系統弄到垃圾信件
我也太大意沒檢查垃圾信件而直接刪除
所以在選最佳解時我是依當時回答的狀況來選
也怪我自己太心急了，造成你的反感～
在此對你致上十二萬分的歉意…
希望你原諒我這不懂世事的小鬼！ >_<"

2007-01-04 19:26:59 補充：
啊！… 確實如此～
我剛去很久沒用的sfilc信箱查看
真的有你寄給我的信件
而且我13:53分寄，你13:58分就回覆了！
想法，真的再次對你感到抱歉～
因為我忘了回覆信箱之前有修改到sfilc信箱
這是我知識+改版後第一次發問
要到那裡設定回覆信箱呢…
我還是改成奇摩信箱好了
不然又會發生類似問題
對回答者不公及產生誤會！
真的很不好意思！

Answer 3

我在昨天看到這偏時，第一直覺就是想到用VBA，所以就嘗試寫了一下，但自己是VBA初學(寫有點慢...^^")...寫完就發現 whitefox 以用函數回答，小弟看了真是佩服！所以就把寫完的結果留在自己的檔案庫啦～
剛發現版主希望用VBA，所以就把這程式碼貼出....但個人覺得 whitefox 的方法還是很好，是很棒的函數運用！且做完後只要用複製、選擇性貼上值，就可以避免運算過慢的問題了～



Sub 展開()
Dim i, j, k, s, n As Integer
n = 1
Application.ScreenUpdating = False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
j = Selection.Rows.Count

For i = 2 To j
k = Range("c" & i).Value
Range("A" & i).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
For s = 1 To k

Sheets("Sheet2").Select
Range("A" & n).Select
ActiveSheet.Paste
n = n + 1

Next s
Worksheets("sheet1").Select
Next i

Application.ScreenUpdating = True
End Sub


2007-01-04 09:00:25 補充：
忘了補充
原工作表名稱： sheet1
展開後的工作表名稱：sheet2

2007-01-04 16:47:36 補充：
已回信給你囉～有附加檔案！

2007-01-04 17:14:44 補充：
不否認最佳解答的選擇（因為我也覺得棒），但覺得這句話很讓人不能接受『最符合我的需求』，說希望要VBA也是您，來信索取檔案試用也是您～但收到信沒回應，也沒說什麼地方不適用～所以對上面那句話非常感冒！

2007-01-04 18:57:28 補充：
您所設定的回覆信箱並非yahoo信箱

Answer 4

做法一
將A1/B1/C1/D1選起來(反白)
資料-->篩選-->自動篩選
這樣你隨時要看哪一冊,在冊數的下拉選單選擇就好了

做法二
將C column 選取
-->資料-->排序-->將選定範圍擴大-->排序
-->主要鍵"冊數", 遞增 , 有標題列
-->確定
這樣就會照"冊數"排序