Shulou(Shulou.com)11/24 Report--

Hello, everyone! I'm Chang Xiao an.

Let me guess, is your computer like mine? there are so many files in the folder that looking for files is like looking for a needle in a haystack.

▲ contains multiple files in each folder, although "my computer" has its own search function, you can search with keywords directly in the search bar:

But the speed of retrieval is too slow, usually find the specified file is already.

So I had this idea:

Is it possible to make a file directory with Excel? You can open the file directly by clicking the "Open File" button.

With this in mind, I tried to use Excel to make a directory like this:

Doesn't it look much more convenient?

There are two ways to make such a catalog, Xiao an.

1. Import the file information into the Power Query editor by Power Query method, pick out the "file path and file name", and upload it to Excel.

Then use the HYPERLINK function to make hyperlinks on it!

Procedure:

❶ prepares the file and confirms the file address, for example, my current file is on the desktop.

❷, click * * data * *-> * * get data * *-> * * from File * *-> * * Browse * *-click the folder where you want to create a directory-* * OK * *, and upload it to Power Query.

❸ click [convert data]-press and hold the [Ctrl] key to select "Name" and "Folder Path" columns-right-click "Delete other columns"-[close and upload].

The file information processed in this way is uploaded to Excel.

❹ adds a column to the far right of the table area and enters the formula:

= HYPERLINK ([@ [Folder Path& [@ Name], "Open File")

In order to make the data beautiful, ❺ right-clicked the "Folder Path" column to hide.

Well, such a directory is finished, click "Open File" in column C, and you can open the corresponding file in column A.

PS:Power Query requires Office version 2013 or above

In addition to PQ,Excel, there is another very powerful function that you may listen to more, but use less-VBA.

All right, needless to say, let's move on to the second way to make a catalog.

02. Webpage method if you do not want to install the Power Query plug-in on your computer, you can also use the webpage method to make a file directory.

Procedure:

❶ opens the folder where you need to make a directory-copy path-paste the path in the browser-[enter].

❷ [Ctrl+A] Select all the data, copy and paste it into Excel.

❸ selects columns B and C, [Ctrl -] deletes columns B and C, and deletes "index of".

❹ enters the formula in column B:

The full path of the folder is connected to the data under "name" after the PS:A1 is locked. = HYPERLINK ($Ayog1century A4Magol A4)

❺ click on the formula to open the corresponding folder.

03. To sum up, two methods of making file directories are introduced in this paper:

❶ PQ method: after importing the file information into the PQ editor, pick out the information related to the address, and use the formula to generate hyperlinks to make the file directory.

❷ web page method: open the file path in the web page, then paste it into Excel, and then use the formula to generate a hyperlink to make the file directory.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Chang Xiaoan, Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.