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

逆引きVLOOKUP②(+フィルタでCC名簿一発作成(より詳しく)

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

    逆引きVLOOKUP①抜粋
    VLOOKUP(XLOOKUP
    )といえば
    あるキーを元に、関連情報を引っ張ってくるイメージで
    「社員番号を入れたら氏名表示」が「商品コードを入れたら商品名表示」といった使い方が一般的ですが、視点を変えて、
    結果側(検索先)に注目して、そこからキーを絞り込みたいというケースを私は逆引きVLOOKUPと名付けています。
    例:「この人をメールCC(上司等)に入れるべき宛先はどれ?」
      「この人に関係する案件、どれだっけ?」
      「この製品を使っている部署は?」

    逆引きVLOOKUP①では、以下の例を挙げて、
    「課がなくて部長のみのケースや、上司が兼務だったり、と
    もう少し注意するところがあるので、次回以降に詳しく説明します。」
    と記載しました。

    画像

    そこで以下の手順で「仕込み」について説明していきます。
    結構手間がかかりますが、一度作ってしまえば、その後の修正はかなり楽です。
    また、兼務がなく、次課長宛・部長宛の区別も不要であれば、仕込みも少しラクです。

    実際のリストイメージ

    最終リスト

    画像
    CC宛名リスト
    画像
    宛先貼付シート

    最終シート

    「ここに都度貼り付ける→」シートは、「空のシート」で案内のみ記載しています。

    画像

    状況

    以下の通りです。部長が次長を兼務したり、次長が複数のチームを兼務しています。

    画像

    初回仕込手順:下準備

    「CC宛名リスト」の初回の仕込みには結構手間がかかります。

    下準備1:CC宛名リスト・宛先貼付シートの箱を作る

    宛先貼付シートで、使用者が都度貼るところは、
    薄い黄色にしておくとわかりやすい。

    画像
    CC宛名リスト
    画像
    宛先貼付シート

    下準備2:「職位読替シート」( 次課長・部長パターン読替シート)を作る

    次課長パターン・部長パターンでパターン化できるようにしておく

    画像
    職位読替

    下準備3:「メールアドレス」シートを作る

    社員番号・メールアドレス・氏名(氏名は不要だがわかりやすさのため)
    (Outlookの仕組みによっては、メールアドレスでなくても
    ・社員番号を直接貼れたり
    ・氏名/所属名をセットできたりするので設定不要の場合もある)

    画像
    メールアドレス


    初回手順

    手順1:CC宛名リスト貼り付け

    普段使っている全体の社員名簿から、部長・次課長名簿をフィルタ抽出して、「CC宛名リスト」に貼り付ける(所属コード~氏名まで)

    画像
    CC宛名リスト


    手順2:兼務の場合は、2行データを作る

    VLOOKUPで参照するため、兼務分の行を作成します。
    この手順も、別の回で詳しく説明するつもりです。

    ※兼務で一人2行以上にしている分について、わかりやすくするため、いったん赤太字にしています。(実際は強調はしません)

    画像
    CC宛名リスト

    手順3:次課長パターン・部長パターンのVLOOKUP(XLOOKUP)式を記載

    ・「職位列」から、「職位読替」シートの次課長パターン・部長パターンを呼び出します。
    ・IF条件をつけて、「0」表示を回避します。(結果が0ならブランク""にする)
    この後の処理で、結果列を使うため、「0」が残らないようにする。
    (0表示は、オプションの「0表示なし」で消せるが、そうではなく実質「ブランク」にする必要あり。)

    次課長パターン列:
    =IF(VLOOKUP($H46,職位読替!$A:$B,3,0)=0,"",VLOOKUP($H46,職位読替!$A:$B,3,0))
    部長パターン列:
    =IF(VLOOKUP($H46,職位読替!$A:$B,2,0)=0,"",VLOOKUP($H46,職位読替!$A:$B,2,0))

    画像
    CC宛名リスト
    画像
    職位読替

    手順4:メールアドレスをVLOOKUP

    画像
    CC宛名リスト

    手順5:次課長フラグ・部長フラグ列

    次課長フラグ列(部長列も同様)
    =IF(K29="","",IF(ISERROR(VLOOKUP($G29,対象名簿!$R:$R,1,0))=FALSE,1,""))
    ・次課長パターン列がブランクならブランク("")
    ・VLOOKUPで所属名列を参照して見つかったら「1」を立てる
     (詳細説明)XLOOKUPでもCOUNTIFでも対応できる
     ISERROR関数で、VLOOKUP結果を確認して、
     エラーでなければ(FALSE)[1]を立て、そうでなければブランク("")

    ※次課長・部長のパターンが無い場合は、「=IF(K29="","",」の部分が無くなるため少し楽になります。

    =IF(K29="","",の後
    ■XLOOKUP
    =IF(ISERROR(XLOOKUP($G30,対象名簿!$R:$R,$R:$R))=FALSE,1,""))
    ■COUNTIF
    =IF(COUNTIF(対象名簿!R:R,G31)>0,1,""))

    画像
    CC宛名リスト
    画像
    宛先貼付

    出来あがりイメージ

    画像

    CC宛名リスト

    シート一覧

    画像

    ※「ここに都度貼り付ける→」シートは、「空のシート」で案内のみ記載しています。

    手順6:宛先貼付シートのメールアドレス欄もVLOOKUPで呼び出し記載する。


    CC宛名シートと異なり、行数がわからないので、エラー対応も含めて記載する。(IFERRORやXLOOKUPで該当なしをブランクにする)
    =IFERROR(VLOOKUP(S70,$AN:$AP,3,0),"")

    画像

    手順7:ファイルを読み取り専用にする

    せっかく作ったCC宛名リストに上書きされないよう、上書き専用にする。

    初回作成以降


    初回作成後は、CC宛名リストのメンテナンスをする
    人事異動があったとき
    ・少しなら、特定の行だけ直す。
    ・大きな異動なら、社員名簿からの貼り付け部分を、すべて洗い替える。
    ・メールアドレスでエラーが出ていたら、メールアドレスシートに追加する。

    画像


    outlookへの貼り付け方

    フィルタをかけて、メールアドレス列を選択+コピー

    画像

    Outlookメールに貼り付け

    Excelのフィルタ結果の列をそのまま、宛先やCCに貼れます。
    最後に「名前の確認」をクリックすると、社内メールアドレスに変換表示されます。(社内で該当なしだと、変換されず、メアドは半角英数字のまま)

    画像
    画像

    Outlookの送信は色々と便利な機能があります。記事にしました ↓
    Outlookでのメール送信 ─ Gmailとの違いと便利機能

    留意事項


    補足1

    今回の場合、一括でメールを送るので、
    「誰を対象にしている」かわかるように、メール本文に対象者名簿をつける必要があります。(メール宛先を見てもらえばいいのですが、忙しい上司たちはそんなのできないよ、と言いがちです。)

    一人ずつ異なるメールを送る場合

    では、一人ずつにCCで送るにはどうすれば良いでしょうか
    この場合は、逆引きVLOOKUPではなく、
    宛名シート側で、通常のVLOOKUP関数で宛名作成はできます。
    ただし、1行ずつ宛名一覧を作ったあとは、チマチマと、メールを1通ずつ作る羽目になります。
    これを一括で送るのは、ExcelVBAでOutlookを作成する必要があります。
    難しそうですが(そして以前は難しかったのですが)、生成AIを使えば比較的簡単に、Outlook自動送信マクロ(VBA)を作ることができます。
    こちらも今後VBAの説明の際に案内していきます。

    補足2

    もう一つ面倒なのが、兼務の人も含めた縦型リストを作ることです。
    VBAで対応する方法もありますが、
    比較的簡単なのはpowerQuery を使って以下の手順で処理します。
    ①ピボットの解除で、横の表示を縦にして、
    ②属性を分離する。(所属1→「1,所属」、職位1→「1、職位」…
    ③分離した属性名(所属、職位・・・)でピボット
    ④ピボットした所属C等をグループ化
    これを準備すれば、繰り返し処理ができます。
    今後の回で説明していきます。
    PowerQueryピボット解除+再ピボットで横持ち表を縦にする(横持ち兼務複数リストを縦にする)

    補足3

    もし「〇室」が2パターン「部と同格」「部内室」のどちらのパターンもあれば、以下の「読み替え表」を作って対応します。
    まず、このリストを見て、該当がなければ、通常の職位読み替えを行うイメージです。

    画像




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

    あなたへのおすすめ