【Excel】エクセルで数値を固定する方法（F4・ドル・数式）
Excelで計算式をコピーした際に、参照するセルがずれて計算結果が変わってしまうことがあります。
このようなときは、F4キーでドル記号を付ける絶対参照を使うと、数値やセル参照を固定できます。
数式内のセル番地を固定したい場合は、列記号と行番号の前にドル記号を付けます。
たとえばA2を固定する式は、$A$2です。
売上表の税率、割引率、単価、目標値など、コピー先でも変えたくない基準セルに便利な機能です。
ここではF4キー、ドル記号、数式のコピーを使って数値を固定する方法を確認していきましょう。
エクセルで数値を固定する方法【F4キーで絶対参照】
それではまず、F4キーでセル参照を固定する方法について解説していきます。
| 商品 | 価格 | 税率 | 税込価格 |
|---|---|---|---|
| りんご | 100 | 10% | 110 |
| みかん | 200 | 10% | 220 |
F4キーによるドル記号の入力
数式を入力している途中で、固定したいセル番地にカーソルを合わせてF4キーを押します。
たとえばD2に税込価格を求める式を入れる場合、価格がB2、税率がC2なら、式は=B2*(1+$C$2)のように作成できます。
F4キーを1回押すと、選択中のセル参照は$C$2となり、列と行の両方が固定されます。
入力例
=B2*(1+$C$2)
C2の税率を固定し、下方向へコピーしても同じ税率を使う数式です。
ノートパソコンなどでF4キーが別機能に割り当てられている場合は、Fnキーを押しながらF4キーを押す必要があるかもしれません。
数式バー内でセル番地だけをドラッグ選択してからF4キーを押すと、意図した参照だけを変更しやすくなります。
F4キーを複数回押したときの切り替え
F4キーは、押す回数によって参照形式を順番に切り替える仕組みです。
通常のA1から始めると、1回目は$A$1、2回目はA$1、3回目は$A1、4回目はA1に戻ります。
列も行も動かしたくない場合は$A$1、行だけ固定したい場合はA$1を選びます。
A1は相対参照です。
$A$1は絶対参照です。
A$1は行固定、$A1は列固定を表します。
表を縦方向へコピーするだけなら、行番号が変わらないようにする必要があるかを確認しましょう。
横方向へもコピーする表では、列記号まで固定すべきかを考えることが重要です。
数式コピー後の参照確認
数式を下へコピーした後は、コピー先のセルを選択して数式バーを確認します。
D2の=B2*(1+$C$2)をD3へコピーすると、価格のB2はB3へ変わり、固定した$C$2は変わりません。
固定が必要なセルだけにドル記号を付けると、計算式を読みやすく保てます。
誤ってすべてのセルを固定すると、各行の商品価格ではなく先頭行の価格ばかりを参照してしまいます。
コピー後の結果だけでなく、数式バーの参照先も確認する習慣が大切です。
【操作のポイント】F4キーはセル番地を入力している最中に押し、数式のどの参照を固定するかを見極めます。
ドル記号による絶対参照と複合参照
続いては、ドル記号の位置によって変わる参照方法を確認していきます。
| 参照形式 | 列 | 行 | 用途 |
|---|---|---|---|
| A1 | 変化 | 変化 | 通常のコピー |
| $A$1 | 固定 | 固定 | 税率や基準値 |
絶対参照による基準値の固定
絶対参照は、コピー先にかかわらず同じセルを参照し続ける方法です。
たとえばB1に消費税率10%を入力し、B2からB10に価格を入力した場合、C2には=B2*(1+$B$1)と入力します。
$B$1という書き方では、列Bと行1の両方が固定されます。
消費税率をB1に置く場合の数式
=B2*(1+$B$1)
この式を下へコピーすると、B2だけがB3、B4へ変化します。
税率を各数式に直接入力するよりも、基準値を1か所に集めるほうが、変更時の作業を減らせます。
行固定による横方向の計算
A$1のように行番号だけを固定する参照は、横方向に数式をコピーする集計表で役立ちます。
たとえば1行目に月別の係数が並び、各商品の計算結果を右へコピーする場合、係数の行だけを固定できます。
A$1では列記号Aは変化し、行番号1だけが固定されます。
右にコピーするとA$1はB$1、C$1へ変化するため、各月の係数を自然に参照できます。
下にコピーしても1行目を参照し続けるため、見出し行や係数行を利用した表で便利です。
列固定による縦方向の計算
$A1のように列記号だけを固定する参照は、縦方向にコピーする式と横方向にコピーする式を組み合わせるときに使います。
たとえばA列に商品名、B列以降に月ごとの数量を置く表では、商品ごとの条件をA列から取り出す式に利用できます。
$A1では列Aが固定され、行番号だけがコピー先に応じて変化します。
複雑な表では、数式を一度作成してから縦横にコピーし、参照の変化を確認するとミスを防げます。
【操作のポイント】ドル記号は固定したい方向だけに付け、横コピーと縦コピーの両方を想定して選びます。
数式のコピーとオートフィルによる固定
続いては、固定した数式をオートフィルで展開する操作について解説していきます。
| 商品 | 税抜価格 | 税率 | 税込価格 |
|---|---|---|---|
| りんご | 100 | 10% | =B2*(1+$C$2) |
| みかん | 200 | 10% | コピー後 |
先頭セルへの数式入力
数式は、計算結果を表示したい列の先頭データ行に入力します。
1行目にヘッダーがある表では、D2を選択して=B2*(1+$C$2)と入力し、Enterキーを押します。
C2に固定値がある場合は、C2を$C$2へ変更してからコピーすることが重要です。
価格に税率を加える基本式
=B2*(1+$C$2)
B2は商品ごとに変化し、$C$2は共通の税率として固定されます。
税率セルに10%と入力している場合、Excelは計算で0.1として扱います。
税率を10と入力しているなら、式は=B2*(1+$C$2/100)のように100で割る必要があります。
フィルハンドルによる数式展開
数式を入力したD2セルの右下には、小さな四角形のフィルハンドルが表示されます。
フィルハンドルを下方向へドラッグすると、D2の数式がD3以降へコピーされます。
オートフィルでは相対参照だけが行に合わせて変化し、$C$2は固定されたままです。
データが連続している場合は、フィルハンドルをダブルクリックして最終行までコピーする方法もあります。
貼り付け後の数式と値の違い
コピーしたセルを通常貼り付けすると、数式そのものがコピーされます。
一方で値として貼り付けると、計算結果の数値だけが残り、元の税率を変更しても結果は変わりません。
今後も再計算したい表では数式を残し、確定した帳票では値貼り付けを使い分けます。
固定参照を使った数式は、基準値が変わればすべての結果を再計算できる点が大きな利点です。
【操作のポイント】最初の数式に絶対参照を設定してから、フィルハンドルで下方向へコピーします。
数値を固定値として入力する方法
続いては、セルの値を計算式ではなく固定値として扱う方法を確認していきます。
| 入力内容 | セル表示 | 扱い |
|---|---|---|
| 100 | 100 | 数値 |
| ‘001 | 001 | 文字列 |
数式内へ直接入力する固定数値
税率などが今後も変わらない前提なら、数式の中へ数値を直接入力する方法があります。
たとえば価格B2に10%を加える式は、=B2*1.1と入力できます。
直接入力した1.1はセル参照ではないため、コピーしても常に同じ固定値です。
税率10%を直接使う式
=B2*1.1
割引率15%を直接使う式
=B2*(1-15%)
ただし、税率や率を変更する可能性がある場合は、別セルへ値を置いて絶対参照する方法が管理しやすいでしょう。
値貼り付けによる計算結果の固定
計算結果を固定したいときは、対象セルをコピーしてから値として貼り付けます。
ホームタブの貼り付けメニューから値を選ぶか、右クリックメニューの値貼り付けを利用します。
値貼り付け後のセルには数式が残らず、表示されていた数値だけが保存されます。
月次の締め処理や外部へ渡すデータでは、後から参照元を変更して金額が変わらないようにする目的で使われます。
元の数式へ戻せるよう、操作前に別シートへコピーしておくと安心です。
文字列としての数値固定
社員番号、商品コード、郵便番号など、先頭のゼロを消したくない数字は文字列として入力します。
入力前にセルの表示形式を文字列にするか、先頭にアポストロフィを付けて’00123のように入力します。
文字列化した数値は計算には使えないため、番号やコードの管理に限定するのが基本です。
数値計算とコード管理が同じ列に混在すると、並べ替えや集計で問題になることがあります。
【操作のポイント】再計算が必要なら絶対参照、結果を確定したいなら値貼り付けを選びます。
固定できないときの原因と確認項目
続いては、F4キーやドル記号が期待どおりに働かない場合の確認項目を解説していきます。
| 症状 | 確認内容 |
|---|---|
| F4で変わらない | 編集モードとFnキー |
| コピーで結果がずれる | ドル記号の位置 |
セル編集モードの確認
F4キーは、セルを選択しているだけでは参照形式を切り替えられない場合があります。
数式バーまたはセル内で数式を編集し、対象のセル番地にカーソルがある状態で押しましょう。
数式の編集モードでF4キーを押すことが、参照切り替えの基本です。
キーボードのFnロック設定によっては、Fnキーとの同時押しも試してください。
ドル記号の位置の確認
=$B2では列Bだけが固定され、行2はコピー先に応じて変わります。
下方向へコピーしても行を変えたくない場合は、=$B$2のように行番号の前にもドル記号が必要です。
目的と異なる参照形式になっていると、見た目は似ていても計算結果が大きく変わることがあります。
数式表示とエラー値の確認
セルに計算結果ではなく数式そのものが表示される場合、表示形式が文字列になっている可能性があります。
表示形式を標準に変更してから、F2キーとEnterキーで数式を再確定します。
参照先に文字列や空白があると、固定参照自体は正しくても計算エラーになることがあります。
数式の固定設定と、参照元セルのデータ形式は分けて確認しましょう。
【操作のポイント】固定できないと感じたときは、編集モード、Fnキー、ドル記号の位置、データ形式を順に確認します。
まとめ エクセルで数値を固定する方法（数式・ドル・F4）
Excelで数値やセル参照を固定する基本は、F4キーを使ってドル記号を付けることです。
$A$1は列と行を固定する絶対参照、A$1は行固定、$A1は列固定として使い分けます。
税率や単価など、コピー先でも同じ値を使うセルには絶対参照を設定してからオートフィルを行うことが大切です。
計算結果を変更不能な数値として残したい場合は、値貼り付けも活用しましょう。
数式をコピーした後に数式バーを確認すれば、参照ずれを早い段階で見つけられます。
