エクセルで期間別の在籍人数をカウントする方法を教えてください

エクセルで「元データ」のシート情報から
各クラスの月別の在籍人数を「月別一覧」のシートに反映する方法を教えて頂きたいです。
1日/月でも在籍していれば「1」としてカウントします。
例）
※入　2020/6/9　出2020/7/8　の場合　2020年6月と7月にそれぞれ1カウントずつ
　入　2020/6/9　出2020/6/30 の場合　2020年6月のみに1カウント

何卒宜しくお願いします。

No.2 ベストアンサー 回答者： goomania 回答日時： 2021/01/29 23:54

ご希望の月別クラス別在籍者人数の一覧を別シート「月別一覧」に作成するとすると、

まず、月別一覧の月のセル(添付画像のB3～M3)について、添付画像のように月初の日付を入力し、セルの書式設定で表示形式を「m"月"」としておきます。
元データの「クラス」「入日付」「出日付」がB3からD13にあるとすると、
添付画像でいえば、月別一覧シートのB4に、

=SUMPRODUCT((元データ!$B$3:$B$13=$A4)*(元データ!$C$3:$C$13<=EOMONTH(B$3,0))*(元データ!$D$3:$D$13>=B$3))

という数式を記述し、右方向および下方向へオートフィル等でコピーします。
数式中の$B$3:$B$13、$C$3:$C$13、$D$3:$D$13につていは、実際のデータの範囲に従って変更してください。

合計欄はB7に

=SUM(B4:B6)

という数式を記述し右方向へコピーします。
これで添付画像のような、ご質問者のご希望の表が作成できると思います。

この回答へのお礼

回答ありがとうございます。こちらの関数で表が完成しました。
SUMPRODUCT関数、勉強になりました。

お礼日時：2021/02/02 16:43

No.5 回答者： yoreyore 回答日時： 2021/02/01 10:21

別シートでも、同一シートでも、countifsでやったらダメですか。

この回答へのお礼

回答ありがとうございます。
自分でCOUNTIFSを使ったときは数値が全て0になってうまくいかなかったのですが、教えていただいた方法でうまく反映できました。
名前の定義も数式が見やすくなって便利でした。今後の参考にさせていただきます。

お礼日時：2021/02/02 16:43

No.4 回答者： enunokokoro 回答日時： 2021/01/30 00:07

No.3の回答者です。

回答内容を推敲している間に、No.2の回答がされましたね。
SUMPRODUCT関数を使った部分が同じものだったようです。
投稿する前に確認しておくべきでした。

No.3 回答者： enunokokoro 回答日時： 2021/01/30 00:02

違うかもしれませんが、こういうことでしょうか？

（元データシートの範囲が3行目から100行目だとして）

月別一覧シートのB４セルに
　=SUMPRODUCT((元データ!$C$3:$C$100<=EOMONTH(DATE(B$2,B$3,1),0))*1,(元データ!$D$3:$D$100>=DATE(B$2,B$3,1))*1,(元データ!$B$3:$B$100=$A4)*1)
で対応させます。各クラスごとにカウントさせたら、合計を
出すのは簡単ですよね。

個人的には、元データシートのE列以降に作業列を用意して、
E3セルに日付として月初めの2020/4/1を入れて
　=AND($C3<=EOMONTH(E$2,0),EOMONTH($D3,-1)+1>=E$2)*1
にすることで、3行目以降それぞれのカウントをさせます。
月別シートでは、この作業列を元にSUMIF関数でクラス別を集計

この回答へのお礼

回答ありがとうございます。SUMPRODUCT関数を使い表を完成させることができました。元データシートに作業列を用意する方法も試してみます。

お礼日時：2021/02/02 16:43

No.1 回答者： 銀鱗 回答日時： 2021/01/29 16:33

結構面倒なことになりますよ。

手作業でやる手順をそのまま数式で示してみます。

調べたい期間をMIN関数とMAX関数で比較しましょう。
在籍の最終日がA1セルにあると仮定します。

2020年12月に在籍したかを調べたい場合、

　MIN("2021/1/1",A1)
これは 2021年1月1日 以下の値が返ります。
A1の値が2021年1月1日よりも小さければA1の値が返り、
A1の値が2021年1月1日よりも大きければ2021年1月1日の値が返ります。
すなわち、2021年1月1日の値が返らなければ2020年12月31日以前に在籍していると判断できる。

同様に
　MAX("2020/11/30",A1)
これで、2020年11月30日の値が返らなければ2020年12月1日以降に在籍していると判断できる。

この２つを条件をAND関数に渡すなどして数えれば良いんじゃないかな。

・・・
調べたい年がB1セル、月がC1セル、日がD1セルにあると仮定するなら、
　MIN(DATE(B1,C1,D1+1),A1)
　MAX(DATE(B1,C1,D1-1),A1)
みたいにすると楽ができる。

他にもやり方はありますが、あとは工夫してみてください。

この回答へのお礼

ご回答ありがとうございます。
countifs関数を使う事しか考えていなかったので、MINとMAX関数が活用できるとは思ってもみませんでした。参考にさせていただきます。

お礼日時：2021/02/02 11:46