Excel関数での求め方～アルバイト管理表その２～

質問

改めて宜しくお願いします。

画像のようにアルバイト員の管理表を作成しております。
E列の作業列を元にF列の雇用人数残を求めていますが
作業列なしに求める方法を教えてください。

E2には、=ROW()-C2
F2には、=COUNTIF(E2:E$20,"<"&ROW())

となっており、E20、F20までコピーされております。

ちなみに、土曜日と日曜日は会社が休日の為、勤務日数に入りません。
C列の
「0」は0日後に退職、つまり1日アルバイトを意味し、
「－」は入社のはずがアルバイトキャンセルになってしまったことを意味します。

宜しくお願いいたします。

回答No.6

MackyNo1 · ベストアンサー

>やはりCtrl＋Shift+Enterを使わない式というわけにはいきませんでしょうか？

補助列を使用しないで計算するには、配列数式を使用する必要があるので、基本的にこの操作は必要となります。
もちろん配列をIF関数で取得しなければ、INDEX関数で配列を範囲として認識させることも可能ですが、作成可能な場合でも数式を工夫する必要があり、数式が無意味に煩雑になりますのでお勧めできません。

＞画像の備考(G列)の日付に誤りがありました。

私の回答した数式ではA列に記載した日付（営業日）で判断する数式ですので、回答内容に何も変更するようなことではないと思うのですが・・・

ちなみに私の作成した数式を簡単に解説すると、前回のご質問の回答を以下のように修正しています。

前提となる前回回答した連続日付の場合の数式
=COUNT(INDEX(1/(A2:A100+C2:C100>A2),))

「(A2:A100+C2:C100>A2)」の部分は、A2：A100セルの日付（1つ下のA3セルならそのセルから下にA101セルまでの範囲）にC列の加算日数を加えた値が、その日の日付を超えている場合は「TRUE」、それ以外は「FALSE」（C列に文字列（－）が入力されている場合はVALUEエラー）となる配列を取得する数式で、これを分母にして割り算すると、TRUEの場合のみ「1」を返し、それ以外の場合はDIV/0またはVALUEエラーとなります。
この配列をINDEX関数で括って、範囲と認識させCOUNT関数で数字の数をカウント（この関数ではエラー値は無視される）している数式です。

今回の数式
=COUNT(1/(IF((A2:A20+C2:C20)>MAX(A:A),MAX(A:A)+1,SMALL(A:A,RANK(A2:A20,A:A,1)+C2:C20))>A2))

前回の数式の「A2:A20+C2:C20」の部分が
IF((A2:A20+C2:C20)>MAX(A:A),MAX(A:A)+1,SMALL(A:A,RANK(A2:A20,A:A,1)+C2:C20))
となっています。

A2セルと比較する基本は「SMALL(A:A,RANK(A2:A20,A:A,1)+C2:C20)」の部分ですが、これはA2セル以下の日付を小さい順にした数値にC列の数値を加算した部分で同様に在籍期間の最後の日付が求められる部分です。
しかし、この数式ではC列の数字を加算した数値が最も大きい日付（最大値）よりも大きくなることがあり、エラーとなります。
これを避けるために加算した値が日付の最大値を超える場合は、最大値の翌日（必ず在籍期間となる）日付を取得し、それ以外の場合はSMALL関数で得られた日付でそれぞれのA列のセルと比較していることになります。

このように、配列を四則演算以外で取得したい場合は、INDEX関数で範囲として認識させることができないため、Ctrl+Shift+Enterの操作が必要となるわけです。

お礼

順を追っての丁寧な解説、ありがとうございました。

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

この数式ができるまでの流れがとてもわかりやすかったです。

今回の数式を使わせて頂きます。
ありがとうございました。

回答No.5

bunjii

