リストの文字列と一致したセルに自動で色を付ける方法

大勢のマネジメントをする中で、

「新人を分かりやすく色分けしたい」
「対象者に色を付けたい」

なんて風にExcelのシートに自動で色を付けられないかと思うことがあります。

1つの文字列と一致した場合なら、

条件付き書式⇒セルの強調表示ルール⇒文字列

と進んで任意の文字列を入れればOKですが、上の図の様に
「リスト内のどれかと一致したセルに色を付けたい」場合はどうすればいいか?

上に書いた方法の場合、1人ずつ名前を入れる(もしくは名前の書かれたセルを指定する)必要があります。
5人や10人なら面倒ですが同じ操作を人数分回繰り返せば出来なくはないです。

ただ、これが100人・1000人単位の場合、非効率過ぎて話になりません。

折角作るのだから人数や色分け種別の変更ごとに柔軟かつスムーズに変更できるようにしておきたい…。

 

そんな時に便利な方法がこちらです↓

 

その1.リストに名前を付ける(と分かりやすい)

まず、抽出したい名前(なり品名なり)のリストに分かりやすい名前を付けます。

  1. [Ctrl]+[F3]を押す
  2. 「名前の管理」というダイアログが出るので[新規作成]を押す
  3. 「名前」にそのリストの名前を入力する。今回は「新人」と入力します。
  4. 「参照範囲」にリストの範囲を選択し[OK]を押す

 

 

 

 

以上で名前が付きました。

 

その2.書式設定をする

ここからが本題の色付け設定です。
利用するのはやはりというか[条件付き書式設定]です。今回は「数式を使用して、書式設定するセルを決定」を使って設定していきます。

  1. 色分けしたい範囲を選択する。この時、どこを最初にクリックしたかを覚えていてください。今回はB4を最初にクリックしています。
  2. 選択状態のまま[条件付き書式]⇒[新しいルール]⇒[数式を使用して、書式設定するセルを決定]の順で進みます。
  3. 数式の入力欄に半角英数で『=COUNTIF(新人,B4)』と入力する。
    分かるとは思いますが、=COUNTIF(新人⇒リストの名前,B4⇒最初にクリックしたセル)です。
    リストの名前を付けなかった場合は「=COUNTIF($H$4:$H$8,B4)」の様に絶対参照で入力してもOKです。
  4. 書式設定から任意の色を設定して[OK]⇒[OK]

するとこの様に色分けされました。

同じ範囲に更に別のリストを書式設定することで何個でも色分け設定を行うことが出来ます。

今回作成した方法だとルールの管理もシンプルなので、色変更や範囲変更も楽々できます。
事前にリストに名前を付けたのは後でルールを変更するときに一目でわかるようにする為です。

 

操作は以上です。

補足としてはリスト自体も関数を使って特定の条件で自動抽出できるようにしておくと編集の手間が省けて便利ですね。
そのあたりはまた次回紹介できればと思います。

念のため今回使用したサンプルファイルも置いておくので気になった方はどうぞ~

Book1

 

 

 

 

 

Leave a Reply