[[20150419202445]]　『多対多の場合のデータの統合』（く）　ページの最後に飛ぶ

『多対多の場合のデータの統合』（く）

下記のようなデータがあります。

≪シート１≫
A列　部品番号（親）
B列　納期
C列　計画数

≪シート2≫
A列　部品番号（親）
B列　子部品番号
C列　構成数量

上記二つのシートを新しいシート（シート3）に統合し、シート2の子部品番号の
引落計画を作成したいのですが、方法はありますでしょうか？

SQLであれば、恐らくCROSS JOIN結合等で解決するのでしょうが・・・
多対多の組み合わせで中々実現できず、困っております。

実現したいシート3は下記のとおりです。
１、部品番号（=親部品番号）
２、子部品番号
３、Bシート1のB列の納期
４、引落計画数（シート1の計画数×シート2の構成量）

どなたかアドバイスをお願いします
出来ましたら、今回はvbaではなく関数等で解決できたらありがたいです。

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

 シート１の部品番号は重複はないのですか？
 であれば、
 基本シート2をコピーして、必要な情報を VLOOKUP で参照すればできそうだと思いますが、
 それではできない点があるでしょうか。

（Mook） 2015/04/19(日) 22:42

MOOK様
ありがとうございます。
シート１の部品番号は重複します。
異なる納期、数量にて何レコードか存在する場合があるし、１レコードのこともあります。
シート２についても親に対して、子が複数存在します。

両方複数あるので、どっちのシートを基調にしても、VLOOLKでは、一番上の行しか返さず、
不都合があるのです。
（く） 2015/04/19(日) 22:50

 うーん、シート１で同じ部品番号が複数ある場合、
 シート３で参照する納期や計画数は複数あるうちのどれを参照するのでしょうか。

（Mook） 2015/04/19(日) 23:06

MOOK様
すべてです。
そこで煮詰まっているのですが・・・
例えば、
1行目 (1)部品番号 (1)子部品番号 (1)シート1のB列の納期　(1)引落計画数
2行目　(1)部品番号 (2)子部品番号 (1)シート1のB列の納期　(1)引落計画数
3行目　(1)部品番号 (3)子部品番号 (1)シート1のB列の納期　(1)引落計画数
4行目　(1)部品番号 (1)子部品番号 (2)シート1のB列の納期　(2)引落計画数
5行目　(1)部品番号 (2)子部品番号 (2)シート1のB列の納期　(2)引落計画数
6行目　(1)部品番号 (3)子部品番号 (2)シート1のB列の納期　(2)引落計画数
7行目　(2)部品番号 (1)子部品番号 (1)シート1のB列の納期　(1)引落計画数
8行目　(2)部品番号 (2)子部品番号 (1)シート1のB列の納期　(1)引落計画数
9行目　(2)部品番号 (1)子部品番号 (2)シート1のB列の納期　(2)引落計画数
10行目　(2)部品番号 (2)子部品番号 (2)シート1のB列の納期　(2)引落計画数
11行目　(2)部品番号 (1)子部品番号 (3)シート1のB列の納期　(3)引落計画数
12行目　(2)部品番号 (2)子部品番号 (3)シート1のB列の納期　(3)引落計画数
13行目　(3)部品番号 (1)子部品番号 (1)シート1のB列の納期　(1)引落計画数
14行目　(4)部品番号 (1)子部品番号 (1)シート1のB列の納期　(1)引落計画数
15行目　(4)部品番号 (1)子部品番号 (1)シート1のB列の納期　(1)引落計画数
・
・
・・・という風にシート１、２ともランダムな数字を持って組み合わせをしなければならない形になります。

（く） 2015/04/20(月) 07:18

こんにちは

ADO2.8に参照設定しておいて、

Sub test()

    Dim cn    As ADODB.Connection
    Dim rs    As ADODB.Recordset
    Dim r     As Long
    Dim c     As Long
    Dim sSQL  As String

    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Extended Properties") = "Excel 8.0"
        .Open ThisWorkbook.FullName
    End With
    sSQL = ""
    sSQL = sSQL & "SELECT S2.部品番号, S2.子部品番号, S1.納期, S1.計画数*S2.構成数量 AS 引落計画数 "
    sSQL = sSQL & "FROM [Sheet2$] AS s2 INNER JOIN [Sheet1$] AS s1 ON s2.部品番号 = s1.部品番号;"
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open sSQL, cn, adOpenDynamic, adLockReadOnly, adCmdText

    With Worksheets("Sheet3")
        .Cells.ClearContents
        For c = 1 To rs.Fields.Count
            .Cells(1, c).Value = rs.Fields(c - 1).Name
        Next c

        .Cells(2, 1).CopyFromRecordset rs
    End With

    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
