今なら家電が全品9%OFF!

「VLOOKUP 関数」を使用してシート又はブック間で検索し抽出しているのですが、その抽出したものが、そのシートの何行目にあったか、行番号が知りたいのですが、行番号も一緒に抽出する方法(関数)はありますか?

関数やマクロなど全く無知なので、どうかお助けいただきたいと思います。よろしくお願いします!

A 回答 (1件)

MATCH関数を使用して下さい。


仮に=VLOOKUP(検索値,A:C,2,FALSE)ならば、=MATCH(検索値,A:A,0)で行番号が戻ります。

この回答への補足

mu2011さん、ご回答ありがとうございました。
大変助かりました。

補足日時:2009/08/27 23:41
    • good
    • 0
この回答へのお礼

mu2011さん、ご回答いただきありがとうございました。
知りたかった行番号が分かり、手間がはぶけました(嬉)

あと…追加でお伺いしたいことがございます。
抽出した言葉が複数ある場合(2,4,6行とあった場合)、若い行番号(2行目)が検索されますが、複数ある場合、全ての行番号を抽出する方法はありますか?

お手数ですが、ご回答いただければ幸いです。
よろしくお願いします。

お礼日時:2009/08/26 22:27

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

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

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

QExcel でブック全体を検索するには

Excel 2000 を使っています。

複数のシートにまたがって(つまりブック全体)検索するには、どうすればいいのでしょうか?
以前会社でやったときは、[検索] 画面で設定できたと思うのですが、見当たりません。

Aベストアンサー

Ctrlを押しながら、またはShiftキーを併用するなどして複数のシートを選択した上でCtrl+Fで検索するのはどうでしょう?

Qエクセルで、book全体の検索&抽出の方法

たびたびお世話になっております。
エクセルのbook全体で、ある文字を検索し、ヒットしたセルを含む行全てを選択&抽出することは可能でしょうか。
book全てに対応するオートフィルタのようなものでしょうか。何か良い方法がありましたらご教授ください。
よろしくお願いいたします。

Aベストアンサー

「ヒットしたセルを含む行」というところが気になりますが、以下のようなことではないんですよね?f^_^;

1.シート名で右クリックし、「すべてのシートを選択」
2.「編集」→「検索」

QExcel 複数ブックに跨った文字列の検索

複数のブックに対して文字列を検索する方法についてご教授いただけないでしょうか?
http://www.keep-on.com/excelyou/2001lng4/200111/01110094.txt
にそれらしき方法が記載されたいたのですが、その中で、
Windowsの<red>検索</red>が手っ取り早いという記載があったのですが、これはどのように使えばよいかもご教授頂けると助かります。どうぞよろしくお願いいたします。

Aベストアンサー

Windowsの機能を使っての検索について(OS:XP)

1:検索ボタンを押して検索窓を表示させます。
2:何を検索するか聞かれたら「ドキュメント(ワープロ~~)」を選択
3:最終更新日時を聞いてくると思うので、そこは無視して、下の「詳細検索オプションを使う」を選択
4:すると【ドキュメントに含まれる単語または句】という設定項目がでてきます。
5:そこに検索したい語句を入力

の手順でメモ帳やエクセルファイルなどの中身まで見てくれますよ。
中を探したいファイルを全部同じフォルダにでも突っ込んで検索かければ楽かと思います。

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

QEXCELマクロで全シート対象の検索マクロを教えて

EXCELマクロで全シート対象の検索マクロを教えてください。
アプリケーションダウンロードタイプでも自作マクロの
どちらでもよいです。

Aベストアンサー

