麻辣家族討論版版 » Excelㄧ般區 » 橫式資料轉換為直式資料

返回列表下一主題上一主題發帖

[發問] 橫式資料轉換為直式資料

中學生 Rank: 2

帖子: 190
主題: 16
精華: 0
積分: 236
點名: 286
作業系統: windows xp
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2011-8-8
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2016-7-24 07:43 | 只看該作者

[發問] 橫式資料轉換為直式資料

本帖最後由 aer 於 2016-7-24 07:47 編輯

[請問公式如何寫，才能將sheet1(上圖示) 的橫式資料，轉換為sheet2(下圖示) 的直式資料？謝謝！

[

報名轉檔.zip (11.08 KB)

樓主熱帖

收藏

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-23

2^#

發表於 2016-7-24 12:04 | 只看該作者

A2．陣列公式：〔Shift + Ctrl → Enter〕三鍵
=TRIM(MID(SUBSTITUTE(INDIRECT("Sheet1!"&TEXT(SMALL(IF(Sheet1!$B$2:$M$64="",4^8,ROW($2:$64))*100+COLUMN($B:$M)^(COLUMN(A$1)>1),ROW(A1)),"!r0c00"),)&"","梯","梯"&REPT(" ",99)),99^(COLUMN(A$1)>2),99))

1 評分人數

ML089: 巧妙 +COLUMN($B:$M)^(COLUMN(A$1)>1)金錢 + 2

大學生 Rank: 4

帖子: 1387
主題: 4
精華: 0
積分: 1397
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-11-22

熱烈參與勳章

3^#

發表於 2016-7-24 12:59 | 只看該作者

http://blog.xuite.net/hcm19522/twblog/435047307

中學生 Rank: 2

帖子: 190
主題: 16
精華: 0
積分: 236
點名: 286
作業系統: windows xp
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2011-8-8
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章

4^#

發表於 2016-7-24 20:31 | 只看該作者

回復 2# 准提部林
謝謝版主！公式測試沒問題。
不過，是否可解釋一下公式意思？感謝您！

中學生 Rank: 2

帖子: 190
主題: 16
精華: 0
積分: 236
點名: 286
作業系統: windows xp
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2011-8-8
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章

5^#

發表於 2016-7-24 20:32 | 只看該作者

回復 3# hcm19522
謝謝您的回覆！

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-23

6^#

發表於 2016-7-24 21:16 | 只看該作者

回復 4# aer

這公式不好解釋, 將公式分段, 請試著自行去理解:

Xl0000011.rar (62.92 KB)

大學生 Rank: 4

帖子: 1387
主題: 4
精華: 0
積分: 1397
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-11-22

熱烈參與勳章

7^#

發表於 2016-7-25 11:20 | 只看該作者

回復 6# 准提部林
"准大" 巧妙安排程式可懂不易寫
想請教,"!r0c00"),)&"" ,此段加 &"" 何意

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-23

8^#

發表於 2016-7-25 12:40 | 只看該作者

回復 7# hcm19522

避空格出現0, 將其去掉可見分明!

這種公式慢, 資料多, 最好用VBA

中學生 Rank: 2

帖子: 190
主題: 16
精華: 0
積分: 236
點名: 286
作業系統: windows xp
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2011-8-8
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章

9^#

發表於 2016-7-25 18:17 | 只看該作者

回復 hcm19522

避空格出現0, 將其去掉可見分明!

這種公式慢, 資料多, 最好用VBA
准提部林發表於 2016-7-25 12:40

也請您賜教VBA 程式，因資料將近有1000筆，無限感激！

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-23

10^#

發表於 2016-7-25 19:15 | 只看該作者

回復 9# aer

程式碼很簡單, 多看幾次應可以理解:

Sub TEST()
Dim Arr, Brr, i&, j%, T, N&
With Sheets("Sheet1").UsedRange
Arr = .Value
ReDim Brr(1 To .Count, 1 To 3)
End With
For i = 2 To UBound(Arr)
For j = 2 To UBound(Arr, 2)
If Arr(i, j) <> "" Then
N = N + 1: T = Split(Trim(Arr(i, j)), "梯")
Brr(N, 1) = Arr(i, 1)
Brr(N, 2) = T(0) & "梯"
Brr(N, 3) = T(1)
End If
Next j
Next i
With Sheets("Sheet2")
.UsedRange.Clear
If N = 0 Then Exit Sub
.[A1:C1] = Array("姓名", "梯次", "日期")
.[A2:C2].Resize(N) = Brr
Application.Goto .[A1]
End With
End Sub

複製代碼

靜思自在 : 吃苦了苦、苦盡廿來，享福了福、福盡悲來。

返回列表下一主題上一主題