計算式を固定する絶対参照！数式をずれないように
Excel 2021, 2019, 2016, 2013 Microsoft 365
解説記事内の画像はExcel 2021のものです。
スポンサーリンク
この表で「手数料」を出すには、
＝ 購入額 × 手数料率
を計算すればOK。
B4番地にはこの計算式が既に入力されているので、B4番地を選択して数式バーを見ると、
＝ A4 ＊ B1
となっていることが分かります。
ところが、残りのセルにも同じく
＝ 購入額 × 手数料率
の数式をコピーしようと、オートフィルすると、
計算結果がおかしかったり、エラーが表示されたりしています！
早速、これを解決していきましょう！
スポンサーリンク
そもそも計算式は「ずれる」が当たり前
こういうことが起きたら、「今、何が起きているのか」を把握するのが一番重要。
そこで便利なのが、［数式］タブ、［ワークシート分析］グループにある、［数式の表示］ボタン。
状況把握したいシートさえ表示されていれば、特にセルの範囲選択などは必要ありません。
このボタンをクリックすると、
シート内の数式が入力されているセルが、計算結果ではなく、その数式自体が表示されるようになりました。
また、パーセント表示されていたセルは、数字での表示に変わりましたが、そういうもんだと思って気にする必要はありません。
［数式の表示］ボタンをONにしたことで、現状がとてもよく分かるようになっています。
数式を入力した「手数料」欄の先頭のセル、B4番地を選択すると、
＝ A4 ＊ B1
の数式で使われているセルが強調表示されます。
これは正しい数式ですね。
お次のB5番地を選択すると、
＝ A5 ＊ B2
と、手数料率のセルから1つズレてしまっていることが分かります。
さらにB6番地を選択すると、
＝ A6 ＊ B3
と、手数料率のセルから、さらにもう1つズレてしまっています。
そもそもExcelは、数式をコピーすると、元の数式から1つずつズレていくようにできています。
そう、それがExcel。
購入額のセルは個々にあるので、1つずつズレていくことで正しいセルを指定できて、むしろありがたいですが、
手数料率のセルはB1番地しかないので、ズレては困るのに、そもそものExcelの仕組みのせいで、順にズレてしまっています。
そこで必要なのが、B1番地から絶対にズレてほしくない、絶対に見に行って欲しいという「絶対参照」の指定です。
では、ここから修正に入りますが、状況把握はもう終わったので、［数式］タブ、［ワークシート分析］グループにある、［数式の表示］ボタンをOFFにし、通常の表示に戻しておきます。
スポンサーリンク
計算式を絶対参照に修正
それでは、ズレてしまっている計算式を修正していきましょう。
計算式が入力されている「手数料」欄の先頭のセルは、計算自体は正しく行われているものの、コピーするとズレてしまうので、この先頭のセルから修正してします。
修正したい計算式が入っている先頭のセルを選択し、数式バーを見ると、このセルの計算式が映し出されています。
「B1」の部分をズレないように固定したいので、数式内の「B」と「1」の間でクリックすると、そこに文字カーソルが入り、「B1」が修正できる状態になります。
この状態で、
キーボードのF4キーをポンッと押すと、
「B1」の「B」の前、「1」の前の両方にドルマークが付きました！
この、ドルマークが両方に付いている状態が、「B1から絶対にズレないでくださいね」という、絶対参照の合図。
ちなみに、F4キーをポンッポンッと何回も押していくと、ドルマークが片方にだけ付いたり、ドルマークが無くなったりと押す度に変わっていきます。
片方にしかドルマークが付いていないものは、入力した数式を、縦方向にも横方向にもコピーするとき用で、これからやるように、数式をコピーするのが縦方向か横方向かどちらか一方の場合は、難しく考えず、両方にドルマークが付いていればズレない、と単純に覚えていただいてOKです。
というわけで、「B1」の「B」の前、「1」の前の両方にドルマークが付くまで、キーボードのF4キーを押します。
これで、「数式をコピーしても、B1から絶対にズレないでくださいね」という設定ができたので、［入力］ボタンで数式を確定します。
今度は計算がうまくいっています！
せっかくなので、［数式］タブ、［ワークシート分析］グループにある、［数式の表示］ボタンをONにして数式を見てみましょう。
どの計算式でも、ズレずにB1番地を掛けていることが分かります。
これが絶対参照です！
次の操作のために、［数式の表示］ボタンをOFFにして、通常の表示にしておきましょう。
スポンサーリンク
最初から絶対参照のドルマークを付けるには
先ほどは、既に入力し終わった数式を修正しましたが、まだ数式を入力しておらず、入力する前から絶対参照にしなければいけないことが分かっている場合の方法も押さえておきましょう。
練習用データを操作しながらご覧いただいている方は、画面左下のシート見出しで「Sheet2」に切り替えます。
これはまだ数式を入力していない表です。
B4番地に、
＝ 購入額 × 手数料率
の式を入力しますが、数式をコピーしたときのために、既に手数料率のセルは固定しなければいけないことは分かっているので、最初から
＝ A4 ＊ $B$1
となるよう、数式を入力していくことにしましょう。
計算式を入力したいB4番地を選択し、
キーボードで「＝」を入力します。
A4番地を掛けたいので、A4番地でクリックします。
A4番地を初めとする「購入額」は、それぞれにセルがあるので、計算式をコピーしたときに、ズレてOKなセル。
なので、このA4番地には余計な設定を付け加えず先に進みます。
「かける」を意味する「＊」を入力、
そして、B1番地をかけたいので、B1番地をクリックで指定したら、
キーボードでF4キーをポンッと押すと、絶対参照のドルマークが付きます。
もちろん、何度も押してしまった場合には、「B」の前、「1」の前の両方にドルマークが付くまでF4を押していきます。
これで計算式の入力は終わりなので、［入力］ボタンをクリックして、計算式を確定します。
正しい計算式を残りのセルにもコピーできました！
数式入力中に、絶対参照のドルマークを付ける操作を、セリフでまとめると、
ここは、このセル かける このセルを固定 です
という操作になります。
| ここ | 計算式を入力するセルを選択 |
|---|---|
| は | ＝を入力 |
| このセル | A4番地をクリック |
| かける | ＊を入力 |
| このセル | B1番地をクリック |
| を固定 | F4キーを押す |
| です | ［入力］ボタンで計算式を確定 |
そして、今回ご紹介した、
- そもそも計算式はコピーするとズレる
- ズレないように固定する
を総合的に復習できるよう割合の計算とパーセント表示［2パターン完全攻略］をご用意していますので、ぜひチャレンジしてみてください。
動画で覚える絶対参照
【再生すると音が出ます】
YouTubeサイトでご覧になりたい方はこちらから。
スポンサーリンク
かつて、こんな後悔のご経験はありませんか？
もっと早くからパソコンを触っておけば...
今まさしく、あの時とは比べものにならない大きな進化が、今まで経験したことのない驚異的なスピードで私たちを巻き込んでいます。
そうです、AIです。
あの時の何倍も何十倍も大きな後悔をしないために、本当に必要な情報とテクニックが手に入る場所、それがBe Cool UsersのAI特化メンバーシップです！
Be Cool Users管理人の略歴・得意なことをまとめました！
お仕事のご依頼も「自己紹介・お仕事のご依頼につきまして」からご連絡くださいませ。
「こんなことできる？」もお気軽にご相談ください。