無力のため、いつもFind、FindNextには苦労するのですが、思い切ってあげてみます。VBAの経験がおありなら、欠点は修正してもらえるかもと思って。
標準モジュールに
Sub test01()
s = InputBox("検索文字列=")
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
Set x = sh.Cells.Find(what:=s)
If x Is Nothing Then GoTo p1
MsgBox sh.Name & x.Address
b = sh.Name & x.Address
sh.Activate
x.Activate
'---
Do
Set y = sh.Cells.FindNext(after:=ActiveCell)
If y Is Nothing Then GoTo p1
If sh.Name & y.Address = b Then GoTo p1
MsgBox sh.Name & y.Address
y.Activate
Loop
p1:
Next
End Sub
シート名タブの左側のシートから探していきます。
上記はMggBoxで表示にしてますが、特別セルにセルアドレス値を出すなど適当に修正してください。
少数例でテストしてますが、十分テストしてください。

無力のため、いつもFind、FindNextには苦労するのですが、思い切ってあげてみます。VBAの経験がおありなら、欠点は修正してもらえるかもと思って。
標準モジュールに
Sub test01()
s = InputBox("検索文字列=")
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
Set x = sh.Cells.Find(what:=s)
If x Is Nothing Then GoTo p1
MsgBox sh.Name & x.Address
b = sh.Name & x.Address
sh.Activate
x.Activate
'---
Do
Set y = sh.Cells.FindNext(after:=ActiveCell)
If y Is Nothing T...続きを読む

QEXCEL ブック全体で条件計算方法

説明下手なので具体的に質問します。

sheetA sheetB sheetC ・・・ sheetHと
様式は同じで、入力されている文字が違うbookがあります。
あるセル(R3C6)に各sheet毎に○か×が書いてあります。
少なくとも一つでも○があった場合は○、
一つも○がなかった場合は×を
sheetZにまとめて判定結果を出す場合
sheetZにはどのような関数を書けば良いのでしょうか?

イメージ的にはこんな感じなんですが・・・

sheetZ(R3C6)
=IF(sheetA:sheetH!R3C6="○", "○", "×")

これだとすべてのsheetで○じゃないといけない?

Aベストアンサー

#3のWendy02です。

>Sheetに名前(地名)が付いてしまっていて

それは、#3の式じゃむりです。皆さんの式と、中身自体は変っていませんから、ひとつずつ、シートを読んでいるだけです。Char() というのは、あくまでも、A~Hという意味シート名を作り出すことです。

そこで、お使いになるか分りませんが、ユーザー定義関数を作ってみました。

=MyCountIf("シート名の初め:シート名の終わり!セル番地","○")

=IF(MyCountIf("sheetA:sheetH!R3C6","○") , "○", "×")
と、「" "」シート名を含めた範囲を、クォーテーションマークで括ります。

コードの取り付け方

Alt +  F11 (Altを押しながらF11)を押すと、Visual Basic Editor 画面が出てきます。
次に、メニューの[挿入]-[標準モジュール]と開けて、クリックすると、画面が現れますので、以下のコードを貼り付けて、

貼り付けた後は、Alt + Q で、画面を閉じます。
後は、一般式と同じです。ただし、アドインにしてあげない限りは、そのブック内しか使用できません。

'<標準モジュール設定>
Function MyCountIf(範囲 As Variant, 検索値 As String) As Variant
 'MyCountIf(シート範囲,セル,検索値)
  Dim s_Range As String
  Dim m_Range As String
  Dim m_Find As String
  Dim firstSh As String
  Dim lastSh As String
  Dim n1 As Integer
  Dim n2 As Integer
  Dim i1 As Integer
  Dim i2 As Integer
  Dim buf As Integer
  Dim j As Integer
  Dim k As Long
  
  m_Find = 検索値
 
  If TypeName(範囲) = "Range" Then
   MyCountIf = WorksheetFunction.CountIf(範囲, m_Find): Exit Function
  End If
 
  If TypeName(範囲) = "String" Then
   s_Range = 範囲
  '区切り文字検索
   n1 = InStr(s_Range, ":")
   n2 = InStr(s_Range, "!")
  Else
   MyCountIf = CVErr(xlErrNA)
  End If
  If n1 > n2 Then
   MyCountIf = WorksheetFunction.CountIf(Range(範囲), m_Find): Exit Function
  End If
  If n1 > 0 Then firstSh = Mid$(s_Range, 1, n1 - 1)
  If n2 > 0 Then lastSh = Mid$(s_Range, n1 + 1, n2 - n1 - 1)
  m_Range = Mid$(s_Range, n2 + 1)
  i1 = ActiveWorkbook.Worksheets(firstSh).Index
  i2 = ActiveWorkbook.Worksheets(lastSh).Index
  If i1 > i2 Then buf = i1: i1 = i2: i2 = buf
  For j = i1 To i2
   k = k + WorksheetFunction.CountIf(Worksheets(j).Range(m_Range), m_Find)
  Next j
  MyCountIf = k
