2017年4月2日 星期日

Excel-多個儲存格為一組來顯示特定格式(OFFSET,COUNTIF,設定格式化的條件)

在 Excel 中有一個如下圖含有數組4X3的儲存格區域,如何判定每一個區域若是有超過6個數字時,則以藍色填滿該區域中的儲存格。該如何處理?
例如:儲存格F2:I4中的12個儲存格,其中有8個儲存格含有數字,超過6個數字,則應以藍色填滿該區中的所有儲存格。
Excel-多個儲存格為一組來顯示特定格式(OFFSET,COUNTIF,設定格式化的條件)
以下先以二個儲存格為一組來試試如何處理。
Excel-多個儲存格為一組來顯示特定格式(OFFSET,COUNTIF,設定格式化的條件)
1. 選取儲存格B2,設定格式化的條件。
選取規則類型:使用公式來決定要格式化哪些儲存格。
規則:=(B2<>"")*(C2<>"")
格式:填滿藍色儲存格
Excel-多個儲存格為一組來顯示特定格式(OFFSET,COUNTIF,設定格式化的條件)
2. 選取儲存格C2,則重覆步驟 1。
3. 複製儲存格B2:C2的格式,則至儲存格B2:O12。
特別注意:儲存格B2和儲存格C2要分別做,再複製格式,貼至其他儲存格。
但是,如果是下圖?
每個區域都有12個儲存格,則一個區域必須設定12次,才能將該區域的格式複製到其他23個區域中。有沒有更好的方式呢?
Excel-多個儲存格為一組來顯示特定格式(OFFSET,COUNTIF,設定格式化的條件)
方法是有,但是相對是較長的公式:
1. 選取儲存格B2:E4,設定格式化的條件。
選取規則類型:使用公式來決定要格式化哪些儲存格。
規則:=COUNTIF(OFFSET($B$2,INT((ROW(1:1)-1)/3)*3,INT((COLUMN(A:A)-1)/
4)*4,3,4),"")<6 p="">
格式:填滿藍色儲存格
2. 複製儲存格B2:E4的格式,貼至儲存格B2:Q19。
其中『INT((ROW(1:1)-1)/3)*3』和『INT((COLUMN(A:A)-1)/4)*4』公式,在套用到每一個儲存格時,會產生以下圖中的數列清單。
image
你可以發現每 12 個儲存格會產生相同的結果,即每個區域中的 12 個儲存格的結果是相同的。而公式中的參數『3』和『4』,對應每個區或的列數和欄數。
將來,若是擴大了每個區域的儲存格數量,則只要修改對應的列數和欄數即可。
再利用 COUNTIF 函數計算每個區域中的空白儲存格數是否小於 6(假設非空白填入的是數字)。
因此,每個區域中的12個儲存格藉此可以設定相同的格式化條件。
所以,你不再需要重覆12次為相同區域中的儲存格設定相同的格式化條件。

沒有留言:

張貼留言

檢視其他文章

好康東東