【函數】 >1組的打"V"；=1的打"X" - Office不分區

帖子: 323
主題: 79
精華: 0
積分: 407
點名: 0
作業系統: win7
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2017-10-22
最後登錄: 2022-1-18

12^#

發表於 2017-11-7 11:29 | 只看該作者

回復 11# Hsieh

H超級版主:
感謝您不吝再次指導!
貴解答公式完全達到小弟的需求
感恩^^

joblyc017

　暱稱:

帖子: 111
主題: 3
精華: 0
積分: 156
點名: 0
作業系統: Window
軟體版本: office 365
閱讀權限: 20
性別: 女
註冊時間: 2017-8-14
最後登錄: 2023-6-26

13^#

發表於 2017-11-7 12:01 | 只看該作者

本帖最後由 joblyc017 於 2017-11-7 12:13 編輯

回復 10# papaya

原公式，COUNTIF(OFFSET($E$1:$H$1,ROW($1:$7),),B2)
→以E1:H1為基準，向下移動1、2、3、4、5、6、7列
→向下移動1列為E2:H2
→向下移動2列為E3:H3
→（以此類推）
→向下移動7列為E8:H8
※計算E欄至H欄的各列（2列~8列），主標生肖（B2）出現的次數

樓主修改，COUNTIF(OFFSET($E$1:$H$1,ROW($2:$8),),B2)
→向下移動2列為E3:H3
→向下移動3列為E4:H4
→（以此類推）
→向下移動8列為E9:H9
※沒有計算E2:H2主標生肖（B2）出現的次數，且E9:H9為空值列，無計算出現次數的必要

最右邊的字元與移動列數相符，故將原公式的A2，改成RIGHT(A2,1)，完整公式詳下圖：

帖子: 323
主題: 79
精華: 0
積分: 407
點名: 0
作業系統: win7
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2017-10-22
最後登錄: 2022-1-18

14^#

發表於 2017-11-7 13:20 | 只看該作者

本帖最後由 papaya 於 2017-11-7 13:21 編輯

回復 13# joblyc017
J大:
感謝您不吝再次指導!

謝謝您的詳細解說。
小弟先前也有由F8瞭解貴公式的執行結果。
瞭解ROW($1:$7)是OFFSET的高度範圍。

將A2改為RIGHT(A2,2)是可因應A欄的數字變化；但還是沒有與D欄產生串連；
所以當C2公式拉過C8,C9,....，都顯示"V"；但對應的A8,B8,A9,B9...都是空白儲存格。

另外~如果將A2改為102，B2不變(=虎)，則C2也還是會顯示"V" ；但 2並沒有"虎"。

因此，如果貴公式能因應D欄變化，則小弟就又能多學習到一個解答公式了。
謝謝您^^

joblyc017

　暱稱:

帖子: 111
主題: 3
精華: 0
積分: 156
點名: 0
作業系統: Window
軟體版本: office 365
閱讀權限: 20
性別: 女
註冊時間: 2017-8-14
最後登錄: 2023-6-26

15^#

發表於 2017-11-7 15:01 | 只看該作者

本帖最後由 joblyc017 於 2017-11-7 15:10 編輯

回復 14# papaya

因樓主提供的資料，A欄編號與須移動列數相符，故原公式採OFFSET函數移動列數
簡單例子，如下圖

假設A欄編號不與移動列數相符，如下圖，可使用MATCH函數，尋找A2於D2:D6的相對列數

故是否比對D欄，就依個人習慣和資料內容來決定

「將A2改為102，B2不變(=虎)，則C2也還是會顯示"V" ；但 2並沒有"虎"」的原因
→原公式的判斷寫成「組別次數為1，顯示X，餘顯示V」
→故將A2改為102，B2仍為虎，而E3:H3沒有「虎」時，組別次數為0組，就變成顯示V，因為不是1組

公式調整項目如下：
1.當A欄為空值時，C欄顯示空值→解決「當C2公式拉過C8,C9,....，都顯示"V"；但對應的A8,B8,A9,B9...都是空白儲存格」
2.找不到主標生肖時，組別次數為0，顯示「查無主標生肖」；組別次數為1，顯示「X」；組別次數大於1，顯示「V」
3.解決編號大於10的情況，將原公式的RIGHT(A2,1)，改成A2-100

hcm19522

大學生 Rank: 4

帖子: 1387
主題: 4
精華: 0
積分: 1397
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-11-22

熱烈參與勳章

16^#

發表於 2017-11-7 15:35 | 只看該作者

本帖最後由 hcm19522 於 2017-11-7 15:51 編輯

=IF(SUM(MMULT((INDEX(J$2:M$9,A2,MATCH(B2,OFFSET(E$1:H$1,A2,),))=J$2:M$8)*1,{1;1;1;1})*MMULT((E$2:H$8=B2)*1,{1;1;1;1}))>1,"V","X")
紅色去掉可看組數

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

帖子: 323
主題: 79
精華: 0
積分: 407
點名: 0
作業系統: win7
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2017-10-22
最後登錄: 2022-1-18

17^#

發表於 2017-11-7 15:47 | 只看該作者

回復 15# joblyc017
J大:
再次謝謝您的詳細解說。

針對您只以A欄作主標的之公式寫法，小弟已完全了解了~謝謝您的熱心指導^^
只是為因應A欄的數字變化，公式就必須再更改，有點不易維護^^"

就如H超級版主的公式，可因應A欄數字的任何變化(因為公式中有與D欄數字作連結)；
只是公式中的$E$2:$E$8&$F$2:$F$8&$G$2:$G$8&$H$2:$H$8和$J$2:$J$8&$K$2:$K$8&$L$2:$L$8&$M$2:$M$8，
小弟很想讓其能以$E$2:$H$8和$J$2:$M$8來替代~因為如果遇到二區塊的相對欄位很多時，會比較方便。
但目前小弟還沒有找到適當的函數可因應~尚在努力研究中^^"

帖子: 323
主題: 79
精華: 0
積分: 407
點名: 0
作業系統: win7
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2017-10-22
最後登錄: 2022-1-18

18^#

發表於 2017-11-7 16:01 | 只看該作者

回復 16# hcm19522
h大:
公式測試結果:答案都是#N/A
可能是那裡有筆誤?

MMULT函數
方向應該對了 b^_^d
只是MMULT這個函數，小弟總是學(抓)不到要領^^///

帖子: 323
主題: 79
精華: 0
積分: 407
點名: 0
作業系統: win7
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2017-10-22
最後登錄: 2022-1-18

19^#

發表於 2017-11-7 16:20 | 只看該作者

本帖最後由 papaya 於 2017-11-7 16:21 編輯

回復 16# hcm19522
h大:
抱歉!沒有說清楚
A欄 D欄
1       1
7       2
4       3
7       4
         5
         6
         7

   答案都OK

A欄       D欄
101       101
107       102
104       103
107       104
            105
            106
            107

答案都是#N/A

有辦法一公式二表格通用嗎?
謝謝您^^

hcm19522

大學生 Rank: 4

帖子: 1387
主題: 4
精華: 0
積分: 1397
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-11-22

熱烈參與勳章

20^#

發表於 2017-11-7 16:31 | 只看該作者

本帖最後由 hcm19522 於 2017-11-7 16:32 編輯

回復 19# papaya

A2-->MATCH(A2,D:D,)-1
http://blog.xuite.net/hcm19522/twblog

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/