マニアックな関数の1つであるINDIRECT関数(インダイレクト関数)。この関数を使いこなせれば、間違いなくExcel上級者といえるでしょう。
この記事ではINDIRECT関数の基本的な機能や関数の書き方、便利な使い所について解説します。
この記事ではExcelCamp講師が研修で実際に説明している手順に沿って、内容を解説いたします。
INDIRECT関数とは？機能をわかりやすく解説
INDIRECT関数(インダイレクト関数)は、文字列を、セル名やセル範囲名、シート名に変換できる関数です。
これだけでは理解しづらいと思いますので、具体例を用いて説明します。
たとえば、B2セルに「HAPPY」という文字列を、B4セルに「B2」という文字列を入力します。
この状態で、それぞれ以下の数式を書くと、結果はどのようになるでしょうか。
=B2
=”B2″
=INDIRECT(B4)
=INDIRECT(“B4”)
=INDIRECT(“B2”)
=INDIRECT(B2)
先に結論を紹介すると、結果は表のとおりになります。
| 数式 | 結果 |
|---|---|
| =B2 | HAPPY |
| =”B2″ | B2 |
| =INDIRECT(B4) | HAPPY |
| =INDIRECT(“B4”) | B2 |
| =INDIRECT(“B2”) | HAPPY |
| =INDIRECT(B2) | エラー(#REF!) |
ExcelCamp講師 野沢先生上の表の結果になることが当たり前だと感じる方は、次の見出し「結果の比較1」～「結果比較6」は読まなくて大丈夫です。
どこか一つでも「なぜそうなるの？」と思った方は、説明にお付き合いいただけると幸いです。（少しややこしいです）
結果の比較1：=B2と書く場合
数式の結果：HAPPYと表示される
まず=B2と書いた場合はそのままセルの参照がされ、B2セルに書かれている値である「HAPPY」が結果として表示されます。
ExcelCamp講師 野沢先生これはExcelの基本的な機能です。
結果の比較2：=”B2″と書く場合
数式の結果：B2と表示される
“(ダブルクオテーション)で囲った文字は、セル名であろうと数値であろうと「文字列」として認識されます。
そのためB2セルの書かれている値は参照されず、「B2」が文字列として表示されます。
ExcelCamp講師 野沢先生こちらもよく使う基本機能なので、ここまでは知っているという方も多いはずです。
結果の比較3：=INDIRECT(B4)と書く場合
数式の結果：HAPPYと表示される
INDIRECT関数の仕組みは、まず()で囲ったセルに書かれている値を読みにいきます。B4セルに書かれているのは「B2」という値です。
次に書かれていた値を、セル名やセル範囲に変換します。「B2」という値をB2セル(セル名)に変換するのです。
B2セルに書かれているのは「HAPPY」という値なので、セルの参照機能により結果は「HAPPY」と表示されます。
ExcelCamp講師 野沢先生INDIRECT関数でセル名を囲うと、セルに書かれている値を新たなセル名として認識するので、今回の場合、
=INDIRECT(B4)と=B2は同じ意味になります。
※B4セルに「B2」と書かれているので。
結果の比較4：=INDIRECT(“B4”)と書く場合
数式の結果：B2と表示される
次は、INDIRECT関数に値を直接入れたパターンです。今回「B4」は“(ダブルクオテーション)で囲われているので、セル名ではなく文字列として認識されます。
この場合「B4」という文字が、そのままセル名(=B4)に変換されます。
そのためB4セルに書かれている値を参照し、結果はB2と表示されることになります。
ExcelCamp講師 野沢先生まとめると、INDIRECT関数の()内にセル名を入れるか、文字をいれるかで以下のように処理が変わります。
結果の比較5：=INDIRECT(“B2”)と書く場合
数式の結果：HAPPYと表示される
INDIRECT関数の()内には「B2」という文字列が入っているので、「結果の比較4」と同じ考え方です。
B2という文字列がB2セルというセル名に変換され、B2セルに書かれている「HAPPY」という文字が表示されます。
結果の比較6：=INDIRECT(B2)と書く場合
数式の結果：エラー(#REF!)
最後にB2というセル名をINDIRECT関数に入れた場合を見てみましょう。
セル名を入れた場合「結果の比較3」で解説したように、INDIRECT関数はまずB2セルに書かれている値を読み取ります(書かれているのは「HAPPY」)。
次に書かれている「HAPPY」という文字を、セル名やセル範囲として読み取ろうとします。しかしHAPPYという名前のセル名も範囲名も存在しないため、エラーコード(#REF!)が表示されるのです。
ExcelCamp講師 野沢先生以上がINDIRECT関数の基本的な機能の紹介でした。
「機能はわかったけど、いつ使うの？」「二度手間じゃないの？」と思った方は、ぜひ次の見出しをご覧ください。
INDIRECT関数の便利な使い方1：シート名をセルから参照する
通常、「シート名」をセルから参照して関数や数式を組むことはできませんが、INDIRECT関数を用いることで実現できます。
例えば以下のような、店舗ごとにシートが分かれているExcelシートで説明します。
埼玉、東京、神奈川、千葉の4シートに、それぞれの店舗の月次売上がまとまっています。
各店舗の売上合計を集計する必要があり、一番左の「集計」シートにこのように数値をまとめたいとします。
ExcelCamp講師 野沢先生通常のセル参照では、このように数式を書くはずです。
=埼玉!B15
これでも実現できますが、店舗名ごとにシート名を手動で変更しなければいけません。
そのまま下に数式をコピーすると、シート名ではなくセル名が相対参照されてしまいます。
「シート名は固定値で、セルが相対参照される」というのが通常のExcelの機能です。
しかし今回の場合は逆にセルはB15に固定したまま、シート名をB列から参照できたらとても便利です。
ExcelCamp講師 野沢先生今回は店舗が4つしかありませんが、100店舗、200店舗あった場合には手動で編集していては時間がいくらあっても足りません。
こんなときにINDIRECT関数を使うと楽ができます。
シート名をセル参照するINDIRECT関数の書き方は、以下のとおりです。
=INDIRECT(B3&“!B15”)
B3：店舗名が書かれているセルを参照しています。
“!B15”：!はシート名を表す記号。直前のB3セルの値と合わせた埼玉!で「埼玉というシート」という意味。B15は埼玉シート内のB15セル(売上が書かれているセル)を意味する。
最後の)は省略可能です。このままEnterキーを押して式を確定してください。
正しく売上数値が表示されています。
この状態で急がば回れ→Ctrl&D(*)で下に数式をコピーしてみましょう。
*ExcelCampで推奨する最速の「移動と選択」技。詳しくはこちらの記事を参照ください。
それぞれ正しく数式が反映されています。
ExcelCamp講師 野沢先生数式の中身を確認すると、きちんとシート名をセルから参照できていることがわかります。
INDIRECT関数の便利な使い方2：選んだ項目に連動するプルダウンを作成する
上のように「左の列で都道府県を選んだら、右の列ではその都道府県の地名だけをプルダウンで表示させたい」、そんな経験はございませんか？
この「連動プルダウン」を実現するためには、通常のプルダウンの書き方ではない特殊な方法が必要です。
具体的には以下のステップで作成しますが、ステップ4にINDIRECT関数が登場します。
連動するプルダウンの作り方手順
手順1：プルダウンの参照元リストを作成する
手順2：「名前の定義」で各リストに名前をつける
手順3：1つ目のプルダウンを「セル参照」で設定する
手順4：連動させるプルダウンを「INDIRECT関数」で設定する
ExcelCamp講師 野沢先生連動プルダウンの作り方は、以下の記事にかなり詳しく載せているので、ご興味ある方はぜひご確認ください！
休日「1日」を投資して、平日の生産性を最大「20倍」にするExcel研修
私たちが提供するExcel研修「上位1%を目指す！ExcelCamp」では、これまで延10,000名以上の方に研修を受講いただき、受講者満足度は9.71(※)という大変高い評価を頂いております。
※10点満点中の評価の平均値。
休日1日を研修に使うのは「やや重たい」というご意見もいただきます。
しかし、本当にExcelスキルを実践で使えるレベルで高めるとなると、通常数年はかかるものです。
実際にExcelCampで教える内容は、代表の中田が業務コンサルタントとして数年間、毎日10時間以上Excelと向き合った結果ようやく生まれたスキルです。
そのことを考えると、休日の「たった1日」を投資して、その後のExcel業務の生産性を圧倒的に高めることは決して悪くない選択だと思います。
参加者の中にはお伝えしたスキルを仕事の現場で活かし、生産性が「20倍」になったという事例もあります。
動画サービス「bizplay(ビズプレイ)」で研修の一部を無料公開中！
※全4回の動画のダイジェスト版です。
※第1~4回の内容をフルで視聴するには、bizplayへの無料会員登録が必要です。
現在セミナー動画見放題サービス「bizplay」で、ExcelCamp Day1の講義内容の一部を無料配信しています。
全4回に分けてExcelが得意になるための考え方や覚えておくべき基本操作を解説していますので、Day1への参加を迷われている方はぜひ一度ご覧ください。
著書「神速Excel」も絶賛発売中
