尝试在Excel(Excel)中填写包含工作表中多个其他单元格的文本或数据的单元格可能是一个非常耗时的过程。如果电子表格中有数百或数千行，则尤其如此。
一旦您知道如何在Excel 中正确使用(Excel)Flash Fill，您就可以让Excel完成所有繁重的工作。您只需提供几个手动单元格条目，以帮助Excel准确了解您要执行的操作。然后，Excel为电子表格的其余部分完成其余的工作。
如果这听起来像是您想尝试的节省时间的技巧，那么让我们看看如何使用此功能来提高自己的工作效率。
注意(Note)：Excel 中的快速(Excel)填充(Flash Fill)功能仅在Excel 2013及更高版本中可用。
如何在 Excel 中使用 Flash 填充
Excel中最简单的Flash Fill应用是将两个单词组合在一起(combining two words together)。在下面的示例中，我们将向您展示如何使用Flash Fill将名字和姓氏快速组合到第三个单元格中作为全名。
在此示例中，C 列包含名字，D 列包含姓氏，E 列是全名列。
- 首先，按照您希望的方式在第一个单元格中输入全名（结合“名字(First Name)”单元格和“姓氏”单元格的内容。
- 在此之后，开始在下一个单元格中输入相同的内容（左侧单元格中的名字(First Name)和姓氏）。您会注意到 Excel 的快速填充(Flash Fill)功能会自动从您手动输入的单元格内容中检测模式。
使用这种“学习”，Excel将提供它认为您想要键入的内容的预览。它甚至会向您展示其余单元格将如何填充列的其余部分。
- 只需按Enter接受此预览。您将看到Excel 快速填充(Excel Flash Fill)功能发挥其魔力，因为它会为您填充此列中的所有其余单元格。
如您所见，如果将手动键入一个单元格和Enter与必须手动键入新列中所有单元格的名称进行比较， Flash Fill功能可以节省大量时间。
如果您发现Flash Fill功能不起作用，您需要在Excel中打开(Excel)Flash Fill功能。您可以在本文的最后一节中看到如何做到这一点。
Excel 快速填充选项
当您执行上述快速填充(Flash Fill)步骤时，您会注意到填充的单元格旁边会出现一个小图标。如果您选择此图标右侧的下拉箭头，您将看到一些可用于快速填充(Flash Fill)功能的其他选项。
使用此下拉框中的Flash Fill选项，您可以：(Flash Fill)
- Undo Flash Fill : 撤消按Enter后填充整个列的操作(Enter)
- 接受建议(Accept suggestions)：这将告诉 Excel 的此列的快速填充(Flash Fill)功能，您对列的更改感到满意并希望保留它们
- 选择 xx 个空白单元格(Select xx blank cells)：让您识别任何未填写且为空白的单元格，以便您可以在需要时修复这些单元格
- 选择 xx 个更改的单元格：允许您选择在(Select xx changed cells)Flash Fill功能更新这些单元格后自动更改的所有单元格
选择接受建议后，您会看到“已更改单元格”的“选择”数字将降至零。这是因为一旦您接受更改，这些单元格内容将不再被Flash Fill功能视为“更改”。
如何在 Excel 中启用 Flash 填充
如果您注意到当您开始键入第二个单元格时Excel不提供Flash Fill预览，您可能需要启用该功能。
去做这个：
选择文件(File)>选项(Options)>高级(Advanced)。向下滚动到“编辑选项(Editing options)”部分，确保选中“为单元格值启用自动完成”(Enable AutoComplete for cell values)和“自动快速填充”(Automatically Flash Fill)。
选择确定(OK)完成。现在，下次您在填写第一个单元格后开始输入第二个单元格时，Excel应该会检测到该模式并为您提供预览，以了解它认为您希望如何填写该列中的其余单元格。
您还可以通过选择功能区数据工具(Data Tools)组 下的数据(Data)菜单中的快速填充(Flash Fill)图标来为突出显示的单元格激活快速填充功能。(Flash Fill)
您也可以通过按键盘上的Ctrl + E来使用它。
何时在 Excel 中使用 Flash 填充
连接两列的全名是如何在Excel中使用(Excel)Flash Fill的一个简单示例，但此强大功能还有许多更高级的用途。
- 从另一列中的大字符串（如来自完整地址的邮政编码）中提取子字符串
- 从字母(Pull)数字字符串中提取数字
- 从字符串之前或之后删除空格
- 将(Insert)子字符串（如逗号或破折号）插入现有字符串
- 重新格式化日期
- 用新文本替换(Replace)部分字符串
请记住，无论Flash Fill功能多么有用，它都不会在您更改原始单元格时自动更新。
例如，在本文第一部分的名字和姓氏示例中，您可以使用Concatenate 函数(Concatenate function)实现相同的输出，然后用该函数填充列的其余部分。
=CONCATENATE(C2,” “,D2)
执行此操作时，如果前两个单元格中的任何一个发生更改，则全(Full Name)名将更新。这样做的一个缺点是，如果您删除前两列中的任何一列，全(Full Name)名列将清除或显示错误。
这就是为什么当您想要将原始列完全且永久地转换为新格式化的字符串或数字时，最好使用Excel中的(Excel)Flash Fill功能。
How To Use Flash Fill In Excel
Trying to fill out cells in Excel that incorporate text or data from multiрle оther cells in a ѕheet can be a very time consυming proсess. This is esрecially true if there are hundreds or thousands of rows in the spreadsheet.
Once you know how to use Flash Fill in Excel correctly, you can let Excel do all of the heavy lifting. You just provide a couple of manual cell entries to help Excel understand exactly what you’re trying to do. Then, Excel does the rest of the work for the rest of the spreadsheet.
If this sounds like a time-saving tip you’d like to try, let’s take a look at how you can use this feature to improve your own productivity.
Note: The Flash Fill feature in Excel is only available in Excel 2013 and later.
How To Use Flash Fill In Excel
The simplest application of Flash Fill in Excel is combining two words together. In the example below, we’ll show you how to use Flash Fill to quickly combine a first name and a last name into a third cell for the full name.
In this example, column C contains the first name, column D contains the last name, and column E is the column for the full name.
- First, type the full name into the first cell the way you’d like it to look (combining the contents of the First Name cell and the Last Name cell.
- After this, start typing the same thing into the next cell (the First Name and Last Name from the cells to the left). You’ll notice that Excel’s Flash Fill feature will automatically detect the pattern from the content of the cell above it that you typed manually.
Using this “learning”, Excel will provide a preview of what it thinks you want to type. It will even show you how the rest of the cells will fill in for the rest of the column.
- Just press Enter to accept this preview. You’ll see the Excel Flash Fill feature perform its magic as it fills in all of the rest of the cells in this column for you.
As you can see, the Flash Fill feature can save a tremendous amount of time if you compare manually typing one cell and Enter to having to manually type the names for all of the cells in the new column.
If you’ve noticed that the Flash Fill feature doesn’t work, you need to turn on the Flash Fill feature in Excel. You can see how to do that in the last section of this article.
Excel Flash Fill Options
When you perform the Flash Fill steps above, you’ll notice a small icon will appear next to the filled-in cells. If you select the drop-down arrow to the right of this icon, you’ll see some additional options you can use with the Flash Fill feature.
Using the Flash Fill options in this drop-down box, you can:
- Undo Flash Fill: Undo the action that filled in the entire column after you pressed Enter
- Accept suggestions: This will tell Excel’s Flash Fill feature for this column that you’re fine with the changes to the column and would like to keep them
- Select xx blank cells: Lets you identify any cells that didn’t get filled-in and are blank so that you can fix those if you need to
- Select xx changed cells: Lets you select all of the cells that automatically changed after the Flash Fill feature updated those cells
Once you select Accept suggestions, you’ll see that the “select” numbers for the “changed cells” will drop to zero. This is because once you accept the changes, those cell contents are no longer considered “changed” by the Flash Fill feature.
How To Enable Flash Fill In Excel
If you’ve noticed that Excel doesn’t provide a Flash Fill preview when you start typing the second cell, you may need to enable the feature.
To do this:
Select File > Options > Advanced. Scroll down to the Editing options section and make sure both Enable AutoComplete for cell values and Automatically Flash Fill are selected.
Select OK to finish. Now, the next time you start typing in the second cell after filling in the first, Excel should detect the pattern and provide you with a preview for how it thinks you want to fill in the rest of the cells in the column.
You can also activate the Flash Fill feature for the cell you have highlighted by selecting the Flash Fill icon in the Data menu under the Data Tools group in the ribbon.
You can also use it by pressing Ctrl + E on the keyboard.
When To Use Flash Fill In Excel
Concatenating full names from two columns is a simple example of how you can use Flash Fill in Excel, but there are many more advanced uses for this powerful feature.
- Extract a substring from a large string (like a zip code from a full address) in another column
- Pull numbers out of alphanumeric strings
- Remove spaces from before or after strings
- Insert substrings (like a comma or dash) into existing strings
- Reformat dates
- Replace part of a string with new text
Keep in mind that, however useful the Flash Fill feature is, it will not automatically update when you change the original cells.
For example, in the first and last name example in the first section of this article, you could achieve the same output by using the Concatenate function and then filling the rest of the column with that function.
=CONCATENATE(C2,” “,D2)
When you do this, if either of the first two cells change, the Full Name will update. One drawback of this is that if you delete either of the first two columns, the Full Name column will clear or display an error.
This is why the Flash Fill function in Excel is best used when you want to fully and permanently convert the original columns into a newly formatted string or number.
