エクセル2016でfilter関数がないので、、抜き出す関数をおしえてください。

表題の通りなのですがexcel2016をメインで使用しており

Aの表にて型番[ab]のものだけ抽出してBの表のようにあらわしたくおもってます

FILTER関数ならこの式のように簡単にできるのは把握しているのですが、これをEXCEL2016ベースでやるにはどうしたらできるでしょうか？できればVBでなく関数で行いたいのです。

というもの以前に関数だけでこれを実施する方法を記載しているウェブサイトがあって、そこを見てやっていたのですが、そのサイトがなくなってしまっていて、その時作ったエクセルもどこへやら、全くやり方がわかりません。。。

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

（ここは見たのですがhttps://www.ilove-it.net/filter/　私が行いたいAの表は1000行程度あって、この方法だとあまり実用的ではないのです。できたら、データがあるものだけが上詰めで出るものが理想でして過去に見た式はそれができていました。ちなみに1000行の内、抜き出したいのはマックス70行程度なのです）

m()m

質問者からの補足コメント

• 今回は素早いご回答頂き大変ありがとうございました。非常に助かっております。
  
  すいません。一つ条件が変わり[ab]でなく[ab]が含まれるものを抽出しなければならなくなりました。
  色々調べてみて、index(find(ならいけると思ったのですが、うまくいきません。
  
  どういった式ならこれができますでしょうか？
  
  どうぞよろしくお願いいたします。

  
  No.1の回答に寄せられた補足コメントです。 補足日時：2023/09/19 09:41

• 今回は素早い回答誠にありがとうございました。
  
  申し訳ないのですが、条件が変わりまして「ab」でなく「ab」が含まれるものを抽出しなければならなくなりました。
  
  この場合WEBで色々調べてみてindex(findなら大丈夫かと思ってつくってみたのですが、うまくいかず、、、。どういう式ならこれができるでしょうか？
  
  宜しければどうぞよろしくお願いいたします。m()m

  
  No.1の回答に寄せられた補足コメントです。 補足日時：2023/09/19 09:50

No.1 ベストアンサー 回答者： fujillin 回答日時： 2023/09/18 16:46

こんにちは

別表として詰めて抽出したいのなら、エクセルの機能の「フィルタオプション」の機能を使うのが簡単でしょう。
関数で行いたい場合には、一気に求めずに作業列を利用して該当データをチェックしそれを基に求める方法の方が、後のメンテナンス等を考えると良いと思います。
（要はご自身が理解できる内容で作成しておく方が良いという意味です）
http://office-qa.com/Excel/ex257.htm
https://note.com/drascent/n/n7cb0a7465873


どうしても一気に求めたいのなら、ご提示のレイアウトでF4セルに
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(B$4:B$12)/(B$4:B$12=E$4),ROW(A1))),"")
を入力し、下方にフィルコピーでできるでしょう。

この回答へのお礼

本当にありがとうございます！！

お礼日時：2023/09/19 09:07

No.2 回答者： goomania 回答日時： 2023/09/18 21:33

作業列を使わない方法は、既にNo.1さんによって回答されているので、ご質問者が見たと仰っている

https://www.ilove-it.net/filter/
で解説されている方法を応用し、COUNTIF関数を使って、作業列を作成して対応する方法をお示しします。

添付画像をご覧ください。
抽出対象の型番を、F4セルで指定すると、対象の商品名がG4以下に表示されるものとます。

D4セルに
=COUNTIF(型番の表示範囲,当該行の型番セルの値)
という数式を入れます。画像の例では、

=COUNTIF($B$4:$B4,B4)

を入れて下方向へコピーしています。

G4セルに
=IFERROR(INDEX(商品名の表示範囲,MATCH(抽出指定型番セル&"-"&ROW(A1),INDEX(型番の表示範囲&"-"&作業列の表示範囲,0),0)),"")
という数式を入れます。画像の例では、

=IFERROR(INDEX($C$4:$C$12,MATCH($F$4&"-"&ROW(A1),INDEX($B$4:$B$12&"-"&$D$4:$D$12,0),0)),"")

を入れて下方向へコピーしています。

これで、ご質問者のご希望である
>データがあるものだけが上詰めで出る
という状態になると思います。

この回答へのお礼

本当にありがとうございます！！

お礼日時：2023/09/19 09:07

No.3 回答者： goomania 回答日時： 2023/09/19 00:14

No.2です。

前回回答で、

=COUNTIF(型番の表示範囲,当該行の型番セルの値)

と数式の説明をしましたが、言葉足らずでした。

=COUNTIF(型番の表示範囲の最上部のセル～当該行のセル,当該行の型番セルの値)

という表現が適切だと思います。お詫びして修正します。

No.4 回答者： mike_g 回答日時： 2023/09/19 01:08

配列(CSE)数式の

=IFERROR(INDEX(品名,SMALL(IF(型番=E$4,ROW(型番)),ROW(A1))-3),"")
を入力したセル F4 を下方にズズーッとオートフィルするだけえ～ｯ！

ただし、下記の[名前の管理]に示す名前を参照範囲に名付けておくこと

[名前の管理]
名前→ 参照範囲
ーーーーーーーーー
型番→ =$B$4:$B$12
品名→ =$C$4:$C$12

この回答へのお礼

本当にありがとうございます！！

お礼日時：2023/09/19 09:07