在 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

格式:填滿藍色儲存格

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次為相同區域中的儲存格設定相同的格式化條件。

學不完.教不停.用不盡文章列表

arrow
arrow
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(0) 人氣()