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