標題:
[發問]
【函數】 >1組的打"V"；=1的打"X"
[打印本頁]
作者:
papaya
時間:
2017-11-6 11:23
標題:
【函數】 >1組的打"V"；=1的打"X"
本帖最後由 papaya 於 2017-11-6 11:25 編輯
[attach]27906[/attach]
D欄=A欄編號的E︰H區=B欄生肖和同列J︰M區的對應生肖；
在E︰H區和J︰M區~二區同列再一起出現的組合次數︰
=1次時；C2=X
>1次時；C2=V
請問C2的函數公式?
謝謝!
[attach]27907[/attach]
作者:
joblyc017
時間:
2017-11-6 12:25
回復
1#
papaya
「兔」於同列的E8、K8出現，但樓主的示意圖，C3顯示為「X」，不知是否有其他規則？
目前我的解法，判斷兔為「V」
[attach]27908[/attach]
作者:
papaya
時間:
2017-11-6 13:39
回復
2#
joblyc017
J大:
抱歉!沒有完全說清楚^*^
假設E︰H為
主標區
；J︰M為
對應區
；二區的
順序不能對調
。
因為
兔
；
牛
共計1組
所以C3=X
謝謝^^
作者:
joblyc017
時間:
2017-11-6 14:16
本帖最後由 joblyc017 於 2017-11-6 14:25 編輯
回復
3#
papaya
請問所謂的對應是指「E欄、F欄、G欄、H欄 分別對應 J欄、K欄、L欄、M欄」嗎？
若是這樣的對應方式，並且強調「二區的順序不能對調」，
就不太能理解，為何虎和牛是判斷為「V」？
主標位置：G2──虎，相對位置：L2──龍
主標位置：F8──牛，相對位置：K2──兔
還是說規則是以下的情況，這是我個人依資料的猜測：
情況一、G2為虎，假設對應區的J2、K2、L2只要其中一值為虎，就顯示「V」
情況二、G2為虎，假設對應區的J2、K2、L2為「鼠、牛、兔」，而M2為「虎」，則顯示「X」
[attach]27911[/attach]
作者:
Hsieh
時間:
2017-11-6 14:18
本帖最後由 Hsieh 於 2017-11-6 14:22 編輯
回復 joblyc017
J大:
抱歉!沒有完全說清楚^*^
假設E︰H為主標區；J︰M為對應區；二區的順序不能對調。 ...
papaya 發表於 2017-11-6 13:39
既是如此，那C5為何是V?
[attach]27909[/attach]
[attach]27910[/attach]
作者:
papaya
時間:
2017-11-6 16:06
回復
4#
joblyc017
J大:
這樣說好了︰
A欄填入的數字和B填入的文字，是為D欄的相同數字和該編號E︰H主標區的文字
基準組合= E︰H主標區=B填入的文字和在同列J︰M對應區的對應儲存格的文字。
因此︰
當A2=1；B2=虎時；即是以主標區虎(G2)對應龍(L2)為基準組合；
所以當E︰H的他列有出現虎及其同列的J︰M也有出現龍時~
EX︰F5=虎*M5=龍；E7=虎*K7=龍(只要限區同列即可)
則虎龍就有共計3組(基準組合亦計算在內)=>C2=V
當A3=7；B3=兔時；即是以主標區虎(E8)對應牛(J8)為基準組合；
所以當E︰H的他列雖有出現兔，但其同列的J︰M都沒有出現牛時~
則兔牛就只有基準組合共計1組=>C3=X
A4=4；B4=猴時；即是以主標區猴(H5)對應龍(M5)為基準組合；
所以當E︰H的他列雖有出現猴，但其同列的J︰M都沒有出現龍時~
則猴龍就只有基準組合共計1組=>C4=X
當A5=7；B5=牛時；即是以主標區牛(F8)對應兔(K8)為基準組合；
所以當E︰H的他列有出現牛及其同列的J︰M也有出現兔時~
EX︰F2=牛*K2=兔
則牛兔就有共計2組(基準組合亦計算在內)=>C5=V
============================================
可以不用IFERROR函數嗎? MS2003版沒有此函數。
公式能貼在回答欄嗎?相片無法下載。
謝謝您!
作者:
papaya
時間:
2017-11-6 16:08
本帖最後由 papaya 於 2017-11-6 16:10 編輯
回復
5#
Hsieh
H超級版主:
A5=7*B5=牛:即主標區=牛(F8)，其在同列的對應儲存格=兔(K8)=>基準組合；
另在F2有牛，K2有兔；所以共計2組(含基準組合)=>大於1組=>C5=V
===================================
抱歉!公式能貼在回答欄嗎?
檔案暫無權無法下載。
謝謝您!
作者:
joblyc017
時間:
2017-11-6 16:34
回復
6#
papaya
[attach]27913[/attach]
作者:
papaya
時間:
2017-11-6 18:21
回復
8#
joblyc017
J大:
答案正確!
只是公式很深奧，尚在仔細研究中。
先在此向您致謝~感恩^^
作者:
papaya
時間:
2017-11-6 21:13
回復
8#
joblyc017
[attach]27916[/attach]
J大:
C2
=IF(SUMPRODUCT(COUNTIF(OFFSET($E$1:$H$1,
ROW
(
$1:$7
),),B2)*COUNTIF(OFFSET($J$1:$M$1,
ROW
(
$1:$7
),),INDEX($J$2:$M$9,A2,MATCH(B2,OFFSET($E$1:$H$1,A2,),0))))=1,"X","V")
因為看到公式中沒有利用D欄。且如果將公式中的
ROW($1:$7)
改為
ROW($2:$8)
，則答案會有誤；
所以不太了解公式中要如何因應D欄數字的變化。
因此想再向您請教:
如果將D欄編號改為
101~107
那C2公式要如何再編輯?
謝謝您!
[attach]27917[/attach]
作者:
Hsieh
時間:
2017-11-7 11:09
回復
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
時間:
2017-11-7 11:29
回復
11#
Hsieh
H超級版主:
感謝您不吝再次指導!
貴解答公式完全達到小弟的需求
感恩^^
作者:
joblyc017
時間:
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)，完整公式詳下圖：
[attach]27918[/attach]
作者:
papaya
時間:
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
時間:
2017-11-7 15:01
本帖最後由 joblyc017 於 2017-11-7 15:10 編輯
回復
14#
papaya
因樓主提供的資料，
A欄編號與須移動列數相符
，故原公式採OFFSET函數移動列數
簡單例子，如下圖
[attach]27927[/attach]
假設
A欄編號不與移動列數相符
，如下圖，可使用MATCH函數，尋找A2於D2:D6的相對列數
[attach]27926[/attach]
故是否比對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
[attach]27925[/attach]
作者:
hcm19522
時間:
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")
紅色去掉 可看組數
作者:
papaya
時間:
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來替代~因為如果遇到二區塊的相對欄位很多時，會比較方便。
但目前小弟還沒有找到適當的函數可因應~尚在努力研究中^^"
作者:
papaya
時間:
2017-11-7 16:01
回復
16#
hcm19522
h大:
公式測試結果:答案都是#N/A
可能是那裡有筆誤?
MMULT函數
方向應該對了 b^_^d
只是MMULT這個函數，小弟總是學(抓)不到要領^^///
作者:
papaya
時間:
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
時間:
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
作者:
papaya
時間:
2017-11-7 17:04
回復
20#
hcm19522
h大:
您太厲害了!
都解決了!
A2-->MATCH(A2,D:D,)-1
您這神來一筆，連J大的A2盲點，小弟也解決了~@~@
謝謝您的不吝指導!
高手! b^_^d
作者:
joblyc017
時間:
2017-11-7 20:39
本帖最後由 joblyc017 於 2017-11-7 20:43 編輯
回復
21#
papaya
若樓主比對D欄為必要條件，之前提供的解法，也是將A2替換成MATCH(A2,$D:$D,0)-1即可達成
另外，為了維護容易，將原本的INDEX函數改成OFFSET函數，
並修改成只要資料結構不變，列數不超過1001，不須調整公式，
超過1001列，就調整公式兩處的ROW($1:$1000)
→例：調整成ROW($1:$2000)，可自動計數至2001列
修改後之完整公式，詳下圖：
[attach]27928[/attach]
作者:
papaya
時間:
2017-11-8 07:41
回復
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超級版主的公式可呈現搜尋主標的區和對應區的生肖，各組合的生肖必須是出現在
相對應儲存格
的條件。
這一題承蒙各位先進不吝的指導
小弟獲益良多
感恩再感恩.........^^
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
