また、お知恵を貸してください。【エクセル】

質問

先日は、時系列での同じ質問をさせてもらったのですが
今回は、年間で日付を同じように左の表から右の図に自動入力させたいのですが
前回同様に条件付き書式で数式を入力し塗りつぶしを使用して表す方法で考えています。
数式を教えてください。
エクセルは2010です。

回答No.4

kagakusuki · ベストアンサー

回答No.3の続きです。

　次に以下の操作を行って、左の表において期間内の日付の所のみを塗りつぶすための条件付き書式を設定して下さい。

C5セルを選択
　　↓
Excelウインドウの[ホーム]タブをクリック
　　↓
「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=SUMPRODUCT(($NG$3:$NM$3<>"")*ISNUMBER(1/(DATE(YEAR(SUBSTITUTE($D$2,"年",)&"年1月1日"),4,COLUMNS($C:C))>=DATE(YEAR(SUBSTITUTE($D$2,"年",)&"年1月1日")+(MONTH($NG4:$NM4)<4),MONTH($NG4:$NM4),DAY($NG4:$NM4)))/DAY($NG4:$NM4)/(DATE(YEAR(SUBSTITUTE($D$2,"年",)&"年1月1日"),4,COLUMNS($C:C))<=DATE(YEAR(SUBSTITUTE($D$2,"年",)&"年1月1日")+(MONTH($NH4:$NN4)<4),MONTH($NH4:$NN4),DAY($NH4:$NN4)))/DAY($NH4:$NN4)))
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた[色]欄をクリック
　　↓
現れた色のサンプルの中にある赤色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある「書式」欄が赤色の塗りつぶしとなっている行の「適用先」欄に

=$C$5:$ND$5

と入力
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[OK]ボタンをクリック
　　↓
C5～ND5のセル範囲をまとめて範囲選択
　　↓
[Ctrl]キーを押しながら[C]キーを押す事で、選択した範囲をコピー
　　↓
C8セルを選択
　　↓
[Alt]キーを押す
　　↓
[Alt]キーから指を放してから、[E]キーを押す
　　↓
[E]キーから指を放してから、[S]キーを押す
　　↓
[形式を選択して貼り付け]ダイアログボックスが表示された事を確認してから、[T]キーを押す
　　↓
[形式を選択して貼り付け]ダイアログボックスの中の[書式]と記されている欄の所にのみチェックが入っている事を確認し、[Enter]キーを押す
　　↓
C11セルを選択
　　↓
[Alt]キーを押す
　　↓
[Alt]キーから指を放してから、[E]キーを押す
　　↓
[E]キーから指を放してから、[S]キーを押す
　　↓
[形式を選択して貼り付け]ダイアログボックスが表示された事を確認してから、[T]キーを押す
　　↓
[形式を選択して貼り付け]ダイアログボックスの中の[書式]と記されている欄の所にのみチェックが入っている事を確認し、[Enter]キーを押す
　　↓
左の表全体を含むセル範囲をまとめて範囲選択
　　↓
「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある「適用先」欄が

=$C$8:$ND$8

となっている行を選択
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[ルールの編集]ボタンをクリック
　　↓
現れた「書式ルールの編集」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた[色]欄をクリック
　　↓
現れた色のサンプルの中にある黄色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「書式ルールの編集」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある「適用先」欄が

=$C$11:$ND$11

となっている行を選択
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[ルールの編集]ボタンをクリック
　　↓
現れた「書式ルールの編集」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた[色]欄をクリック
　　↓
現れた色のサンプルの中にある緑色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「書式ルールの編集」ダイアログボックスの[OK]ボタンをクリック
　　↓
前に設定していた罫線に関する条件付き書式のものも含めて、「条件付き書式ルールの管理」ダイアログボックスの中にある[条件を満たす場合は停止]欄のチェックが全て外れている状態にする
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[OK]ボタンをクリック

　次に、NG4～NN12のセル範囲(期間の初日や最終日を入力する欄)の書式の表示形式を[日付]の

3月14日

或いは

3/14

等にして下さい。
　次に、NG4～NG6のセル範囲にあるセルを結合して下さい。
　そして同様にNH4～NH6のセル範囲にあるセルを結合して下さい。
　次に、NG4～NH6のセル範囲をまとめてコピーして、NG4～NN12のセル範囲に貼り付けて下さい。

