入退社日より各月末の在籍者数を把握できる関数

質問

教えてください。

エクセル2010を使用しております。
社員の入退社日から毎月の月末時における入社数、退社数、在籍者数を把握できる表を作成したいと考えております。

各スタッフの氏名、入社日、退職日、在籍年数のデータが入力された表があり、行を変えてC列に年月日（月末）、D列に入社数、E列に退社数、F列に在籍者数を表示させたいと考えております。

また、B列に日付を入力して、エンド・オブ・マンスを使って翌月末日をC列に表示させました。月末の日付を表示させるには、他に良い方法があるでしょうか。

よろしくお願いいたします。

回答No.2

minosennin · ベストアンサー

入社数、退社数とは、当月1ヶ月間の人数ですね。以下、その前提での一例です。

B列に入社日、C列に退職日、明細行が101行まであり、集計を103行目に表示するものとします。
C103は当月の月末日です。

入社数　入社日が当月1日から当月末日までの人数をカウント
D103　=COUNTIFS(B2:B101,"<="&$C103,B2:B101,">="&DATE(YEAR($C103),MONTH($C103),1))
退社数　退職日が当月1日から当月末日までの人数をカウント
E103　=COUNTIFS(C2:C101,"<="&$C103,C2:C101,">="&DATE(YEAR($C103),MONTH($C103),1))
在籍者数　(入社日が当月末日までの人数)　-　(退職日が当月末日までの人数)
F103　=COUNTIF(B2:B101,"<="&$C103)-COUNTIF(C2:C101,"<="&$C103)

なお、月末日の求め方はEOMONTHの他前のご回答にもあるとおり
DATE関数でも求められます。
=DATE(YEAR(A1),MONTH(A1),0)　先月末日
=DATE(YEAR(A1),MONTH(A1)+1,0)　当月末日

お礼

ご回答ありがとうございます。

入社数、退社数、在籍者数の計算式もわかりやすく、初心者の私でも簡単にできました。
各関数の意味をもっと理解して、どうして導き出されるのか勉強してみます。

本当に助かりました。
ありがとうございました。

回答No.3

KURUMITO

行を変えてとのことで別のシートに表示させるのではないということでしたら例えば回答1の例でシート1の99行目までにデータが有るとして100行目にはＣ100セルに年月日（月末）がＤ１００セルに入社数、Ｅ100セルに退社数、Ｆ100セルに在籍者数の文字が入力されるとしたらＣ101セルには例えば2010年1月31日からの月の末日を表示させるのでしたら201/1/31のように入力しＣ102セルには次の式を入力して下方にドラッグコピーします。

=DATE(YEAR(C101),MONTH(C101)+2,0)

Ｄ101セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($C101="","",IF(COLUMN(A1)<=2,COUNTIF(Sheet1!B$1:B$99,">="&DATE(YEAR($C101),MONTH($C101),1))-COUNTIF(Sheet1!B$1:B$99,">"&$C101),IF(COLUMN(A1)=3,COUNTIF(Sheet1!$B$1:$B$99,"<="&$C101)-COUNTIF(Sheet1!$C$1:$C$99,"<="&$C101),"")))

お礼

度々のご回答ありがとうございます。

おかげさまで、No.2 のご回答者様の数式で、必要としていたデータが導き出されました。
苦労してご回答いただけました数式ですが、初心者の私には複雑で理解できず申し訳ございません。

ご回答いただけましたことに、改めて感謝申し上げます。

回答No.1

KURUMITO

例えばシート1には元のデータが有ってＡ列には氏名、Ｂ列には入社日、Ｃ列には退職日、Ｄ列には在籍年数が入力されているとします。
そこでお求めの表ですがシート2に作成するとしてＣ1セルには年月日（月末）、Ｄ1セルには入社数、Ｅ1セルには退社数、Ｆ1セルには在籍者数とそれぞれ文字が入力されているとします。
そこでＣ2セルですがこのセルは表を作成するうえでの最初の日付として2010/1/31のように統計を取りたい年月の末日を入力します。Ｃ3セルには次の式を入力して下方にドラッグコピーすることで1カ月ごとの末日が表示されます。

=DATE(YEAR(C2),MONTH(C2)+2,0)

Ｄ２セルには次の式を入力してＦ2セルまで右方向にドラッグコピーしたのちに下方にもドラッグコピーします。下方にドラッグコピーします。

=IF($C2="","",IF(COLUMN(A1)<=2,COUNTIF(Sheet1!B:B,">="&DATE(YEAR($C2),MONTH($C2),1))-COUNTIF(Sheet1!B:B,">"&$C2),IF(COLUMN(A1)=3,COUNTIF(Sheet1!$B:$B,"<="&$C2)-COUNTIF(Sheet1!$C:$C,"<="&$C2),"")))

お礼

ご回答ありがとうございます。

基準日となる月末の年月日を入力して、数式を入れてドラッグコピーで毎月の末日が簡単に入力できました。
入社数の数式ですが、ちょっと複雑で私には理解できませんでした。申し訳ございません。

毎月末日の自動入力は助かりました。
本当にありがとうございました！