麻辣家族討論版版 - Powered by Discuz! Board

標題: 取前3小(中式排名)的同欄第1列數字。 [打印本頁]

作者: papaya 時間: 2019-4-13 01:56 標題: 取前3小(中式排名)的同欄第1列數字。

本帖最後由 papaya 於 2019-4-13 02:02 編輯

[attach]30377[/attach]

[attach]30378[/attach]

H7=B7:F7的最小數字的第1列同欄數字(最多5個，即下拉到H11)，
I7=B7:F7的第二小數字的第1列同欄數字(最多4個，即下拉到H10)，
J7=B7:F7的第三小數字的第1列同欄數字(最多3個，即下拉到H9)，

H14,H21,H28 =>同理類推。
I14,I21,I28 =>同理類推。
J14,J21,J28 =>同理類推。

請問︰H7︰J7的函數公式？
誠請各位大大幫忙和賜教!
謝謝各位!

作者: papaya 時間: 2019-4-13 05:18

本帖最後由 papaya 於 2019-4-13 05:28 編輯

修正筆誤:
(最多4個，即下拉到H10)=>(最多4個，即下拉到I10)
(最多3個，即下拉到H9)=>(最多3個，即下拉到J9)
請問︰H7︰J7的函數公式？ =>請問︰H7，I7，J7的函數公式？
謝謝!

作者: hcm19522 時間: 2019-4-13 10:36

https://blog.xuite.net/hcm19522/twblog/587328668

作者: papaya 時間: 2019-4-13 12:46

回復 3# hcm19522
hcm19522大大:!您好!
答案測試OK了!謝謝您!

=ISERR(H7) =>不好意思，除錯公式要怎麼寫?
誠請賜教!再次謝謝您!

作者: hcm19522 時間: 2019-4-13 15:52

回復 4# papaya
圈選H7:J28-->點設定格式化條件 (常用下上方中間)-->下公式-->選色

作者: papaya 時間: 2019-4-13 16:30

回復 5# hcm19522
hcm19522大大:您好!
謝謝您的耐心幫忙~感恩^^

我自己自作聰明，出了一些狀況^*^
等整理好檔案，再繼續向您請教!
謝謝您!

作者: papaya 時間: 2019-4-13 19:08

回復 5# hcm19522
hcm19522大大:
不好意思，因為想讓解題者容易閱覽，所以將表格內容內容簡化，但想到添入完整內容卻影響到公式的解答；
因自己的自作聰明，給您添麻煩，尚請見諒^^"

[attach]30384[/attach]

需求︰
將B︰AX共49欄分成5欄+5欄+5欄+5欄+5欄+5欄+5欄+5欄+5欄+4欄共10區段
統計各區段在第7,14,21,28,35,42,49,56,63,69列的最小和第二小和第三小(中式排名)的同欄第一列的數字。
請問︰AZ7︰CC7的函數公式？
詳如︰區段測試檔。
謝謝您！

作者: hcm19522 時間: 2019-4-14 09:47

回復 7# papaya

AZ7=SMALL(OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),MOD(COLUMN(A1)-1,3)+1)

作者: papaya 時間: 2019-4-14 10:59

本帖最後由 papaya 於 2019-4-14 11:12 編輯

回復 8# hcm19522
正確答案
[attach]30389[/attach]

目前公式的答案
[attach]30387[/attach]
[attach]30388[/attach]
hcm19522大大:您好!
不好意思，貴解題公式的答案缺少"中式排名"和"顯示同欄第一列數字"的需求；另外下拉後有非答案的數字。
煩請賜正!
謝謝您!

作者: hcm19522 時間: 2019-4-19 10:12

回復 9# papaya

(每區輔助)AZ6:CC6{=SMALL(IF(IFERROR(MATCH(OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),),99)={1,2,3,4,5},OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)),MOD(COLUMN(A1)-1,3)+1)

