すでにあるExcelのシートで、部分的に再利用したいデータや数式をコピーして別の場所にペーストする機会はけっこうあるものです。
このとき、絶対参照と相対参照という概念が出てきます。セルを参照するときにセル番地が常に固定される方式が「絶対参照」で、数式に連動して変化する方式が「相対参照」です。
Excelの標準的な仕様は相対参照です。この記事では、シート内の式を絶対参照でコピペする方法をご紹介します。
ふつうにコピー＆ペーストすると「相対参照」に
一番わかりやすい相対参照の例を見てみましょう。Excelで関数の入ったセルをコピー＆ペーストすると、相対参照の形でペーストされます。
たとえば、A1からC1まで数値の入ったセルがあるとします。D1でその合計を求めたい場合、D4に入れる式は「=SUM(A1:C1)」です。
このセルをコピーし、一列下のD2にベースとすると、=SUM(A2:C2)に自動的に変化します。これが相対参照と呼ばれる機能です。
▲セルを選択してコピー＆ペーストすると自動的に参照セルの値が変わる
いちいち、合計するセルをA1:C1からA2:C2に書き換えなくても済むので、便利な気遣いですが、時には自動的に書き換えないでほしい場合もあります。
数式の一部に絶対参照を入れる
これに対して、参照位置を固定して変化させない方法を絶対参照と呼びます。式の一部に絶対参照を入れた例を示しましょう。
絶対参照は、列に対しても行に対しても設定することができます。変更したくない部分に対し「$」を付加するだけでいいのです。
たとえば、売上げを単価と個数であらわしている表を考えてみましょう。値上げした場合、単価の数字がかわります。そのため、単価はいちいち入力するより、独立したセルに記入しておくほうが一括変更できて便利です。
1月の売上げは、単価×個数となります。
=B1*B3です。
このセルをプルダウンしてコピーしていくと、相対参照で、
=B2*B4
というふうに変化していき、意味をなしません。そこで、単価の参照部分を「$B$1」として固定します。個数のB4はそのままです。
すると、この式をコピーしても、
=$B$1*B4
=$B$1*B5
というふうになっていき、単価はずっとB1のセルを参照、個数はB4、B5……とずれていきます。これで絶対参照と相対参照のメリットが生きます。
▲毎回参照する「単価」を$B$1として絶対参照にする
数式を一時的に無効にしてコピーを行う
前述の例のように最初からコピー先でも参照先が変更されないようにあらかじめ設計してあった場合はいいのですが、相対参照だけで作られた数式を、絶対参照のようにして他の場所にペーストするにはどうすればいいでしょうか。
すこし裏技的な方法を使ってみましょう。それは「置換」です。いったん数式を数式でなくしてからコピーし、ペーストしたあとで数式に戻すという方法です。
「CTRL+H」というショートカットキーで「検索と置換」画面を呼び出します。「検索する文字列」に「=」を、「置換語の文字列」に「 =」を入れてください。半角スペースと=です。「すべて置換」をクリックします。
▲「CTRL+H」で「検索と置換」画面を呼び出し、「=」を「 =」にすべて置換する
「=」は「これから後は数式」という印なので、その前に半角スペースが入ることで、セル内の数式はただの文字列となります。こうしてただの文字列となったセルを、移動したい場所にコピー＆ペースト。
▲文字列となったD列は数字ではなく文字が表示される。任意の場所にコピー＆ペーストする
そして、ペースト後にもう一度「検索と置換」画面を呼び出し、今度は逆の手順、すなわち「 =」を「=」に全置換することで、文字列を数式に戻します。こうして、相対参照の数式を絶対参照のまま別の位置に移すことができました。
すこしイレギュラーな方法ですが、ややこしい数式をいちいち絶対参照に書き換えるのは面倒というときに利用してみてください。置換作業のときに入力ミスをしないようにご注意くださいね。
インターネットを最大限楽しむには「ビッグローブ光」の光回線がオススメ！
「インターネット回線が遅い」
「インターネット料金が高い」
などのお悩みはありませんか？
安定した速度とリーズナブルな光回線なら、
IPv6接続(IPoE方式)*1で最大1Gbps*2の「ビッグローブ光」がオススメです。
「ビッグローブ光」なら他社の光回線（フレッツ光・コラボ光）をお使いでもお乗り換えはかんたんです。
工事費実質無料、おトクな特典もご用意しています。
*1 IPv6に対応していないサービス/サイトはIPv4接続となります。
*2 最大通信速度は光回線タイプによって異なります。最大通信速度はお客さまのご利用機器、宅内配線、回線の混雑状況などにより低下します。
