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

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

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語言：簡(jiǎn)體中文
• 時(shí)間：2024-09-12

查看詳情

C2單元格輸入以下公式，向下拖動(dòng)復(fù)制：

=SUM($B$2:B2)

這就是一個(gè)典型的引用區(qū)域自動(dòng)擴(kuò)展的用法，

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

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

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

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

C2使用的公式為：

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

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

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

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

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

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

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

2、按部門添加序號(hào)

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

A2單元格公式為：

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

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

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

最終的統(tǒng)計(jì)結(jié)果，就可以看做是部門的序號(hào)。

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

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

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

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

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

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

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

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

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

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

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

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

推薦閱讀：EXCEL中的公式$有什么作用? 用$符號(hào)玩轉(zhuǎn)相對(duì)/絕對(duì)/混合引用

最新評(píng)論