(license)みなさん、こんにちは!タカハシ(@ntakahashi0505)です。エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。人が入力する場合は必ず人為的なミスが入り込みます。それ回避する方法として開いたブックからファイル名を取得して利用する方法をお伝えします。tonari-it.com2015.08.22みんなから集めたエクセルファイルのファイル名を使って、入力ミスを避けるテクニックについてお伝えしました。運用上ミスが減らせるようにマスタというものを準備します。いうことで、エクセルVBAでマクロを作るときに知っておきたいマスタデータのことについてお伝えしていきます。前回のおさらいマスタデータとは社員マスタを準備する部署マスタを準備するユーザーの入力とその影響は最小限に社内のマスタ管理の注意点まとめ連載目次:エクセルVBAで経費データをデータベースに集約する前回のおさらい毎月、社内の皆さんから受け取る経費精算書ですが、以下の部分は手入力なので、人為的なミスが混入する可能性がありました。そのミスを回避すべく、ファイル名から「対象月」を取得してしまおう、という作戦をとってコードを作成しました。Sub12,13行目で、以下の形式のファイル名から「YYYYMM」の部分を抽出して、それらから「対象月」の日付型データを生成しています。YYYYMM経費精算書_名前_XXXX.xlsxエクセルファイル内よりもファイル名のほうが入力ミスをしづらい、または入力ミスを発見しやすいということを期待できます。今回は、「XXXX」の社員Noの部分を利用して、部署No、部署、氏名について対策をとるべく準備を進めていきます。マスタデータとはまず、前準備として、現在マクロを書いているブックにシートを追加して「マスタデータ」を整備していきます。】マスタデータとは、企業内データベースなどで、業務を遂行する際の基礎情報となるデータのこと。それらを集約したファイルやデータベースのテーブルなど。「マスタ」と省略するのが一般的である。(引用:IT用語辞典)社員マスタを準備する例えば、以下ような「社員マスタ」を準備します。部署No部署は、おのずと決まってきます。それら社員Noに紐づくデータを「コチラ側」に正しい情報として持っておいて、それを参照をするのです。その役割を担う「コチラ側」のデータをマスタ、もしくはマスタデータと言うわけです。部署マスタを準備するさてもう一つ、今回は以下のような「部署マスタ」という別のマスタも用意しておきます。前述の「社員マスタ」のD列「部署」は、C列「部署No」をキーにして「部署マスタ」からVLOOKUP関数で引っ張ってきています。「社員マスタ」のセルD2であれば、以下のような関数が仕込まれています。=VLOOKUP(C2,部署マスタ!A:B,2,FALSE)こうしておけば、社員の部署が変更された場合は「社員マスタ」の「部署No」を変更すれば、「部署」も連動して変更できます。部署の名称が変更になった場合は「部署マスタ」の「部署」を変更することで、「社員マスタ」も連動して変更できます。ユーザーの入力とその影響は最小限にこのように、マスタデータを準備さえしておけば、「社員No」のみをキーに、その他のデータは正しいものを引っ張ってくることができます。その社員Noはファイル名に仕込まれていますので、エクセルファイル内のユーザーの入力内容に依存せずに経費データを収集、蓄積できるようになります。業務フローにもよりますが、そもそもシート上の入力欄自体なくしてもいいのかもしれません。このように、ユーザーが入力すべきデータとそのミスによる影響範囲を最小限に留めておく、という仕組みづくりは、プログラミング自体とともに重要なポイントと言えます。社内のマスタ管理の注意点さて、今回のシリーズでは経費精算書だけにフォーカスをしていますが、部署や社員のデータというのは、他の業務でも使用される可能性は十分にあります。本来はこれらの「おおもとのマスタ」は、経理担当者のローカルPC内のいちエクセルファイルで完結する場合は、あまりないかも知れません。一般的には「おおもとのマスタ」は、管理システムにあったり、共有サーバーにあったりすることもありますね。その「おおもとのマスタ」と、VBAで使用するマスタをいかにして連動させるか、というのは運用上考えておくべき課題となります。その点、念頭に置いておいていただければと思います。まとめ以上、エクセルVBAでマクロを作る際に知っておきたいマスタデータのことについてお伝えしました。繰り返しになりますが、ユーザーの入力にはどうやってもミスが混入します。入力自体が最小限になること、そしてその入力のミスによる影響を最小限に留めておくことを目指してみましょう。次回は具体的に、ファイル名から社員番号を抜き出す方法をお伝えします。【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換するエクセルVBAでバラバラの経費精算書のデータをまとめるマクロの作り方をお伝えしています。今回は、開いたブックのファイル名から番号を取り出して数値に変換するために、いくつかの便利な関数を紹介していきます。tonari-it.com2018.06.26どうぞお楽しみに!連載目次:エクセルVBAで経費データをデータベースに集約する請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。プランノーツ代表、コミュニティ「ノンプロ研」主宰。ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ★フォロー頂ければ嬉しいです。「コミュニティ+講座」形式で継続性&教え合える環境も提供。!ノンプログラマーがプログラミングスキルを身に着ける支援ををするコミュニティ。セミナー・もくもく会・Facebookグループのサポートで「自らで学び続ける力をつける」支援、「教え合うことで学びの価値を上げる」場の提供をしています。【エクセルVBA】ユーザーフォームを作ってみよう!VBEでフォームを挿入する方法エクセルのユーザーフォームを作成して動かすことを目標に、フォームの挿入やプロパティでの設定、VBAでの動かし方を順を追ってご紹介していく連載です。初回の今回は、VBEでユーザーフォームを挿入して、基本的なプロパティの設定方法をご紹介していきます。「初心者でもわかるエクセルVBAのクラスモジュール」というテーマでシリーズ連載をしております。今回は、エクセルVBAで自作コレクションの要素を取得するItemプロパティの作り方をお伝えします。【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法エクセルVBAでバラバラの経費精算書を一つのデータベースにまとめるマクロの作り方についてお伝えしています。今回はVLookupメソッドを使ったときに発生するエラーを回避する方法についてお伝えします。VBAの本格解説本を書く必要があるのか3冊目となる書籍の企画が通過しました。VBAの本格解説本です。今からVBAの本格解説本を書く必要があるのかについて、出版社さんに企画の際に送った文面をだいたいそのまま記事として公開します。表の範囲をRangeプロパティで直接指定することでも表に処理を行うことは可能です。データは日々更新され、表の範囲も変わっていきます。そんな問題を解決してくれるのがCurrentRegionプロパティです。2017.09.21Excel・VBA活用術エクセルVBAでピボットテーブルを操作するための第一歩~ピボットテーブルオブジェクト変数~エクセルのピボットテーブル操作(作成、更新など)をVBAで自動化の第一歩、オブジェクト変数へ操作対象のピボットテーブルをセットする方法を、「オブジェクトとは?」というところからご紹介しています。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Platform23HTML・CSS32iPhone・アプリ29Monaca・スマホアプリ開発7PC全般・Windows86PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント108セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Platform23HTML・CSS32iPhone・アプリ29Monaca・スマホアプリ開発7PC全般・Windows86PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント108セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。RowSourceプロパティとAddItemメソッド2020.01.21GASでGoogleスライドのSlideオブジェクトの概要とその取得方法2020.01.162020.01.24Facebookページいつも隣にitのお仕事タイトルとURLをコピーしました。