[[20201015123450]]　『Accessのクエリをエクセルで再現』（しのみや）　ページの最後に飛ぶ

『Accessのクエリをエクセルで再現』（しのみや）

 Accessで作られていたものをAccess廃止に伴いエクセルで再現しています

 Accessのクエリで、
 元テーブルと商品テーブルのNOが黒矢印で紐づいております

 【元テーブル】
 記号　NO　管理
 aa12　2　　○
 ds52　3　　○
 ce66　1　　×

 【商品テーブル】
 NO　　商品
 1　　　Ａ
 2　　　Ｂ
 3　　　Ｃ

 クエリ実行後
 記号　商品　管理
 aa12　Ｂ　　○
 ds52　Ｃ　　○
 ce66　Ａ　　×

 エクセルのVLOOKUPと同じ動きなのでVLOOKUPで再現しています

 【商品テーブル】を別シートに作成し、名前の定義で「商品」としています

 With Sheets("元テーブル")

   .Columns("C:C").Insert 　　
　 Shift:=xlToRight,CopyOrigin:=xlFormatFromLeftOrAbove
   .Range("C1").Value = "[商品]"

   .Range("C2").FormulaR1C1 = "=VLOOKUP(RC[-1],商品,2,0)"
   .Range("C2").AutoFill Destination:=.Range("C2:C100)

   .Columns("C:C").Copy
   .Columns("B:B").PasteSpecial Paste:=xlPasteValues
   Application.CutCopyMode = False
   .Columns("C:C").Delete Shift:=xlToLeft

 End With

 マクロの記録から作成したもので、実際動いていますのでこれで
いいのですが…
 こういった作業が多くあり、毎回この記述を使っていて良い記述なのかな…と思っています

 効果的な方法はありますでしょうか？
 VLookupでなくても問題ないです

＜使用 Excel：Excel2010、使用 OS：Windows10 ＞

 特に問題らしき点は感じないですが、
 Destinationの最終行を決め打ちしていいのかなぁとは思います。

 値をじかに埋める例

     With Sheets("元テーブル")
         .Range("B1").Value = "[商品]"

         With .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
            .Value = Application.VLookup(.Cells, Application.Range("商品"), 2, False)
         End With
     End With

（半平太） 2020/10/15(木) 14:21

 半平太さん　ありがとうございます
 >Destinationの最終行を決め打ちしていいのかなぁとは思います。
 気を付けます

 私はセルに式を入れにいっていましたが、
 vbaでエクセル関数を使うことができるのですね

 Application.と　WorksheetFunction.の違いが気になって調べていて
 エラーを返す、返さないのことが書かれていることがほとんどでしたが
 https://www.excel.studio-kazu.jp/kw/20191120162833.htmlを見つけました

 WorksheetFunction.だと、loopで１行ずつに対して
 Application.は１回（という表現はおかしいかもですが）で済みますね

 という理解をしました…正確かどうかちょっとわかりませんが…

（しのみや） 2020/10/15(木) 15:05

 Application.VLookupを教えていただいて順調に使わさせてもらっているのですが

 if文を使っているところがありまして

 Public Cur最終行 As Currency

 With Sheets("元テーブル")

        Cur最終行 = .Cells(Rows.Count, "A").End(xlUp).Row

        .Columns("N:N").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("N1").Value = "[区分]"

        .Range("N2").FormulaR1C1 = "=IF(RC[-1]*1<60,1,2)"
        .Range("N2").AutoFill Destination:=.Range("N2:N" & Cur最終行)

        .Columns("N:N").Copy
        .Columns("N:N").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False

 End With

 こちらの記述もApplication.ですっきりと書けるのかと
 調べていたのですが、ifが使えるようなワークシート関数がなさそうなのですが
 ifは.FormulaR1C1に式を入れにいくのでよいのでしょうか

 覚えたてなので、いろいろごっちゃになっていたらｽﾐﾏｾﾝ
（しのみや） 2020/10/15(木) 16:11

 数式でやる場合は、通常、こんな風にやっています。(私は）

     With Sheets("元テーブル")
         Cur最終行 = .Cells(Rows.Count, "A").End(xlUp).Row
         .Columns("N:N").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
         .Range("N1").Value = "[区分]"

         With .Range("N2:N" & Cur最終行)
             .FormulaR1C1 = "=IF(RC[-1]*1<60,1,2)"
             .Value = .Value
         End With
     End With

 ※Insertする必要があるのかどうかは、こちらでは分かりません。

（半平太） 2020/10/15(木) 17:02

 半平太さん　ありがとうございます
 引き続き調べていたのですが…IFはワークシート関数にはない様子でした

 ないものは、.FormulaR1C1に入れていくという方法で進めてみます
 記述もありがとうございます
 勉強になります
（しのみや） 2020/10/15(木) 17:10

コメント返信：

[ 一覧(最新更新順) ]

[[20201015123450]] 『Accessのクエリをエクセルで再現』（しのみや） ページの最後に飛ぶ

[[20201015123450]]　『Accessのクエリをエクセルで再現』（しのみや）　ページの最後に飛ぶ