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