複数条件でcountif以外を使う方法

質問

色々質問を探して考えてみたのですが、どうしてもわからないので質問させてください。

果物にそれぞれ商品コードがあり、
A列に果物の名前を入力すると、B列に商品コードが入力されるような式をB列に入れています。

【表】

　　　　A　　　　　　　　　　B

1　　　品名　　　　　　　商品コード

2　みかんを購入　　　　　0010

3　りんごをもらう　　　　　0020

4　ぶどうを捨てる　　　　　0030

【式】　B列に入力

＝If（countif(A:A,"*みかん*",0010）,if(countif(A:A,"*りんご*",0020),countif(A:A,"*ぶどう*",0030,"")))

検索値が文字の上、文章の中から拾っているため上記の関数を使っていたのですが、
最近果物の数とそれに対する商品コードが増えたため、上記の式では多すぎて入力できません。
お手数ですが、どなたかお分かりになる方ご回答お願いします。

回答No.1

keithin · ベストアンサー

基本的には添付図のように対応表を用意します。

その上でちょっと複雑な数式になりますが、
Ｂ２：
=INDEX(F:F,MIN(IF(ISNUMBER(FIND($E$2:$E$4,A2)),ROW($E$2:$E$4),9999)))&""
と記入し、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力、以下コピーします。

お礼

お礼が遅くなって申し訳ありません。
ご回答ありがとうございました。

色々試行錯誤した結果、なんとか出来ました。
本当にありがとうございまいた！

回答No.4

Nouble

失礼しました、修正させてください
条件は同じです。

=if(sumproduct(($a4=*&$d:$d&*)*($a4=*&$e:$e&*))<>1,¨コード表から特定できません¨,offset($f$2,sumproduct(($a4=*&$d:$d&*)*($a4=*&$e:$e&*)),0,1,1))

お礼

お礼が遅くなって申し訳ありません。
ご回答ありがとうございました。

がんばって試してみたのですが、上手くいかず…すみません。
別のやり方で出来ましたので、また、別の表で試してみようと思います。
ありがとうございました。

回答No.3

Nouble

コード表がエクセル形式で存在し
D列にミカンや林檎などといつた名詞が
E列に拾う、捨てる、売る、もらうなどといった動詞(？)が
F列には対応するコードが
各々入っていて
A列を第1キーB列を第2キーにソートされている
他ブック参照でも問題ないのですが
書きづらくなるのでご容赦を
今回はこのコート表が同シート内横手3行目からあるものとします。

今手元にExcelがないので勘違いしてたらスミマセン。

ではいきます。
A列データ先頭の真横のB列のセル(仮にB4とします、変わる場合は$A4の4を連動させて変更ください)に
=if(of(sumproduct(($A4=*&$D:$D&*)*($A4=*&$E:$E&*))<1,sumproduct(($A4=*&$D:$D&*)*($A4=*&$E:$E&*))>1),¨コードから特定できません¨,offset($F$2,sumproduct(($A4=*&$D4:$D4&*)*($A4=*&$E:$E&*)),0,1,1)
必要なだけ下にフィルしてください

お役に立てていたなら幸いです。

回答No.2

tom04

こんばんは！
VBAになってしまいますが・・・
一例です。

↓の画像のようにSheet2に表を作成しておきます。

画面左下の操作したいSheet見出し（Sheet1とします）上で右クリック　→　コードの表示　→VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i As Long, k As Long, ws As Worksheet
Set ws = Worksheets("Sheet2")
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
For k = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
If InStr(Cells(i, 1), ws.Cells(k, 1)) Then
With Cells(i, 2)
.Value = ws.Cells(k, 2)
.NumberFormatLocal = "0000"
End With
End If
Next k
Next i
End Sub 'この行まで

※　Sheet1のB列に関数が入っている場合はすべて消えて生データになりますので、
別Sheetでマクロを試してみてください。

お望みの方法でなかったらごめんなさいね。m(_ _)m

お礼

お礼が遅くなって申し訳ありません。
ご回答ありがとうございました。

マクロは今まで使ったことがないのですが、使いこなせれば便利なんでしょうね！
機会があれば試してみようと思います。
ありがとうございました！