我有一个表(在MysqL中)有3列:
- 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
现在我需要制作一个包含上述信息的矩阵,如下所示:
- Computers Printers Software
- A Company X Company Y
- B Company X Company Y Company Y
- C Company Y Company Z
最终我需要在Excel中使用它.
实际上我有不同数量的类别,所以在MysqL中使用每列的连接进行操作并不是一个好选择.我可以在PHP中编写一个函数,但我想知道是否有更优雅的解决方案.
我在Excel中查看了一个数据透视表,但它们似乎更适合数字作为值.但也许我忽视了一些事情,因为我自己从不使用Excel.
有任何想法吗?
解决方法
我遇到了与数据透视表相同的问题…完美的摘要,但不适用于文本矩阵.
我刚刚“解除了”我使用过的一些代码示例.这里我有A-D列中的数据,并在F列周围构建矩阵(在同一张纸中).
检查一下这是否有帮助.
我仍然无法让代码看起来正确,所以请注意很多代码在代码窗口之前启动.
代码示例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
代码示例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,Range("H1:" & MatrixLastColAddress),0)
- ' Get position of Qualification Name within Matrix titles.
- TableRow = Application.Match(Sheets("Lijst").Cells(LijstCurrentRow,Range("G2:G" & MatrixLastRow),0)
- Set CellToFill = Range("G1").Offset(TableRow,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