所謂的高手并不一定和你有多大的差別，只是他們可能善于思考如何利用Excel公式解決實(shí)際問(wèn)題、提升工作效率。如果你能掌握以下8組公式，那么相對(duì)于你的同事，你基本可以稱作Excel高手了。新函數(shù)，參數(shù)為要提取的數(shù)據(jù)區(qū)域。=TEXTJOIN(',',TRUE,IF($A$4:$A$18=D8,$B$4:$B$18,''))
TEXTJOIN函數(shù)的作用是使用分隔符進(jìn)行數(shù)據(jù)連接，第一參數(shù)：','，表示將逗號(hào)用作分隔符第三參數(shù)：IF($A$4:$A$18=D8,$B$4:$B$18,'')，一個(gè)數(shù)組公式，可以返回班級(jí)對(duì)應(yīng)的所有姓名。=IF(COUNTIF($B$4:B4,B4)=1,'','是')
這個(gè)公式本質(zhì)是利用countif函數(shù)來(lái)判斷的，如果數(shù)據(jù)出現(xiàn)重復(fù)，countif的結(jié)果就會(huì)大于1，最后再利用if函數(shù)輸出結(jié)果即可。 =IFS(B5>=90,'優(yōu)秀',B5>=80,'良好',B5>=60,'及格',B5<60,'不及格')
這類問(wèn)題非常常見(jiàn)，計(jì)算獎(jiǎng)金提成，判斷等級(jí)等等，最簡(jiǎn)單的方法就是利用ifs函數(shù)語(yǔ)法：=IFS(第1個(gè)條件，第1個(gè)條件正確返回的結(jié)果，第2個(gè)條件，第2個(gè)條件正確返回的結(jié)果，……)條件與結(jié)果都是成對(duì)出現(xiàn)的，最多可以設(shè)置127對(duì)。=FILTER(B4:B18,A4:A18=E4,'找不到結(jié)果')
一對(duì)多查詢，可以把它看做是一個(gè)篩選問(wèn)題，而新函數(shù)FILTER就是一個(gè)篩選函數(shù)，可以輕松搞定這樣的問(wèn)題用法：FILTER（要返回結(jié)果的區(qū)域，篩選條件，篩選不到結(jié)果返回的值）'隔行=SUMPRODUCT((MOD(ROW(C4:L9),2)=1)*C4:L9)'隔列=SUMPRODUCT((MOD(COLUMN(B5:G14),2)=1)*B5:G14)
這個(gè)函數(shù)本質(zhì)是一個(gè)SUMPRODUCT函數(shù)的單條件計(jì)數(shù)，庫(kù)存的數(shù)據(jù)都在奇數(shù)行，所以我們用ROW函數(shù)獲取數(shù)據(jù)的行號(hào)，然后再用MOD求得奇數(shù)行。=LOOKUP(1,0/((A4:A12=E4)*(B4:B12=F4)),C4:C12)
多條件查詢，使用LOOKUP函數(shù)是最簡(jiǎn)單的，只需要記得函數(shù)的書(shū)寫(xiě)規(guī)則即可=LOOKUP(1,0/((條件1)*(條件2)),返回的結(jié)果列)，有幾個(gè)條件，就在括號(hào)里寫(xiě)幾個(gè)。=SUMIF(A4:A15,'*車間*',C4:C15)
關(guān)鍵字求和主要是利用了通配符，如關(guān)鍵字【車間】的前后各鏈接一個(gè)*，表示只要包含“車間”2個(gè)字就會(huì)被統(tǒng)計(jì)。關(guān)于Excel的函數(shù)還有很多，如果能做的根據(jù)實(shí)際場(chǎng)景隨手拈來(lái)、任意組合就可以達(dá)到意想不到的效果，只有心中始終有一個(gè)信念：沒(méi)有解決不了的問(wèn)題，也沒(méi)有查不到的函數(shù)，這樣你才會(huì)不遺余力、不被困難所嚇倒！
|
