更新日：2024.10.03
Excel（エクセル）で数式を作成し、セルをコピーしたら、数字がおかしくなってしまった、エラーがでてしまったなどという経験はありませんか？そんなときは『$（ドルマーク）』を活用しましょう。
Excelの計算式をコピーするときに「一緒に参照もずれてほしいもの」と「ずれてほしくないもの」がありますよね。それを指定できるのが『$（ドルマーク）』を使用した、『相対参照』、『絶対参照』、『複合参照』の3種類です。
今回はこの3つの参照の違いについて、効率的に数式を作成するテクニックを学習しましょう。
監修：みずき さん
EXCEL女子の研修講師をしています。Excel研修では「覚える」だけではなく「理解する」ことで、自身の力としてExcelスキルを身につけられる研修を実施しています。研修をするたびにExcelが大好きになり、Excelってすごい！と実感しています。Excelの他には写真が好きで食べ物や風景を撮影しています！
そもそも、「$」とは？
「$」といえば通貨でも使用しますが、Excelでは『固定』を意味します。
Excelではセルを参照するときに、列方向はアルファベット、行方向は数字を使って表します。
セル参照のアルファベットや数値に「$」を付けることで行や列のどちらかを固定したり、行と列の両方を固定することができます。これから実際に「$」をどのように活用するかご説明します。
参照方式の種類と具体例をご紹介
Excelの相対参照について
「$」を使用した固定の方法を学習する前に、まずはExcelの相対参照についてご説明します。
相対参照とは、セルの位置を相対的に参照します。数式をコピーすると、セルの参照はコピー先に合わせて自動的に調整されます。
下図にはセル【A1】に「100」が入力されています。セル【B1】の「=A1+50」という数式は「=100+50」を意味します。
オートフィル機能を使って、下方向に数式をコピーしてみます。参照元に応じて１つずつずれているのが分かります。これが相対参照です。
具体例：相対参照を使って各商品の売上を求める
ここでは、相対参照を使って、「合計額」を求める数式を入力しコピーします。「価格」は「価格×個数」で求めます。
操作方法
（1）セル【D3】をアクティブにし「=B3*C3」と入力する
（数式で乗算するときは「*」を使用します。）
（2）セル【D3】から下方向へコピーする
そうすると、一つずつ参照元のセルがずれるので、それぞれの合計額を求めることができます。
絶対参照について
特定の位置にあるセルを必ず参照します。数式をコピーしてもセル参照は移動しません。セルを絶対参照にするには「$」を付けます。
同じ値を何度も使用したいときに、行番号と列のアルファベットの前にそれぞれ「$」を入力すると固定できます。相対参照と同じ例を使用して「A1」を「$A$1」と入力します。
このまま下方向へコピーすると、セル【A1】を固定しているので、求めた値が変わっていないのが確認できます。これが絶対参照です。
具体例：絶対参照を使って商品の税込価格を求める
数式の中に固定したいセルがある場合は絶対参照が有効です。
下図では、セル【C3】に消費税を求めるため「=B3*C1」と入力します。
このまま下方向にコピーするとエラーが表示されてしまいます。これは、消費税率が入ったセル【C1】が固定されていないので、１つ下にずれてしまったからです。消費税率ではなく、セル【C2】の「消費税」という文字列が数式に選択されていることが下図から分かります。
エラーを表示させないために、下図では、消費税率が入力されたセル【C1】を絶対参照にしてセル【C3】に表示します。数式は「=B3*$C$1」となります。
消費税率が入力されているセル【C1】を絶対参照にするために数式を「=B3*$C$1」とし下方向へコピーすると、絶対参照にしたセル【C1】の参照は変わらずにコピーができます。
複合参照について
列と行のどちらかを固定する参照方式です。相対参照と絶対参照が組み合わさっていることから、複合参照といいます。
◆列だけを固定する場合
「$A1」のように列のアルファベットの前に「$」を入力すると列だけを固定できます。
◆行だけを固定する場合
「A$1」のように行の数字の前に「$」を入力すると行だけを固定できます。
具体例：複合参照を使って九九の表を作成する
ここでは、複合参照を使って九九の表を作成していきたいと思います。
複合参照とは相対参照と絶対参照を組み合わせた参照方法です。列だけを絶対参照にしたり、行だけを絶対参照にすることができます。
「C$5」「$C5」のように、セル番地の列または行のどちらか一方に「$」を付けることで、参照先の列または行だけが固定されて、複合参照になります。
下図の黄色のセル【D3】に数式を入れて、下方向と右方向にコピーするだけで九九の表が作成できます。
黄色のセルは「1×1」を求めるので、「D2*C3」と入力します。
下方向や右方向に数式をコピーしてみると、エラーが出てしまい、正確な九九の表を作成することができませんでした。
数式を見てみると、下図のようになります。これはどのセルの数式も、
「自セルと同じ列の、１行上のセル」×「１列左の、自セルと同じ行のセル」
という相対参照のコピーになっていることが分かります。
この「=D2*C3」のセル【D2】とセル【C3】に「$」を付けることによって、コピーした時にセル番地がずれずに縦軸と横軸の交点を求めることができます。
本来求めるための「自セルと同じ列の、2行目のセル」×「C列の、自セルと同じセル」という式にするためには、「=D2*C3」の「2」と「Ｃ」に「$」を付けて、複合参照にします。（「=D$2*$C3」）
違う場合も比較するために、3の段の場合でも考えていきましょう。
セル【F7】に「3×5」を求めるためには、同じように「自セルと同じ列の、2行目のセル」×「C列の、自セルと同じ行のセル」を入力されていればいいことが分かります。（「=F$2*$C7」）
では実際に黄色のセル【D3】に数式を入れてみましょう。
操作方法
（1）「1×1」の計算をするセル【D2】に「=D$2*$C3」を入力する
（2）セル範囲【D3:D11】をコピーする
（3）次に、そのままコピーした1番下のセル【D11】の右下の十字マークをL列までコピーする
数式で確認すると下図のようになります。ずれてほしくないセルの列または行だけが固定されて、九九の表を完成させることができました。
ショートカットで効率的に「$」を使い分ける
絶対参照や複合参照を使用するときに、覚えておきたいのがショートカットです。毎回「$」を入力するのは大変なので効率のいい方法をご紹介します。
F4キーの活用
「$」を入力したい数式の中の参照部分を選択して『F4キー』を押すと、参照方式を切り替えることができます。
- F4キーを1度押すと「絶対参照」で完全固定。($B$1)
- もう1度押すと「複合参照」で行のみ固定。(B$1)
- 更に押すと「複合参照」で列のみ固定。($B1)
- また更に押すと「相対参照」に戻ってくる。(B1)
そのほかにも、Excel上での『F4キー』は参照方式の切り替えだけではなく、直前の操作を繰り返すときにも使うことができます。この機能をうまく活用して、時間短縮や効率よく業務に役立てていきましょう。
応用編：VLOOKUP関数でも使える
VLOOKUP関数とはExcelの表を縦方向に検索し、検索条件に合った値を取り出してくれる便利な関数です。よくあるトラブルの１つとして、数式の入ったセルをコピーすると、参照範囲がずれてしまうということがあります。では実際に「$」を使って、どのようにVLOOKUP関数を活用できるのか紹介していきます。
=VLOOKUP(検索値,検索範囲,列番号,[検索方法])
参照するデータの中から必要な値を取り出して、指定のセルに入力することができます。- 検索値…検索したい値
- 検索範囲…データ一覧表の範囲
- 列番号…データ一覧表の中で、検索したい値が何列目なのかを指定（左から数える）
- 検索方法…完全一致（FALSEまたは0）、近似一致（TRUEまたは1）のどちらかを指定
相対参照を使用するとエラーが出てしまう
『在庫数一覧』の表の中から商品名に該当する在庫数を検索して、『売上個数管理』の表の在庫数に値を入力してみます。
セル【G4】には「=VLOOKUP(E4,B4:C8,2,FALSE)」という数式が入力されています。
（セル【G4】にセル範囲【B4:C8】の在庫数一覧から「バナナ」を探して、その隣（2列目）の数字を入力してね。という意味です。）
VLOOKUP関数の数式を下にコピーするとエラーになってしまいました。エラーになったセル【G5】を確認すると、相対参照になっていたので検索範囲がずれてしまったことが分かります。
VLOOKUP関数を使うときは、検索範囲を固定したいので相対参照ではなく絶対参照を活用しましょう。固定したいセル範囲【B4:C8】を選択したら、F4キーを1回押して絶対参照にします。こうすることで数式を下にコピーしても参照範囲がずれずに、正常な値を検索できます。
まとめ
今回は、「$」を使った参照方式の種類と、実践から応用までをご紹介しました。Excelで計算やデータ集計を行うときは、参照方式に注意して作業するとミスも大きく減らすことができます。
「$」は使用頻度も高いので、しっかり使いこなして作業効率を上げていきましょう。
当社では、『Microsoft認定トレーナー』資格を持つEXCEL女子による「Excel研修」を提供しております。書籍化するなど注目いただいている研修です。
社員教育に時間を割けない、Excelスキルの底上げをしたいなどお悩みでしたら、ぜひお気軽にお問い合わせください。
PAGE TOP
お役立ち資料ダウンロード
「EXCEL女子の常駐・スポット派遣」「社員教育サービス」の
サービス内容・料金について詳しくご紹介しています。