エクセルで作成したデータベースから複数条件で検索を行う資料を作ろうとしていますが、うまくいきません。関数で考えるのには無理があるのでしょうか?
条件式は下記のとおりです。
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.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)
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.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.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で動作確認済。
お探しの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ランキング
-
エクセル 価格表から単価を呼び...
-
シートAで横に並んだ項目→シー...
-
行数が不規則な一週間ごとの合...
-
ある列、或いは、ある行のセル...
-
エクセルの順位別一覧表の自動...
-
Excel2013のF6キー操作について
-
エクセルで複数のシートにまた...
-
excelの不要な行の削除ができな...
-
Excelが固まってしまった。
-
エクセルで特定の範囲内から小...
-
スマートな関数を教えて下さい。
-
Excel元に戻す方法を教えてくだ...
-
Excelはなんで先頭の0を消すん...
-
【Microsoft Office Excel Comp...
-
4つのパターンを表示するEXACT...
-
[関数について]わかる方教えて...
-
Excel 2019 のピボットテーブル...
-
Excel初心者です。 詳しい方、...
-
関数を教えて下さい。
-
エクセルでチェックボックスを...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報