☰

ピボット テーブル - 複数の独立したデータ列を要約する

tag-icon • tag-icon microsoft-excel
ピボット テーブル - 複数の独立したデータ列を要約する

システムへの毎日の入力を含む大きなテーブルがあり、そこには次のような列があります。

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

品目名と数量は互いに独立しています。ある日は品目 A を 10 個受け取り、別の日は品目 D を 5 個、品目 B を 6 個、品目 C を 234 個受け取ることがあります。前者の場合、2 列のみにデータがあり、後者の場合、6 列にデータがあります。他のすべての列は空になります。例:

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

たとえば、1 年間の A の毎日の数量や、1 年間の A の非ゼロ値の数を表示するには、ピボット テーブルをどのように使用しますか? A は複数の列に表示される可能性があり、考えられる項目は数百に及ぶため、各変数を列に含むテーブルを単純に作成することは現実的ではありません。

複数の方法ですべての列を追加しようとしましたが、B のすべての値と C のすべての値が与えられた場合、A の値が返されてしまいます。これは、値が依存している場合は便利ですが、相関がないためページが乱雑になります。

これが不可能な場合、スプレッドシートから必要な情報を取得するにはどのような方法が良いでしょうか?

答え1

データをどうしても再編成する必要があります。現在直面している理由から、列の数が不確定な状態というのは、本当によくありません。他の人が提案しているように、私のアドバイスは、データを、毎日の項目ごとに 1 行ずつある単純な 3 列の表に変換することです。

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)。このコードは、変換されたデータを「再編成されたデータ」という名前の新しいワークシートの 3 つの列に出力します。

答え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 は「変更されていない」部分を表します。データがこのように表されている場合は、簡単なピボット テーブルを使用して問題を解決できます。
ここに画像の説明を入力してください

関連情報