Excel　計算式を固定する絶対参照、数式をずれないように

Excel　計算式を固定する絶対参照、数式をずれないように

この表で「手数料」を出すには、
＝ 購入額 × 手数料率を計算すれば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から絶対にズレないでくださいね」という設定ができたので、［入力］ボタンで数式を確定します。

残りのセルは、まだ間違った計算式のままなので、修正した計算式をコピーしていきましょう。
修正した計算式が入力されているB4番地が選択されている状態で、オートフィルすると、

今度は計算がうまくいっています！

せっかくなので、［数式］タブ、［ワークシート分析］グループにある、［数式の表示］ボタンをONにして数式を見てみましょう。

どの計算式でも、ズレずにB1番地を掛けていることが分かります。
これが絶対参照です！

次の操作のために、［数式の表示］ボタンをOFFにして、通常の表示にしておきましょう。

最初から絶対参照のドルマークを付けるには

先ほどは、既に入力し終わった数式を修正しましたが、まだ数式を入力しておらず、入力する前から絶対参照にしなければいけないことが分かっている場合の方法も押さえておきましょう。

これはまだ数式を入力していない表です。
B4番地に、
＝ 購入額 × 手数料率
の式を入力しますが、数式をコピーしたときのために、既に手数料率のセルは固定しなければいけないことは分かっているので、最初から
＝ A4 ＊ $B$1
となるよう、数式を入力していくことにしましょう。
計算式を入力したいB4番地を選択し、

キーボードで「＝」を入力します。

A4番地を掛けたいので、A4番地でクリックします。
A4番地を初めとする「購入額」は、それぞれにセルがあるので、計算式をコピーしたときに、ズレてOKなセル。
なので、このA4番地には余計な設定を付け加えず先に進みます。

「かける」を意味する「＊」を入力、

そして、B1番地をかけたいので、B1番地をクリックで指定したら、

キーボードでF4キーをポンッと押すと、絶対参照のドルマークが付きます。
もちろん、何度も押してしまった場合には、「B」の前、「1」の前の両方にドルマークが付くまでF4を押していきます。

これで計算式の入力は終わりなので、［入力］ボタンをクリックして、計算式を確定します。

計算式が入力できたので、計算式をコピーしましょう！
計算式が入っているB4番地が選択されている状態で、オートフィルすると、

正しい計算式を残りのセルにもコピーできました！