・合計金額が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.1
- 回答日時:
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.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.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.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.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)
ソルバーは複数の変数の最適解を求めることができる機能です。
「試行錯誤が必要な問題」が発生した際に威力を発揮します。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(お金・保険・資産運用) 至急!【Wolt】各メニューの価格設定の簡単な計算方法 3 2023/03/05 11:58
- 消費税 消費税の納税額の計算 1 2023/02/19 18:12
- 簿記検定・漢字検定・秘書検定 棚卸減耗損について 2 2022/05/19 04:48
- その他(資産運用・投資) idecoではなりませんが、企業確定拠出年金に加入しています。 毎月の購入する運用商品を4種類、スイ 2 2023/03/18 09:58
- 簿記検定・漢字検定・秘書検定 簿記2級 売上原価の計算と期末商品の評価に関する質問 3 2023/06/24 23:50
- Excel(エクセル) スプレッドシートでの質問です 1 2023/07/06 20:43
- その他(データベース) pythonでsqlight勉強中、クエリー結果の利用法教えて下さい 1 2022/04/28 20:38
- Excel(エクセル) Excel 関数 vlookupなどの使い方について質問です。 シート1に品番、商品名、単価、発注条 6 2022/06/15 19:16
- メルカリ メルカリShopsを作るとメルカリで販売は楽になりますか メルカリとまったく変わりませんか? 2 2022/10/11 19:22
- 会社・職場 レジの打ち間違いについて お弁当屋でレジの仕事してるんですが… 1つの商品を打ち間違いして レジ締め 2 2023/07/14 14:57
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報