- エクセルでデータを取出す(抽出する)機能「フィルタオプションの設定」に関する説明をしています。
- フィルタオプションの設定ではエクセルの同一シート内はもとより、別シートや別ブックへの抽出が可能です。
- フィルタオプションの設定ではオートフィルタでできない詳細な検索が可能になります。
次のデータ表を使って説明します。
- データ表作成時のポイント
- 1行に1データとなるようにする
- データの抽出は行単位で行われるので、同じデータ群が複数行にわたっていては不都合です。
- データの先頭行に項目行(列見出し)を作成する
- データリストの先頭行が項目行と認識されます。下図では2行目の部分。
- 列見出しは重複しない固有のものにする
- 例えば、担当者が複数の場合、列見出しは担当者1、担当者2のように区別できるようにします。
- 1行に1データとなるようにする
| B | C | D | E | F | G | H | |
| 2 | 伝票№ | 日付 | 担当者 | 型番 | 単価 | 数量 | 売上金額 |
| 3 | 1001 | 2006/1/1 | 岡田 | A-001B | 5,000 | 15 | 75,000 |
| 4 | 1002 | 2006/1/1 | 上村 | A-001W | 5,000 | 22 | 110,000 |
| 5 | 1003 | 2006/1/1 | 相沢 | C-105W | 8,000 | 14 | 112,000 |
| 6 | 1004 | 2006/2/1 | 井上 | B-022B | 6,000 | 24 | 144,000 |
| 7 | 1005 | 2006/2/1 | 相沢 | A-001B | 5,000 | 15 | 75,000 |
| 8 | 1006 | 2006/2/1 | 岡田 | A-001W | 5,000 | 12 | 60,000 |
| 9 | 1007 | 2006/3/1 | 上村 | B-022B | 6,000 | 13 | 78,000 |
| 10 | 1008 | 2006/3/1 | 岡田 | C-105B | 8,000 | 12 | 96,000 |
| 11 | 1009 | 2006/3/1 | 相沢 | A-001W | 5,000 | 16 | 80,000 |
| 12 | 1010 | 2006/4/1 | 上村 | B-033W | 7,000 | 14 | 98,000 |
| 13 | 1011 | 2006/4/1 | 相沢 | C-105B | 8,000 | 24 | 192,000 |
| 14 | 1012 | 2006/4/1 | 岡田 | B-033W | 7,000 | 22 | 154,000 |
| 15 | 1013 | 2006/5/1 | 井上 | A-001B | 5,000 | 13 | 65,000 |
| 16 | 1014 | 2006/5/1 | 上村 | C-105W | 8,000 | 23 | 184,000 |
| 17 | 1015 | 2006/5/1 | 井上 | B-033W | 7,000 | 14 | 98,000 |
| 18 | 1016 | 2006/6/1 | 岡田 | C-105B | 8,000 | 18 | 144,000 |
| 19 | 1017 | 2006/6/1 | 相沢 | B-022B | 6,000 | 19 | 114,000 |
フィルタオプションの設定（詳細な条件設定での抽出） Topへ
- オートフィルタの条件設定では2条件までです。それ以上の条件のときはフィルタオプションの設定が使えます。
- 抽出したデータを他のセルやシートに書き出す場合にも便利です。
(別シートへ書き出す時は、別シートがアクティブな状態で実行しなければなりません)
一つの条件で抽出する
- 【問題】データ表から「担当者」が『井上』のデータをL2セル以降に抽出しなさい。
- 【解答例】
複数条件で抽出する [AND条件，OR条件の書き方]
- 検索条件は同一行はAND条件での検索になります。
- 担当者が「岡田」かつ 売上金額が10万以上のデータを抽出したい場合
- 下のように、同一行に条件を並べます。
- 異なる行とはOR条件での検索になります。
- 担当者が「岡田」 かつ 売上金額が10万以上
または 「井上」 かつ 売上金額が10万以上 のデータを抽出したい場合- ANDとORを使って書きなおすと
担当者が「岡田」 AND 売上金額が10万以上
OR
担当者が「井上」 AND 売上金額が10万以上
- ANDとORを使って書きなおすと
- 下のように、別の行に条件を並べます。
- 担当者が「岡田」 かつ 売上金額が10万以上
期間の条件の書き方
- 【問題】データ表から「日付」が2月のデータをL2セル以降に抽出しなさい。
- 【解答例】
- 2月とはデータの値が日付になっていますので、2006/2/1以上 かつ 2006/3/1未満とすればよいでしょう。
- この条件はANDになりますので、同じ行に条件を書きます。
同じ列見出しでの条件なので2列に同じ見出し（ここでは「日付」)を並べます。- J2:K2セルに列見出し「日付」と入力。
J3セルに「>=2006/2/1」 K3セルに「<2006/3/1」と入力します。
- J2:K2セルに列見出し「日付」と入力。
- 細かいことですが、2006/2/28以下としないのは日付のシリアル値では2006/2/28は午前0時までですので、
もし2006/2/28 9:00というようなデータがあったら、これらのデータは無視されてしまうからです。
2006/2/28と表示されていても時刻部分が無いとは限らないと考えているわけです。
- 【解答例2】作業列を利用する
条件設定に数式を使う方法
- 条件を羅列するのは面倒・・・、見た目をすっきりさせたい・・・という場合は、数式で抽出条件を書けばよいでしょう。
- 【ポイント】条件式を入力するセルの上には空白セルが必要です。
- ただし、列見出しと同じでない文字列、例えば「条件」とか文字列なら入力してあってもよさそうです。
- 担当者が「岡田」かつ 売上金額が10万以上のデータを抽出したい場合の例です。
- 【例】
フィルタオプションで別シートへの抽出/重複データの削除 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 - 【操作手順】
- Sheet2を表示し、A1セルを選択します。
(ポイント：別のシートに抽出する場合には、抽出先をアクティブにした状態で操作します。） - 【データ】→【フィルタ】→【フィルタオプションの設定】を選択します。
- (注1) この時複数のセルを選択した状態ですと警告メッセージが表示されます。
一つのセルを選択した状態だと、警告は表示されません。
- (注2) また下記の「列見出しを含むリスト・・・」の警告が表示されることもあります。
この時は、気にせず「OK」をクリックすると先に進めます。
- (注1) この時複数のセルを選択した状態ですと警告メッセージが表示されます。
- 【フィルタオプションの設定】ダイアログで設定をします。
- リスト範囲 ： Sheet1!$B$2:$D$7
抽出先の『指定した範囲』にチェックを入れます。
抽出範囲 ： Sheet2!$A$1 とします。
『重複するレコードは無視する』にチェックを入れます。 - 【OK】ボタンをクリックします。
- リスト範囲 ： Sheet1!$B$2:$D$7
- Sheet2に重複していないデータが抽出されました。
B C D 2 氏名 住所 電話番号 3 会田一郎 鹿児島市山下町 123-1234 4 井上次郎 鹿児島市易居町 223-2233 5 上田浩一 鹿児島市名山町 334-3344
- Sheet2を表示し、A1セルを選択します。
- 【操作手順】
フィルタオプションで特定の項目(列)を抽出する Topへ
- 上記ではデータ表の全項目を抽出しましたが、特定の項目(列)のデータを抽出できます。
- 【例】B2:D7のデータから重複しないデータの氏名と電話番号をB9:C9以降に抽出します。
- 【操作手順】
- 抽出する項目(列見出し)をあらかじめ抽出先に入力しておきます。
この例では B9セルに『氏名』、C9セルに『電話番号』と項目を入力しています。 - 【データ】→【フィルタ】→【フィルタオプションの設定】を選択します。
- リスト範囲 ： B2:D7 (項目行を含めてデータ範囲を指定します)
『指定した範囲』にチェックを入れます。
抽出範囲に抽出したい項目が入力されている『$B$9:$C$9』とします。
『重複するレコードは無視する』にチェックを入れます。 - 【OK】ボタンをクリックします。
- リスト範囲 ： B2:D7 (項目行を含めてデータ範囲を指定します)
- B10:C12にデータが抽出されました。
B C D 2 氏名 住所 電話番号 3 会田一郎 鹿児島市山下町 123-1234 4 井上次郎 鹿児島市易居町 223-2233 5 会田一郎 鹿児島市山下町 123-1234 6 上田浩一 鹿児島市名山町 334-3344 7 会田一郎 鹿児島市山下町 123-1234 8 9 氏名 電話番号 10 会田一郎 123-1234 11 井上次郎 223-2233 12 上田浩一 334-3344
- 抽出する項目(列見出し)をあらかじめ抽出先に入力しておきます。
フィルタオプションの設定をVBAで処理する Topへ
- データを追加するたびに、毎回フィルタ オプションの設定を実行するのは面倒です。
- VBAを作成して、ボタンをクリックするだけで実行できるようにしてみます。
- なお、ここで使用しているのはExcel2002です。
くどいようですが、上記に書きましたようにこのバージョンは完全一致での検索となり、その他のバージョンでは前方一致となりますことをお断りしておきます。
VBAの作成例
- データおよびコードを利用しやすいように修正しました。
- 某掲示板にてコードが動かないとの相談がありましたので、入力ミス等が起きかねないことからデータを表で書き改めました。
さらに、コードもコピー＆貼り付けができるように画像から表での表記にしました。(2010年2月)
- 某掲示板にてコードが動かないとの相談がありましたので、入力ミス等が起きかねないことからデータを表で書き改めました。
- Sheet1の元データ例
<colgroup> <col width="40" /> <col width="50" /> <col width="120" /> <col width="100" /> <col span="3" width="80" /> <col width="120" /></colgroup>
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に「食費」と入力します。
- [ツール]→[マクロ]→[新しいマクロの記録]を選択し、「マクロの記録で[OK]ボタンをクリックします。
- Sheet2のセルを選択した状態(Sheet2がアクティブな状態)で実行します。
- (注1) この時複数のセルを選択した状態ですと警告メッセージが表示されます。
一つのセルを選択した状態だと、警告は表示されません。 - (注2) また下記の「列見出しを含むリスト・・・」の警告が表示されることもあります。
気にせず「OK」をクリックすると先に進めます。
- [データ]→[フィルタ]→[フィルタオプションの設定]を選択し、設定を入力します。
[OK]ボタンをクリックすると、Sheet2にデータが抽出されます。
- 自動記録を終了するため、[記録終了]ボタンをクリックします。
または、メニューバーの[ツール]→[マクロ]→[記録終了]を実行します。
- [ツール]→[マクロ]→[Visual Basic Editor]を選択し、VBEのウィンドウを開きます。
「標準モジュール」の「Module1」をダブルクリックすると、右側に記録作成されたコードが表示されます。
以下のようなコードが記録されます。
<colgroup> <col width="700" /></colgroup>
Sub Macro2()
'
' Macro1 Macro
' マクロ記録日 : 2010/2/13 ユーザー名 : owner
'
Sheets("Sheet1").Range("B2:H13").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B2:B3"), CopyToRange:=Range("B5"), Unique:=False
End Sub - このままのコードでは、抽出先Sheet2のB5以下の行をクリアしないとうまく動きません。
また、データが増減した場合にも対応できないかもしれませんので、コードをちょっと変更します。
<colgroup> <col width="700" /></colgroup>
Sub Macro2()
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
と修正しました。
- Excel2007で最大行が増えましたので、最終行を捜す部分を
- [表示]→[ツールバー]→[フォーム]を選択し、フォームツールバーを表示します。
[ボタン]をクリックし、Sheet2にボタンを作成します。
- 「マクロの登録」ダイアログが開くので、先ほど作成したマクロ「Macro2」を選択し、[OK]ボタンをクリックします。
- Sheet2の[ボタン1]にマクロが登録され、ボタンをクリックするとマクロが実行されます。
- ボタンを右クリックすると、ボタン名を変更できますので、「データ抽出」としてみました。
B3セルに「教育・教養費」と入力し、「データ抽出」ボタンをクリックするとデータが抽出されました。
