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

標題: [發問] 組合跳列篩選 [打印本頁]

作者: ziv976688 時間: 2021-10-29 13:36 標題: 組合跳列篩選

附件 : [attach]34298[/attach]
說明：
1_本題範例為7組(實際共50組)。
2_每個組合的搜尋範圍 = C216:AY825。
3_當列和下5列為1個比對組合。
4_第1個比對組合=221:226；第2個比對組合=233:238；第3個比對組合=245：250；
其餘------類推(即每跳12列增加1個比對組合) 。
5_目前Sheet1! C 的公式為一式一列。
PS：
C1的公式是以列221比對列226，並將當列(列221)沒有顯示在下5列(列226)的數字~
顯示在C1→右拉到AY1填滿。其餘 _C2：C7_的公式同理類推。

需求：
請將Sheet2! C 的公式改為一式到位。
謝謝 !

作者: ML089 時間: 2021-10-30 09:14

本帖最後由 ML089 於 2021-10-30 09:16 編輯

C1 陣列公式
=LOOKUP(1,IF({1;0},{0,""},INDEX(列221ALL,SMALL(IF(COUNTIF(列226C_AY,列221C_AY)=0,COLUMN(列221C_AY)),COLUMN(A1)))^{0,1}))

建立公式名稱：先選擇C1，工具列公式 - 名稱管理員 - 新增以下名稱

列221ALL =OFFSET(Sheet2!$A$221:$AY$221,ROW()*12-12,)
列221C_AY =OFFSET(Sheet2!$C$221:$AY$221,ROW()*12-12,)
列226C_AY =OFFSET(Sheet2!$C$226:$AY$226,ROW()*12-12,)

作者: ziv976688 時間: 2021-10-30 10:44

回復 2# ML089
附件 : [attach]34311[/attach]
版大 : 您好 !
感謝指導~辛苦您了

測試結果~公式顯示的值是當列(Ex:列221)的所有值，並沒有移除有出現在下5列(Ex:列226)的相同值。
我將公式放在Sheet2!J1~這樣比較好測試。
請詳見附件。

PS : 末學沒有用名稱定義~因為公式沒有太長(以本題高難度的公式需求，貴公式解已經是超簡短了)。

作者: ML089 時間: 2021-10-30 12:21

本帖最後由 ML089 於 2021-10-30 12:23 編輯

回復 3# ziv976688
詳檔案測試
[attach]34314[/attach]

