Excel情報554　関数 --別シートのデータを自動で切り替える方法

【テーマ】
別シートのデータを見たい場合、シート数が多いと手動で切り替えるのは時間がかかり、面倒です。今回はシート名に対応するデータを瞬時に表示する方法をご紹介します。

【方法】
INDIRECT関数、COUNTA関数を使用します。

【参考】
条件を指定して合致したデータのみ表示することもできます。

■ 今回の内容

別シートのデータを見たい場合、シート数が多いと手動で切り替えるのは時間がかかり、面倒です。

特定のセルにシート名を入力するか、プルダウンで選択するだけで、そのシートのデータを瞬時に表示できれば

作業効率が大幅に向上します。

今回は、INDIRECT関数及びスピル機能を使って、瞬時にデータを切り替える方法をご紹介します。

※スピル機能は、Excel2021以降またはMicrosoft365に対応

今回ご紹介する方法を使うと、瞬時にデータが切り替わります。

■設定方法

例として、下図を使用します。

◆データを表示するシート([データ表示]シート)

　　　◆データが入力されている各支店シート

　　　　　(例として下図は[札幌支店]シート)

※下記が前提になります。

　　・(データを表示するシート含め)各シートが同じレイアウトであることと。

　　・データとデータの間に空白行がないこと。

【数式の設定】

[データ表示]シートのデータが始まる行の左端のセル(例ではB5セル)に、下記数式を設定します。

=IFERROR

【使用する関数】

◆IFERROR関数

数式がエラーになったときに、指定した別の値やメッセージを表示する関数です。

シート名を入力セル(例ではB2セル)が空白の時にエラーを表示させないように設定しています。

「IFERROR」関数については当メールサービスのバックナンバー244をご覧下さい。

【バックナンバー 244】エラーを非表示「IFERROR」

◆INDIRECT関数

セルやシート名を文字列として指定し、それを実際のセル参照として使える関数です。

設定を変えるだけで、参照先を自由に変更できます。

「INDIRECT」関数については当メールサービスのバックナンバー057をご覧下さい。

【バックナンバー 057】シートの特定セルの値をとる

◆COUNTA関数

指定した範囲のデータが入っているセルの個数を数える関数です。

「COUNTA」関数については当メールサービスのバックナンバー057をご覧下さい。

【バックナンバー 176】入力されているデータの数を調べる

【数式の説明】

=IFERROR(INDIRECT("'"&B2&"'!B5:D"&COUNTA(INDIRECT("'"&B2&"'!B:B"))+3),"")

①　B2セルで設定したシートのB列を、INDIRECT関数で参照し、

　　 COUNTA 関数でデータの個数を数えます。

　　データの見出しが4行目から始まるため、+3することで最終行番号の結果を返します。

②　B2セルで設定したシートのB5セルからD列のデータがある最終行をINDIRECT関数で参照します。

B2セルにシート名(例では[横浜支店])を設定すると、

設定したシートの最終行までのデータをスピル機能で瞬時に表示します。

■ ご参考までに

FILTER関数を使用することで、条件に合致したデータのみ表示することができます。

※FILTER関数は、Excel2021以降またはMicrosoft365に対応

「FILTER」関数については当メールサービスのバックナンバー543をご覧下さい。

【バックナンバー 543】 FILTER関数で検索する方法

[B5セルに設定した数式]

=FILTER(INDIRECT("'"&B2&"'!B5:D"&COUNTA(INDIRECT("'"&B2&"'!B:B")+3)),

INDIRECT("'"&B2&"'!D5:D"&COUNTA(INDIRECT("'"&B2&"'!D:D")+3))>=C2)