![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
No.1ベストアンサー
- 回答日時:
こんにちは!
VBAになってしまいますが、一例です。
↓の画像のようにSheet1にSheet2の表に基づいて表示するとします。
Alt+F11キー → メニュー → 挿入 → 「標準モジュール」を選択 → VBE画面に
↓のコードをコピー&ペーストしてマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
Sub Sample1() 'この行から
Dim i As Long, k As Long, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
Range(wS1.Cells(2, 2), wS1.Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2)).ClearContents
For k = 2 To wS2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To wS1.Cells(Rows.Count, 1).End(xlUp).Row
If InStr(wS1.Cells(i, 1), wS2.Cells(k, 1)) > 0 Then
wS1.Cells(i, 2) = wS2.Cells(k, 2)
End If
Next i
Next k
Application.ScreenUpdating = True
End Sub 'この行まで
※ 関数でないので、データ変更があるたびにマクロを実行する必要があります。
お望みの方法でなかったらごめんなさいね。m(_ _)m
![「特定の文字列を含む場合指定の値を返す エ」の回答画像1](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/1/667667_5497ea4480270/M.jpg)
ありがとうございます。 しかも、すごいスピードで。
試してみたらうまくいきました。 いくつかCとCOとかで重複してしまうものがありましたが、仕方がないのかもしれません。
非常に助かりました。
No.11
- 回答日時:
B5 =index(D$5:E$9,max(index((row(D$5:D$9)-row(D$4))*countif(a5,"*"&D$5:D$9&"*")*(len(D$5:D$9)=max(index(countif(a5,"*"&D$5:D$9&"*")*len(D$5:D$9),))),)),2)
一応この数式では、Ctrl+Shift+Enter の操作は不要です。しかしご覧のとおり、数式そのものがだいぶ難解です。なお「p」と「cp」、「g」と「rg」を区別した上で、正しいほうの色が表示されます。大文字と小文字は区別されません。
![「特定の文字列を含む場合指定の値を返す エ」の回答画像11](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/4/1037181_5497ea45cc0f8/M.jpg)
ありがとうございます。 画像もつけていただき非常にありがたいです。
数式がかなり入れ子になっているので、理解している人しか編集できませんが、勉強になりました。 こういうやり方もありですね。
No.10
- 回答日時:
No9の回答の補足です。
質問の添付画像を見ないで一般論で回答してしましました。
検索するデータにはPとcp、およびGとMGがあるのですね。
この場合は、PおよびGはそれぞれ該当データが2つあることになりますので、下の方のデータ(cpとMGの行)を引っ張ってきます。
したがってcpを検索した(1つしか該当データが無い)場合には正確な位置を返しますので、一覧表でPはcpの下に配置してください。
同様にMGの下にGを配置しすれば、提示した数式をそのまま使用できます。
No.9
- 回答日時:
添付画像のようなリストがある場合、以下のような式を入力してCtrl+Shift+Enterで確定すればご希望の一部検索データを引っ張ってくることができます。
=INDEX(B:B,MAX(COUNTIF(D2,"*"&$A$2:$A$9&"*")*ROW($A$2:$A$9)))
該当データが2つある場合は下の方のデータを引っ張ってきます。
また、該当データが無い場合は、C1セルの値(返す値)を表示しますので、このセルに空白文字列(="")またはスペースなどを入力しておけばエラー処理の代わりになります。
![「特定の文字列を含む場合指定の値を返す エ」の回答画像9](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/0/1223543_5497ea45bdc2c/M.jpg)
有難うございます。
エラー処理の方法まで親切にありがとうございます。
NAが出ちゃうと面倒ですからね。
シンプルな方法で非常に参考になりました。
No.8
- 回答日時:
添付図が表示されていないようなのでもう一回
コードは(増やせるけど)2文字まで かつ 表は998色まで増やせるとして(例は7色)
B4セルは
=INDEX($E$4:$E$11,MOD(MAX(EXACT(RIGHT(LEFT(A4,FIND(" ",A4&" ")-1),{1,2}),
$D$4:$D$11)*10^{4,5}+ROW($D$4:$D$11)-ROW($D$3)),10^4))
[Ctrl]+[Shft] +[Enter] で確定、配列数式。{ }で挟まれる
添付図参照、
半角空白文字までを抜き出し、さらに、右から1文字と2文字を抜き出して、表と完全比較。
(大文字と小文字で違う文字とする)
1文字と2文字で両方一致する場合、2文字のほうを優先して抜き出している
詳細な解説はご要望があれば。
![「特定の文字列を含む場合指定の値を返す エ」の回答画像8](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/5/655143_5497ea4502838/M.jpg)
ありがとうございます。
こういうやり方もありですね。 皆さん色々工夫しますね。
流石です。 ある意味エクセルの関数の組み合わせなどは
クイズみたいで面白いですね。
No.7
- 回答日時:
2文字(増やせるけど)のコード かつ 表は998色まで増やせるとして(例は7色)
B4セルは
=INDEX($E$4:$E$11,MOD(MAX(EXACT(RIGHT(LEFT(A4,FIND(" ",A4&" ")-1),{1,2}),$D$4:$D$11)*10^{4,5}+ROW($D$4:$D$11)-3),10^4))
[Ctrl]+[Shft] +[Enter] で確定、配列数式。{ }で挟まれる
添付図参照、
半角空白文字までの右から1文字と2文字を抜き出して、完全比較。
2文字のほうを優先して、抜き出している
詳細な解説はご要望があれば。
No.6
- 回答日時:
こんにちは。
難しく考えすぎずに、
VLOOKUPの第四引数をFALSEにして、
VLOOKUP(検索値, 範囲, 列番号, [検索の型])
検索値にワイルドカードを使用すれば、
VLOOKUP関数自身で解決できませんか。?
例 - 検索値を"み*"にすると、"みどり"等にヒットしますが・・・。
はずしていたら、ごめんなさい。
ありがとうございます。
検索対象の文字列が結構長いのがあったりして、
ワイルドカードだと関係ないのもひっパテしまいます。。
とはいえ、Vlookdでワイルドカードというやり方は
今後活用させていただきます。勉強になりました。
No.5
- 回答日時:
関数を使って対応するには、色の種類が少ないのでしたら力技で良いのですが多くなった場合には次のように作業列を作って対応することもできますね。
例えばA3セルから下方にバックPといった製品名が入力されているとします。
D1セルから右横方向にはP、cp、R、Gといった文字が入力されその2行目にはピンク、チェリーピンクなどの色が入力されているとします。
D3セルには次の式を入力して右横方向にドラッグコピーしたのちに下方向にもドラッグコピーします。
=IF(D$1="","",IF(COUNTIF($A3,"*"&D$1&"*")=0,"",MAX($C3:C3)+1))
最後にお求めの色の表示をB列に表示させるためにB3セルには次の式を入力して下方にドラッグコピーします。
=IF(COUNT(D3:XX3)=0,"",INDEX(D:XX,2,MATCH(MAX(D3:XX3),D3:XX3,0)))
これで対応する色が表示されますね。なお色の種類は作業列の1行目と2行目を利用して自由に増やすこともできますね。
作業列が目障りでしたらそれらの列を選択したのちに右クリックして「非表示」を選択すればよいでしょう。
No.3
- 回答日時:
通常の関数では厳しい(私では無理)ので、ユーザ定義関数は如何でしょうか。
シートタブ上で右クリック→コードの表示→VBE画面メニューで挿入→標準モジュール→サンプルコード貼り付けてお試しください。
使用例
仮に抽出表をA1:B4、対応表がD1:E5、先頭行は見出し行とします。
抽出表のB2に=mylook(A2,$D$2:$E$5)、下方向にオートフィル
Function mylook(r1, r2)
Dim i As Long
For i = 1 To r2.Rows.Count
If r2(i, 1) <> "" Then
If r1 Like "*" & r2(i, 1) & "*" Then
mylook = r2(i, 2)
Exit Function
End If
End If
Next
mylook = CVErr(xlErrNA)
End Function
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・「I love you」 をかっこよく翻訳してみてください
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・昔のあなたへのアドバイス
- ・かっこよく答えてください!!
- ・あなたが好きな本屋さんを教えてください
- ・スタッフと宿泊客が全員斜め上を行くホテルのレビュー
- ・【大喜利】【投稿~8/27】 こんなガソリンスタンド二度と来るか!なぜそう思った?
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・【お題】動物のキャッチフレーズ
- ・【お題】甲子園での思い出の残し方
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・「それ、メッセージ花火でわざわざ伝えること?」
- ・自分用のお土産
- ・人生で一番お金がなかったとき
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・ちょっと先の未来クイズ第1問
- ・ゴリラ向け動画サイト「ウホウホ動画」にありがちなこと
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの2項目比較および...
-
【マクロ】ファイル名の一括変...
-
Excelでの表の作り方
-
【マクロ】フォルダ内にあるPDF...
-
Excelの複数条件の関数
-
Excelの条件付き書式について
-
Excelの警告について
-
【マクロ】functionプロシージ...
-
EXCELの散布図で日付が1900年に...
-
エクセルの数式バーのフォント...
-
エクセルでファイルの最終更新...
-
【Excel】版が同じ事を示す番号...
-
googleのスプレッドシート
-
エクセルの問題です。絶対値の...
-
Excelの数式について教えてくだ...
-
Excelで数値を時間数に変換する...
-
エクセルまたはGoogleスプレッ...
-
GoogleスプレッドシートでA1に...
-
2列に入っているデータを1列...
-
【マクロ】 IFERROR関数をマク...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報