　以上で設定は終了です。
　後はD2セルに「平成27」(文字列データ)或いは「2015」(数値データ)などといった年を指定するデータを入力し、右の表に各期間の名称と、期間の初日の日付、期間の最後の日の日付をそれぞれ入力しますと、左の表においてそれらの期間に対応するセルが塗りつぶされます。

　尚、Excelにおいては、2015年の内に2016/2/29の日付を入力するなどといった、閏年ではない年の内に2月29日を入力しようとする際に、

2/29

や

2月29日

等の様に年を指定しない形式で入力しましても、"2/29"や"2月29日"といった文字列データになるだけで、日付けデータとはなりません。
　それでは年が不明なため、日付として計算する事が出来ませんので、閏年以外の年の内に2/29を入力する際には

2016/2/29

等の様に、必ず年数を指定する形で入力する様にして下さい。
　尚、今回私が提案させて頂いた方法の場合は、右の表に入力された日付が、例えば西暦9996年2月29日などといったとんでもない年の日付であっても、それが年を含む日付データに変換可能な値でありさえすれば、D2列に例えば2016と入力されていれば、西暦2016年2月29日のデータとして扱われます。(文字列データとしての"2/29"や"2月29日"は不可です)

お礼

質問の仕方が悪く、添付の画像も粗悪で申し訳ありませんでした。
それでも、こうやって詳しく教えていただいて感謝します。ありがとうございます。
加えて、添付の際、いつもプリントスクリーン機能を使って別のCADソフトに貼り付けてJPEGに変換しているのですが何か添付する方法があれば教えてください。
すみません

回答No.3

kagakusuki

期間を記入する右側の表の構造が異なりますから、質問No.8937448と同じ方法では上手く行かない筈です。
　それに日付の場合は閏年の問題があり、2月29日の有無により、3月以降の日付が1日ずれてしまう事がない様にするための処理が結構面倒になります。
　又、閏年と「閏年ではない年」では、1年の日数が異なるため、閏年には左の表の枠に囲まれた部分が、1列増える様にするための条件付き書式も必要となります。
　ですから、どの年の表なのかという事を示すデータを入力するセルも必要となります。

　質問者様が添付された画像は文字が全て潰れていて、どの様なデータが入力されているのかという事も、データが入力されているセルのセル番号も、全て判別不明な状態となっていますので、取り敢えず以下の様な仮定に基づいて話を進めさせて頂きます。

・D2セルに「平成27」(文字列データ)或いは「2015」(数値データ)などといった年を指定するデータを入力するものとする

・左の表はB3:ND12のセル範囲に設けられていて、3行目には月が入力されていて、B列は項目名の入力欄として使用されていて、C列～ND列が1年間の中の特定の日付を示す列であり、C列は4/1の日付に相当する

・右の表に記入される期間に基づいて、左の表の内、行番号5行目と8行目と11行目の行の、期間内に該当する列のセルのみが、条件付き書式によって色が変わる様にする

・右の表はNF3:NN12のセル範囲に設けられていて、3行目には期間の名称が入力されていて、NF列は項目名の入力欄として使用されていて、行番号5行目と8行目と11行目の行の、NG列、NI列、NK列に、各期間が始まる日付が入力されていて、NH列、NJ列、NL列に、各期間の最後の日付が入力されるものとする

・「2015/2/29」や「2016年度の2月29日」などといった、存在しえない日付は入力される事が無いものとする

　上記の仮定に基づいた場合、御質問の件を解決する方法の一例は以下の様なものとなります。

　まず、B列～NC列の、2行目と3行目の間に水平の罫線を引いて下さい。
　同様にB列～NC列の、3行目と4行目の間、6行目と7行目の間、9行目と10行目の間、12行目と13行目の間にも水平の罫線を引いて下さい。
　次に、3行目～12行目のB列の両サイドに垂直の罫線を引いて下さい。
　左の表においては、B列の両サイドの2本を除き、垂直の罫線は引かないで下さい。
　ND3～ND12のセル範囲には罫線を引かないで下さい。
　次に、Q3セル(4/15に相当する列の3行目のセル)の書式設定において文字の横位置を[中央揃え]に設定して下さい。(セルの結合は不可)
　次に、Q3セルに次の関数を入力して下さい。

=TEXT((SUBSTITUTE($D$2,"年",)&"年4月1日")+COLUMNS($C:Q)-1,"m月")

