EXCELで会員管理（第５回）期間内の入会数を一瞬で集計するには？

第４回　住所から市区町村は取り出せる？

それじゃあ、いよいよ会員数を数える方法に入るね。
会員数はデータを入力していくだけで自動計算できるように数式を組み立てるんだ。わざわざ人数を数えて集計表に入力する。こういう事をやっていると、いつまで経っても時間がかかるし、間違えも減らないんだ。

長かったですねー。たどり着くのか不安になりましたよ。
そして、この長いエントリーが果たしてどれだけ読んでもらえるのか。

それは言っちゃいけないコトだぞ。

これで、管轄支店毎の集計も一瞬でできるってコトですね。でも難しい数式になるんじゃないですか？

管轄支店までの集計を一気にやると難しくなるから、まずは現在の会員数を求める、期間内の入会数と退会数を求めるという事をやってみよう。管轄支店毎の集計は、それにちょっと手を加えるだけなんだ。

エクセルが上達したければ、今回説明するようにデータを入力するシートと集計を行うシートをわけて作れるようになる事。これが必須なんだけど、実はこれがなかなか理解してもらえない事でもあるんだ。

わたしも、今回の会員管理がなかったら、給与計算システムの帳票の数字をみてエクセルに入力する、電卓で計算した数字をエクセルに入力する、そんな、ちょっと計算機能が便利なワープロ的な使い方しかしていませんでした。
誰がやってもできるし、スピードも大して変わらないし、そんなにスキルアップなんて考えなくても良いかなーなんて。

エクセルは道具だから、どう使うかが大切なんだよ。
特に事務系で毎日エクセル使って数字に関わる仕事をしているのに、エクセルなんて少し出来れば充分、大しできなくても問題無いって言う人は、もしかしたら「あなたが任されてい仕事が大した事じゃない」のかも知れないよね。毎日大した事ないものに時間を費やしていて大丈夫なのか？という疑問は持った方が良いと思うんだ。

そんな不安を煽るような言い方しないでくださいよ。私も何のスキルも無いから不安なんですよー。

いやー、キミは大丈夫だと思うよ。行動力あるしメゲないし度胸あるし。

それは褒められているんでしょうか？

●このエントリの内容

1. わかりやすくするために作業列を使う考え方
2. 今現在の会員数をカウントするには？
3. 期間内の入会と退会の人数をカウントするには？
4. 指定期間の終了日時点での会員数をカウントするには？
5. 最後に

一応、登場人物の設定についてはこちら

今回は条件に一致するデータ件数を数えたいからCOUNTIF関数を使う。
SUMIF関数もそうなんだけど、第二引数の検索条件を工夫すればいろんな集計ができるんじゃないか？と考えてしまうとドツボにはまっちゃう事になるんだ。

例えば、COUNTIF関数でこんな指定をすると、第一引数の範囲で指定したセル範囲のうち、条件に一致するセルの個数を求める事ができるんだ。

記号が多くて難しそうです・・・。これ全部覚える必要があるんでしょうか。

いろいろな検索条件を知っておいた方が良いのは確実だけど、実は一番上の =COUNTIF(範囲,"対象") と言うパターンだけ覚えておけば後は応用で何とかなるんだ。

数える対象か、対象外か、その２択で考えた方が解りやすいよね。そういう状況を作るようにするんだ。

え、でも全てが対象、対象外の2択にはなりませんよね？

だから作業列を使って、そういう状況を作るんだ。

下図のK列以降が作業列。対象か対象外かで判定して、対象だけを数える。これなら =COUNTIF(範囲,"対象") というパターンだけで大丈夫でしょ？

なるほど～。

それにね、COUNTIF関数の検索条件では、指定が難しい割に細かい条件指定ができないんだ。ちょっと美しくない感じもするだけど、こうやって作業列を使う方法がわかりやすいし、数式も入れやすいからオススメなんだ。

じゃあ今回は作業列をつくり、その作業列を使って下図のような集計してみよう。

今現在の会員数をカウントするには、退会日が入力されているかどうかで判断できるよね。会員情報を入力したシートに作業列を足していく。

K2のセルにIF関数を使って、退会日が空白だったら対象（現在会員）、そうでなかったら対象外（退会済み）と入力する数式を作り、データの最終行までコピーする。まずはこんな風に作業列を作っておくんだ。
=IF(J2="","対象","対象外")

そしてその作業列をCOUNTIF関数で数えるんだ。
集計用シートのC8のセルに、こんな数式を入力すれば、現時点の会員数が計算できる。会員情報シートのK列に「対象」というセルが何個あるか？という意味になる。
=COUNTIF(会員情報!K:K,"対象")

なるほどです。でもこれなら、わざわざ作業列使わなくても、COUNTIF関数だけでもできるんじゃないですか？

うん。できるんだけど、これは作業列使う、これは使わないってのが、混在していると後から見た時わかりづらいからね。数える対象が１つだけ、現時点の会員数を求めるだけで良いならCOUNTIF関数のみでやっちゃうけど。

同じようにしておいた方がわかりやすいって事ですね。いろいろ考えているんですね。

