あなたの映画力を試せる!POPLETA映画検定(無料) >>

Vlookupだとセルの値全てで照合しますが、特定の文字列を含む場合でVlook的に利用するにはどうすればよいでしょうか。  find やIFだとキリがないかな・・と思い格闘中です。 
アドバイスいただけると幸いです。よろしくお願いします。

「特定の文字列を含む場合指定の値を返す エ」の質問画像

このQ&Aに関連する最新のQ&A

A 回答 (11件中1~10件)

こんにちは!


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
    • good
    • 0
この回答へのお礼

ありがとうございます。 しかも、すごいスピードで。

試してみたらうまくいきました。 いくつかCとCOとかで重複してしまうものがありましたが、仕方がないのかもしれません。

非常に助かりました。

お礼日時:2013/01/10 14:25

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
    • good
    • 0
この回答へのお礼

ありがとうございます。 画像もつけていただき非常にありがたいです。

数式がかなり入れ子になっているので、理解している人しか編集できませんが、勉強になりました。 こういうやり方もありですね。

お礼日時:2013/01/10 14:27

No9の回答の補足です。



質問の添付画像を見ないで一般論で回答してしましました。
検索するデータにはPとcp、およびGとMGがあるのですね。

この場合は、PおよびGはそれぞれ該当データが2つあることになりますので、下の方のデータ(cpとMGの行)を引っ張ってきます。
したがってcpを検索した(1つしか該当データが無い)場合には正確な位置を返しますので、一覧表でPはcpの下に配置してください。
同様にMGの下にGを配置しすれば、提示した数式をそのまま使用できます。
    • good
    • 0
この回答へのお礼

わざわざ有難うございます。

お礼日時:2013/01/10 14:28

添付画像のようなリストがある場合、以下のような式を入力してCtrl+Shift+Enterで確定すればご希望の一部検索データを引っ張ってくることができます。



=INDEX(B:B,MAX(COUNTIF(D2,"*"&$A$2:$A$9&"*")*ROW($A$2:$A$9)))

該当データが2つある場合は下の方のデータを引っ張ってきます。
また、該当データが無い場合は、C1セルの値(返す値)を表示しますので、このセルに空白文字列(="")またはスペースなどを入力しておけばエラー処理の代わりになります。
「特定の文字列を含む場合指定の値を返す エ」の回答画像9
    • good
    • 1
この回答へのお礼

有難うございます。
エラー処理の方法まで親切にありがとうございます。
NAが出ちゃうと面倒ですからね。

シンプルな方法で非常に参考になりました。

お礼日時:2013/01/10 14:29

添付図が表示されていないようなのでもう一回



コードは(増やせるけど)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
    • good
    • 0
この回答へのお礼

ありがとうございます。

こういうやり方もありですね。 皆さん色々工夫しますね。 
流石です。 ある意味エクセルの関数の組み合わせなどは
クイズみたいで面白いですね。

お礼日時:2013/01/10 14:30

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文字のほうを優先して、抜き出している

詳細な解説はご要望があれば。
    • good
    • 1
この回答へのお礼

ありがとうございました。

このようなやり方も研究してみます。
まったく思いつきませんでした。

お礼日時:2013/01/10 14:31

こんにちは。



難しく考えすぎずに、
VLOOKUPの第四引数をFALSEにして、

VLOOKUP(検索値, 範囲, 列番号, [検索の型])

検索値にワイルドカードを使用すれば、
VLOOKUP関数自身で解決できませんか。?

例 - 検索値を"み*"にすると、"みどり"等にヒットしますが・・・。

はずしていたら、ごめんなさい。
    • good
    • 1
この回答へのお礼

ありがとうございます。

検索対象の文字列が結構長いのがあったりして、
ワイルドカードだと関係ないのもひっパテしまいます。。

とはいえ、Vlookdでワイルドカードというやり方は
今後活用させていただきます。勉強になりました。

お礼日時:2013/01/10 14:32

関数を使って対応するには、色の種類が少ないのでしたら力技で良いのですが多くなった場合には次のように作業列を作って対応することもできますね。


例えば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行目を利用して自由に増やすこともできますね。
作業列が目障りでしたらそれらの列を選択したのちに右クリックして「非表示」を選択すればよいでしょう。
    • good
    • 0
この回答へのお礼

ありがとうございました。

このような関数の組み合わせの仕方は全く思いつきませんでした。
参考にさせていただきます。

