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

解決済みの質問

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

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

という条件です。

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

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

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

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

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

投稿日時 - 2008-06-30 17:07:22

QNo.4140930

暇なときに回答ください

質問者が選んだベストアンサー

#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:単価A =B1-0+B4+(B6>0)
 C3:単価B =B1-1+B4+(B6>1)
 C4:単価C =B1-2+B4+(B6>2)
 C5:単価D =B1-3+B4+(B6>3)

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

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

 単価1≡(B1-0,B1-1,B1-2,B1-3)≡(502,501,500,499)
-------------------------------
S2.個数1
 合計個数を4等分(B2=INT(A2/4)=245)します。
 余り(3)は個数Dに振ります。

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

 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

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

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

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

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

 残額2=残額1-値上げ額×合計個数=B5=B3-A2*B4=940
-------------------------------
S6.単価3 (商品個別値上げ)
 今度は商品別に値上げします。
 一つの商品を1円値上げすると、
 その商品全体で【おおよそ】B2(245)の値上げになります。
 
 何種類の商品を値上げできるかは、
  ROUND(残額2/B2,)=B6=ROUND(B5/B2,)=4
 となります。

 ここでは【残額ではなく差額】をできるだけ小さくするので、
 INTではなくROUNDで丸めます。
 
 B6の値は通常0~4の5種類です。
 (合計個数が極端に少ない場合は考慮していません)
 ・負になることはありません。
 ・B6が5以上⇒残額3がB2の4.5倍以上の場合は、
  S4の段階で値上げ額が増えているはずです。
 ・INTとROUNDの違いがあるので、4はありえます。

 B6の値が
 ・0⇒値上げなし
 ・1⇒1商品(商品A)を値上げ
 ・2⇒2商品(商品A,B)を値上げ
 ・3⇒3商品(商品A,B,C)を値上げ
 ・4⇒4商品(商品A,B,C,D)を値上げ

 単価3
 ≡(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.差額1
 単価3に基づいて差額を求めます。
 B6でROUNDを使っているので、この値は【負になりえます】

 差額1=残額2-S6での値上げ額=B7=B5-IF(B6=4,A2,B2*B6)=-43
 
 私が間違えたところですね(汗
 S6での値上げ額は、B6が
 ・0のときはB2×0
 ・1のときはB2×1
 ・2のときはB2×2
 ・3のときはB2×3
 なのですが、
 ・4のときはB2×4【とは限りません】

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

 まず、額を大きく動かすために、
 ★「商品A,Bを増やし(減らし)て、商品C,Dを減らす(増やす)」 
 という2対2の交換を行うのですが、そのために、
 【その作業を行ったとき、いったい幾ら変動するのか】
 を求めます。

 単価2の段階では、差は1ずつですから、
 A,Bを1増やしてC,Dを1減らせばプラス4ですが、
 S6で商品個別値上げの結果を踏まえると、例えば、

 B6が1のときは、Aが1円上がっているので、、
  (4+2)-(1+0)=5
 B6が2のときは、A,Bが1円ずつ上がっているので
  (4+3)-(1+0)=6

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

 【表】
  B6  0 1 2 3 4
 --------------------------
 商品A 3 4 4 4 4
 商品B 2 2 3 3 3
 商品C 1 1 1 2 2
 商品D 0 0 0 0 1
 --------------------------
  B8  4 5 6 5 4

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

 B6        : 0  1  2  3  4
 B6-2      :-2 -1  0 +1 +2
 ABS(B6-2)   : 2  1  0  1  2
 6-ABS(B6-2) : 4  5  6  5  4
 
 増分=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.個数2
 ★の作業で動く額が判ったので、
 増やすのか減らのすか、何回やるかを決めます。

 INT(差額1/増分)=B9=INT(B7/B8)=-11

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

 個数2≡(B2+B9,B2+B9,B2-B9,B2+3-B9)
 ≡(245+(-11),245+(-11),245-(-11),248-(-11))
 ≡(234,234,256,259)
-------------------------------
S10.個数3
 S9を踏まえて差額2を求めます。
 
 差額2=MOD(差額1,増分)=B10=MOD(B7,B8)=1

 S9の段階で、差額は3以下になっているハズなので、
 残りは1ずつ、差額2=B10 回調整します。

 1ずらすために何と何を交換すれば良いのかは…
 【表】を見ると、
 B6が3のとき以外は、商品Cと商品Dの単価は1違いなので、
 ・B6が3のとき ⇒ 商品Aと商品Bを入れ替え
 ・B6が3でないとき ⇒ 商品Cと商品Dを入れ替え

 個数3
  ≡(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) 

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

投稿日時 - 2008-07-01 23:41:55

お礼

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

投稿日時 - 2008-07-03 17:44:59

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

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

回答(7)

ANo.7

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

     A列   B列   C列       D列
1行目 502   空白   =INT(B1)    =A1*C1
2行目 503   空白   =INT(B2)    =A2*C2
3行目 504   空白   =INT(B3)    =A3*C3
4行目 505   空白   =INT(B4)    =A4*C4
5行目 空白   空白   =SUM(C1:C4)  =SUM(D1:D4)
6行目 =SEDEV(C1:C4)

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

投稿日時 - 2008-07-02 20:07:25

お礼

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

投稿日時 - 2008-07-03 17:43:35

ANo.5

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

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

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

投稿日時 - 2008-07-01 06:44:27

補足

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

投稿日時 - 2008-07-01 13:12:26

ANo.4

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

●入力欄
 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

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

以上ご参考まで。

投稿日時 - 2008-07-01 01:08:36

お礼

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

投稿日時 - 2008-07-01 11:49:08

ANo.3

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

     A列   B列   C列       D列
1行目 502   空白   =INT(B1)    =A1*C1
2行目 503   空白   =INT(B1)    =A2*C2
3行目 504   空白   =INT(B1)    =A3*C3
4行目 505   空白   =INT(B1)    =A4*C4
5行目 空白   空白   =SUM(C1:C4)  =SUM(D1:D4)
6行目 =SEDEV(C1:C4)

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

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

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

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

投稿日時 - 2008-06-30 19:55:47

お礼

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

投稿日時 - 2008-07-01 11:51:08

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

で、私の解き方。
商品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個です。

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

投稿日時 - 2008-06-30 19:22:54

お礼

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

投稿日時 - 2008-07-03 17:47:42

ANo.1

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
こんな感じの事ですか?
VBAでも難しそうな。。。

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

投稿日時 - 2008-06-30 19:01:00

お礼

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

投稿日時 - 2008-07-03 17:46:05

あなたにオススメの質問