跳到主要内容

将公式应用于 Excel 中的整列(5 个技巧)

使用大型 Excel 工作表通常需要在整个列中应用一致的公式。然而,将此公式输入到每个单独的单元格中是一个费力的过程。本教程旨在向您介绍快速有效的策略,用于在整个列中扩展单个公式,从而节省大量时间并确保数据处理的一致性。


通过双击填充手柄将公式沿列复制

将公式应用于整列的最简单、最有效的方法之一是通过简单的鼠标双击技巧,对于很长的数据集特别有效。

  1. 选择包含要复制的公式的列中的第一个单元格。
  2. 找到填充柄(单元格右下角的绿色小方块)并双击它。

    双击填充手柄

请注意: 此方法将沿列应用公式,并在相邻列中最后填充的单元格处停止。


通过拖动填充手柄沿列复制公式

将公式沿列应用的另一种常见方法是手动拖动填充柄。这种方法还克服了双击方法的限制,即当遇到相邻列中的空白单元格时停止。

  1. 选择包含要复制的公式的列中的第一个单元格。
  2. 找到填充柄(单元格右下角的绿色小方块),按住鼠标左键并将其拖动到要扩展公式的单元格。

    拖动填充手柄

提示: 如果您想在右侧的一行中应用公式,只需将填充柄向右拖动到需要应用公式的单元格即可。

使用“填充”命令将公式应用到整列

Excel的 填写 命令是将公式应用于列的另一种有效方法。使用方法如下:

  1. 选择要应用公式的单元格列。 确保您选择的第一个单元格包含您要复制的公式。
  2. 点击 首页 标签,在 编辑 组,选择 > Down.

    填充命令

提示: 要将公式水平应用到一行的右侧,请选择以包含公式的单元格开头的行,然后单击 > .

使用快捷键将公式复制到列中

对于那些熟悉键盘快捷键的人来说,Excel 提供了一种快速替代方法 填写 指挥,提高公式应用效率。让我们探讨如何使用此快捷方式来更快地填充公式。

  1. 选择要应用公式的单元格列。 确保您选择的第一个单元格包含您要复制的公式。
  2. 媒体 按Ctrl + D.

    捷径

提示: 要将公式应用到一行的右侧,请选择以包含公式的单元格开头的行,然后按 Ctrl + R.

通过复制粘贴单元格将公式应用于整列

您还可以将公式应用到列,只需复制包含公式的单元格并将其粘贴到要应用公式的列中的单元格上即可。

  1. 选择包含公式的单元格,然后按 按Ctrl + C 复制它。
  2. 选择要应用公式的所有单元格,然后按 按Ctrl + V 粘贴复制的公式。

    复制粘贴单元格

提示: 复制公式后,您可以选择要应用公式的多个范围,然后按 按Ctrl + V 将公式同时粘贴到所有这些范围中。

解决填充句柄、重新计算和格式问题

在本节中,我们将深入探讨您在使用 Excel 的填充句柄、公式重新计算和单元格格式设置时可能面临的常见挑战。我们将为这些问题提供实用的解决方案,确保更流畅的 Excel 体验。如果您遇到其他问题或需要进一步澄清,请随时联系 发表评论.


看不到填充手柄

如果填充柄不可见,请检查它是否在 Excel 选项中启用。去 文件 > 附加选项 > 先进,并在下 编辑选项,确保 启用填充手柄和单元格拖放 选项已选中。

看不到填充手柄


填充单元格时公式不会重新计算

如果使用上述方法后公式没有自动重新计算,可能是由于工作簿的计算设置所致。要解决此问题,请将工作簿设置为自动计算:转到 公式 > 计算选项,然后选择 自动表。这可确保公式在发生更改后立即更新。

公式不会重新计算


复制公式时避免不需要的格式

