跳到主要內容

如何在Excel中每x行增加數字？

doc增量x第1行

通常，我們可以使用Fill Handle填充一列中的序列號，但是您是否曾經嘗試過每x行以增量號填充一列？ 例如，前五行填充數字1，在第六行該值將變為6，然後在第2行該值將變為11，依此類推，如下圖所示，為解決此問題，本文可能為您提供了恩。

使用公式每x行增加一個數字

箭頭藍色右氣泡 使用公式每x行增加一個數字

下面的簡單公式可以幫助您根據需要快速在每x行的增量列中填充列，請執行以下操作：

1。 輸入以下公式： = INT（（ROW（E1）-1）/ 5）+1 放入要填充序列號的空白單元格，請參見屏幕截圖：

doc增量x第2行

備註：在上式中，數字 5 表示每5行增加一次數字，您可以根據需要進行更改。

2。 然後將填充手柄向下拖動到您要填充增量數字的單元格，並且每1行插入數字後，增量為5，請參見屏幕截圖：

doc增量x第3行

 

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！

 

Comments (28)
No ratings yet. Be the first to rate!
alberto arroyo
alberto arroyo
This comment was minimized by the moderator on the site
I need to increase a value by X percentage every 3 months. I need the new value to stay fixed for 3 months and then again increase by the same percentage or a different one for another period of 3 months. In other words, I would like to have every 3 months a new number automated by a formula. May I have the formula?
Reply
0
0
Faizul
Faizul
This comment was minimized by the moderator on the site
Hi, I'm working on a data creation where I have to create sequential numbers based on data present in columnA. I have to create id in sequence which I can later concatinate after generating the sequence. Challenge is depending on the count of each value in columnA, the series should fill incrementally based on the count of each value. Also max sequence numbers can be 6 only. If ColumnA rows for a specific value goes above 6, then next series number should start. Any help would be highly appreciated. Eg shown below.

ColumnA ColumnB
PTSA 1
PTSA 1
PTSB 2
PTSB 2
PTSC 3
PTSD 4
PTSE 5
PTSE 5
PTSE 5
PTSE 5
PTSE 5
PTSE 5
PTSE 6
PTSE 6
PTSF 7
Reply
0
0
Vignesh
Vignesh
This comment was minimized by the moderator on the site
Hi all,

How do i increase in the range of 100 instead of 1. For ex after every 5 columns i need 100 ,200 etc
Reply
0
0
skyyang
skyyang     Vignesh
This comment was minimized by the moderator on the site
Hello, Vignesh
To solve your problem, please apply the below fromula:
=(INT((ROW(A1)-1)/5)+1)*100
Please have a try, hope it can help you!
Reply
Report
0
0
Alfredo
Alfredo
This comment was minimized by the moderator on the site
Hello!!! very interesting!!! Is there a way to do this but with months?
Something like:
A1
25/11/2018
25/11/2018
25/11/2018
25/11/2018
25/12/2018
25/12/2018
25/12/2018
25/12/2018
25/01/2019
25/01/2019
25/01/2019
25/01/2019.... and so on?

Thanks!
Reply
0
0
skyyang
skyyang     Alfredo
This comment was minimized by the moderator on the site
Hello, Alfredo
To solve your problem, please apply the below formula:
=DATE(YEAR("2018/11/25"),MONTH("2018/11/15")+INT((ROW(A1)-1)/4),DAY("2018/11/25"))
Note: If the date format does not match your need, please change the date format to oyur own.
Please have a try, hope it can help you!
Reply
Report
0
0
Yazen
Yazen
This comment was minimized by the moderator on the site
Hello,

What is the formula to add sequential numbers to every 8 rows. I don't want the rows in between to have any numbers (2-7, or 9-16, and so on).

Thank you,
Yazen
Reply
0
0
skyyang
skyyang     Yazen
This comment was minimized by the moderator on the site
Hello, Yazen
Sorry, I can't get your point, could you give a detailed example?
You can insert a screenshot or an Excel file of your problem here.
Thank you!
Reply
Report
0
0
Yazen
Yazen     skyyang
This comment was minimized by the moderator on the site
I am unable to add a screenshot or attach a file. But, here is what the excel looks like:
1 Apple 1
Apple 2
Apple 3
Apple 4
Apple 5
Apple 6

2 Orange 1
Orange 2
Orange 3
Orange 4
Orange 5
Orange 6

3 Banana 1
Banana 2
Banana 3
Banana 4
Banana 5
Banana 6

4 Melon 1
Melon 2
Melon 3
Melon 4
Melon 5
Melon 6


The numbers from 1 to 4 are manually entered. I am trying to figure out a formula that lets me add these numbers automatically to the first row category only and not every cell in that category.

Any help would be greatly appreciate it.

Thank you.
Reply
0
0
skyyang
skyyang     Yazen
This comment was minimized by the moderator on the site
Hello,
To solve your problem, please use the below formula:
=IF(MOD((ROW(A1)-1)/6, 1)=0,(ROW(A1)-1)/6+1, "")

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-increase-numbers.png
Please have a try, hope it can help you!
Reply
Report
0
0
Yazen
Yazen     skyyang
This comment was minimized by the moderator on the site
Thank you very much, that's exactly how I wanted it to be.
Reply
0
0
Jay
Jay
This comment was minimized by the moderator on the site
Hey, thank you for this article. can I increase the specified row by other value instead of 1?
Reply
0
0
skyyang
skyyang     Jay
This comment was minimized by the moderator on the site
Hello，Jay，
Sorry, I can't get your point, could you give a detailed example? You can insert a picture here to describe your problem.
Thank you!
Reply
Report
0
0
Rahul
Rahul
This comment was minimized by the moderator on the site
Thank you so much for this article.. It saved a lot of time for me. Appreciate your support guys..
Reply
0
0
Marjan
Marjan
This comment was minimized by the moderator on the site
Hello, I was trying to use the same formula for every 12 rows. =INT((ROW(R5)-1)/(12))+1877. But it does not work
Reply
0
0
skyyang
skyyang     Marjan
This comment was minimized by the moderator on the site
Hello, Marjan, To increment number every 12 Rows, please use this formula:=INT((ROW(E1)-1)/12)+1
Please try it, thank you!
Reply
Report
0
0
S_
S_
This comment was minimized by the moderator on the site
Thank you so much for this solution!!!!...........Your blog saves my night's sleep :)
Reply
0
0
Srinath V
Srinath V
This comment was minimized by the moderator on the site
Very helpful, thanks !
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations