VLookUp関数、Index関数、Match関数より便利なマクロ-複数シート間、複数ファイル間でのマッチング

この講座は有料講座です。
講座の購入後ご覧になれます。

ログインアカウントの新規作成

解説

演習問題テーマ:VLookUp関数、Index関数、Match関数より便利なマクロ-複数シート間、複数ファイル間でのマッチング

複数の表の間でのマッチングのアレンジです。VLookUp関数より便利なマクロ-複数シート間、複数ファイル間でのマッチングを行います。マクロでは、セルに関数が埋め込まれているのではなく、処理の結果だけが記入されています。複数シート間での処理をエクセルワークシート関数で行うと、転記先のシートを開く都度、「別ファイルへの参照を行いますか?」といったことを確認するダイアログが出ますが、マクロではそういうことは起こりません。そういうことも、地味ですが大きなメリットです。

この教材についての過去の質問・感想

12242 : 小川慶一の回答 (2020-07-02 08:06:54)

受講生 さん:

お楽しみいただけているようでなによりです。
ひきつづき、よい学びを☆


12238 : 受講生さんのコメント (2020-07-01 21:41:01)

小川慶一さん:
Replace関数というものがあるんですね!ありがとうございます。ここ数日どう質問していいかも分からずやっと質問しました。困難に遭うたび新しいことを身につけられてうれしいです。
> さざなみさん:
>
> こんばんは。
>
> 以下のサンプルコードを参考にしてください。
> ポイントは、以下の2点です。
> [a] replace関数を使う
> [b] データの型を一致させてから比較する
>
>

'replace関数の使い方について学びましょう。
> Sub hoge()
>     Dim moji
>     moji = "abc-12-34-56-78"
>     
>     Dim new_moji
>     new_moji = Replace(moji, "-", "") 'ハイフンを見つけたら長さ0の文字列と置換します
>     
>     Range("A1").Value = "abc12345678"
>     Range("A2").Value = moji
>     Range("A3").Value = new_moji
>     
>     If Range("A1").Value = Range("A3").Value Then
>         Range("C1").Value = "A1とA3のセルの値は一致しています"
>     Else
>         Range("C1").Value = "A1とA3のセルの値は一致していません"
>     End If
>     
>     If Range("A1").Value = new_moji Then
>         Range("C2").Value = "A1の値と変数new_mojiの中身は一致しています"
>     Else
>         Range("C2").Value = "A1の値と変数new_mojiの中身は一致していません"
>     End If
> End Sub
> 
> '数値と文字を比較した場合、一見同じようでも「一致していない」と判定されることがあります
> '以下の[1]では、左辺は、セルA5の値。これは数値「12345678」です。
> '               右辺は、new_mojijという変数。これは文字列「"12345678"」が格納されています。
> 'これらは、一見同じようでも、違うものです。
> Sub fuga()
>     Dim moji
>     moji = "12-34-56-78"
>     
>     Dim new_moji
>     new_moji = Replace(moji, "-", "") 'ハイフンを見つけたら長さ0の文字列と置換します
>     
>     Range("A5").Value = "12345678"
>     Range("A6").Value = moji
>     Range("A7").Value = new_moji
>     
>     If Range("A5").Value = Range("A3").Value Then
>         Range("C5").Value = "A1とA3のセルの値は一致しています"
>     Else
>         Range("C5").Value = "A1とA3のセルの値は一致していません"
>     End If
>     
>     If Range("A5").Value = new_moji Then '[1]
>         Range("C6").Value = "A1の値と変数new_mojiの中身は一致しています"
>     Else
>         Range("C6").Value = "A1の値と変数new_mojiの中身は一致していません"
>     End If
> End Sub
> 
> '前記の問題の回避策は、データ型を変換してから比較をすることです。
> '以下では、 12345678 という数値を "12345678" という文字列に変換しました。
> 'これで、左辺は "12345678", 右辺も "12345678" ということで、めでたく「一致している」という結果を得られます。
> Sub piyo()
>     Dim moji
>     moji = "12-34-56-78"
>     
>     Dim new_moji
>     new_moji = Replace(moji, "-", "") 'ハイフンを見つけたら長さ0の文字列と置換します
>     
>     Range("A9").Value = "12345678"
>     Range("A10").Value = moji
>     Range("A11").Value = new_moji
>     
>     If Range("A9").Value = Range("A3").Value Then
>         Range("C9").Value = "A1とA3のセルの値は一致しています"
>     Else
>         Range("C9").Value = "A1とA3のセルの値は一致していません"
>     End If
>     
>     'Cstr関数は、数値を文字列に変換します
>     If CStr(Range("A9").Value) = new_moji Then
>         Range("C10").Value = "A1の値と変数new_mojiの中身は一致しています"
>     Else
>         Range("C10").Value = "A1の値と変数new_mojiの中身は一致していません"
>     End If
> End Sub

