问题描述
有没有办法自动将数据从列表形式转换为表格形式,而不是手动进行?
Is there a way to get automatically transform the data from the 'list form' into the 'table form' other than doing it manually?
最后,我想在excel中使用'表格'
In the end I am wanting to use the 'table form' in excel
列表表单
Department: QUALITY CONTROL
Worker: DAVID
Case # 75967
Case # 75845
Case # 75949
Department: PORCELAIN
Worker: JONATHAN
Case # 75891
Case # 75947
Case # 75962
Department: SUB-STRUCTURE
Worker: BILL
Case # 75997
Case # 75864
Case # 75993
表格
任何帮助将非常感谢。我甚至不知道该怎么做。
Any help would be greatly appreciated. I didn't even know what to Google to find out how to do this
推荐答案
的代码我认为这将为你工作。原始列表应在Sheet1中,有序数据写入Sheet2。我使用数组(sData和sData2)来存储时态数据。
EDITED - See below first piece of code I think this would work for you. The original list is supposed to be in "Sheet1", ordered data is written to "Sheet2". I'm using arrays (sData and sData2) for storing temporal data.
Dim lLastRow As Long
Dim i As Integer
Dim k As Integer
Dim sData() As String
Dim sData2(0 To 2) As String
Private Sub ListToTable()
'get number of rows with data
lLastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
k = 2 'destination table will start in line 2 (line 1 for titles).
'Set titles in destination sheet
Worksheets("Sheet2").Cells(1, 1).Value = "Depertment"
Worksheets("Sheet2").Cells(1, 1).Font.Bold = True
Worksheets("Sheet2").Cells(1, 2).Value = "Worker"
Worksheets("Sheet2").Cells(1, 2).Font.Bold = True
Worksheets("Sheet2").Cells(1, 3).Value = "Case"
Worksheets("Sheet2").Cells(1, 3).Font.Bold = True
For i = 1 To lLastRow
'split the data using ":" as delimiter
sData = Split(Worksheets("Sheet1").Cells(i, 1), ":")
If sData(0) = "Department" Then
sData2(0) = Trim(sData(1)) 'Trim just for eliminating spaces
ElseIf sData(0) = "Worker" Then
sData2(1) = Trim(sData(1))
Else
sData2(2) = Trim(sData(0))
Worksheets("Sheet2").Cells(k, 1).Value = sData2(0)
Worksheets("Sheet2").Cells(k, 2).Value = sData2(1)
Worksheets("Sheet2").Cells(k, 3).Value = sData2(2)
k = k + 1
End If
Next i
End Sub
根据注释更新
在您的评论中,第二列表到表转换。基本上你首先需要区分列表中的两件事。这取决于您的数据。我选择检查是否单元格中的前两个( Left
)字符是否是一个数字( IsNumeric
)。然后代码非常类似于上面的一个。当定义上面的变量时,添加 Dim sFirstColumn as String
和 Dim iSecondColumn作为Integer
(或根据你的数据)
UPDATE according to commentIn your comments you ask for a second list-to-table transformation. Basically you first need to differentiate between the "two things" in your list. This depends on your data. I choose to check is the first two (Left
) characters in the cells is a number or not (IsNumeric
). Then the code is very similar to the one above here. When defining the variables on top add Dim sFirstColumn as String
and Dim iSecondColumn as Integer
(or whatever according to your data).
For i = 1 To lLastRow
If Not IsNumeric(Left(Worksheets("Sheet1").Cells(i, 1), 2)) Then
sFirstColumn = Worksheets("Sheet1").Cells(i, 1).Value
Else
iSecondColumn = Worksheets("Sheet1").Cells(i, 1).Value
Worksheets("Sheet2").Cells(k, 1).Value = sFirstColumn
Worksheets("Sheet2").Cells(k, 2).Value = iSecondColumn
k = k + 1
End If
Next i
这篇关于如何将标题列表转换为表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!