秒學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)

[:]

0 0 vote
Article Rating
訂閱(Subscribe)
來自於(Notify of)
0 Comments
Inline Feedbacks
View all comments