【Excel】見出し列を外部参照にして、複数シートの見出しを共通化・一括変更可能にする：Tech TIPS

支店別の売り上げデータなど、同じ見出しを使って複数のシートを作成することもあるのではないだろうか。このような場合、支店名が変更になると、全てのシートを個別に変更しなくてはならなくなる。実は、セル参照などを使えば、テンプレートとなっているシートのみ変更するだけで、他のシートに自動的に反映させることができる。その方法を紹介しよう。

[小林章彦，デジタルアドバンテージ]

連載目次

対象：Excel 2013／2016／2019／2021／365

同じ見出し列を他のシートで使いたい場合の便利な方法
同じ見出し列を他のシートで使う場合、単純にコピーしてしまうと、値が変更になった場合、コピー先のシートでも手動で直さなければならなくなる。そのような場合、テンプレートとなっているシートだけを変更したら、他のシートにも自動的に反映されると便利だ。その方法を紹介しよう。

　「Microsoft Excel（エクセル）」で表を作成していると、支店名など別のシートの見出し列や行をそのまま使うことも多いだろう。その際、テンプレートとなっているシートの見出し列などをコピーして、新たに作成したシートに貼り付けているのではないだろうか。

　ただ、見出し列などをコピーしてしまうと、項目名が変更された場合に全てのシートを変更しなければならなくなる。項目名が変更される可能性がある場合は、見出し列をコピーせずに参照するようにするとよい。その方法を紹介しよう。

セル参照で別シートのデータを自動反映する

　最も簡単なのは、セル参照を使う方法だ。新しいシートで「=Sheet1!A3」（「A3」セルを参照する場合）といった具合に入力し、テンプレートとなるシートの見出し列を参照するようにすればよい。

　後は、オートフィル機能で見出し列に対してコピーすれば、テンプレートの見出し列が反映できる。テンプレート側（参照元）の支店名などを変更すれば、新しいシート側（参照先）にもその変更が自動的に反映される。

セル参照で別シートのデータを自動反映する（1）
「Sheet1」がテンプレート用シートで、「Sheet2」がこれから表を作る新しいシートである。まず「Sheet2」の見出し列の先頭セルに、参照先のセル番号を指定する。

▼

セル参照で別シートのデータを自動反映する（2）
オートフィル機能を使って、見出し列にコピーする。ただし、参照先が空白セルの場合、画面のように「0」が表示されてしまうので注意してほしい。

▼

セル参照で別シートのデータを自動反映する（3）
テンプレートとなっているシートで値を変更する。

▼

セル参照で別シートのデータを自動反映する（4）
参照先の新しいシートの値も自動的に変更される。

　ただし、参照元のセルに空白があったときには、参照先のセルに「0」と表示されてしまう点に注意してほしい。参照先に「0」を表示させたくない場合は、IF関数を使い、「=IF(Sheet1!A3="","",Sheet1!A3)」としておけばよい。

　こうしておけば、項目数が増えた場合でも自動的に反映することが可能だ。

IF関数を使って空白セルであっても「0」を表示させないようにする
IF関数を使うことで、空白セルがあった場合でも「0」にならないようにできる。

［リンク貼り付け］で貼り付けてデータを自動反映する

　項目数が決まっているのであれば、貼り付け時に［リンク貼り付け］を選択することで、データを自動反映させることが可能だ。

　テンプレートとなるシートの見出し列を選択し、［Ctrl］＋［C］キーでコピーする。新しいシートの見出し列の先頭セルを右クリックし、［貼り付けオプション］−［リンク貼り付け］を選択する。これで、見出し列が新しいシートに反映されるようになる。

　［リンク貼り付け］で貼り付けたセルを見ると分かるが、実態はセル参照を行っている。そのため、参照元のセルに空白があったときには、参照先のセルに「0」と表示されてしまう点に注意してほしい。

