- 公開日：
エクセルでシートを連動させる関数
この記事では、エクセルでシートを連動させる関数をご紹介します。
「データを変更すると参照先のシートにも連動して更新される関数」「シート名が特定のセルに連動して反映される関数」の2つの関数を紹介します。
それぞれ例を用いて説明しているので、ぜひ日常の業務でご活用ください。
エクセルでシートを連動させる関数
エクセルでシートを連動させる関数をご紹介します。
INDIRECT関数
データを変更すると参照先のシートにも連動して更新される関数には、「INDIRECT関数」があります。
INDIRECT関数は、セルの参照を文字列で指定する関数です。セルだけではなく、他のシートも参照することができます。
書式は「=INDIRECT(参照文字列,[参照形式])」のように記述します。
使い方については、以下をご参照ください。
ExcelのINDIRECT関数の使い方｜セルや他のシートを参照する
CELL関数
シート名が特定のセルに連動して反映される関数に、「CELL関数」もあります。
CELL（セル）関数は、Excelにおいてセルの内容や書式、位置等を調べることが出来る関数です。
書式は「=CELL(検査の種類,[参照])」のように記述します。
以下の記事で、使い方について解説しています。
INDIRECT関数でシートを連動させる方法
以下、INDIRECT関数を使ってシートを連動させる方法をご紹介します。
今回は、「各店舗の売上比較表」シートのB列に「A店」「B店」「C店」シートのB12セルの売上データを表示します。
①「各店舗の売上比較表」シートを選択します。
②B3セルに「=INDIRECT(」と入力します。
INDIRECT関数の引数「参照文字列」として、シート名とB12セル（"A店!B12"）を指定します。
シート名はA列を参照できるため、「=INDIRECT(」に続いて「A3&」と入力します。
「&」を付けることで、後の文字列が結合されます。
「=INDIRECT(A3&」に続いて「"!B12")」を入力し、Enterを押します。
「!」を付けることで、シートを参照しているという意味になります。
結果（例：171）が表示されました。
「=A店!B12」でも結果は同じですが、INDIRECT関数を使えば1つずつ参照せず数式をコピーできるので便利です。B3セルのフィルハンドルをダブルクリックまたは下にドラッグします。
「A店」「B店」「C店」シートに入力されている売上データが「各店舗の売上比較表」シートに反映されました。
試しにデータを変更してみます。
①「A店」シートを選択し、②B12セルに「200」と入力し変更します。
「各店舗の売上比較表」シートを選択して確認すると、データの変更に伴って、自動的にB3セルのデータも「200」に変更されていることがわかります。
INDIRECT関数を使って、別シートのデータを連動させて表示することができました。
ドロップダウンリストでシートを連動させる
INDIRECT関数の活用方法として、ドロップダウンリストでシートを連動させることができます。
以下、ドロップダウンリストでシートを連動させる方法をご紹介します。
今回は、「各店舗の売上合計」シートのA3セルに設定するドロップダウンリストで店舗名を切り替えて、「A店」「B店」「C店」シートの売上データを表示されるようにします。
①「各店舗の売上合計」シートを選択します。
②ドロップダウンリストを設定したいセル（例：A3セル）、③データタブ、④「データの入力規則」の順に選択します。
「データの入力規則」ダイアログボックスが表示されます。
①「入力値の種類」でリストを選択し、②「元の値」にそれぞれの店舗名（例：A店,B店,C店）を入力します。
③「OK」ボタンを押します。
店舗名のドロップダウンリストが設定できました。
①ドロップダウンリストの右側の三角ボタンを押して、②任意の選択肢（例：A店）を選択しておきましょう。
INDIRECT関数を使って、A3セルで選択した店舗名のシートを参照する数式を入力します。
B3セルに「=INDIRECT(A3&"!B12")」と入力します。
結果（例：171）が表示されました。
試しにドロップダウンリストの店舗名を変更してみます。
①ドロップダウンリストの右側の三角ボタンを押して、②別の店舗名（例：B店）を選択します。
店舗名の変更に伴って、参照されるシートが切り替わり、データが変更されました。
ドロップダウンリストの選択肢に応じて、別シートのデータを連動させて表示することができました。
CELL関数でシートを連動させる方法
以下、CELL関数を使ってシートを連動させる方法をご紹介します。
なお、CELL関数のほかにLEN関数、FIND関数、RIGHT関数も使用するので、それぞれの使い方については次の記事をご確認ください。
ExcelのLEN関数の使い方｜半角と全角の区別なく文字数を返す
ExcelのFIND関数の使い方｜文字列が最初に現れる位置を検索
ExcelのRIGHT関数の使い方｜文字列の右側から指定した文字数を返す
今回は、シート名「A店」をB1セルに表示します。
それぞれの関数をわかりやすく説明するためにエリアを設けます。
CELL関数を使って、ファイルパスを取得します。
G2セルに「=CELL("filename")」と入力し、Enterを押します。
パスを取得できました。次に、LEN関数を使ってパス全体の文字数を数えます。
G3セルに「=LEN(G2)」と入力し、Enterを押します。
パスの総数が返りました。次に、FIND関数を使って「"]"」の位置を調べます。
G4セルに「=FIND("]",G2)」と入力し、Enterを押します。
「"]"」の位置が返りました。次に、RIGHT関数を使ってパスの総数（例：69）から"]"の位置（例：67）を差し引いた文字分を取り出します。
G5セルに「=RIGHT(G2,G3-G4)」と入力し、Enterを押します。
すると、シート名「A店」が返されます。
一つの数式でシート名を表示したい場合は、任意のセル（例：B1セル）に「=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))」と入力し、Enterを押します。
シート名「A店」が表示されました。試しに、シート名を変更してみます。
「やまだ店」と入力し、Enterを押します。
シート名の変更に伴って、自動的にB1セルも「やまだ店」に変更されていることがわかります。
CELL関数を使って、シート名を連動させて特定のセルに反映することができました。