☰

数据透视表 - 汇总多个独立的数据列

tag-icon • tag-icon microsoft-excel
数据透视表 - 汇总多个独立的数据列

我有一张大表，其中包含系统的每日输入，其中的列如下所示：

天 商品名称1 商品数量1 商品名称2 商品数量2 .....

商品名称和数量相互独立。在某一天，有人可能会收到 10 件商品 A，而在另一天，有人可能会收到 5 件商品 D、6 件商品 B 和 234 件商品 C。在前一种情况下，只有 2 列有数据，而在后一种情况下，6 列有数据。所有其他列都将为空。示例：

Day N1  Q1  N2  Q2  N3  Q3
1   A   10
2   D   5   B   6   C   234

如何使用数据透视表来显示一年中每天的 A 数量，或者一年中 A 的非零值的数量？请注意，A 可能出现在多个列中，并且可能的项目有数百个，因此简单地创建一个包含每个变量的列的表是不可行的。

我尝试过多种方式添加所有列，但最终会返回 A 的值，给出 B 的所有值，给出 C 的所有值，这在值相关时很有用，但由于它们不相关，因此会使页面混乱。

如果这不可能，那么从电子表格中获取所需信息的最佳方法是什么？

答案1

您迫切需要重新组织您的数据。由于您现在遇到的原因，拥有不确定数量的列是一个非常糟糕的主意。我的建议是，正如其他人所建议的那样，将您的数据转换为一个简单的三列表，每行代表一个日常项目。

Day  Item  Quant
 1     A     10
 2     D      5
 2     B      6
 2     C    234

然后，您可以根据需要使用数据透视表来汇总和深入研究数据。

因此，现在的问题是如何在不确定数量的列中重新组织这些数据。幸运的是，一个相当简单的 VBA 程序可以做到这一点。将以下代码粘贴到新的 VBA 模块中（按Alt+ F11，然后转到Insert> Module）。

Sub SalvageMyTable()
Dim sOrig As Worksheet, sOut As Worksheet
Dim arrIn() As Variant
Dim rOut As Range, arrOut() As Variant
Dim i As Long, j As Long, k As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set sOrig = ActiveSheet
'Store original data in an array for faster processing
arrIn = sOrig.UsedRange.Value
'Create maximal output array. This can be resized before outputting the data.
ReDim arrOut(0 To 2, 0 To UBound(arrIn, 1) * (UBound(arrIn, 2) - 1) / 2) As Variant
'Loop through original table and copy values to output array.
For i = LBound(arrIn, 1) + 1 To UBound(arrIn, 1)
    For j = LBound(arrIn, 2) + 1 To UBound(arrIn, 2) Step 2
        If arrIn(i, j) <> "" Then
            arrOut(0, k) = arrIn(i, LBound(arrIn, 2))
            arrOut(1, k) = arrIn(i, j)
            arrOut(2, k) = arrIn(i, j + 1)
            k = k + 1
        Else
            Exit For
        End If
    Next j
Next i
'Resize output array
ReDim Preserve arrOut(0 To 2, 0 To k) As Variant
'Create output sheet and print output there.
Set sOut = Worksheets.Add
sOut.Name = "Reorganized Data"
sOut.Range("A1").Value = "Day"
sOut.Range("B1").Value = "Item"
sOut.Range("C1").Value = "Quantity"
sOut.Range("A2").Resize(k, 3).Value = Application.WorksheetFunction.Transpose(arrOut)
'Reset Excel settings
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

确保打开包含原始数据的工作表。然后转到 VBA 编辑器并运行此代码（按F5）。此代码将在名为“重新组织的数据”的新工作表中的三列中输出转换后的数据。

答案2

为了能够可靠地完成该数据集的数据分析，您需要更改数据的设置/组织/输入方式。

它可以在 Excel 中完成，但它也是一个非常容易设置的数据库。

物品表（物品编号、名称、描述）
车辆表（车辆编号、名称、描述、类型、品牌型号）
使用情况表（物品编号、车辆编号、日期、使用次数）

您也可以在 Excel 中使用不同的工作表执行此操作，并使用数据验证在用途表中提供项目和车辆的下拉列表。如果您没有编程经验，这可能是最好的选择。

很抱歉没有回答您的问题，但真正的问题是您的数据的组织。

答案3

您首先将表格转换为以下格式 Day NQ，样本数据如下

1 A 10
2 D 6
2 B 6
2 C 234

然后，如果您在此表上进行透视，您应该会得到结果。要转换为上面的表格，您需要创建一个新工作表。假设主工作表中有 10 行和 3 个名称数量对（假设 Sheet1 是工作表的名称，而不是标题行/列），那么您可以在新工作表中从第 1 行到第 30 行（10*3）使用以下公式

1    =Sheet1!A1  =Sheet1!B1  =Sheet1!C1
    ...
10   =Sheet1!A10  =Sheet1!B10  =Sheet1!C10
11   =Sheet1!A1  =Sheet1!D1  =Sheet1!E1
    ...
20   =Sheet1!A10  =Sheet1!D10  =Sheet1!E10
21   =Sheet1!A1  =Sheet1!F1  =Sheet1!G1
    ...
30   =Sheet1!A10  =Sheet1!F10  =Sheet1!G10

答案4

请参阅下面的图片。1 表示已维修的部分，0 表示“未更改”的部分。如果您的数据是这样表示的，那么您可以使用简单的数据透视表来解决您的问题。
在此处输入图片描述

相关内容