EXCELで会員管理(第5回)期間内の入会数を一瞬で集計するには?

エクセルで時間を損しない仕事術

データ管理で困らないEXCEL術

誰も教えてくれないWORD技

情報技術ライダーのワード・エクセル研修



前回まで

いきなり1000件の会員情報をエクセルに入力しなければいけなくなったミカミ。株主総会というイベントを間近に控えた多忙な毎日の中で、万全な事前準備で順調に入力を終え、またブチョーのルールお構いなしのデータも、関数や置換機能を駆使し、わずかな時間で修正を終えることができた。住所から都道府県を取り出す、ネット上に公開されている郵便番号データの活用方法も覚え、いよいよ会員数を自動計算する事になるが・・・。

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

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

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

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

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

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

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

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

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

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

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

●このエントリの内容

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

わかりやすくするために作業列を使う考え方

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

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

数式例 意味
=COUNTIF(範囲,"対象") 対象と入力されているセルの個数を求めます。
=COUNTIF(範囲,">="&B2) B2の値以上が入力されているセルの個数を求めます。
=COUNTIF(範囲,"*ゴ") 値がゴで終わるセルの個数を求めます。
=COUNTIF(範囲,"*") 値が入力されているセルの個数を求めます。
=COUNTIF(範囲,"<>"&"*") 値が入力されていないセルの個数を求めます。

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

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

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

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

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

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

countif01

なるほど~。

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

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

countif00

今現在の会員数をカウントするには?

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

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

countif02

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

countif00

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

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

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

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

期間内の入会と退会の人数をカウントするには?

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

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

countif00

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

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

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

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

countif03

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

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

countif04

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

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

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

指定期間の終了日時点での会員数をカウントするには?

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

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

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

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

countif06

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

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

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

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

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

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

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

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

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

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

最後に

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

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

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

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

企業の業務改善を徹底サポート

経理総務の残業削減・業務効率化ならお任せ下さい!

情報技術ライダー

  • 上場企業で業務効率化に成功したExcelスキルをわずか1日で習得
  • 管理会計業務、データ入力、データ集計(CSVデータの処理)でのエクセルを徹底的に効率化
  • 貴社に出向いての研修やExcelマクロの制作も承ります!

コメントは受け付けていません。

このページの先頭へ