こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

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

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


A B C D E F G H I

1 値引割当表(内税)
2
3                     値引額:2,500 消費税率: 5%
4
5   No.品     名数量    価 格  税込価格 値引価格
6            単 価  金 額単 価  金 額 単 価 金 額
7   1        500   7638,000 
8
9  2        100  20020,000
10  3
11  4
12  5
13  計          ¥58,000   

投稿日時 - 2014-03-13 16:34:51

QNo.8512258

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

 御質問の件を解決するためには、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以下に税抜きの単価をそれぞれ入力しますと、税込の単価や価格と、値引き後の単価や価格が自動的に表示されます。

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2014-03-14 04:01:30

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

投稿日時 - 2014-03-13 18:55:49

あなたにオススメの質問