こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセル 日数計算 特定の日だけカウントしない2

お尋ねします。


計画からの遅れ日数を計算したいと思っています。

休日は入れません。営業日(稼働日)のみでカウントします。

ただし、土曜日は基本的に営業日(稼働日)です。

日曜日と祝日だけを除いた日数の計算をしたいのです。

例)  12/23~1/15 の 日曜と特定の休日(12/29~1/5)除いた日数の計算

networkday等つかうと、土曜日も休日となってしまうため、困っています。

どなたか知恵を貸してくださいませんでしょうか?

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2011-02-28 11:47:06

QNo.6556329

困ってます

質問者が選んだベストアンサー

私の質問は休日リストには日曜日以外の休日の日付だけが入力されているケースで回答しました。

また、日付のカウントは開始日もカウントする数式にしていますので、片方の日付を入れないなら、以下のような数式になります。

=SIGN(C2-B2)*SUMPRODUCT((WEEKDAY(MIN(B2,C2)+ROW($A$1:$A$300))>1)*(MIN(B2,C2)+ROW($A$1:$A$300)<=MAX(B2,C2))*(COUNTIF($G$2:$G$22,MIN(B2,C2)+ROW($A$1:$A$300))=0))

ちなみに、日曜を含めた休日リストがすでに作成されているなら、WEEKDAY関数部分を除外した以下のような数式で計算することができます。

=SIGN(C2-B2)*SUMPRODUCT((MIN(B2,C2)+ROW($A$1:$A$300)<=MAX(B2,C2))*(COUNTIF($G$2:$G$22,MIN(B2,C2)+ROW($A$1:$A$300))=0))

投稿日時 - 2011-02-28 14:39:16

補足

遅れを調べるので、初日はカウントしないことにしたいです。
頂いた数式で当てはめたところ、
D2= 1
D3= -4
D4= 6
になってしまいました。

4の行で言えば、
遅れ日数は、
12/23木
12/24金
12/25土
12/27月
12/28火
以上の5日間が正解になるような数式だと
うれしいのですが、、
何度もすみません。。。。

投稿日時 - 2011-02-28 16:10:44

お礼

再度試したところ、上記の数式でできました!
ありがとうございます。

投稿日時 - 2011-03-09 15:19:44

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(13)

ANo.13

回答No11です。
私の示した式では1、-3,5の値が得られますね。
1、-4,6の答えとなってしまうのはあなたの表で例えば22/11/24のように直接セルに入力していることによるものです。そのセルを数式バーで見れば2022/11/24のようになっていますね。それでは2022年の日付になっています。G列のデータは2010/11/21のように2010年の日付になっています。
セルに22/11/24と入力するとパソコンは自動的に2022/11/24と20が省略されて入力されたものと解釈しているのです。
もしも平成22年11月24日と入力したいのなら10/11/24と入力するか、あるいは、H22/11/24と入力してもよいでしょう。
もちろん平成22年11月24日とセルに入力してもよいでしょう。しかしこのように入力するのは大変なので平成22年11月24日とセルに表示させたい場合にはセルには2010/11/24と入力してから、(入力以前でもよい)そのセルを右クリックして「セルの書式設定」から「表示形式」のタブで「日付」を選択し、カレンダーの日付を「和暦」にして「平成13年3月14日」を指定すればよいでしょう。

投稿日時 - 2011-03-02 07:09:30

お礼

仕事の合間をぬって質問していたため、返信に時間がかかってしまいました。

いろいろと丁寧にありがとうございました!
おっしゃる通りにしましたら、できました。

投稿日時 - 2011-03-09 15:12:48

ANo.12

計画 実績 遅れ
商品A 22/11/24 22/11/25
商品B 22/11/29 22/11/25
商品B 22/12/22 22/12/28

上記の例示のデータは「22/11/24」と入力したのでしょうか?

その場合は、セルには2022/11/24日と入力されているので(数式バーを確認してみてください)、一覧には該当する休日が無いために、補足のような数値の違いが出てきます。

2010年で入力したいなら「10/11/24」のように入力してください。

投稿日時 - 2011-03-01 16:00:38

補足

仕事の合間をぬって行っているため、返答おそくなりました。すみません。
そうでした!! ほかの人からもらったファイルなので、気づきませんでした。。 当てはめたところ、見事できました。
ただ、手元にあるファイルが 22/12/22 という形式になっており、これがかなり多数あり、下準備として、日付に変換する必要が出てきました。甘えてしまうようですが、22/12/22 から 2011/12/22に変換する方法はありますでしょうか?
 

