函數公式可以說是Excel的靈魂,所以對於一些基礎實用性的函數公式我們必須掌握哦!
一、Len、Lenb:返回文本字符串中的字符數個數或字符數。
目的:返回指定字符串的字數和字節數。
方法:
在目標單元格中輸入公式:=LEN(A1)、=LENB(A1)。
解讀:
1、Len函數的作用是:統計指定字符串中的字符數,暨每個字符均為1,一個英文字母、一個數字為1個字符,一個漢字也是1個字符。18個漢字+5個字母+3個數字+2個符號=28個字數。
2、Lenb函數的作用是:統計指定字符串中的字節數,英文字母為1,而一個漢字為2個字節,要注意區別對待哦!18個漢字(18*2)+5個字母(5*1)+3個數字(3*1)+2個符號(2+1)=47個字節。
二、Match:返回指定值在相對範圍中的位置。
目的:定位出“銷售員”的相對位置。
方法:
在目標單元格中輸入公式:=MATCH(H3,B3:B9,0)。
解讀:
Match函數是常見的定位函數,語法結構為:=Match(定位的值,定位範圍,[匹配模式])。其中匹配模式有:
-1:查找大於或等於查找值的最小值,查找範圍必須按降序排列。
0:精準匹配。省略第三個參數是,默認為精準匹配模式。
1:查找小於或等於查找值的最大值,查找範圍必須按升序排列。
其中最常用的匹配模式為精準匹配。
三、Mod:求餘數。
目的:利用身份證號碼提取性別。
方法:
在目標單元格中輸入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。
解讀:
1、Mod函數的作用是求餘,語法結構為:=Mod(被除數,除數)。
2、利用Mid函數提取身份證號中的第17位,利用Mod函數求餘,如果為奇數返回“男”,如果為偶數,返回“女”。
四、Year、Month函數:提取日期中的年份、月份。
目的:提取出生日期中的月份。
方法:
在目標單元格中輸入公式:=YEAR(D3)、=MONTH(D3)。
解讀:
其實除了Year、Month提取日期中的年、月之外,用Day函數還可以提取日哦。語法結構及用法相同哦!
五、Now函數:提取系統當前的日期和時間。
目的:提取系統當前的日期和時間。
方法:
在目標單元格中輸入公式:=NOW()。
解讀:
Now函數的作用是提取系統的日期和時間,如果要單純的獲取日期、時間,則可以是實用快捷鍵Ctrl+;和Ctrl+Shift+;。
六、Rank、Sumproduct函數:排序。
目的:對“銷量”進行排序。
方法:在目標單元格中輸入公式:=RANK(D3,D$3:D$9,0)或=SUMPRODUCT((D$3:D$9>D3)/COUNTIF(D$3:D$9,D$3:D$9))+1。
解讀:
1、當排序的值重複時,用Rank函數排序則會出現“跳躍”的情況,如示例中的兩個第4名之後直接為第6名,為了避免“跳躍”情況,可以實用Sumproduct函數來完成排名,此排名方式稱為:中國式排名。
2、Rank函數語法結構:=Rank(排序值,排序範圍,排序方式)。排序方式有:0或1。0為降序,1為升序。
3、Sumproduct函數的作用為:返回相應的數組或區域的乘積的和。其排序方式為改良用法哦!
七、Weekday、Text:返回日期對應的星期。
目的:返回指定日期對應的星期。
方法:
在目標單元格中輸入公式:=WEEKDAY(F3,2)、=TEXT(F3,"aaaa")、=TEXT(F3,"aaa")。
解讀:
1、Weekday函數的轉置結果為對應的數字星期,代碼“aaaa”對應的為長星期,“aaa”對應的為短星期。
2、語法結構:=Weekday(日期或引用,代碼),常用代碼為2,暨1代表星期一,7代表星期日。
3、Text函數的主要作用是:根據指定的代碼將對應的值轉換為文本形式。其中“aaaa”代表長星期。“aaa”代表短星期。
八、Iferror、Iserror:如果有錯誤返回指定的值或進行標識。
目的:判斷“完成率”情況。
方法:
在目標單元格中輸入公式:=IFERROR(E3/D3,"")、=ISERROR(E3/D3)。
解讀:
1、Iferror函數的作用是:當表達式有錯誤時,返回指定的值。語法結構為:=Iferror(表達式,返回值)。
2、Iserror函數的作用是:當表達式有錯誤時,返回True,否則返回False。語法結構為:=Iserror(表達式)。
九、Subtotal:返回一組列表或數據庫的分類彙總情況。
語法:=Subtotal(功能代碼,數據列表會數據庫)。
一句話解讀:
在指定的區域中按照功能代碼的要求進行分類統計。
功能代碼:
代碼分類1-11和101-111兩大類,但是對應的函數名稱相同,難倒是功能相同,又有什麼區別了?
1-11:包含手動隱藏的行、值。
101-111:不包含手動隱藏的行、值。暨以可見單元格為統計對象。
目的:統計銷量的平均值。
方法:
在目標單元格中輸入公式:=SUBTOTAL(101,D3:D9)。
解讀:
1、代碼101代表計算平均值。
2、代碼101-111統計時不包含隱藏的值,暨只對可見單元格可見。所以在篩選“男”銷售員的銷量平均值時自動重新計算。
十、Value:將一個文本型的數值轉換為數值類型。
目的:對文本型的銷量進行轉換並參與計算。
方法:
在目標單元格中輸入公式:=VALUE(D3)。
解讀:
1、從示例中可以看出“銷量”為文本型。無法參與求和。
2、當使用公式=VALUE(D3)將其轉換為數值類型之後,可以做求和運算。
結束語:
文本從實際出發,對工作中經常用到的函數公式進行了羅列,共10組,對其使用技巧,你Get到了嗎?如果有不懂、不明白的地方,歡迎在留言區留言討論哦,如果親覺著使用,別忘了“點轉評”哦,有親的支持,小編會進一步努力的哦!
閱讀更多 Excel函數公式 的文章