エクセルの関数で算出したいものがあります。

質問

・合計金額が493,520円
・商品の種類は４種類
・商品の個数は980個
・４種類ある商品の単価の差は1～5円程度
・980個のうち、何がいくつあるかの内訳は、４等分になるべく近い数字にする。

という条件です。

計算式がわからなかったので、電卓片手に、何度も計算したら、偶然いい数字が見つかりました。

合計金額493,520円
個数は980個
商品A の単価は502円、個数は202個 =101,404
商品B の単価は503円、個数は243個 =122,229
商品C の単価は505円、個数は247個 =124,735
商品D の単価は504円、個数は288個 =145,152

今回は、たまたま闇雲に電卓を打ったら、ぴったりな数字が出てきましたが、今後、随時数字を変えて出てくるそうです。
数字が変わる部分は、合計数字・個数です。商品が４種類あることと、単価の差が１～５円程度ということと、個数の内訳の差がなるべく４等分に近い数字だというのが条件です。

答え合わせは、計算のつじつまが合えばいいので、何種類かパターンが選べる場合もあるでしょうが、１つのパターンさえ出れば問題ありません。また確実な正解データがないので、手計算のつじつまが合えばＯＫです。

説明下手だと思いますが、規則がありそうで、なさそうなものですが、関数で数字が出るようなものは作れますか？教えて下さい。

回答No.6

noname#70958 · ベストアンサー

#4＝#5です。
とりあえず動作にはご満足いただけたようで安心しました。

数式を自分で書けるかどうかは（センスというよりも）経験と慣れだろうと思いますが、
他人の書いた数式を読めるかどうか、というのはまた別の話で、
「確かに動くんだけど、何やってるのかよく判らん」ということは私もよくありますし、
#4の数式を半年後に見たら、私自身も何やってるのか悩んじゃう気がします(^^;;

考え方や作業自体はさほど複雑なものではないのですが、
数式を減らすために無理矢理ネストしたり、
字数を削るためにちょこちょこ「ズル」をしていることもあって、
余計わかりにくくなっているかもしれません。

以下、処理の流れをざっと解説してみました。
…「ざっと」と言う割に非常に長いですが。
----------------------------------------------------------------
まず、#4-5の数式をバラして書くと、こんな感じになります。
（作業セルはB列に移しました)

　A1：合計金額　
　A2：合計個数　

　B1：　=INT(A1/A2)
　B2：　=INT(A2/4)
　B3：　=A1-((B1-3)*A2+6*B2)
　B4：　=INT(B3/A2)
　B5：　=B3-A2*B4
　B6：　=ROUND(B5/B2,)
　B7：　=B5-IF(B6=4,A2,B2*B6)
　B8：　=6-ABS(B6-2)
　B9：　=INT(B7/B8)
　B10：　=MOD(B7,B8)

　C2：単価Ａ　=B1-0+B4+(B6>0)
　C3：単価Ｂ　=B1-1+B4+(B6>1)
　C4：単価Ｃ　=B1-2+B4+(B6>2)
　C5：単価Ｄ　=B1-3+B4+(B6>3)

　D2：個数Ａ　=B2+B9+IF(B6=3,B10)
　D3：個数Ｂ　=B2+B9-IF(B6=3,B10)
　D4：個数Ｃ　=B2-B9+IF(B6<>3,B10)
　D5：個数Ｄ　=A2-SUM(D2:D4)
----------------------------------------------------------------
上記の数式に沿って、【合計金額：493910　合計個数：983】
の場合について解説します。（質問文の値だと、例として少し都合が悪いので…）

「まずおおざっぱな数字を立てて、残額(差額)を調整していく」
というのが基本的な流れです。
-------------------------------
S1．単価１
　【必ず余りが出るように】仮の単価を少なめに見積もります。
　単価Ａを　B1＝INT(A1/A2)＝502　として、
　単価Ｂ以下を順に１減らせば、
　最大の額 × 合計個数 ＜ 合計金額　ですから、
　全体として必ず余りが出ます。

