エクセルで部署ごとに入力したデータを別のシートに結合したデータが欲しい。
と質問を受けました。
結果的にここでの方法は使わないことになりましたが、記録を残すことにしました。使わない理由は入力者が多いため、思ってもいなかった操作方法をする可能性があり、入力方法の決まりごとが多くなるためです。
エクセルのバージョン：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。