Excelで特定の日付以降の非表示

質問

閲覧有難うございます。

Excelを使用してシフト表を作りたいのですが、開始日を各月の１１日、締め日を１０日とした場合

１０日以降の日付を表示しない方法を教えていただきたいです。

日付の自動入力を使っているのですが、月末日が変動する月

（２月や９月など、３０日までしかないまたは３０日以前に終わってしまう月）

はオートフィルで自動入力の関数文を指定した場合

１０日以降の日付まで表示されてしまうのです。

１１日から１０日まで限定で表示したい場合はどうすればいいでしょうか。

IF文を使えばいいのか、それとももっと別の関数を使うべきなのかわかりません。

教えていただければ幸いです。

以下自分が書いている状態です。

C2に年数（2013）　D2に月（今でしたら9）
D3に＝DETA（C2,D2,11) 　E3に=（D3+1）とし、F3・・＝（E3+1）となっております。以下右に同じようにオートフィルで関数文が指定されてます。

回答No.7

cj_mover · ベストアンサー

＃４、ｃｊです。お礼欄のリクエストへのレスです。

エラーになる状況がこちらでは判らないのですが、
何度かシミュレーションして手順を詳細に改めました。
数式についても、＃４投稿時に編集ミスがあったので、
それぞれ一文字（絶対参照記号）だけ追加しています。

　　Ｘセルの計算結果をＹセルで参照して、
　　そのＹセルの計算結果をＺセルで参照して、
　　そのＺセルの計算結果を、、、
というような数珠つなぎ状態は
表計算アプリケーションでは、なるべく避けた方が好いです。
でもまぁ、これは私の主義のようなものであって、
「、、、でなければならない」というような意味では決してありませんが、
継続的に扱うブックなら、私はそういう数式を残しません。
私の手順は結構面倒臭いと思いますけれど、それでも、
仕上がった数式の計算ステップ数や参照のあり方などは
シンプルなものになるように書いています。

絶対参照で書いていますが、
こちらは主義というより好みに近いでしょうけれど、
自分ならこうする、というものです。

こちらではエラーを再現することが出来ませんので、
もし、またうまく行かなかったら、もう少し詳しい状況を教えてください。
念の為ですが、もしも、セルに ######## のように表示されているということでしたらば
それは、セルの幅とフォントサイズを調整すれば正しく表示されます。

◆手順以下
（D3 セルには事前に数式=DATE($C$2,$D$2,11)が設定してある前提です。）
Excel対象ブックを開いておき、当ページを表示
１◆【１２日以降の２７日間の数式となる文字列を数式設定】
　　当ページから下の数式をコピー　※前後に空白や改行を入れないよう注意
　　　　="=$D$3+"&COLUMN()-4
　　[Alt]キー を押下げたまま [Tab] キー押下げ　　　→ Excelを表示
　　[数式バー]左にある[名前ボックス]に
　　　　E3:AE3
　　とタイプして[Enter]キー　　　　→　E3:AE3 を選択状態に
　　[F2]キー押下げ　　　→　E3セルを編集モードに
　　（E3セルに入力済の数式がある場合はそのまま消去）
　　[Ctrl]キー を押下げたまま [V] キー押下げ　　　→ [数式バー]にコピーした数式を貼付け
　　貼り付けた数式の前後に空白や改行がないか[数式バー]を確認
　　[Ctrl]キー を押下げたまま [Enter]キー押下げ　　　→　E3:AE3 に数式を確定

