Excel函數詳解-不同角度看ROUND函數,原來還可以這麼玩!

2021-01-08 小哥聊經驗

今天繼續了解數學計算中用到的函數，具有四捨五入功能的函數，共有四對，分別都有不同的使用場景，今天我們一起看看這四對組合函數的使用場景和使用注意事項！

ROUND函數

ROUND函數符合我們正常生活所說的四捨五入的規則，雖然它有兩個參數，但想操作自如也並非易事，我們一起看看在它簡單的參數下有這不簡單的使用規則。語法結構如下圖：

ROUND函數語法結構圖

我們最常用的恐怕就是帶有2位小數的公式寫法啦，不過今天我們要把內容擴展一下，看看這個函數除了我們已經了解的，還能幹什麼呢？我們沒有了解的部分恐怕就是精度數為負數的部分，我也將這部分數據加以測試，也得到有意思的結果也是有規律的，先上測試數據，如下圖：

ROUND測試數據圖

圖中的數據把鏡度數分為三部分，負數，0，正數，正數部分比較容易理解，代表保留的小數位數，1就是保留一位並根據四捨五入的規則，是否進行保留位加1，0則道理同上，只保留整數部分，而負數部分就比較於意思了，-1就是10的1次方為進位基數，-2就是10的2次方進位基數，即當為-1時：>0且<5時，返回0，大於等於5且小於15時，返回10，大於等於15且小於25的則返回20，看完了整數的部分，我們需要確認一下帶有小數是否會有對結果並沒有影響，結果並沒有驚喜，從上面的原理來看10的0.1次方結果是0.794328234724281，這個值也確實不太好計算取捨所以就簡化處理，對第二參數進行取整運算後再進行下一步的處理，說了這麼多，你是不是覺的沒有用啊？我只要知道他的功能就行了啊！

其實這些知識點是很有用的，在函數的使用，我們恰恰需要注意的地方就是函數未處理的地方或者禁區，只有知道這些，我們才能更好的編寫公式的時候不會出錯，就以上面的講述的為例，在編寫ROUND函數的公式，第二參數就不用在意是不是整數，用不用使用比如INT函數來取整一下，還有我們通過了解它的特性，在編寫公式我們就會多一種處理方法比如，階段性評分或評級，就完全可以用ROUND函數鏡度數為負數來處理，在計算機的程序中，執行數學計算的比比較大小效果要高哦！

日常案例

某公司的銷售額每月已不能達到5萬元的，沒有獎金，5萬到15萬以內的為一級獎金一級獎金以5萬基數，每增加一萬元，超出基數的部分以3%作為獎金，15萬元到25萬以內的則為二級獎金以15萬元為基數，則以增加的部分則以5%作為獎金，依次類推，超過25萬的則三級獎金，25萬為基數，超出基數的部分則以8%為獎金，獎金計算模式：N級=1級+2級+……+N級，求每人的每月的工資，具體數據如下：

銷售公司的員工工資表

好了我們按照上面的要求，先來分析整理一下我們的已知條件為邏輯公式：

<5萬，工資1500 + 獎金0，

5萬<=銷售額<15萬，工資1500+（銷售額-5萬）*3%（對應獎金率）

15萬<=銷售額<25萬，工資1500+（銷售額-15萬）*5%+3000（上級獎金）

25萬<=銷售額，工資1500+（銷售額-25萬）*8%+（5000+3000）（上級獎金）

既然我們都有了工資的計算公式的，只需將這些轉化為Excel的公式就行了，我們第一個可能會想到的用IF，但條件怎麼寫呢？這裡就有個技巧了，在編寫比較的，需要看你用的什麼比較符號了，用>或>=符號就要範圍數字從大到小寫，反過來用<或<=符號就要從小往大的寫，別弄反了，切記切記，說完這個開始我們的編寫吧，我們這裡選>或>=符號

獎金的公式=if(銷售額>=250000,(銷售額-250000)*0.08+8000,if(銷售額>=150000,(銷售額-150000)*0.05+3000,if(銷售額>=50000,(銷售額-50000)*0.03,0)))

