金額を人数割りしたい

質問

友人から聞かれて困っています。

友人の会社ではコピー用紙などの備品の請求書を、部署ごとに人数割してそれぞれの金額を計算してるらしいのですがエクセルで計算するとどうしても合わないらしいのです。
＞説明
請求金額　￥５０００

営業　４人　４０％　￥２０００
管理　６人　６０％　￥３０００
―――――――――――――――
合計　10人　100％　　￥５０００

このような感じで請求金額を入れると金額が％で割られて合計金額と請求金額が一致するようにしたいのですが、金額や人数などが複雑になると１円の差額が出たりしてピッタリいかないんです。１００％になるはずの合計％も合わなかったりします。どの関数を使えばいいのでしょうか？
どうかよろしくおねがいします！

回答No.3

yahtzen · ベストアンサー

いろんな方法が考えられると思いますよ。
たとえば小学校レベルな(?)計算方法で切捨てをうまく使ってというのも考えられます。いろんな味付けがあると思いますが。。。

文章では伝わらないと思いますが端数はいちばん負担の大きいグループにということで。そして10円単位でということで。
・まずは負担率で分けます。その際に金額を1/10にしてしかもその負担率の人数の倍数になるように切り捨て、後に10倍します。
・いちばん負担率の大きいグループは別の計算式を使ってほかのグループの端数を合算して割り勘します。もちろんここでまた端数が出ますがやはり切り捨てで"あまり"を作って適当に分配してはいかがでしょうか。

もちろん金額が小さければ誤差が大きくて使い物になりません。そのほかにもすごくがんばればグループごとにまったく同じ金額というのもできるかもしれませんがそこまでするほどでもないのでは？

お礼

１円や１％の誤差なら適当に分配して金額が合えばイイ、
と私も思うのですが…友人にそうしてはどうかとに勧めてみます。「端数が出たら負担の一番多い部署に」というようように決めて割り振ればいいんですよね。
ご丁寧にありがとうございました！

回答No.6

mshr1962

按分自体は
総金額*部の人数/全体の人数
でできますが、他の方の言われるように差額ができるので処置が必要です。
部を決めてそこで清算なら
=FLOOR(総金額*部の人数/全体の人数,按分の単位)
※按分の単位が10の乗数ならFLOORでなくROUNDDOWNで桁数指定でもOK
で清算する部以外の金額を計算
清算する部では
=総合計-他の部の合計

1番多い（または少ない）部で行う場合は、各部を計算後に差額を出して
RANK関数で順位が1の部に別列で加算する。

お礼

ご回答ありがとうございます。皆さんの意見を参考にさせて頂き、無事解決しました！
ご意見ありがとうございました。

回答No.5

imogasi

私も＃１の方と同じ結論で、エクセルを偶々使っているだけで、電卓を使っても同じ経験は、再々します。悩ましい点です。
算数的なもので、こう言うケースが起こることは、避けられないので、あとは経験・慣習・担当者の裁量で
担当が適当にル‐ルを決めて処理しているのが現状と思います。通常は部長課長に聞くべきほどのことでもないし。
不足・過剰額の場合の修正帰属
（１）一番ウエイトの大きいところ
　　　一番ウエイトの小さいところ
（２）総務
（３）自分の部
（４）次の下の位を調べて、最高・最低のところで修正
（５）明細の最下行
エクセルの式でビルトインしようとすると案外、式が
上手く行かないですね。
=IF(SUM(A1:A6)<25,A3+1,)は循環参照になりますし。
自行以外の和も最下行以外は出しにくい。
別列が必要になる。
強いてやるなら、VBAでもロジックをプログラムに組んで、ボタンやメニューにでも登録が必要かなと思う。

お礼

参考にさせて頂きました！１番負担の少ない所に割り振って修正することにしました。１％や１円ぐらいですからね。ありがとうございました！

回答No.4

yahtzen

そうですね。たとえばNo.3の方法で金額が出ましたら実際には～%が最初の設定とどのくらいずれているかをチェックする項目も作っておきましょう。そしてあまりにもずれているもしくはやはり誤差が気になるということでしたらその～%の項目をいじってみて妥当な配分できれいに割り切れるのも見つかりそうですよね。
なんだかおもしろそうになって来ました。実際の人数とかによってはエクセルだけでもぴたっという数値をはじき出せるような気もしてきましたよ。がんばってみてくださいね。

お礼

やってみました！一応完成する事ができました。
教えて頂いた通り、％合計と金額合計の欄の下にそれぞれ「誤差」の欄を作りました。そのまた下にＭＩＮで最小値を表示し、１番負担の少ない所に自分で割り振って誤差を０にする、という方法にしました。キレイに割り振りたかったんですが…（性格上）そんなに手間のかかることでもないですし。yahtzenさんの意見は大変参考になりました！
友人から別の作業もお願いするかも…と言われているのでまた質問見る機会がありましたらよろしくお願いします！
ありがとうございました！

回答No.2

yahtzen

割り勘ですと"割り勘くん"などのフリーツールがありますがそういったものではなくどうしてもエクセルで計算したいということでしょうか。

補足

無理ではないのでしたらエクセルでなんとかしたいのですが・・・方法がありましたら教えていただけますか？割り勘のソフトも見てみます。

回答No.1

deecyan

どんな関数を使っても無理だと思うのですが
端数は四捨五入してトータルとの差額を
請求元（総務とか）がプールして帳尻を合わせると思います

お礼

やはりどこでもそうするようですね。ありがとうございます！