数据透视表中的文本值? [英] Text values in a pivot table?
问题描述
位置类别供应商
A电脑公司X
A打印机公司Y
B电脑公司X
B打印机公司Y
B软件公司Y
C电脑公司Y
C软件公司Z
现在我需要一个包含上述信息的矩阵,如下所示:
电脑打印机软件
A公司X公司Y
B公司X公司Y公司Y
C公司Y公司Z
最终我需要在Excel中。
实际上,我有一个可变数量的类别,所以在MySQL中使用每个列的连接不是一个好的选择。我可以在PHP中编写一个函数,但是我想知道是否有更优雅的解决方案。
我在Excel中查看了一个数据透视表,但它们似乎更适合数字值。但是也许我忽略了一些东西,因为我从来没有使用过Excel。
任何想法?
我遇到了与数据透视表相同的问题...完美的摘要,但不是文本矩阵。
我只是取消了我使用的一些代码示例。在这里,我将数据列在A-D中,并围绕F列生成矩阵(在同一张表中)。
检查是否有帮助。
我仍然无法找到正确的代码,所以请注意,很多代码在代码窗口之前启动。
代码示例1:
'填写值
表格TempFile)。选择
ListRow = 1
MisMatchCounter = 0
直到细胞(ListRow,1).Value =
'从列表的第三列获取表项。
TableEntry = Cells(ListRow,3).Value
关于错误简历Next
如果Err.Number> 0 Then MsgBox Err.Number
'获取产品名称在行标题范围内的位置。
如果TableEntry<> 然后
TableRow = Application.Match(Cells(ListRow,1),Range(F3:& MYLastRowAddress),0)'比现实少2行
'获取产品尺寸在列标题范围内的位置。
TableColumn = Application.Match(Cells(ListRow,2),Range(G2:& MYLastColAddress),0)
Set CellToFill = Range(F2 ).Offset(TableRow,TableColumn)
'如果单元格中已有一个条目,请将其与新条目分开,并带有逗号和空格。
如果Err.Number = 0然后
如果CellToFill.Value<> 然后
CellToFill.Value = CellToFill.Value& ,
CellToFill.Value = CellToFill.Value& TableEntry
Else
CellToFill.Value = TableEntry
如果
Else
MisMatchCounter = MisMatchCounter + 1
表单(错误)。单元格(MisMatchCounter,1).Value = ListRow
表单(错误)。单元格(MisMatchCounter,2 ).Value = Cells(ListRow,1)
Sheets(Errors)。Cells(MisMatchCounter,3).Value = Cells(ListRow,2)
错误)。细胞(MisMatchCounter,4).Value = Cells(ListRow,3)
表格(错误)细胞(MisMatchCounter,5).Value = Cells(ListRow,4)
结束如果
结束如果
错误GoTo 0
ListRow = ListRow + 1
循环
代码示例2:
Sub CreateManualMatrix()
Dim TableRow,TableColumn As Integer
Dim TableEntry As String
Dim CellToFill As Range
'Sheet被称为Lijst
'列A是顶行的名称
'列B是左列的名称
'列C是Matrix的价值
'矩阵顶行从H1开始
'矩阵左列开始在G2
MatrixLastColAddress = Range(H1)。End(xlToRight).Address
MatrixLastRow = Range(G65536)。结束(xlUp).Row
LijstReadColumn = 3
LijstCurrentRow = 2'如果没有标题使用
直到表格(Lijst ).Cells(LijstCurrentRow,1).Value =
'从列表的第三列获取表项。
TableEntry = Sheets(Lijst)。单元格(LijstCurrentRow,LijstReadColumn).Value
'获取Matrix中Employee名称的位置。
TableColumn = Application.Match(Sheets(Lijst)。Cells(LijstCurrentRow,1),Range(H1:& MatrixLastColAddress),0)
'获得Matrix标题中的资格名称。
TableRow = Application.Match(Sheets(Lijst)。Cells(LijstCurrentRow,2),Range(G2:G& MatrixLastRow),0)
设置CellToFill = Range(G1)。Offset(TableRow,TableColumn)
'如果单元格中已经有一个条目,请用逗号和空格将其与新条目分开。
如果CellToFill.Value<> 然后CellToFill.Value = CellToFill.Value& ,
将新条目添加到单元格。
CellToFill.Value = CellToFill.Value& TableEntry
LijstCurrentRow = LijstCurrentRow + 1
循环
End Sub
I have a table (in MySQL) with 3 columns:
Location Category Supplier
A Computers Company X
A Printers Company Y
B Computers Company X
B Printers Company Y
B Software Company Y
C Computers Company Y
C Software Company Z
Now I need to make a matrix containing the above information, like this :
Computers Printers Software
A Company X Company Y
B Company X Company Y Company Y
C Company Y Company Z
Eventually I need to have this in Excel.
In reality I have a variable number of categories, so doing it in MySQL with a join for each column is not a good option. I could write a function in PHP, but I was wondering if there's a more elegant solution.
I looked a pivot tables in Excel, but they seem more suited for numbers as values. But maybe I'm overlooking something, since I never work with Excel myself.
Any idea's?
I ran into the same problem with pivot tables... Perfect for summaries, but not for text matrices.
I have just "lifted" some code examples that I used. Here I have the data in columns A-D and build the matrix (in the same sheet) around column F.
Check to see if this helps.
I still have trouble getting the code to look right , so please be aware that a lot of the code starts before the code window.
Code Example 1:
'Fill in the values
Sheets("TempFile").Select
ListRow = 1
MisMatchCounter = 0
Do Until Cells(ListRow, 1).Value = ""
' Get table entry from third column of list.
TableEntry = Cells(ListRow, 3).Value
On Error Resume Next
If Err.Number > 0 Then MsgBox Err.Number
' Get position of product name within range of row titles.
If TableEntry <> "" Then
TableRow = Application.Match(Cells(ListRow, 1), Range("F3:" & MYLastRowAddress), 0) ' 2 rows less than reality
' Get position of product size within range of column titles.
TableColumn = Application.Match(Cells(ListRow, 2), Range("G2:" & MYLastColAddress), 0)
Set CellToFill = Range("F2").Offset(TableRow, TableColumn)
' If there's already an entry in the cell, separate it from the new entry with a comma and space.
If Err.Number = 0 Then
If CellToFill.Value <> "" Then
CellToFill.Value = CellToFill.Value & ","
CellToFill.Value = CellToFill.Value & TableEntry
Else
CellToFill.Value = TableEntry
End If
Else
MisMatchCounter = MisMatchCounter + 1
Sheets("Errors").Cells(MisMatchCounter, 1).Value = ListRow
Sheets("Errors").Cells(MisMatchCounter, 2).Value = Cells(ListRow, 1)
Sheets("Errors").Cells(MisMatchCounter, 3).Value = Cells(ListRow, 2)
Sheets("Errors").Cells(MisMatchCounter, 4).Value = Cells(ListRow, 3)
Sheets("Errors").Cells(MisMatchCounter, 5).Value = Cells(ListRow, 4)
End If
End If
On Error GoTo 0
ListRow = ListRow + 1
Loop
Code Example 2:
Sub CreateManualMatrix()
Dim TableRow, TableColumn As Integer
Dim TableEntry As String
Dim CellToFill As Range
'Sheet is called Lijst
'Column A is names for top row
'Column B is names for left column
'Column C is value for Matrix
'Matrix Top Row starts at H1
'Matrix Left Column starts at G2
MatrixLastColAddress = Range("H1").End(xlToRight).Address
MatrixLastRow = Range("G65536").End(xlUp).Row
LijstReadColumn = 3
LijstCurrentRow = 2 'make 1 if no header is used
Do Until Sheets("Lijst").Cells(LijstCurrentRow, 1).Value = ""
' Get table entry from third column of list.
TableEntry = Sheets("Lijst").Cells(LijstCurrentRow, LijstReadColumn).Value
' Get position of Employee name within Matrix.
TableColumn = Application.Match(Sheets("Lijst").Cells(LijstCurrentRow, 1), Range("H1:" & MatrixLastColAddress), 0)
' Get position of Qualification Name within Matrix titles.
TableRow = Application.Match(Sheets("Lijst").Cells(LijstCurrentRow, 2), Range("G2:G" & MatrixLastRow), 0)
Set CellToFill = Range("G1").Offset(TableRow, TableColumn)
' If there's already an entry in the cell, separate it from the new entry with a comma and space.
If CellToFill.Value <> "" Then CellToFill.Value = CellToFill.Value & ","
' Add the new entry to the cell.
CellToFill.Value = CellToFill.Value & TableEntry
LijstCurrentRow = LijstCurrentRow + 1
Loop
End Sub
这篇关于数据透视表中的文本值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!