エクセルで合計金額と商品の個数から明細をつくる方法

質問

デパートの場所を借りての10日間の宣伝販売の話とお考え下さい。商品Ａは売価1,000円、商品Ｂは売価3,000円、商品Ｃは売価5,000円だとします。売り子はアルバイトでよその売り場も見ているため売れた正確な個数は勘定していません。（消費税については総額表示方式に従います。）

　売上金はすべて現金か金券でデパートのレジに入りますが毎日閉店後に点検のレシートをもらいます。そのレシートには日々売上xxx,xxx円、個数合計xx個という形で印字されています。当然宣伝販売の開始時と終了時の在庫の確認は行うものとし、万引きや販促目的使用などの動きを含めて把握してあるとします。

　この場合、エクセルを使ってレシートの情報と前後の在庫数だけで毎日のそれぞれの商品が売れた個数がわかるようにするにはどうしたらよいでしょうか。回答が複数考えられる場合は、終了時の在庫の確認をきっちり行うためどれか一つの正解で良いものとします。

回答No.4

noname#75810 · ベストアンサー

No3さんのおっしゃることも数学的には一理ありますが、
今回は不明値が正の整数と決まっているので、そんなに難解ではありません。
売れる個数が1000個程度なら解は最大で1000000通り程度です。
3品目の単価によっては、価格の組み合わせとしてありうる解の範囲が
狭まりますし、安い商品と高い商品の価格差によっては、
更に狭まります。在庫確認でめぼしをつければ、更に狭まるでしょう。
とはいえ、実は、上の例だと、かなり多数の解が考えられます。
この場合、AB, BCの価格差がどちらも2,000円というのが致命的です。
ACを1個づつ買った値段とB2個の値段が同じになるからです。
実際どうなんでしょう。

お礼

＞実際どうなんでしょう。
実際はご回答のようなことが起こります。質問ではモデライズしてありますが、商品の種類は４つしかありませんし、売れても一日５０個が上限です。しかし同じ商品を複数売った金額と別々の商品を同じ数だけ組み合わせたときの金額が同じになることは確かにあります。VBAかVBかCを使って組み合わせを全部あたりちから技で解くしかないようです。条件はいくつか設定できるのではずれたとたんにループから抜ければ全部当たる必要はないでしょうが。

ご回答ありがとうございました。

回答No.3

imogasi

ｘ＋ｙ＋ｚ＝Ｋ　個数合計
Ａｘ＋Ｂｙ＋Ｃｚ＝Ｔ　売り上げ合計
の２つの式（Ａ，Ｂ．Ｃ、ｋ，Ｔは定数）から、ｘ、ｙ，ｚを求めるというのはできないと思います。
解が求まるのは未知数の数だけ、方程式が必要です。
種類が３種とは限らないと思いますが増えるとなおさらです。
ｘ、ｙ、ｚは正整数という条件を入れてもできないのでは。
エクセルのゴールシーク、ソルバーも十分使いこなしてないのですが、調べてみてください。無理ではないかな。

お礼

＞無理ではないかな。
エクセルの関数だけ使うとするならどうもそのようですね。
ご回答ありがとうございました。

回答No.2

noname#75810

No1の補足です。
「連立方程式をうまく解く数式」というのはツール\ゴールシーク
のつもりでした。でもこれって式変形すれば使う必要がない
関数ですので、調べてません。

お礼

自動的に計算っていうのがなかなか難しいようです。再度のご回答に感謝します。

回答No.1

noname#75810

売り上げ個数(全体; a; b; c)= C; Ca; Cb; Cc
売り上げ金額(全体; a; b; c)= P; Pa; Pb; Pc
とします。以下の連立方程式を解いてください。
1. C = Ca + Cb + Cc
2. P = CaPa + CbPa + CcPa
このうち、Ca, Cb, Ccがわからないので、このままでは解けません。
Cbを常識的な個数に固定して計算してみることになるでしょうか。
おそらく、在庫確認で大体のCbは分かると思います。
あわなければCbをずらせばいいわけです。
売り上げが1円単位で正確というのであれば、
かなり正確な個数が出せると思います。
この方法は数的感覚が鈍い人がやると時間がかかるのが欠点です。
連立方程式をうまく解く数式があったと思いますので、
誰か詳しい人補足お願いします。

お礼

確かに正解はいくつか考えられます。数学の問題ではなくつじつまが合えばいいのでどれか一つのうまくいく答えで良いことにしたいと考えています。

ご回答ありがとうございました。