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

2019年9月4日

Excelで似たような数式をコピーする際、

オートフィル機能を使用すると効率的です。

この記事では基本的な使い方と

数式がずれて欲しくない場面で

固定する方法を紹介します。

サンプルファイル

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

商品ごとに単価があり、

それぞれに月毎の販売数量があります。

そこから掛け算で販売金額を出し、

固定の手数料を足し消費税を加算します。

書いている時期が消費税増税間近のため

月毎に消費税率が存在します。

サンプル表

こちらのサンプルファイルを

用意したので記事を見ながら

試して頂くと効率的です。

手順

基本的なオートフィル

まず販売金額のセルに単価と数量を

掛け算する数式を入れます。

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

そのままコピー&ペーストしても良いですが、

ここはオートフィルで手間を減らします。

セル選択すると右下に

■マークが表示されるので

それを下方向にドラッグします。

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

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

すると数式がコピーされ

セル参照の位置も自動調整されます。

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

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

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

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

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

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

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

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

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

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

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

これを意図通りにオートフィルするには

絶対参照という指定方法を利用します。

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

絶対参照とは数式のセル参照を

コピーやオートフィルする際に

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

列のみ固定

先ほどの販売金額では単価の

列(横)方向は移動してほしくないですが

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

それを実現するには列の前に$を付けて

$C8と入力しオートフィルかコピーを行います。

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

これにより単価のC列指定が固定されたまま

数式のコピーが実行されます。

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

行・列を共に固定

先ほどの販売金額に

固定の手数料を加算する例です。

この場合は手数料セルは1つのため

$K$4と指定した上で

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

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

これにより全ての月の販売金額への

固定手数料の加算が完了します、

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

行のみ固定

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

書いている時期は2019年09月で

税率アップの直前です。

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

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

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

税率のセルの縦方向の参照は

動いてほしくありません。

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

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

これにより税率の参照をずらさずに

数式をコピーすることが可能です。

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

関連記事

息抜きぬこ動画

連続データを効率的にセル入力する方法(オートフィルとフラッシュフィル)

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

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

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

フォローする