本文將用到:
INT()/TRUNC() 返回整數ROUND() 返回取整或保留小數位ROUNDUP() 返回向上取整或保留小數ROUNDDOWN() 返回向下取整或保留小數位FLOOR() 倍數向下取整CEILING() 倍數向上取整MROUND() 倍數四捨五入取整前幾天被問到一個取整問題:如何讓2469336後四位取整後變為2460000,而不是2470000?
提到取整,大多數人一般首先想到用INT()函數,這是用來數字取整最簡單最常用的函數。INT()常用於將小數取整為整數,並且沒有四捨五入的操作。
但觀察問題中數據2469336已然是個整數,顯然INT()無法直接解決問題。
問題中糾結在於萬位數字是6還是7,所以將整數2469336,轉化為小數246.9336×10000,因此問題就轉化為將246.9336取整為246。
根據以上思路,分三步進行:
(1)將2469336除以10000轉化為246.9336
(2)INT取整246.9336為246
(3)將取整結果還原為原數據量級
綜上公式為INT(2469336/10000)*10000=246000
可有一步到位的方法嗎?
當然有。
而且不止一種,只是有點冷門。
儘管鮮為人知,但實際上如若掌握,非常事半功倍。
1. 函數ROUND()
平時常用於保留小數位。
舉例1:將3.14159四捨五入保留四位小數
公式為ROUND(3.14159,4)=3.1416,其中括號內逗號後的4,表示保留的小數點後的位數。
(1)如果將其改為負號,會報錯嗎?或是得出什麼其他結果?
輸入公式ROUND(314.159,-1),結果等於310。
(此刻的你是不是覺得表哥搞錯了結果?)
並沒有搞錯,結果就是310,是一個整數。
明明平時作為保留小數用的函數,為什麼結果是整數?而且個位還不見了?
如果公式改為ROUND(314.159,-2),那麼結果為300。
可能此刻你已經猜到了,玄機就在於負號。(如果你猜中了,想像表哥在為你點讚)
保留小數位時,公式中逗號後面為正號,只是通常省略掉不寫,而逗號後面若為負,表示小數點前去掉的位數,同時遵循是四捨五入原則。
如果公式為ROUND(354.159,-2),因為小數點前第2位是5,根據四捨五入原則,百位數的3就進為4,則結果為400。
在本文開篇的案例中,如果使用函數ROUND(),公式為ROUND(2469336,-4),去掉小數點前4位數,四捨五入後,結果為2470000,但這並不是我們想要的答案。
如何才能得出我們想要的2460000?
(2)除了ROUND可以取整,還有看起來很像的ROUNDDOWN()、ROUNDUP()也可以分別用來向上向下取整。
ROUNDUP(2469336,-4)=2470000
ROUNDDOWN (2469336,-4)=2460000
案例想求得是向下取整,因此選擇用ROUNDDOWN即可。
表哥Tips:注意ROUNDDOWN()雖然文字是向下取整,但意思不是要減掉1,而是不進行四捨五入的操作,保持數字原狀而已。
2. 如果函數ROUNDDOWN()輸入很長很麻煩,那麼試一試FLOOR()函數吧。這個路數有些不同。
根據OFFICE官方說明可知,此函數是在Excel2007開始嵌入的,如果你在使用的版本包含在上述說明之內,才可以使用FLOOR函數。
為啥要特意說明呢?因為還有人在用上個世紀的版本呀(好心勸更新中)
FLOOR在英語中是地板的意思,在空間的下方,所以FLOOR函數的意思估計你猜到了。是的,正是向下取整。
為什麼表哥會說他路數有些不同呢?
通常,一提到取整,想到的是去掉小數位,以及在上文剛剛提到的在在小數位前進行四捨五入。然而用FLOOR取整時原理不太一樣,函數內部需要進行乘法運算,再得出結果。
比如,FLOOR(314.159,3)意思是將314.159向下取整,最接近3的倍數,結果為312。
如何應用到用的開篇案例呢?
由於預期結果的個十百千位均為0,因此需要在萬位取整,則選擇10000作為基數,求向下取整最接近10000的倍數。公式為:FLOOR(2469336,10000),結果為2460000。
表哥TIPS:括號內逗號後面的參數,不一定設定為正整數,根據實際情況,也可以設定為小數和負數。但要注意,括號內逗號前後,正負號一定要一致,否則會報錯。
圖為OFFICE官方案例,基本上各種情況都覆蓋全了。
3. 拓展內容:
(1)入地上天
上面半開玩笑講到因為FLOOR的意思是地面,地面在空間的下方,所以是向下取整的含義。那麼現在請你想一想向上取整是哪個函數呢?
在房間內,與地板空間相對的是天花板,也許你會推測向上取整會是天花板嗎?
天花板的英文單詞是CEILING,而與FLOOR對應的向下取整函數,正是如此命名。(表哥不知如何描述這個函數的開發工程師,命名如此隨意,還是有情趣呢,哈哈)
CEILING的用法與FLOOR相同,只是前者向上,後者向下。
還是以本文經典案例算一算吧。將2469336下上取整,最接近10000的倍數,公式為CEILING(2469336,10000)=2470000
(2)這種以倍數取證路數的函數還有MROUND()
一看到ROUND就條件反射地認為與四捨五入有關。
機智如你,確實如此。
MROUND的作用是將數值四捨五入到最接近某數的整倍數。
314.159求得2的整倍數是314,四捨五入後仍然是314。而315.159求得2的整倍數有兩個,一個是314,一個是316,四捨五入後316距離315.159更為接近,所以取得316。
但注意,此函數在位於中點取值時,由於算法限制,結果不明,建議繞道行之。
官網測試的例子如下:
(3)最後爆出個大冷門函數TRUNC()
用於取整的函數,與INT用法很相似,但不同之處在於負數的取整。
是由於兩者負數取整的方向不同。
INT認為負數取整要往遠離零點的方向,所以結果更小,而TRUNC認為負數取整要靠近零點的方向,所以結果比INT的大。
另外,在小數取整數也有所區別。
函數INT()只能處理簡單的去掉小數點取整,而TRUNC()可以像上文講到ROUND一樣,可以
指定在小數點前去掉位數取整,而且沒有四捨五入的操作,純粹取整。如下圖:
以上就是因為一個取整問題,不禁歷數EXCEL中幾乎所有的取整函數。
表哥認為隨著OFFICE不斷更新,今後還會有更多更好用的方法。
你覺得哪個函數最好用?
歡迎給表哥留言,
與大家分享你的取整大法
↖(^ω^)↗撒花