詳細設定(フィルターオプションの設定)でデータを書き出す：Excel基本講座

エクセルでデータを取出す(抽出する)機能「フィルタオプションの設定」に関する説明をしています。
フィルタオプションの設定ではエクセルの同一シート内はもとより、別シートや別ブックへの抽出が可能です。
Excel2010、Excel2013、Excel2016 をお使いの方は以下のページをご覧ください。

スポンサードリンク

2022/2/9

完全一致でデータを抽出する	複数条件で抽出する　[AND条件]
複数条件で抽出する　[OR条件]	期間でデータを書き出したいとき
条件設定に数式を使う方法
別シートへデータを書き出す	特定項目だけのデータを書き出す
VBAを使ってデータを書き出す

このページで使用するデータ表　(以下、『データ表』とします)

このページでは次のデータ表を使って説明します。
データ表作成時のポイント
- 1行に1データとなるようにする
  - データの抽出は行単位で行われるので、同じデータ群が複数行にわたっていては不都合です。
- データの先頭行に項目行(列見出し)を作成する
  - データリストの先頭行が項目行と認識されます。下図では2行目の部分。
- 列見出しは重複しない固有のものにする
  - 例えば、担当者が複数の場合、列見出しは担当者1、担当者2のように区別できるようにします。

	B	C	D	E	F	G	H
2	伝票№	日付	担当者	型番	単価	数量	売上金額
3	1001	2022/1/1	岡田	A-001B	5,000	15	75,000
4	1002	2022/1/1	上村	A-001W	5,000	22	110,000
5	1003	2022/1/1	相沢	C-105W	8,000	14	112,000
6	1004	2022/2/1	井上	B-022B	6,000	24	144,000
7	1005	2022/2/1	相沢	A-001B	5,000	15	75,000
8	1006	2022/2/1	岡田	A-001W	5,000	12	60,000
9	1007	2022/3/1	上村	B-022B	6,000	13	78,000
10	1008	2022/3/1	岡田	C-105B	8,000	12	96,000
11	1009	2022/3/1	相沢	A-001W	5,000	16	80,000
12	1010	2022/4/1	上村	B-033W	7,000	14	98,000
13	1011	2022/4/1	相沢	C-105B	8,000	24	192,000
14	1012	2022/4/1	岡田	B-033W	7,000	22	154,000
15	1013	2022/5/1	井上	A-001B	5,000	13	65,000
16	1014	2022/5/1	上村	C-105W	8,000	23	184,000
17	1015	2022/5/1	井上	B-033W	7,000	14	98,000
18	1016	2022/6/1	岡田	C-105B	8,000	18	144,000
19	1017	2022/6/1	相沢	B-022B	6,000	19	114,000

フィルタオプションの設定（詳細な条件設定での抽出）　　　　Topへ

複数の条件でデータを書き出すときは詳細設定(フィルターオプションの設定)が便利です。
抽出したデータを他のセルやシートに書き出す場合にも便利です。
(別シートへ書き出す時は、別シートがアクティブな状態で実行しなければなりません)

完全一致でデータを抽出する

Excel2002以外のバージョンでは検索値が前方一致となるので、注意してください。
結構知らない方が多いと思います。

前方一致となる例

データ表から「担当者」が『井上』のデータをM5セル以降に抽出します。
- Excel2002の場合は、M3セルに「井上」と入力しても完全一致での検索が行われます。
  Excel2007,2010,2013,2016の場合は「井上」とだけ入力すると前方一致での検索となります。
  つまり、「井上」と一緒に「井上A」も抽出されます。「A井上」は抽出されません。
下図の表は説明のため、D3セルを「井上A」、D4セルを「A井上」に変更しています。
抽出条件　M3セルに　井上　と入力すると、井上A も一緒に抽出されてしまいます。

完全一致での検索を行うための条件の書き方

データ範囲の外側に抽出条件を入力します。
M2セルに列見出し「担当者」を入力します。
M3セルには「="=井上"」と入力すると完全一致での検索条件になります。セルには　=井上　と表示されます。
または、「'=井上」　と頭に「’（シングルクォーテーション）」を付けて入力します。
[データ]タブの並べ替えとフィルターグループにある[詳細設定]を選択します。
リスト範囲にデータ表のセル範囲　B2:H19　
検索条件範囲に　M2:M3　を設定します。
指定した範囲にチェックを入れて、抽出範囲に　M5　とします。
- 入力するボックスにカーソルを表示して、シートのセル範囲をマウスでドラッグして指定すると、ダイアログでは絶対参照になります。
- M5　を指定するとき、M5セル以降のセルが空白であることが必要です。
  このセル範囲に項目名を入力して、その入力したセル範囲を指定すると、入力された項目名だけのデータを抽出することができます。特定項目の抽出をご覧ください。
