1. ホーム
  2. ブログ
  3. 絶対参照($)の使い方|コピーしても壊れない式

絶対参照($)の使い方|コピーしても壊れない式

#

この記事は 事務でよく使うExcel関数まとめ|目的から探せる一覧 の1本です。他の関数もまとめて見られます。

1行目は合っていたのに、下にコピーしたら途中から結果がおかしくなった。 原因は式ではなく、参照が一緒に動いてしまうことです。

これを止めるのが $(ドル記号) です。関数ではありませんが、これを知らないと関数が正しく動きません。

何が起きているのか

税率をB1に書いて、各行で掛け算します。

C4に =B4*B1 を入れ、60,000×10%=6,000 が出た画面
C4 に =B4*B1。ここまでは合っている。これを下にコピーすると……
式を下にコピーしたら5行目と6行目が 0 になった画面。数式バーは =B6*B3
2行目以降が 0。数式バーを見ると =B6*B3。掛ける相手の B1 まで一緒に下へ動いた。 B2、B3 は空欄なので 0。

Excelは式をコピーすると、参照先も同じ方向に動かします(相対参照)。金額の列はそれで正しい。税率は動いてほしくない。

$ は「動くな」という指示

C4を =B4*$B$1 に直して下までコピーし、全行の消費税が正しく出た画面。数式バーは =B6*$B$1
=B4*$B$1 にして下までコピー。どの行でも掛ける相手は B1 のまま。

$ の付け方は4通り

書き方意味よく使う場面
A1行も列も動く同じ行の中の計算
$A$1行も列も動かない税率・単価など、1か所の値を全体で使う
$A1列だけ固定右にコピーしても、見る列を変えたくない
A$1行だけ固定下にコピーしても、見る行を変えたくない
!

$ は「すぐ後ろを固定する」

$A1 → 後ろは A なので列が固定。A$1 → 後ろは 1 なので行が固定。

F4キーで切り替わる

  1. 数式バーで、対象のセル番地(B1)をクリック
  2. F4 を押す

    押すたびに B1 → $B$1 → B$1 → $B1 → B1

  3. 欲しい形で止めて Enter
!

ノートパソコンでは Fn+F4 のことがある

反応しなければ $ を直接打っても同じ(Shift+4)。

縦横に広がる表では、片方だけ固定する

担当者別・月別の表を、式1つのコピーで埋めます。

B2に =COUNTIFS(明細!$B:$B,$A2,明細!$C:$C,B$1) を入れて右と下にコピーし、担当者別×月別の件数表が埋まった画面
B2 に式を1つ書いて、右と下にコピー。$A2(列だけ固定)と B$1(行だけ固定)の組み合わせ。
部分書き方理由
担当者名$A2右にコピーしても担当者はA列から。下にコピーすると次の担当者へ
月B$1下にコピーしても月は1行目から。右にコピーすると次の月へ
参照する範囲$B:$Bどこにコピーしても同じ列

全部を $A$2 $B$1 にすると、全部のセルが同じ値になります。$ を1つも付けなければ、コピーした瞬間に全部ずれます。「何を動かしたいか」から逆算して $ を置く。

実務で $ が要る場面

  • 1か所の値を全行で使う:消費税率、為替、手数料率
  • マスタを参照する:XLOOKUP・VLOOKUPの「探す範囲」は必ず固定
  • 集計の範囲:SUMIFS・COUNTIFSの範囲。固定しないと下にコピーするほど範囲が短くなる
  • 縦横の集計表:$A2 と B$1 の使い分け

これまでの記事で マスタ!$A:$A や 明細!$D:$D と書いてきた理由そのものです。

よくあるつまずき

×

下にコピーすると、だんだん合計が小さくなる

=SUM(B2:B10) の範囲を固定していない。=SUM($B$2:$B$10)。

×

全部のセルが同じ値になった

固定しすぎ。動いてほしい方向の $ を外す。

確認のしかた

一番上の行で手計算と合うか。一番下の行で数式バーを見て、参照先が意図どおりか。 ずれは下にいくほど大きくなるので、一番下が正しければ全部正しい。

次に読むとよい記事


「昔から使っている集計ファイルの数字が、どうも怪しい」という場合、原因はこのあたりにあることが多いです。現状のヒアリングと課題整理は無料です。

#Excel#絶対参照#数式

この記事について

SUPRAXは、林業・製造・運送の現場経験をもとに、中小企業の業務改善・AI活用・GISの支援を行っています。 記事の内容について「うちの場合はどうか」を聞きたい方は、無料相談をご利用ください。

無料で相談してみる

あわせて読みたい

COUNTIFS関数の使い方|条件に合う件数を数える

「未処理が何件残っているか」を見るたびに、フィルタをかけて右下の件数を見て、外して、担当者で絞り直して……。 COUNTIFS(カウントイフズ)を使うと…

記事一覧へ戻る