[發問] 增寫向左填滿的公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

2^#

發表於 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)輸入公式

右拉複製

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

johnny1680

中學生 Rank: 2

帖子: 55
主題: 15
精華: 0
積分: 69
點名: 0
作業系統: Win7
軟體版本: Office 2003
閱讀權限: 20
註冊時間: 2020-12-6
最後登錄: 2022-1-5

3^#

發表於 2021-10-28 17:56 | 只看該作者

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

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

4^#

發表於 2021-10-28 18:10 | 只看該作者

回復 3# johnny1680

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

文字格式

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

johnny1680

中學生 Rank: 2

帖子: 55
主題: 15
精華: 0
積分: 69
點名: 0
作業系統: Win7
軟體版本: Office 2003
閱讀權限: 20
註冊時間: 2020-12-6
最後登錄: 2022-1-5

5^#

發表於 2021-10-28 18:25 | 只看該作者

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

ziv976688

高中生 Rank: 3 Rank: 3

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

6^#

發表於 2021-10-28 22:27 | 只看該作者

回復 2# ML089
附件 :

TEST_1.rar (2.51 KB)
版大 : 您好 !
不好意思~有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的公式。

以上懇請賜教謝謝您 !

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 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 其他方式

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

8^#

發表於 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(超過數量時, "", 計算)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式