よくあるコピーのミスを修正する

コピー時のミスの多くには、1つの共通した原因があります。固定すべき参照が移動してしまうか、移動すべき参照が固定されたままになっているのです。

相対参照はデータを入力するにつれて移動するため、税率や見出しのように1つの共有セルを指す数式は、新しい行ごとにそのセルからずれていきます。その結果、誤った数値やエラーが発生します。

このレッスンでは、よくある問題と、それぞれの修正方法を説明します。

E1に税率0.08が入力されていて、D2に=C2*E1と入力してから下方向に入力するとします。結果を見てみましょう。

• D2 = =C2*E1（正しい状態）
• D3 = =C3*E2（E2は空です）
• D4 = =C4*E3（やはり空です）

Eの参照が税率のセルからずれてしまいました。2行目より下の行では0を掛けることになるため、どの行も0になります。

共有セルがずれないように固定します。入力する前に、数式を=C2*$E$1に変更してください。

ドル記号によって、E列と1行目の両方が固定されます。これで下方向に入力すると、次のようになります。

• D3 = =C3*$E$1
• D4 = =C4*$E$1

すべての行で正しい税率が使われます。Cは移動しますが、$E$1はその位置に固定されたままです。

数式を、参照先がシートの端から外れてしまう場所にコピーすると、#REF!が表示されます。

たとえば、=A2-B2のような数式をA列に置いて左方向にドラッグすると、参照できる有効なセルがないため、#REF!が返されます。このエラーは「参照先が存在しなくなった」という意味です。

参照先がシート内に収まる場所に数式を貼り付けるか、有効なセルを参照するように数式を書き直せば修正できます。

コピーした割り算の数式が空のセルに当たると、0で割ることになり、#DIV/0!が表示されます。

たとえば、C列の一部のセルが空白の状態で、=B2/C2を列の下方向に入力するとします。空のセルで割ると、エラーが発生します。

数式をIFERRORで囲み、代わりに空白や0を表示するようにします。例：=IFERROR(B2/C2,0)。割り算が可能な場所では計算し、計算できない場所ではエラーを非表示にできます。

よくあるミスの1つは、フィルハンドルを遠くまでドラッグしすぎて必要なセルを上書きしたり、入力済みの値の上に数式を貼り付けたりすることです。

すぐにCtrl+Z（Cmd+Z）を押して元に戻してください。スプレッドシートには十分な取り消し履歴があるため、誤って入力した複数回分の操作を順に戻せます。

元に戻したら、正しい最終行でドラッグを止めるよう注意して、もう一度入力してください。

フィルハンドルをドラッグすると、数式だけでなく書式もコピーされます。そのため、意図しない場所に罫線、色、通貨の書式が広がることがあります。

数式だけをコピーするには、ドラッグした後にオートフィル オプションボタンを使い、書式なしで入力を選択します。貼り付ける場合は形式を選択して貼り付けを使い、数式だけを選択すると、貼り付け先の見た目をそのままにできます。

何かがおかしいと感じたとき、最初にすることはいつも同じです。問題のあるセルをクリックして、数式バーを確認してください。

それぞれの参照先が意図した場所を指しているか確認します。本来$E$1であるべき定数がE5になっていれば、固定が不足しています。参照が#REF!になっていれば、参照先が削除されています。数式バーを見れば、原因不明の誤った数値を明確な修正方法に変えられます。

シート全体を一度に確認するには、数式の表示をオンにします。ExcelではCtrl+`（グレーブアクセントキー）を押し、Google Sheetsでは「表示」、続いて「数式の表示」を選択します。

すべてのセルに結果ではなく数式が表示されるため、列を確認して、参照がずれている行をすぐに見つけられます。ショートカットをもう一度押すと、通常の表示に戻ります。

最善の修正方法は、そもそも問題を起こさないことです。ドラッグする前に、すべての参照を確認し、それぞれが行ごとに移動すべきか、固定したままにすべきかを判断してください。

1つの税率、見出しの合計、検索表など、固定する必要があるものにはドル記号を付けます。簡単な方法は、参照内をクリックしてF4を押すことです。押すたびに固定方法が切り替わります。先に固定し、その後で入力してください。

手数料の列が、最初の行を除いてすべて0になっているとします。5行目をクリックすると、数式バーには=C5*E4と表示されますが、税率はE1に入力されています。

原因は明らかです。E1が固定されていなかったため、E4までずれてしまいました。元の数式を=C2*$E$1に修正してから、列に再入力してください。これですべての行が正しく表示されます。1つのドル記号で列全体を修正できました。

コピーしたときに壊れる数式を修正できるようになりました。

• ずれる定数は、$E$1のようにドル記号で固定すると修正できます。
• #REF!は参照先がシートの範囲外に出たことを、#DIV/0!は空のセルで割ったことを意味します。後者はIFERRORで対処できます。
• Ctrl+Zで誤った入力を取り消せます。書式なしで入力を使うと、書式をきれいに保てます。
• 数式バーまたは数式の表示で原因を確認し、入力前に参照を固定します。

「数式を正しくコピーする」の学習を完了しました。