　次に、Q3セルをコピーして、行番号3行目の行における左の表の各月の真ん中付近の日付(2月なら14日、その他の月なら15日)の列の所のセルに、1箇所ずつ貼り付けて下さい。(見栄えの問題だけですので、大体真ん中あたりというだけで良く、多少左右にずれても構いません)

　次に、NF3:NN12のセル範囲に、右の表のための罫線を引いて下さい。
　次に、NG3セルとNH3セルを結合して下さい。
　同様に、NI3:NJ3、NK3:NL3、NM3:NN3の各セル範囲に関しても、2個1組で1つの結合セルとなる様に結合して下さい。
　次に、それら2個1組で結合した各結合セルに、各期間の項目名を入力して下さい。
　尚、右の表において期間の最終日を入力する列の3行目のセルである、NH3セル、NJ3セル、NL3セル、NN3セルには何も入力されていない様にして下さい。
　このNG3セル、NI3セル、NK3セル、NM3セルに項目名を入力し、NH3セル、NJ3セル、NL3セル、NN3セルを空欄にしておく事は、後で設定する条件付き書式において、「期間の始まりの日付が入力されている列がどの列であるのか」という事を、関数で判定する事に関わってきますので、必ず実行する様にして下さい。(但し、期間の開始日を入力しない列には、期間の項目名を入力してもしなくてもどちらであっても構いません)

　次に以下の操作を行って、左の表に条件付き書式を設定して下さい。

C3セルを選択
　　↓
Excelウインドウの[ホーム]タブをクリック
　　↓
「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=DAY((SUBSTITUTE($D$2,"年",)&"年4月1日")+COLUMNS($C:C))=1
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「スタイル」欄の中にある実線をクリック
　　↓
「罫線」欄の中にある右側の縦線のみをクリックし、右側の縦罫線のみが実線で、他の罫線は灰色となっている様にする
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある「書式」欄において、右側の縦線のみが設定されている行の「適用先」欄に

=$C$3:$ND$12

と入力
　　↓
ND6セルを選択
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=DAY((SUBSTITUTE($D$2,"年",)&"年4月1日")+COLUMNS($C:ND))=1
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「スタイル」欄の中にある実線をクリック
　　↓
「罫線」欄の中にある右側の縦線のみをクリックし、下側の横罫線のみが実線で、他の罫線は灰色となっている様にする
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある「書式」欄において、下側の横線のみが設定されている行の「適用先」欄に

=$ND$6,$ND$9,$ND$12

と入力
　　↓
ND3セルを選択
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=DAY((SUBSTITUTE($D$2,"年",)&"年4月1日")+COLUMNS($C:ND))=1
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「スタイル」欄の中にある実線をクリック
　　↓
「罫線」欄の中にある右側の縦線のみをクリックし、上下2本の横罫線のみが実線で、他の罫線は灰色となっている様にする
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある「書式」欄において、上下2本の横線のみが設定されている行の「適用先」欄が

=$ND$3

となっている事を確認
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[OK]ボタンをクリック

　これで閏年に合わせて左の表のに列数を変えるための条件付き書式が設定されます。

※まだ途中なのですが、そろそろこのサイトの回答欄に入力可能な文字数制限を超えそうですので、残りは又後で投稿致します。

回答No.2

MackyNo1

＞今回は、年間で日付を同じように左の表から右の図に自動入力させたいのですが

以下の質問の続きですね。
http://okwave.jp/qa/q8937448.html

基本は、これまでの皆さんの回答を参考にすれば、同じような処理で対応することができます。
ただし、ベストアンサーにされた方法は、数式が複雑で簡単に対応しにくいかもしれません。

お勧めは、私の回答した日付（や時刻）をセルから参照するNo1の方法です（試されたのでしょうか？）。
この方法なら、日付単位の場合、さらに簡単な数式で条件付き書式を設定することができます。

これまでの回答で何度も指摘していますが、添付画像が小さいので、日付や開始日や終了日の条件がどのセルに入力されているのかなど、具体的にレイアウトやセル番地を例示するようにしてください。

ちなみに、開始や終了日を縦方向に入力しているなら、配列を使用した1つの数式で複数の日付範囲の条件付き書式をまとめて設定することもできます。

回答No.1

msMike

》 先日は、…同じ質問をさせてもらった…
その際に画像が見辛い/読み取れないと指摘されていたのに、なぜ今回も似た画像を添付したのかなぁ～！