２◆【月によっては非表示となるセルに数式となる文字列を数式設定】
　　[Alt]キー を押下げたまま [Tab] キー押下げ　　　→ Webブラウザを表示
　　当ページから下の数式をコピー　※前後に空白や改行を入れないよう注意
　　　　="=IF(DAY(DATE($C$2,$D$2+1,0))<"&COLUMN()-3&","""",$D$3+"&COLUMN()-4&")"
　　[Alt]キー を押下げたまま [Tab] キー押下げ　　　→ Excelを表示
　　[数式バー]左にある[名前ボックス]に
　　　　AF3:AH3
　　とタイプして[Enter]キー　　　　→　AF3:AH3 を選択状態に
　　[F2]キー押下げ　　　→　AF3セルを編集モードに
　　（AF3セルに入力済の数式がある場合はそのまま消去）
　　[Ctrl]キー を押下げたまま [V] キー押下げ　　　→ [数式バー]にコピーした数式を貼付け
　　貼り付けた数式の前後に空白や改行がないか[数式バー]を確認
　　[Ctrl]キー を押下げたまま [Enter]キー押下げ　　　→　AF3:AH3 に数式を確定

３◆【数式の戻り値文字列を数式に変換】
　　[数式バー]左にある[名前ボックス]に
　　　　E3:AH3
　　とタイプする　　　　→　E3:AH3 を選択状態に
　　[Ctrl]キー + [C] キー押下げ　　　→　E3:AH3 をコピーモードに
　　[Alt]キー を押下げたまま [E] [S] [V] キー押下げ　　　→　[値のみ貼付け]のダイアログ選択
　　[Enter]キー　　　→　[値のみ貼付け] を実行
　　[Ctrl]キー + [H] キー押下げ　　　→　置換ダイアログ表示、E3:AH3 を置換待機状態に
　　置換ダイアログの [検索する文字列] に、半角で一文字だけ = と入力
　　置換ダイアログの [置換後の文字列] に、半角で一文字だけ = と入力
　　置換ダイアログの [すべて置換] ボタンをクリック　　　→　置換による数式化完了

手順以上◆

以上の手順のより
◆◆最終的な数式は

　　D3 は手を加えず元のまま
　　　　=DATE(C2,D2,11)
　　E3:AE3 は新たに
　　　　=$D$3+1、=$D$3+2、=$D$3+3、、、=$D$3+27
　　AF3:AH3 はそれぞれ
　　　　=IF(DAY(DATE($C$2,$D$2+1,0))<29,"",$D$3+28)
　　　　=IF(DAY(DATE($C$2,$D$2+1,0))<30,"",$D$3+29)
　　　　=IF(DAY(DATE($C$2,$D$2+1,0))<31,"",$D$3+30)

のようになります◆◆

以上です。

お礼

遅くなって申し訳ないです。
忙しくて中々先に進められなくてチェックする暇がなかったです。　すいません。

様々な方法を知ることが出来て、すごく勉強になりました。

シンプルなものから、他の事象に対しても有効そうなものまで。
このなかからベストアンサーを選ぶのはすごく迷いましたが、お礼に対してもう一度返信してくれたということ、より細かな手順の提示というところで、こちらの方をベストアンサーとさせていただきます。

他の皆様のやり方もすべて挑戦してみて、無事できました。
本来なら個別でお礼という形を取るべきですが
どれも驚きの連続でしたし、お礼の回答が一辺倒になってしまうと思いましたので
こちらの項目でまとめて皆様に御礼を申し上げます。

回答していただいた方々ありがとうございました。
まだ、いくつか詰まってるところがありますので、また質問することになると思います。

その際はまた機会がございましたらよろしくお願いします。

回答No.10

MarcoRossiItaly

ご覧のとおり様々な方法があって、If 文というより IF 関数を使う方法、EDATE（恐らく expiration date）関数を使う方法、条件付き書式を使う方法…が出ましたね。

その他に、へそ曲がりな（笑）方法としては、翌月 11 日に達したセルでは「0」を算出し、ゼロについてはセルの書式（表示形式）で非表示にするという手もあります。

E3　=(max($D3:d3)<=date($C2,$D2+1,11)-2)*(d3+1)
　　あるいは
　　=(0<d3)*(d3<=date($C2,$D2+1,11)-2)*(d3+1)

　　※「date($C2,$D2+1,11)」という部分は「edate($D3,1)」と書き換えてもオッケー。
　　　ただし Excel 2003 以前においては、EDATE はアドイン「分析ツール」の関数。

と記入。通常の日付の書式のままだと、ご存じかもしれませんが、「0」は「1900/1/0」といった感じに表示されます。ユーザー定義書式として「[=0]"";d」などを設定すると、ゼロは消えます。

あるいはセルの書式は変更せずに、Excel のオプションの詳細設定で「ゼロ値のセルにゼロを表示する」のチェックを外すということでも可能です。

回答No.9

noname#204879

[No.8]へのコメント、
書き漏らしましたが、セル D3、E3 の書式は事前に d に設定しています。
イワズモガナなこととは思うけど、為念。

回答No.8

noname#204879

セル E3 に次の[条件付き書式]を設定
　　数式が　　　　=E3>DATE($C2,$D2+1,0)
　　フォント色　　白
セル E3 に数式 =D3+1 を入力して、此れを右方へズズーッとドラッグ＆ペースト

回答No.6

bunjii

>IF文を使えばいいのか、それとももっと別の関数を使うべきなのかわかりません。
IF関数を使いますが入れ子構造にしないと#VALUEになるセルが発生します。
D3セルは良いとして、E3セルには次の式を使うと良いでしょう。

=IF(ISTEXT(D3),"",IF(AND((YEAR(D3+1)-YEAR($D3))*12+MONTH(D3+1)-MONTH($D3)>0,DAY(D3+1)>10),"",D3+1))

最初のISTEXT（D3)は直前のセル（左側）が文字列のとき#VALUEエラーが起こるので、それを回避するためです。
AND((YEAR(D3+1)-YEAR($D3))*12+MONTH(D3+1)-MONTH($D3)>0,DAY(D3+1)>10)は初日の月数と翌月1日以降の月数を算出して翌月の10日までに該当するか否かを判定するための条件です。
この式には12月11日から1月10日までについても対応しています。

尚、10月分を4行目に設定するときはD4セルに次の式を使うと良いでしょう。

=DATE(YEAR(D3),MONTH(D3)+1,11)

その時、E4の式はE3の式を下方にコピーすればそのまま使えます。

回答No.5

tom04

こんにちは！

すでに色々回答は出ていますので、参考程度で・・・
Excel2007以降での方法になりますが、

E3セルに
=IFERROR(IF(D3+1<EDATE($D3,1),D3+1,""),"")

という数式を入れ、AH3セルまでオートフィルでコピーしてみてください。m（_ _）m

回答No.4

cj_mover

こんにちは。お邪魔します。

ついでに全体の数式を軽～いものに直しましょう。
"数式を作る数式"を使うので、手順、確認しながら進めてください。

◆手順以下

　E3 に
="=D$3+"&COLUMN()-4
　AE3 まで右にオートフィル

　AF3 に
="=IF(DAY(DATE($C$2,$D$2+1,0))<"&COLUMN()-3&","""",D$3+"&COLUMN()-4&")"
　AH3 まで右にオートフィル

