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

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

ただいまの
回答率

89.12%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 10K+

zorac

score 42

前提・実現したいこと

前提:環境は Excel 2010 + PowerBI です。 Accessは使用不可です。

イメージ説明

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

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

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

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

Public Sub MergeTables()
    Dim con       As Object
    Dim res       As Object

    Set con = CreateObject("ADODB.Connection")
    Set res = CreateObject("ADODB.Recordset")
    con.Provider = "Microsoft.ACE.OLEDB.12.0"

    con.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
    con.Open ThisWorkbook.FullName

    Dim SQL   As String

    SQL = SQL & "select A.*,B.指標A,B.指標B,B.指標C,C.ランク,C.コメント "
    SQL = SQL & "from ([Sheet1$] as A "                                         '3つ以上の表をjoinする場合は括弧で囲まないと
    SQL = SQL & "left join [Sheet2$] as B on A.店舗コード = B.店舗コード) "     '「構文エラー:演算子がありません」となる
    SQL = SQL & "left join [Sheet3$] as C on A.店舗コード = C.店舗コード"

    res.Open SQL, con, 1, 1

    Application.ScreenUpdating = False

    Worksheets("Sheet4").Range("A2").CopyFromRecordset res

    Application.ScreenUpdating = True

    res.Close
    con.Close
End Sub
  • 気になる質問をクリップする

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+3

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/28 12:33

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

    キャンセル

  • 2017/08/28 13:12

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

    キャンセル

  • 2017/08/28 19:38

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

    キャンセル

+1

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/27 10:47

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

    キャンセル

  • 2017/08/27 17:30

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

    キャンセル

  • 2017/08/28 12:31

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

    キャンセル

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

  • ただいまの回答率 89.12%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる