エクセルで結合セルを参照する場合の書式設定

質問

エクセル2010です。
A列が3行毎の結合セルになっていて、その結合セルには日付けが入っているのですが、結合セルの日付けがtodey()より小さい（過去）の場合、B列、C列、D列・・・を行方向に書式設定したいのですがうまく行きません。
範囲全体を指定して、A１＜today()でダメだったので、B１：F1と範囲指定してA1＜today()、B2:F2と範囲指定してA1＜today()、B3:F3も同じようにA1<today()とA1セルの横の3行をバラバラで書式設定して、書式コピーして範囲全体に貼り付けても部分的におかしな色になってしまいます。

何か方法があるのでしょうか？

回答No.1

mt2015 · ベストアンサー

=OFFSET($A$1,INT((ROW()-1)/3)*3,0)<TODAY()

または、上に空行を2行追加して
=(MAX($A1:$A3)<TODAY()

で、どうでしょう

お礼

追加の質問への回答も含め非常にスッキリしました。

本当にありがとうございました、

補足

いつもお世話になっております。

今回も下の式で一発でうまく行きました。色々試行錯誤したのがこんなにスッキリ解決できるとは素晴らしい！！

上は当方の表を崩さない、質問に正確な解決方法で、下は当方の実力を忖度した式だと思います。　その通りで、取りあえず下の式に飛びつきました。

そこで、別の質問になってしまうので申し訳ないのですが、結合セルを扱う時に＝MAX関数はどのようなイメージで使われているのですか？

2行の空白行の意味も分かるような分からないような。
１－３行目の間のMAX（日付け）を参照する為だと思うのですが、なぜ3行目をMAXにする必要があるのでしょう？（全くの勘違い？）

更に当方の、A1を参照する3行の1行毎に書式を指定する方法ではナゼうまく行かないのかもわかりません。（部分的にうまく行ったりいかなかったり再現性が見つけられませんでした）

解決後のご回答に対する追加の質問で本当に恐縮ですが、出来ればご教示お願い致します。

回答No.4

bunjii

>A列が3行毎の結合セルになっていて、その結合セルには日付けが入っているのですが、結合セルの日付けがtodey()より小さい（過去）の場合
A1:A3が結合されている場合はA1の値が表示され、A2:A3の値は未入力（Blank）になりますがセル自体は隠されているだけで参照対象になり得ます。
従って、B列から右側のセルと比較するときは3行毎に位置合わせが必要になります。

>範囲全体を指定して、A１＜today()でダメだったので
その数式では1行目、4行目、7行目、・・・・のように飛び飛びになります。

>B１：F1と範囲指定してA1＜today()、B2:F2と範囲指定してA1＜today()、B3:F3も同じようにA1<today()とA1セルの横の3行をバラバラで書式設定して
論理としては3組の条件付き書式で良いのですがコピー&ペーストの手順で不備があったのではないでしょうか？

>何か方法があるのでしょうか？
回答No.2の数式で良いと思います。
他に次の数式でも目的通りになります。
=OFFSET($A$1,INT((ROW()-1)/3)*3,0)<TODAY()
回答No.1の数式では一部にズレが出るはずです。

お礼

いつもお世話になっております。
仰ること全てその通りでした。

(1)再現性が無かったのは、最初から表を作り直してやれば同じ結果が得られる（当然ですが）ことから、色々やっているうちにC&Pの手順がいい加減になっていたと思います。

(2)MAX関数ではズレが出る、ことが今日の朝分かって色々どこが違っているのか調べ始めていたところです。
昨日うまくいった（前日までの入力行が指定書式通り）のにと思い、再度このページで回答を確認しようとしてこのご指摘を見ました。

昨日テストシートで日付で設定しておいたのですが、本日の1行目だけ青字になっていました。

再質問前のご指摘に感謝です。　
またまた無い知恵絞って試行錯誤せずに済みました。

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

＜追記＞
MAX関数ではどうしてズレがでるのかが気になりますが・・・

回答No.3

mt2015

ANo.1です。

> そこで、別の質問になってしまうので申し訳ないのですが、結合セルを扱う時に＝MAX関数はどのようなイメージで使われているのですか？
>
> 2行の空白行の意味も分かるような分からないような。
> １－３行目の間のMAX（日付け）を参照する為だと思うのですが、なぜ3行目をMAXにする必要があるのでしょう？（全くの勘違い？）

結合セルの場合、ご存知の様ですが実際に値が入っているのは結合範囲の一番左上のセルになります。
例えば、A3:A5が結合されている場合、値が入っているのはA3のみで、A4:A5は空です。
MAX関数を使用して、自行の2つ上～自行までのA列の最大値を求めることで結合セルの日付が取得できると言う訳です。
＃MAXでなくてもSUMでも同じ事が出来ます

ただ、これをやるとA1から始まっている場合、「A1の２つ上」とやるとエラーになりますのでワザと2行の空白行を作っています。

> 更に当方の、A1を参照する3行の1行毎に書式を指定する方法ではナゼうまく行かないのかもわかりません。（部分的にうまく行ったりいかなかったり再現性が見つけられませんでした）

貴方の方法でも式を　$A1＜today()　とすれば大丈夫だったと思います。

お礼

追加の質問への回答も含め非常にスッキリしました。

本当にありがとうございました。

補足

色々試行錯誤しているうちに、参照の絶対と相対が抜けてしまいました。

再度やってみました！　うまく行きました！！

結合セルでのMAX関数の使い方を1つ覚えました。感謝！！！

回答No.2

chie65536（@chie65535）

B1～F30全体を範囲指定して、条件付書式で
=INDIRECT("A"&INT((ROW()-1)/3)*3+1)<TODAY()
の数式を指定してみて下さい。

お礼

お世話になります。

確認していたのでお礼が遅れましたが、うまく行くことを確認しました。

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