Excel超链接到一个特定的单元格。

I need to hyperlink a cell in one spreadsheet to a corresponding cell in another spreadsheet. So for example, C7 in sheet1 has a hyperlink that will bring you to C7 in sheet 2. I need to do this for a fairly large spreadsheet and each hyperlink has to be different. Is there a way to do this on mass without having to go to each cell and set the hyperlink for each cell independently? (Using MS Office 2010)

我需要将一个电子表格中的单元格链接到另一个电子表格中的相应单元格。例如，sheet1中的C7有一个超链接它会把你带到图2中的C7。我需要为一个相当大的电子表格做这个，每个超链接必须是不同的。有没有一种方法可以在质量上做到这一点而不需要分别去每个细胞并为每个细胞设置超链接?(使用MS Office 2010)

3 个解决方案

#1

You can use the following excel formula: (paste into cell C7)

您可以使用以下excel公式:(粘贴到单元格C7中)

=HYPERLINK("[Book1.xlsx]Sheet2!"&CELL("address",C7),"click")

Notes:

注:

[Book1.xlsx] must be the name of the workbook
[Book1。必须是工作簿的名称
Sheet2 must nbe name name of the sheet you are hyperlinking to
Sheet2必须是要超链接到的表的名称

Essentially it uses the above two as a prefix to the link, and then the address of the current cell (c7 in the case of your example) to finish the link.

本质上，它使用上述两个作为链接的前缀，然后使用当前单元格的地址(在本例中为c7)完成链接。

The above example once pasted into cell C7 can be dragged down to generate links based on the formula cell's address.

上面的示例一旦粘贴到单元格C7中，就可以根据公式单元格的地址向下拖动以生成链接。

Update: (per chris)

更新:每克里斯()

=HYPERLINK("#'Sheet2'!"&CELL("address"),"click")

#2

Three years late, I would go a bit further and use ADDRESS(row,column) to build the cell address, rather than use CELL() which is a volatile function. If you're building a large spreadsheet and using a volatile function more than just a handful of times, you're going to notice the performance hit.

三年之后，我将更进一步，使用ADDRESS(行、列)来构建单元地址，而不是使用cell ()， cell()是一个可变函数。如果您正在构建一个大型电子表格，并使用一个volatile函数，次数不止几次，那么您将会注意到性能受到的影响。

ADDRESS() is not volatile, so it doesn't trigger a recalculation all the time, and it's also more flexible to use.

ADDRESS()不是易变的，所以不会一直触发重新计算，而且使用起来也更灵活。

=HYPERLINK("#'Sheet2'!"&ADDRESS(ROW(),COLUMN()),"click")

Replace ROW() and COLUMN() with whatever number you require.

用所需的任何数字替换ROW()和COLUMN()。

For example, for a specific cell in Sheet2 use

例如，对于Sheet2中使用的特定单元格

=HYPERLINK("#'Sheet2'!"&ADDRESS(ROW(Sheet2!C7),COLUMN(Sheet2!C7)),"click")

If you want Sheet2, third column, and 1 row below (relatively), use

如果您想要Sheet2、第三列和以下一行(相对而言)，请使用

=HYPERLINK("#'Sheet2'!"&ADDRESS(ROW()+1,3),"click")

#3

Sorry to nitpick, it also can look like this:

不好意思挑剔一下，也可以这样:

" - starting quote
# - local book (spreadsheet)
'Sheet2' - name of sheet you are going to (has to be in single quotes)
!C7 - cell in the other sheet you are trying to go to
"- ending quote
, - separating comma used in the hyperlink syntax
"click" - link text to appear in cell

Final function syntax:

最后的功能语法:

=HYPERLINK("#'Sheet2'!C7","click")

#1