エクセル関数

数値を構成比の高い順で振り分けについて。

合計発注数を売上構成比の高いお店順に振り分けたいのです。

Aは店舗名、Bは店の売上構成比、Dが合計発注数でCに各店舗の発注数をいれたいのです。（下記、図参考）

例え
ば、合計発注数を300と入力すると自動的に構成比の高いお店順に数を振り分けられるようにしたいのです。
振り分ける数量も最小数量が2個とも設定したいです。

これで単純に合計発注数を構成比で掛けると発注数が小数点になるうえ
ROUNDなどで微調整しても合計発注数と数が合わなくなってしまいます。（切捨てや切り上げの関係です）
店舗が200以上もあるので
微調整にも手間取ってしまいます。
そこでどうにか皆さんの知恵を拝借したく思います。
どうか宜しくお願い致します。
下限値 2
A B C D
1 ●●店 7.8% 300

2 ■■店 3.5%

3 △△店 4.9%

4 ●×店 0.0%

質問者からの補足コメント

• ありがとうございます。
  私の日本語が下手でうまく説明が出来ていない事は申し訳ございませんでした。
  
  実は
  C2 =IF(A2=INDEX($A$2:$A$5,MATCH(MAX($B$2:$B$5),$B$2:$B$5,0)),
  $D$2-SUMIF($A$2:$A$5,"<>"&INDEX($A$2:$A$5,MATCH(MAX($B$2:$B$5),$B$2:$B$5,0)),$C$2:$C$5),IF(MROUND($D$2*B2)<2,2,MROUND($D$2*B2,0))
  このような関数を作成してみたのですが、
  構成比が高いお店なのに、マイナス値が出てしまい、訳がわからなくなりました。
  そこで皆さんにご教示頂けないかと思い相談させていただきました。
  
  どうもありがとうございました。

  No.2の回答に寄せられた補足コメントです。 補足日時：2018/10/17 23:04

No.3 ベストアンサー 回答者： ママチャリ 回答日時： 2018/10/20 07:59

まりもさんって、細かいですね。

アバウトなわたしは次のように考えます。

全店に2個づつ割り当てる。残りを構成比によって按分する。
C2セルの式にすると、こんな感じです。
=ROUND(2+($C$1-COUNT(B:B)*2)*B2,0)

丸めによる誤差は、例えば、3つ余った場合、構成比の上位3店舗に＋１するだけなので、「手間取ってしまう」ほどでもないと思いますが・・・。面倒ならExcelにやらせても良いし・・・。

この回答へのお礼

ご丁寧ありがとうございました。
こんなに簡単に出来るなんて嬉しいです。
お助けいただき感謝しております。

お礼日時：2018/10/20 08:19

No.2 回答者： pontajya 回答日時： 2018/10/17 22:25

行1を参照数値用に使います。

A1→合計発注数
B1→構成比最大値の店舗数
C1→下限値2の合計
D1→最大値以外の発注数の合計

C列は計算用に使います。
計算結果が2以下なら2を、2を越えれば”a”になるように計算式を入れ、C1で最小値2の合計を計算します。
B1にはCOUNTIF関数を使って、C列の”a”の数を入れます。

列Dで、列Cが2でも”a”でもなかったら普通に計算、列Cが2だったら2、”a”だったら0とします。

列Eで、（合計発注数ーD1）/B1として、構成比最大値で合計が合うように計算。

こんな感じでどうでしょうか。

VBAで関数を作ればスッキリできそうですが。

No.1 回答者： mabuterol 回答日時： 2018/10/17 21:40

日本語が良く分からない。

そもそも商品は、各店舗から○個仕入れたいというリクエストが来て、それを振り分けるのか？
それとも本部の方で勝手に店舗に商品を振り分けるのか？

仮に売上構成比の最も高い店舗から３００個仕入れたいと言われたら、その店舗に３００個わたして終わりなのか？