INDEX＋MATCH関数で複数条件にANDで一致したデータを抜き出す方法
【エクセル時短】は「少しでも早く仕事を終わらせたい！」というビジネスパーソンのみなさんに、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関数のほうが、より汎用的に使える組み合わせと覚えておいてください。
次の
エクセルのif関数で等しくない（一致しない）を表す記号
エクセルCOUNTIF関数を使って、複数条件ORに一致する、セルをカウントすることができます。 COUNTIF関数は、１つの条件に一致する、セルの個数をカウントする関数ですが２つの方法で、複数条件ORを指定することができます。 １つは、COUNTIF関数とSUM関数を組み合わせた式です。 もう１つは、COUNTIF関数だけを使った式で、COUNTIF関数をプラスしていく形になります。 ここでは、２つの方法で、複数条件ORに一致するセルをカウントしています。 複数条件ORには、同じ列と複数の列での「ＡまたはＢ」があります。 同じ列と複数の列の場合の、OR条件も説明しています。 COUNTIF関数を使って、複数条件ORを使って、色々なセルがカウントできるようになります。 COUNTIF関数 検索条件に一致する、セルの個数を返します。 COUNTIF関数の書式と引数 COUNTIF 範囲, 検索条件 範囲 検索条件に一致する、セルの個数を求める範囲を指定します。 検索条件 条件を、数値、式、または文字列で指定します。 COUNTIF関数で複数条件ORに一致するセルをカウント COUNTIF関数の条件は、１つしか指定できませんが、 COUNTIF関数に複数条件ORを指定して、セルをカウントできる方法があります。 同じ列に、複数条件OR または を指定する方法と、複数の列で、複数条件OR または を指定する方法の、２つを紹介します。 「R1293」または、「R3298」に一致する、セルの個数をカウントします。 F2に、COUNTIFの式を入力しましょう。 「R1293」または、「R3298」に一致する、セルは「２」あります。 COUNTIF関数を「＋」をつなげることで、ORの複数条件になります。 F2に、式を入力しましょう。 「種類」が「W1」に一致するセルが「１」あります。 同じ列に、複数条件OR または を指定する方法と、同じです。 COUNTIF関数を「＋」をつなげることで、ORの複数条件になります。 COUNTIF関数を「＋」をつなげて、複数条件OR または を指定する方法の弱点は、条件が多くなるほど式が長くなることです。 次に紹介する方法は、その弱点を解消してくれます。 COUNTIF関数にOR条件を指定する時の選択 COUNTIF関数だけでOR条件を指定する方法と、COUNTIF関数とSUM関数を組み合わせて、OR条件を指定する方法を紹介しましたが、どちらにも弱点があります。 COUNTIF関数だけで、ORの複数条件を指定する場合は、同じ列でも、複数の列でもOR条件を設定できますが、COUNTIF関数を「＋」でつなげなくてはいけないので、式が長くなってしまいます。 COUNTIF関数とSUM関数を組み合わせた式は、簡略化されて、すっきりとした式にはなりますが、同じ列でのOR条件になってしまいます。 どちらを使えばいいのでしょう？ 同じ列で、OR条件を設定する場合は、COUNTIF関数とSUM関数を組み合わせた式、複数の列でOR条件を設定する場合は、COUNTIF関数を「＋」でつなげる式がいいでしょう。 COUNTIF関数でセル参照 エクセルCOUNTIF関数には、文字列、数値などを指定できますが、セル参照を指定して、条件に一致するセルをカウントすることができます。 COUNTIF関数には、比較演算子もよく使います。 COUNTIF関数に比較演算子を使って、セル参照する場合、特殊な指定をします。 例えば、A以上、B以下などで比較演算子を使うとき、セル参照を使うと、エラーになってしまうことがあります。 その場合は、指定方法が間違っていることが多いです。 COUNTIF関数にセル参照を指定する場合は、気を付けましょう。 こちらの記事もご覧ください。
次の
