はじめのはじめに
本文はすべて我が配下、ChatGPTに出力させたものです。検証も面倒でメモ程度に出力したものですので眉唾で見てください。
はじめに
GoogleスプレッドシートやExcelで作業する際、「特定の条件に合致するセルをハイライトしたい」というニーズはよくあります。今回は、別の領域の値を参照して条件付き書式を適用する高度なテクニックを紹介します。特に、R1C1参照方式とINDIRECT関数を組み合わせた方法は、大量のデータを扱う際に非常に便利です。
課題の例
例えば次のような状況を考えてみましょう：
- メインデータはF2:AR147に存在する
- 変更情報はAS2:CE147に記録されている（変更がある場合は「変更」と表示）
- F2:AR147の各セルに対応するAS2:CE147のセルに「変更」がある場合、そのF2:AR147のセルをハイライトしたい
解決方法：INDIRECT関数とR1C1参照を使った条件付き書式
この課題は、条件付き書式と以下の数式を使うことで簡単に解決できます：
=INDIRECT("R"&ROW()&"C"&(COLUMN()+39), FALSE) = "変更"
数式の解説
この数式は非常にパワフルですが、分解して理解する必要があります：
-
ROW()- 現在のセルの行番号を返します -
COLUMN()- 現在のセルの列番号を返します -
COLUMN()+39- 現在の列から39列右の列番号を計算します（例：F列(6)からAS列(45)への移動） -
"R"&ROW()&"C"&(COLUMN()+39)- R1C1形式のセル参照を文字列として構築します -
INDIRECT(..., FALSE)- 構築した文字列をセル参照として評価します（FALSEはR1C1形式を指定） -
= "変更"- 参照先のセルの値が「変更」と等しいかをチェックします
具体的な適用例
F列からAR列までのセルは、それぞれAS列からCE列の対応するセルを参照します：
- F列 → AS列（+39列）
- G列 → AT列（+39列）
- ...
- AR列 → CE列（+39列）
条件付き書式の設定手順
- F2:AR147の範囲を選択します
- 「書式」→「条件付き書式」を選択
- 「条件の種類」で「カスタム数式」を選択
- 数式欄に
=INDIRECT("R"&ROW()&"C"&(COLUMN()+39), FALSE) = "変更"を入力 - 書式設定（背景色など）を選択
- 「完了」をクリック
このテクニックの応用と利点
応用例
- データ検証：入力値が別の場所にある許容範囲内かをチェック
- 複数条件：複数の列の値を組み合わせて条件を構築
- 動的参照：シート内の位置に依存せず、相対的な位置関係で条件を記述
利点
- VBAやGoogle Apps Scriptを使わずに実装可能
- セルの変更に即座に反応する
- 大量のセルに一度に適用できる
- 数式の再利用が簡単
注意点
- 数式内の「+39」という数値は、参照先のセルが何列離れているかによって調整が必要です
- シートの構造を変更（列の挿入・削除など）すると、調整が必要になる場合があります
- 大量のセルに条件付き書式を適用すると、スプレッドシートのパフォーマンスが低下する可能性があります
まとめ
INDIRECTとR1C1参照を組み合わせた条件付き書式は、スプレッドシートの高度な機能を活用した効率的な方法です。この手法を使えば、複雑な条件での書式設定も簡単に実現できます。ぜひ皆さんのスプレッドシート作業に取り入れてみてください。