close
標題:

區域陣列公式研習

aa.jpg

 

此文章來自奇摩知識+如有不便請留言告知

發問:

http://www.FunP.Net/473816請問︰下列的45:89和659:778是以什麼作共45列及共120列起迄列的數字標準?又X1:Y1和X2:Y2和X3:Y3應該是填入何列值?10取2共45列*2欄AD4：AE48區域陣列公式AD4=MID(SMALL((MID(ROW(101:189),3,1)>MID(ROW(101:189),2,1))*ROW(101:189),ROW(45:89)),MOD(COLUMN(A:B)-1,2)+2,1)10取3共120列*3欄AW4：AY123區域陣列公式AW4=MID(SMALL((MID(ROW(1012:1789),4,1)>... 顯示更多 http://www.FunP.Net/473816 請問︰ 下列的45:89和659:778是以什麼作共45列及共120列起迄列的數字標準? 又X1:Y1和X2:Y2和X3:Y3應該是填入何列值? 10取2共45列*2欄 AD4：AE48區域陣列公式 AD4 =MID(SMALL((MID(ROW(101:189),3,1)>MID(ROW(101:189),2,1))*ROW(101:189),ROW(45:89)),MOD(COLUMN(A:B)-1,2)+2,1) 10取3共120列*3欄 AW4：AY123區域陣列公式 AW4 =MID(SMALL((MID(ROW(1012:1789),4,1)>MID(ROW(1012:1789),3,1))*(MID(ROW(1012:1789),3,1)>MID(ROW(1012:1789),2,1))*ROW(1012:1789),ROW(659:778)),MOD(COLUMN(A:C)-1,3)+2,1) 10取4共210列*4欄 BS4：BV213區域陣列公式 BS4 =MID(SMALL((MID(ROW(10123:16789),5,1)>MID(ROW(10123:16789),4,1))*(MID(ROW(10123:16789),4,1)>MID(ROW(10123:16789),3,1))*(MID(ROW(10123:16789),3,1)>MID(ROW(10123:16789),2,1))*ROW(10123:16789),ROW(X1:Y1)),MOD(COLUMN(A:D)-1,4)+2,1) 10取5共252列*5欄 CO4：CS255區域陣列公式 CO4 =MID(………….,ROW(X2:Y2)),MOD(COLUMN(A:E)-1,5)+2,1) 10取6共210列*6欄 DL4：DQ213區域陣列公式 DL4 =MID(………….,ROW(X3:Y3)),MOD(COLUMN(A:F)-1,6)+2,1) 以上 敬請各為大師不吝賜教為禱!謝謝^^ 更新: 本題公式參考出處︰ http://tw.knowledge.yahoo.com/question/question?qid=1013112300411 意見012&013

最佳解答:

這公式的原創是 Worlon 大師，我只是順勢裁剪應用而已。 雖然對其邏輯識之無礙，但基於尊重原創，建議去邀請 W大闡釋，較為得宜。 2013-11-26 00:19:51 補充： 110取4共210列*4欄 16789 - 10123 + 1 = 6667 -----Y1 6667 - 210 + 1 = 6458 -----X1 SMALL(......,ROW(6458:6667)) ============================= 10取5共252列*5欄 156789 - 101234 + 1 = 55556 -----Y2 55556 - 252 + 1 = 55305 -----X2 SMALL(......,ROW(55305:55556)) 2013-11-26 00:24:31 補充： 10取6共210列*6欄 1456789 - 1012345 + 1 = 444445 -----Y3 444445 - 210 + 1 = 444236 -----X3 SMALL(......,ROW(444236:444445)) ============================= 各公式中的片段 MOD(COLUMN(A:?)-1,3)+2 在本題的應用上都可簡化為 COLUMN(A:?)+1 10取5及6, ROW超過EXCEL2003的限制，恐不適用。 況且迴圈數過鉅，多耗在無謂的資料上，效率大打折扣。 2013-11-26 00:32:23 補充： 上述公式除了迴圈虛耗外，迴圈內的SMALL函數亦是效率的殺手。 但本題對研究陣列公式者，頗有助益。 個人對陣列公式始終一知半解，參研Worlon大的這個公式，頗多啟迪。 2013-11-27 15:38:51 補充： >>題文： >>下列的45:89和659:778是以什麼作共45列及共120列起迄列的數字標準? >>又X1:Y1和X2:Y2和X3:Y3應該是填入何列值? >>意見014： >>本題是否能得解?也只有看造化了^^" 那就奇了！ 早在意見011～012，不就已詳列了你題文所要的解答！ 而其臚列之算式，也已明示了這些數值的原理。 2013-11-27 16:23:49 補充： 110取4共210列*4欄 16789 - 10123 + 1 = 6667 -----Y1 6667 - 210 + 1 = 6458 -----X1 SMALL(......,ROW(6458:6667)) ============================= 10取5共252列*5欄 156789 - 101234 + 1 = 55556 -----Y2 55556 - 252 + 1 = 55305 -----X2 SMALL(......,ROW(55305:55556)) ============================= 10取6共210列*6欄 1456789 - 1012345 + 1 = 444445 -----Y3 444445 - 210 + 1 = 444236 -----X3 SMALL(......,ROW(444236:444445)) ============================= 各公式中的片段 MOD(COLUMN(A:?)-1,?)+2 在本題的應用上都可簡化為 COLUMN(A:?)+1 10取5及6, ROW超過EXCEL2003的限制，恐不適用。 況且迴圈數過鉅，多耗在無謂的資料上，效率大打折扣。 上述公式除了迴圈虛耗外，迴圈內的SMALL函數亦是效率的殺手。 但本題對研究陣列公式者，頗有助益。 個人對陣列公式始終一知半解，參研 Worlon大 的原始公式，頗多啟迪。

