excel查找元素并赋值_轻松查找并修复Excel错误

excel查找元素并赋值

How much time do you spend trying to find and fix error in your Excel workbooks? Or even worse, fixing errors in other people's workbooks. After commenting on my article See Formulas on an Excel Worksheet, Patrick O'Beirne, author of Spreadsheet Check and Control , asked if I'd like a review copy of his new Excel add-in, XLTest. It's designed to test your workbooks, in Excel 2007 and earlier versions.

您花费多少时间尝试查找并修复Excel工作簿中的错误？ 甚至更糟的是，修复其他人的工作簿中的错误。 在对我的文章“ 在Excel工作表上查看公式”发表评论后， Spreadsheet Check and Control的作者Patrick O'Beirne问我是否想要他的新Excel加载项XLTest的复本 。 它旨在在Excel 2007和早期版本中测试您的工作簿。

Even though all my workbooks are error free 😉 I accepted his offer. Patrick sent the add-in, instructions, and a couple of sample files. Here's a screen shot of the first sample. If your files look like this, you might need more help than this add-in can provide!

即使我所有的工作簿都没有错误😉我接受了他的报价。 Patrick发送了该加载项，说明和几个示例文件。 这是第一个示例的屏幕截图。 如果您的文件看起来像这样，则您可能需要比该外接程序所能提供的更多帮助！

加载项命令 (The Add-In Commands)

After I installed the add-in, a drop-down menu appeared on the Ribbon, as well as all the icons. I'd rather have just the drop-down menu, so maybe there's a way to turn off one or the other.

安装插件后，功能区上会出现一个下拉菜单以及所有图标。 我宁愿只有下拉菜单，所以也许有一种方法可以关闭一个或另一个。

快捷键 (Key Shortcuts)

The add-in adds 8 key shortcuts that you can see in the Ribbon, and in the popup menu that appears when you right-click a cell. Since the add-in features these shortcuts, let's look at a few of those first.

外接程序添加了8个快捷键，您可以在功能区中以及在右键单击单元格时出现的弹出菜单中看到。 由于加载项具有这些快捷方式，因此让我们首先看看其中的一些。

• Copy Formula: This is handy if you want to move a formula without changing the relative references. It's quicker than copying the formula from the Formula Bar, and pasting it into another cell, which is the way I'd do it without this shortcut.

  复制公式 ：如果要移动公式而不更改相对引用，此方法很方便。 这比从公式栏复制公式并将其粘贴到另一个单元格中要快得多，这是我没有此快捷方式的方式。

• Operate on Selection: Select non-contiguous cells, in multiple rows and columns, then move or copy them to a different location. If you try this in Excel, you'll get an error, so this shortcut could really save you some time and aggravation. To get this to work in Excel 2007, I had to select the top left cell last.

  选择时操作 ：在多行和多列中选择不连续的单元格，然后将其移动或复制到其他位置。 如果您在Excel中尝试此操作，则会收到错误消息，因此此快捷方式确实可以为您节省一些时间和麻烦。 为了使它在Excel 2007中正常工作，我必须最后选择左上方的单元格。

• Select Formula Region: Selects all the cells in the current region that have the same formula (in relative R1C1 terms) as the active cell. This helps you see if you've entered or updated a formula in all the relevant cells. Excel's error checking could flag those cells for you, but I usually have that turned off because it clutters up the worksheet.

  选择公式区域 ：选择当前区域中所有具有与活动单元格相同的公式(相对R1C1而言)的单元格。 这有助于您查看是否已在所有相关单元格中输入或更新了公式。 Excel的错误检查可以为您标记这些单元格，但是我通常将其关闭，因为它会使工作表变得混乱。

• Jump to Bottom Right: I use Ctrl+End to go to the bottom right, so I'd rather have another feature shortcut here.

  跳到右下角：我使用Ctrl + End转到右下角，因此我希望在此处使用另一个功能快捷键。

记录您的工作簿 (Document Your Workbook)

The rest of the commands let you test your workbooks for errors, starting with the Start New Test Session command. It opens a dialog box that lets you choose from 4 options for keeping logs, recording settings, opening files and closing open workbooks.

从“启动新测试会话”命令开始，其余命令可让您测试工作簿是否有错误。 它会打开一个对话框，您可以从4个选项中进行选择，以保留日志，记录设置，打开文件和关闭打开的工作簿。

Next, you can document your workbook with the Worksheet Documentation command. Select all the options, or just a few, and list the results in a new workbook or existing one. All the details are reported in a well organized worksheet. Here's a small section of the report for the demo file.

接下来，您可以使用“工作表文档”命令来记录您的工作簿。 选择所有选项或仅选择几个选项，然后将结果列出在新工作簿或现有工作簿中。 所有详细信息都报告在组织良好的工作表中。 这是演示文件报告的一小部分。

In Excel 2007, when I selected Number Formats with the other options, the Format Cells dialog box stayed open, and none of the Custom Number Formats were listed in the documentation.

在Excel 2007中，当我使用其他选项选择“数字格式”时，“格式单元格”对话框保持打开状态，并且文档中未列出任何“自定义数字格式”。

Everything else was correctly documented though, including the VBA modules.

不过，其他所有内容(包括VBA模块)均已正确记录。

检查您的工作簿 (Inspect Your Workbook)

