エクセルで見積額に合うように人工を割り振り

見積を作成してます。
変な話かもしれませんが、決められた見積額になるように人工を割り振らなければならなくなりました。エクセルでそのような方法をご教示していただけないでしょうか。

仮に、見積額算出の考え方は下記のように考えます。
　　　　　単価　　　人工
技師a　50000×　0.5　=25000
技師b　45300×　2.5　=113250
技師c　35500×　6.0　=213000
技師d　28500×　5.5　=156750
合計508000円に
経費１　508000×0.05=25400
経費２　（508000+25400）×0.4=213360
を加え、
合計508000+25400+213360=746760円を
端数処理した、746000円が見積額となります。

しかし、今回は見積額が900000円になる（あるいは、もっとも近い金額）ように、上記人工を調整（0.5人工単位）したい状況です。
VBAでも構いません。

すみませんが、よろしくお願いいたします。

No.2 ベストアンサー 回答者： keithin 回答日時： 2014/09/13 12:23

＞決められた見積額になるように人工を割り振らなければならなくなりました

さて、現実には先に「業務に対する知識」として、たとえば技師ａに対してｂ、ｃ、ｄの人工をそれぞれ３対５対６で設定するみたいな具合に、おおまかな編成がまずあって、それを元にａの人工を幾つ投入すれば全体が目標額に近づくかみたいな具合に作成していきます。
こういう場合は「ゴールシーク」の機能を使って当たりを出します。




しかし今回のご相談は

＞今回は見積額が900000円になる（あるいは、もっとも近い金額）ように、上記人工を調整（0.5人工単位）したい

そういう前提は無しの、ただ目標額に近づけたいだけということで、そういうの所謂「数字遊び」とも言いますけど、その場合はエクセルの「ソルバー」の機能などを使って一番近い数字を勝手に割振りさせる事もできます。


ご利用のエクセルのバージョンが不明のため具体的に何を選んでこれをクリックみたいな手取り足取り段取りをご説明しませんが、次のように作成していきます。


手順：
ソルバーを追加する
添付図のようにシートを作成する
Ｃ２：　=F2*0.5　以下同文
Ｄ１１：目標額を手で記入する
Ｄ１２：=(D9-D11)^2

ソルバーを起動する
目的セル　Ｄ１２
目標値　　最小値
変数セルの範囲　Ｆ２：Ｆ５
制約条件
　Ｆ２：Ｆ５　整数
　Ｆ２：Ｆ５　>=0

と設定し実行すると、勝手に人工を割り振ってくれます。

この回答へのお礼

ご回答ありがとうございました。

ゴールシークは使ったことありましたが、「ソルバー」の機能というのがあるのですね。今回のようなときにぴったりの機能ですね。

丁寧に手順まで教えていただきありがとうございました。
さっそく試してみて、うまく計算することがありました。
これからもこの機能を活用してみたいと思います。

お礼日時：2014/09/14 14:35

No.1 回答者： trajaa 回答日時： 2014/09/13 06:51

こういう感じ？

この回答へのお礼

おはようございます、早速のご回答ありがとうございました。
最終結果は回答の通りです。
この結果の人工を探し出すのに、手動で何パターンも試していくのではなくて、自動で割り振りができれば、と考えています。

お礼日時：2014/09/13 07:36