Excelの顧客リストを条件付き書式で色付けし、XLOOKUP関数でデータを抽出してみたぁ～！！

名前検索で本人データに色付けと抽出!!
会社名・部署から年齢まで表示させる!?

色付けは条件付き書式…と関数やな…!?

　左の画像のように、お得意先のExcel名簿があって、検索窓に担当者の名前を入力したら、名簿上のその人の氏名・会社名・所属部署等にも指定した色が付き・・・検索窓の下に会社名や所属部署等が表示されるようにしたい・・・

　ふとした思い付きだけでやってみようとしただけなので、この試みが実際の現場で実用性があるのか解りません。
　でも、条件付き書式やExcel関数を近年、実務で使用することが無いため備忘録としてやってみようと思います。 　今日は、私が行った方法を書いて置こうと思います。

• 【検索データを条件付き書式で検索する方法】
  
  1. 
     1. 先ず、お得意先の『名簿全体』を選択します。
     2. 次に、リボンの『ホーム』タブを選択します。
     3. そして、開いたメニューのスタイル項目の『条件付き書式』をクリックします。
     4. さらに、開いたメニューの中から『新しいルール』をクリックします。
     
     
     
  2. 
     1. すると、新しい書式ルールダイアログが開きますのでルールの種類を選択をして下さい項目の一番下の『数式を使用して、書式設定するセルを決定』を選択します。
     2. 次に、ルールの内容を編集してください項目の次の数式を満たす場合に値を書式設定の入力欄にCOUNTIF関数で、今回の場合、検索欄の検索結果に表示されるセルG2:セルG5を絶対参照で範囲指定して、選択している顧客名簿のセルA2を検索条件と逆指定し『=COUNTIF($G$2:$G$5,A2)』と入力します。
     3. そして、『書式』ボタンをクリックします。
     
     
     
  3. 
     1. セルの書式設定ダイアログが開いたら『好みの色』を選択します。
     2. 次に『OK』ボタンをクリックして閉じます。
     
     
     
  4. 
     • すると、新しい書式ルールダイアログに戻りますので『OK』ボタンをクリックして閉じます。
     
     
     
  5. 
     • これで、セルG2の検索入力欄に例えば部署の『営業』と入力すれば名簿の部署欄の該当部分に色が付きます。
       ※ 同じように氏名なら氏名欄、年齢なら年齢欄、会社名なら会社名欄に色が付きます。
     
     
     
  条件付き書式設定は以上、以降は下のセルにXLOOKUP関数で会社名・部署・年齢を表示させる方法・・・
• 【検索氏名から会社名を表示】
  
  1. 
     1. セルG3に会社名を表示させたいのでXLOOKUP関数で検索値はセルG2なので『=XLOOKUP($G$2,』と入力します。
     2. 検索範囲は氏名なのでA列のセルA2:セルA31を指定し『=XLOOKUP($G$2,$A$2:$A$31,』と入力します。
     3. 戻り範囲は会社名なのでセルC2:C31を指定し『=XLOOKUP($G$2,$A$2:$A$31,$C$2:$C$31,』と入力します。
     4. 見つからない場合は空白にしたいので半角のダブルクォーテーションを二つ「""」を入力して半角の閉じカッコ「)」で閉じ『=XLOOKUP($G$2,$A$2:$A$31,$C$2:$C$31,"")』と入力します。
     5. そして『Enter』で確定します。
     
     
     
• 【検索氏名から部署名を表示】
  
  1. 
     1. セルG4に部署名を表示させたいのでXLOOKUP関数で検索値はセルG2なので『=XLOOKUP($G$2,』と入力します。
     2. 検索範囲は氏名なのでA列のセルA2:セルA31を指定し『=XLOOKUP($G$2,$A$2:$A$31,』と入力します。
     3. 戻り範囲は部署名なのでセルD2:D31を指定し『=XLOOKUP($G$2,$A$2:$A$31,$D$2:$D$31,』と入力します。
     4. 見つからない場合は空白にしたいので半角のダブルクォーテーションを二つ「""」を入力して半角の閉じカッコ「)」で閉じ『=XLOOKUP($G$2,$A$2:$A$31,$C$2:$C$31,"")』と入力します。
     5. そして『Enter』で確定します。
     
     
     
• 【検索氏名から年齢を表示】
  
  1. 
     1. セルG5に年齢を表示させたいのでXLOOKUP関数で検索値はセルG2なので『=XLOOKUP($G$2,』と入力します。
     2. 検索範囲は氏名なのでA列のセルA2:セルA31を指定し『=XLOOKUP($G$2,$A$2:$A$31,』と入力します。
     3. 戻り範囲は年齢なのでセルB2:B31を指定し『=XLOOKUP($G$2,$A$2:$A$31,$B$2:$B$31,』と入力します。
     4. 見つからない場合は空白にしたいので半角のダブルクォーテーションを二つ「""」を入力して半角の閉じカッコ「)」で閉じ『=XLOOKUP($G$2,$A$2:$A$31,$C$2:$C$31,"")』と入力します。
     5. そして『Enter』で確定します。
  
  上記の設定が終了すると・・・
• 【検索名の名簿の色付け＆データ抽出】
  
  1. 
     • これで、セルG2に担当者名を入力すればセルG3に会社名、セルG4に部署、セルG5に年齢が表示されて、顧客データ一覧の氏名・年齢・会社名・部署の欄に色を付けることが出来ました。
  
  

　今日のポイントは裏技的な条件付き書式の部分のCOUNTIF関数の指定の方法でした。
　このやり方を何かに応用して頂けたら幸いです。