[[20150726132759]] 『別ブックのセル一つコピーしたら他のいくつかのセ』(ナマコ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『別ブックのセル一つコピーしたら他のいくつかのセルも自動で参照するようにしたい』(ナマコ)

複数の顧客データが入っているブックから、顧客名(1つのセル)をコピーしたら電話番号や住所などが自動で該当ブックから参照されるようにしたいです。

='C:\顧客データ\[A.xlsx]Sheet1'!$D$2

リンク貼り付けを使って上の式をメモ.XLSXに貼り付けると、顧客名が起動時に表示されます。

上記のD2セルの左右に引っ張りたいデータがあるため、OFFSETとINDIRECTを使えばいけるかな
と思ったのですが、INDIRECT関数を使って、

=INDIRECT("'C:\顧客データ\[A.xlsx]Sheet1'!$F$2")

とすると、メモ.XLSXを開いた時点ではREFエラーとなり、A.xlsxを開いた時にリンクが更新されます。
これだとメモ.xlsxを閲覧するために、参照元のブック(100以上)を全て開かないといけません。

メモ.xlsxを起動した時点で、全てのデータを参照元ブックから引っ張ってくるにはどうすれば良いでしょうか。
必要な項目全部をリンク貼り付けする以外の方法を教えて下さい。

< 使用 Excel:Excel2007、使用 OS:Windows7 >


 >='C:\顧客データ\[A.xlsx]Sheet1'!$D$2

 その数式の「$」マークを除去する。
 ※数式全体をマウスでドラッグ選択し、白黒反転状態で、F4キーを数回押しても除去できます。

 するとこんなのになります。
     ↓
 ='C:\顧客データ\[A.xlsx]Sheet1'!D2

 後は、そのセルを左右にコピーするだけです。

(半平太) 2015/07/26(日) 23:48


説明が足りていなくてすみません。

引っ張りたいデータは 2行であれば、B列、F列、I列…と飛び飛びになっており、単純にコピーはできないんです。
複数選択して『コピー→貼り付け』ならもちろんできるんですが、その複数選択が面倒なため、どれか一つの
項目を『コピー→別ブックに(リンク)貼り付け』とした時点で、ほかの必要な項目がすべて自動的に
参照されるようにしたいのです。
(ナマコ) 2015/07/27(月) 12:13


 >複数選択して『コピー→貼り付け』ならもちろんできるんですが、その複数選択が面倒なため、どれか一つの 
 >項目を『コピー→別ブックに(リンク)貼り付け』とした時点で、ほかの必要な項目がすべて自動的に 
 >参照されるようにしたいのです。

 そうだったんですか?

 そうなると、エクセルに「どんな風に飛び飛びなのか」教えてやらなければならないのですけど、
 (1) なにか規則性があることなんですか?
 (2) それとも、規則性がないので、都度どこかのセルに何か情報を入れて、
    こんどはこんなとこにあるセルを一緒に引っ張ってくるんだよ、って教えてやる心算りなのですか?

(半平太) 2015/07/27(月) 13:46


規則性はありません。
なので、(2)の方法です。

現在やろうとしている方法は、
1.顧客のブックから顧客名をコピー
2.メモ.xlsxにリンク貼り付け (例) ='C:\顧客データ\[A.xlsx]Sheet1'!$D$2 など
3.マクロでそのリンクを別セルに書き出し
4.INDIRECT関数で文字列を読む
5.OFFSET関数で横にずらす。

ところが、現状では直接リンク貼り付けした部分(顧客名)
 ='C:\顧客データ\[A.xlsx]Sheet1'!$D$2
はメモ.xlsxを開いた時点で表示されるのですが、関数を使った部分は参照元のブック(例)A.xlsx
を開かないと表示がREFエラーになってしまう状況です。参照元ブックを開くと、電話番号や住所など、
ほしいデータを取ってくる状態です。

これを、直接リンク貼り付けしたときのように、参照元のブックを開かなくても全部のデータを取ってくる
ようにしたいのです。
(ナマコ) 2015/07/27(月) 15:51


 >規則性はありません。 
 >なので、(2)の方法です。 
    :   :
 >5.OFFSET関数で横にずらす

 「こんなとこにあるセルを一緒に引っ張ってくるんだよ」と云う
 具体的な情報に関する説明がなされていません。

 幾つ横にずれたところのセルなのか、どこで指定しているんですか?
 ※固定的な規則性はなくとも、どこでどう(動的に)指定するかに関する規則は必要です。

 あと、関連2ブックの拡張子が「XLSX」と云うことは、マクロ用のブックが別に存在する、との前提ですね?

(半平太) 2015/07/27(月) 17:26


何度もレス頂いてありがとうございます。
うまく伝えられてないので、数式をすべて書きます。
メモ.xlsxではなく、メモ.xlm でしたね。その中の数式です。

C1セル 作業用セル マクロでC3セルの数式を書き出し これもメモ.xlm起動時に正常表示される。
=GET.CELL(6,Sheet1!C3)&LEFT(NOW(),0)

C3セル 顧客名の表示セル 参照元ブックからリンク貼り付け 参照元ブックを開かなくても、メモ.xlm起動時に顧客名が表示される。
='C:\顧客データ\[A.xlsx]Sheet1'!$D$2

ここまでは動作的に問題ありません。

しかし、以下のセルからは、メモ.xlmと同時に、参照元ブックを開かないとREFエラーになります。同時に開くと、住所や電話番号などを参照して表示はできるが、参照元ブックを開かない状態でも表示されるようにしたいのです。参照元のブックが非常に多くなるためです。

C4セル 電話番号の表示セル INDIRECT関数でC1の数式を読み取り、OFFSET関数でいくつか横にずらす。電話番号は顧客名の二つ横なので二つずらしている。
=OFFSET(INDIRECT(MID(C1,2,100)),0,2)

以下、C5、C6、・・・とOFFSET関数の最後の数字を変えることで、いくつ横にずれるか指定しています。
(ナマコ) 2015/07/27(月) 18:03


 ようやく意味が分かりました。

 >=OFFSET(INDIRECT(MID(C1,2,100)),0,2) 

 そう云う数式は使えないです。
 ※INDIRECT関数は、対象となるブックが開いてないと正常に動作しません。

 ・・・で、結局のところ、規則性がなく、
 「数式文字列」と「各セルの数式の中に書かれたオフセット値」だけが頼り、となると厳しいです。

 まぁ、苦労する気なら、C1セルにオフセット値を持たせるようなことで、そこそこのVBA案は作れるかも知れませんが、
 また前提条件等について色々お聞きしなければならないですから、苦労対効果が悪すぎの感があります。

 なので、ここで私は降ります。ご健闘を祈ります。

(半平太) 2015/07/27(月) 21:56


こんにちは

どうせマクロが設定してあるなら、WorkBook_Openイベントで

Private Sub Workbook_Open()

    Dim mBk As Workbook
    Application.ScreenUpdating = False
    Set mBk = Workbooks.Open("C:\My Documents\A.xlsx")  'A.xlsxのフルパス
    DoEvents
    mBk.Close False
    Application.ScreenUpdating = True
End Sub

のようにしておいてはどうですか?

(ウッシ) 2015/07/28(火) 08:21


半平太さん、どうもありがとうございました。

ウッシさん

参照元となるブックはA.xlsxだけでなく、100以上ある(場合によっては1000を超え、しかもどんどん増える)ため、そのすべてを記述することはできません。

データの格納場所は固定されているのですが、
このマクロをフォルダ単位で指定することはできますか?
(ナマコ) 2015/07/28(火) 12:00


こんにちは

参照元ブックが多すぎて、どの位の時間が掛かるか分からないですけど、

Private Sub Workbook_Open()

    Dim mSh As Worksheet
    Dim tR  As Range
    Dim mR  As Range
    Dim d   As Object
    Dim s   As String
    Dim aBk As Workbook
    Dim mBk As Workbook

    Set d = CreateObject("Scripting.Dictionary")

    On Error Resume Next
    Application.ScreenUpdating = False
    For Each mSh In ThisWorkbook.Worksheets
        Set tR = mSh.UsedRange.SpecialCells(xlCellTypeFormulas)
        If Not tR Is Nothing Then
            For Each mR In tR
                If mR.Formula Like "='*" Then
                    s = Replace(Mid(mR.Formula, 3, InStr(3, mR.Formula, "]") - 3), "[", "")
                    If Not d.exists(s) Then
                        Debug.Print s
                        d.Add s, ""
                        Set mBk = Workbooks.Open(s)
                    End If
                End If
            Next
        End If
    Next
    ThisWorkbook.Activate
    ActiveWorkbook.RefreshAll

    For Each aBk In Workbooks
        If aBk.Name <> ThisWorkbook.Name Then
            aBk.Close False
        End If
    Next
    Application.ScreenUpdating = True
    On Error GoTo 0
End Sub

100〜1000ブック開いたらメモリがアウトかも知れないです。

どこかの数式で再計算が走ったら、

=OFFSET(INDIRECT(MID(C1,2,100)),0,2)

のような数式は結局エラーになります。

(ウッシ) 2015/07/28(火) 14:12


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.