　単価１≡(B1-0,B1-1,B1-2,B1-3)≡(502,501,500,499)
-------------------------------
S2．個数１
　合計個数を４等分（B2＝INT(A2/4)＝245）します。
　余り(3)は個数Ｄに振ります。

　個数１≡(B2,B2,B2,B2+3)≡(245,245,245,248)
-------------------------------
S3．残額１
　ご要望のあった部分です。
　単価１と個数１にもとづいてこの時点の残額を求めます。
　そのためにまず、現在の合計を出すのですが、
　【単価１や個数１は実際にはシート上に書き出していませんから】
　一発で出すために、共通部分を括り出して計算します。

　502*245+501*245+500*245+499*248
　= (499+3)*245 + (499+2)*245 + (499+1)*245 + (499+0)*248
　= 499*245 + 499*245 + 499*245 + 499*248 + 3*245 + 2*245 + 1*245
　= 499*(245+245+245+248)+(3+2+1)*245
　= (502-3)*983+6*245

　・最小の額(単価Ｄ＝B1-3＝499)に合計個数を掛けたもの
　・差分の和(3+2+1)にB2を掛けたもの(6*B2)
　の和が現在の合計となます。

　残額１＝合計金額－現在の合計＝B3=A1-((B1-3)*A2+6*B2)＝1923
-------------------------------
S4．単価２　（全品一括値上げ）
　残額１(1923)は合計個数(983)よりも多いので、
　【全ての商品を一括して】値上げします。
　全ての商品を1円値上げすれば全体で合計個数＝983円値上げというわけです。

　幾ら値上げできるかは、
　　INT(残額１/合計個数)＝B4＝INT(B3/A2)
　で求まります。

　単価２≡(B1-0+B4,B1-1+B4,B1-2+B4,B1-3+B4)≡(503,502,501,500)
-------------------------------
S5．残額２
　単価２に基づいて残額を更新しますが、
　直接計算せずに、残額１から、S4で値上げした分だけ差っ引きます。

　残額２＝残額１－値上げ額×合計個数＝B5=B3-A2*B4＝940
-------------------------------
S6．単価３　（商品個別値上げ）
　今度は商品別に値上げします。
　一つの商品を１円値上げすると、
　その商品全体で【おおよそ】B2(245)の値上げになります。

　何種類の商品を値上げできるかは、
　　ROUND(残額２/B2,)＝B6＝ROUND(B5/B2,)＝4
　となります。

　ここでは【残額ではなく差額】をできるだけ小さくするので、
　INTではなくROUNDで丸めます。

　B6の値は通常０～４の５種類です。
　（合計個数が極端に少ない場合は考慮していません）
　・負になることはありません。
　・B6が５以上⇒残額３がB2の4.5倍以上の場合は、
　　S4の段階で値上げ額が増えているはずです。
　・INTとROUNDの違いがあるので、４はありえます。

　B6の値が
　・０⇒値上げなし
　・１⇒１商品（商品Ａ）を値上げ
　・２⇒２商品（商品Ａ，Ｂ）を値上げ
　・３⇒３商品（商品Ａ，Ｂ，Ｃ）を値上げ
　・４⇒４商品（商品Ａ，Ｂ，Ｃ，Ｄ）を値上げ

　単価３
　≡(B1-0+B4+(B6>0),B1-0+B4+(B6>1),B1-0+B4+(B6>2),B1-0+B4+(B6>3))
　≡(504,503,502,501)

　なお、B1-0+B4+(B6>0)　は、B1-0+B4+IF(B6>0,1,0)　と同じ意味です。

　単価はこれで確定しました。
-------------------------------
S7．差額１
　単価３に基づいて差額を求めます。
　B6でROUNDを使っているので、この値は【負になりえます】

　差額１＝残額２－S6での値上げ額＝B7＝B5-IF(B6=4,A2,B2*B6)＝-43

