软体: 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 ※ 编辑: JieJuen 来自: 218.164.49.72 (05/11 19:40)