→ JieJuen:檔案中附上已較為熟悉的OFFSET法 03/26 05:00
推 ljuber:感謝 哈 因為我要重新編輯的關係XD 03/26 08:36
→ ljuber:並不是要刪除XD 03/26 08:37
推 ljuber:其實後來把左邊的插入下面 如資本適足性插入c3下面就好做多 03/26 08:42
※ 編輯: JieJuen 來自: 218.164.49.72 (05/11 19:40)
軟體: Excel
測試版本: 2003
剛剛看到一個甚妙的題目，不過被刪除了，十分可惜~
可能是不需要用到了(若真不能公佈請告知修改)。
看起來也沒有很難，但對我來說很難解= =
問題:
一工作表如下 ("指標"工作表)
類型 財務比率名稱
資本適足性 ( C) C1
C2
C3
資產品質(A) A1
A2
管理能力(M) M1
獲利能力(E) E1
E2
E3
流動性(L) L1
市場敏感性(S) S1
其他(O) O1
O2
欲轉置成為 ("調整"工作表)
C1 分數 排序 C2 分數 排序 C3 分數 排序 資本適足性 ( C) 排序 A1 …
說明:
希望能連動，因為在做某種系統測試，"指標"工作表的內容會常常變動。
============================================================================
觀察:
1. "指標"也許不會很多，若能直接跑出"調整"的內容而不需參照一些額外的格子(用
定義代替)，比較方便，雖然計算慢也無妨。(計算慢是萬一"指標"很多的話)
2. 是一種不太規則的轉置，轉成橫向並穿插加入1或2欄特定的文字。
名稱就加分數 排序，類型加排序。
3. 公式寫出來大概不太實用，表格型式變更時不容易更改；但此法要對陣列做不少
重新配置，蠻好玩的。結果搞半天重點變成在玩轉陣列 XD
開始:
第一個當然先把類型位置定義好，(日後可改用動態名稱)
Type =指標!$A$2:$A$14
接下來是正式的第一步，但也是最難解釋的一步...所以直接跳過(!!)
不是開完笑 XD，
第一步產生一個一維陣列 m
形狀就是Type
在Type中非空白的格子，都在 m 中給它一個獨一無二的數值，
但是我想跳過怎麼給數值，所以跟直接跳過沒兩樣!!
但為什麼要給數值呢？這倒可以解釋一下，
想用 #19mXg4IQ 8923 這篇的想法，
每一個非空白的格子都給它一個序號，
這樣"可用small由小到大取出 再還原給index去參照"。
從結果來看，原先資料是
資本適足性 ( C) C1
C2
C3
資產品質(A) A1
A2
管理能力(M) M1
獲利能力(E) E1
E2
E3
流動性(L) L1
市場敏感性(S) S1
其他(O) O1
O2
給的數字是(上下翻頁比對)
m 右邊
14.4 2
3
4
4.4 5
6
6.4 7
7.4 8
9
10
10.4 11
11.4 12
12.4 13
14
看右邊，都是資料的列號，可輕易還原~
這個二維陣列就是 n
現在這個陣列已經可以用small取出序號了(s)，前幾位最小的數是
s 代表的內容
2 C1
3 C2
4 C3
4.4 資本適足性 ( C)
5 A1
6 A2
6.4 資產品質(A)
非常令人滿意!(繼續完全無視4.4這種怪數字)
接下來要處理分數 排序的穿插文字了!
第二個"座標"出場~
如果能讓2.1 代表 C1
2.2 代表 "分數"二字
2.3 代表 "排序"二字
然後通通放到某個陣列中，用small依序取出時得到
2.1 2.2 2.3
即可傳回
C1 分數 排序
同理，
3.1 代表 C2
3.2 代表 "分數"二字
3.3 代表 "排序"二字
4.1 代表 C3
4.2 代表 "分數"二字
4.3 代表 "排序"二字
換句話說，讓小數決定做什麼運算
.1 顯示C1 C2 C3等等
.2 "分數"二字
.3 "排序"二字
這樣就太好了
那麼，C3 完畢，下一欄就是久違的 "資本適足性 ( C)" 了 ;P
真巧，它的代號是 4.4，剛好可以接在4.3之後 XDD
繼續順理成章，再加個 4.5 來代表排序，就什麼事都做完了
這個順理成章的陣列ss 長這樣
2.1 2.2 2.3
3.1 3.2 3.3
4.1 4.2 4.3
4.4 4.5 9E+99
5.1 5.2 5.3
6.1 6.2 6.3
6.4 6.5 9E+99
因為 4.4代表的 "資本適足性 ( C)" 右邊只需要一欄 4.5 排序，
所以陣列多了一個沒用的欄位，就擺到最後面(大數)吧
現在所有工作都完成了，除了一個小問題：
我們還沒開始第一步。
還好已經先夢到結果，知道第一步是產生一個 4.4
這.4是表示要傳回 "資本適足性 ( C)" 等等類型，固定不動
左邊的4就有點學問了，複習一下
1 類型 財務比率名稱 D欄
2 資本適足性 ( C) C1 4
3 C2 4
4 C3 4
5 資產品質(A) A1 6
6 A2 6
7 管理能力(M) M1 7
"資本適足性 ( C)" 要擺在 "C3" 的後面
所以這個4代表"C3"的列號
如果能在D欄的D2格寫一個
D2 =IF(B3<>"",ROW(),D3) 就解決了
但是沒有辦法在陣列中 使各元素用到"等於後一個元素"這種寫法呀
(嚴格說來是 後一個又等於後一個,...繼續)
還好，下一個類型"資產品質(A)"的列號5 -1 就變成4了，
對了，是要變4.4，所以-0.6。
不過， "資本適足性 ( C)" 傳回的2-0.6=1.4 沒有用到
考慮最底下
12 市場敏感性(S) S1
13 其他(O) O1
14 O2
"其他(O)"在13列， 13-0.6=12.4
12.4是要接在S1後面的，
結果沒人負責 O2 後面的 14.4 ?!
還記得剛剛 "資本適足性 ( C)" 傳回的 1.4 沒有用到
所以就把它改成 14.4就好啦
算法就是O2的 14+0.4
到這個地步，您沒看累，我都寫累了,,,
如果您再回頭看(還來呀...)，或是一開始就發現(大概也忘了)
應該會發現，
類型傳回的值，不是它本身所代表的值
"資本適足性 ( C)" 傳回14.4 (我希望)它代表 4.4
"資產品質(A)" 傳回4.4 (我希望)它代表 6.4
從頭到尾都是"我希望"，有沒有這麼無理取鬧呀= =
最後當然要做個轉換，算出4.4在 m (遠目...)中的排名
再查一下 Type 中非空白者的列號 TypeRow (又是個新陣列沒錯) "即可"
這裡牽涉到陣列中(不能用RANK函數)的排名算法
(可以用SUMPRODUCT變成普通公式喔)
前面那麼困難的都過了，這個小意思啦~
現在把 ss 隨著欄位增加 依序用small一個一個取出 定義成 a
就可以開始寫公式了！ (聽說結束是另一段旅程的開始)
============================================================================
接下來是備份時間:
直接看答案就看這，從最前面問題問完就接這裡也是可以。
定義 (按出現順序)
Type =指標!$A$2:$A$14
m =IF(Type<>"",IF(ROW(Type)=MIN(ROW(Type)),COUNTA(指標!
$B:$B)+0.4,ROW(Type)-0.6))
n =IF({1,0},m,ROW(Type))
s =SMALL(n,ROW(INDIRECT("1:"&COUNT(n))))
ss =IF(MOD(s,1),s+{0,0.1,9E+99},s+0.1*{1,2,3})
TypeRow =IF(Type<>"",ROW(Type))
a =SMALL(ss,COLUMN(調整!A:A))
(a要在A欄定義)
唯一的公式 (普通公式)
=CHOOSE(RIGHT(a),INDEX(指標!$B:$B,INT(a)),"分數","排序",INDEX(指標!
$A:$A,SMALL(TypeRow,SUMPRODUCT(--(a>=m)))),"排序")
http://2y.drivehq.com/p/IndexArray.xls
--
完全歡迎其他作法分享
本文當是練功吧 XD
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 114.47.34.32