エクセル初心者です。
MATCH関数で、別ファイルの値を取得しようとしていますが
うまくいかないのでどなたか教えていただけませんでしょうか?

下記のように直接ファイル名やシート名を記述するとうまく
いくのですが、

[式]
 =MATCH(C7,[test.xls]テストシート!C1:C100,0)

ファイル名とシート名を変えることがあるので、

 セルB5 → test.xls
 セルC5 → テストシート
として、B5とC5を用いて記述しようと、&や""を使っていろいろと
式に入れてみたのですが、うまくいきません。
途中まではうまくいきそうになっても、C1:C100 の範囲指定で
tesx.xlsではなくて自分自身の C1:C100 を参照したりと、
思った結果になりません。

どなたか、教えていただけると助かります。
宜しくお願いいたします。

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

A 回答 (3件)

既回答者には質問のいみが判ったようだが、私にはもやもやして、はっきり意図がわかりにくい。


ーー
こう書いたらどうだ。
C7でファイルを選択する(決定する)文字列か数値を入れる。
下記で言えば大阪や神戸など。コードでもありえる。

E1:F4
E列  F列
指定語 ファイル名
大阪  A.xls
神戸  B.xls
京都  C.xls
奈良  D.xls
それで参照するブックを選択する。
そしてそのブック内の、決まったシートの決まったセルの値を参照したい。
と言うことかな。(シート名まで指定はF列の書き方を工夫すれば出来る。)
ーーー
であれば、C7で指定したデータに基づいて、VLOOKUP関数で
=VLOOKUP(C7,$e$1:$f$4,2,FALSE)
これで文字列としてエクセルファイル名が決まる。
MATCH関数とVLOOKUP関数は働きは似ていて、同じような用途に使える。
しかしそのまま=をつけて、セルに書いても
(1)フルパス指定の点(ファイル指定でA.xlsの前に着く部分すなわちフォルダ名は同じだと思うから”XXX”で指定し、それとファイル名を&でつなぐ。
セル番地は同じと思うので””の中に書く。
(2)ただの文字列から、「セルを参照する」に働きを変えてくれる
関数INDIRECT関数を思い起こす必要が有る。
Googleで「ndirect関数 使い方」「ndirect関数」でWEB照会すること。
ーー
エクセルで他ブックのセル参照は
http://www.excel-jiten.net/formula/ref_other_boo …
の最後の所をじっくりみて考えて。
ーーー
セル参照は他ブックの範囲(多数セル群)は出来なかった思うが
質問者の意図はどうなっているのか?出来ると思ってないですか。
    • good
    • 0

=MATCH(C7,INDIRECT("["&B5&"]"&C5&"!C1:C100"),0)


としたらどうなりまりか?
ただし、関連ブック(ファイル)をすべて開いておく必要があります。
    • good
    • 0
この回答へのお礼

早速、ご回答をありがとうございました。

昼間からずっと悩んでいたのですが、教えていただいた式を
入れてましたところ、まるで何事も無かったかのように静かに
求めていた値が現れて嬉しかったです。

本当に助かりました。
どうもありがとうございました。

お礼日時:2009/05/16 02:46

マクロを使うと上手くいきます。


私が試作したもので解説します。
スクリーンショットの画像も参考にして読んで下さい。

MATCH関数で別ファイルの値を取得するファイルをmihon.xlsとし、
データが入っているファイルを2つ、それぞれ
ファイル名:test.xls シート名:テストシート
ファイル名:renshu.xls シート名:練習シート
としました。

テストシートにはC1セルからC100セルまでデータがあり、
練習シートにもC1セルからC100セルまで別のデータがあります。

mihon.xlsのシートのD5あたりに、
B5のファイル名とC5のシート名を入力後に
MATCH関数の中のファイル名とシート名の指示を変更させるための
マクロに連結したボタンを作りました。
つまり、B5のファイル名とC5のシート名を入力して[式の変更!]ボタンをクリックすると、
C9セルにあるMATCH関数のなかにあるファイル名とシート名が書き換わります。

[式の変更]ボタンのクリック時のマクロに、この1行を書きます。
Worksheets("Sheet1").Range("C9").Formula = "=MATCH(C7,[" & Range("B5").Value & "]" & Range("C5").Value & "!C1:C100,0)"

これで55nyanさんが希望している作動が実現できます。

わからないことがありましたら、補足をお願いします。
「エクセル MATCH関数でファイル名等の」の回答画像1
    • good
    • 0
この回答へのお礼

遅い時間にもかかわらず、詳しいご回答をいただいて恐縮です。

マクロはまだやったことがなく、難しそうですがやってみたいと
思います。

取り急ぎ、お礼申し上げます。
いろいろ教えていただいて、どうもありがとうございました。

お礼日時:2009/05/16 02:51

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

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

このQ&Aを見た人はこんなQ&Aも見ています

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

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

Qセル番号を返す関数

エクセルで、セル番号を返す関数はありますか??
セルの内容ではなくて、番号です。(A1、B3など)
調べてみましたがなかなか見つからなかったので、
わかる方教えてください。
宜しくお願い致します。

Aベストアンサー

文字として"C4"を返す。
=ADDRESS(ROW(C4),COLUMN(C4),4)

式を入力したセルの位置を文字列で返す。
=ADDRESS(ROW(),COLUMN(),4)

ADDRESSの3つ目の引数を変えると
$C$4
$C4
C$4
C4
の4つの形に出来ます。

Qエクセル関数>参照ファイル名をセルから呼び出す

別ファイル(ブック)のセルを表示させたいのですが、例えば
=[あいうえお.xls]かきくけこ!$A$5
とせずに、
ファイル名は A1セル の文字列を充てる、というようなことは出来ないでしょうか?

関数が分からない人でも、簡単に参照したいファイル名を変えられるようにしたいのです。

よろしくお願い致します。

Aベストアンサー

A1に「あいうえお.xls」があるとき
 =INDIRECT("[" & A1 & "]かきくけこ!$A$5")
でよいでしょう。
ただし「あいうえお.xls」が開いている必要があります。(#REF!エラーになるため)

A1に入力されたブック名が開いていないなら、ブックを開くようにするのであればマクロが必要です。ブック名を入力しているのがSheet1だとしたら以下のマクロをALT+F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて保存して下さい。
するとINDIRECT関数を入力したブックを開くときに、A1セルのブックも開くようになります。ただし下から3行目のファイルパスは修正が必要です。

Sub Auto_open()
Dim wb, mybk As Workbook
Set mybk = ThisWorkbook
For Each wb In Workbooks
  If wb.Name = Sheets("Sheet1").Range("A1").Value Then
    Exit Sub
  End If
Next wb
Workbooks.Open "C:\Temp\" & Sheets("Sheet1").Range("A1").Value
mybk.Activate
End Sub

A1に「あいうえお.xls」があるとき
 =INDIRECT("[" & A1 & "]かきくけこ!$A$5")
でよいでしょう。
ただし「あいうえお.xls」が開いている必要があります。(#REF!エラーになるため)

A1に入力されたブック名が開いていないなら、ブックを開くようにするのであればマクロが必要です。ブック名を入力しているのがSheet1だとしたら以下のマクロをALT+F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて保存して下さい。
するとINDIREC...続きを読む

Qエクセル 同じ値を探して隣の数値をコピーする

エクセルで以下の作業を簡単にする方法を教えて下さい。

Sheet1の1行目には見出しがあり、A列とB列の2行目から下にデータが入っています。(約200行)
A列     B列
田中    13
山本     8
谷口    11
鈴木    6
佐々木    9
奥村     15




Sheet2のA列1行目から下には別のデータが入っています。(約600行)
A列
太田川
山村
田中
多賀先
鈴木
奥村
幸田




Sheet2のA列のデータと完全一致するデータ(名前)をSheet1のA列から探して、同じ名前があれば、その隣のB列にある数値をSheet2のB列に貼り付ける。
見つからない場合はSheet2のB列は空欄のままです。
Sheet2
A列      B列
太田川     
山村
田中    13
多賀先
鈴木     6
奥村    15
幸田


Aベストアンサー

シート2のB2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(Sheet1!A:A,A2)=0,"",VLOOKUP(A2,Sheet1!A:B,2,FALSE))

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エクセルで別ファイルのデータを読み込み自動入力させたいのですが

【顧客リスト】というファイルのsheet1に、A列を氏名、B列を住所、C列を年齢の欄として複数行入力しています。
【見積書】というファイルのセルE20に氏名を入力すると、【顧客リスト】A列から同じ値の行を探し出し、その同行のB列の値を【見積書】のE21に自動的に入力されるようにしたいと思っています。
どなたかご回答の程、よろしくお願いいたします。

Aベストアンサー

常に、【顧客リスト】と【見積書】の両方のファイルが開いているという条件で、

【見積書】のE21に
=INDEX([顧客リスト.xls]Sheet1!$B$2:$B$*,MATCH(E20,[顧客リスト.xls]Sheet1!$A$2:$A$*,0))
で良いかと。なお式中の"*"には、【顧客リスト】の際下段の行数を入れてください。

解説ですが、
まずMATCH関数で【見積書】セルE20の値が、【顧客リスト】のA列の何行目にあるかを検索し、その値をINDEX関数に入れて、B列の値を表示する方法です。

Qシート参照で変数を使いたい(EXCEL)

通常、Excelで別シートのセルを参照する時は「'シート名'!B3」といった数式になりますが、この「シート名」をユーザーに入力してもらうような仕組みを作りたいと考えています。

・あるシートのA3(例)にシート名を入力すると、B4セルに入力されたシートのB3セルの内容が表示される

このようなこと、可能でしょうか?

Aベストアンサー

INDIRECT関数を使うといいでしょう。

セルA1に入力されたシート名の、セルB3を参照する場合

   =INDIRECT(A1& "!B3")


また上記ではセルB3が固定になってますが、
セルA1 に参照したい【シート名】をいれ
セルA2 に参照したい【セル番地】まで入れておくと
なお、使い勝手がよくなるかも知れませんね。

   =INDIRECT(A1& "!" & A2)
 
 
 

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

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....続きを読む

Qエクセルで多数のシートをまとめる方法

エクセルで10以上あるシートを一発でひとつにまとめる方法はあるでしょうか?

コピー&ペーストを繰り返すしかないのでしょうか…

仕事で明日やらなければならないので、
お分かりになる方、是非教えてください!

Aベストアンサー

まぁ既出回答にもあるように,10数回程度コピー貼り付けすることで,どうという作業ではないとは思います。

さておき。
ご利用のエクセルのバージョンが不明ですが,ご利用のOfficeのバージョンによっては

1.ワードを起動する
2.挿入のファイルからエクセルブックを指定する
3.添付図のようなダイアログが表示されたらラッキー成功です,ブック全体を指定して挿入する
4.Ctrl+Aで全体を選び,コピーして,エクセルの新しいシートに貼り付ける

と一発でできます。
添付図のようにならなかったときは,出来ません。

QEXCELで別のシートのデータを参照して返す方法

ちょっとしたことなのですが、うまく式が書けなくて悩んでいます。

作業用のシート1と参照用のシート2があり、
シート1のA列には
 あおき
 あべ
 いぐち
 いはら
などと文字列(名前)が入力されています。
こちらは今度の野球のスタメンだと思ってください。

シート2にはA列とB列があって、
わたなべ 55
いぐち  43
あおやま 67
いはら  41
などと、名前:背番号が羅列されています。こちらが参照用の全選手の背番号リストだと思ってください。

ここで、 シート1のB列に、A列の名前に対応した背番号を返したいのです。
なので、シート1のB列に
IF(シート1のA列の値=シート2のA列のいずれかの値)だったとき、
適合したシート2の行のB列の値を表示する
という式を入れたいのですが、どのように式を書けばよいでしょうか。

すみませんがどなたか教えてください。

Aベストアンサー

私もその場合はVLOOKUP関数を使用しています。

「B1」=VLOOKUP(A1,Sheet2!$A:$B,2,0)


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報