No.5ベストアンサー
- 回答日時:
私の質問は休日リストには日曜日以外の休日の日付だけが入力されているケースで回答しました。
また、日付のカウントは開始日もカウントする数式にしていますので、片方の日付を入れないなら、以下のような数式になります。
=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))
この回答への補足
遅れを調べるので、初日はカウントしないことにしたいです。
頂いた数式で当てはめたところ、
D2= 1
D3= -4
D4= 6
になってしまいました。
4の行で言えば、
遅れ日数は、
12/23木
12/24金
12/25土
12/27月
12/28火
以上の5日間が正解になるような数式だと
うれしいのですが、、
何度もすみません。。。。
No.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日」を指定すればよいでしょう。
仕事の合間をぬって質問していたため、返信に時間がかかってしまいました。
いろいろと丁寧にありがとうございました!
おっしゃる通りにしましたら、できました。
No.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」のように入力してください。
この回答への補足
仕事の合間をぬって行っているため、返答おそくなりました。すみません。
そうでした!! ほかの人からもらったファイルなので、気づきませんでした。。 当てはめたところ、見事できました。
ただ、手元にあるファイルが 22/12/22 という形式になっており、これがかなり多数あり、下準備として、日付に変換する必要が出てきました。甘えてしまうようですが、22/12/22 から 2011/12/22に変換する方法はありますでしょうか?
日付の変換についてですが、別の質問を新たに立てようと思いました。
ですので、これで解決とさせていただきます。
いろいろとありがとうございました。非常に助かりました。
日付が、22/11/22になっていたことをいち早く指摘していただいたのと、OK Waveの使用マナーもご指導いただいた点に感謝して、ベストアンサーとさせていただきます。
どうもありがとうございました。
No.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)))
この回答への補足
頂いた数式で当てはめたところ、
D2= 1
D3= -4
D4= 6
になってしまいました。
ご協力大変ありがたいのですが、、、、
この数式で正確に求めているものができました。
ベストアンサーが二つ選択できれば、
していたところです。
大変助かりました。ありがとうございます。
No.10
- 回答日時:
>遅れを調べるので、初日はカウントしないことにしたいです。
頂いた数式で当てはめたところ、
D2= 1
D3= -4
D4= 6
になってしまいました。
おかしいですね。
後から提示した数式をそのまま入力すれば、ご希望の数値が返ってくると思いますが・・・
この回答への補足
ご協力ありがとうございます。 うーん
またもや、
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 (日)
No.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)),"")
No.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))
No.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)))
この回答への補足
近いのですが、D4=6になってしまいます。
D4=5でないといけません。
12/23木
12/24金
12/25土
12/27月
12/28火
↑日曜と、祝日(不定期)を除いた遅れ日数は“5”となります。
No.6
- 回答日時:
画像のG列で,ご自分でセルの書式を設定しているのでちゃんと出来ると判断していましたが,
>D2= 00/00/01
:
>となってしまいました
日付の書式を引き継いでしまっているので,数式を入れたセルの書式設定の表示形式を標準などに直してください。
#余談ですが,ご質問のようなケースでは日曜を休日リストにいれてしまうのは簡便で良い方法と思います。
休日リストは「全体が日付順で並んでいる」必要は全くありませんので,
○旗日をずらり並べる
の下に(多少セルを開けてもOK)
○日曜日を「+7日」ずつしてずらり並べる
のように作成するので構いません。
#今回ご質問の内容とは直接関係ないので解説はしませんが,
○マッキントッシュのエクセルで作業をしているとか,
○引き算してマイナスの日付を出すためわざわざ1904年基準にチェックを入れているとか,
そういう状況では,そういう事をしていない(若しくはWindowsのエクセルで作成した)ブックとの間で「(今回まさに計算している)日付の値」をコピーしたり数式で参照したりしたときに,日付が変わってしまうので気を付けて使って下さい。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 労働相談 有給計算について 回答お願いします。 建設業経営です。休日は日曜のみ 従業員の有給計算について 雇い 2 2023/03/26 06:12
- 求人情報・採用情報 月単位、週40時間の変形労働時間制で、 日曜祝日土曜半日の固定休+シフト制(平日一日)の企業の場合、 1 2023/08/10 23:43
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- 人事・法務・広報 みなし残業と手当の関係 1 2023/04/12 09:23
- Excel(エクセル) セルに特定の色が出た時だけ、式を発動させたい 4 2022/06/17 10:32
- 求人情報・採用情報 年間休日について 内定いただいている会社が ・年間休日110日 ・週休2日制 土曜、日曜、祝日 休み 7 2023/01/22 02:24
- アルバイト・パート 22年10月から社会保険適用拡大について、詳しい方いらっしゃいましたら教えてください 2 2022/09/01 20:40
- 数学 【どこが1番安いか ややこしい計算を解いて欲しい】 スポーツジム3社どこに通おうか迷っています A社 4 2022/10/18 01:06
- 所得・給料・お小遣い 勤務時間:8時ー17時勤務(2時間休憩) 実稼働:7時間 勤務日数:週6勤務 毎週日曜日を休みとして 2 2023/07/26 18:25
- 夏休み・春休み 年間休日128日!一年の三分の一が、休みです。もっと休み欲しいですか? 2 2022/05/02 00:37
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Officeを開くたびの「再起動メ...
-
英数字のみ全角から半角に変換
-
マイクロソフト 一時使用コード...
-
マクロの書き方を教えて下さい
-
outlookのメールが固まってしま...
-
大学のレポート A4で1枚レポー...
-
エクセルでXLOOKUP関数...
-
会社PCのメールが更新されない
-
【Excel VBA】PDFを作成して,...
-
excelの画面のグリッド線の消滅。
-
teams設定教えて下さい。 ①ビデ...
-
Microsoft Formsの「個人情報や...
-
PCを買い換えました。 今使って...
-
エクセルで英文字に入れた下線...
-
Office 2021 Professional Plus...
-
会社のTeamsのことで相談です。...
-
【Excel】セル内の文字が正しい...
-
みつも朗ってソフトはなにがで...
-
マクロ1があります。 A1のセル...
-
Outlook 電源OFFの受診の仕方
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
「生産性ソフトウェア」とは何...
-
会社PCのメールが更新されない
-
【関数】○年○ヶ月と表示された...
-
WEBの記事を印刷する際にA...
-
エクセルでXLOOKUP関数...
-
Microsoft familyに追加されま...
-
会社のOutlookにてメールを予約...
-
Microsoft Formsの「個人情報や...
-
Microsoft365の一部を解約したい
-
マクロ自動コピペ 貼り付ける場...
-
Outlook で宛先が複数の場合の人数
-
outlookのメールが固まってしま...
-
【Excel VBA】PDFを作成して,...
-
大学のレポート A4で1枚レポー...
-
office365って抵抗感ないですか?
-
Microsoftにofficeアプリについ...
-
Excel テーブル内の空白行の削除
-
マイクロソフト 一時使用コード...
おすすめ情報