☰

資料透視表 - 匯總多個獨立資料列

tag-icon • tag-icon microsoft-excel
資料透視表 - 匯總多個獨立資料列

我有一個大表，其中包含系統的每日輸入，其中包含如下所示的列：

日 項目名稱 1 項目數量 1 項目名稱 2 項目數量 2 .....

項目名稱和數量彼此獨立。在某一天，一個人可能會收到10 個項目A，而在另一天，一個人可能會收到5 個項目D、6 個項目B 和234 個項目C。列有數據，而在後一種情況，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

請在下面找到這張圖片。那些代表已維修的部分，零代表「未更改」的部分。如果這是您的資料的表示方式，那麼您可以使用簡單的資料透視表來解決您的問題。
在此輸入影像描述

相關內容