第十章 使用COUNTIF 格式化条件来寻找重复项目.docVIP

適用於 Microsoft Office Excel 2003 本文摘錄自 MrE。如需更多秘訣和詳細資訊，請造訪MrE 網站 (英文)。 您可以使用格式化條件和 COUNTIF 函數找出某個資料範圍中的重複項目。以下是詳細說明。 設定第一個格式化條件公式 我們會先為第一個資料儲存格設定一個格式化條件，然後再將此格式化條件複製至整個範圍中。 在本範例中，儲存格 A1 包含一個欄標題 (「發票」)，因此我們會先選取儲存格 A2，再按一下?[格式]?功能表中的?[設定格式化的條件]。這時會出現 [設定格式化的條件] 對話方塊。第一個方塊中顯示的文字是?[儲存格的值]。只要按一下此方塊旁邊的箭頭，即可選擇?[公式為]。 選取?[公式為]?之後，對話方塊的外觀會發生變更。原本的?[介於?x?and?y]?方塊會消失，並為單一公式方塊所取代。此公式方塊十分有用。您可以將任何公式輸入其中，只要該公式能夠算出 TRUE 或 FALSE 即可。 在此範例中，我們必須使用?COUNTIF?公式。請在此方塊中輸入以下公式： =COUNTIF(A:A,A2)1 此公式所代表的意義是：檢視欄 A 的整個範圍。計算該範圍中有多少儲存格的值與儲存格 A2 的值相同。接著，比對計算結果是否大於 1。 沒有重複的項目存在時，計算結果將一律是 1；因為儲存格 A2 也在該範圍之內，所以我們只能在欄 A 中找到一個包含的值與 A2 相同的儲存格。 附註?在此公式中，A2 代表目前的儲存格 — 也就是您正在為其設定格式化條件的儲存格。因此，如果您的資料位於欄 E 中，而您正在儲存格 E5 中設定第一個格式化條件，則該公式便會是=COUNTIF(E:E,E5)1。 選取用來標示重複項目的顏色 現在我們可以選取一種醒目的格式，用來識別我們所找到的任何重複項目。在?[設定格式化的條件]對話方塊中，按一下?[格式]?按鈕。 按一下?[圖樣]?索引標籤，再選取一種鮮艷的色樣，例如紅色或黃色。接著，請按一下?[確定]，將[儲存格格式]?對話方塊關閉。 這時您可以透過預覽方塊檢視您所選取的格式。請按一下?[確定]，將?[設定格式化的條件]?對話方塊關閉，然後… 什麼也沒發生。如果這是您第一次設定格式化條件，那麼能夠在此獲得一些結果，應該是再理想不過的了。然而，除非儲存格 A2 中的資料剛好與其他儲存格中的資料發生重複，否則條件將會是 FALSE，而不會套用任何格式設定。 將格式化條件複製到其他儲存格中 您必須將儲存格 C2 中的格式化條件向下複製到範圍內的其他儲存格中。趁游標仍在 A2 中時，按一下?[編輯]?功能表中的?[複製]。按 CTRL+空格鍵將整欄選取起來。接著，請按一下?[編輯]?功能表中的?[選擇性貼上]。在?[選擇性貼上]?對話方塊中，按一下?[格式]，然後按一下?[確定]。 這時您已將格式化條件複製到該欄的所有儲存格中了。現在，您終於可以看到部份儲存格出現了填滿顏色的格式設定，代表有重複的項目存在。 為了獲得充分資訊，我們將會前往儲存格 A3，並查看其條件公式 (在您將它從 A2 複製過來之後)。請將儲存格 A3 選取起來，然後按一下?[格式]?功能表中的?[設定格式化的條件]。這時?[格式為]?方塊中的公式已經變更為計算儲存格 A3 中的資料在欄 A 中出現了多少次。 您最多可以為 65536 個儲存格設定格式化條件，每個儲存格均能針對目前的儲存格與其他 65535 個儲存格進行比較。技術上而言，第一個步驟中的公式可以是?=COUNTIF($A$2:$A$1751,A2)1。 此外，在將格式化條件複製到整欄中時，您也可以在使用?[選擇性貼上]?指令之前，僅選取包含資料的儲存格。 僅標示某個重複項目的第二個例項 先前的解決方案假設您想要為兩個重複的發票編號一併加上醒目提示，以決定該以手動方式刪除或修正哪一個項目。如果您不想要為第一個出現的重複項目加上標示，請將公式修正為： =COUNTIF($A$2:$A2,A2)1 附註?請務必依照上面的公式輸入貨幣符號。 在此公式的第一個引數中，當您將公式往下複製到其他欄時，只有資料範圍內的第二個儲存格參照會發生變更。這意謂著當此公式在尋找重複的項目時，僅會針對目前儲存格上方的儲存格一直到資料範圍中的第一個儲存格進行比對。 排序資料 事實上，您無法以格式化條件為基礎，針對某欄進行排序。如果您想要針對資料進行排序，讓重複項目能夠集中在單一區域中，請依照下列步驟進行： 首先，請在儲存格 B1 中輸入標題：「重複項目」。接著，請在 B2 中輸入以下公式： =COUNTIF(A:A,A2)1 趁游標仍在儲存格 B2 中時，按兩下「自動填滿」控點 (位於儲存格右下角的小正方形)，在該欄中將此公式一路向下複製。 現在，您