クロス集計の視覚化 モザイクプロットとその他の分析

クロス集計が重要であることは、どのような書籍やサイトを見ても散々言われています。そして、本来は集計そのものが大切なのではなく、その結果から何が読み取れるかを考えることが最も大切なプロセスです。しかし、何となくクロス集計表の数字を眺めているだけでは、新しい知見を得ることは難しい場合が多いと思います。そこで、クロス集計の結果を視覚的に理解できるようにしてみたいと思います。

では、クロス集計の視覚化には、どのようなグラフが最適でしょうか。恐らく最も単純なグラフは、モザイクプロットと呼ばれるグラフです。

モザイクプロットの何がいいのでしょうか。それは各要素の「割合」と「サイズ」が一目で把握できることではないでしょうか。この二つを同時に把握できるグラフはなかなか他にありません。

残念なことに、エクセルの標準機能の中にはこのモザイクプロットを作成する機能はありません（2018年5月現在）。他の様々なグラフ機能がある中で、何故、これほど有用なモザイクプロットの機能がないのでしょうか...。考えていてもしょうがないので、何とか作成する方法を探してみることにします。

簡単な方法としては、専用のソフトやアドインを探す、ということなのでしょうけれども、有料のものも多く、手を出しづらいかもしれません。しかし、高品質なものを求めているのであれば、代価を支払ってでもそのようなソフトを購入すべきです。

ただし、もっと気軽にモザイクプロットを作成したいのであれば、ネット上で提案されている方法を試してみるのも良いかもしれません。個人的に、しっくりくる方法を見つけることができなかったため、モザイクプロットを作成するVBAを書いてみることにします。アイディアは陳腐なもので、「クロス集計表の各数字の大小に応じた四角図形を作成・並べる」、というものです。

Sub myMosaicPlot()

'Use InputBox to let a user select a table range
    Dim myRange As Range
    Set myRange = Application.InputBox(Prompt:="Select cross table. Include row & column headers.", Type:=8)

'Get start row & column, and number of rows & columns of the table
    Dim myRow As Long, myRows As Long, myColumn As Long, myColumns As Long, Table_Value() As Variant, Table_Ratio() As Variant, Column_Ratio() As Variant
    
    myRow = myRange.Row
    myColumn = myRange.Column
    myRows = myRange.Rows.Count
    myColumns = myRange.Columns.Count

    ReDim Table_Value(1 To myRows - 1, 1 To myColumns - 1)
    ReDim Table_Ratio(1 To myRows - 1, 1 To myColumns - 1)
    ReDim Column_Ratio(1 To myColumns - 1)


'Get value & ratio into objects
        For j = 1 To myColumns - 1
            For i = 1 To myRows - 1
            Table_Value(i, j) = ActiveSheet.Cells(myRow + i, myColumn + j)
            Table_Ratio(i, j) = Table_Value(i, j) / WorksheetFunction.Sum(myRange)
            Column_Ratio(j) = Column_Ratio(j) + Table_Value(i, j)
            Next
        Column_Ratio(j) = Column_Ratio(j) / WorksheetFunction.Sum(myRange)
        Next


'Create a box chart for each cell
    Entiresize = 300 'Size of the chart
    FromColumn = 100 'Location of the chart
    myspace = 3 'space between each box
    
    Dim myShape() As Shape, myGroup() As ShapeRange, myPlot As ShapeRange
    ReDim myShape(1 To myRows - 1, 1 To myColumns - 1) As Shape, myGroup(1 To myRows - 1) As ShapeRange
    
        With ActiveSheet
            For j = 1 To myColumns - 1
            FromRow = 100
                For i = 1 To myRows - 1
            
                Set myShape(i, j) = .Shapes.AddShape(msoShapeRectangle, FromColumn, FromRow, Column_Ratio(j) * Entiresize, Table_Ratio(i, j) / Column_Ratio(j) * Entiresize)
                
                    With myShape(i, j).TextFrame2
                    .TextRange.Characters.Text = WorksheetFunction.Trim(ActiveSheet.Cells(myRow + i, myColumn + j).Text)
                    .TextRange.ParagraphFormat.Alignment = msoAlignCenter
                    .VerticalAnchor = msoAnchorMiddle
                    End With
            
                FromRow = FromRow + Table_Ratio(i, j) * Entiresize / Column_Ratio(j) + myspace
            
                Next
            
            FromColumn = FromColumn + Column_Ratio(j) * Entiresize + myspace
            Next
            
            For i = 1 To myRows - 1
                For j = 1 To myColumns - 1
            
                myShape(i, j).Select Replace:=False
            
                Next j
            
                With Selection
                .ShapeRange.Group.Select
                Set myGroup(i) = Selection.ShapeRange
                .ShapeRange.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent & (i-1) Mod 6 + 5
                .ShapeRange.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent & (i-1) Mod 6 + 5
                End With
        
            .Range("A1").Select
        
            Next i
        
            For i = 1 To myRows - 1
            myGroup(i).Select Replace:=False
            Next i
        
        Selection.ShapeRange.Group.Select
        Set myPlot = Selection.ShapeRange
        .Range("A1").Select
    
    