End Function

#3のWendy02です。

>Sheetに名前(地名)が付いてしまっていて

それは、#3の式じゃむりです。皆さんの式と、中身自体は変っていませんから、ひとつずつ、シートを読んでいるだけです。Char() というのは、あくまでも、A~Hという意味シート名を作り出すことです。

そこで、お使いになるか分りませんが、ユーザー定義関数を作ってみました。

=MyCountIf("シート名の初め:シート名の終わり!セル番地","○")

=IF(MyCountIf("sheetA:sheetH!R3C6","○") , "○", "×")
と、「" "」シート名を含めた範囲...続きを読む

QEXCEL:複数シートから特定行を一覧化したい

【 EXCELで悩んでいます 】
複数のシートから特定の言葉の記入がある行だけ、
新規ワークシートへ自動で書き出したいのですが難儀しています。

当方エクセル初心者でして、
ピボットテーブルもマクロも組んだことがありません。
工程数がかかってもいいので、できるだけ優しい方法を教えて下さい。

仕事で使うデータで、これがあると非常に助かります。

【 準備しているデータ 】
・参照したいデータシートは8枚
・それぞれ、同じフォーマットで内容が異なる60行弱の文字データ
(画像参照ください。このようなデータシートが8枚あります)

【 やりたいこと 】
8枚にも渡るシートの内容のなかから、
特定の文字が入っているもの(画像でいうと、G列「進捗」の「作業中」になっている行)だけを自動的に抽出して
一枚の新規シートにまとめて一覧化したい

【 環境 】
EXCEL 2003
Windows

面倒かとは思いますが、
お優しいエクセルマスター様がいらっしゃると信じております。
どうかよろしくお願い申し上げます。

Aベストアンサー

>なお、MackyNo1様がお考えの記録マクロの利用する方法もよろしければお知らせいただきたいのですがよろしいでしょうか?

以下のようなキー操作でマクロを記録してください。

まず、準備として、1行目に項目名を記載した集計シートを作成しておきます。

「ツール」「マクロ」「新しいマクロの記録」から、以下の操作をします。

集計シートのA2セルからデータ挿入範囲を大きめに選択し、Deleteキーで以前のデータを削除します(最初は何も入力されていませんが)。
次に1枚目のシートの項目行を選択し、データの入力されているA2セルからデータ範囲を大きめに選択し(予定最大データ数分まで選択します)、「データ」「フィルタ」「オートフィルタ」で必要なデータを表抽出し、Ctrl+Cでコピーして、集計シートのA2セルでCtrl+Vで貼り付けます。
2枚目のシートからは以下の操作を行います。
同様に、2枚目のシートのA2セルから、データ範囲を大きめに選択してからフィルタで抽出したデータをコピーし、集計シートのA1セルを選択して、Ctrl+↓のショートカット操作を行い、マクロの記録ツールバーの「記録終了」右にある「相対参照」のアイコンをクリックし、↓キーを押してから、相対参照ボタンをもう一度押して捜査隊参照を解除してCtrl+Vで貼り付けます。
この操作を8枚目のシートまで行い、最後にすべてのシートのフィルタモードを、「データ」「フィルタ」「オートフィルタ」で解除すれば完成です。

最終セルの選択にCtrl+↓のショートカット操作を利用すること、相対参照の記録ボタンをA列の一番下のデータの1つ下のセルを選択すること、各シートのデータ範囲を最初に選択しておいてからフィルタ操作するところが記録マクロ操作のポイントです。

