エクセル 条件に合うデータを抽出。 FILTER関数で条件に一致する行を抽出する

EXCELで条件に一致したデータを検索＆抽出するならVLOOKUP関数

範囲または配列の対応する要素の積を合計した結果を返します。 「SUMPRODUCT関数」自体の使い方は配列同士で積（掛け算）を求め、 さらにそれを合計するものです。 例えば単価と数量が一覧になっているものを範囲指定するだけで 合計金額が計算される便利な関数です。 複数条件で抽出する「SUMPRODUCT関数」の使い方 それでは複数条件で抽出する場合、条件を指定した配列を複数指定します。 さらにそれらの配列にそれぞれ条件を指定します。 さらにROW関数で行数を掛けてあげると 複数条件に合致するデータの行番号が返ってくるというものです。 行番号が分かればあとは 「INDEX関数」でデータを抽出するだけです。 「SUMPRODUCT関数」のまとめ• 複数条件でデータを抽出するときは「SUMPRODUCT関数」と「INDEX関数」• 「SUMPRODUCT関数」で複数条件を指定して行番号を取得する• 最終的に「INDEX関数」で目的のデータを抽出する いかがでしたでしょうか？ 今回は複数条件でデータを抽出することができる「SUMPRODUCT関数」をご紹介しました。 慣れるまでは使い方が難しいかもしれませんが、 マスターすればよりExcelの幅が広がり、業務効率もアップします。 使いこなせている人も多くはいない関数なので 初心者から上級者にぐっと近づく一歩にもなります。 ぜひとも「SUMPRODUCT関数」を覚えておきましょう！ 以上、「【Excel】複数条件でデータを抽出する（SUMPRODUCT関数）」でした。

次の

Excel（エクセル）でデータを抽出、抜き出す方法｜オートフィルター機能

Excelで作成した下のようなデータ表があります。 1行に1つのデータ、先頭行に見出しがあります。 検索条件 検索条件は以下の3つとし、3つの条件に合うデータを抽出します。 ここでは、表の上へ設定します。 下のように表の上へ5行追加して、検索条件を入力します。 見出しは表の見出しと同じにします。 コピーして貼り付けた方が無難です。 下のようになります。 検索条件にはアスタリスクなどのワイルドカードの使用ができます。 横並びの場合はAND条件、縦並びの場合はOR条件となります。 ［データ］タブの［詳細設定］をクリック 準備が出来たら、データの抽出を行います。 表内でクリックして、［データ］タブの［並べ替えとフィルター］グループにある［詳細設定］をクリックします。 ［詳細設定］のポップヒントには以下のように記されています。 フィルターオプションの設定 ［フィルターオプションの設定］ダイアログボックスが表示されます。 ［リスト範囲］は、自動で範囲が設定されていると思います。 直接入力しても構いません。 ［抽出先］は［選択範囲内］を選択しておきます。 既定のままです。 ［フィルターオプションの設定］ダイアログボックスは下のようになります。 ［OK］ボタンをクリックします。 やりやすい方法で行ってください。 データは下のようになります。 検索条件が何であるかが、一目で分かりますね。 フィルターの解除 フィルターを解除するには、［データ］タブの［並べ替えとフィルター］グループにある［クリア］ボタンをクリックします。 クリア 現在のデータ範囲に適用されているフィルターと並べ替え状態を解除します。 検索条件の追加 検索条件を［商品コード］が「4または5で始まる」という条件に変えてみます。 ［納入月日］と［得意先名］は同じ条件にします。 同じように［詳細設定］をクリックして、［フィルターオプションの設定］ダイアログボックスで設定します。 検索条件範囲は、今度はセル［A2］からセル［C4］とします。 データは以下のようになります。 抽出したデータの書き出し 抽出したデータを別に書き出すこともできます。 その場合は、［フィルターオプションの設定］ダイアログボックスで、下のように［抽出先］を［指定した範囲］に変更します。 ここでは、表の下に書き出したいので、セル［A23］をクリックします。 ［フィルターオプションの設定］ダイアログボックスは下のようになります。 ［OK］ボタンをクリックします。 以下のように抽出したデータが書き出されます。 抽出データを作業中のシート以外へコピーすることはできません。 例えば、［データ抽出］シートに表示したい場合は、［データ抽出］シートに切り替えて、［データ］タブの［詳細設定］をクリックします。 シートを切り替えて、リスト範囲を指定します。 ここでは、［フィルターオプション］シートに切り替えます。 リスト範囲であるセル［A6］からセル［D21］を指定します。 ［検索条件範囲］も同じようにセル［A2］からセル［C4］を指定します。 ［フィルターオプションの設定］ダイアログボックスは下のようになります。 そして、抽出先となるセル位置をクリックします。 下のように抽出されたデータが表示されます。 特定の列のみの抽出と重複レコードの削除 また、特定の列のみ抽出して 重複レコードを削除することもできます。 たとえば、［商品コード］の一意のレコード（重複レコードを削除）のみを抽出したいと思います。 まず、抽出先に書き出したい項目と同じ名前の見出しを入力します。 そして、［データ］タブの［詳細設定］をクリックします。 ［フィルターオプションの設定］ダイアログボックスが表示されたら、［抽出先］は［指定した範囲］、［リスト範囲］は［A6からD21］ですね。 ［抽出範囲］でセル［F6］を指定します。 ［重複するレコードは無視する］のチェックボックスをオンにして、［OK］ボタンをクリックします。 以下のように一意の商品コードが抽出されます。 重複レコードの削除について Excel2007以降には、［データ］タブに［重複の削除］というコマンドがあります。 ポップヒントには、以下のように記されています。 重複の... 検索条件の記述方法 抽出条件の記述方法については、Microsoftのページが参考になります。 分かりやすく解説されていますので、ぜひご参照ください。 以下は、検索条件で「～で終わる」を指定する場合の方法です。 ［詳細設定］をクリックして表示される［フィルターオプションの設定］ダイアログボックスは何かと便利ですね。 関連以下は、関連ページです。

