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