粗利率をExcelで計算する方法｜無料テンプレートと全体の集計式

粗利率は「粗利÷売上」で求めます。粗利は売上から原価を引いた金額です。Excelでは割り算の結果をパーセント表示にすれば、粗利率を確認できます。

Excelで粗利率を計算する基本式

売上がF9、原価がD9にある場合、粗利率の基本式は次のとおりです。両方の金額を入力し、売上が0円でないときに使えます。

=(F9-D9)/F9

売上10,000円・原価8,000円なら、粗利2,000円÷売上10,000円で結果は0.2です。式を入れたセルを選び、［セルの書式設定］の［表示形式］で［パーセンテージ］を選ぶと20％と表示されます。式に「×100」は加えません。無料テンプレートには、空欄や売上0円を確認する式も入れています。

複数の取引をまとめるときは、粗利合計÷売上合計で計算してください。下の2取引は20％と40％ですが、全体の粗利率は38％です。率の単純平均30％とは一致しません。

取引・金額	粗利・粗利率
取引A 売上10,000円 原価8,000円	2,000円 20％
取引B 売上90,000円 原価54,000円	36,000円 40％
合計 売上100,000円 原価62,000円	38,000円 38％

金額はすべて税抜・円の架空例です。原価欄には、その行の売上に対応する原価の合計額を入れます。

粗利率の無料Excel作例をダウンロード。登録不要で、上の2取引と6行分の入力欄・数式を収録しています。販売商品とは別の練習ファイルです。

開いたら「粗利率」シートのD9を8000から9000へ変えてください。H9が20.0％から10.0％、H5の全体率が38.0％から37.0％へ変わります。確認後はD9を8000へ戻します。

日々の売上を記録し、月次や取引先ごとの集計も続けたい方には、売上管理 Excelテンプレートがあります。Excelファイルと説明テキストをZIPで受け取り、設定とサンプルの置き換えから始められます。

販売版では「売上台帳」の7行目から日付・取引先・数量・単価・原価合計などを入れます。売上・粗利の式と集計画面は設定済みです。原価はI列へ入力します。

全体の粗利率と単純平均が違う理由

単純平均は、2つの取引の率を同じ重みで扱います。20％と40％を足して2で割ると30％です。しかし、取引Bの売上は取引Aの9倍あります。全体を求める計算では、この売上額の違いも反映する必要があります。

取引Aの粗利は2,000円、取引Bは36,000円です。粗利合計38,000円を、売上合計100,000円で割ると0.38になります。表示形式をパーセントにすると38％です。

=SUM(G9:G14)/SUM(F9:F14)

この基本式は、全行の入力がそろい、売上合計が0円でない場合の式です。G列が粗利、F列が売上です。無料作例では未入力を確認する式も加えています。

作例のH17には比較用の単純平均、H5には全体率を表示しています。H17の式は次のとおりです。全体の粗利率として使うのはH5です。

=IF(ISNUMBER(H5),IF(COUNT(H9:H14)=0,"",AVERAGE(H9:H14)),H5)

全取引の売上額が同じ場合など、単純平均と全体率が一致することもあります。売上額が変わっても正しく集計できるよう、合計した金額から率を求めてください。Microsoftの平均・加重平均の説明でも、単純平均と重みを付けた平均を分けています。

粗利率の無料Excelテンプレートを使う

元のファイルを保管し、コピーを開いてください。「粗利率」のA9:D10にあるサンプル2件の内容を消し、黄色のA9:D14へ自分の取引を入れます。E～H列の数式と、5行目・17行目は残してください。行そのものは削除しません。

入力する列	入れる内容
A列	取引を区別する名前
B列	数量。取引Aは5
C列	1個・1件あたりの単価。取引Aは2000
D列	その行全体の原価合計。取引Aは8000

取引Aでは、数量5×単価2,000円で売上10,000円です。1個あたりの原価が1,600円なら、5個分の8,000円をD9へ入れます。D列へさらに数量を掛ける必要はありません。