お礼日時:2013/01/10 14:34

ANo.2です。


一つ訂正です。
C3に入れて配列式として確定する式は↓にしてください
=MAX(ISNUMBER(FIND(F$3:F$7,A3))*E$3:E$7)
    • good
    • 0
この回答へのお礼

わざわざ有難うございます。

お礼日時:2013/01/10 14:34

通常の関数では厳しい(私では無理)ので、ユーザ定義関数は如何でしょうか。


シートタブ上で右クリック→コードの表示→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
    • good
    • 1
この回答へのお礼

ありがとうございます。

なるほど・・ ユーザー定義を利用してしまうんですね。
ちょっと面白そうなので勉強してみます。

お礼日時:2013/01/10 14:35

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

QEXCELで、指定セルにある文字列が入っていれば、別のセルに指定文字を入れたい

EXCELで、指定するセルに、例えば「猫」という文字列が入っていれば、
別の指定セルに1という数字をいれ、
入っていなければ、0という数字を入れたいです。

IF関数を使えばよいのかと思ったのですが、
よくわかりません。
すみませんが教えていただけないでしょうか。

Aベストアンサー

指定セルをA1とします。

=IF(A1="猫",1,0)

でOKです。
文字列を見るときは、" "で囲めばいいのです。

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Q複数の文字列のいずれかが含まれていたらTRUEを返す関数について

複数の文字列のいずれかが含まれていたらTRUEを返す関数について
エクセル2003を使用しています。
B列に例えば住所が入力されていて、「東京都北区」「千代田区」「東村山市」等いくつかの文字列のいずれかが含まれていたら、A列にTRUEを返すということをしたいのですが方法がわかりません。
単純なことのような気がするのですがどうしても思いつきません。よろしくお願いいたします。

Aベストアンサー

こうかなあ。。。
A2セル
=OR(SUBSTITUTE(B2,$D$2:$D$4,"",1)<>B2)
[Ctrl]+[Shft] +[Enter] で確定、配列数式です({}で囲まれる)
下へオートフィル

D2:D4に文字列を入力しておく

Qvlookup関数で検索値を含む文字列を検索する方法

vlookup関数で例えば E1のセルに=vlookup(D1,A:C,3,false)とした場合、D1が佐藤であれば、A列に「佐藤」がある場合には当然、「佐藤」がある行の3列目の値が返されますが、「佐藤」はなく「佐藤A」や「A佐藤」がある場合にもこれらがある行の値を返して欲しいのですが、いい方法はありませんでしょうか。*を使ってできると思ったのですがうまくいきません。上記例で、=vlookup("*佐藤*",A:C,3,false)とすればできますが、*佐藤*の部分はD1の引用を利用したいのです。
 よろしくお願いします。

Aベストアンサー

=VLOOKUP("*"&D1&"*",A:C,3,FALSE)

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QExcelで○○を含むという条件にてvlookup処理(比較)できるでしょうか?

説明が難しいのですが、以下のようなAとBの2つのシートがありまして、BシートのA列にある条件がAシートのA列の歯抜の状態で入力されてます。これにBシートのB列に、AシートのB列を表示したいという状況があります。

具体的なイメージは、以下の通りです。

===========================================
Aシート
 A        B
1 山田真太郎 東京
2 鈴木波奈子 名古屋
3 斉藤ミツル 大阪
-------------------------------------------
Bシート
 A  
1 藤ミツ
2 奈子
3 田真太
===========================================


BシートのB列に関数などを利用して以下のように表示させたいです。
==========================================
 A     B
1 藤ミツ 大阪
2 奈子  名古屋
3 田真太 東京
==========================================

このように、「セルにある言葉を含む」という条件にて、Vlookupを使ったような計算式というのを組む事ができるでしょうか?

私が感じているBシートのB1に入れる関数イメージ
=VLOOKUP((*A1*),A!$A$1:$B$3,2)

何か良い方法があればご教授ください。

説明が難しいのですが、以下のようなAとBの2つのシートがありまして、BシートのA列にある条件がAシートのA列の歯抜の状態で入力されてます。これにBシートのB列に、AシートのB列を表示したいという状況があります。

具体的なイメージは、以下の通りです。

===========================================
Aシート
 A        B
1 山田真太郎 東京
2 鈴木波奈子 名古屋
3 斉藤ミツル 大阪
-------------------------------------------
Bシート
 A  
