Salesforceを触る機会が多くなってExcelの使い方忘れそうなのでメモ的に。

こういったリストがあった際に

フィルター→並び替え→１つ目の企業の範囲を選択＆コピー→別シートにペースト　

であったり

フィルター→検索条件をかける＆コピー→別シートにペースト

など行なっている方が多いと思います。
しかしVLOOKUP関数を使うと台紙にペーストするだけで自動的に各シートに振り分けできるようなツールを
作成することができます。一定のフォーマットで繰り返し作業を行う方にとってはかなり便利です。

作成したExcelファイルに貼り付けるだけでこんな感じで絞り込みができます。
今回はこれの作成方法に沿ってVLOOKUP関数の利便性について紹介がてら書きます。

目次
  1. VLOOKUP関数とは？
    1. 概要と形式
  2. 難点
      1. ①１列目（左端）の値しか検索できない
      2. ②検索列に同じ名前のセルが複数ある場合が厄介
  3. 実際に使えそうなものを作成してみた
    1. 同じ名前のセルを識別できるようにする
      1. 検索結果
      2. 検索用のセルを追加＋G列を数式化
    2. 会社名欄（K2セル）の結果も自動表示
    3. 完成

VLOOKUP関数とは？

まずは簡単にVLOOKUP関数がどのようなものかを書きます。

概要と形式

この関数は簡単にいうと、特定の値を検索して、検索範囲内の別の値を返す関数です。

例えば、商品を管理している表があれば「商品名で検索して値段を表示させる」といったことができます。

上記のイメージ。
特定のセルや単語で検索→範囲を指定→ヒットしたら同じ行の別の列で返す　といった流れです。

=VLOOKUP( 検索する単語orセル , 検索範囲 , ヒットした際に返す列番号 , 検索の一致条件 )

式はこの形式で書きます。

難点

この関数を使う際の難点は主に2つ

①１列目（左端）の値しか検索できない

検索の際には１列目の値を検索することになります。先ほどの画像で言うと、商品名の列の値しか検索できません。
つまり、最終的に表示させたいものは検索する列より右にある必要があります。

②検索列に同じ名前のセルが複数ある場合が厄介

タイトルの通りです。

最初にお見せした取引先のシートだと、取引先名の部分に同じ名前のセルが複数あると思います。
この状態で検索をかけた場合、１番最初のものしか検索結果として表示されません。後ほど記す内容で対処しますが、これがかなり厄介です。

この2点を対策した上で作成を行います。

実際に使えそうなものを作成してみた

それでは最初に紹介したシートを作成していきましょう。
早速ですが、先に最終形を見た方がわかりやすいと思うのでそれを載せます。

このような形になります。一番下「列番号」は見やすいように作成したものなので実際には作成しません。
ここまでに至る経緯を順を追って説明します。

同じ名前のセルを識別できるようにする

まずは難点②で紹介した「検索列に同じ名前のセルが複数ある場合が厄介」の対策をします。
今回の場合は1,2,3….と番号を振ることによって解決します。

オレンジ色の部分ですね。COUNTIF関数を使うことによって、番号を生成していきます。
COUNTIF関数とは指定した範囲に特定の値が何個あるか？を表示させる関数です。これを使います。

このように行に合わせて検索範囲を広げていきます。
4枚目の画像の範囲に達した時点で株式会社サンプルが2個になったので結果が2となりました。

この結果を出したC列と元の取引先名E列を組み合わせたのがD列です。これを検索値としてVLOOKUPを使います。

検索結果

別のシートにこういったものを作成します。このシートのG列を検索値としてA〜F列を結果として表示させるイメージですね。G列と先ほどのC列が連動します。

実際の中身はこんな感じですね。

ただし、このままでは毎回「株式会社A1」「株式会社A2」…と打たないといけないので不便です。

検索用のセルを追加＋G列を数式化

ということで以下の内容を増やします。

J2 , K2 のセルに会社名を入力する欄を追加しました。
これでK2に会社名を入力すると、G列に自動的に番号が降られた名前が表示されるようにできます。
（=$K$2&1,2,3…　という式になります。）

これで、K2に名前を打ったら勝手に検索されてA〜F行に結果が返されるようになりました。
このままでもいいのですが、もっと使いやすくしてみましょう。

会社名欄（K2セル）の結果も自動表示

タイトルの通り、K2セルも自動で出力されるようにしましょう。

=VLOOKUP(1,元シート!A2:D14,4,)

式としてはこのようになります。A列から数えて4列目を返す＝取引先名になりますね。
元シートというのは

これのことですね。A列はオレンジの部分です。このA列もCOUNTIF関数を使って作成しています。
ここでは1の数をカウントしていますが、1が出てくる＝取引先名に新しい値が出てきているということなのでこの原理を利用しているというわけです。

先ほどの検索結果シートの式　=VLOOKUP(1,元シート!A2:D14,4,)ではA列が1、つまり１つ目に出てきた会社しか表示できないので
検索結果を表示させるシートを複製します。その際に検索値を2,3,4…と変えていくことで表示される結果がシートによって変わります。

完成

これにて完成です！
あとは元の表を白の部分（D~I列）にペーストするだけで各シートに分類された結果が出ます。

最初だけ作るのが大変ですが、作ってしまえばずっと使える仕組みなのでぜひVLOOKUP関数の使い方の勉強がてら作成してみてはいかがでしょうか？

おしまい