☰

2 つの異なるブックから Excel をオフセットし、もう一方に影響を与えずに一方のコピーを作成します。

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba microsoft-excel-2007
2 つの異なるブックから Excel をオフセットし、もう一方に影響を与えずに一方のコピーを作成します。

私は 2 つの異なるワークブックを使用しています。1 つ目は [Excel データのみ] で、データベースとして作成され、2 つ目は [請求書テンプレート] で、請求書を作成するために作成されました。

請求書を作成するときに、オフセットを使用して、たとえばクライアントの所在地のリストを読み取ります。

=OFFSET('[Excel Data Only.xlsx]Channels'!$A$2,MATCH(Invoice!$C$1,'[Excel Data Only.xlsx]Channels'!$A$2:$A$198,0)-1,1,COUNTIF('[Excel Data Only.xlsx]Channels'!$A$2:$A$198,Invoice!$C$1),1)

そしてそれは正常に動作しています。

問題は、たとえばデスクトップ上の異なるパスを持つワークブック[請求書テンプレート]のコピーを作成したい場合、オフセットが次のように変更されることです。

=OFFSET('C:\Users\anthony\Desktop\[Excel Data Only.xlsx]Channels'!$A$2,MATCH(Invoice!$C$1,'C:\Users\anthony.boulos\Desktop\[Excel Data Only.xlsx]Channels'!$A$2:$A$198,0)-1,1,COUNTIF('C:\Users\anthony.boulos\Desktop\[Excel Data Only.xlsx]Channels'!$A$2:$A$198,Invoice!$C$1),1)

そのため、[Excel データのみ] ワークブックのパスは自動的に変更され、他のパスではなく [請求書テンプレート] のパスを変更しただけなので、アクセスできなくなります。

では、[請求書テンプレート] のパスを変更するときに、[Excel データのみ] のパスに影響を与えないように、この数式をどのように管理すればよいでしょうか。

助けてくれてありがとう！

答え1

外部参照を含む Excel ファイルを開くと (セキュリティ警告が表示されたら更新を許可する)、Excel は参照を解決するために何らかの自動処理を実行するようです。ソースとターゲットの両方を移動するケース、特にドライブ文字の変更に最適化されているようです(フロッピー ディスク時代の名残でしょうか)。

残念ながら、ターゲット ファイル (Invoice Template.xlsxこの例では) のみを移動すると、問題が発生する可能性があります。

おそらく、最も早く問題を解決する方法は、リボンedit linksにあるダイアログを使用することですData。そこにはすべての外部ファイルのテーブルがあり、それらが指している場所を変更できます ( change source)。そこにパスを修正するとExcel Data Only.xlsx、スプレッドシート内のこのファイルへのすべての参照が一度に更新されます。

答え2

バッチで更新する必要がある大量のファイルで問題が発生している場合 (または、冒険したい気分の場合、最初にバックアップを忘れないでください)、raw ファイルを編集してみることができます*.xlsx。これは実際には zip アーカイブです。

アーカイブエクスプローラー（7-ジップたとえば、サブディレクトリ に外部参照が見つかります\xl\externalLinks\_rels\。おそらく、 (番号は異なる場合があります) という名前のファイルがありますexternalLink1.xml.rels。これは、(相対) ファイルパスと名前を含む XML ファイルです。そこで更新できます。次のように入力されます。.../externalLinkPathファイルの場所を基準としてマークします。そのタイプを に変更すると、http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing問題が解決する場合もあります。

externalLink1.xml.rels は次のようになる可能性がありますが、そのパスを具体的にテストしたわけではありません。

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
      <Relationship 
          Id="rId1" 
          Type="http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing"
          Target="'C:\Users\anthony\Desktop\channels%20(BE%20VERY%20VERY%20PRECAUTIOUS%20WITH%20THIS%20FILE)!!!!\0000%20TEMPLATES\INVOICE%20template\New%20template\Excel%20Data%20Only.xlsx "
          TargetMode="External"
      />
 </Relationships>

PS: Excel ファイルやパスで特殊文字 (括弧、感嘆符など) を使用することはお勧めしません。これも問題の原因となる可能性があります。

関連情報