close

3163

Excel如何用公式篩選新進員工

蔡明明:錦子老師您好,請問若我的員工資料在工作表1,想要在工作表2顯示每月新進員工,是否能利用函數組合,在B11儲存格僅輸入年月(202010),即可自動帶出本月新進員工資料及各部門新進員工人數?謝謝。

image 工作表1

image 工作表2

錦子老師:解決這個問題,作法如下。

步驟1:點取工作表2B3儲存格輸入公式:

=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儲存格。

image

【公式說明】

=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),"")

公式1TEXT(工作表1!$G$3:$G$28, "YYYY/MM")

G3:G28儲存格內容變成西元年份/月文字格式。

image

公式2LARGE((公式1="2020/10")*ROW(工作表1!$G$3:$G$28),ROW()-2)

公式1運算結果等於2020/10字串乘上對應的列號,取第N(目前儲存格列號減1)大的值。

image

公式3 SUMPRODUCT(公式2)

公式2運算結果加總。

image

公式4OFFSET(工作表1!$A$1,公式3-1,COLUMN()-2)

從工作表1A1儲存格移動N(公式3運算結果減1)N(目前儲存格欄編號減2)欄。

image

公式5IFERROR(公式4,"")

如果公式4傳回錯誤訊息,表示找不到符合的內容,則不填入資料。

步驟2:點取工作表2L3儲存格輸入公式:

=COUNTIF($I$3:$I$8,K3)

再將公式複製到L4:L5儲存格。

image

以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~

更多相關影片教學:請點我

更多相關文章:請點我


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()