（Excel）グループ内で特定の数値が存在する場合のみ別セルに値を入力する方法

お世話になります。以下のような動作を関数で実現させたいのですが、行き詰ってしまったためお知恵を貸してくださいませ。

【データ構成】
A科目 B本部 Cチーム D取引先コード E増減 という6項目で構成された明細があります。
そして、Aの科目グループ毎に、Bの本部毎合計行、Cのチームごと合計行が間に挟まっています。

A B C D E F（作業用追加列）
1 aaa 北本部 Nチーム a123 300
2 aaa 北本部 Nチーム b222 -250
3 aaa 北本部 Nチーム合計 50
4 aaa 北本部 Oチーム c899 -50
5 aaa 北本部 Oチーム d900 300
6 aaa 北本部 Oチーム合計 250
7 aaa 北本部合計 300
8 aaa 南本部 Sチーム b123 100
9 aaa 南本部 Sチーム合計 100
10 aaa 南本部合計 100
…

【実現したい動作】
やりたいことは、「フィルタにより明細のEの数値が一定の基準値（100以上OR▲100以下）の場合、明細とその明細の含まれる本部及びチーム合計行を表示させたい」というものです。
なお、合計行は、表示されている明細の合算値ではなく、固定値として保持しています。

【実現するための方針】
合計行はプラスマイナスでネットされるため、単純な数値フィルタにすると、除外されてしまう場合もあるので、Fにフィルタ用のダミー列をつくり、各行に「合計行かつ、条件に該当する明細が自分のグループに存在する場合は、フィルタにヒットする数値（ここでは”100”）がFにセットされる、という式を入れたいと考えております。

そこで、「『合計行』（B or C列に”合計”の文字が含まれる）場合、自分の科目と同じグループ、かつ同じ本部の中で該当する明細があるかを探しにいく」、という方向で以下のような式を組んでみましたが、おそらくMAX/MIN関数の使い方が間違っているのかうまくいきません。
この式にこだわるつもりはないのですが、やりたいことのイメージを補足するため記載させて頂きます。

（例：3行目に入る式）
=IF(AND(COUNTIF(B3,"*合計*"),MAX(IF(A:A=A3)*(B:B=LEFT(B3,2)),E:E))>=100),100,IF(AND(COUNTIF(B3,"*合計*"),MIN(IF(A:A=A3)*(B:B=LEFT(B3,2)),E:E))<=100),100,IF(AND(COUNTIF(C15,"*合計*"),MAX(IF(A:A=A3)*(C:C=LEFT(C3,4)),E:E))>=100),100,IF(AND(COUNTIF(C15,"*合計*"),MIN(IF(A:A=A3)*(C:C=LEFT(C3,4)),E:E))<=100))

この数式の誤り（方向性のみでも結構です）、あるいはよりよい方法があれば教えていただけますとありがたく存じます。
どうぞよろしくお願いいたします。

No.1 ベストアンサー 回答者： jmuiny 回答日時： 2016/03/18 13:14

該当行のF列に「1」をセットする一例です。

わかりやすいように作業列を複数使います。
3行目の式が書いてあったので、データを3行目からとします。

G列は明細の該当のものに1を設定
H列は該当明細の部名
I列は該当明細のチーム名

F3
=IF(G3=1,1,IF(OR(AND(COUNTIF(B3,"*合計"),COUNTIF($H:$H,SUBSTITUTE(B3,"合計",""))),AND(C3<>"",COUNTIF($I:$I,SUBSTITUTE(C3,"合計","")))),1,""))
G3
=IF(AND(ISERROR(FIND("合計",B3)),ISERROR(FIND("合計",C3)),ABS(E3)>=100),1,"")
H3
=IF(G3=1,B3,"")
I3
=IF(G3=1,C3,"")

この回答へのお礼

なるほど…！目からウロコでした。ありがとうございました。とても助かりました。

お礼日時：2016/03/18 18:37