【Excel】エクセルで差額・差引残高を計算する方法（関数・自動計算・計算式）
Excelで売上と支出の差額、請求額と入金額の差引残高を管理したい場面は多くあります。
手入力で計算すると数字の更新漏れや計算ミスが起こりやすいため、数式を設定して自動計算できる表にすることが大切です。
差額だけを求めるのか、前月からの残高を繰り越すのかによって、使う計算式やセルの参照方法は変わります。
この記事では、サンプルデータを使いながら、Excelで差額・差引残高を計算する基本式、関数、オートフィルによる自動計算の方法を解説します。
差額は引き算の計算式で求めます。
差引残高は前行の残高に当行の増減額を加減して求めます。
1行目を見出し行として、データは2行目から入力する前提です。
取引件数が増えても正しく集計できるよう、参照先を確認しながら設定していきましょう。
エクセルで差額を自動計算する基本式
| A | B | C | D |
|---|---|---|---|
| 日付 | 売上 | 支出 | 差額 |
| 4月1日 | 120,000 | 35,000 | 85,000 |
| 4月2日 | 98,000 | 42,000 | 56,000 |
それではまず、売上や収入から支出を引く差額の基本的な計算方法について解説していきます。
差額は、比較したい二つの数値のうち、基準となる金額から差し引く金額を引いて算出します。
Excelでは数式の先頭に半角のイコールを入力することが基本です。
差額計算の入力手順
差額を表示したいD2セルをクリックします。
数式バーまたはセルに、B2からC2を引く式を入力します。
=B2-C2
Enterキーを押すと、B2セルの売上120,000円からC2セルの支出35,000円が引かれ、D2セルには85,000と表示されます。
セル参照を使うと、B2またはC2の金額を変更したとき、D2の差額も自動的に再計算されます。
金額を直接数式へ書き込むよりも、元データのセルを参照する方法が管理しやすいでしょう。
オートフィルによる下方向へのコピー
D2セルに正しい差額の計算式を入力できたら、同じ計算を下の行にもコピーします。
D2セルを選択し、右下に表示される小さな四角形のフィルハンドルを下方向へドラッグします。
表の途中に空白行がない場合は、フィルハンドルをダブルクリックしても、隣接するデータ行の末尾まで数式をコピーできます。
コピー後のD3セルには=B3-C3、D4セルには=B4-C4のように、行番号だけが自動で変わります。
これは相対参照と呼ばれる仕組みです。
数式をコピーした位置に合わせて、参照するセル番地も移動します。
取引明細のように同じ引き算を繰り返す表では、オートフィルを使うと入力時間を大きく減らせます。
差額がマイナスになる場合の表示
支出が売上より大きい場合、差額はマイナスの数値として表示されます。
たとえば売上が30,000円、支出が45,000円なら、計算結果は-15,000です。
マイナスを赤字や括弧付きで表示すると、赤字や不足額を見つけやすくなります。
D列を選択してセルの書式設定を開き、表示形式で数値または通貨を選びます。
ユーザー定義で正の数と負の数の書式を分ければ、負の数だけ赤色にする設定も可能です。
【操作のポイント】差額の計算式は、収入や売上を左側、支出や原価を右側に置くと、プラスとマイナスの意味を統一しやすくなります。
差引残高を繰り越す計算式
| A | B | C | D |
|---|---|---|---|
| 日付 | 入金 | 出金 | 差引残高 |
| 4月1日 | 100,000 | 20,000 | 80,000 |
| 4月2日 | 50,000 | 15,000 | 115,000 |
続いては、前回までの金額を引き継いで差引残高を自動計算する方法を確認していきます。
差額は1行内の二つの金額を比較する計算ですが、差引残高は前行までの残高を含める点が異なります。
現金出納帳、預金出納帳、売掛金管理表などでは、残高を1行ずつ積み上げる計算式が必要です。
最初の残高を求める計算式
残高を開始する最初のデータ行では、前行に繰越残高がないため、入金から出金を引く式を入力します。
この例ではD2セルを選択し、次の数式を入力します。
=B2-C2
入金100,000円、出金20,000円の場合、D2セルには80,000円が表示されます。
月初に前月繰越額がある場合は、別のセルに開始残高を入力し、そのセルを式に含める方法が便利です。
たとえばF1セルに開始残高を入力したなら、D2セルの式は=$F$1+B2-C2となります。
F1のように固定したいセルは、列記号と行番号の前にドル記号を付けて絶対参照にします。
二行目以降の残高計算
次の行からは、直前の残高に当日の入金を足し、出金を引く式を使用します。
D3セルには、前行のD2セルを参照する計算式を入力します。
=D2+B3-C3
前日の残高80,000円に入金50,000円を加え、出金15,000円を差し引くため、D3セルは115,000円になります。
この式を下方向へオートフィルすれば、D4では=D3+B4-C4という形に変化します。
残高列の式は直前行を参照するため、途中行の数式を消さないことが重要です。
途中の残高セルを上書きすると、その下の行にも影響が及ぶ可能性があります。
開始残高と繰越残高の管理
月ごとの表を作る場合は、前月末の残高を当月の開始残高として引き継ぎます。
開始残高をF1セルなどに置くと、明細データと区別しながら計算できます。
開始残高が50,000円の場合の最初の残高式です。
=$F$1+B2-C2
この式では、下方向へコピーしても開始残高の参照先はF1セルのままです。
入力した開始残高を変更するだけで、月内すべての差引残高を更新できます。
ただし、月末残高を次月へ手入力で転記する場合は、転記ミスに注意しましょう。
【操作のポイント】最初のデータ行と二行目以降では残高計算の式が異なるため、最初の行を確認してから二行目以降をオートフィルします。
SUM関数による累計残高の算出
| A | B | C | D |
|---|---|---|---|
| 日付 | 入金 | 出金 | 累計残高 |
| 4月1日 | 100,000 | 20,000 | 80,000 |
| 4月2日 | 50,000 | 15,000 | 115,000 |
続いては、SUM関数を使って入金と出金の累計から残高を求める方法を確認していきます。
直前行の残高を参照する方法に加え、累計範囲を指定する方法もあります。
SUM関数を使う方式は、途中の残高セルが空白になっても計算の根拠を追いやすい点が特徴です。
累計入金と累計出金の差額
D2セルには、先頭行から現在行までの入金合計から、出金合計を引く数式を入力します。
=SUM($B$2:B2)-SUM($C$2:C2)
最初のSUM関数はB2から現在行のB2までの入金累計を求めます。
二つ目のSUM関数はC2から現在行のC2までの出金累計を求めます。
計算式をD3以降へコピーすると、開始セルのB2とC2は固定され、終了セルだけがB3やC3へ変わります。
このため、各行でその時点までの累計入金と累計出金の差を自動表示できます。
Excel画面での数式入力
数式を入力するセルを選択した後、数式バーの入力欄をクリックします。
SUMと入力したら、候補からSUMを選んでも構いません。
範囲指定では、B2の前のドル記号を残し、後ろのB2だけを相対参照にすることがポイントです。
固定する場所とコピー時に変化させる場所を分けることで、累計式を正しくオートフィルできます。
開始残高を加えたSUM関数
開始残高がある場合は、SUM関数による差額に開始残高のセルを加えます。
=$F$1+SUM($B$2:B2)-SUM($C$2:C2)
F1セルに前月繰越額を入力しておけば、各行の累計残高へ開始残高が反映されます。
開始残高のセルF1は絶対参照なので、数式を下へコピーしてもF1から移動しません。
取引を追加した際は、数式が最終行までコピーされているか確認しましょう。
【操作のポイント】SUM関数の開始セルだけを絶対参照にすると、コピー先ごとに累計範囲が自然に広がります。
IF関数による空白行とエラーの処理
| A | B | C | D |
|---|---|---|---|
| 日付 | 入金 | 出金 | 差引残高 |
| 4月1日 | 100,000 | 20,000 | 80,000 |
続いては、未入力行を空白のままにして、見やすい残高表を作るIF関数の使い方を確認していきます。
あらかじめ多くの行へ数式をコピーすると、データがない行にも0が表示されることがあります。
IF関数を組み合わせると、入力前の行には計算結果を表示しない設定が可能です。
日付が空白なら結果も空白にする式
A列の日付が未入力なら、D列も空白にしたい場合はIF関数を使います。
=IF(A2=””,””,B2-C2)
この式では、A2セルが空白なら空文字を表示し、空白でなければB2-C2を計算します。
空文字は見た目には空白として扱われるため、未使用行に0が並ぶ状態を防げます。
日付以外にも、伝票番号や取引先名など、必ず入力される列を判定条件に使用できます。
残高計算にIF関数を組み合わせる方法
差引残高の二行目以降で空白行を非表示にするなら、D3セルへ次の式を入力します。
=IF(A3=””,””,D2+B3-C3)
A3セルが空欄のときはD3セルも空欄になり、日付を入力すると残高が表示されます。
残高を先に計算しておきたい場合でも、入力予定のない行まで数値を表示しないことで表の視認性が高まります。
ただし、日付を入力せず金額だけ入力しても計算されないため、入力ルールは表内で統一しましょう。
エラー表示を抑えるIFERROR関数
参照先にエラーが含まれる場合や、割り算を含む応用計算ではIFERROR関数も役立ちます。
差額計算そのものではエラーが起きにくいものの、他の表から金額を取得する式では注意が必要です。
=IFERROR(B2-C2,”確認中”)
計算が正常なら差額を表示し、エラーなら確認中と表示します。
エラーを無条件に隠すと入力ミスに気付きにくくなるため、原因を確認したうえで使用する姿勢が大切です。
【操作のポイント】空白行の判定には、入力漏れが少ない列を条件に選び、表全体で同じ入力順を守ります。
差額と残高を見やすくする表示形式
| 項目 | 計算結果 | 表示例 |
|---|---|---|
| 差額 | 85000 | 85,000円 |
| 赤字差額 | -15000 | -15,000円 |
| 残高 | 115000 | 115,000円 |
続いては、計算した差額と差引残高を確認しやすくする表示形式と条件付き書式を確認していきます。
数式が正しくても、桁区切りやマイナス表示が整っていないと数値を読み違えるかもしれません。
金額列は表示形式まで整えて初めて実務で使いやすい一覧表になります。
桁区切りと通貨表示
売上、支出、差額、残高の各列を選択し、ホームタブの桁区切りスタイルをクリックします。
85,000のように3桁ごとにカンマが表示され、大きな金額も確認しやすくなります。
円記号や円の文字を表示したい場合は、セルの書式設定から通貨または会計を選びます。
会計表示では通貨記号と数値の位置がそろうため、帳簿形式の表に向いています。
負の差額を赤くする条件付き書式
差額列や残高列を選択し、ホームタブの条件付き書式からセルの強調表示ルールを開きます。
指定の値より小さいを選び、値に0を入力すると、マイナス値だけへ書式を設定できます。
赤い文字や淡い赤色の塗りつぶしを指定すると、赤字や不足残高をすぐに把握できます。
数値そのものを変更せずに注意箇所を見つけられることが、条件付き書式の利点です。
数式と値の確認方法
計算結果ではなく数式を確認したいときは、対象セルをクリックして数式バーを見ます。
数式タブの数式の表示を使うと、ワークシート内の計算結果が数式表示へ切り替わります。
差額列でB列とC列を正しく参照しているか、残高列で直前行を参照しているかを点検しましょう。
特に行の追加や削除を行った後は、参照範囲が意図どおりか確認する習慣が重要です。
【操作のポイント】負の数だけを強調する条件付き書式を設定すると、赤字や残高不足を目視で確認しやすくなります。
まとめ エクセルで計算する差引残高・差額の方法
Excelで差額を計算する基本式は、売上や入金のセルから支出や出金のセルを引く=B2-C2です。
同じ式を複数行へ適用するときは、最初の数式を入力してからオートフィルでコピーする方法が効率的です。
差引残高では、最初の行に入金から出金を引く式を入れ、二行目以降は前行の残高を加える=D2+B3-C3のような式を設定します。
SUM関数を使うなら、=SUM($B$2:B2)-SUM($C$2:C2)によって、各行の累計入金と累計出金の差を求められます。
開始残高がある表では、開始残高のセルを絶対参照にして計算式へ加えると、月次の残高管理がしやすくなります。
未入力行を空白にするIF関数、負の数を見つけやすくする条件付き書式、桁区切り表示も組み合わせると、実務で使いやすい自動計算表に仕上がります。
差額と残高の意味を分けて、データの並びと計算式を統一し、正確な金額管理につなげましょう。
