Excelで数量や単価を入力するだけで合計金額まで自動計算できる表を作れば、日々の集計作業を大幅に減らせます。
売上管理、見積書の下書き、在庫管理、家計簿などでは、計算式を一度正しく設定しておくことが重要です。
この記事では、1行目に見出しがあるサンプル表を使い、合計、数量、単価、消費税、オートフィルを使った自動計算表の作り方を順番に解説します。
自動計算表の基本は、数量に単価を掛けて金額を出し、最後にSUM関数で合計する流れです。
計算式を最初の行に入力してオートフィルすると、入力漏れや計算ミスを抑えやすくなります。
Excelで合計と数量を自動計算する基本設定
| 商品名 | 数量 | 単価 | 金額 |
|---|---|---|---|
| ノート | 3 | 120 | 360 |
| ペン | 5 | 80 | 400 |
それではまず、数量と単価から金額を求める基本設定について解説していきます。
ここではA列を商品名、B列を数量、C列を単価、D列を金額として使用します。
1行目は見出し、2行目から明細データとして扱うと、数式の範囲を分かりやすく管理できます。
表の見出しと入力列の準備
最初にA1からD1へ、商品名、数量、単価、金額と入力します。
数量はB列、単価はC列に集めることで、金額を計算するD列の役割が明確になります。
商品名のような文字列と、数量や金額のような数値を別の列に分けることが、後の並べ替えやフィルターにも役立ちます。
見出し行には塗りつぶしや太字を設定すると、入力欄と計算欄を見間違えにくくなります。
おすすめの列構成は、A列が商品名、B列が数量、C列が単価、D列が金額です。
必要に応じてE列に備考、F列に担当者を追加しても計算式には影響しません。
掛け算の数式の入力
金額を表示したいD2セルを選択し、半角のイコールから数式を入力します。
=B2*C2
この式は、B2セルの数量とC2セルの単価を掛け算する意味です。
数式を入力してEnterキーを押すと、数量が3、単価が120ならD2には360と表示されます。
セル参照を使う数式は、元の数量や単価を変更すると金額も自動で更新されます。
電卓で再計算する必要がなくなる点が、自動計算表の大きな利点です。
【操作のポイント】掛け算記号は、キーボードのアスタリスクではなく、Excelの数式では半角の記号として入力します。
数値の表示形式の調整
金額列は、数式が正しくても桁区切りがないと数字を読み取りにくい場合があります。
D列を選択してホームタブの桁区切りスタイルを使うと、10000が10,000のように表示されます。
通貨として扱う表なら、セルの書式設定で通貨や会計を選び、円記号を表示してもよいでしょう。
表示形式を変えてもセル内の計算結果そのものは変わりません。
数量は通常の数値、単価と金額は桁区切りというように、列ごとに表示を整えると実務向けの表になります。
【操作のポイント】金額に小数が不要な場合は、小数点以下の表示桁数を減らして見やすくします。
オートフィルによる数式の一括コピー
| 行 | 数量 | 単価 | 金額の数式 |
|---|---|---|---|
| 2 | 3 | 120 | =B2*C2 |
| 3 | 5 | 80 | =B3*C3 |
続いては、入力した計算式を明細行へ一括で反映するオートフィルを確認していきます。
商品が何十件もあるとき、各行に同じ構造の数式を手入力すると、参照セルを間違える原因になります。
D2に入力した式をオートフィルで下へコピーすると、行番号は自動的に変化します。
フィルハンドルを下方向へドラッグする操作
D2セルを選択すると、選択枠の右下に小さな四角形が表示されます。
この四角形はフィルハンドルと呼ばれ、下方向へドラッグすると数式をコピーできます。
たとえばD2の=B2*C2をD6までドラッグすれば、D3には=B3*C3、D4には=B4*C4という式が自動で入ります。
これは相対参照という仕組みによるもので、コピー先に合わせて参照位置が移動します。
【操作のポイント】明細の最終行までドラッグし、途中に空白行がないかを確認してから実行します。
連続データへのダブルクリック適用
隣の列に数量や商品名が連続して入力されている場合は、フィルハンドルをダブルクリックする方法も便利です。
Excelは隣接する連続データの最終行を判断し、その位置まで数式をコピーします。
明細が多い表では、ドラッグより短時間で処理できるでしょう。
ただし、途中に空白セルや空白行があると、意図した行までコピーされないことがあります。
オートフィル後は最終行の数式バーを確認すると、コピー漏れを発見しやすくなります。
【操作のポイント】ダブルクリック後に金額列の末尾を確認し、数式が入っていない行があれば追加します。
コピー後に確認する参照セル
オートフィルした後、D3やD4を選択して数式バーを確認してください。
D3の数式が=B3*C3となっていれば、相対参照によるコピーは正しく行われています。
もし全行で=B2*C2のままなら、数式を文字列として入力したか、参照方法に問題があるかもしれません。
数式の先頭にイコールがあることも、必ず確認しましょう。
数式は、表示された金額だけでなく、セルを選択したときに数式バーへ表示される内容まで確認することが大切です。
【操作のポイント】数式バーで行番号がコピー先に対応しているかを見ると、計算結果だけでは見つけにくい誤りも確認できます。
SUM関数による金額合計の算出
| 金額 | 内容 |
|---|---|
| 360 | ノート |
| 400 | ペン |
| 760 | 合計 |
続いては、明細ごとの金額をまとめて集計するSUM関数について確認していきます。
金額列を作っただけでは表全体の売上や支出は分からないため、最終行に合計欄を設けます。
SUM関数は指定範囲にある数値を合計する基本関数です。
合計行とSUM関数の入力
明細が2行目から10行目まである場合は、D11セルを合計欄にします。
=SUM(D2:D10)
この数式はD2からD10までの金額をすべて加算します。
範囲指定のD2:D10にあるコロンは、開始セルから終了セルまでを意味します。
合計欄の左隣のセルには合計と入力し、太字や上罫線で明細と区別すると見やすくなります。
【操作のポイント】合計セルには金額を手入力せず、必ずSUM関数を入れて更新に対応させます。
オートSUMボタンを使う操作イメージ
数式を手入力しなくても、ホームタブまたは数式タブのオートSUMを使って集計できます。
合計を表示したいD11セルを選択してオートSUMをクリックすると、Excelが上方向の数値範囲を候補として選択します。
点線で囲まれた範囲がD2からD10になっていることを確認してEnterキーを押します。
明細の間に空白がある場合は範囲が途中で止まることがあるため、ドラッグで範囲を指定し直してください。
【操作のポイント】オートSUMの候補範囲は必ず確認し、合計行自身を範囲に含めないようにします。
数量列を合計する数式
売上金額だけでなく、販売個数や発注数の合計を出したい場面もあります。
数量がB2からB10に入力されているなら、数量合計のセルには次の式を入力します。
=SUM(B2:B10)
金額合計と数量合計を同じ行に並べると、売上と個数をひと目で確認できます。
数量と金額は別々にSUM関数で集計することで、平均単価や目標との差額も後から計算しやすくなります。
【操作のポイント】数量欄には文字や単位を混在させず、数値のみを入力すると集計が安定します。
消費税と税込合計を含めた計算式
| 税抜合計 | 税率 | 税込合計 |
|---|---|---|
| 10,000 | 10% | 11,000 |
続いては、税抜金額に消費税を加えた税込合計の計算方法を確認していきます。
請求金額や見積金額を扱う表では、税抜合計と税込合計を区別して表示すると確認がしやすくなります。
税率をセルに入力する方法
税率をF1セルなど、表の分かりやすい位置に入力します。
F1セルに10パーセントと入力し、パーセント表示に設定すると計算式で利用できます。
税率を数式へ直接入力するより、専用セルに入力して参照する方が、税率変更時の修正箇所を一つにできます。
税率を一つのセルにまとめる設計は、計算表を長く使うときに便利です。
【操作のポイント】税率は10ではなく10パーセントとして入力し、セルの表示形式をパーセントにします。
税込金額を計算する数式
税抜合計がD11、税率がF1にある場合、税込合計には次の数式を使用できます。
=D11*(1+F1)
1に税率を加えることで、税抜金額へ消費税を上乗せできます。
税抜合計が10,000円で税率が10パーセントなら、計算結果は11,000円です。
掛け算の前後にある括弧を省略しないことが、意図どおりの計算につながります。
【操作のポイント】税率セルを変更したときに税込合計が更新されるか確認してから表を運用します。
端数処理に使うROUND関数
税計算では小数点以下の端数が発生することがあります。
円単位へ四捨五入したい場合は、ROUND関数を使います。
=ROUND(D11*(1+F1),0)
最後の0は小数点以下を表示しないという指定です。
切り捨てならROUNDDOWN関数、切り上げならROUNDUP関数を使い分けます。
端数処理のルールは会社や取引先の規定に合わせる必要があります。
【操作のポイント】税抜の明細単位で丸めるのか、合計後に丸めるのかを先に決めておきます。
入力ミスを防ぐ自動計算表の整え方
| 確認項目 | 設定例 |
|---|---|
| 数量 | 0以上の整数 |
| 単価 | 0以上の数値 |
| 金額 | 数式セルとして保護 |
続いては、自動計算表を安全に使い続けるための入力ミス対策を確認していきます。
数式が正しくても、数量や単価に誤った値が入力されれば結果も誤ってしまいます。
数式セルと入力セルの色分け
数量と単価の入力セルは白、金額や合計など数式が入るセルは薄い色で塗り分ける方法が有効です。
入力する場所が視覚的に分かるため、数式の上書きを防ぎやすくなります。
数式セルを手入力用のセルと同じ見た目にしないことが、初めて使う人にも親切な表づくりにつながります。
入力セルは淡い黄色、計算セルは淡い緑色など、用途が分かる配色を決めておくと便利です。
【操作のポイント】色だけに頼らず、見出し名にも数量入力や自動計算などの役割を表す言葉を入れます。
データの入力規則による数量制限
データタブのデータの入力規則を使うと、数量に不適切な値が入ることを防げます。
数量列には整数、最小値は0という条件を設定すると、文字列やマイナスの数量を入力しにくくなります。
単価列には小数を許可するか、円単位の整数だけにするかを業務に合わせて選びます。
入力規則は計算前の誤りを減らす仕組みとして役立ちます。
【操作のポイント】入力規則を設定した後は、試しに文字や負の数を入力し、警告が出るか確認します。
テーブル機能による計算範囲の拡張
明細が今後も増える表では、データ範囲をExcelのテーブルに変換する方法があります。
表内のセルを選択して挿入タブからテーブルを作成すると、新しい明細行を追加したときに数式や書式が引き継がれやすくなります。
通常のセル範囲では、明細を追加するたびに数式をコピーする必要があるかもしれません。
継続的に使う売上表や在庫表にはテーブル機能が向いています。
【操作のポイント】テーブル作成時は先頭行を見出しとして使用する設定を確認します。
まとめ エクセルで数量と合計の自動計算表を作る方法
Excelの自動計算表は、数量、単価、金額の列を作り、金額列へ掛け算の数式を入れることで作成できます。
最初の明細行に=B2*C2のような数式を入力し、オートフィルで下方向へコピーすれば、各行の金額を効率よく計算できます。
最後に=SUM(D2:D10)のようなSUM関数を設定すると、明細全体の合計金額も自動で更新されます。
数量合計と金額合計を別々に集計することで、販売数と売上額を同時に把握できます。
消費税を含める場合は税率を専用セルに置き、税込合計の数式から参照すると、変更にも対応しやすくなります。
入力セルと数式セルを色分けし、入力規則やテーブル機能を活用すれば、見やすく間違いにくい計算表へ育てられるでしょう。