用ROUND函數編寫的公式：=if(round(銷售額，-5)/100000>=3,(銷售額-250000)*0.08+8000,if(round(銷售額，-5)=2,(銷售額-150000)*0.05+3000,if(round(銷售額，-5)/100000=1,(銷售額-50000)*0.03,0))),如果這兩個公式擺在你的面前，你會選哪個呢？

當然第二個了，別看第二個公式長，它的共用性高，哪麼問題來了，什麼叫共用性？共用性就是共同使用相同的結構或內容，其實只要我們仔細觀察，我們就會發現第二個公式的條件裡，基本都是有這個結構round(銷售額，-5)/100000,而這種結構我們就可以通過定義名稱的方式，將-5和100000抽離出來，這樣就給這個公式帶來更強的擴展使用性和可維護性，只要的分階段的方式類似，我們只需修改名稱的值來擴展公式的使用廣度，不過往往有點和缺點並存，它的缺點是什麼呢？

它的缺點就是它本身就是利用的round的函數的特性加以應用的，一旦超出了ROUND函數的特性，它也就沒有這方面的使用價值了，而第一個公式，笨重，可維護性差，擴展困難，但它有著更好的兼容性，這也可能是人們為什麼的部分的採用它的來處理問題的原因吧。說了這麼，還是來看一下最終的結果吧，我就用第二個公式做的：

完成公式填充後的效果

如果像這樣的工作只做一次的，就沒有必要進行優化，而且這兩種方式，你用哪個順手就用哪個就可以了，如果你的這樣的工作重複性比較高，哪我們就需要優化並把抽離成結構化，以後再遇到這樣類似的問題，只需要在這個公式的基礎稍微調整數據就能完成新的工作，這樣我們就能大大的提高效率，好了說了這麼，我們一起來做一下這個表的結構化，首先我們需要將條件的部分拆分成兩部分，對應條件和結果抽離成一張表，我們給它起個名字叫Base：A列內容為等級，存儲1，2，3，b列：50000，1500000，250000，c列存儲是3%，5%，8%，d列內容為：0，3000，8000；至於加不加標題都可以，加標題是為了你以後再用的時候知道每列的內容是什麼！

Base表格數據內容

至於常用數，-5或100000，你可以自定義存儲也可以存儲在條件表的一個固定位置也可以，我定義名稱AN為-5和RN：100000，基礎數據創建完成後，再來編寫公式，這次我們不用if語句，而使用lookup代替if,獎金的公式變為=IFNA(([@銷售額]-LOOKUP(ROUND([@銷售額],RN)/AN,Base!A:A,Base!C:C)*LOOKUP(ROUND([@銷售額],RN)/AN,Base!A:A,Base!D:D)+LOOKUP(ROUND([@銷售額],RN)/AN,Base!A:A,Base!E:E)),0),只要我們遇到等級按0-5，5-15，15-25這樣類似的規則，我們只需調整AN和RN的數值，只需修改B列和右側的內容即可。

用lookup代替if的方法當然也可以優化第一個公式，不過的是需要調整Base表中所有的內容，數據少無所謂，一旦多起來即使少修改一項的內容，也會大大的提高效果的。通常我們在工作中，即使能優化一兩件事情也要去做，因為這種優化積累到一定程度，就會有質的提升了，好了今天的文章就寫到這了，也希望你從中對ROUND函數有一點新的認識和用法的上的啟發。如果想詳細了解LOOKUP函數代替if函數的用法在Excel基礎知識-解密開發小項目的全過程中有詳細介紹！最後還是老口號：學習工作的路上你並不孤單，我們一路同行！

