【excel】シート名を参照、自動でセルに表示させる/INDIRECT、CELL等

excelは表計算ソフトですが、非常に複雑な作業もできて効率的なツールです。そのためexcelを使いこなせることがビジネスマンのひとつのスキルとも言えます。excelにはいろんな関数がありますが、ここで紹介したいのはindirect関数やcell関数です。参照を行うために変数を使うという発想は効率化のためにはとても大切になってきます。関連するいくつかのexcel関数を組み合わせることでも手作業を大幅に省略することが可能になるでしょう。関数を使えばセル内に書かれたシート名を取得し、そのシート内のセル情報を参照するということもできます。また、シート名を自動で取得することも可能になります。

excel関数内の文字列は「”」で囲む

これから紹介するindirect関数やcell関数では文字列を扱うことになるため特別な注意が必要となります。文字列は、excel関数の中では「”」(ダブルクォーテーション)で囲う必要があります。excelを使う上で基本的なルールですが忘れてしまいがちなポイントでもあります。適切な場所でダブルクォーテーションを使っていかなければエラーが生じてしまいます。

excelで文字列をつなぐには「&」が必要

excelでは文字列にダブルクォーテーションをつけるということの他、「&」で連結するというルールもあります。セルの参照をする場合にも重要になります。例えば、 = ”ABC” & ”DEFG” とセル内に記述すれば、 ABCDEFG と表示されるようになります。

便利なexcel関数はどんどん覚えよう

excelで使える関数では四則演算などを行う数学的なものや論理関数、Webサービスと連携した関数までいろいろあります。excelを使っていくにこれらすべてを覚える必要はありません。excelでよく使う機能だけを覚えれば良いでしょう。ただし、何も考えずexcelを使っていると便利な機能に気が付かないままになっていることもあります。excel上でめんどうさを感じた場合には用途に合ったexcel関数が用意されていないか調べてみましょう。

indirect関数で特定のシート名からセルを参照

excelのindirect関数ではシート名から参照ができる

indirect関数は指定した文字列への参照をすることができます。A1のセルに「100」という値が入っている場合、 =indirect(A1) とすれば参照先はA1のセルとなり「100」の値を返します。変数にすることで柔軟な対応ができます。 さらにこのindirect関数を応用すれば、別の関数における引数を文字列として作ることも可能になります。引数とは関数のカッコの中の情報の事です。上の式で言えばA1がindirect関数の引数です。この引数をindirect関数によって作り出し、別の関数のカッコ内に入れるというやり方をしていきます。excelのシート名を書き込んだセルを用意しておけば、indirect関数によって何度もシート名を書くことなく参照ができるようになります。他のexcel関数でもただのセル参照をすることはできます。ただしセルの値がシート名などの文字列である場合indirect関数が使えると便利になります。

シート名からセルの参照をする方法

excel上に複数のシートを作成しており、別のシートの情報を取得したくなった場合、 =sheet2!A1 と記述してもsheet2のA1セルを参照可能です。これをindirect関数を使って表現してみましょう。シート名の「sheet2」を「sheet1」内のB1に書き込み、 =indirect(B1 & ”!A1”) とすればsheet2のA1を参照できます。変数として表現することで、B1の内容を書き換えるだけでいろんな変化にも対応できるようになるので便利です。

関数の引数としてindirect関数を使った参照

別のexcel関数の引数としてindirect関数を組み込めば、よりその恩恵を受けられます。別のシートにある値を使った簡単な計算をしてみましょう。 まず、同じシート内にある値を使った計算だと、 =sum(A1:A3) とすればA1からA3までの合計を計算できます。 ではA1とA3の値が別のシート「sheet2」にあるとします。関数を書き込むシートを「sheet1」として、このシート内のB1に「sheet2」と記述しておきます。その状態で、 =sum(indirect(B1 & ”!A1:A3”)) とすれば別のシートにある値を使って計算ができます。

cell関数でシート名も自動で参照しよう

excelのcell関数ではシート名が取得できる

indirect関数では別のシート名からその内部にあるセルを参照することができました。これに対してcell関数を使えばシート名を自動で読み取ることができるうようになります。シート名をセル内に取得すれば自動的にタイトルのような表示をすることもできます。シート名を変更した場合でも自動で更新されるため編集などもしやすくなります。ただしcell関数だけでシート名を取得できるわけではありません。まずファイルパスをcell関数で取得することになり、ここからシート名だけを切り取ることになります。

cell関数でシート名を含むフルパスを参照

=cell(”filename” , A1) と記述すればシート名を含むexcelのファイルパスが取得できます。第二引数のA1はシート内のどのセルでもかまいません。cell関数は指定したセルの書式や位置、内容に関する情報を返す関数です。excelにおけるさまざまな情報を得られるでしょう。上の式ではセルA1のファイルパスを返す指示となり、特にセルの場所は問題とならず省略することも可能です。

cell関数でシート名を参照する方法

それではシート名だけを取得する式を見てみましょう。 =right(cell(”filename”,A1),len(cell(”filename”,A1))-find(”]”,cell(”filename”,A1))) この式によってexcelファイルにあるシート名が取得できます。

cell(”filename”,A1) では「 パス¥[ファイル名]シート名 」という形でフルパスが返されます。excelファイルが置かれている場所も一緒に表示されます。 excelのファイルがどのこにあるのかと言った情報は必要なく、「シート名」だけを得るには、フルパスからシート名だけを切り抜きます。 right関数は第1引数の文字列右側から、第2引数の文字数分の文字列を返します。第一引数はフルパス、第二引数はlen関数で得たフルパスの文字数から「 ] 」が現れるまでの文字数を引きます。 find関数は第二引数の中に第一引数で指定したものが何文字目にあるのか調べられます。 こうしてexcel関数をいくつか組み合わせていけばいろんなことができるようになります。

excelでシート名の一覧を取得する方法

上の式ではexcelファイルにあるシート名一覧までは取得することができません。シートの一覧はexcelの画面下に表示されますが、数が増えてくると一覧が見えなくなります。またセルにこの一覧を表示できればさまざまな応用をしていくこともできます。また、この作業を変数を使って表すことに大きな意味があります。excelでは繰り返しの作業が増えるほど変数による参照の良さが分かるようになります。自動的に表示されるようにすればシートが増えても勝手に一覧としてセルに反映されます。 Visual Basic Editorを使うことで、このシート一覧表示が可能になります。 「Alt + F11」でVisual Basic Editorを起動した後、画面右下にあるイミディエイトウィンドウに「For Each i In ThisWorkbook.Sheets: Debug.Print i.Name: Next i」と記述します。エンターを押すと、もうこれだけでシート名の一覧が表示されます。非常に便利な機能なので使ってみると良いでしょう。

変数で参照すればexcelをもっと効率化できる

excelでは手書きで文字を打つのではなく、変数として指定することで便利さが増します。indirect関数やcell関数などを使えるようになればシート名の参照や別シートのセル情報を取得することもできます。また、直接文字を打つのではなく、変数にするということはシート名などに変更があったとしても自動的に影響を受けることになり、修正の手間も省けます。できるだけ多くの関数を使いこなせるようになり、作業の効率化を目指しましょう。