>さらに贅沢を言わせて頂けるならば、祝日も除いた日であると有難いです。
>その場合、土日と同じく祝日も表に入りません。
C列の日数が土日祭日を除いた日数と言うことになると,WORKDAY関数を使うことになりますので満了日の算出は作業用の列を使わなければならないようです。
また、祝日は年によって変動がありますので対象年のデータベース（特定の列）が必要です。

G2:G18に2014年の祝日データをセットし、E列に満了日を算出します。

E2=IF(ISNUMBER(C2),WORKDAY(A2,C2,G$2:G$18),"")

当日の契約人数（D列）は降順になっていますが一般的には昇順で作成されるようです。
提示の方法で算出する場合は以下の式で良いと思います。

D2=COUNTIF(E2:E$20,">"&A2)

貼付画像はExcel 2013で検証した結果ですがExcel 2007以降で同様な結果を得られると思います。

お礼

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

回答No.4

kagakusuki

前回の御質問

　質問No.8503786 Excel関数での求め方【OKWave】
　　http://okwave.jp/qa/q8503786.html

に対する回答で、

>Excelではバージョンの違いによって使う事が出来る方法と出来ない方法が出て来ますので、Excelの御質問をされる際には、なるべく「御使いになられているExcelのバージョン」を併記して頂く様に御願い致します。

と申し上げた筈ですが、またもや、Excelのバージョンを併記しておられませんね。
　私は、前回の御質問に対する回答No.3の所に書かれているお礼欄を読んでおりますから、質問者様が御使いになられているExcelのバージョンがExcel2010である事は承知しておりますが、前回の御質問を読んでいない人間にとっては、Excelのバージョンが判りませんから、答え難い質問になってしまっています。
　つまり、質問者様がExcelのバージョンを書かない事によって、回答出来る人間を必要以上に限ってしまい、回答が得られる可能性を自ら減らしておられるのです。
　ですから、今後、Excelの御質問をされる際には、毎回、「御使いになられているExcelのバージョン」を併記して頂く様に御願い致します。

　さて本題ですが、例えば、3月6日が丁度退職日であったアルバイトに関しては、3月6日の同時雇用人数残の中には含めない様にするものと考えて宜しいでしょうか？

　その場合には、まず、F2セルに次の関数を入力してから、F2セルをコピーして、F3以下に貼り付けると良いと思います。

=IF(ISNUMBER(INDEX($A:$A,ROW())),SUMPRODUCT(ISNUMBER(1/$A$1:INDEX($A:$A,MATCH(9E+307,$A:$A))/$C$1:INDEX($C:$C,MATCH(9E+307,$A:$A))/($A$1:INDEX($A:$A,MATCH(9E+307,$A:$A))<INT(INDEX($A:$A,ROW()))+1)/($A$1:INDEX($A:$A,MATCH(9E+307,$A:$A))-(WEEKDAY($A$1:INDEX($A:$A,MATCH(9E+307,$A:$A)),3)>4)*(WEEKDAY($A$1:INDEX($A:$A,MATCH(9E+307,$A:$A)),3)-4)+$C$1:INDEX($C:$C,MATCH(9E+307,$A:$A))+INT(($C$1:INDEX($C:$C,MATCH(9E+307,$A:$A))+4+(WEEKDAY($A$1:INDEX($A:$A,MATCH(9E+307,$A:$A)),3)<4)*(WEEKDAY($A$1:INDEX($A:$A,MATCH(9E+307,$A:$A)),3)-4))/5)*2>=INT(INDEX($A:$A,ROW()))+1))*1),"")

お礼

※Excel2010を使用しております。（以後気を付けます。）

ご回答ありがとうございます。
ものすごい数式が出てきて驚愕しております。
解読に努めてみますのでお時間を頂ければ幸いです。
ちなみに、祝日が出てきてその日付の行が飛ばされた場合、
対応するのは難しいでしょうか？

補足になりますが、
申し訳ございません。
画像の備考(G列)の日付に誤りがありました。

正しくは、下記になります。

