动态创建超链接_创建动态超链接

动态创建超链接

Last week, I heard from Kevin Lehrbass, who runs the My Spreadsheet Lab website. Kevin has posted an Excel video on YouTube, that shows how you can make a dynamic hyperlink, using array formulas.

上周，我收到了运行My Spreadsheet Lab网站的Kevin Lehrbass的来信。 凯文(Kevin)在YouTube上发布了一个Excel视频，展示了如何使用数组公式创建动态超链接。

Select a country from a drop down list, and click on the hyperlink to go to the selected country’s largest city. Well, you’ll go to that location in the table – you won’t actually end up in the city!

从下拉列表中选择一个国家，然后单击超链接转到所选国家最大的城市。 好吧，您将转到表格中的该位置-您实际上将不会最终进入城市！

选择一个国家 (Select a Country)

At the top of the worksheet, Kevin created a drop down list of countries, by using data validation. You’ll start by selecting a city in that cell.

在工作表的顶部，Kevin通过使用数据验证创建了一个国家/地区下拉列表。 您将从在该单元格中选择一个城市开始。

找到最大的人口 (Find the Largest Population)

In the cells below the city, Kevin uses array formulas to find the largest population for that country, and the row in which that population is found.

在城市下面的单元格中，凯文使用数组公式查找该国家/地区的最大人口以及该人口所在的行。

If you need help with this step, you can see the instructions for MIN IF and MAX IF on my website, and watch my short video. It uses an example with office supply quantities.

如果您需要有关此步骤的帮助，可以在我的网站上查看有关MIN IF和MAX IF的说明，并观看我的简短视频。 它以办公室供应数量为例。

创建一个超链接 (Create a Hyperlink)

Next, Kevin creates a hyperlink formula that uses the row number and city name. The # sign at the start of the reference is how you link to a cell within the workbook.

接下来，凯文创建一个使用行号和城市名称的超链接公式。 参考开始处的＃号是您如何链接到工作簿中的单元格的方法。

Click on the link to go to the selected country’s largest city. Of course, you’ll go to that location in the table – you won’t actually end up in the city.

单击链接转到所选国家/地区最大的城市。 当然，您将转到表格中的该位置-您实际上不会最终到达城市。

You can find more details on the HYPERLINK formula, and another short video, on my Contextures site: Excel HYPERLINK Function

您可以在我的Contextures网站上找到有关HYPERLINK公式的更多详细信息和另一个短片： Excel HYPERLINK函数

观看视频 (Watch the Video)

To see how to create the dynamic hyperlink, watch Kevin’s video. At the end of the video, Kevin combines the separate formulas into one large formula. You could skip that step, if you’d rather, and just hide rows 8 and 9, with the population and row formulas.

要查看如何创建动态超链接，请观看Kevin的视频。 在视频的结尾，Kevin将单独的公式组合为一个大公式。 如果愿意，您可以跳过这一步，只用填充和行公式隐藏第8行和第9行。

演示地址

翻译自: https://contexturesblog.com/archives/2013/04/04/create-a-dynamic-hyperlink/

动态创建超链接