【Excel】2つ以上のSheetをドラックで選択する indirect 関数の使い方 !

【Excel】2つ以上のSheetをドラックで選択する  indirect 関数の使い方 !
ホーム » Excel » 【Excel】2つ以上のSheetをドラックで選択する indirect 関数の使い方 !

まえがき

 少し前にVBAに関する記事を書きましたが、今回は普通のExcelに関する内容です。
 Excelで複数のセルを選択するとき、セルの右下をドラックすることで、一気に周りのセルを書き換えることができます。しかも、行番号や列番号も勝手に書き換えてくれて(たまに迷惑なこともありますが)大変に便利です。しかし、この機能は、シートの選択には使えないので、複数シートにまたがるデータの場合は逐一シート名を手作業で書き換える必要があり、面倒です。そこで、今回は複数シートにまたがるデータをドラックで一度に選択するために、indirect関数を使った方法を解説します。
  indirect関数とは、引数に入力した文字列を参照先として認識し、その場所にある値を返す関数です。
の詳細は以下をご覧ください。
https://support.microsoft.com/ja-jp/office/indirect-%E9%96%A2%E6%95%B0-474b3a3a-8a26-4f44-b491-92b6306fa261
 

使うデータ

 以下の、シート「1組」、「2組」、「3組」のテストの点数を「学年」シートで取り扱う場合を例に考えます。

複数シートにまたがるデータを手作業で選択する場合

 手作業で 「学年」シート にデータを書き込む場合、以下のような手順になります。
※ここは基本的な部分ですので読み飛ばしても大丈夫です。

まず、B2セルで「1組」シートのB2:B4セルの平均を計算します。

次に、B2セルをC2セルにドラッグしてコピーしますが、シート名はそのまま、セルの選択範囲だけがずれてしまったので、エラーになっています。

結局、 シート名と、セルの選択範囲を手作業で書き換えました。

 今回の例の様に、データ数が少なければ手書きでも問題ないと思いますが、シート数が多くなればいちいち手書きで書き換えるのは大変です。それに、マクロ化して業務効率化を図ることもできません。

複数シートにまたがるデータをドラッグで選択する場合

  indirect関数を使ってドラッグで選択する場合は以下のような方法になります。

「1組」シートの のB2:B4セル を選択する文を、indirect関数の引数として入力します。ただし、シート名としてB1セルを指定しています。平均の文は先ほどと同じです。

B2セルをドラッグしてコピーします。画像に写っていませんが、C2セルの入力は「=AVERAGE((INDIRECT(C$1&”!B2:B4″)))」となっており、シート名としてC1セルの「2組」が選択されています。

平均の他に、最大、最小を求める場合も、最初のAVERAGEMAXMINに書き換えるだけで簡単に求めることができます。

まとめ

  indirect関数を使って、今回は複数シートにまたがるデータをドラックで一度に選択する方法をご紹介しました。ただ、この方法はセルの選択(今回の例では「B2:B4」)は手入力となるのでシートごとに選択範囲が違う場合は逆に面倒になる場合があります。その場合、どこか目立たないところに セルの選択文字列を入力しておいて、そこを参照する等の工夫が必要でしょう。Excelは上手に使おうと思うと、工夫のしどころが多くて大変ですね。面白くもありますが。