Excel関数 条件一致先の条件が一致した際の計算

Excel関数にて、下記の表のように条件書式を使い、条件が一致した場合、色付けをしたいのですが、お力添えの程お願いできますでしょうか。

Ａ列は日付、Ｂ列は通し番号、Ｃ列は状況、Ｄ列は検索値（入力箇所・条件書式箇所）。

下記表は、空欄のＤ列に、当日から過去入力されたＢ列の通し番号と同じ番号を入力した場合、また状況の（Ｃ列）が『完了』していれば、Ｄ列に入力した番号が赤字になるというものです。

一点、過去に『完了』している番号のものも、月半ばなどで継続に戻った場合、月頭等で『完了』していても、最新の状態を認識し条件を適応する。ただし、過去のもののみで、先の情報（日付上）は認識しないものとします。

Ａ列：Ｂ列：Ｃ列：Ｄ列
7/1：111：完了：（空欄）
7/1：122：継続：111を入力したら（赤色）
7/3：133：新規：122を入力したら（なし）
7/5：124：新規：122を入力したら（なし）
7/7：111：継続：133を入力したら（なし）
7/9：111：継続：111を入力したら（なし）
7/9：111：完了：122を入力したら（なし）
7/9：134：完了：111を入力したら（赤色）

※条件書式の中に、IFとCountif、Match等使って試してみたのですが、適応できませんでした。

申し訳ございませんが、お力添えの程宜しくお願いします。

No.4 ベストアンサー 回答者： tom04 回答日時： 2015/07/18 11:22

No.3です。

前回の条件付き書式の数式を↓に変更してください。
=IF(COUNTIF(B$2:B2,D3),INDEX(C$2:C2,MAX(IF(B$2:B2=D3,ROW(A$1:A1))))="完了")

前回の数式ではB列にないデータ（番号）が入力されても「赤」のフォントになってしまいます。

どうも失礼しました。m(_ _)m

この回答へのお礼

tom04さん、ご連絡遅くなり申し訳ございません。

ご親切にご対応頂き、本当にありがとうございました。
訂正頂いた分で実行できました。
イメージ通りの内容です。

本当にありがとうございました。

お礼日時：2015/07/21 19:23

No.5 回答者： CoalTar 回答日時： 2015/07/18 12:43

No.1です。

少し説明不足だったので添付図に関することで、
条件付き書式の数式で判定の基準となっている値をE列に持ってきています
E3セルに
=IF(ISNA(MATCH(D3,$B$2:INDEX($B:$B,ROW()-1),0)),FALSE,INDEX($C:$C,MAX(IF(D3=$B$2:INDEX($B:$B,ROW()-1),ROW($B$2:INDEX($B:$B,ROW()-1))))))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
下へオートフィル

XL2003なので、tom04さんの方法で条件付き書式を作成してください
中身は、tom04さんとほぼ一緒です。

>そのほかの方法で対応でそうなものはありますでしょうか？
そのまえに、条件は上記式で良いのでしょうか？
簡単に説明すると
検索範囲は2行目から1行上までで、合致する中で一番下のC列の値
その値が「完了」だったら文字を赤くする

この回答へのお礼

CoalTarさん、ご連絡遅くなり申し訳ございません。

頂いた内容通り、実行できました。
ご親切にご対応頂き本当にありがとうございました。

お礼日時：2015/07/21 19:22

No.3 回答者： tom04 回答日時： 2015/07/18 09:50

こんにちは！

こういうコトでしょうか？
↓の画像ではD3セル以降のD列を範囲指定　→　条件付き書式　→　新しいルール　→　数式を使用して・・・　→　数式欄に
=IF(D3<>"",INDEX(C$2:C2,MAX(IF(B$2:B2=D3,ROW(A$1:A1))))="完了")

という数式を入れ、書式からフォント色を「赤」に設定しています。
前提条件として、A列日付は昇順になっているとします。

※　外していたらごめんなさい。m(_ _)m

No.2 回答者： mike_g 回答日時： 2015/07/17 16:15

率直に申し上げて、御説明がダラダラし過ぎて理解困難です。

No.1 回答者： CoalTar 回答日時： 2015/07/16 18:41

とりあえずできますが、範囲が広いと計算が遅くなります。

そのときは、マクロを考えたほうが良さそう。
また、日付ではなく1行上までの検索です

添付図参照
D3セルで条件付き書式
数式が
=IF(ISNA(MATCH(D3,$B$2:INDEX($B:$B,ROW()-1),0)),FALSE,INDEX($C:$C,MAX(IF(D3=$B$2:INDEX($B:$B,ROW()-1),ROW($B$2:INDEX($B:$B,ROW()-1))))))="完了"
D3セルを下へ右ボタンフィルドラッグし、書式のみコピー

この回答へのお礼

解答ありがとうございます。　ただごめんなさい、条件付き書式に登録を行うと次のメッセージが表示されます＜条件　条件付書式には、参照演算子（ＯＲ演算子、ＡＮＤ演算子、範囲演算子など）および配列定数は使用できません＞と出てしまいます。ちなみにこちらのエクセルは2013です。

そのほかの方法で対応でそうなものはありますでしょうか？

お礼日時：2015/07/17 18:54