ウォーターサーバーとコーヒーマシンが一体化した画期的マシン >>

いつもお世話になっています。

Excelで「区」を抽出する関数を教えていただけませんでしょうか。
幾つか調べましたが、参考にしながらも上手く作る事ができませんでした。

神奈川県川崎市幸区●●●●●123456 ⇒ 幸区
東京都港区●●●●●●123456 ⇒ 港区

そして、市があって区が無い場合は「市」を抽出したいです。
神奈川県大和市●●●●●●123456 ⇒ 大和市

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

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

A 回答 (10件)

No4の方の


全国の市町村コード一覧
http://citycode.fmd4.com/home
を利用してみました。
Sheet2に上記サイトのデータを貼り付け
市町村名にある空白を置き換えの機能で削除
画像にあるようなデータにします。

A列に住所があるとして
B列に 県名を表示する式
=IF(MID(A2,4,1)="県",LEFT(A2,4),LEFT(A2,3))
県名を表示させるのは簡単です。
C列に市町村名を表示させるにはSheet2の市町村名のデータを利用して
=MID(A2,LEN(B2)+1,MAX(IF(COUNTIF(Sheet2!D:D,LEFT(REPLACE(A2,1,LEN(B2),""),COLUMN(A1:H1)))>0,COLUMN(A1:H1),0)))
と入れて Ctrl+Shift+Enter で決定させて
B、C列を下までコピーします。
「住所から「区」を抜き出すには」の回答画像10
    • good
    • 0

こんばんは。



私のほうは、正規表現でやってみたつもりが、全国レベルになるとうまくいかないものが出てきて、結局、個別処理なんていう方法を取らざるを得ませんでした。とても、褒められたコードではありませんが、せっかくエラーのでないレベルに達したもので、公開しておきます。一応、このコードの味噌は、最長マッチと最短マッチの妙ですね。

ただし、
 東京都利島村利島村一円
等の島で、村が最初に来るもの対応していません。

'//
Sub Test1()
 Dim s As Long, k As Long, g As Long, kn As Long, s2 As Long, d As Long, m as Long, t as Long
 Dim c As Range
 Dim buf As String
 Application.ScreenUpdating = False
 With CreateObject("VBScript.RegExp")
  For Each c In Range("A1", Cells(Rows.Count, 1).End(xlUp))
   buf = c.Value
   buf = SpecialArrange(buf, c)
   g = InStr(buf, "郡"): s = InStr(buf, "市")
   k = InStr(buf, "区"): kn = InStr(buf, "県")
   d = InStr(buf, "北海道"): s2 = InStr(s + 1, buf, "市")
   m = InStr(buf, "町"): t = InStr(buf, "都")
   
   If (k > 0 Or t > 0) And s2 = 0 And m = 0 And s = 0 And (s - k = 1 Or g - k = 1 Or s - k > 1) Then
    .Pattern = "[都道府県](.*[区市])"
   ElseIf g > 0 And g - kn > 1 And g - d > 1 And g - s > 1 And s2 = 0 And s * g <> 0 And s < g Then
    .Pattern = "[都道府県]([^市]*[市])"
   ElseIf g > 0 And g - kn > 1 And g - d > 1 And (s = 0 Or g - s > 1) Then
    .Pattern = "[都道府県]([^郡]*[郡])"
   ElseIf g > 0 And s > 0 And s - g = 1 Then
    .Pattern = "[都道府県](.+市)" '蒲郡市
   ElseIf g > 0 And s > 0 And g - kn > 1 And (g > d) And (g < s Or g < s2) Then
    .Pattern = "[都道府県]([^郡].*郡)"
   ElseIf g > 0 And k = 0 And s - kn = 1 Then
    .Pattern = "[都道府県](.*市)" '市原市
   ElseIf s2 > 0 And k = 0 And s - kn = 1 Then
    .Pattern = "[都道府県](市[^市]*市)" '市原市
   ElseIf g > 0 And k = 0 And g - kn > 1 And s < g Then
    .Pattern = "[都道府県]([^市].*市)"
   ElseIf g > 0 And k = 0 And g - kn > 1 Then
    .Pattern = "[都道府県]([^郡市町].*[郡市町])"
   ElseIf g > 0 And g - kn = 1 Then '郡山仕様
    .Pattern = "[都道府県]([^市町]*[市町])"
   ElseIf (d > 0 Or k > 0) And s > 0 And s2 = 0 And g = 0 Then
    .Pattern = "[都道府県](.*市)"
   ElseIf s2 - s = 1 Then
    .Pattern = "[都道府県]([^市]+市市)" '野々市
   ElseIf s2 - s = 1 Then
    .Pattern = "[都道府県]([^市].*市)"
   ElseIf s > 0 And s2 > 1 And m = 0 And s2 - s > 1 And s - kn > 1 Then
    .Pattern = "[都道府県]([^市]*市)"
   ElseIf s > 0 And m = 0 And (s2 - s > 1 Or s2 = 0) And s - kn > 1 Then
    .Pattern = "[都道府県]([^市].*市)"
   ElseIf s > 0 And m > 0 And s - kn > 1 Then
    .Pattern = "[都道府県]([^市]*市)"
   ElseIf s2 > 0 Then
    .Pattern = "[都道府県](.*市)"
   ElseIf g = 0 And k > 0 Then
    .Pattern = "[都道府県]([^区].*区)"
   ElseIf g > s Then
    .Pattern = "[都道府県]([^郡].*郡)" '余市郡
   ElseIf InStr(c.Value, "利島村") = 0 Then
    .Pattern = "[都道府県]([^郡市町区].*[郡市町区])"
   Else
   'unfixed
   End If
   .Global = True
   On Error Resume Next
   If .Test(buf) Then
    With .Execute(buf)(0)
     buf = .SubMatches(0)
     c.Offset(, 1).Value = buf
    End With
   End If
   If Err.Number > 0 Then c.Offset(, 1).Value = ""
   On Error GoTo 0
  Next c
  Application.ScreenUpdating = True
 End With
