データベースから複数条件で検索を行いたい

質問

エクセルで作成したデータベースから複数条件で検索を行う資料を作ろうとしていますが、うまくいきません。関数で考えるのには無理があるのでしょうか？
条件式は下記のとおりです。

A列 B列 C列 判定
１ ? ? 40点
１ 1 a 100点
１ 1 b 80点
１ 2 ? 60点
２ ? ? 80点
２ 1 ? 50点
（?は無条件。どのような内容が入っていても
対象と考えています）

たとえば、
A列に「１」、B列に「1」、C列に「a」が入力されている場合は１００点
A列に「１」、B列に「1」、C列に「b」が入力されている場合は８０点
A列に「１」、B列に「２」、C列はどのような入力がされていても、６０点
A列に「１」、BおよびC列には上記以外が入力されている場合は４０点
と表記させる事を考えています。
この判定を求めるためのシートは、別途入力を行うため、行数はかなり
増えることが予想されます。

すみません。わかる方がおられれば教えていただけますか？
宜しくお願いします。

回答No.8

noname#52504

●関数でワイルドカードを使った検索

まず、質問文の条件を文字通りに受け取ると、
{1,1,a}という組み合わせは、{1,?,?}という条件も充たしますから、
「{1,1,a}は40点でもあり100点でもある」ことになってしまいます。

おそらく「優先順位の低い順」に並べてあるのだと思いますが、
機械的処理では通常上から順にチェックしますから、
「優先順位の高い順」に並べたほうが処理が簡単になります。

以下、条件表がSheet1に「優先順位の高い順に」記入してあり、
入力欄はSheet2の2行目以下であるとします。
また、A～C列の値はいずれも1文字に限るとします。

【条件表】
A列　B列　C列 判定
1　　　1　　　a　　100点
1　　　1　　　b　　80点
1　　　2　　　?　　60点
1　　　?　　　?　　40点
2　　　1　　　?　　50点
2　　　?　　　?　　80点

Sheet2のD2セルを、
　=INDEX(Sheet1!$D$2:$D$99,MATCH(TRUE,INDEX(SEARCH(Sheet1!$A$2:$A$99&Sheet1!$B$2:$B$99&Sheet1!$C$2:$C$99,A2&B2&C2)=1,),0))

として下方にフィル。

※英字の大文字と小文字は区別されません。
※A～C列の値が1文字に限らない場合は、?の代わりに*を用い、
　かつ、適当な区切文字を挟んで連結するか、各列毎に判定する必要があります。
---------------------------------------------------------
上記数式は、他シートを参照しているのと、３列を連結しているせいで長くなっていますが、
一般に、
=VLOOKUP(C1,A1:B99,2,0)
とする代わりに
=INDEX(B1:B99,MATCH(TRUE,INDEX(SEARCH(A1:A99&"】",C1&"】")=1,),0))
とすれば、ワイルドカードを使った検索が可能です。
※今回のケースでは、文字数が決まっているので(?)、終端処理を省いています。
---------------------------------------------------------
条件表を「優先順位の高い順」に並べ直すのが困難な場合は、
例えば、

　=OFFSET(Sheet1!$D$1,MAX(INDEX((LEN(Sheet1!$A$2:$A$99&Sheet1!$B$2:$B$99&Sheet1!$C$2:$C$99)>0)*ISNUMBER(SEARCH(Sheet1!$A$2:$A$99&Sheet1!$B$2:$B$99&Sheet1!$C$2:$C$99,A2&B2&C2))*ROW(Sheet1!$D$2:$D$99),))-1,)

など。
[一致する条件のうち、最も下方にある条件を採用する]

Excel2003で動作確認済。

回答No.7

n-jun

データの範囲が不明ですが、
データのＡ・Ｂ・Ｃを連結してキーとし、判定を項目とすれば
Dictionaryオブジェクトが使えそうですけど。（ＶＢＡですが）

回答No.6

n-jun

個々に提示されているデータが、ほぼ実際のデータなのでしょうか？

例えば、心理クイズみたいに選択した内容によって結果が変わってきて、
同じ４０点でもA・B・C列の組み合わせがいくつかあったりするとか？
なんて感じがしていたのですが、違っていたらすいません。

お礼

n-junさん、何度もありがとうございます。
おっしゃるとおり、組み合わせが多すぎるため、何とかVLOOKUP関数で、ワイルドカードが使用できないか考えているのですが…。

