Excelで作成した数式を、オートフィルを使ってコピーすることがよくありますが、場合によっては、コピーされた数式が正しくないことがあります。相対参照と絶対参照の判断をしながら数式を作成することがポイントになります。
Excelで数式を作成する際の相対参照と絶対参照の使い分けのポイントと手順を説明します。
目次
Excelで絶対参照を使った数式を作成する6つの手順
Excelで売上額の構成比を求めてみます。
構成比は、「各商品の売上額÷全体の売上額」で求められます。
まずは、絶対参照を使わずに構成比を求め、次に、絶対参照を使って数式を作成してみます。
では、手順を説明していきます。
1.絶対参照を使わずに構成比を求める
ここでは、下の表を例にして説明します。
D3セルに売上額の構成比を求める数式を入力します。
=C3/C7
と入力し、Enterで確定します。
2.オートフィルで数式をコピー
オートフィルを使って、D7セルまで数式をコピーすると、次のようなエラー値が表示されます。
Excelの7つあるエラー値のうちのひとつです。
3.コピーされた数式を確認
ここで、D4セルを選択し、数式バーを確認してみます。
D3セルに「=C3/C7」と入力した数式は、D4セルにコピーされたとき、「=C4/C8」となっていることが確認できます。
「C3」→「C4」、「C7」→「C8」と変化していることがわかります。
このように、Excelで数式をコピーしたとき、コピーした位置に応じて、相対的に変化するものを「相対参照」といいます。
4.正しい数式を考えてみる
ここで、D3セルとD4セルにどのような数式を入力すれば、正しい構成比が求められるのか考えてみると、
D3セルには、「=C3/C7」、
D4セルには、「=C4/C7」という数式を入力すればいいことがわかります。
5.正しい数式を作成する
そこで、「C3」→「C4」は「相対参照」、「C7」はコピーしても変化しないように「絶対参照」にします。
まず、セル範囲D3～D7を選択し、数式を削除しておきます。
D3セルを選択し、「=C3/C7」と入力後、F4（ファンクション4）キーを1回押します。
「=C3/$C$7」となったことを確認し、Enterキーで確定します。
6.オートフィルで数式をコピーする
オートフィルで数式をコピーし、
パーセントスタイルにし、小数点以下第1位まで表示します。
このように、Excelで数式を作成する際は、入力すべき式を2つ考え比較します。
相対参照にするのか、絶対参照にすべきなのか考え、絶対参照にする場合、F4キーを1回押し、「＄」を付けます。
Excelの相対参照と絶対参照を理解し、作業を効率化しましょう。
まとめ
Excelで絶対参照を使った数式を作成する6つの手順
- 絶対参照を使わずに構成比を求める
- オートフィルで数式をコピー
- コピーされた数式を確認
- 正しい数式を考えてみる
- 正しい数式を作成する
- オートフィルで数式をコピーする