相關焦點

  • excel日期函數:不同日期函數的返回值解析
    在前不久的文章中，我們給大家分享了在excel中錄入日期的格式，不知道大家還記得嗎？不記得的小夥伴可以看看教程《在excel裡，80%的職場人錄入的日期都是錯的！》複習一下。上回我們說到了日期的格式，這回就該說日期的計算了。
  • EXCEL之FLOOR函數運用
    錯誤值；3) 如果數字剛好是進位基準的倍數，就不再進位，否則最小可進位到零；4) =FLOOR() 函數與 =CEILING() 函數的區別為：前者趨向於零進位；後者趨向於無窮大進位。示例 1 ：下表用於計算一業務小組各成員的提成。提成的付給是以￥1000為單位的，也就是每￥1000 付一筆提成。
  • Excel Round函數的使用方法,含批量給公式添加Round保留小數實例
    二、Round函數的使用方法及實例(一）保留兩位小數的實例1、選中 B1 單元格，輸入公式 =round(a1,2)，按回車，返回 5.68；把滑鼠移到 A1 右下角的單元格填充柄上，按住左鍵並往下拖到 B3，則 A2 和 A3 中的數值也實現保留兩位小數；操作過程步驟，如圖1所示：
  • excel中的替代函數——replace和substitute函數的應用實例
    在excel中，常用的替換函數有replace和substitute函數，這兩個函數都可以替換單元格中的部分內容，功能和ctrl+H的功能類似，但是使用函數的目的一方面不會破壞原數據，另一方面與其他函數結合可以實現更多功能，對於substitute的參數=substitute（單元格，被替換的字符串，新字符串，指定替換第幾個），第四個參數可以省略，表示全部替換。
  • Excel的ROUND函數
    我們上篇將到了Excel的INT函數，接下來將Excel的ROUND函數。ROUND函數：將數字四捨五入到指定的位數用法：ROUND（數字，位數）例題：=ROUND(9.91，1)，得到的結果值是9.9。=ROUND(-9.915，2)，得到的結果值是-9.92。
  • excel中的rept函數——這個函數太有意思了!
    在excel中，rept函數表示將文本重複一定的次數，此函數的參數為rept（重複顯示的文本，重複的次數）。比如下圖中在B2單元格中輸入函數=REPT(A2,4)，那麼就會在B2單元格中把A2單元格中的內容重複4次顯示，這個函數僅僅就有這麼一種用法嗎？
  • EXCEL函數公式大全之利用TODAY函數NETWORKDAYS函數製作倒計時
    EXCEL函數公式大全之利用TODAY函數NETWORKDAYS函數製作工作日期倒計時。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數TODAY函數和NETWORKDAYS函數的組合。
  • 輕鬆應對數字分項取捨問題,rounddown函數和roundup函數的使用
    我們在實際工作中，有時候我們需要處理帶有分項的數據，比如涉及金額的數據，就很容易會出現分項的數據，當我們對這些具有分項的數據進行計算的時候，我們為了方便計算，我們通常會將這些數據進行統一格式的分項去留，比如採用四捨五入的方式保留一位分項或許2位分項，這時候我們就可以利用函數來輕鬆解決
  • excel函數公式if怎麼用
    今天花點心思寫一下excel中if函數的用法，可能在數據小的時候大家用不到這些函數，但是如果表格數據量非常大的時候，那麼excel中if函數的節省時間的效果出出來了。在使用if函數之前，我先把數據準備好，以一個班級的語文成績為例，依照不及格<60，60<良<80,優秀>80來劃分成績等級。當然了三個判定條件大家一開始理解不了，可以先從及格不及格簡單的判定開始了解if函數的用法。那麼在C2中判定及格與不及格的if函數寫法是=IF(B2>=60,"及格","不及格")。
  • SEMer必備:常見的 Excel 函數全部在這裡了!
    Excel 有很多強大的函數，這篇文章主要介紹各種函數的用途。實戰會後續文章講解。函數可以被我們想像成一個盒子，專門負責將輸入轉換成輸出，不同的函數對應不同的輸出。=Vlookup( lookup_value ,table_array,col_index_num,[range_lookup] )上文的 Vlookup 就是一個經典函數。
  • excel函數技巧:如何用三角函數製作環形氣泡圖
    相信即便是一些「認為自己函數玩的不錯」的同學，看到上圖中的函數也是「懵」的。存在即合理，既然微軟有這些函數，就一定是多年使用EXCEL的客戶，對於使用體驗反饋中所涉及的，例如財務函數，在財務工作中大部分的計算都是「加減乘除」，可是對於真正的財務分析師來說，單純的四則運算是遠遠不夠的。
  • Excel中find函數、left函數的使用方法的介紹
    Excel中find函數的功能就是返回一個字符串在另外的一個字符串中開始出現的位置（注意：區分大小寫的） Find函數的各參數具體含義： =FIND(find_text,within_text,start_num) 第一個參數
  • Excel取捨函數ROUND
    在Excel中，ROUND是最常用的取捨函數，其按照四捨五入取捨，即判斷需保留小數位數的右邊1位，小於等於4則捨去，大於等於5則進位，其使用方法如下。ROUND函數的格式=ROUND（數值，小數位數）「數值」可以為單元格或數字；「小數位數」即需保留的小數位數。為正數時，對小數點右側四捨五入；為負數時，對小數點左側四捨五入。實例如下表中，需要對A2至A5單元格的數值進行取捨計算，結果如下。
  • Excel常用數學函數匯總
    一、sum/count/average 這三個函數應該是最最常用的啦，sum是求和、count是計數、average是求平均值，來結合下面的例子看一下它們如何使用。
  • Excel小白講函數-不同類型的函數同解一題,你這樣玩過嗎?
    其實你要說名字不重要的，具體要看怎麼解決這種問題的方法？沒錯，不過解決的方法實在太多了，我今天就簡單枚舉兩類，可能有16種組合解法，好了廢話不多數說，上正文：文本/數字+lookup大法思路：首先需要創建的使用lookup的輔助表，表的結構為如下圖：
  • excel替換函數教程:substitute函數和replace函數的用法及案例
    在excel進行文本替換，除了用查找替換功能，也可以用函數來實現。這節課，我們就來講下substitute函數和replace函數的用法及案例。首先，來看一下案例圖表：上半部分表格，需要的是將A列單元格裡的B列單元格內容替換掉，也就是刪除掉，這種情況用的是substitute函數。C4單元格公式=SUBSTITUTE(A4,"、"&B4,"")公式由3部分組成：目標單元格，需要替換的內容，替換後的內容。A4是目標單元格，即「李大嘴、邢育森、燕小六、祝無雙」。
  • excel電子表格int取整函數介紹
    小於0的數，就原來的數小1一起來看下面的例子：例一：要求：用int函數取整直接輸入公式=INT(A2)結果如下：解釋：正如上面結果運算中，當數值大於0是，只要整數部分，不要小數；當數值小於0時，就去整數部分再減1，與平時我們接觸的四捨五入取整不一樣。
  • excel if函數的詳細教程(基本用法和嵌套用法)
    本篇將介紹excel if函數的詳細教程，有興趣的朋友可以了解一下！excel是我們工作中很常用的表格製作工具，它不僅僅只是用來製作表格，還能幫助我們處理數據（比如：運算、篩選、排序等等）。今天小編要介紹的是excel if函數的詳細用法，希望對大家有所幫助！
  • 快速刪除excel單元格中的空格,tirm函數與clean函數的使用技巧
    我們在實際工作中，當我們使用excel表格處理數據的時候，我們有時候會發現excel單元格中會存在空格，我們都清楚，有些人的名字是3個字的，有些人的名字是2個字的，有的人為了美觀，通常會在2個字的名字中間增加一個空格，這樣看上去就比較協調，但是單元格中存在空格，我們在後續處理數據的操作就會出現問題
  • excel如何按條件求和?全都在這裡:從sum函數到sumifs函數
    對於excel裡面的數字，常用的統計就是求和及計數。如果你沒有學會這些函數，那麼就無法應對大量數據的表格。現在，我們就來用函數公式實現excel自動化辦公。一、掌握「sum函數」的用法sum函數是將單元格中的數字相加，用法為：=sum（單元格\單元格區域，……）不引用單元格的話，就直接填寫內容。可以添加N個單元格\單元格區域。