初心者です。異なるシート間における表を参照し、置換の
ような事がしたいのですが、VLOOKUPが使えるのでしょうか?
やりたいのは、下記の通りです。
Sheet1のような表があります。
Sheet2のA列を参照し、Sheet1のB列を変換し、
Sheet3のような結果を得たいのです。
近似値もできるみたいなのでVLOOKUPを使おうと
思ったのですが、「含む」は論理値でどう指定すればいいか
分からず困っています。
そもそもVLOOKUPでできるかも疑問ですし...
教えてください、よろしくお願いします。
Sheet1
A列 B列
12345 りんご、みかん、イチゴ
67891 いんげん
23456 チョコ、あめ
67890 みかん、りんご
56789 あめ
Sheet2
A列 B列
りんご くだもの
みかん くだもの
いちご くだもの
いんげん 野菜
チョコ おかし
あめ おかし
Sheet3
A列 B列
12345 くだもの
67891 野菜
23456 おかし
67890 くだもの
56789 おかし
No.3ベストアンサー
- 回答日時:
これも残念ながら一筋縄ではいかないと思います。
前回のご質問もそうですが、そもそも「一つのセルに複数のデータ」というのが、
Excelの基本的な考え方としてはイレギュラーなので、処理も難しくなってしまいますね。
(まぁ、そうせざるを得ない状況もあるんですが…)
さて。
Sheet3のA列については、
Sheet3!A1:=Sheet1!A1
として相対参照で素朴に引けば解決ですね。
問題はB列ですが…
A案 Sheet1のB列を[区切り位置]でばらしてから、VLOOKUPで一致検索
1.Sheet1のB列を選択する。
2.データ>区切り位置>[カンマやタブなど…]>次へ
[その他]にチェックを入れる>[その他]の枠内に読点"、"を入力する>次へ
[表示先]にC1を指定>完了
これで、Sheet1のC列以降にデータが一つずつ入りますから、
後は、普通にVLOOKUPを重ねるだけです。
3.Sheet3!B1:=VLOOKUP(VLOOKUP(A1,Sheet1!$A$1:$C$99,3,0),Sheet2!$A$1:$B$99,2,0)
おそらくこれが普通の処理です。
B案 Sheet2を、A列を基準に昇順で並べ替えてから、VLOOKUPで近似検索
1.Sheet2のA:B列を選択する
2.データ>並べ替え>最優先されるキー:A列,昇順>
オプション>[ふりがなをつかわない]>OK>OK
3.Sheet3!B1:=VLOOKUP(VLOOKUP(A1,Sheet1!$A$1:$B$99,2,0),Sheet2!$A$1:$B$99,2,1)
挙動が直感的に判り難いので、トラブルが起こりそうな気がします。
お勧めしません。
C案 Sheet1のB列の最初のデータを数式で切り出して、VLOOKUPで一致検索
1.Sheet1!C1:=IF(ISERROR(FIND("、",B1)),B1,LEFT(B1,FIND("、",B1)-1))
2.Sheet3!B1:=VLOOKUP(VLOOKUP(A1,Sheet1!$A$1:$C$99,3,0),Sheet2!$A$1:$B$99,2,0)
これも、まぁ、よく見る処理です。
D案 数式一発でやってみる
1.Sheet3!B1:=INDEX(Sheet2!$B$1:$B$99,MATCH(FALSE,ISERROR(FIND(Sheet2!$A$1:$A$99,VLOOKUP(A1,Sheet1!$A$1:$B$99,2,0))),0))
を配列数式として入力。
※通常の数式は、入力した後Enterキーで確定しますが、
配列数式を入力するときは、CtrlとShiftを押しながらEnterで確定します。
略解
VLOOKUPでIDから対応するグループを引く。
⇒FINDでSheet2!A列の各行の値をグループから探して、見つかるかどうかを判定
⇒MATCHでエラーでない(値を含む)ものの行位置を取得
⇒INDEXで対応する行位置のデータを取得
「Sheet2にりんごがない場合でもみかんを見つけてくだものを返す」スグレものですが、
ある程度慣れた人でないと、実際の運用は難しいかもしれません。
長乱文陳謝
やっぱり関数だと一筋縄にいかないですか。
マクロの勉強始めましたが、なかなかで...
1つのセルに複数あるデータの数がとても多いので、
D案でやってみました!でも、これは私が応用できるか
が問題ですね...
いつもありがとうございます。感謝です!
No.2
- 回答日時:
参考です。
Sub Macro1()
Dim r As Integer, i As Integer
Dim rowpos As Integer
Dim myItemNo As Double
Dim myItem As String
Dim myAnswer As String
Dim Lookuptable As Variant
Lookuptable = Worksheets("Sheet2").Cells(1, 1).CurrentRegion.Value 'sheet2の情報をメモリ上に持ってくる
r = UBound(Lookuptable, 1) '行数を得る
rowpos = 1
With Worksheets("Sheet1")
Do While .Cells(rowpos, 1).Value <> ""
myItemNo = .Cells(rowpos, 1).Value
myItem = .Cells(rowpos, 2).Value
myAnswer = ""
For i = 1 To r
If InStr(myItem, Lookuptable(i, 1)) > 0 Then '初めての分類名のみ処理する
If InStr(myAnswer, Lookuptable(i, 2)) = 0 Then
If myAnswer = "" Then myAnswer = Lookuptable(i, 2) Else myAnswer = myAnswer & "," & Lookuptable(i, 2)
End If
End If
Next
If myAnswer = "" Then myAnswer = "該当なし"
Worksheets("Sheet3").Cells(rowpos, 1).Value = myItemNo
Worksheets("Sheet3").Cells(rowpos, 2).Value = myAnswer
rowpos = rowpos + 1
Loop
End With
End Sub
No.1
- 回答日時:
Excel:条件付き文字列の結合方法
http://okwave.jp/qa3304401.html
こちらの逆バージョンと言う事でしょうか。
シート1及びシート2の1行目は項目名があり、
シート3にはデータは無記入であるとした時、
シート3の2行目以降に置換した結果をだします。
Sub Test2()
Dim Dic As Object
Dim r2 As Range, r3 As Range
Dim v As Variant, vv As Variant
Dim key As Variant
Dim i As Integer
Set Dic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
v = .Range(.[A2], .Cells(Rows.Count, "B").End(xlUp)).Value
For i = 1 To UBound(v, 1)
Dic(v(i, 1)) = Split(v(i, 2), "、")
Next
End With
With Sheets("Sheet2")
Set r2 = .Range(.[A2], .Cells(Rows.Count, "B").End(xlUp))
End With
Set r3 = Sheets("Sheet3").Range("A2")
For Each key In Dic.keys
With Application
vv = .Index(r2, .Match(Dic.Item(key)(0), r2.Columns(1), 0), 2)
End With
If IsError(vv) Then
r3.Value = key
r3.Offset(, 1).Value = "該当なし"
Else
r3.Value = key
r3.Offset(, 1).Value = vv
End If
Set r3 = r3.Offset(1)
Next
End Sub
シート3のA列に既にデータがあるような場合、
質問内容を勘違いしているかも?
その時は、すいません。
この回答への補足
さっそく、ご回答いただきありがとうございます。
できました!ありがとうございます!!
ただ、実は、マクロがまったく分からなくて...
今回の分はこれでできそうですが、列の行が増えたり、ずれたり
したものやじゃっかん内容の違うものもやることになりそうで、
その時の応用が効かなくて。。。
関数などで行う方法がありますでしょうか?
ご存知でしたら、教えてください!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Excel(エクセル) Excelにて、行の最後のセルの値をコピーして別sheetに張りつけるVBAコードをご教授願います 3 2022/11/20 14:35
- Visual Basic(VBA) VBAでvlookup関数から、別シート参照するやり方・・・ 2 2022/11/14 18:49
- Visual Basic(VBA) Sheet「状況」から、分類の年齢別カウント数をSheet「D表」へ転記する下記マクロを作っています 7 2022/12/14 17:57
- Visual Basic(VBA) VBA 別sheetからの転記なのですが 2 2023/05/22 15:55
- Visual Basic(VBA) Sheet1のA列にコードB列にメアド、Sheet2のB列にコード一覧とD列にメアド一覧があり、Sh 3 2022/10/19 11:57
- Excel(エクセル) Excel_マクロ_複数のシートのVLOOKUPで表示された#N/A以外に色付けをしたいです 1 2023/02/16 22:37
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Visual Basic(VBA) 改行ごとに行を追加し、数量を分割 4 2023/07/11 16:39
- Visual Basic(VBA) vbaのvlookup関数エラー原因を教えていただけないでしょうか。 3 2022/04/25 16:16
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
再起動が必要です
-
ワードでプリンターがA4までな...
-
ワードでルビを振ると「単語が...
-
Office2012の起動が遅い
-
ワード2016で図の形式の色の変...
-
理想的な行数、文字数、フォント
-
ワードが白と黒が反転してしま...
-
大学のレポートについてです。...
-
ワード レイアウトの崩れ
-
ワードが打ち込みできません
-
ワード PDF化した際、背景の...
-
ワードです。 スマホでは横書き...
-
chatgptで、2000000トークンと...
-
参照用数字配列がいつの間か変...
-
Officeソフトで印刷の入稿デー...
-
目次は文書中の先頭にのみにし...
-
IMEパッドの手書き入力
-
互換性のないSafariからみた画...
-
KADOKAWA電撃大賞の互換ソフト...
-
ワードやエクセルのファイルの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
参照用数字配列がいつの間か変...
-
ワードでプリンターがA4までな...
-
ワード PDF化した際、背景の...
-
理想的な行数、文字数、フォント
-
Officeソフトで印刷の入稿デー...
-
chatgptで、2000000トークンと...
-
ワードが白と黒が反転してしま...
-
最近MacBookに変えました。 マ...
-
ワードで作成した画像を保存す...
-
ワード2016で図の形式の色の変...
-
アスタリスクは真ん中じゃない...
-
ワード レイアウトの崩れ
-
互換性のないSafariからみた画...
-
ワードで大量の図形を一括でグ...
-
Office2012の起動が遅い
-
A4サイズ横位置というのは ワー...
-
ワードってスマホから見ると、...
-
スマホのワードが開かないのっ...
-
ワードで文章打つ時、全角、半...
-
「大谷翔平」をローマ字で書くには
おすすめ情報