AZ7:CC11{=IFERROR(SMALL(IF(OFFSET($B$7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)=N(OFFSET(AZ$1,ROW()-MOD(ROW(A1),7)-1,)),OFFSET($B$1,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)),MOD(ROW(A1)-1,7)+1),"")

AZ6:CC6複製到每區 ;AZ7複製到每區修改一處$B$7-->$B$14 右拉下拉

作者: hcm19522 時間: 2019-4-19 14:21

https://blog.xuite.net/hcm19522/twblog/587392713

作者: papaya 時間: 2019-4-20 13:58

本帖最後由 papaya 於 2019-4-20 13:59 編輯

回復 10# hcm19522
hcm19522大大:您好!
(每區輔助)AZ6:CC6{=SMALL(IF(IFERROR(MATCH(OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),),99)={1,2,3,4,5},OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)),MOD(COLUMN(A1)-1,3)+1)
改為:
=SMALL(IF(IF(ISERROR(MATCH(OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),)),99, MATCH(OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),))={1,2,3,4,5},OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)),MOD(COLUMN(A1)-1,3)+1)
不知道哪裡錯了?顯示"公式錯誤!"
請幫我修正，可以嗎?謝謝您!

AZ7:CC11已將IFERROR改IF(ISERROR)完成。

作者: hcm19522 時間: 2019-4-20 15:23

回復 12# papaya

作者: papaya 時間: 2019-4-20 18:10

回復 13# hcm19522
hcm19522大大:
(每區輔助)AZ6:CC6{=SMALL(IF(IFERROR(MATCH(OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),),99)={1,2,3,4,5},OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)),MOD(COLUMN(A1)-1,3)+1)
您誤會了!我是要如何將2007的 IFERROR改為2003的IF ISERROR
您的範例檔還是2007的 IFERROR，在2003Excel還是無法顯是答案^^"

作者: hcm19522 時間: 2019-4-21 10:08

回復 14# papaya

SMALL(IF(IFERROR(MATCH(OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5),),99)={1,2,3,4,5},OFFSET($B7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)),MOD(COLUMN(A1)-1,3)+1)

刪IFERROR( 與 ,99) (藍色) 最後區刪 ,5) 與 5改4 (紅色)

作者: papaya 時間: 2019-4-21 13:16

本帖最後由 papaya 於 2019-4-21 13:26 編輯

回復 15# hcm19522
hcm19522大大:
感謝您的耐心賜教!

輔助欄AZ6:CC6的公式OK了!謝謝您!

AZ7:CC11的公式
=IFERROR(SMALL(IF(OFFSET($B$7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)=N(OFFSET(AZ$1,ROW()-MOD(ROW(A1),7)-1,)),OFFSET($B$1,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)),MOD(ROW(A1)-1,7)+1),"")
最後一區段，5改4
改成2003版函數
=IF(ISERROR(SMALL(IF(OFFSET($B$7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)=N(OFFSET(AZ$1,ROW()-MOD(ROW(A1),7)-1,)),OFFSET($B$1,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)),MOD(ROW(A1)-1,7)+1)),"",SMALL(IF(OFFSET($B$7,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)=N(OFFSET(AZ$1,ROW()-MOD(ROW(A1),7)-1,)),OFFSET($B$1,,MAX(INT((COLUMN(A1)-1)/3)*5),,5)),MOD(ROW(A1)-1,7)+1))

目前顯示的答案不完全正確~很多儲存格沒有數字(="")^*^

煩請指正!不勝感激!

測試檔
[attach]30451[/attach]

作者: papaya 時間: 2019-4-21 13:33

本帖最後由 papaya 於 2019-4-21 13:36 編輯

補充:
AZ7:CC11的公式改成2003版函數後~
還會造成循環參照^^"
不知道是哪裡改錯了?
煩請指正!謝謝您!

作者: ML089 時間: 2019-4-21 19:49

公式中的儲存格都要定資料位置，造成公式龐大不易維護，也不易看懂。
最後龐大的公式容易超過2003版公式巢狀迴圈限制，單一公式不容易發展。
建議以名稱公式來組合，比較容易維護公式

AZ7 =IF(ISERROR(_AZ7),"",_AZ7)
右拉下拉

AZ7格的名稱公式
_Y =LOOKUP(9,0/("小計"=$A1:$A7),{6,5,4,3,2,1,0})
_X =MAX(INT((COLUMN(A1)-1)/3)*5) * MAX() 主要是避免使用 N(OFFSET())
_W =COUNT(OFFSET($B$1,,_X,,5))       * 一般為5，最後一組為4
_B1 =OFFSET($B$01,,_X,,_W)                * $1:$1
_B71 =OFFSET($B$71,,_X,,_W)          * $71:$71
_B7 =OFFSET($B7,-_Y,_X,,_W)          * 活動位置
_AY7 =IF(MOD(COLUMN(!C1),3)=0,0,INDEX(OFFSET(!$B7:$AX7,-_Y,),MATCH(OFFSET(!AY7,-_Y,),!$B$1:$AX$1,)))
_AZ7 =SMALL(IF(SMALL(_B7+_B71%%*0,1+COUNTIF(_B7,"<="&_AY7  ))=_B7+_B71%%*0,_B1),1-_Y)
* _B71%%*0 本項暫時不用參加排序及驗證， "*0" 乘0表示取消

