Tom
Lv 4
Tom 發問時間: 電腦與網際網路軟體 · 7 年前

區域陣列公式研習

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?qi...

意見012&013

5 個解答

評分
  • 7 年前
    最佳解答

    這公式的原創是 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大 的原始公式,頗多啟迪。

  • 匿名使用者
    7 年前

    抱歉, 這題又遲到 XD

    (因為用 "EXCEL" 關鍵字沒搜到此題, 信件又沒每天收)

    對上題沒有任何不悅, 請別介意

    看到有興趣的題目很高興

    想出些甚麼或解出來更高興

    有人看, 一起討論, 就更更高興了 ^ ^

    2013-12-01 01:37:13 補充:

    關於效率問題, 使用區域陣列大多可以大為提升

    (本來每格算一次, 變成全部只算一次)

    不過, 的確, 會更抽象些 ^ ^

  • KATO
    Lv 5
    7 年前

    10取5 →252組

    10取6 →210組

    這種固定格式的解答,公式複製選擇性貼上值後存檔,公式只用一次而已。

    且數量又不是多到不可負荷,建議以人工鍵入即可^^

    想研究公式,其意固然可嘉~

    但有時打破砂鍋問到底,也必須遇上對的時候和遇到願意頃囊的人^^"

    2013-11-25 22:43:59 補充:

    本來是不想多說,既然版大有回應,就一吐為快。

    東邪大師所說的該題發問者一開始就有來信邀答,

    但我覺得只是一次用性固定使用的列表,就回信說,一式到位的列表公式很難,

    需要思考一下(甚至我不會)並建議直接鍵入;

    然後他又來信說願意付費,且因是上班族,只能付酬2000元,

    我再回信說,的確是非我能力所及,愛莫能助,絕非待價而沽,請其勿誤會。

    "續"

    2013-11-25 22:44:06 補充:

    因此,雖然我明知道W大師近來已不太愛多傷腦筋回答繁瑣的問題,

    但我還是幫其熱場,希望他能得到W大師的全力協助。

    後來看該發問者的歷史紀錄,感覺該題有可能棄題,

    故我又提醒他有無問題都要回應。

    我沒資格代該題的解答者W大師或其他參與的大師們發言,

    所以大師們的感受我不清楚;

    但就我個人而言︰熱場+說明+善意提醒→結果還是棄題!!!!!當然心情不爽,

    尤其深感對不起W大師。

    也因此今天沒有感性的幫本題的版大熱場,

    只是理性的建議以人工鍵入,

    畢竟本題也是固定性列表,而非如J大的變動性列表,且有刪除對應作用;

    尚請版大見諒。

  • Tom
    Lv 4
    7 年前

    邀請原著者亦是一途,

    但是總感覺這樣一來一往,有點二邊不是人~因為一邊婉拒了,才去邀請另一邊~

    無惡意,尚請東邪大師莫怪^^"

    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?qi...

    希望藉此也能幫助到其他有需要的知識友^^

    謝謝您囉^^

    ==========

    http://tw.knowledge.yahoo.com/question/question?qi...

    勞請您再以原10取2和10取3公式寫法~繼續賜教10取4~6的公式

    麻煩您了^^"

    2013-12-01 02:49:16 補充:

    Worlon 大師︰

    您太客氣了!還特別再回應。謝謝您^^

    區域陣列公式真的效能很高,

    但誠如大家所言︰像天書一般^^

    有機會再專程向您請教! 謝謝您^^

  • 您覺得這個回答如何?您可以登入為回答投票。
  • 7 年前

    請"東邪大師"賜教 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 右拉再下拉

還有問題?馬上發問,尋求解答。