社内のActive Directory（AD）内のユーザーについて、セキュリティ及び監査要件の対応(SOX)の為に、退職し一定期間経過したアカウントについてはAD上から定期的に削除を行うようにしているのですが、この処理について漏れ(有効になったままのユーザーが居ないかどうか)を確認するため、人事が更新している入退職を含む異動のリストとAD内のユーザーアカウントのステータスを照合し、削除対象かどうかを判別できるリストを作成しました。
もし職場で同じような処理を行う必要がある方がいれば参考にしてください。
リストの内容
- A列：ADから読み込んだユーザーID
- B列：人事のリスト(Sheet2)内にあるユーザーID（ADと同じ情報）との照合結果（照合するものがない場合はリストに無いと表示させる）
- C列：Sheet2にある対象ユーザーIDの退職日を参照
- D列：削除が必要なアカウントかどうか（削除が必要になる日数が経過しているかの確認）経過なら削除、未経過なら削除不要
完成した状態のリスト
処理手順
- まずA列にADから読み込んだユーザーIDを書き出します。Excelの「データ」タブから「データの取得」→「その他のデータベース」でActive DirectoryをPower Queryで開けるので、Power Queryで開くと更新が楽なのでおすすめです。 ※Power Queryを使ってADのユーザーIDを読み込む方法についてはこちら
PowerQueryを使ってActiveDirectoryからUserIDを書出し
- 次にB列にvlookupでSheet2のC列にあるユーザーIDとA列を照合します。
Sheet2のUserIDの位置
B列にvlookupでSheet2 C列にあるUserIDと照合した結果を書出し
B列の関数（VLOOKUP＝特定のシート内の特定の列の値と値が一致するか確認)=VLOOKUP(照合先の値,照合元の値があるシート!その列:その列,1,FALSE)今回の例：=VLOOKUP(A2,Sheet2!C:C,1,FALSE) - 次にC列に退職日を表示させます。退職日はSheet2の中から取得しますが、同じ列に複数のIDがあるので、一番下の行にある値を取得するように関数を書きます。
Sheet2の日時列の位置。ここから退職日(=同じユーザーIDの中の一番下の行の値）を取得します
C列にSheet2の日時列から、複数の同じIDがあるユーザーID列の一番下の行の値(退職日)を取得
C列の関数=IF(COUNTIF(値取得元シート!ユーザーIDの列:ユーザーIDの列,照合するセル),INDEX(値取得元シート!インデックスで値を取得する列:インデックスの値を取得する列,MAX(IF(値取得元シート!$ユーザーIDの列$2:$ユーザーIDの列$9999=照合するセル,ROW(値取得元シート!$ユーザーIDの列$2:$ユーザーIDの列$9999)))),"値取得元シートに無し")今回の例：(※記事のタイトルの処理です。)=IF(COUNTIF(Sheet2!C:C,A2),INDEX(Sheet2!B:B,MAX(IF(Sheet2!$C$2:$C$9999=A2,ROW(Sheet2!$C$2:$C$9999)))),"Sheet2に無し") - 次にD列に退職日から規定の日数が経過しているかどうかを判別する関数を入力します。例では60日以上経過したアカウントを「経過」、60日未満のものを「未経過」としています。
確認日から規定の日数(例では60日)が経過したかどうかの判別
今回の例：=IF(TODAY()-C2>60,"経過","未経過") - 次にE列にAD上からの削除が必要かどうかを表示させる関数を入力します。削除が必要な場合は「要削除」削除しなくてよい場合は「不要」と表示させるようにしています。
AD上からの削除要否の表示
=IF(D2="経過","要削除","不要")
C列の処理は照合対象が変わった場合も、同じように照合対象が複数有る中の最下段の値を取得したい！といった場合に使いますことができると思います。同じような処理をしたいケースは少なくないと思うので、ご活用ください。
コメント