エクセルでデータなどを作成する際、手入力ではどうしても打ち間違いなどのミスを起こしてしまいがちです。
本人は気を付けていても、自分の目ではなかなか見つけられないだけでなく、【Ctrl+F】を使い1つずつ文字列を検索しても、間違いである文字列に検討が付かなければ、修正速やかな修正を行うことは困難と言えます。
このように、膨大な数値や文字列を使って大事な資料を作成する場合は、修正も非常にラクに済ませることができる【INDIRECT関数】がおすすめです。
INDIRECT関数を使うことにより、正確なデータを作成できるようになるだけでなく、あらかじめ作成されたデータを参照してデータ作成を行うため、作業効率が格段にアップし、あらゆる作業と並行してデータ作成をスムーズに行えるようになります。
今回は多くのシーンで大活躍してくれるINDIRECT関数の使い方と応用方法、エラーの理由と解決方法について解説します。
目次
INDIRECT関数とは？
エクセルを使うなかで覚えておきたい関数。そのなかでも特に知っておくと非常に作業が捗る関数が【INDIRECT関数】です。
INDIRECT関数とは、あらかじめ作成した文字のリストと関数を使用することで、セル内に直接文字を入力せずとも文字を入力してくれることを指します。
在庫管理などの商品名リストを作成する際には非常に役立つだけでなく、入力時間やミスを省くことができるため、効率よくリスト作成することが可能になります。
INDIRECT関数の使い方と書式
INDIRECT関数の書式は以下になります。
| =INDIRECT(参照文字列,[参照形式]) |
それぞれの引数について解説します。
第1引数【参照文字列】
INDIRECTのあとに続く引数ひとつめは【参照文字列】です。この引数を入力しなければ関数の役割を果たすことができません。
参照文字列はセル参照した文字列や数値を指定する役割があります。
文字列として指定する場合には【””】(ダブルクォーテーション)を用いて囲う必要があります。
なお、どの関数においても、文字列を使用する場合には【””】で加工必要があるので、この機会にあわせて覚えておきましょう。
第2引数【参照形式】
つぎに使用する引数が【参照形式】です。
これは入力必須項目ではないため、記入しなくても問題ありません。
| 省略またはTRUEの場合:A1形式のセル参照 |
| FALSEの場合:R1C1形式のセル参照 |
エクセルでは【A1形式】と【R1C1形式】の2種類の参照方法があります。
意味は以下のようになります。
| A1形式 | 「A1」「B2」「C3」など、英字で列を表し、数字で行を表すこと |
| R1C1形式 | 「R2C2」といった、「ROW'(行)」と「COLUMN(列)」の頭文字にそれぞれ数字を入力して表すこと |
形式の使い方は、人によってそれぞれ使いやすい方を活用すると良いでしょう。
INDIRECT関数の基本的な使い方
INDIRECT関数の基本的な使い方について解説します。
このようなデータを作成しました。
【カスミソウ】【バラ】【カーネーション】と、商品名が記載されたデータのなかから、INDIRECT関数を使い、指定したセルの商品名を表示させます。
参照したいセルは【E4セル】と書かれており、【バラ】が指定されているので、この場合の書式は以下になります。
| =INDIRECT(B3) |
B3のセル内に【E4】と商品名の書かれたセルを指定している文字列を、INDIRECT関数にて表示することができました。
INDIRECT関数を使うことで、あらかじめ記載された文字列を参照することができるため、複数の商品名の管理や名簿作成の際に、打ち間違いなどのミスを減らすだけでなく、商品名部分を変更するだけで、関数の入ったセルが自動で変更されますから、入力作業の多いオフィスワークでは活用シーンの多い関数と言えます。
関数の挿入を使ってINDIRECT関数を使う方法
関数には手入力で関数を作成する方法と、【関数の挿入】というツールを用いて関数を作成する方法の2通りがあります。
手入力で関数を作成する際、関数のスペル、引数の数、また引数の意味を把握していなければ関数を作成する場合に「ここには何を入れればいいんだろう？」と疑問符が出てくることも少なくありません。
しかし、関数の挿入を使うことで、関数のスペルを忘れてしまった人や、頭文字しか浮かばないという状況の場合には関数を検索することができ、引数の書き方について忘れてしまった方にも、引数の説明を読みながら関数を作成することができるのです。
要するに、関数を覚えたての方や初心者はもちろん。エクセル上級者のちょっとしたど忘れにも対応するということなのです。
ですから、関数を作成する場合は、手入力と関数の挿入の2通りを覚えておき、状況に合わせて変えていくスタンスを取ることをおすすめします。
では、関数の挿入を使った関数の作成方法を説明していきます。
まずは関数の挿入の場所について確認しましょう。
赤枠部分に【fx】と書かれた記号があるのがわかるかと思います。
これが関数の挿入です。
この部分をクリックすると、関数の検索ダイアログボックスが開きます。
【何がしたいかを簡単に入力して、[検索開始]をクリックしてください】と書かれた部分があるので、この文書を削除しましょう。
INDIRECT関数の最初にある【IND】部分を入力し、検索開始をクリックすると、【IND】を含んだ関数が一覧表示されます。
今回は上から2番目に表示されているので、クリックで選択し【OK】を押しましょう。
関数の引数ダイアログボックスが開きました。
参照文字列の部分に、セル参照を支指示したセル数値を記入、またはクリックして入力します。
この場合は【B3】を入力、またはクリックしましょう。
参照形式では
| TRUE(一部を含む) |
| FALSE(完全一致) |
を選択し入力するのですが、入力必須項目ではないため省きます。
このような関数になったら【OK】を押しましょう。
手入力と同じように、BセルでしていしたE4セルに書かれた【バラ】が表示されました。
このように、関数の挿入を使うことで、どこに何を入力、クリックして選択すれば良いのかが非常にわかりやすいため、関数の意味や引数の意味を理解しながら書式を作成し、セル数値を求めることが可能になります。
関数が難しく、なかなか覚えられないと悩む方や、関数を理解しながら覚えたいという方にはおすすめのツールですから、ぜひこの機会に2通りの関数の作成方法を覚えておきましょう。
INDIRECT関数を用いて別シート名を参照する
INDIRECT関数では、別シート内にあるセル参照も可能です。
その手順を画像と合わせて解説していきます。
まずはこのように
- 【集計】
- 【カスミソウ】
- 【バラ】
- 【カーネーション】
のようにそれぞれエクセルにシートを作成します。
つぎに【集計】シートのD3セル内に関数を入力します。このときの関数の書式は以下のようになります。
| =INDIRECT(C3&”!C2″) |
【=INDIRECT(C3&】のあとに、【”!C2″】と入力したのち、【Enter】を押します。
この書式の意味には、シート名とC2セルを【&】を使うことによって文字列を結合させるという意味があります。
【カスミソウ】シートで入力した550が関数により自動入力されました。
次に、オートフィル機能を用いてバラ、カーネーションも同じように入力させます。
以下のGIFをご覧下さい。
若干GIFが荒くわかりにくいかもしれませんが、オートフィル機能を用いて、関数をコピーし、D4、D5にも関数が入力され、それぞれ値段が入力されたことが確認できるかと思います。
このように、あらかじめ用意された文字列を関数を使って指定してリスト化することで、入力ミスを防げるだけでなく、瞬時にリストを作成することが可能になります。
INDIRECT関数の応用
INDIRECT関数を用いることで、さまざまなデータが手動ではなく関数で入力できることがわかったかと思います。
そこでつぎは、INDIRECT関数の応用について紹介します。
別シートにあるデータを1つのエクセルにまとめる場合
複数のエクセルシートにそれぞれ複数のデータがあると、ひとつにデータをまとめた方が見やすいと感じることがあります。
例えば、店舗ごとによって来客数や売り上げなどのデータをまとめている場合、それぞれのシートに合計数を記載するかと思います。
しかし、1つのシートに集約し、それぞれ月間で確認することができれば、1年でどれだけの来客数や売り上げがあり、どの月が1番多かったかをすぐに確認することができるようになります。
では、実際にエクセルを使用して、どのようなシートになるかを作成方法と合わせて解説していきます。
シート名【ひがし店】
シート名【にし店】
シート名【みなみ店】
シート名【きた店】
まずは商品の在庫数を管理するシートを作成します。
それぞれのシートには店舗名のシート名、合計数が記載されていることがわかります。
これをひとつのシートにまとめて一目で確認できるよう表示させます。
INDIRECT関数の書式として、
| =INDIRECT(“ひがし!C6”) |
と記載し【Enter】を押します。
合計数である【200】が表示されました。
オートフィルを使って東店の合計数の右下角からきた店の合計数セルに向けて関数をコピーさせます。
ひがしの合計数が表示されました。
【にし店】【みなみ店】【きた店】の関数の書式を、それぞれのシート名に修正します。
関数がそれぞれ的確な書式となり、【にし店】【みなみ店】【きた店】にもそれぞれの合計在庫数が表示されました。
この方法を用いることで、月間の在庫数だけでなく、売り上げ数、売上金額なども合わせて合計数シート、売り上げ数シート、売上金額シートなどとしてひとつにまとめることができます。
INDIRECT関数におけるエラーの理由と解決方法
INDIRECT関数を使用する場合、複数の引数を入力することがないため、エラーが起きにくい関数と言えます。
しかし、ちょっとした入力ミスなどによって細かいエラーが起きてしまうことがあります。
ここでは、INDIRECT関数におけるエラーの理由と解決方法について解説します。
＃REF!エラーの理由と解決方法
INDIRECT関数を使用する際、よく見られるエラーが【#REF!】エラーです。
しっかり関数を記載したはずなのに＃REF!エラーが出てしまう原因としては、指定したシート名のなかに記号が使われていて、関数が有効とみなされていない可能性が考えられます。
エクセルの関数のなかでよく使用するのが【()】ですが、この【()】でも＃REF!エラーになってしまいます。
シート名には不要な記号は使用せず、カタカナや英数字、平仮名や漢字などで記入することで、あらかじめ＃REF!エラーを防ぐことができます。
数値を返した際【0】が表示される場合の理由と解決方法
INDIRECT関数を入力したのにもかかわらず、検索結果が【0】と表示されてしまうことも珍しくありません。
このエラーの理由は、セル参照したいセル数値が、データよりも大きくなっている可能性が考えられます。
画像のセル参照の部分を確認してください。
参照すべきセルが【E6】となっているのが確認できるかと思います。
商品名はE3からE5までしかありません。
そのため、参照できる数値や文字列がないために、0と表示されてしまうのです。
0と表示された場合は、参照すべきセルの数値に誤りがないかをチェックしてみましょう。
まとめ
INDIRECT関数を覚えてしまえば、複雑な文字列の入力だけでなく、データ処理も簡単に瞬時に終えられることがわかってもらえたと思います。
手入力ではヒューマンエラーを起こしやすく、エラーを見つけ出すとしてもひとつひとつ見て確認しなければならないため、見つけ出すことはおろか修正も容易ではありません。
重要なデータ入力や数字の管理を行いたい場合は、手入力で行うだけでなく、INDIRECT関数を用いて正確な情報を入力していく方法も導入しましょう。
INDIRECT関数を使うことで、今よりも格段と正確なデータが作成でき、さらに作業効率も飛躍的にアップしますよ。
