EXCELで会員管理（第６回）管轄支店毎の会員数を一瞬で集計するには？
前回まで
いきなり1000件の会員情報をエクセルに入力しなければいけなくなったミカミ。株主総会というイベントを間近に控えた多忙な毎日の中で、万全な事前準備で順調に入力を終え、またブチョーのルールお構いなしのデータも、関数や置換機能を駆使し、わずかな時間で修正を終えることができた。住所から都道府県を取り出す、ネット上に公開されている郵便番号データの活用方法も覚え、いよいよ会員数を自動計算する事になるが・・・。
いよいよ管轄支店毎の集計になるけど、特別に難しくなる訳じゃなく考え方は前回の延長。作業列を使ってカウントする対象か対象外かを判定し、それをCOUNTIF関数で数えるという考え方に、管轄支店の条件を加えるだけなんだよ。
それもCOUNTIF関数でできちゃうんですか？
COUNTIF関数でもできる。けど答えは１つじゃないよ。ケースバイケースで楽な方法を選択できるようになろう。
●このエントリの内容
COUNTIFS関数を使って複数条件でカウントする
それじゃあ、こんな感じで、管轄支店毎に、指定した期間内の入会者数、退会者数、終了日時点での会員数、現在の会員数を集計してみよう。
まず、期間内の入会数を管轄支店毎に求めてみよう。
会員情報シートのG列に管轄支店が入力されている。期間内の入会者かどうかはL列で判定できるよね。
２つ以上の検索条件範囲、検索条件に一致するセル個数を求める場合はCOUNTIFS関数を使うんだ。
カウントイフズですか？
そう。COUNTIFにSがついて、COUNTIFSね。
北海道支店の期間内の入会者数は、会員情報シートの
・G列の管轄支店が北海道である
・L列の期間内の入会者が対象である
この２つの条件を満たす場合のセル個数になるよね。
それを数式にするとこうなる。
=COUNTIFS(会員情報!$L:$L,"対象",会員情報!$G:$G,$B7)
続けて期間内の退会者数はこうなる。
=COUNTIFS(会員情報!$M:$M,"対象",会員情報!$G:$G,$B7)
終了日時点での会員数は・・・もうわかるよね？
=COUNTIFS(会員情報!$N:$N,"対象",会員情報!$G:$G,$B7)
じゃあ現在の会員数は、
=COUNTIFS(会員情報!$K:$K,"対象",会員情報!$G:$G,$B7)
これで良いんですね？
第一引数の検索条件範囲１が変わるだけなんですね？あとはみんな同じですもんね。
そう。あとはズリズリっとコピーしてあげると、管轄支店毎の集計が完成って訳さ。
おーすごいです。あとは入会者が増えた時にデータを追加して、退会者が出たら退会日を入力して、あとは期間だけ指定すれば必要な集計ができるんですね。もうちょっとしたシステムですね！
COUNTIFS関数を使うと、余計な作業列を使わずに複数条件でのカウントが可能なのがメリットなんだ。
デメリットもあるんですか？
COUNTIFS関数はExcel2007以降に追加された関数だから、それ以前のバージョンでは使えない。あとは３つ以上の検索条件を指定すると、数式の引数が多くなりすぎてわかりづらい。という点かな。
COUNTIFS関数だと古いバージョンのEXCELでは集計できない？
え、古いバージョンのエクセルで使えないってどういう事ですか？古いエクセルでも新しいバージョンのエクセルって開けましたよね？
互換パックの事を言っているのかな？互換パックは古いバージョンのエクセルでも、最新バージョンのエクセルファイルを開く事はできるんだけど、新しく追加された機能や関数まではサポートされていないんだ。つまりCOUNTIFS関数はExcel2003ではエラーになってしまうんだ。
Word/Excel/PowerPoint 用 Microsoft Office 互換機能パック
でも、もうExcel2003もサポート終了しているんですよね。もう古いバージョンの事は気にしなくても良いんじゃないですか？
それは正論すぎる正論なんだけど、そうは言ってもまだまだ使っている人が残っているから、気にしてあげた方が親切だよね。
それにね、COUNTIFS関数でできる事はCOUNTIF関数でもできるんだ。むしろ3つ以上の検索条件でカウントしたい場合は、作業列は必要になるけどCOUNTIF関数の方がわかりやすくなるんだ。より汎用的な方法を知る、使えるようになった方が活用の幅が広がるよ。
EXCEL2003でもCOUNIF関数と作業列を使えば複数条件でカウントできる（3つ以上の条件なら作業列を使うべし）
でも、COUNTIF関数だと検索条件は１つしか指定できませんよね？それなのにどうやってやるんですか？
作業列の使い方と、検索条件の指定方法にコツがあるんだ。
また別に作業列作るってことですか？それはメンドイんですけど。
いや、対象、対象外を判定する作業列に、管轄支店を加えるだけなんだ。
既にある数式の先でも後でも良いんだけど、
=G2 & IF(AND(I2>=開始日,I2<=終了日),"対象","対象外")
と、管轄支店を結合するんだ。
G列に入力されている管轄支店を、人数をカウントするための作業列全てに付け加えるんですね。
そして集計表側には、こんな風に数式を入力する。
=COUNTIF(会員情報!$L:$L,$B7&"対象")
ポイントは検索条件の指定方法。B7に入力してある管轄支店と対象を結合して指定する。
作業列で管轄支店、対象・対象外の順で結合したから、COUNTIF関数の検索条件でも、管轄支店、対象の順で結合するんですね。
そうだね。あとは範囲の指定が変わるだけ。退会数なら
=COUNTIF(会員情報!$M:$M,$B7&"対象")
終了日時点での会員数なら
=COUNTIF(会員情報!$N:$N,$B7&"対象")
現在の会員数なら
=COUNTIF(会員情報!$K:$K,$B7&"対象")
へー、こんな使い方もできるんですね。想像もつきませんでした。発想って大切ですね。これならどのバージョンのエクセルでも対応できるし良いですね。
COUNTIFS関数やSUMIFS関数で、３つ以上の条件で集計したいような場合は作業列を使って何を集計方法を使うと、何を集計しているのかわかりやすくなる、どんなバージョンのエクセルでも対応できる、というメリットがある。逆にデメリットとしては作業列を作らなくちゃいけないという事かな。
私は説明聞いたし簡単にコピーできる事もわかっているから良いですけど、データが増えた時に、作業列の計算式をコピーし忘れた！ってなりそうですもんね。ブチョーには触られたくないですね！
最後に
全６回でお届けするエクセルで会員管理について。
最終回はCOUNIFS関数を使う方法と、作業列を使ってCOUNIF関数でカウントする方法になります。
作業列を使って複数の検索条件で集計する方法は、Excel2003以前でも使えるというのもありますが、何よりわかりやすく応用が効くという点でぜひマスターして欲しい技です。これはSUMIF関数でもよく使用します。
定例の集計業務は、フィルタで絞り込んで件数を数える、ピボットテーブルで毎回集計する、という方法が使われがちですが、今回のようにSUMIF関数やCOUNTIF関数を使って集計するための計算式を仕込んでおき、データを入力するだけで自動集計できるようにしておくと圧倒的に手間もミスも減ります。
