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

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

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

42^#

發表於 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 三鍵輸入公式

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

43^#

發表於 2013-4-8 16:04 | 只看該作者

回復 42# ML089

剛剛測試了一下，不知道這樣的理解正不正確：
frequency(data_array,bin_array)
若data_array的值為d[1],d[2]...d[n]，bin_array的值為b[1],b[2]...b[x]，回傳result_array的值為r[1],r[2]...r[x+1]
會先搜尋bin_array中，第一個出現最小的值min(bin_array)，假設第一個出現的為b[y]
則r[y]=data_array中小於等於b[y]的值的數量
至於不是第一個出現的b[y']=min(bin_array)，r[y']的值會是零
然後再搜尋bin_array中第一個出現，第二小的值b[z]
r[z]=data_array中，小於等於b[z]且大於b[y]的值的數量，其它的r[z']的值一樣是零
以此類推，然後r[x+1]=data_array中，大於max(bin_array)的值的數量

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

44^#

發表於 2013-4-8 16:52 | 只看該作者

回復 43# Bodhidharma

稍微模擬了一下，試了一些數字，看起來應該是正確的
(懶得用動態範圍，因此把可輸入的資料限定在100列以內)

frequency函數模擬.rar (10.37 KB)

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

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

45^#

發表於 2013-4-8 17:28 | 只看該作者

回復 43# Bodhidharma

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

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

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

46^#

發表於 2013-4-8 17:44 | 只看該作者

回復 Bodhidharma

bin_array 不用排序，若有重複數時，計算值會標註於第一次出現之數位置其他為0
ML089 發表於 2013-4-8 17:28

嗯，反正result_array[x]就是回傳「小於等於bin_array[x]，且大於"bin_array中，次小於bin_array[x]"」這個區間的數值個數
如果已經是bin_array中最小的，則回傳所有小於等於該值的個數
若有重複的話就標註第一個，其它為零
然後大於bin_array中最大數值個數的，就放在result_array的列後一列

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

47^#

發表於 2013-4-8 21:19 | 只看該作者

回復 46# Bodhidharma

把整個函數寫在一起，基本上應該就滿清楚的
請大家幫我看一下這樣的概念正不正確

frequency函數說明.rar (10.83 KB)

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

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

48^#

發表於 2013-4-9 11:24 | 只看該作者

回復 47# Bodhidharma

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

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

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

49^#

發表於 2013-4-9 19:47 | 只看該作者

回復 48# ML089

感謝說明

我試圖把公式寫在同一行，不過發現功力不足
直好另外加一行，把原本的結果當成輔助列

直覺上應該是要用定義名稱，不過我定義名稱還不夠熟
不會處理這種有動態範圍的公式：IF(COUNTIF(OFFSET($B$2,,,ROW()-1,),B2)>1,0,......)

想請教這種有動態範圍的公式，是否可以用定義名稱處理？
(還是說這跟本不是定義名稱的問題？)

frequency函數說明(處理非數字).rar (12.37 KB)

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

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

50^#

發表於 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 三鍵輸入公式