[attach]30454[/attach]

作者: papaya 時間: 2019-4-21 20:45

回復 18# ML089
ML089版主:您好!
全如需求。
萬分感謝您所作的詳細說明和範例檔。
我會仔細研讀~尤其是名稱定義公式。

作者: ML089 時間: 2019-4-22 09:00

回復 19# papaya
補充

_AY7 =IF(MOD(COLUMN(!C1),3)=0,0,INDEX(OFFSET(!$B7:$AX7,-_Y,),MATCH(OFFSET(!AY7,-_Y,),!$B$1:$AX$1,)))
只適用於 $B$1:$AX$1 內的值不重複時才能使用

AY7 名稱(在AZ7格之名稱公式)可以修改如下
=IF(MOD(COLUMN(!C3),3)=0,0,LOOKUP(,0/(--_B1=--OFFSET(!AY7,_Y,)),_B7)

作者: papaya 時間: 2019-4-22 09:58

回復 20# ML089
ML089版主:您好!
瞭解^^
第一列的01~49數字不會重覆。
感謝您的補充。
您的公式我還在消化中~
希望能達到自己修改為前3大(包括比對71列時，也是取同欄比較的數字)的目的^^"

作者: ML089 時間: 2019-4-22 10:09

回復 21# papaya

需求︰
將B︰AX共49欄分成5欄+5欄+5欄+5欄+5欄+5欄+5欄+5欄+5欄+4欄共10區段
統計各區段在第7,14,21,28,35,42,49,56,63,69列的最小和第二小和第三小(中式排名)的同欄第一列的數字。
請問︰AZ7︰CC7的函數公式？
詳如︰區段測試檔。
謝謝您！

這題的麻煩是
X向為間距5，最後一格間距4
Y向為間距7，最後一格間距6
若能將資料規格排列，亦能使公式更簡潔。
計算資料的核心滿簡單的，可以要公式找資料定位資料耗費太多做業。

作者: papaya 時間: 2019-4-22 11:50

本帖最後由 papaya 於 2019-4-22 11:53 編輯

回復 22# ML089
ML089版主:您好!
我也瞭解問題出在最後一區段的欄列與其他區段不同，但App下載到電腦後轉Excel就是這樣的固定格式，也曾想要以手工插入一空白列，
而且期數更新後，每次貼資料時，資料還要分段重新貼上，尤其是一更改就要動49個檔案*6種類型*3種排序=855個檔案，
然後49個號碼，任何排序的最後區段還是4個號碼，還是無法全部配合一式到底的公式~因此只好以公式來遷就格式~
畢竟公式只要貼一次作範本，期數更新後，只要選擇貼上純值就可以了。
給您在解題時，增添許多的困擾，真是抱歉^^"

再次感謝您的耐心指導^^
後敘:
老實說:我本來是想10個區段分作10個公式，不需要非得一式到底完成~因為公式只要貼一次作範本就不用再更動了。
也曾想以VBA來完成，但想到這麼多式樣的需求，分N次發問，能不能得到最終結果也是未知數，因為常常花了一二個月的時間，得到9成的階段答案，
但最終一成的結果發問題，卻剛好都無人理，前功盡棄又如何?只能徒嘆罷了~所以打消以VBA取得需求的念頭^^"

作者: ML089 時間: 2019-4-22 14:31

回復 23# papaya

了解，盡量配合資料來源格式是比較好

有時大家時間並沒有那麼剛好有空，會造成貼文無人問津，可以過一陣再貼一次。

當然簡化問題或分割問題也需要，一般就能決解的問題大家容易回答，若要想一下，常常想一下就忘記這個問題。

作者: papaya 時間: 2019-4-22 15:19

回復 24# ML089
ML089版主:您好!
不好意思，順道請教一下:
10區段各分成10段公式，公式會簡化些嗎?
如果可以的話，請您寫AZ7:BB7和CA69:CC69就好，其它區段的位址和垂直列數變化我自己更改。
等您有空再說。謝謝您^^

作者: papaya 時間: 2019-4-22 18:28

本帖最後由 papaya 於 2019-4-22 18:30 編輯

回復 24# ML089
補充~
上一樓的AZ7:BB7和CA69:CC69的唯一希望的是2003版的一般數值型陣列公式，
不用一式到底完成，也不用"除錯"。

晚飯後。我再作一個範例，
所以等明天以後，您有空再說就可以了。
謝謝您^^

作者: papaya 時間: 2019-4-22 21:22

本帖最後由 papaya 於 2019-4-22 21:27 編輯

回復 24# ML089
ML089版主:您好!
AZ7
=SMALL(IF(SMALL(IF(MATCH(OFFSET($B$6,LOOKUP(1,0/(B$7:B$7>0),ROW($1:1)),,,5),OFFSET($B$6,LOOKUP(1,0/(B$7:B$7>0),ROW($1:1)),,,5),)={1,2,3,4,5},OFFSET($B$6,LOOKUP(1,0/(B$7:B$7>0),ROW($1:1)),,,5)),COLUMN(A1))=OFFSET($B$6,LOOKUP(1,0/(B$7:B$7>0),ROW($1:1)),,,5),$B$1:$F$1),MOD(ROW(A1),7))
右拉到BB7下拉至BB12

BC7
=SMALL(IF(SMALL(IF(MATCH(OFFSET($G$6,LOOKUP(1,0/(G$7:G$7>0),ROW($1:1)),,,5),OFFSET($G$6,LOOKUP(1,0/(G$7:G$7>0),ROW($1:1)),,,5),)={1,2,3,4,5},OFFSET($G$6,LOOKUP(1,0/(G$7:G$7>0),ROW($1:1)),,,5)),COLUMN(A1))=OFFSET($G$6,LOOKUP(1,0/(G$7:G$7>0),ROW($1:1)),,,5),$G$1:$K$1),MOD(ROW(A1),7))
右拉到BE7下拉至BE12
其餘類推

CA7
=SMALL(IF(SMALL(IF(MATCH(OFFSET($AU$6,LOOKUP(1,0/(AU$7:AU$7>0),ROW($1:1)),,,4),OFFSET($AU$6,LOOKUP(1,0/(AU$7:AU$7>0),ROW($1:1)),,,4),)={1,2,3,4},OFFSET($AU$6,LOOKUP(1,0/(AU$7:AU$7>0),ROW($1:1)),,,4)),COLUMN(A1))=OFFSET($AU$6,LOOKUP(1,0/(AU$7:AU$7>0),ROW($1:1)),,,4),$AU$1:$AX$1),MOD(ROW(A1),7))
右拉到CC7下拉至CE12
其餘類推

AZ69
=SMALL(IF(SMALL(IF(MATCH(OFFSET($B$68,LOOKUP(1,0/(B$69:B$69>0),ROW($1:1)),,,5),OFFSET($B$68,LOOKUP(1,0/(B$69:B$69>0),ROW($1:1)),,,5),)={1,2,3,4,5},OFFSET($B$68,LOOKUP(1,0/(B$69:B$69>0),ROW($1:1)),,,5)),COLUMN(A1))=OFFSET($B$68,LOOKUP(1,0/(B$69:B$69>0),ROW($1:1)),,,5),$B$1:$F$1),MOD(ROW(A1),6))
右拉到BB69下拉至BB73

CA69
=SMALL(IF(SMALL(IF(MATCH(OFFSET($AU$68,LOOKUP(1,0/(AU$69:AU$69>0),ROW($1:1)),,,4),OFFSET($AU$68,LOOKUP(1,0/(AU$69:AU$69>0),ROW($1:1)),,,4),)={1,2,3,4},OFFSET($AU$68,LOOKUP(1,0/(AU$69:AU$69>0),ROW($1:1)),,,4)),COLUMN(A1))=OFFSET($AU$68,LOOKUP(1,0/(AU$69:AU$69>0),ROW($1:1)),,,4),$AU$1:$AX$1),MOD(ROW(A1),6))
右拉到CC69下拉至CC73

範例檔:
[attach]30463[/attach]

如果再加上比對71列(同名次並排者，也是取同欄較小的數字，
請教您:公式要如何編寫?

PS:希望是2003版的一般數值型的陣列公式，不用一式到底完成，也不用"除錯"。
謝謝您^^

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)