エクセルで作成された二つの製品表を一つにする作業にてこずっています。
製品表は二つともAカラムにIDがあります。
製品表1には、同一のIDが二つ以上存在しません。
製品表2には、同一のIDが1~3存在します。
製品表1は
000128|product1
0KV502|product2
のように並んでいます。
製品表2は、
A | B |C
000128 | UNIT | UNIT NO.
000128 | BOX | BOX NO.
000128 | PALLET | PLT NO.
0KV502 | UNIT | UNIT NO.
0KV502 | PALLET |
のように、同一IDに対し、Unit番号、Box番号、Plt番号が異なる段に並んでいます。
IDは必ず入力されていますが、Unit番号、Box番号、Plt番号は必ずしも入力されているとは限りません。
また同一IDに対し、2列しかない場合や1段しかない場合もあります。
また、製品表1にあり、製品表2にないIDもあります。
この製品表2にある、Unit番号、Box番号、Plt番号を下の図のように、製品表1にコピーしたいです。
000128| product1 | Unit No | Box no | Plt No
0KV502 | product2 | Unit No | Box no | Plt No
製品表1のIDを変数「ID1」に格納し、
Dim ID1, ID2, Code, EAN, VL, PU As String
Dim row1, row2, c1, c2 As Integer 'row1,c1が製品表1のループに使い、row2、c2を製品表2のループに使います。
Dim qty, weight, PP As String '本当はUnit番号、Box番号、Plt番号以外にも数量や重量などのデータもコピーします。同じ段にあるので、その点はすでに解決済みです。
Sub my_merge()
row1 = 6 '製品表1のデータは6段目から始まります。
Worksheets("Items").Activate '製品表1の名前はItemsです。
Do Until row1 = 10'本当は8000列ありますが、デバグしやすいように最初の10段だけ比較しています。
ID1 = Cells(row1, 1).Value '製品表1のデータを格納しています。
row2 = 3 '製品表2は3段目から始まります。
Do Until row2 = 20'本当は12000段ありますが、デバグしやすいように最初の20段だけ比較しています。
Worksheets("uom").Activate'uomが製品表2です。
ID2 = Cells(row2, 1).Value'uomにあるIDを格納します。
If ID2 = ID1 Then'IDが一致したら
Call my_copy'コピーします。
Else
Call my_copy_delete'違ったら、前回コピーしたものを削除します。
End If
row2 = row2 + 1
Loop
Call my_debug
row1 = row1 + 1
Loop
Worksheets("Items").Activate
End Sub
Sub my_debug()
Debug.Print "a loop-------------"
Debug.Print "row1 is " & row1
Debug.Print "row2 is " & row2
Debug.Print "ID1 is " & ID1
Debug.Print "ID2 is " & ID2
Debug.Print "Code is " & Code
Debug.Print "EAN is" & EAN
Debug.Print ActiveSheet.Name & " is active"
End Sub
Sub my_copy()
Code = Cells(row2, 2).Value 'CodeでUNITかBOXかPALLETかを判断します。
qty = Cells(row2, 3).Value '数量
EAN = Cells(row2, 4).Value 'EANはUNIT、BOX、PALLETの番号です。
VL = Cells(row2, 5).Value
weight = Cells(row2, 6).Value
PU = Cells(row2, 7).Value
PP = Cells(row2, 8).Value
End Sub
Sub my_copy_delete()
ID2 = 0
Code = 0
qty = 0
EAN = 0
VL = 0
weight = 0
PU = 0
PP = 0
End Sub
Sub my_paste_group()
'本当は、UNIT,BOX,PALLETが、それぞれ重量、質量など、IDを含んで8つデータを持っているので、それにあわせてデータを横に並べます。
If Code = "UNIT" Then '
c1 = 14
Worksheets("Items").Activate
Call my_paste
ElseIf Code = "BOX" Then
c1 = 22
Worksheets("Items").Activate
Call my_paste
ElseIf Code = "PALLET" Then
c1 = 30
Worksheets("Items").Activate
Call my_paste
Else
MsgBox "unexpected error"
Exit Sub
End If
End Sub
Sub my_paste()
Cells(row1, c1).Value = ID2
Cells(row1, c1 + 1).Value = Code
Cells(row1, c1 + 2).Value = EAN
Cells(row1, c1 + 3).Value = VL
Cells(row1, c1 + 4).Value = PU
Cells(row1, c1 + 5).Value = qty
Cells(row1, c1 + 6).Value = weight
Cells(row1, c1 + 7).Value = PP
End Sub
当方欧州住まいで、今朝から11:30まで丸一日かけたのですが、完全に煮詰まりました。
誰か助けてください。。
No.1ベストアンサー
- 回答日時:
マクロは解らないけど、対応させるためのカラムを追加して、セルに式を構築していけば、Vlookup 関数でいけそうだけど。
Vlookupの参照は、先頭の1カラム対応だから、
1.まず、表2(Sheet2とする)の方に、A列手前にもう一つカラムを足して、ID+codename を作る
=CONCATENATE(B2,C2)
A列2行目に上記の式を入れたら、コピーして下方の行全部にペースト(以下の作業は皆一行作ったらそれをコピーして下方の行にペーストすると、全部すぐ計算される)
Sheet2 は、以下の列になる
A列( 式 =CONCATENATE(B2,C2) )、B列(id)、C列(codename)、D列(各番号)
ちなみに、一行目には、カラムの項目名が入ってることとして、2行目以降をデータとして説明しています。
2.Sheet2を、上記で追加したA列で並べ替えしておく
3.表1(Sheet1とする)へ戻り、各行の M,N,O列に、比較用に id+"Unit" などの文字列を作る
M列( 式 =CONCATENATE($A2,"Unit") )、N列( 式 =CONCATENATE($A2,"Box") )、O列( 式 =CONCATENATE($A2,"Pallet") )
※ コード数が4つ以上有るなら、その分を、P,Q列などにも作成しておきます。コード数増加に対応するなら、X列からとか、AA列からの方がいいかも
4.Sheet1!C列2行目にVlookup計算式を入れる、範囲指定のところは、Sheet2 の2行目以降全部が入るようにする。つまり$80のところは適宜書き換えること
=VLOOKUP(M2,Sheet2!$A$2:$D$80,4,FALSE)
同じく sheet1のD,E列同じ行に上記コピーペーストすると以下のデータが入る
D列 =VLOOKUP(N2,Sheet2!$A$2:$D$80,4,FALSE)
E列 =VLOOKUP(O2,Sheet2!$A$2:$D$80,4,FALSE)
コードの数だけ、コピーペーストで列を作ります。
※ Sheet2!D 列で、データがないと、上記計算結果は 0 が入ってしまうので、空文字列としたいなら、Sheet2!D 列で、データがないセルには、先頭に ' のみを入れて空文字列としておいて下さい
5.仕上げに、Sheet1のA列からL列まで全体をコピーして、Sheet3 または別のファイルに、「形式を選択して貼り付け」→「値」にチェックしてペースト実行
こちらの 値だけ貼り付けた物は、元のSheet2のデータ変更が反映されないが、Sheet1 のM列以降がない表として使える
Vlookupでマッチするデータがないと、 #N/A と表示されるが、これは、値コピーしたシートでなら、マクロで消せるだろう。
No.2
- 回答日時:
初歩的なことですが
Dim ID1, ID2, Code, EAN, VL, PU As String
ではなく、
Dim ID1 As String, ID2 As String, Code As String, EAN As String, VL As String, PU As String
と書きます。 ID1, ID2, Code, EAN, VL は型指定を省略したとみなされ Variant 型になります。
次に、
Dim row1, row2, c1, c2 As Integer
ではなく、
Dim row1 As Long, row2 As Long, c1 As Long, c2 As Long
と書きます。 Integer 型は -32768 から 32767 の範囲しか表現できません。つまり、 32767 行までしか処理できません。
Long 型を使用して下さい。 -2147483648 から 2147483647 の範囲を表現できるのですべての行を処理できます。
要約すると VB は Pascal ではありません。 VB の言語仕様(わりとひどい。といっても古い言語で古いPC(16ビット時代)との互換性だからしかたない)を確認して下さい。(とは言っても、公式資料も何年も修正されていない間違いがあるので注意が必要なんですが・・・。まあ、マイクロソフトが .NET を押しているからしかたないですね。 「VBA なんて使わず .NET 使ってね」な感じですからね。)
あと、用語が一般的でないのが気になりますが・・・。
一般的には row = 行 で、段ではあまり数えません。 Word の場合には paragraph = 段 とするのが適当ですが・・・。
本題ですが Excel DB 状態なので、データ接続(SQL)を使うのが一般的だと思います。
以下の手順の説明はExcel2007です。
まず、製品表1(Items シート)に ITEM_TABLE と名前を定義します。
手順は、製品表1の範囲を選択して(列名を含めて選択します)、数式タブの定義された名前グループの名前の定義をクリックします。新しい名前ダイアログで名前欄に「ITEM_TABLE」と入力してOKで名前を定義します。
次に、製品表2(uom シート)に UOM_TABLE と名前を定義します。
手順は同様です。
ファイルを保存します。(仮に W:\Excel DB.xlsb とします。バイナリ形式です。)
データタブの外部データの取り込みグループのその他のデータソースをクリックします。データ接続ウィザードをクリックします。
「ODBC DSN」を選択し次へ、「Excel Files」を選択し次へ、このファイルを選択します。
次へ、完了。
データのインポートダイアログでプロパティをクリック
接続のプロパティダイアログで「使用」タブの「バックグラウンドで更新する」のチェックを外します。
「定義」タブの「コマンド文字列」にSQLを指定します。
製品表1(ITEM_TABLE)は ID と NAME の2つの列を持ちます。以下、テストデータ
IDNAME
000128product1
0KV502product2
製品表2(UOM_TABLE)は ID, CODE, QTY, EAN, VL, WEIGHT, PU と PP の8つの列を持ちます。以下、テストデータ
IDCODEQTYEANVLWEIGHTPUPP
000128UNIT000128 U QTY000128 U EAN000128 U VL000128 U WEIGHT000128 U PU000128 U PP
000128BOX000128 B QTY000128 B EAN000128 B VL000128 B WEIGHT000128 B PU000128 B PP
000128PALLET000128 P QTY000128 P EAN000128 P VL000128 P WEIGHT000128 P PU000128 P PP
0KV502UNIT0KV502 U QTY0KV502 U EAN0KV502 U VL0KV502 U WEIGHT0KV502 U PU0KV502 U PP
0KV502PALLET0KV502 P QTY0KV502 P EAN0KV502 P VL0KV502 P WEIGHT0KV502 P PU0KV502 P PP
上記の構成に対して下のSQL文をコマンド文字列に指定します。
SELECT i.ID AS ITEM_ID, i.NAME AS ITEM_NAME,
MAX(IIF(u.CODE = 'UNIT', u.CODE, null)) As Unit_Code,
MAX(IIF(u.CODE = 'UNIT', u.EAN, null)) As Unit_EAN,
MAX(IIF(u.CODE = 'UNIT', u.VL, null)) As Unit_VL,
MAX(IIF(u.CODE = 'UNIT', u.PU, null)) As Unit_PU,
MAX(IIF(u.CODE = 'UNIT', u.QTY, null)) As Unit_QTY,
MAX(IIF(u.CODE = 'UNIT', u.WEIGHT, null)) As Unit_WEIGHT,
MAX(IIF(u.CODE = 'UNIT', u.PP, null)) As Unit_PP,
MAX(IIF(u.CODE = 'BOX', u.CODE, null)) As Box_Code,
MAX(IIF(u.CODE = 'BOX', u.EAN, null)) As Box_EAN,
MAX(IIF(u.CODE = 'BOX', u.VL, null)) As Box_VL,
MAX(IIF(u.CODE = 'BOX', u.PU, null)) As Box_PU,
MAX(IIF(u.CODE = 'BOX', u.QTY, null)) As Box_QTY,
MAX(IIF(u.CODE = 'BOX', u.WEIGHT, null)) As Box_WEIGHT,
MAX(IIF(u.CODE = 'BOX', u.PP, null)) As Box_PP,
MAX(IIF(u.CODE = 'PALLET', u.CODE, null)) As Pallet_Code,
MAX(IIF(u.CODE = 'PALLET', u.EAN, null)) As Pallet_EAN,
MAX(IIF(u.CODE = 'PALLET', u.VL, null)) As Pallet_VL,
MAX(IIF(u.CODE = 'PALLET', u.PU, null)) As Pallet_PU,
MAX(IIF(u.CODE = 'PALLET', u.QTY, null)) As Pallet_QTY,
MAX(IIF(u.CODE = 'PALLET', u.WEIGHT, null)) As Pallet_WEIGHT,
MAX(IIF(u.CODE = 'PALLET', u.PP, null)) As Pallet_PP
FROM `W:\Excel DB.xlsb`.ITEM_TABLE i, `W:\Excel DB.xlsb`.UOM_TABLE u
WHERE i.ID = u.ID
GROUP BY i.ID, i.NAME
「このブックの接続先は~」が表示されたら「はい」をクリックします。
出力先はとりあえず新規シートにでもしておいて下さい。
備考 XML形式よりもバイナリ形式の方がパフォーマンスがいいはずです。
SQL文のファイルパスを指定(変更)する箇所が2カ所なので注意して下さい。
ここまで、解説しておいてなんですが、Excelファイル(ADO、ODBC)に対してSQL文を実行するのではなく大本のデータベースに対してSQL文を実行してExcelにデータを返すようにしてください。
まあ、セキュリティの都合でExcelファイルにダウンロードしたデータベースのデータしか操作できないなら別ですが・・・。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBAコードが作動せず、どこに問題があるのか教えて下さい。 3 2023/06/13 13:20
- Visual Basic(VBA) VBA 別ブックからの転記の高速化について VBA 別ブックからの転記の高速化についてご教授下さい。 19 2022/07/26 13:07
- Visual Basic(VBA) VBA初心者です。 2 2022/10/10 11:52
- Excel(エクセル) マクロで最終行から上に検索を逆にしたい 1 2022/05/17 18:27
- Visual Basic(VBA) ローマ字、ハイフン付きの並び替え ローマ字抽出方法 Excelマクロ 4 2022/04/01 14:10
- Visual Basic(VBA) vbaを早くしたい 5 2022/09/09 10:58
- Excel(エクセル) なぜExit Subがあるのかわかりません 4 2023/02/19 12:34
- Visual Basic(VBA) ユーザーフォームに2つのコンボボックス銀行名「ConboBox1」支店名を「ConboBox2」とし 4 2022/08/03 17:34
- Visual Basic(VBA) VBAで時間(00:00形式)を積算(足し算)したい 1 2022/11/15 17:04
- Visual Basic(VBA) ExcelのVBAコードについて教えてください。 6 2022/06/08 12:55
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
楽しくて最高のプログラミング...
-
小学1年生の子です。塾に行かせ...
-
プログラミング
-
vba クリップボードクリアにつ...
-
ラズパイpico PIOモード
-
IT業界より楽に稼げる業界って...
-
pythonにてseleniumを使うも、...
-
プログラミング ソースコード
-
pythonで複数画像からgifを作る...
-
正規表現で複数マッチ条件で悩...
-
そのまま使っただけなのに・・...
-
Gitについて質問。 クローンし...
-
rpa化する言語としてら何があり...
-
httpリクエストの送り元の特定
-
プログラミングで例えばゲーム...
-
このURLで広告を出しているのは...
-
Pythonでgif画像が上手く作れない
-
windowsでテキストファイルの各...
-
Webサイト内に埋め込んだmp4動...
-
Pythonって何を意識した言語な...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
chatGPTで次々と質問をしていく...
-
昔のパソコン少年の武勇伝「店...
-
スカラーのベクトル微分
-
ハッシュテーブル(連想配列)が...
-
最新のプログラム言語を学ぶに...
-
pythonにてseleniumを使うも、...
-
Windowsのアプリ開発ってなんの...
-
vba クリップボードクリアにつ...
-
Google ColaboでGUI作成
-
sublimit textっていうエディタ...
-
フリーランスのエンジニアって...
-
ExcelVBAでFormulaR1C1を列範囲...
-
matplotlibで任意の角度の円弧...
-
そのまま使っただけなのに・・...
-
家庭のパソコンで Python の 環...
-
初心者powershellのPS1ファイル...
-
Webプログラムってネイティブア...
-
httpリクエストの送り元の特定
-
プログラミングで例えばゲーム...
-
rpa化する言語としてら何があり...
おすすめ情報