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

質問

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

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

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

という式みたいなので、
='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() とかで挿入できるでしょうか？
日付の入っているセルがちょうど横の方に連番で存在しています。

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

回答No.1

kybo · ベストアンサー

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

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

お礼

INDIRECTで出来ましたー。
どうもありがとうございます。

日付がちょっと、2010.06.01と2010/0601だったり
ファイル名と微妙に違っていたのでMIDで出来ないかなと
思ったんですがそれは調整しました。

でもこれ、参照先を開いてないといけないんですね。。。
何十個も開けないですし、根っこの部分が盲点でした……。

回答No.5

keithin

日付の整形はTEXT関数でします。

A4に日付が記入してあるなら
="='item\zaiko\[zaiko_" & TEXT(A4,"yyyy_mm_dd") & ".xls]在庫シート'!B5"
で数式の文字列を作成し，コピーして形式を選んで値のみ貼り付けてから，=を=に全て置換。

で。マクロを使ってしまうなら，そんなに手の込んだ道具を使わなくても「数式をそのままマクロで書き込ませてしまえ」ば出来ます。マクロではTEXT関数の代わりにFormat関数を使います。

sub macro2()
dim h as range
for each h in range("A4:A28")
h.offset(0, 1).formula = "='item\zaiko\[zaiko_" & format(h, "yyyy_mm_dd") & ".xls]在庫シート'!B5"
next
end sub

お礼

ありがとうございます。
今回はマクロで日付の入っているセルをループさせて読もうと思います。

形式とかが変わったら参考にさせてもらいます。

回答No.4

imogasi

質問が１とびして難しいほうに行っているのですが、本当は学習という点から言えば
学ぶのは下記の（１）ー＞（２）の順である。
下記の（２）のようにデータを他ブックに別々に保存すると、後の利用の際に
色々な点で、難しくなる。
（１）同一ブックの複数シートの場合
Sheet1からSheet4のA1セルにA,B,C,Dと入れておく
Sheet1のC1にSheet1と入れる
+ハンドルを出してC4まで引っ張る。
Sheet1
Sheet2
Sheet3
Sheet4
となる。
E1セルに
=INDIRECT(C1&"!A1")と入れて下方向に引っ張る
結果
A
B
C
D
のようにSheet1からSheet4のA1の値を持ってくる。
（２）別々のブックの場合
同じような考え方だが、そのブックを開いておかないと
そのブックのそのシートのセルを参照できない。
そこでVBAなら
開いているsheetに
01化
01化B
01化c
のように「同一フォルダに在るExcelブック名」を例えで３つ書き上げておく。
Sub test01()
For i = 1 To 3
Path = "C:\Documents and Settings\OTO\My Documents"
file = Cells(i, "C")
Cells(i, "A") = ExecuteExcel4Macro("'" & Path & "\[" & file & ".xls]" & "Sheet1" & "'!R1C1")
Next i
End Sub
/は￥で置き換えること。
をVBEの画面に貼り付けて実行すると、結果は
A1:A3に
シリアル番号
コード
室料
と取れる。たまたま私の場合には上記３つのブックのSheet1のA1セルには、上記のデータが入っていたもの。
この場合は見かけ！では、各上記ブックを開いているようには見えない。
これを質問の場合も使えると思う。

お礼

ありがとうございます。
順番にやってみました。
VBAも、入れてみたら動いてくれたので色々といじってやってみます。

'完成品のシート４行目から２８行目まで入れる処理
For i = 4 To 28
'X列について。ファイル名の日付部分はA列から参照する
Cells(i, "X") = ExecuteExcel4Macro("'item\zaiko\[zaiko_" & Cells(i, "A") & ".xls]在庫シート'!R7C3")
Next i

回答No.3

KURUMITO

Ａ1セルから下方に2010-06-01のように日付が入力されているとしたら次のように式を作って下方にオートフィルドラッグすればよいでしょう。

=INDIRECT("'item￥zaiko￥[[zaiko_"&A1&".xls]在庫シート'!B5")

お礼

その式で挿入の目的は果たせましたー。
どうもありがとうございます。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

残念ながらそのようなことはできません。
Indirect関数を使うとできるのですが、対象のブックを開いておく必要があります。
しかし、一方通行(下記で言うA1,B1セル)を変化させても、求める結果がわからなくて良い。
または、質問文になるような数式を書く手間を省きたい。
ならば方法があります。下記を参考にして、10セル位で試してから行ってみてください。
A1 C
B1 3
C1 ="="&A1&B1
C1セルコピー
E1セルに形式を選択して貼り付け 値
E列を = から = へ置換。

お礼

文字を結合して作った文字をさらにセル指定で使用できるんですね。
"="を最初文字で作っておいて増やしてから値に変換で計算させると。
これは応用が利きそうです。どうもありがとうございました。