>
>
> > 質問です。ある10桁以上の連続した番号を含むファイルAがあります。Bにも同じ番号が入力されています。10桁以上の番号をキーにして、Bの情報をAのファイルに転記したいのですが、Bの番号にはハイフンが含まれています。ハイフンを排除したいのですがどうしたらできますか?発展編2を受ければスマートなやり方ができますか?検索・置換やブイルックアップを使わずにマクロでやりたいです。


12237 : 小川慶一の回答 (2020-07-01 19:28:57)

さざなみさん:

こんばんは。

以下のサンプルコードを参考にしてください。
ポイントは、以下の2点です。
[a] replace関数を使う
[b] データの型を一致させてから比較する

'replace関数の使い方について学びましょう。
Sub hoge()
    Dim moji
    moji = "abc-12-34-56-78"
    
    Dim new_moji
    new_moji = Replace(moji, "-", "") 'ハイフンを見つけたら長さ0の文字列と置換します
    
    Range("A1").Value = "abc12345678"
    Range("A2").Value = moji
    Range("A3").Value = new_moji
    
    If Range("A1").Value = Range("A3").Value Then
        Range("C1").Value = "A1とA3のセルの値は一致しています"
    Else
        Range("C1").Value = "A1とA3のセルの値は一致していません"
    End If
    
    If Range("A1").Value = new_moji Then
        Range("C2").Value = "A1の値と変数new_mojiの中身は一致しています"
    Else
        Range("C2").Value = "A1の値と変数new_mojiの中身は一致していません"
    End If
End Sub

'数値と文字を比較した場合、一見同じようでも「一致していない」と判定されることがあります
'以下の[1]では、左辺は、セルA5の値。これは数値「12345678」です。
'               右辺は、new_mojijという変数。これは文字列「"12345678"」が格納されています。
'これらは、一見同じようでも、違うものです。
Sub fuga()
    Dim moji
    moji = "12-34-56-78"
    
    Dim new_moji
    new_moji = Replace(moji, "-", "") 'ハイフンを見つけたら長さ0の文字列と置換します
    
    Range("A5").Value = "12345678"
    Range("A6").Value = moji
    Range("A7").Value = new_moji
    
    If Range("A5").Value = Range("A3").Value Then
        Range("C5").Value = "A1とA3のセルの値は一致しています"
    Else
        Range("C5").Value = "A1とA3のセルの値は一致していません"
    End If
    
    If Range("A5").Value = new_moji Then '[1]
        Range("C6").Value = "A1の値と変数new_mojiの中身は一致しています"
    Else
        Range("C6").Value = "A1の値と変数new_mojiの中身は一致していません"
    End If
End Sub

