|
每逢月末，各位財務(wù)“表哥”是否為結(jié)賬工作而努力加班？往來賬、庫存賬、成本賬......這賬那賬的，忙得不可開交。 作為一名財務(wù)人員，我們無法改變這種月末工作量疊加的狀況。但我們可以善用Excel的函數(shù)功能來提高工作效率，從而少加班甚至不加班。結(jié)合日常財務(wù)工作，在這里給大家分享一些常用的Excel函數(shù)，希望可以在你的財務(wù)工作中派上用場。 一、求和函數(shù) 1.數(shù)據(jù)求和---SUM SUM是Excel的常用函數(shù)之一，它可以對一組或多組數(shù)據(jù)進行求和。 函數(shù)語法：SUM(數(shù)據(jù)區(qū)域1,數(shù)據(jù)區(qū)域2......) 如下表所示，對納稅總額求和。 2.單條件求和---SUMIF SUMIF函數(shù)，用于對符合單個條件的數(shù)據(jù)進行求和。 函數(shù)語法：SUMIF(條件區(qū)域,條件,求和區(qū)域) 如下表所示，對稅種是“印花稅”的納稅額進行匯總。 3.多條件求和---SUMIFS SUMIFS函數(shù)，用于對符合多個條件的數(shù)據(jù)進行求和。 函數(shù)語法：SUMIFS(求和區(qū)域,條件區(qū)域1,條件1,條件區(qū)域2,條件2......) 如下表所示，對所屬分公司為“B分公司”，并且稅種為“印花稅”的納稅額求和。 二、求和之王---SUMPRODUCT SUMPRODUCT函數(shù)對指定的幾組數(shù)組之間對應(yīng)的元素相乘，然后再對每個乘積進行求和。 函數(shù)語法：SUMPRODUCT(數(shù)組1,數(shù)組2...…) 如下表所示，先對每個商品計算銷售金額，最后對所有商品的銷售額進行匯總。 函數(shù)的經(jīng)典用法：SUMPRODUCT((條件1)*(條件2)*(條件N)*(數(shù)據(jù)區(qū)域)) 如下表所示，求B部的顯示器銷售總額。 三、統(tǒng)計函數(shù) 1.單條件統(tǒng)計---COUNTIF COUNTIF函數(shù)，用于統(tǒng)計區(qū)域中滿足單個條件的數(shù)量。 函數(shù)語法：COUNTIF(統(tǒng)計區(qū)域,條件) 如下表所示，統(tǒng)計“內(nèi)存”商品的入庫次數(shù)。 2.多條件統(tǒng)計---COUNTIFS COUNTIFS函數(shù)，用于統(tǒng)計區(qū)域中滿足多個條件的數(shù)量。 函數(shù)語法：COUNTIFS(統(tǒng)計區(qū)域1,條件1,統(tǒng)計區(qū)域2,條件2......) 如下表所示，統(tǒng)計商品為“內(nèi)存”，驗收員是“張三”的驗收次數(shù)。 四、判斷是非的IF IF是邏輯函數(shù)，如果滿足某個條件時，返回指定的結(jié)果；否則返回另一個結(jié)果。 函數(shù)語法：IF(判斷條件,條件成立的結(jié)果,條件不成立的結(jié)果) 如下表所示，根據(jù)A列數(shù)據(jù)判斷，如果當(dāng)前崗位是“財務(wù)”，那么就需要加班，否則不加班。 如果判斷是否多個條件同時成立，需結(jié)合AND函數(shù)。 函數(shù)語法：AND(條件1,條件2......) 如下表所示，如果當(dāng)前崗位是“財務(wù)”，并且處于“月末”時段，則需要加班，否則不加班。 如果判斷其中某一條件成立的，結(jié)合OR函數(shù)。 函數(shù)語法：OR(條件1,條件2......) 如下表所示，如果當(dāng)前崗位是“財務(wù)”或者“統(tǒng)計”，則需要加班，否則不加班。 五、查找函數(shù) 1.“大眾情人”---VLOOKUP 日常工作中經(jīng)常用到VLOOKUP函數(shù)進行縱向查找，它被看作是表哥表妹們的“大眾情人”。 函數(shù)語法：VLOOKUP(要找誰,在哪找,返回第幾列的內(nèi)容,精確查找/近似查找) 如下表所示，根據(jù)單元格A9的姓名查找對應(yīng)的崗位。
函數(shù)中的第4個參數(shù)，通常都是使用“0”進行精確查找，使用“1”則是近似查找。 2.查找之王---LOOKUP 除了VLOOKUP之外，日常工作中還會經(jīng)常使用到的一個查找函數(shù)LOOKUP，它可以實現(xiàn)雙向查找。 經(jīng)典函數(shù)語法：LOOKUP(1,0/((條件區(qū)域1=條件1)*(條件區(qū)域2=條件2)),查詢區(qū)域) 如下表所示，使用LOOKUP函數(shù)查找部門是“財務(wù)”，并且崗位是“稅務(wù)助理”的人員姓名。
六、保留指定小數(shù)位的函數(shù) 1.指定小數(shù)位并四舍五入---ROUND 函數(shù)語法：ROUND(數(shù)值,保留的小數(shù)位) 如下表所示，對納稅金額四舍五入保留1位小數(shù)。
2.不進位并舍去指定小數(shù)位后的小數(shù)---ROUNDDOWN 不管指定的小數(shù)位后面的數(shù)字是否大于5，直接舍去并保留指定的小數(shù)位數(shù)。 函數(shù)語法：ROUNDDOWN(數(shù)值,保留的小數(shù)位) 如下表所示，對金額保留1位小數(shù)，并不進位直接舍去第2位及后面的所有小數(shù)。
3.進位并舍去指定小數(shù)位后的小數(shù)---ROUNDUP 不管指定的小數(shù)位后面的數(shù)字是否大于5，直接進位并保留指定的小數(shù)位數(shù)。 函數(shù)語法：ROUNDUP(數(shù)值,保留的小數(shù)位) 如下表所示，對納稅金額進位并保留1位小數(shù)，舍去第2位及后面的所有小數(shù)。
七、返回區(qū)域指定位置的值---INDEX INDEX函數(shù)語法：INDEX(區(qū)域,第幾行,第幾列) 如下表所示，返回表格第3行與第2列交叉單元格的值，即單元格B3。
八、返回指定值在區(qū)域中的位置---MATCH 函數(shù)語法：MATCH(查找的值,查找區(qū)域,查找模式) 第三個參數(shù)查找模式可以為-1、0、1，通常使用0進行精確查找。 如下表所示，查找代碼“1122”所在區(qū)域A1:A6的位置。
九、雙劍合璧---INDEX+MATCH 無論從左到右，還是從右到左的查找，INDEX+MATCH的組合都可輕松搞定。如下表所示，查找科目名稱為“預(yù)付賬款”所對應(yīng)的科目代碼。
十、容錯函數(shù)---IFERROR IFERROR，當(dāng)在公式的計算結(jié)果出現(xiàn)錯誤時，返回指定的值；否則返回公式的結(jié)果。 函數(shù)語法：IFERROR(檢查的值,錯誤時指定的內(nèi)容) 如下表所示，使用IFERROR函數(shù)將錯誤值轉(zhuǎn)為空。
十一、字符提取函數(shù) 1.從左提取字符---LEFT 函數(shù)語法：LEFT(被提取的字符串,從左提取的字符個數(shù)) 2.從右提取字符---RIGHT 函數(shù)語法：RIGHT(被提取的字符串,從右提取的字符個數(shù)) 3.從左邊指定位置提取字符---MID 函數(shù)語法：MID(被提取的字符串,從左起第幾位開始提取,提取幾個字符)
十二、字符串長度計算函數(shù) 1.計算字符串的字符個數(shù)---LEN 函數(shù)語法：LEN(字符串) 2.計算字符串的字節(jié)個數(shù)---LENB 函數(shù)語法：LENB(字符串) LEN用于計算字符串的字符數(shù)，漢字、數(shù)字、字母、標(biāo)點符號，都按1個字符計算；LENB用于計算字符串的字節(jié)數(shù)，漢字、中文狀態(tài)下的標(biāo)點符號，每個字符按2個字節(jié)計算，其他字符按1個字節(jié)計算。
十三、字符提取與字符串長度函數(shù)組合運用 如下表所示，使用LEFT、RIGHT、LEN、LENB函數(shù)組合分別提取字符串的科目名稱與科目代碼。
上述公式中，使用LENB函數(shù)首先取得A2單元格的字節(jié)數(shù)為12；而LEN函數(shù)取得A2單元格的字符數(shù)為8。這里的“科目名稱”全是漢字，每個漢字的字節(jié)數(shù)都比字符數(shù)多出1，所以字節(jié)總數(shù)比字符總數(shù)多出來的就是漢字的個數(shù)，最后用LEFT提取字符串的左邊幾位。
由于LENB函數(shù)取得的字節(jié)數(shù)會比LEN函數(shù)取得的字符數(shù)多出漢字的個數(shù)，據(jù)此，LEN取值后乘以2減去字節(jié)數(shù)，兩者之差正好是字符串右邊數(shù)字的個數(shù)，最后使用RIGHT函數(shù)提取字符串的右邊幾位。 十四、指定范圍的隨機函數(shù)---RANDBETWEEN 函數(shù)語法：RANDBETWEEN(數(shù)值下限,數(shù)值上限) RANDBETWEEN函數(shù)能夠產(chǎn)生一個在指定范圍內(nèi)的隨機數(shù)值，如下表所示，隨機產(chǎn)生一個在20至50之間的數(shù)值。
十五、日期函數(shù) 1.DATEDIF函數(shù)，用于計算兩個日期之間的間隔。 函數(shù)語法：DATEDIF(開始日期,結(jié)束日期,返回的類型) DATEDIF是一個隱藏函數(shù)，輸入時屏幕上沒有任何的提示。它的第三個參數(shù)返回類型可以是Y、M、D，其中，Y表示年，M表示月，D表示日。如下表所示，計算客戶的欠款月數(shù)，使用'M'參數(shù)。
2.EDATE函數(shù)，用于返回在某一日期經(jīng)過指定月數(shù)后的日期。 函數(shù)語法：EDATE(日期,月數(shù)) 第二個參數(shù)可以是正數(shù)，代表未來的日期；也可以是負(fù)數(shù)，代表過去的日期。如下表所示，合同有效期為3個月，計算合同的到期日。
十六、平均值函數(shù) 1.用于計算一組數(shù)據(jù)的平均值---AVERAGE 函數(shù)語法：AVERAGE(求平均值的區(qū)域)
2.用于對符合單條件的數(shù)據(jù)求平均值---AVERAGEIF 函數(shù)語法：AVERAGEIF(條件區(qū)域,條件,平均值區(qū)域) 如下表所示，對部門為“財務(wù)”的人員工資計算平均值。
3.用于對符合多條件的數(shù)據(jù)求平均值---AVERAGEIFS 函數(shù)語法：AVERAGEIFS(平均值區(qū)域,條件區(qū)域1,條件1,條件區(qū)域2,條件2......) 如下表所示，對部門為“財務(wù)”，并且性別為“男”的人員計算平均工資。
十七、替換函數(shù) 1.REPLACE 函數(shù)語法：REPLACE(要替換的字符串,開始位置,替換個數(shù),新的內(nèi)容) 如下表所示，把身份證號碼的第5至第14個數(shù)字替換為星號。 2.SUBSTITUTE 函數(shù)語法：SUBSTITUTE(包含舊內(nèi)容的字符串，舊內(nèi)容，新內(nèi)容，第幾個舊內(nèi)容)
如下表所示，把身份證號的后四位數(shù)字替換為星號。 REPLACE與SUBSTITUTE的區(qū)別在于，前者是從字符串指定位置上開始，并且指定替換的長度，即一共要替換幾位；后者是對字符串的指定舊內(nèi)容替換為新內(nèi)容，并指定替換第幾位舊內(nèi)容。如果不指定，則把所有的舊內(nèi)容都替換。 十八、字符位置查找函數(shù)---FIND FIND可以對查找的字符內(nèi)容進行定位，以確定其位置。 函數(shù)語法：FIND(要查找的字符,包含查找字符的單元格,從第幾個位置開始查找)
如下表所示，把科目代碼提出來。根據(jù)會計科目都帶有“-”符號的特性，使用FIND函數(shù)把“-”的位置找出來，它的前1位到第一位之間的所有數(shù)字就是科目代碼，再結(jié)合LEFT函數(shù)提取即可。
十九、去除空格的函數(shù)---TRIM TRIM函數(shù)，可以去除字符串兩端的空格。如果字符串中間有連續(xù)兩個以上的空格，只保留一個必要的空格 函數(shù)語法：TRIM(字符串) 如下表所示，由于A9單元格的字符前有空格，造成VLOOKUP出錯，結(jié)合TRIM去除字符空格后問題即可解決。
二十、簡易個稅計算 如果你為每月的工資表個稅計算使用復(fù)雜的公式而感到煩惱，那快來試試下面的簡易個稅計算公式吧。 如果你覺得文章對你有幫助，請轉(zhuǎn)發(fā)分享給你的朋友。 |
|
|
