Excel 入退職月から任意の月末に在籍する部署ごとの人数を算出

シート1
A列-職種(ドロップダウンリストで50職種ほど設定)
B列-入職月(例　2016/01)
C列-退職月(例　2016/10)
D列-名前
なお、必ず入職月は1日から始まり、退職月は末日と考えます
これが仮に100名だとすると対象は(sheet1!A2:A101)となります

これをもとにシート2に
任意の月末時点における各部署の人数を算出するための計算式をご教授願います
なお、当該月に在籍していれば人数に含めるので、
退職月が2016/10であれば10月は1人として数えます

シート2は次のように考えています
1行目B列から右に月(たとえば2016/01、2016/02・・・)
2行目A列から下に職種(職種1,職種2・・・)

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

質問者からの補足コメント

• ありがとうございます。
  でも、職種ごとの算出をどうしたらよいのかわかりませんでした。すみません。

  No.1の回答に寄せられた補足コメントです。 補足日時：2016/12/13 22:39

• あれ、私、書くところ間違えてるのでしょうか･･･??
  
  銀鱗さん、ありがとうございます。
  実際にデータをいれて少し近づいてきた気はしたのですがご提示いただいた「任意の月」とは入職日、退職日、いずれのことを指すのでしょうか。入退職日はそれぞれに違う方が混在しているのですが、退職日のみをみればよいのでしょうか。また、今月来月ですでに退職が確定している方を先に入力しておいても問題ないでしょうか。

  No.2の回答に寄せられた補足コメントです。 補足日時：2016/12/13 22:48

• tatsu99さん、ありがとうございます。
  なんだか雲をつかむような質問ですみません。
  
  １案で結構です。入職日は1日、退職日は末日、任意の日付(締日)は末日。
  これでデータを作成することは問題ありません。
  また最後にご指摘の通り、原則、在職中の方の退職日欄は空白ですが、わかった時点で入力してしまうことはやはりNGなのでしょうか。(たとえば12/13の時点で翌月1/31退職と入力してしまう)
  なるべくシンプルにしたいとは思いますので、入力ルールがあればそれもあわせてご教授いただければ幸いです。

  No.3の回答に寄せられた補足コメントです。 補足日時：2016/12/13 22:55

• tatsu99さん、お礼を書いてから気づいたことがありこちらに記入させていただきます。
  
  スクリーンショット画像の入退職日は年/月となっており、日付部分が抜けていますが、これは実際の入力は年/月/日(入職日であれば1日、退職日は末日)を入力して、表示形式のみ変更してあると理解してよろしいのでしょうか。
  No.5のenunokokoroさんのところに記入したことと重複してしまい恐縮ですが、年/月だけを入力すると1日の日付が勝手に入ってしまいます。細かいことを言ってしまいすみませんが、なにか対処方法等あるようでしたらあわせておしえていただけますでしょうか。

  No.4の回答に寄せられた補足コメントです。 補足日時：2016/12/15 22:29

• 

  わずか4日のうちに7つもご回答をいただき見事に解明!
  たくさんの方にご教授いただきましたことを重ねてお礼申し上げます。
  今回は私が理解できた１番目だったtatsu99さんをベストアンサーとさせていただきます。
  でも本当に皆さん素晴らしく、丁寧なご指導にただただ感謝です。
  ありがとうございました。

    補足日時：2016/12/17 01:53

No.4 ベストアンサー 回答者： tatsu99 回答日時： 2016/12/14 00:04

1案前提の場合です。

＞原則、在職中の方の退職日欄は空白ですが、わかった時点で入力してしまうことはやはりNGなのでしょうか。
＞(たとえば12/13の時点で翌月1/31退職と入力してしまう)
問題ありません。わかった時点で、未来の日付を入れてください。
提示した添付の図は、退職日欄が未来の日付の場合も含まれています。

B2へ
=COUNTIFS(Sheet1!$A$2:$A$101,$A2,Sheet1!$B$2:$B$101,"<="&B$1,Sheet1!$C$2:$C$101,">="&B$1)+COUNTIFS(Sheet1!$A$2:$A$101,$A2,Sheet1!$B$2:$B$101,"<="&B$1,Sheet1!$C$2:$C$101,"")