'前記の問題の回避策は、データ型を変換してから比較をすることです。
'以下では、 12345678 という数値を "12345678" という文字列に変換しました。
'これで、左辺は "12345678", 右辺も "12345678" ということで、めでたく「一致している」という結果を得られます。
Sub piyo()
    Dim moji
    moji = "12-34-56-78"
    
    Dim new_moji
    new_moji = Replace(moji, "-", "") 'ハイフンを見つけたら長さ0の文字列と置換します
    
    Range("A9").Value = "12345678"
    Range("A10").Value = moji
    Range("A11").Value = new_moji
    
    If Range("A9").Value = Range("A3").Value Then
        Range("C9").Value = "A1とA3のセルの値は一致しています"
    Else
        Range("C9").Value = "A1とA3のセルの値は一致していません"
    End If
    
    'Cstr関数は、数値を文字列に変換します
    If CStr(Range("A9").Value) = new_moji Then
        Range("C10").Value = "A1の値と変数new_mojiの中身は一致しています"
    Else
        Range("C10").Value = "A1の値と変数new_mojiの中身は一致していません"
    End If
End Sub



> 質問です。ある10桁以上の連続した番号を含むファイルAがあります。Bにも同じ番号が入力されています。10桁以上の番号をキーにして、Bの情報をAのファイルに転記したいのですが、Bの番号にはハイフンが含まれています。ハイフンを排除したいのですがどうしたらできますか?発展編2を受ければスマートなやり方ができますか?検索・置換やブイルックアップを使わずにマクロでやりたいです。


12236 : さざなみさんのコメント (2020-07-01 18:19:04)

質問です。ある10桁以上の連続した番号を含むファイルAがあります。Bにも同じ番号が入力されています。10桁以上の番号をキーにして、Bの情報をAのファイルに転記したいのですが、Bの番号にはハイフンが含まれています。ハイフンを排除したいのですがどうしたらできますか?発展編2を受ければスマートなやり方ができますか?検索・置換やブイルックアップを使わずにマクロでやりたいです。


8617 : 小川慶一の回答 (2017-08-07 12:33:01)

受講生 さん:

> > マクロの自動記録から、ファイルを開く コピぺ 開くの動画の手順が全くわかりませんでした。
> 「わからない」のは、上記、「マクロの自動記録から、ファイルを開く コピぺ 開くの動画の手順」のことだけという理解でよいでしょうか?
>
> 私もこの状況がわかりません。


分からないのは、「状況」のことですか?
「状況がわかりません」ということでしたら、僕は、状況について説明します。

そうではなくて、具体的な操作の方法のことではないか?と推定しましたので、具体的な操作方法について説明しますね。
(*1)普段から、日本語で人にものを伝えるときには、なるべく相手に誤解のない表現を使うようにしましょう。そういう努力は、マクロを書くときにそのまま活きます。


まずは、以下を確認してください。

[1] .zipファイルを「解凍」し、その解凍してできたフォルダ内のファイルを開いているか?
[2] エクセルの「ファイルを開く」ダイアログからファイルを開いているか?
[3] 目的としているファイルを開いているか?

以下、それぞれの詳細です。

[1]
.zip ファイル内のエクセルファイルを開いても、正しく自動記録されません。
正しく自動記録するには、解凍してできたフォルダ内のファイルを開いてください。

特に、このケースでは、

[a] ksfollow.zip

の中に、

[b] ks201_mondai.zip

があり、OSのバージョンによっては、 [a] の中で [b] を解凍できてしまう場合があるので要注意です。

[a] を解凍し、[a]を解凍してできたフォルダの中から[b]を見つけ、その[b]を解凍してください。


[2]
エクスプローラ上に表示されたエクセルファイルをダブルクリックしてエクセルファイルを開いても、「ファイルを開く操作」の記録はとれません。
エクセルリボン左端にある「ファイル」をクリックし、そして画面左に表示される「開く」メニューから目的のファイルを探してきて、開いてください。

上記[2]の操作は、マクロのスキルというよりエクセルの基本スキルなので、これ以上の説明はここでは割愛します。
(尚、発展編1では、詳しくその操作も解説しています)


[3]
今回のケースであれば、自動記録しながら開くファイルは「応募状況.xls」です。
「顧客リスト.xls」ではありません。

