vfp取得excel的句柄_如何使用填充句柄将顺序数据自动填充到Excel中
vfp取得excel的句柄
The Fill Handle in Excel allows you to automatically fill in a list of data (numbers or text) in a row or column simply by dragging the handle. This can save you a lot of time when entering sequential data in large worksheets and make you more productive.
Excel中的“填充手柄”使您只需拖动手柄即可自动在行或列中填充数据列表(数字或文本)。 在大型工作表中输入顺序数据时，这可以节省大量时间，并使您的工作效率更高。
Instead of manually entering numbers, times, or even days of the week over and over again, you can use the AutoFill features (the fill handle or the Fill command on the ribbon) to fill cells if your data follows a pattern or is based on data in other cells. We’ll show you how to fill various types of series of data using the AutoFill features.
如果数据遵循模式或基于以下内容，则可以使用“自动填充”功能(填充功能区上的填充手柄或“填充”命令)来手动填充数字，而不是一遍又一遍地手动输入数字，时间或一周中的几天。其他单元格中的数据。 我们将向您展示如何使用自动填充功能填充各种类型的数据系列。
将线性序列填充到相邻单元格中 (Fill a Linear Series into Adjacent Cells)
One way to use the fill handle is to enter a series of linear data into a row or column of adjacent cells. A linear series consists of numbers where the next number is obtained by adding a “step value” to the number before it. The simplest example of a linear series is 1, 2, 3, 4, 5. However, a linear series can also be a series of decimal numbers (1.5, 2.5, 3.5…), decreasing numbers by two (100, 98, 96…), or even negative numbers (-1, -2, -3). In each linear series, you add (or subtract) the same step value.
使用填充手柄的一种方法是将一系列线性数据输入到相邻单元格的行或列中。 线性序列由数字组成，其中下一个数字是通过在其前面的数字上添加“阶跃值”而获得的。 线性序列的最简单示例是1、2、3、4、5。但是，线性序列也可以是一系列十进制数(1.5、2.5、3.5…)，将数字减2(100、98、96) …)，甚至是负数(-1，-2，-3)。 在每个线性系列中，您要添加(或减去)相同的步长值。
Let’s say we want to create a column of sequential numbers, increasing by one in each cell. You can type the first number, press Enter to get to the next row in that column, and enter the next number, and so on. Very tedious and time consuming, especially for large amounts of data. We’ll save ourselves some time (and boredom) by using the fill handle to populate the column with the linear series of numbers. To do this, type a 1 in the first cell in the column and then select that cell. Notice the green square in the lower-right corner of the selected cell? That’s the fill handle.
假设我们要创建一列连续数字，在每个单元格中增加一个。 您可以键入第一个数字，按Enter键以转到该列的下一行，然后输入下一个数字，依此类推。 非常繁琐且耗时，尤其是对于大量数据。 通过使用填充手柄用线性数字序列填充列，我们将节省一些时间(和无聊)。 为此，请在该列的第一个单元格中键入1，然后选择该单元格。 请注意所选单元格右下角的绿色方块？ 那是填充句柄。
When you move your mouse over the fill handle, it turns into a black plus sign, as shown below.
将鼠标移到填充手柄上时，它将变为黑色加号，如下所示。
With the black plus sign over the fill handle, click and drag the handle down the column (or right across the row) until you reach the number of cells you want to fill.
在填充手柄上用黑色加号标记时，单击该手柄并将其向下拖动到列中(或在整个行中向右)，直到达到要填充的单元格数量。
When you release the mouse button, you’ll notice that the value has been copied into the cells over which you dragged the fill handle.
释放鼠标按钮时，您会注意到该值已复制到拖动填充手柄的单元格中。
Why didn’t it fill the linear series (1, 2, 3, 4, 5 in our example)? By default, when you enter one number and then use the fill handle, that number is copied to the adjacent cells, not incremented.
为什么不填充线性序列(在我们的示例中为1，2，3，4，5)？ 默认情况下，当您输入一个数字然后使用填充手柄时，该数字将被复制到相邻的单元格中，而不是递增。
NOTE: To quickly copy the contents of a cell above the currently selected cell, press Ctrl+D, or to copy the contents of a cell to the left of a selected cell, press Ctrl+R. Be warned that copying data from an adjacent cell replaces any data that is currently in the selected cell.
注意：要快速复制当前所选单元格上方的单元格内容，请按Ctrl + D，或者要将单元格内容复制到所选单元格的左侧，请按Ctrl + R。 请注意，从相邻单元格复制数据会替换所选单元格中当前存在的所有数据。
To replace the copies with the linear series, click the “Auto Fill Options” button that displays when you’re done dragging the fill handle.
要用线性系列替换副本，请在完成填充手柄后单击“自动填充选项”按钮。
The first option, Copy Cells, is the default. That’s why we ended up with five 1s and not the linear series of 1–5. To fill the linear series, we select “Fill Series” from the popup menu.
默认选项是“复制单元格”。 这就是为什么我们以5个1而不是1–5的线性级数结束的原因。 要填充线性序列，我们从弹出菜单中选择“填充序列”。
The other four 1s are replaced with 2–5 and our linear series is filled.
其他四个1被2-5代替，我们的线性序列被填充。
You can, however, do this without having to select Fill Series from the Auto Fill Options menu. Instead of entering just one number, enter the first two numbers in the first two cells. Then, select those two cells and drag the fill handle until you’ve selected all the cells you want to fill.
但是，您可以执行此操作而不必从“自动填充选项”菜单中选择“填充系列”。 而不是只输入一个数字，而是在前两个单元格中输入前两个数字。 然后，选择这两个单元格并拖动填充手柄，直到选择了要填充的所有单元格。
Because you’ve given it two pieces of data, it will know the step value you want to use, and fill the remaining cells accordingly.
因为您已给它提供了两条数据，所以它将知道您要使用的步长值，并相应地填充其余单元格。
You can also click and drag the fill handle with the right mouse button instead of the left. You still have to select “Fill Series” from a popup menu, but that menu automatically displays when you stop dragging and release the right mouse button, so this can be a handy shortcut.
您也可以单击并用鼠标右键，而不是左侧拖动填充柄。 您仍然必须从弹出菜单中选择“填充系列”，但是当您停止拖动并释放鼠标右键时，该菜单会自动显示，因此这可能是方便的快捷方式。
使用Fill命令将线性序列填充到相邻单元格中 (Fill a Linear Series into Adjacent Cells Using the Fill Command)
If you’re having trouble using the fill handle, or you just prefer using commands on the ribbon, you can use the Fill command on the Home tab to fill a series into adjacent cells. The Fill command is also useful if you’re filling a large number of cells, as you’ll see in a bit.
如果您在使用填充手柄时遇到问题，或者只喜欢在功能区上使用命令，则可以使用“主页”选项卡上的“填充”命令将一系列填充到相邻的单元格中。 如果您要填充大量单元格，则“填充”命令也很有用，如您所见。
To use the Fill command on the ribbon, enter the first value in a cell and select that cell and all the adjacent cells you want to fill (either down or up the column or to the left or right across the row). Then, click the “Fill” button in the Editing section of the Home tab.
要在功能区上使用“填充”命令，请在一个单元格中输入第一个值，然后选择该单元格以及要填充的所有相邻单元格(在列的上下方向或行的左侧或右侧)。 然后，单击“主页”选项卡的“编辑”部分中的“填充”按钮。
Select “Series” from the drop-down menu.
从下拉菜单中选择“系列”。
On the Series dialog box, select whether you want the Series in Rows or Columns. In the Type box, select “Linear” for now. We will discuss the Growth and Date options later, and the AutoFill option simply copies the value to the other selected cells. Enter the “Step value”, or the increment for the linear series. For our example, we’re incrementing the numbers in our series by 1. Click “OK”.
在“系列”对话框中，选择要“行”还是“列”系列。 在“类型”框中，现在选择“线性”。 稍后我们将讨论“增长”和“日期”选项，“自动填充”选项只是将值复制到其他选定的单元格中。 输入“步进值”或线性系列的增量。 对于我们的示例，我们将序列中的数字加1.。单击“确定”。
The linear series is filled in the selected cells.
线性序列填充在选定的单元格中。
If you have a really long column or row you want to fill with a linear series, you can use the Stop value on the Series dialog box. To do this, enter the first value in the first cell you want to use for the series in the row or column, and click “Fill” on the Home tab again. In addition to the options we discussed above, enter the value into the “Stop value” box that you want as the last value in the series. Then, click “OK”.
如果您有很长的列或行要填充线性序列，则可以使用“序列”对话框中的“停止”值。 为此，请在行或列中要用于该系列的第一个单元格中输入第一个值，然后再次单击“主页”选项卡上的“填充”。 除了上面讨论的选项外，还将值输入到您希望作为系列中的最后一个值的“停止值”框中。 然后，单击“确定”。
In the following example, we put a 1 in the first cell of the first column and the numbers 2 through 20 will be entered automatically into the next 19 cells.
在下面的示例中，我们在第一列的第一个单元格中输入1，然后数字2至20将自动输入到接下来的19个单元格中。
跳过行时填充线性系列 (Fill a Linear Series While Skipping Rows)
To make a full worksheet more readable, we sometimes skip rows, putting blank rows in between the rows of data. Even though there are blank rows, you can still use the fill handle to fill a linear series with blank rows.
为了使整个工作表更具可读性，有时我们跳过行，将空白行放在数据行之间。 即使有空白行，您仍然可以使用填充手柄用空白行填充线性系列。
To skip a row when filling a linear series, enter the first number in the first cell and then select that cell and one adjacent cell (for example, the next cell down in the column).
要在填充线性序列时跳过一行，请在第一个单元格中输入第一个数字，然后选择该单元格和一个相邻的单元格(例如，该列中的下一个单元格)。
Then, drag the fill handle down (or across) until you fill the desired number of cells.
然后，向下(或横向)拖动填充手柄，直到填充所需数量的单元格。
When you’re finished dragging the fill handle, you will see your linear series fills every other row.
拖动填充手柄完成后，您会看到线性系列每隔一行填充一次。
If you want to skip more than one row, simply select the cell containing the first value and then select the number of rows you want to skip right after that cell. Then, drag the fill handle over the cells you want to fill.
如果要跳过多个行，只需选择包含第一个值的单元格，然后选择要在该单元格后立即跳过的行数。 然后，将填充手柄拖到要填充的单元格上。
You can also skip columns when you are filling across rows.
跨行填充时，您也可以跳过列。
将公式填充到相邻单元格中 (Fill Formulas into Adjacent Cells)
You can also use the fill handle to propagate formulas to adjacent cells. Simply select the cell containing the formula you want to fill into adjacent cells and drag the fill handle down the cells in the column or across the cells in the row that you want to fill. The formula is copied to the other cells. If you used relative cell references, they will change accordingly to refer to the cells in their respective rows (or columns).
您也可以使用填充手柄将公式传播到相邻的单元格。 只需选择包含要填充的公式的单元格到相邻的单元格中，然后将填充手柄向下拖动到该列中的单元格中，或者拖动到要填充的行中的单元格之间即可。 该公式将复制到其他单元格。 如果使用了相对单元格引用，则它们将相应地更改以引用其各自行(或列)中的单元格。
You can also fill formulas using the Fill command on the ribbon. Simply select the cell containing the formula and the cells you want to fill with that formula. Then, click “Fill” in the Editing section of the Home tab and select Down, Right, Up, or Left, depending on which direction you want to fill the cells.
您也可以使用功能区上的“填充”命令来填充公式。 只需选择包含公式的单元格和要用该公式填充的单元格。 然后，在“主页”选项卡的“编辑”部分中单击“填充”，然后根据要填充单元格的方向选择“向下”，“向右”，“向上”或“向左”。
NOTE: The copied formulas will not recalculate, unless you have automatic workbook calculation enabled.
注意：除非您启用了自动工作簿计算，否则复制的公式将不会重新计算。
You can also use the keyboard shortcuts Ctrl+D and Ctrl+R, as discussed earlier, to copy formulas to adjacent cells.
您还可以使用键盘快捷键Ctrl + D和Ctrl + R(如前所述)将公式复制到相邻的单元格中。
通过双击填充手柄填充线性系列 (Fill a Linear Series by Double Clicking on the Fill Handle)
You can quickly fill a linear series of data into a column by double clicking the fill handle. When using this method, Excel only fills the cells in the column based on the longest adjacent column of data on your worksheet. An adjacent column in this context is any column that Excel encounters to the right or left of the column being filled, until a blank column is reached. If the columns directly on either side of the selected column are blank, you cannot use the double click method to fill the cells in the column. Also, by default, if some of the cells in the range of cells you’re filling already have data, only the empty cells above the first cell containing data are filled. For example, in the image below, there’s a value in cell G7 so when you double click on the fill handle on cell G2, the formula is only copied down through cell G6.
您可以通过双击填充手柄将线性数据系列快速填充到列中。 使用此方法时，Excel仅根据工作表上数据的最长相邻列填充列中的单元格。 在这种情况下，相邻列是Excel在要填充的列的右侧或左侧遇到的任何列，直到到达空白列为止。 如果直接在所选列的任一侧上的列为空白，则不能使用双击方法填充该列中的单元格。 同样，默认情况下，如果您要填充的单元格范围中的某些单元格已经有数据，则仅填充第一个包含数据的单元格上方的空白单元格。 例如，在下面的图像中，单元格G7中有一个值，因此当您双击单元格G2上的填充手柄时，该公式仅向下复制到单元格G6中。
填充增长系列(几何图案) (Fill a Growth Series (Geometric Pattern))
Up until now, we’ve been discussing filling linear series, where each number in the series is calculated by adding the step value to the previous number. In a growth series, or geometric pattern, the next number is calculated by multiplying the previous number by the step value.
到目前为止，我们一直在讨论填充线性序列，该序列中的每个数字都是通过将步长值添加到前一个数字来计算的。 在增长系列或几何图案中，下一个数字是通过将前一个数字乘以步长值来计算的。
There are two ways to fill a growth series, by entering the first two numbers and by entering the first number and the step value.
通过输入前两个数字以及输入第一个数字和步长值，有两种方法来填充增长序列。
方法一：在增长系列中输入前两个数字 (Method One: Enter the First Two Numbers in the Growth Series)
To fill a growth series using the first two numbers, enter the two numbers into the first two cells of the row or column you want to fill. Right-click and drag the fill handle over as many cells as you want to fill. When you’re finished dragging the fill handle over the cells you want to fill, select “Growth Trend” from the popup menu that automatically displays.
要使用前两个数字填充增长系列，请将两个数字输入到要填充的行或列的前两个单元格中。 右键单击并将填充手柄拖动到要填充的单元格上。 当您将填充手柄拖到要填充的单元格上时，请从自动显示的弹出菜单中选择“增长趋势”。
NOTE: For this method, you must enter two numbers. If you don’t, the Growth Trend option will be grayed out.
注意：对于这种方法，您必须输入两个数字。 否则，“增长趋势”选项将显示为灰色。
Excel knows that the step value is 2 from the two numbers we entered in the first two cells. So, every subsequent number is calculated by multiplying the previous number by 2.
Excel从我们在前两个单元格中输入的两个数字知道该步进值为2。 因此，每个后续数字都是通过将前一个数字乘以2来计算的。
What if you want to start at a number other than 1 using this method? For example, if you wanted to start the above series at 2, you would enter 2 and 4 (because 2×2=4) in the first two cells. Excel would figure out that the step value is 2 and continue the growth series from 4 multiplying each subsequent number by 2 to get the next one in line.
如果要使用此方法以1以外的数字开头怎么办？ 例如，如果要从2开始上述序列，则应在前两个单元格中输入2和4(因为2×2 = 4)。 Excel将计算出步长值为2，并从4继续将每个后续数字乘以2得到下一个连续的增长系列。
方法二：在增长系列中输入第一个数字并指定步长值 (Method Two: Enter the First Number in the Growth Series and Specify the Step Value)
To fill a growth series based on one number and a step value, enter the first number (it doesn’t have to be 1) in the first cell and drag the fill handle over the cells you want to fill. Then, select “Series” from the popup menu that automatically displays.
要基于一个数字和一个步长值填充生长系列，请在第一个单元格中输入第一个数字(不必为1)，然后将填充手柄拖到要填充的单元格上。 然后，从自动显示的弹出菜单中选择“系列”。
On the Series dialog box, select whether your filling the Series in Rows or Columns. Under Type, select :”Growth”. In the “Step value” box, enter the value you want to multiply each number by to get the next value. In our example, we want to multiply each number by 3. Click “OK”.
在“系列”对话框上，选择是以行还是列填充系列。 在类型下，选择：“增长”。 在“步进值”框中，输入要乘以每个数字以获得下一个值的值。 在我们的示例中，我们要将每个数字乘以3。单击“确定”。
The growth series is filled in the selected cells, each subsequent number being three times the previous number.
生长序列填充在所选单元格中，每个后续数字是前一个数字的三倍。
使用内置项目填充系列 (Fill a Series Using Built-in Items)
So far, we’ve covered how to fill a series of numbers, both linear and growth. You can also fill series with items such as dates, days of the week, weekdays, months, or years using the fill handle. Excel has several built-in series that it can automatically fill.
到目前为止，我们已经介绍了如何填充线性和增长的一系列数字。 您还可以使用填充手柄将日期，星期几，工作日，月份或年份等项目填充到系列中。 Excel具有几个可以自动填充的内置系列。
The following image shows some of the series that are built in to Excel, extended across the rows. The items in bold and red are the initial values we entered and the rest of the items in each row are the extended series values. These built-in series can be filled using the fill handle, as we previously described for linear and growth series. Simply enter the initial values and select them. Then, drag the fill handle over the desired cells you want to fill.
下图显示了Excel中内置的一些系列，跨行扩展。 粗体和红色项是我们输入的初始值，每行中的其余项是扩展系列值。 可以使用填充手柄填充这些内置系列，正如我们之前针对线性和增长系列所述。 只需输入初始值并选择它们。 然后，将填充手柄拖到要填充的所需单元格上。
使用Fill命令填充一系列日期 (Fill a Series of Dates Using the Fill Command)
When filling a series of dates, you can use the Fill command on the ribbon to specify the increment to use. Enter the first date in your series in a cell and select that cell and the cells you want to fill. In the Editing section of the Home tab, click “Fill” and then select “Series”.
填充一系列日期时，可以使用功能区上的“填充”命令来指定要使用的增量。 在单元格中输入系列的第一个日期，然后选择该单元格和要填充的单元格。 在“主页”选项卡的“编辑”部分中，单击“填充”，然后选择“系列”。
On the Series dialog box, the Series in option is automatically selected to match the set of cells you selected. The Type is also automatically set to Date. To specify the increment to use when filling the series, select the Date unit (Day, Weekday, Month, or Year). Specify the Step value. We want to fill the series with every weekday date, so we enter 1 as the Step value. Click “OK”.
在“序列”对话框中，将自动选择“序列输入”选项以匹配所选单元格的集合。 类型也自动设置为日期。 要指定填充系列时要使用的增量，请选择“日期”单位(日，周日，月或年)。 指定步长值。 我们要用每个工作日的日期填充系列，因此我们输入1作为“步长”值。 点击“确定”。
The series is populated with dates that are only weekdays.
该系列填充了只有工作日的日期。
使用自定义项目填充系列 (Fill a Series Using Custom Items)
You can also fill a series with your own custom items. Say your company has offices in six different cities and you use those city names often in your Excel worksheets. You can add that list of cities as a custom list that will allow you to use the fill handle to fill the series once you enter the first item. To create a custom list, click the “File” tab.
您还可以使用自己的自定义项目填充系列。 假设您的公司在六个不同的城市设有办事处，并且您经常在Excel工作表中使用这些城市名称。 您可以将该城市列表添加为自定义列表，一旦输入第一个项目，就可以使用填充手柄填充系列。 要创建自定义列表，请单击“文件”选项卡。
On the backstage screen, click “Options” in the list of items on the left.
在后台屏幕上，单击左侧项目列表中的“选项”。
Click “Advanced” in the list of items on the left side of the Excel Options dialog box.
在“ Excel选项”对话框左侧的项目列表中，单击“高级”。
In the right panel, scroll down to the General section and click the “Edit Custom Lists” button.
在右侧面板中，向下滚动到“常规”部分，然后单击“编辑自定义列表”按钮。
Once you’re on the Custom Lists dialog box, there are two ways to fill a series of custom items. You can base the series on a new list of items you create directly on the Custom Lists dialog box, or on an existing list already on a worksheet in your current workbook. We will show you both methods.
进入“自定义列表”对话框后，有两种方法可以填充一系列自定义项。 您可以将系列建立在直接在“自定义列表”对话框上创建的新项目列表的基础上，也可以基于当前工作簿中工作表上已有的现有列表。 我们将向您展示这两种方法。
方法1：基于新的项目列表填充自定义系列 (Method One: Fill a Custom Series Based on a New List of Items)
On the Custom Lists dialog box, make sure NEW LIST is selected in the Custom lists box. Click in the “List entries” box and enter the items in your custom lists, one item to a line. Be sure to enter the items in the order you want them filled into cells. Then, click “Add”.
在“自定义列表”对话框中，确保在“自定义列表”框中选择了“新列表”。 单击“列表条目”框中，然后在自定义列表中输入项目，一行中输入一项。 确保以您希望它们填入单元格的顺序输入项目。 然后，单击“添加”。
The custom list is added to the Custom lists box, where you can select it so you can edit it by adding or removing items from the List entries box and clicking “Add” again, or you can delete the list by clicking “Delete”. Click “OK”.
自定义列表将添加到“自定义列表”框中，您可以在其中进行选择，以便通过在“列表条目”框中添加或删除项目并再次单击“添加”来进行编辑，或者可以通过单击“删除”来删除列表。 点击“确定”。
Click “OK” on the Excel Options dialog box.
在“ Excel选项”对话框上单击“确定”。
Now, you can type the first item in your custom list, select the cell containing the item and drag the fill handle over the cells you want to fill with the list. Your custom list is automatically filled into the cells.
现在，您可以在自定义列表中键入第一个项目，选择包含该项目的单元格，然后将填充手柄拖到要用列表填充的单元格上。 您的自定义列表将自动填充到单元格中。
方法二：基于现有项目列表填充自定义系列 (Method Two: Fill a Custom Series Based on an Existing List of Items)
Maybe you store your custom list on a separate worksheet in your workbook. You can import your list from the worksheet into the Custom Lists dialog box. To create a custom list based on an existing list on a worksheet, open the Custom Lists dialog box and make sure NEW LIST is selected in the Custom lists box, just like in the first method. However, for this method, click the cell range button to the right of the “Import list from cells” box.
也许您将自定义列表存储在工作簿中的单独工作表上。 您可以将列表从工作表导入“自定义列表”对话框。 要基于工作表上的现有列表创建自定义列表，请打开“自定义列表”对话框，并确保在“自定义列表”框中选择了“新列表”，就像第一种方法一样。 但是，对于这种方法，请单击“从单元格导入列表”框右侧的单元格范围按钮。
Select the tab for the worksheet that contains your custom list at the bottom of the Excel window. Then, select the cells containing the items in your list. The name of the worksheet and the cell range are automatically entered into the Custom Lists edit box. Click the cell range button again to return to the full dialog box.
选择工作表的选项卡，其中包含您在Excel窗口底部的自定义列表。 然后，选择包含列表中项目的单元格。 工作表的名称和单元格范围将自动输入到“自定义列表”编辑框中。 再次单击单元格范围按钮以返回到完整对话框。
Now, click “Import”.
现在，单击“导入”。
The custom list is added to the Custom lists box and you can select it and edit the list in the List entries box, if you want. Click “OK”. You can fill cells with your custom list using the fill handle, just like you did with the first method above.
自定义列表将添加到“自定义列表”框中，您可以选择它，并根据需要在“列表条目”框中编辑列表。 点击“确定”。 您可以使用填充手柄使用自定义列表填充单元格，就像使用上述第一种方法一样。
The fill handle in Excel is a very useful feature if you create large worksheets that contain a lot of sequential data. You can save yourself a lot of time and tedium. Happy Filling!
如果您创建包含大量顺序数据的大型工作表，则Excel中的填充手柄是一项非常有用的功能。 您可以节省很多时间和精力。 开心加油！
vfp取得excel的句柄