如何使用公式在 Google 表格中的单元格中插入超链接? [英] How to insert hyperlink to a cell in Google sheet using formula?

问题描述

我正在尝试以一种可以使用=MATCH()"复制的方式插入一个指向单元格的超链接；功能.但是，我似乎无法找到一种在不使用 GID 的情况下链接 Google 表格中的单元格的方法.

当我右键单击并获取此单元格的链接"时我得到一个带有#gid=1933185132"的 URL到底.然而，它没有结构，我不能像我通常在 Excel 中所做的那样将它与 MATCH 公式一起使用并自动填充.

<块引用>

• #gid 永远不会改变.选项卡名称可能会更改并且公式会中断，使用 gid 更安全.
• A1 是您需要使用match、address 函数找到动态链接的部分.

我找不到有关此主题的文档，也找不到使用选项卡名称的方法.

命名范围

1. 在文档中定义一个命名范围.
2. 在单元格中选择插入链接".
3. 在链接对话框中，选择此电子表格中的命名范围".
4. 选择在步骤 1 中创建的范围的名称.
5. 点击应用"按钮.
6. 将鼠标指针移到新链接上.包含像#rangeid=nnnnnnnnnn"这样的链接的弹出窗口将出现.
7. 右键单击弹出窗口中的链接以打开浏览器的上下文菜单.
8. 选择复制链接地址"；或只是复制"功能.

根据您的浏览器，您的剪贴板现在将包含完整的 URL:

https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#rangeid=nnnnnnnnnn

或者剪贴板将只有范围 ID 片段:

#rangeid=nnnnnnnnnn

如果您只有片段，则需要将其附加到文档的 URL 以创建该范围的完整 URL.

可能还有其他一些更简单的方法来获取范围 ID，但我还没有注意到.请参阅相关问题，回答类似问题.

PS:复制命名范围的 URL 后，您可以删除按照上述步骤创建的链接.

自定义函数

在公式中使用.

简单范围:

=HYPERLINK(getLinkByRange("Sheet1","A1"), "Link to A1")

命名范围:

=HYPERLINK(getLinkByNamedRange(NamedRange"),链接到命名范围")

代码，插入脚本编辑器(工具>脚本编辑器):

function getLinkByRange(sheetName, rangeA1, fileId){//文件+工作表var file = getDafaultFile_(fileId);var sheet = file.getSheetByName(sheetName);返回 getCombinedLink_(rangeA1, sheet.getSheetId(), fileId, file)}函数 getLinkByNamedRange(name, fileId){//文件+范围+工作表var file = getDafaultFile_(fileId);var range = file.getRangeByName(name);var sheet = range.getSheet();返回 getCombinedLink_(range.getA1Notation(), sheet.getSheetId(), fileId, file)}函数 getDafaultFile_(fileId){//获取文件var 文件；if (fileId) { file = SpreadsheetApp.openById(fileId);}else file = SpreadsheetApp.getActive();返回文件；}函数 getCombinedLink_(rangeA1, sheetId, fileId, file){var externalPart = '';if (fileId) { externalPart = file.getUrl();}返回 externalPart + '#gid=' + sheetId + 'range=' + rangeA1;}

I am trying to insert a hyperlink to a cell in a fashion that can be replicated using '=MATCH()" function. However, I can't seem to figure out a method to link a cell in Google sheets without using the GID.

When I right-click and "Get link to this cell" I get a URL with "#gid=1933185132" in the end. However this has no structure and I can't use it with a MATCH formula and autofill this like I normally do in Excel.

https://docs.google.com/spreadsheets/d/sheetkey/edit#gid=1933185132

However if this is has a cell reference like so

https://docs.google.com/spreadsheets/d/sheetkey/edit#Sheet1!C12

I can easily recreate it for the MATCH function.

Question: Is there an alternate way to link cell like I have shown above? If not Can I use a formula to extract the GID of "Sheet1!C12"?

I have searched the google forums and stack overflow to the best of my extent and the only solutions I saw seemed to use scripts with "var sheet" something which I cant make sense of having 0 knowledge of coding.

It should be a very straightforward thing to do, but I am not able to find a way out. Any insight into the issue is appreciated. Thank you very much.

解决方案

Range

You can create a link like this:

=hyperlink("#gid=1166414895range=A1", "link to A1")

Each tab has a unique key, called gid, you'll find it in the link:

• #gid will never change. Tab name may be changed and the formula will break, using gid is safer.
• A1 is a part you need to find using match, address functions to get dynamic links.

I could not find a documentation on this topic, and could not find a method using tab names.

Named Range

1. Define a named range in the document.
2. In a cell select "Insert link".
3. In the link dialog box, select "Named ranges in this spreadsheet."
4. Select the name of the range created in step 1.
5. Click the "Apply" button.
6. Move mouse pointer over the new link. A pop-up containing a link like "#rangeid=nnnnnnnnnn" will appear.
7. Right click on the link in the pop-up to open your browser's contextual menu.
8. Select the "Copy Link Address" or just "Copy" function.

Depending on your browser, your clipboard will now contain either the full URL:

https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#rangeid=nnnnnnnnnn

Or the clipboard will have just the range ID fragment:

#rangeid=nnnnnnnnnn

If you have only the fragment, you'll need to append it to the URL of the document to create a complete URL for the range.

There may be some other, simpler way to get the range ID, but I've not noticed one yet. See related question, answer to a similar question.

PS: After you've copied the URL for the named range, you may delete the link that was created by following the steps above.

Custom functions

Use in a formula.

Simple range:

=HYPERLINK(getLinkByRange("Sheet1","A1"), "Link to A1")

Named range:

=HYPERLINK(getLinkByNamedRange("NamedRange"), "Link to named range")

The code, insert into the script editor (Tools > Script Editor):

function getLinkByRange(sheetName, rangeA1, fileId)
{
  // file + sheet
  var file = getDafaultFile_(fileId);  
  var sheet = file.getSheetByName(sheetName);
  
  return getCombinedLink_(rangeA1, sheet.getSheetId(), fileId, file)    
}

function getLinkByNamedRange(name, fileId)
{
  // file + range + sheet
  var file = getDafaultFile_(fileId);    
  var range = file.getRangeByName(name);  
  var sheet = range.getSheet();
  
  return getCombinedLink_(range.getA1Notation(), sheet.getSheetId(), fileId, file)
      
}


function getDafaultFile_(fileId)
{
  // get file
  var file;
  if (fileId) { file = SpreadsheetApp.openById(fileId); }
  else file = SpreadsheetApp.getActive();      
  return file;      
}

function getCombinedLink_(rangeA1, sheetId, fileId, file)
{
  var externalPart = '';
  if (fileId) { externalPart = file.getUrl(); }    
  return externalPart + '#gid=' + sheetId + 'range=' + rangeA1;   
}

这篇关于如何使用公式在 Google 表格中的单元格中插入超链接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！