EXCEL2003 別シートを参照する条件付書式のINDIRECTの使

質問

EXCEL2003　別シートを参照する条件付書式のINDIRECTの使い方

いつもお世話になっております。
別シートを参照する条件付書式のINDIRECTの使い方について教えてください。

現在シートが2枚（表とLIST)あり表のH列がブランクではない時に
I5：U6に条件付書式を入れたいと思っています。
シート構成は以下の通りです。

--------------------------------------------------------------
設定したい条件（例：I4/4月度の氏名：「ああ」の場合）

H5がブランクではない時
シート：表のI5の値（320）が
シート：LISTのBK3より（設定された値/4月度の氏名：「ああ」は293）より
大きい場合に赤の太文字にしたい
--------------------------------------------------------------

ブランクではない場合はH5<>"",

LISTの値を見る（ああの4月の値を出す）数式は
以下の式を使ったことがあるのですが
=SUMPRODUCT((LIST!$BF$3:$BF$147=F5)*(LIST!$BK$2:$BV$2=I4)*(LIST!$BK$3:$BV$147))

この組み合わせ方や別シートを参照するINDIRECTの使い方がわかりません。

設定したい条件を可能にするにはどのようにしたらいいのでしょうか？
ご教示お願いいたします。

シート:表(I4:U4は4月：3月の意味です）
　　　　　　F　　　　　G　　　　H　　　　 　I　　 　・・・　　　U
4 No. 　　氏名 　　区分 　　 4 　 　　　 5　　　・・・　　　3
5 11 　　　ああ 　　AA 　　 320 　　　310　　・・・　　　300
6 12 　　　いい 　　AB 　　 295 　　　200　　・・・　　　280

シート：LIST（BF2:BV147で1ヶ月の制限の値が入った表です）
　　BF　　　BG　　 　BK　 　BL・・・　　BV
2 　 No. 　 氏名　 　　4　 　　 5・・・ 　　3
3　 11 　 ああ　　 　293 　　 263・・・290
4　 12 　　いい 　　 295　 　　300・・・293

回答No.1

KURUMITO · ベストアンサー

条件付き書式で他のシートを参照する場合には、それらの範囲に名前を付けて対応するのがよいでしょう。
例えばLISTのシートではＢＧ列を選択したのちに「数式」タブの「名前の定義」で名前の窓に　氏名範囲　とでも入力してＯＫします。また、ＢＫ列からＢＶ列までを選択してから同じ操作で名前の窓に　月範囲　とでも入力してＯＫします。
その後に表シートではＩ5セルからＴ1000セルまでを範囲として指定したのちに「ホーム」タブで「条件付き書式」を選択して、条件付きの式の窓には次の式を入力してから書式を設定してＯＫします。

=I5>INDEX(月範囲,MATCH($G5,氏名範囲,0),COLUMN(A1))

お礼

レスありがとうございます。

ご指導いただいたとおりやったところできたのですが、
先に設定していた下記条件が青になってしまいました。
ひとつにまとめなければいけないということなのでしょうか？

320を越してしまった時は赤の太文字にするという式
=AND($H5<>"",I5>320)

補足

失礼しました。
違いますよね・・・ひとつにまとめたら書式設定ができないですよね。
LISTの該当する時間を越して320未満のものとしなければいけなかったので・・・
>320をどこに書き足せばいいでしょうか？
引き続きご教示お願いできますでしょうか？

回答No.3

imogasi

質問例を再現するのにえらく時間がかかった。
もう少し質問の表現に工夫して、読者・回答者の負担を軽くすることを考えるのが、質問者の務めではと思うが。
ーー
例えば、質問表現は
今年と去年の月別１２ヶ月の個人の計数表があります。（各月の列は両表でデータの在る列が違います）
ある個人の今年の計数が、去年の同月より良い（大の）場合は、今年の計数のセルに色をつけたい。
これなら、２行で済むではないか。
そして現質問に近い実例を挙げる。
ーーー
条件付き書式では、他シートのデータを比較に使おうとすると、「抽出条件：条件付書式で、他のワークシートまたはブックへの参照は使えません」とエラーなる。
この解決策は、セルへの名前の設定で凌ぐことである。
例えばＳｈｅｅｔ４のＣ２とＳｈｅｅｔ３のＢ２(わざとヅラしてある）を比べるときは
Ｓｈｅｅｔ４のＣ２に「aa]の名前をつけたとき
Ｓｈｅｅｔ３で「数式が」で　=B2>aa　を入れて、パターン色設定で実現する。
ーーー
この例の場合は4月ー来年３月までと多セルについて考える必要があるので
例　Ｓｈｅｅｔ４　Ｃ１：Ｆ１
４月 ５月 ６月 ７月　（8月以降は説明を短くするため省略）
の見出しに、「月名」と名前をつける。
ーー
データも含めて名前をつける。
例　Ｓｈｅｅｔ４　Ｃ１：Ｆ２
４月 ５月 ６月 ７月
24 11 34 23
Ｓｈｅｅｔ４でＣ１：Ｆ２を範囲指定し
挿入ー名前ー定義ー「月」で名前をつける。
ーー
Ｓｈｅｅｔ３で　Ｂ２：Ｅ２を範囲指定して
書式ー条件付き書式ー数式が、で数式に
=B2>INDEX(月,2,MATCH(B1,月名,0))
これでＢ２：Ｅ２の値を変えると、対応したＳｈｅｅｔ４の月と比較して、大の場合セルに色が付くようだ。
ーー
残るは同じ氏名（例　ああ）の行を捉えることだ。
名前（か氏名ＮＯ）を使いＭＡＴＣＨ関数でＳｈｅｅｔ４の氏名列「ああ」などの行を探し、上記の式の「月、２」の２のところへ、ＭＡＴＣＨ関数式で置き変える。

回答No.2

MackyNo1

他シートを参照する条件付き書式をＩＮＤIRECT関数を使って作成するときは以下のような手順が汎用性があり簡単かもしれません。

まず条件付き書式を付けるセル1つを選択し、他シートを参照する条件付き書式の数式を入力し、「ツール」「オプション」の全般タブで「Ｒ１Ｃ１形式を使用する」にチェックを入れます。

次に他シートを参照する部分、例示の数式なら「LIST!R3C58:R147C58」の部分を「INDIRECT（"LIST!R3C58:R147C58",FALSE）のようにその文字列部分を「""」で囲んだ数式にします。

数式バーからこの数式をコピーし、そのセルをアクティブセルとして条件付き書式の数式として登録します。

最後にツール」「オプション」の全般タブで「Ｒ１Ｃ１形式を使用する」のチェックを外します。