関数を含むセルのコピー&ペーストと絶対参照

タイトルだと何のことなのかは分からないかもしれないが、選択したセルやセル範囲をコピー&ペーストすることは、エクセルが作業効率が高い表計算ソフトの証。


セルに含まれているのが文字列や数値という"値"と呼ばれる性質のものならばコピペも簡単にできるが、関数によって返される値をコピーするときは少し知識が必要。


つまり、セルに直接文字や数値が入っている場合は、何のためらいもなくコピーやペースト、切り取りが出来るが、セルが関数で表示されたものだった場合は、目的によってコピーやペースト、切り取りの方法が変わる。


この表では、「数値」が直接入力されているセルの右隣「金額」が関数で算出されている。




「数値」だけをコピペしたい場合、そのままコピペして何の問題もない。

しかし、「金額」をコピペすると関数が入ったままなので、コピペ先のセルの値が変化してしまう。
全部「\0」に。

全部「10倍の金額」に



原因は関数も生きたままコピーされるため、参照するセル、もしくはセル範囲が変化するから。
通常は「数値」の列を参照して10倍している。



しかし、関数をコピーすると、参照範囲が1つ右にずれるため、下の図では「空白」のセルを10倍していて、結果は「\0」。

下の図では、「金額」のセルの10倍をしているため、「数値」のセルから考えると「100倍」の値になってしまっている。





この問題を解決するには、2つの方法がある。コピペする目的によってそれは決まる。

1. まず単純に「金額」の数値のみを値としてコピペしたい場合は、貼り付けた前後に指定できる「値のみ」で貼り付ければよい。

◆コピーして貼り付ける前の場合
貼り付けたい先頭のセルを選択して右クリック「形式を選択して貼り付け」を選ぶ。

するとこんなダイアログが出てくるので「貼り付け」項目の「値」にチェックをして「OK」を押す。

すると金額のセルにあった「値」通りの数値で貼り付けられる。



◆コピーして貼り付けた後の場合
貼り付けた後でも、貼り付けた範囲のすぐ近くに表示される「書類」マークをクリックして、「値のみ」をクリックすればOK。

2. もう1つは、貼り付け先のセルでも「数値」の列を参照させて10倍するという関数を保持したままコピーしたい場合である。

解決策は「$」マークによるセルの絶対参照。
コピーしたいセルは「E68」を参照している関数の入ったセル。この「E68」の参照がズレなければ関数は生きたままコピーされる。



「E68」に「$」マークを付けることで行や列の参照場所を保持したままコピペすることが出来る。ドルマークはこんな風に付けられる。

表記	意味
$E$68	E列の68行目のセルを絶対参照
$E68	E列のセルを絶対参照して行は絶対参照しない
E$68	68行目のセルを絶対参照して列は絶対参照しない
E68	行も列も絶対参照しない

この指定をうまく使えばエクセルではいろんなことが出来る。


よってここでは「$E$68」を使う。
※注意は絶対参照に関数を変更する際は、「金額」の列のセル1つ1つに「$」マークを付加していく必要があるので、数が多いときは一括で「置換」をする。
関数は、
=E68*10
=E69*10
=E70*10
・
・
・
と並んでいるため、
「金額」の範囲を選択して「Ctrl + F」で検索を出し、「置換」を選ぶ。「E」→「$E$」へ一括置換する。



これで崩れないで関数ごとコピーできる形になった。
実際にコピーしてみると、



関数も崩れずコピペできている。




この方法は今回の掛け算のような簡単な関数だけでなく、自分で組んだ様々な関数に有効なので、表組みには欠かせない。