![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e6f04cf)
表題の通りなのですがexcel2016をメインで使用しており
Aの表にて型番[ab]のものだけ抽出してBの表のようにあらわしたくおもってます
FILTER関数ならこの式のように簡単にできるのは把握しているのですが、これをEXCEL2016ベースでやるにはどうしたらできるでしょうか?できればVBでなく関数で行いたいのです。
というもの以前に関数だけでこれを実施する方法を記載しているウェブサイトがあって、そこを見てやっていたのですが、そのサイトがなくなってしまっていて、その時作ったエクセルもどこへやら、全くやり方がわかりません。。。
どうぞよろしくお願いいたします。
(ここは見たのですがhttps://www.ilove-it.net/filter/ 私が行いたいAの表は1000行程度あって、この方法だとあまり実用的ではないのです。できたら、データがあるものだけが上詰めで出るものが理想でして過去に見た式はそれができていました。ちなみに1000行の内、抜き出したいのはマックス70行程度なのです)
m()m
![「エクセル2016でfilter関数がない」の質問画像](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/6/542515699_6507e86b81471/M.jpg)
No.1ベストアンサー
- 回答日時:
こんにちは
別表として詰めて抽出したいのなら、エクセルの機能の「フィルタオプション」の機能を使うのが簡単でしょう。
関数で行いたい場合には、一気に求めずに作業列を利用して該当データをチェックしそれを基に求める方法の方が、後のメンテナンス等を考えると良いと思います。
(要はご自身が理解できる内容で作成しておく方が良いという意味です)
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))),"")
を入力し、下方にフィルコピーでできるでしょう。
No.4
- 回答日時:
配列(CSE)数式の
=IFERROR(INDEX(品名,SMALL(IF(型番=E$4,ROW(型番)),ROW(A1))-3),"")
を入力したセル F4 を下方にズズーッとオートフィルするだけえ~ッ!
ただし、下記の[名前の管理]に示す名前を参照範囲に名付けておくこと
[名前の管理]
名前→ 参照範囲
ーーーーーーーーー
型番→ =$B$4:$B$12
品名→ =$C$4:$C$12
![「エクセル2016でfilter関数がない」の回答画像4](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/c/298588_650875eeb796a/M.jpg)
No.3
- 回答日時:
No.2です。
前回回答で、=COUNTIF(型番の表示範囲,当該行の型番セルの値)
と数式の説明をしましたが、言葉足らずでした。
=COUNTIF(型番の表示範囲の最上部のセル~当該行のセル,当該行の型番セルの値)
という表現が適切だと思います。お詫びして修正します。
No.2
- 回答日時:
作業列を使わない方法は、既に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)),"")
を入れて下方向へコピーしています。
これで、ご質問者のご希望である
>データがあるものだけが上詰めで出る
という状態になると思います。
![「エクセル2016でfilter関数がない」の回答画像2](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/f/142736_650842f6b7bd2/M.png)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) スプレッドシートで抽出したい値があります。助けて下さい。 3 2021/12/11 10:58
- Excel(エクセル) Indirect関数について、Formulatextで抽出した数式を参照したい。 1 2022/12/15 11:16
- 会計ソフト・業務用ソフト エクセルです 2 2022/09/17 12:14
- その他(データベース) Access Nz関数の合計値の小数点桁数について 1 2021/12/14 14:51
- Excel(エクセル) IF文の管理を簡単にしたい 4 2021/11/07 11:23
- Excel(エクセル) エクセルについて教えてください。 1 2021/10/19 17:24
- Excel(エクセル) エクセル特定記号で囲まれた数字、文字を抽出 3 2021/12/02 14:11
- Perl INDIRECT 横に再度抽出したい 3 2021/11/10 23:23
- Excel(エクセル) エクセルをスプレッドシートにしたんですがfilter関数前にxlwsが付いてしまい、関数が反映されま 2 2021/12/24 03:21
- Excel(エクセル) ExcelのIF関数や複数の関数の複雑な組み合わせを、ひたすら練習できるような問題集があったら教えて 6 2021/10/22 14:32
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
エクセル2016でfilter関数がなく、さらに条件が[含まれる]で抜き出せる関数を知りたいです。
Excel(エクセル)
-
Excel関数、何がいけないのかわかりません。
Excel(エクセル)
-
エクセルをスプレッドシートにしたんですがfilter関数前にxlwsが付いてしまい、関数が反映されま
Excel(エクセル)
-
-
4
複雑なシフト表から1日ごとの出勤者、シフトを抜き出したいです
Excel(エクセル)
-
5
Excelで数式の入っているセルを空白と認識させたい
Excel(エクセル)
-
6
Excelでセル参照したとき、書式も一緒に持ってくるには?
Windows Vista・XP
-
7
EXCELの条件付き書式で数式を空白と認識してくれる方法
Excel(エクセル)
-
8
指定期間内のデータを別シートへ抽出
Excel(エクセル)
-
9
excelで、空白を除いてデータを抽出する方法について
Excel(エクセル)
-
10
UNIQUE関数が使えないバージョンで重複削除したい。
Excel(エクセル)
-
11
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには
Excel(エクセル)
-
12
Excelの条件付き書式設定の太い罫線
Excel(エクセル)
-
13
リンク元の日付が空白の時リンク先セルも空白にしたい
Excel(エクセル)
-
14
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
15
本日の日付とマッチした列の値を抽出
Excel(エクセル)
-
16
EXCEL VBAで全選択範囲の解除
Excel(エクセル)
-
17
Excelの入力規則で2列表示したい
Excel(エクセル)
-
18
エクセルのエラーメッセージ「400」って?
Visual Basic(VBA)
-
19
エクセルで特定の列が0表示の場合、その行を非表示にしたい
Excel(エクセル)
-
20
ある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelデータをコピペして、ペー...
-
スプレッドシート、Excelでの数...
-
エクセルの関数について教えて...
-
スプレッドシートで使う数式を...
-
Microsoft Officeの中古は信用...
-
エクセルで会社の従業員のデー...
-
Excelで50個のセルに同じ文字を...
-
エクセルの表で1年間の曜日を...
-
エクセルでの特別な文字を上に...
-
エクセルで不等号記号(≠)が上に...
-
エクセルでB列でフィルターをか...
-
エクセルでセルに標準で入力さ...
-
Excelで、項目の種類ごとにカウ...
-
【マクロ】アクティブセルにブ...
-
EXCELの質問です 119から足した...
-
【マクロ】アクティブセルの、...
-
【マクロ】アクティブセルの行...
-
エクセルの空欄をつめて、次の...
-
Excelの数式について教えてくだ...
-
非表示列の再表示に失敗
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報
今回は素早いご回答頂き大変ありがとうございました。非常に助かっております。
すいません。一つ条件が変わり[ab]でなく[ab]が含まれるものを抽出しなければならなくなりました。
色々調べてみて、index(find(ならいけると思ったのですが、うまくいきません。
どういった式ならこれができますでしょうか?
どうぞよろしくお願いいたします。
今回は素早い回答誠にありがとうございました。
申し訳ないのですが、条件が変わりまして「ab」でなく「ab」が含まれるものを抽出しなければならなくなりました。
この場合WEBで色々調べてみてindex(findなら大丈夫かと思ってつくってみたのですが、うまくいかず、、、。どういう式ならこれができるでしょうか?
宜しければどうぞよろしくお願いいたします。m()m