* すぐに回答を!

エクセルで前方一致のVlookupのような関数はないでしょうか。
具体的には、A列とB列にデータがあり、A列のデータの文頭部分の文字列が
B列のデータのどれかと一致した場合に、C列に一致したB列のデータを返したいです。
データの行数が非常に多い(3,4万行)であるため、できるだけ動作の軽い
関数であればなお助かります。

A          B           C
zzzfewfe      dadaf         zzzf
dadaf3233      a11111k        dadaf
aabbbb-fefe3     zzzf         aabbbb
a11111k33r3      aabbbb       a11111k

宜しく御願い致します。

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

A 回答 (4件)

こんばんは。



私なら、並べ替えをして比較しているのではないかと思いました。
3~4万行ですから、マクロでも、総当たり制では無理があると思います。

そこで、配列に入れてみました。なるべくスピードに心がけて作ってみました。

'標準モジュール


Sub Test1()
  Dim i As Long, j As Integer
  Dim rng As Range
  Dim rng2 As Range
  Dim ar As Variant
  Dim ar2() As String
  Set rng = Range("A1", Range("A65536").End(xlUp))
  Set rng2 = rng.Offset(, 1)
  ar = Application.Transpose(rng.Value)
  ReDim ar2(UBound(ar))
  For i = LBound(ar) To UBound(ar)
    For j = Len(ar(i)) To 1 Step -1
      If Application.CountIf(rng2, Left(ar(i), j)) Then
        ar2(i - 1) = Left(ar(i), j)
        Exit For
      End If
    Next j
  Next
  '書き出し場所
  Range("C1").Resize(UBound(ar2())).Value = Application.Transpose(ar2())
  Set rng2 = Nothing
  Set rng = Nothing
End Sub
    • good
    • 0

数式は少し重そうですが、文字数が例示程度なら



D1セルに
=MATCH(LEFT($A1,COLUMN(A1)),$B:$B,0)
右へ下へオートフィル(B列の文字数の最大まで
例示の場合 7文字なのでJ列まで)
文字数はLEN関数で調べます

C1セルに
=IF(ISNA(LOOKUP(2^8,D1:J1)),"",INDEX(B:B,LOOKUP(2^8,D1:J1)))
下へオートフィル
    • good
    • 0

関数よりマクロの方が簡単で速いと思う


一例です

Sub Macro1()
Dim i
For i = 1 To Range("b65536").End(xlUp).Row
Range("a1", Range("a65536").End(xlUp)). _
Find(What:=Cells(i, 2).Value, LookIn:=xlValues, LookAt:=xlPart) _
.Offset(0, 2).Value = Cells(i, 2).Value
Next i
End Sub
    • good
    • 0

B列の文字数は不定なんですよね


関数では無理そうです

VBAならこんな↓感じです
---------
Sub TS()
 al = 1
 Do
  bl = 1
  Do
   mjl = Len(Cells(bl, 2))
   If Left(Cells(al, 1), mjl) = Cells(bl, 2) Then
    Cells(al, 3) = Cells(bl, 2)
    Exit Do
   End If
   bl = bl + 1
  Loop Until Cells(bl, 2) = ""
  al = al + 1
 Loop Until Cells(al, 1) = ""
End Sub
----------
    • good
    • 0
この回答へのお礼

おっしゃるとおり文字数は不定です。
やはりVBAを使わないと駄目そうなんですね。
有難うございます。

お礼日時:2009/05/26 23:05

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

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

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

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

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)

QVLOOKUP関数 、住所、 部分一致 (困っています。至急即応頂けると助かります)

初歩的な質問で申し訳ございません。
御存じの方いらっしゃいましたら、ご教授頂きたく思います。

ただいまVLOOKUP関数を使い、A列の住所に対応するコードを
K列・L列の元の表と対応させながら、、B列に振ると言う作業をしております。
     A                  B       K        L
 1 横浜市港南区○町1-2-1             地区       コード
 2 横浜市金沢区△町1-2-3            横浜市緑区    横浜A
 3  横浜市都筑区□町4-5-7            横浜市港南区  横浜B
                               横浜市金沢区  横浜C           
                               