当您需要在 Excel 中复制公式但希望避免保留原始单元格的格式时,可以考虑以下一些解决方案:

  • 使用填充手柄时:应用公式后, 自动填充选项 图标 自动填充选项图标 将出现在应用范围的右下角附近。单击此图标并选择 不带格式填充.

    不带格式填充

  • 使用填充命令或快捷键时:请注意,没有直接的方法可以只填充公式而无需格式化。在这种情况下,请考虑使用其他方法来应用您的公式。
  • 使用复制粘贴方法时:一个 粘贴选项 图标 粘贴选项图标 粘贴公式后,将出现在应用范围的右下角附近。单击它并选择 公式 选项 公式选项。这将仅粘贴公式,忽略任何源单元格格式。

    仅粘贴公式

以上是与在Excel中将公式应用于整列相关的所有相关内容。我希望本教程对您有所帮助。如果您想探索更多 Excel 提示和技巧, 请点击这里 访问我们广泛收集的超过数千个教程。

Comments (65)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Bonjour et merci pour le tuto.

Petit question, je sais pas si elle a deja été posée, mais comment faire si j'ai un colonne de 1000 lignes ?
This comment was minimized by the moderator on the site
Hi there,

You can double-click on the small green square in the lower-right corner of the cell which contains the formula. The formula will then fill in the below cells at once.

Amanda
This comment was minimized by the moderator on the site
When dragging function down a column I cant get formula to go past line 30. 31 and down shows #VALUE! any suggestions?
This comment was minimized by the moderator on the site
I need to combine 2 columns (last name first name) into 1 column (name) and then delete the 2 columns that I combined. How do I do that?
This comment was minimized by the moderator on the site
Formula =A1&" "&B2
make sure there's a space between the "
This comment was minimized by the moderator on the site
Hi, i wanted to ask something
I want to count the total Shifts within the Shift column and put it in either Shift 1,Shift 2, or Shift 3 in column Q,R & S.
As you can see in the picture, i already copy the formula from M2 (Circle 2) to below it.
The thing is, i wanted to count the Shift per Each Day.
But as you can see i had a little bit of problem. I had 2 problems :
1. I want to copy the formula to the cell below BUT in a different day, NOT the same day, i searched on the internet and i still don't have a clue on how to do that.
2.If there's no way to do that, is there any way that i can count the shift (i'm using COUNTIF here in Q2,R2 & S2 (Circle 3) ) from 23rd July - 30th July, without including the shift from the same day ? The point is i wanted to retrieve one shift per day, and as you can see if i retrieve the data, it counts as three/four shift per day (Circle 1).

If there's any question about my problem, do feel free to ask !!
Thank you.
This comment was minimized by the moderator on the site
I forgot to attach the picture i think, here it is.
This comment was minimized by the moderator on the site
hi, if the cell are not the same size in a column, then how can i copy the calculation of the top cell of column to the end (there are 200 rows in that column), as example if the first cell contains three rows merged but the second one is of two rows merged then dragging option does not work, what to do then ? pls advise.
This comment was minimized by the moderator on the site
Hi Saif,
Hotkeys may work.
Select the target column or cells you will apply formulas, type the formula in the formula bar, and then press Ctrl + Enter keys simultaneously.
This comment was minimized by the moderator on the site
Pershendetje , si mund te funksionoje formula ne excel pa i dhene save , sepse nuk e shfaq veprimi e kryer pa dhe save. Faleminderit!
This comment was minimized by the moderator on the site
can it be used for to varting values
This comment was minimized by the moderator on the site
How i can use them in VBA?
This comment was minimized by the moderator on the site
Hi Rakesh,
All of methods introduced in the article are very easy. Is the VBA necessary?
This comment was minimized by the moderator on the site
How do i divide the one entire coloumn filled with number by 1000 or any number witout creating nother coloumn?
This comment was minimized by the moderator on the site
Hi Sunny,

Kutools for Excel’s Operation feature can help solve this problem.

(1) Select the column you need to divided by a certain number;

(2) Click Kutools > More > Operation;
(3) In the Operation Tools dialog, specify Division, type in the divisor, and click OK.
This comment was minimized by the moderator on the site
Hi, How can I do sum of two different column with formula? Like I want to sum of A1 and D1 and getting answer on F1 then which formula apply?
This comment was minimized by the moderator on the site
you can use this formula,F1=A1+D1
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations