クリーニング関連企業様 データチェックツール開発
クリーニング関連企業様の売り上げ報告データのチェックツールを開発。
概要
- 毎月15日締めで関連業者から1ヶ月の売上を入力したExcelブックがメールで送られて来る。
- 受信したそれらのメールは、受信トレイに追加した「2013年7月」等のフォルダに手作業で移動する。
- 添付されているExcelブックを1つのExcelブックにまとめるが、その前にシートの内容のチェックを行う。
- シートの内容に入力漏れやルールを無視した入力が目立ち、そのチェックをするためのツールを開発。
簡単な流れや問題点
- 毎月15日締めで月の中旬の後半に関連業者からExcelブックを添付したメールが送られてくる。
- メールはOutlookで受信する。(業者数は50件前後)
- 今までの流れ
- 受信したOutlookのメールに添付されているExcelブックを開き、データの誤りをチェック。
- 誤りなどがあった場合は修正。
- 添付のExcelブックを別名で保存。別名は企業KEY6桁+企業名+拡張子(.xls または .xlsx)
- 最後にすべてのExcelブックを1つのブックにまとめる。
- 問題点
- 添付されているExcelブックは1つの場合もあれば、業者によって複数の場合がある。
- 1つのExcelに、関連業者が管轄している物件の売上がシート毎に入力されている場合があり、また、Excelブック毎に1物件として、物件数分のExcelブックに分けている場合もある。
- 添付のExcelブックを開いて各シートをチェックするのに手間がかかる。
- 売上入力用として提供されているExcelブックは、シート内に物件番号、部屋№、品名コード、受付日、単価、数量、金額、対象期間等を入力する欄がある。
- 主な誤り(※以下のような誤りのチェックに時間がかかったり、見落としがある。)
- 未入力の項目がある。
- 最終行の次行以降に金額の合計が入力されていることがある。
- 本来はシートの入力エリアの横に、部屋番号・顧客名の一覧および品名コード・品名の一覧があるが、業者によっては顧客や品名の一覧を別シートにしてしまっているところもある。
- 売上が10行あったとしても同じ値が必ず入力される物件番号、対象期間等はあらかじめ余分にコピーして実際にあるべき明細行数よりはるかに多い場合や少ない場合がある。
- 品名や顧客名はそれぞれの一覧を参照する数式が入力されており、品名コードや部屋№を入力すると該当する品名や顧客名が表示されるが、数式が削除されている場合があったり、存在しない品名コードなどが入力されていることによる数式エラーになっていることがある。
問題点に対応するためのツールを作成
ツール概要

- [ 添付ファイル保存画面 ]
- 受信日の範囲、受信トレイ内のフォルダ名を指定し、それに合致するメールを一覧表示する。
- Excelブックは命名規定に沿った名前にする必要があるがそれを自動で行う。
- 業者マスタにメールアドレス、企業KEY、企業名をあらかじめ登録することで、今まで手作業で別名で保存していたExcelブックの名前を命名規則に沿った名前にする。
- 例)メールアドレス=tanabe@abc.com、企業KEY=100999、企業名=株式会社田辺 の場合
→ 100999_株式会社田辺.xls (拡張子は添付のExcelブックに合わせる。) - 「保存」ボタンクリックで、自動生成された名前で添付ファイルを保存する。
- データチェック画面
- ①シート名チェック
- 以下のようなシートはデータチェックで対象から除外する必要がある。そのために、ブック内のシートはデータチェック対象か対象外かを指定する画面を作成。
- 入力例が記載されているシート
- 業者が自分で作成した顧客一覧や品名一覧のシート
- 何も入力されていない空のシート
- 別の業務で使用する売上を集計した請求書シート
- この画面で対象のチェック「あり」のシートは、データチェック時にチェック対象とする。
- チェック「なし」のシートは、データチェックでチェック対象外とする。
- Excelブックの内容をこの場で確認できるように、各行をダブルクリックすることで該当のExcelブックを開く。
- ②データチェック
- シート名チェック後、「データチェック」ボタンクリックで対象のフォルダ(添付ファイル保存フォルダ)のExcelブックを一つずつチェックする。
- データチェックの結果をエラーリストとして出力する。
- 出力されたエラーリストを参考に、以下のうち、[ エラー ] が0件になるようにExcelを修正していく。(これは手作業)
- [エラー] (以下はエラーの一例)
- 物件番号がブランク
- 部屋番号がブランク
- 受付日が未来日
- 品名がブランク、「#N/A」等の数式エラー。
- 受付日がブランク、「20130.07.10」のように日付として評価できない値が入力されている。
- 数量、単価、金額に数字以外が入力されている。
- 数量×単価と金額が一致しない。
- [要チェック] (以下は要チェックの一例)
- 受付日が過去日(対象となる期間より前の日付)
- 品名コードがブランク(品名が正しければ、品名コードはブランクでも問題ない為)
- [自動修正] (以下は無条件で固定値をセットする項目の一例)
- 固定値の「0」をセットする項目。
- 企業KEYはそのブック名の先頭6桁の企業KEYをセット。
- 企業名はそのブック名の企業KEYの後の「_ 」の次から拡張子の前の企業名をセット。
以下は要チェックの一例。要チェックはデータチェック上問題ないと言えるが、必要に応じて修正することがあるもの。
開発環境等
- 開発言語
- Visual Basic 2010、Excel VBA、OutlookVBA
- DB
- Access
- ツール等
- Microsoft Visual Studio 2010