hcm19522

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

papaya

回復 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

回復 12# papaya

papaya

回復 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

回復 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

回復 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))

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

煩請指正!不勝感激!

測試檔
每區段的前三小-EX_1.rar (766.47 KB)

下載次數: 2

2019-4-21 13:15

papaya

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

ML089

公式中的儲存格都要定資料位置，造成公式龐大不易維護，也不易看懂。
最後龐大的公式容易超過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表示取消       

每區段的前三小_ML089.rar (783.81 KB)

下載次數: 2

2019-4-21 19:49

papaya

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

ML089

回復 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)