1,600円をD9へ入れると、取引Aの粗利率は84％、全体率は44.4％になります。数値としては計算できるため、入力警告は出ません。原価の単位を確認して8000へ直すと、20％と38％へ戻ります。

Excelで粗利率の計算表を作る

数式から作る方は、新しいブックのシート名を「粗利率」に変更してください。A8:H8へ次の見出しを、A列から順に入れます。完成ファイルを使う方は、この作成手順をやり直す必要はありません。

列	8行目の見出し
A・B	取引／数量
C・D	単価（円）／原価合計（円）
E・F	入力状態／売上（円）
G・H	粗利（円）／粗利率

A9:D9には「取引A、5、2000、8000」、A10:D10には「取引B、30、3000、54000」を、それぞれ1セルずつ入力します。11～14行目は追加の取引用に空けておきます。

E9で、数量・単価・原価の入力を確認する

E9へ次の式を貼り付けてEnterを押してください。初期例では「入力済」と表示されます。A～D列がすべて空欄なら未使用の行として扱い、一部だけ入力した行は確認内容を表示します。

=IF(COUNTA(A9:D9)=0,"",IF(A9="","取引未入力",IF(OR(ISBLANK(B9),ISBLANK(C9)),"数量・単価未入力",IF(ISBLANK(D9),"原価未入力",IF(COUNT(B9:D9)<>3,"数値で入力",IF(MIN(B9:D9)<0,"負数を確認","入力済"))))))

ISBLANKで空欄を調べ、COUNTで3つの数値がそろっているかを確認します。原価0は数値なので計算できます。「8,000円」など単位を付けた文字列は「数値で入力」と表示します。負数を入力した場合も確認を促します。

F9・G9・H9で売上、粗利、粗利率を計算する

F9には数量×単価の式を入れます。初期例の結果は10000です。

=IF(E9="入力済",B9*C9,"")

G9には売上から原価合計を引く式を入れます。結果は2000です。

=IF(E9="入力済",F9-D9,"")

H9には粗利÷売上の式を入れます。売上が0円の行には「売上0」と表示します。

=IF(E9<>"入力済","",IF(F9=0,"売上0",G9/F9))

E9:H9をコピーし、E10:H14へ貼り付けてください。10行目は売上90000、粗利36000、粗利率0.4になります。

H9:H14を選択し、［セルの書式設定］の［表示形式］で［パーセンテージ］を選び、小数点以下を1桁にします。20.0％、40.0％と表示されれば設定できています。式に「×100」は加えません。Microsoftのパーセント表示の説明を参照できます。

B5・E5・H5で合計と全体率を出す

A5へ「売上合計」、D5へ「粗利合計」、G5へ「全体率」と入力します。B5には次の式を入れてください。初期例では100000になります。

=IF(COUNTA(A9:D14)<>COUNT(F9:F14)*4,"入力を確認",IF(COUNT(F9:F14)=0,"",SUM(F9:F14)))

1取引にはA～D列の4項目が必要です。入力セルの個数と、売上を計算できた行数×4が合わなければ「入力を確認」と表示します。原価だけが未入力の行を外して、残りの取引だけで全体率を出すことを防ぎます。

E5には粗利合計の式を入れます。B5が数値なら粗利を合計し、それ以外はB5の表示を引き継ぎます。初期例の結果は38000です。

=IF(ISNUMBER(B5),SUM(G9:G14),B5)

H5へ次の式を入れ、表示形式をパーセンテージ・小数1桁にしてください。38.0％になれば完成です。

=IF(ISNUMBER(B5),IF(B5=0,"売上0",E5/B5),B5)

B5・E5・C9:D14・F9:G14は数値の桁区切りを設定すると読みやすくなります。表示上の桁数を変えても、全体率は丸める前の計算結果から求めます。

空欄・0・赤字で表示が変わるときの確認

