Excel表不会扩展为新数据

This week, while working on a client's Excel project, I ran into some trouble with Excel tables, while adding new data. Instead of expanding to include the rows, the table just ignored them.

本周，在处理客户的Excel项目时，我在添加新数据时遇到了Excel表的麻烦。 该表没有扩展以包括行，而是忽略了它们。

Fortunately, the problem is easy to fix, if you know how, and if you have the patience to do lots of clicking.

幸运的是，如果您知道如何解决问题，并且有足够的耐心进行单击，则该问题很容易解决。

忽略新数据 (New Data Ignored)

Tables are a great feature in newer versions of Excel. They make it easy to add, sort, and filter your data. Usually, tables behave quite nicely, unless, of course, you try to add a Custom View to the workbook, or do something with a group of sheets that contain tables.

在Excel的较新版本中，表格是一项很棒的功能。 它们使添加，排序和过滤数据变得容易。 通常，表的行为都很好，除非您当然尝试将“自定义视图”添加到工作簿，或者对包含表的一组工作表进行某些操作。

The file contains a UserForm for data entry, similar to the one shown below, that you can download from my Contextures site. When you click the "Add this part" button, the new record is added at the end of the existing rows.

该文件包含一个用于数据输入的用户窗体 ，类似于下面显示的用户窗体 ，您可以从我的Contextures网站上下载该窗体 。 当您单击“添加此部分”按钮时，新记录将添加到现有行的末尾。

Everything worked well on my computer, so I sent the file to my client, for testing. Unfortunately, things didn't go well on his computer.

一切在我的计算机上都能正常运行，因此我将文件发送给客户端进行测试。 不幸的是，在他的计算机上一切都不好。

The data overflowed the table, instead of it expanding automatically. As a result, the new data didn't sort with the other items, and wasn't included in the drop down list, based on one of columns.

数据使表溢出，而不是自动扩展。 结果，新数据没有与其他项目进行排序，并且没有包含在基于列之一的下拉列表中。

After hearing about the problem, I tried the code on a couple of other computers, and in different versions of Excel. I couldn't reproduce the problem, so there had to be a setting on my client's computer that was causing the problem.

听说此问题后，我在其他几台计算机以及不同版本的Excel中尝试了该代码。 我无法重现该问题，因此必须在导致问题的客户端计算机上进行设置。

解决问题 (Fix the Problem)

After exploring a few possibilities, I finally figured out that the problem was caused by an AutoCorrect setting. Who even remembers that Excel has AutoCorrect settings?

在探索了几种可能性之后，我终于发现问题是由“自动更正”设置引起的。 谁甚至还记得Excel具有自动更正设置？

I added code to the UserForm, to change those settings, but you can make the same changes manually. However, the settings are buried 5 clicks deep, so pack a lunch for the journey!

我向用户窗体添加了代码，以更改那些设置，但是您可以手动进行相同的更改。 但是，设置被深埋5次点击，因此请打包午餐！

NOTE: There is lots of room on the Ribbon's Table Tools Design tab – maybe these settings could be added there, in a future version of Excel. In the meantime, you could add the AutoCorrect Options command to one of the tabs, so it's easier to access.

注意：功能区的“表格工具设计”选项卡上有很多空间-可能在将来的Excel版本中将这些设置添加到其中。 同时，您可以将“自动更正选项”命令添加到选项卡之一，以便于访问。

For example, put it on the Review tab, with the other Proofing tools.

例如，将其与其他校对工具一起放在“审阅”选项卡上。

更改自动更正设置 (Change the AutoCorrect Settings)

NOTE: Changing these settings will affect all workbooks that you open in Excel, on your computer.

注意 ：更改这些设置将影响您在计算机上用Excel打开的所有工作簿 。

To fix the tables, so they automatically expand to include new rows or columns, follow these steps:

若要修复表，以便它们自动扩展以包括新的行或列，请按照下列步骤操作：

1. At the left end of the Ribbon, click the File tab
   在功能区的左端，单击“文件”选项卡
2. Then click Options
   然后单击选项
3. In the Excel Options window, at the left, click Proofing
   在“ Excel选项”窗口的左侧，单击“校对”
4. 

   In the AutoCorrect options section, click AutoCorrect Options

   在自动更正选项部分，单击自动更正选项

5. Click the AutoFormat As You Type tab – you've finally reached the settings!
   单击“键入时自动套用格式”选项卡-您终于完成了设置！
6. 

   Add check marks to "Include new rows and columns in table" and "Fill formulas in tables to create calculated columns"

   在“在表中包括新行和列”和“在表中填充公式以创建计算列”中添加复选标记

7. Click OK, twice, to return to Excel
   单击确定两次，以返回到Excel

视频：Excel表不会自动展开 (Video: Excel Table Doesn't Expand Automatically)

To see the steps for setting up an Excel table, and checking the AutoCorrect options so it expands automatically, please watch this short video.

要查看设置Excel表以及检查“自动更正”选项使其自动展开的步骤，请观看此简短视频。

演示地址

下载样本文件 (Download the Sample File)

To experiment with Excel tables, and to get the code to change the AutoCorrect settings programmatically, please go to the Excel Table page on my Contextures website.

要尝试使用Excel表，并获取以编程方式更改“自动更正”设置的代码，请转到Contextures网站上的“ Excel表”页面 。

翻译自: https://contexturesblog.com/archives/2015/04/30/excel-table-doesnt-expand-for-new-data/