Hsieh

回復 10# papaya

C2=IF(SUMPRODUCT(ISNUMBER(FIND($B2,$E$2:$E$8&$F$2:$F$8&$G$2:$G$8&$H$2:$H$8))*ISNUMBER(FIND(INDEX($J$2:$M$8,MATCH($A2,$D$2:$D$8,0),MATCH($B2,INDEX($E$2:$H$8,MATCH($A2,$D$2:$D$8,0),),0)),$J$2:$J$8&$K$2:$K$8&$L$2:$L$8&$M$2:$M$8)))>1,"V","X")
向下複製

papaya

回復 11# Hsieh

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

joblyc017

回復 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)，完整公式詳下圖：
   

下載 (37.24 KB)

2017-11-7 11:45

papaya

回復 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

回復 14# papaya

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

下載 (7.88 KB)

2017-11-7 15:05

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

下載 (8.74 KB)

2017-11-7 15:04

故是否比對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

下載 (39.81 KB)

2017-11-7 15:04

hcm19522

=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")
紅色去掉 可看組數

papaya

回復 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來替代~因為如果遇到二區塊的相對欄位很多時，會比較方便。
但目前小弟還沒有找到適當的函數可因應~尚在努力研究中^^"

papaya

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

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

papaya

回復 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

回復 19# papaya

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