"取得と変換" の機能を使えば、1つの表を「データ別に複数のシートに分割」することができます
- 1つの表を、内容に応じた「複数の表(シート)に分割」する
- 「職員一覧表」を「部署別の職員一覧」にする
- "Power Query"を使って、「クエリ」を作成する
- 「表」を選択して、"PowerQueryエディタ"を立ち上げる
- "PowerQueryエディタ"が立ち上がる
- "クエリの一覧" を開く
- 部署「会計」の"クエリ"を作成
- 「会計」の"クエリ"を複製して、他の部署の「クエリ」を作成
- 複製(コピー)した「会計」のクエリを使って「営業部A」のクエリを作成
- クエリの名前を変更し、同じ操作で他の部署を作成(複製)する
- 作成した「全部署のクエリ」の"接続"を作成する
- 「データのインポート」で、"PowerQueryエディタ"との接続を作成
- 「元のデータ」のシートに、クエリとの接続が作成される
- 「クエリ」をExcelに読み込んで「ワークシート」を作成
- クエリがExcelに読み込まれ、「会計」のワークシートが作成
- 操作を繰り返して、すべての部署の「ワークシート」を作成
- 元のデータの変更内容が、作成した「ワークシート」に反映される
- "Power Query"を使って、「クエリ」を作成する
- 「複数の表」を「1つの表」に"合体"させる
1つの表を、内容に応じた「複数の表(シート)に分割」する
複数の情報が入った「1つの表」を「情報別に別々の表(シート)に分割」するのは手間のかかる作業です。
取得と変換の機能を使えば、簡単な操作で「表を情報別に別々の表に分割」することができます。
そして、取得と変換の機能で "分割" した「表」は「元の『表』の内容が変更されても最新の情報に更新する」ことができます。
「職員一覧表」を「部署別の職員一覧」にする
全部署が含まれた「全職員の一覧表」を「部署別の表」に変換します。
"Power Query"を使って、「クエリ」を作成する
「クエリ」は、"表(テーブル) のデータ" を「抽出」「並べ替え」「演算」「結合」「分割」することができる「仕組み」です。
"取得と変換" は、外部プログラム(アドイン)の "Power Query" を使って「クエリ」の操作をExcelの標準機能のように使えるようにしたものです。
「データの取得と変換」を実行すると、自動的に「Power Query エディタ」が立ち上がります。
※ "Power Query エディタ" が立ち上がると、"エディタ" を終了させるまでは一時的に "Excel" が使えなくなります
「表」を選択して、"PowerQueryエディタ"を立ち上げる
分割したい「表」を選択して "PowerQueryエディタ" を立ち上げます。
※「表」が、"テーブル化" されていない場合は「テーブル」に変換されます
- 分割する「表」のどこか1つのセルを選択(※ アクティブ状態にする)
- 「データ」タブを選択
- 「テーブルまたは範囲から」をクリック
- テーブルの作成ダイアログで、変換(分割)する「表」が正しく選択されているのを確認
- 「先頭行をテーブルの見出しとして・・・」にチェック
- 「OK」ボタンをクリック
表を「テーブル化」して作業効率を上げる - 事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介
"PowerQueryエディタ"が立ち上がる
選択した(アクティブセルがある)表が読み込まれた "Power Query エディタ" が立ち上がります。
この ”Power Query エディタ" を終了させる(閉じる)まで、Excelの操作はできません。
"クエリの一覧" を開く
画面の左にある「クエリ」の上の「 >」をクリックして、"クエリの一覧" を開く。
作成済みの「クエリ」の一覧は画面左端に表示されます、表示されない場合は「>」ボタンをクリックすることで展開することができます。
部署「会計」の"クエリ"を作成
今回作成する「クエリ」は、フィルター機能で「会計」のデータだけに絞り込む内容です。
作成した「クエリ」は、作成順に「テーブル1」「テーブル2」のように「テーブル名」が付けられています。
クエリの名前を「会計」に変更し、「部署名」の項目のフィルター機能を使って、「会計」のデータに絞り込みます。
- クエリの設定のボックスのプロパティで、名前を「会計」に変更します
- 「部署名」の項目のラジオボタン「▼」をクリック
- フィルターのダイアログが開くので「すべて選択」を解除
- 「会計」のボックスにチェックを入れて選択
- 「OK」ボタンをクリック
「会計」の"クエリ"を複製して、他の部署の「クエリ」を作成
作成した「会計」のクエリを複製して、他の部署のクエリを作成します。
- 「会計」のクエリを、右クリック
- 開いたダイアログで「複製」をクリック
複製(コピー)した「会計」のクエリを使って「営業部A」のクエリを作成
複製(コピー)した「会計」のクエリ「会計(2)」を使って、「営業部A」のクエリを作成します。
- 「部署名」のラジオボタン「▼」をクリック
- フィルターのダイアログで「営業部A」にチェックを入れる
- 「OK」ボタンをクリック
クエリの名前を変更し、同じ操作で他の部署を作成(複製)する
クエリの名前を「営業部A」に変更し、同じように複製(コピー)の操作を繰り返して他の部署のクエリを作成する。
- クエリの設定のプロパティの名前を「営業部A」に変更
- 「営業部A」を複製する
作成した「全部署のクエリ」の"接続"を作成する
操作(複製とフィルター操作)を繰り返して、すべての部署の 「クエリ」 を作成が終わったら、Power Query エディタを閉じて(終了して)"Excel" と "Power Query エディタ" の「接続」を作成します。
"クエリ" と "Excelのブック" の「接続」を作成することで、「クエリ」の内容を「テーブル」として表示することができます。
クエリと接続されている「テーブル」は、"更新" することで最新の状態になります。
- 「閉じて読み込む」のラジオボタン「▼」をクリック
- プルダウンリストから「閉じて次に読み込む」をクリック
「データのインポート」で、"PowerQueryエディタ"との接続を作成
"PowerQueryエディタ" が閉じ(終了し)、Excelの画面に戻ります。
「データのインポート」のダイアログが開くので、「接続の作成のみ」を選択します。
- ブックで表示する方法を「接続の作成のみ」にする
- 「OK」ボタンをクリック
「元のデータ」のシートに、クエリとの接続が作成される
「元のデータ(表)」があるシートに、クエリ(Power Query エディタ)との「接続」が作成されます。
この「接続」により、Excelの「シート」とPower Query エディタとの連携(関連付け)がされた状態になります。
「クエリ」をExcelに読み込んで「ワークシート」を作成
部署ごとの「接続」を使って、「クエリ」、の内容をExcelの「ワークシート」に "テーブル"として読み込んでいきます。
- 「会計」のクエリを "右クリック" する
- プルダウンリストから「読み込み先...」を選択
- データのインポートのダイアログで表示方法を「テーブル」にする
- データを返す先を「新規ワークシート」にする
- 「OK」ボタンをクリック
クエリがExcelに読み込まれ、「会計」のワークシートが作成
クエリとの "接続" の機能で、「会計」のクエリの内容が、Excelの "テーブル" に変換されて、新しい「会計」のワークシートが作成されます。
操作を繰り返して、すべての部署の「ワークシート」を作成
同じ操作を繰り返して、すべての部署のワークシートを作成していきます。
元のデータの変更内容が、作成した「ワークシート」に反映される
取得と変換で、分割(作成)した「テーブル(ワークシート)」は「クエリ」と "接続" された状態です。
クエリを「更新」することで、"クエリ" で作業(抽出)が行われ、「テーブル」は最新の内容になります。
営業部Aに「新入社員」を追加
「元の表(テーブル)」に、「営業部A」の「新入社員」のデータを追加して、クエリを「更新」すると、テーブル「営業部A」に追加されます。
クエリを「更新」する
「営業部A」のワークシートで「表」を選択し、クエリを「更新」する。
- 「営業部A」の「表」にカーソルを置く(アクティブセルを移動)
- 「クエリ」タブを選択(※ 「表(テーブル)」にアクティブセルがないと表示されない)
- 「更新」をクリック
今回の例のように、データの少ない「表」であれば手作業で表を分割する方が簡単かもしれません。
しかし、取得と変換(PowerQueryエディタ)を使って分割された「表(テーブル)」は、「元の表(データ)」の内容が変わっても変更内容が反映されます。
たとえば、会計ソフトから「仕訳データ」を書き出し(CSVで)た「表」から自動的に「業者別の表」を作成したりすることができます。
「複数の表」を「1つの表」に"合体"させる
さらに取得と変換を使えば、今回と逆の操作「部署別の表」から「全部署の一覧表」を作成することもできます。
複数の表(シート)を1つに "結合" する「取得と変換」(その1) - 事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介
複数の表(シート)を1つに "結合" する「取得と変換」(その2) - 事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介
- 1つの表を、内容に応じた「複数の表(シート)に分割」する
- 「職員一覧表」を「部署別の職員一覧」にする
- "Power Query"を使って、「クエリ」を作成する
- 「表」を選択して、"PowerQueryエディタ"を立ち上げる
- "PowerQueryエディタ"が立ち上がる
- "クエリの一覧" を開く
- 部署「会計」の"クエリ"を作成
- 「会計」の"クエリ"を複製して、他の部署の「クエリ」を作成
- 複製(コピー)した「会計」のクエリを使って「営業部A」のクエリを作成
- クエリの名前を変更し、同じ操作で他の部署を作成(複製)する
- 作成した「全部署のクエリ」の"接続"を作成する
- 「データのインポート」で、"PowerQueryエディタ"との接続を作成
- 「元のデータ」のシートに、クエリとの接続が作成される
- 「クエリ」をExcelに読み込んで「ワークシート」を作成
- クエリがExcelに読み込まれ、「会計」のワークシートが作成
- 操作を繰り返して、すべての部署の「ワークシート」を作成
- 元のデータの変更内容が、作成した「ワークシート」に反映される
- "Power Query"を使って、「クエリ」を作成する
- 「複数の表」を「1つの表」に"合体"させる