??函數(shù)組合的應(yīng)用往往把復(fù)雜的問題簡單化，起到1+1>2的效果，已經(jīng)介紹幾種常用的組合很少，供大家參考，希望能幫助到大家！
組合一：IF+AND/OR+LEFT/MID/RIGHT
當(dāng)我們需要批量計(jì)算某項(xiàng)多重條件限制的補(bǔ)貼發(fā)放金額的時候，比如：工齡5年以上或出勤率95%以上者補(bǔ)貼50元；出勤率90%以上同時工作表現(xiàn)“優(yōu)秀”者另加100元補(bǔ)貼”
例：按下表?xiàng)l件設(shè)置，根據(jù)多重條件要求測算“補(bǔ)貼”應(yīng)發(fā)數(shù)額：
員工 工齡 出勤率 工作表現(xiàn) 補(bǔ)貼條件：
工齡5年以上或出勤率95%以上者補(bǔ)貼50元；
出勤率90%以上同時工作表現(xiàn)“優(yōu)秀”者可另加100元補(bǔ)貼。
為解決上面的需求,最為簡單的辦法,可以IF、AND、OR、LEFT四個函數(shù)綜合運(yùn)用
組合二：VLOOKUP+MATCH
如下圖，根據(jù)需要自由查詢特定數(shù)據(jù)
在H2單元格中輸入：=VLOOKUP(G2,$A$1:$E$15,MATCH(H1,A1:E1,0),0)
知識點(diǎn)：
match函數(shù)的作用是返回在指定方式下與指定數(shù)組匹配的數(shù)組中元素的相應(yīng)位置，是一個輔助函數(shù)，返回的位置可以提供給其它函數(shù)作為引用。
它有三個參數(shù)，參數(shù)1是查找值，可以是數(shù)值、文本或者邏輯值，或者對上述類型的引用；參數(shù)2是查找區(qū)域，含有要查找值的連續(xù)單元格、一個數(shù)組、或者是數(shù)組的引用；參數(shù)3是數(shù)字-1、0或者1，指明以何種方式查找。
如果是-1，函數(shù) match查找大于或等于參數(shù)1 的最小數(shù)值，如果是0，函數(shù) match查找等于 參數(shù)1 的第一個數(shù)值。如果是,1，函數(shù) match查找小于或等于 參數(shù)1的最大數(shù)值。
組合三：INDEX+MATCH
用MATCH函數(shù)來定位查詢值的位置，再用INDEX函數(shù)返回指定區(qū)域中指定位置的內(nèi)容，二者結(jié)合，可以實(shí)現(xiàn)上下左右全方位的查詢。
應(yīng)用實(shí)例：
如下圖所示，根據(jù)姓名查詢部門和職務(wù)，也就是傳說中的逆向查詢。
知識點(diǎn)：
INDEX:查找所選區(qū)域制定行指定列的值
如下圖：=INDEX(A1:C6,4,2)='女”
組合四：MIN+IF
用于計(jì)算指定條件的最小值。
如下圖所示，要計(jì)算指定部門最低薪資
H2單元格可以使用數(shù)組公式：
=MIN(IF(D:D=G2,E:E))
先用IF函數(shù)判斷D列的部門是否等于G2指定的部門，如果條件成立，則返回E列對應(yīng)的分?jǐn)?shù)，否則返回邏輯值FALSE：
接下來再使用MIN函數(shù)計(jì)算出其中的最小值。
MIN函數(shù)有一個特性，就是可以自動忽略邏輯值，所以只會對數(shù)值部分計(jì)算，最終得到指定部門的最低分?jǐn)?shù)。
注意，由于執(zhí)行了多項(xiàng)計(jì)算，所以在輸入公式時，要按Shift+ctrl+Enter鍵哦。
組合五：LEN+SUBSTITUTE
如果遇到文本格式的，單元格，根據(jù)內(nèi)容特征如何快速分析單元格中含有幾個項(xiàng)目，如下表格中，通過科目之間用頓號隔開，統(tǒng)計(jì)通過注會的科數(shù)是“頓號”(、)的個數(shù)+1
所以：要統(tǒng)計(jì)的科目數(shù)=單元格總字符數(shù)-將“頓號”替換為空格后字符數(shù)+1
D2單元格公式為：
=LEN(C2)-LEN(SUBSTITUTE(C2,'、',))+1
先用LEN函數(shù)計(jì)算出B列單元格的字符長度，然后再用SUBSTITUTE函數(shù)將頓號全部替換掉之后，計(jì)算替換后的字符長度。
用字符長度減去替換后的字符長度，就是單元格內(nèi)頓號的個數(shù)。
知識點(diǎn)1：
SUBSTITUTE(text，old_text，new_text，[instance_num])
Text是需要替換其中字符的文本，或是含有文本的單元格引用；
Old_text是需要替換的舊文本；
New_text用于替換old_text 的文本；
Instance_num 為一數(shù)值，用來指定以new_text 替換第幾次出現(xiàn)的old_text；如果指定了instance_num，則只有滿足要求的old_text 被替換；如果缺省則將用 new_text 替換 TEXT 中出現(xiàn)的所有 old_text。
知識點(diǎn)2：
LEN：表示返回文本串的字符數(shù)。
組合六：TEXT+MID
常用于日期字符串的提取和轉(zhuǎn)換。
如下圖所示，要根據(jù)B列身份證號碼提取出生年月。
C2單元格公式為：
=TEXT(MID(c2,7,8),'0000-00-00')
MID函數(shù)用于從字符串的指定位置開始，提取特定數(shù)目的字符串。
再使用TEXT函數(shù)，將這個字符串變成'0-00-00'的樣式，結(jié)果為'1975-12-26'。
這個時候，已經(jīng)有了日期的模樣，但是本身還是文本型的，所以再加上兩個負(fù)號，也就是計(jì)算負(fù)數(shù)的負(fù)數(shù)，通過這么一折騰，就變成真正的日期序列了。
最新審計(jì)培訓(xùn)課程預(yù)告
聯(lián)系客服
