吃土的毎天

備忘録として

ファイル名からexcelデータの一致検索

Excelでレコードのある項目(IDや商品コード:以下ID)と一致する名前のファイルが特定のフォルダに含まれているかどうかを調べる手順。windowsで。

用意するもの

前処理

  1. Flexible Renamerを使ってファイル名を検索したいIDのみとなるようにリネームする。
  2. 「数字や文字列の削除」から「先頭からn文字目より後を削除」など。
  3. もし重複するようであれば連番を先頭につけて、後から連番ファイルのみexplorerで削除。
  4. フォルダのパスをコピー。
  5. フォルダを「Shift」キーを押しながら右クリック。→表示されたメニューから「コマンドウィンドウをここで開く」をクリック。→開いた「コマンドプロンプト」の画面の「>」あとに「dir /b >name.txt」と入力し、「Enter」キーを押す(「name.txt」の「name」は任意)(参考)ことで、ファイルを含むフォルダのファイル名一覧を適当な場所にテキストファイルに吐き出し。
  6. 作成されたtxtファイルをWordで開いて、検索と置換を用いて日付などID以外の情報を全て削除。1行に1つのIDのみが書かれている状態にしてtxtで保存。

Excel操作

  1. Excelのデータ一覧(ここではシート1とする)とは別のシート(ここではシート2とする)に、先程作ったtxtからID一覧をA列にコピー。
  2. シート2のB列全行に"1"と入力。
  3. シート1の調べたいIDがA列、1行目は見出しとして、B1にデータ有無と記載。
  4. シート1B2に「=VLOOKUP(A2,Sheet2!$A$2:$B$○○,2,FALSE)」と入力(○○はシート2のID列の行数。2はB列を指す。falseは完全一致のこと:参考
  5. B2からB最終行までオートフィル。
  6. データ有は"1"、データ無は"#N/A"で表される。
  7. もし#N/Aが鬱陶しかったら「=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$2:$B$○○,2,FALSE)),"",VLOOKUP(A2,Sheet2!$A$2:$B$○○,2,FALSE))」で空欄になる。(参考