エクセル 複数の条件？

質問

お世話になります。エクセル2007です。

商品の卸値を決めるのに複数の工程別単価シートから参照して計算してだそうとしてます。

卸値＝（Ａ工程の単価　+　Ｂ工程の単価）　*　自社マージンかけ率

例えば、Ａ工程のＸ社、Ｙ社、Ｚ社の納品書のシートを
コピーしてまとめてＡ工程単価シートをつくり、商品単価の入るシートにVLOOKUPで、よびだしています。
今まではＡ工程だけだったのですが、Ｂ工程も計算したいと思ったのですが、うまくつくれません。

　　　　Ａ　　　 Ｂ　　　 C　　　　　D　　　　　　　　　　　　E
　１　卸値　　数量　 売上 Ａ工程単価　　　　Ｂ工程単価

今までの式はＡ１に　=(IF(ISERROR(D1),0,ROUNDDOWN((D1*10/7.5),-1)))　で、
常に総売り上げや平均卸値などを
もだしたいので
エラーを０にして総売上は =SUM(Ｃ：Ｃ)のように
計算しています。

Ａ工程はあるけれど、Ｂ工程はない仕事もあり、
場合には　Ｃ１のＢ工程のセルにエラーが出てしまいますが、
複数の条件でどういった式をいれればいいのかわかりません。

Ｄ１にデータがありＥ１がエラーのときはＢ１のＡ工程だけの単価で計算したいです。
Ｂ工程だけのときはありえないので、エラーでＯＫです。

もっとシンプルに質問できたかもしれませんが
いろいろ書いてしまいました。すみません。

どうぞよろしくお願いします。

回答No.2

kagakusuki · ベストアンサー

例え同じ工程であっても、商品の種類毎に、求められるクオリティーが異なるという事もあるでしょうし、発注する毎に、発注する数や納期が異なる事もある筈ですが、
それに対して、同じ工程であっても、会社毎に、クォリティーや処理速度、処理可能な数、等々は異なりますし、会社同士の付き合いというものもあると思いますから、
単純に工程毎の各社のコストのみを基準にして、商品の卸値を決める事は出来ないのではないかと思います。
　例えば、1000個の商品が必要になり、その納期まで10日しかない場合において、A工程において最もコストが安い会社が「エ社」であったとしても、「エ社」の設備では1日あたり80個分しかA工程の処理を行えなかったとしたら、A工程の一部は「エ社」以外の会社にも割り振る必要が出て来ますから、「数量」欄に入る数値は、人間が決めなければならないのではないかと思います。
　ですから、Excelで自動化する事が出来るのは、人間が判断する際の目安として、全てのパターンを算出する異までではないかと思います。

　そこで、添付画像の様な形式の表にされては如何でしょうか。
　尚、ここでは仮に工程別単価シートのシート名をSheet2としています。
　又、卸値の順位は卸値が安い順、利益の順位は「各工程に要する費用の合計と、卸値との差額」が多い順となっていて、[ ]の中の数字は、同じ値（重複）が何個あるのかを表しています。
　又、C列のマージンと、各数量の値は、手入力するしかありません。

　まず、Sheet1のH6セルに次の数式を入力して下さい。

=IF(OR(ROWS($6:6)>((COUNT(Sheet2!$B:$B)-1)*($G$4="有")+1)*((COUNT(Sheet2!$D:$D)-1)*($I$4="有")+1)*((COUNT(Sheet2!$F:$F)-1)*($K$4="有")+1),$G$4<>"有"),"",INDEX(Sheet2!A:A,INT((ROWS($6:6)-1)/(((COUNT(Sheet2!$D:$D)-1)*($I$4="有")+1)*((COUNT(Sheet2!$F:$F)-1)*($K$4="有")+1)))+ROW(Sheet2!$A$3)+1))

　次に、Sheet1のH6セルをコピーして、Sheet1のI6セルに貼り付けて下さい。
　次に、Sheet1のJ6セルに次の数式を入力して下さい。

=IF(OR(ROWS($6:6)>((COUNT(Sheet2!$B:$B)-1)*($G$4="有")+1)*((COUNT(Sheet2!$D:$D)-1)*($I$4="有")+1)*((COUNT(Sheet2!$F:$F)-1)*($K$4="有")+1),$I$4<>"有"),"",INDEX(Sheet2!C:C,MOD(INT((ROWS($6:6)-1)/((COUNT(Sheet2!$F:$F)-1)*($K$4="有")+1)),(COUNT(Sheet2!$D:$D)-1)*($I$4="有")+1)+ROW(Sheet2!$A$3)+1))

　次に、Sheet1のJ6セルをコピーして、Sheet1のK6セルに貼り付けて下さい。
　次に、Sheet1のL6セルに次の数式を入力して下さい。

=IF(OR(ROWS($6:6)>((COUNT(Sheet2!$B:$B)-1)*($G$4="有")+1)*((COUNT(Sheet2!$D:$D)-1)*($I$4="有")+1)*((COUNT(Sheet2!$F:$F)-1)*($K$4="有")+1),$K$4<>"有"),"",INDEX(Sheet2!E:E,MOD(ROWS($6:6)-1,COUNT(Sheet2!$F:$F))+ROW(Sheet2!$A$3)+1))

　次に、Sheet1のL6セルをコピーして、Sheet1のM6セルに貼り付けて下さい。

　次に、Sheet1のA6セルに次の数式を入力して下さい。

=IF(OR(ROWS($6:6)>((COUNT(Sheet2!$B:$B)-1)*($G$4="有")+1)*((COUNT(Sheet2!$D:$D)-1)*($I$4="有")+1)*((COUNT(Sheet2!$F:$F)-1)*($K$4="有")+1),COUNTIF($G$4:$L$4,"有")=0),"",ROUNDDOWN(SUM($G6:$L6)*$F$2,-1))

　次に、Sheet1のB6セルに次の数式を入力して下さい。

=IF($A6="","",RANK($A6,$A:$A,1)&"["&COUNTIF($A:$A,$A6)&"]")

　次に、Sheet1のD6セルに次の数式を入力して下さい。

=IF(COUNT($A6,$C6)=2,$A6*$C6,"")

　次に、Sheet1のE6セルに次の数式を入力して下さい。

=IF($A6="","",$A6-SUM($G6:$L6))

　次に、Sheet1のF6セルに次の数式を入力して下さい。

=IF($C6="","",$C6/$A6)

　次に、Sheet1のF6セルの書式設定を、[パーセンテージ]にしてください。
　次に、Sheet1のG6セルに次の数式を入力して下さい。

=IF($C6="","",RANK($C6,$C:$C)&"["&COUNTIF($C:$C,$C6)&"]")

　次に、Sheet1のA6～M6の範囲をコピーして、同じ列の7行目以下に貼り付けて下さい。

　以上です。

お礼

ありがとうございます。
お礼遅くなって申し訳ありません。
大元の考え方から、表まで作っていただいて
本当にありがとうございます。とても嬉しいです。

ご指摘の通り、完全に自動化は難しく、
考えあぐねていました。

誠に申し訳ないのですが、まだ回答者様のご提示していただいたものを
よく理解できていません。　数日中にじっくり勉強させていただきます。
遅くなりましたが、取り急ぎお礼させていただきます。

回答No.1

mshr1962

A1=ROUNDDOWN(((IF(ISERROR(D1),0,D1)+IF(ISERROR(E1),0,E1))*10/7.5),-1))

お礼

出来ました。
まだ基本がわかってないと痛感しました。
どうもありがとうございます。