麻辣家族討論版版's Archiver

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

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117445&ptid=23446]2#[/url] [i]ML089[/i] [/b]
不好意思,可不可以請您指導2003版的公式?
[color=Red][b]也不用排序[/b][/color];[b]只要[color=Blue]向左填滿[/color]就可以了[/b]。
2007版的IFERROR改成2003版的 ISERROR
公式會好長^^"
謝謝您 !

ML089 發表於 2021-10-28 18:10

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117447&ptid=23446]3#[/url] [i]johnny1680[/i] [/b]

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

文字格式

johnny1680 發表於 2021-10-28 18:25

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117448&ptid=23446]4#[/url] [i]ML089[/i] [/b]
版主 :
感謝您的指導:lol

ziv976688 發表於 2021-10-28 22:27

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117445&ptid=23446]2#[/url] [i]ML089[/i] [/b]
附件 :   [attach]34295[/attach]
[font=Verdana]版大 : 您好 !
不好意思~有[b][color=Blue]2個[/color][/b]問題想向您請教 :
B10
=IF(ISERROR([color=Blue][b]MOD[/b][/color](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))
[b][color=Blue]1_[/color][/b][b]末學不太理解~[/b]公式中為何會需要用到[color=Blue][b]取餘數[/b][/color]的[color=Blue][b]MOD[/b][/color]函數?

[b][color=Blue]2_[/color][/b]如果第5列有顯示數字的欄位[color=Magenta][b]不確定[/b][/color],如何將公式[color=Magenta][b]超拉的欄位顯示""[/b][/color]。
EX : B10的公式。

[b]以上    懇請賜教     謝謝您 ![/b]
[/font]

ML089 發表於 2021-10-28 23:07

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117454&ptid=23446]6#[/url] [i]ziv976688[/i] [/b]

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

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117454&ptid=23446]6#[/url] [i]ziv976688[/i] [/b]


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

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117454&ptid=23446]6#[/url] [i]ziv976688[/i] [/b]

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

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117454&ptid=23446]6#[/url] [i]ziv976688[/i] [/b]


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

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117454&ptid=23446]6#[/url] [i]ziv976688[/i] [/b]

若是 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

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117457&ptid=23446]7#[/url] [i]ML089[/i] [/b]
[font=Verdana][b][color=Blue][b]1_[/b][/color]瞭解為何要採取[color=Blue]MOD[/color]函數的用意了~謝謝您的耐心說明~感恩:handshake
[color=Blue]2_[/color]多種將公式超拉的欄位顯示""的說明~受惠良多[/b]:victory:
[/font]

ziv976688 發表於 2021-10-29 02:29

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117461&ptid=23446]11#[/url] [i]ML089[/i] [/b]
[font=Verdana][b]版大[/b] :
[b]您的函數公式修為真是[color=Blue]爐火純青[/color]~佩服 ![/b]:lol

最後末學[b][color=Blue]採用[/color][/b]的是 :
[color=Blue][b]B12[/b][/color] =INDEX(5:5,SMALL(IF(COUNTIF($B7:$K7,$B5:$I5)=0,COLUMN($B5:$I5),99),COLUMN(A1)))&""
[b][color=Blue]B14 [/color][/b]=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個公式可以[color=Magenta][b]不綁定位址[/b][/color]~[b][color=Blue]在B:AY浮動的範圍內都可適用[/color][/b]。

[b]謝謝您耐心的指導多個公式~受惠良多~感恩[/b]:handshake
[/font]

hcm19522 發表於 2021-10-29 10:12

[url]https://blog.xuite.net/hcm19522/twblog/590097036[/url]

johnny1680 發表於 2021-10-29 11:06

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117471&ptid=23446]14#[/url] [i]hcm19522[/i] [/b]
感謝您的幫忙!

頁: [1]

麻辣家族討論版版為 麻辣學園 網站成員  由 昱得資訊工作室 © Since 1993 所提供