Excelの条件付き書式で別シートを参照

Excel2003で条件付き書式の数式を別シートから参照するとエラーとなり使うことができない

例として以下のような2つのシートがあり、[集計]シートから[結果]シートを条件付き書式で
参照してみる

[結果]シート

excel1

[集計]シート

excel2

ここで[集計]シートのB2に条件付き書式を入力してみると、以下のようなエラーが表示される

excel3

excel4

 

直接指定することができないので、INDIRECT関数を使用するで回避できる

参考:Excel(エクセル)基本講座:条件付き書式の使い方/別シートを参照する

INDIRECT関数を使用して数式を作成

excel5

この条件付き書式は、[結果]シートのB2からB4の中の最大値に赤色を付けるという意味なので
最大値である90に赤色が塗りつぶされる

excel6

しかし、この条件付き書式を横方向にコピーすると・・・

excel7

vlookupの検索条件をBさんにすると色が塗りつぶされず、意図した結果とならない

C2の条件付き書式を確認してみると、INDIRECTの参照先が「"結果!B$2:B$4"」なっている
※本来であれば、「=C2=MAX(INDIRECT(“結果!C$2:C$4”))」と変わってほしい

excel8

どうもINDIRECT関数だけではオートフィルのコピーではうまく相対参照が生かし切れない

そこで、INDIRECT関数にCELL関数を加えて数式を作成する

参考:エクセルへの近道(CELL関数の使い方)

B2の条件付き書式を以下のような数式に変更する

「=B2=MAX(INDIRECT("結果!"&CELL("ADDRESS",B$2)&":"&CELL("ADDRESS",B$4)))」

excel9

この数式であれば、フィルコピーをしても相対参照を生かすことができ、以下のように意図する結果となる

excel10

excel11

excel12

 

条件付き書式で別シートを参照する際には

INDIRECT(“シート名”CELL(“ADDRESS”,セル)

この数式の形を作ればよい!?

広告
カテゴリー: Office パーマリンク

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google フォト

Google アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中