ピボットテーブルで複数列を集計したい

ある社の製品を「持ってる」「知っている」「知らない」に分けて回答してもらいました。

ピボットテーブルで出身地別に集計しようと思いましたがうまくいきません。
添付画像のとおりでデータの表が調査結果です。

ピボットテーブルでやってみようとしたのですが、
1社(Panasonic)だけならば、選択したあとにフィルターボタンから「持ってる」だけを選択すると実現できました。
しかし対象をSONY、SANYO増やしていくと、よくわからない結果になってしまい、どうにもなりません。

どなたか理想の表を実現できる手段をご存知ないでしょうか？

No.1 ベストアンサー 回答者： goomania 回答日時： 2018/09/19 23:57

ご質問者のやりたいことを実現する方法として２つほど思いつきました。

１．ご質問者も使われたピボットテーブルを使用する方法
２．ピボットテーブルではなく、関数を使用する方法
上記の方法を順に説明します。

１．ピボットテーブルを使用する方法
ご質問者のご希望の表を作成するため、以下の準備をします。
（１）もともとのデータはsheet1にあるものとしてsheet2に内容をコピーします。
（２）sheet2の適当な場所（ここでは$I$9）に、入力規則を使って「持ってる」「知ってる」「知らない」を選択できるセルを設置します。
（３）「持ってる」「知ってる」「知らない」という回答内容が入力されているセルに集計したい項目に該当すれば1、そうでなければ0とする関数を記入します。
※C2の例を示すと=IF(Sheet1!C2=$I$9,1,0)という関数。これを縦方向、横方向にオートフィルでコピーします。　　　
（４）上記の結果、添付画像1のような表ができるので、これをピボットテーブルを使って添付画像2のように加工します。

２．関数を使用する方法
（１）もともとデータのあるシートとは別のシートに、集計結果の表を作成するため、「日本」「北米」「アジア」といった行方向の項目と「Panasonic」「SONY」･･･といった列方向の表題をあらかじめ作成します。
（２）そのシートの適当な場所（ここでは$I$6）に、入力規則を使って「持ってる」「知ってる」「知らない」を選択できるセルを設置します。
（３）集計値を計算するセルに条件に合致したものの件数をカウントするための計算式を記入します。　
※B2の例を示すと=COUNTIFS(Sheet1!$B$2:$B$8,$A2,Sheet1!C$2:C$8,$I$6)という関数。これを縦方向、横方向にオートフィルでコピーします。
（４）縦方向、横方向の合計をSUM関数を使って集計して完成です。
※添付画像3を参照してください。

この回答へのお礼

goomaniaさん、ご丁寧にありがとうございます。やってみます

お礼日時：2018/09/20 10:37

No.2 回答者： 30246kiku 回答日時： 2018/09/20 11:05

VBA で良かったですか？ダメならスルーということで

標準モジュールに以下を記述し Samp1 を実行してみます
処理対象は、アクティブシートの A5 ～ の表
結果は新規シートに


Option Explicit

Public Sub Samp1()
　　Dim vC As Variant, v As Variant
　　Dim r As Range

　　vC = Array("持ってる", "知ってる", "知らない")

　　Application.ScreenUpdating = False
　　With ActiveSheet
　　　　With Worksheets.Add
　　　　　　Set r = .Range("B2")
　　　　End With

　　　　For Each v In vC
　　　　　　Call Samp1Sub(.Range("A5"), v, r)
　　　　Next
　　End With
　　Application.ScreenUpdating = True
End Sub

Private Sub Samp1Sub(rng As Range, ByVal sS As String, r As Range)
　　Dim dic As Object
　　Dim vA As Variant
　　Dim sF As String
　　Dim i As Long, j As Long, k As Long, n As Long

　　Set dic = CreateObject("Scripting.Dictionary")

　　With Range(rng.End(xlDown).Offset(1), rng.End(xlToRight))
　　　　vA = .Value
　　End With
　　sF = "=SUM(RC[" & 2 - UBound(vA, 2) & "]:RC[-1])"
　　n = 1
　　For j = 1 To UBound(vA, 2) - 1
　　　　vA(n, j) = vA(n, j + 1)
　　Next
　　vA(n, j) = "総計"

　　For i = n + 1 To UBound(vA) - 1
　　　　For j = 3 To UBound(vA, 2)
　　　　　　If (vA(i, j) = sS) Then
　　　　　　　　vA(i, j) = 1
　　　　　　Else
　　　　　　　　vA(i, j) = 0
　　　　　　End If
　　　　Next
　　　　k = dic(vA(i, 2))
　　　　If (k = 0) Then
　　　　　　n = n + 1
　　　　　　dic(vA(i, 2)) = n
　　　　　　For j = 1 To UBound(vA, 2) - 1
　　　　　　　　vA(n, j) = vA(i, j + 1)
　　　　　　Next
　　　　　　vA(n, j) = sF
　　　　Else
　　　　　　For j = 2 To UBound(vA, 2) - 1
　　　　　　　　vA(k, j) = vA(k, j) + vA(i, j + 1)
　　　　　　Next
　　　　End If
　　Next
　　n = n + 1
　　sF = "=SUM(R[" & 2 - n & "]C:R[-1]C)"
　　vA(n, 1) = "総計"
　　For j = 2 To UBound(vA, 2)
　　　　vA(n, j) = sF
　　Next

　　r.Value = "【" & sS & "】"
　　With r.Offset(1).Resize(n, UBound(vA, 2))
　　　　.Value = vA
　　　　With .Rows(1)
　　　　　　.Interior.ColorIndex = 15
　　　　　　.HorizontalAlignment = xlCenter
　　　　End With
　　　　.Borders.LineStyle = xlContinuous
　　End With
　　Set r = r.Offset(n + 3)
　　Set dic = Nothing
End Sub

この回答へのお礼

お礼遅れました。社外に出す資料のためVBAは可能な限り避けたいので今回は使用しませんでしたが、ローカルで試したら行けました！
データ抽出だけするならばこちらのほうが良さそうです。ありがとうございました。

お礼日時：2018/11/05 10:56