エクセル 値 抽出。 エクセルDGET関数で条件を満たすデータを抽出

INDEX+MATCH関数で条件に合った行の値を抜き出す基本的な使い方

エクセル 値 抽出

Excelシート上の参考表から必要なデータを検索し、指定した箇所に表示させることのできるVlookup関数。 大変便利な関数ですが、いくつかの弱点があります。 そのうちの一つが、参照表内に同じ検索値が複数ある場合に、最初に一致した検索値を引用してしまうというもの。 シリーズ4回目となる今回は、 Vlookup 関数と COUNTIF 関数を組み合わせることで、同一の検索値が 複数ある場合に任意のデータを抽出する方法をご紹介します。 Vlookup関数は、検索値が複数の場合に機能しない!?COUNTIF関数で対応しよう! 例えば、以下の表で、左の参考表に従って右の表の「担当者名」を埋めていきたい場合、 普通にVlookup関数を使っても上手くいきません。 仮にF2のセルにVookupの式を入力してみます。 すると以下の表のように、F列には取引先ごとにすべて同じ担当者名が表示されてしまいます。 なんとか株式会社の場合は「山田真一」「山村和彦」「青木修平」と表示したいところを、すべて「山田真一」となってしまっている訳です。 これは、Vlookup関数の特性上、同じ値の検索値が複数ある場合に、検索範囲の一番最初のデータが引用されてしまうため。 参照表で「なんとか株式会社」を検索値とするデータは3つ存在しており、そのうちの一番最初にヒットする値である「山田真一」が表示されてしまうのです。 この問題を解決するには、複数ある同一の検索値をそれぞれ唯一無二のものにすればOK。 そのために「 COUNTIF」という関数を使います。 重複データの処理に便利なCOUNTIF関数 さて、話を進める前に、一旦COUNTIF関数の使い方を学びましょう。 COUNTIF関数とは、検索範囲内から条件に一致するセルの個数を数字で返してくれるというもの。 Excelシート上のリストの中で、重複するデータがあるかどうか調べる時などに大変便利です。 例えば、以下の表でA列に入力された番号にダブりがあるか調べたい場合。 B列にCOUNTIF関数の式を入力することで、番号の隣のセルに出現する数を表示することができます。 B2のセルにに挿入する式は =COUNTIF(A:A,A2 A2と同じセルの値のセルがA列でいくつあるのか数える式です。 定義は以下の通り。 この式をB22までコピー&ペーストした結果が以下の表です。 「195」という値は3つ、「160」は1つ・・・というようにA列の各セルについて、A列で重複して入力されているかどうか確かめることができるのです。 COUNTIF関数を工夫すれば、重複するセルを一括して抽出できる COUNTIF関数を使えば、いくつ重複するデータがあるのか数えることができると説明しました。 しかしながら、この方法では各セルのデータについて、他にダブっている値のセルが存在することは分かっても、一目瞭然で消すべきデータを見分けることができません。 こんな場合は、以下のように関数に少し細工をします。 B列の数字が示すのは、各セルの番号がその列で何番目に出てきたかということ。 つまり、B列で2以上の数字が出たA列の値は、それより上の行ですでに出現しているということになるわけです。 よって、B列で「2」以上の値を削除すれば重複データは無くなります。 なぜでしょう。 式はそれぞれ「A2からA3までのセルで、A3の値はいくつかあるか」「A2からA4までのセルで、A4の値はいくつあるか」・・・・という意味を表し、検索する範囲がどんどん下へ広がっていくイメージです。 とすれば、B列の各セルに入力された関数は、自分自信より下の行のセルは参照していないことになり、よってB列に表示された数字は「そのセルの隣にある値が、A列で何回目に出てきたか」ということを表すのです。 「重複の削除」は使えない!? COUNTIF関数を使って、重複するデータを抽出する方法をご紹介しました。 この方法を活用すれば、複数の重複するデータに対応できないVlookup関数の弱点をカバーすることが可能になります。 具体的なやり方を学ぶのは、に回しましょう。 ちなみに、Excelには「重複の削除」という機能がついておりますが、これは重複しないデータまで削除してしまうバグが確認されているそうです。 式も複雑でないCOUNTIF関数を覚えておいて損はありません!.

次の

最小値や最大値を抽出する方法 エクセル操作

エクセル 値 抽出

【エクセル時短】は「少しでも早く仕事を終わらせたい!」というビジネスパーソンのみなさんに、Excelの作業効率をアップするワザをお届けする連載です。 毎週木曜日更新。 Excelで作成した表には、縦方向(行)と横方向(列)にデータが並んでいますよね。 その表から「特定の値に対応するデータを取り出す」と聞いて、真っ先に思い浮かぶのは「 VLOOKUP(ブイ・ルックアップ)関数」だと思います。 しかし、 特定の行と列がクロスする箇所、つまり「2つの値に対応するデータを取り出す」場合、VLOOKUP関数だけでは、ちょっとやりにくいのです。 表の形式によっては、VLOOKUP関数が使えないこともあります。 【エクセル時短】第42回では、このような「 クロス抽出」を関数の組み合わせで実現するテクニックを紹介します! クロス抽出でVLOOKUP関数をどう使う? ここでは例として、以下のような表を用意しました。 縦方向に「日付」、横方向に「地区」があり、それぞれを値として指定すると「担当者」が取り出される、という具合です。 セルH2に「1月29日」、セルH3に「名古屋」という2つの値を指定すると、セルH5に担当者として「加藤」が抽出されるようにします。 ここで、VLOOKUP関数の構文をあらためて見てみます。 VLOOKUP( 検索値, 範囲, 列番号, 検索の型) 引数[ 検索値]には「H2」、引数[ 範囲]には「A2:E13」と指定するとして...... 引数[ 列番号]はどうしましょうか? 「名古屋」は3列目なので「3」と指定すれば担当者を求められますが、いちいち何列目かを数えて関数式を修正するのはスマートではありません。 この問題は、もう1つの関数「 MATCH(マッチ)関数」が解決してくれます! MATCH関数でデータの位置を数値化 MATCH関数は、「指定した条件に一致するデータがセル範囲の何番目にあるか」を求める関数です。 構文は以下のとおり。 MATCH( 検索値, 検査範囲, 照合の種類) 引数[ 検査範囲]の先頭のセルの位置を1として数え、引数[ 検索値]が何番目にあるかを数えます。 完全一致のデータを検索する場合、引数[ 照合の種類]には「0」を指定します。 先ほどの表で「地区」が何番目にあるかを求めるには、以下のような関数式になります。 これをVLOOKUP関数と組み合わせる手順を見てみましょう。 ここではMATCH関数の結果をVLOOKUP関数の引数に利用したいので、引数[ 検査範囲]には「A2:E2」と指定しています。 2MATCH関数の結果を確認する MATCH関数の結果として、セルI3に「3」と表示されました。 「名古屋」の列番号が数値として取り出されたことにより、VLOOKUP関数の引数として使えます。 3VLOOKUP関数とMATCH関数を組み合わせる セルH5に、VLOOKUP関数とMATCH関数を組み合わせた以下のような関数式を入力します。 引数[ 列番号]をMATCH関数で指定するわけですね。 INDEX関数+MATCH関数も定番の組み合わせ ここまではVLOOKUP関数を使いましたが、クロス抽出を実現する関数の組み合わせとしては、 INDEX(インデックス)関数とMATCH関数もあります。 INDEX関数の構文は以下のとおり。 INDEX( 参照, 行番号, 列番号, 領域番号) INDEX関数は「配列形式」と「セル参照形式」の2つの使い方ができる関数で、上記はセル参照形式での構文です。 引数[ 範囲]のうち、指定した引数[ 行番号]と引数[ 列番号]が交わる箇所のデータを取り出します。 まさにクロス抽出のための関数ですね。 参照するセル範囲が1つの場合、引数[ 領域番号]は省略しても結果は変わりません。 まずはINDEX関数の働きを見てみましょう。 表の「第5回」に相当する5行目、「広島」に相当する3列目のデータを取り出す、という使い方です。 ただ、引数[ 行番号]に「5」、引数[ 列番号]に「3」などと、いちいち数値に変換して指定するのでは実践的ではありませんよね。 そこで、MATCH関数の出番です。 INDEX関数とMATCH関数を以下のように組み合わせれば、1つの関数式でクロス抽出が可能になります。 INDEX関数の引数[ 行番号]と引数[ 列番号]をMATCH関数で指定するわけですね。 1つの関数式で、意図どおりのクロス抽出ができました! ちなみに、VLOOKUP関数+MATCH関数の例とは異なり、表の左端にある「日付」ではなく、右端に追加した「開催回数」を条件にしていることにお気づきでしょうか? VLOOKUP関数では引数[ 検索値]が表の左端にある必要がありますが、INDEX関数なら、そうした制約はありません。 INDEX関数+MATCH関数のほうが、より汎用的に使える組み合わせと覚えておいてください。 close 閉じる.

次の

Excel

エクセル 値 抽出

エクセルの重複行削除機能を使う方法 まずは、 エクセルの「重複行削除機能」を使う方法についてご紹介します。 具体的な手順は、以下の通りです。 今回は、とあるバーの会員リストに関するデータ(下図)を例にご説明します。 この時、「性別」「年齢」などの項目名も忘れずに選択しましょう。 2. 「重複の削除」をクリック 「データ」リボンの「重複の削除」をクリックデータ範囲選択後、画面上部の「データ」リボンにある「重複の削除」をクリックします。 3.重複条件を選択し、OKをクリックして削除 表示されたダイアログボックスで、データが重複した場合に削除したい項目名を選択し、OKをクリックすると、重複データが削除されます。 ここでは、「性別」と「お気に入りのカクテル」が重複しているデータを削除しました。 このため、同じ性別で同じ「お気に入りのカクテル」の行が削除されることとなります。 今回のデータでは9個分の重複データが削除されました。 条件を変更することで違うデータが得られるので、必要に応じて何を選択するか考えましょう。 エクセルの重複データを抽出して削除する方法 エクセルの「重複行削除機能」以外に、 重複データを抽出してから削除する方法もあります。 おおまかな手順は以下の通りです。 「重複する値」というウィンドウで値が「重複」になっていることを確認• 抽出された重複データを含む表のどこかのセルを選択し、「データ」タブから「データツール」の「重複の削除」をクリック• 「重複の削除」というウィンドウで重複している項目をひとつ以上選択し、OKを押して削除 具体的な方法を解説していきます。 1.重複データを抽出する 先ほどのリストを例に、重複データの抽出方法をご紹介します。 この時、項目部分は選択する必要がありません。 今回は、「お気に入りのカクテル」の重複データを抽出します。 2.「重複する値」というウィンドウで値が「重複」になっていることを確認 次に、「重複する値」というウィンドウで、値が「重複」になっていることを確認してください。 書式は好きな色を選択しましょう。 OKをクリックすると、 データが重複しているセルに任意の色が付きます。 これで重複データの抽出は完了です。 3.抽出した重複データを削除 上記で抽出した重複データを削除すると、重複するデータを持ち色が変わった行が消えて、重複行削除機能と同様の結果になります。 重複データが少ない場合には、色が変わったセルを1つずつ目視で削除していくという方法もありますが、 ここでは、重複データが多い場合でも使える方法をご紹介します。 まず、重複データを含む表のどこかのセルを選択します。 セルは表のなかであれば重複部分でなくても構いません。 4.「重複の削除」というウィンドウで重複している項目をひとつ以上選択し、OKを押して削除 次に、「データ」タブから「データツール」の「重複の削除」をクリック。 表示される「重複の削除」ウィンドウで、重複が含まれる項目を選択します。 今回は、先ほど抽出した「お気に入りのカクテル」のみを選択しました。 OKを押すと、「お気に入りのカクテル」が他のデータの「お気に入りのカクテル」と重複した16のデータが削除されます。 誰とも被っていない「一意のデータ」のみが残るため、見やすくなっているのが分かるでしょう。 エクセルの重複データをCOUNTIF関数でチェックする方法 最後は、 COUNTIF関数を活用して重複データをチェックする方法をご紹介します。 おおまかな手順は以下の通りです。 カウント結果表示用の列を準備し、COUNTIF関数を入力• 入力した計算式をカウント列すべてのセルにコピーし実行 以下で、順を追って説明します。 COUNTIF関数とは COUNTIF関数とは、指定した条件に当てはまるセルの数をカウントする関数です。 重複データをチェックしたい表に活用すれば、どの部分が重複しているのか一目で確認できます。 ただし、COUNTIF関数は、重複をチェックするのみなので、重複部分を削除したい場合は今回紹介した前述の方法をとる必要があります。 COUNTIF関数の計算式は、「=COUNTIF(範囲,検索条件)」で表されます。 COUNTIF関数がわからない方は「」の記事がおすすめです。 1. カウント結果用の列を準備し、先ほどの表にCOUNTIF関数を活用して、重複データがあるかチェック まず、カウントした結果を表示するための列を用意し、一番上のセルにCOUNTIF関数を入力します。 この際、表をくまなく参照できるよう、範囲については「絶対参照(F4キー)」を忘れないようにしましょう。 絶対参照については、「」で詳しく解説しています。 これで「C3からC30のセルについて、C3と重複する数」を表示してくれます。 2.入力した計算式をカウント列すべてのセルにコピーし実行 次に、入力した計算式をカウント列すべてにコピーします。 正しく絶対参照されていれば、すべての列で重複データのチェックができるはずです。 関数を実行すると、下図のように数字が表示されます。 この数字が2以上の場合は、「重複あり」となります。 前述の通り、COUNTIF関数は重複があるかチェックする関数です。 重複データを削除したい場合は、エクセルの重複データを抽出して削除する方法などので削除することが必要です。 大量のデータをエクセルで処理する際、重複部分を自力で探すのは大変です。 エクセルには重複を見つけ、削除するための便利な機能があるので、今回の記事を参考に、ぜひ活用してみてください。

次の