日付の関数を教えてください

質問

例としまして、1月24日(月)で、土日祭日抜いた3日前、1月19日（水曜日）と関数で表示したいのですが、教えてください。また、毎月、休日を指定出来るにしたいのですが、よろしくお願いします。

回答No.3

KURUMITO · ベストアンサー

シート１のA１セルに2011/1/24と入力して、土日祭日を抜いた３日前をB１セルに表示させるとしたら、その前にその年の祭日（会社の休日などを含む）の日付を例えばシート２のA２セルからA２９セルまでに入力するとします。２０１１年の祝日を入力する場合には、シート２のA１セルには2011/1/1と入力し、セルの表示形式でユーザー定義でyyyy”年" として２０１１年と表示させます。
これらの作業が済んだ後でシート１のB１セルには次の式を入力します。

=WORKDAY(A1,-3,Sheet2!A$2:A$29)

なお、セルの表示形式はユーザー定義で　m"月"d"日"(aaaa) と入力します。これで１月１９日（水曜日）と表示されますね。

次に、毎月休日を指定できるようにしたいとのことですが、指定した月の休日を表示させるためには結構面倒な計算になりますので次のようにしてはどうでしょう。
シート２のA３０セルには次の式を入力してA１３６セルまでオートフィルドラッグします。なお、A１３０セル以降でエラー表示が出る場合にはそれらの行は削除してA列にはエラー表示が出ないようにしてください。

=IF(MOD(ROW(A1),2)=1,IF(YEAR(A$1+IF(WEEKDAY(A$1,2)<7,6-WEEKDAY(A$1,2),13-WEEKDAY(A$1,2))+INT(ROW(A1)/2)*7)<>YEAR(A$1),"",A$1+IF(WEEKDAY(A$1,2)<7,6-WEEKDAY(A$1,2),13-WEEKDAY(A$1,2))+INT(ROW(A1)/2)*7),IF(YEAR(INDIRECT("A"&ROW()-1)+1)<>YEAR(A$1),"",INDIRECT("A"&ROW()-1)+1))

これでその年の土曜日と日曜日の日付が表示されます。なお、シート２のA２セルから下方の日付はセルの表示形式で日付から選んで表示させるようにします。
次にシート２のB２セルには次の式を入力してB１３６セルまでオートフィルドラッグします。

=IF(AND(A2>=Sheet1!C$1,A2<=DATE(YEAR(Sheet1!C$1),MONTH(Sheet1!C$1)+1,0)),A2,"")

この式ではシート１のC１セルに表示させたい月を、例えば今年の２月の休日を表示させたい場合には、シート１のC１セルに2011/2/1と入力して、セルの表示形式をユーザー定義で　m"月" として２月と表示させます。
このようにシート１のC１セルで指定した月の休日になる日付がシリアル値の形でシート２のB列に表示されることになります。
そこでシート１のC１セルで指定した月の休日をシート１のC２セルから下方に表示させるためにはC２セルに次の式を入力して下方にオートフィルドラッグします。

=IF(ISERROR(SMALL(Sheet2!B:B,ROW(A1))),"",SMALL(Sheet2!B:B,ROW(A1)))

表示形式を日付にすることでその月の休日が並んで表示されます。

お礼

いろいろありがとうございました。関数が、難しいですね。もっと勉強します。

回答No.2

imogasi

基本的に関数では質問のことは出来ないのです。
プログラム（例えばVBA）なら1/23, 1/22, 1/21・・・と１日づつ、ヅラして（減らして）いって、その日が土曜OR日曜、祝日、会社休業日（1月２日、３日など）でないかを判断し、それらに該当しない日を１日ずつ足して３になった日で止めて、その日が求める日とすれば良い。しかし関数にはくり返しをする機能は無く、意味的に、くり返しの必要なものは（例SUMやCOUNTIFなど）関数の中に組み込まれているものだけです。
ある日の曜日はWEEKDAY関数で判断できます。
しかし祝日は計算できない（一定でなく、毎年変わる日もある）ので、祝日一覧表がいります。会社休業日も同じです。
ーー
以上は一般論なのですが、MSは社会的に必要そうなので、エクセルにアドイン（特別に組んだプログラム）で実現する関数として、作っているものがあり、WORKDAY関数があります。
この関数は土日は当然として除外して考えてくれます。
祭日は第2引数として１つ日付を除外する例が良く解説書に上がってます。
また、除外が２－３日なら｛　　｝内に除外する日（その月中の祭日）をカンマで区切って指定できます。具体的な日付を調べて、関数の中に入れないといけませんが。
１年間の祭日（データが１年間の範囲のものになる可能性があれば）になると、数が１５以上になって式に入れるには多すぎます。
その場合はセルのある列の範囲に、前もって入力しておきたいものです。
別にNETWORKDAYSにはそれが出来るが、WORKDAYもそれが出来るようです。
例データ　テスト
E1:E5に
2011/1/1
2011/1/2
2011/1/3
2011/1/10
2011/2/11
以下略
１年分などを書き連ねる。来年には追加か変更必要ーー＞不便。忘れたら大事になる。
ーー
A列データ　B列関数の結果
B1の式は　=workday(A1,-3,$E$1:$E$5)　　３日前をテストしてます。
３日前の場合は、第2引数はマイナスをつけます。
2011/2/12　2011/2/8　
2011/2/13　2011/2/8
2011/2/14　2011/2/8
2011/2/15　2011/2/9

2010/12/30　2010/12/27
2010/12/31　2010/12/28
2011/1/1 2010/12/29
2011/1/2 2010/12/29
2011/1/3 2010/12/29
2011/1/4 2010/12/29
2011/1/5 2010/12/30
2011/1/6 2010/12/31
2/11,1/1,1/2,1/3など省いて計算されてます。
たまたま関数があったために出来ましたが、こういうタイプの問題は関数は苦手です。
実際データによって何処に結果があるかわからないようなタイプが苦手で、プログラムならやさしいタイプです。例えばA列の数を上から１セルづつ足して２０を始めて越える行のようなもの。
人間ではやさしそうでもエクセル関数では僧でないものも多いことを知ってください。

お礼

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

回答No.1

keithin

まずエクセルを使って計算してみます。
Excelのアドインの設定で，分析ツールをチェックします。
2003までならツールメニューのアドインで，2007以降を使っているならExcelのオプションのアドインで。
除外する祝祭日をエクセルに教える必要があります。
たとえばシート２のA列に
2011/1/1
2011/1/2
2011/1/3
2011/1/10
2011/2/11
2011/3/21
　：
などの具合にして，そのセル範囲を選び，名前ボックスに「祝日一覧」などと記入して名前を付けておきます。
名前ボックス：数式バーの左端の通常A1などと表示が出ている場所
2003までは挿入メニューの名前の定義，2007以降では数式タブの名前の定義などで作成してもよい

以上の準備を済ませてから，
A1に　2011/1/24　と日付を記入して，
B1に　=WORKDAY(A1,-3,祝日一覧)　という数式を入れ，
仕上げに各セルを右クリックしてセルの書式設定の表示形式のユーザー定義で
m月d日(aaa)
と設定してできあがり。

＃ご相談投稿の際には，使いたいソフトの名称は勿論のこと，あなたが使っているそのソフトのバージョンもしっかり書いて投稿してください。ソフトの種類やバージョンによって，どこをどう操作したらいいのか変わる場合もありますから回答する方も困りますし，聞く側も折角教わったのに何を言われたのか判らなくて無駄にならないように。

お礼

ありがとうございました。今後、気を付けます。