質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

2回答

23144閲覧

Excel 複数シートのテーブルをvlookupせずに統合する方法

zorac

総合スコア42

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

2クリップ

投稿2017/08/26 18:59

編集2017/08/28 14:56

###前提・実現したいこと
前提:環境は Excel 2010 + PowerBI です。 Accessは使用不可です。

イメージ説明

Sheet1~Sheet3にデータが別れており、店舗コードという一意な値が存在するものの、
その並びはばらばらの状態でデータが存在します。
こういったデータを統合してSheet4のようなひとまとまりのテーブルを作りたいと考えています。

現状vlookupで処理していますが、データ件数は万を超え、シート数は30前後と多く、
処理速度と作業精度の観点からvlookupを使わない方法を探しています。
データツールの統合やリレーションを試してみましたが、データが数値のみではない為に上手くいきません。
Excelの機能を使って上手く処理する方法はないものでしょうか。

最終的にはVBAで最新データの取得から集計作業まで、全体を自動化する事を考えていますが、集計機能を作り込んでいくと
かなり時間が掛かる為、Excel標準機能で済ませられないかどうか調べているという状況です。
VBAを使わなければどうにもならないという事であれば、どういう方法で処理するのが簡潔でしょうか。
Pivotが使えるものはPivotで処理してコピーするといったアイデアがあれば是非教えて下さい。

###下記コードで解決しました。

VBA

1Public Sub MergeTables() 2 Dim con As Object 3 Dim res As Object 4 5 Set con = CreateObject("ADODB.Connection") 6 Set res = CreateObject("ADODB.Recordset") 7 con.Provider = "Microsoft.ACE.OLEDB.12.0" 8 9 con.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1" 10 con.Open ThisWorkbook.FullName 11 12 Dim SQL As String 13 14 SQL = SQL & "select A.*,B.指標A,B.指標B,B.指標C,C.ランク,C.コメント " 15 SQL = SQL & "from ([Sheet1$] as A " '3つ以上の表をjoinする場合は括弧で囲まないと 16 SQL = SQL & "left join [Sheet2$] as B on A.店舗コード = B.店舗コード) " '「構文エラー:演算子がありません」となる 17 SQL = SQL & "left join [Sheet3$] as C on A.店舗コード = C.店舗コード" 18 19 res.Open SQL, con, 1, 1 20 21 Application.ScreenUpdating = False 22 23 Worksheets("Sheet4").Range("A2").CopyFromRecordset res 24 25 Application.ScreenUpdating = True 26 27 res.Close 28 con.Close 29End Sub

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

excelの機能というより、VBAでSQLを使った処理になりますが、
こちら(Excelの集計をSQLでおこなう方法。)が参考にならないでしょうか?

集計などもSQLの表現で行え、集計結果だけを書き出すのであれば、処理時間も改善されるでしょうし。

投稿2017/08/27 04:45

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

zorac

2017/08/28 03:33

saziさん ExcelでSQLを実行出来るとは知りませんでした。 私の要件を満たすのには完璧なソリューションのように思えます。 午後から試してみたいと思います。 ありがとうございます。
sazi

2017/08/28 04:12

かなり前ですが、私もAccessの使えない環境でCSVからデータ集計を行い結果表を生成するのに、似たような方法でVBAでのSQL発行を行ったことがあります。書式などの整形については雛形を用いるなどで手間でしたが、処理速度に関しては断然SQLを使用した処理が上だったように記憶しています。
zorac

2017/08/28 10:38

saziさん 教えて頂いた方法で無事解決できました。 速度も申し分ありません。ありがとうございました。
guest

0

【Excel】似た複数の表を瞬時にまとめる「統合」ワザ――集計やスッキリさせたいときに超便利 の記事が参考になりそうです。
初めに店舗マスターのワークシートを用意して、そこから統合をしていけば、全ての支店を網羅した複数のシートが統合できるでしょう。空欄の支店は後で削除なり非表示なりすればいいわけです。

投稿2017/08/26 23:06

seastar3

総合スコア2285

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

zorac

2017/08/27 01:47

ご回答ありがとうございます。 事前にマスタファイルを用意し「統合」を行うと、店舗名や地域などの 文字列データのセルは空白となり、数値データのみが統合されます。 ご紹介頂いた記事の通り再度試してみましたが、やはり上手くいきませんでした。 データツールに内包される機能である以上、当然の仕様と言われればそれまでですが、 統合の機能を見ていると、私の要件を達成する機能もあるんじゃないかと思わずには居られません。 引き続き宜しくお願いします。
seastar3

2017/08/27 08:30

zoracさん、ご期待通りでなくて申し訳ありません。 私も試してみましたが、文字列は空欄になりますね。 最低限、縦横のくくりは発生するので、あとのランクとコメントは、 重複がない前提でsheet2とsheet3のvlookupを使うのが自然だと思います。 もっとシートや項目が多いと大変だとは思いますが。
zorac

2017/08/28 03:31

seastar3さん わざわざ試して頂きありがとうございます。 今現在vlookupを多用して業務を行っていますが、作業者のExcelスキル不足もあり、時間と精度に問題があります。 どうにか標準機能で出来ないものかと、Pivotや統合を試してみたのですが、やはり演算ありきの機能なので文字列は期待通りには扱えませんでした。 裏技のようなテクニックがあるのではないかと、そんな思いで色々と検索しています。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問