End Sub

シートのフィールド名は適宜変更して試してみて下さい。

（ウッシ） 2015/04/20(月) 08:55

 横から失礼します

 「出来ましたら、今回はvbaではなく関数等で解決できたらありがたいです。 」

 とのことで、関数専門家さんからの回答を楽しみにしているんですが、どうでしょうね。
 なぜ、ＶＢＡじゃいやなんですか？

（β） 2015/04/20(月) 09:01

ウッシさん
大変ありがとうございます。
試してみます。

βさん
実は、上記を他のデータベースソフト（ファイルメーカー）の中で実現できず、
手詰まってしまい、もしエクセルで関数等を使って実現できたら、
その手法を応用しようと考えているためです。

（く） 2015/04/20(月) 09:19

ファイルメーカーについては、こちらの掲示板と同様なサイトが二つありますが、ご存知ないですか。

みんなで助け合おう初心者のFileMaker Pro Q&A

初心者のFileMaker Pro Q&A

よく似た名前ですが、別々の質問・回答式の掲示版。名前で検索されたらどうですか。

（三輪車） 2015/04/20(月) 10:09

こんにちは

今のファイルメーカーならRDBなので、

    sSQL = ""
    sSQL = sSQL & "SELECT S2.部品番号, S2.子部品番号, S1.納期, S1.計画数*S2.構成数量 AS 引落計画数 "
    sSQL = sSQL & "FROM [Sheet2$] AS s2 INNER JOIN [Sheet1$] AS s1 ON s2.部品番号 = s1.部品番号;"

のシート名をテーブル名に変更してSQL実行するだけでも出来そうな気がしますけど、ダメでしょうか？

（ウッシ） 2015/04/20(月) 10:19

三輪車さん
存じ上げています。

ウッシさん
ありがとうございます。
試してみます。
EXECUTESQLという関数があり、トライしてみましたが、
データがうまく引っ張ってこれませんでした。

上記を参考にもう一度トライしてみます。
（く） 2015/04/20(月) 10:26

 かゆいところに手が届かない FileMaker ですが、ExecuteSQL は Full Outer Join
 をサポートしていないようです。

http://ywc.com/filemaker/?p=95
https://fmhelp.filemaker.com/docs/13/ja/fm13_sql_reference.pdf

 Inner Join であれば、可能のようですが。

（Mook） 2015/04/20(月) 10:37

MOOK様
調べていただき、ありがとうございました。
上記確認いたします。
（く） 2015/04/20(月) 11:50

 >ウッシさんへ
 Excelに繋ぐなら
 質問者のExcelバージョンが2010なので
 接続文字列がちょっと　心配です。　現行バージョンで行うのなら・・・。
 ちょっと気になったので。

 ファイルメーカーって　使ったことがありませんが　SQLが使えるのですね！！

（ichinose） 2015/04/21(火) 05:46

おはようございます、ichinose さん

当方もExcel2010なのですが、動いちゃったのでそのまま記載しました。

動かなかったら、"Microsoft.Jet.OLEDB.4.0" とか、"Excel 8.0"の辺りを
修正しないとって思ってたのですが。

Excel2010、使っている方に試してみて欲しいです。
動かないという報告頂ければコード直します。

Excel2013になると、"Microsoft.ACE.OLEDB.12.0" とか、"Excel 12.0"
にしないとダメかとは思います。

（ウッシ） 2015/04/21(火) 07:46

ウッシさん
みなさん
コメントありがとうございます。

なるべくFILEMAKERの中で解決したかったので、
ウッシさんに教わったINNER JOINでFILEMAKERの中で
Execute関数を再度作成にトライ→無事にできました。

EXCEL上で動かす教えていただいたコードは、取り組んだことがないので、
じっくり勉強させていただきます！
取り急ぎお礼申し上げます。
（く） 2015/04/21(火) 10:57

こんにちは、（く）さん

出来て良かったですね。

FILEMAKERは使った事がないので評価版入れて、QueryBuilder使ってExecuteSQL 作って試したのですが、
FILEMAKER自体の使い方が良く分からなくて断念してました。

FILEMAKERの中でExecute関数使う方法ってどこかにアップされてますか？

http://notonlyfilemaker.com/wp-content/uploads/2014/01/The-Missing-FM12-ExecuteSQL-Reference-ja.pdf
とか有っても、それ以前の使い方が分からないです。

