【原】【實(shí)用公式解析】統(tǒng)計(jì)入職年份

ExcelEasy 2022-05-24 發(fā)布于北京

展開全文

分類：計(jì)數(shù)

文末有視頻，詳細(xì)解析本文公式。

問題

假設(shè)有數(shù)據(jù)如下圖：

左表中記錄了每個(gè)人的入職日期，希望在右表中統(tǒng)計(jì)每個(gè)年份的入職人數(shù)。

分析

最簡單的當(dāng)然是數(shù)據(jù)透視表：

對這類問題來說，數(shù)據(jù)透視表方便快捷，是首選方案。

但是數(shù)據(jù)透視表在某些場景中不是很合適，比如，在制作Dashboard時(shí)，沒有辦法讓透視表適應(yīng)Dashboard的風(fēng)格和格式要求，很多時(shí)候必須放過來，需要讓Dashboard根據(jù)透視表做格式的的改變。

又比如，如果我們這個(gè)計(jì)算是中間結(jié)果，那么實(shí)用數(shù)據(jù)透視表就相當(dāng)于將自動(dòng)化過程拆分成了兩個(gè)過程，中間必須手動(dòng)處理。

這時(shí)候，需要使用公式解決。

要求是計(jì)數(shù)，首選是使用COUNTIFS。

但是COUNTIFS函數(shù)只能將入職時(shí)間的整列區(qū)域作比較，不能比較入職時(shí)間的年份部分，盡管我們可以將其中的年份拆分出來。

但是我們可以使用輔助列。

輔助列方案

我們可以添加一個(gè)輔助列，

其中入職年份使用公式：

=YEAR(C5)

然后使用公式：

=COUNTIFS(D5:D24,F5:F19)

可以得到結(jié)果：

這個(gè)方案可以完美解決問題，但是輔助列“入職年份”，實(shí)際上是個(gè)冗余信息。

如果表格比較大，這樣的信息會造成空間的浪費(fèi)，同時(shí)多了一列公式，從性能上來說也不經(jīng)濟(jì)。

SUMPRODUCT方案

我們可以使用SUPRODUCT函數(shù)，這樣就可以在不添加輔助列的情況下進(jìn)行年份統(tǒng)計(jì)。公式如下：

=SUMPRODUCT(--(YEAR($C$5:$C$24)=E5))

在這里，我們使用YEAR函數(shù)將日期列的年份取出，并分別與當(dāng)前的年份進(jìn)行比較，其中的：

--(YEAR($C$5:$C$24)=E5)

部分，實(shí)際上是一個(gè)數(shù)組：

{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;1}

你可以自己推導(dǎo)一下這個(gè)數(shù)組的產(chǎn)生過程。

將整個(gè)SUMPRODUCT公式雙擊填充到結(jié)果區(qū)域，即完成需求：

這個(gè)公式有一個(gè)缺陷，如果我們的數(shù)據(jù)發(fā)生了變化，增加了新人，或者有人的入職年份做了調(diào)整，結(jié)果區(qū)域的年這一列需要手動(dòng)調(diào)整，另外人數(shù)列的公式也需要重新填充到新的年份。因此，這個(gè)SUMPRODUCT方案是個(gè)半自動(dòng)的，不完善的方案。

LAMBDA方案

利用新的LAMBDA函數(shù)，可以制作出完全自動(dòng)的方案，不僅僅根據(jù)年份自動(dòng)統(tǒng)計(jì)人數(shù)，還可以根據(jù)源數(shù)據(jù)自動(dòng)獲得年份并排序。

公式如下：

=LET(    dates, C5:C24,    all_year, YEAR(dates),    u_year, SORT(UNIQUE(all_year)),    rows, COUNT(u_year),    MAKEARRAY(        rows,2,        LAMBDA(r,c,IF(c=1,INDEX(u_year,r),SUMPRODUCT(--(all_year=INDEX(u_year,r)))))    ))

LET函數(shù)的作用是提高可讀性，首先定義了四個(gè)參數(shù)：

dates，數(shù)據(jù)區(qū)域的入職日期列
all_year，入職日期列中所有日期的年份，通過對dates參數(shù)使用YEAR函數(shù)獲得
u_year，all_year中所有的不重復(fù)列表，并且是升序排序過的。對all_year參數(shù)使用UNIQUE函數(shù)獲得不重復(fù)列表，然后再使用SORT函數(shù)進(jìn)行排序
rows，u_year的個(gè)數(shù)，代表了結(jié)果區(qū)域的行數(shù)

LET函數(shù)的最后一個(gè)參數(shù)是MAKEARRAY函數(shù)，這個(gè)函數(shù)返回一個(gè)行數(shù)為rows，列數(shù)為2的數(shù)組。數(shù)組的值用IF函數(shù)定義：