跳到主要內容

將公式套用至 Excel 中的整列(5 個技巧)

使用大型 Excel 工作表通常需要在整個列中應用一致的公式。然而,將此公式輸入到每個單獨的單元格中是一個費力的過程。本教程旨在向您介紹快速有效的策略,用於在整個列中擴展單一公式,從而節省大量時間並確保資料處理的一致性。


透過雙擊填充手把將公式沿列複製

將公式應用於整列的最簡單、最有效的方法之一是透過簡單的滑鼠雙擊技巧,對於很長的資料集特別有效。

  1. 選取包含要複製的公式的列中的第一個儲存格。
  2. 找到填充柄(單元格右下角的綠色小方塊)並雙擊它。

    雙擊填充手柄

注意: 此方法將沿列套用公式,並在相鄰列中最後填入的儲存格處停止。


透過拖曳填充手把沿列複製公式

將公式沿列應用的另一種常見方法是手動拖曳填充柄。這種方法還克服了雙擊方法的限制,即當遇到相鄰列中的空白單元格時停止。

  1. 選取包含要複製的公式的列中的第一個儲存格。
  2. 找到填滿柄(儲存格右下角的綠色小方塊),按住滑鼠左鍵並將其拖曳到要擴充公式的儲存格。

    拖曳填充手柄

小提示: 如果您想在右側的一行中套用公式,只需將填充手柄向右拖曳到需要套用公式的儲存格即可。

使用“填充”命令將公式應用到整列

Excel的 填寫 命令是將公式應用於列的另一種有效方法。使用方法如下:

  1. 選擇要套用公式的儲存格列。 確保您選擇的第一個儲存格包含您要複製的公式。
  2. 首頁 標籤,在 編輯 組,選擇 > .

    填充命令

小提示: 若要將公式水平套用到一行的右側,請選取以包含公式的儲存格開頭的行,然後按一下 > .

使用快捷鍵將公式複製到列中

對於熟悉鍵盤快捷鍵的人來說,Excel 提供了快速替代方法 填寫 指揮,提高公式應用效率。讓我們探討如何使用此快捷方式來更快地填入公式。

  1. 選擇要套用公式的儲存格列。 確保您選擇的第一個儲存格包含您要複製的公式。
  2. 媒體推薦 按Ctrl + D.

    捷徑

小提示: 若要將公式套用到一行的右側,請選取以包含公式的儲存格開頭的行,然後按 Ctrl + R.

透過複製貼上儲存格將公式應用於整列

您也可以將公式套用到列,只需複製包含公式的儲存格並將其貼上到要套用公式的列中的儲存格上即可。

  1. 選擇包含公式的儲存格,然後按 按Ctrl + C 複製它。
  2. 選取要套用公式的所有儲存格,然後按 按Ctrl + V 貼上複製的公式。

    複製貼上單元格

小提示: 複製公式後,您可以選擇要套用公式的多個範圍,然後按 按Ctrl + V 將公式同時貼到所有這些範圍中。

解決填滿句柄、重新計算和格式問題

在本節中,我們將深入探討您在使用 Excel 的填滿句柄、公式重新計算和儲存格格式設定時可能面臨的常見挑戰。我們將為這些問題提供實用的解決方案,確保更流暢的 Excel 體驗。如果您遇到其他問題或需要進一步澄清,請隨時聯繫 發表評論.


看不到填充手柄

如果填充柄不可見,請檢查它是否在 Excel 選項中啟用。去 文件 > 選項 > 高級,並在下 編輯選項,確保 啟用填充手柄和單元格拖放 選項被選中。

看不到填充手柄


填充儲存格時公式不會重新計算

如果使用上述方法後公式沒有自動重新計算,可能是因為工作簿的計算設定所致。若要解決此問題,請將工作簿設定為自動計算:前往 公式 > 計算選項,然後選擇 Automatic 自動錶。這可確保公式在發生變更後立即更新。

公式不會重新計算


複製公式時避免不需要的格式

當您需要在 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