原価が空欄なら、確認してから合計する

D9を空欄にすると、E9は「原価未入力」、B5・E5・H5は「入力を確認」になります。D9へ8000を戻し、全体38.0％へ戻ることを確認してください。原価が本当にかからない取引には0を入れます。D9が0なら取引Aは100％、全体は46％です。

売上0円は、率と金額を分けて扱う

B9を0にすると、売上は0円、粗利は−8,000円です。H9は「売上0」と表示します。全体の粗利にはこの−8,000円を含めるため、粗利合計28,000円÷売上合計90,000円で31.1％になります。確認後はB9を5へ戻してください。

すべての取引の売上合計が0円なら、全体率も「売上0」です。未入力の行だけなら合計欄は空欄になります。この2つを0％とまとめないようにしています。

原価が売上を上回れば、負の粗利になる

D9を12000にすると、粗利は−2,000円、率は−20％です。全体率は34％になります。括弧付きの数値はマイナスの表示です。数式の故障ではないため、売上と原価を確認したうえで判断してください。練習後はD9を8000へ戻します。

無料作例の入力欄は0以上の数量・単価・原価を想定しています。返品などの負の入力値を含む台帳へ広げる場合は、集計対象と入力ルールを別途設計してください。

7件目を追加するときは、数式と合計範囲を延ばす

無料作例の入力欄は9～14行目の6取引です。7件目を入れる場合は、A14:H14をコピーして空いているA15:H15へ貼り付け、A15:D15を新しい取引に置き換えてください。入力欄の書式と入力規則も引き継ぎます。

次にB5・E5・H17の式を開き、範囲の終端「14」を「15」へ変更します。H5はB5とE5を参照しているので、そのまま使えます。15行目に入力するだけでは集計されません。

A15:D15へ「追加取引、2、5000、6000」を入れると、売上合計110,000円、粗利合計42,000円、全体率38.2％になります。元の2取引と足し合わせて確認してください。さらに増やすときは、17行目の比較欄や19行目以降の説明と重ならない場所へ表を広げます。

日々の売上入力から、月次・取引先別の確認へ進む

今回の無料作例は、全体の粗利率と入力の間違いを確かめるための小さな表です。日付を付けて取引を残し、月別や取引先別の集計表を自作したい方は、Excelで売上管理表を作る手順へ進めます。

数式や集計画面を設定した状態から使い始めるなら、売上管理 Excelテンプレートを選べます。無料作例と販売版は、次の用途で使い分けてください。

無料作例でできること

6取引の数量・単価・原価合計を入れ、各行と全体の粗利率を確認できます。単純平均との違いや未入力の表示を試す用途です。日付や取引先を使った月次集計は収録していません。

販売版に用意していること

売上台帳5,000行、取引先別300件の分析欄、月次分析、目標・前年実績と比較するダッシュボードを収録しています。日々の取引を入力し、対象月を選んで数字を確認するためのExcelです。

購入後はZIPを展開し、元ファイルを保管して「はじめに」を確認してください。その後「設定」で会社名・期首月・売上目標・カテゴリ・担当者を整え、売上台帳のサンプル入力を自分のデータへ置き換えます。数式セルは残してください。

販売版の原価入力も1行分の合計です。原価が空欄の売上行では「原価未入力」と表示し、該当する集計の粗利・粗利率の確定表示を保留します。原価0円の行には0を入れます。無料作例のセル番号や警告文は、販売版とは異なります。

販売中v1.2はPCでの利用をおすすめしています。銀行・POS・ECからの自動連携や個別のデータ移行は含みません。テンプレートは買い切りで、Excelなどの利用料金は別途必要です。

執筆・作例制作：SpreadHub。2026年10月16日確認。無料作例は入力変更や空欄・0など21件の計算テストと、保存後の再読込を確認しました。Microsoft Excel実機・PDF・実印刷は未確認です。掲載画像はファイルから描画した表示例です。