'Add column headers at the bottom of the plot
    Dim myColumnHeaders() As Shape, ColumnHeader As String, myColumnHeader As ShapeRange
    ReDim myColumnHeaders(1 To myColumns - 1) As Shape

        FromColumn = 100
            For i = 1 To myColumns - 1
            ColumnHeader = .Cells(myRow, myColumn + i).Value
            Set myColumnHeaders(i) = .Shapes.AddShape(msoShapeRectangle, FromColumn, FromRow, Column_Ratio(i) * Entiresize, 50)
            FromColumn = FromColumn + Column_Ratio(i) * Entiresize + myspace
        
                With myColumnHeaders(i).TextFrame2
                .TextRange.Characters.Text = ColumnHeader
                .TextRange.ParagraphFormat.Alignment = msoAlignCenter
                .VerticalAnchor = msoAnchorMiddle
                .TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
                End With
                With myColumnHeaders(i)
                .Fill.Visible = msoFalse
                .Line.Visible = msoFalse
                End With
            Next
        
            For i = 1 To myColumns - 1
            myColumnHeaders(i).Select Replace:=False
            Next
        
            With Selection
            .ShapeRange.Group.Select
            Set myColumnHeader = Selection.ShapeRange
            End With
            .Range("A1").Select
        
        
'Add row headers on the right of the plot
    Dim myRowHeaders() As Shape, RowHeader As String, myRowHeader As ShapeRange
    ReDim myRowHeaders(1 To myRows - 1) As Shape

        FromRow = 100
            For i = 1 To myRows - 1
            RowHeader = .Cells(myRow + i, myColumn).Value
            Set myRowHeaders(i) = .Shapes.AddShape(msoShapeRectangle, FromColumn, FromRow, 50, Table_Ratio(i, myColumns - 1) / Column_Ratio(myColumns - 1) * Entiresize)
            FromRow = FromRow + Table_Ratio(i, myColumns - 1) * Entiresize / Column_Ratio(myColumns - 1) + myspace
        
                With myRowHeaders(i).TextFrame2
                .TextRange.Characters.Text = RowHeader
                .TextRange.ParagraphFormat.Alignment = msoAlignLeft
                .VerticalAnchor = msoAnchorMiddle
                .TextRange.Font.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent & (i-1) Mod 6 + 5
                End With
                With myRowHeaders(i)
                .Fill.Visible = msoFalse
                .Line.Visible = msoFalse
                End With
            Next
        
            For i = 1 To myRows - 1
            myRowHeaders(i).Select Replace:=False
            Next
        
            With Selection
            .ShapeRange.Group.Select
            Set myRowHeader = Selection.ShapeRange
            End With
            .Range("A1").Select
    
    
'Add Y axis (0%, 20%, 40%, 60%, 80%, and 100%) on the left of the plot
    Dim myShape_Yaxis() As Shape
    ReDim myShape_Yaxis(1 To 6) As Shape

        FromColumn = 100
        FromRow = 100
    
            For i = 1 To 6
            Set myShape_Yaxis(i) = .Shapes.AddShape(msoShapeRectangle, FromColumn - 40, FromRow - 15 + ((i - 1) * (Entiresize + (myRows - 2) * myspace)) / 5, 40, 30)
                With myShape_Yaxis(i).TextFrame2
                .TextRange.Characters.Text = (6 - i) * 20 & "%"
                .TextRange.ParagraphFormat.Alignment = msoAlignCenter
                .VerticalAnchor = msoAnchorMiddle
                .TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
                End With
                With myShape_Yaxis(i)
                .Fill.Visible = msoFalse
                .Line.Visible = msoFalse
                End With
            Next
        
            For i = 1 To 6
            myShape_Yaxis(i).Select Replace:=False
            Next
        
            With Selection
            .ShapeRange.Group.Select
            Set myYaxis = Selection.ShapeRange
            End With
            .Range("A1").Select
        
        End With


'Grouping all sub-groups
    myPlot.Select
    myColumnHeader.Select Replace:=False
    myRowHeader.Select Replace:=False
    myYaxis.Select Replace:=Fals
    
    Selection.ShapeRange.Group.Select
    
End Sub

コードを実行すると、Inputboxが現れますので、ここにクロス集計表の範囲を指定します。その際に、行と列のヘダーを含めるようにします。

すると、以下のようなモザイクプロットが作成されます。

図形はグループ化しているため、特定の行グループの色を変えたい場合などは、一度グループ解除をする必要があります…。大変面倒なのですが、自作のコードなんてこんなものだろうと諦め、そこは目をつぶることにしています。何はともあれ、これでクロス集計をモザイクプロットとして視覚化することができました。縦方向に見れば、各列要素の割合が見て取れますし、また横方向を見ることによって、各列の規模を把握することができます。

モザイクプロットの他にも、クロス集計の視覚化として、コレスポンデンス分析というものがあります。マーケティングの分野ではとても有名な統計分析手法で、行要素間および列要素間の関係を座標上で見て取ることができます。残念ながら、この機能もエクセルにはないため、他の方法に頼ることになります。エクセルで完結する方法もないことはないらしいのですが、ここは統計分析に特化した”R”というアプリケーションを使い、分析と視覚化の処理を行います（この方法については、ここでは割愛します。）。結果は以下の通りです。

縦軸と横軸の座標の上で、それぞれの軸に何かしらの意味を持たせ、行要素と列要素のそれぞれをポジショニングしています。ポジショニングと言うといかにもマーケティングの言葉に聞こえますが、それ以外の場合においても、データから物事のトレンドを見抜く上で助けになります。

コレスポンデンス分析は視覚的に訴えやすく、いかにも統計分析をしているような気にさせてくれるかもしれません。しかし、コレスポンデンス分析の結果の解釈や、その計算方法、その背後にある考え方などを理解することは（少なくとも統計を勉強している方でなければ）容易ではありません。そのような状況で上記のような座標図を資料として使用することは、だいぶリスクが高いと言えるでしょう。

