別ブックから値を参照引用する方法の、一つの案を紹介します。
多くの記事で、ブックを開いてコピペする方法が紹介されていると思います。
しかし、これですと、他の人が開いている時には読み取り専用で開く、など色々な配慮が必要です。
ここで紹介する、マクロから関数を入力する方法であれば、そういった煩わしさがありません。
別ブックを参照引用する関数
セルに、次のような関数を入力すると、別ブックの値が参照引用できます。
=’C:\Users\★★★\Desktop\[test2.xlsx]Sheet1′!A1これと同じ内容を、マクロを使って入力します。
準備
下記のように、別ブックから値を持って来たい、という想定です。
・準備として、どこかのセルに、参照したい別ブックのアドレスを入力しておきます。
仮に、[セル：D1] に参照したいブックのアドレスを記載したとして話を進めます。
※[設定] という名前の別シートを用意して、ここにアドレスを記載しておくのも良いですね。
この [セルに記載されたアドレス] を元に、マクロから関数を使います。
・この [セルに記載されたアドレス] は、書式設定で [文字列] に指定しておきます。
[セル：D1] を選択 > 右クリック > セルの書式設定 > 表示形式 > 文字列
コード
一つのセルを参照引用
Sub 別ブック参照()
'【別ブックを参照引用する関数】は、セル：D1 に記載されている前提です
'一旦セルをクリア
Range("A1").ClearContents
'関数が文字列になったりしない様に、念の為にセルの書式設定を標準にする
Range("A1").NumberFormatLocal = "G/標準"
'値を貼り付けたいセルに、【別ブックを参照引用する関数】を挿入する
Range("A1").Value = Range("D1").value
'関数の再計算を実行する
Range("A1").Calculate
'関数が残ると動作が重たいので、値を上書きする
'値に値を上書きすると、関数が消えて、値だけが残ります
Range("A1").Value = Range("A1").Value
MsgBox "完了"
End Sub
範囲を参照引用
範囲を参照引用するには、先程の コード に加えて、オートフィル という機能を使用します。
オートフィル は、関数をコピーしてくれます。
流れとしては、
・一つのセルを参照引用
・上記のセルを縦にオートフィル
・上記の範囲を、さらに横にオートフィル
Sub 別ブック参照_範囲()
'【別ブックを参照引用する関数】は、[セル：D1] に記載されている前提です
'範囲の指定
Dim 範囲 As Range
Set 範囲 = Range("A1:C10")
'一旦範囲をクリア
範囲.ClearContents
'関数が文字列になったりしない様に、念の為に範囲の書式設定を標準にする
範囲.NumberFormatLocal = "G/標準"
'値を貼り付けたいセルに、【別ブックを参照引用する関数】を挿入する
Range("A1").Value = Range("D1").Value
'縦列のオートフィル
Range("A1").AutoFill Destination:=Range("A1:A10"), _
Type:=xlFillDefault
'横行のオートフィル
Range("A1:A10").AutoFill Destination:=範囲, _
Type:=xlFillDefault
'セル内を再計算実行
範囲.Calculate
'コピー_上書き貼り付け
範囲.Value = 範囲.Value
MsgBox "完了"
End Sub
まとめ
別ブックを参照引用する方法の紹介でした。
このコードの特徴は、下記の三点です。
①マクロを使って、セルに関数を入力する
②上記の関数をオートフィルで範囲拡張できる
③最終的には関数を残さず、値だけにする
④参照元のファイルが開かれていても機能する
特にお気に入りは、③です。
関数が存在すると、ブックを開いた際に、関数の計算が自動実行されます。
これがブックを開く速度を重くするので、関数を使うが関数は残さない、というのがミソです。
④については、複数人で使用しているファイルを集計する時に便利です。
おまけ
参照元のセルが結合されていた場合、どうなるのでしょうか？
結果は、結合されて存在しないセルは、参照引用の結果は、ゼロ となります。
コメント