OFFSET(Sheet2!$C$221:$AY$221,ROW()*12-12,)
1 使用名稱公式設定帶入原公式，執行 OK
2 將其帶入原公式內，執行NG。
主要是在工作表公式裡 OFFSET  參數若使用 ROW()時被視為陣列型態(不定值)，需要在 OFFSET外在掛 N(....)
這個決解方法就是在 ROW(...) 外掛上 MAX( ... )，系統會視為定值
問題是原式加入 OFFSET 及 MAX 後，巢狀迴圈超過數量，無法使用
拿INDEX(...)這段主要公式來測試，
      加 OFFSET(...ROW(...) ...) ，NG
      加 OFFSET(...MAX(ROW(...) ...) ，OK
你可以用 INDEX(...OFFSET(...MAX(ROW(...))...) & ""，也可以達成，只是數字為文字型態
下方舊檔案無法刪除，不用下載

作者: ziv976688 時間: 2021-10-30 13:44

回復 4# ML089
版大 : 您好 !
真不好意思，末學實在是淺薄~沒有想到名稱定義有導正公式型態的作用~
一直以來都以為設定名稱定義公式~只是為了縮減公式字元而已

承您熱心指導本題解答公式，並耐心說明所有呈現的結果與其原由~
末學受益良多~由衷感激

作者: ziv976688 時間: 2021-10-30 19:09

回復 4# ML089
版大 : 您好 !
不好意思~末學才疏學淺~一直打擾您~尚請見諒^^"
附件 : [attach]34318[/attach]
因為表格格式改變~第1列改為標題；跳12列改為跳13列；
所以末學將貴公式解改為~
C2
=INDEX(OFFSET($A$221:$AY$221,MAX(ROW()*13-13),),SMALL(IF(COUNTIF(OFFSET($C$226:$AY$226,MAX(ROW()*13-13),),OFFSET($C$221:$AY$221,MAX(ROW()*13-13),))=0,COLUMN(OFFSET($C$221:$AY$221,MAX(ROW()*13-13),))),COLUMN(A1)))&""
只是不知為何答案不正確(多了1跳)~
公式非得要放在C1~才能顯示正確答案。

請問：公式要如何修正~才能由任1列起始，都可得到正確答案? 謝謝您 ^_^

作者: ziv976688 時間: 2021-10-30 20:27

回復 4# ML089
版大 : 您好 !
連吃飯都在想~終於開竅了~只要將ROW( )改為ROW(A1)就可以了

萬分感謝您一直耐心的指導和熱心幫忙~末學才能功力漸進~感恩

作者: ziv976688 時間: 2021-10-30 21:22

回復 4# ML089
附件 : [attach]34319[/attach]
版大 : 您好 !
你可以用 INDEX(...OFFSET(...MAX(ROW(...))...) & ""，也可以達成，只是數字為文字型態
因為上述公式右拉到AY~會產生許多#NUM!
所以最後還是採用名稱定義的公式~只是過了一段時間要再用到時~末學就常常忘了當初設立定義的公式是什麼用意?

再次謝謝您的耐心指導~感恩

作者: ziv976688 時間: 2021-10-30 22:09

回復 4# ML089
#8的附件弄錯了

這個才對~[attach]34322[/attach]
謝謝

作者: ML089 時間: 2021-10-31 00:18

回復 8# ziv976688

定義名稱公式好像很麻煩，如果資料位置都是名稱定義，日後維護公式反而容易，如要擴張資料範圍或移動資料位置，公式不用改變，只要重新資料位置名稱，修改公式反而容易一些。

作者: ziv976688 時間: 2021-10-31 08:39

回復 10# ML089
瞭解了~謝謝您

作者: 准提部林 時間: 2021-10-31 11:02

=LOOKUP(1,IF({1;0},{0,""},INDEX($A:$AY,ROW(A1)*12+209,SMALL(IF(COUNTIF(INDEX($C$215:$AY$298,ROW(A1)*12,),INDEX($C$210:$AY$293,ROW(A1)*12,))=0,COLUMN($C:$AY),99),COLUMN(A1)))^{0,1}))

作者: 准提部林 時間: 2021-10-31 11:41

2003沒有IFERROR函數, 寫一個自訂函數, 加到需要的檔案中, 即可使用:
[attach]34324[/attach]

作者: ziv976688 時間: 2021-10-31 11:44

回復 12# 准提部林
感謝准大出手救援
測試後~完全符合需求~感恩

PS :
後來發現末學的#7公式也是換了起始列~答案就不對了
因為末學弄了2小時，還是無法達成任一列起始全部都是正解，
所以就想再上來請教M版大~
看到准大已經完成正解~
末學萬分感激

作者: 准提部林 時間: 2021-10-31 11:47

再減幾字
=iferror(INDEX($A:$AY,ROW(A1)*12+209,SMALL(IF(COUNTIF(INDEX($C:$AY,ROW(A1)*12+214,),""&INDEX($C:$AY,ROW(A1)*12+209,))=0,COLUMN($C:$AY)),COLUMN(A$1))),"")

作者: ziv976688 時間: 2021-10-31 12:49

回復 15# 准提部林
正在納悶為何會#12跳到#14~原來是#13沒有即時顯示。
感謝您的指導~IsError轉IFERROR函數的巨集

謝謝指導多樣公式~讓末學有學習的機會~感恩

作者: 准提部林 時間: 2021-10-31 15:41

回復 17# ziv976688

看15樓方式:
ROW(A1)*12+209 = 221 --- 下拉遞增12
ROW(A1)*12+214 = 226

純數字計算, 自己推敲推敲~~

作者: ziv976688 時間: 2021-10-31 15:48

回復 17# 准提部林
不好意思~因為剛剛想到~想自行測試看看~所以刪了提問~但卻再一次與您同步~真巧。
補上提問~測試後~再向您報告~謝謝您^_^
准大 :
請教您~
=LOOKUP(1,IF({1;0},{0,""},INDEX($A:$AY,ROW(A1)*12+209,SMALL(IF(COUNTIF(INDEX($C$215:$AY$298,ROW(A1)*12,),INDEX($C$210:$AY$293,ROW(A1)*12,))=0,COLUMN($C:$AY),99),COLUMN(A1)))^{0,1}))

以C221：AY221比對C226：AY226如果是以$C$215:$AY$298比對$C$210:$AY$293來表達的話~
假設是改為C221：AY221比對C224：AY224
那公式要如何修改?

懇請賜教 ! 謝謝您 !

作者: ziv976688 時間: 2021-10-31 16:30

本帖最後由 ziv976688 於 2021-10-31 16:45 編輯

回復 17# 准提部林
准大 :
221比對224
=LOOKUP(1,IF({1;0},{0,""},INDEX($A:$AY,ROW(A1)*12+209,SMALL(IF(COUNTIF(INDEX($C$213:$AY$296,ROW(A1)*12,),INDEX($C$210:$AY$293,ROW(A1)*12,))=0,COLUMN($C:$AY),99),COLUMN(A1)))^{0,1}))
詳細比對答案無誤。
萬分感謝您的指導~末學受益良多~感恩^_^

PS :
原來是末學將比對的列數弄反了~210+2和293+2~所以跑出的答案不對~
只想到列數差數，忘了考慮到是COUNTIF函數^^*

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