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

標題: [發問] 增寫向左填滿的公式 [打印本頁]

作者: johnny1680 時間: 2021-10-28 14:37 標題: 增寫向左填滿的公式

[attach]34290[/attach]
B3 =IF(COUNTIF($B7:$Z7,B5)=0,B5,"")
如果想要第3列的答案如同第9列的答案；
請問：B3的公式要如何再增編？
謝謝！

作者: ML089 時間: 2021-10-28 17:01

B10
=IFERROR(MOD(SMALL(IF(COUNTIF(7:7,$B5:$I5)=0,$B5:$I5+COLUMN($B:$I)*10^5),COLUMN(A1)),10^5),"")
三鍵(CTRL+SHIFT+ENTER)輸入公式

右拉複製

作者: johnny1680 時間: 2021-10-28 17:56

回復 2# ML089
不好意思，可不可以請您指導2003版的公式?
也不用排序；只要向左填滿就可以了。
2007版的IFERROR改成2003版的 ISERROR
公式會好長^^"
謝謝您 !

作者: ML089 時間: 2021-10-28 18:10

回復 3# johnny1680

=INDEX(5:5,SMALL(IF(COUNTIF(7:7,$B5:$I5)=0,COLUMN($B:$I),99),COLUMN(A1)))&""
陣列公式，三鍵輸入

文字格式

作者: johnny1680 時間: 2021-10-28 18:25

回復 4# ML089
版主 :
感謝您的指導

作者: ziv976688 時間: 2021-10-28 22:27

回復 2# ML089
附件 : [attach]34295[/attach]
版大 : 您好 !
不好意思~有2個問題想向您請教 :
B10
=IF(ISERROR(MOD(SMALL(IF(COUNTIF(7:7,$B5:$Z5)=0,$B5:$Z5+COLUMN($B:$Z)*10^5),COLUMN(A1)),10^5)),"",MOD(SMALL(IF(COUNTIF(7:7,$B5:$Z5)=0,$B5:$Z5+COLUMN($B:$Z)*10^5),COLUMN(A1)),10^5))
1_末學不太理解~公式中為何會需要用到取餘數的MOD函數?

2_如果第5列有顯示數字的欄位不確定，如何將公式超拉的欄位顯示""。
EX : B10的公式。

以上懇請賜教謝謝您 !

作者: ML089 時間: 2021-10-28 23:07

回復 6# ziv976688

B10
=IF(ISERROR(MOD(SMALL(IF(COUNTIF(7:7,$B5:$Z5)=0,$B5:$Z5+COLUMN($B:$Z)*10^5),COLUMN(A1)),10^5)),"",MOD(SMALL(IF(COUNTIF(7:7,$B5:$Z5)=0,$B5:$Z5+COLUMN($B:$Z)*10^5),COLUMN(A1)),10^5))
1_末學不太理解~公式中為何會需要用到取餘數的MOD函數?
說明：為了排列位置不變，要以COLUMN NO 作為排序主導，$B5:$Z5 + COLUMN($B:$Z)*10^5 = {200017, ....} 使用SMALL排序，取出值用 MOD( 200017, 10^5) 取出 17 值

2_如果第5列有顯示數字的欄位不確定，如何將公式超拉的欄位顯示""。
超拉變成 "" 方式很多
1 製造錯誤，用IFERROR( ..., "") 來變成 ""
2 製造空格位置，空格用 &"" 會變成 ""，不然會成為 0 ，但其他數字都會變成文字型數字
3 計算有多少量要顯示，超過數量時為 ""，IF( 超量判斷, "", 計算式)
4 其他方式

作者: ML089 時間: 2021-10-28 23:15

回復 6# ziv976688

B11 =IF(COLUMN(A1)>COUNT(0/(COUNTIF($B7:$K7,$B5:$I5)=0)),"",SMALL(IF(COUNTIF($B7:$K7,$B5:$I5)=0,$B5:$I5),COLUMN(A1)))
三鍵輸入，右拉

以數字大小排列方式，用 IF(超過數量時, "", 計算)

作者: ML089 時間: 2021-10-28 23:19

回復 6# ziv976688

B12 =INDEX(5:5,SMALL(IF(COUNTIF($B7:$K7,$B5:$I5)=0,COLUMN($B5:$I5),99),COLUMN(A1)))&""

這是採用 column 99 位置為空格 & "" 來製造空格

作者: ML089 時間: 2021-10-28 23:23

回復 6# ziv976688

B13 =IFERROR(INDEX(5:5,SMALL(IF(COUNTIF($B7:$K7,$B5:$I5)=0,COLUMN($B5:$I5)),COLUMN(A1))),"")
以錯誤來製造空格 IFERROR( …, "")

作者: ML089 時間: 2021-10-28 23:42

回復 6# ziv976688

若是 2003版沒有 IFERROR 函數可以使用，也可用其他方式，例如lookup

B14 =LOOKUP(1,IF({1;0},{0,""},INDEX(5:5,SMALL(IF(COUNTIF($B7:$K7,$B5:$I5)=0,COLUMN($B5:$I5)),COLUMN(A1)))^{0,1}))
三鍵輸入右拉

作者: ziv976688 時間: 2021-10-29 02:00

回復 7# ML089
1_瞭解為何要採取MOD函數的用意了~謝謝您的耐心說明~感恩
2_多種將公式超拉的欄位顯示""的說明~受惠良多

作者: ziv976688 時間: 2021-10-29 02:29

回復 11# ML089
版大 :
您的函數公式修為真是爐火純青~佩服 !

最後末學採用的是 :
B12 =INDEX(5：5,SMALL(IF(COUNTIF($B7：$K7,$B5：$I5)=0,COLUMN($B5：$I5),99),COLUMN(A1)))&""
B14 =LOOKUP(1,IF({1;0},{0,""},INDEX(5：5,SMALL(IF(COUNTIF($B7：$K7,$B5：$I5)=0,COLUMN($B5：$I5)),COLUMN(A1)))^{0,1}))
因為這2個公式可以不綁定位址~在B：AY浮動的範圍內都可適用。

謝謝您耐心的指導多個公式~受惠良多~感恩

作者: hcm19522 時間: 2021-10-29 10:12

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

作者: johnny1680 時間: 2021-10-29 11:06

回復 14# hcm19522
感謝您的幫忙!

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