エクセル同じ番号が全て条件を満たしたら記を入れる

質問

エクセルで、
１つの注文番号に、複数の型番の品物が載っています。
注文番号が同じもので全て出荷済みになった場合のみ、
請求書を発行せよと言う表示をさせたです。
　　A　　　　　　B　　　C　　　D　
　注文番号　型番　出荷　請求書
1　001　　　　イ　　済　　　要発行
2　001　　　　ロ　　済　　　要発行
3 002　　　　ロ　　済　　　保留
4 002　　　　ハ　　未　　　保留
5　003　　　　ニ　　未　　　保留

このたとえの場合、
注文番号001はイとロと言う品物を出荷済みなので、Dに請求書を「要発行」、
注文番号002はロは出荷済みだがハは未集荷なので、請求書を「保留」
と表示させたいです。

Aの列に同じ注文番号が幾つあるかをチェックして、
同じ注文番号のうちCの列が全て「済み」になっているものついては「要発行」、
同じ注文番号のうちCの列に「未」がある場合は「保留」
と表示させたいです。

注文番号は日々、ドンドン追加されていくので、「001」を検索するとか、
A1と同じものを検索すると言う式は使えません。

この場合、どのような数式を使えばできますか？

回答No.1

nattocurry · ベストアンサー

一例です。

D1に
=IF(COUNTIF(A:A,A1)=SUMPRODUCT((INDIRECT("A$1:A$"&COUNTA(A:A))=A1)*(INDIRECT("C$1:C$"&COUNTA(C:C))="済")),"要発行","保留")
と入力して、D1をD2以降にコピペ。

お礼

ありがとうございます。
単純化した例では、うまくいっていますが、
本番のもうちょっと複雑なものでは、上手くいっていません。
もうちょっとトライして、ダメだったら改めて質問します。

この数式の場合、６行目以下が空欄だと「要発行」の表示が出てしまいますが、
空欄の場合、Dも空欄にしたい場合は、どうすればよいですか？

回答No.2

guchi_yama

D1=IF(A1="","",IF(COUNTIF(A:A,A1)=SUMPRODUCT(($A$1:$A$1000=A1)*($C$1:$C$1000="済")),"要発行","保留"))

と、A列が未入力の場合、空白にする関数を最初に入れれば、
6行目以降の空白部分に対して空白を返します。

※SUMPRODUCT()の範囲に対して、1000までと仮定して入れています。

お礼

ありがとうございます。
シンプルな練習では上手く働きました。

先ほどの式も、今の式も、本番のもうちょっと複雑なものでは、
なぜか、要発行でも保留でもなく、空欄が返ってきます。

今日は、時間がなくなってきましたので、また明日挑戦したいと思います。