[發問] 【函數】 >1組的打"V"；=1的打"X"

papaya

下載 (158.66 KB)

2017-11-6 11:23

D欄=A欄編號的E︰H區=B欄生肖和同列J︰M區的對應生肖；
在E︰H區和J︰M區~二區同列再一起出現的組合次數︰
=1次時；C2=X
>1次時；C2=V

請問C2的函數公式?
謝謝!
12生肖.rar (2.38 KB)

下載次數: 8

2017-11-6 11:23

papaya

回復 22# joblyc017
J大:
感謝您不吝再次指導!
瞭解了!感恩^^


後來發現H超級版主的公式中已有呈現MATCH($A2,$D$2:$D$8,0)，只時當時小弟專注在想簡化$E$2:$E$8&$F$2:$F$8&....，所以沒有注意到^^"
另如果將$E$2:$E$8&$F$2:$F$8&....改為$E$2:$H$8；$J$2:$J$8&$K$2:$K$8&......改為$J$2:$M$8；
則H超級版主的公式可呈現搜尋主標的區和對應區的生肖，各組合的生肖必須是出現在相對應儲存格的條件。

這一題承蒙各位先進不吝的指導
小弟獲益良多
感恩再感恩.........^^

joblyc017

回復 21# papaya

若樓主比對D欄為必要條件，之前提供的解法，也是將A2替換成MATCH(A2,$D:$D,0)-1即可達成

另外，為了維護容易，將原本的INDEX函數改成OFFSET函數，
並修改成只要資料結構不變，列數不超過1001，不須調整公式，
超過1001列，就調整公式兩處的ROW($1:$1000)
→例：調整成ROW($1:$2000)，可自動計數至2001列

修改後之完整公式，詳下圖：

下載 (38.34 KB)

2017-11-7 20:17

papaya

回復 20# hcm19522
h大:
您太厲害了!
都解決了!
A2-->MATCH(A2,D:D,)-1
您這神來一筆，連J大的A2盲點，小弟也解決了~@~@

謝謝您的不吝指導!
高手!  b^_^d

hcm19522

回復 19# papaya

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

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   

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

papaya

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

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

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

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

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