Excel如何格式化條件設定組別分類




上篇文章提到如何設定組別分類自動更新,其實只講了一半,同樣的例子,除了簡單的儲存格帶超連結公式,還可以進一步更細緻一點,弄個設定格式化條件,以下分享:

一、先前文章最後的報表,彙總樞紐後,帶連結公式,整理出地區客戶的組別資料。

帶連結公式,整理出地區客戶的組別資料

二、首先,既然有N組的地區客戶別,為了後續操作起見,先編個流水序號。在「A1」儲存格打個「1」,鼠標移到這個儲存格的右下角,可以看到游標從白十字變成黑十字,往下拉,拉完之後會有一個小四方形:「自動填滿選項」,點選「以數列方式填滿」,輕輕鬆鬆建立一串序號編碼。

「自動填滿選項」,點選「以數列方式填滿」

三、接下來,先設定簡單的超連結公式,第一行比較特別,輸入:「=樞紐!B3」,帶入樞紐明細的第一行資料,第二行開始輸入:「=IF(樞紐!B4=””,組別!D1,樞紐!B4)」,而且比照前一步驟提到的黑十字游標往下拉,拉多少就自動複製多少公式。關於這兩個連結公式的奧妙,其實只要在每個儲存格上,想想Excel是怎麼依照公式連結的,應該不難理解。

「=IF(樞紐!B4="",組別!D1,樞紐!B4)」

四、黑十字游標一直往下拉很方便,但我們沒有那麼多組別,拉多了也是白搭,所以需要利用「COUNTA」函數。先輸入此函數,按「fx」跳出函數說明及輸入視窗,點選「value1」右邊的儲存格範圍圖標,選擇「樞紐!B:B」範圍。視窗可以看到關於這個函數的說明:「計算範圍中非空白儲存格的數目」,也可以看到「計算結果=7」。

五、利用函數特性,在「B1」儲存格輸入文字:「項目個數」,在「C1」儲存格輸入公式:「=COUNTA(樞紐!B:B)-2」,如此可清楚得到樞紐明細表裡,究竟有多少個我們要的組別。然後在「B2」儲存格輸入文字:「設定檢查」,在「C2」儲存格輸入公式:「=IF(MAX(D:D)<C1,”須追加公式”,”OK”)」,Excel就會自動檢查所拉的流水序號夠不夠。MAX函數顧名思義,取最大值,所以這公式是如果拉的流水號小於項目個數,便會跳出「須追加公式」,否則顯示「OK」。其實很多報表的勾稽檢查,都可以用相同方式,讓Excel自動幫你複核,以後有機會,來寫篇專章分享。

如果拉的流水號小於項目個數,便會跳出「須追加公式」

六、項目個數「OK」了之後,先選取D、E、F三欄,然後在「常用、「樣式」的功能群組,拉下「設定格式化的條件」,點選「新增規則」。

在「常用、「樣式」的功能群組,拉下「設定格式化的條件」

七、在跳出來的視窗,選擇「使用公式來決定要格式化哪些儲存格」,輸入公式:「ROW()>$C$1」,意思是行數大於C1(項目個數)的儲存儲,要統一設定格式。

八、在上個步驟視窗裡的預覽區塊,點選右邊的「格式」,便會跳出「儲存格格式」的視窗,在「字型」這個頁籤,色彩的部份改為白色。

九、格式化條件設定好了,可以發現,超過第五行以下的儲存格,全部顯示為白字,也就是跟背景一樣顏色,除非特別選取範圍有反灰的影子,不然一般情況和列印出來的,都是看不到的,正所謂眼不見為淨!






當前文章延伸閱讀:
樞紐分析表