返回列表 上一主題 發帖

[發問] 統計列數範圍的問題。

[發問] 統計列數範圍的問題。

本帖最後由 ziv976688 於 2018-5-4 16:52 編輯

列數範圍的問題.rar (96.87 KB)
列數範圍的問題.png
2018-5-4 16:48

R2=
統計在B2︰Gn範圍內的各當列,有出現 L2︰Q16各當列相同6個值的次數。

R2公式
=SUM(N(MMULT(COUNTIF(L2:Q2,INDIRECT("B2:G"&COUNTA(A:A))),{1;1;1;1;1;1})=6))

=SUM(N(MMULT(COUNTIF(L2:Q2,B$2:G$10648),{1;1;1;1;1;1})=6))

=SUM(N(MMULT(COUNTIF(L2:Q2,B$2:G$5463),{1;1;1;1;1;1})=6))

=SUM(N(MMULT(COUNTIF(L2:Q2,B$2:G$5462),{1;1;1;1;1;1})=6))

問題︰
當比對範圍列到
COUNTA(A:A) 列(因為A︰G等7欄的資料會不定時續增),16048列,5463列時,公式都無法計算出正確值;

只有比對範圍列到 <= 5462 (Ex︰S2公式)時,則公式才可以計算出正確值。

請問︰
R2的公式要如何修改 ?
謝謝 !

回復 1# ziv976688
參考看看
R2填入
  1. =SUMPRODUCT((L2=B:B)*(M2=C:C)*(N2=D:D)*(O2=E:E)*(P2=F:F)*(Q2=G:G))
複製代碼

TOP

本帖最後由 ziv976688 於 2018-5-5 09:59 編輯

回復 2# naruto018

n大:
這公式不行;但還是謝謝您的熱心賜教^^

TOP

回復 1# ziv976688

MMULT 函數有陣列容量限制,大致為
excel 2007 MMULT的容量有 4^10 = 1048576
excel 2003 MMULT的容量有 5461

我用 EXCEL2007版執行,R2就能執行
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

回復 4# ML089

瞭解了!謝謝您!
請問:
2003版,有其它函數可供R2的公式需求嗎?
謝謝您^^

TOP

回復 5# ziv976688

EXCEL2003版解法
R2 =SUMPRODUCT(N((B$2:B$16048^7+C$2:C$16048^7+D$2:D$16048^7+E$2:E$16048^7+F$2:F$16048^7+G$2:G$16048^7)=(L2^7+M2^7+N2^7+O2^7+P2^7+Q2^7)))
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

回復 6# ML089

測試OK了!
謝謝您的不吝賜教!感恩^^

TOP

本帖最後由 ziv976688 於 2018-5-5 13:47 編輯

回復 6# ML089
本以為^7是7次方的意思;因此測試:
^7全部改為^6   =>答案OK
^7全部改為^3   =>答案NG
所以^7就不是7次方

請問:
以L2:Q2的數字比對B2:G10648有6個儲存格(不限欄位)的數字相同的列數有幾列~
^7是代表什麼意思?
謝謝您^^"

TOP

回復  ML089
本以為^7是7次方的意思;因此測試:
^7全部改為^6   =>答案OK
^7全部改為^3   =>答案NG
所 ...
ziv976688 發表於 2018-5-5 13:32


^7就是7次方的意思;目的就是放大每位數字的間距離,保證各數放大後相加之合具有唯一性。

^3放大間距過小,一般^7以上大致OK

前公式已經可以計算幾個相同
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

回復 9# ML089
謝謝版主的耐心解說,
瞭解了!感恩^^

TOP

        靜思自在 : 犯錯出懺悔心,才能清淨無煩惱。
返回列表 上一主題