エクセル 条件 に 合う データ を 抽出。 Excel(エクセル)で条件に合うデータを別表に順番に抽出する方法/If、Large、Row、CountA関数等の組み合わせ方

Excel(エクセル)で条件に合うデータを別表に順番に抽出する方法/If、Large、Row、CountA関数等の組み合わせ方

エクセル 条件 に 合う データ を 抽出

「Excelの便利機能活用術」 INDEX関数とMATCH関数で表からデータを検索して抽出する ITライター 立山秀利 ExcelでINDEX関数を使うと、表から行と列を指定してデータを抽出できる。 さらにMATCH関数を組み合わせると、目的のデータを検索して抽出できるようにできる。 今回は両関数の基本的な使い方を解説する。 行と列を指定してデータを抽出するINDEX関数 INDEX関数は指定した表から、指定した行および列が交差する位置するデータを取得する関数である。 この表の2行目かつ3列目のデータ(C4)をA1セルに抽出するには、A1セルにINDEX関数を次のように入力すればよい。 行番号は3行目を1行目とするので、4行目は2となる。 列番号はA列を1で指定するので、C列は3となる。 すると、表の2行目、3列目に位置する「京都」が抽出される。 (画像をクリックすると拡大表示します) 指定した値を検索するMATCH関数 MATCH関数は指定したセル範囲から、指定した値が先頭から何番目にあるかを検索し、数値を返す関数である。 最大値を検索するなら1、最小値を検索するなら-1を指定する。 省略すると1を指定したとみなされる。 例えば、次の画面のように、A3~A5セルに都道府県名が入力されているとする。 このセル範囲で「千葉」が何番目に位置するかをA1セルに求めるには、A1セルにMATCH関数を次のように入力すればよい。 完全一致で検索するので、引数「照合の種類」には0を指定する。 (画像をクリックすると拡大表示します) INDEX関数とMATCH関数の組み合わせでデータを検索・抽出 INDEX関数とMATCH関数を組み合わせると、データを検索して抽出できる。 まずはMATCH関数を用いて、目的のデータが表の行または列の何番目に位置するのか、検索して数値として取得する。 その数値をINDEX関数の行または列に指定すれば、目的のデータを検索して抽出できる。 例えば、次の画面のように、売上のデータ表がA1~E10セルに、商品の元データの表がG1~I6セルにあるとする。 売上のデータ表のB列に入力した商品コードから、該当する商品名を元データの表から検索し、C列に抽出したいとする。 D列の単価も同様に、B列の商品コードから検索・抽出したいとする。 B~C列のセルにはどのような数式を入力すればよいか、商品名の最初のセルであるC3セルを例に見ていく。 まずはMATCH関数を使い、B3セルの商品コードが商品の元データの表のI列で何行目にあるか検索する。 引数「検査範囲」には見出し行を除いたI3~I6を指定する。 B3セルの「B001」はI3~I6セルの3番目に位置するので、MATCH関数は3を返す。 その数値をINDEX関数の行に指定し、抽出したいデータが格納されている列を指定することで、目的のデータを抽出できる。 INDEX関数の引数「配列」には、商品の元データの表で見出し行を除いたG3~I6セルを指定する。 引数「行」には、先ほどのMATCH関数の数式をそのまま指定する。 商品名は商品の元データの表の1列目にあるので、引数「列」には1を指定する。 以下、同様の仕組みでC4~C10セルに商品名、D3~D10セルに単価を検索・抽出する数式を入力すればよい。 (画像をクリックすると拡大表示します) 表からデータを検索して抽出したい場合、VLOOKUP関数を利用することが多いが、検索対象のデータが表の1列目になければ対応できない。 しかし、今回紹介したINDEX関数とMATCH関数を組み合わせた方法なら、検索対象のデータが表の1列目になくても対応できる。 このように、INDEX関数を使えば、行と列を指定して表からデータを抽出でき、MATCH関数を使えば、表内の位置検索が可能になる。 そして、これら両関数を組み合わせれば、目的のデータを検索して抽出できるようになるのである。 その他の【Excel】を見る•

次の

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

エクセル 条件 に 合う データ を 抽出

