如何一步到位進行數值取整——Excel冷門函數用法大匯總

2020-12-11 Excel表哥妙用

本文將用到:

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函數取整用法

輸入公式ROUND(314.159,-1),結果等於310。

(此刻的你是不是覺得表哥搞錯了結果?)

並沒有搞錯,結果就是310,是一個整數。

明明平時作為保留小數用的函數,為什麼結果是整數?而且個位還不見了?

如果公式改為ROUND(314.159,-2),那麼結果為300。

ROUND函數取整用法2

可能此刻你已經猜到了,玄機就在於負號。(如果你猜中了,想像表哥在為你點讚)

保留小數位時,公式中逗號後面為正號,只是通常省略掉不寫,而逗號後面若為負,表示小數點前去掉的位數,同時遵循是四捨五入原則。

ROUND函數取整用法3

如果公式為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()函數吧。這個路數有些不同。

FLOOR函數適用office版本

根據OFFICE官方說明可知,此函數是在Excel2007開始嵌入的,如果你在使用的版本包含在上述說明之內,才可以使用FLOOR函數。

為啥要特意說明呢?因為還有人在用上個世紀的版本呀(好心勸更新中)

FLOOR在英語中是地板的意思,在空間的下方,所以FLOOR函數的意思估計你猜到了。是的,正是向下取整。

為什麼表哥會說他路數有些不同呢?

通常,一提到取整,想到的是去掉小數位,以及在上文剛剛提到的在在小數位前進行四捨五入。然而用FLOOR取整時原理不太一樣,函數內部需要進行乘法運算,再得出結果。

比如,FLOOR(314.159,3)意思是將314.159向下取整,最接近3的倍數,結果為312。

FLOOR函數用法1

如何應用到用的開篇案例呢?

FLOOR取整用法案例

由於預期結果的個十百千位均為0,因此需要在萬位取整,則選擇10000作為基數,求向下取整最接近10000的倍數。公式為:FLOOR(2469336,10000),結果為2460000。

FLOOR取整用法案例

表哥TIPS:括號內逗號後面的參數,不一定設定為正整數,根據實際情況,也可以設定為小數和負數。但要注意,括號內逗號前後,正負號一定要一致,否則會報錯。

圖為OFFICE官方案例,基本上各種情況都覆蓋全了。

Office官網對floor的應用舉例

3. 拓展內容:

(1)入地上天

上面半開玩笑講到因為FLOOR的意思是地面,地面在空間的下方,所以是向下取整的含義。那麼現在請你想一想向上取整是哪個函數呢?

在房間內,與地板空間相對的是天花板,也許你會推測向上取整會是天花板嗎?

天花板的英文單詞是CEILING,而與FLOOR對應的向下取整函數,正是如此命名。(表哥不知如何描述這個函數的開發工程師,命名如此隨意,還是有情趣呢,哈哈)

CEILING的用法與FLOOR相同,只是前者向上,後者向下。

CEILING用法舉例

還是以本文經典案例算一算吧。將2469336下上取整,最接近10000的倍數,公式為CEILING(2469336,10000)=2470000

(2)這種以倍數取證路數的函數還有MROUND()

一看到ROUND就條件反射地認為與四捨五入有關。

機智如你,確實如此。

MROUND的作用是將數值四捨五入到最接近某數的整倍數。

MROUND函數四捨五入結果對比

314.159求得2的整倍數是314,四捨五入後仍然是314。而315.159求得2的整倍數有兩個,一個是314,一個是316,四捨五入後316距離315.159更為接近,所以取得316。

但注意,此函數在位於中點取值時,由於算法限制,結果不明,建議繞道行之。

官網測試的例子如下:

Office對MROUND函數的限制說明

(3)最後爆出個大冷門函數TRUNC()

用於取整的函數,與INT用法很相似,但不同之處在於負數的取整。

INT與TRUNC結果對比

是由於兩者負數取整的方向不同。

INT認為負數取整要往遠離零點的方向,所以結果更小,而TRUNC認為負數取整要靠近零點的方向,所以結果比INT的大。