End Sub
Function SpecialArrange(buf As Variant, rng As Range)
  '個別対応
   If InStr(buf, "市市場") > 0 Then
    buf = Replace(buf, "市市場", "市")
   ElseIf InStr(buf, "今市市") > 0 Then
    rng.Offset(, 1).Value = "今市市"
    buf = ""
   ElseIf InStr(buf, "余市町") > 0 And InStr(buf, "郡") = 0 Then
    rng.Offset(, 1).Value = "余市町"
    buf = ""
   ElseIf InStr(buf, "郡市") > 0 Then '栃木県芳賀郡市貝帳
    buf = Mid(buf, 1, InStr(buf, "郡市"))
   ElseIf InStr(buf, "四日市市") > 0 Then '奈良県大和郡
     rng.Offset(, 1).Value = "四日市市"
     buf = ""
   ElseIf InStr(buf, "市市野") > 0 Then '佐渡市市野沢
    buf = Mid(buf, 1, InStr(buf, "市市"))
   ElseIf InStr(buf, "市市") > 0 Then '佐渡市市野沢
    buf = Mid(buf, 1, InStr(buf, "市"))
   ElseIf InStr(buf, "市郡中") > 0 Then '郡中新田
    buf = Replace(buf, "市郡中", "市")
   ElseIf InStr(buf, "臼杵市市浜") > 0 Then '臼杵市
    buf = Replace(buf, "臼杵市市浜", "臼杵市")
   ElseIf InStr(buf, "大和郡") > 0 Then '奈良県大和郡
     buf = Mid(buf, 1, InStr(buf, "大和郡") + 2)
   ElseIf InStr(buf, "四日市市市場") > 0 Then '奈良県大和郡
     buf = Replace(buf, "四日市市市場", "四日市市")
   End If
  SpecialArrange = buf
End Function
    • good
    • 0

VLOOKUPで拾える一覧表作ってみました



No4で提示した市町村コード表をコピーして
http://citycode.fmd4.com/home

新規ブックsheet1のA1セルに貼り付けた後に下記マクロを実施してください

Sub sample()

Sheets("Sheet1").Range("A1").Select
Rows("1:4").Select
Selection.Delete Shift:=xlUp
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("D1").Select
ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-2]&RC[-1]"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D1935")
Range("D1:D1935").Select
Range("E1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],5)"
Range("F1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-2],8)"
Range("G1").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND(""区"",RC[-4])),RC[-5],RC[-4])"
Range("E1:G1").Select
Selection.AutoFill Destination:=Range("E1:G1935")
Range("E1:G1935").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((RC[-7]&RC[-1]=R1C1:RC[-7]&R1C7:RC[-1])*1)"
Selection.AutoFill Destination:=Range("H1:H1935")
Range("H1:H1935").Select
ActiveWindow.SmallScroll Down:=-12
Range("H1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$H$1935").AutoFilter Field:=8, Criteria1:="1"
Range(Selection, Selection.End(xlDown)).Select
Range("E1:H1934").Select
Range("H1").Activate
ActiveWindow.SmallScroll Down:=-177
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Delete Shift:=xlToLeft

