エクセルで自動化！ OFFSET関数を使った条件付き書式範囲の変更方法【ダウンロード即利用】
えくせるサイトウ
Excelの条件付き書式を使用すると、
セルに特定の条件が満たされた場合に、セルの背景色やフォントなどの書式を自動的に変更できます。
しかし、条件付き書式の範囲を変更する必要がある場合があります。
例えば、新しいデータが追加された場合に範囲を自動的に拡張する必要がある場合などです。
この記事では、条件付き書式範囲を自動的に変更する方法について説明します。
過去に、OFFSET関数に関連する記事を複数掲載しています。
『【OFFSET関数】本当の理解を目指して』では、
OFFSET関数の原理原則を、
『【OFFSET関数】SUM関数を組み合わせた動的な範囲指定 -1-』では、
「1.SUM関数を組み合わせた動的な範囲指定に向けた仕込み」、
『【OFFSET関数】SUM関数を組み合わせた動的な範囲指定 -2-』では、
「2. SUM関数を組み合わせた動的な範囲指定を実施」
に焦点を当てました。
主に、次のようなシーンで役立ちます。
投稿を編集 “【OFFSET関数】本当の理解を目指して” ‹ データ分析ドットコム — WordPress
- 【条件付き書式】セル範囲を可変しながら、条件付き書式を適用できる
- 【参照範囲】セル範囲を可変しながら、セル値の取得やグラフを作成することができる
- 【計算】セル範囲を可変しながら、合計値や平均値を計算できる
本記事では、「【条件付き書式】セル範囲を可変しながら、条件付き書式を適用できる」を解説します。
なお、OFFSET関数に関連する記事は次の通りです。
- 【OFFSET関数】本当の理解を目指して
- 【OFFSET関数】SUM関数を組み合わせた動的な範囲指定 -1-
- 【OFFSET関数】SUM関数を組み合わせた動的な範囲指定 -2-
- 【OFFSET関数】グラフ範囲を自動的に変更する方法
目次
例題
サンプルデータ
以下のようなサンプルデータを考えます。
A列には日付、B列には売上が入っています。
このデータに条件付き書式を適用して、データを追加または削除しても、
売上の最大値が入力されたセルの背景色が自動的に変わるように設定します。
解法
大まかな流れ
- COUNTA関数を使用して、データ数を調べる
- 調べたデータ数を、OFFSET関数の引数に渡し、データ数分だけ、行数と列数だけ移動し、データを取得
- 取得したデータの中から、MAX関数で最大値を取得
- 上記3ステップの数式化し、条件付き書式の範囲として設定する
COUNTA関数でデータ数を調べ、OFFSET関数でデータ数分だけのデータを取得し、
取得したデータの中から最大値を探索します。
なぜ、COUNTA関数がデータ数を調べることが可能かに関しても、触れておきます。
COUNTA関数は、指定された範囲内にある空でないセルの数を数える関数です。
この空でないセルの数は、データが入力されたセルの数を表します。
つまり、COUNTA関数を用いることで、データ数を調べることができるわけです。
平たく言うと、
「指定された範囲にある空でないセルの数 ⇒ データが入力された範囲のセル数 ⇒ データ数」
ということです。
各ステップに関して
ステップ1
1.[ホーム] → 2.[条件付き書式] → 3.[新しいルール]の順を押す
ステップ2
1.次の関数式を入力
=B1=MAX(OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)) 
2.[書式]を押し、好きな背景色に設定
B1セルだけでなく、B1セル以外にも適用させる必要があります。
次のステップ3で設定を実施します。
ステップ3
次の関数式を入力します。
条件付き書式の適用範囲を設定します。
ここでは、B列全体のため、次の関数式のようになります。
=$B:$B 
上記の3ステップにより、条件付き書式の範囲を自動的に変更することができます。
参考記事
COUNTA関数以外の代替案
動的なグラフの作成方法に関して、COUNTA関数を紹介しましたが、代替手段が他にもあります。
簡単なまとめとなります。
なお、1つ目が一番簡単ですが、他の作業者がテーブル解除する恐れがあります。
- データ範囲をテーブルに変更することで、グラフの参照範囲を可変する
- MATCH関数を使ってもグラフのデータ範囲をテーブルに変更する
最後に
Excelで条件付き書式範囲を自動的に変更する方法について、解説してきました。
自動的に範囲が変更できれば、様々なメリットを享受できると思います。
この他にもたくさんの関数が存在します。表計算ソフトには様々な関数が用意されており、
それらをうまく活用することで、日々の業務や生活に役立てることができます。
今後もExcelやGoogleスプレッドシートなどを使って、便利な機能や使い方を学んでいきましょう。
解答付きサンプルデータ
参考記事
上記記事の読みやすさをさらに向上させるために、Excel関数に関する原理原則についてまとめた記事を紹介します。
Excel関数を学ぶ前に
Excelほど、馴染みがあり、広く浸透しているツールもないでしょう。 さらに、Excel関数はExcel作業を便利するパッケージであり、習得することで、大きな進歩につながりま…
Excel関数の種類は大まかに知っておこう
冷静に、スムーズに仕事ができるため、Excel関数の種類を大まかに知っておくことが大切です。 Excel 2019では、Excel関数が全486関数もありますが、Excel機能を上手に活用…
Excel関数にはそれぞれ構文がある
Excel関数には構文があります。それが、引数と戻り値です。構文に沿えばいいだけです。言わば、数学の公式のようなものです。理論を知らなくても、公式に当てはめれば答え…
Excel関数の引数とは
Excelの引数は、料理を完成させるための材料のようなものです。しかも、材料に種類があります。その種類を理解すると、Excel関数を上手く使いこなせるようになります。
Excelの検索ダイアログから、関数を探す
Excelの検索ダイアログに使えば、目的の関数を発見しやすくなります。WEB検索する前に、Excel関数の検索ダイアログを使ってみましょう。
