【無理なく再現】スプレッドシートで別シートの重複に色付けする方法

スプレッドシートで別シートの重複する値に色付けする方法を記してます。

既にマスタシートなどで設定した値があったとき、別シートに記載した値が重複していないか確認することができる便利な設定方法です。

スプレッドシートで別シートの重複値に自動で色付けする

以前にスプレッドシートを利用して

といった記事をご紹介しました(^^)

今回は応用編として、

• 同じファイルにある別シートの重複値を
• 自動で色付けする方法

について紹介します。

具体的に、

といった手順で設定を行います。

例として、

と設定しました↓

手順1. マスタとなるデータに名前付き範囲を設定

まずは以下のような値段のマスタデータがあるとします。

実際の値段マスタのデータ↓

このマスタデータに、名前付き範囲を設定していきます。

参考記事【名前付き範囲の使い方】プルダウンも自動更新！

自動ﾌﾟﾙﾀﾞｳﾝ!

スプレッドシート名前付き範囲の使い方！プルダウンも自動更新出来るよ！

スプレッドシートの 名前付き範囲 という機能をご存知でしょうか？ 実はこの機能、プルダウン範囲を追加→自動更新してくれる ...

続きを見る

今回は" 値段マスタ "のシートに、「値段マスタ」とデータ名を称して進めます。

マスタデータに名前付き範囲を設定する方法

最初に、別シートで重複値として認識させたいデータ範囲を選択します。

範囲を選択したら「データ ＞ 名前付き範囲」と選択すると、

画面右端に以下のような設定画面が表示されます。

「名前付けされた範囲1」の表記場所に、わかりやすい名前を設定します。

任意の名前を入力したら「完了」をクリックすることで、

マスターデータの範囲に名前を付与することができました。

手順2. 別シートに条件付き書式を設定する

次に同ファイル内・別シートに以下のようなデータがあるとします。

実際のペンの値段データ↓

このうち単価の列（F列）で、値段マスタと重複する値に条件付き書式で色付けをしていきます。

別シートの重複値に条件付き書式を設定する方法

まず重複値の確認対象となる範囲を選択します。

次に「表示形式 ＞ 条件付き書式」を選択します。

すると、スプレッドシートの画面右端に条件付き書式の設定画面が開きます。

セルの書式設定の条件を「カスタム数式」へ変更し、

以下数式を入力して、完了をクリックします。

 code >=countif(indirect("値段マスタ"),F2) 

これで別シートにある値が入力された時点で、すぐに値に色付けが行われます。

重要！他シートの参照はINDIRECT関数を使う

他シートの値を参照する場合、INDIRECT関数を利用します。

=INDIRECT(セル参照の文字列, [A1 形式にする])

参考：https://support.google.com/

今回は" 値段マスタ "シートの名前付き範囲である「値段マスタ」を、INDIRECT関数の参照文字列に指定しました。

 code
=countif(indirect("値段マスタ"),F2);

この設定がうまくできれば、簡単に別シートの重複値を条件付き書式で色付けしてあげることができます。

別の名前付き範囲を設定すれば違う色付けも可能

重複値に色付けするシートに、別の名前付き範囲を設定してあげることで値ごとに色を変更することもできます。

1. 別の名前付き範囲を追加する

例として、「値段マスタ」シートのマスタデータを以下のように追加します。

実際に追加した内容↓

次に追加した範囲を選択し、名前付き範囲の設定を行います。

今回は「値段マスタ2」と名称しました。

2. 別シートに条件付き書式を追加する

「ペン単価」シートを選択し、条件付き書式を追加します。

先に重複値の色付けする範囲を選択し、

「表示形式 ＞ 条件付き書式」を選択したら 「条件を追加」を選択。

カスタム数式を選択して、以下式を追加します。

追加する式は以下のとおりです。

 shortcode
=countif(indirect("値段マスタ2"),F2)

重複する色を値段マスタ1と別の色に設定すれば、

値段マスタ2の範囲に該当する値だけ、黄色にハッチングすることができました。

まとめ

スプレッドシートで別シートに重複値があったら色付けする方法について紹介しました。

うまく設定することはできましたか？

ポイントとしては、

• COUNTIF関数
• INDIRECT関数

といった2つの関数の組合わせを理解するだけで、比較的かんたんに再現することができます。

ぜひぜひトライしてみてください(^^)