Chiquilin Site■06.03.22_Excel：抽出データを詰めて表示する（条件抽出）
あまりにも内容が古くなり過ぎたので作り直しました。
数式での方法は
下にまとめています。
★その１：フィルタの詳細設定を使う方法
毎回の更新作業が面倒かもしれませんが フィルタの詳細設定（Excel2003以前はフィルタオプションの設定）を使うのが簡単です。
D2セルに検査項目名「分類」 D3セルに検査値「Ａ」を入力※
◆Excel2007以降
[データ]→[フィルタ]の[詳細設定]
◆Excel2003以前
[データ]→[フィルタ]→[フィルタオプションの設定]
※ Excel2002以外は 前方一致検索になります。完全一致にする場合は
「'=A」と入力して下さい
その際、下の画像のような エラーが表示されることがあります。
……が気にしないで下さい。[OK]を押せばいいです。
[指定した範囲]を選択して
・リスト範囲：B2:C12
・検索条件範囲：D2:D3
・抽出範囲：E2:F2
これで抽出することができます。別シートに抽出する際は 別シートから フィルタの詳細設定を指定して下さい。その際 毎回 範囲を取り直しする必要があります。
★その２：PowerQuery（クエリ）を使う方法
フィルタで抽出する方法はお手軽ですが 毎回操作する手間が発生します。数式のようにキーワードを入力したら即座に抽出とはいきません。
設定の手間を省く方法として PowerQueryを使った方法（パラメータクエリ）があります。セルの値をパラメータにしてクエリで抽出します。
セル D2に検査項目名「分類」 D3セルに検査値「A」を入力
セル D3を選択した状態で 名前ボックスに「分類」と入力し
Enterで確定（セル D3を「分類」で名前定義）
キーワードの入力欄はこれで準備完了です。
B:C列のテーブル（ここでは「テーブル2」）を選択し
[データ]→[テーブルまたは範囲から]
→PowerQueryエディタが開きます。
クエリ作成の手順は
・定義付けしたセルの名前を呼び出す
・フィルタで適当なキーワードで抽出
・キーワード部分をパラメータ値に変更
→[閉じて読み込む]
となります。
今回は詳細エディタのコードを直接書き換えます。
let
分類 = Excel.CurrentWorkbook(){[Name="分類"]}[Content]{0}[Column1],
ソース = Excel.CurrentWorkbook(){[Name="テーブル2"]}[Content],
変更された型 =
Table.TransformColumnTypes(
ソース,{{"商品名", type text}, {"分類", type text}}
),
フィルターされた行 =
Table.SelectRows(
変更された型, each ([分類] = 分類)
)
in
フィルターされた行
テーブル名を「テーブル2」としていますが 実際のテーブル名に変更してください。
尚 パラメータクエリを使う方法にはもうひとつ MicrosoftQueryを使う方法があります。古い方法なので今はほとんど使われなくなっていますが「セルに入力すると同時に抽出」がやりたい場合はこっちの方が便利かもしれません。
→条件を指定して抽出する３
★その３：数式（作業列を使う方法）
まず先に。Microsoft365や Excel2021では FILTER関数が使えるので この手の処理が数式で困ることはほぼなくなりました。
■D3セルに「A」と入力
■E3セル
=FILTER(B3:C12,C3:C12=D3)
これでおしまいです。先頭のセルに数式を入れればスピルで後は勝手に表示されます。以下は古いバージョンの Excelでどうしても数式でやりたい場合の方法として残しておきます。
まずは「その1」
■D3セル
=IF(C3="A",ROW(),"")
フィルハンドルをダブルクリック
■E3セル
=IF(COUNT($D:$D)<ROW(A1),"",INDEX(B:B,SMALL($D:$D,ROW(A1))))
Ｆ列にマウスの右ボタンでフィルドラッグ[書式なしコピー(フィル)]
必要行まで下方向にオートフィルコピー
この方法の良いところはフィルタ機能を使うのと違って 更新作業が楽なことくらいです。決して処理は速くありません。ただ列単位で指定できますのでお手軽ではあります。
続いて「その2」。元データを昇順に並べ替えしていいなら 下のような処理をするのがお勧めです。処理はとても速いです(2007.03.01 追加)
C列の分類を基準にして 昇順に並べ替え
D2セルに条件となる「A」を記入
■D3セル
=IF(D4,MATCH(D2,C3:C12,0),"")
■D4セル
=COUNTIF(C3:C12,D2)
■E3セル
=IF($D$4<ROW(A1),"",INDEX(B:B,$D$3+ROW(A3)-1))
F列にマウスの右ボタンでフィルドラッグ[書式なしコピー(フィル)]
必要行まで下方向にオートフィルコピー
最後に「その3」。どうしてもソート(並べ替え)できない場合は 面倒でも下記の方法がいいと思います(07.03.08 追加)
E1セルに条件となる「A」を記入
■D2セル
=D3+(C3=E$1)
フィルハンドルをダブルクリック
■E3セル：(10.08.29 変更)
=IF($D$2<ROW(A1),"",INDEX(B:B,MATCH($D$2-ROW(A1),$D$2:$D$12,-1)+1))
■F3セル
=IF(E3="","",E$1&"")
E3:F3セルを選択し マウスの右ボタンでフィルドラッグ
必要行まで下方向にオートフィルコピーし[書式なしコピー(フィル)]
E列の式の MATCH関数については
重くなりますので列参照にしないようにしましょう。少し多めの範囲を取っておくかテーブル設定を入れて構造化参照にした方がいいと思います。作業列を使う方法の中では（というか数式だけで対処する方法の中では） 最も負担が少ない方法です。
★その４：数式（その他）
作業列を使わなくても やりようによってはできます。バージョンによってお勧めの方法が異なるので分けて紹介します。
★Excel for365・Excel2021の場合
■E3セル
=IFERROR(FILTER(B$3:C$12,$C$3:$C$12=$D$3),"")
★Excel2010以降の場合
■E3セル
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($C$3:$C$12)
/($C$3:$C$12=$D$3),ROW()-2)),"")
必要行まで下・右方向にコピー
★Excel2007の場合
■E3セル
=IFERROR(INDEX(B:B,
SMALL(IF($C$3:$C$12=$D$3,ROW($3:$12)),ROW()-2)),"")
数式を Ctrl + Shift + Enter で確定
必要行まで下・右方向にコピー
★Excel2003以前の場合
=INDEX(B:B,13-LARGE(($C$3:$C$12="A")*13
-ROW($3:$12),ROW(A1)))&""
数式を Ctrl + Shift + Enter で確定
必要行まで下・右方向にコピー
Excel2007以降なら IFERROR関数を使って
■E2セル
=IFERROR(INDEX(B:B,1/LARGE(INDEX(($C$3:$C$12="A")
/ROW($3:$12),0),ROW(A1))),"")
普通に Enter で確定
のようにしてもいいかと思います。
