如何用Excel 巨集轉換下列數據

#excel

9116zm 2022-03-24 21:21:48 ‧ 1025 瀏覽

分享至

我有一串數據 ,想轉成可以用樞紐方式分析 ,但有需要先轉成下面如圖所示嗎?!

BeEvil_Y iT邦新手 4 級 ‧ 2022-03-25 08:36:18 檢舉

看你是要人工，還是寫巨集。
第一步：資料全複制
第二步：貼成同一條（Word→右鍵(空白處)→純文字）
第三步：把「^p」取代成空白

這時你的資料應該為：(全部連在一起)
元件1Shot Number 1 Shot Column 2 Shot Row 3 Shot center x 45 y 35

第四步：把元件二字，取代為「^p元件」
第五步：把Shot Number 取代為「^pShot Number 」

這時你的資料應該為：(有段行，其餘都連在一起)
元件1
Shot Number 1 Shot Column 2 Shot Row 3 Shot center x 45 y 35
Shot Number 2 Shot Column 2 Shot Row 3 Shot center x 45 y 35
Shot Number 3 Shot Column 2 Shot Row 3 Shot center x 45 y 35
…
第六步：再把資料貼回空的Excel裡面
第七步：把Shot Number, Column..等字眼全部取代成「,」

這時你的資料應該為：
元件1
1,2,3,45,35
2,2,3,45,35
3,2,3,45,35

第八步：資料→資料剖析→分隔符號→逗點.

這樣就全部都分好了，很簡單。
寫巨集也是同一個邏輯。
以上步驟全部可以用VBA寫好。

9116zm iT邦新手 5 級 ‧ 2022-03-25 11:42:19 檢舉

不好意思 ,上面我懂 ,但是我沒VBA底子 ,請問可以再請教你如何寫嗎?

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2022-03-25 08:47:39

最佳解答

有需要

執行 VBA 前

執行 VBA

執行後結果

回應 2
分享
檢舉

9116zm iT邦新手 5 級 ‧ 2022-03-30 11:13:31 檢舉

海大大 ,可以參考下面新的條件嗎?

海綿寶寶 iT邦大神 1 級 ‧ 2022-03-30 21:38:49 檢舉

程式碼如下

Option Explicit
Const mainSheetName = "Report"
Const dataSheetName = "Raw"
Sub Main()
    Dim sComponent, sNumber, sColumn, sRow, sCenterX, sCenterY, sFocus As String
    Dim bFirst As Boolean
    
    bFirst = True
    
    Sheets(mainSheetName).Select
    Range("C1").Select
    Do While ActiveCell <> ""
        If ActiveCell.Value = "ShotNo" Then
            If bFirst = True Then
                bFirst = False
            Else
                Call SaveOneRecord(sComponent, sNumber, sColumn, sRow, sCenterX, sCenterY, sFocus)
            End If
            sNumber = ActiveCell.Offset(0, 1)
        ElseIf ActiveCell.Value = "WaferNo" Then
            Call SaveOneRecord(sComponent, sNumber, sColumn, sRow, sCenterX, sCenterY, sFocus)
            bFirst = True
            sComponent = ActiveCell.Offset(0, 1)
        ElseIf ActiveCell.Value = "Row" Then
            sColumn = ActiveCell.Offset(0, 1)
            sRow = ActiveCell.Offset(0, 3)
        ElseIf ActiveCell.Value = "ShotCentX" Then
            sCenterX = ActiveCell.Offset(0, 1)
            sCenterY = ActiveCell.Offset(0, 3)
        ElseIf ActiveCell.Value = "Last" Then
            sFocus = ActiveCell.Offset(0, 2)
        End If
        
        ActiveCell.Offset(1, 0).Select
    Loop
    
    Call SaveOneRecord(sComponent, sNumber, sColumn, sRow, sCenterX, sCenterY, sFocus)
    
End Sub
Sub SaveOneRecord(ByVal pComponent, ByVal pNumber, ByVal pColumn, ByVal pRow, ByVal pCenterX, ByVal pCenterY, ByVal pFocus)
    Sheets(dataSheetName).Select
    Range("A10000").End(xlUp).Offset(1, 0).Select
    
    ActiveCell.Offset(0, 0).Value = pComponent
    ActiveCell.Offset(0, 1).Value = pNumber
    ActiveCell.Offset(0, 2).Value = pColumn
    ActiveCell.Offset(0, 3).Value = pRow
    ActiveCell.Offset(0, 4).Value = pCenterX
    ActiveCell.Offset(0, 5).Value = pCenterY
    ActiveCell.Offset(0, 6).Value = pFocus
    
    Sheets(mainSheetName).Select
End Sub

程式碼如下
```
Option Explicit
Const mainSheetName = "Report"
Const dataSheetName = "Raw"
Sub Main()
    Dim sComponent, sNumber, sColumn, sRow, sCenterX, sCenterY, sFocus As String
    Dim bFirst As Boolean
    
    bFirst = True
    
    Sheets(mainSheetName).Select
    Range("C1").Select
    Do While ActiveCell <> ""
        If ActiveCell.Value = "ShotNo" Then
            If bFirst = True Then
                bFirst = False
            Else
                Call SaveOneRecord(sComponent, sNumber, sColumn, sRow, sCenterX, sCenterY, sFocus)
            End If
            sNumber = ActiveCell.Offset(0, 1)
        ElseIf ActiveCell.Value = "WaferNo" Then
            Call SaveOneRecord(sComponent, sNumber, sColumn, sRow, sCenterX, sCenterY, sFocus)
            bFirst = True
            sComponent = ActiveCell.Offset(0, 1)
        ElseIf ActiveCell.Value = "Row" Then
            sColumn = ActiveCell.Offset(0, 1)
            sRow = ActiveCell.Offset(0, 3)
        ElseIf ActiveCell.Value = "ShotCentX" Then
            sCenterX = ActiveCell.Offset(0, 1)
            sCenterY = ActiveCell.Offset(0, 3)
        ElseIf ActiveCell.Value = "Last" Then
            sFocus = ActiveCell.Offset(0, 2)
        End If
        
        ActiveCell.Offset(1, 0).Select
    Loop
    
    Call SaveOneRecord(sComponent, sNumber, sColumn, sRow, sCenterX, sCenterY, sFocus)
    
End Sub
Sub SaveOneRecord(ByVal pComponent, ByVal pNumber, ByVal pColumn, ByVal pRow, ByVal pCenterX, ByVal pCenterY, ByVal pFocus)
    Sheets(dataSheetName).Select
    Range("A10000").End(xlUp).Offset(1, 0).Select
    
    ActiveCell.Offset(0, 0).Value = pComponent
    ActiveCell.Offset(0, 1).Value = pNumber
    ActiveCell.Offset(0, 2).Value = pColumn
    ActiveCell.Offset(0, 3).Value = pRow
    ActiveCell.Offset(0, 4).Value = pCenterX
    ActiveCell.Offset(0, 5).Value = pCenterY
    ActiveCell.Offset(0, 6).Value = pFocus
    
    Sheets(mainSheetName).Select
End Sub
```

修改

登入發表回應