Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


6782 / 13646 ツリー ←次へ | 前へ→

【43099】VLOOKUP 難問 06/10/2(月) 11:43 質問[未読]
【43100】Re:VLOOKUP ハチ 06/10/2(月) 12:27 発言[未読]
【43104】Re:VLOOKUP 難問 06/10/2(月) 13:02 発言[未読]
【43108】Re:VLOOKUP ハチ 06/10/2(月) 14:38 回答[未読]
【43166】Re:VLOOKUP 難問 06/10/4(水) 12:50 お礼[未読]
【43101】Re:VLOOKUP inoue 06/10/2(月) 12:28 発言[未読]
【43105】Re:VLOOKUP 難問 06/10/2(月) 13:04 発言[未読]
【43127】Re:VLOOKUP inoue 06/10/3(火) 0:51 発言[未読]
【43167】Re:VLOOKUP 難問 06/10/4(水) 12:51 お礼[未読]
【43118】Re:VLOOKUP りん 06/10/2(月) 19:33 発言[未読]

【43099】VLOOKUP
質問  難問  - 06/10/2(月) 11:43 -

引用なし
パスワード
   どなたか助けてください。
業務の中でよく出てくる作業を毎回手作業で時間をかけて
やっております。
【マクロにしたい操作】
2つのDB(大体1万件程度)の共通するフィールドを使い、
VLOOKUPでDBを1つに合体させています。
【サンプル】
売上.xls
顧客番号&コード    顧客番号    コード    品番    1月    2月    3月
294072PG001    29407    2PG001    00RP    54350    11350    29850

売上2.xls
顧客番号&コード    顧客番号    コード    品番    1月    2月    3月
2040002PG001    20400    2PG001    03RP    179750    259750    34750

手作業
1.2つのDBにフィールドを追加し、共通する複数のフィールドを
&結ぶ数式をまず、入れます。
2.売上.xlsの最右列を使用し、vlooup関数を使用して
売上2.xlsのデータを引っ張ります。
=IF(A2="","",IF(ISNA(VLOOKUP([売上2.xls]京都!A2,[売上2.xls]京都!A$2:G$51,6,FALSE))=TRUE,"該当なし",VLOOKUP([売上2.xls]京都2!A2,[売上2.xls]京都2!A$2:G$51,6,FALSE)))
★今回は検索範囲の中から6列目を返すという式を入れていますが、
毎回列は替わります。
また、2、3、6、8、23列目の値を返したいという場合は
どの部分で指定したらよいのかも教えてください。
手作業だと、1つ式をつくり、それを右にコピーして、
返したい値のある列部分を数式の中で変更するという
やりかたでやっています。


自動記録でやってみたのですが、なかなか上手くいかないのが現状です。

【43100】Re:VLOOKUP
発言  ハチ  - 06/10/2(月) 12:27 -

引用なし
パスワード
   ▼難問 さん:
>どなたか助けてください。
>業務の中でよく出てくる作業を毎回手作業で時間をかけて
>やっております。
>【マクロにしたい操作】
>2つのDB(大体1万件程度)の共通するフィールドを使い、
>VLOOKUPでDBを1つに合体させています。
>【サンプル】
>売上.xls
>顧客番号&コード    顧客番号    コード    品番    1月    2月    3月
>294072PG001    29407    2PG001    00RP    54350    11350    29850
>
>売上2.xls
>顧客番号&コード    顧客番号    コード    品番    1月    2月    3月
>2040002PG001    20400    2PG001    03RP    179750    259750    34750
>

A2の値は、"294072PG001"と"2040002PG001"で共通してない ように見えますが
C列が共通の項目なんでしょうか?

VBAでやるなら共通項目の列をループさせた値で、
相手側(売上2.xls)の列をFindして
必要なデータをOffsetで取得すれば出来そうです。
もしくは丸ごともってきていらないところを削除するか ですね。

>手作業
>1.2つのDBにフィールドを追加し、共通する複数のフィールドを
>&結ぶ数式をまず、入れます。
>2.売上.xlsの最右列を使用し、vlooup関数を使用して
>売上2.xlsのデータを引っ張ります。
>=IF(A2="","",IF(ISNA(VLOOKUP([売上2.xls]京都!A2,[売上2.xls]京都!A$2:G$51,6,FALSE))=TRUE,"該当なし",VLOOKUP([売上2.xls]京都2!A2,[売上2.xls]京都2!A$2:G$51,6,FALSE)))
>★今回は検索範囲の中から6列目を返すという式を入れていますが、
>毎回列は替わります。
>また、2、3、6、8、23列目の値を返したいという場合は
>どの部分で指定したらよいのかも教えてください。
>手作業だと、1つ式をつくり、それを右にコピーして、
>返したい値のある列部分を数式の中で変更するという
>やりかたでやっています。
>
>
>自動記録でやってみたのですが、なかなか上手くいかないのが現状です。

【43101】Re:VLOOKUP
発言  inoue E-MAILWEB  - 06/10/2(月) 12:28 -

引用なし
パスワード
   >業務の中でよく出てくる作業を毎回手作業で時間をかけて
>やっております。
VBAとR1C1参照形式を学んでもらえば、
ループさせずにFormulaR1C1プロパティで列単位に一発で
必要な式はセットできると思います。
R1C1参照形式では提示のような式は行によって変異しないからです。

ところで、
>2.売上.xlsの最右列を使用し、vlooup関数を使用して
>売上2.xlsのデータを引っ張ります。
>=IF(A2="","",IF(ISNA(VLOOKUP([売上2.xls]京都!A2,[売上2.xls]京都!A$2:G$51,6,FALSE))=TRUE,"該当なし",VLOOKUP([売上2.xls]京都2!A2,[売上2.xls]京都2!A$2:G$51,6,FALSE)))
VLOOKUP内の第一引数はただの「A2」ではありませんか?

