秒學EXCEL將對角線的儲存格上色

[:zh]

設定格式化的條件原理

1. 選取要格式化的儲存格
2. 使用公式來決定要格式化哪些儲存格
3. 格式化在此公式為True的值: 填入
   • =ROW(A1)=COLUMN(A1)
   • 此處A1只是舉例，倘若有位移，請自行增減運算，注意row()和column()的關聯是否符合需求即可。

進階版(未說明者請自行練習)

調整row()和column()的等式可進一步畫出下列底色：

1. 對角線右上半和左下半設不同底色
   1. 右上半：=ROW(A1)<COLUMN(A1)
   2. 左下半：=ROW(A1)>COLUMN(A1)
   3. 每個對角線依序上彩虹色，由對角線往外擴散。
      1. 對角線左下1：=ROW(A1)=COLUMN(A1)+1
      2. 對角線左下2：=ROW(A1)=COLUMN(A1)+2
      3. …(略)…
      4. 對角線左下N：=ROW(A1)=COLUMN(A1)+N
      5. 對角線右上1~N：類似左上(略)。
2. 奇數列與偶數列設上不同底色
3. 畫田字線。
   • 田字線的四個區塊都設不同底色

ps. 若要搭配樞紐分析表，則注意補齊原始資料空值，否則若欄或列有空值，則跳行會導致看起來對，實際上卻是不正確的。

超進階版

目的：擷取欄與列的標題文字進行數字比對，並且按照兩者之差決定彩虹色。

步驟說明(以下欄、列位址僅供參考)：

1. 欄與列的標題文字完全相等，且所對應的值大於0時
   • =AND($A8=B$5, B8>0)。
   • 不完全相等時，需替換$A8=B$5，說明如後。
2. 若欄與列的標題文字不完全相等，舉例而言，若欄與列的標題文字分別都顯示為「0時」、「1時」、「2時」、…、「23時」，需要去掉「時」之後，再比對數字。
   • 擷取方法：MID($A8, 1, LEN($A8)-LEN(“時”)。
3. 由於擷取之後為文字，需轉換為數字才可進行運算
   • 將擷取結果以value()轉換為數值。
4. 負值問題：因為0-1 = -1；0-2=-2、1-2=-1；…(略)。但我要0-1=23；0-2=22、1-2=23；…(略)，因此只要判斷當<0時，就將結果再加上24，如：
   • IF(
     VALUE( MID(B$5,1,LEN(B$5)-1))-1<0,
     VALUE( MID(B$5,1,LEN(B$5)-1))-1+24,
     VALUE(MID(B$5,1,LEN(B$5)-1))-1)
   • 而0-2=-2的情況也類似（其餘自行類推）：
     IF(
     VALUE( MID(B$5,1,LEN(B$5)-1))-2<0,
     VALUE( MID(B$5,1,LEN(B$5)-1))-2+24,
     VALUE(MID(B$5,1,LEN(B$5)-1))-2)
5. 彩虹色的完整語法如下（僅列出其中一條，餘類推）
   • =AND(VALUE(MID($A8,1,LEN($A8)-1))=(
     IF(
     VALUE( MID(B$5,1,LEN(B$5)-1))-1<0,
     VALUE( MID(B$5,1,LEN(B$5)-1))-1+24,
     VALUE(MID(B$5,1,LEN(B$5)-1))-2)
     ),B8>0)

[:en]

設定格式化的條件原理

1. 選取要格式化的儲存格
2. 使用公式來決定要格式化哪些儲存格
3. 格式化在此公式為True的值: 填入
   • =ROW(A1)=COLUMN(A1)
   • 此處A1只是舉例，倘若有位移，請自行增減運算，注意row()和column()的關聯是否符合需求即可。

進階版(未說明者請自行練習)

調整row()和column()的等式可進一步畫出下列底色：

1. 對角線右上半和左下半設不同底色
   1. 右上半：=ROW(A1)<COLUMN(A1)
   2. 左下半：=ROW(A1)>COLUMN(A1)
   3. 每個對角線依序上彩虹色，由對角線往外擴散。
      1. 對角線左下1：=ROW(A1)=COLUMN(A1)+1
      2. 對角線左下2：=ROW(A1)=COLUMN(A1)+2
      3. …(略)…
      4. 對角線左下N：=ROW(A1)=COLUMN(A1)+N
      5. 對角線右上1~N：類似左上(略)。
2. 奇數列與偶數列設上不同底色
3. 畫田字線。
   1. 田字線的四個區塊都設不同底色

ps. 若要搭配樞紐分析表，則注意補齊原始資料空值，否則若欄或列有空值，則跳行會導致看起來對，實際上卻是不正確的。

超進階版

目的：擷取欄與列的標題文字進行數字比對，並且按照兩者之差決定彩虹色。

步驟說明(以下欄、列位址僅供參考)：

1. 欄與列的標題文字完全相等，且所對應的值大於0時
   • =AND($A8=B$5, B8>0)。
   • 不完全相等時，需替換$A8=B$5，說明如後。
2. 若欄與列的標題文字不完全相等，舉例而言，若欄與列的標題文字分別都顯示為「0時」、「1時」、「2時」、…、「23時」，需要去掉「時」之後，再比對數字。
   • 擷取方法：MID($A8, 1, LEN($A8)-LEN(“時”)。
3. 由於擷取之後為文字，需轉換為數字才可進行運算
   • 將擷取結果以value()轉換為數值。
4. 負值問題：因為0-1 = -1；0-2=-2、1-2=-1；…(略)。但我要0-1=23；0-2=22、1-2=23；…(略)，因此只要判斷當<0時，就將結果再加上24，如：
   • IF(
     VALUE( MID(B$5,1,LEN(B$5)-1))-1<0,
     VALUE( MID(B$5,1,LEN(B$5)-1))-1+24,
     VALUE(MID(B$5,1,LEN(B$5)-1))-1)
   • 而0-2=-2的情況也類似（其餘自行類推）：
     IF(
     VALUE( MID(B$5,1,LEN(B$5)-1))-2<0,
     VALUE( MID(B$5,1,LEN(B$5)-1))-2+24,
     VALUE(MID(B$5,1,LEN(B$5)-1))-2)
5. 彩虹色的完整語法如下（僅列出其中一條，餘類推）
   • =AND(VALUE(MID($A8,1,LEN($A8)-1))=(
     IF(
     VALUE( MID(B$5,1,LEN(B$5)-1))-1<0,
     VALUE( MID(B$5,1,LEN(B$5)-1))-1+24,
     VALUE(MID(B$5,1,LEN(B$5)-1))-2)
     ),B8>0)

[:]