Weblog of S-ppc.com

静岡市まちづくり公社って何してる会社?
<< July 2017 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 >>
<< ツイッターアナリティクス | main | アンケート集計 >>

クロス集計的なやつ

今回は、アンケート集計結果をグラフ化する際、フィルタで抽出した結果を反映させる方法について書きます。

 

エクセルのシートにアンケートで集められたデータを入力するのですが、

例えば下記のように入力されたシートがあったとします。

A列に性別のデータ(A1が男A2が女A3が男みたいな)

B列に地域のデータ(B1が東京都B2が埼玉県B3が千葉県みたいな)

C列に好きなスポーツを複数回答可(C1が野球 サッカーC2がサッカー C3がサッカー バスケみたいな)

 

こういったデータでグラフを作る時に、まず課題となるのが、複数回答の部分です。

C列でサッカーを集計する際、=countif(C1:C3,"サッカー")と入力すると、

C1の野球 サッカーとC3のサッカー バスケをカウントしてくれません。

 

そこで、集計するセルに=countif(C1:C3,"*サッカー*")というように、

ワイルドカードを使ってカウントすれば、C1もC3もカウントしてくれます。

 

ただ、東京都の男性だけの集計を見たいと思い、元のデータにフィルタをかけて

表示しても、countifは非表示の部分もカウントしてしまうので、

集計データには反映されません。

 

フィルタをかけた状態をカウントする関数としてsubtotalというものがありますが、

これだと特定の文字を抽出してカウントする事が出来ません。

 

そこで、sumproduct関数を使い

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW(INDIRECT("1:"&ROWS(C1:C3))),))*(C1:C3="サッカー"))

みたいな記述をしてみたのですが、この式でサッカーの前後にアスタリスクを使うとカウントされませんでした。

 

結局マクロを使ってsubtotal_countifというものを定義してカウントしました。

以下がそのマクロの記述になります。

 

Public Function subtotal_countif(rgSelect As Range, moji As String)
    Dim rg As Range 'セル
    Dim cot As Long 'カウンタ

    For Each rg In rgSelect
        If Rows(rg.Row).Hidden = False Then '表示されている行だけ対象にする
            If rg Like moji Then
                cot = cot + 1
            End If
        End If
    Next
    subtotal_countif = cot
 End Function

 

エクセルのVBEメニュー(ALT+F11)で、挿入→標準モジュールで挿入すれば

=subtotal_countif(範囲,”*抽出文字*”)という式が使えるようになります。

これで、フィルタをかけた状態で特定の文字を抽出カウントする事が出来るようになり、

グラフもそのフィルタをかけた状態のものを作る事が出来ます。

 

雑談 | permalink | comments(0) | trackbacks(0)

この記事に対するコメント

コメントする









この記事のトラックバックURL
http://weblog.s-ppc.com/trackback/229
この記事に対するトラックバック