如何在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.

我的文件夹的结构如下:

My folder is structured like:

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屋！