E0213_1.png

【L2儲存格】
式一、$B$4:$J$11<0

  • 判斷B4:J11是否小於0,是回傳TRUE,否回傳FALSE
  • 故式一為{0,0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0,1;0,0,0,0,0,0,0,0,0;
         0,0,1,0,0,0,0,0,0;0,0,0,0,0,0,0,0,1;0,0,0,0,0,0,0,0,1}
    ☆ 為了簡化版面,1代表TRUE,0代表FALSE


式二、COLUMN($B$3:$J$3)

  • 顯示B3:J3欄數
  • 故式二為{2,3,4,5,6,7,8,9,10}


式三、IF(式一,式二,"")

  • 若式一為1,顯示式二,反之顯示空值
  • 式一:{0,0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0,0;0,0,0,0,0,0,0,0,1;0,0,0,0,0,0,0,0,0;
        0,0,1,0,0,0,0,0,0;0,0,0,0,0,0,0,0,1;0,0,0,0,0,0,0,0,1}
  • 式二:{2,3,4,5,6,7,8,9,10}
  • 故式三為{"","","","","","","","","";"","","","","","","","","";"","","","","","","","",10;
         "","","","","","","","","";"","",4,"","","","","","";"","","","","","","","",10;
         "","","","","","","","",10}


式四、SMALL(式三,ROW(A1))-1

  • ROW(A1):顯示A1之列數,故值為1
  • 取式三第1小之值,再減1
  • 式三:{"","","","","","","","","";"","","","","","","","","";"","","","","","","","",10;
        "","","","","","","","","";"","",4,"","","","","","";"","","","","","","","",10;
        "","","","","","","","",10}
  • 故式四為「3」


式五、INDIRECT("R1C"&式四,0)

  • 式四:3
  • 參照第1列第3欄之值,參照位置表達方式為R0C0
  • 故式五為「12月1日」
    ☆ 儲存格格式設定為「m月d日」


式六、IFERROR(式五,"")

  • 若式五為錯誤值,顯示空值
  • 式五:12月1日
  • 故L2為「12月1日」

※ 上述為陣列公式,故完成輸入後,請按下組合鍵CTRL+SHIFT+ENTER

 

【M2儲存格】
式一、MATCH(L2,$1:$1,0)+1

  • 尋找L2於第1列的位置──3,再加1
  • 故式一為「4」

 

式二、INDEX($A$4:$J$11,0,式一)<0

  • 式一:4
  • 索引A4:J11的整列之第4欄,亦即D4:D11,判斷是否小於0,是回傳TRUE,否回傳FALSE
  • 故式二為{0;0;0;0;0;1;0;0}
    ☆ 為了簡化版面,1代表TRUE,0代表FALSE


式三、ROW($A$4:$A$11)

  • 顯示A4:A11的列數
  • 故式三為{4;5;6;7;8;9;10;11}


式四、IF(式二,式三,"")

  • 若式二為1,顯示式三,反之顯示空值
  • 式二:{0;0;0;0;0;1;0;0}
  • 式三:{4;5;6;7;8;9;10;11}
  • 故式四為{"";"";"";"";"";9;"";""}


式五、COUNTIF($L$2:L2,L2)

  • 計算L2:L2為L2的個數
  • 故式五為「1」


式六、SMALL(式四,式五)

  • 式四:{"";"";"";"";"";9;"";""}
  • 式五:1
  • 取式四第1小之值
  • 故式六為「9」


式七、INDIRECT("A"&式六)

  • 式六:9
  • 參照A9之值
  • 故式七為「PWAAZ0000003」


式八、IFERROR(式七,"")

  • 若式七為錯誤值,顯示空值
  • 式七:PWAAZ0000003
  • 故M2為「PWAAZ0000003」

※ 上述為陣列公式,故完成輸入後,請按下組合鍵CTRL+SHIFT+ENTER

 

【N2儲存格】
式一、MATCH(L2,$1:$1,0)

  • 尋找L2於第1列的位置
  • 故式一為「3」

 

式二、INDIRECT("R3C"&式一,0)

  • 式一:3
  • 參照第3列第3欄之值,參照位置表達方式為R0C0
  • 故式五為「12月1日」
  • 故式二為「8000021518」


式三、IFERROR(式二,"")

  • 若式二為錯誤值,顯示空值
  • 式二:8000021518
  • 故N2為「8000021518」

 

【O2儲存格】
式一、MATCH($M2,$A:$A,0)

  • 尋找M2於A欄的位置
  • 故式一為「9」


式二、MATCH($L2,$1:$1,0)

  • 尋找L2於第1列的位置
  • 故式二為「3」


式三、式二-2+COLUMN(A1)

  • 式二:3
  • COLUMN(A1):顯示A1之欄數,故值為1
  • 故式三為「2」


式四、INDIRECT("R"&式一&"C"&式三,0)

  • 式一:9
  • 式三:2
  • 參照第9列第2欄之值,參照位置表達方式為R0C0
  • 故式四為「2」


式五、IFERROR(式四,"")

  • 若式四為錯誤值,顯示空值
  • 式四:2
  • 故O2為「2」

 

文章標籤
創作者介紹
創作者 Lyc 的頭像
Lyc

*YC的筆記鋪

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