- 帖子
- 2025
- 主題
- 13
- 精華
- 0
- 積分
- 2053
- 點名
- 0
- 作業系統
- WIN7
- 軟體版本
- Office2007
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 台北市
- 註冊時間
- 2011-3-2
- 最後登錄
- 2024-3-14
     
|
本帖最後由 ML089 於 2014-10-16 22:04 編輯
請問S,T,U三欄的公式是什麼呢?
t8899 發表於 2014-10-16 18:09 
6#
請問S,T,U三欄的公式是什麼呢?
7#
找到了,是這四個 ==> INDIRECT({"w","v","X","Y"}
顯示是第一個W , V,X,Y 的順序有差嗎???
前面以經說明公式為多筆儲存格陣列公式，公式一次回傳4個值 已經包含 S,T,U
7#樓的檔案公式如下
AC2:AF2(多筆儲存格陣列公式)
=IF(ROW(A1)>COUNT($K$2:$L$900),"",INDIRECT({"L","I","J","K"}&ROUND(1/MOD(LARGE((0&$K$2:$K$900)+1/ROW($2:$900),ROW(A1)),1),0)))
AG2:AJ2(多筆儲存格陣列公式)
=IF(ROW(A1)>COUNT($Y$2:$Y$900),"",INDIRECT({"W","V","X","Y"}&ROUND(1/MOD(LARGE((0&$Y$2:$Y$900)+1/ROW($2:$900),ROW(A1)),1),0)))
上2式亦可改為單筆陣列公式
AC2 =IF(ROW(A1)>COUNT($K$2:$L$900),"",INDIRECT("L"&ROUND(1/MOD(LARGE((0&$K$2:$K$900)+1/ROW($2:$900),ROW(A1)),1),0)))
AD2 =IF(ROW(A1)>COUNT($K$2:$L$900),"",INDIRECT("I"&ROUND(1/MOD(LARGE((0&$K$2:$K$900)+1/ROW($2:$900),ROW(A1)),1),0)))
AE2 =IF(ROW(A1)>COUNT($K$2:$L$900),"",INDIRECT("J"&ROUND(1/MOD(LARGE((0&$K$2:$K$900)+1/ROW($2:$900),ROW(A1)),1),0)))
AF2 =IF(ROW(A1)>COUNT($K$2:$L$900),"",INDIRECT("K"&ROUND(1/MOD(LARGE((0&$K$2:$K$900)+1/ROW($2:$900),ROW(A1)),1),0)))
AG2 =IF(ROW(A1)>COUNT($Y$2:$Y$900),"",INDIRECT("W"&ROUND(1/MOD(LARGE((0&$Y$2:$Y$900)+1/ROW($2:$900),ROW(A1)),1),0)))
AH2 =IF(ROW(A1)>COUNT($Y$2:$Y$900),"",INDIRECT("V"&ROUND(1/MOD(LARGE((0&$Y$2:$Y$900)+1/ROW($2:$900),ROW(A1)),1),0)))
AI2 =IF(ROW(A1)>COUNT($Y$2:$Y$900),"",INDIRECT("X"&ROUND(1/MOD(LARGE((0&$Y$2:$Y$900)+1/ROW($2:$900),ROW(A1)),1),0)))
AJ2 =IF(ROW(A1)>COUNT($Y$2:$Y$900),"",INDIRECT("Y"&ROUND(1/MOD(LARGE((0&$Y$2:$Y$900)+1/ROW($2:$900),ROW(A1)),1),0)))
|
|
發表於 2014-10-15 17:09
| 