次の

集計表から抽出・集計DSUM 関数 DSUM関数の使い方

Office365に新しく追加された、 FILTER関数を使えば リスト（表）から条件に合うものを抜き出すことができます• FILTER関数を使えば、 条件に合うDATA（行）を別の場所に抜き出すことができます 今までも、表（リスト）から 条件に合うものを表示させる方法にはいくつかありましたが、どの方法も使いにくい点がありました。 今回新たに追加された FILTER関数は、革新的な新しい機能、 スピル（溢れる）特性により簡単に条件に合うデータを抜き出すことができるようになりました。 VLOOKUP関数で条件に合うものを "抜き出して表示" 条件に合うデータが "１つ" しかない場合であれば、 VLOOKUP 関数を使って条件に該当するデータを抜き出すことができます。 しかし、 VLOOKUP 関数は 最初に条件に該当するデータ（のみ）を抽出してしまうため、 複数のデータが条件に該当する場合には使うことができません。 条件に合う 「複数の結果を表示 」させるためには、 他の関数（COUNTIF関数等）と組み合わせる必要があります。 「フィルター機能」で条件に合うものを "表示" 「フィルター機能」を使えば簡単に条件に該当する複数のDATAを "表示"させることが可能です。 しかし、フィルター機能は 「元のリストにフィルターをかけて 」条件に 非該当のDATAを隠す方法なので、 "別の場所"に表示させたい場合は 「可視セル」の機能でコピペするなど面倒な作業が必要です。 「FILTER関数」で条件に合うものを "抜き出して表示" FILTER関数は検索した「結果」が複数になっても スピル（溢れる）特性によりすべての「結果」が表示されるので、あらかじめ 「結果が何個になるか」を気にする必要はありません。 そして、 FILTER関数を使って抜き出したファイルは、 元のデータを追加・修正すれば自動的に更新されます。 VLOOKUP関数で、 条件に該当する 複数のDATAを表示 VLOOKUP関数は職員名簿から、特定の職員のDATAを検索するような 「表引き」をする際にはとても使い易い関数です。 しかし、特定の職員の「勤怠データ」を抽出するような、 「条件に該当するものが 複数ある」場合には 使うことができません。 「青空一郎」の ８月の勤怠データを抽出する 「遅刻」や「早退」などの勤怠データのみを集計した一覧表から特定の職員のデータだけを抽出する場合、 同一職員のデータが複数ある場合があります。 青空一郎 ８月１日 遅刻• 青空一郎 ８月３日 欠勤• 青空一郎 ８月５日 早退 このような場合 VLOOKUP関数の特性で、そのままでは １つ目のデータしか表示できません。 これを回避するためには、 COUNTIF関数を使って 条件に合うデータが "何番目" に出てきたかをカウントし、その "値" と "職員番号" を連 結することで 「同じ職員の異なるデータ」にします。 VLOOKUP関数を使って検索する際には、用意しておいた 表題の"数値"と"社員番号"を連結して"検索キー"を作成することで回避します。 引数「条件」は相対参照で指定します。 セル番地 「C3」に入力した数式を下方向にコピーすることで、 相対参照にした、引数 「条件」の部分が 1つずつ増加します。 それにより、 １行目からその行までに同一データが「何個」あったかをカウントします。 「出現回数」と 「社員番号」のフィールド（項目）を "＆" で連結して 「検索キー」にします。 VLOOKUP関数で、勤怠情報を抜き出す VLOOKUP関数でB列～G列の 左端の「検索キー」のフィールド（項目）を検索します。 引数 「検索値」は "社員番号" と、用意しておいた項目名 「No. 」の "値"を "＆" で連結したものとします。 引数 「検索値」に 「No. 引数 「範囲」に "B列からG列までの列全体"を指定します。 引数 「列番号」に "４番目"の 「氏名」を指定 「勤務状態」に数式を入力して、 下方向にコピー 「勤務状態」のフィールドに 「氏名」の数式をコピーして、引数 「列番号」を "６"に変更。 「氏名」と 「勤務状態」の数式を、 下方向にコピー。 このように、 VLOOKUP関数で条件に合致するものが 複数あるものを抜き出すには、とても複雑な作業が必要です。 COUNTIF関数で、検索条件に該当するデータが範囲内で "何番目に出現したのか"を算出• "何番目に出現したか"の "値"と 「社員番号」を 連結（＆）して 「検索キー」を作成• 「氏名」のフィールド（項目）に入力した、 VLOOKUP関数の"検索値"を、 検索キーを入力した"セル"と用意した「No. 」のセルを連結（＆）したものにする• 「勤務状態」のフィールドにも、VLOOKUP関数を入力 このような複雑な手順で 最初に出現 した「青島一郎」と ２番目に出現した 「青島一郎」は 異なるデータとして検索することで、 VLOOKUP関数の "最初に条件に合致したものしか抽出しない" という欠点を回避します。 問題点は、最初に 「No. 」として 用意しておいた数以上の「結果」があった場合には表を作り直す必要があり、逆に 用意した「No. 」の数より「結果」が少なかった場合がエラーが表示されてしまいます。 エラーを回避するためには "IFERRO関数"を組み合わせる必要がらに数式が複雑になってしまいます。 表（リスト）を 「テーブル化」する• 「表（リスト）」の どこか１カ所を選択状態にする• 「挿入」タブを選択• 「テーブル」をクリック• テーブルの作成ダイアログで、 "テーブルに変換される範囲"が正しく選択されていることを確認• 「先頭行をテーブルの見出しとして･･･」にチェックを入れる• 「OK」をクリック テーブル化された表で 「フィルターをかける」 フィルター機能は、条件に合うものを抽出して表示」するのではなく 「条件に合わないものを見えなくする（隠す）」仕組みです。 「氏名」のフィールド（項目）の "フィルターボタン" をクリック• フィルターのダイアログで 「すべて選択」のチェックを外す• 「氏名」のフィールドの 全ての "要素" の選択が解除されました• 表示したい 「青空一郎」にチェックを入れる 「青空一郎」のみが表示されました 「青空一郎」以外の部分に "フィルター" がかかり、見えなくなりました。 このように、フィルター機能を使えば簡単に 条件に合うものだけを表示させることができます。 フィルターをかけた状態で "コピー して貼り付け" てやれば、条件に合うものだけを抜き出すことができます。 しかしこの方法では、 元のデータ（表）が更新されるたびに作業をやり直す必要があります。 FILTER関数を使って別の場所に、 条件に合うデータのリストを作成 FILTER関数を使えば、VLOOKUP関数のような複雑な手順を踏むことなく簡単に条件に合うデータ（行）を抜き出すことができます。 FILTER関数とVLOOKUP関数の最大の相違点は、 FILTER関数は "スピル特性"により 「数式が入力されていない部分にも"結果"が表示される」ことです。 条件に合致するデータが 複数ある場合、数式を入力してあるセルの "下側の範囲"に、 スピル（溢れる）ように 「ゴースト」として「結果」が表示されます。 この特性により、あらかじめ検索結果の "数 が何個になるか "気にする必要がありません。 FILTER関数を入力 条件に合致する「結果」を表示させる範囲の 「左上」にFILTER関数を入力。 「結果」が 複数ある場合は、スピル特性により 「ゴースト」として 数式のない部分に表示される。 引数 「配列」に "表全体"を指定• 引数 「条件 」に 「氏名」のフィールド（項目）を指定• 「日付」のフィールド（項目）を選択して、 "右クリック"• プルダウンリストから 「セルの書式設定」を選択• セルの書式設定のダイアログで、 「表示形式」のタブを選択• 分類で 「日付」を選択• 「OK」をクリック 選択したフィールド（項目）のみを表示する FILTER関数は 通常１行（レコード）を抽出するので、 全てのフィールド（項目）が表示されます。 少し複雑になりますが、FILTER関数を ネスト（）構造にすることで任意の項目のみを抽出して表示させることができます。 「配列」の仕組みで、 任意の項目だけ表示する 配列の仕組みを使えば、 任意の項目だけ表示することができます。 あらかじめ 「表示したい項目」を用意しておき、元の項目と COUNTIF関数を使って比較して 「配列」を作成します。 FILTER関数をネスト（）構造にして、 外側のFILTER関数の 引数「含む」にCOUNTIF関数でつくった数式を入れます。 このように、今までは難しかった 「条件に合うデータを抜き出す」作業が、 FILTER関数を使えば簡単にできるようになります。 k-ohmori9616.

次の