其他解答:

抱歉, 這題又遲到 XD (因為用 "EXCEL" 關鍵字沒搜到此題, 信件又沒每天收) 對上題沒有任何不悅, 請別介意 看到有興趣的題目很高興 想出些甚麼或解出來更高興 有人看, 一起討論, 就更更高興了 ^ ^ 2013-12-01 01:37:13 補充： 關於效率問題, 使用區域陣列大多可以大為提升 (本來每格算一次, 變成全部只算一次) 不過, 的確, 會更抽象些 ^ ^|||||10取5 →252組 10取6 →210組 這種固定格式的解答，公式複製選擇性貼上值後存檔，公式只用一次而已。 且數量又不是多到不可負荷，建議以人工鍵入即可^^ 想研究公式，其意固然可嘉~ 但有時打破砂鍋問到底，也必須遇上對的時候和遇到願意頃囊的人^^" 2013-11-25 22:43:59 補充： 本來是不想多說，既然版大有回應，就一吐為快。 東邪大師所說的該題發問者一開始就有來信邀答， 但我覺得只是一次用性固定使用的列表，就回信說，一式到位的列表公式很難， 需要思考一下(甚至我不會)並建議直接鍵入； 然後他又來信說願意付費，且因是上班族，只能付酬2000元， 我再回信說，的確是非我能力所及，愛莫能助，絕非待價而沽，請其勿誤會。 "續" 2013-11-25 22:44:06 補充： 因此，雖然我明知道W大師近來已不太愛多傷腦筋回答繁瑣的問題， 但我還是幫其熱場，希望他能得到W大師的全力協助。 後來看該發問者的歷史紀錄，感覺該題有可能棄題， 故我又提醒他有無問題都要回應。 我沒資格代該題的解答者W大師或其他參與的大師們發言， 所以大師們的感受我不清楚； 但就我個人而言︰熱場+說明+善意提醒→結果還是棄題!!!!!當然心情不爽， 尤其深感對不起W大師。 也因此今天沒有感性的幫本題的版大熱場， 只是理性的建議以人工鍵入， 畢竟本題也是固定性列表，而非如J大的變動性列表，且有刪除對應作用； 尚請版大見諒。|||||邀請原著者亦是一途， 但是總感覺這樣一來一往，有點二邊不是人~因為一邊婉拒了，才去邀請另一邊~ 無惡意，尚請東邪大師莫怪^^" 2013-11-25 14:56:58 補充： 003意見的一式到位公式︰ 因為各組合數前面都加1~ 在10取2是多100列， 在10取3是多1000列， 在10取4是多10000列， 在10取5是多100000列， 在10取6是多1000000列； 所以10取2~10取4是OK，但10取5~10取6超過65536列^^" E大大加油^^ 2013-11-25 18:23:23 補充： 哈~哈~也就是說該題棄題，參與的人，這幾天心情都不好囉^^" 只好等幾天再說了^^ 2013-11-26 01:27:31 補充： Re︰意見009-010 不好意思，挑起K大大的痛處! Sorry^^" 2013-11-26 01:27:49 補充： 東邪大師︰ 感謝您詳細的說明，小的受教了^^ 看到E大大的公式時，有發覺前面+1位數，10取5~ 6會超過65536列， 雖不適用，但因您之賜而至少知道其數字的計算來源，也是一得。 感謝您! 本題是否能得解?也只有看造化了^^" 晚安! 2013-11-27 16:05:40 補充： 東邪大師︰ 哈~哈~經您一說，再回首看自己的題文， 貴意見011～012確實已詳覆本題需求。 敬請您上答!感恩^^ =============== Sorry~ 自己太專注意見的問與答，不知不覺而將標的轉移至10取5和10取6的超列問題， 而忘了初衷^^" 尚請大師包涵~千萬莫怪~謝謝您^^ 2013-11-27 21:05:04 補充： E大大: 您的研習精神，小弟讚佩b^_^d 如此10取2~9各表列的佳解，相信是許多樂迷想要的~ 閒置在意見區~太可惜了! 請移駕下列問題的回答區 http://tw.knowledge.yahoo.com/question/question?qid=1613112704562 希望藉此也能幫助到其他有需要的知識友^^ 謝謝您囉^^ ========== 另http://tw.knowledge.yahoo.com/question/question?qid=1613112404274 勞請您再以原10取2和10取3公式寫法~繼續賜教10取4~6的公式 麻煩您了^^" 2013-12-01 02:49:16 補充： Worlon 大師︰ 您太客氣了!還特別再回應。謝謝您^^ 區域陣列公式真的效能很高， 但誠如大家所言︰像天書一般^^ 有機會再專程向您請教! 謝謝您^^|||||請"東邪大師"賜教 10取3邏輯教較易懂 =MID(SMALL(IF((MID(ROW($1012:$1789),2,1) 2013-11-25 12:01:47 補充： 補 001 =MID(SMALL(IF((MID(ROW($1012:$1789),4,1)>MID(ROW($1012:$1789),3,1))*(MID(ROW($1012:$1789),3,1)>MID(ROW($1012:$1789),2,1)),ROW($1012:$1789),9^9),ROW(A1)),COLUMN(B1),1) 2013-11-27 19:35:12 補充： http://www.FunP.Net/698822 2013-11-27 19:46:37 補充： 更改說明 : F2:M252 右拉再下拉D4CDE60C03A2F1BC
arrow
arrow
    文章標籤
    百科 原理 大大 更多 格式
    全站熱搜

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

    E-mail轉寄