(license)みなさん、こんにちは!タカハシ(@ntakahashi0505)です。エクセルVBAでバラバラの経費精算書のデータをまとめるマクロの作り方をお伝えしています。【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのことエクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。今回は、エクセルVBAでマクロを作るときに知っておきたいマスタデータのこと、またその準備の仕方についてお伝えしていきます。tonari-it.com2018.06.25マスタデータとは何か、またその作り方についてお伝えしました。今回はそのマスタデータでデータを取り出すキーとなる社員Noをファイル名から取り出していきますよ。いうことで、エクセルVBAで開いたブックのファイル名から番号を取り出して数値に変換する方法をお伝えします。行ってみましょう!目次これまでのおさらい手入力によるミスや揺らぎによる影響を避けるためにファイル名から社員番号を切り出すReplace関数で拡張子を取り除くRight関数で末尾から文字を切り出すVal関数で文字列型を数値型に変換するまとめ連載目次:エクセルVBAで経費データをデータベースに集約するこれまでのおさらい社員の皆さんから集めたエクセルでできた経費精算書のデータがあります。これらを、同じフォルダに放り込んでおいてマクロを走らせれば、全ての経費データを自動で集めてくれるというマクロを作成しています。ここまでで作成したコードはコチラです。Sub手入力によるミスや揺らぎによる影響を避けるためにただ、以下の部分は各スタッフさんの手入力になるのですが、入力ミスや揺らぎが混入する可能性があるわけです。それを防ぐために、以下のような「社員マスタ」を用意しました。社員Noだけ指定してあげれば、部署No、部署、氏名は全部マスタから抽出できるわけです。それで、社員Noはどこから抽出するかというと、以下の形式で構成されている、経費精算書ファイルのファイル名です。YYYYMM経費精算書_名前_XXXX.xlsx末尾の4桁のXXXXが社員Noにあたりますので、これを抜き出し、かつ数値に変換する方法を紹介していきます。ファイル名から社員番号を切り出すさあ、上記の形式で構成されるファイル名から社員Noを抜き出していきましょう。Replace関数で拡張子を取り除くまず、ファイル名の末尾には拡張子「.xlsx」がありますのでこれを取り除きます。特定の文字列を取り除きたい場合はReplace関数を使います。置換後の文字列)取り除くというか、Replace関数のもともとの機能は”置換”なのですが、置換後の文字列を「””」と指定することで取り除くのと同様の働きをします。今回のケースでは、以下のようにすればOKです。"")Right関数で末尾から文字を切り出す拡張子が取り除ければ、以前使用したRight関数を使って末尾から文字を切り出して取得することができます。以下のようにすれば、社員Noを取得することができます。4)Val関数で文字列型を数値型に変換するただ、実はもう一工夫必要です。いうのも、実際の社員Noは「数値型」なのですが、上記ファイル名から取得した値は「文字列型」です。型が異なるので、「数値型」に変換して揃える必要があります。それで、文字列を数値に変換するときはVal関数を使います。Val(文字列)今回の場合ですが、社員Noを表す数値型の変数はstaffIdですから、以下のようにすることで社員Noを数値型で取得することができます。4))まとめ以上、エクセルVBAで開いたブックのファイル名から番号を取り出して数値に変換する方法をお伝えしました。Replace関数、Right関数、Val関数を使いました。どれも使用頻度の高い関数ですので、ぜひ使いこなせるようになりたいですね。まとめのコードはコチラです。Subさて、次回ですがこの社員Noをキーにして、マスタから他のデータを引っ張ってきたいと思います。【エクセルVBA入門】マクロでVLookupメソッドを使ってデータを検索する方法エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズの7回目です。今回は、業務で有効なテクニックとしてもう一つ、マスタシートからVLookupメソッドでデータを取得してくる方法についてお伝えしていきます。tonari-it.com2015.08.26どうぞお楽しみに!連載目次:エクセルVBAで経費データをデータベースに集約する請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。プランノーツ代表、コミュニティ「ノンプロ研」主宰。ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ★フォロー頂ければ嬉しいです。「コミュニティ+講座」形式で継続性&教え合える環境も提供。!ノンプログラマーがプログラミングスキルを身に着ける支援ををするコミュニティ。セミナー・もくもく会・Facebookグループのサポートで「自らで学び続ける力をつける」支援、「教え合うことで学びの価値を上げる」場の提供をしています。関連記事Excel・VBA活用術エクセルのゴールシークを使って目標から逆算して毎月の必要なアクションを知る過去データから回帰分析で未来の予測をすることができます。今回はエクセルのゴールシークという機能を使うことで、ゴールである目標値から逆算をして前提となる数値を求める方法についてお伝えします。2016.10.02Excel・VBA活用術エクセルVBAでピボットテーブルを作成!PivotCacheにデータソース範囲をセットするピボットテーブルをVBAで作成する1ステップ目、ピボットテーブルのデータソース範囲を指定する方法をご紹介しています。VBAでピボットテーブルを作成する前提知識として、ピボットキャッシュとは?やデータソースからピボットテーブルが作成される流れも簡単にご紹介しています。2017.02.13Excel・VBA活用術YouTubeチャンネル「いつも隣にITのお仕事」でVBAの解説動画を配信開始しましたエクセルVBAプログラミングの解説動画の配信を開始しました!ようやくです...。今回はYouTubeでの解説動画配信のお知らせとともに、なぜ解説動画を配信するしようと思ったのかについてお伝えします。GoTo構文を使って任意のメッセージを表示する方法をご紹介しています。これを使えば、どこのセルがエラーになっているのかをメッセージで表示して、VBAツールを使う人にエラーの原因と対策を伝えることができますよ。【エクセルVBA】ChangeイベントとEnableEventsプロパティで部分一致検索をする方法WorksheetオブジェクトのChangeイベントと、ApplicationオブジェクトのEnableEventsプロパティを使って、セルに得意先名の一部を入力したら、マスタシートから正式名称を取得して入力セルに返す、というコードをご紹介しています。面倒かつ間違い易い入力作業は、出来るだけ簡略化したいですね。VBA】メール本文から欲しい情報だけを取り出す方法MailItemオブジェクトのBodyプロパティとMid関数を使って、Outlookのメール本文から欲しい情報だけを取り出す方法をご紹介しています。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣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をコピーしました。