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

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 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 三鍵輸入公式

TOP

Bodhidharma

中學生 Rank: 2

帖子: 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)

TOP

Bodhidharma

中學生 Rank: 2

帖子: 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)的值的數量

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 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 三鍵輸入公式

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 142
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2026-7-16

2025年全勤獎章 2024年全勤獎章 2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

41^#

發表於 2013-4-7 14:52 | 只看該作者

回復 40# Bodhidharma
bin_array非遞增數值陣列時
會以小於等於bin_array陣列元素，並扣除其餘元素以計算過之元素作為計算
例如:陣列元素中有重複的值，因為在第一次出現時已經計算過該值以下的出現頻率
所以第二次出現時就會扣除已計算過的結果

學海無涯_不恥下問

TOP

Bodhidharma

中學生 Rank: 2

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

40^#

發表於 2013-4-7 13:15 | 只看該作者

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

第一個最高次數的值
D2 =IF(ROW()=INT(1%+1/MOD(MAX(OFFSE ...
ML089 發表於 2013-4-7 11:03

嗯，一樣始用 int(1%+1/mod(max(重複次數+1/所在列數),1) 的原理
保留了"重複次數"以及"該列在整個工作表的第幾個位置"的資訊
因此在對應的時候，就不用像我的方式offset半天，找「該列在該組的第幾個位置」，可以直接對應「該列在整個工作表的第幾個位置」

不過E2公式
=IF(B2=B3,INT(N(E3))+1+1/ROW(),"")
並沒有處理"不同組"或是"數值為零"的情況，是否應改為
=IF(B2>0,IF((A2=A3)*(B2=B3),INT(N(E3))+1+1/ROW(),1+1/ROW()),"") ?

另外frequency函數我不是很熟
可否解釋一下，當bin_array非遞增時候該函數的運算過程？

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

39^#

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

38^#

發表於 2013-4-7 10:07 | 只看該作者

回復 36# Bodhidharma

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

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

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

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

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

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 142
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2026-7-16

2025年全勤獎章 2024年全勤獎章 2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

37^#

發表於 2013-4-7 09:42 | 只看該作者

回復 36# Bodhidharma
1.這樣可傳回同群組內B欄各數值的第一次出現的位置，傳回該數值出現次數
=FREQUENCY(OFFSET($B$1,MATCH($A2,$A:$A,0)-1,,COUNTIF($A:$A,$A2),)*10^0,OFFSET($B$1,MATCH($A2,$A:$A,0)-1,,COUNTIF($A:$A,$A2),)*10^0)
2.這樣判斷是否B欄數值為連續出現
=(FREQUENCY(INDIRECT("RC2:R"&MATCH($A2,$A:$A,0)+COUNTIF($A:$A,$A2)-1&"C2",0)*10^0,INDIRECT("RC2:R"&MATCH($A2,$A:$A,0)+COUNTIF($A:$A,$A2)-1&"C2",0)*10^0)=FREQUENCY(INDIRECT("R[1]C2:R"&MATCH($A2,$A:$A,0)+COUNTIF($A:$A,$A2)-1&"C2",0)*10^0,INDIRECT("R[1]C2:R"&MATCH($A2,$A:$A,0)+COUNTIF($A:$A,$A2)-1&"C2",0)*10^0)+1)
3.C欄只要是以上兩條件吻合就是該群組連續最多的眾數
兩個判斷式相乘再乘上B欄的值，就能得到答案

眾數.rar (12.04 KB)

學海無涯_不恥下問

TOP

Bodhidharma

中學生 Rank: 2

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

36^#

發表於 2013-4-7 04:13 | 只看該作者

另外我也有思考是否能增加組名與"眾數"的對照欄
不過因為原po的資料會一直增加，所以很難用動態的方式設定輔助欄
因此後來我就放棄了，還是回到offset的方式來處理
Bodhidharma 發表於 2013-4-7 03:43

其實也還好，用
{=IFERROR(INDEX(A:A,SMALL(IF(OFFSET($A$2,,,COUNTA(A:A),)=OFFSET($A$3,,,COUNTA(A:A),),"",ROW(OFFSET($A$2,,,COUNTA(A:A),))),ROW(A1))),"")}
之類的公式就可以抓出所有的組名
然後眾數位置那欄再弄個動態範圍基本上就符合原po的需求了

TOP

[發問] 找出各群組的眾數(不含0)

[收藏此主題] [關注此主題的新回復]

[通過 QQ、MSN 分享給朋友]