Aさん エクセルの数式や関数で直接式の中に値を入れるのと、セルを参照させる方法がありますが、どのように使い分ければ良いのでしょうか？
森田 良い質問ですね。
エクセルの数式や関数では基本的にセル参照を行なうようにすれば良いですよ！
では、その理由を含め、具体的に解説していきますね。
はじめに
本題に入る前に、この記事がおすすめな人を挙げてみます。
- エクセルの数式や関数を使う頻度が高い人
- エクセルの数式や関数で「セル参照」を原則とすべき理由を理解したい人
- 数値を扱うエクセルの表を誰かに説明する頻度が高い人
ちなみに、最低限「数式の構成要素」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【超初心者向け】Excel（エクセル）の数式と関数の違いは？
「セル参照」のメリット
まず、結論からお伝えすると、セル参照には次の4つのメリットがあります。
- ワークシート上に計算の元となる値が表示されているため、第三者が見て理解しやすい
- 計算の元となる値の変更が容易
- 計算の元となる値の変更漏れが起きにくい
- 今後マクロ・VBAを覚える場合に「変数」という概念を覚えるのに役立つ
エクセルを日常的に使う人ならば、上記の1～3に該当する場合が大半のため、原則「セル参照」にして問題ないといえます。
では、それぞれのメリットの詳細を見ていきましょう。
【メリットその1】ワークシート上に計算の元となる値が表示されているため、第三者が見て理解しやすい
まず、メリットの1つ目ですが、セル参照しているため、ワークシート上に数式や関数が参照しているセルが表示されていることになります。
そのため、「なぜ、その計算結果になっているのか」が計算元の値とともに確認できるため、第三者にもわかりやすいです。
さらに、上記のように式も参考までに記述しておくと、なお読み手に親切ですね。
特に、印刷したうえで確認する可能性があるワークシートの場合、印刷後は数式バーの中身の式を物理的に確認できませんので、上記のように計算の元となる値を入れるように注意しましょう。
【メリットその2】計算の元となる値の変更が容易
メリットの2つ目ですが、セル上の値を変更すれば、そのセルを参照しているすべての数式・関数に影響させることが容易です。
同じセルを参照している数式・関数が多ければ多いほど、その効果は絶大ですよ。
仮に、これがすべて定数で組まれている場合は、1セルずつ手作業で修正するか、置換機能で変更するしかありません。
手修正は手間がかかりますし、置換も関係ない数式や値まで変更する可能性があることを考えると、セル参照が便利なのは明らかですね。
【メリットその3】計算の元となる値の変更漏れが起きにくい
メリットの3つ目は2つ目と似ていますが、計算元の値がワークシート上に表示されているため、その値が変更しなければならない場合や誤っていた場合、相対的に気づきやすいです。
よって、それらの変更やメンテナンスの処理漏れが起きにくいともいえますね。
【メリットその4】今後マクロ・VBAを覚える場合に「変数」という概念を覚えるのに役立つ
最後に、メリットの4つ目はオマケ的なものなので、この部分は興味のある方だけお読みください。
マクロ・VBAを覚えていきたい人にとって、VBAのコードの可読性と保守性を高めてくれる「変数」は学習が必須な概念です。
この変数とセル参照は実は似た概念なのです。
よって、早めにセル参照を活用できるようになっておくと、いざマクロ・VBAを学習しようとした際に、変数の概念を習得することも早くなりますよ。
ちなみに、下記のように任意の変数名と、その中に格納する値を入れておくと、たとえば数式中に変数名を使って計算できたりします。
上記の例でいうと、”Dim”のあとに続いている言葉（"X"や"Y"）が変数となります。
ご興味のある方は次の記事も見てみてくださいね。
【Excel VBA】変数の基礎知識まとめ | Excelを制する者は人生を制す ～No Excel No Life～
【注意点】他ブックからの参照は極力しない！
この便利なセル参照ですが、1点だけ注意点があります。それは、他ブックのセル参照を極力しないことです。
私がなぜ、他ブックのセル参照をしないことを強調するのか、それは次の3つのデメリットがあるからです。
では、順番に見ていきましょう。
【デメリットその1】ブックを開く度にメッセージが表示され、逐一データ更新の判断が必要
まず、デメリットの1つ目は、他ブックのセルを参照しているブックを開くと、次のようなメッセージが表示されます。
このブックには、安全ではない可能性がある外部ソースのリンクが1つ以上含まれています。
リンクを信頼できる場合、リンクを更新して最新データを取り込みます。信頼できない場合は、データをそのまま手元で処理してかまいません。
このメッセージが表示されたら、最新データを更新する場合は「更新する」を、更新しない場合は「更新しない」をそれぞれクリックする必要があります。
正直、自分だけで全ブックを管理しているなら、問題ないかもしれませんが、会社であれば、複数のブックを複数名で管理することになるため、どのブックからどのブックを参照しているかは判別が困難です。
また、参照しているブックのフォルダ上の保存先を変更されただけで、リンクエラーになりますので、エラーの原因特定が非常に難しくなります。
こうしたデータ管理上のリスクや余分な工数増加の可能性があるため、ブック跨ぎのセル参照はおすすめできません。
【デメリットその2】数式が長くて可読性が落ちる
2つ目のデメリットは、他ブックのセルを参照すると、数式が長くなってしまい、可読性が落ちてしまう点です。
具体例として、他ブックを参照した複数の関数が入っている数式をご覧ください。
IFERROR関数とVLOOKUP関数とMATCH関数の3つを組み合わせているので、ただでさえ長い数式です。
さらに、ブック跨ぎのセル参照を行っているため、他ブックの格納先のフォルダパスとブック名が記載されており、2行の数式になっていますね。
こちらを同一ブックのセル参照にした場合にどうなるかも見ていきましょう。
他ブックの格納先のフォルダパスとブック名がなくなったため、数式が1行に収まるようになりましたね。
どちらが読みやすい数式かといったら、当然後者です。
このように、他ブックのセル参照は数式の可読性低下にもつながります。
以上の2点のデメリットがあるため、他ブックの参照は極力行わないことをおすすめします。
【例外】こんな場合は「定数」でもOK！
ここまでセル参照が原則だと、口を酸っぱく伝えてきましたが、もう一方の「定数」も絶対悪ではありません。
以下のような場合は定数を用いると良いので、うまく使い分けましょう！
変更となる可能性が低い「固定値」の場合
計算の元になる値が基本的に変わる可能性がない場合は、無理にセル参照させることはないです。
たとえば、消費税などはその代表でした。
安倍政権以降はいつ消費税が変わるか分からない時代になったので、セル参照にした方が良いかもですが、このように変更の可能性がほぼない値は定数を用いた方が良いですね。
第三者に見せたくない値がある場合
次に、第三者に見せたくない値を数式に用いる場合も定数を使った方が良いです。
セル参照は、計算の元となる値をワークシート上に表示させるため、その部分を非表示にしない限りは第三者にて確認できてしまいます。
このような場合は、定数を用いた方が良いですね。
さいごに
エクセルの真の上級者は、「複数の関数を用いて長い数式にできる」というよりも、セル参照を用いた数式をフル活用したワークシートの使い方ができる人です。
こうした人は、ワークシート上に配置している数値をうまく使い、数式の計算の元にしているので、見た目も実用性も高いワークシートをつくってしまいます。
いきなり、このレベルに達するのは難しいですが、本記事で触れた「セル参照を原則にする」ということを意識して実務に取り組むことから始めてみてくださいね。
日々試行錯誤する中で、どのようにワークシートを使えばいいか、周囲のうまい人はどう使っているかを考え、学ぶ良いきっかけになるはずです。
次回は、エクセルの数式で絶対参照・相対参照の使い分けについて解説する予定です。
ご参考になれば幸いですm(_ _)m
森田 本記事は、実は私の書籍である「すごい！関数」の中で盛り込むはずだったんですが、ページ数の都合で泣く泣くカットした部分を元に記事にしてみました。
内容自体は担当編集さんにも「わかりやすい」とは仰っていただいていたんですが、書籍のターゲットが中級者以上だったため、優先順位の観点からカットするに至りました。
まだ、こういうボツ原稿で記事にできていない部分があるので、これから少しずつ記事化していきたいと思います。
