1. Home »
2. Excel講座の総目次 »
3. Excel基本講座の目次 »
4. 詳細設定(フィルター オプションの設定)でデータを書き出す

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

• このページでは次のデータ表を使って説明します。
• データ表作成時のポイント
  • 1行に1データとなるようにする
    • データの抽出は行単位で行われるので、同じデータ群が複数行にわたっていては不都合です。
  • データの先頭行に項目行(列見出し)を作成する
    • データリストの先頭行が項目行と認識されます。下図では2行目の部分。
  • 列見出しは重複しない固有のものにする
    • 例えば、担当者が複数の場合、列見出しは担当者1、担当者2のように区別できるようにします。
• B C D E F G H
  2 伝票?? 日付 担当者 型番 単価 数量 売上金額
  3 1001 2024年1月1日 岡田 A-001B 5,000 15 75,000
  4 1002 2024年1月1日 上村 A-001W 5,000 22 110,000
  5 1003 2024年1月1日 相沢 C-105W 8,000 14 112,000
  6 1004 2024年2月1日 井上 B-022B 6,000 24 144,000
  7 1005 2024年2月1日 相沢 A-001B 5,000 15 75,000
  8 1006 2024年2月1日 岡田 A-001W 5,000 12 60,000
  9 1007 2024年3月1日 上村 B-022B 6,000 13 78,000
  10 1008 2024年3月1日 岡田 C-105B 8,000 12 96,000
  11 1009 2024年3月1日 相沢 A-001W 5,000 16 80,000
  12 1010 2024年4月1日 上村 B-033W 7,000 14 98,000
  13 1011 2024年4月1日 相沢 C-105B 8,000 24 192,000
  14 1012 2024年4月1日 岡田 B-033W 7,000 22 154,000
  15 1013 2024年5月1日 井上 A-001B 5,000 13 65,000
  16 1014 2024年5月1日 上村 C-105W 8,000 23 184,000
  17 1015 2024年5月1日 井上 B-033W 7,000 14 98,000
  18 1016 2024年6月1日 岡田 C-105B 8,000 18 144,000
  19 1017 2024年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 も一緒に抽出されてしまいます。
  

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

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

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

• AND条件で検索するには、検索条件を同一行に入力します。
• 担当者が「岡田」かつ 売上金額が 10,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月とはデータの値が日付になっていますので、2024年2月1日以上 かつ 2024年3月1日未満とすればよいでしょう。
• この条件はANDになりますので、同じ行に条件を書きます。
  同じ列見出しでの条件なので2列に同じ見出し(ここでは「日付」)を並べます。
  • M2とN2セルに列見出し「日付」と入力。
    M3セルに >=2024年2月1日 N3セルに <2024年3月1日 と入力します。
  • 細かいことですが、2022年2月28日以下 としないのは日付のシリアル値では2022年2月28日は午前0時までですので、もし2022年2月28日 9:00というようなデータがあったら、これらのデータは無視されてしまうからです。
    2022年2月28日と表示されていても時刻部分が無いとは限らないと考えているわけです。
• データが抽出できました。
  

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

• 条件を羅列するのは面倒・・・、見た目をすっきりさせたい・・・という場合は、数式で抽出条件を書けばよいでしょう。
• 【ポイント】条件式を入力するセルの上には空白セルが必要です。
  • ただし、列見出しと同じでない文字列、例えば「条件」とか文字列なら入力してあってもよさそうです。
• 担当者が「岡田」かつ 売上金額が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」をクリックします。
       セルを1個だけ選択してから操作をやり直します。
       
     • または、以下のような警告が表示されることがあります。
       この場合は[OK]ボタンをクリックして先に進みます。
       
  3. 【フィルタオプションの設定】ダイアログで設定をします。
     • リスト範囲 : Sheet1!$B2ドル:$D7ドル
       抽出先の「指定した範囲」にチェックを入れます。
       抽出範囲 : Sheet2!$B2ドル とします。
       「重複するレコードは無視する」にチェックを入れます。
       [OK]ボタンをクリックします。
       
  4. Sheet2に重複していないデータが抽出されました。
     

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

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

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

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