A列とK列の値が完全一致(吻合)している場合は、上手くいくのですが、
上記のようにA列は全住所でK列が住所の一部と言うように、両者の値に齟齬
 がある場合、検索方法を「TRUE」にしても、 ワイルドカードを使っても、Bに適 
 確なコードをふることができません。
 また複数の関数を駆使し、新たな関数を拵えるほど、エクセルに通暁しておりま せん。
 解決法がありましたら、何卒お教え下さい。期限が切迫している状況です。
 宜しくお願い申し上げます。

初歩的な質問で申し訳ございません。
御存じの方いらっしゃいましたら、ご教授頂きたく思います。

ただいまVLOOKUP関数を使い、A列の住所に対応するコードを
K列・L列の元の表と対応させながら、、B列に振ると言う作業をしております。
     A                  B       K        L
 1 横浜市港南区○町1-2-1             地区       コード
 2 横浜市金沢区△町1-2-3            横浜市緑区    横浜A
 3  横浜市...続きを読む

Aベストアンサー

ワイルドカードの逆引きになるので,綺麗なVLOOKUPでは解決できません。


作成例(但し前方一致の場合):
  K列     L列
1 空白     該当無し
2 地区     コード
3 横浜市緑区  横浜A
4 横浜市港南区 横浜B
5 横浜市金沢区 横浜C


  A列            B列
1 横浜市港南区○町1-2-1

B1:
=INDEX(L:L,MIN(IF(ISERROR(FIND($K$3:$K$5,A1)),"",ROW($K$3:$K$5))))
と記入し,コントロールキーとシフトキーを押しながらEnterで入力する

QExcelで文字列の中から部分一致したものを抽出

エクセルで特定の文字を含むセルを抽出する方法を探しています。

A列に元データ、C列に検索条件があります。
C列の条件に一部でも一致するデータを抽出したいです。(結果イメージを添付します)

可能であれば、関数で対応したいです。

どなたかお分かりになる方いらっしゃいましたら、
教えていただけないでしょうか?

よろしくお願いいたします。

Aベストアンサー

こんにちは!
A列は当然文字列だと思いますが、C列の日付セルがシリアル値だと厄介です。
とりあえずC列の日付セルは「文字列」だという前提での一例です。

画像の配置ではB2セルに
=IF(OR(ISNUMBER(FIND(C$2:C$6,A2))),"○","")
これは配列数式になりますので、Ctrl+Shift+Enterで確定!
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → B2セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定!
数式の前後の{ }マークが入り配列数式になります。
これをフィルハンドルで下へコピーしてみてください。

※ 検索データ(C列データ)が増える場合は元データにまず使うことはないであろうと思われる
「*」(アスタリクス等の文字、#のような文字でも構いません)を入力しておき、数式内の
>C$2:C$6
部分の範囲を広げておきます。
そして検索データが増えた場合はアスタリクスの代わりに検索文字列を入力します。
(空白セルは範囲指定しないようにするためです)

※ C列日付データがシリアル値の場合、別の方法を考える必要があります。m(_ _)m

こんにちは!
A列は当然文字列だと思いますが、C列の日付セルがシリアル値だと厄介です。
とりあえずC列の日付セルは「文字列」だという前提での一例です。

画像の配置ではB2セルに
=IF(OR(ISNUMBER(FIND(C$2:C$6,A2))),"○","")
これは配列数式になりますので、Ctrl+Shift+Enterで確定!
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → B2セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定!
数式の前後の{ }マークが...続きを読む

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

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

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

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

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

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

宜しくお願いします。

Aベストアンサー

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

でどうでしょうか?

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)でも可能ですのでお試しください。

Q2つのシート間での重複データのチェック

