エクセルで作成したデータベースから複数条件で検索を行う資料を作ろうとしていますが、うまくいきません。関数で考えるのには無理があるのでしょうか?
条件式は下記のとおりです。
A列 B列 C列 判定
1 ? ? 40点
1 1 a 100点
1 1 b 80点
1 2 ? 60点
2 ? ? 80点
2 1 ? 50点
(?は無条件。どのような内容が入っていても
対象と考えています)
たとえば、
A列に「1」、B列に「1」、C列に「a」が入力されている場合は100点
A列に「1」、B列に「1」、C列に「b」が入力されている場合は80点
A列に「1」、B列に「2」、C列はどのような入力がされていても、60点
A列に「1」、BおよびC列には上記以外が入力されている場合は40点
と表記させる事を考えています。
この判定を求めるためのシートは、別途入力を行うため、行数はかなり
増えることが予想されます。
すみません。わかる方がおられれば教えていただけますか?
宜しくお願いします。
A 回答 (8件)
- 最新から表示
- 回答順に表示
No.8
- 回答日時:
●関数でワイルドカードを使った検索
まず、質問文の条件を文字通りに受け取ると、
{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文字に限らない場合は、?の代わりに*を用い、
かつ、適当な区切文字を挟んで連結するか、各列毎に判定する必要があります。
---------------------------------------------------------
上記数式は、他シートを参照しているのと、3列を連結しているせいで長くなっていますが、
一般に、
=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.5
- 回答日時:
関数では(1)式が長くなったり、(2)ネストが深くなったりして
、式が雑になって、実質実用的ではないと思う。(1)は(2)が原因でしょうが。実質的に不可能といって過言ではないと思う。
下記ユーザー関数の場合分け程度をIF関数のネストを重ねれば、関数式でもできるはずだが、式で書くのは馬鹿げていると思う。
ーー
それで実質VBAの利用だが、ユーザー関数を使ってやってみました。
VLOOKUP関数を使ってワイルドカード(*)式の検索ができないかやってみたが、現状私の力ではできなかった。質問の?の部分をワイルドカード式の考えで処理できないか、ということ
ーー
A列、B列、C列は各々1桁のコードとする。
3セルの値を結合した文字列=A1&B1&C1を空き列につくるものとする。文字列化したコードにする。
(もしA,B,C列が2桁以上の時は、その列分は定桁式のコードとして合成キーを作る。1+02+Cを結合して102Cのように。単純に左詰めするとおかしくなる)下記例は各々1桁と言う簡単な例にします。
ユーザー関数だと筋道を立てて記述するだけです。
ツール=マクロ=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列は3列を結合した文字列のことで、この回答ではここを出発点にします。
例データ
A列 B列
11a100
11b80
12360
21150
13440
24580
11c40
11540
11d40
12560
22280
12760
21450
上記はテスト例の数が少ないが十分テストしてください。
ありがとうございます。
VLOOKUP関数で、ワイルドカードを何とか利用できないか、私も考えてみたのですが、やはりできません。IF関数はデータ件数の関係上、現実的ではなので、imogasiさんの教えていただいたVBAで挑戦してみます。
No.4
- 回答日時:
ANo.3 に示した式は次式でもOKです。
参考までに。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
- 回答日時:
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)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルについて教えてください。 1回目が80以上(数字) or 対象外 (文字列)or (空欄) 2 2022/10/17 11:42
- Excel(エクセル) エクセルでエラーを無視して一番左側のセルの値を返したい 2 2023/07/27 13:06
- Visual Basic(VBA) VBA 検索と入力 Excel ブック ぶぶぶ シート ししし 列V 検索対象の列です 最終行は、お 6 2023/05/17 01:40
- Visual Basic(VBA) VBA シート間の転記で、条件の追加コードの書き方について教えて下さい。 13 2023/02/26 09:31
- Excel(エクセル) 列を自動で追加したい 3 2022/07/11 12:58
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- その他(Microsoft Office) Excelの関数(FILTER関数)について教えてください 2 2023/07/31 16:11
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- Excel(エクセル) Excel ある複数列に数値を入力した際に、別の列に本日の日付を入力したいです 7 2023/03/01 23:31
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 2 2022/06/25 22:42
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのデーターが2か月前の...
-
エクセルVBA、別ブックへ転記す...
-
【マクロ】顧客番号にて一致さ...
-
エクセル共有したが、アクセス...
-
エクセル②
-
(マクロ)データをAブックからB...
-
Excelでセルの値が同じか...
-
エクセルを使っていて2024/5/15...
-
指定文字の間に
-
Microsoft 365の Excel を使用...
-
エクセルの計算
-
エクセルでの作業計算方法について
-
Excelで全角を半角にしたいので...
-
エクセル関数に詳しい方教えて...
-
vba Bookを最小にせずに、UserF...
-
Googleスプレッドシートでファ...
-
エクセル 文字を増やしたい。
-
はがきについて。
-
エクセルの暗号化なしのバーの...
-
【マクロ】必要な項目(列)の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報