另外,在小數取整數也有所區別。

函數INT()只能處理簡單的去掉小數點取整,而TRUNC()可以像上文講到ROUND一樣,可以

指定在小數點前去掉位數取整,而且沒有四捨五入的操作,純粹取整。如下圖:

INT與TRUNC結果對比2

以上就是因為一個取整問題,不禁歷數EXCEL中幾乎所有的取整函數。

表哥認為隨著OFFICE不斷更新,今後還會有更多更好用的方法。

你覺得哪個函數最好用?

歡迎給表哥留言,

與大家分享你的取整大法

↖(^ω^)↗撒花

相關焦點

  • 最全的Excel捨入、取整函數用法(共7大類)
    提起excel數值取值,都會想起用INT函數。其實excel還有其他更多取整方式,根據不同的要求使用不同的函數。
  • excel數值「取整」公式全集,共7種,你知道幾種?
    =ROUND(1534.56,-1) 結果為 1530=ROUND(1534.56,-2) 結果為 1500=ROUND(1534.56,-3) 結果為 2000五、向上捨入式取整隻要數值大於1,都可以向上進一位。
  • Excel取整公式(全集)
    文/趙志東(微信公眾號: Excel精英培訓)提起excel數值取值,都會想起用INT函數。
  • excel電子表格int取整函數介紹
    每一個函數都用它的用途,可能在有些生活上處理數據的時候,遇到int函數。好,閒話不多說,我們一起看看int函數的用法int函數是一個用於小數取整的函數,值得注意的是只是當大於0的數取整數部分,後面的小數部分直接忽略。
  • EXCEL數值取整時,負數怎麼處理?
    EXCEL中對數值取整,有很多函數可以實現,但是每個函數的含義是不一樣的,如果不注意就會出錯,今天就分享一下INT,TRUNC,ROUND,ROUNDUP,ROUNDDOWN這幾個函數的用法: INT函數是將數值向下取整為最接近的整數,注意是向下,所以任意數取整後都會小於這個數。
  • Excel數值取整的8種正確打開方式
    2、INT函數取整方法:選中目標單元格,在目標單元格中輸入公式「=INT(A2)」,下拉完成填充即可。註:若數值為正數,可直接去掉小數點取整,若數值為負數,需減1再取整。3、TRUNC函數取整方法:選中目標單元格,在目標單元格中輸入公式「=TRUNC(A2)」,下拉完成填充即可。註:TRUNC函數取整不論數值是正數還是負數,去掉小數後直接取整不進位。
  • Excel如何對單元格數值取整?7種方法結果各不相同
    今天上午有朋友在公眾號中問,如何對單元格數值取整,現在我們就來解決這個問題。一、函數介紹1.ROUND、ROUNDUP、ROUNDDOWN函數這三個函數的用法已經在前面文章中介紹過,大家可以參考下面這篇文章。
  • 方便實用,教你在excel中取整函數的使用
    教你在excel中取整函數的使用第一步:excel取整函數的使用還是很方便的,下面我們就舉例給大家介紹一下excel取整函數。所需要的函數:int()第三步:單元格B2輸入公式:=INT(A2),也就是excel取整函數,然後回車,你會發現單元格B2已經是單元格A2的整數部分了,然後單元格右下角是黑色十字形狀進行下拉填充第四步:對A列數據四捨五入到整數部分,要用到函數:=round()第五步:單元格C2輸入公式
  • Excel 取整、四捨五入公式全集
    文/趙志東(微信公眾號: Excel精英培訓)提起excel數值取值,都會想起用INT函數。
  • Excel捨入、取整函數公式,幫你整理齊了(共7大類)
    來源:祥順財稅俱樂部提起excel數值取值,都會想起用INT函數。其實excel還有其他更多取整方式,根據不同的要求使用不同的函數。本文適合所有粉絲,共計732字,預計閱讀時間3分鐘。三、四捨五入式取整當ROUND函數的第2個參數為0時,可以完成四捨五入式取整=ROUND(12.4) 結果為 12=ROUND(12.6) 結果為 13四、整數位取整當ROUND函數第2個參數為負數時
  • Excel取整函數之ROUND函數 Excel取整函數之ROUND函數的方法
    我們在常用的excel操作中,經常會對表格中的數字取整操作。今天小編要為大家講解的是Excel取整函數之ROUND函數的方法。Excel取整函數之ROUND函數round取整常用的四捨五入取整函數為round函數。
  • Excel常用取整函數——INT、ROUND、TRUNC說明
    今天講幾個Excel常用的取整函數。一、INT函數用法:若數值為正數,去掉小數後直接取整。若數值為負數,去掉小數後需要再-1取整。實例:二、ROUND函數用法:ROUND(數值,小數位數),ROUND即為對數值的絕對值四捨五入。
  • 學會這8個組Excel函數,解決工作中80%的難題
    函數是excel中最重要的分析工具,面對400多個excel函數新手應該從何下手呢?
  • excel函數公式大全之利用SUM函數與RANK函數的組合對數值進行排名
    excel函數公式大全之利用SUM函數與RANK函數的組合對數值進行排名,excel函數與公式在工作中使用非常的頻繁,會不會使用公式直接決定了我們的工作效率,今天我們來學習一下提高我們工作效率的函數SUM函數與RANK函數。對於SUM函數大家在熟悉不過了,我們就不在做過多的講解。
  • excel圖文教程:常用數值取捨方法匯總
    說到excel中關於數值取捨的方法,可能很多小夥伴都不怎麼在意,總覺得不就是對數值進行四捨五入或者是取整嗎,用處又不大,有什麼好講的。nonono!數值的取捨遠遠不止這些,並且它們在工作中用處可大了,比如計算工齡,用於貨幣單位的轉化等等,今天我們就一起來好好認識認識它們吧!學習更多技巧,可以收藏部落窩教育excel圖文教程。
  • excel圖文教程:常用數值取捨方法匯總
    說到excel中關於數值取捨的方法,可能很多小夥伴都不怎麼在意,總覺得不就是對數值進行四捨五入或者是取整嗎,用處又不大,有什麼好講的。nonono!數值的取捨遠遠不止這些,並且它們在工作中用處可大了,比如計算工齡,用於貨幣單位的轉化等等,今天我們就一起來好好認識認識它們吧!學習更多技巧,可以收藏部落窩教育excel圖文教程。
  • excel函數利用ROUNDDOWN函數ROUND函數ROUNDUP函數進行四捨五入
    ,excel函數公式大全之利用ROUNDDOWN函數ROUND函數ROUNDUP函數對數字進行向下捨入、四捨五入、向上捨入操作,excel函數與公式在工作中使用非常的頻繁,會不會使用公式直接決定了我們的工作效率。
  • excel函數公式實戰:文本函數TEXT常用技巧匯總
    要說在excel中最特別的文本函數,那必定非TEXT函數莫屬,外界給它的稱號不計其數「文本之王」「整容大師」「千面鬼才」等等,由此可看出對它的喜愛。下面小花就和大家匯總了TEXT函數5種最實用的用法,趕緊來看看吧!學習更多技巧,請收藏。
  • 1.2.21 EXCEL篇之函數篇-數學與三角函數1
    >常用的數學函數,主要分為以下幾大類:一、數值捨入取整二、數值求和三、對乘積進行計算、求和四、計算各種總計值五、平方根、隨機數下面我們逐一來看看這五大類函數的具體使用方法~一、數值捨入取整(1) round函數作用:根據指定的位數位置四捨五入語法:round(number,num_digits)語法解析:round(數值,保留位數)
  • Excel如何把小數點四捨五入取整?
    Excel如何把小數點四捨五入取整呢?下面一起看下解決的方法。操作步驟如下:方法一:四捨五入函數ROUND函數格式:ROUND(數值或數值單元格,要保留的位數)例:ROUND(20.156,2) 表示對20.156四捨五入,保留2位小數,結果為20.16