Excelで数式を下方向や横方向へコピーすると、参照先まで自動で変わるため、集計表を素早く作成できます。
一方で、固定したい単価や税率までずれたり、数式が途中でコピーされなかったりして、意図しない計算結果になることもあります。
数式コピーでは、相対参照、絶対参照、複合参照、オートフィル、一括入力という仕組みを使い分けることが大切です。
下まで連続コピーするならフィルハンドルのダブルクリックが便利です。
参照先を変えたくないセルにはドル記号を付けた絶対参照を使います。
コピー後に数式バーで参照範囲を確認すると、ずれを早く発見できます。
この記事では、エクセルで数式をコピーする基本操作から、参照先を固定する方法、数式がずれる場合の対処法、一括入力の方法までを詳しく解説します。
エクセルで数式を下まで連続コピーする方法
| 商品 | 数量 | 単価 | 金額 |
|---|---|---|---|
| りんご | 3 | 120 | =B2*C2 |
| みかん | 5 | 80 | 下へコピー |
| ぶどう | 2 | 350 | 下へコピー |
それではまず、数式を下まで連続コピーする操作について解説していきます。
売上表のように同じ計算を複数行へ適用する場面では、数式を手入力で繰り返す必要はありません。
最初の計算式を作成してからコピーすれば、Excelが各行に合わせてセル参照を自動調整します。
フィルハンドルのドラッグ操作
フィルハンドルとは、選択中セルの右下に表示される小さな四角形です。
たとえばD2セルに=B2*C2と入力し、Enterキーで確定します。
するとD2には数量と単価を掛けた金額が表示されます。
D2セルをもう一度選択し、右下の小さな四角形にマウスポインターを重ねてください。
ポインターが細い黒い十字に変わったら、コピーしたい最終行まで下へドラッグします。
この操作により、D3には=B3*C3、D4には=B4*C4というように、行番号だけが変化した数式が入ります。
ドラッグ中に表示される枠線を確認すると、コピー先の最終行を間違えにくくなります。
データが数十行程度であれば、最も直感的に使える方法です。
ただし、長い一覧表ではドラッグ距離が大きくなり、行を見失うことがあるため次の操作が便利です。
フィルハンドルのダブルクリック操作
隣の列に連続したデータがある場合は、フィルハンドルをダブルクリックするだけで数式を末尾までコピーできます。
たとえばA列に商品名が入力されており、2行目から100行目まで空白なく続いているとします。
D2セルを選択して右下のフィルハンドルをダブルクリックすると、A列の連続データを目安にD100まで数式が自動入力されます。
下まで一括でコピーしたい場合は、ドラッグよりダブルクリックのほうが速く、操作ミスも減らせます。
ただし、判定に使われる隣接列の途中に空白セルがあると、そこでコピーが止まる場合があります。
途中で止まったときは、空白セルの有無を確認するか、範囲選択して貼り付ける方法へ切り替えましょう。
【操作のポイント】ダブルクリックでの連続コピーは、数式列の隣にある基準列が空白なく入力されている表で特に有効です。
コピーと貼り付けによる範囲指定
コピー先の行数を自分で正確に決めたい場合は、コピーと貼り付けを使います。
まず数式が入ったD2セルを選択し、CtrlキーとCキーを同時に押します。
続いてD3からD50など、数式を入れたい範囲を選択してからCtrlキーとVキーを押してください。
選択したすべてのセルに、元の数式を基準とした相対的な数式が配置されます。
この方法では、途中に空白行があっても選択範囲全体へコピーできます。
数式をコピーした後は、先頭行、途中行、最終行の数式を数式バーで確認しましょう。
参照がB2とC2から、B50とC50まで規則正しく変わっていれば正常です。
【操作のポイント】コピー先を先に範囲選択する方法は、空白行を含む帳票や、決まった行数だけ計算式を入れたい場合に向いています。
エクセルで参照先を変えずにコピーする方法
| 商品 | 金額 | 税率 | 税込金額 |
|---|---|---|---|
| りんご | 360 | 10% | =B2*(1+$C$1) |
| みかん | 400 | 10% | 下へコピー |
続いては、参照先を変えずに数式をコピーするための絶対参照を確認していきます。
税率、割引率、固定単価、基準日など、どの行でも同じセルを参照したい計算では絶対参照が必要です。
絶対参照のドル記号
Excelでは、セル番地の前にドル記号を付けると、コピーしても参照先を固定できます。
たとえばC1セルに10パーセントの税率があり、D2セルで税込金額を計算する場合を考えます。
D2セルの数式は、=B2*(1+$C$1)と入力します。
B2は商品ごとに変化する金額なので相対参照のままにし、C1だけを$C$1として固定します。
この数式をD3へコピーすると、金額の参照はB3へ変化しますが、税率の参照は$C$1のままです。
列記号と行番号の両方にドル記号を付けると、縦方向にも横方向にも参照先が動かなくなります。
相対参照はB2のような書き方です。
絶対参照は$B$2のような書き方です。
数式内で固定する値だけにドル記号を付けることが、正しいコピーの基本になります。
F4キーによる参照形式の切り替え
ドル記号をキーボードで入力しなくても、F4キーで参照形式を切り替えられます。
数式を編集している状態で、固定したいセル参照の文字列にカーソルを置きます。
そこでF4キーを押すと、C1、$C$1、C$1、$C1という順番で表示が切り替わります。
C1は行と列の両方が変わる通常の相対参照です。
$C$1は行と列の両方を固定する絶対参照です。
C$1は行だけを固定し、$C1は列だけを固定する複合参照です。
ノートパソコンではFnキーを押しながらF4キーを使う機種もあります。
【操作のポイント】参照形式を切り替える前に、数式バー内で対象セル番地の位置へカーソルを置くと、別の項目を変更する失敗を防げます。
複合参照を使う表計算
複合参照は、掛け算表や単価表のように、行と列の片方だけを固定したい場合に役立ちます。
たとえばB1からE1に月別の係数があり、A2からA10に商品別の基準額がある表では、行見出しと列見出しを組み合わせて計算します。
表の左上にあたるB2セルへ、=$A2*B$1と入力してください。
この数式を右方向と下方向へコピーすると、A列の基準額は列Aを保ち、1行目の係数は1行目を保ちながら参照先が変わります。
縦へコピーするときに固定したいのが列なら列記号の前にドル記号を付けます。
横へコピーするときに固定したいのが行なら行番号の前にドル記号を付けます。
【操作のポイント】複合参照は、どの方向へコピーするかを考えてから設定すると理解しやすくなります。
エクセルで数式を一括入力する方法
| A | B | C | D |
|---|---|---|---|
| 商品 | 数量 | 単価 | 金額 |
| りんご | 3 | 120 | =B2*C2 |
| みかん | 5 | 80 | 同時入力 |
続いては、複数のセルへ数式をまとめて入れる一括入力の方法を確認していきます。
大量の行に同じ規則の計算式を設定する場合は、コピー操作だけでなく、選択範囲への一括入力も便利です。
CtrlキーとEnterキーによる同時入力
同じ数式を複数セルへ一度に入力するには、先に入力先の範囲を選択します。
たとえばD2からD100をドラッグして選択し、数式バーに=B2*C2と入力します。
通常のEnterキーでは選択範囲の先頭セルだけに入力されますが、Ctrlキーを押しながらEnterキーを押すと、選択したすべてのセルへ数式が入力されます。
各行では相対参照が自動調整されるため、D3には=B3*C3、D4には=B4*C4が入ります。
CtrlキーとEnterキーは、事前に入力範囲が決まっている大量データで特に便利です。
1行目は見出しとして使い、実際の計算式は2行目から入力します。
見出しを含めて選択すると、文字列の見出しセルにも数式が入ってしまうため注意が必要です。
テーブル機能による自動計算列
データ範囲をテーブルに変換すると、計算式を入力した列へ自動で数式を反映できます。
表の内部にあるセルを選択し、挿入タブのテーブルを選ぶか、CtrlキーとTキーを押します。
先頭行をテーブルの見出しとして使用する設定を確認してから、OKを選択します。
金額列の最初のデータ行に=[@数量]*[@単価]のような数式を入力すると、同じ列の下方向へ計算式が展開されます。
テーブルでは新しい行を追加した場合にも、計算列の数式が引き継がれやすい点が特徴です。
【操作のポイント】データが追加され続ける管理表では、通常の範囲よりテーブルを使うと数式の入れ忘れを防げます。
ショートカットキーによる下方向コピー
キーボード中心で作業したい場合は、CtrlキーとDキーの組み合わせを使います。
まず、数式が入力されたD2セルからコピー先のD50セルまでを選択します。
このとき、数式があるD2セルを選択範囲の先頭に含めることが重要です。
選択後にCtrlキーとDキーを押すと、先頭セルの数式が下のセルへコピーされます。
横方向へコピーしたいときは、CtrlキーとRキーを使います。
CtrlキーとDキーは下方向への入力です。
CtrlキーとRキーは右方向への入力です。
どちらも元になる数式を選択範囲の上端または左端に置きます。
【操作のポイント】コピー元を含めて範囲選択してからショートカットキーを押すと、貼り付け先を間違えにくくなります。
エクセルで数式がずれる原因
| コピー前 | コピー後 | 確認点 |
|---|---|---|
| =B2*C1 | =B3*C2 | C1がずれる |
| =B2*$C$1 | =B3*$C$1 | C1を固定 |
続いては、数式コピーで参照先がずれる代表的な原因を確認していきます。
数式のずれは故障ではなく、Excelが相対参照の規則に従っている結果であることがほとんどです。
相対参照のまま固定セルをコピーした場合
最も多い原因は、固定すべきセルを相対参照のままコピーしていることです。
たとえばC1セルに税率があるのに、=B2*(1+C1)と入力して下へコピーすると、次の行ではC2を参照します。
C2が空白なら税率を計算できず、結果が変わったりエラーになったりします。
全行で共通の値を使うセルは、$C$1のような絶対参照へ変更する必要があります。
数式バーで一つ上の行と比較すると、どの参照先が変わっているかを把握しやすくなります。
【操作のポイント】数式をコピーする前に、固定値のセルだけをF4キーで絶対参照にしておくと修正作業を減らせます。
行や列の挿入による参照範囲の変化
計算式を作成した後に行や列を挿入すると、参照範囲が意図と異なる形へ変化することがあります。
SUM関数では、合計範囲の内部へ行を追加した場合は自動的に範囲へ含まれることが多い一方、範囲の外側では含まれない場合があります。
たとえば=SUM(D2:D10)の直後に新しい明細を追加した場合、合計範囲がD2:D11に変わっているか確認しましょう。
参照先が複数シートにまたがる数式では、シート名やセル範囲まで確認することが大切です。
数式タブの参照元のトレースを使うと、参照しているセルを矢印で確認できます。
複雑な計算表では、数式の表示を有効にして全体の参照構造を見る方法も有効です。
コピー元と貼り付け先の選択ミス
コピー元の数式がすでに誤っている場合は、そのまま下の行へ誤りが広がります。
また、フィルハンドルをドラッグするときに対象列を間違えると、別の列の書式や数式を上書きしてしまうことがあります。
作業前には、数式を入力する列の見出しと先頭セルを確認してください。
特に金額列、原価列、利益列が隣接する表では、アクティブセルの枠線を見てから操作する習慣が役立ちます。
誤ってコピーした直後なら、CtrlキーとZキーで元に戻せます。
【操作のポイント】大量コピーの前に数行だけ試し、計算結果と数式の両方を確認してから最終行まで展開しましょう。
エクセルで数式コピーを正確に行う確認方法
| 確認箇所 | 確認内容 | 目的 |
|---|---|---|
| 数式バー | セル参照 | ずれの発見 |
| 表示形式 | 通貨や日付 | 見た目の統一 |
続いては、コピーした数式を正確に管理するための確認方法を解説していきます。
計算結果だけを見るのではなく、数式、書式、エラー表示を順に確認すると、表の信頼性を高められます。
数式バーで参照先を比較する方法
コピー後のセルをクリックすると、数式バーに実際の数式が表示されます。
先頭行と次の行を順番に選択し、行番号だけが適切に変化しているか確認しましょう。
固定するはずのセルにドル記号が残っているかも重要な確認項目です。
表示された計算結果が偶然正しく見えても、参照先が誤っていればデータ更新時に問題が表面化します。
定期的に使うテンプレートでは、特に基準セルやマスターデータの参照を点検してください。
【操作のポイント】数式バーは、セル内に見えない長い数式や複数関数の参照先を確認する際にも役立ちます。
数式の表示による全体点検
数式タブから数式の表示を選択すると、計算結果の代わりにワークシート内の数式を表示できます。
CtrlキーとShiftキーと@キーを同時に押して切り替えることも可能です。
数式が縦方向に並んだ状態で確認できるため、途中の行だけ参照形式が違う箇所を見つけやすくなります。
表示を元へ戻すときは、同じ操作をもう一度実行します。
数式の表示中は列幅が広がる場合があります。
これは長い数式を表示するための挙動であり、計算結果が壊れたわけではありません。
エラー値と計算結果の確認
数式をコピーした後に、#REF!、#DIV/0!、#VALUE!などが表示される場合があります。
#REF!は参照先が削除されたときに起こりやすいエラーです。
#DIV/0!は割り算の分母が空白または0の場合に表示されます。
#VALUE!は数値として計算する場所に文字列が含まれる場合などに発生します。
エラーが最初に出た行を確認すると、コピー範囲の問題か元データの問題かを切り分けやすくなります。
【操作のポイント】エラーを空白に見せるためだけに数式を消すのではなく、参照先と入力データを確認して原因から修正しましょう。
まとめ エクセルで数式をコピーする方法（ずれる・一括・参照先を変えずに下まで連続コピー）
エクセルで数式をコピーする場合は、目的に合わせてフィルハンドル、コピーと貼り付け、ショートカットキー、一括入力を使い分けることが重要です。
短い表ではフィルハンドルのドラッグ、長い連続データではダブルクリック、決まった範囲にはCtrlキーとDキーが便利です。
参照先を変えずにコピーしたいセルには、$A$1のような絶対参照を設定します。
列だけまたは行だけを固定する複合参照を使えば、月別表や商品別表のような二方向コピーにも対応できます。
数式がずれたときは、ドル記号の有無、コピー元の数式、行や列の追加、選択範囲を順に確認しましょう。
コピー後は数式バーや数式の表示を使い、先頭行から最終行まで規則どおりに参照されているか点検することが大切です。
正しい参照形式を理解すれば、Excelの集計、売上管理、在庫管理、請求書作成などの作業を効率よく進められるようになります。
