組合跳列篩選 - Excelㄧ般區 - 麻辣家族討論版版

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

9^#

發表於 2021-10-30 22:09 | 只看該作者

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

這個才對~

7A_1903期統計表(效果檔)_1030.rar (43.3 KB)
謝謝

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

8^#

發表於 2021-10-30 21:22 | 只看該作者

回復 4# ML089
附件 :

7A_1903期統計表.rar (24.84 KB)
版大 : 您好 !
你可以用 INDEX(...OFFSET(...MAX(ROW(...))...) & ""，也可以達成，只是數字為文字型態
因為上述公式右拉到AY~會產生許多#NUM!
所以最後還是採用名稱定義的公式~只是過了一段時間要再用到時~末學就常常忘了當初設立定義的公式是什麼用意?

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

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

7^#

發表於 2021-10-30 20:27 | 只看該作者

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

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

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

6^#

發表於 2021-10-30 19:09 | 只看該作者

回復 4# ML089
版大 : 您好 !
不好意思~末學才疏學淺~一直打擾您~尚請見諒^^"
附件 :

7A_1903期統計表(效果檔).rar (12.41 KB)
因為表格格式改變~第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列起始，都可得到正確答案? 謝謝您 ^_^

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

5^#

發表於 2021-10-30 13:44 | 只看該作者

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

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

版主 Rank: 7 Rank: 7 Rank: 7

ML089

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

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

4^#

發表於 2021-10-30 12:21 | 只看該作者

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

回復 3# ziv976688
詳檔案測試

組合跳列篩選_1030A.rar (23.77 KB)

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(...))...) & ""，也可以達成，只是數字為文字型態
下方舊檔案無法刪除，不用下載

組合跳列篩選_1030.rar (20.66 KB)

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

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

3^#

發表於 2021-10-30 10:44 | 只看該作者

回復 2# ML089
附件 :

組合跳列篩選_1030.rar (20.66 KB)
版大 : 您好 !
感謝指導~辛苦您了

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

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

版主 Rank: 7 Rank: 7 Rank: 7

ML089

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

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

2^#

發表於 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,)

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