投稿日時 - 2011-03-09 14:44:03

お礼

日付の変換についてですが、別の質問を新たに立てようと思いました。
ですので、これで解決とさせていただきます。

いろいろとありがとうございました。非常に助かりました。

日付が、22/11/22になっていたことをいち早く指摘していただいたのと、OK Waveの使用マナーもご指導いただいた点に感謝して、ベストアンサーとさせていただきます。 

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

投稿日時 - 2011-03-09 15:17:39

ANo.11

回答No7です。
次の式で良いのではないでしょうか。

=IF(COUNT(B2:C2)<>2,"",C2-B2+IF(C2>=B2,(COUNTIF(G:G,">="&B2)-COUNTIF(G:G,">"&C2))*-1,COUNTIF(G:G,">="&C2)-COUNTIF(G:G,">"&B2)))

投稿日時 - 2011-03-01 08:29:53

補足

頂いた数式で当てはめたところ、
D2= 1
D3= -4
D4= 6
になってしまいました。

ご協力大変ありがたいのですが、、、、

投稿日時 - 2011-03-01 15:26:00

お礼

この数式で正確に求めているものができました。

ベストアンサーが二つ選択できれば、
していたところです。

大変助かりました。ありがとうございます。

投稿日時 - 2011-03-10 13:48:58

ANo.10

>遅れを調べるので、初日はカウントしないことにしたいです。
頂いた数式で当てはめたところ、
D2= 1
D3= -4
D4= 6
になってしまいました。

おかしいですね。

後から提示した数式をそのまま入力すれば、ご希望の数値が返ってくると思いますが・・・

投稿日時 - 2011-02-28 17:36:10

補足

ご協力ありがとうございます。 うーん

またもや、
D2= 1
D3= -4
D4= 6
になってしまいました。

普通にオートフィルドラッグしてみたのですが、、

大変厚かましいのですが、そちらで再現できるのでは
と思い、下に記しました。

計画実績遅れ
商品A22/11/2422/11/25
商品B22/11/2922/11/25
商品B22/12/2222/12/28

2010/11/21 (日)
2010/11/28 (日)
2010/12/5 (日)
2010/12/12 (日)
2010/12/19 (日)
2010/12/26 (日)
2010/12/29 (水)
2010/12/30 (木)
2010/12/31 (金)
2011/1/1 (土)
2011/1/2 (日)
2011/1/3 (月)
2011/1/4 (火)
2011/1/5 (水)
2011/1/9 (日)
2011/1/16 (日)
2011/1/23 (日)
2011/1/30 (日)
2011/2/6 (日)
2011/2/13 (日)
2011/2/20 (日)
2011/2/27 (日)
2011/3/6 (日)
2011/3/13 (日)
2011/3/20 (日)

投稿日時 - 2011-03-01 15:24:01

ANo.9

 回答番号:ANo.8です。
 B列かC列に日付が入力されていない場合に、エラーを出さない様にする事を忘れておりました。

=IF(COUNTIF($B2:$C2,">0")=2,SIGN($C2-$B2)*SUMPRODUCT((WEEKDAY($B2+SIGN($C2-$B2)*ROW(OFFSET(D$1,,,ABS($C2-$B2))))>1)*(COUNTIF($G:$G,$B2+SIGN($C2-$B2)*ROW(OFFSET(D$1,,,ABS($C2-$B2))))=0)),"")

投稿日時 - 2011-02-28 17:14:14

ANo.8

 D2セルに次の数式を入力した後、D2セルをコピーして、D3以下に貼り付けると良いと思います。

=SIGN($C2-$B2)*SUMPRODUCT((WEEKDAY($B2+SIGN($C2-$B2)*ROW(OFFSET(D$1,,,ABS($C2-$B2))))>1)*(COUNTIF($G:$G,$B2+SIGN($C2-$B2)*ROW(OFFSET(D$1,,,ABS($C2-$B2))))=0))

投稿日時 - 2011-02-28 16:54:42

ANo.7

D2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(B2:C2)<>2,"",C2-B2-IF(C2>=B2,COUNTIF(G:G,">="&B2)-COUNTIF(G:G,">"&C2),COUNTIF(G:G,">="&C2)-COUNTIF(G:G,">"&B2)))

