☰

如何向输出值添加超链接以将其链接到其来源

tag-icon • tag-icon microsoft-excel
如何向输出值添加超链接以将其链接到其来源

我有以下公式，可输出另一张工作表中的值。它正常运行。

=IFERROR(INDEX(Planner!$B$11:$B$164,SMALL(IF(($F$4=Planner!$A$11:$A$330),ROW(Planner!$A$11:$A$308)-MIN(ROW(Planner!$B$11:$B$373))+1,""),ROWS($A$1:A1))),"")

上述公式和函数对我来说是新的，所以我不太清楚如何添加链接。

上面的公式循环遍历数据以检查行$F$4中是否有数据，因此添加超链接会更复杂一些。

我如何向输出添加超链接，以便它超链接到源单元格的位置？

主表

在此处输入图片描述

计划表

在此处输入图片描述

答案1

这是一个有趣的问题，要得到答案需要了解一些原理：

  1. 您实施的公式称为“第 n 次查找”。普通 Excel VLOOKUP 将仅返回第一个匹配项，但使用“第 n 次查找”，您可以创建所有匹配条目的列表（如果匹配，则每个列表条目包含其行索引号，如果不匹配，则包含“”），然后使用 SMALL 函数返回第 n 个最小匹配行索引号。在这种情况下，n 是从公式所在的当前行号派生出来的，因此当您向下复制和粘贴“第 n 次查找”公式时，您将连续返回下一个匹配的行索引。最后，公式使用 INDEX 从该索引处的 B 列中查找关联值。
  2. INDEX 返回值作为引用，这意味着它也可以用作需要引用的其他函数（例如 CELL）的参数
  3. HYPERLINK 可以生成指向当前打开的 Excel 文件中单元格的链接，但需要对该单元格的文本引用，而不仅仅是普通引用。这是因为 HYPERLINK 主要用于通过文本 URL 链接到网站或文档。幸运的是，我们可以使用 CELL 函数返回指向当前工作簿中任何引用的文本 URL 或地址，例如=HYPERLINK(CELL("address",Sheet1!A1),"Go to cell A1")

现在我们已经准备好了所有材料来混合出一个可行的配方：

=HYPERLINK(CELL("address",__INDEX PART OF YOUR FORMULA__),__YOUR WHOLE FORMULA__)

所以

=HYPERLINK(CELL("address", INDEX(Planner!$B$11:$B$373,SMALL(IF(($F$4=Planner!$A$11:$A$373),ROW(Planner!$A$11:$A$373)-MIN(ROW(Planner!$B$11:$B$373))+1,""),ROWS($A$1:A1)))), IFERROR(INDEX(Planner!$B$11:$B$373,SMALL(IF(($F$4=Planner!$A$11:$A$373),ROW(Planner!$A$11:$A$373)-MIN(ROW(Planner!$B$11:$B$373))+1,""),ROWS($A$1:A1))),"")

我们必须删除 IFERROR 部分，因为 IFERROR 强制 INDEX 返回的引用解析为其值，这意味着 CELL 无法解析它。不幸的是，当不再有任何匹配的行返回时，超链接将返回 #NUM！错误。要解决这个问题，我们只需将 IF 的 ELSE 部分返回的“”替换为列表中的最后一个索引号，即 373-11+1

=HYPERLINK(CELL("address", INDEX(Planner!$B$11:$B$373,SMALL(IF(($F$4=Planner!$A$11:$A$373),ROW(Planner!$A$11:$A$373)-MIN(ROW(Planner!$B$11:$B$373))+1,373-11+1),ROWS($A$1:A1)))), IFERROR(INDEX(Planner!$B$11:$B$373,SMALL(IF(($F$4=Planner!$A$11:$A$373),ROW(Planner!$A$11:$A$373)-MIN(ROW(Planner!$B$11:$B$373))+1,""),ROWS($A$1:A1))),"")

答案2

您可以使用以下内容，准确使用您当前使用的公式段。

它将显示查找结果，但超链接将转到所定位值的实际单元格。

HYPERLINK("#Planner!B"  &  TargetRow,  Target)

对于Target，替换整个当前公式。这是将显示输出单元格中定位值的部分。

因为TargetRow您只需要当前公式的一部分，即生成INDEX返回行的那部分。

因为您知道工作表，所以只需将其作为文本输入即可，这听起来很合乎逻辑。但您也知道在找到的行中所需的列：B 列。虽然在考虑如何创建超链接时，将其作为文本输入可能听起来不合逻辑，但一旦您想到了，这样做是很自然的。

所以放在一起，它看起来像这样:(直到你可以移动用户前进并使用LET）

= HYPERLINK("#Planner!B"  &  SMALL(IF($F$4=Planner!$A$11:$A$330,ROW(Planner!$A$11:$A$308)-MIN(ROW(Planner!$B$11:$B$373))+1,""),ROWS(Sheet1!$A$1:A1))+10,  IFERROR(INDEX(Planner!$B$11:$B$164, 
SMALL(IF(($F$4=Planner!$A$11:$A$330),ROW(Planner!$A$11:$A$308)-MIN(ROW(Planner!$B$11:$B$373))+1,""),ROWS($A$1:A1))),""))

我想到了一个潜在的格式化怪癖。我的版本是 2205，当我单击单元格中的任意位置时，它都会激活超链接效果。在此之前我使用的每个版本中（考虑到HYPERLINK可能六个月内不创建新公式），只有在格式化单元格时选择非标准水平对齐选项时，才会出现整个单元格单击功能。否则，只有当我直接单击显示的内容而不是空白处时才会发生这种情况。如果您的用户报告“嗯，超链接不起作用”，并且这适用于任何超链接，而不仅仅是这些，则需要考虑这一点。相反，如果您遇到“可点击的空白处选择单元格，而不是超链接”效果，那么这也可以在其他应用程序中使用。

（有趣的是，当我使用LET（在您标记问题的 Excel-2010 中不可用）时，公式结果不会被格式化为超链接。因此，如果您最终可以使用该函数使公式看起来很简单，则必须手动格式化公式的单元格以看起来像超链接（带下划线，蓝色），尽管单击的单元格的超链接不会变为紫色，表明它至少被使用过一次。）

相关内容