　私が間違えたところですね(汗
　S6での値上げ額は、B6が
　・０のときはB2×０
　・１のときはB2×１
　・２のときはB2×２
　・３のときはB2×３
　なのですが、
　・４のときはB2×４【とは限りません】

　実際、この例で、個数Ｄは端数の３を振られて248個ありますから、
　B6が４のときの値上げ額は、245+245+245+248＝合計個数となります。
　S6で【おおよそ】としているのはそのためです。
-------------------------------
S8．増分
　単価が確定したので、今度は個数を調整して差額１を埋めます。

　まず、額を大きく動かすために、
　★「商品Ａ，Ｂを増やし（減らし）て、商品Ｃ，Ｄを減らす（増やす）」　
　という２対２の交換を行うのですが、そのために、
　【その作業を行ったとき、いったい幾ら変動するのか】
　を求めます。

　単価２の段階では、差は１ずつですから、
　Ａ，Ｂを１増やしてＣ，Ｄを１減らせばプラス４ですが、
　S6で商品個別値上げの結果を踏まえると、例えば、

　B6が１のときは、Ａが１円上がっているので、、
　　（４＋２）－（１＋０）＝５
　B6が２のときは、Ａ，Ｂが１円ずつ上がっているので
　　（４＋３）－（１＋０）＝６

　というふうにB6に応じて変動します。
　表にするとこんな感じです。

　【表】
　　Ｂ６　　０　１　２　３　４
　--------------------------
　商品Ａ　３　４　４　４　４
　商品Ｂ　２　２　３　３　３
　商品Ｃ　１　１　１　２　２
　商品Ｄ　０　０　０　０　１
　--------------------------
　　Ｂ８　　４　５　６　５　４

　これをどう数式に書くかですが、
　左右対称になっている点に着目して、

　B6　　　　　　　　：　０　　１　　２　　３　　４
　B6-2　　　　　　：－２　－１　　０　＋１　＋２
　ABS(B6-2)　 　：　２　　１　　０　　１　　２
　6-ABS(B6-2)　：　４　　５　　６　　５　　４

　増分＝B8＝6-ABS(B6-2)＝4
　としました。
　ABSは絶対値を求める関数です。

　本来なら（実務であれば）普通にINDEXやCHOOSEを使って、
　増分＝B8＝CHOOSE(B6+1,4,5,6,5,4)
　増分＝B8＝INDEX(B6+1,{4,5,6,5,4})
　などとすべき場面です。
-------------------------------
S9．個数２
　★の作業で動く額が判ったので、
　増やすのか減らのすか、何回やるかを決めます。

　INT(差額１／増分)＝B9＝INT(B7/B8)＝-11

　ここで、差額１は負数かもしれないので、
　【MODとの相性を考えて】ROUNDではなくINTを使っています。
　負数に対して、ROUNDは0への丸め、INTは負の無限大への丸めです。
　ex. ROUND(-3/7,0)=0　／　INT(-3/7)=-1　／　MOD(-3,7)=4

　個数２≡(B2+B9,B2+B9,B2-B9,B2+3-B9)
　≡(245+(-11),245+(-11),245-(-11),248-(-11))
　≡(234,234,256,259)
-------------------------------
S10．個数３
　S9を踏まえて差額２を求めます。

　差額２＝MOD(差額１，増分)＝B10＝MOD(B7,B8)＝1

　S9の段階で、差額は３以下になっているハズなので、
　残りは１ずつ、差額２＝B10　回調整します。

　１ずらすために何と何を交換すれば良いのかは…
　【表】を見ると、
　B6が３のとき以外は、商品Ｃと商品Ｄの単価は１違いなので、
　・B6が３のとき　⇒　商品Ａと商品Ｂを入れ替え
　・B6が３でないとき　⇒　商品Ｃと商品Ｄを入れ替え

　個数３
　　≡(B2+B9+IF(B6=3,B10),B2+B9-IF(B6=3,B10),B2-B9+IF(B6<>3,B10),A2-SUM(D2:D4))
　　≡(234,234,257,258)　

　個数Ｄ=D5は、他の３つに揃えて書くならば、
　　B2-B9-IF(B6<>3,B10)+MOD(A2,4)
　となります。
　MOD(A2,4)は、S2で振り分けた余り(3)です。
----------------------------------------------------------------
以上ご参考まで。超々長乱文深謝。

お礼

感激しました。ここまで詳しく書いて下さるとは。。。本当に嬉しいです。本当にありがとうございました。がんばります。とても感謝しています。

回答No.7

zap35

＃０３です
＞「実行」をクリックすると、失敗になってしまいました
もしかすると先の回答で誤った式を書いたためかもしれませんので
訂正させてください。
以下が正解です。（C列の式がウソでした）

　　　　　Ａ列　　　Ｂ列　　　Ｃ列　　　　　　　Ｄ列
１行目　502　　　空白　　　=INT(B1)　　　　=A1*C1
２行目　503　　　空白　　　=INT(B2)　　　　=A2*C2
３行目　504　　　空白　　　=INT(B3)　　　　=A3*C3
４行目　505　　　空白　　　=INT(B4)　　　　=A4*C4
５行目　空白　　 空白　　　=SUM(C1:C4)　　=SUM(D1:D4)
６行目　=SEDEV(C1:C4)

ソルバーは複数の変数の最適解を求めることができる機能です。
「試行錯誤が必要な問題」が発生した際に威力を発揮します。

お礼

ありがとうございます。もう一度やってみます。
ソルバー、自分でも使えることが他にいっぱいありそうなので、もっと理解したいです。

回答No.5

noname#70958

#4です。
今朝あらためて見たら、どうもしくじってたようです。
入力値によっては合計金額がキッチリ合わないことがありますね。

　A5：　=MOD(A3,A2)-IF(A4=4,A2,INT(A2/4)*A4)

に訂正します。スミマセン。

補足

どういう計算式か自分で考えてみようと思ったのですが、
この式だけでも、さっぱりわかりません。
どうして、３を引くのか、６を足すのか、４で割るのか・・・
センスの問題なのでしょうか？INT関数はわかるし、式も割り算と引き算と足し算なのでセル値に数字をあてはめれば、電卓で出せますが、計算式の意味がわかりません。もし、よかったら、教えていただけませんか？
=A1-((INT(A1/A2)-3)*A2+6*INT(A2/4))

回答No.4

noname#70958

作業セルをいくつか使えば数式でもなんとか…。

●入力欄
　A1：合計金額
　A2：総個数

●作業セル
　A3：　=A1-((INT(A1/A2)-3)*A2+6*INT(A2/4))
　A4：　=ROUND(MOD(A3,A2)/INT(A2/4),)
　A5：　=A3-A2*INT(A3/A2)-INT(A2/4)*A4

●単価
　C2：商品A　=INT($A$1/$A$2)+INT($A$3/$A$2)-ROW()+2+($A$4>ROW()-2)

　としてC5まで下方にフィル

●個数
　D2：商品A　=INT(A2/4)+INT(A5/(6-ABS(A4-2)))+IF(A4=3,MOD(A5,6-ABS(A4-2)))
　D3：商品B　=INT(A2/4)+INT(A5/(6-ABS(A4-2)))-IF(A4=3,MOD(A5,6-ABS(A4-2)))
　D4：商品C　=INT(A2/4)-INT(A5/(6-ABS(A4-2)))+IF(A4<>3,MOD(A5,6-ABS(A4-2)))
　D5：商品D　=A2-SUM(D2:D4)

　※似たような式ですがフィルできないので注意
----------------------------------------------------------
ex.合計金額：493,520　合計個数：980　の場合
　505　267
　504　267
　503　225
　502　221

ex.合計金額：678,901　合計個数：234 の場合
　2903　63
　2902　57
　2901　56
　2899　58

金額の差は４以内，個数の差は、総個数のおおむね1/16程度に収まるはずです。

以上ご参考まで。

お礼

びっくりしました。まさしく魔法です。
ＡＢＳ関数を知らないので、この式がどうなっているかの理解が全くできていません。ゆっくりとどういう計算式なのか、理解してみます。ありがとうございました。質問したいことが出てくるかもしれませんので、まだ締め切らないでいます。本当にありがとうございました。

回答No.3

zap35

ソルバー機能を用いるのが良いでしょう。
まず「ツール」→「アドイン」で「ソルバーアドイン」にチェックをつけます。その上で以下の通りの表を作ってください

　　　　　Ａ列　　　Ｂ列　　　Ｃ列　　　　　　　Ｄ列
１行目　502　　　空白　　　=INT(B1)　　　　=A1*C1
２行目　503　　　空白　　　=INT(B1)　　　　=A2*C2
３行目　504　　　空白　　　=INT(B1)　　　　=A3*C3
４行目　505　　　空白　　　=INT(B1)　　　　=A4*C4
５行目　空白　　 空白　　　=SUM(C1:C4)　　=SUM(D1:D4)
６行目　=SEDEV(C1:C4)

B列は各商品の個数が入る列でソルバーで求める値です。Ｃ列のINT関数はＢ列の結果が少数になったときに整数にするためのものです。また６行目はＣ列の個数の標準偏差です。標準偏差が小さくなればなるほど個数のバラツキは小さくなるので「ほぼ近い個数」になります

「ツール」→「ソルバー」でウィザードを開き
・目的セル　→　A6
・目標値　　→　最小値
・変化させるセル　→　B1:B4
・制約条件には以下の２条件を指定します
　　　C5=980
　　　D5=493520

これで「実行」で求めた結果が以下になります
Ａ列　　Ｂ列　　　　　　　Ｃ列　　Ｄ列
502　　218　　　　　　　218　　109436
503　　236.9832883　　236　　118708
505　　272　　　　　　　272　　137360
504　　254　　　　　　　254　　128016
23.23790008 　　　　　　980　　493520

値の組み合わせによってはきれいにでない場合もあると思いますが、今回の組み合わせではご自身が出された結果と比べていかがでしょうか
また今回は少数を整数にするのにINT関数を使用しましたが、四捨五入のROUND関数でも良いかもしれません。色々試してみてください

お礼

ソルバーアドインという機能を初めて知りました。
同じようにやってみましたが、「実行」をクリックすると、失敗になってしまいました。このソルバーアドインがどういう機能か全く知らないので、時間があるときに勉強します。ありがとうございました

回答No.2

grumpy_the_dwarf

関数一発は無理だし、ゴールシークも変数が一つじゃないとダメな
ので厳しいと思います。

で、私の解き方。
商品AからDはそれぞれ単価1円ずつ違うことに決めます。商品Bの単
価をint(合計金額/個数)、Aはそれより1円安く、CとDは1円及び2円
高いことになります。ここは関数で決められますね。
個数の初期値はint(個数/4)で、足りない分は商品Aにでもつけとき
ます。ここでA～Dの総額と目標になる合計金額の差を見ます。
合計に届いてなければ、差額5円ごとにAを2こ減らしCとDを1こずつ
増やします。多すぎたらその逆です。最後に残った差額を調整すれ
ばとりあえずの解答が出ます。たとえば合計576980円で975個なら、
590円から593円で173、243、280、279個ですね。ここまで3分かかり
ません。

個数に差がつきすぎて不満なら、Dの単価を1円上げて、「差額5円ご
とに」を「6円ごとに」で計算し直します。すると590、591、592，
594円で268、240、234，233個です。

どこかのセルで差額をモニタしとけば、そんなに大変な作業じゃな
いですよ。

お礼

とてもわかりやすかったです。この方法なら、自分の頭の中でも整理できます。他の方で、ぴったりが出る関数を教えてくださった方がいたので、今回はその方法で解決いたしましたが、grumpy_the_dwarfさんの回答もとても参考になりました。ありがとうございます。

回答No.1

n-jun

http://ja.wikipedia.org/wiki/%E3%83%8A%E3%83%83%E3%83%97%E3%82%B5%E3%83%83%E3%82%AF%E5%95%8F%E9%A1%8C
こんな感じの事ですか？
ＶＢＡでも難しそうな。。。

他の回答をお待ち下さい。

お礼

まさにナップザップです！形があるようで、形がないような。。。他の方に回答を頂いて解決できました。ありがとうございます。