文:老祝
今天和大家分享一組常用的函數(shù)套路,大家遇到類似問題可以直接拿來(lái)套用即可。
1、常規(guī)查詢
(資料圖片僅供參考)
如下圖所示,要根據(jù)G2單元格姓名,在A~E數(shù)據(jù)區(qū)域中查詢對(duì)應(yīng)的年齡。
經(jīng)典套路:
=VLOOKUP(G2,B1:E6,4,0)
套路指南:
第三參數(shù),指定要返回查詢區(qū)域中第幾列的內(nèi)容,不是整個(gè)工作表的第幾列。
2、逆向查詢
如下圖所示,要根據(jù)G2單元格姓名,在A~E數(shù)據(jù)區(qū)域中查詢對(duì)應(yīng)的工號(hào)。
經(jīng)典套路:
=LOOKUP(1,0/(G2=B2:B6),A2:A6)
套路指南:
=LOOKUP(1,0/(條件區(qū)域=指定條件),要返回的區(qū)域)
可以根據(jù)需要,將公式中的 0/(條件區(qū)域=指定條件),寫成:
0/((條件區(qū)域1=指定條件1)*(條件區(qū)域2=指定條件2)*……)
從而實(shí)現(xiàn)任意角度的多條件查詢。
3、查詢?nèi)f金油
如下圖所示,要根據(jù)H2單元格姓名,查詢所在的部門。
經(jīng)典套路:
=INDEX(B1:F1,MATCH(H2,B2:F2,))
套路指南:
由MATCH函數(shù)找到查詢值的精確位置,然后由INDEX函數(shù)返回指定區(qū)域中,對(duì)應(yīng)位置的內(nèi)容??梢詫?shí)現(xiàn)上下左右任意方向的查詢。
4、年齡計(jì)算
如下圖所示,要根據(jù)C列的出生年月計(jì)算年齡。
經(jīng)典套路:
=DATEDIF(C2,TODAY(),"y")
套路指南:
雖是隱藏函數(shù),卻早已是眾人皆知的秘密。
第一參數(shù)是開始日期,第二參數(shù)是結(jié)束日期,第三參數(shù)是返回的數(shù)據(jù)類型。
使用Y,表示返回整年數(shù)。
使用M,則表示返回整月數(shù)。
5、身份證計(jì)算
如下圖所示,要根據(jù)C列的身份證號(hào)碼計(jì)算出生年月。
經(jīng)典套路:
=--TEXT(MID(C2,7,8),"0-00-00")
套路指南:
先使用MID函數(shù),從C2單元格提取出表示出生年月的8位數(shù)字,再用TEXT函數(shù)將其轉(zhuǎn)換為日期樣式的文本。
最后使用兩個(gè)負(fù)號(hào)進(jìn)行運(yùn)算,變成真正的日期序列值。
6、個(gè)人所得稅計(jì)算
如下圖所示,要根據(jù)E列工資額計(jì)算個(gè)稅。
經(jīng)典套路:
=ROUND(MAX((E2-3500)*0.1*{0.3,1,2,2.5,3,3.5,4.5}-5*{0,21,111,201,551,1101,2701},0),2)
套路指南:
只要把公式中的E2換成實(shí)際的單元格引用即可,其他不用管它。
7、根據(jù)個(gè)稅倒推稅前工資
如下圖所示,要根據(jù)E列個(gè)稅金額計(jì)算稅前工資。
經(jīng)典套路:
=ROUND(MIN(((E2 5*{0,21,111,201,551,1101,2701})/({0.3,1,2,2.5,3,3.5,4.5}/10)) 3500),2)
套路指南:
注意個(gè)稅額不能為0。
工資還不夠納稅起點(diǎn)的,藍(lán)瘦不?
8、中國(guó)式排名
如下圖所示,要對(duì)E列的成績(jī)進(jìn)行中國(guó)式排名,也就是相同成績(jī)不占用名次。
經(jīng)典套路:
=SUMPRODUCT((E$2:E$6>E2)/COUNTIF(E$2:E$6,E$2:E$6)) 1
套路指南:
運(yùn)算過程比較復(fù)雜,三言兩語(yǔ)說不清了。
使用的時(shí)候,只要把公式中的單元格地址換成實(shí)際的數(shù)據(jù)區(qū)域即可。
[責(zé)任編輯:linlin]
個(gè)稅反推稅前工資公式(一組常用函數(shù)套路,Excel達(dá)人都在用)-天天
歌手趙英俊(趙英俊離世三天,緋聞女友袁姍姍發(fā)文悼念,心碎表情
晶鋼門板(晶鋼門板差距在哪里?可別買了劣質(zhì)晶鋼門)-快播
個(gè)性網(wǎng)名大全最新版的(慢熱文靜有深意的微信昵稱個(gè)性氣質(zhì))-環(huán)球
進(jìn)口牛奶品牌(大喝三天,十二款新疆奶真實(shí)測(cè)評(píng))
涇川(涇川:致富路上“?!睘榘?
金華趕集網(wǎng)(雙峰:合演“售賣二手摩托車”騙局 夫妻雙雙獲刑)-
金溪教育網(wǎng)(金溪縣學(xué)前教育聯(lián)盟骨干教師下鄉(xiāng)聽課評(píng)課(圖))-焦
好聽的個(gè)性網(wǎng)名大全(個(gè)性男生網(wǎng)名大全好聽?zhēng)洑?可愛點(diǎn))-環(huán)球要聞
今年鬼節(jié)是幾號(hào)(我國(guó)傳統(tǒng)節(jié)日七月半鬼節(jié)的由來(lái))
紅燒魚塊怎么做最好吃(#福氣年夜菜#爸爸的味道之紅燒魚塊)-環(huán)球
潔面摩絲(這些洗面奶深層清潔讓肌膚感覺清爽、柔嫩、潤(rùn)澤,如絲