関数に詳しい方お願い致します。Excel2010 FILTER関数を使わずに、指定の範囲から複数条件

関数に詳しい方お願い致します。Excel2010

FILTER関数を使わずに、指定の範囲から複数条件を検索し、一致した行のみを抜き出そうとしています。

・抜出範囲 シート「入力用 D3:AR502」
・条件指定に使用しているセルは、シート「入力用 J3:J502」以外は全て計算式が入っています

また、指定したい条件が4パターンあります。

①・入力用AT3:AT502とE1が一致
・入力用J3:J502の左二文字とC1が一致
・入力用K3:K502とE1が不一致

②・入力用AT3:AT502とE1が一致
・入力用J3:J502の左二文字とC1が一致
・入力用K3:K502とE1が一致

③・入力用AT3:AT502とE1が不一致
・入力用J3:J502の左二文字とC1が一致
・入力用K3:K502とE1が一致

④・入力用AT3:AT502とE1が不一致
・入力用J3:J502の左二文字とC1が一致
・入力用K3:K502とE1が不一致

上記のうち、①のパターンをやろうとした関数が下記なのですが、3つ目の(入力用!$K$3:$K$502<>$E$1)の部分が反映してないものしてくれませんでした…。

{=IF(入力用!D3="","",IFERROR(INDEX(入力用!$D$3:$AR$502,MATCH(LARGE((入力用!$AT$3:$AT$502=$E$1)*(LEFT(入力用!$J$3:$J$502,2)=$C$1)*(入力用!$K$3:$K$502<>$E$1)/ROW(入力用!$D$3:$AR$502),ROWS(入力用!$D$3:$D3)),1/ROW(入力用!$D$3:$AR$502),0),COLUMNS(入力用!$D$3:D$3)),""))}


添削や、もしくは完全に別の案でも構いませんのでご教示お願い致します。
情報不足部分あれば補足致します。

No.4 ベストアンサー 回答者： fujillin 回答日時： 2021/05/31 12:37

No3です

＞具体的には、E1<>K列が反映していませんでした
当方の環境では、正しく認識できています。

＞…セルの中に入ってるのが別の式だからでしょうか？
式そのものは関係ないはずです。式の結果を判断するはずなので。

試しに、反映されないセルに対して、直接、
　=(E1<>対象セル）
が、TRUEになるのかどうか確認してみてはいかがでしょうか？

計算の内容は理解しておられるようですので、条件を減らすなどしてみて、「何が本当の原因なのか」を調べてみてください。
実際のデータが不明なので、残念ながら、当方でできることはなさそうに思います。

この回答へのお礼

該当の列を比較したら一致しなかったので、一致するようにバリュー関数など使用して直したら正常に作動しました！
質問文の式もこれでダメだったようです…。
見た目に惑わされて、初歩的な確認を怠っておりました。お恥ずかしい限りです。

今回教えて頂いた式が簡潔で分かりやすいので、使用させて頂こうと思います！ご教示ありがとうございました！

お礼日時：2021/05/31 19:47

No.3 回答者： fujillin 回答日時： 2021/05/30 22:49

No2です

＞LARGEを使っていたのですが、AGGREGATEの集計方法は14ではなく
＞15で良いのでしょうか？
元のデータ群での出現順に詰めて表示するなら15です。
逆順がよければ14にしてください。
No1に書き忘れましたが、配列数式ではありませんので、Ctr+Shift+Enterは不要です。

＞また、D550セルに入力というのは、例であって実際は別の場所でも
＞大丈夫であるという認識で合っていますか？
同じシート内なら大丈夫です。
他のシートの場合は、当然ですが、シート名を正しく付加しないと参照関係がうまくいきません。

この回答へのお礼

まず同じシートに実際に入力してみましたが、上手くいきませんでした。
具体的には、E1<>K列が反映していませんでした(E1と一致しているものも抜き出してしまっている)
質問文で私が書いた式の時もだったのですが、<>を使った条件が反映しないです…セルの中に入ってるのが別の式だからでしょうか？

お礼日時：2021/05/31 10:31

No.2 回答者： fujillin 回答日時： 2021/05/30 21:54

こんばんは

「行で1セットになっているデータから、複数条件に合致する行だけ抽出したい」ということと解釈しました。


簡単な方法は、空き列を作業列にして、その3行目のセルに
　=AND(AT3=$E$1,LEFT(J3,2)=$C$1,K3<>$E$1)
などと入力して、下方にフィルコピーすると、該当するか否かが表示されます。
（上式は①の条件の例です）
抽出するには、このうちのTRUEの行のみを行えば宜しいです。


直接求めることも可能ですが、配列計算をさせることになるので、無駄な計算が多く、計算が重くなる原因になりやすいです。
以下、ご参考までに。
別シートに抽出すると、シート名の分だけ関数式が長くなるので、同一シートでの抽出の例です。

①の例として、D550セルに
=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW($A$3:$A$502)/($E$1=$AT$3:$AT$502)/($C$1=LEFT($J$3:$J$502,2))/($E$1<>$K$3:$K$502),ROW(A1))),"")
を入力して、右方、下方にフィルコピーで抽出されると思います。

原理としては、「条件式で除算することで、合致しないものは0割りのエラー値にして省く」という方法です。
条件が複数ある場合は（＝AND条件の場合）、それぞれの条件式で重ねて除算を行えばよいことになります。
条件が異なるケースを求める場合は条件式を入れ替え、条件の数に応じて除算の回数を増減すればよいという仕組みにしてありますので、比較的応用しやすいのではと思います。

この回答へのお礼

ご回答ありがとうございます！
LARGEを使っていたのですが、AGGREGATEの集計方法は14ではなく15で良いのでしょうか？
また、D550セルに入力というのは、例であって実際は別の場所でも大丈夫であるという認識で合っていますか？
今実際に打って確認出来る状況でなく、確認もせずに重ねて申し訳ありません…！

お礼日時：2021/05/30 22:29

No.1 回答者： banzaiA 回答日時： 2021/05/30 21:39

後学のため教えてください。

なぜ「FILTER関数を使わずに」なのですか？
FILTER関数を使えばなにか不具合でもあるのですか？

この回答へのお礼

ExcelでFILTER関数を使うのは、現状だとMicrosoft365版のExcelでしか使用できない為です。
今回は使用しているのはExcel2010ですので、使用できないのです。

お礼日時：2021/05/30 21:46