エクセルの学校：全文検索：Hyper Estraier： VLOOKUP

Results of 1 - 1 of about 7929 for VLOOKUP (0.002 sec.)

[[20110222211247]]: #score: 3346; @digest: 4637723811b55c60cb2176f1ab1f0560; @id: 53449; @mdate: 2011-02-25T06:29:56Z; @size: 18136; @type: text/plain; #keywords: 本情 (104573), 報! (78788), 得") (45837), ・◎ (44580), irow (42071), 中() (34105), 報!$ (29703), 報取 (28686), 報" (19054), 戦中 (17883), 報") (13240), 値= (12659), 基本 (10515), ーモ (10471), formular1c1 (10363), 情報 (8392), 号= (7858), 苦戦 (7553), 索値 (7246), 囲= (6152), 列番 (5985), 対参 (5767), 号, (5494), operation (5425), skipblanks (5347), vlookup (5181), 記録 (4971), 値, (4685), xlpastevalues (4416), 値貼 (3882), 囲, (3711), false (3223); 『vlookupの検索値を最終行まで取得したい』（miffy）
winXP ex03 いつも同じような質問ですみません… 今、"情報取得"シートのA列を検索値に、"情報取得"シートのB列には"基本情報"シートの2列目を、 C列には"基本情報"の4列目を、D列には"基本情報"の9列目を表示したいと思っています。 A列はA3セル以下に以前教えていただいたマクロを実行する都度必要なデータが抽出されるようになっています。以下のコードですと、検索値がA3セルのままになってしまい、「検索値」のところをいじってみたのですがうまくいきません。また、C列D列と同様にコードを打たなくても良い方法があれば助かります。よろしくお願いします。 Sub 苦戦中() Dim 範囲 As Range Set 範囲 = Worksheets("基本情報").Range("A:I") 列番号 = 2 検索値 = Worksheets("情報取得").Range("A3") 値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False) irow = Range("b" & Rows.Count).End(xlUp).Row Range("b" & irow + 1).Value = 値 End Sub ---- >いじってみたのですがうまくいきません。がんばっているようなので、ヒントをb 「列番号」に「2」が入っているから >"基本情報"シートの2列目になるのはわかりますよね? >"基本情報"の4列目になる為には、どうすればいいでしょうか? また、同じく >"基本情報"の9列目にする為には? >Range("b" & irow + 1).Value = 値ここで「"b"」となっている為、「B列」に表示しているのはわかりますよね? >C列には >D列にはそうすると、何処を変更すればよいでしょう? ループすることで、コンパクトに出来そうですがそのまま命令文を増やすことからはじめてみませんか? がんばってくださいね^^ （キリキ）（〃⌒o⌒)b ---- マクロで数式を埋め込んで値化するのでは駄目なのでしょうか? たとえば、 B3:B10セルを選択して =VLOOKUP($A3,基本情報!$A:$I,2,FALSE) を入力して Ctrl + Enter C3:C10セルを選択して =VLOOKUP($A3,基本情報!$A:$I,4,FALSE) を入力して Ctrl + Enter B3:C10セルを選択してコピー→値貼り付けこの手順をマクロの記録にとれば、マクロを実行した時に B3:C10 の値が更新されることになります。実際は、10行目までではなくデータの最終範囲までなのでその点は変更が必要です。また、記録にとったままだと、画面がちらちらするのでその辺りも変更しておくのが良いと思いますが。 (HANA) ---- >キリキさんレスありがとうございます。以下のようなものは出来ているのですが… 「検索値」であるA3を下の最終行まで取得することができません…。 Sub 苦戦中() Dim 範囲 As Range Set 範囲 = Worksheets("基本情報").Range("A:I") 列番号 = 2 検索値 = Worksheets("情報取得").Range("A3") 値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False) irow = Range("b" & Rows.Count).End(xlUp).Row Range("b" & irow + 1).Value = 値 Set 範囲 = Worksheets("基本情報").Range("A:I") 列番号 = 4 検索値 = Worksheets("情報取得").Range("A3") 値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False) irow = Range("c" & Rows.Count).End(xlUp).Row Range("c" & irow + 1).Value = 値 Set 範囲 = Worksheets("基本情報").Range("A:I") 列番号 = 9 検索値 = Worksheets("情報取得").Range("A3") 値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False) irow = Range("d" & Rows.Count).End(xlUp).Row Range("d" & irow + 1).Value = 値 End Sub >HANAさんレスありがとうございます。コピーと貼り付けをマクロに…ですか、思いつきませんでした。しかし情報が日々増えていき、多数の人に配布することになる（ことを目標に作っている）ため、その「最終範囲まで」が場合によってまちまちです。検索値=のあとで最終行を指定するようなことは難しいのでしょうか… (miffy) ---- ＞その「最終範囲まで」が場合によってまちまちです。ですから、その部分は変更が必要ですね。数式を埋め込んで値化・・・で良いならまずはマクロの記録をやってみて下さい。出来たコードをこちらに載せてください。ちなみに、最終行は＞＞irow = Range("b" & Rows.Count).End(xlUp).Row で求められていますね? （今度は、A列を見る事になりますが。） (HANA) ---- ちょっと時間が無いので簡単に書いておきます。たとえば、 B3:B10セルを選択して =VLOOKUP($A3,基本情報!$A:$I,2,FALSE) を入力して Ctrl + Enter すると、 Range("B3:B10").Select Selection.FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)" こんなコードが出来ます。 2行目の Selection とは、1行目の Range("B3:B10") の事なので一行にまとめて Range("B3:B10").FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)" と出来ます。実際は、B3:B10の範囲に数式を入れれば良いのではなく A列の最後の行まで数式を入れる必要があります。 A列の入力がある最後の行は irow = Range("A" & Rows.Count).End(xlUp).Row で求められるので、B3 から B列の irow行の範囲に数式を入れれば良いです。 Range("B3:B" & irow).FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)" もしも irowが「10」だったら "B3:B" & 10 → B3:B10 に成りますね? C列には、VLOOKUP関数の列が「4」になった数式が入れば良いので Range("C3:C" & irow).FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,4,FALSE)" もしもこの数式の変更場所が分からなければこの式も記録にとってみれば良いと思います。少し難しいですが、「基本情報!C1:C9」は、基本情報シートのC1:C9セルの事ではありませんので、混同なさいません様に。それから、 B3:C10セルを選択してコピー→値貼り付けすると Range("B3:C10").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False こんなコードが出来ます。 2行目と3行目の Selection はやはり B3:C10 の事なので Range("B3:C10").Copy Range("B3:C10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False これまた C10 の所の 10 → irow ですので同じ様に変更します。あとは、実行後にどうしておきたいかコピーモードは解除しておきたい貼り付けた範囲が選択されているのでどこか一つのセルを選択して終わりたい等ありましたら、マクロの記録を参考に付け加えて行ってください。コピーモードの解除は Esc で出来ますね。実際は、D列まであるので D列の処理は付け加えてください。また、変数「irow」を使うので変数の宣言 Dim irow As Long もお忘れなく書いておいてください。まずは駆け足で。。。読んだだけでは分かりにくいと思いますので実際にマクロの記録をとって出来たコードとにらめっこしながら一つずつ納得して行ってもらえればと思います。 (HANA) ---- それでは、σ(^o^;)はコードに説明をつけてみましょうかねb Sub 苦戦中() '「範囲」という Rangeオブジェクトの変数宣言 Dim 範囲 As Range '変数「範囲」に、基本情報シートの A列からI列をセット Set 範囲 = Worksheets("基本情報").Range("A:I") '変数「列番号」に、2 を格納列番号 = 2 '変数「検索値」に、情報取得シートの、A3 に入っている情報（値）を格納検索値 = Worksheets("情報取得").Range("A3") '変数「値」に、VLOOKUP関数を使用し返ってきた値を格納値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False) '変数「irow」に、B列の最終行を取得 '※ B列の一番下段のセルから、Ctrl + ↑をした時のセルの行番号と同じ irow = Range("b" & Rows.Count).End(xlUp).Row 'B列の irow（最終行）に、+1 した場所（最終行の一つ下のセル）に、「値」を返す Range("b" & irow + 1).Value = 値 End Sub 少し理解が進むとうれしいです^^ （キリキ）(〃⌒o⌒)b ---- >HANAさん遅い時間に丁寧にありがとうございます。とりあえず、B列だけでも、と以下のコードにしてみましたが「検索値」の行でエラーになってしまいます。 ”実行時エラー1004 アプリケーション定義またはオブジェクト定義のエラーです” というメッセージです。たくさん書いていただいたのに理解できておらず申し訳ありません… Sub 苦戦中() Dim 範囲 As Range Dim irow As Long Set 範囲 = Worksheets("基本情報").Range("A:I") 列番号 = 2 検索値 = Worksheets("情報取得").Range("B3:B" & irow).FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)" 'エラー箇所 Range("B3:B" & irow).Copy 'D列まで作ったら("B3:D" & irow) にする? Range("B3:B" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False) irow = Range("b" & Rows.Count).End(xlUp).Row Range("b" & irow + 1).Value = 値 End Sub (miffy) ---- 全体の処理は見ていませんが、irow に値を入れないまま、使用しているようですね。それではアドレスが存在しないので、当該のエラーになります。 irow = Range("b" & Rows.Count).End(xlUp).Row は Dim irow As Long の直後で実行してみてどうでしょうか。 (Mook) ---- 今日は、社外に出る予定だったのですが、予定が変わりました。。。（行程が一日ずれたので。。。）まずはマクロの記録をとって下さい。記録したマクロを実行して、動きを確認して下さい。そこから、一行に纏める部分。書き替える部分を変更して下さい。すると＞検索値 = Worksheets("情報取得").Range("B3:B" & irow).FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)" なんて事にはならないと思いますよ。現在作っておられるマクロとは別物として考えて下さい。「データ範囲の最終行をirowに入れる」と言う事以外は別の物だと思って頂ければ良いと思います。 (HANA) ---- >Mookさん HANAさんレスありがとうございます。 HANAさんのおっしゃるとおりマクロの記録で"「検索値」が難しい"の意味がようやくわかりました。以下のコードで動きました。ありがとうございました。 Sub 苦戦中() Dim irow As Long irow = Range("a" & Rows.Count).End(xlUp).Row Range("B3:B" & irow).FormulaR1C1 = "=VLOOKUP(RC[-1],基本情報!C[-1]:C[7],2,FALSE)" Range("B3:B" & irow).Copy Range("B3:B" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("c3:c" & irow).FormulaR1C1 = "=VLOOKUP(RC[-2],基本情報!C[-2]:C[6],4,FALSE)" Range("c3:c" & irow).Copy Range("c3:c" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,Transpose:=False Range("d3:d" & irow).FormulaR1C1 = "=VLOOKUP(RC[-3],基本情報!C[-3]:C[5],9,FALSE)" Range("d3:d" & irow).Copy Range("d3:d" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End Sub HANAさんのおっしゃっているとおり、最後のコピーモードの解除のヒントをいただけないでしょうか。 Escボタンを押してマクロの記録をしてもコードがなく、Range("b3").Activate を最後に足してもコピーモードはそのままで… (miffy) ---- 出来ましたか良かったです。丁度「難しい様なら、記録をとった状態のコードを載せて貰えば良いですが」と書こうと思った所でした。作製されたコードに関してもう少し書かせて下さい。現在のコードは B列に数式を埋め込む B列の範囲をコピー・・・・・・・◎ B列の範囲に値貼り付け・・・・・◎ C列に数式を埋め込む C列の範囲をコピー・・・・・・・◎ C列の範囲に値貼り付け・・・・・◎ DB列に数式を埋め込む D列の範囲をコピー・・・・・・・◎ D列の範囲に値貼り付け・・・・・◎ と成っています。でも、B;D列は連続しているので B列に数式を埋め込む C列に数式を埋め込む D列の数式を埋め込む B:D列の範囲をコピー・・・・・◎ B:D列の範囲に値貼り付け・・・◎ とやっても良く有りませんか? セルへのアクセス回数が減るので、寧ろすっきりすると思います。＞Escボタンを押してマクロの記録をしてもコードがなくコピーモードの状態で、 Escボタンを押してみて下さい。（コピーモードでない状態で Escボタンを押しても何も記録されません。）例えば 1.A1セルを選択 2.コピー 3.Esc を押すです。 Range("A1").Select Selection.Copy Application.CutCopyMode = False こんなコードが出来ますが、コピーモードが解除されるのは 3の手順をやった時なので、3行目の部分が該当しますね。 (HANA) ---- >HANAさんできましたーー!非常にわかりやすい説明で大変勉強になりました。 Sub 苦戦中() Dim irow As Long irow = Range("a" & Rows.Count).End(xlUp).Row Range("B3:B" & irow).FormulaR1C1 = "=VLOOKUP(RC[-1],基本情報!C[-1]:C[7],2,FALSE)" Range("c3:c" & irow).FormulaR1C1 = "=VLOOKUP(RC[-2],基本情報!C[-2]:C[6],4,FALSE)" Range("d3:d" & irow).FormulaR1C1 = "=VLOOKUP(RC[-3],基本情報!C[-3]:C[5],9,FALSE)" Range("b3:d" & irow).Copy Range("b3:d" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub こんなにスッキリするものなんですね! 明らかに速くなりました。早速A列のデータ抽出用マクロの中に入れて動かしたいと思います。お仕事のお忙しい中で何度もありがとうございました。 (miffy) ---- 出来ましたか。良かったです。せっかくなので B3セルに =VLOOKUP($A3,基本情報!$A:$I,2,FALSE) C3セルに =VLOOKUP($A3,基本情報!$A:$I,4,FALSE) B4セルに =VLOOKUP(A3,基本情報!A:I,2,FALSE) C4セルに =VLOOKUP(A3,基本情報!A:I,4,FALSE) とした時にマクロの記録で作製される数式の違いを確認してみて頂けると良いと思います。絶対参照の数式を入力した場合の数式の変化する場所、しない場所。相対参照で入力した場合の数式の変化する場所、しない場所。それから、これから先マクロを書いていく時に便利に使えるかもしれないので。。。 Range("b3:d" & irow).Copy Range("b3:d" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False この3行の変わりに Range("b3:d" & irow).Value = Range("b3:d" & irow).Value この1行も試してみて下さい。 (HANA) ---- >HANAさん絶対参照と相対参照試してみました。絶対参照だと[]がなくなるようですね… よくわからずすみません copyのところも、セルに関数を残さないためにやっていたことなんですね。ようやくわかりました。いろいろ教えていただきありがとうございます。 (miffy) ---- ＞絶対参照と相対参照試してみました。絶対参照だと[]がなくなるようですね… そうなんです。基本的には数式を埋め込もうと思った時にマクロの記録にとってエクセルにVBAでの数式を書いてもらって流用すればよいと思いますが雰囲気だけでもわかっておいてもらえると便利だと思うので少しイメージを書いてみます。たとえば、B3セルに＞=VLOOKUP($A3,基本情報!$A:$I,2,FALSE) の式を入れて、D3までフィルドラッグした場合目的の数式にするためには C3,D3の数式内の列番号を表す部分を 4,9に変えますね。マクロの記録を取った場合も Range("B3").FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)" Range("C3").FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,4,FALSE)" Range("D3").FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,9,FALSE)" の様に、列番号を表す部分だけが違う式が記録されたと思います。たとえば、B3セルに＞=VLOOKUP(A3,基本情報!A:I,2,FALSE) の式を入れて、D3までフィルドラッグした場合目的の数式にするためには A3 → B3,C3になっているので A3,A3に戻す A:I → B:J,C:K になっているので A:I,A:Iに戻す列番号を表す部分を、4,9に変えるといった変更が必要になります。マクロの記録を取った場合も Range("B3").FormulaR1C1 = "=VLOOKUP(RC[-1],基本情報!C[-1]:C[7],2,FALSE)" Range("C3").FormulaR1C1 = "=VLOOKUP(RC[-2],基本情報!C[-2]:C[6],4,FALSE)" Range("D3").FormulaR1C1 = "=VLOOKUP(RC[-3],基本情報!C[-3]:C[5],9,FALSE)" 3箇所が違う式が記録されていますね。数式を記録にとる際も、絶対参照にしていたものは絶対参照で相対参照にしていたものは相対参照で記録されるのでそのあたりも考えながら記録にとっていくとちょっとした変更なら簡単にできる様になると思います。もしもコード内でも「=VLOOKUP($A3,基本情報!$A:$I,2,FALSE)」と書けたら後で見た時もわかりやすいですよね。。。それは、基本的には ↓な感じで書けば良いのですが Range("B3").Formula = "=VLOOKUP($A3,基本情報!$A:$I,2,FALSE)" 数式によっては単純に「""」でくくって入れただけではうまくいかない場合もありますのでその際は、マクロの記録も参考にしてください。たとえば、=IF(A1="","",B1)といった式は Range("C1").Formula = "=IF(A1="""","""",B1)" の様になります。記録にとると ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-1])" 「"」が二つずつになっているので「二つずつにするんだ」と推測して修正する。実は、今回の様なパターンであればマクロの記録だけでも＞その「最終範囲まで」が場合によってまちまちです。をクリアするマクロを作る事が出来ます。でも、この度コードを作成した様に基本部分はマクロの記録で作ってポイント部分だけ書きかえる同時に不要部分は削除するのは、簡単だったと思いませんか? マクロの記録だけでは出来ない事もありますがコードを作成する際にマクロの記録はとても役に立つと思うので上手に活用してみてもらえるのが良いと思います。ちなみに、「コピー後値貼り付け」と「○○.Value = ○○.Value」は結果が少し変わってくる場合がありますのでその様に記憶に留めておいて頂ければと思います。なお、最初に載せられたコードを使用するにはループ処理が必要になってきます。これは、マクロの記録ではどうしても出来ない事です。でも、わかるようになると色々な事が簡単に出来る様になると思います。今回の場合のループは割と簡単な方だと思いますのでこの機会にやっておいてみられてはどうでしょう? たとえば、↓のマクロはループ処理で B3:B5に 3〜5 の値を入れます。 Sub ループ1() Dim i As Long For i = 3 To 5 Range("B" & i).Value = i Next End Sub (HANA) ---- >HANAさん本当に細かくありがとうございます!! 絶対参照の部分、大変便利に使えそうです。実は「こういうことがしたい」ということばかりが先に立ってvbaの基本を理解せずに組んでいるので、マクロの記録機能は最初の最初だけ「ふ〜んマクロってこんなことができるなんてすごいなぁ」という程度で「でも編集が大変そうだなぁ」で避けていました。 HANAさんのご指導で今後は記録も大いに活用していきたいと思います。また、loopも、今諦めの境地に至っている「やりたいこと」があるのですが、それには絶対必要になるようなんですが、自分で作り出したら無限loopに陥り、このHPでそこを解決したらまたその処理の中で他にloop（もしくは上記のFor〜nextで出来るのかも）を使わないといけないことがわかり、大苦戦しているところです。 HANAさんに教えていただいた基本を元にもう一度検証してみたいと思います! ありがとうございました。 (miffy) ---- 私はかなりイイカゲンなコードを書くので Do 〜 Loop とか Loop 〜等のループ処理はまさに無限loop に成ってしまうので For 〜 Next の方が良く使います。今回の物もループ処理で処理出来ますので例題として挑戦してみられてはどうでしょう。（本番の前の練習です。）せっかくキリキさんがコードに解説を付けてくれた事ですし。。。 (HANA) ...; http://www.excel.studio-kazu.jp/wiki/kazuwiki/201102/20110222211247.txt - [detail] - similar

PREV NEXT

訪問者：カウンタ