G2から
←3/6終了時点の残り人数(P,K,B,A)
←3/5終了時点の残り人数(P,K,B)
←3/4終了時点の残り人数(P,K)
←3/3終了時点の残り人数(P,K,E)
←2/28終了時点の残り人数(P,K,F,E)
←2/27終了時点の残り人数(P,K,J,F)
←2/26終了時点の残り人数(P,K,J)
←2/25終了時点の残り人数(P,K,J,H)
←2/24終了時点の残り人数(P,K,J)
←2/21終了時点の残り人数(P,L,K,J)
←2/20終了時点の残り人数(P,L,K)
←2/19終了時点の残り人数(P,O,L)
←2/18終了時点の残り人数(P,O)
←2/17終了時点の残り人数(P,O)
←2/14終了時点の残り人数(S,P,O)
←2/13終了時点の残り人数(S,P)
←2/12終了時点の残り人数(S)
←2/11終了時点の残り人数(S,R)
←2/10終了時点の残り人数(S)

回答No.3

MackyNo1

添付画像の集計データを補助列を使用せずに1つの式で計算するには以下のような配列数式を使用する必要があります。

=COUNT(1/(IF((A2:A20+C2:C20)>MAX(A:A),MAX(A:A)+1,SMALL(A:A,RANK(A2:A20,A:A,1)+C2:C20))>A2))

ただし、IF関数を使った配列数式ですので、入力後、Ctrl＋Shift+Enterで確定してください。

お礼

※Excel2010を使用しております。

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

私の力量では何をなさっているのかわかりませんが
見事に一発で表せていて、
さらに平日が祝日になった場合でも
対応できているのがすごいです。

やはりCtrl＋Shift+Enterを使わない式というわけにはいきませんでしょうか？

補足になりますが、
申し訳ございません。
画像の備考(G列)の日付に誤りがありました。

正しくは、下記になります。

G2から
←3/6終了時点の残り人数(P,K,B,A)
←3/5終了時点の残り人数(P,K,B)
←3/4終了時点の残り人数(P,K)
←3/3終了時点の残り人数(P,K,E)
←2/28終了時点の残り人数(P,K,F,E)
←2/27終了時点の残り人数(P,K,J,F)
←2/26終了時点の残り人数(P,K,J)
←2/25終了時点の残り人数(P,K,J,H)
←2/24終了時点の残り人数(P,K,J)
←2/21終了時点の残り人数(P,L,K,J)
←2/20終了時点の残り人数(P,L,K)
←2/19終了時点の残り人数(P,O,L)
←2/18終了時点の残り人数(P,O)
←2/17終了時点の残り人数(P,O)
←2/14終了時点の残り人数(S,P,O)
←2/13終了時点の残り人数(S,P)
←2/12終了時点の残り人数(S)
←2/11終了時点の残り人数(S,R)
←2/10終了時点の残り人数(S)

回答No.2

bunjii

>ちなみに、土曜日と日曜日は会社が休日の為、勤務日数に入りません。
何を意味しているかが理解できません。
提示の表では土日にも採用があるので矛盾します。

>「0」は0日後に退職、つまり1日アルバイトを意味し、
採用当日のみ就業であれば雇用人数に含める必要がありそうです。
あなたの集計では算入されていませんので不合理ではないでしょうか？

>「－」は入社のはずがアルバイトキャンセルになってしまったことを意味します。
空欄の方が扱い易いと思います。
尚、C列の日数は契約期間（日数）と思いますが、土日を除いた日数と言うことでしょうか？

上記の要件が不備のため適切な関数式を組み難い状態です。
また、配列を使って計数する必要があるため作業用の列を使うか数式の確定時にCtrl+Shift+Enterの打鍵を必要とすることになりそうです。

お礼

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

>提示の表では土日にも採用があるので矛盾します。
申し訳ございません。
おっしゃっているのは
画像の備考(G列)の日付のことですよね。
当方のミスです。
正しくは、下記になります。

