3163 |
Excel如何用公式篩選新進員工 |
蔡明明:錦子老師您好,請問若我的員工資料在工作表1,想要在工作表2顯示每月新進員工,是否能利用函數組合,在B11儲存格僅輸入年月(2020年10月),即可自動帶出本月新進員工資料及各部門新進員工人數?謝謝。
工作表1
工作表2
錦子老師:解決這個問題,作法如下。
步驟1:點取工作表2的B3儲存格輸入公式:
=IFERROR(OFFSET(工作表1!$A$1,SUMPRODUCT(LARGE((TEXT(工作表1!$G$3:$G$28, "YYYY/MM")="2020/10")*ROW(工作表1!$G$3:$G$28),ROW()-2))-1,COLUMN()-2),"")
再按CTRL+SHIFT+ENTER鍵完成陣列公式輸入,將公式複製到B3:B1儲存格。
【公式說明】
=IFERROR(OFFSET(工作表1!$A$1,SUMPRODUCT(LARGE((TEXT(工作表1!$G$3:$G$28, "YYYY/MM")="2020/10")*ROW(工作表1!$G$3:$G$28),ROW()-2))-1,COLUMN()-2),"")
公式1:TEXT(工作表1!$G$3:$G$28, "YYYY/MM")
將G3:G28儲存格內容變成西元年份/月文字格式。
公式2:LARGE((公式1="2020/10")*ROW(工作表1!$G$3:$G$28),ROW()-2)
將公式1運算結果等於2020/10字串乘上對應的列號,取第N(目前儲存格列號減1)大的值。
公式3: SUMPRODUCT(公式2)
將公式2運算結果加總。
公式4:OFFSET(工作表1!$A$1,公式3-1,COLUMN()-2)
從工作表1的A1儲存格移動N(公式3運算結果減1)列N(目前儲存格欄編號減2)欄。
公式5:IFERROR(公式4,"")
如果公式4傳回錯誤訊息,表示找不到符合的內容,則不填入資料。
步驟2:點取工作表2的L3儲存格輸入公式:
=COUNTIF($I$3:$I$8,K3)
再將公式複製到L4:L5儲存格。
以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~
更多相關影片教學:請點我
更多相關文章:請點我
留言列表