跳到主要內容

如何在Excel中將數字四捨五入到最接近的5/10/50/100?

有時,您可能希望將數字轉換為最接近的5/10/50/100,以便在Excel中進行計算。 本教程提供了一些簡單的公式,可以將數字四捨五入到最接近的特定數字,還介紹了將數字四捨五入到下一個或最後一個最近的數字的公式。
doc四捨五入至最接近的5 10 50 1

四捨五入至最接近的5

 


箭頭藍色右氣泡 四捨五入至最接近的5

四捨五入至最接近的5/10/50/100

四捨五入到最接近的5 =圓(A2 / 5,0)* 5
四捨五入到最接近的10 =圓(A2 / 10,0)* 10
四捨五入到最接近的50 =圓(A2 / 50,0)* 50
四捨五入到最接近的100 =圓(A2 / 100,0)* 100

doc四捨五入至最接近的5 10 50 2

注意:

您也可以使用此公式 = MROUND(A2,5) 將數字四捨五入至最接近的5。

舍入到下一個最近的5/10/50/100

舍入到下一個最近的5 = ROUNDUP(A2 / 5,0)* 5
舍入到下一個最近的10 = ROUNDUP(A2 / 10,0)* 10
舍入到下一個最近的50 = ROUNDUP(A2 / 50,0)* 50
舍入到下一個最近的100 = ROUNDUP(A2 / 100,0)* 100

doc四捨五入至最接近的5 10 50 3

四捨五入到最接近的5/10/50/100

舍入到最接近的5 =回合(A2 / 5,0)* 5
舍入到最接近的10 =回合(A2 / 10,0)* 10
舍入到最接近的50 =回合(A2 / 50,0)* 50
舍入到最接近的100 =回合(A2 / 100,0)* 100

doc四捨五入至最接近的5 10 50 4


無需公式即可輕鬆將數字向上或向下取整,甚至在Excel中取整

眾所周知,有些公式可以四捨五入,但是您不想使用公式四捨五入,因此Kutools for Excel的Round實用程序可以幫您一個忙。 點擊免費試用30天!
文檔輪
Kutools for Excel:擁有300多個便捷的Excel加載項,可以在30天內免費試用。
 

最佳辦公生產力工具

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

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

kte選項卡201905


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

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (22)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
may i know how to set a formula which include 2 criteria, 1/2 remain 1/2 and every 3/4 round up to 1, e.g. 3/4->1, 1-3/4->2, 2-3/4->3
This comment was minimized by the moderator on the site
I want to round up in the same columm,

Example : 1201 to be 1250
This comment was minimized by the moderator on the site
I want to round up or down decimal places in the same columm,

Example : 12.49 to be 12 and 12.50 to be 13
This comment was minimized by the moderator on the site
I want to generate an IF formula that will help me round up or down decimals in the same column

example :

Cell A1 ----- 6.13 want it to round up to 6.49
Cell A2 ----- 6.74 want it to round up to 6.79
Cell A3 ----- 6.84 want it to round down to 6.79
This comment was minimized by the moderator on the site
Sorry, Jose, I do not understand your round up and down rule.
This comment was minimized by the moderator on the site
I want to generate an IF formula that will help me round up or down decimals in the same column

example :

Cell A1 ----- 6.13 want it to round up to 6.49
Cell A2 ----- 6.74 want it to round up to 6.79
Cell A3 ----- 6.84 want it to round down to 6.79
This comment was minimized by the moderator on the site
HOW TO ROUNDUP 2.5 =5 AND 7.8=10
This comment was minimized by the moderator on the site
Use this formula =mround("cell address where 2.5 or 7.8 is written in the excel sheet",5) i.e. if 2.5 is written in cell A1 then =mround(a1,5) and if 7.8 is written in cell B1 then =mround(b1,5)
This comment was minimized by the moderator on the site
Sorry I have no idea.
This comment was minimized by the moderator on the site
i need formula that will write 95000 as 90000 or 87000 as 80000, if figure 89000 then it should be 90000.
Means only 9 or above then should mround to 10 otherwise not
This comment was minimized by the moderator on the site
You can go to our forum to submit your question, someone may answer you. https://www.extendoffice.com/forum.html
This comment was minimized by the moderator on the site
HOW TO ROUND 50.49 =50 AND 50.50=60 AND 90.49=90 AND 90.50=100
This comment was minimized by the moderator on the site
Hello, you just need this formula =ROUND(reference, 0).
This comment was minimized by the moderator on the site
well articulated article, worked for me thanks
This comment was minimized by the moderator on the site
Can I use this in a formula? For example, I want the formula =(C118*D118)+50, but I want the answer to round up to the nearest five. So if C118 is 216, and D118 is 13, I want 2808 + 50 to round up to 2860. I'd like to to it all in the same column rather than adding a column. Thanks!
This comment was minimized by the moderator on the site
use this formula =ceiling((c118*d118)+50,10)
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