消費税の変更、値引額を各単価に割当計算表の作成です

質問

物品の請求書に値引額が記載されてきますが、単価の金額に応じて各単価に割当るのと、これから消費税も逐次変わりますので、５％・８％・１０％と任意に変更できそれに応じて計算できる関数を教えてください。
下記のような表をエクセルで作っています。

A B C D E F G H I

1 値引割当表（内税）
2
3 　　　　　　　　　　　　　　　　　　　 値引額：2,500 消費税率： 5％
4
5　　 No. 品　　　　　名 数量 　　　　価 格 　税込価格 値引価格
6 　　　　　　　　　　　　単　価　　金　額 単 価　　金 額　 単 価 金 額
7　　 1 　　　　　　　 500 　　 76 38,000 　
8
9　 2 　　　　　　　 100 　　200 20,000
10　　3
11　　4
12　　5
13　　計 　　　　　　　　　　¥58,000

回答No.2

kagakusuki

御質問の件を解決するためには、1つ問題が御座います。
　それは、金額の総計を税抜き価格の5％増しにした上で、2500円を引いた価格になる様にした場合、単価を1円未満の端数を持った値としなければならないという事です。
　もし、単価が整数値となる様にした場合には、金額の総計は、「元値の5％増しの金額から2,500円を引いた金額」とは異なった金額になってしまいます。
　例えば、¥58,000の5％増しは\60,900ですが、これを税込の単価に振り分けますと、¥79.8と\210になってしまい、元値が\76の品の単価に1円未満の端数が生じます。
　そして、\60,900から2,500円を差し引いた金額は\58,400ですが、これを値引き後の単価に振り分けますと、¥76.524137931…と¥201.379310345…になってしまい、1円未満の端数が生じます。

　消費税率を掛けた後に生じる1円未満の端数に関しては、切り捨ててしまって処理する場合も御座いますが、それをやってしまいますと、税込\79.8から1円未満の端数である\0.8を切り捨てて\79とした品が500個分の価格と、税込\210の品100個分の価格の合計は¥60,500になってしまい、¥58,000の5％増しと比べて\400も少ない金額になってしまいます。
　そこから更に\2,500を差し引きますと、\58,000となり、これを値引き後の単価に振り分けますと、¥75.735537190…と¥201.322314050…になってしまい、1円未満の端数が生じます。
　そこで、1円未満の端数を切り捨てて、¥75の品500個と\201の品100個としますと、金額の総計は\57,600にしかならず、¥58,000の5％増しから\2,500を差し引いた金額と比べて、\800も少ない金額になってしまいます。
　尚、この問題は、四捨五入で値を丸めた場合においても生じる事があります。

　この様に、1円未満の端数を、どの段階でどのように丸めるのかによって、計算結果が異なって来ますので、1円未満の端数をどうするのかをお決め願います。

　因みに、1円未満の端数があっても気にせずに、そのまま計算する場合の関数は次の様なものとなります。
　尚、御質問文の例では、列や行がずれてしまっていて、どの列や行に、どの値が入力されているのか定かではありませんので、仮に、E3セルに値引き額が入力されていて、I3セルに消費税率、C7以下に数量、D7以下に税抜きの単価、E7以下に品毎の税抜き価格を入力すると、
F7以下に税込の単価、G7以下に品毎の税込の価格、H7以下に値引き後の単価、I7以下に品毎の値引き後の価格をそれぞれ表示させるものとします。

　その場合、まず、E7セルに次の関数を入力して下さい。

=IF(COUNT(INDEX($C:$C,ROW()),INDEX($D:$D,ROW()))=2,INDEX($C:$C,ROW())*INDEX($D:$D,ROW()),"")

　次に、F7セルに次の関数を入力して下さい。

=IF(ISNUMBER(INDEX($D:$D,ROW())),INDEX($D:$D,ROW())*SUM(1,$I$3),"")

　次に、G7セルに次の関数を入力して下さい。

=IF(COUNT(INDEX($C:$C,ROW()),INDEX($F:$F,ROW()))=2,INDEX($C:$C,ROW())*INDEX($F:$F,ROW()),"")

　次に、H7セルに次の関数を入力して下さい。

=IF(ISNUMBER(INDEX($E:$E,ROW())),(1.05-SUM($E$3)/$E$12)*INDEX($E:$E,ROW())/INDEX($C:$C,ROW()),"")

　次に、I7セルに次の関数を入力して下さい。

=IF(ISNUMBER(INDEX($E:$E,ROW())),INDEX($C:$C,ROW())*INDEX($H:$H,ROW()),"")

　次に、E12セルに次の関数を入力して下さい。

=SUM(E$6:INDEX(E:E,ROW()-1))

　次に、F7セルとG7セルの書式設定の表示形式を[通貨]とした上で、[記号]を「\」に、小数点以下の桁数を2に設定して下さい。
　　次に、F7セルとG7セルの書式設定の表示形式を[通貨]とした上で、[記号]を「\」に、小数点以下の桁数を好みの桁数に
設定して下さい。
　次に、I3セルの書式設定の表示形式を[パーセンテージ]として下さい。
　次に、E7～I7の範囲をコピーして、E8～I11の範囲に貼り付けて下さい。
　次に、E12セルをコピーして、G12セルとI12セルにそれぞれ貼り付けて下さい。

　以上で準備は完了で、後は、E3セルに値引き額を、I3セルに消費税率を、C7以下に数量を、D7以下に税抜きの単価をそれぞれ入力しますと、税込の単価や価格と、値引き後の単価や価格が自動的に表示されます。

回答No.1

japaneseman5963

エクセルの表をsnipping toolというソフトで切り取って添付画像とした方がいいです。
出来なければすくなくともコンマでくぎってあらわさないと。
この表示から判読するのはムリだと思う