ビジネス
エクセル時短術
2016/11/8 20:00

【Excel】作業を効率化する数式コピー術まとめ――基礎からセル参照のちょいテクまで一挙に紹介!

このコーナーでは、エクセルのいまさら聞けない基本的な機能や、達人が使っている超速ワザなど、オフィスワークに役立つ情報を紹介します。

 

数式を1つ作れば、それをコピーして表を完成させることができる、というのがエクセルの便利なところです。しかし、数値ではなく特定のセルを参照している数式の場合、コピーには少しコツが必要になります。今回はそんな数式のコピーにまつわるテクニックを基礎~応用まで一挙に紹介します。

 

【基礎編】いまさら聞けない数式の基本コピーワザ4選

数式のコピーとひとくちにいっても、実はいくつか方法があるということをご存知でしょうか?  まずは数式のコピー方法を基礎から紹介します。

 

1. 「■」(フィルハンドル)をドラッグしてコピーする

ポインターを「■」に合わせ「+」に変わったところでドラッグすると、数式がコピーできます。

084-01
↑セル右下の 「■」にポインターを合わせ「+」に変わったらドラッグします

 

084-02
↑ 数式がコピーされます

 

2. 「■」(フィルハンドル)をダブルクリックしてコピーする

数式を縦方向にコピーする場合は、「■」をダブルクリックしましょう。縦長の範囲にコピーする場合は、ドラッグより素早くコピーできます。

084-03
↑ 「■」をダブルクリックします

 

084-04
↑ 数式がコピーされます

 

3. ショートカットキーでコピーする

コピー元とコピー先が離れている場合は、ショートカットキーによるコピペを使います。

084-05
↑ コピー元のセルを選択してCtrl+Cキーを押し、コピー先を選択してCtrl+Vキーを押します

 

084-06
↑ 数式がコピーされます

 

4. 貼り付けオプションを使って数式だけをコピーする

表が色分けされている場合など、セルの書式をコピーしたくない場合は、貼り付けオプションで「数式」を選択します。

084-07
↑ セルを選択してCtrl+Cキーを、コピー先でCtrl+Vキーを押し、「貼り付けオプション」 → 「数式」を選択します

 

084-08
↑ 数式だけがコピーされ、書式はコピーされません

 

【応用編】セル参照を使った数式のコピーには3通りある!

数式などで数値を打ち込んで計算するのではなく、「A1*B1」といったように数値が入力されたセルを指定する方法を「セル参照」と呼びます。このセル参照には大きく「相対参照」と「絶対参照」、そして「複合参照」という3通りの参照方法があることをご存知でしょうか? 1つの数式でセルを参照する際は、どれを使っても計算結果に変わりはありません。しかし、数式をコピーするときには使い分けに注意が必要です。

 

1.数式のコピーと相対参照

「参照するセルをクリックする」という通常のセル参照の方法であれば、相対参照になります。基本編の例もすべて相対参照です。相対参照を使用した数式をコピーすると、参照するセルそのものではなく「左隣のセル」といった位置関係がコピーされます。

 

ここでは、商品の価格と個数を掛ける数式を例にみてきましょう。

081-01
↑ セルD2(=B2*C2)では2つ左のセルB2と、左隣のセルC2を参照しています。セルD2の数式をセルD3にコピーすると、「2つ左のセル×左隣のセル」という関係性が維持され、セルD3(=B3*C3)というように数式が自動的に変更されます

 

2. 数式のコピーと絶対参照

一方、絶対参照では、数式をコピーしても参照先は変わりません。絶対参照をするには、列番号と行番号の前に「$」(ドルマーク)を付けます。

 

ここでは、商品の価格に消費税率を例にみていきましょう。この例でいう消費税率のように、数式の中に固定したい参照先がある場合は絶対参照が有効です。

081-02
↑ セルC3(=B3*$C$1)は、 数式のなかで消費税率のセルC1を絶対参照しています。 セルC3の数式をセルC4にコピーすると、セルC4(=B4*$C$1)というように、絶対参照に指定したC1は変わりません

 

ちなみに、上の例でC3が「=B3*C1」という消費税率のセルC1を相対参照した数式だった場合、これをコピーすると「左隣×2つ上のセル」という位置関係が参照されるので、セルC4は「=B4*C2」となります。ここでは、セルC2には「消費税」と文字が入力されているためエラーが表示されてしまいます。

081-03
↑ 相対参照でコピーすると、参照先が自動で変わります

 

前述のとおり、絶対参照をするには、列番号と行番号の前に「$」(ドルマーク)を付けます。絶対参照の「$」は、参照するセルを指定してF4キーを押すと入力できます。

081-04
↑ 「=B3*」と入力し絶対参照するセルC1を指定し、F4キーを押します

 

081-05
↑ 「C1」が「$C$1」に変わります。Enterキーを押すと、数式が入力されます

 

3. 数式のコピーと複合参照

数式をコピーした際に、相対参照は参照先の位置関係(例:左隣のセル)を、絶対参照は参照先のセルそのもの(例:C3セル)を固定させますが、複合参照は列や行のいずれか(例:B列)を固定させることができます。

 

複合参照を指定する際、行番号を固定する場合は、セルを指定してF4キーを2回押して行番号にのみ「$」を付けます。列番号を固定する場合は、F4キーを3回押して列番号にのみ「$」を付けます。

 

ここでは、ドル円換算表で5ドルと10ドルの換算式を作成します。

082-01
↑ 1ドル99円というのは固定したい値なので、まず、絶対参照を使ってセルB3に「=$A$3*」と入力します。そして、次に行だけ固定するために、複合参照するセルB2を指定し、F4キーを2回押します

 

082-02
↑ 「=$A$3*B$2」に変わります。Enterキーを押します

 

これで「絶対参照×複合参照」の数式が入力されました。

 

続いて、上で行番号を固定した複合参照が入った数式を列方向にコピーしてみます。

082-03
↑ セルB3をセルC3にコピーします

 

082-04
↑ セルC3に数式がコピーされ、10ドルの換算結果が表示されます(セルC3: =$A$3*C$2)

 

このように、セルを列方向にコピーすると、固定されていない列番号は「B」から「C」へ変わりますが、固定されている行番号は「2」のまま変わりません。もちろん、絶対参照されているA3セルもそのままです。

 

セル参照は複雑なように感じるかもしれませんが、何もしなければ相対参照、コピー時に固定したいものがあれば行や列番号に「$」をつける、とイメージすれば理解しやすいのではないでしょうか。数式コピーを活用して、作業をよりスムーズに進めてみましょう。

TAG
SHARE ON