[:zh]
設定格式化的條件原理
- 選取要格式化的儲存格
- 使用公式來決定要格式化哪些儲存格
- 格式化在此公式為True的值: 填入
- =ROW(A1)=COLUMN(A1)
- 此處A1只是舉例,倘若有位移,請自行增減運算,注意row()和column()的關聯是否符合需求即可。
進階版(未說明者請自行練習)
調整row()和column()的等式可進一步畫出下列底色:
- 對角線右上半和左下半設不同底色
- 右上半:=ROW(A1)<COLUMN(A1)
- 左下半:=ROW(A1)>COLUMN(A1)
- 每個對角線依序上彩虹色,由對角線往外擴散。
- 對角線左下1:=ROW(A1)=COLUMN(A1)+1
- 對角線左下2:=ROW(A1)=COLUMN(A1)+2
- …(略)…
- 對角線左下N:=ROW(A1)=COLUMN(A1)+N
- 對角線右上1~N:類似左上(略)。
- 奇數列與偶數列設上不同底色
- 畫田字線。
- 田字線的四個區塊都設不同底色
ps. 若要搭配樞紐分析表,則注意補齊原始資料空值,否則若欄或列有空值,則跳行會導致看起來對,實際上卻是不正確的。
超進階版
目的:擷取欄與列的標題文字進行數字比對,並且按照兩者之差決定彩虹色。
步驟說明(以下欄、列位址僅供參考):
- 欄與列的標題文字完全相等,且所對應的值大於0時
- =AND($A8=B$5, B8>0)。
- 不完全相等時,需替換$A8=B$5,說明如後。
- 若欄與列的標題文字不完全相等,舉例而言,若欄與列的標題文字分別都顯示為「0時」、「1時」、「2時」、…、「23時」,需要去掉「時」之後,再比對數字。
- 擷取方法:MID($A8, 1, LEN($A8)-LEN(“時”)。
- 由於擷取之後為文字,需轉換為數字才可進行運算
- 將擷取結果以value()轉換為數值。
- 負值問題:因為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)
- IF(
- 彩虹色的完整語法如下(僅列出其中一條,餘類推)
- =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)
- =AND(VALUE(MID($A8,1,LEN($A8)-1))=(
[:en]
設定格式化的條件原理
- 選取要格式化的儲存格
- 使用公式來決定要格式化哪些儲存格
- 格式化在此公式為True的值: 填入
- =ROW(A1)=COLUMN(A1)
- 此處A1只是舉例,倘若有位移,請自行增減運算,注意row()和column()的關聯是否符合需求即可。
進階版(未說明者請自行練習)
調整row()和column()的等式可進一步畫出下列底色:
- 對角線右上半和左下半設不同底色
- 右上半:=ROW(A1)<COLUMN(A1)
- 左下半:=ROW(A1)>COLUMN(A1)
- 每個對角線依序上彩虹色,由對角線往外擴散。
- 對角線左下1:=ROW(A1)=COLUMN(A1)+1
- 對角線左下2:=ROW(A1)=COLUMN(A1)+2
- …(略)…
- 對角線左下N:=ROW(A1)=COLUMN(A1)+N
- 對角線右上1~N:類似左上(略)。
- 奇數列與偶數列設上不同底色
- 畫田字線。
- 田字線的四個區塊都設不同底色
ps. 若要搭配樞紐分析表,則注意補齊原始資料空值,否則若欄或列有空值,則跳行會導致看起來對,實際上卻是不正確的。
超進階版
目的:擷取欄與列的標題文字進行數字比對,並且按照兩者之差決定彩虹色。
步驟說明(以下欄、列位址僅供參考):
- 欄與列的標題文字完全相等,且所對應的值大於0時
- =AND($A8=B$5, B8>0)。
- 不完全相等時,需替換$A8=B$5,說明如後。
- 若欄與列的標題文字不完全相等,舉例而言,若欄與列的標題文字分別都顯示為「0時」、「1時」、「2時」、…、「23時」,需要去掉「時」之後,再比對數字。
- 擷取方法:MID($A8, 1, LEN($A8)-LEN(“時”)。
- 由於擷取之後為文字,需轉換為數字才可進行運算
- 將擷取結果以value()轉換為數值。
- 負值問題:因為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)
- IF(
- 彩虹色的完整語法如下(僅列出其中一條,餘類推)
- =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)
- =AND(VALUE(MID($A8,1,LEN($A8)-1))=(
[:]