[OK]ボタンをクリックします。
「担当者」が「井上」のデータが抽出されました。
伝票No. 1001の　井上A　が抽出されていません。

複数条件で抽出する　[AND条件]

AND条件で検索するには、検索条件を同一行に入力します。
担当者が「岡田」かつ　売上金額が 100,000以上のデータを抽出したい場合
下のように、M3セルには　'=岡田　、N3セルには　>100000　と検索条件を同一行に入力します。

↓
データが抽出できました。

複数条件で抽出する　[OR条件の書き方]

OR条件で検索するには検索条件を異なる行に入力します。
担当者が「岡田」　かつ　売上金額が100000以上
または
「井上」　かつ　売上金額が100000以上
のデータを抽出したい場合
- ANDとORを使って書きなおすと
  担当者が「岡田」　AND　売上金額が10万以上
  OR
  担当者が「井上」　AND　売上金額が10万以上　
  と、AND条件は同じ行に、OR条件は異なる行に入力します。
下のように、別の行に条件を並べます。
M2セルには　列見出しの　担当者、N2セルには売上金額
M3セルには　'=岡田　N3セルには　>100000　
M4セルには　'=井上　N4セルには　>100000　と入力します。

↓
データが抽出できました。

期間でデータを書き出したいとき

【問題】データ表から「日付」が2月のデータをL2セル以降に抽出しなさい。

期間の条件の書き方

2月とはデータの値が日付になっていますので、2022/2/1以上かつ　2022/3/1未満とすればよいでしょう。
この条件はANDになりますので、同じ行に条件を書きます。
同じ列見出しでの条件なので2列に同じ見出し（ここでは「日付」)を並べます。
- M2とN2セルに列見出し「日付」と入力。
  M3セルに　>=2022/2/1　N3セルに　<2022/3/1　と入力します。
- 細かいことですが、2022/2/28以下　としないのは日付のシリアル値では2022/2/28は午前0時までですので、もし2022/2/28 9:00というようなデータがあったら、これらのデータは無視されてしまうからです。
  2022/2/28と表示されていても時刻部分が無いとは限らないと考えているわけです。
↓

作業列を利用する方法

作業列を利用してもよいならば、I3セルに=MONTH(C3)　と入力して、I19セルまでコピーします。
リスト範囲に作業列を含める必要があるので、B2:I19　とします。
M2:M3セルに抽出条件を書きます。
M2セルには「作業列」、M3セルには「2」と入力します。
作業列を除いてデータを抽出したいので、M5:S5セルに抽出する列見出しを書き出しておきます。
リスト範囲は作業列を含めるので B2:H19　とします。
抽出範囲を列見出しを入力したM5:S5とするのがポイントです。

↓
指定した列見出しのデータが抽出できました。

Filter関数を利用する方法

Excel for Microsoft 365などではFilter関数が使用できるようになりました。
M3セルに　=FILTER(B3:H19,MONTH(C3:C19)=2)　と入力するだけです。
Filter関数の使い方は　FILTER関数でデータを抽出する:Excel関数　をご覧ください。

条件設定に数式を使う方法

条件を羅列するのは面倒・・・、見た目をすっきりさせたい・・・という場合は、数式で抽出条件を書けばよいでしょう。
【ポイント】条件式を入力するセルの上には空白セルが必要です。
- ただし、列見出しと同じでない文字列、例えば「条件」とか文字列なら入力してあってもよさそうです。
担当者が「岡田」かつ　売上金額が10万以上のデータを抽出したい場合の例です。
条件を数式で入力します。
データリストの一番上の行のデータに関する条件式を書きます。
- ここの例では3行目がデータリストの最上行になっていますので、数式ではD3セル、H3セルの条件を書きます。
- M3セルに　=AND(D3="岡田",H3>=100000)　とします。
  または、論理式で=(D3="岡田")*(H3>=100000)としてもよいでしょう。
  M3セルには　その条件式の結果が表示されます。ここでは　FALSE と表示されました。
検索条件範囲には上の空白セルを含めて、「M2:M3」とします。(ダイアログでは絶対参照となります。)
以下のように、条件と一致したデータが抽出できました。

フィルタオプションで別シートへの抽出/重複データの削除　　　 Topへ

フィルタオプションを使って、別シートに重複しないデータを抽出する方法です。
別のシートに抽出する場合には、抽出先をアクティブにした状態で操作します。 ←ここがポイントです。

【問題】Sheet1の住所録で重複した5，7行目を除いて、Sheet2へ抽出します。

	B	C	D
