复杂工作簿的Excel工具

最新推荐文章于 2022-08-01 11:36:30 发布

culiao2169

最新推荐文章于 2022-08-01 11:36:30 发布

阅读量670

点赞数

文章标签： linux java python 大数据 mysql

原文链接：https://contexturesblog.com/archives/2014/06/19/excel-tools-for-complex-workbooks/

版权

复杂工作簿的Excel工具 (Excel Tools for Complex Workbooks)

Today, we'll look at Excel tools for complex workbooks. These tools can help you manage links to other files, track formulas, and do other helpful tasks.

今天，我们将研究用于复杂工作簿的Excel工具。这些工具可以帮助您管理到其他文件的链接，跟踪公式以及执行其他有用的任务。

复杂的工作簿挑战 (Complex Workbook Challenges)

This week, I got an email from someone who sets up complex Excel workbooks, and has a few challenges:

这周，我收到了设置复杂的Excel工作簿的人的电子邮件，但遇到了一些挑战：

Some files have so many sheets that it’s tough to find the sheet you need.
有些文件的工作表太多，以至于很难找到所需的工作表。
Files have links to other files, and sometimes the links don’t refresh correctly
文件具有指向其他文件的链接，有时链接无法正确刷新
It’s difficult to track formulas, and see how things are connected.
跟踪公式以及查看事物之间的联系非常困难。

Are there any tools could help with these challenges? I suggested a few things, some free tools, and some paid tools. If you have other suggestions, please add them in the comments.

是否有任何工具可以帮助应对这些挑战？我建议了一些东西，一些免费工具和一些付费工具。如果您还有其他建议，请在评论中添加。

用于管理Excel文件的免费工具 (Free Tools for Managing Excel Files)

航海家 (Navigator)

Dave Peterson and Ron de Bruin created a Navigator add-in that creates a list of all the sheets in the active workbook. The commands are added to the Ribbon’s Home tab, at the far right.

Dave Peterson和Ron de Bruin创建了一个Navigator加载项，该加载项创建了活动工作簿中所有工作表的列表。这些命令将添加到最右侧的功能区的“主页”选项卡中。

Click on a sheet name, to go to that sheet. You can also sort the sheets in alphabetical order.

单击工作表名称，以转到该工作表。您还可以按字母顺序对工作表进行排序。

TIP: You can add the “Select a Sheet” command to the Quick Access Toolbar, so it’s even easier to navigate. Right-click on the command on the Ribbon, and click Add to Quick Access Toolbar.

提示：您可以将“选择工作表”命令添加到快速访问工具栏，以便更加轻松地导航。右键单击功能区上的命令，然后单击“添加到快速访问工具栏”。

查询 (Inquire)

To see all the connections between workbooks, you can use Microsoft’s Inquire add-in, in some versions of Excel 2013. Here’s a screen shot with the commands that are on the INQUIRE tab, after you install the add-in.

若要查看工作簿之间的所有连接，可以在某些版本的Excel 2013中使用Microsoft的Inquire加载项。这是安装加载项后，INQUIRE选项卡上的命令的屏幕截图。

电子表格工作室 (Spreadsheet Studio)

Another free Excel add-in, Spreadsheet Studio, was created by chartered accountant, Joseph McDaid. Use it to review and audit your Excel files, and create comments with priority ranking and task allocation. Here is a screen shot of its tab on the Ribbon.

另一个免费的Excel加载项Spreadsheet Studio是由注册会计师Joseph McDaid创建的。用它来检查和审核您的Excel文件，并创建具有优先级和任务分配的注释。这是功能区上其选项卡的屏幕截图。

用于管理Excel文件的付费工具 (Paid Tools for Managing Excel Files)

There are paid tools as well, and some have a trial version, so you can how the tool works, before investing in it.

也有付费工具，有些有试用版，因此您可以在投资之前先了解该工具的工作方式。

FastExcel (FastExcel)

Charles Williams sells an Excel add-in package -- FastExcel V3 Bundle -- that is terrific for sorting out your workbooks, and seeing where you can streamline things.

查尔斯·威廉姆斯(Charles Williams)销售一个Excel附加程序包-FastExcel V3捆绑包 -对于整理您的工作簿以及查看可以简化的地方非常有用。

Here is a screen shot of some of the tools on its Ribbon tab – in the FastExcel Manager section. There are too many tools to show then all!

这是“功能区”选项卡上“ FastExcel Manager”部分中一些工具的屏幕截图。没有太多工具可以显示全部！

RefTreeAnalyser (RefTreeAnalyser)

Jan Karel Pieterse sells a formula auditing tool – RefTreeAnalyser – which has Ribbon commands, as well as a handy pop-up if you right-click a cell.

扬·卡雷尔·皮特瑟斯(Jan Karel Pieterse)出售了公式审核工具RefTreeAnalyser，该工具具有功能区命令，如果您右键单击单元格，则可以使用方便的弹出窗口。

Excel工具 (Excel Tools)

My Contextures Excel Tools add-in has a few features that help with large workbooks, such creating a list of all the worksheets (with links), listing all the formulas on a sheet, and making a quick backup copy of a file. Here’s a small section of its Ribbon tab.

我的Contextures Excel Tools加载项具有一些有助于大型工作簿的功能，例如创建所有工作表的列表(带有链接)，在工作表上列出所有公式以及快速备份文件副本。这是其功能区选项卡的一小部分。