如何在 Excel 中对另一个工作簿进行相对引用? [英] How do I make a relative reference to another workbook in Excel?

问题描述

我正在制作一张表格来计算价格.该工作表必须引用其他几个工作簿才能获得不同组件的价格.这在我的计算机上运行良好，但是当我将它们移动到服务器或另一台计算机时，它找不到引用.

I am producing a sheet to calculate prices. The sheet has to have a reference to several other workbooks to get the prices for different components. This works fine on my computer but when I move them to the server or another computer, it can't find the references.

我的文件夹结构如下:

Folder
|-- prices.xlsx
|-- Fixed Components
|   |-- ComponentsA.xlsx
|   +-- ComponentsB.xlsx
|
+-- Variable Components
    |-- ComponentsC.xlsx
    +-- ComponentsD.xlsx

prices.xlsx 是引用其他工作表的主工作表.在我的计算机上，它使用绝对路径构建引用，因此当我复制文件时，路径保持固定在我的机器上，而不是引用另一台 PC 上的文件.

prices.xlsx is the main sheet that references the other sheets. On my computer it builds the references with an absolute path, so when I copy the files the path stays fixed to my machine, instead of referencing the the files on the other PC.

有什么方法可以使引用相对，以便我可以在主表中放入类似 ='Variable Components[ComponentsC.xlsx]Sheet1'!A1 的内容.我不想为此使用 VBA，因为人们不倾向于信任宏，然后抱怨函数不起作用.

Is there any way to make the references relative so that I can put in the main sheet something like ='Variable Components[ComponentsC.xlsx]Sheet1'!A1. I would not want to use VBA for this as people don't tend to trust Macros and then complain the functions don't work.

推荐答案

我见过的将外部文件组织到子文件夹中的唯一解决方案需要使用 VBA 来解析外部文件的完整路径公式.这是一个站点的链接，其中包含其他人使用过的几个示例:

The only solutions that I've seen to organize the external files into sub-folders has required the use of VBA to resolve a full path to the external file in the formulas. Here is a link to a site with several examples others have used:

http://www.teachexcel.com/excel-help/excel-how-to.php?i=415651

或者，如果您可以将所有文件放在同一个文件夹中，而不是将它们分成子文件夹，那么即使您将文件移动到网络位置，Excel 也会解析外部引用而无需使用 VBA.然后您的公式就变成了简单的 ='[ComponentsC.xlsx]Sheet1'!A1，没有要遍历的文件夹名称.

Alternatively, if you can place all of the files in the same folder instead of dividing them into sub-folders, then Excel will resolve the external references without requiring the use of VBA even if you move the files to a network location. Your formulas then become simply ='[ComponentsC.xlsx]Sheet1'!A1 with no folder names to traverse.

这篇关于如何在 Excel 中对另一个工作簿进行相对引用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！