2	氏名	住所	電話番号
3	会田一郎	鹿児島市山下町	123-1234
4	井上次郎	鹿児島市易居町	223-2233
5	会田一郎	鹿児島市山下町	123-1234
6	上田浩一	鹿児島市名山町	334-3344
7	会田一郎	鹿児島市山下町	123-1234

【解答例】Sheet2への抽出結果

B C D

2 氏名住所電話番号

3 会田一郎鹿児島市山下町 123-1234

4 井上次郎鹿児島市易居町 223-2233

5 上田浩一鹿児島市名山町 334-3344
【操作手順】
1. Sheet2を表示し、A1セルを選択します。
  (ポイント：別のシートに抽出する場合には、抽出先をアクティブにした状態で操作します。）
2. [データ]タブの並べ替えとフィルターグループにある[詳細設定]を選択します。
  - (注)　この時複数のセルを選択した状態ですと警告メッセージが表示されます。
    一つのセルを選択した状態だと、警告は表示されません。
    この時は、気にせず「OK」をクリックすると先に進めます。
3. 【フィルタオプションの設定】ダイアログで設定をします。
  - リスト範囲　：　Sheet1!$B$2:$D$7
    抽出先の『指定した範囲』にチェックを入れます。
    抽出範囲　：　Sheet2!$A$1　とします。
    『重複するレコードは無視する』にチェックを入れます。
    【OK】ボタンをクリックします。
4. Sheet2に重複していないデータが抽出されました。

フィルタオプションで特定の項目(列)を抽出する　　　　 Topへ

上記ではデータ表の全項目を抽出しましたが、特定の項目(列)のデータを抽出できます。
【例】B2:D7のデータから重複しないデータの氏名と電話番号をB9:C9以降に抽出します。
【操作手順】
1. 抽出する項目(列見出し)をあらかじめ抽出先に入力しておきます。
  この例では B9セルに『氏名』、C9セルに『電話番号』と項目を入力しています。
2. [データ]タブの並べ替えとフィルターグループにある[詳細設定]を選択します。
  - リスト範囲　：　B2:D7 (項目行を含めてデータ範囲を指定します)
    『指定した範囲』にチェックを入れます。
    抽出範囲に抽出したい項目(列見出し)が入力されている『$B$9:$C$9』とします。
    『重複するレコードは無視する』にチェックを入れます。
    【OK】ボタンをクリックします。
3. B10:C12にデータが抽出されました。

フィルタオプションの設定をVBAで処理する　　 Topへ

データを追加するたびに、毎回フィルタオプションの設定を実行するのは面倒です。
VBAを作成して、ボタンをクリックするだけで実行できるようにしてみます。
なお、ここで使用しているのはExcel2002です。
くどいようですが、上記に書きましたようにこのバージョンは完全一致での検索となり、その他のバージョンでは前方一致となりますことをお断りしておきます。

VBAの作成例

データおよびコードを利用しやすいように修正しました。
- 某掲示板にてコードが動かないとの相談がありましたので、入力ミス等が起きかねないことからデータを表で書き改めました。
  さらに、コードもコピー＆貼り付けができるように画像から表にテキスト表示にしました。(2010年2月)

Sheet1の元データ例

	B	C	D	E	F	G	H
2	No	月日	項目名	収入	支出	概要	購入店名
3	1	2010年1月1日	繰越	120,000
4	2	2010年1月1日	交際費		12,000	お年玉
5	3	2010年1月2日	食費		12,000		はいから亭
6	4	2010年1月3日	教育・教育費		2,300		金海堂
7	5	2010年1月3日	食費		2,400	惣菜	タイヨー
8	6	2010年1月4日	教育・教育費		230		金海堂
9	7	2010年1月4日	食費		500		セブンイレブン
10	8	2010年1月5日	食費		625	お菓子	クッキー
11	9	2010年1月8日	教育・教育費		1,000		春苑堂
12	10	2010年1月8日	食費		1,200		はいから亭
13	11	2010年1月10日	教育・教育費		570		金海堂

Sheet2の抽出データ例

VBA(マクロ)作成手順

【操作手順】

抽出先のSheet2を選択し、抽出条件をB2に「項目名」、B3に「食費」と入力します。
Sheet2のセルを選択した状態(Sheet2がアクティブな状態)で実行します。
[開発]タブのコードグループにある[マクロの記録]を選択します。
マクロの記録ダイアログが表示されます。
[OK]ボタンをクリックします。
[データ]タブの並べ替えとフィルターグループにある[詳細設定]を選択します。
フィルターオプションの設定でリスト範囲に　Sheet1!B2:H13　
検索条件に　Sheet2!B2:B3　を指定します。
抽出先「指定した範囲」にチェックを入れ、抽出範囲にSheet2!B5　と設定します。
[OK]ボタンをクリックすると、Sheet2にデータが抽出されます。
自動記録を終了するため、[開発]タブの[記録終了]ボタンをクリックします。
[開発]タブの[Visual Basic ]を選択し、VBEのウィンドウを開きます。
ショートカットキーは[Alt]+[F11]です。

「標準モジュール」の「Module1」をクリックすると、右側に記録作成されたコードが表示されます。

以下のようなコードが記録されます。(不要な部分は削除しています)

Sub Macro1()
'
' Macro1 Macro

'
　　Sheets("Sheet1").Range("B2:H13").AdvancedFilter Action:=xlFilterCopy, _
　　　　CriteriaRange:=Range("B2:B3"), CopyToRange:=Range("B5"), Unique:=False
End Sub

このままのコードでは、抽出先Sheet2のB5以下の行を抽出のたびにクリアしないとうまく動きません。
また、データが増減した場合にも対応できないかもしれませんので、コードをちょっと変更します。

Sub Macro1()
　　Dim myRow1 As Long, myRow2 As Long
　　　　'----Sheet1とSheet2のB列で最終行を捜します。
　　　　myRow1 = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
　　　　myRow2 = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
　　　　　　'----Sheet2のB5以下が入力されていたらクリアします。
　　　　　　If myRow2 >= 5 Then
　　　　　　　　Sheets("Sheet2").Range("B5:H" & myRow2).ClearContents
　　　　　　End If
　　　　　　'----フィルタオプションの設定で抽出します。
　　　　　　'----元データはSheet1、抽出条件はSheet2のB2:B3、抽出先はSheet2のB5です。
　　　　　　Sheets("Sheet1").Range("B2:H" & myRow1).AdvancedFilter _
　　　　　　　　　　Action:=xlFilterCopy, _
　　　　　　　　　　CriteriaRange:=Sheets("Sheet2").Range("B2:B3"), _
　　　　　　　　　　CopyToRange:=Sheets("Sheet2").Range("B5"), _
　　　　　　　　　　Unique:=False
End Sub

Excel2007で最大行が増えましたので、最終行を捜す部分を
myRow1 = Sheets("Sheet1").Range("B65536").End(xlUp).Row
↓
myRow1 = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
と修正しました。

[開発]タブのコントロールグループの[挿入]からフォームコントロールの[ボタン]をクリックし、Sheet2にボタンを作成します。
Sheet2のシートをクリックします。
「マクロの登録」ダイアログが開くので、先ほど作成したマクロ「Macro1」を選択し、[OK]ボタンをクリックします。
Sheet2の[ボタン1]にマクロが登録され、ボタンをクリックするとマクロが実行されます。
ボタンを右クリックすると、ボタン名を変更できますので、「データ抽出」としてみました。
B3セルに「教育・教養費」と入力し、「データ抽出」ボタンをクリックするとデータが抽出されました。

データ抽出の条件設定がバージョンによって異なる

Excel2002 が異なる挙動をしますので注意が必要です。
【相違点】　検索条件に文字列だけを入力した場合に検索結果に相違を生じます。
- Excel97,Excel2000,Excel2003そして、Excel2007,Excel2013,2016などは前方一致で検索されます。
- Excel2002では完全一致で検索されます。
【例】　検索条件(Criteria)として「岡」と入力すると
- Excel97,2000,2003そして、Excel2007では、"岡本"、"岡田"、"岡崎" など「岡」で始まる文字列が検索されます。
- Excel2002では、"岡"だけが検索されます。
【対処法】　次に示す数式やワイルドカード文字を利用することで、バージョンに関係なく、同じ結果が得られるようになります。
- 指定した文字列に完全に一致するアイテムだけを検索するには？
  - ="=文字列" :[文字列]に検索する文字列を入力します。
    (例)　="=岡"　　←岡と完全一致するものを検索する
- 指定した文字列で始まるアイテムを検索するには？
  - (例)　岡*　のようにワイルドカード文字の*(アスタリスク)を使います。　　←岡で始まるものを検索する。
【条件の書き方】
- 複数条件の合計の関数(DSUM関数) などデータベース関数の検索条件の設定方法と同じです。
- ワイルドカードを使用できます。
【参考資料】
- [XL2002]複雑な検索条件を使用してリストデータを抽出する方法
  http://support.microsoft.com/kb/402757/ja

スポンサードリンク

よねさんのWordとExcelの小部屋｜Excel(エクセル)講座の総目次｜Excel(エクセル)基本講座の目次｜フィルタオプションの設定・詳細設定(データ抽出)

PageViewCounter
Counter
Since2006/2/27