找出各群組的眾數(不含0) - Excelㄧ般區

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

2^#

發表於 2013-4-7 10:07 | 顯示全部帖子

回復 36# Bodhidharma

公式速度要快，需要將每一項都會重複計算的計算數讓想辦法只計算一次，就需要用輔助欄或定義名稱。

我提供的輔助欄公式是採用擴大範圍來計算，如果組項非常多時可以採用動態範圍來替代，可以縮短更多時間。

定義名稱其實也可以加快計算，並非使用定義名稱就會變慢，例如一個公式內會使用好幾次動態範圍，用定義名稱來設動態範圍就能節省不少時間。
若定義名稱相對於每一個儲存格都是 "引數" 都需要根據這些 "引數s"此做一遍計算，這時反而會變得更慢。

台端上面的分析說明寫的很棒，應該給你一個讚。

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

3^#

發表於 2013-4-7 11:03 | 顯示全部帖子

此題採用垂直輔助欄可以用下列公式較為簡單

第一個最高次數的值
D2 =IF(ROW()=INT(1%+1/MOD(MAX(OFFSET(E$1,MATCH(A2,A:A,)-1,,COUNTIF(A:A,A2))),1)),B2,"")

輔助欄 : 是否連續出現，格式設為整數型態較為美觀
E2 =IF(B2=B3,INT(N(E3))+1+1/ROW(),"")

眾數0407.rar (23.43 KB)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

4^#

發表於 2013-4-7 18:05 | 顯示全部帖子

回復 40# Bodhidharma

1.
對，
前E2公式
=IF(B2=B3,INT(N(E3))+1+1/ROW(),"")
並沒有處理"不同組"或是"數值為零"的情況，可以改為
=IF(AND(A2=A3,B2=B3,B2<>0),INT(N(E3))+1+1/ROW(),"")

2.
FREQUENCY看說明確實不容易懂，記得剛開始學此函數時也在網路上找很多實例來了解，仍需做做小模型反覆比較差異才能深入。

FREQUENCY並無遞增或遞減的規定
是計算小於或等於bins_array間的data_array數量。

因為題目要求在連續重複數的第一個顯示其值，因標記數要大於等於data_array(該重複數區內)。

例如 A1:A9中A4:A6是重複數 {1,2,3,4,4,4,7,8,9}

一式
=FREQUENCY(ROW(1:9),ROW(1:9)*(L1:L9<>L2:L10))
={1;1;1;0;0;3;1;1;1;0}

二式
=FREQUENCY(-ROW(1:9),-ROW(1:9)*(L1:L9<>L2:L10))
={1;1;3;0;0;1;1;1;1;0}

兩式都可以得最大重複數 3，但位置卻不同。這就是 ROW(1:9) 與 -ROW(1:9) 差一個負號的用意。

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2013-4-8 17:28 | 顯示全部帖子

回復 43# Bodhidharma

bin_array 不用排序，若有重複數時，計算值會標註於第一次出現之數位置其他為0

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 2013-4-9 11:24 | 顯示全部帖子

回復 47# Bodhidharma

如果能模擬出正確答案，對FREQUENCY函數已經充分了解
另外加一點，非數字自動排除，計算值會往前排列

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 2013-4-9 23:05 | 顯示全部帖子

回復 49# Bodhidharma

I2
=IF(ROW()=COUNTA(B:B)+1,COUNTIF(A:A,">"&MAX(B:B)),IF(OR(ISERR(-B2),MATCH(B2,B:B,)<>ROW()),0,COUNTIF(A:A,"<="&B2)-IF(B2=MIN(B:B),0,COUNTIF(A:A,"<="&SMALL(B:B,COUNTIF(B:B,"<"&B2))))))

一般公式

此公式模擬 FREQUENCY 但沒有處理 bin_array 有非數值之情況，

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式