
表題の通りなのですがexcel2016をメインで使用しており
Aの表にて型番[ab]のものだけ抽出してBの表のようにあらわしたくおもってます
FILTER関数ならこの式のように簡単にできるのは把握しているのですが、これをEXCEL2016ベースでやるにはどうしたらできるでしょうか?できればVBでなく関数で行いたいのです。
というもの以前に関数だけでこれを実施する方法を記載しているウェブサイトがあって、そこを見てやっていたのですが、そのサイトがなくなってしまっていて、その時作ったエクセルもどこへやら、全くやり方がわかりません。。。
どうぞよろしくお願いいたします。
(ここは見たのですがhttps://www.ilove-it.net/filter/ 私が行いたいAの表は1000行程度あって、この方法だとあまり実用的ではないのです。できたら、データがあるものだけが上詰めで出るものが理想でして過去に見た式はそれができていました。ちなみに1000行の内、抜き出したいのはマックス70行程度なのです)
m()m

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

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)),"")
を入れて下方向へコピーしています。
これで、ご質問者のご希望である
>データがあるものだけが上詰めで出る
という状態になると思います。

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
エクセル2016でfilter関数がなく、さらに条件が[含まれる]で抜き出せる関数を知りたいです。
Excel(エクセル)
-
Excel関数、何がいけないのかわかりません。
Excel(エクセル)
-
UNIQUE関数が使えないバージョンで重複削除したい。
Excel(エクセル)
-
-
4
エクセルをスプレッドシートにしたんですがfilter関数前にxlwsが付いてしまい、関数が反映されま
Excel(エクセル)
-
5
Excelでセル参照したとき、書式も一緒に持ってくるには?
Windows Vista・XP
-
6
データ入力規則リスト 空白を無視
Excel(エクセル)
-
7
excelで、空白を除いてデータを抽出する方法について
Excel(エクセル)
-
8
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには
Excel(エクセル)
-
9
エクセルで空白セルを含む列の最終行の値を取得する式を教えてください
Excel(エクセル)
-
10
複雑なシフト表から1日ごとの出勤者、シフトを抜き出したいです
Excel(エクセル)
-
11
Excelで重複データの件数ではなく、何番目かを求める方法
Excel(エクセル)
-
12
リストと一致する値のセルを塗りつぶしたい。
その他(Microsoft Office)
-
13
エクセルのフィルターを複数シートに連動させたいです。 エクセルファイルに15シートあります。 そのう
Excel(エクセル)
-
14
エクセルで連続データから、数個飛ばしのデータを抜き取る方法
Excel(エクセル)
-
15
関数を使用して、エクセルの勤務表から、勤務形態別に担当者を抽出したい
Excel(エクセル)
-
16
エクセルの関数について教えてください
Excel(エクセル)
-
17
VLOOKUP関数を使用時、検索する値は昇順に並べ替える必要がありますか。
Access(アクセス)
-
18
ある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?
Excel(エクセル)
-
19
E列のセルに数値が入れば(空白でなければ)B列の同じ行のセルに色がつく
その他(Microsoft Office)
-
20
リンク先のファイルを開かなくても、値が読み込めるようにできますか?(SUMIFSを使ってます)
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】=EXACT(a1,b1) a1とb1...
-
【マクロ】excelファイルを開く...
-
エクセルのリストについて
-
【マクロ】【相談】Excelブック...
-
エクセルの関数について
-
【マクロ】数式を入力したい。...
-
【マクロ】元データと同じお客...
-
【マクロ】実行時エラー '424':...
-
他のシートの検索
-
【マクロ】左のブックと右のブ...
-
【画像あり】オートフィルター...
-
Office2021のエクセルで米国株...
-
LibreOffice Clalc(またはエク...
-
vba テキストボックスとリフト...
-
エクセルのVBAで集計をしたい
-
【マクロ】【配列】3つのシー...
-
エクセルシートの見出しの文字...
-
【マクロ】【画像あり】ファイ...
-
【マクロ】【画像あり】❶ブック...
-
【マクロ画像あり】❶1つの条件...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】元データと同じお客...
-
エクセルの関数について
-
【画像あり】オートフィルター...
-
エクセルのVBAで集計をしたい
-
エクセルのリストについて
-
【マクロ】数式を入力したい。...
-
【マクロ】【相談】Excelブック...
-
Office2021のエクセルで米国株...
-
【マクロ】実行時エラー '424':...
-
他のシートの検索
-
エクセルの複雑なシフト表から...
-
【マクロ】【配列】3つのシー...
-
vba テキストボックスとリフト...
-
【マクロ】左のブックと右のブ...
-
【マクロ】変数に入れるコード...
-
エクセルシートの見出しの文字...
-
【マクロ】別ファイルへマクロ...
-
【関数】同じ関数なのに、エラ...
-
Amazonでマイクロソフトオフィ...
-
ページが変なふうに切れる
おすすめ情報
今回は素早いご回答頂き大変ありがとうございました。非常に助かっております。
すいません。一つ条件が変わり[ab]でなく[ab]が含まれるものを抽出しなければならなくなりました。
色々調べてみて、index(find(ならいけると思ったのですが、うまくいきません。
どういった式ならこれができますでしょうか?
どうぞよろしくお願いいたします。
今回は素早い回答誠にありがとうございました。
申し訳ないのですが、条件が変わりまして「ab」でなく「ab」が含まれるものを抽出しなければならなくなりました。
この場合WEBで色々調べてみてindex(findなら大丈夫かと思ってつくってみたのですが、うまくいかず、、、。どういう式ならこれができるでしょうか?
宜しければどうぞよろしくお願いいたします。m()m