しかしながら、コレスポンデンス分析の結果を、何とか説明可能な範囲で利用できないでしょうか。一つの方法は、コレスポンデンス分析の結果をもとにクロス集計表を「並び替える」ことです。

唐突に「並び替える」というワードが出てきましたが、これには立派な意味があります。コレスポンデンス分析の計算の課程の中で、「各行および各列の項目にそれぞれ数値を割り当てる」というプロセスがあります。そして割り当てられた数値をもとに行と列を移動させるのですが、その様子が「行と列の並び替え」に当たります。この割り当てられる数値は複数存在するのですが（行数と列数の少ない方の数から１を引いた数）、その中の最初の結果を使用し、クロス集計表を並び替えたいと思います。

上記に示したクロス集計表に対して、Rを使いコレスポンデンス分析を行った結果、各行と各列に割り当てられた数値は以下の通りです（ここでも詳細は割愛します）。

Row1:Row8 {-1.46249899, 1.79231815, 0.06866948, -1.54543379, 1.12795191, 0.29087298, 0.09733284, -0.41117815}

Column1:Column6 {-1.2818798, -1.3445163, 0.65211, 1.5695245, 0.4157471, 0.3883627}

並び替え機能を使い、まず行項目の数値を使い降順に並び替えます。その結果を転置させ（Special PasteのTransposeを使えば簡単に行うことができます）、今度は列項目の数値を使い降順に並び替えます。最後に、もう一度転置をさせれば、下図の左のようなクロス集計表になります。このクロス集計表をもとに作成したモザイクプロットが下図の右になります。

最初の結果よりも、明らかに類似項目（特に類似列）がまとまっていたり、全体のトレンドや、逆にトレンドから外れているような項目を見つけやすくなっています。項目の数が多くなると、感覚的にこのような並び替えを行うことは非常に困難になるので、上記のような方法を試してみる価値はあるのではないでしょうか。

この方法では、コレスポンデンス分析の結果のほんの一部しか使用していないため、結果の解釈や表現としては厳密には適切とは言えないでしょう。それでも、最終的なグラフとしてモザイクプロットを使用していることにより、第三者にとっても比較的理解しやすい表現となります。特に、コレスポンデンス分析の座標図からは、各項目のサイズが全く見て取れないのですが、その点モザイクプロットであれば、各項目のサイズをはっきりと視覚的に確認することができます。

Rank関数の応用例

数字の大小に応じて「順位」をつける関数がRank関数です。対象範囲の中に同数が入っているような場合に異なる計算方法をするために、Rank.AVE関数とRank.EQ関数を使い分けることができます。詳細はマイクロソフトの公式サイトで確認できます。ただし、集計対象が金額などのような場合、同数が存在するという可能性は低いのかもしれません。そのような時には、シンプルに =Rank(...) という関数を使えば大丈夫です。

集計や分析の中で、順位をもとに「並び替える」という行為が大きな役割を果たすことがよくあります。ただし、順位をもとに並び替えるだけなのであれば、備え付けの並び替え機能でことは足ります。では、どんな時にRank関数がその威力を発揮するのでしょうか。いくつかその応用例を紹介したいと思います。

例えば、今期の売上リストを表示する際に、前期の順位も表示する、ということが役立つかもしれません。または、過去〇期内での最高順位、のようなものはどうでしょうか。上位〇位に入った期の数、という情報も面白いかもしれません。音楽CDの売り上げランキングなんかでよく見る集計方法です。

これらの集計を行うには、Rank関数を使って、「順位を期ごとに並べた行列」を作っておけばよいのです。ピボットまたはSumifs関数でクロス集計したものを用意し、これをもとに別の場所にRank関数を用いて各期のランキング並べます。

期ごとの売り上げの集計：Sumifs関数を使った集計

期ごとの順位の行列：Rank関数を使って各期の順位を返す

セル"B12"には以下の式を入力します。

=RANK(B2,B$2:B$8)

最初の引数（B2）が対象の数値で、2番目の引数で全体の範囲を指定します。

最終的なランキング表に検索関数（Vlookup関数またはIndex関数）で引っ張ってきたり、その他の関数（ランクの最小値を返す関数にはMIN関数、〇位以内に入った期を数えるにはCountifs関数）、という流れです。

ランキング表：上記の順位の行列からの集計

前述した順位行列は「作業シート」というシートにあるとします。上記の「2016年の順位」、「過去の最高順位」、「3位までに入った年の数」を求めるための数式は以下の通りです。

2016年の順位：=INDEX(作業シート!$E$12:$E$18,MATCH(B4,作業シート!$A$12:$A$18,0))

過去の最高順位：=MIN(INDEX(作業シート!$B$12:$E$18,MATCH($B4,作業シート!$A$12:$A$18,0),0))

3位までに入った年の数：=COUNTIFS(INDEX(作業シート!$B$12:$F$18,MATCH($B4,作業シート!$A$12:$A$18,0),0),"<=3")

MIN関数やCOUNTIFS関数を使うにあたり、集計範囲を変動にするために、INDEX関数で範囲を返す方法を使いました（よろしければINDEX関数に関する記事もご確認ください）。

同じような考え方ではありますが、ある二つ順位列を比較し、大きな違いがあるのかどうか探る、という分析も可能です。シンプルな方法としては、対象１と対象２（店舗や地域など）の順位の差の絶対値を計算し、順位に大きな差がある項目を探す、という方法です。絶対値の計算方法はABS関数です。

使用している関数は以下のとおりです。