VBAの作成例

• データおよびコードを利用しやすいように修正しました。
  • 某掲示板にてコードが動かないとの相談がありましたので、入力ミス等が起きかねないことからデータを表で書き改めました。
    さらに、コードもコピー&貼り付けができるように画像から表にテキスト表示にしました。(2010年2月)
• Sheet1の元データ例
  
  B C D E F G H
  2 No 月日 項目名 収入 支出 概要 購入店名
  3 1 2024年1月1日 繰越 120,000
  4 2 2024年1月2日 交際費 12,000 お年玉
  5 3 2024年1月3日 食費 12,000 はいから亭
  6 4 2024年1月4日 教育・教育費 2,300 金海堂
  7 5 2024年1月5日 食費 2,400 惣菜 タイヨー
  8 6 2024年1月6日 教育・教育費 230 金海堂
  9 7 2024年1月7日 食費 500 セブンイレブン
  10 8 2024年1月8日 食費 625 お菓子 クッキー
  11 9 2024年1月9日 教育・教育費 1,000 春苑堂
  12 10 2024年1月10日 食費 1,200 はいから亭
  13 11 2024年1月11日 教育・教育費 570 金海堂
• Sheet2の抽出データ例
  

VBA(マクロ)作成手順

1. 抽出先のSheet2を選択し、抽出条件をB2に「項目名」、B3に「食費」と入力します。
   Sheet2のセルを選択した状態(Sheet2がアクティブな状態)で実行します。
2. [開発]タブのコード グループにある[マクロの記録]を選択します。
   
3. マクロの記録ダイアログが表示されます。
   [OK]ボタンをクリックします。
   
4. [データ]タブの並べ替えとフィルター グループにある[詳細設定]を選択します。
   
5. フィルターオプションの設定でリスト範囲に Sheet1!B2:H13
   検索条件に Sheet2!B2:B3 を指定します。
   抽出先「指定した範囲」にチェックを入れ、抽出範囲にSheet2!B5 と設定します。
   [OK]ボタンをクリックすると、Sheet2にデータが抽出されます。
   
6. 自動記録を終了するため、[開発]タブの[記録終了]ボタンをクリックします。
   
7. ちなみに、抽出データは下図のようになります。
   
8. [開発]タブの[Visual Basic ]を選択し、VBEのウィンドウを開きます。
   ショートカットキーは[Alt]+[F11]です。
   
9. 「標準モジュール」の「Module1」をクリックすると、右側に記録作成されたコードが表示されます。
   
   以下のようなコードが記録されます。(不要な部分は削除しています)
   
   Sub Macro1()
   '
   ' Macro1 Macro
   
   '
   Sheets("Sheet1").Range("B2:H13").AdvancedFilter Action:=xlFilterCopy, _
   CriteriaRange:=Range("B2:B3"), CopyToRange:=Range("B5"), Unique:=False
   End Sub
10. このままのコードでは、抽出先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
      と修正しました。
11. Sheet2を選択します。
    [開発]タブのコントロール グループの[挿入]からフォームコントロールの[ボタン]をクリックし、Sheet2にボタンを作成します。
    
12. 「マクロの登録」ダイアログが開くので、先ほど作成したマクロ「Macro1」を選択し、[OK]ボタンをクリックします。
    
13. Sheet2の[ボタン1]にマクロが登録され、ボタンをクリックするとマクロが実行されます。
    
14. ボタンを右クリックすると、ボタン名を変更できますので、「データ抽出」としてみました。
    B3セルに「教育・教養費」と入力し、「データ抽出」ボタンをクリックするとデータが抽出されました。
    

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

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

スポンサードリンク
(追記) (追記ここまで)

PageViewCounter
Counter
Since2006/2/27