1234567
返回列表 上一主題 發帖

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

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
35# 跳轉到 »
發表於 2013-4-7 03:43 | 只看該作者
本帖最後由 Bodhidharma 於 2013-4-7 03:45 編輯
1.
眾數讓我以為是要用 MODE() 函數找出現頻率最高的數，
應該是各組中找出最高連續重複數之第一位數位置 ...
ML089 發表於 2013-4-7 01:00


=INT(1%+1/MOD(MAX(INDEX(FREQUENCY(-ROW($1:$88),-ROW($1:$88)*(((A$1:A$88<>G2)+(B$1:B$88<>B$2:B$89)+(B$1:B$88=0))>0))+1/ROW($2:$90),)),1))
這個式子真是博大精深！
利用int(1%+1/mod(max(重複次數+1/所在列數),1)
來回傳"重複次數最多，在最前面出現的資料列數"這個用法很有意思
之前有寫過「重複人名表」之類的東西，我使用的方法是將重複次數*10000+row()，(假設資料小於10000筆)
沒想到還有這種處理方式！

另外frequency函數的用法還滿特別的
frequency(負1到負最後一個數字,負1到負最後一個數字，不過同組非零且與前一列相同的話就是0)
不太了解這種用法為什麼能夠做到"眾數"的要求
frequency我看說明一般好像沒有這種非遞增的bin_array用法，不知道原理是什麼？

我原本也有思考是否能夠完全不用輔助欄，不過想不出來
這個公式可以完全達到我輔助欄「往下數值重複個數」的效果！
(不過在公式效率上因為有用到陣列相乘，應該會略遜於用輔助欄的方式)

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

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
34#
發表於 2013-4-7 01:00 | 只看該作者
1.
眾數讓我以為是要用 MODE() 函數找出現頻率最高的數，
應該是各組中找出最高連續重複數之第一位數位置於C欄標示其值，其他位置標示為 0

2.
提供一個沒有名稱定義的範例，為加速計算增加輔助欄將每一組最多連續重複的第一數位置標訂出來。
=INT(1%+1/MOD(MAX(INDEX(FREQUENCY(-ROW($1:$88),-ROW($1:$88)*(((A$1:A$88<>G2)+(B$1:B$88<>B$2:B$89)+(B$1:B$88=0))>0))+1/ROW($2:$90),)),1))

3.
C欄的公式就能大幅簡化計算
=IF(A2="","",(VLOOKUP(A2,G:H,2,)=ROW())*B2)

4.
範列檔案
眾數_ML089.rar (6.71 KB)
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
33#
發表於 2013-4-4 01:51 | 只看該作者
本帖最後由 Bodhidharma 於 2013-4-4 01:53 編輯
回復  PJChen

試寫了一個，加了一個輔助欄，其實邏輯幾乎跟Hsieh板主一樣

定義名稱純粹只是讓公式不 ...
Bodhidharma 發表於 2013-4-3 23:13


突然發現我「往下數值重複個數」這欄有點問題，每組的最後一個資料一定會是空格
D2儲存格=IF(($A2=$A3)*($B2>0),IF($B2=$B3,D3+1,1),"")
應該修正為
=IF($B2>0,IF((A2=A3)*($B2=$B3),D3+1,1),"")

眾數_3.rar (9.91 KB)

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
32#
發表於 2013-4-4 01:33 | 只看該作者
回復 31# Bodhidharma

"眾數_3"的檔案，目前看來符合我的需求,不過我要進一步套進正常的報表中試試看!!

明天開始連續假期,我要4點多要起床,現在我已經腦袋空空了....要改天才能試了!!我會再回覆最終結果....

感謝你們熱心的幫忙.....感激不盡!!

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
31#
發表於 2013-4-4 01:24 | 只看該作者
本帖最後由 Bodhidharma 於 2013-4-4 01:27 編輯
回復  Bodhidharma

所以當有多個眾數的時候，你希望只顯示第一個....yes
然後只考慮"連續出現最多次". ...
PJChen 發表於 2013-4-4 01:16



"如果分開兩個不同地方出現，就視為不同值？....這個倒不一定,應該是同一組若有二個相同的眾數,以連續值的眾數為優先考量"
耶…我問題的意思是說
x
x
y
y
y
z
z
z
z
y
y
x
x
x
是否在算數量的時候，即便x跟y的總數有5個，但是因為在不同地方出現，所以被看成"2個x,3個y,4個z,2個y',3個x'，所以還是要顯示第一個z?
然後"以連續值的眾數為優先考量"我看不太懂
最後，我的前面那個"眾數_3"的檔案，到底符不符合你的需求啊啊啊？

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
30#
發表於 2013-4-4 01:16 | 只看該作者
回復 29# Bodhidharma

所以當有多個眾數的時候，你希望只顯示第一個....yes
然後只考慮"連續出現最多次"....yes
如果分開兩個不同地方出現，就視為不同值？....這個倒不一定,應該是同一組若有二個相同的眾數,以連續值的眾數為優先考量

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
29#
發表於 2013-4-4 01:09 | 只看該作者
回復  Bodhidharma
回復  Hsieh

請問,當眾數不連續時,如何讓C欄的眾數只出現一次?  
群組        數值        眾數 ...
PJChen 發表於 2013-4-4 00:51



所以當有多個眾數的時候，你希望只顯示第一個，
然後只考慮"連續出現最多次"，如果分開兩個不同地方出現，就視為不同值？

這樣的話，我原始的檔案(眾數_3)應該就是你要的吧？

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
28#
發表於 2013-4-4 00:51 | 只看該作者
回復 27# Bodhidharma
回復 19# Hsieh

請問,當眾數不連續時,如何讓C欄的眾數只出現一次?   眾數.zip (12.76 KB)
群組        數值        眾數        往下數值重複個數        數量        該組眾數        DE欄相乘        G欄最大
C        0        0                1        3.7882        0        8
C        0.44        0        1        1        3.7882        1        8
C        3.9105        0        1        1        3.7882        1        8
C        3.8372        0        1        3        3.7882        3        8
C        3.7882        0        1        4        3.7882        4        8
C        3.7638        0        1        1        3.7882        1        8
C        3.7882        3.7882        2        4        3.7882        8        8
C        3.7882        0        1        4        3.7882        4        8
C        3.8372        0        2        3        3.7882        6        8
C        3.8372        0        1        3        3.7882        3        8
C        3.7882        0        1        4        3.7882        4        8
C        3.0062        0                1        3.7882        0        8

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
27#
發表於 2013-4-4 00:30 | 只看該作者
回復  Bodhidharma

條件越多就越麻煩，輔助欄位就要增加
Hsieh 發表於 2013-4-4 00:07



越看越不懂…雖然多了幾個輔助欄，但是結果似乎和你原本的版本不會有差異
1 0
2 3
2 0
3 3
3 0
4 0
1 0
0 0
的現象還是存在啊

不過我覺得跟本的問題是出現原po沒有講清楚如果有重複的"眾數"要怎麼處理
只顯示第一個：用我原始的檔案
兩個都顯示：C2儲存格=IF(D2=MAX(OFFSET(INDEX(A:A,MATCH(A2,A:A,0),),,3,COUNTIF(A:A,A2),)),B2,0)
只顯示最後一個：Hsieh的第二個輔助欄加我原始的檔案

以及我前面問的：
0
1
1
2
2
2
2
3
3
1
1
1
1
0
這樣是要標2(最早出現的"連續出現最大值")還是要標後面的1(總共出現最多)?

TOP

PJChen
　暱稱: PJ

大學生Rank: 4

帖子
913 
主題
150 
精華
0 
積分
1089 
點名
0  
作業系統
win10 
軟體版本
office 2019 
閱讀權限
50 
性別
女 
註冊時間
2011-8-28 
最後登錄
2023-7-19 

論壇榮譽勳章論壇贊助勳章
26#
發表於 2013-4-4 00:26 | 只看該作者
回復 25# Hsieh
回復 24# Bodhidharma

謝謝啦!!幫我發現問題...
我再拿新的檔查看一下數據,至少以我個人來說,比較能看懂,多幾行公式沒關係的!!

TOP

        靜思自在 : 有智慧才能分辨善惡邪正；有謙虛才能建立美滿人生。
1234567
返回列表 上一主題