Excel関数での求め方

質問

行き詰まってしまいましたので、皆様のお知恵をお貸しください。

画像のようにアルバイト員の管理表を作成しております。
D列の同時雇用人数を手入力で計算していますが
関数を用いて自動で計算する数式を教えて頂きたく
宜しくお願い致します。

もし可能でしたら、[Ctrl]+[Shift]+[Enter]キーを押さなくて
良い数式だとありがたいです。

回答No.2

MackyNo1 · ベストアンサー

C列の「-」と「0」のはどういう意味なのでしょうか？

ひとまずこれらの値は計算対象外とするなら、D2セルに以下の式を入力して下方向にオートフィルコピーしてください。

=COUNT(INDEX(1/(A2:A100+C2:C100>A2),))

お礼

ご回答ありがとうございました。
説明不足があり申し訳ございません。
C列の「-」はアルバイト員がキャンセル等の理由で入社にならなかったことを意味し、
「0」は0日後に退職、つまり１日アルバイトを意味しています。

ご教示いただきました数式で一発で表すことができました。
誠にありがとうございました。

回答No.4

kagakusuki

>C列の「-」と「0」のはどういう意味なのでしょうか？

　アルバイト員の話なのですから、「0」は1日だけのアルバイトだと思います。
　一方、「-」は何日後に退職したのかが決まっていない場合、即ち、退職の予定が無く、現在も勤務し続けているアルバイトだと思います。

　それから、質問者様の添付画像の例では、残っている人数の計算結果が間違っているのではないでしょうか？
　例えば、2014/2/28が終了した時点において残っているのは、「Pさん」、「Kさん」、「Bさん」、「Aさん」の4人だけではなく、「Aさん」、「Bさん」、「Dさん」、「Gさん」、「Iさん」、「Kさん」、「Nさん」、「Pさん」、「Qさん」の9人ではないでしょうか？
　A列の「日付」は、アルバイトの開始日であって、退職日ではないの思ったのですが、違うのでしょうか？
　もしA列の「日付」がアルバイトの開始日だとしますと、人数を計算される際に、アルバイトの開始日と退職日を混同されてはおられないでしょうか？

　御質問の件の場合、退職日そのものが記載されておりませんので、COUNTIF関数或いはCOUNTIFS関数"のみ"では、人数を求める事は出来ません。
　ですから、別の列の所に、一旦、退職日が(関数を用いて自動的に)表示される様にしてから、COUNTIFS関数を使用して人数を求めるのが一般的な方法だと思います。

　まず、適当な列(ここでは仮に、E列を使う事にします)の2行目のセル(E2セル)に次の関数を入力して下さい。

=IF(ISNUMBER($A2),IF(ISNUMBER($C2),$A2+$C2,$C2&""),"")

　次に、D2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),COUNTIFS($A:$A,"<="&$A2,$E:$E,">"&$A2)+COUNTIFS($A:$A,"<="&$A2,$E:$E,"-"),"")

　尚、もしも、A列に入力される値が例えば「2014/2/28 12:30」の様な、時刻のデータも含んでいる日時のデータである場合には、D2セルに入力する関数は次の様なものにして下さい。(こちらは、A列のデータが日付けのみのデータである場合にも有効です)

=IF(ISNUMBER($A2),COUNTIFS($A:$A,"<"&INT($A2)+1,$E:$E,">="&INT($A2)+1)+COUNTIFS($A:$A,"<="&INT($A2)+1,$E:$E,"-"),"")

　次に、D2～E2の範囲をコピーして、同じ列範囲の3行目以下に貼り付けて下さい。
　因みに、もしも、E列に退職日が表示されているのが気になる場合には、E列全体に対して、[切り取り]と[切り取ったセルの挿入]操作を行う事で、「～日後に退職」の列と「同時雇用人数」の列の間に、「退職日」の列を移動させたり、別のシート上に「退職日」の列を移動させたりする事も出来ます。

　尚、COUNTIFS関数はExcelのバージョンがExcel2007よりも前のバージョンのものでは使う事が出来ませんので注意して下さい。
　この様に、Excelではバージョンの違いによって使う事が出来る方法と出来ない方法が出て来ますので、Excelの御質問をされる際には、なるべく「御使いになられているExcelのバージョン」を併記して頂く様に御願い致します。

　それから、別のやり方として、余計な列を設けずにSUMPRODUCT関数を使って処理してしまうという方法もあります。
　但し、SUMPRODUCT関数を使った方法の場合、セル範囲として指定している範囲内に含まれる行数が多くなりますと、処理が重くなってしまいますので注意して下さい。(この難点は、他の回答においてSUMPRODUCT関数を使っている方法でも同様です)
　そのやり方は次の通りです。

　まず、退職の予定が無い人の行におけるC列の「～日後に退職」欄には、必ず「-」等の何らかの文字を入力しておく様にして下さい。
　次に、D2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),SUMPRODUCT(ISNUMBER($A$1:INDEX($A:$A,MATCH(9E+307,$A:$A)))*($A$1:INDEX($A:$A,MATCH(9E+307,$A:$A))<=$A2)*ISNUMBER(1/($A2-$A$1:INDEX($A:$A,MATCH(9E+307,$A:$A))<$C$1:INDEX($C:$C,MATCH(9E+307,$A:$A))))),"")

　次に、D2セルをコピーして、D3以下に貼り付けて下さい。

　以上です。

　因みに、SUMPRODUCT関数内でセル範囲を指定する際に、何故、$A2:$A$20等ではなく、

$A$1:INDEX($A:$A,MATCH(9E+307,$A:$A))

等の様な指定の仕方をしているのかと申しますと、こうする事で、A1セル～「A列に日付が入力されている最下段の行のセル」の範囲を自動的に指定する事が出来ますので、例えば、A15までしかデータが入力されていない場合において、計算する必要のないA16～A20までの範囲を計算せずに済みますので、SUMPRODUCT関数の処理に要する時間を少しでも短縮する事が出来るからです。(ついでに、データが入力されている行数が増えた場合にも、関数を修正する必要が無くなります)

お礼

懇切丁寧なご教示ありがとうございました。
また、説明不足・日付の混同が見られ申し訳ございませんでした。

C列の「0」はおっしゃる通り１日だけのアルバイトを意味していましたが
「-」はアルバイト員がキャンセル等の理由で入社にならなかったことを意味しておりました。
ご丁寧なご教示をして頂いただけに、申し訳ない気持ちでいっぱいです。

今回教えて頂いたことをよく学んで、次に活かしていきたいと思います。
誠にありがとうございました。

回答No.3

mshr1962

D2に

=SUMPRODUCT((($A2:$A$20+$C2:$C$20)>$A2)*1)

でD20までコピー

お礼

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

私の環境(Excel2010)では2～18行において
#VALUWE!となってしまいました。

回答No.1

aokii

F2セルに以下の式を入れ、下にドラッグコピーする。
=A2+C2

D2セルに以下の式を入れ、下にドラッグコピーする。
=COUNTIF(F2:F$20,">"&A2)

お礼

ご教示ありがとうございました。

シンプルな式でとてもわかりやすく
勉強になりました。

ありがとうございました。