>なお、MackyNo1様がお考えの記録マクロの利用する方法もよろしければお知らせいただきたいのですがよろしいでしょうか?

以下のようなキー操作でマクロを記録してください。

まず、準備として、1行目に項目名を記載した集計シートを作成しておきます。

「ツール」「マクロ」「新しいマクロの記録」から、以下の操作をします。

集計シートのA2セルからデータ挿入範囲を大きめに選択し、Deleteキーで以前のデータを削除します(最初は何も入力されていませんが)。
次に1枚目のシートの項目行を選択し、データ...続きを読む

QVlookup関数⇒別のブックから、データを検索

【エクセル】リストに、別のリストからデータを追加

タイトルの件、質問いたします。

下記の添付ファイルをご覧ください。

【やりたい事】

元データブック・Sheet1のC2セルに下記の関数を入れていまが

=VLOOKUP(A2,???????,3,FALSE)

別のシートの情報検索する場合の、範囲の指定の仕方がわかりません。
範囲は、図2の別のブックにあります。

最終的に、図3のように、住所を検索して、入れていきたいと思います。

ご存知の方いらっしゃいましたら、宜しくお願いします。

Aベストアンサー

マウス操作で簡単に入力できますのでお試しください。
対象のブックを開けて、=VLOOKUP(A2,まで入力したら後はマウスを使って、対象のブック→対象のシート→対象の範囲(A列をクリック、Shiftキー押下してC列をクリック)と左クリックするだけです。

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行までとしていますが、必要に応じて変更して下さい

Qエクセルで別ブックのセルを参照する式の内部

エクセルで別ブックのセルを参照する式の内部


別ブックのセルを参照する時は

='パス名[ブック名]シート名'!セル名

という式みたいなので、
='item\zaiko\[zaiko_2010_06_01.xls]在庫シート'!B5
という式を作りました(パスは適当です)。

それで、
='item\zaiko\[zaiko_2010_06_01.xls]在庫シート'!B5
='item\zaiko\[zaiko_2010_06_02.xls]在庫シート'!B5
='item\zaiko\[zaiko_2010_06_03.xls]在庫シート'!B5
='item\zaiko\[zaiko_2010_06_04.xls]在庫シート'!B5

と日付を連番で複数のセルに入れていきたいので、
日付の 2010_06_01 の部分を他セルから持ってきたいのですが、
この式の中に 文字列参照のMID() とかで挿入できるでしょうか?
日付の入っているセルがちょうど横の方に連番で存在しています。

分かる方いましたら教えてください。
よろしくお願いします。

エクセルで別ブックのセルを参照する式の内部


別ブックのセルを参照する時は

='パス名[ブック名]シート名'!セル名

という式みたいなので、
='item\zaiko\[zaiko_2010_06_01.xls]在庫シート'!B5
という式を作りました(パスは適当です)。

それで、
='item\zaiko\[zaiko_2010_06_01.xls]在庫シート'!B5
='item\zaiko\[zaiko_2010_06_02.xls]在庫シート'!B5
='item\zaiko\[zaiko_2010_06_03.xls]在庫シート'!B5
='item\zaiko\[zaiko_2010_06_04.xls]在庫シート'!B5

と日付を連番で複数のセルに入れていきたい...続きを読む

Aベストアンサー

=INDIRECT("''item\zaiko\[zaiko_"&A1&".xls]在庫シート'!B5")
とすれば出来ることはできますが、参照先のブックは開いている必要があります。

単に、沢山の数式を入力するのが面倒だというだけであれば、A1のセル以降に日付部分が入力してある場合、
="='item\zaiko\[zaiko_"&A1&".xls]在庫シート'!B5"
のように入力し、下へ数式をオートフィル等でコピーし、
入力した数式範囲を範囲選択→コピー→形式を選択して貼り付け→値でOKし、
範囲選択したまま、編集→置換で検索する文字列「=」、置換後の文字列「=」で置換します。


人気Q&Aランキング