色分けしてしまった表で関数を使えるようにする書式置換のテクニック

2017.11.29基礎・入門文字列操作書式その他

一覧表で「済み」となった案件は、セルに色をつけて「まだ済んでいないもの」と区別している場合があります。

そのようなセルに色をつけて区別している一覧表の判別は、関数では判別できないことに後から気づいて困ることがあります。
Excelは一部の例を除いてセルの色を計算式で読み出すことができないのです。
今回はExcelの置換機能を応用して、色分けで管理されている進行状況を計算式で判定できるように、色の情報から進行状況の値に変換する方法を紹介します。

実際に操作するファイルはこちらからダウンロードできます。

色分けしたセルをExcelで判別する

ダウンロードしたファイルをご覧ください。

元の表

 

5行目から、案件ごとの日付と得意先、見積額、請求額が記録されています。
一覧表は進捗状況が一目でわかるように色分けされていて、見積済みが黄色、請求済みが赤、入金済みが青で塗りつぶされています。

実は、Excelは唯一、フィルター機能を設定した場合のみ、色分けを判別できます。
A5から始まる表をクリックして、[データ]タブの[フィルター]を設定します。

フィルター設定

 

設定したら、フィルターの三角をクリックして[色フィルター]を開いてみましょう。

色分けフィルター

 

一覧表に設定されている色がリストされ、色でフィルターすることができます。
[色で並べ替え]でも色は判別され、同じ色の行がひとまとめになります。
Excelではこれ以外では色を判別しません。

計算式でどの色の進捗状況か把握することができないため、自動的に何かを管理する仕組みを作成しにくくなっています。

次の作業のためにフィルターを解除しましょう。

色分けを値に変換する

関数や数式で判別するためには、色ではなく、値で進捗状況を記録しておく必要があります。
つまり、色をセルの値に変換すればよいのです。
色が付いているセルを値に変換するのは、[ホーム]タブの[置換]です。
普通の置換ではなくオプションを使います。

はじめに、色のついているE列をコピーし、F列に貼り付けます。E列ではなくとも色が付いている列であれば構いません。

コピー

 

F列を選択して、[ホーム]タブの[置換]をクリックします。

置換

 

置換ダイアログボックスが表示されますので、[オプション]ボタンをクリックします。

オプションボタン

 

[検索する文字列]のテキストボックスには何も入れず、[書式]ボタンの下向き三角をクリックします。
[セルから書式選択]をクリックし、セルB1をクリックします。

書式の設定

 

すると、文字列は何でもよく、書式が黄色のセルが検索対象になります。

書式セット

次に、[置換後の文字列]のテキストボックスに「見積書発行」と入力します。

置換文字列セット

 

[すべて置換]のボタンをクリックします。

置換後

 

F列の黄色のセルがすべて「見積書発行」となりました。
これで、色を値に変換することができました。
では同様に、F列のセルで、B2と同じ色で塗りつぶしたセルを「請求書発行」、セルB3と同じ色で塗りつぶしたセルを「入金済み」に変換しましょう。

すべて変更済み

 

これで、進捗状況がセルの値になったので、関数などで管理することができるようになりました。

書式の置き換えがうまくできないとき

慣れていないと書式の置換は少し手順が多く、難しく感じるかもしれません。
うまくできない場合のよくあるケースを紹介します。

  • [検索する文字列]に書式を設定するつもりが[置換後の文字列]に書式を設定している。またはその逆。
  • [置換後の文字列]に検索文字を設定するつもりが[置換後の文字列]に検索文字を設定している。またはその逆。

このようなケースに対処するには、置換ダイアログボックスを初期状態に戻すのが一番です。

初期状態に戻すには、[検索する文字列][置換後の文字列]ともに文字を消し、どちらの[書式]ボタンも下向き三角から[書式検索のクリア]をします。

置換ダイアログボックスのリセット

 

こうすることで、置換ダイアログボックスは初期状態になります。
この状態から再度置換の設定をしましょう。

検索や置換する書式を任意に指定したい

すでにセルに設定している書式を使うのではなく、一つ一つ設定するには、[検索する文字列]または[置換後の文字列]の[書式]ボタンの下向き三角をクリックし、[書式]をクリックすることで[セルの書式設定]と同じダイアログボックスを表示できるのでここで設定します。

書式の任意設定

検索する文字や置換後の文字も元のセルからそのまま反映したい

今回のようなケースでは、文字も手入力するよりコピーした方が正確です。
置換ダイアログボックスは閉じずに他の作業ができますので、セルA1をコピーして、[検索する文字列]のテキストボックスをクリックして右クリックから貼り付けすることで、セルA1の内容を検索する文字とすることができます。
ただし、セルA1を選択した時点で、はじめの範囲選択は解除されるので、ダイアログボックスにコピーした後に再度範囲選択をします。

まとめ

今回は色分けで管理している一覧表に、関数で扱うための値を入れる方法について解説しました。

何よりも、色分けで管理するのは、Excelで管理する以上あまりいい方法ではありません。
進捗状況は、きちんと進捗状況欄を設け、その値によって色が自動的につくように、条件付き書式を指定するのが理想です。

書式で置換の方法は、今回のようなケースに限らず、他の使い方でも有効です。
置換ダイアログボックスでは、検索する文字列と置き換え後の文字列どちらにも文字を入れなければ、文字は関係なく、書式同士の置換になります。
通貨形式の書式設定されているセルを一度に数値書式に変更することができます。
赤色のセルのみロックするということもできます。
このように応用範囲も広いので、書式での置換方法はExcelの便利なテクニックの一つなのです。

書式で置換は、条件付き書式で設定された書式では検索対象にできないので、注意が必要です。

Excel業務をもっと「ラク」にするなら

Excel業務を自動化したい!
Excelをデータベース化したい!

 

Back to top