Excel

エクセルのクエリ結合で部署ごとのデータ(テーブル)を結合する

2020-06-22

エクセルで部署ごとに入力したデータを別のシートに結合したデータが欲しいんだけど、

と質問されたので、その方法を残すことにしました。

結果的にここでの方法は、使わないことになりました。理由は、入力者が多いため、思ってもいなかった操作方法をする可能性があり、入力方法の決まりごとが多くなるためです。

エクセルのバージョン:Office Home & Business 2019

やりたいこと・シートごとに入力したデータを別シートで結合

やりたいこと(イメージ)

  • 部署ごとにシートを分けてデータ入力(更新あり)
  • 結合したデータも更新したい

エクセル・シートごとの集計を別シートで集計する

地味にやるなら、追加されるごとにコピペ。実際のシートはもっと多いし、データももっとあり。それじゃ、大変だってことで、何か方法ある?という質問でした。

ぱっと見でそのままじゃできないなと思ったこと。

  • セルの装飾が多い
  • 結合セルがある
  • 項目名に空白あり
  • 表とタイトルの間に空白がない

これはよくあることで、多くの人は、装飾と結合するのが好き。データ加工するときに困ります。と言っても伝わることは少ない。

結合結果を別ブックに集計する方法

シートごとに入力したデータを別のブックのシートに集計する方法です。

集計元のデータをテーブル変換する

まずはじめに、データをテーブル変換しておきます。この方が集計時に楽です。

テーブル変換するには、注意点があります。例えば、結合セルはダメ等。

ここでの例では、2つのシートをテーブル変換します。

  • 1部売上のテーブル名:uriage1
  • 2部売上のテーブル名:uriage2

エクセル・シートごとの集計を別シートで集計する

(1)データ範囲をクリックし、リボン [ 挿入 ] タブ > [ テーブル ] グループ > [ テーブル ] をクリックします。

(2)「先頭行をテーブルの見出しとして使用する」にチェックを入れて、「OK」をクリックします。

(3)テーブル名を変更します。

エクセル・テーブル変換

(4)2部の売上もテーブル変換します。

2部売上のテーブル名:uriage2

エクセル・シートごとの集計を別シートで集計する

テーブル名は、そのままでも次の作業はできるけれど、名前をつけておくとわかりやすいです。

(5)保存してブックを閉じます。

データの取得

(1)新しいエクセルブックを開きます。

(2)リボン [ データ ] タブ > [ データの取得と変換 ] グループ >[ データの取得 ] > [ ファイルから ] > [ ブックから ] をクリックします。

エクセル・クエリ結合でデータ風景する

(3)売上データのブックを選択します。

→ ナビゲータが表示されます。

(4)「uriage1」を選択します。

エクセル・シートごとの集計を別シートで集計する

(5)画面右下の「読み込み」をクリックします。

エクセル・クエリ結合でデータ風景する

→ Sheet2に読み込まれます。

エクセル・シート別に入力されたデータの集計方法

(6)「uriage2」も同じ手順で読み込みます。

エクセル・シートごとの集計を別シートで集計する

「uriage2」読み込み後

エクセル・シートごとの集計を別シートで集計する

クエリと接続の画面

エクセル・シートごとの集計を別シートで集計する

クエリの結合でデータ結合

(1)リボン [ データ ] タブ > [ データの取得と変換 ] グループ >[ クエリの結合 ] > [ 追加 ] をクリックします。

エクセル・シートごとの集計を別シートで集計する

(2)テーブルを選択し、[OK]をクリックします。

  • 主テーブル:uriage1
  • 主テーブルに追加するテーブル:uriage2

エクセル・シートごとの集計を別シートで集計する

(3)閉じて読み込むをクリックします。

エクセル・シートごとの集計を別シートで集計する

設定は以上で終わりです。

Sheet4に結合結果が読み込まれます。

エクセル・シートごとの集計を別シートで集計する

売上1や売上のシートにデータの追加等した場合は、Sheet4のデータを更新すればOK。

データ更新

この方法の見づらい点

  • シートが増える
    閉じて読み込むの設定で、「接続の作成のみ」にすれば、シートは増えません。
  • もとの売上データと読み込んだデータがあり間違えるかもしれない

結果を同じブックに集計する方法・スッキリ版

集計結果は、同じブック内に追加し、クエリの接続結果をシートに読み込まない方法です。

まず、シート「売上1部」と「売上2部」のあるブックを開き、データをテーブル変換します。方法は、別ブックで集計する方法の最初に記載してます。

(1)データ範囲を選択します。例えば、セルA4。

(2)リボン [ データ ] タブ > [ データの取得と変換 ] グループ >[ テーブルまたは範囲から] をクリックします。

エクセルデータ取得と変換

→ PowerQueryエディターが開きます。

エクセル・シートごとの集計を別シートで集計する

(3)[閉じて読み込む]>[閉じて読み込む]をクリックします。

エクセル・シートごとの集計を別シートで集計する

(4)「接続の作成のみ」を選択し「OK」をクリックします。

エクセル・シートごとの集計を別シートで集計する

売上2も同様に設定します。

→ クエリの接続後

エクセル・シートごとの集計を別シートで集計する

結果はSheet1に読み込み。

エクセル・シートごとの集計を別シートで集計する

結果もシートに読み込まない方法もあり。

1部と2部のデータを更新した場合は、Sheet1でデータ内を選択し、右クリックで更新をクリックすればOK。

 

[st_toc]

-Excel

© 2020 パソコンメモ