-
よねさんのExcelとWordの使い方 »
-
エクセル2013基本講座：目次 »
-
フィルター(データ抽出) »
-
詳細設定を使ってデータを抽出する
詳細設定のメリット Topへ
- Excel2007以降で「詳細設定」と名前の付いたコマンドボタンができました。Excel2003以前での「フィルターオプションの設定」という機能と同じものです。
- 詳細設定ではオートフィルターではできない、複雑な条件でのデータ抽出が可能になります。
例えば、オートフィルターでは異なるフィールド間の条件はAND条件での抽出はできますが、OR条件の抽出はできません。
- 詳細設定ではデータリストとは別のシートへ抽出データを送ることができます。
オートフィルターでの抽出はデータリスト内での表示/非表示になるため、コピー＆貼り付けの操作が必要となります。
- 詳細設定では抽出条件を数式で指定することができる。(抽出条件を書き出すセルを少なくすることが可能。)
詳細設定のデメリット Topへ
- 抽出条件を書き出す必要がある。抽出条件の書き方が分かりにくい。
- 別のシートでデータを書き出すときは、書き出すシートから操作を行う必要があるなど、操作が分かりにくい。
- お試しいただくために、このページで使用したデータリストを表にしておきます。
コピーして利用してください。
- データリスト
| 伝票№ |
日付 |
担当者 |
型番 |
単価 |
数量 |
金額 |
消費税 |
合計金額 |
| 1001 |
2013/5/1 |
岡田 |
A-001B |
5,000 |
15 |
75,000 |
3,750 |
78,750 |
| 1002 |
2013/5/1 |
上村 |
A-001W |
5,000 |
22 |
110,000 |
5,500 |
115,500 |
| 1003 |
2013/5/1 |
相沢 |
C-105W |
8,000 |
14 |
112,000 |
5,600 |
117,600 |
| 1004 |
2013/5/2 |
井上 |
B-022B |
6,000 |
24 |
144,000 |
7,200 |
151,200 |
| 1005 |
2013/5/2 |
相沢 |
A-001B |
5,000 |
15 |
75,000 |
3,750 |
78,750 |
| 1006 |
2013/5/2 |
岡田 |
A-001W |
5,000 |
12 |
60,000 |
3,000 |
63,000 |
| 1007 |
2013/5/2 |
上村 |
B-022B |
6,000 |
13 |
78,000 |
3,900 |
81,900 |
| 1008 |
2013/5/3 |
山岡田 |
C-105B |
8,000 |
12 |
96,000 |
4,800 |
100,800 |
| 1009 |
2013/5/3 |
相沢 |
A-001W |
5,000 |
16 |
80,000 |
4,000 |
84,000 |
| 1010 |
2013/5/3 |
上村 |
B-033W |
7,000 |
14 |
98,000 |
4,900 |
102,900 |
| 1011 |
2013/5/4 |
相沢 |
C-105B |
8,000 |
24 |
192,000 |
9,600 |
201,600 |
| 1012 |
2013/5/4 |
岡 |
B-033W |
7,000 |
22 |
154,000 |
7,700 |
161,700 |
| 1013 |
2013/5/5 |
井上 |
A-001B |
5,000 |
13 |
65,000 |
3,250 |
68,250 |
| 1014 |
2013/5/5 |
上村 |
C-105W |
8,000 |
23 |
184,000 |
9,200 |
193,200 |
| 1015 |
2013/5/5 |
井上 |
B-033W |
7,000 |
14 |
98,000 |
4,900 |
102,900 |
| 1016 |
2013/5/6 |
岡田山 |
C-105B |
8,000 |
18 |
144,000 |
7,200 |
151,200 |
| 1017 |
2013/5/6 |
相沢 |
B-022B |
6,000 |
19 |
114,000 |
5,700 |
119,700 |
詳細設定の抽出条件の書き方 Topへ
-
- 文字列の検索を行うと、部分一致で検索されます(Excel2002だけは完全一致で検索されます)
- 検索条件(Criteria)として文字列を入力すると、その文字列ではじまるアイテムがすべて検索されます。
- たとえば、検索条件として「岡」と入力すると、"岡本"、"岡田"、"岡崎" など「岡」で始まるものが検索されます。
- 指定した文字列に完全に一致するアイテムだけを検索するには、次に示す数式を入力します。
-
空白セル/空白でないセルの抽出条件の書き方
- 空白セルを抽出したいときは ="=" または '= とします。
- 空白でないセルの時は ="<>" または '<> とします。
-
- N4セルにフィールド名(列見出し)を入力します。下図では「担当者」と入力しています。
(入力された値が条件に使用されます、書式は関係ありません。見やすくするために色を付けているだけです。)
N5セルに抽出したいデータを入力します。下図では「岡田」と入力しました。
- データリスト内のセルを選択して、[データ]タブの[詳細設定]を実行します。
- フィルターオプションの設定 ダイアログが表示されます。
(下図では、セル範囲をシート上でドラッグして範囲指定していますので、絶対参照で入力されています。$マークなしの相対参照でも問題はありません。)
- 指定した範囲にチェックを入れます。
- リスト範囲にデータリストの範囲 C4:K21 を指定します。
- 検索条件範囲に N4:N5 を指定します。
- 抽出範囲にデータの抽出先の先頭のセル N8 を指定します。
- [OK]ボタンをクリックします。
- データが抽出されました。
- 抽出されたデータに「岡田山」というデータが含まれています。
検索条件に入力した「岡田」で始まるデータが抽出されているのか確認できます。
-
- 検索条件と完全一致したデータを抽出するにはどうすればよいのでしょう?
- 検索条件に '=岡田 と入力します(先頭にアポストロフィを入力しています)。
または、 ="=岡田" と入力します。
- データリスト内のセルを選択して、[データ]タブの[詳細設定]を実行します。
ダイアログボックス内の設定は上記と同じです。
- 「担当者」が「岡田」と完全に一致するデータのみが抽出されました。
-
- 検索条件が複数ある時、AND条件で検索したいときは同じ行に条件を書きます。
- N4セルに「担当者」、O4セルに「金額」とフィールド名を入力しています。
N5セルに「'=上村」、O5セルに「>=100000」と入力しています。
担当者が上村 かつ 金額が10万以上 の条件（AND条件）としています。
- 設定画面は省略して、結果を示します。
AND条件でデータが抽出されました。
- 同じ列で複数の条件を書くときは、フィールド名も複数書く必要があります。
- 金額が10万以上、15万以下とする場合、下図のようにN列とO列とに条件を書き出す必要があります。
同じフィールド名を複数書くのがポイントになります。
- 下図のように条件を設定して、詳細設定を実行します。
- 条件と一致したデータが抽出できました。
-
- 検索条件が複数ある時、OR条件で検索したいときは同じ列に条件を書きます。
- N4セルに「担当者」とフィールド名を入力しています。
N5セルに「'=上村」、N6セルに「'=岡田」と入力しています。
担当者が上村 または 岡田 の条件（OR条件）としています。
- 設定画面は省略して、結果を示します。
OR条件でデータが抽出されました。
-
- 上記のAND条件とOR条件の書き方を組み合わせます。
基本はAND条件は同じ行、OR条件は同じ列に書きます。
- 下図は、担当者が「上村」 かつ 金額が「>=100000」 、または 担当者が「岡田」のデータを抽出するという条件になります。
- 空白のセル(O6セル)は「条件なし」ということです。
- 設定画面は下図のようになります。
- AND条件とOR条件でデータが抽出されました。
-
- 検索条件に数式を使うことができます。
この時、検索条件を入力するセルの上のセルは空白セルとするか、データリストのフィールド(列見出し)とは異なる値を入力します。
- 数式を使用する例
- 検索条件を入力します。
- 数式はデータリストの最上部のセルを使って、 =E5="岡田" としました。
- 数式を入力したセルの上のセルには「条件」と入力しました。
データリストのフィールド名(列見出し)と同じものは使えません。空白セルとしてもOKです。
- 数式で定義した名前や列見出しを使用することもできます。
- 列見出しを使用した例
数式を =担当者=”岡田” としています。エラーが表示されるので気持ちが悪いのですが、データの抽出には問題がないようです。
- データリスト内のセルを選択して、[データ]タブの[詳細設定]を実行します。
- 検索条件範囲は N4:N5 となっていることに注意してください。
- 担当者が「岡田」と等しいデータが抽出されました。
- 複数の条件の数式の例
- 上記の 担当者が「上村」 かつ 金額が「>=100000」 、または 担当者が「岡田」のデータを抽出するという条件を数式で書いてみます。
↓
=OR(AND(E5="上村",I5>=100000),E5="岡田") としました。
- なお、数式はいろいろな書き方が考えられます。以下のようにすることもできます。
- =(E5="上村")*(I5>=100000)+(E5="岡田")
- 詳細設定を実行します(設定画面は上記2.と同じになりますので、省略します)。
データが抽出されました。
-
- データリストがあるシートとは異なる他のシートへデータを抽出することができます。
- ただし、データを抽出する(書き出し先の)シートから[詳細設定]を実行する必要があります。
- データリストがSheet1にある時、抽出データをSheet2へ書き出したいときにSheet1で[詳細設定]を実行すると「抽出データを作業中のシート以外へコピーすることはできません。」とエラーが表示されます。
Sheet2をアクティブにして(Shee2のセルを選択して)、リボンの[データ]タブの[詳細設定]を実行すると、エラーは出ません。
エラーの文章が分かりにくいのが困りもので、抽出データは作業中のシートへコピーします。よって、データを抽出する先のシートを作業中(アクティブな状態)で実行してくださいと、言いたいわけなのです。
- データリストがSheet1にある時、抽出データをSheet2へ書き出したいときの操作手順
- 抽出データを書き出したいSheet2を選択します。
- [データ]タブの[詳細設定]を実行します。
- 指定した範囲にチェックを入れます。
- リスト範囲にデータリストの範囲 Sheet1!C4:K21 を指定します。
- 検索条件範囲に Sheet1!N4:N5 を指定します。
- 抽出範囲にデータの抽出先の先頭のセル Sheet2!B3 を指定します。
- [OK]ボタンをクリックします。
- Sheet2へデータが抽出できました。
-
- 詳細設定では抽出先に抽出するフィールドを指定してデータを抽出することができます。
- 抽出先にフィールドを入力します。
フィールドの並び順は元データと異なっていてもOKです。
- データリスト内のセルを選択して、[データ]タブの[詳細設定]を実行します。
- 抽出範囲に入力したフィールドのセル範囲を指定します。
- 入力していたフィールドのデータが抽出できました。
- データが入力されている範囲内でデータ抽出を行うこともできます。
オートフィルターと同じような状態になりますが、検索条件をどこか別のセルで指定する必要があります。
- A1:A2に検索条件を入力しています。 「担当者」が「岡田」と完全に一致するデータを抽出するという条件にしています。
- データリスト内のセルを1つ選択します。
[データ]タブの[並べ替えとフィルター]グループにある[詳細設定]を実行します。
- 「選択範囲内」にチェックが入っているのを確認します。
リスト範囲に「C4:K21」、検索条件範囲に「A1:A2」と指定します。
- 2件のデータが表示されました。
行番号を見ると、他の行は非表示になっているのが分かります。
-
詳細設定の解除
- データ抽出を行った後、元に戻すには、[データ]タブの[並べ替えとフィルター]グループにある[クリア]を実行します。
よねさんのExcelとWordの使い方｜エクセル2013基本講座：目次｜フィルター(データ抽出)｜詳細設定を使ってデータを抽出する