メインコンテンツへスキップ
見出し画像

逆引きVLOOKUP①(+フィルタでCC名簿一発作成:出力したい結果に対応する元情報をセットする)

    長年Excelを触ってきた中で、画期的なアイディア!と勝手に思っている利用方法を案内します。
    その名も「逆引きVLOOKUP」です。(ChatGPTがつけてくれた名前です)
    VLOOKUP(XLOOKUP)といえば
    あるキー(社員番号や商品コードなど)を元に、関連情報を引っ張ってくる
    イメージです。

    たとえば、「社員番号を入れたら氏名表示」が「商品コードを入れたら商品名表示」といった使い方が一般的ですね。

    でも、視点を変えてみると、こんなニーズが生まれることはありませんか?

    「この人をメールCC(上司等)に入れるべき宛先はどれ?」
    「この人に関係する案件、どれだっけ?」
    「この製品を使っている部署は?」

    つまり、結果側(検索先)に注目して、そこからキーを絞り込みたいというケースです。
    このようなとき、私はこれを「逆引きVLOOKUP」と呼んでいます。(というか 呼び始めました)

    ※ネット検索して出てくる「VLOOKUPの逆引き」とは異なります。
    ネット検索して出てくるのは、キーの左側に取りたい値列がある場合で、XLOOKUPなら一発解決するやつですね。

    「ちょっと何言ってるか、わからない」と思いますので、イメージと例で説明します。

    ■通常のVLOOKUPとの違い

    画像

    ▼こんなイメージです。わかるでしょうか?

    画像

    ■逆引きの活用事例:督促メールでのCC展開

    例:社員宛に「督促メール」を送る場面です。
    ・宛先には該当の社員全員
    ・CCには該当者の上司を入れたい

    このとき、通常のVLOOKUPで「上司が誰か」を1人ずつ探すのは非効率です。
    逆に、「この人が属している所属」から、上司一覧を作成できるはずです。
    これで「対象者と所属を貼るだけ」で一括CC展開できる仕組みを作ることができます。


    ▼ Excelの例:逆引きでCC対象を抽出する

    以下のような2つの表を用意します。(1つのブックの二つのシート)

    画像

    一つ目:都度所属と氏名を貼り付けるシート
    二つ目:上司の名簿。どこかに「フィルタ用フラグ」列を作る。
       フラグ列は、所属が合致すれば、「1」を立てる式をセットする。
     =IF(ISERROR(VLOOKUP(L74,対象者貼り付け表!I:I,1,0))=TRUE,"",1)
     IFとISERRORとVLOOKUPを組み合わせます。(少し面倒ですが)
     ※実はこの事例ではCOUNTIFでも書けます。

    宛先一覧の名簿を貼ると、上司名簿にフラグ:1が立つので
    あっという間にCC対象者を呼び出せます。

    画像

    ■この表のいいところ

    • 関数を書かなくていい
      事前にフィルター列(=フラグ列)を用意しておけば、
      オートフィルタだけで抽出できる
       → VLOOKUP関数を毎回書く必要なし

    • 一対一ではなく一対多の関係に対応
       → 「同じ所属の部下が2人」というようなケースにも柔軟に
       対応できる
       → 他にも複数案件にまたがる関係者も可視化可能

    • 対象者を変えるだけで再利用可能
       → 対象者一覧を貼り替えるだけで、CCリストが変わる

    • シンプルでみんなで使える
       → 私が以前いた部署では、この「宛名テンプレート」を私が仕込んでおき、 他のメンバーはID所属だけ貼れば誰でも宛名が出せるようになっていました。(皆で、読み取り専用で使っていました)
      督促や個別案内作業が多い中で皆の必需品になっていました。

    ■実際には、もう少し詳細の設定が必要

    課がなくて部長のみのケースや、上司が兼務だったり、と
    もう少し注意するところがあるので、次回以降に、詳しく説明します。
     ↓↓
    逆引きVLOOKUP②(+フィルタでCC名簿一発作成(より詳しく))
    PowerQueryピボット解除+再ピボットで横持ち表を縦にする(横持ち兼務複数リストを縦にする)

    ■その他の逆引きVLOOKUPが活きるユースケース

    ChatGPTに聞いたところ、他にもいろいろ使えそうな案が出てきました。こちらも、次回以降にユースケースと事例を実際に案内していきます。

    ■補足■
    Excelとの長い付き合いの中で、独自のアイディアとして、
    自慢できるのは、以下の3つですが。そのうちの一つです。
    ■逆引きVLOOKUP【今回説明】
    ■COUNTIF等を使った計算式を一発で消す方法
     (同じシート内のSUM等は残する)
     別のブックにコピーして、リンクを解除するだけです。
     ▶▶第3章|Excel×ChatGPT ─どんどん聞いてみよう
     /あるある2|リンク式だけを“値に変えたい”
    ■VLOOKUPのキーを行・列複合で指定して、マトリックス表を作る
     【今後説明します】

     
     
    書くことで見る世界がほんの少し変わるエッセイが書けるといいなと思っています。 週3位アップしています(1年2ヶ月で200を超えました)。ビジネスやITの話は水曜中心で、それ以外はランダム。 50代後半でIT業界に転職して60歳を過ぎました。最近は在宅介護も始まりました。

    あなたへのおすすめ