End Sub

sheet2に一覧表ができますので
セル範囲 A1:C1358 に表5
セル範囲 B1:C1358 に表8
と名前を定義してください

=IFERROR(VLOOKUP(LEFT(住所,8),表8,2,0),VLOOKUP(LEFT(住所,5),表5,3,0))

これで抽出可能になるはずです

今後市町村合併等があっても最新の市町村コード表から対応表を作り直せば、対応可能かと思われます
    • good
    • 0

No4です、ちょいと補足



全国的に見ると

北海道余市町
山形県東村山郡
山形県西村山郡
山形県北村山郡
千葉県市原市
千葉県市川市
東京都町田市
東京都東村山市
東京都武蔵村山市
新潟県十日町市
福島県郡山市
群馬県北群馬郡
長野県大町市
広島県廿日市市
愛知県四日市市

思いつくところをざっと上げてみた

まだあるかもしれないけど
この辺りの市町村郡の文字が含まれている市町村群の扱いをどう処理するか

市と区のみの抽出まで進んだところで、止まってしまった
=IFERROR(MID(E5,MAX(4,IFERROR(FIND("市",E5),0)+1),FIND("区",E5)),MID(E5,(MID(E5,4,1)="県")+4,FIND("市",E5,5)-(MID(E5,4,1)="県")-3))
    • good
    • 0

若干の手直しが必要かもしれませんけど、こちらで試してみたら?



住所を分割する
http://menushowdelay.blog.fc2.com/?no=525
    • good
    • 0

[No.1補足]へのコメント、


》 その場合は、空白にする事はできますでしょうか?
そのこと自体は簡単だけど、下記のような実在する住所の場合にも対応させなきゃならんとすると、私にはお手上げです。
北海道河東郡音更町柳町南区
北海道河西郡更別村旭区
福井県坂井市春江町為国中区
兵庫県多可町中区
岡山県岡山市南区北七区
    • good
    • 0

ネット上にはこんなものがあります



全国の市町村コード一覧
http://citycode.fmd4.com/home

こういった物をうまく使うとやりやすいでしょう

例えば"市"だけで探そうとすると千葉県市原市とか市川市とかは変なところで区切られてしまう

一覧表を作ってVLOOKUP関数で拾い出すとか
何かしらの規則性を見つけ出すとか

東京都の場合であれば6文字目まで取れば区別は付きそうですね

直接の回答にはなっていません、あしからず
    • good
    • 0

・四日市市の場合最初の”市”で四日市と判定するので無理

    • good
    • 0

Q、「区」を抽出する関数?


A、ほとんど不可能。

一度、そういう関数の作成に挑戦したことがあります。

Step1、全国の地名を郵便番号一覧で調査・分析。

まず、ここで・・・。

都郡市山市

などの例外的サンプルを抽出することから。結果、相当に、この類の地名・住所が存在することが判明。

【結果】郵便番号簿解析ツールを利用するのが一番確実。
    • good
    • 0

》 市があって区が無い場合は「市」を抽出したい


市も区もない場合は、どうするどうする?

この回答への補足

mike_gさん
ありがとうございます。市も区もない場合があるのですね。身近になかったので想定すらしていませんでした...。

その場合は、空白にする事はできますでしょうか?よろしくお願いいたします。

補足日時:2015/01/11 21:23
    • good
    • 0

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

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

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

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

Q【Excel】住所から市区町村名を抜き出す方法

