A列
1_223
01_432
22_02
以上のような文字列で
1
1
22
の数字だけをB列に切り出し
C列 D列
1 北海道
22 岡山県
を参照して
E列にB列の答えを一度の作業で
表記できるエクセルのマクロまたは関数を教えてください。
E列
北海道
北海道
岡山県
マクロで作ってみました。
Sub main() Dim b As Long Dim e As Long For b = 1 To Range("A1").End(xlDown).Row c = Cells(b, "A") If InStr(1, c, "_") > 0 Then d = Left(c, InStr(1, c, "_") - 1) d = Val(d) For e = 1 To Range("C1").End(xlDown).Row If Cells(e, "C") = d Then Cells(b, "E") = Cells(e, "D") Exit For End If Next e End If Next b End Sub
B1の数式を次のようにして下にコピー。
=VALUE(LEFT(A1,FIND("_",A1)-1))
E1の数式を次のようにして下にコピー。
=VLOOKUP(B1,C:D,2,FALSE)
ありがとうございます。
B列の関数はA列にデータがあるのでデータの終わりまでB2でダブルクリックしてコピーできますが
E列はC列D列のデータが47行しかないためそこで終わってしまいます。
なにか一度にA列のデータの最後の位置までコピーする方法はありますか?
列の位置は変更になってもかまいません。
B列へは、
=VALUE(LEFT(A6,FIND("_",A6)-1))
おそらくA列のアンダーバーまで抜き出したものを
キーとして使っていると推測できるので、
あとは数値化します。
E列には、
=VLOOKUP($B6,$C$6:$D$7,2,FALSE)
VLOOKUP関数を使い、
Bをキーにして、C列D列のデータを検索し、D列の都道府県名を返します。
ありがとうございます。
参考にさせていただきます。
以下の方法はいかがでしょうか?
1. B1 に
=VALUE(LEFTB(A1,FINDB("_",A1,1)-1))
を入れて、B3までコピー。
2. E1 に
=VLOOKUP(B1,$C$1:$D$2,2,TRUE)
を入れて、E3までコピー。
ありがとうございます。
参考にさせていただきます。
関数だけでも可能なので関数のほうでクリアしてみましょう。
今回はいらないですが、右側の文字列についても書いておきます。
使うのは、
考え方
01_432がA1にあるとして考えます。
①文字列を分けたい。これはleft関数、right関数を使います。
=left(A1,2) ・・・・・これをαとします
=right(A1,3) ・・・・・これをβとします
でクリア。次はこれを関数で表現
②α、βをアンダーライン"‗"で分けたい。これはfind関数で"_"を見つけます。
=find("_",A1)とすると、3と出ます。leftでは2,rightでは3にする必要がある。
なので、
=left(A1,find("_",A1)-1)・・・・・これをχとします
=len(A1)で、7が取得できるのでこれを使う。
=right(A1,find("_",len(A1)-find("_",A1))・・・・・これをδとします
χは01、δは432と出力されました。
③次は、01を1にしましょう。これはint関数を使います。
χを以下のように書き換えます。int関数で包み込んだだけ。
=int(left(A1,find("_",A1)-1))
δも同様に
=int(right(A1,find("_",len(A1)-find("_",A1)))
数値になりましたよね?
以下、
χがB1にあるものとします。
リストは、C1からD5までとします。
④χを元にC列を参照し、D列の値を表示したい。
ここで=Vlookup関数を使います。
=vlookup(B1,C1:D5,2,FALSE)
④をもってクリアできていると思うのですがどうでしょうか。
何がわからないのかが分からない状態だったり、
何ができるのかわからない状態だとだとなかなか調べにくいですよね。
VBAを覚えるにしてもExcelの関数を一通り押さえておく必要がありますので、この回答にわからない関数があればぜひ調べてみてください。
とくにVlookup関数は頻度が高いものだと思いますので、ぜひ覚えちゃってください。
方向が違うだけのHlookup関数というものもありますよ。
ありがとうございます。
参考にさせていただきます。
マクロで作ってみました。
Sub main() Dim b As Long Dim e As Long For b = 1 To Range("A1").End(xlDown).Row c = Cells(b, "A") If InStr(1, c, "_") > 0 Then d = Left(c, InStr(1, c, "_") - 1) d = Val(d) For e = 1 To Range("C1").End(xlDown).Row If Cells(e, "C") = d Then Cells(b, "E") = Cells(e, "D") Exit For End If Next e End If Next b End Sub
ありがとうございます。
これで完璧です。
うまく行きました。
マクロでしたらB列は使わなくてよいのでしょうか?
ありがとうございます。
これで完璧です。
うまく行きました。
マクロでしたらB列は使わなくてよいのでしょうか?