やはりVBAが現実的なのでしょうか？

回答No.5

imogasi

関数では（１）式が長くなったり、（２）ネストが深くなったりして
、式が雑になって、実質実用的ではないと思う。（１）は（２）が原因でしょうが。実質的に不可能といって過言ではないと思う。
下記ユーザー関数の場合分け程度をIF関数のネストを重ねれば、関数式でもできるはずだが、式で書くのは馬鹿げていると思う。
ーー
それで実質VBAの利用だが、ユーザー関数を使ってやってみました。
VLOOKUP関数を使ってワイルドカード（＊）式の検索ができないかやってみたが、現状私の力ではできなかった。質問の？の部分をワイルドカード式の考えで処理できないか、ということ
ーー
A列、B列、C列は各々１桁のコードとする。
３セルの値を結合した文字列=A1&B1&C1を空き列につくるものとする。文字列化したコードにする。
（もしA,B,C列が２桁以上の時は、その列分は定桁式のコードとして合成キーを作る。1+02+Cを結合して102Cのように。単純に左詰めするとおかしくなる）下記例は各々１桁と言う簡単な例にします。
ユーザー関数だと筋道を立てて記述するだけです。
ツール＝マクロ＝VBE－挿入ー標準モジュールに
Function ten(a)
Select Case a
Case "11a"
ten = 100
Exit Function
Case "11b"
ten = 80
Exit Function
Case Else
GoTo p1
End Select
p1:
Select Case Left(a, 2)
Case "12"
ten = 60
Exit Function
Case "21"
ten = 50
Exit Function
Case Else
GoTo p2
End Select
p2:
Select Case Left(a, 1)
Case 1
ten = 40
Case 2
ten = 80
Case Else
ten = 10
End Select
End Function
ーーー
Case Else
GoTo p2
のようなコードを作ったこと無かったが、結果的にうまくいくようだ。
もっとスマートなコードがあるかもしれない。
ーー
B列の関数は形上は簡単な　=ten(A1)　　と入れるだけです。
A列は３列を結合した文字列のことで、この回答ではここを出発点にします。
例データ
A列　　B列
11a 100
11b 80
123 60
211 50
134 40
245 80
11c 40
115 40
11d 40
125 60
222 80
127 60
214 50
上記はテスト例の数が少ないが十分テストしてください。

お礼

ありがとうございます。
VLOOKUP関数で、ワイルドカードを何とか利用できないか、私も考えてみたのですが、やはりできません。IF関数はデータ件数の関係上、現実的ではなので、imogasiさんの教えていただいたVBAで挑戦してみます。

回答No.4

noname#204879

ANo.3 に示した式は次式でもＯＫです。参考までに。

D3: =40*(A2=1)+20*(A2=1)*(B2=2)+60*(A2=1)*(B2=1)*(C2="a")+40*(A2=1)*(B2=1)*(C2="b")+80*(A2=2)-30*(A2=2)*(B2=1)

汎用性がないという点で、“邪道”な式と承知しています。
「判定を求めるためのシート」が示されていない質問も困ったモンですが・・・

回答No.3

noname#204879

A　　B　　C　　D
1　A列　B列　C列　判定
2　　1　　?　　?　　40
3　　1　　1　　a　 100
4　　1　　1　　b　　80
5　　1　　2　　?　　60
6　　2　　?　　?　　80
7　　2　　1　　?　　50

D2: =40*(A2=1)+20*AND(A2=1,B2=2)+60*AND(A2=1,B2=1,C2="a")+40*AND(A2=1,B2=1,C2="b")+80*(A2=2)-30*AND(A2=2,B2=1)

回答No.2

n-jun

データベースのぶぶんがどのように作られているのか不明なので、
何とも言えませんが。
関数では難しいのではないかと感じます。

回答No.1

n-jun

単にD列の式が知りたいのですか？
?は無条件とはいっても１やa,ｂではないわけですよね？

補足

質問の言葉足らずですみません。
データベースのD列欄に、A～C列の内容から判断して、判定を表示させたいと思っています。別シートに条件をまとめているのですが、１００パターン近くあり、if関数では無理があり困っています。
もともと他のデータベースを転用しており、余計なデータが混在しているため、1やa,bなど様々な内容が含まれており、ややこしくなっています。
宜しくお願いします。