いつもお世話になります。セル内に住所が入力してあり、その住所を元に地方公共団体コードをつけたいと思います。地方公共団体コードとは市区町村ごとに充てられた6桁の数字です。
A列に住所が入力してあります。(県名は入力してありません。)次にB列に以下のような式を入力しました。
=if(find("区",a1),left(a1,find("区",a1)),if(find("郡",a1),left(a1,find("郡",a1)),if(find("市",a1),left(a1,find("市",a1)),"")
A列に区が含まれていたら、左から区の部分までをぬきだし、偽なら郡を探し、真ならば左から郡のところを抜き出し、偽なら市を探し、真ならば左から市のところを抜き出し偽ならば何もしない。
すごく、分かりづらいかもしれませんが・・・。ごめんなさい。
うまくいかないのですが、良い方法はありませんでしょうか?

Aベストアンサー

#02です
>、"郡"の場合は抽出する文字列を郡ではなく"町"もしくは"村"で

=IF(ISERROR(FIND("郡",A1)),LEFT(A1,MAX(IF(ISERROR(FIND({"区","市"},A1)),0,FIND({"区","市"},A1)))),LEFT(A1,MAX(IF(ISERROR(FIND({"町","村"},A1)),0,FIND({"町","村"},A1)))))

完璧にはできませんが、IF文繰り返しより少しはマシでしょうか

Q詳細な住所から、行政区だけを抽出するやり方

エクセルにおいて、
都道府県から市区町村郡、詳細なマンションの住所が
一つのセルに入力されています。
そこから、行政区だけを隣のセルに抽出する方法が
ありますでしょう??

いろいろな関数を検索していますが、いまいち
検索キーワードが見つかりません><
とうとつな質問ですが、ご教授お願いいたします。

Aベストアンサー

こんな感じで。

参考URL:http://oshiete1.goo.ne.jp/qa4522517.html

Q所在地の市区郡だけを表示させたい。エクセル関数

所在地の市区郡だけを表示させたい。エクセル関数
市区郡から●丁目まで表示している所在地データ(全国 約2000件)があります。
都道府県名の表示はありません。
このデータを東京都の23区26市4郡にそれぞれ分けるため、はじめに市区郡だけを表示させようと思い、
LEFT関数とIF関数等を考えましたが、市区郡までの文字数が違っており、混乱しています。
お手数ですが、ご教授願います。

Aベストアンサー

全国2000件の住所データから、東京都の区市郡に属するものを探し
てそれぞれ区別できるマーキングをしたいということで、該当する
区市郡だけを抜き出して表示するような列を作れればいいですね。
そうすれば後は並べ替えてチョイチョイですから。

まずは、抽出したい区市郡名の一覧表を用意して下さい。五十音順
に並んでいる必要はありません。「一覧」と名前を定義したことに
します。

住所データからleft関数で何文字か取り出して、一覧に一致したら
ビンゴなんですが、何文字抜き出せばいいのか判りませんね。一番
短いのが北区の2文字、長いのは武蔵村山市やあきる野市の5文字で
一定しません。そこで、2文字から5文字抜き出した配列を作り、そ
れぞれ一覧で検索して一致するものが見つかった一番長い文字数を
採用することにします。たとえば「国分寺市戸倉1丁目」という住所
から{国分;国分寺;国分寺市;国分寺市戸}という配列が出来て、それ
をmatch関数で一覧から探すと{#n/a;#n/a;35;#n/a}のように一致し
ないところが#n/aエラーになるので、チョイチョイと加工して4とい
う数値が得られ、この行は4文字抜き出せばいいなというふうに結論
します。東京じゃない住所だったら最終的に0文字抜き出すので空白
になると。その式はこちら

=left(A1,max(not(isna(match(left(A1,{2;3;4;5}),一覧,0)))*{2;3;4;5}))

説明のわりに短いですね。

全国2000件の住所データから、東京都の区市郡に属するものを探し
てそれぞれ区別できるマーキングをしたいということで、該当する
区市郡だけを抜き出して表示するような列を作れればいいですね。
そうすれば後は並べ替えてチョイチョイですから。

まずは、抽出したい区市郡名の一覧表を用意して下さい。五十音順
に並んでいる必要はありません。「一覧」と名前を定義したことに
します。

住所データからleft関数で何文字か取り出して、一覧に一致したら
ビンゴなんですが、何文字抜き出せばいいのか判りません...続きを読む

QExcelで住所を2つ(町名迄と番地以降)に分けるには?

お世話になります。
Winxp、Excel2003を使用しています。
以下のような住所データがあります。
これを、町名迄と番地以降の2つのセルに分けるには、どうすれば良いでしょうか?よろしくお願いします。

セルA1に東京都小平市東町5-8-15
      ↓  
セルA2には東京都小平市東町
セルA3には5-8-15
としたい。

Aベストアンサー

こんにちは~

A2セルに
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890))-1)

