首頁 > 運動
15個Excel函式公式案例解讀,便捷高效,辦公必備
由 Excel函式公式 發表于 運動2021-06-21
簡介功能:Text函式功能為根據指定的格式將數值轉換為文字,Mid函式的功能為:從字串中指定的起始位置返回指定長度的字元
向下取整是什麼意思
在資料的處理和分析中,函式或公式是使用率比較高的工具之一,但Excel的函式非常的繁多,想要全部掌握,幾乎是不可能的,所以,我們必須掌握常用的函式,公式!
一、對Excel工作表中的數值向下取整
函式:
Int。
功能:
將數值向下取整為最接近的整數。
語法結構:
=Int(值或單元格引用)。
目的:
捨去“月薪”小數點後的值。
方法:
在目標單元格中輸入公式;=INT(G3)。
解讀:
Int函式的作用為向下取整,即不進行四捨五入,直接省去小數點後面的值。
二、對Excel工作表中的數值向上取整
函式:
Roundup。
功能:
向上舍入數字。
語法結構:
=Roundup(值或單元格引用,小數位數)。
目的:
對“月薪”向上取整。
方法:
在目標單元格中輸入公式:=ROUNDUP(G3,0)。
解讀:
Roundup函式的作用為“向上舍入”數字,即如果保留小數後還有值,一律“進一”。
三、對Excel工作表中的數值“四捨五入”
函式:
Round。
功能:
按指定的位數對數值“四捨五入”。
語法結構:=Round(值或單元格引用,小數位數)。
目的:
對“月薪”四捨五入後,保留一位小數。
方法:
在目標單元格中輸入公式:=ROUND(G3,1)。
解讀:
Round函式對數字進行四捨五入,按照指定的小數位數保留值。
四、在Excel工作表中隨機生成0-1之間的隨機數
函式:
Rand。
功能:
返回大於或等於0小於1的平均分佈隨機數。
語法結構:
=Rand()。
目的:
在“備註”列隨機生成0-1之間的隨機數。
方法:
在目標單元格中輸入公式:=RAND()。
解讀:
如果對生成的隨機值不滿意,可以按F9(或Fn+F9)進行重新整理。
五、在Excel工作表中隨機生成指定範圍內的隨機值
函式:
Randbetween。
功能:
返回一個介於指定值之間的隨機值。
語法結構:
=Randbetween(開始值,結束值)。
目的:
在“備註”列生成100-500之間的隨機值。
方法:
在目標單元格中輸入公式:=RANDBETWEEN(100,500)。
解讀:
如果對生成的隨機值不滿意,可以按F9(或Fn+F9)進行重新整理。
六、從Excel工作表中的身份證號碼中提取出生年月。
函式:
Text+Mid。
功能:
Text函式功能為根據指定的格式將數值轉換為文字,Mid函式的功能為:從字串中指定的起始位置返回指定長度的字元。
語法結構:
=Text(數值或單元格引用,格式程式碼);=Mid(值或單元格引用,起始位置,字元長度)。
目的:
從身份證號碼中提取出生年月的8位數值並轉換為日期形式。
方法:
在目標單元格中輸入公式:=TEXT(MID(C3,7,8),“0-00-00”)。
解讀:
1、身份證號碼中從第7位開始,長度為8的字元為出生年月。
2、首先利用Mid函式提取出生年月8位數字,然後用Text函式將其轉換為日期格式。
七、從Excel工作表中的身份證號碼中提取性別。
函式:
If+Mod+Mid。
功能:
Mod函式的功能為:返回兩個數相除的餘數。
語法結構:
=Mod(被除數,除數)。
目的:
從身份證號碼中判斷性別。
方法:
在目標單元格中輸入公式:=IF(MOD(MID(C3,17,1),2),“男”,“女”)。
解讀:
1、身份證號碼中的第17位數值代表性別,奇數為“男”,偶數為“女”。
2、首先利用Mid函式從身份證號碼中提取第17位,並作為Mod函式的被除數,當除數為2時,其返回的值只有0或1兩種,然後用If函式進行判斷,如果值為1,則返回“男”,否則返回“女”。
八、
從Excel工作表中的身份證號碼中計算年齡。
函式:
Datedif。
功能:
以指定的方式統計兩個日期之間的差值。
語法結構:
=Datedif(開始日期,結束日期,統計方式)。常用的統計方式有三種,“Y”、“M”、“D”,即“年”、“月”、“日”。
目的:
根據身份證號碼計算對應的年齡。
方法:
在目標單元格中輸入公式:=DATEDIF(TEXT(MID(C3,7,8),“0-00-00”),TODAY(),“y”)。
解讀:
1、Datedif函式為系統隱藏函式,在官方的函式庫中時無法找到對應的解讀部分,在輸入函式名稱時,沒有聯想提示部分。且只能在16及以上版本中才能使用,WPS中也可以使用。
2、公式中首先利用Text+Mid從身份證號碼中提取出生年月,然後和今天(Today())對比,計算兩個日期之間的相差的年份(Y)。
3、此公式具有通用性,無論何時開啟工作表,其年齡是自動計算的最新值,如果引數“結束日期”Today()替換成“2020-11-08”,其年齡不會自動變化,不會增長。
九、將Excel工作表中的數值視覺化
函式:
Rept。
功能:
根據指定次數,重複文字。
語法結構:
=Rept(文字,重複次數)。
目的:
將“月薪”以圖表的形式顯示。
方法:
在目標單元格中輸入公式:=REPT(“|”,G3/500)。
解讀:
除以500是因為縮小G3單元格的值,否則在較小的空間中無法正常顯示,在實際的應用中,需要靈活處理。
十、將Excel工作表中字元的首字元轉換為大寫
函式:
Proper。
功能:
將一個文字字串中各英文單詞的首字母轉換為大寫,其他字母轉換為小寫。
語法結構:
=Proper(字串或單元格引用)。
目的:
將“拼音”中的第一個字母大寫。
方法:
在目標單元格中輸入公式:=PROPER(C3)。
十一、將Excel工作表中的字元全部大寫
函式:
Upper。
功能:
將文字字串轉換成字母全部大寫形式。
語法結構:
=Upper(字串或單元格引用)。
目的:
將“拼音”全部大寫。
方法:
在目標單元格中輸入公式:=UPPER(C3)。
十二、將Excel工作表中的字元全部小寫
函式:
Lower。
功能:
將一個字串中的所有字母轉換為小寫形式。
語法結構:
=Lower(字串或單元格引用)。
目的:
將“拼音”全部小寫。
方法:
在目標單元格中輸入公式:=LOWER(C3)。
十三、將Excel工作表中的日期轉換為星期。
函式:
Text。
功能:
根據指定的格式將數值轉換為文字。
語法結構:
=Text(值或單元格引用,格式程式碼)。
目的:
將“出生日期”中的值轉換為對應的星期。
方法:
在目標單元格中輸入公式:=TEXT(C3,“aaaa”)。
解讀:
程式碼“aaaa”代表長星期,即星期X。
十四、計算出Excel工作表中的日期對應的周次。
函式:
Weeknum。
功能:
返回一年中的周次。
語法結構:
=Weeknum(日期或單元格引用,計算方式)。
目的:
返回“出生日期”對應的周次。
方法:
在目標單元格中輸入公式:=WEEKNUM(C3,2)。
解讀:
“計算方式”為2時,代表一週從星期一開始,星期日結束。
十五、將Excel工作表中的數值取整並大寫。
函式:
Numberstring。
功能:
按照指定的格式對數字四捨五入取整並大寫。
語法結構:
=Numberstring(數字或單元格引用,格式程式碼)。
目的:
對“月薪”大寫。
方法:
在目標單元格中輸入公式:=NUMBERSTRING(G3,2)。
解讀:
1、Numberstring函式為系統隱藏函式,且只能在16及以上版本中應用,在WPS中同樣可以使用。
2、格式程式碼“2”的作用為:將數值按照會計格式進行大寫。