【43104】Re:VLOOKUP
発言  難問  - 06/10/2(月) 13:02 -

引用なし
パスワード
   >A2の値は、"294072PG001"と"2040002PG001"で共通してない ように見えますが
>C列が共通の項目なんでしょうか?

B列とC列を結んだ値をA列にセットしています。
サンプルであげているのはたまたま先頭行があっていないだけで
2つのDBの各1万件の中には共通するものがあります。

【43105】Re:VLOOKUP
発言  難問  - 06/10/2(月) 13:04 -

引用なし
パスワード
   >VLOOKUP内の第一引数はただの「A2」ではありませんか?
失礼しました。
A2です。

>VBAとR1C1参照形式を学んでもらえば、
ループさせずにFormulaR1C1プロパティで列単位に一発で
必要な式はセットできると思います。

ということは初心者ではまず、無理だということですね。
(ToT)/~~~

【43108】Re:VLOOKUP
回答  ハチ  - 06/10/2(月) 14:38 -

引用なし
パスワード
   ▼難問 さん:
>>A2の値は、"294072PG001"と"2040002PG001"で共通してない ように見えますが
>>C列が共通の項目なんでしょうか?
>
>B列とC列を結んだ値をA列にセットしています。
>サンプルであげているのはたまたま先頭行があっていないだけで
>2つのDBの各1万件の中には共通するものがあります。

Findで行う案を。
昔、作ったやつをちょっと手直ししただけなので上手く動作するか検証してません。
WorkSheets(1)、(2)のところを環境に合わせて編集。
Select Case i のあたりがデータをセットしているところになってます。

2つのBookを開いた状態で実行してみてください。


Option Explicit

Sub Find_Test()
  Dim Ws1 As Worksheet, Ws2 As Worksheet
  Dim Ran1 As Range, Ran2 As Range
  Dim R As Range
  Dim Fi As Range
  Dim i As Long, j As Long
  
  '前準備
  Set Ws1 = Worksheets(1) 'ここを Workbooks("xx.xls").WorkSheets("シート名")に変更
  Set Ws2 = Worksheets(2) '↑と同じ感じ
  'Ws1.Columns(1).Insert Shift:=xlToRight 'A列を挿入する必要があるならコメント解除
  'Ws2.Columns(1).Insert Shift:=xlToRight
  With Ws1
    Set Ran1 = Range(.Range("B2"), .Range("B65536").End(xlUp)).Offset(, -1)
  End With
  With Ws2
    Set Ran2 = Range(.Range("B2"), .Range("B65536").End(xlUp)).Offset(, -1)
  End With
  Ran1.Formula = "=CONCATENATE(B2,C2)"
  Ran1.Value = Ran1.Value
  Ran2.Formula = "=CONCATENATE(B2,C2)"
  Ran2.Value = Ran2.Value
  
  'データ転記
  For Each R In Ran1.Cells
    Set Fi = Ran2.Find(R.Value, , xlValues, xlWhole, , , False, False)
    If Fi Is Nothing Then
      R.Offset(, 7).Value = "該当なし"  'Ws2になげれば.offset(,7)H列に"該当なし"
    Else
      For i = 7 To 11 'A列のOffset(,7)H列から11のJ列までにWs2のデータを代入
        Select Case i
          Case 7: j = 2  ',7(H列)に,2(C列)を
          Case 8: j = 3  ',8(I列)に,3(D列)を 以下略
          Case 9: j = 6
          Case 10: j = 8
          Case 11: j = 23
          Case Else: j = 0
        End Select
        If j <> 0 Then R.Offset(, i).Value = Fi.Offset(, j).Value
      Next i
    End If
  Next R
  
  '後処理
  Set Ws1 = Nothing: Set Ws2 = Nothing
  Set Ran1 = Nothing: Set Ran2 = Nothing: Set Fi = Nothing

End Sub

【43118】Re:VLOOKUP
発言  りん E-MAIL  - 06/10/2(月) 19:33 -

引用なし
パスワード
   難問 さん、こんばんわ。

B2セルに6を入れて、
=IF(A2="","",IF(ISNA(A2,[売上2.xls]京都!A$2:G$51,B2,FALSE))=TRUE,"該当なし",VLOOKUP(A2,[売上2.xls]京都2!A$2:G$51,B2,FALSE)))

という風に、変わる可能性のある値は固定ではなくて、セルに入れたら修正は楽になりますよ。

【43127】Re:VLOOKUP
発言  inoue E-MAILWEB  - 06/10/3(火) 0:51 -

引用なし
パスワード
   >>VBAとR1C1参照形式を学んでもらえば、
>>ループさせずにFormulaR1C1プロパティで列単位に一発で
>>必要な式はセットできると思います。
>ということは初心者ではまず、無理だということですね。
VBA掲示板に質問されている以上は、
自分でマクロを書くつもりではないのですか?
「列単位に一発」というのはマクロとしては複雑ではないのですが。

例えば、
Sub TEST()
Range("B1:B100").FormulaR1C1 = "=RC1"
End Sub
というマクロを書いて実行すれば、
B列の1行目から100行目までA列の値を表示する式が入ります。

【43166】Re:VLOOKUP
お礼  難問  - 06/10/4(水) 12:50 -

引用なし
パスワード
   ありがとうございます。
なんとか改造してみて
分かってきました。
ありがとうございました。

【43167】Re:VLOOKUP
お礼  難問  - 06/10/4(水) 12:51 -

引用なし
パスワード
   ありがとうございます。
まだまだこれからもっと勉強しないとと
実感しました。

6782 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free