投稿日時 - 2011-02-28 16:06:18

補足

近いのですが、D4=6になってしまいます。

D4=5でないといけません。


12/23木
12/24金
12/25土
12/27月
12/28火
↑日曜と、祝日(不定期)を除いた遅れ日数は“5”となります。

投稿日時 - 2011-02-28 16:15:35

ANo.6

画像のG列で,ご自分でセルの書式を設定しているのでちゃんと出来ると判断していましたが,

>D2= 00/00/01
 :
>となってしまいました

日付の書式を引き継いでしまっているので,数式を入れたセルの書式設定の表示形式を標準などに直してください。


#余談ですが,ご質問のようなケースでは日曜を休日リストにいれてしまうのは簡便で良い方法と思います。
休日リストは「全体が日付順で並んでいる」必要は全くありませんので,
○旗日をずらり並べる
の下に(多少セルを開けてもOK)
○日曜日を「+7日」ずつしてずらり並べる
のように作成するので構いません。


#今回ご質問の内容とは直接関係ないので解説はしませんが,
○マッキントッシュのエクセルで作業をしているとか,
○引き算してマイナスの日付を出すためわざわざ1904年基準にチェックを入れているとか,
そういう状況では,そういう事をしていない(若しくはWindowsのエクセルで作成した)ブックとの間で「(今回まさに計算している)日付の値」をコピーしたり数式で参照したりしたときに,日付が変わってしまうので気を付けて使って下さい。

投稿日時 - 2011-02-28 14:49:13

ANo.4

D2に
=C2-B2-COUNTIF(G:G,"<="&C2)+COUNTIF(G:G,"<="&B2)
以下コピー
のように。

投稿日時 - 2011-02-28 13:31:54

お礼

当てはめたところ、以下のようになりました。

D2= 00/00/01
D3= -00/00/04
D4= 00/00/06

となってしまいました。うーん。。。
だんだんと近づいてきているようなのですが、、、

投稿日時 - 2011-02-28 14:23:10

ANo.3

質問の趣旨が、遅れのない場合はマイナス表示したいということなら(この部分だけが希望の状態ではないということなら)、単純に前回の質問で回答した数式のA1セルとB1セルを交換したような数式にすれば差が表示できます。

例示のレイアウトなら、例えばD2セルに以下のような数式を入力してください。

=SIGN(C2-B2)*SUMPRODUCT((WEEKDAY(MIN(B2,C2)+ROW($A$1:$A$300)-1)>1)*(MIN(B2,C2)+ROW($A$1:$A$300)-1<=MAX(B2,C2))*(COUNTIF($G$2:$G$22,MIN(B2,C2)+ROW($A$1:$A$300)-1)=0))

#関連質問では、回答者にこれまでの経過がわかるように、必ずその質問のリンクを付けるようにしましょう。

投稿日時 - 2011-02-28 13:04:35

補足

D2に頂いた数式を当てはめオートフィルで下方にドラッグしたところ、
D2=1 (正解は1)
D3=-5  (正解は-3)
D4=6 (正解は-5)
となってしまいます。 

行4(D4の答え) で言えば、12/26(日)を除いた、
営業日ですので、遅れは、
12/23、24、25、27、28 となり5日間ということになります。

いかがでしょうか?

投稿日時 - 2011-02-28 13:42:03

お礼

↑ D4=6 (正解は5!)でした。失礼。間違えです

投稿日時 - 2011-02-28 14:26:30

ANo.2

前回の質問http://questionbox.jp.msn.com/qa6556060.htmlと全く同じ質問内容ですが、「日曜日と祝日だけを除いた日数の計算をしたい」のではないのですか?


前回の回答で不都合な部分を具体的に例示するようにしてください。

投稿日時 - 2011-02-28 12:37:58

お礼

確かに。画像を載せて説明した方が、わかりやすいのかもと思い直して書いてしまいました。 以後、注意します。

投稿日時 - 2011-02-28 13:08:45

ANo.1

↓こんなの見つけましたがどうでしょうか。
http://www.geocities.jp/chiquilin_site/data/071101_specific_day.html

参考URL:http://www.geocities.jp/chiquilin_site/data/071101_specific_day.html

投稿日時 - 2011-02-28 12:21:58

お礼

参考になりそうです。

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

投稿日時 - 2011-03-01 10:43:15

あなたにオススメの質問