> OKを押すと、エラーメッセージ2重に開くと・・・とのメッセージが出ます。

「顧客リスト.xls は既に開いています。2重に開くと、これまでの変更内容は破棄されます。顧客リスト.xls を開きますか?」というエラーメッセージでしょうか?
たぶんそうだと思いますので、その前提で話を進めますね。

このエラーは、マクロの記録中云々に限らず、すでに開いているエクセルファイルを、エクスプローラ上でのダブルクリックなり、上記[2]の方法でなりで開こうとしたときに出るエラーメッセージです。
ためしに、マクロの記録をしていない状態で、何かのファイルを開き、編集し、それからその同じファイルを改めて開こうとしてみてください。
そうすると、同じエラーメッセージを確認できるかと思います。

ということで。

[1] マクロの問題ではなく、Windows基本操作の知識
[2] マクロの問題ではなく、エクセル基本操作の知識
[3] マクロの問題ではなく、エクセル基本操作の知識

ということでした。マクロ以前の問題点ですが、これらをまずは確認してください。
そのうえで、また何かあれば質問をしてください。

(*2)あと、エラーメッセージは全文書いてください。なるべく正解な情報を提供するというのは、基本中の基本です。回答者の立場になって考えれば分かることかと思いますが、でないと、どういうエラーが出たのかこちらでいろいろ推測して余計な手間が発生しますので、回答がとても負担です。おまけに、これでもし僕の推測が違ったなら、僕のほうは、かなり時間をかけてこのお返事を書いたにもかかわらず、最初から回答やりなおしです。
「自分の質問が不明確なために、回答者によけいな調査をさせたりすることのないように。そして、余計な二度手間を相手に発生させないように」ということまで気にして質問をできるようになると、マクロを書く技術も自然に上達します。
こういう方向の努力も、一層されるとよいかと思います。

(*1), (*2) については、以下の記事を参考にしてください。
「エクセルマクロ習得に関係する4つの能力」
http://www.exvba.com/4skills.php


8614 : 受講生さんのコメント (2017-08-06 10:45:29)

お疲れ様です。
8559 : 小川慶一の回答 (2017-07-19 10:40:37)の内容で下記の質問について
回答されておられますが、

> マクロの自動記録から、ファイルを開く コピぺ 開くの動画の手順が全くわかりませんでした。
「わからない」のは、上記、「マクロの自動記録から、ファイルを開く コピぺ 開くの動画の手順」のことだけという理解でよいでしょうか?

私もこの状況がわかりません。Excelのバージョンで2013ですが、まずフアイルを開くときに、マクロの録音をおして、オープンマクロの名前をつけて、
OKを押すと、エラーメッセージ2重に開くと・・・とのメッセージが出ます。
過去の回答の内容を見ても、わかりません。
また、操作方法ですが、マクロの自動記録のやり方は、ksfollowのファイルがデスクトップあります。その中から、ksfollow-downloadを開いて、
【動画25】 VLookUp関数、Index関数、Match関数より便利なマクロ-複数シート間、複数ファイル間でのマッチングから、ks202からks201_mondai.zip
上書きの確認から、すべていいえ、ks201_mondaiから、顧客リスト.xlsから、開発タブから、マクロの記録 マクロ名をFileOpenmacroを入れOKをクリック そのあたりの操作方法がわかりません。動画では、9:40からの部分です。よろしくお願いします。


8559 : 小川慶一の回答 (2017-07-19 10:40:37)

受講生 さん:

> マクロの自動記録から、ファイルを開く コピぺ 開くの動画の手順が全くわかりませんでした。

マクロの自動記録のやり方が分からない、ということでしょうか。
でしたら、導入編で復習してください。
僕がマクロの自動記録中にしている作業の詳細を知りたいということであれば、何分何秒から何分何秒のどの操作か?ということをお知らせください。

> マッチングの問題はTRUE、false Exitforは理解できましたが、わからないまま次にステップに進むことは、まずいですか?