［リンク貼り付け］で貼り付けてデータを自動反映する（1）
テンプレートとなっているシートで、見出し列を選択し、［Ctrl］＋［C］キーでコピーする。

▼

［リンク貼り付け］で貼り付けてデータを自動反映する（2）
新しいシートの見出し列の先頭セルを右クリックし、メニューで［貼り付けオプション］−［リンク貼り付け］を選択する。

▼

［リンク貼り付け］で貼り付けてデータを自動反映する（3）
コピーしたセル範囲が貼り付けられる。貼り付けられたセルを見ると分かるが、実態はセル参照となっている。

　また、セルの書式は反映されないので、［リンク貼り付け］で貼り付けた後、再度、右クリックメニューで［貼り付けオプション］−［書式設定］を選択して、セルの書式設定をコピーするとよい。

［名前の定義］でデータを自動反映する

　［名前の定義］を使うことで、見出し列などを自動反映可能な形でコピーできる（「名前の定義」については、Tech TIPS「Excelの「名前の定義」でセルの範囲に名前を付ける」参照のこと）。テンプレートとなるシートの見出し列を選択した状態で、右クリックメニューの［名前の定義］を選択する。［新しい名前］ダイアログが表示されるので、選択したセル範囲に付ける名前を「名前」欄に入力する。「範囲」は［ブック］を選択しておき、「参照範囲」欄が選択しているセル範囲になっているかを確認して、［OK］ボタンをクリックする。参照範囲は、ここで変更することも可能だ。

　新しいシートの見出し列の先頭セルに「=＜名前＞」と入力すれば、「名前の定義」で選択したセル範囲が反映される。ただし、この場合も、名前を付けたセル範囲に空白があったときには、参照先のセルに「0」と表示されてしまう点に注意してほしい。

［名前の定義］でデータを自動反映する（1）
テンプレートとなるシートで見出し列を選択し、右クリックメニューの［名前の定義］を選択する。

▼

［名前の定義］でデータを自動反映する（2）
［名前の定義］ダイアログが表示されるので、「名前」欄でセル範囲に名前を付ける。また、「範囲」欄は、デフォルトの［ブック］のままにしておく。

▼

［名前の定義］でデータを自動反映する（3）
新しいシートの見出し列の先頭セルに「=＜名前＞」を入力すると、名前を定義したセル範囲がコピーされる。

　「名前の定義」を使って参照した場合も、参照元の支店名などを変更すれば、参照先も自動的に変更される。また、名前を付けたセル範囲内に行を追加した場合も、新しいシート側に反映される。ただ、「名前の定義」で設定したセル範囲の外に支店名を追加したような場合は反映されないので注意してほしい。

［名前の定義］でデータを変更する（1）
名前を定義したセル範囲内の値を変更する。

▼

［名前の定義］でデータを変更する（2）
参照している新しいシートの値も自動的に変更される。

「名前の定義」を編集・削除する

　「名前の定義」は、［数式］タブの［名前の管理］アイコンをクリックして表示される［名前の管理］ダイアログで編集したり、削除したりすることが可能だ。参照範囲もここで変更できるので、セル範囲外に追加した支店名などを反映したい場合は、ここで参照範囲を変更すればよい。

「名前の定義」を編集・削除する（1）
名前の定義で設定したセル範囲を編集したり、削除したりするには、［数式］タブの［名前の管理］をクリックして開く、［名前の管理］ダイアログで行う。

▼

「名前の定義」を編集・削除する（2）
［名前の管理］ダイアログが開いたら、操作したい「名前」を選択する。削除したい場合は、［削除］ボタンをクリックする。参照範囲を変更したい場合は、「参照範囲」欄を編集すればよい。

▼

「名前の定義」を編集・削除する（3）
セル範囲を変更したことで、空白セルが範囲外となり、「0」が表示されなくなった。項目が増えた場合は、セル範囲を広げることで、項目を追加することもできる。