A3セルに
=SUBSTITUTE(A1,A2,"")

と入れてみてください。

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で1セル内の住所を市郡町街と丁目以下の地番等に分離する方法は?

Sheet1のA列に例えば群馬県内の住所のリストがあります。(架空の住所です。)
前橋市池端町3-2-21……(セルA1)
桐生市黒保根町水沼7-3-5
高崎市大沢町1-18-204
沼田市佐山町8-6-5-402
勢多郡北橘村下箱田396
多野郡吉井町岩井27-5
以下1,000件程度

Sheet2のA1:B1538に郵政公社からダウンロードした群馬県内の郵便番号一覧表があり、A列に市郡町街大字等、B列に対応する郵便番号があります。(名前定義を仮に「〒」とします)

Sheet1の住所の郵便番号を検索する必要がありますが、そのためには住所の丁目地番より左の部分(市郡町街大字等)を分離しなければなりません。
これをマクロを使わずに1個の計算式で表示したいといろいろ工夫しましたが、どうもいい知恵が浮かびません。仕方なく一旦B列(B1)に
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,9,1),8,1),7,1),6,1),5,1),4,1),3,1),2,1)
という長たらしいネスト式を使って「2~9」の数値をすべて「1」に置き換え、
C列(C1)に =LEFT(A1,FIND(1,B1)-1)
と計算式を入れて市郡町街大字等を抽出し、D列(D1)に
VLOOKUP(C1,〒,2,FALSE) と入れてようやく郵便番号を検索しております。
結果としてはこれで正しい答は出るのですが、標準搭載の関数だけで(マクロを使わずに)何かもっと簡単な方法がありはしないかという気がしてなりません。
ご存知の方があればどうか教えてください。(OS等はWinMe→Excel2000Proです。)

Sheet1のA列に例えば群馬県内の住所のリストがあります。(架空の住所です。)
前橋市池端町3-2-21……(セルA1)
桐生市黒保根町水沼7-3-5
高崎市大沢町1-18-204
沼田市佐山町8-6-5-402
勢多郡北橘村下箱田396
多野郡吉井町岩井27-5
以下1,000件程度

Sheet2のA1:B1538に郵政公社からダウンロードした群馬県内の郵便番号一覧表があり、A列に市郡町街大字等、B列に対応する郵便番号があります。(名前定義を仮に「〒」とします)

Sheet1の住所の郵便番号を検索する必要がありますが、その...続きを読む

Aベストアンサー

=LEFT(A1,LENB(A1)-LEN(A1))
でどうでしょうか?
A1で言えば"3-2-21"と削除するべき部分が1バイト文字になっていますので、バイトで文字数を数えた時の長さを文字数で数えた時の長さを引けば、
2バイト文字で構成される文字の長さがでます。

QEXCELで、セルに住所欄があり、うち市区郡のみを抽出する方法

初心者ですいません。どなたかお助けください。
EXCELで、たとえば
       A             
 1兵庫県神戸市灘区加納町1-1-1
 2兵庫県姫路市広畑区尾上町568
 3兵庫県飾磨郡夢前町田中678
とあった場合(住所はすべて兵庫県下です)、

     A
1 神戸市灘区(神戸市のみ区まで)
2 姫路市
3 飾磨郡

または隣のセルで
    B
1 神戸市灘区
2 姫路市
3 飾磨郡
といった形式に変更できないでしょうか?
よろしくご教示願います。。

Aベストアンサー

acmrさん、こんにちわ。
B1のセルに、長くなりますが以下の式をコピーしてください。
で、下にドラッグしていけばOKです。

=IF(ISERROR(FIND("神戸市",A1)),IF(ISERROR(FIND("郡",A1)),MID(A1,4,FIND("市",A1)-3),MID(A1,4,FIND("郡",A1)-3)),MID(A1,4,FIND("区",A1)-3))

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【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エクセル 数値だけ抽出するには?

エクセルで、文字列+スペース+数字と入力されているセルから数値だけ取り出す方法を教えていただけますか?
文字列とスペースの字数は一定で、数値の桁数は6-8桁と変動します。
A列からb列のように変換したいのです。

A列          B列
PMID:_12345678    12345678
PMID:_123456     123456

宜しくお願いいたします。

Aベストアンサー

B1に
VALUE(REPLACE(A1,1,6,""))
でよいでしょう.

数値の桁数には依存しません.


人気Q&Aランキング