広告

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

2019年11月17日

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

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

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

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

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

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

今回は応用編として、

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

について紹介します。

具体的に、

  1. マスタデータに名前付き範囲を設定する
  2. 別シートの重複値に条件付き書式を設定する

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

例として、

  • マスタデータは「値段マスタ」シート
  • 色付けするのは「ペン単価」シート

と設定しました↓

各シート名称
各シート名称のイメージ

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

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

値段マスタデータのサンプルテーブル
値段のマスターデータイメージ

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

No 値段1 値段2 値段3 値段4 値段5
1 ¥50 ¥60 ¥70 ¥80 ¥90
2 ¥51 ¥61 ¥71 ¥81 ¥91
3 ¥52 ¥62 ¥72 ¥82 ¥92
4 ¥53 ¥63 ¥73 ¥83 ¥93
5 ¥54 ¥64 ¥74 ¥84 ¥94
6 ¥55 ¥65 ¥75 ¥85 ¥95
7 ¥56 ¥66 ¥76 ¥86 ¥96
8 ¥57 ¥67 ¥77 ¥87 ¥97
9 ¥58 ¥68 ¥78 ¥88 ¥98
10 ¥59 ¥69 ¥79 ¥89 ¥99

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

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

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

続きを見る

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

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

名前付き範囲とは

範囲に名前を付与することで、何度も選択範囲を呼び出して利用することができます。

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

名前付き範囲を称するデータ範囲を選択
名前付き範囲のデータ範囲を選択

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

名前付き範囲を選択した画像
データ > 名前付き範囲を選択

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

名前付き範囲の設定画面イメージ
名前付き範囲の設定画面

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

名前付き範囲の設定画面
名前付き範囲に名前を付与

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

名前付き範囲に名前を設定して完了するイメージ
名前を付与 > 完了 をクリック

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

名前付き範囲に名前が設定された
任意のデータ範囲に名前が付与された

OYAKUN
OYAKUN
ここまでで、任意の範囲に名前を付与することができました!

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

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

ペンの管理データのマスタ
別シートの別のマスタデータ

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

No 商品名 カテゴリ 保存棚 管理番号 単価
1 黒ペン細 文房具 PEN棚1 01-001 ¥50
2 黒ペン中 文房具 PEN棚2 02-001 ¥70
3 黒ペン太 文房具 PEN棚3 03-001 ¥249
4 赤ペン細 文房具 PEN棚1 01-002 ¥90
5 赤ペン中 文房具 PEN棚2 02-002 ¥150
6 赤ペン太 文房具 PEN棚3 03-002 ¥248
7 青ペン細 文房具 PEN棚1 01-003 ¥91
8 青ペン中 文房具 PEN棚2 02-003 ¥151
9 青ペン太 文房具 PEN棚3 03-003 ¥249
10 青ペン極太 文房具 PEN棚4 04-001 ¥250

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

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

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

重複値の対象となる範囲を選択する
重複値の対象範囲を選択する

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

メニューから条件付き書式を選択する
表示形式 > 条件付き書式

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

条件付き書式の設定画面
条件付き書式の設定画面

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

条件付き書式にカスタム数式を設定した
条件付き書式にカスタム数式を設定した

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

 code >=countif(indirect("値段マスタ"),F2) 
OYAKUN
OYAKUN
" 値段マスタ "の部分は、先の章で指定した値段マスタ範囲に付与した範囲名称のことです。
カスタム数式を設定して完了をクリック
カスタム数式を設定して完了をクリック

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

別シートのセル重複値だけ色が変更された
別シートのセル重複値だけ色が変更された

あひる
あひる
これなら別シートに記載されている値かどうか、すぐに分かるね!

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

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

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

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

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

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

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

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

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

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

別のシートの重複値に違う色付けしたイメージ
別のシートの重複値に違う色付けしたイメージ

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

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

名前付き範囲を追加したイメージ
名前付き範囲を追加したイメージ

実際に追加した内容↓

値段6 値段7 値段8 値段9 値段10 値段11
¥100 ¥110 ¥120 ¥130 ¥140 ¥150
¥101 ¥111 ¥121 ¥131 ¥141 ¥151
¥102 ¥112 ¥122 ¥132 ¥142 ¥152
¥103 ¥113 ¥123 ¥133 ¥143 ¥153
¥104 ¥114 ¥124 ¥134 ¥144 ¥154
¥105 ¥115 ¥125 ¥135 ¥145 ¥155
¥106 ¥116 ¥126 ¥136 ¥146 ¥156
¥107 ¥117 ¥127 ¥137 ¥147 ¥157
¥108 ¥118 ¥128 ¥138 ¥148 ¥158
¥109 ¥119 ¥129 ¥139 ¥149 ¥159

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

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

名前付き範囲2を設定したイメージ
名前付き範囲2を設定したイメージ

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

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

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

範囲2つめに条件付き書式を設定する
範囲2つめに条件付き書式を設定する

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

条件を追加のイメージ
条件を追加のイメージ

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

範囲2にcountifを設定したイメージ
範囲2にcountifを設定したイメージ

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

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

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

値段マスタ2に該当するセルの色を変更する
値段マスタ2に該当するセルの色を変更する

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

値段マスタ2に該当するセルだけ色が変わった
値段マスタ2に該当するセルだけ色が変わった

まとめ

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

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

ポイントとしては、

  • COUNTIF関数
  • INDIRECT関数

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

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

OYAKUN
OYAKUN
別シートをマスタとして利用しているときに、非常に使い勝手が良いと思います♪