と入力し、オートフィルで、右側、及び、下へコピーしてください。

Sheet1の行数は101行までサポートしています。もし、増やしたい場合は、101の値を増やしたい数に変更してください。

基本的な考え方は、
1)A列の値がSheet1のA列に一致、かつ、ある時点の日付が入職日以降、かつ、ある時点の日付が退職日以前　と
2)A列の値がSheet1のA列に一致、かつ、ある時点の日付が入職日以降、かつ、退職日が空白
上記１）２）の和を求めています。

この回答へのお礼

tatsu99さん、ご丁寧に解説いただきありがとうございます。
ご返信が遅くなり失礼いたしました。
まさにこれ！です。本当にありがとうございます。

お礼日時：2016/12/15 21:54

No.7 回答者： enunokokoro 回答日時： 2016/12/16 11:57

No.5の回答者です。

> 自動的に2016/1/1という認識をされてしまう
Excelでセル内に入力したものを、自動が様々な形式で認識するのは
理解していますよね？

日付けだけでなく、通貨やパーセンテージなどのようなものを認識して、
その書式に適した表示形式に勝手に変換されるものです。

日付形式の区切りにも使うスラッシュは分数としても利用されますが、
入力前に書式として[分数]として指定しないと日付として認識されること
があります。
日付けの範囲にある1月から12月までと1日から最大31日までの範囲を
分数として入力すると、勝手に年を入力した西暦として判断して、月日で
処理されるものですね。

このように、日付として判断する形式に年＋月の「2016/11」で入力した
ものを年月日である「2016/11/1」として認識して、シリアル値として管理
されます。
これを利用して、指定年の月初めから入力するときに年＋月だけ入力
して、これを他のセルへとオートフィルするだけで、楽にその月の連続
データを入力することができるのです。

また、Excelの日付関数には月の末日を指定するEOMONTH関数が
ありますが、逆の月初めを求めるだけの関数はありませんよね。
https://kokodane.com/kan27.htm
これは、月初めを入力するのは常に「1日」なので簡単だけど、月ごとに
末尾が違うもので入力をしなければならないので、簡単にできる関数で
用意されたのだと思います。

今回の数式で利用している月初めと月末の関係でも同じです。
確実なのは、No.3の回答者さんが書いているように入力時に年月日を
すべて入力するのがベストだとと思います。

でも省略しても同じ結果ならば、簡略した入力でもベターだと思います。
数式についても、発想を違うNo.2の方法も同じ結果なので、簡略できる
入力なども利用して、数式も必要最低限にしたものを用意して利用した
ほうが管理も簡単です。

末日で管理したいということなので、No.5の数式のまま、Sheet1の
C列と、Sheet2の1行目の月別のセルを末日で入力しておいても
結果としては同じ値を得ることができるようになります。
ですから、No.2の回答者が提示した数式でもベターだと思います。

この回答へのお礼

enunokokoroさん、ありがとうございます。
本題からずれてしまったのに丁寧にご教授いただきお礼申し上げます。
日付の件は自分でもどのようにしたら一番よいのか自分なりに考えてみます。

お礼日時：2016/12/17 01:43

No.6 回答者： tatsu99 回答日時： 2016/12/15 22:49

>スクリーンショット画像の入退職日は年/月となっており、日付部分が抜けていますが、これは実際の入力は年/月/日(入職日であれば1日、退職日は末日)を入力して、表示形式のみ変更してあると理解してよろしいのでしょうか。

はい、そのようになっています。

>年/月だけを入力すると1日の日付が勝手に入ってしまいます。細かいことを言ってしまいすみませんが、なにか対処方法等あるようでしたらあわせておしえていただけますでしょうか。

一例です。
添付の図のように、入退職日をセルの書式設定でユーザー定義の　yyyy/m に設定します。
入退職日を入力するとき、2016/12/31のように日付まで入力してください。
そうすると、その日付が入力され、表示上は月までになります。