"D2": =RANK(B2,B$2:B$15)

"E2": =RANK(C2,C$2:C$15)

"F2": =ABS(D2-E2)

商品 "i" と "m" に大きな差（10と11）が見られますので、この違いを視覚的に見えるようにしてみます。

表にしてみると意外とインパクトがあります。場合によっては、金額や数字をそのまま使うよりも、順位が大きく変動していることを示す方が、メッセージとしては伝わりやすいかもしれません。

「複数」に対応した検索

関数を使った検索を行う際、「複数の結果を返す」方法や、「複数の条件」で検索するための方法について紹介します。これらは、以前にVlookup関数に関する投稿の際に紹介した「４つのVlookup関数の欠点」の3番目と4番目に対応するものです。

Vlookup関数やIndex関数では、これらの「複数の条件・返り値」に対応することは難しくなります。どうしてもこれらの関数で処理を行いたい場合は、元のデータテーブルに補足列を追加したり、またはデータをある条件にそって並び替えたりしている必要があります。場合によっては、それらの方が簡単で理解しやすいこともあると思いますが、ここではこれらの作業なしに、直接上記の二つの目的を達成することを試みてみます。

「複数の返り値」と「複数の条件」は一見異なる問題に聞こえますが、実は事の本質は全く同じです。それは「上から何番目にあるかを調べる」ということです。

例えば、「複数の返り値」の場合は、まず必要な情報は「ある条件のもと、△回目にヒットするのは、上から数えて〇番目のセルにある」ということです。同様に「複数の条件」の場合、知りたいのは、「条件が△個あり、△個の条件全てにヒットするのは、上から数えて〇番目にある」ということが知りたいのです。つまり、この〇に入る数、すなわち「上から数えて何番目か」をまず取得します。

この〇に入る数字を取得するために、簡単な配列関数を使用します。配列関数についてのオフィシャルサイトでの紹介は以下の通りです。

support.office.com

多くの場合、配列関数はその内容が複雑になりがちです。しかし、今回のケースはそこまで複雑なものにはなりません。

仮に、データテーブルに、1から始まる連番が記載されている列がある、と仮定しましょう。もちろん、そのような状況は考えにくいのですが、話を簡単にするため、最初はこの仮定を置きます。

まず「複数の返り値」についてです。例えば、上から数えて2番目に条件に合うセルが、上から何番目のセルなのかを調べてみます。式は以下の通りです。

=SMALL(IF(検索列１=条件１, 連番列), 2)

Small関数は、下から数えて△番目の値を返す関数です。

SMALL 関数 - Office サポート

IF関数で指定している条件に合う場合のみ、対応している連番(1,,,4,,,7,,,10,,,13,,,16,,,19,)の数字が配列として返り、更にその中から2番目に小さい数字(4)を最終的に返します。これで「上から数えて何番目か」が分かったことになります。

最後に、返ってきた４という数字をIndex関数の中で使うことにより、2番目に条件に合った行の値を返すことができます。

下のシートでは、検索列の中に検索値である「あ」があるか見ており、セルF6では、2番目に「あ」が見つかった行から参照値を返しています。式は以下の通りです。

=INDEX($C$2:$C$21,SMALL(IF($B$2:$B$21=$F$2,$A$2:$A$21),E6))

SMALL関数で、"４"という数字を返していることになります。

複数の条件を扱う場合は、IF関数の中の条件式（検索列=条件）を＊で掛けて繋げます。何故このようなことをするのか、という疑問があるかもしれませんが、詳しい説明は割愛します。ちなみに、ANDの場合は式を掛けて（＊）、ORの場合は式を足す（＋）という処理をします。

では最後に、「１から始まる連番がデータテーブルに存在している」という不自然な仮定を外した場合の処理を考えます。上記の式では、IF関数がTRUEの時に返す値として、この連番を指定していました。

ここに、ROW関数を使って取得できるテーブルの行番号の連番の値を入れます。データテーブルが始まる行番号はいつも同じというわけではないので、ヘダーの行番号で引き算すると、１から始まる連番が取得できたことになります。

では実際の処理を見てみましょう。セルF6には、２つの条件「あ」と「ア」で、2番目に両方の条件が合う行の参照値を返しています。式は以下の通りです。

=INDEX($C$2:$C$21,SMALL(IF( ($A$2:$A$21=$F$1)*($B$2:$B$21=$F$2),ROW($A$2:$A$21)-ROW($A$1)),E6))

最初の Row関数で｛2,3,4,5,...,21}という配列を指定し、二つ目のRow関数でヘダー行(1)を引くことで、1から始まる連番｛1,2,3,4,...,20}を取得しています。上の例と同様に、SMALL関数で「上から何番目の行か」を取得しています。

式が少し長くなってしまうのですが、行っていることはそこまで複雑なことではありません。また、配列関数ですので、式を入力した後はCtrl + Shift + Enterで配列関数にします。

複数の条件や複数の返り値を扱うケースというのは、実務上どこまで必要とされるのか分かりませんが、いざという時に使えるように備えておきたいものです。

Index関数 - より適応範囲の広い検索のために

Vlookup関数やHlookup関数は使用機会も多くとても便利ですが、万能というわけではありません。もっと一般性を持った検索ができるIndex関数を用いることによって、Vlookupではできなかった様々な検索や集計ができるようになります。

support.office.com

オフィシャルサイトでは様々な使い方が紹介されていますが、主に使うことが多いのは３種類ではないかと思います。

最初の使い方は、１列または１行の範囲から、検索を行う方法です。よく、Vlookup関数で対応できないような場合（返したい値の列が検索値の含まれる列より左にある、など）に用いられることがあります。

