・合計金額が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です。
説明下手だと思いますが、規則がありそうで、なさそうなものですが、関数で数字が出るようなものは作れますか?教えて下さい。
No.6ベストアンサー
- 回答日時:
#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)です。
----------------------------------------------------------------
以上ご参考まで。超々長乱文深謝。
感激しました。ここまで詳しく書いて下さるとは。。。本当に嬉しいです。本当にありがとうございました。がんばります。とても感謝しています。
No.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)
ソルバーは複数の変数の最適解を求めることができる機能です。
「試行錯誤が必要な問題」が発生した際に威力を発揮します。
No.5
- 回答日時:
#4です。
今朝あらためて見たら、どうもしくじってたようです。
入力値によっては合計金額がキッチリ合わないことがありますね。
A5: =MOD(A3,A2)-IF(A4=4,A2,INT(A2/4)*A4)
に訂正します。スミマセン。
この回答への補足
どういう計算式か自分で考えてみようと思ったのですが、
この式だけでも、さっぱりわかりません。
どうして、3を引くのか、6を足すのか、4で割るのか・・・
センスの問題なのでしょうか?INT関数はわかるし、式も割り算と引き算と足し算なのでセル値に数字をあてはめれば、電卓で出せますが、計算式の意味がわかりません。もし、よかったら、教えていただけませんか?
=A1-((INT(A1/A2)-3)*A2+6*INT(A2/4))
No.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程度に収まるはずです。
以上ご参考まで。
びっくりしました。まさしく魔法です。
ABS関数を知らないので、この式がどうなっているかの理解が全くできていません。ゆっくりとどういう計算式なのか、理解してみます。ありがとうございました。質問したいことが出てくるかもしれませんので、まだ締め切らないでいます。本当にありがとうございました。
No.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関数でも良いかもしれません。色々試してみてください
ソルバーアドインという機能を初めて知りました。
同じようにやってみましたが、「実行」をクリックすると、失敗になってしまいました。このソルバーアドインがどういう機能か全く知らないので、時間があるときに勉強します。ありがとうございました
No.2
- 回答日時:
関数一発は無理だし、ゴールシークも変数が一つじゃないとダメな
ので厳しいと思います。
で、私の解き方。
商品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
- 回答日時:
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで80万行、50列位のデ...
-
ゼロを表示
-
Excelの計算が合いません。 諸...
-
Excelの警告について
-
今まで文字化けなく開けていたc...
-
EXCELの散布図で日付が1900年に...
-
エクセルでファイルの最終更新...
-
エクセルの数式バーのフォント...
-
作成した数式を値として表示し...
-
ExcelでASCを使って全角を半角...
-
Excelセルを跨いで合計を出す方法
-
Excel 大小比較演算子による「...
-
Excel関数について教えてくださ...
-
マクロの処理が遅くなった
-
エクセルで入力すると隣のセル...
-
Excel関数について教えてくださ...
-
エクセルの文字が途中から消える
-
エクセルを使用して、円周率を...
-
エクセルのセル内に分数などの...
-
条件付き書式設定で罫線を引き...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報