30 de funcții Excel în 30 de zile: HYPERLINK

Ieri la maraton 30 de funcții Excel în 30 de zile we did the text replacement using the function SUBSTITUI (SUBSTITUTE) and made flexible reports with it.

În a 28-a zi a maratonului, vom studia funcția HYPERLINK (HYPERLINK). Instead of manually creating hyperlinks using the Excel Ribbon command of the same name, you can use this feature.

So let’s get into the details of the function HYPERLINK (HYPERLINK) and examples of its use. If you have additional information or examples, please share them in the comments.

Funcţie HYPERLINK (HYPERLINK) creates a link that opens a document stored on a computer, network server, local network, or the Internet.

Funcţie HYPERLINK (HYPERLINK) allows you to open documents or navigate to specific locations in a document. With it you can:

  • Create a link that navigates to a specific location in the same file.
  • Create a link to an Excel document in the same folder.
  • Create a link to a website.

Funcţie HYPERLINK (HYPERLINK) has the following syntax:

HYPERLINK(link_location,friendly_name)

ГИПЕРССЫЛКА(адрес;имя)

  • link_location (address) – A string of text that specifies the location of the desired location or document.
  • friendly_name (name) is the text that will be displayed in the cell.

If you are not sure that you can create the correct reference for a function HYPERLINK (HYPERLINK), insert it manually using the command Hyperlink (Hyperlink), which is located on the tab Insera Excel ribbons. This way you will learn the correct syntax, which you then repeat for the argument link_location (address).

Example 1: Referencing a location in the same file

There are several different ways to create a text string for an argument link_location (address). In the first example, the function ADRESA (ADDRESS) returns the address for the first row and first column in the worksheet whose name is specified in cell B3.

Simbol # (pound sign) at the beginning of the address indicates that the location is in the current file.

=HYPERLINK("#"&ADDRESS(1,1,,,B3),D3)

=ГИПЕРССЫЛКА("#"&АДРЕС(1;1;;;B3);D3)

30 de funcții Excel în 30 de zile: HYPERLINK

Also, you can use the operator & (concatenation) to blind the link address. Here the sheet name is in cell B5 and the cell address is in C5.

=HYPERLINK("#"&"'"&B5&"'!"&C5,D5)

=ГИПЕРССЫЛКА("#"&"'"&B5&"'!"&C5;D5)

30 de funcții Excel în 30 de zile: HYPERLINK

To refer to a named range in the same Excel workbook, simply provide the range name as an argument link_location (address).

=HYPERLINK("#"&D7,D7)

=ГИПЕРССЫЛКА("#"&D7;D7)

30 de funcții Excel în 30 de zile: HYPERLINK

Example 2: Referencing an Excel file in the same folder

To create a link to another Excel file in the same folder, simply use the filename as an argument link_location (address) in function HYPERLINK (HYPERLINK).

To specify the path to a file that is one or more levels higher in the hierarchy, use two periods and a backslash (..) for each level.

=HYPERLINK(C3,D3)

=ГИПЕРССЫЛКА(C3;D3)

30 de funcții Excel în 30 de zile: HYPERLINK

Example 3: Linking to a website

Utilizarea funcțiilor HYPERLINK (HYPERLINK) You can link to pages on websites. In this example, the site link is assembled from text strings, and the site name is used as the argument value. friendly_name (name).

=HYPERLINK("http://www." &B3 & ".com",B3)

=ГИПЕРССЫЛКА("http://www."&B3&".com";B3)

30 de funcții Excel în 30 de zile: HYPERLINK

Lasă un comentariu