=INDEX(参照したい値を含む列, MATCH(検索値, 検索する値を含む列, 0)) 

指定した範囲の中で、（上から、または左から）何番目の数字か、を２番目の引数で指定します。ここに直接数字を打ち込んでしまってはあまりIndex関数を使うメリットがありません。そうではなく、行番号と列番号の取得にMatch関数を使って、検索に柔軟性を持たせる使い方をすることが多いのではないでしょうか。

２番目の使い方は、配列から交差するセルを見つける方法です。まず、検索範囲の配列を指定し、行番号（上から下に向けて何番目か）と列番号（左から右に向けて何番目か）を２・３番目の引数に指定します。前述したように、行番号と列番号に直接数字を打ち込むのではなく、Match関数を（2回）使いましょう。

=INDEX(参照したい値を含む範囲, MATCH関数, MATCH関数) 

３番目の使い方は、行番号または列番号に０（または空欄）を入力することにより、配列を返す方法です。関数の結果として配列が返ってくるということは、その配列をさらに別の関数でネストすることにより、様々な処理ができるということです。この使い方の方が、Index関数の便利さをよりよく実感できると思いますので、今回はこちらをメインに話すことにします。

例えば、経理やレポーティングで働く際に、以下のような構造のデータを定期的に受け取る、という場面があるのではないでしょうか。

垂直方向に会計科目や商品名、また水平方向に子会社や店舗、などです。当たり前のように目にする表です。しかし、実はこのような構造の表は、データベースから取り出すRawDataと比べると、集計や分析などを目的としたユーザーにとっては親切な構造ではありません。

理由は色々ありますが、上記のような分割表（クロス表）の主な欠点としては、データを様々な角度から集計・分析したいときに、（RawDataのテーブルに比べて）処理が煩雑になってしまうということです。例えば「支店ごと（縦）に、商品カテゴリA・B・Cそれぞれに属する商品の売上を（横に）集計」することを考えてみます。以下のような集計用のシートがあるとします。

Sumifs関数を使うことは容易に想像できますが、もとのデータでは店舗がそれぞれの列に広がってしまっているという問題があるため、このままでは直接集計することはできません。

また、当然ながらそのままピボットテーブルにすることもできません。実際に行ってみればすぐに分かりますが、店舗や子会社の名前がそれぞれ別の変数のタイトルのように扱われてしまうためです。

ここで、配列を返してくれるIndex関数が役に立ちます。Sumifs関数の集計範囲にIndex関数を使うことによって、左右に広がっている列の中から、自動的に対象範囲を選んでくれます。