なんかネット上にあるFILEMAKERに関するものは分かりにくい情報ばかりですね。

（ウッシ） 2015/04/21(火) 11:20

 ExecuteSQL はスクリプトの中の関数なので、レイアウトだけでは使用できませんね。
 スクリプトの書き方も独特ですし、関数の結果の取得のしかたもちょっと慣れが必要です。

 とりあえず、関数リファレンスです。
http://www.filemaker.com/help/12/fmp/jp/html/func_ref3.33.6.html

 ところで、見返してみると当初は Cross Join のような結果を求めていたようですけれど、
 Inner Join でよかったのかな？

（Mook） 2015/04/21(火) 11:41

 本編解決後ですが・・・。

 >当方もExcel2010なのですが、動いちゃったのでそのまま記載しました。

 自ブックに対して　というより開いているブックに対してという事ですよね？
 提示されたコード自体はThisworkbookですから、当然開いていますから、Jetでも可能です。
 (XLS拡張子なら　閉じていてもJetでも可能ですよね）

 心配と申し上げたのは、閉じているxlsxやxlsm拡張子等　の新仕様ブックに対してのJet記述に対してということです。

 本来、ADOで開いているブックに対して接続というのは、実仕事では考えていないので
 閉じたブックに対しての接続のことを対象としていました。

 閉じている新仕様ブックに対しては、Jetでは　エラーになりますよ！！

 Win7　Excel2010（32Bit）で確認しました。

 FileMakerねえ、、機能云々は全く知りませんが、Office/VBAでお仕事を頂く当社の強力なライバルアプリ
 です。一番はCobolプログラマですが、最近まわりで見かけなくなってきました。

（ichinose） 2015/04/22(水) 06:01

こんにちは

≪シート１≫≪シート2≫
上記二つのシートを新しいシート（シート3）に統合

という事でしたので自ブックでもいいのかと思ってました。

普段使わないので、開いていれば Jetでも可能というのは知りませんでした。

SQL文で解決しそうなので使ってみただけです。

閉じているxlsxやxlsm拡張子等　の新仕様ブックに対してデータの抽出だけで、
自ブックのシート３に書き込むとすれば、

Sub test1()

    Dim cn    As ADODB.Connection
    Dim rs    As ADODB.Recordset
    Dim r     As Long
    Dim c     As Long
    Dim sSQL  As String

    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0; HDR=YES"
        .Open ThisWorkbook.Path & "\目的のファイル.xlsx"
'        .Open ThisWorkbook.FullName
    End With
    sSQL = ""
    sSQL = sSQL & "SELECT S2.部品番号, S2.子部品番号, S1.納期, S1.計画数*S2.構成数量 AS 引落計画数 "
    sSQL = sSQL & "FROM [Sheet2$] AS s2 INNER JOIN [Sheet1$] AS s1 ON s2.部品番号 = s1.部品番号;"
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open sSQL, cn, adOpenDynamic, adLockReadOnly, adCmdText

    With Worksheets("Sheet3")
        .Cells.ClearContents
        For c = 1 To rs.Fields.Count
            .Cells(1, c).Value = rs.Fields(c - 1).Name
        Next c

        .Cells(2, 1).CopyFromRecordset rs

    End With

    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
End Sub

で、出来ましたけど、書き込みとかまでは使った事もないです。

（ウッシ） 2015/04/22(水) 07:48

ウッシさん
すみません。
掲示板を見れていませんでした。
上記私も関数がうまくいかずに四苦八苦していたのですが、
ファイルメーカーのSQL用の教材があったので、これを改造して、
書き出せました。

アクセスと違って、フィールドの中にに書き出される形なので、
これをいったん外にエクスポートして、再びテーブルにインポートという
面倒くさい処理になってしまいます。
ファイルメーカーの専門家ならばもっと良い方法があるかもしれません。

VBAなどが詳しくない私はファイルメーカーのスクリプト（マクロみたいなもの）で
簡単に自動化できるので、重宝していますが、今回みたいに
応用が利かない場合はシンドイです・・・

MOOKさん
よくわからずCROSS jOINと書きましたが、ウッシさんのご教授で
やってみたところ、INNER　JOINでよかったです。

みなさん
コメントを頂き、ありがとうございました。

（く） 2015/04/24(金) 13:35

コメント返信：

[ 一覧(最新更新順) ]

[[20150419202445]] 『多対多の場合のデータの統合』（く） ページの最後に飛ぶ

[[20150419202445]]　『多対多の場合のデータの統合』（く）　ページの最後に飛ぶ