1 藤ミツ
2 奈子
3 田真...続きを読む

Aベストアンサー

NO2です。
因みに=VLOOKUP("*"&A1&"*",A!A:B,2,FALSE)でも可能ですのでお試しください。

Q【Excel】複数の列を参照して、特定の文字列を含む行全体を別シートに抽出する関数

左画面(Book2)のようなシートがあります。

Book2の中で列D・列F・列Iの3列を参照し、別シート(Book4)のA1セルに入力した文字列と一致する文字を含む行全体を、Book4のA3セル以下に抽出するための関数を探しています。

画像の例は、「北海道」という文字列を3列のいずれかに含む行だけを手動で貼り付けたものなのですが、この処理を関数を用いて自動で行いたいのです。

参照したい文字列(北海道)は、3つの列にランダムに含まれている状態です。

元データ(Book2)を更新すれば、別シート(Book4)がリアルタイムに自動で生成されるようにしたいので、関数を使った方法で行いたいと思っています。

ご教示お願い申し上げます。

Aベストアンサー

こんばんは!
一例です。
元データは「Book2」のSheet1にあり、表示するのは「Book4」のSheet1とします。
Book4・Sheet1のA1セルに検索したい文字列を入力し、そのデータを表示させます。
(今回は画像通り”北海道”と入力しておいてください)

Book2のSheet1のJ列を作業用の列として使用します。
J3セルに
=IF(COUNTIF(D3:H3,[Book4.xlsx]Sheet1!$A$1),ROW(),"")
という数式を入れフィルハンドルでこれ以上データはない!という位まで下へコピーしておきます。

そしてBook4、Sheet1のA4セルに
=IFERROR(INDEX([Book2.xlsx]Sheet1!A:A,SMALL([Book2.xlsx]Sheet1!$J:$J,ROW(A1))),"")
という数式を入れ、列・行方向にフィルハンドルでコピーしてみてください。

※ 作業列が目障りであれば遠く離れた列に設けるか
非表示にしておきます。m(_ _)m

Qあるセルに特定の文字列を打つと、他のセルに決められた文字が自動入力するように

お世話になります。

表題の通り、
あるセルに特定の文字列を打つと、
他のセルにあらかじめ決められた文字が自動入力するようにしたいです。

具体的に言うと、
(1)セル(A,1)に「キリン」と打ち込むと

   A   B   C   D
1 キリン
2
3
4

(2)1列目の B,C,Dに予め決めておいた文字が入力されるようにしたい

   A   B   C   D
1 キリン 首  長い  アフリカ
2
3
4

のです。
エクセルで可能でしょうか?
詳しい方よろしくお願いいたします!

Aベストアンサー

出来ます。この場合はキリンがキーになっていますね。
先ずこのキーをもとにSheet2にデータを登録しておきます。
   A   B   C   D
1 キリン 首  長い  アフリカ
2 ゾウ  鼻  長い アフリカ
3 
4
次に表示させたいセルに
 A   B             C           D
1  =VLOOKUP(A1,Sheet2!A:D,2) =VLOOKUP(A1,Sheet2!A:D,3) ″ 
2  =VLOOKUP(A2,Sheet2!A:D,2) =VLOOKUP(A2,Sheet2!A:D,3) ″
3  =VLOOKUP(A3,Sheet2!A:D,2) =VLOOKUP(A3,Sheet2!A:D,3) ″
4  =VLOOKUP(A4,Sheet2!A:D,2) =VLOOKUP(A4,Sheet2!A:D,3) ″

を入れておきます。
これで出来ると思います。
エラー表示がいやな場合は、=IF(A1="","",VLOOKUP(A1,Sheet2!A:D,2)) の様にして下さい。

出来ます。この場合はキリンがキーになっていますね。
先ずこのキーをもとにSheet2にデータを登録しておきます。
   A   B   C   D
1 キリン 首  長い  アフリカ
2 ゾウ  鼻  長い アフリカ
3 
4
次に表示させたいセルに
 A   B             C           D
1  =VLOOKUP(A1,Sheet2!A:D,2) =VLOOKUP(A1,Sheet2!A:D,3) ″ 
2  =VLOOKUP(A2,Sheet2!A:D,2) =VLOOKUP(A2,Sheet2!A:D,3) ″
3  =VLOOKUP(A3,Sheet2!A:D,2) =VLOOKUP(A3,Sheet2!A...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング