Excelの複数条件を実現する数式の比較
表形式のデータから、複数の条件を指定してデータを取得する方法について調べて見ました。方法としては下記のようなものがあります。
それぞれ一長一短がありますので、どいうった結果を取得したいかによって使い分ければいいと思います。当店では複数条件でデータを取得する場合には配列数式を使うことが多いです。
なお最後の「検索に使うデータを結合しておいて、結合した値に対して検索する方法」は詳しくは記載しておりません。
方法によっての結果の違い
サンプルでは条件をB2:C3で、データ表をB5:E20にデータを入力しています。またそれぞれの方法はG6:G9で指定し、設定している数式は横のH列に表示しています。
条件に完全に一致するデータが複数ある場合の挙動
この場合に違いがはっきり出てきます。
- IF関数+VLOOKUP関数
- 最初に一致したデータのみが結果となります。
- 配列数式
- 配列数式では一致したデータを配列で取得して、それをSUM関数で合算していますので、一致したデータの合計となっています。
- DGET関数
- DGET関数は、一致したデータが複数ある場合、#NUM!のエラーとなります。サンプルでは条件に一致するデータが複数あるので#NUM!が表示されています。
- DSUM関数
- DSUM関数は、一致したデータが複数ある場合、その結果を合算したもとなります。
条件に完全に一致するデータが一つの場合の挙動。ただし一部条件に合致したデータがその前にある場合
- IF関数+VVLOOKUP関数
- 最初に一部一致したデータを結果にしようします。検索条件が「りんご」と「ピンク」のAND条件なので、一部一致した最初のデータ…番号1の「りんご」が対象となり、その色（ピンク）が一致しないので、結果-1となります。
- 配列数式
- 特に問題なく結果を得ることができます。
- DGET関数
- 一致するデータが1件だけなので、特に問題なく結果を得ることができます。
- DSUM関数
- 特に問題なく結果を得ることができます。
条件に完全に一致するデータが一つの場合の挙動
この場合、結果は同じになります。
- IF関数+VLOOKUP関数
- 一部一致したデータも存在しないので、特に問題なく結果を得ることができます。
- 配列数式
- 特に問題なく結果を得ることができます。
- DGET関数
- 一致するデータが1件だけなので、特に問題なく結果を得ることができます。
- DSUM関数
- 特に問題なく結果を得ることができます。
IF関数とVLOOKUP関数を組み合わせて使う
使い易いところ
- IF関数とVLOOKUP関数と比較的によく使う関数だけで実現できる。
使い辛いところ
- データ表中の検索に使うフィールドが一意でない場合（=重複がある）、最初のデータ以降は無視されますので、正しい結果を返さないことがあり得ますので注意が必要です。
配列数式を使う
使い易いところ
- 数式だけで複雑な条件を組むことができ、結果を取得することができる。
- 配列数式を理解していれば、ほぼ何でも実現することができる自由度が一番高い方法です。
使い辛いところ
- 配列数式を理解していないと、予期せぬ結果になってしまう場合があります。
- 配列数式を確定するには特殊な入力方法（Windowsの場合Ctrl+Shiftを押しながら数式を確定する）しないといけないので、配列数式を知らない方が修正しようとすると正しい結果が得られなくなります。
- 数式が複雑になる傾向がある。
DGET関数を使う
リストまたはデータベースの列から指定された条件を満たす 1 つの値を抽出します。
検索条件を満たすレコードがない場合、エラー値 #VALUE! が返されます。
検索条件を満たすレコードが複数存在する場合は、エラー値 #NUM! が返されます。
使い易いところ
- 条件の指定用の表(サンプルのB2:C3)さえ準備すれば、DGET関数だけで条件に一致したデータを取り出すことができる。
- 条件の指定用の表は横方向にAND条件、縦方向はOR条件となろので、色々な検索方法が使えます。
使い辛いところ
- 条件の指定用の表を作らないとDGET関数は使えない。ということは数式だけで条件を組んで結果を得ることができない。
- データ表の中で条件に一致するデータが複数ある場合は、#NUM!が結果となりますので、条件に一致するデータが複数ある場合には向きません。
DSUM関数を使う
リストまたはデータベースのレコードで指定されたフィールド (列) を検索し、条件を満たすレコードの合計を返します。
使い易いところ
- 条件の指定用の表(サンプルのB2:C3)さえ準備すれば、DSUM関数だけで条件に一致したデータを取り出すことができる。
- 条件の指定用の表は横方向にAND条件、縦方向はOR条件となろので、色々な検索方法が使えます。
使い辛いところ
- 条件の指定用の表を作らないとDSUM関数は使えない。ということは数式だけで条件を組んで結果を得ることができない。
広告
