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

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

論壇榮譽勳章論壇贊助勳章

32^#

發表於 2013-4-4 01:33 | 只看該作者

回復 31# Bodhidharma

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

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

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

TOP

Bodhidharma

中學生 Rank: 2

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

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

中學生 Rank: 2

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

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

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

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

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

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

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

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

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