Excel 数式がずれないようにコピー(オートフィル)する方法

2020年6月3日

Excelで似たような数式をコピーする際、オートフィル機能を使用すると効率的です。

この記事では基本的な使い方と数式がずれて欲しくない場面で固定する方法を紹介します。

サンプルファイル

下記のようなサンプルで進めます。

商品ごとに単価があり、それぞれに月毎の販売数量があります。

そこから掛け算で販売金額を出し、固定の手数料を足し消費税を加算します。

書いている時期が消費税増税間近のため月毎に消費税率を用意しています。

サンプル表

こちらのサンプルファイルを用意したので記事を見ながら試して頂くと効率的です。

手順

基本的なオートフィル

まず販売金額のセルに単価と数量を掛け算する数式を入れます。

販売金額のセルに単価と数量を掛け算する数式を入力

そのままコピー&ペーストしても良いですが、ここはオートフィルで手間を減らします。

セル選択すると右下に■マークが表示されるのでそれを下方向にドラッグします。

(マウスボタンを押しながら下に引っ張る)

数式を下方向にオートフィル

すると数式がコピーされ、セル参照の位置も自動調整されます。

この操作は縦・横どちらの方向も可能で

複数セルを同時に実施することも可能ですが

縦・横同時(斜め)には不可能です。

セルを複数して横方向にオートフィル

複数選択、横方向でも数式がコピーされます。

数式によってはこれで構いませんが、

このシートではセル参照が自動調整された結果、

数量は8月、9月と横に移動して正解ですが

単価も横に動いて7月、8月の数量を

参照する状態になってしまっています。

セルを複数して横方向にオートフィル(結果)

これを意図通りにオートフィルするには絶対参照という指定方法を利用します。

数式がずれないようにオートフィル(絶対参照)

絶対参照とは数式のセル参照をコピーオートフィルする際に

列や行がずれないように固定する方法です。

列のみ固定

先ほどの販売金額では単価の列(横)方向は移動してほしくないですが

縦(行)方向は移動してほしい状態です。

それを実現するには列の前に$を付けて$C8と入力しコピーオートフィルを行います。

単価の参照を列方向のみ固定した例

これにより単価のC列指定が固定されたまま数式のコピーが実行されます。

単価の参照を列方向のみ固定した例(結果)

行・列を共に固定

先ほどの販売金額に固定の手数料を加算する例です。

この場合は手数料セルは1つのため$K$4と指定した上で

数式をコピーオートフィルします。

手数料の行・列を共に固定した例

これにより全ての月の販売金額への固定手数料の加算が完了します、

手数料の行・列を共に固定した例(結果)

行のみ固定

最後に消費税を加える数式を記入します。

書いている時期は2019年09月で税率アップの直前です。

よって月によって税率が変わります。

その税率はM4~O4セルにあります。

この場合、行方向に数式をコピーしても

税率のセルの縦方向の参照は動いてほしくありません。

そのため行に$を付与しM$4とします。

税率の参照を行方向のみ固定した例

これにより税率の参照をずらさずに数式をコピーすることが可能です。

税率の参照を行方向のみ固定した例(結果)

関連記事

アルファベットをオートフィルで連続入力する方法(ユーザ設定での方法と関数での方法)

オートフィルやコピーに強いセル指定の方法(OFFSET関数とROW・COLMUN関数の組み合わせ)

マクロ・VBA 絶対参照を一括で設定・解除する方法