「わからない」のは、上記、「マクロの自動記録から、ファイルを開く コピぺ 開くの動画の手順」のことだけという理解でよいでしょうか?


8557 : 受講生さんのコメント (2017-07-18 21:37:48)

お疲れ様です。
4番目の問題は、発展編で詳しくやられるとのことでしょうが、あまりにも、わからないことが、ありました。
マクロの自動記録から、ファイルを開く コピぺ 開くの動画の手順が全くわかりませんでした。マッチングの問題はTRUE、false Exitforは理解できましたが、わからないまま次にステップに進むことは、まずいですか?


3日がかりのその仕事、3分で終わらせる方法教えます。ガラパゴスタディーオンライン講座 ユーザー登録

本講座の動画一覧

  1. 【動画1】 住所情報を都道府県から市区町村レベルで細かく分割する
    【動画1】 住所情報を都道府県から市区町村レベルで細かく分割する 未習得
  2. 【動画2】 保険料額表から標準報酬額ごとの保険料負担額を算出する
    【動画2】 保険料額表から標準報酬額ごとの保険料負担額を算出する 未習得
  3. 【動画3】 セル内の文字列から区切り文字を見つけ、その出現回数+1回だけデータ転記する
    【動画3】 セル内の文字列から区切り文字を見つけ、その出現回数+1回だけデータ転記する 未習得
  4. 【動画4】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を切り出す
    【動画4】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を切り出す 未習得
  5. 【動画5】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を反映しつつデータ転記する
    【動画5】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を反映しつつデータ転記する 未習得
  6. 【動画6】 VLookUp関数、Index関数、Match関数より便利なマクロ-該当する行が見つからない場合の処理をアレンジ
    【動画6】 VLookUp関数、Index関数、Match関数より便利なマクロ-該当する行が見つからない場合の処理をアレンジ 未習得
  7. 【動画7】 VLookUp関数、Index関数、Match関数より便利なマクロ-複数シート間、複数ファイル間でのマッチング
    【動画7】 VLookUp関数、Index関数、Match関数より便利なマクロ-複数シート間、複数ファイル間でのマッチング 未習得
  8. 【動画8】 転記先シートを生成してデータを流し込む。
    【動画8】 転記先シートを生成してデータを流し込む。 未習得
  9. 【動画9】 転記先ファイルを生成してデータを流し込む。
    【動画9】 転記先ファイルを生成してデータを流し込む。 未習得
  10. 【動画10】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で作成する。
    【動画10】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で作成する。 未習得
  11. 【動画11】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で同一ファイル内に連続的に作成する。
    【動画11】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で同一ファイル内に連続的に作成する。 未習得
  12. 【動画12】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で複数の別ファイルとして連続的に作成する。
    【動画12】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で複数の別ファイルとして連続的に作成する。 未習得
  13. 【動画13】 ファイル配布→配布先でのデータ記入→記入済データを手元資料に統合
    【動画13】 ファイル配布→配布先でのデータ記入→記入済データを手元資料に統合 未習得
  14. 【動画14】 データ記入用ファイルを配布する-その1
    【動画14】 データ記入用ファイルを配布する-その1 未習得
  15. 【動画15】 データ記入用ファイルを配布する-その2
    【動画15】 データ記入用ファイルを配布する-その2 未習得
  16. 【動画16】 各部署からファイルを回収し、記入済データを手元資料に統合その1
    【動画16】 各部署からファイルを回収し、記入済データを手元資料に統合その1 未習得
  17. 【動画17】 各部署からファイルを回収し、記入済データを手元資料に統合その2
    【動画17】 各部署からファイルを回収し、記入済データを手元資料に統合その2 未習得
  18. 【動画18】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その1)
    【動画18】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その1) 未習得
  19. 【動画19】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-1)
    【動画19】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-1) 未習得
  20. 【動画20】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-2)
    【動画20】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-2) 未習得

塾長 小川慶一

メニュー

コメント紹介

もっと見る

ページの先頭へ