For me, the main feature in the XL Test add-in is the Detailed Inspection. Instead of spending hours or days combing through your worksheets, click a button and get a report in a few seconds. Again, there were problems with reporting the Number Formats, but I'm sure Patrick can sort that out quickly.

对我来说，XL Test加载项的主要功能是详细检查。 无需花费数小时或数天来梳理工作表，只需单击一个按钮即可在几秒钟内获得一份报告。 再次，在报告数字格式方面存在问题，但是我确信Patrick可以快速解决该问题。

It creates a detailed report, with errors and other problems listed. You can quickly focus on the crucial errors, and get things fixed.

它会创建详细的报告，并列出错误和其他问题。 您可以快速关注关键错误，并解决问题。

I don't know what happens if you choose to see errors in separate cells, and there are more errors than columns. Maybe it wraps around, or maybe its head explodes!

我不知道如果您选择在单独的单元格中查看错误，并且错误多于列，该怎么办。 也许它缠起来，或者它的头爆炸了！

您的工作簿的其他测试 (Other Tests for Your Workbook)

There are several other tests that you can run in the XLTest add-in. For example, colour and document the data validation, conditional formatting or number formats on a worksheet. These test will quickly highlight any cells in a range that are different than their neighbours, and allow you to fix them.

您还可以在XLTest加载项中运行其他几个测试。 例如，在工作表上为数据验证，条件格式或数字格式上色并记录文档。 这些测试将快速突出显示与其邻居不同范围内的任何单元，并允许您对其进行修复。

After testing, you can click the add-in command to clear all the fill colour from a worksheet. Since the tests also add rectangle shapes with hyperlinks, it would help if those could also be removed with a single click.

测试后，您可以单击加载项命令以清除工作表中的所有填充颜色。 由于测试还添加了带有超链接的矩形形状，因此如果也可以通过单击将其删除，将会有所帮助。

The add-in also has a command for Batch Testing, so you can run all the tests on a workbook, with a single click, instead of running each test individually. The documentation warns of Excel memory problems if you try this on a large workbook.

加载项还具有用于批处理测试的命令，因此您可以单击一次在工作簿上运行所有测试，而不必单独运行每个测试。 如果在大型工作簿上尝试此文档，则会警告Excel内存问题。

附加功能 (Additional Features)

Test Cases: The XLTest add-in can run a list of tests, and create a report on the results of each test. Use this to ensure that a new version of a workbook works the same as the previous version, except where you have intentionally changed things. The add-in will also convert any existing Scenarios to test cases, so you can run those.

测试用例 ：XLTest加载项可以运行测试列表，并创建每个测试结果的报告。 使用它来确保工作簿的新版本与以前的版本相同，除非您有意更改了内容。 加载项还将所有现有的方案转换为测试用例，因此您可以运行那些。

• Comparison: With the add-in, you can compare worksheets or workbooks, and create a detailed list of differences. For workbooks, even the VBA code is compared.

  比较 ：使用外接程序，您可以比较工作表或工作簿，并创建差异的详细列表。 对于工作簿，甚至可以比较VBA代码。

• Housekeeping: There are several housekeeping features, such as creating a table of contents, unprotecting a sheet, and deleting custom styles.

  内务处理 ：有多种内务处理功能，例如创建目录，取消保护工作表和删除自定义样式。

• Functions: The add-in also adds 14 functions to Excel, such as GetFormula, ColorName and FileSize.

  函数 ：外接程序还向Excel添加了14个函数，例如GetFormula，ColorName和FileSize。

您应该购买XLTest加载项吗？ (Should You Buy the XLTest Add-in?)

If you're an expert programmer, you might have your own code that does error testing, comparison and housekeeping, so you won't need Patrick's add-in.

如果您是专家程序员，那么您可能拥有自己的代码，该代码可以进行错误测试，比较和内务处理，因此您将不需要Patrick的加载项。

If you don't have your own code, this add-in would be well worth its purchase price (£199, approx $296 US), in the time you'd save in looking for errors, and other tests. Yes, the add-in is more expensive than many other utilities that I've seen. It's a bargain though, when compared to hiring an Excel programmer or trying to do the testing yourself.

如果您没有自己的代码，则在节省查找错误和进行其他测试的时间时，此外接程序非常物有所值(199英镑，约合296美元)。 是的，该加载项比我见过的许多其他实用程序都昂贵。 不过，与雇用Excel程序员或尝试自己进行测试相比，这是便宜的。

For workbooks that you've inherited from colleagues or clients, you might not even know where to begin the error hunt. The XLTest add-in can do most of the detective work for you – it even unprotects and unhides sheets, rows and columns.

对于从同事或客户那里继承来的工作簿，您甚至可能不知道从哪里开始错误查找。 XLTest加载项可以为您完成大部分侦探工作-甚至可以取消保护和取消隐藏工作表，行和列。

And, of course, the real value in XLTest is in finding those critical errors that you didn't even know you should look for. If the add-in saves your job, it's priceless! ____________

而且，当然，XLTest的真正价值在于找到您根本不知道应该寻找的那些严重错误。 如果外接程序可以节省您的工作，那将是无价的！ ____________

翻译自: https://contexturesblog.com/archives/2009/10/19/easily-find-and-fix-excel-errors/

excel查找元素并赋值