考えているというより実体験からだよ。

次は開始日と終了日の期間内の人数を求めるんだけど、数式がわかりやすくなるように、B2には開始日、C2には終了日と名前定義しているからね。

で、この開始日と終了日の期間内に入会した数、退会した数を求める。

会員情報を入力したシートのL2セルに、IF関数を使って、入会日が開始日以上で、入会日が終了日以下だったら「対象」、そうでなかったら「対象外」と求めて、データの最終行までコピーしておく。そしてその列の「対象」だけをCOUNTIF関数で数えるという形にする。

考え方は今現在の会員数を数える時と一緒ですね。作業列に対象か対象外か明確にしておいて、対象のものだけ数える。
あ、でも入会日が開始日以上で、入会日が終了日以下って２つの条件はどうやって判定するんですか？

数式はこんな風になる。
=IF(AND(I2>=開始日,I2<=終了日),"対象","対象外")

AND関数を使って、入会日が開始日以上で、入会日が終了日以下を判定するんだ。両方の条件を満たせばTRUE、そうでなければFALSEを返すんだ。

ちょっとわからなくなりました・・・。
AND関数を使って入会日が開始日以上で、入会日が終了日以下だったらTRUE、そうでなかったらFALSEを求めるって事ですね。
IF関数で、そのAND関数の返り値がTRUEなら対象、そうでなかったら対象外を求めるんですね・・・。

そうそう。退会日も参照するセルが変わるだけで同じだね。
=IF(AND(J2>=開始日,J2<=終了日),"対象","対象外")

最終的に、期間内の入会者数を求める数式はこうなる。
=COUNTIF(会員情報!L:L,"対象")

じゃあ退会者数はこうですね？
=COUNTIF(会員情報!M:M,"対象")

COUNTIF関数はみんな同じ使い方ですね。範囲が変わるだけで。
どんな条件でカウントするかは作業列を見れば良いし、COUNTIF関数は使い方が同じだし、確かに解りやすいですね。

最後に期間の終了日時点での会員数を求めるという難題をやってみよう。

え？期間の終了日時点での会員数ってどういう事ですか？

例えば、去年の12月末時点の会員数はどうだったのだろうか？と過去に遡って人数を調べたい場合になるね。
その場合、1月以降に入会した人、1月以降に退会した人を除いてカウントする必要があるよね。

作業列の数式もこんな感じで難しくなるんだ。
=IF(AND(I2<=終了日,OR(J2="",J2>終了日)),"対象","対象外")

ひゃー、AND関数の中にOR関数がありますよ。これは無理ですー。

じゃ、その部分を重点に説明するね。
AND(I2<=終了日,OR(J2="",J2>終了日))

まず、I2<=終了日が、入会日が終了日以下ならTRUEという意味なのはわかるよね。

続いてOR(J2="",J2>終了日)で、退会日が空白か、退会日が終了日より大きければTRUEという意味なのも良いかな。

この条件を両方満たすなら対象。そうでなければ対象外となる。

退会日の方はどうしてOR関数を使うんですか？J2>終了日だけじゃダメ・・・
あっ！退会日って退会した時に入力するから、普通は何も入力されていませんもんね。終了日より後に退会した人だけの判定じゃダメなんですね。

でも、ややこしいですね。
こういうのもパッと書けちゃうですか？どうして思いつくんですか？どんな頭の構造しているんですか？

いやいやいや、これも何度も失敗してるよ。実は失敗の方が多いんだよ。今回だって書いているうちに訳が分からなくなって、作業列を２つ作って検証して、１つにまとめるなんて事をやっている。そして、正しくできているか不安だから何度何度も確認してるよ。

へー、そうなんですね。意外です。

意外なもんか。こうやったら出来るんじゃないか？と仮説を立てて、実際にやってみて、失敗して、失敗の原因を考えて、こうやったら出来るんじゃないか？というスパイラルを何回も繰り返すからスキルって習得できるんだ。だから失敗の方が遥かに多いんだよ。

全６回でお届けするエクセルで会員管理について。

第５回目はCOUNTIF関数を使って会員数をカウントする方法になります。

COUNTIF関数は検索条件が指定でき、色々な条件を指定する事ができるので、その検索条件を工夫すればいろんな集計ができるんじゃないか？と考えてしまうと飛んだ苦労を強いられる事があります。集計を楽にするには、今回のように集計しやすい作業列を作る。その発想が大切なんです。

作業列を使わずにやろうとすると、数式が複雑になりやすくなります。複雑な数式は作れると、作った人には妙な満足感がありますが、作った人しか解析できないものになってしまいます。できるだけシンプルでわかりやすい様に構築する。そんな意識をもっておくと良いと思います。

• 第１回　とにかく1000件のデータを入力しなくちゃいけなくなったのですが？
• 第２回　ブチョーの入力した変なデータを直すのが手間なんですが？
• 第３回　住所から都道府県を取り出し対応する管轄支店を入力する？
• 第４回　住所から市区町村は取り出せる？
• 第５回　期間内の入会数を一瞬で集計するには？
• 第６回　管轄支店毎の会員数を一瞬で集計するには？