【excel2003】任意の曜日を稼働日とする任意の日付け間の数の抽出

質問

【excel2003】任意の曜日を稼働日とする任意の日付け間の数の抽出

いつもご回答者の皆様方にはお世話になっております。
また一つ関数で実現したいことができたのですが、
添付画像のようなデータがある場合に、D3セルに下記条件の値を反映させたいのです。

条件
1. C3の値をA1の月の月曜日と金曜日の数を抜いた日数で割る。
2. 1.で出した数をF3以上、H3以下の日付から月曜日と金曜日の数を抜いた日数に積算する。

識者の方々よろしくお願いいたします。m(_ _)m

回答No.4

MackyNo1 · ベストアンサー

補足と訂正です。

Ｎｏ２の回答ですが、２の特定の範囲での計算式は、セル範囲を相対参照にしていますので、オートフィルして下のセルにも適用する場合は、絶対参照の数式にする必要があります。
また、提示した数式は月曜と金曜の日数を計算する式になっていますので、月曜日と金曜日の数を抜いた日を求めるなら以下のように、総日数から引き算してください。

=H3-F3+1-SUMPRODUCT((WEEKDAY(F3+ROW(A$1:INDEX($A$1:$A$31,H3-F3))-1)={2,6})*1)

ちなみに、日付を参照する数式を入力すると、表示セルで自動的に日付と認識し、日付として表示されますので、計算後の値を数値で表示したい場合は、セルの書式を「標準」や「数値」にしてください。

回答No.5

mimeu

識者ではありませんが質問者さんの知恵の輪を楽しませていただきました

セルA1に朔日が入るとは書いてないので、処理が複雑になりますが

[A] セルA1の月の月曜と金曜の数
=INT((DATE(YEAR(A1),MONTH(A1)+1,1)-DATE(YEAR(A1),MONTH(A1),1)-INDEX({0,6,5,4,3,2,1},WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),2))+6)/7) + INT((DATE(YEAR(A1),MONTH(A1)+1,1)-DATE(YEAR(A1),MONTH(A1),1)-INDEX({4,3,2,1,0,6,5},WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),2))+6)/7)

[B] C3の値をA1の月の月曜日と金曜日の数を抜いた日数で割る。
=C3/(DATE(YEAR(A1),MONTH(A1)+1,1)-DATE(YEAR(A1),MONTH(A1),1) - [A]の結果)

[C] F3以上、H3以下の日付から月曜日と金曜日の数を抜いた日数
=Date(H3)-Date(F3)+1-INT((H3-F3+1-INDEX({0,6,5,4,3,2,1},WEEKDAY(F3,2))+6)/7) + INT((H3-F3+1-INDEX({4,3,2,1,0,6,5},WEEKDAY(F3,2))+6)/7)

[D] [B] と [C] の答の和を出す数式をセルD3に書く。

お礼

いろいろと教えて頂きありがとうございました。

回答No.3

MackyNo1

A1セルにその月の1日を入力しているなら、以下の式を入力するとそれぞれの日数が求められますので、その日数から適宜計算してください。。

1. C3の値をA1の月の月曜日と金曜日の数を抜いた日数

=DAY(DATE(YEAR($A$1),MONTH($A$1)+1,0))-SUMPRODUCT((MONTH(($A$1+ROW($A$1:$A$31)-1))=MONTH($A$1))*(WEEKDAY($A$1+ROW($A$1:$A$31)-1)={2,6}))

2. F3以上、H3以下の日付から月曜日と金曜日の数を抜いた日数

=SUMPRODUCT((WEEKDAY(F3+ROW(A1:INDEX(A1:A31,H3-F3))-1)={2,6})*1)

それぞれの日数から適宜計算してください。

回答No.2

tom04

こんにちは！
外していたらごめんなさい。
一気に！というのは難しいと思いますので
↓の画像のように（今回は月初めの日付をA2セルに入力しています）
A2セルに月の1日目を入力するとその月1か月分の日付が表示できるように作業用の列を設けてみました。

I3セルに
=IF(MONTH($A$2+ROW(A1)-1)=MONTH($A$2),$A$2+ROW(A1)-1,"")
J3セルに
=IF(I3="","",IF(OR(WEEKDAY(I3)={2,6}),1,""))
という数式を入れ、I3・J3セルを範囲指定し、J3セルのフィルハンドルで下へずぃ～～！っとコピーします。
これでA2セルに入れた月の月末までの日付がI列に
J列にI列の日付の月・金曜日だけに「1」が表示されるはずです。

この表を使って作業を行います。
そしれ、B2セルは単純に
=COUNT(I:I)　としてその月の日数を表示させます。
C2セルは
=COUNT(J3:J33)　として月・金曜日の日数を表示させます。

そして、H4セルに
=SUMPRODUCT(($I$3:$I$33>=E4)*($I$3:$I$33<=G4)*($J$3:$J$33<>1))
としれオートフィルで下へコピーしておきます。
これは「Start」～「Last」までの中で作業用の表が「1」以外、すなわち月・金曜日以外の数を表示させます。

最後にD4セルに
=C4/($B$2-$C$2)*H4
として下へコピーすると
画像のような感じになります。

以上、長々と書きましたけど、参考になれば良いと思います。
尚、的外れなら読み流してくださいね。m(__)m

お礼

いろいろと教えて頂きありがとうございました。

回答No.1

noname#204879

》 …を抜いた日数に積算する

「積算する」とはどういう意味ですか？
「添付画像」が示すデータの場合、セル D3 に期待する数値は何ですか？

お礼

いろいろと教えて頂きありがとうございました。

補足

「乗ずる」ということですね。
掛け算するという意味です。

数値は、2010年3月の場合、月曜日と金曜日が合わせて9日ありますので、
1.の数値は300/(31-9)=13.6363・・・・・・・・
2.の数値は3/1から3/15までの月曜日と金曜日が5日ありますので、
(15-5)*13.6363・・・・=136.3636・・・・がD3の数値となります。