[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『多対多の場合のデータの統合』(く)
下記のようなデータがあります。
≪シート1≫
A列 部品番号(親)
B列 納期
C列 計画数
≪シート2≫
A列 部品番号(親)
B列 子部品番号
C列 構成数量
上記二つのシートを新しいシート(シート3)に統合し、シート2の子部品番号の
引落計画を作成したいのですが、方法はありますでしょうか?
SQLであれば、恐らくCROSS JOIN結合等で解決するのでしょうが・・・
多対多の組み合わせで中々実現できず、困っております。
実現したいシート3は下記のとおりです。
1、部品番号(=親部品番号)
2、子部品番号
3、Bシート1のB列の納期
4、引落計画数(シート1の計画数×シート2の構成量)
どなたかアドバイスをお願いします
出来ましたら、今回はvbaではなく関数等で解決できたらありがたいです。
< 使用 Excel:Excel2010、使用 OS:Windows7 >
シート1の部品番号は重複はないのですか? であれば、 基本シート2をコピーして、必要な情報を VLOOKUP で参照すればできそうだと思いますが、 それではできない点があるでしょうか。
(Mook) 2015/04/19(日) 22:42
両方複数あるので、どっちのシートを基調にしても、VLOOLKでは、一番上の行しか返さず、
不都合があるのです。
(く) 2015/04/19(日) 22:50
うーん、シート1で同じ部品番号が複数ある場合、 シート3で参照する納期や計画数は複数あるうちのどれを参照するのでしょうか。
(Mook) 2015/04/19(日) 23:06
(く) 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ではなく関数等で解決できたらありがたいです。 」
とのことで、関数専門家さんからの回答を楽しみにしているんですが、どうでしょうね。 なぜ、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
>ウッシさんへ Excelに繋ぐなら 質問者のExcelバージョンが2010なので 接続文字列がちょっと 心配です。 現行バージョンで行うのなら・・・。 ちょっと気になったので。
ファイルメーカーって 使ったことがありませんが SQLが使えるのですね!!
(ichinose) 2015/04/21(火) 05:46
当方も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
≪シート1≫≪シート2≫
上記二つのシートを新しいシート(シート3)に統合
という事でしたので自ブックでもいいのかと思ってました。
普段使わないので、開いていれば Jetでも可能というのは知りませんでした。
SQL文で解決しそうなので使ってみただけです。
閉じているxlsxやxlsm拡張子等 の新仕様ブックに対してデータの抽出だけで、
自ブックのシート3に書き込むとすれば、
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
アクセスと違って、フィールドの中にに書き出される形なので、
これをいったん外にエクスポートして、再びテーブルにインポートという
面倒くさい処理になってしまいます。
ファイルメーカーの専門家ならばもっと良い方法があるかもしれません。
VBAなどが詳しくない私はファイルメーカーのスクリプト(マクロみたいなもの)で
簡単に自動化できるので、重宝していますが、今回みたいに
応用が利かない場合はシンドイです・・・
MOOKさん
よくわからずCROSS jOINと書きましたが、ウッシさんのご教授で
やってみたところ、INNER JOINでよかったです。
みなさん
コメントを頂き、ありがとうございました。
(く) 2015/04/24(金) 13:35
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.