Excelについて教えてください。
以下の2つのシートがあります。

Sheet1
すずき 03-0000-0000
やまだ 03-1111-1111
たなか 03-2222-2222

Sheet2
03-1111-1111
03-4444-4444
03-0000-0000
03-2222-2222

Sheet1には名称と電話番号、Sheet2には別所から抽出した電話番号のみのデータがあります。
Sheet1のデータの中からSheet2に電話番号があるものだけを知りたいのですが、たとえばSheet1の 各行のC列 に Sheet2に一致する番号があった場合は●等付けるにはどうすればよいでしょうか。

よろしくお願いします

Aベストアンサー

シート1のC列に式を入れます。
=IF(ISNA(VLOOKUP(B1,Sheet2!A:A,1,FALSE))=TRUE,"-","●")

【式の説明】
シート1のセルB1の値が、シート2のA列に、存在する場合は●を、存在しない場合は-をセット。

QSUMIFS関数でOR条件を使いたい場合の関数

Excel2010を使用しています。
家計簿を作成しているのですが、SUMIFS関数でOR条件を使用したい場合どのように書けばよいでしょうか。

図は、明細と合計欄です。
ピンクのセルが円、それ以外はドルです。
合計欄のように、円とドルをそれぞれ分けて項目毎の合計額を出したいです。
セルE21のように、円の場合は「MUFJ」「ゆうちょ」ですので、MUFJとゆうちょのSUMIFS関数合計値を足しています。
これを、1行にまとめたいのですが、適切な関数はないでしょうか?
SUMIFSではOR条件が使えないようですので・・・・・・

明細行に円・ドルの列を追加すれば簡単なのですが、明細行の列はこれ以上増やしたくないので、できれば数式のほうで処理できればと思っています。

よろしくお願いいたします。

Aベストアンサー

例示のデータなら以下のような数式になります。

=SUM(SUMIFS($C$2:$C$14,$D$2:$D$14,{"MUFJ","ゆうちょ"},$B$2:$B$14,B21))

QEXCELで、特定の文字を含むセルを抽出したいのですが。

仕入れ帳のようなシートで、B列に商品名があります。
商品名に特定の文字(数字又は文字)を含む時、空白列のC列に、その文字を抽出する方法をご教示ください。B列の商品名は変化させずにです。
意図は、当シート内で並べ替えをする時に、C列をキーとするためです。
商品名が、各種文字と数字で構成されており、そのままでは、希望する並べ替えができないのです。
宜しくお願い致します。

Aベストアンサー

「IF」関数、「ISERR」関数、「FIND」関数を組合せれば可能ですね。
たとえば、文字「a」を検索するなら、B2セルを対象として、C2セルに、

=IF(ISERR(FIND("a",B2)),"","aが入っています")

と入れます。

注意事項として、アルファベットを検索する場合、大文字と小文字を区別します。
つまり、上の例ではB2セルに「a」があれば、「aが入っています」、と表示されますが、「A」では表示されません。
(「FIND」を「SEARCH」にすれば「a」・「A」どちらでも表示される)

QexcelのVLOOKUPで検索値を2つにできますか?

excelのVLOOKUPで検索値を2つにしたいです
私の知っているVLOOKだと下記のことは対応できます
例えば、A列に会社名、B列に住所、C列に電話番号 とあった場合
「住所が検索値と同一なら電話番号を表示しなさい」という指示は出せます

そこで質問です
「会社名と住所が検索値と同一なら電話番号を表示しなさい」
というような、複数の検索値を持つ事はできないのでしょうか?

参考になるURLなどでも結構ですので、ご存知の方よろしくお願いいたします

Aベストアンサー

元の表をA列に会社名、B列に住所、C列に「=A2&B2」、D列に電話番号のようにして
=VLOOKUP(会社名&住所,$D$2:$C$100,2,FALSE)
のようにすれば可能です。


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

人気Q&Aランキング

おすすめ情報