Excelで合計、割合、税込み価格などを計算するとき、同じ計算式を行ごとに入力し直すのは手間がかかります。
数式のコピー、オートフィル、貼り付けを使い分ければ、1行目に作成した式を表の最終行まで素早く連続入力できます。
一方で、コピー後に参照セルがずれる、数式が計算されない、連番だけになってしまうといったつまずきも起こりがちです。
この記事で押さえるポイント
・フィルハンドルで計算式を下方向へコピーする方法
・相対参照、絶対参照、複合参照によるずれの防止
・コピーできないときの表示形式、計算方法、保護設定の確認
ここでは、1行目を見出しとして、2行目からデータを入力した売上表を例に解説します。
最初の計算式を正しく作り、参照の種類を理解することが、ミスのない連続入力への近道です。
エクセルで計算式をコピーする方法1【フィルハンドルによる連続入力】
それではまず、最も基本的なフィルハンドルを使った計算式のコピーについて解説していきます。
| 商品名 | 単価 | 数量 | 金額 |
|---|---|---|---|
| ノート | 120 | 3 | =B2*C2 |
| ペン | 150 | 2 | |
| 付箋 | 280 | 4 | |
| ファイル | 350 | 1 |
入力する数式
=B2*C2
B列の単価とC列の数量を掛け、D列に金額を表示する式です。
フィルハンドルの位置
まず、D2セルに「=B2*C2」と入力して、Enterキーを押します。
数式が入ったD2セルを選ぶと、選択枠の右下に小さな四角形が表示されます。
この小さな四角形がフィルハンドルです。
フィルハンドルは値、連番、書式、数式を周囲のセルへ展開するための操作部分です。
マウスポインターを右下の四角形に重ね、黒い十字の形に変わったことを確認します。
ポインターの形が白い太い十字のままではセル選択になっているため、少し位置を調整しましょう。
ドラッグによる下方向コピー
続いては、フィルハンドルをドラッグして数式を連続入力する操作を確認していきます。
D2セル右下のフィルハンドルをつかみ、D5セルまで下へドラッグします。
マウスボタンを離すと、D3には「=B3*C3」、D4には「=B4*C4」、D5には「=B5*C5」が自動的に入ります。
これは、コピー元の式が行の移動量に合わせて変化する相対参照だからです。
式を1件ずつ書き換えなくても、Excelが各行の単価と数量を対応付けて計算します。
【操作のポイント】ドラッグ先はデータが入力されている最終行までにします。空白行まで広げると、見た目には空欄でも数式が入るため、集計範囲を確認するときに迷うことがあります。
ダブルクリックによる最終行までの展開
データ件数が多い表では、フィルハンドルを最終行までドラッグするよりダブルクリックが便利です。
D2セルを選択し、右下のフィルハンドルをダブルクリックします。
Excelは通常、隣接する列の連続したデータ範囲を読み取り、その最終行まで数式をコピーします。
今回の表ではB列またはC列のデータがD5まで続いているため、D2の式がD5まで展開されます。
途中に空白セルがあると、その直前でコピーが止まる場合があります。
その場合はドラッグ、またはコピーと貼り付けを使うと確実です。
【操作のポイント】ダブルクリックは表が途切れず入力されているときに有効です。数百行の明細表でも短時間で数式を入力できます。
エクセルで計算式をコピーする方法2【コピーと貼り付け】
続いては、コピーと貼り付けを使って離れた範囲や複数セルに数式を入れる方法を確認していきます。
| 商品名 | 単価 | 数量 | 金額 |
|---|---|---|---|
| ノート | 120 | 3 | =B2*C2 |
| ペン | 150 | 2 | 貼り付け先 |
| 付箋 | 280 | 4 | 貼り付け先 |
| ファイル | 350 | 1 | 貼り付け先 |
ショートカットキー
コピーはCtrlキーとCキーです。
貼り付けはCtrlキーとVキーです。
Windows版Excelでは、マウス操作と組み合わせると作業時間を減らせます。
単一セルをコピーする操作
まずは、D2セルの数式をコピーして別のセルへ貼り付ける基本操作について解説していきます。
D2セルを選択し、Ctrlキーを押しながらCキーを押します。
セルの周囲に動く点線が表示されたら、コピーできている状態です。
点線はコピー元が選択されている合図であり、Escキーを押すと解除できます。
貼り付け先のD3セルを選択してCtrlキーとVキーを押すと、数式と表示形式が貼り付けられます。
貼り付け後のD3セルには「=B3*C3」が入り、参照先が自動調整されます。
範囲を選択して一括貼り付けする操作
続いては、複数行を選択して一度に数式をコピーする方法を確認していきます。
コピー元のD2セルをコピーした後、D3からD5までをドラッグして選択します。
そのままCtrlキーとVキーを押すと、選択した各セルへ数式が相対参照の状態で貼り付けられます。
選択範囲の先頭だけでなく、各行に対応した式が作られる点が特徴です。
コピー元が1セルでも、貼り付け先を複数セルにすれば連続入力できます。
【操作のポイント】貼り付け範囲に結合セルが含まれているとエラーになることがあります。表の計算列ではセル結合を避けると管理しやすくなります。
数式のみを貼り付ける操作
続いては、罫線や色を変えずに数式だけをコピーする方法について解説していきます。
コピー後、貼り付け先のセルを右クリックし、貼り付けオプションから「数式」を選択します。
ホームタブの貼り付けメニューにある「数式」でも同じ操作ができます。
通常の貼り付けでは数式と書式が一緒にコピーされますが、数式のみの貼り付けなら貼り付け先の背景色、罫線、表示形式を保てます。
既存の帳票デザインを崩したくない場合に役立つ方法です。
【操作のポイント】数式だけを貼り付けた後は、通貨や日付などの表示形式が意図どおりか確認します。数値そのものは同じでも、見え方が変わることがあります。
計算式がずれる原因とセル参照の設定
続いては、計算式をコピーしたときに参照先がずれる原因と、ドル記号を使った設定を確認していきます。
| 商品名 | 金額 | 税率 | 税込金額 |
|---|---|---|---|
| ノート | 360 | 10% | =B2*(1+$C$2) |
| ペン | 300 | 下へコピー | |
| 付箋 | 1120 | 下へコピー |
固定したいセルを含む数式
=B2*(1+$C$2)
B2は行ごとに変える相対参照、C2は税率として固定する絶対参照です。
相対参照による自動調整
まずは、通常のコピーで使われる相対参照について解説していきます。
「=B2*C2」をD2からD3へコピーすると、「=B3*C3」に変わります。
列記号にも行番号にもドル記号が付いていない参照は、コピー先に応じて移動します。
明細ごとに単価、数量、金額を対応させる場合には、この動きが適しています。
相対参照は、同じ形の計算を各行や各列へ繰り返すための基本設定です。
【操作のポイント】ずれが起きたように見えるときでも、明細行を計算する式なら相対参照が正しい場合があります。式バーでコピー前後の参照先を見比べましょう。
絶対参照による税率や単価表の固定
続いては、コピーしても参照セルを動かさない絶対参照を確認していきます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品名 | 金額 | 税率 | 税込金額 |
| 2 | ノート | 360 | 10% | =B2*(1+$C$2) |
| 3 | ペン | 300 | ↓ |
税率がC2セルだけに入力されている場合、式を下へコピーしても参照先は常にC2でなければなりません。
このとき「=B2*(1+C2)」のままコピーすると、次の行ではC3、さらに次はC4を参照してしまいます。
「=B2*(1+$C$2)」と入力すると、列Cと行2の両方が固定されます。
絶対参照は、全行共通の税率、割引率、基準値、換算レートを参照するときに欠かせません。
【操作のポイント】数式内で参照セルを選択した状態でF4キーを押すと、相対参照、絶対参照、複合参照を順番に切り替えられます。
複合参照による行または列の固定
続いては、表を横方向と縦方向の両方にコピーするときに便利な複合参照を確認していきます。
「$C2」は列Cだけを固定し、行番号はコピー先に合わせて変化します。
「C$2」は行2だけを固定し、列記号はコピー先に合わせて変化します。
たとえば、商品別の単価が縦方向に並び、月別の係数が横方向に並ぶ表では、複合参照が役立ちます。
どこを動かし、どこを固定したいのかを先に決めると、ドル記号の付け方を判断しやすくなります。
参照形式の整理
A1は列と行が動く相対参照です。
$A$1は列と行を固定する絶対参照です。
$A1は列固定、A$1は行固定の複合参照です。
【操作のポイント】コピー後に意図しないセルを参照した場合は、数式を削除する前に式バーを確認します。参照形式だけを直せば、計算結果を復旧できることがあります。
連続入力を効率化するオートフィルとテーブル
続いては、長い一覧表で計算式を効率よく入力するオートフィルとテーブル機能について解説していきます。
| 受注日 | 単価 | 数量 | 売上 |
|---|---|---|---|
| 4月1日 | 500 | 2 | =B2*C2 |
| 4月2日 | 800 | 3 | 連続入力 |
| 4月3日 | 250 | 5 | 連続入力 |
オートフィルオプションの使い分け
まずは、フィルハンドル操作の直後に表示されるオートフィルオプションについて解説していきます。
数式をドラッグコピーした後、範囲の右下付近に小さなオートフィルオプションのボタンが表示されることがあります。
このボタンから「セルのコピー」や「書式のみコピー」などを選べます。
数式を維持したい場合は、通常は「セルのコピー」の状態で問題ありません。
計算結果だけでなく罫線や背景色まで変わってしまったときは、オートフィルオプションを見直します。
【操作のポイント】連番として入力したい数字と計算式では適切な設定が異なります。数式列では、連続データではなくセルのコピーを選ぶ意識が大切です。
テーブルの計算列
続いては、Excelテーブルで数式を自動入力する計算列を確認していきます。
表内のセルを選択し、挿入タブまたはホームタブから「テーブルとして書式設定」を実行すると、通常の範囲をテーブルに変換できます。
テーブルの金額列で最初の数式を入力すると、同じ列のデータ行へ数式が自動的に入力されることがあります。
さらに新しい行を表の末尾に追加した場合も、計算列として数式が引き継がれます。
行数が毎月増える売上台帳や在庫表では、テーブル化によってコピー漏れを防げます。
【操作のポイント】テーブルでは「=B2*C2」のような通常の参照が、列名を使う構造化参照に変換される場合があります。計算結果が正しければ問題ありません。
キーボードによる数式の一括入力
続いては、マウスを使わず選択範囲へ同じ数式を入力する方法について解説していきます。
数式を入れたいD2からD5までを選択した状態で、「=B2*C2」と入力します。
Microsoft 365などの新しいExcelでは、CtrlキーとEnterキーを押すと、選択範囲に入力内容をまとめて反映できます。
ただし、同じ文字列の数式が各セルに入るのではなく、通常は各位置に応じた相対参照に調整されます。
複数の離れた範囲へ同時に入れたいときは、Ctrlキーを押しながら範囲を選択する方法もあります。
【操作のポイント】操作前に数式を入力する範囲だけを選択します。見出し行や小計行まで選ぶと、不要なセルにも式が入るため注意しましょう。
計算式をコピーできない場合の確認項目
続いては、ドラッグ、コピー、貼り付けができない、または計算結果が表示されない場合の確認項目を解説していきます。
| 症状 | 主な原因 | 確認先 |
|---|---|---|
| 数式が文字列で表示される | 表示形式が文字列 | ホームタブの表示形式 |
| コピー後に結果が変わらない | 計算方法が手動 | 数式タブの計算方法 |
| 貼り付けできない | 保護、結合セル、フィルター | 校閲タブとセル状態 |
数式が文字列として表示される状態
まずは、「=B2*C2」と表示されるだけで計算されない状態について解説していきます。
セルの表示形式が「文字列」になっていると、Excelは先頭のイコールを計算式ではなく文字として扱います。
対象セルを選択し、ホームタブの表示形式を「標準」または「数値」に変更します。
その後、F2キーを押して数式を編集状態にし、Enterキーを押すと計算が実行されます。
表示形式を変更しただけでは、すでに入力済みの文字列数式が自動で計算式に戻らないことがあります。
【操作のポイント】先頭にアポストロフィが付いている場合も文字列扱いです。式バーを見て、イコールの前に余分な記号や空白がないか確認します。
計算方法が手動になっている状態
続いては、数式はコピーできたのに計算結果だけが更新されない場合を確認していきます。
数式タブの計算方法の設定が「手動」になっていると、元データを変更しても結果がすぐに変わりません。
数式タブから計算方法の設定を開き、「自動」を選択します。
一時的に計算を更新したい場合は、F9キーで再計算を実行する方法もあります。
手動計算は大きなブックの動作を軽くすることがありますが、通常の集計表では自動計算が扱いやすい設定です。
【操作のポイント】別ファイルを開いた後に計算方法が変わったように感じることがあります。結果が不自然なときは、最初に自動計算を確認しましょう。
シート保護と結合セルの状態
続いては、コピーや貼り付けの操作そのものが制限される場合について解説していきます。
シートが保護されていると、ロックされたセルには数式を貼り付けられません。
校閲タブで「シート保護の解除」が表示されている場合は、編集権限を持つ人に確認したうえで解除します。
また、コピー元と貼り付け先で結合セルの大きさが異なると、貼り付けができないことがあります。
フィルターで非表示になった行へコピーすると、想定外の場所まで入力されるケースもあります。
操作前の確認
・編集対象のシートが保護されていないか確認します。
・結合セルを含む範囲では貼り付け先の構成を確認します。
・フィルター中は表示セルだけを選択しているか確認します。
【操作のポイント】共有ファイルでは保護解除や構成変更が他の利用者へ影響します。コピーできない理由を確認してから変更することが安全です。
まとめ エクセルの計算式をコピーする方法
エクセルの計算式をコピーする基本は、最初のデータ行に正しい数式を作り、フィルハンドルをドラッグまたはダブルクリックすることです。
少ない件数ならコピーと貼り付け、長い表や追加行のある台帳ならテーブルの計算列を使うと作業を効率化できます。
コピー後に数式がずれる場合は、相対参照と絶対参照を見直し、固定したいセルにドル記号を付けます。
税率のように全行で共通の値を参照するなら「$C$2」、行または列だけを固定したいなら複合参照を使い分けましょう。
数式が表示されるだけ、計算が更新されない、貼り付けができないときは、セルの表示形式、計算方法、シート保護、結合セルを順に確認します。
コピー機能とセル参照を理解すれば、請求書、売上管理、勤怠集計などの表を正確かつ短時間で作成できるようになります。
