今回はエクセルで作ったデータベースから必要なデータのみを抽出する方法をまとめてみました。
やり方は４種類＋αがありますがそれぞれメリット・デメリットがあります。必要な目的に応じて適時やり方を選んでください。
なお、データベースの作り方には基本となる考え方があります。こちらの考え方が採用されていることを前提としていることもありますのでご確認ください。
▶︎関連記事：エクセルのデータベース 基本の考え方
抽出方法の特徴から考える使いどころ
抽出方法には４つのやり方があります。それぞれに特徴がありますので、必要な目的に応じて選択しましょう。
オートフィルタ
- 抽出するデータを細かく設定できる
- 色や数字の範囲などでも抽出ができる
- データベースの中で非表示になる
→データの抽出が複雑な場合に重宝される。データベースを直接加工する場合に使える
フィルター オプションの設定
- 抽出項目が完全一致のみ
- 同一シート内のほかのエリアにコピペされる
→元のデータを保持し、加工したデータを作りたい場合に使える
スライサー
- テーブルを設定しないと使えない
- 抽出項目が完全一致のみ
- 直観的にデータの抽出が行える
- 操作画面がエクセル上に表示されるので抽出項目の変更が簡単に行える
→抽出項目を何回も変更することが考えられる場合に使える
関数を使う
- 抽出項目を複雑に設定できる
- 抽出先は好きな場所に設定できる
- 関数の知識が必要
- データを加工することはできない
→複雑な条件設定が必要かつ、何度も変更する場合に使える
では実際に見ていきましょう。
データを抽出する
では実際にデータを抽出して見ましょう。
オートフィルタを使う
メリット・デメリット
オートフィルタはユーザーの任意でデータを抽出することができます。
任意で抽出できる他、特定の文字列を含むかどうか、数値であれば任意の値以上もしくは以下、セルの色などでも抽出することができます。
一方で他の抽出方法に比べて抽出する際にオートフィルターの設定を開かないといけないため若干ではありますが他の方法に比べて抽出項目を決めるまでのスピード感に欠ける抽出方法になります。
オートフィルタで抽出を使うとデータベース内のデータから不必要なデータを非表示にしてくれます。不必要なデータは非表示になるだけのためデータとしては存在し続けていますので、一時的にデータ自体を修正するわけではなく一時的に特定のデータのみを見たいときに活躍します。
もちろん表示しているデータを他のエリアに貼り付けることで加工をすればデータの加工も行えます。
データを加工する際は本当に変更していいのか注意が必要になります。
- メリット
- データ抽出の自由度が高い
- 不必要なデータは非表示になるだけなのでデータが変わるわけではない
- デメリット
- 抽出したデータを変更するとデータベースの中身そのものが変わってしまうため注意が必要
- 抽出のスピード感がない
オートフィルタの設定方法
オートフィルタの設定方法は二つありますが、どちらも同じ結果になりますので代表的な方法をお伝えします。
- データベース内のセルを一つ選ぶ
- ホームタブを選択
- 並び替えとフィルターを選択
- フィルターを選択
オートフィルターの使用方法
操作マークをクリックすると操作画面が現れます。
下にあるリストから必要なものを選択すれば任意のデータのみが抽出されます。
操作マークをクリックすると操作画面が現れます。
下にあるリストから必要なものを選択すれば任意のデータのみが抽出されます。 →
茨城株式会社を選んだ状態でOKを押すと
茨城県株式会社のみが選択される。
中ほどにある検索を使えば特定の文字を含むデータが下のリストに現れます。例えば“山“を含むデータのみを抽出する際には検索エリアに山を入力すると対象のデータのみがリストに抽出されます。
またテキストフィルターを使えば特定の文字から始まるデータなどさらに細かくデータを抽出することができます。
同じように数字によるデータであれば任意の数字以上や範囲内の数字などで設定もできます。
他にもデータベースで注意を要するデータには色を付けるなどすることもあるかと思いますが、そういったセルに色を設定したものを抽出することも可能です。
フィルター オプションの設定を使う
メリット・デメリット
フィルターオプションでは２つの抽出方法が選べますが、一つはオートフィルターの機能からデータの抽出方法が制限され文字の条件が完全に一致するだけのもの以外を非表示にするという、劣化機能になりますのでここでは説明しません。
もう一つは特定のデータを別エリアに張り出してくれるがあるので抽出したデータをさらに加工した場合にはおすすめの方法になります。しかし、フィルターオプションの設定ではデータを細かく指定することは不可能で、入力されているデータと完全に合致するもののみを抽出することになります。
- メリット
- 必要なデータを抽出しほかのエリアに貼り付けられる
- 抽出したデータを加工したい場合に元のデータを変更することなく操作できる
- デメリット
- データの抽出が任意の文字列に合致したもののみしか行えない
- 抽出したいデータの項目部分と抽出したいデータ内容を任意の場所に入力する
フィルター オプションの設定方法
- データタブを選択する
- 詳細設定をクリックする
- フィルターオプションに必要な項目を入力する
- 抽出先は指定した範囲を選ぶ
- リスト範囲は抽出元となるデータ範囲を選ぶ
- 検索条件範囲は最初に作成したデータの項目名と抽出したいデータ内容を入力したセルを選ぶ
- 抽出範囲は抽出元と同一のシート内で抽出したデータベースを作りたい場所を指定する。
※抽出範囲となっているが抽出先の先頭行、先頭列となる起点のセルを一つ選ぶだけでよい。
- OKを押すことで抽出されたデータベースが作れる
スライサーを使う
メリット・デメリット
スライサーはテーブルを設定したデータベースに対して必要な項目だけを表示してくれる機能になります。不必要なものについては非表示になるだけのためデータの加工を行う場合はデータベース自体を加工することになるので注意が必要です。
スライサーは直観的にデータの抽出が行えるほか操作画面をエクセル上に貼り付けられるので項目の変更が簡単にできることがメリットになります。
一方でデータベースがテーブルになっていないといけないことと、抽出項目は既に入力されているものに完全一致するものだけと抽出項目を細かく設定することができません。
- メリット
- 直感的に使える
- 操作画面をエクセルに貼り付けられるため操作が抽出項目を素早く変更できる
- デメリット
- 抽出したデータを変更するとデータベースの中身そのものが変わってしまうため注意が必要
- テーブルの設定が必要
- 抽出項目は完全一致のみ
スライサーの設定
- 抽出したいデータベースをテーブルにする
▶関連記事： テーブル機能のまとめ - データ抽出を行うテーブルのセルを選択する（セルはどれでもよい）
- 挿入タブを選択する
- スライサーを選択する
- 抽出したい列の項目名を選ぶ
- 抽出したい項目を選ぶ
スライサーの使用方法
出てきた画面で必要な項目をクリックすることでデータベース内に合致するデータ以外が非表示となりデータが抽出できます。画面では請求日が2/17のもののみを抽出した状態です。選択項目をクリックするだけでデータが抽出されるほか、抽出項目も簡単に変更できます。
複数の項目を選択したい場合は複数選択のスイッチをオンにしましょう。
関数を使う
2019年ごろからエクセルにはfilter関数というものが実装されました。filter関数はデータの中から条件に合致するものを抽出してくれる関数になります。
メリット・デメリット
filter関数は関数なのでセルに抽出したい項目を入力することで抽出してくれますが、その際の抽出先は任意で選ぶことができます。また関数内で条件を指定すれば文字列を含む、数値であれば範囲の設定などが可能になります。
一方で関数なので関数の知識がないと応用の幅が狭くなってしまいます。また、関数で自動的に引っ張ってくるだけなので、データを加工することはできません。
- メリット
- 抽出条件を細かく設定できる
- 抽出先を任意で選べる
- デメリット
- データの加工は行えない
- 関数の知識が必要
filter関数の基本設定
=FILTER(配列,含む,[空の場合])
- 配列：配列の部分に抽出したいデータベースの全体を入力
- 含む：条件を検査する列と条件の内容を入力します
- 空の場合：任意項目で入力しなくても動きます。入力すると抽出するデータがなかった時に表示される内容を決められます。
実際に入力した画面がこちら。
データベースの範囲がA1からC48まであるので「配列」にはA1:C48と入力、検索項目の部分にはA列がG1の項目（検索項目）と合致したものを表示させたいので「含む」にはA1:A48=G1と入力、で合致するものがなかった場合は該当なしと表示させています。
G１に青森県株式会社を入力すればA列の中の青森県株式会社を抽出してくれます。
また受注金額で受注した金額が50000円以下で抽出したい場合は「含む」の部分にC1:C48<=50000と入力します。
複数条件の場合
複数条件で両方の条件に合致する場合は条件を括弧でくくり*でつなげます。
例えば受注金額が30000円以上50000円以下の場合だと条件が①30000円以上②50000円以下の二つになりますので
=FILTER(A1:C48,(30000<=C1:C48)*(C1:C48<=100000),"該当なし")
と入力することになります。
また、複数条件でどれかの条件に合致する場合は条件を括弧でくくり+でつなげます。
例えば受注金額が30000円以下もしくは50000円以上のものを抽出する場合だと条件が①30000円以下②50000円以上の二つになりますので
=FILTER(A1:C48,( C1:C48<=10000)+(C1:C48>=70000),"該当なし")
おまけ：そもそも抽出する必要があるのか
会社で相談を受けて情報を抽出しようとしたところ、目的が項目に合致する件数を知りたいとか、合致する項目の受注金額を知りたいだけだったということもあります。その場合データを抽出する必要はありません。項目件数を調べたいのであればcountif関数、特定の条件の受注金額を知りたいだけであればsumif関数で求めることができます。
countifは指定した範囲内で条件に合致した項目の数を数えてくれます。
＝COUNTIF（範囲,条件）
範囲は検索する範囲を指定し、条件の部分には数えるセルの中身を入力します。条件についてはセルを選択することでセルに入力した中身と合致する項目を数えてくれます。
上の例では
=COUNTIF(B1:B19,E2)
と入力します。
sumifは条件に合致する項目の中から自動的に横列の数字を合計してくれます。
＝SUMIF（範囲、検索条件,[合計範囲]）
範囲は検索したい範囲を選択し検索条件は条件の内容を入力し合計範囲に調べたい数字が入った列を入力します。
上の例では=SUMIF(B1:B19,F2,C1:C19)と入力します。
おすすめ！ 社会人におすすめのExcel学習法はこちら https://www.udemy.com/course/masukawa_040/
