| 種類 | 型 | 付け方 |
|---|---|---|
| 「行」と「列」の絶対参照 | $A$1 | F4キーを1回押す |
| 「行」の絶対参照 | A$1 | F4キーを2回押す |
| 「列」の絶対参照 | $A1 | F4キーを3回押す |
| 相対参照 | A1 | F4キーを0,4回押す |
※PCによってはFnキーを同時に押す必要があります
Excelの関数を便利に使うためには「絶対参照・相対参照」を理解する必要があります。
とても重要な機能なのですが、とても理解しづらいので、ExcelCampでは「ExcelのFコード」と呼んでいます。
※Fコード：ギターのコードの押さえ方の1つで、初心者がつまづきやすいとされている
この記事ではExcelCamp講師が研修で実際に説明している手順に沿って、絶対参照・相対参照についてわかりやすく解説しています。
※Mac版Excelのショートカットは紹介していません。Windows版のショートカットのみを紹介しています。
まずは「セル参照」を解説
相対参照の解説の前に、まずはExcelの基本的な機能から解説します。
Excelにはセル参照という便利な機能があります。
例えばB3セルに「参照元」と書きます。
この状態でB4セルに移動し、=B3と式を記入します。
この状態でEnterキーを押すと、式が確定しB4セルに「参照元」と表示されます。
=セル名と記入することで、そのセルの情報を引っ張ってくることができます。
この機能を「セル参照」といいます。
ExcelCamp講師 野沢先生セル参照で表示させている情報は、あくまで「参照元」の情報に依存します。
そのため下記のように参照元である「B3」セルの情報を書き換えたら、B4セルの情報も自動で書き換わります。
ExcelCamp講師 野沢先生この「セル参照」という機能を前提に、相対参照の説明をします。
相対参照とはなにか
「相対参照」とは、数式を書いたセルをコピーしたときに参照位置が自動で相対的に移動する機能のことです。
ExcelCamp講師 野沢先生言い換えれば、セルをコピーしたときにExcelがいい感じに空気を読んでくれる機能が「相対参照」です。
例えば参照元のセルが以下のように並んでいたとします。
C3~C11セルに式を書き、それぞれ参照元を表示させたいとしたときには、以下の方法をとります。
まず、C3セルに=B3と式を書きます。
Enterキーで確定すると、セル参照の機能によりC3セルに「参照元1」と表示されます。
この状態で、C3~C11セルを選択します。
ここで、選択範囲の上のセルを下にコピーするショートカットであるCtrl&Dを押します。
すると一気に式を書くことができました。
ExcelCamp講師 野沢先生Ctrl&Dは必ず覚えたい便利技のひとつです。
詳しくは下記の関連記事で説明していますので、まだ使いこなせていない方はぜひご一読ください。
機能の解説
なぜコピーするだけで、一気に正しい式が書けたのでしょうか。
下のセルの式を確認みると、式を書くセルの位置に合わせてセル参照する参照元の位置も自動でずらしてくれています。
ExcelCamp講師 野沢先生C3セルなら、ひとつ左隣のB3セル
C4セルなら、ひとつ左隣のB4セル
C5セルなら、ひとつ左隣のB5セル
・・・
のようにExcelは式を書いたセルの位置によって、参照元のセルの位置を相対的に判断してくれます。これを「相対参照」と呼びます。
この機能があるおかげで、私たちはいちいち全てのセルに式を書く必要がなくなっているのです。
絶対参照とはなにか
対して絶対参照とは、セルをどこにコピーしても参照位置が変わらない参照方法のことです。
Excelで表を作ると、参照元がついてきてほしくないときが度々あります。例えば以下のような場合です。
数量と単価を掛けて、金額を計算する式を書いていきます。
=C4*F4と書いて下にコピーしてしまうと、うまくいきません。
このように「1月5日」以降の金額が「0」になってしまいました。
原因を特定するために、D5セルの数式の中身をF2キーを押して確認してみます。
※PCによってはFnキーを押しながらF2キーを押します。
F4セルの単価を参照していたセルが下にずれてしまっています。これがうまく計算できなかった原因です。
ExcelCamp講師 野沢先生良くも悪くもExcelは「バカ正直」に相対参照するので、参照がついてきてほしくない単価のセルまでずれてしまうのです。
このような「ついてきてほしくない参照範囲」がある場合に、「絶対参照」という機能を使います。
Excelの絶対参照の書き方
絶対参照($マーク)をつける：(Fn&)F4
※PCによってはFnキーを押しながらF4キーを押します。
絶対参照のつけ外しはF4キーを使って行います。
ExcelCamp講師 野沢先生Shiftキーを押しながら数字の4キーを押して$マークを手打ちすることもできますが、いちいち書くのが大変なことと入力ミスが多発することから推奨しません。
絶対参照は必ずF4キーを使ってください。
まずは同じように=C4*F4と数式を書きます。
この状態で、F4と書かれた場所にカーソルがある状態でF4キーを一度押します。
するとF4キーが$マークで囲われた状態になります。この状態でEnterキーを押して数式を確定し、下までコピーします。
今度はうまくいきました。F2キーで数式の中身を確認してみます。
セルを下にコピーしても$マークを付けた単価のセルは移動しておらず、正しく計算できています。これが絶対参照です。
ExcelCamp講師 野沢先生まずは「$マークをつけると参照が動かなくなる」ということだけ分かっていただければ大丈夫です。
次からが応用編となります。
Excelで使える、4種類の参照スタイル
F4キーを1回押すことで絶対参照をつけられると解説しましたが、絶対参照には合わせて3つの種類があります。
| 種類 | 型 | 付け方 |
|---|---|---|
| 「行」と「列」の絶対参照 | $A$1 | F4キーを1回押す |
| 「行」の絶対参照 | A$1 | F4キーを2回押す |
| 「列」の絶対参照 | $A1 | F4キーを3回押す |
| 相対参照 | A1 | F4キーを0,4回押す |
ExcelCamp講師 野沢先生相対参照も入れて「4つの参照スタイル」を使いこなせるようになると、Excelでの集計作業が一気に効率化できるようになります。
「行の絶対参照」と「列の絶対参照」について、以下解説していきます。
「行」の絶対参照の設定方法・使い方
行の絶対固定はF4キーを2回押すことで設定できます。
視覚イメージを共有すると、以下のようにセルの上下に「柵」ができるイメージです。
F$4と書くと4行目の前後に柵ができて、上下の相対参照ができなくなります。なのでセルを下にコピーしても、上にコピーしても参照位置はF4セルのまま動きません。
機能を理解するために、先程の式を「行の絶対固定」で書いて下にコピーしてみます。
この状態で関数を下にコピーします。
説明の通り、参照位置は上下に移動できなくなっているので、これで正しく計算されました。
ExcelCamp講師 野沢先生「行」の絶対参照はあくまで上下の参照位置を移動できなくする機能です。
左右方向の移動はできますので、セルを右にコピーした場合は参照位置もついてきてくれます。
「列」の絶対参照の設定方法・使い方
列の絶対固定はF4キーを3回押すことで設定できます。
以下のようにセルの左右に「柵」ができるイメージです。
$F4と書くとF列目の前後に柵ができて、左右の相対参照ができなくなります。なのでセルを右にコピーしても、左にコピーしても参照位置はF4セルのまま動きません。
ExcelCamp講師 野沢先生逆にいうと行方向には動き放題なので、先程の式に列の絶対参照を書いてしまうと、うまくいきません。
機能を理解するために、先程の式を「列の絶対固定」で書いて下にコピーしてみます。
この状態で関数を下にコピーすると、うまく計算されません。
列の絶対参照はあくまで「F列目から動くなよ」という指示なので、F◯セルには自由に移動できてしまいます。
ExcelCamp講師 野沢先生まとめると
・行の絶対参照は「F$4」と書き、「4行目から動くなよ」という指示です。(F列目からは動いてしまう)
・列の絶対参照は「$F4」と書き、「F列目から動くなよ」という指示です。(4行目からは動いてしまう)
となります。
絶対参照を使う場面
絶対参照は「関数で範囲を選んだとき」に最も多く使います。
ExcelCamp講師 野沢先生関数は基本的に、一番上のセルにだけ数式を書き、下にコピーして全範囲に反映させる方法が効率的です。
関数の範囲が相対的にずれてほしいことは基本的にありませんので、関数で範囲を選んだら「すかさずF4キー」を押して絶対参照にしましょう。
ExcelCamp講師 野沢先生関数を書く作業を効率化するために、絶対参照、行の絶対参照、列の絶対参照の3つの理解は必須となります。
記事だけでは説明しづらいテクニックも関わっているため、このスキルを身に着けたい方はぜひExcelCampにお越しください。
絶対参照を使う上での注意点
「カンマ我慢」という注意点があります。
たとえばCOUNTIF関数を書く場合、はじめに検索範囲を指定します。
次のステップはカンマ(,)を打って検索条件を選びますが、カンマを打つ前にF4キーを押して絶対参照を先に設定するべきです。これを「カンマ我慢」といいます。
もし先にカンマを打ってしまうと、
・カンマをBackspaceキーで消す
・F4キーで$マークを付ける
という操作が必要ですが、これでは正しい範囲に$マークを付けることができません。
そのため、F2キーでセル内をアクティブにして「B5」と書かれた場所までカーソルを移動し、F4キーを押すという、かなり遠回りな操作が必要になってしまいます。
カンマの先にF4キーを押すかどうかで、操作効率が「天地の差」なのです。
ExcelCamp講師 野沢先生Excelに慣れている人ほど関数を書く際、反射的にカンマを打ってしまいがちです。
F4キーでの絶対参照を強く意識してもらうためにも、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」も絶賛発売中
