【Excel】元表の変更を別表にも自動反映するなら、コピペじゃなくて「データの取得と変換」：Tech TIPS

元の表を加工せず、値を加えたり、順番を並べ替えたりした表をグラフ化したいようなことはないだろうか。こうした場合、元の表のシートをコピーして、コピーした表を加工するのが一般的だ。しかし、この方法では元の表の値を変更した場合、コピーした表に手動で反映しなければならない。実は、このような場合、「データの取得と変換」機能を使うとよい。その使い方を紹介しよう。

[塩田紳二，著]

連載目次

対象：Excel 2016／2019／365

「データの取得と変換」機能を使って元の表をそのままに値を加工する
元の表はそのままに、値を加工してグラフを作成したような場合、表をコピーするのではなく、「データの取得と変換」機能を使って元の表とリンクしたテーブルを作成するとよい。画面のデータは、「東京都 新型コロナウイルス感染症検査陽性者の状況」。

　「Microsoft Excel（エクセル）」で表をグラフにする際、そのままの値を使えればいいが、場合によっては幾つかの値を合計したり、並べ替えを行ったりした結果を用いたいこともあるだろう。

　このような場合、表を別のシートにコピーして、そのコピーで計算した結果を作成して、グラフ化することが多いのではないだろうか。しかし、表をコピーしてしまうと、元の表で値の変更が行われた場合、コピーした表にその変更を手動で反映しなければならない。場合によっては、コピーした表で行った作業が無駄になってしまうこともある。

　このような場合、元の表をコピーするのではなく、「データの取得と変換」機能を使って「テーブル」を別シートに作成し、そこからグラフを作るとよい。こうして作られたテーブルは、元のセル範囲と同一のデータを維持しながら、並べ替えや書式を別に設定できるからだ。

　グラフ以外にも、ソート順や書式設定、あるいは一部を隠した表など、1つの表から複数のバリエーションを作る場合にも利用できる。「データの取得と変換」を使って作られたテーブルは、元データと常に同じになるため、複数の表やグラフがあってもデータの修正、訂正は元データだけを編集すれば済むので、手動で別の表に反映する必要はない。

「テーブル」機能とは

　Excelには、「テーブル」と呼ばれる機能がある。「テーブル」は、セル範囲に対して設定する。テーブルにすると、集計行／列などを簡単に挿入できるようになるなどの特殊な機能が有効になる。こうした機能の1つに「データの取得と変換」を使ったテーブルの作成がある（作成手順は後述）。

　「リンクされたテーブル」の作成手順では、複数のシートやセル範囲がかかわるため、ここで説明のための用語を整理しておく。あるシートに表形式のデータが入っているセル範囲があるとする。以降では、これを「元表」、Excelの「データの取得と変換」を使って「元表」から別のシートに作成したテーブルを「リンクされたテーブル」と呼ぶ（あくまでも解説のための用語であり、Excelの用語ではないことに注意されたい）。「データの取得と変換」では、元表の状態を監視し、編集があれば、それを「リンクされたテーブル」に反映させる。

「リンクされたテーブル」とは（1）
元表から「リンクされたテーブル」を作ると、個別に並べ替えや書式の設定が行えるようになる。例えば、元表にデータを加える。

▼

「リンクされたテーブル」とは（2）
「リンクされたテーブル」にも反映され、データが追加される。

　こうして作られた「リンクされたテーブル」は、元のセル範囲に対して行った編集（セルの書き換え、行や列の挿入など）が反映される。しかし、「リンクされたテーブル」と「元表」は、個別に並べ替えや書式設定を行うことができる。

「元表」から「リンクされたテーブル」を作る手順

　では、具体的に表から「リンクされたテーブル」を作る手順を解説しよう。Microsoft 365版Excel（以下、Excel 365）とExcel 2016では基本的な手順は同じだが、表示されるダイアログに多少の違いがある。記事中の画面は、Excel 365のものである。

　既にシート上に表データがあるとして、これを「元表」にして「リンクされたテーブル」を作成する。それにはまず、「元表」を選択する。データが入った表全体を選択するのであれば、表内のセルを選択して［Ctrl］＋［A］キーを押せばよい。

　この状態で、［データ］タブにある［データの取得と変換］−［テーブルまたは範囲から］（Excel 365）／［取得と変換］−［テーブルから］（Excel 2016）をクリックする。

　Excel 365では、ここで元表の範囲を確認する［テーブルの作成］ダイアログが表示されるので、［OK］ボタンで先に進む。Excel 2016では、アクティブセルが表内にない場合のみ、このダイアログが表示される。

　その後「Power Queryエディター」の画面が表示される。ここでは、左ペインの領域にある元表の範囲が正しいかどうかを確認した後、右ペインの「クエリ設定」にある「プロパティ」欄の「名前」を設定する（デフォルトは「テーブル＜数字＞」）。ここで指定したものがリンクしたテーブルの名前になる。またExcel 365では、リンクしたテーブルが置かれる新規シートの名前にもなる。

　Excel 2016では、他のシートと同じく「Sheet＜数字＞」となる。名前以外は設定する必要がないので、Power Queryの［ホーム］タブの［閉じる］−［閉じて読み込む］を使ってPower Queryを閉じる。

「元表」から「リンクされたテーブル」を作る（1）
「元表」で「リンクされたテーブル」にしたいセル範囲を選択して、［データ］タブの［テーブルまたは範囲から］をクリックする。

▼

「元表」から「リンクされたテーブル」を作る（2）
［テーブルの作成］ダイアログが表示されるので、選択したセル範囲が指定されていることを確認し、［OK］ボタンをクリックする。

▼

「元表」から「リンクされたテーブル」を作る（3）
［Power Queryエディター］が起動する。必要なら「クエリの設定」の「名前」を変更し、［閉じて読み込む］をクリックする。

▼

「元表」から「リンクされたテーブル」を作る（4）
選択したセル範囲が新しいシートに読み込まれる。

　すると新しいシートが追加され、そこに「リンクしたテーブル」が作成される。このテーブルは、「元表」とリンクしており、「元表」に対して行った変更が自動的に反映される。