（個人的には2016/12のように月までではなく、2012/12/31のように日まで表示したほうが間違いがないので、そのほうが良いと考えますが・・・。）
関数は日付で判断しているので、表示を月までにしても、日まで表示しても、どちらでも問題ありません。

この回答へのお礼

tatsu99さん、こんなつぶやきみたいな質問にもご丁寧にお答えいただき重ね重ねありがとうございます。
うんうんと頷きながら読ませていただきました。感謝です。

お礼日時：2016/12/17 01:36

No.5 回答者： enunokokoro 回答日時： 2016/12/14 12:15

No.2の回答者さんが提示している式でよいのでは？

そのまま質問のシート内容で数式にすると以下のようになりますね。

=COUNTIFS(Sheet1!$A$2:$A$101,$A2, Sheet1!$B$2:$B$101,"<=" & B$1)-COUNTIFS(Sheet1!$A$2:$A$102,$A2, Sheet1!$C$2:$C$102,"<" & B$1)

上記式でもC列に空白セルや将来的な日時があっても対応できます。
添付画像のSheet2の日付部分には「2016/01」形式だと長いので、
和暦の月だけの表示にしています。

この回答へのお礼

enunokokoroさん、ありがとうございます。
No.2銀鱗さんの解法が私の未熟さゆえ理解できなかったということですね。
失礼いたしました。
今回はいろいろな解法があることに奥の深さを感じました。(といっても、私は数式の理解までは到達できませんが)
こちらもご教授いただいた通りでたしかに演算ができました。

ただ、ひとつすみません。これも私の無知かもしれませんが
2016/1とだけ入力しエンターキーを押した際に、たしかに表記は2016/1とすることができたのですが、
自動的に2016/1/1という認識をされてしまうのはこれは致し方ないのでしょうか。
計算上やみかけは問題ないのですが、退職は末日なので若干違和感があります。

お礼日時：2016/12/15 22:06

No.3 回答者： tatsu99 回答日時： 2016/12/13 19:34

シート１の入職月,退職月、及びシート２の1行目、B列以降の月

については、日付型で格納されていると思いますが、
実際の日付を1日に統一しておくことは可能でしょうか。
というのは、関数で日付の比較を行う時に年月の範囲での比較を行うことが難しいためです。
（年月の範囲で比較するとyyyymmの値=YEAR(セル)*100+MONTH(セル)の値を作成し、それで比較が必要になるためです）
理想的には、
1案
入職日：yyyy/mm/1日
退職日：yyyy/mm/末日
ある時点の日付：yyyy/mm/末日
であることが必要ですが、
2案
入職日：yyyy/mm/1日
退職日：yyyy/mm/1日
ある時点の日付：yyyy/mm/1日
でも問題ありません。
上記の設定が可能であれば、集計は可能です。
1案、2案も難しい場合、年月での比較になり、かなり複雑な長い式になるかと。

又、在職中の人の退職日は空白であると理解していますが、それで間違いないでしょうか。

No.2 回答者： 銀鱗 回答日時： 2016/12/13 02:14

＝COUNTIFS(A2:A102,”職種” , B2:B102,"<=任意の月") － COUNTIFS(A2:A102,”職種” , C2:C102,"<任意の月")

でいいんじゃないですか？
任意の月より入職日の方が小さい人から、任意の月より退職日のほうが小さい人を引けばいいんです。

ということで後は自身で数式を調整してください。
（シート2からシート1を参照する方法とか、シート2のA列の値や1行目の値をどう組み込むかなど）

No.1 回答者： angkor_h 回答日時： 2016/12/13 01:17

任意の月末時点の在職者のカウントというのであれば、

次でどうでしょうか。
　　=COUNTIF(退職月列、">="&任意の月末)+COUNTIF(退職月列、"(空白)")

なお、入退職の記録入力が月単位だと、日付が1日になります。
検索が月末という月末日であれば、入退職の記入も日付まで必要です。
また、退職日は在籍日です、念のため。