個人情報は既存の商品購入履歴や新規顧客獲得するために非常に重要な情報となります。 しかしシステムの規模が大きくなるにつれて業務を行う時間は変わらないのに情報が膨大になり必要な情報を取得するのに大量の時間を要することもあるでしょう。 そこで Excel関数を上手く用いて「 Excel(エクセル)でデータ抽出」を行う方法をご紹介したいと思います。 業務の効率化に利用して下さい。 Excelでデータ抽出する方法その1:「VLOOKUP関数」 顧客情報は管理する項目が多いため、1つのまとまった情報ではなく Excelデータ抽出すると別シートにて表記されるケースがあります。 一緒の情報として確認したいといった場合に利用したい関数が「 VLOOKUP関数」になります。 「 VLOOKUP関数」は各Excelデータに共通項目があれば突き合わせをして1つのシート上にまとめられる機能となります。 1-1.「関数の挿入」を選択します。 (今回は最終購入日まで合わせます) 1-5.取得したい「最終購入日」は4列目なので列番号に「4」を入力します 1-6.最終購入日が挿入されました。 Excelでデータ抽出する方法その2:「IF関数」 「 IF関数」を用いることで条件指定の結果を Excel上にデータ抽出することが出来ます。 今回は購入金額によってランクの表記を表したいと思います。 以下のサンプルを用いて「 IF関数」でランキングを設定していきたいと思います。 2-1.「関数の挿入」を選択します。 2-2.関数分類を「すべて表示」に変更し、「IF関数」を選択します。 別の指定方法でも作成出来ます。 「VLOOKUP関数」同様に件数が増えれば下にコピーしていくだけで同じ関数の指定を引き継いで表示することが出来ます。 Excelでデータ抽出する方法その3:「COUNTIFS関数」 IF関数で条件付けが出来るようになるとそれぞれの件数が何件あるのかを集計したくなります。 例えば先ほどの利用ステージもそれぞれのステージに何人の顧客がいるのかを把握することで分析や営業戦略に活かすことにもつなげることが出来ます。 今回はステージが複数存在するので「 COUNTIFS関数」を利用します。 この関数を用いてステージ毎に何人の顧客数がいるのかを集計していきたいと思います。 3-1.集計したいセルにカーソルを移動し、「関数の挿入」を選択します。 3-2.関数の分類を「すべて表示」に変更し「COUNTIFS関数」を選択します。 3-3.関数の分類を「すべて表示」に変更し「COUNTIF関数」を選択します。 ブロンズの条件は「10,000円以内」なので以下の指定となります。 他の関数同様に件数が増えても「検索範囲」を広げることで追加出来ます。 最後に 今回は比較的簡単で汎用性に特化した関数を紹介しました。 利用する人が複雑で利用出来なければ後々廃れていってしまう機能になってしまいます。 単純且つ利便性の良い仕組みの構築を目指して頂いてそのきっかけになれれば嬉しいです。 件数が膨大になっても Excelで簡単にデータ抽出出来るのが今回紹介しました関数のメリットになるので、まだ件数が少ない内から採用を検討頂ければと思います。

次の

COUNTIF関数で条件にあうデータを数えてみよう

エクセル 条件 に 合う データ を 抽出

FindNextメソッド 下の図は、クラス生徒の成績「英語」「数学」「国語」の3教科のそれぞれの点数と3教科合計点数を表にして、合計点の評価別に名前をリストアップできるようにします。 1つの条件に合うデータを繰り返し取り出してリストアップ フィルター機能では各項目の並び替えやリストアップしかできませんが、「合計点」の【A】評価の人の名前をL列に取り出してリストアップします。 使うVBAコードは FindNextメソッドで、Findメソッドで設定した検索条件で連続して検索を行います。 引数「After」で設定したセルの次のセルから検索を行ってくれて、検索内容の含まれるセルをRangeオブジェクトで返してくれます。 Offset , -8. コマンドボタン(CommandButton1)をクリックすると• 変数myRangeとmeRangeと文字列型の変数myAddressと整数型の変数iを宣言する• 変数meRangeに評価を検索する範囲のセルJ3~J22を指定する• 指定したセル検索範囲変数meRangeに検索条件であるシート名【test】のセルL2と同じ値のセルを検索して、最初に見つけたセルを変数myRangeに保管する• 変数myRangeがNothingでない場合(見つけたら)Ifステートメントを開始する• 変数myAddressに見つけたセルを補完する• 以下の処理を繰り返す(Doステートメントの開始)• 同じ条件で変数myRangeの次のセルから検索を開始して見つけたら変数myRangeに保管する• 変数iに1を加える(1行下になります)• 最初に見つけたセルと同じ値のセルを見つけるまで処理を繰り返す(探し続ける)• 見つからなかった場合(Else)• メッセージを表示する「該当者がいません」• Ifステートメント終了宣言• マクロ終了 下の図のようにコマンドボタン1回押すだけでA評価の人がリストアップされました。 これはあくまでも基本的な使い方なので、実際にはもう少し付け加えて、表の中から同じ検索条件で条件に合うデータだけを順に取り出しています。 例えば、上の図のように「A評価」だけではなく「B評価」「C評価」も全て一括で検索して表示する場合でも使っていますし、各科目別に「A評価」だけ取り出してみたりして使っています。 1つの条件を複数検索条件として条件別に転記する 先ほどのA評価のみ表示されている図を参考にすると、残りのB評価とC評価も一緒に表示させることができます。 もちろん同じコマンドボタンを1回だけクリックするだけで!! 簡単に言ってしまうと、15行目のEnd Ifと16行目のEnd Subの間にもう1回2行目~14行目を追加するという事です。 追加1回目がB評価、さらに追加2回目がC評価って事ですね。 そんなに難しくないでしょう? ただし、同じ名前の変数が使えないので名前をちょっと変えれば大丈夫です。 下のコードは2回目のB評価を追加してみました。 Offset , -8. Offset , -8.

次の