提出物チェックするExcelVBA

こんにちは、管理人のtsenです。

私は10年以上人事業務を担当しています。

人事業務では単純な繰り返し作業が多く、
業務効率化のために、自分が開発したExcelマクロ（VBA）を公開したところ

「うちの部署の業務でも使いたい！」
「是非Excelマクロを教えてほしい！」

と、多くの需要があることを知り、

より多くの人の役に立てるのではないかとこのサイトを立ち上げました。

現在では、社内で様々な業務効率化ツールの開発を手掛けています。

今回は、その中から、

Excel集計業務をしている人に役立つ「提出物を一瞬でチェックする方法」について解説します。

人事業務効率化！提出物を一瞬でチェックする方法
このページを読むと効率化できる業務
人事業務を効率化するための事前準備
1. シート名称の保護をかける
2. 提出物の名称と提出者の名称を仕込みましょう
エクセル(Excel)提出物を一瞬でチェックする方法
1. チェックシートを作成する
人事業務効率化お役立ちツール使用の注意点
人事業務効率化のお役立ちツールを無料公開中

人事業務効率化！提出物を一瞬でチェックする方法

人事業務によくある作業で

各部署や各職場などに、
「これを提出してくださいね」
と、
エクセル(Excel)のフォーマットなんかを
一斉にメール配信して、

そのエクセル(Excel)に記入してもらった後に
エクセル(Excel)を集めるっていう業務がありませんか？

いわゆる取りまとめ業務です。

この時に、

・どの部署が何を提出しているか調べたい！
・どこの部署が未提出なのか調べてリマインドを出したい！
・誰が何を提出したのか調べるだけで時間がかかってしょうがない！

という悩みをお持ちの方はいませんか？？

おそらく、普通の人は

職場からメールで提出があるたびに

「提出チェック」みたいなシートでチェック1つ1つ入れていたり

まとめてフォルダに入れておいて

ファイル名なんかを見ながら、提出者を手作業でチェック

しているのではないでしょうか？

これが、少数であれば問題ありませんが、

例えば５０職場とかあると、
確認するだけで時間がかかりますし、

ファイル名も様々な名称を付けて送ってくるので

誰が何を出しているか確認するだけでかなりの時間がかかってしまいます。

このページを読むと効率化できる業務

今回効率化する業務のイメージはこのようなものです。

人事業務を効率化するための事前準備

まず、提出してもらうエクセル(Excel)に事前準備を施す必要があります。

シート名称の保護をかける

まず、提出されるシート名称は「Sheet1」でないといけません。

そこで、シート名称を勝手に変更できないように保護をかけておきましょう。

保護のかけ方は、

校閲タブ＞ブックの保護＞パスワード設定

でかけられます。

提出物の名称と提出者の名称を仕込みましょう

後ほど自動チェックをするときに

キーとなる「提出物名称」と「提出部署名」を仕込んでおく必要があります。

今回は、B1セルに提出物名称、B2セルに提出者名称を仕込みます。

提出物名称は事務局があらかじめ入力しておきましょう。
部署名はブルダウン形式等にし、集計表と完全一致するようにしましょう。

ここでのポイントは、

このキーとなる「提出物名称」と「提出部署名」は

集計表と完全一致していなければいけません。

フリーワードで入力させるようにしてしまうと

略称など、こちらが意図していない名称で提出されるので

プルダウンで選べるようにしておきましょう。

もし、提出物の見た目を崩したくない！という方は

この1行目と2行目の仕込み部分を
非表示にしておいてもOKです。

その場合は、部署名を別の場所で選択させるようにして、

B2セルへリンクさせるようすればOKです。

このように仕込んでおいて、1行目と2行目の仕込み部分は

非表示にしておけば、申請書自体の見た目を損なうことなく仕込むことが出来ます。

エクセル(Excel)提出物を一瞬でチェックする方法

チェックシートを作成する

提出物のほうへの仕込みが終わったら

次はチェックシートを作っておきます。

チェックシートはSheet2に表を作ってください。

シート１へは、マクロボタンを設置して

以下のコードを入力します。

マクロの入力方法やボタンの作り方が分からない人は

まず初めに、こちらを読んでくださいね。

Excelマクロ超初心者向け！マクロの基本操作のすべて

初めてマクロを操作する方向けにマクロを始めるための事前準備・マクロコードを挿入する方法・マクロ形式で保存する方法・マクロの基本的な実行方法・マクロボタンを使う方法など一連の基本的な操作方法がすべて全て出来るようになります。文系出身でも「１からマクロを勉強して実務に活用できるようにする」をコンセプトにしたブログです。

Sub 提出物チェックツール()
    Dim InpFile As String
    Dim WhatC As String
    Dim WhatR As String
    Dim FindC As Range
    Dim FindR As Range

    Sheets("Sheet2").Select
    InpFile = Dir(ThisWorkbook.Path & "\*.xlsx")
    Application.ScreenUpdating = False

    While InpFile > ""
        WhatR = "'" & ThisWorkbook.Path & "\[" & InpFile & "]Sheet1'!"
        WhatC = ExecuteExcel4Macro(WhatR & "R1C2") 'B1
        WhatR = ExecuteExcel4Macro(WhatR & "R2C2") 'B2
        Set FindC = [1:1].Find(WhatC, LookAt:=xlWhole)
        Set FindR = [A:A].Find(WhatR, LookAt:=xlWhole)

        If Not (FindC Is Nothing Or FindR Is Nothing) Then
            On Error Resume Next
            Name ThisWorkbook.Path & "\" & InpFile As _
                ThisWorkbook.Path & "\" & WhatC & "_" & WhatR & "_集計済.xlsx"

            If Err = 0 Then
                Cells(FindR.Row, FindC.Column) = "○"
            End If
            On Error GoTo 0
        End If
        InpFile = Dir
    Wend
End Sub

そしたら、このマクロブックと

提出物のすべてのファイルを同じフォルダへ格納した状態で

マクロを実行してみてください。

tSen

うまくいきましたか？
うまく集計できていれば、提出物のエクセル名称が
「提出物名‗提出者名‗集計済.xlsx」
というファイル名に代わっているはずです。

もし、変わっていなければ、ファイル内の仕込みがうまくいっていない可能性があるので、中身を確認してみましょう。

人事業務効率化お役立ちツール使用の注意点

今回のマクロを使う時の注意点は、これまでに書いてきたように、

・B1セルに提出物名称、B2セルに提出者名称を仕込む
・仕込んだ名称と、チェックシートの名称が完全一致するようにする
・提出されるシート名は「Sheet1」から変更できないようにしておく
・提出物のすべてのファイルとチェックシートを同じフォルダへ格納する

が必要です。

人事業務効率化のお役立ちツールを無料公開中

今回は、今回は、人事業務効率化お役立ちツールとして

エクセル(Excel)提出物を一瞬でチェックする方法について

具体的な方法を解説してきました。

このサイトでは、人事業務の効率化に役立つツールをたくさん紹介しています。

主にExcelマクロを使った無料で導入できるツールになりますので是非取り入れてみてください。