G2から
←3/6終了時点の残り人数(P,K,B,A)
←3/5終了時点の残り人数(P,K,B)
←3/4終了時点の残り人数(P,K)
←3/3終了時点の残り人数(P,K,E)
←2/28終了時点の残り人数(P,K,F,E)
←2/27終了時点の残り人数(P,K,J,F)
←2/26終了時点の残り人数(P,K,J)
←2/25終了時点の残り人数(P,K,J,H)
←2/24終了時点の残り人数(P,K,J)
←2/21終了時点の残り人数(P,L,K,J)
←2/20終了時点の残り人数(P,L,K)
←2/19終了時点の残り人数(P,O,L)
←2/18終了時点の残り人数(P,O)
←2/17終了時点の残り人数(P,O)
←2/14終了時点の残り人数(S,P,O)
←2/13終了時点の残り人数(S,P)
←2/12終了時点の残り人数(S)
←2/11終了時点の残り人数(S,R)
←2/10終了時点の残り人数(S)

>採用当日のみ就業であれば雇用人数に含める必要がありそうです。
採用人数というよりは、その日が終わった時点での雇用人数残を求めたいのです。

>尚、C列の日数は契約期間（日数）と思いますが、土日を除いた日数と言うことでしょうか？
その通りです。
さらに贅沢を言わせて頂けるならば、祝日も除いた日であると有難いです。
その場合、土日と同じく祝日も表に入りません。

宜しくお願い致します。

回答No.1

satoron666

雇用人数残の求め方が良く分かりません。

現在の行数－「～日後に退職」を引いて
何を出しているのですか？
基本マイナスになると思いますが…

どういう流れで雇用人数残を出すのか
箇条書きで説明して頂きたいです。

そして、もっと効率良い方法がありそうですが…

お礼

ご回答ありがとうございます。
（ひとまずお礼をさせて頂きます。）

補足になりますが、
申し訳ございません。
画像の備考(G列)の日付に誤りがありました。

正しくは、下記になります。

G2から
←3/6終了時点の残り人数(P,K,B,A)
←3/5終了時点の残り人数(P,K,B)
←3/4終了時点の残り人数(P,K)
←3/3終了時点の残り人数(P,K,E)
←2/28終了時点の残り人数(P,K,F,E)
←2/27終了時点の残り人数(P,K,J,F)
←2/26終了時点の残り人数(P,K,J)
←2/25終了時点の残り人数(P,K,J,H)
←2/24終了時点の残り人数(P,K,J)
←2/21終了時点の残り人数(P,L,K,J)
←2/20終了時点の残り人数(P,L,K)
←2/19終了時点の残り人数(P,O,L)
←2/18終了時点の残り人数(P,O)
←2/17終了時点の残り人数(P,O)
←2/14終了時点の残り人数(S,P,O)
←2/13終了時点の残り人数(S,P)
←2/12終了時点の残り人数(S)
←2/11終了時点の残り人数(S,R)
←2/10終了時点の残り人数(S)

補足

※Excel2010を使用しております。

ご回答ありがとうございます。
また、説明不足ですみません。

箇条書きで説明します。
Sさんの例でいきますと、

1. Sさんは2/10(月)に入社します。
2. Sさんは5営業日後に退職します。5営業日後とは2/17(月)です。
3. 2/10(月)は20行目、2/17(月)は15行目に当たります。
4. 言い換えると、Sさんは20行目に入社し、15行目に退職します。
5. A列にはあらかじめ営業日でない日は除かれているので
単純に、20-5=15(作業列)となり、15行目において雇用人数残
の数式「=COUNTIF(E15:E$20,"<"&ROW())」に該当しなくなり
カウント対象から外れます。

という流れになります。

拙い説明になりますが以上のような感じです。
もっと効率良い方法がございましたらお教え願えればと思います。