![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?8acaa2e)
表題の通りなのですが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も見ています
-
「環境が人を育てる」って本当?環境によって人格や生き方は本当に変わるのか
環境が人生に与える影響は実際どれほどのものなのか、専門家の田宮由美さんに伺った。
-
Excel関数、何がいけないのかわかりません。
Excel(エクセル)
-
エクセル2016でfilter関数がなく、さらに条件が[含まれる]で抜き出せる関数を知りたいです。
Excel(エクセル)
-
エクセルをスプレッドシートにしたんですがfilter関数前にxlwsが付いてしまい、関数が反映されま
Excel(エクセル)
-
-
4
複雑なシフト表から1日ごとの出勤者、シフトを抜き出したいです
Excel(エクセル)
-
5
UNIQUE関数が使えないバージョンで重複削除したい。
Excel(エクセル)
-
6
Excelの条件付き書式設定の太い罫線
Excel(エクセル)
-
7
Excelでセル参照したとき、書式も一緒に持ってくるには?
Windows Vista・XP
-
8
本日の日付とマッチした列の値を抽出
Excel(エクセル)
-
9
Excelで数式の入っているセルを空白と認識させたい
Excel(エクセル)
-
10
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには
Excel(エクセル)
-
11
EXCEL VBAで全選択範囲の解除
Excel(エクセル)
-
12
指定期間内のデータを別シートへ抽出
Excel(エクセル)
-
13
excelで、空白を除いてデータを抽出する方法について
Excel(エクセル)
-
14
エクセルのエラーメッセージ「400」って?
Visual Basic(VBA)
-
15
エクセルで特定の列が0表示の場合、その行を非表示にしたい
Excel(エクセル)
-
16
EXCELの条件付き書式で数式を空白と認識してくれる方法
Excel(エクセル)
-
17
ある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?
Excel(エクセル)
-
18
リンク元の日付が空白の時リンク先セルも空白にしたい
Excel(エクセル)
-
19
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
20
OFFSET関数とCOUNTIFって組み合わせはできますか? COUNTIF関数で行を新しく追加した
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのツールバーから数値...
-
祝日と土曜、日曜の合計をカウ...
-
【マクロ】2回実行したら、エ...
-
特定の文字列を含む、住所を抽...
-
EXCELの散布図で日付が1900年に...
-
マイクロソフトのPADを使ってい...
-
Excel分数の表示について
-
Excelで表を作ったところに文字...
-
マクロエクセルのブロック解除
-
文字2桁、3桁交じりの文字列...
-
絶対参照
-
Excelについての質問です。 B2...
-
在庫管理表に使うエクセルの関...
-
【マクロ】VLOOKUPにて参照元に...
-
【EXCEL】画像の黄色部分の抽出...
-
DATE関数で現在の年齢を出した...
-
ユーザー定義関数をアドイン登...
-
エクセルでの作業計算方法について
-
行数が不規則な一週間ごとの合...
-
Excelピボットテーブルの1行目
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報
今回は素早いご回答頂き大変ありがとうございました。非常に助かっております。
すいません。一つ条件が変わり[ab]でなく[ab]が含まれるものを抽出しなければならなくなりました。
色々調べてみて、index(find(ならいけると思ったのですが、うまくいきません。
どういった式ならこれができますでしょうか?
どうぞよろしくお願いいたします。
今回は素早い回答誠にありがとうございました。
申し訳ないのですが、条件が変わりまして「ab」でなく「ab」が含まれるものを抽出しなければならなくなりました。
この場合WEBで色々調べてみてindex(findなら大丈夫かと思ってつくってみたのですが、うまくいかず、、、。どういう式ならこれができるでしょうか?
宜しければどうぞよろしくお願いいたします。m()m