数据透视表中的文本值？

发布于 2024-12-09 12:19:16 字数 771 浏览 0 评论 0原文

我有一个包含 3 列的表（在 MySQL 中）：

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。

有什么想法吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

揪着可爱 2024-12-16 12:19:16

我在数据透视表中遇到了同样的问题...非常适合摘要，但不适用于文本矩阵。

我刚刚“举起了”我使用的一些代码示例。在这里，我在 AD 列中有数据，并围绕 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, 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

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

回复收藏 0 原文

司马昭之心 2024-12-16 12:19:16

我无法检查查询是否正常，但或多或少会是这样的查询：

SELECT t1.Location, MAX(t1.Computers), MAX(t1.Printers),  MAX(t1.Software)

FROM (
SELECT
 t.Location,
 CASE WHEN t.Category = 'Computers' THEN
   t.Supplier
 END Computers,

 CASE WHEN t.Category = 'Printers' THEN
   t.Supplier
 END Printers,

 CASE WHEN t.Category = 'Software' THEN
   t.Supplier
 END Software,
FROM
YOUR_TABLE t
) t1
GROUP BY t1.Location

I have no way to check if the query is fine, but more or less would be a query like this:

SELECT t1.Location, MAX(t1.Computers), MAX(t1.Printers),  MAX(t1.Software)

FROM (
SELECT
 t.Location,
 CASE WHEN t.Category = 'Computers' THEN
   t.Supplier
 END Computers,

 CASE WHEN t.Category = 'Printers' THEN
   t.Supplier
 END Printers,

 CASE WHEN t.Category = 'Software' THEN
   t.Supplier
 END Software,
FROM
YOUR_TABLE t
) t1
GROUP BY t1.Location

回复收藏 0 原文

巨坚强 2024-12-16 12:19:16

您所寻求的通常称为交叉表。这可以像这样静态地完成：

Select Location
    , Min( Case When Category = 'Computers' Then Supplier End ) As Computers
    , Min( Case When Category = 'Printers' Then Supplier End ) As Printers
    , Min( Case When Category = 'Software' Then Supplier End ) As Software
From MyTable
Group By Location

但是，如果您寻求的是动态数量的类别（以及列），则不能在 SQL 中本地完成。后面的解决方案称为动态交叉表。最好的方法是在中间层中构建类似于上面静态版本的 SQL 语句，或者使用可以执行相同操作的报告工具。

What you seek is often called a crosstab. This can be done statically like so:

Select Location
    , Min( Case When Category = 'Computers' Then Supplier End ) As Computers
    , Min( Case When Category = 'Printers' Then Supplier End ) As Printers
    , Min( Case When Category = 'Software' Then Supplier End ) As Software
From MyTable
Group By Location

However, if what you seek is to have a dynamic number of categories (and thus columns), this cannot be done natively in SQL. This later solution is called a dynamic crosstab. The best approach is either to build the SQL statement akin to the static version above in your middle-tier or using a reporting tool which will do the same.

回复收藏 0 原文

~没有更多了~