　B3:AH3 を選択し、そのまま連続して
　　　コピー
　　　値のみ貼付け
　　　置換機能
　　　　　検索する文字列　　=
　　　　　置換後の文字列　　=

手順以上◆

◆◆最終的な数式は

　D3 に
=DATE(C2,D2,11)
　E3:AE3 に
=D$3+1、=D$3+2、=D$3+3、、、=D$3+27
　AF3:AH3 に
=IF(DAY(DATE($C$2,$D$2+1,0))<29,"",D$3+28)
=IF(DAY(DATE($C$2,$D$2+1,0))<30,"",D$3+29)
=IF(DAY(DATE($C$2,$D$2+1,0))<31,"",D$3+30)

のようにします◆◆

お礼

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

皆様とはちょっと異なるやり方ということで今試していたのですが、どうも置換機能の場所でエラーを起こしてしまいますね。

このやり方はできれば会得しておきたいのでまた新たに回答いただければと思っております。

回答No.3

mshr1962

翌月の11日以降の日付を空白にすればいいので、翌月11日は

=DATE($C$2,$D$2+1,11)

で求められます。なので

D3=DATE($C$2,$D$2,11)
E3=IF(D3+1<DATE($C$2,$D$2+1,11),D3+1,"")

としてE3の式をAH3までコピーしてください。

お礼

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

IF文を使ってやる方法がメインとなるみたいですね。

回答No.2

sora1515

IF関数でどうでしょうか。

AE3に=IF(AD3="","",IF(DAY(AD3)>9,"",AD3+1))
これをAH3までオートフィル。

AE3は閏年最小のセルAH3は31日までの月を想定です。

お礼

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

すごくシンプルでわかりやすいです。

これはすぐに使えそうですね。

回答No.1

keithin

＞１０日以降の日付を表示しない

やり口は様々ありますが割と文字通りに計算してみると
Ｄ３に
=IF(DATE($C$2,$D$2,COLUMN(K3))>DATE($C$2,$D$2+1,10),"",DATE($C$2,$D$2,COLUMN(K3)))
と記入、右にコピー。

＃実際には
＞D3に＝DETA（C2,D2,11) 　E3に=（D3+1）とし、F3・・＝（E3+1）

そのやり方で、ＡＥ列までのセルは何の支障もなく計算できています。つまりここまでは１０日を超えてるか、気にする必要は全然ありません。
実際にはＡＦ列からＡＨ列までの３つのセルだけについて、１０日を超えているかどうかを個別にチェックすれば良いだけのお話です。

もっともこの手のご相談では、最初からみんな同じ数式が並んでる方が「気持ちイイ」のでしょうか、前述のようなやり方を好まれるかたがほとんどなんですけどね。

お礼

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

回答してくださったかたの方法を今一つずつ試しているところなのでベストアンサーはまだつけられません。

自分はどちらかと言えば、要所要所で複雑であったり詳しく書かれてるほうがいいんですけどね。
なんというか全部コピーだと後々に開いた時に困ったり、他人がみたときにもわかりやすい方がいいですしね。