=SUMIFS(INDEX(集計したい列を含む範囲,0, MATCH関数), 条件範囲1, 条件1, 条件範囲2, 条件2, ...)  

 (Sheet1にクロス表データがあります。）

0を入力するというのは、少し違和感があるかもしれませんが、「行は一つに指定せずに全部選択する」というイメージで捉えると理解しやすいかもしれません。

もちろん、Sumifs関数以外でも、配列を引数にする関数であれば何でも使用できますので、様々の指標の値を店舗ごとに並べることができます。Countifsでも、Averageifsでも大丈夫です。

上記で行っていることは、「２次元（縦と横）に広がる集計表を、フラットデータと同じように集計することを可能にしている」ということです。そこまで大したことないように思えなくもないのですが、ただ実際にIndex関数以外の方法で同じ集計を行おうとすると、不必要な過程が増えてブック全体の構成が混沌としてきます。是非、縦横の表を見たら「Index関数でそのまま集計できる」と覚えていても損はありません。

最初に触れたように、Vlookup関数は万能ではありません。Index関数の方が適応範囲が遥かに広く、「Vlookup関数でできることはIndex関数（Match関数と併用）で可能」です。しかし、Index関数もやはり万能ではなく、もう少し複雑な検索になると対応できないことがあります。そのような場合は、やはり配列関数の力が必要になります。

上記を踏まえると、エクセルでの検索機能の適応範囲は以下のようになっています。

Vlookup関数　＜　Index関数　＜＜　配列関数

作りをシンプルにするためにも、必要のない状況で難しい関数を使うことは避けましょう。「Vlookup関数は使ってはいけない」というような記事やコメントを目にしたことがありますが、そのようなことは絶対にないと思います。もちろん、Vlookupには多くの弱点がありますが、そこは状況に応じてIndex関数や配列関数を使い分ければよいのではないでしょうか。ただし、その「使い分け」ができず、Vlookupのみの限られた検索や集計ができないようでは、限られた見解しか得ることができません。Index関数は適応ケースが非常に多いですので、是非ともマスターするようにしてください。

Average関数 - 平均値と外れ値

何かの「平均値」を計算したり、または平均値を時系列に並べてみる際に、なんとなく数字が大きすぎるような気がしたり、期によって数字が異様にばらつくことがあります。例えば、新規会員数の月平均、または、年間の購入額の1人当たりの平均、というようなケースです。収益やコストの大きな増減に一喜一憂する前に、まず考えるべきは「なぜそのような大きな変化が発生しているのか」です。もし、それらの変化を説明できるようなトレンドに身に覚えがないのであれば、まず最初に疑うべきは「外れ値」の存在です。外れ値は全体の数字の分布から、上方または下方に異常に大きく外れている値のことを指します。マイナスを取らない数字を扱う場合（顧客数、金額、など）には、上方の外れ値が発生しやすくなります。いくつかの極端に大きな数字のために、集計値（合計や平均値）が上方に引っ張られてしまいます。

例えば、新しい店舗を開いた場合の施設のメンテナンスコストを予測したい場合、「既存の店舗の延床面積当たりのコスト」を新設店舗の床面積で掛けることによって算出するのが自然でしょう。では、「既存の店舗の延べ床面積当たりのコスト」はどのように計算したらよいでしょうか？まず思い浮かぶ方法は、各店舗の床面積当たりのコストの「平均値」でしょう。しかし外れ値がある場合には、その平均値が大きく上方に引き寄せられてしまうかもしれません。特に、平均を計算する際の個体数（店舗数）が少ない場合には、その影響は計り知れません。つまり、コストを過大に予測してしまう危険があります。

平均値を直接計算するAverage関数は非常に便利なのですが、外れ値のような性質の数字を全く考慮しておらず、集計があまりに無機的過ぎるように思える時があります。

AVERAGE 関数 - Office サポート

そこで、平均値が重要な役割を果たすようなケースでは、Average関数を使う前に、まず外れ値の有無を確かめてみましょう。外れ値を確認する簡単な方法は、グラフ機能の中の「箱ひげ図」(Boxplot)を用いてデータを視覚化することです。

エクセルの箱ひげ図での外れ値の判別の方法ですが、第3四分位＋1.5 x (第3四分位 - 第1四分位) より大きい数が外れ値となっています（上側のみ）。

外れ値がないようであれば、そのまま平均値を計算してもまず問題ないでしょう。また、外れ値の定義にかかる値があるとしても、ヒストグラムの分布が滑らかであれば、あえて外す必要はありません。

しかし、分布が滑らかではなく、突如大きな数字が表れているような場合には、それは外れ値と見てよいでしょう。では外れ値がある場合にはどのように処理したらよいでしょうか？シンプルな方法は、そのような外れ値を対象から外した後に平均を集計することです。箱ひげ図を見て、ある値以上の数字は外れ値であると確認できた場合には、境界線より小さい値のみを平均を計算する、つまりAverageif(s)関数ですぐに計算することができます。

AVERAGEIFS 関数 - Office サポート

もしくは、平均値ではなくて、外れ値の影響を受けにくい「中央値」(Median関数)を使用するという方法も適切かもしれません。データの上下〇〇％を除外した上で平均を計算するTrimmean関数も可能でしょう。フィギアスケートの採点で最高点と最低点がカットされているのと同じような計算です。または、「期待値」を計算したいのであれば、集計対象のデータが確立分布を反映していなければなりません。場合によっては、一番出現率の高そうな「最頻値」の方が目的にあっているかもしれません。どの方法が適切なのか決定するために大切なことは、そもそものビジネス上での目的は何であるのかを見失わない、また実際に箱ひげ図やヒストグラムなどのデータを自らの目で見て判断する、ということです。

また、データが十分あるのであれば、目的となる対象と性質の近いデータをのみを使った集計をするべきです。例えば、新規店舗のコスト予測のために床面積当たりのコストの平均を知りたい場合、予定されている新規店舗の大きさが似ており、同じ地域にある既存の店舗を集計対象をします。特に光熱費などは、店舗の規模によっては固定費と変動費の割合が大きく異なるかもしれません。また料金も地元の電力会社によって異なります。

仮に箱ひげ図と同様の定義を採用するとして、外れ値を除いた平均値を計算してみましょう。Averageifs関数は、Sumifs関数と非常に似ている関数ですので、全く難しいものではありません。問題は、外れ値の境界線の値を求める方法です。前述したとおり「第3四分位から箱の高さの1.5倍を乗せた高さ」が外れ値の境界線となります。単純に考えれば、境界線の値は以下のように計算できます。上記の

=QUARTILE.EXC(集計範囲, 3) + 1.5*(QUARTILE.EXC(集計範囲, 3) - QUARTILE.EXC(集計範囲, 1))

上記の計算で返された値を境界線とし、Averageifs関数で外れ値を除いた平均値を計算できます。

充分にデータがあるのであれば、全体の中から特定の条件に合ったものだけ（規模や地域など）で境界線を求めましょう。四分位の計算には、条件を指定できるような単独の関数はありませんので、配列関数を使って集計範囲を条件で絞ります（Ctrl+Shft+Enter）。あとは、この値を参照してAverageifs関数を使い、平均値を求めます。

外れ値は除外する、という考えは間違っていないと思いますが、逆に外れ値を探して活用することも可能かもしれません。データの分析というと何か難しいイメージを持つ人もいるかもしれませんが、何か他とは結果が大きく異なるケースを探す、ということも新しい洞察に繋がるかもしれません。ある数字が異常に高いもしくは低いような場合は、そこに探るべき成功事例または修正が必要な課題が潜んでいるのかもしれません。

Countifs関数 - ヒストグラム

売上や支出の金額などの数字が含まれているデータがあるときに、恐らく最も手軽に計算が可能で、かつ関心が高そうな情報は、その「合計値」でしょう。次いで、各支店や各商品ごとの「小計」や、一人当たりの購入の「平均値」、もしかしたら「最大値」や「最小値」も有益な情報かもしれません。去年の売上からの「成長率」や、コストの「削減率」もきっと大切な数字です。

上記に挙げた例は一つの「数字」です。単位は通貨かもしれませんし、同じ単位で割っていれば「割合」になります。とても大切な情報に思えますが、これらの数字から得られる情報量は非常に限られています。もし会社の誰かが「売り上げの合計が1000万円である」と言った場合、極端な話ですが、一人の客が1000万円の商品を買ったのか、もしくは1000人が一万円の商品を買ったのか、の区別はつきません。「顧客あたりの平均値が10万円だった」という情報が追加されたとしたらどうでしょうか？もう少し全体像が見えてきた気がしますが、しかしそれでも分かることは「購入者の数が100人であった」ということだけです。99人が1万円の購入をし、残りの1人が901万円の購入をした可能性はゼロではありません。来期の平均購入額も10万円前後になるであろうと予想するには早すぎます。上記で見たように、平均値や合計値のような集計された数字は、便利で説得力があるように見える反面、全体の様子を捉えるための情報を失い過ぎていることがよくあります。

そこで、「〇〇万円前後の購入の客が〇〇人、△△万円前後の購入の客が△△人」というように、「一定の金額幅に属する顧客の数の「分布」」を捉えることを考えてみたいと思います。一般的に、ヒストグラム、と呼ばれているものです。この分布を視覚的に捉えることができるようにすることを、とりあえずの最初の目標とします。求めたいのは、平均値のような「数」ではなく、目で確認できる「分布」の形です。この場合、シンプルな集計のみで、なおかつ大切な情報をなるべく失わなずに全体の様子を表現することができます。

まず金額の幅を決めます。例えば、5万円ぐらいの幅にするとすれば、5万円きざみの数字を縦に並べます。あとは、これらの数字を引数としたCountifs関数を使えばすぐに結果が求まります。あとは、グラフにすれば、最初の目的は達成できたことになります。

このヒストグラムには失われていない多くの有益な情報が残っています。ヒストグラムを視覚的に見ることにより「およそ〇〇円の購入があった顧客が〇〇人」ということはもちろん分かります。また、「一番多いのは何円くらいの購入者で、全体の何割くらいか」、「左右どちらの裾が長いのか」、「異常に高い金額の購入者はいるのか」など、より深い洞察を与えてくれます。そして、よっぽど複雑な形をしていない限り、その平均値や中央値、最頻値、分散、最小値、最大値、などなど、正確な数字は一見するだけでは分からなくても、おおよその情報はすぐに頭に入ってきます。

ヒストグラムを作るために多くの情報が必要になることはありません。顧客または支店ごとなど、一定の規則に従って集計された「数字」が一列さえあれば良いのです。

上記ではCountifs関数を使いましたが、エクセルにはすでにFrequencyという関数があり、ヒストグラムを作るための関数が存在します。もちろん、このFrequency関数を使えば全く問題ないのですが、配列関数として処理されるため、扱いになれていない人が多いかもしれません。また、Excel2016からはヒストグラムのグラフ作成機能が追加されていますが、やはり関数で地道に作成したほうが、その後のレイアウトの調整や分析がしやすい気がします。

さて、上記のような単純なヒストグラムの少し寂しいところは、「一時点の集計しか行えず、時系列の流れを追いにくい」という点があります。場合によっては、年度ごとにいくつかのヒストグラムを並べることもできます。しかし、もし顧客ごと、または支店ごとなどの細かい情報が揃っているのであれば、そのようなヒストグラムの並列は個体レベルの動きを無視していしまっているので、集計の方法としては非常にもったいないです。

仮に、ある2016年から2017年までの2年間の顧客レベルの売上のデータがあるとして、「最初の年の購入額と次年度の購入額」をもとに、両年でのヒストグラムの集計を試みます（2変数ヒストグラム）。まず縦と横に全く同じ金額幅の数列を並べます。仮に、縦の軸を2016年とし、横の軸を2017年とします。そして、2016年と2017年の購入額をもとにCountifs関数で集計を行います。すると以下のような表が出来上がることになります。

数字のままだと理解しづらいため、条件付き書式で色をつけてみます。言うまでもなく、対角線付近に数えられている顧客は「2016年も2017年もおよそ同じくらいの購入額であった顧客数」です。また、左下の三角形に集計されている顧客は、「2016年に比べ、購入額が2017年に減った顧客の数」となり、逆に右上の三角形は、「購入が増えた顧客の数」です。

この表を見ることによって、顧客レベルの購入額の大まかな変化を、視覚的にイメージすることができます。新規の顧客が多ければ、上端の範囲の色が濃くなりますし、逆に顧客を失っているようであれば、左端の範囲の数字が濃くなります。

もちろん、全体のデータに対して上記のような表を作ってもよいですが、何かしらの条件で絞り（商品カテゴリーなど）、これらの表を見比べる、ということも分析としては面白いかもしれません。Countifs関数の条件を一つ追加するだけで簡単に行うことができます。

最後に、これらの2次元の表から、各範囲ごとの数の小計を行ってみましょう（セルD16:D18）。例えば、対角セルのみの合計を行いとするならば、どのようにしたらよいでしょうか。簡単なように聞こえて、以外と苦労する人が多いのではないでしょうか。作業列を追加するなど様々な方法があると思うのですが、恐らく以下の関数が一番すっきりしているのではないでしょうか。

=SUM(IF(C5:C14=D4:M4,D5:M14,0))

 配列関数なので、関数入力後にCtrlとShiftを押しながらEnterをたたきます。同様に、左下の三角形の小計、また右上の三角形の小計は、等号を不等号に変えるだけで行うことができます。

=SUM(IF(C5:C14>D4:M4,D5:M14,0))

=SUM(IF(C5:C14<D4:M4,D5:M14,0))

Frequency関数でも触れたのですが、配列関数は扱いが難しい時があり、その存在すら知らない方が多くいます。しかし、その便利さを知ると、配列関数なしに集計作業をすることが考えられなくなります。まだご存知でない方は、是非何かの機会に習得されることをお勧めします。

Countifsについて

Countifs関数は複数の条件に当てはまる対象の個数（カウント）となります。Sumifs関数とは違い、Countifsは数を数えているだけですので、当然ながら引数の中に集計列はありません。Sumifs関数と同様に、一つの条件のみを扱うCountif関数は使う意味はありません。条件の数によらず、常にCountifsを使えばいいのです。

=COUNTIFS(検索範囲1, 検索値1, 検索範囲2, 検索値2, …)

以前にも言及したのですが、データの集計や分析をする上で、常に複数の条件によるデータの切り分けを意識していることが大切です。その意味でも集計・分析をする際には、とりあえず何も疑わずに、Sumifs関数およびCountifs関数を用いてクロス集計を行う習慣を身につけてみましょう。

しかし、経理・財務における集計において、多くの場合において集計対象の単位は「金額」です。「売上」や「支出」の小計が切り分けによってどのように異なるのか、ということが関心となることがほとんどでしょう。そのような理由で、Sumifsはよく使うけれども、Countifsはそうでもない、という方をよく目にします。

確かに、業務内容によっては「条件に合った対象を数える」という行為は、あまり意味がないのかもしれません。例えば「月ごとの売上小計を支店ごとに集計する」「カテゴリーごとの商品の売上小計を年代ごとに集計する」というようなケースでは、Countifs関数の出番は全くありません。ただしそのような単純な金額の集計から、何かしらのストーリーにたどり着くことは難しいでしょう。上記の例では、「特定の支店・商品の売上が増えている、または減っている」というような事象は確認できますが、その背景に「何が起きているのか？（WHAT）」ということを知ることはできないからです。「何が」が分からなければ、より大切な「なぜ？（WHY）」を確認することは当然できません。

Whatとして捉えたい数字の例は、「支店ごとの新規の顧客の数」、「支店ごとで今期から購入がなくなった顧客の数」、「〇〇円以上の購入があった顧客の数」、「地区ごとで売上が増加・減少した支店の数」、「商品ごとの、値下げする前・後の販売数」、「商品ごとの、商品Aと一緒に購入された回数」、などです。対象が「顧客」、「商品」、「支店」などとなるので、起きている現象をより明確に把握することができます。よりマネージャー視点の見方ができるといっても良いかもしれません。

ですので、「Sumifsを使う機会がある場合には、いつもCountifsも使って何かしてみる」ことをお勧めしたいと思います。依頼されている業務とは一見関係がないように見えるとしても、です。多くの場合、金額だけでは見えない動きや傾向を捉えることができます。

例として、上記で言及した「支店ごとで今期から購入がなくなった顧客の数」を計算してみます。仮に、以下のようなデータ（Raw Data）があるとしましょう。

このRaw Dataをピボットテーブル、もしくはデータリボンの「重複の削除」機能を使って（個人的には後者をお勧め）、「顧客IDと支店IDの組み合わせ」のユニークリストのテーブルを作成します。

この新しいテーブルに前期と今期の売上の列を追加します。仮に前期を2016年とし、今期を2017年とするならば、Sumifs関数を使い、条件に顧客IDと支店ID、更に年（2016年、または2017年）を指定します。このテーブルをテーブル１と呼ぶことにしましょう。

（Raw Dataに「年」というフィールドがないので、事前にそのような列をRaw Dataに追加してもいいですし、もしくはSumifs関数の条件に〇〇年1月1日以降、かつ〇〇年12月31日以前、というように条件を指定しても大丈夫です。ちなみに、ピボットテーブルを使えば、もちろんこの作業はもっと簡単に行えますが、空欄ができてしまうなど後々不都合が生じます。）

最後に「支店ID」のユニークリストテーブルを別に用意します。こちらをテーブル２と呼ぶことにしましょう。これで準備が整いました。

ここで、テーブル２に列を追加し、Countifs関数を使って以下のような条件を指定します。

条件１：支店ID

条件２：2016年に売上がある (>0)

条件３：2017年に売上がない (=0)

これで、先に掲げた目的は達成できました。もう少し踏み込んだ集計をするとすれば、同じ要領で「2017年の新しい顧客」も追加して、2016年から2017年の顧客数の変化を説明してみましょう。関数の中の条件は以下の通りです。

条件１：支店ID

条件２：2016年に売上がない (=0)

条件３：2017年に売上がある (>0)

2016年と2017年の顧客数をそれぞれ追加すると、支店ごとの顧客数の増減の詳細を確認することができます。

つまり「2016年の顧客数 - 離れていった顧客数 + 新しい顧客数 = 2017年の顧客数」の動きを各支店ごとに集計できたことになります。割合を計算すれば、「リピート率・定着率」などを比べることもできますし、支店の規模に対する新規の顧客数の比較もできるかもしれません。単純に「顧客が増えた・減った」ではなくて、その内訳を見ることによって各支店の強みや課題が見えてきます。ここでは支店ごとの比較を示しましたが、もちろん商品やその他の変数で置き換えても同じような比較を行うことができます。

全く難しい技術を必要とせずに、何かしらの洞察に繋がる可能性を持っているのがCountifs関数です。もちろん、これらの集計だけでは問題の根底にたどり着くことは決してないでしょう。しかし、その糸口を見つける第一歩となり得ます。

データを見る際にまず考慮すべきことは、意義のある比較ができるようにデータを切り分ける、ということです。そのような意味においても、Countifsの重要性を感じていただけるのではないかと思います。