我有一個大表,其中包含系統的每日輸入,其中包含如下所示的列:
日 項目名稱 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
請在下面找到這張圖片。那些代表已維修的部分,零代表「未更改」的部分。如果這是您的資料的表示方式,那麼您可以使用簡單的資料透視表來解決您的問題。