「関数を使うときに参照したいセルがズレてしまう」
このようなとき、どうやって参照箇所を固定したらよいかわからない方もいるでしょう。
エクセル初心者が特に躓きやすいのが参照ルールです。このルールを把握しないままだと、関数や数式を使う際にエラーが出てしまったり、思うように計算ができなかったりします。
本記事で、エクセルを使ううえで理解しておきたい絶対参照について確認していきましょう。
エクセルの絶対参照とは
絶対参照は、エクセルでセルの位置を固定して参照する方式です。エクセルでは普通にセル参照を利用すると、関数や数式を複製した際に参照するセルも併せて移動します。
しかし、絶対参照なら参照元のセルが固定されているため、関数や計算式がどこに複製されても、参照元のセルは変わりません。
絶対参照を行う場合は、セルの行番号と列番号の前に$を挿入して固定します。例えば、セルC5を絶対参照にする場合は、Cと5の前に＄を付けて「$C$5」と入力します。
エクセルで使える絶対参照以外の相対参照と複合参照とは
エクセルでは絶対参照以外にも以下2種類の参照方式を使用できるため、用途に合わせて使い分ける必要があります。
- 相対参照
- 複合参照
それぞれの特徴や使い方について確認していきましょう。
相対参照
相対参照は、エクセルでセルをコピーする際に参照元のセルがコピー先に応じて動く特性を持っています。行番号と列番号に$を付けないことで、参照先に合わせた移動が可能です。
また、エクセルでは関数の使用時など、セルをクリックすると相対参照がデフォルトで挿入されるのが特徴です。例えば、セルC5を相対参照にしたいケースでは「C5」と入力することで相対参照となります。
これにより、セルのコピー・移動時に自動的に参照先が調節され、大規模な表でも参照元を再設定せずに操作が行えます。
複合参照
複合参照は、行または列の一方だけを絶対参照にし、もう一方を相対参照にする方式です。セルの行番号や列番号の片方にのみ$を付けます。
これにより、参照元の行または列だけが固定され、もう片方はコピー先に合わせて可変になります。例えば、「C$5」と入力した場合は行番号のみが固定され、列番号は相対参照になります。
3つの参照方式の特徴を以下の表にまとめてみました。
| 参照方式 | 特徴 |
| 絶対参照 | 参照元のセルが固定される。 |
| 相対参照 | 参照先によって、参照元のセルが可変になる。 |
| 複合参照 | 行もしくは列のみが絶対参照になり、片方は相対参照になる。 |
さまざまな用途に合わせて使い分けができるように、それぞれの特徴をしっかり把握しておきましょう。
エクセルで絶対参照を使う方法
エクセルで実際に絶対参照を使って、計算を行ってみましょう。
セルB2に数値があり、その2倍の数値をC2で計算したいとします。数式を書くとき、C2に「=B2*2」と入力することで計算ができますが、C2の数式をC3に複製すると数式が「=B3*2」となってしまい、参照位置がズレてしまいます。
ここで使えるのが絶対参照です。セルB2を絶対参照にするには、数式を「=$B$2*2」に変更します。$を行番号と列番号の前につけることで、参照位置が常にB2に固定されました。
絶対参照を使えば、特定のセルにあらかじめ入力しておいた消費税をひっぱってきて自動で掛け算をしたり、設定した数値で大小を比較したりするなど、さまざまな表作成で活用できます。
また、エクセルで表を作成する際に便利な機能がリストです。リストを使えばあらかじめ設定しておいた項目をプルダウンメニューとして利用できるようになるため、データの入力効率が向上します。
以下の記事で3パターンのリストを作成する方法について解説しているので、こちらもあわせてご覧ください。
エクセルで絶対参照と関数を組み合わせてみよう
絶対参照とエクセルで用意されている関数を組み合わせることで、複雑な表の作成も可能です。今回は絶対参照にIF関数を組み合わせて、テストの点数が80点以上なら「合格」の文字列を出力する成績表の作成を行ってみましょう。
1.表の枠組みを作成する
まずは、表の枠組みを作成していきます。タイトルと見出しを簡単に設定し、点数のデータを入力するところまで行いましょう。
見出しの背景色や罫線の設定など表のデザインはお好みでつけてもらえればよいですが、どのように見た目を整えたらよいのかわからないという方もいるでしょう。以下の記事では、エクセルで表を作成する方法について解説しています。
表の作成方法がわからないという方は、ぜひこちらもあわせてご覧ください。
2.参照元となる合格点のセルを作成する
続いて、関数を使う際に参照するセルに値を入力して合格点を設定します。今回は80点を基準にしたいので、F4に80と入力しました。
このF4を絶対参照することで、すべてのセルで80点を基準にした計算ができるようになります。
3.IF関数で合否判定をする
IF関数を使ってC4のセルが80点以上なら合格、80点以下なら不合格の文字列を出力します。IF関数とは、指定の条件を満たすケースと満たさないケースで異なる結果を表示させられる関数です。
さっそくD4に「=IF(C4>=F4,”合格”,”不合格”)」と入力しましょう。これは、もしもC4の値がF4以上であれば合格と出力して、C4未満であれば不合格と表示するという意味です。
すると、C4には79の数値が入力されており、基準の80点よりも低いので、条件を満たさない場合の出力結果である不合格が出力されます。
4.絶対参照でセルを固定する
D4に設定した関数をオートフィルでD8まで複製しましょう。しかし、すべての結果に合格と表示されてしまい、関数が正常に機能していないことがわかります。
これは、参照方式が相対参照になっていることが原因です。D5の関数を見ると「=IF(C5>=F5,”合格”,”不合格”)」となっており、合格と不合格の基準となるセルが一行ズレたF5になっていることがわかります。
これをF4のセルで固定させるために絶対参照を使います。そのために、D4の関数を「=IF(C4>=$F$4,”合格”,”不合格”)」に変更しましょう。「F4」を「$F$4」に変更することで絶対参照を適用できます。
そして、再度オートフィルでD8まで複製すると、基準がF4に固定され関数が正常に動くようになります。
なお、参照したい実際の点数はセルによって変えてほしいので、最初のC4に関しては絶対参照を使わず、相対参照のままにしておきましょう。
エクセルの絶対参照をショートカットキーで使う
エクセルではショートカットキーを使えば、毎回＄マークを打ち込まなくても参照方式を切り替えられます。エクセルで参照方式の切り替えをする方法は以下のとおりです。
- 絶対参照に切り替える：F4キーを一度押す
- 行のみを固定する複合参照に切り替える：F4キーを二度押す
- 列のみを固定する複合参照に切り替える：F4キーを三度押す
- 相対参照に戻す：F4キーを四度押す
これらの操作により、セル参照の素早い切り替えができます。ショートカットキーを覚えているだけで作業効率が大幅に向上するので、ぜひ覚えておきましょう。
また、絶対参照を使いこなせるようになれば、エクセルのスキルを証明できる資格であるMOS資格を取得できる可能性があります。MOS資格があれば就職や転職に有利に働くので、気になる方は以下の講座からMOS資格の習得に励んでみてはいかがでしょうか。
ショートカットキーが使えない場合は？
ショートカットキーを活用しようと思っても、キーが反応しなくて使えないという方もいるでしょう。そのような場合は、ファンクションキーにロックがかかっている可能性が考えられます。
キーの数に制限のあるノートパソコンの場合、ファンクションキーと同じ位置に音量や画面の調節などができるメディアキーも割り振られているケースもため、ファンクションキーを使用するなら呼び出す機能をその都度切り替える必要があります。
今回の場合では、F4キーとFnキーを同時押しすることで解決できるでしょう。また、毎回同時押しする手間が煩わしいと感じる方は、FnキーとEscキーを同時押しして常にFnキーが押された状態に変更することで、F4キーのみでショートカットキーを使えるようになります。
しかし、この機能に対応していないパソコンもあるので、一度試して効果がなければFnキーとの同時押しで対応するようにしましょう。
エクセルの参照方式を理解するなら
Excel基礎セミナー講習は、エクセルの参照方式を詳しく学びたい方におすすめの講座です。この講習は、わずか2日間で学習を終えられる集中学習で、エクセルの基本から応用までを網羅的に理解できます。
参照方式はエクセルの重要な概念の一つであり、データ分析や表作成の際に不可欠な要素です。講座では、プロの講師がわかりやすいようにかみ砕いて解説してくれるため、理解の難しい参照方式も頭に入りやすいでしょう。
また、エクセルで以下のようなことができるようになりたいのであれば、Excel基礎セミナー講習よりも、マクロやVBAを学べるExcelマクロ・VBAセミナーがおすすめです。
- マクロとVBAの基礎知識
- 自動集計アプリ・プロジェクト管理表の作成
- ファイル処理・転記・帳票作成の自動化
VBAやマクロを使いこなせるようになると、事務職で重宝される人材となります。転職や就職を考えている方は、ぜひExcelマクロ・VBAセミナーもあわせてチェックしてみてください。
エクセルと絶対参照についてのまとめ
今回は、エクセルの絶対参照について解説しました。絶対参照は、セルの参照方法の一つであり、活用することで参照元のセルを固定して利用可能です。
これにより、コピー＆ペーストを行った際に、参照元のセルが変化してしまうことを防止できます。参照方式はほかにも、参照元を可変にする相対参照や、特定の行・列だけを固定する複合参照などがあり、用途によって使い分ける必要があります。
絶対参照を理解することで、エクセルの操作がスムーズになり、効率的な表計算やデータ分析ができるようになるでしょう。エクセルの勉強を始めたばかりの方は慣れるまでに時間がかかるかもしれませんが、ぜひ絶対参照の概念を理解して日々の業務に活かしてみてください。
- Excelを使って転職したい・基本操作を知りたい→Excel基礎セミナー
- ExcelでマクロやVBAを組み業務の負担軽減や効率化を図りたい→Excelマクロ・VBAセミナー
- Excelの資格が欲しい→MOS対策短期集中講座
こちらから自分に合うExcelの講座をお選びください。
