小伙伴們好啊，今天和大家一起學習Excel函數(shù)公式中的一個常用技巧。先來看下面這個表格，要計算從一月份開始，到當前月份的累計銷量：

微軟Office LTSC 2021專業(yè)增強版 簡體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大小：2.2GB
• 語言：簡體中文
• 時間：2024-09-12

查看詳情

C2單元格輸入以下公式，向下拖動復制：

=SUM($B$2:B2)

這就是一個典型的引用區(qū)域自動擴展的用法，

$B$2:B2部分，第一個B2使用了絕對引用，第二個B2使用了相對引用，在公式下拉時會依次變成$B$2:B3、$B$2:B4、$B$2:B5……這樣逐步擴大的求和范圍。最后得到的結果，就是從B2單元格開始，到公式所在行的B列這個范圍之和。

這種自動擴展的引用區(qū)域技巧，在日常公式中經(jīng)常會用到，接下來咱們就列舉幾個有代表性的應用。

1、判斷數(shù)據(jù)是否重復出現(xiàn)

如下圖，要統(tǒng)計B列的姓名是否為重復出現(xiàn)。

C2使用的公式為：

=IF(COUNTIF($B$2:B2,B2)>1,"重復","")

COUNTIF函數(shù)使用動態(tài)擴展的區(qū)域$B$2:B2作為統(tǒng)計范圍，計算B列員工姓名在這個區(qū)域中出現(xiàn)的次數(shù)，如果出現(xiàn)的次數(shù)大于1，就是重復。

以B2為例，令狐沖首次出現(xiàn)，C2單元格公式中的COUNTIF計算結果為1，表示該姓名在$B$2:B2這個區(qū)域中沒有重復出現(xiàn)：

=COUNTIF($B$2:B2,B2)

而到了C8單元格，COUNTIF公式的引用區(qū)域變化為$B$2:B8：

=COUNTIF($B$2:B8,B8)

在$B$2:B8這個區(qū)域中，令狐沖出現(xiàn)了兩次，也就是說B8是重復出現(xiàn)的。

2、按部門添加序號

如下圖，要根據(jù)B列的部門填寫序號，每個部門都要從1開始排序。

A2單元格公式為：

=B2&-COUNTIF($B$2:B2,B2)

這個公式中，COUNTIF函數(shù)以$B$2:B2作為動態(tài)擴展的統(tǒng)計區(qū)域，計算B列的部門出現(xiàn)的次數(shù)。

如果該部門是首次出現(xiàn)，結果就是1，如果是第二次出現(xiàn)，結果就是2……

最終的統(tǒng)計結果，就可以看做是部門的序號。

3、不允許錄入重復數(shù)據(jù)

如果把COUNTIF函數(shù)的這種用法與數(shù)據(jù)驗證功能相結合，就可以實現(xiàn)拒絕錄入重復數(shù)據(jù)。如果要輸入大量的員工姓名，這種方法特別實用。

數(shù)據(jù)驗證中的公式為：

=COUNTIF($D$2:D2,D2)=1

實際使用的時候，公式中的D2需要換成實際選中數(shù)據(jù)區(qū)域的首個單元格，比如你選中的區(qū)域是A2:A20，公式就寫成：

=COUNTIF($A$2:A2,A2)=1

4、必須連續(xù)輸入，不允許有空單元格

使用數(shù)據(jù)驗證功能，還可以限制必須連續(xù)輸入。如果輸入的不完整或是輸入后又刪除了記錄，Excel就不允許在下面繼續(xù)輸入了：

數(shù)據(jù)驗證的公式為

=COUNTBLANK($D$2:D2)=0

COUNTBLANK用于統(tǒng)計數(shù)據(jù)范圍中空單元格的個數(shù)。這里約束的條件就是空單元格數(shù)量為0。

同樣，使用的時候要注意把公式中的D2換成你所選區(qū)域的活動單元格地址。

推薦閱讀：EXCEL中的公式$有什么作用? 用$符號玩轉相對/絕對/混合引用

最新評論